第七單元條件查詢，分組查詢

1. 條件查詢條件查詢是通過where子句進行檢索的查詢方式。 select 欄位名1, 欄位名2, …, 欄位名n from 數據表名 where 查詢條件 1. 如何使用排序（升序，降序）？ asc:表示升序（預設排序方式） desc:降序排序語法：select ... from 表名 [條 ...

1. 條件查詢

條件查詢是通過where子句進行檢索的查詢方式。

select 欄位名1, 欄位名2, …, 欄位名n 
from 數據表名 
where 查詢條件

1. 如何使用排序（升序，降序）？

asc:表示升序（預設排序方式） desc:降序

排序語法：select ... from 表名 [條件] order by 要排序的欄位 asc/desc

-- 將成績從高到低進行排序
select * from StudentScore order by (theoryScore+skillScore) 


-- 查詢學生表，按姓名升序顯示
select * from StudentInfo order by stuName asc -- asc 可以省略

2. 多欄位如何排序？

語法: select ... from 表名 [條件] order by 第一要排序的欄位 asc/desc ，第二要排序的欄位 asc/desc

 -- 查詢成績表，先按學生編號從低到高排序，再按成績從高到低顯示
 select * from StudentScore order by stuId asc,(theoryScore+skillScore) desc


 -- 查詢學生信息，先按班級進行升序，再按生日進行降序
 select * from StudentInfo order by ClassId asc ,stuBirthday desc

3. 如何使用關係表達式查詢（大於，小於，等於）？

 -- 查詢 班級編號 在 1,3,5,7 里的學生信息
 select * from StudentInfo where ClassId in(1,3,5,7)


-- 查詢班級編號 不是偶數的學生信息
select * from StudentInfo where ClassId%2!=0

-- 查詢技能成績大於90分的成績信息
select * from StudentScore where skillScore>90

4. 如何使用between...and 的語法？

between ... and :在...之間，應用範圍：數字類型

-- 查詢技能成績在60-80之間的成績信息
-- 寫法1
select * from StudentScore where  skillScore between 60 and 80
-- 寫法2
select * from StudentScore where  skillScore>=60 and skillScore<=80

5. 如何統計個數，平均分，最大數，最小數，求和？

函數	欄位類型	描述
Min(欄位)	數字類型	最小值
Max(欄位)	數字類型	最大值
Sum(欄位)	數字類型	總和
count(欄位)	無限制	個數
count(distinct 欄位)	無限制	去重後，求個數
avg(欄位)	數字類型	平均數

-- 統計學生的個數
 select count(stuId) from StudentInfo

 -- 統計女生的個數
 select count(stuId) as 女生個數 from StudentInfo where stuSex='女'



 -- 查詢學生平均分
 select avg(skillScore+theoryScore) from StudentScore

 -- 查詢課程名稱為'C# 入門編程'的平均分
  select avg(skillScore+theoryScore) from StudentScore where CourseName='C# 入門編程'

 -- 查詢班級編號最大的班級
 -- 寫法1
 select max(classId) from StudentInfo
 -- 寫法2
select top 1 (skillScore+theoryScore) from StudentScore order by (skillScore+theoryScore) desc
 
 -- 計算最低分
 -- 寫法1
select min(skillScore+theoryScore) from StudentScore
-- 寫法2
select top 1 (skillScore+theoryScore) from StudentScore order by (skillScore+theoryScore) asc



 -- 計算技能總分
 select sum(skillScore) from StudentScore

-- 計算學生編號為180325011 的技能總分
select sum(skillScore) from StudentScore where stuId='180325011'


-- 查詢有多少個人參加了考試
select count(distinct stuId) from StudentScore

2. 聚合函數

6. 能否將所有聚合函數寫在一起？

select 
count(distinct stuId) 學生數量,
Min(skillScore) 最小值,
Max(skillScore) 最大值,
Sum(skillScore) 總和
from StudentScore

3. exists 查詢

功能：用於嵌套查詢 exists後的查詢語句有結果則為真，無結果則為假,如果為真，則執行外層查詢，否則外層查詢不執行語法格式：

select 欄位名1, 欄位名2, …, 欄位名n 
from 數據表名 
where exists 
（select 欄位名 from 數據表名 where …）

-- 如果存在大於20歲,則查詢結果,
select * from StudentInfo where exists
(
    select * from StudentInfo where year(getdate())-year(stuBirthday)>20
)

 
--如果班級裡面有兩個以上的老王,則把老王的信息查詢出來
select * from StudentInfo where exists
(
    select * from
    (
        select count(stuId) as 數量 from StudentInfo where stuName like '王%'
    ) a where a.數量>=1
)

4. 分組查詢

8.如何使用分組查詢?他們的關鍵字分別是什麼？

-語法：select 要分組的欄位，聚合函數 from 表名 group by 要分組的欄位

-group by:分組

小技巧：每什麼就以哪個欄位進行分組

--9.計算每門課程的平均分
select CourseName, avg(skillScore) as 平均分 from StudentScore group by CourseName


--10.統計每個學生的平均分
select stuId,avg(skillScore+theoryScore) from StudentScore group by stuId

-- 統計每個班級有多少個學生
select ClassId,Count(stuId) as 個數 from StudentInfo group by ClassId

 
-- 統計每門課程有多少位同學在學習
select CourseName,count(stuId) from StudentScore group by CourseName


-- 統計每個學生學習了多少門課程
select stuId,count(CourseName) from StudentScore group by stuId


--11.查看每一門課程的平均分,總分,最高分,最低分
select CourseName,avg(skillScore),sum(skillScore),max(skillScore),min(skillScore)
from StudentScore group by CourseName

-- 11.1 查詢每門課程，每個學生的最低分
select CourseName,stuId,min(skillScore) from StudentScore group by CourseName,stuId

--12.統計每門課程的最低分,並且查詢出70分以上的
-- having:在分組的基礎之上進行數據過濾
select CourseName,min(skillScore) from StudentScore group by CourseName
having min(skillScore)>70


--13.統計每門課程,但不包含課程C# 入門編程的最低分,並且查詢出70分以上的

--  13.1統計每門課程最低分
select CourseName,min(skillScore) from StudentScore group by CourseName
--  13.2 在統計之前加上where 課程!=C# 入門編程
select CourseName,min(skillScore) from StudentScore where CourseName!='C# 入門編程' 
group by CourseName 
--  13.3 在統計之後加上having 最低分>=70
select CourseName,min(skillScore) from StudentScore where CourseName!='C# 入門編程' 
group by CourseName  having min(skillScore)>70

--14.查詢每門課程學習的人數大於等於2人的課程名稱與學習人數。
select CourseName,count(stuId) from StudentScore group by CourseName
having count(stuId)>=2

-- 15.查詢不只學了一門課程的學生編號與學習的課程數
-- 翻譯成人話：每個學生學習的課程數，並且學習的數量大於1
select stuId,count(CourseName) from StudentScore group by stuId
having count(CourseName)>1

5. 作業

線上教育平臺，網站首頁顯示所有課程分類，將分類後的信息顯示出來供用戶選擇，創建資料庫Education_DB，創建表（根據說明創建符合規範的表），如圖所示

課程表：

列名	數據類型	說明
CourseId	int	課程編號，自增1,主鍵
CourseName	char(20)	課程名稱，非空

用戶選課表

列名	數據類型	說明
Customer_Id	int	用戶編號、自增1,主鍵
Customer_Name	Varchar(20)	用戶名稱、非空
Customer_Sex	Varchar(2)	用戶性別、只能是男或女
Customer_Age	int	用戶年齡、年齡>=0
Customer_Phone	Varchar(20)	用戶手機號碼、唯一
Customer_Address	Varchar(20)	用戶籍貫、非空、預設北京
CourseId	int	課程編號、外鍵