-- 子查詢: 出現(xiàn)在Where子句中的Select語(yǔ)句被稱為子查詢(subquery)
-- 三種類型的子查詢: (NOT) IN, θ-Some / θ-All, (Not) Exists

-- 表達(dá)式 [not] in (子查詢)
-- 示例: 列出張三, 王三同學(xué)的所有信息 
Select Sname from Student 
  where Sname in ('SanZhang', 'SanWang');
-- 列出選修了001號(hào)課程的學(xué)生的學(xué)號(hào)和姓名
-- 不用子查詢
select SC.`S#`, Student.Sname from SC, Student 
  where `C#` in ('001') and SC.`S#`=Student.`S#`;
-- 使用子查詢( 先找出選了001課程的學(xué)號(hào), 然后再?gòu)腟tudent表中找學(xué)號(hào)對(duì)應(yīng)的姓名 )
select `S#`, `Sname` from Student 
  where `S#` in (select `S#` from SC where `C#`='001');
-- 求既學(xué)過(guò)001號(hào)課程又學(xué)過(guò)002號(hào)課程的學(xué)生學(xué)號(hào)
select `S#` from SC 
  where `C#`='001' and
    `S#` in (select `S#` from SC where `C#`='002');
-- 列出沒(méi)有學(xué)過(guò)李明老師講授課程的所有同學(xué)的姓名
-- 我寫的
select distinct Sname from Student S, SC, Course C 
  where S.`S#`=SC.`S#` and SC.`C#`=C.`C#` and C.`T#` not in 
    (select `T#` from Teacher where Tname='MingLi');
-- PPT給的(小問(wèn)題, 選出來(lái)的人有重復(fù)的, 應(yīng)該加上distinct)(起別名, 簡(jiǎn)潔)
select Sname from Student 
  where `S#` not in (select `S#` from SC, Course C, Teacher T 
    where T.Tname='MingLi' and SC.`C#`=C.`C#` and T.`T#`=C.`T#`);
-- 查詢嵌套, 分外層查詢和內(nèi)層查詢
-- 內(nèi)層查詢與外層查詢無(wú)關(guān), 為非相關(guān)子查詢, 有關(guān)就是相關(guān)子查詢(需要用到外層查詢的相關(guān)變量)

-- 相關(guān)子查詢
-- 示例: 求學(xué)過(guò)001號(hào)課程的同學(xué)的姓名
select Sname from Student Stud 
  where `S#` in (select `S#` from SC where `S#`=Stud.`S#` and `C#`='001');
-- 外層可以給內(nèi)層傳遞參數(shù), 反之不行, 也稱為變量的作用域原則

-- θ-Some / θ-All 子查詢
-- 基本語(yǔ)法:
-- 表達(dá)式 θ-Some (子查詢)
-- 表達(dá)式 θ-All (子查詢)
-- θ是比較運(yùn)算符: <, >, >=, <=, =, <>
-- 示例: 找出工資最低的教師的姓名
select Tname from Teacher
  where Salary <= all(select Salary from Teacher);

-- 找出001號(hào)課成績(jī)不是最高的所有學(xué)生的學(xué)號(hào)
-- 我寫的
select `S#` from SC 
  where `C#`='001' and Score < some(select Score from SC);
-- ppt的
select `S#` from SC
  where`C#`='001' and  Score < some(select Score from SC where `C#`='001');
-- 找出所有課程都不及格的學(xué)生的姓名(相關(guān)子查詢)
select Sname from Student 
  where 60 > all(select Score from SC where `S#`=Student.`S#`);
-- 人工查驗(yàn), 這條語(yǔ)句是不對(duì)的, 所有課程都不及格應(yīng)該只有一個(gè)人, 結(jié)果卻是有過(guò)掛科的
-- +----------+------+-------+
-- | S#       | C#   | Score |
-- +----------+------+-------+
-- | 98030101 | 001  |    92 |
-- | 98030101 | 002  |    85 |
-- | 98030101 | 003  |    88 |
-- | 98040202 | 002  |    90 |
-- | 98040202 | 003  |    80 |
-- | 98040202 | 001  |    55 |
-- | 98040203 | 003  |    56 |
-- | 98030102 | 001  |    54 |
-- | 98030102 | 002  |    85 |
-- | 98030102 | 003  |    48 |
-- +----------+------+-------+
-- +----------+----------+------+------+------+--------+-------+------+
-- | S#       | Sname    | Ssex | Sage | D#   | Sclass | Saddr | PID  |
-- +----------+----------+------+------+------+--------+-------+------+
-- | 98030203 | WuWang   | fe   |   19 | 04   | 980402 | NULL  | NULL |

