【SQL server】基礎入門3——查詢

▶ 别名

select * from userInfo as u							-- 給表取别名

select UserName as Name, UserPwd as Pwd
from userInfo										-- 給列(屬性)取别名
           

※ 注：as 均可省略

▶ 查詢列的資料

-- 就是上面的指令
-- 分别是查詢全部列(*)，查詢指定列
           

▶ 查詢前幾行的資料（top）

select top 1 *
from userInfo										-- 查詢第一行的所有列的資料

select top 50 percent *
from userInfo										-- 查詢前50%行的所有列的資料
           

▶ 排序（order by）

select * from userInfo
order by UserAge asc/desc(升序/降序)					-- 按照UserPwd排序(預設升序)

select * from userInfo
order by UserAge asc, UserId desc					-- 先按照UserAge升序排序，在其相同的情況下根據UserId降序排序
           

▶ 消除重複行（distinct）

▶ 條件查詢（where）

• 比較運算符：=(單等于), >, <, >=, <=, !=(也可寫成<>)

• between…and… 表示在一個連續的範圍内（閉區間）

• in 表示在一個非連續的範圍内

• 邏輯運算符：and ,or, not

☀ 模糊查詢（like）：用于處理字元串類型的值

  

%

  0到多個任意字元

  

_

  1個任意字元

  

[]

  1個範圍内的字元

  

^

  寫在[]内部的開頭，表示"非"

※ 注：%, _寫在[ ]中轉義成本義； [2-9]表示連續範圍

select UserName from userInfo 
where UserAge = 12									-- 查詢年齡為12的user的姓名

select * from userInfo
where UserId between 1 and 3						-- 查詢編号1~3的user的所有資訊

select * from userInfo
where UserId in (1, 3)								-- 查詢編号1或3的user的所有資訊

--模糊
select * from userInfo
where UserName like '%Lo%'							-- 模糊查詢名字帶"Lo"的User的全部資訊

select * from userInfo
where UserAge like '1[^234]'						-- 模糊查詢年齡第一位為1，第二位數不是234的user的全部資訊（注意[]是1個字元，就很好了解了）
           

※ 注：where UserEmail = null (✘)

   where UserEmail is null（✔）

▶ 連接配接查詢（當需要的結果從多張表中取時）

關鍵 ☆：

那些表

？

關系

？

-- 連接配接查詢格式（inner表示内連接配接，關鍵字join加表名，on加條件）	
select * 
from StudentInfo
inner join ClassInfo on cid = cId

-- 如果兩個屬性名稱相同，需要加表名字首來區分
select * 
from StudentInfo
inner join ClassInfo on StudentInfo.cId = ClassInfo.cId
           

-- 一個典例：查詢學生姓名及所在班級名稱

select si.sName, ci.cName
from StudentInfo as si
inner join ClassInfo as ci on si.cId=ci.cId
           

※ 注(外連接配接都簡寫了一個outer)：

内連接配接    inner join

左外連接配接   left join    保留左表中特有的資料

右外連接配接   right join    保留右表中特有的資料

完全外連接配接  full join    左右邊特有的資料都保留

▶ 多表連接配接

-- 一個典例：列印學生姓名，科目名稱，成績
-- 找到哪些表( StudentInfo, SubjectInfo ) → ScoreInfo
-- 找到哪些關系 ScoreInfo.stuId=StudentInfo.stuId , ScoreInfo.subId=SubjectInfo.subId

select stu.sName,sub.sName,score.scoreValue 
from ScoreInfo as score
inner join StudentInfo as stu on score.stuId=stu.stuId
inner join SubjectInfo as sub on score.subId=sub.subId

-- 注意一個連接配接邏輯：每次多寫一行join，是與上面已經連接配接好的表連接配接在一起，而不是from緊跟的那個表
           

▶ 聚合函數（對行資料進行合并，簡單講就是統計）

COUNT(計數)

select COUNT(*) from UserInfo						-- 隻有COUNT能用(*)，其他聚合函數一般是對某一列的(數字)資料進行處理

select COUNT(*) as count_column from UserInfo 
where Id=1											-- 給該列取個别名；where加上條件

-- 注意：null 是不計入數的
           

