腳本之家服務器常用軟件

快捷導航

SQL?Server數據庫入門教程之多表查詢

更新時間：2023年06月27日 09:58:28 作者：M?malloc

這篇文章主要給大家介紹了關于SQL?Server數據庫入門教程之多表查詢的相關資料,多表查詢是SQL數據庫里的高級查詢語句,文中通過圖文以及實例代碼介紹的非常詳細,需要的朋友可以參考下

SQL_Server的前情介紹

??大家好呀，今天是我第三次寫sql_server，也是最近才學習sql_server，也想著記錄一下自己的學習過程，并且分享給大家尼！

一、sql_server技術介紹

SQL Server 是由微軟公司（Microsoft）開發(fā)的關系型數（RDBMS）。RDBMS 是 SQL 以及所有現(xiàn)代數據庫系統(tǒng)的基礎，比如 MS SQL Server，IBM DB2，Oracle，MySQL 以及微軟的 Microsoft Access。

二、學習前的準備工作

編程軟件：SQL Server Management Studio 2012
帶好你的小板凳，我們一起揚帆起航！

SQL_Server之多表查詢

笛卡爾乘積的講解

在數據庫中有一種叫笛卡爾乘積其語法如下：

select * from People,Department

此查詢結果會將People表的所有數據和Department表的所有數據進行依次排列組合形成新的記錄。例如People表有10條記錄，Department表有3條記錄，則排列組合之后查詢結果會有10*3=30條記錄.

多表查詢

接下來我們來看幾個例子吧！

1.查詢員工信息，顯示部門信息

select * from People,department where People.DepartmentId = department.DepartmentId

2.查詢員工信息，顯示職級名稱

select * from People,s_rank where People.RankId = s_rank.RankId

3.查詢員工信息，顯示部門名稱，顯示職級名稱

select * from People,department,s_rank 
	where People.departmentId = department.DepartmentId and People.RankId = s_rank.RankId

內連接查詢

在數據庫的查詢過程中，存在有內連接查詢，這個時候，我們就需要用到inner這個關鍵字，下面我們來看幾個例子吧！

1.查詢員工信息，顯示部門信息

select * from People inner join department on People.departmentId = department.DepartmentId

2.查詢員工信息，顯示職級名稱

select * from People inner join s_rank on People.RankId = s_rank.RankId

3.查詢員工信息，顯示部門名稱，顯示職級名稱

select * from People inner join department on People.departmentId = department.DepartmentId
inner join s_rank on People.RankId = s_rank.RankId

外連接查詢(左外連，右外連，全外連)

1.查詢員工信息，顯示部門信息(左外連)

select * from People left join department on People.departmentId = department.DepartmentId

2.查詢員工信息，顯示職級名稱(左外接)

select * from People left join s_rank on People.RankId = s_rank.RankId

3.查詢員工信息，顯示部門名稱，顯示職級名稱(左外連)

select * from People left join department on People.departmentId = department.DepartmentId
inner join s_rank on People.RankId = s_rank.RankId

4.右外連

A left join B = B right join A

select * from People right join department on People.departmentId = department.DepartmentId

全外連查詢(無論是否符合關系，都要顯示數據)

1.select * from People full join department on People.departmentId = department.DepartmentId

多表查詢的主要例子

1.查詢出武漢地區(qū)所有的員工信息，要求顯示部門名稱，以及員工的詳細資料（顯示中文別名）

select PeopleId 員工編號,DepartmentName 部門名稱,PeopleName 員工姓名,PeopleSex 員工性別,
PeopleBirth 員工生日,PeoPleSalary 月薪,PeoplePhone 電話,PeopleAddress 地址
from People,department where People.departmentId = department.DepartmentId

2.查詢出武漢地區(qū)所有員工的信息，要求顯示部門名稱，職級名稱以及員工的詳細資料

select PeopleId 員工編號,DepartmentName 部門名稱,RankName 職級名稱, PeopleName 員工姓名,PeopleSex 員工性別,
PeopleBirth 員工生日,PeoPleSalary 月薪,PeoplePhone 電話,PeopleAddress 地址
from People,department,s_rank where People.departmentId = department.DepartmentId 
and People.RankId = s_rank.RankId and PeopleAddress = '武漢'

3.根據部門分組統(tǒng)計員工人數，員工工資總和，平均工資，最高工資和最低工資

select DepartmentName 部門名稱, count(*) 員工人數,sum(PeopleSalary) 工資總和,avg(PeopleSalary) 平均工資,max(PeopleSalary) 最高工資,min(PeopleSalary) 最低工資 
from People,department
	where People.departmentId = department.DepartmentId
		group by department.DepartmentId,DepartmentName

4.根據部門分組統(tǒng)計員工人數，員工工資總和，平均工資，最高工資和最低工資平均工資在10000元以下的不參與排序。根據平均工資降序排序

select DepartmentName 部門名稱, count(*) 員工人數,sum(PeopleSalary) 工資總和,avg(PeopleSalary) 平均工資,max(PeopleSalary) 最高工資,min(PeopleSalary) 最低工資 
from People,department
	where People.departmentId = department.DepartmentId 
		group by department.DepartmentId,DepartmentName	
			having avg(PeopleSalary) >= 15000 
				order by avg(PeopleSalary) desc

5.根據部門名稱，然后根據職位名稱分組，統(tǒng)計員工人數，員工工資總和
平均工資，最高工資，和最低工資

select DepartmentName 部門名稱,RankName 職級名稱, count(*) 員工人數,sum(PeopleSalary) 工資總和,avg(PeopleSalary) 平均工資,max(PeopleSalary) 最高工資,min(PeopleSalary) 最低工資 
from People,department,s_rank
	where People.departmentId = department.DepartmentId and People.RankId = s_rank.RankId
		group by department.DepartmentId,DepartmentName,s_rank.RankId,s_rank.RankName