-- 找出001號(hào)課成績(jī)最高的所有學(xué)生的學(xué)號(hào)
select `S#` from SC where `C#`='001' and Score >= all(select Score from SC where `C#`='001');
-- 找出98030101號(hào)同學(xué)成績(jī)最低的課程號(hào)
select `C#` from SC 
  where `S#`='98030101' and Score <= all(select Score from SC where `S#`='98030101');
-- 找出張三同學(xué)成績(jī)最低的課程號(hào)
-- 涉及相關(guān)子查詢
select `C#` from SC, Student S  
  where S.`S#`=SC.`S#` 
    and S.`Sname`='SanZhang' 
      and Score<=all(select Score from SC where S.`S#` = `S#`);

-- 等價(jià)性變換
-- 如下兩種表達(dá)相同
-- 表達(dá)式 = some (子查詢)
-- 表達(dá)式 in (子查詢)
-- 但是 not in 與 <>some 是不等價(jià)的
-- 與 not in等價(jià)的是 表達(dá)式 <> all

-- (NOT) EXISTS 子查詢
-- 基本語(yǔ)法
-- [not] exists (子查詢)
-- 語(yǔ)義: 子查詢結(jié)果中有無(wú)元組的存在(!!!選的是元組)
-- 示例: 檢索選修了趙三老師主講課程的所有同學(xué)的姓名(exists選出符合條件的元組, 而后再?gòu)闹羞x擇需要的字段)
select distinct Sname from Student 
  where exists( select * from SC, Course, Teacher where 
    SC.`C#`=Course.`C#` and SC.`S#`=Student.`S#` 
      and Course.`T#`=Teacher.`T#` and Tname='SanZhao');

-- 檢索學(xué)過(guò)001號(hào)教師主講的所有課程的所有同學(xué)的姓名
-- (似乎有問(wèn)題.....)(表示難以理解....)
select Sname from Student
  where not exists  -- 不存在
    (select * from Course where Course.`T#`='001' and not exists  -- 有一門001教師的課程, 沒(méi)學(xué)過(guò)
      (select * from SC where `S#`=Student.`S#` and `C#`=Course.`C#`));
-- 列出沒(méi)學(xué)過(guò)李明老師講授的任何一門課程的所有同學(xué)的姓名
select Sname from Student
  where not exists 
    (select * from Course, SC, Teacher 
      where Tname='MingLi' 
        and Course.`T#`=Teacher.`T#` 
          and Course.`C#`=SC.`C#` and `S#`=Student.`S#`);
-- 列出至少學(xué)過(guò)98030101號(hào)同學(xué)學(xué)過(guò)所有課程的同學(xué)的學(xué)號(hào)
Select distinct `S#` from SC SC1 
  where not exists                                                          -- 不存在
    (select * from SC SC2 where SC2.`S#`='98030101' and                     -- 有一門課該同學(xué)沒(méi)學(xué)過(guò)
      not exists (select * from SC where `C#`=SC2.`C#` and `S#`=SC1.`S#`));

-- 結(jié)果計(jì)算
-- 示例: 求有差額(差額>0)的任意兩位教師的薪水差額
select T1.Tname as TR1 , T2.Tname as TR2, T1.Salary-T2.Salary from Teacher T1, Teacher T2 
  where T1.Salary > T2.Salary;
-- 示例: 依據(jù)學(xué)生年齡求學(xué)生的出生年份, 當(dāng)前是2015年
select Sname, 2015-Sage+1 as Syear from Student;