MAX(最大值)，MIN(最小值)，AVG(平均數)，SUM(求和)

-- demo1
select MAX(scoreValue) from ScoreInfo
where subId = 2										-- 科目編号為2的最高分

-- demo2
select AVG(scoreValue) from ScoreInfo
inner join SubjectInfo on SubjectInfo.subId=scoreInfo.subId
where subName = '國文'								-- 查詢國文成績的平均分（★思路：找表--ScoreInfo,SubjectInfo；找關系：SubjectInfo.subId=scoreInfo.subId）
           

再說一遍，聚合中NULL不參與計算

▶ 開窗函數 over()

将統計資訊分布到行中

over() 用在聚合函數之後（将被聚合的資料還原到每一行中）

over()一般與聚合函數、排名函數結合使用

select *, AVG(UserAge) over()
from userInfo
           

效果如下↓（列印出表的所有資訊，并将平均年齡列印在每一行）：

▶ 分組（group by）

select cls, COUNT(*) as num
from userInfo
group by cls										-- 根據班級分組并列印各組人數

select cls, gender, COUNT(*) as num
from userInfo
group by cls, gender								-- 根據班級分組後，再把每個班分為男女兩組
													-- 先分男女再分班級也一樣，也就是gender和cls的位置無所謂
           

注 ：

  我們按照班級分組，除班級以外的具體資訊(比如姓名，年齡)都被屏蔽了；隻有分組依據（班級）還能再使用

  雖然每一組的具體資料被屏蔽了，無法展示，但這個組的資料資訊還能夠以聚合函數的形式展示出來：

select cls, COUNT(*) as num, AVG(UserAge) as avgage
from userInfo
group by cls
-- ↑ 求每個班的人數										
-- 每一組的每個具體成員的年齡雖然都被屏蔽了，但我們可以展示這一組的所有成員的平均年齡
-- 這是"行的合并"的思想，好好了解一下
           

★ 其本質是根據分組依據進行行的合并

▶ 分組之後根據分組的結果再篩選（having）

-- 統計各班級的各性别的學生編号大于15的學生人數
select cId, sGender, COUNT(*)
from StudentInfo
where sId > 15
group by cId, sGender 
-- 注意group by分組寫在where條件查詢之後，弄清這個邏輯：先通過where篩選出所有需要分組的學生，再對這些學生進行分組

-- 統計各班級的各性别的學生編号大于15的學生人數大于3的組的資訊（即分組之後根據分組資訊再進行一次篩選）
select cId, sGender, COUNT(*)
from StudentInfo
where sId > 15
group by cId, sGender having COUNT(*)>3
           

▶ 分組的擴充（cube 和 rollup）

group by 後面還可以再加with cube或with rollup，增加多行統計結果集

-- 查詢student表，統計各專業男生、女生人數及每個專業學生人數,男生總人數、女生總人數，以及所有學生總人數
select specialty, ssex, grade, count(*)
from student
group by specialty, ssex,grade with cube
--(★重難點)
-- cube用在group by之後，作用為：不僅根據分組依據進行分組統計，再根據分組依據的所有子集(包括空集)進行分組統計
-- demo: 分組依據為 a, b, c，則結果集多了很多行統計結果分組依據分别為(a, b, c)(a, b)(a, c)(a, c)(a)(b)(c)(空)


-- 統計每個專業的男女生人數，每個專業的總人數和所有學生的總人數
select specialty, ssex, count(*)
from student
group by specialty, ssex with rollup
--(★重難點)
-- rollup用在group by之後，作用為：和cube類似，但不是所有子集，而是從最低級别的分組依據開頭的子集和空集
-- demo: 分組依據為 a, b, c，則結果集多了很多行統計結果分組依據分别為(a, b, c)(a, b)(a)(空)
           

▶ select語句的層次關系

select distinct top n *						-- 需要查詢展示的列
from table1
inner join table2 on ...
inner join table3 on ...					-- 構造完整的查詢表
where ...									-- 先where篩選出資料等待分組或排序
group by ... having ...						-- where篩選後再分組；想要根據分組得到的資訊再篩選，使用having
order by ...								-- 最後對篩選、分組（再篩選）的資訊進行排序
           

> _ <