?? 博客主頁：博主鏈接
?? 本文由 M malloc 原創(chuàng)，首發(fā)于 CSDN??
?? 學(xué)習(xí)專欄推薦：LeetCode刷題集！
?? 歡迎點贊 ?? 收藏 ?留言 ?? 如有錯誤敬請指正！
?? 未來很長，值得我們?nèi)Ρ几案篮玫纳?
------------------??分割線??-------------------------
————————————————

??SQL_Server的前情介紹

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

?? 一、sql_server技術(shù)介紹

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

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

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

??SQL_Server之多表查詢

??笛卡爾乘積的講解

在數(shù)據(jù)庫中有一種叫笛卡爾乘積其語法如下：
select * from People,Department

此查詢結(jié)果會將People表的所有數(shù)據(jù)和Department表的所有數(shù)據(jù)進行依次排列組合形成新的記錄。例如People表有10條記錄，Department表有3條記錄，則排列組合之后查詢結(jié)果會有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 

??內(nèi)連接查詢

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

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

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

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.根據(jù)部門分組統(tǒng)計員工人數(shù)，員工工資總和，平均工資，最高工資和最低工資

select DepartmentName 部門名稱, 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ù)部門分組統(tǒng)計員工人數(shù)，員工工資總和，平均工資，最高工資和最低工資平均工資在10000元以下的不參與排序。根據(jù)平均工資降序排序

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

select DepartmentName 部門名稱,RankName 職級名稱, 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

??如何鞏固學(xué)習(xí)

提示：在學(xué)習(xí)的過程中，我們需要先自行進行思考，而不是一遇到不會的就放棄思考直接看答案，如果最后遇到真的不會的題目，我們可以適當(dāng)?shù)倪M行觀看答案，看自己的思路是否正確，在作出正確的判斷

好啦今日的分享到這里就結(jié)束啦，我是愛你們的M malloc希望可以幫助到你們噢，最后別忘記三連啦??！

提醒：以上內(nèi)容僅可自學(xué)，不可自行發(fā)散。如許轉(zhuǎn)載請聯(lián)系作者，否則將追究責(zé)任。

文章來源地址http://www.zghlxwxcb.cn/news/detail-477012.html

到了這里，關(guān)于[SQL Server]數(shù)據(jù)庫入門之多表查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！