-- 聚集函數(shù)
-- SQL提供了5個(gè)作用在簡(jiǎn)單列值集合上的內(nèi)置聚集函數(shù)agfunc, 分別是:
-- count sum avg max min
-- 示例: 求教師的工資總和
select sum(Salary) from Teacher;
-- 求計(jì)算機(jī)系教師的工資總和
select sum(Salary) from Teacher as T, Dept as D  
  where T.`D#`=D.`D#` and D.Dname='computer';
-- 求數(shù)據(jù)庫(kù)課的平均成績(jī)
select avg(Score) from SC, Course 
  where Cname='database' and Course.`C#`=SC.`C#`;

-- 分組聚集和分組過(guò)濾(分組是SQL的亮點(diǎn))
-- group by 分組條件
-- 示例: 求每個(gè)學(xué)生的平均成績(jī)(以每個(gè)學(xué)生為一個(gè)組)
select `S#`, avg(Score) from SC group by `S#`;
-- 求每一門課程的平均成績(jī)
select C.Cname, avg(SC.Score) avg from SC, Course C 
  where SC.`C#`=C.`C#` group by SC.`C#`;
-- 求不及格課程超過(guò)兩門的同學(xué)的學(xué)號(hào)
select `S#` from SC where Score<60 and count(*)>2 group by `S#`;  -- 錯(cuò)誤示范
-- 上面是錯(cuò)誤的, 因?yàn)镾core<60選出來(lái)的是元組, 而count(*) 是對(duì)于某一列的判斷, 所以是不正確的(后面會(huì)講解正確的操作)

-- 分組過(guò)濾
-- having子句, 無(wú)分組, 不過(guò)濾. 該子句的前提是有g(shù)roup的支持
-- 示例: 求不及格課程超過(guò)兩門的同學(xué)的學(xué)號(hào)
select `S#` from SC where Score<60 group by `S#` having count(*)>2;
-- having 表示對(duì)元組進(jìn)行統(tǒng)計(jì), 統(tǒng)計(jì)需要滿足要求是大于2
-- 示例: 求有1人以上不及格的課程號(hào)
select `C#` from SC where Score<60 group by `C#` having count(*)>1;

--having 和where子句表達(dá)條件的區(qū)別
-- 每一行都要檢查where子句, having是對(duì)分組進(jìn)行檢查, having前提要有g(shù)roup by子句

-- 示例: 求兩門以上不及格課程同學(xué)的學(xué)號(hào)及其平均成績(jī)
select `S#`, avg(Score) from SC where Score < 60 group by `S#` having count(*)>1;
-- 這條語(yǔ)句求的是不及格成績(jī)的平均成績(jī), 我們一般要這個(gè)學(xué)生的成績(jī)的平均 所以不正確
-- 正解(先選出不及格成績(jī)大于1的同學(xué)的學(xué)號(hào), 然后再對(duì)這些學(xué)號(hào)分組, 然后求平均, 這樣就不會(huì)有歧義了)
select `S#`, avg(Score) from SC  
    where `S#` in  (select `S#` from SC where Score<60 group by `S#` having count(*)>1) 
        group by `S#`;

-- SQL語(yǔ)言: 并運(yùn)算 union, 交運(yùn)算 intersect, 差運(yùn)算 except
-- 基本語(yǔ)法:
-- 子查詢 {union [ALL] | intersect [ALL] | except [ALL] 子查詢}
-- 通常情況下自動(dòng)刪除重復(fù)元組: 不帶ALL. 若要保留重復(fù)的元祖, 則要帶ALL
-- 示例:
-- 學(xué)過(guò)002號(hào)課的同學(xué)或?qū)W過(guò)003號(hào)課的同學(xué)學(xué)號(hào)
select `S#` from SC where `C#`='002' union 
select `S#` from SC where `C#`='003';
-- 也可以這樣
select distinct `S#` from SC where `C#`='002' or `C#`='003';
-- 當(dāng)表不一樣時(shí), or就不好使了, 就得用union

-- 求既學(xué)過(guò)002號(hào)課, 又學(xué)過(guò)003號(hào)課的同學(xué)學(xué)號(hào)(mysql 不支持 該 關(guān)鍵字)
select `S#` from SC where `C#`='002' intersect
select `S#` from SC where `C#`='003';
-- 也可以不用intersect來(lái)進(jìn)行(intersect 并不是唯一可選的, 所以增加了sql語(yǔ)言的不唯一性)
select `S#` from SC 
  where `C#`='002' and `S#` in (select `S#` from SC where `C#` = '003');

