快捷導(dǎo)航

SQL?Server數(shù)據(jù)庫(kù)入門(mén)教程之多表查詢

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

這篇文章主要給大家介紹了關(guān)于SQL?Server數(shù)據(jù)庫(kù)入門(mén)教程之多表查詢的相關(guān)資料,多表查詢是SQL數(shù)據(jù)庫(kù)里的高級(jí)查詢語(yǔ)句,文中通過(guò)圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

SQL_Server的前情介紹

??大家好呀，今天是我第三次寫(xiě)sql_server，也是最近才學(xué)習(xí)sql_server，也想著記錄一下自己的學(xué)習(xí)過(guò)程，并且分享給大家尼！

一、sql_server技術(shù)介紹

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

二、學(xué)習(xí)前的準(zhǔn)備工作

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

SQL_Server之多表查詢

笛卡爾乘積的講解

在數(shù)據(jù)庫(kù)中有一種叫笛卡爾乘積其語(yǔ)法如下：

select * from People,Department

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

多表查詢

接下來(lái)我們來(lái)看幾個(gè)例子吧！

1.查詢員工信息，顯示部門(mén)信息

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

2.查詢員工信息，顯示職級(jí)名稱(chēng)

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

3.查詢員工信息，顯示部門(mén)名稱(chēng)，顯示職級(jí)名稱(chēng)

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

內(nèi)連接查詢

在數(shù)據(jù)庫(kù)的查詢過(guò)程中，存在有內(nèi)連接查詢，這個(gè)時(shí)候，我們就需要用到inner這個(gè)關(guān)鍵字，下面我們來(lái)看幾個(gè)例子吧！

1.查詢員工信息，顯示部門(mén)信息

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

2.查詢員工信息，顯示職級(jí)名稱(chēng)

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

3.查詢員工信息，顯示部門(mén)名稱(chēng)，顯示職級(jí)名稱(chēng)

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

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

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

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

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

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

3.查詢員工信息，顯示部門(mén)名稱(chēng)，顯示職級(jí)名稱(chēng)(左外連)

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

全外連查詢(無(wú)論是否符合關(guān)系，都要顯示數(shù)據(jù))

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

多表查詢的主要例子

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

select PeopleId 員工編號(hào),DepartmentName 部門(mén)名稱(chēng),PeopleName 員工姓名,PeopleSex 員工性別,
PeopleBirth 員工生日,PeoPleSalary 月薪,PeoplePhone 電話,PeopleAddress 地址
from People,department where People.departmentId = department.DepartmentId

2.查詢出武漢地區(qū)所有員工的信息，要求顯示部門(mén)名稱(chēng)，職級(jí)名稱(chēng)以及員工的詳細(xì)資料

select PeopleId 員工編號(hào),DepartmentName 部門(mén)名稱(chēng),RankName 職級(jí)名稱(chēng), 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.根據(jù)部門(mén)分組統(tǒng)計(jì)員工人數(shù)，員工工資總和，平均工資，最高工資和最低工資

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

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

select DepartmentName 部門(mén)名稱(chēng), count(*) 員工人數(shù),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.根據(jù)部門(mén)名稱(chēng)，然后根據(jù)職位名稱(chēng)分組，統(tǒng)計(jì)員工人數(shù)，員工工資總和
平均工資，最高工資，和最低工資

select DepartmentName 部門(mén)名稱(chēng),RankName 職級(jí)名稱(chēng), count(*) 員工人數(shù),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