-- 假定所有學(xué)生都有選課, 求沒(méi)學(xué)過(guò)002號(hào)課程的學(xué)生的學(xué)號(hào)
-- 錯(cuò)誤示范(一個(gè)學(xué)生只要有兩條選課記錄, 就必然包含在該合集里面)
select `S#` from SC where `C#` <> '002';
-- 可以寫成這樣(mysql中也沒(méi)有except關(guān)鍵字)
select distinct `S#` from SC except select `S#` from SC where `C#` = '002';
-- 也可以這樣
select distinct `S#` from SC SC1 
  where not exists  (select * from SC where `C#`='002' and `S#` = SC1.`S#`);
-- 這說(shuō)明except也并沒(méi)有增強(qiáng)sql的表達(dá)能力了,
-- 沒(méi)有except, sql也可以用其他的方式表達(dá)同樣的查詢需求, 表達(dá)更簡(jiǎn)單, 但是增加了sql語(yǔ)言的不唯一性.

-- 空值處理
-- 示例: 找出年齡為空的學(xué)生的姓名
select Sname from Student where Sage is null;
-- mysql中空值的計(jì)算
-- count(*)包含null項(xiàng), count(具體列名) 忽略null項(xiàng); count(null)為0
-- avg, max, min, sum對(duì)null計(jì)算時(shí)全部忽略, 如果AVG(null)... 那么值為0
-- group by 對(duì)于null, 會(huì)單獨(dú)將其作為一項(xiàng)置于頂部, distinct類似

-- 內(nèi)連接, 外連接
-- 連接類型(四選一)
-- inner join
-- left outer join
-- right outer join
-- full outer join
-- 連接條件(三選一)
-- natural
-- on <連接條件>
-- using (col1, col2, ..., coln)
-- 錯(cuò)誤示例: 求所有教師的任課情況并按教師號(hào)排序(沒(méi)有任課的教師也需列在表中)
select Teacher.`T#`, Tname, Cname from Teacher 
  inner join Course on Teacher.`T#`=Course.`T#` order by Teacher.`T#`;
-- 上面丟失了一些老師的信息
-- 應(yīng)該使用左外連接(因?yàn)橐Ａ魶](méi)有任課的老師也在列表之中)
select Teacher.`T#`, Tname, Cname from Teacher
  left outer join Course on Teacher.`T#`=Course.`T#` order by Teacher.`T#`;

-- 視圖
-- 示例: 定義一個(gè)視圖CompStud為計(jì)算機(jī)系的學(xué)生, 通過(guò)該視圖可以將Student表中其他系的學(xué)生屏蔽掉
create view CompStud as
  (select * from Student where `D#` in 
    (select `D#` from Dept where Dname='computer'));
-- 示例: 定義一個(gè)視圖Teach為教師任課的情況, 把Teacher表中的個(gè)人隱私方面的信息, 如工資等屏蔽掉, 僅反映其教哪門課及學(xué)分等
create view Teach 
  as ( select T.Tname, C.Cname, Cred from Teacher T, Course C where T.`T#`=C.`T#`);
-- 使用視圖的時(shí)候就像使用table一樣的去使用

-- 對(duì)視圖的更新(注意哪些視圖是可更新的, 哪些是不可更新的)
-- 如果視圖Select目標(biāo)列包含聚集函數(shù), 
-- Select子句使用unique或distinct,
-- 包含group by子句,
-- 包括算術(shù)表達(dá)式計(jì)算出來(lái)的列,
-- 由單個(gè)表的列構(gòu)成, 但并沒(méi)有包括主鍵, 則都 不能更新

-- 對(duì)于由單一Table子集構(gòu)成的視圖, 即如果視圖是從單個(gè)基本表使用選擇, 投影操作導(dǎo)出的, 并且包含了基本表的主鍵, 則可以更新

-- 撤銷視圖
drop view view_name;
-- 示例: 撤銷視圖 Teach
drop view Teach;

數(shù)據(jù)庫(kù)系統(tǒng)學(xué)習(xí)筆記