俺覺(jué)得自 己試著寫(xiě)寫(xiě)sql,調(diào)試調(diào)試還是有幫助的，讀人家sql例子好像讀懂了，自己寫(xiě)就未 必思路正確，調(diào)試得通，寫(xiě)得簡(jiǎn)潔。

跟著網(wǎng)上流行的學(xué)生選課表的例子復(fù)習(xí)了一下： http://www.dbjr.com.cn/article/30655.htm

這篇文字在網(wǎng)上被轉(zhuǎn)載爛了，里面有些sql適合用在應(yīng)用系統(tǒng)里，有些“報(bào)表”的感 覺(jué)更重些，主要是想復(fù)習(xí)前者。前20條大體還挺好，后30條明顯偏報(bào)表風(fēng)格了，而 且后面選例良莠不齊，選了12個(gè)例子做練習(xí)，（其實(shí)很多語(yǔ)法，case, any/all, union之類(lèi)的都沒(méi)包括），用mysql數(shù)據(jù)庫(kù)，并共享自己造出來(lái)的數(shù)據(jù)。關(guān)于這12條 sql, 修正了原文中有紕漏的地方。

sql是基本技能，若能寫(xiě)得好也挺精彩的，還在繼續(xù)練習(xí)。絕不提倡努力寫(xiě)復(fù)雜sql 解決業(yè)務(wù)問(wèn)題。應(yīng)用系統(tǒng)里如果存在很復(fù)雜的sql，往往揭示了業(yè)務(wù)邏輯向下泄露 到sql層的問(wèn)題，不利于維護(hù)和擴(kuò)展，雖然這樣確實(shí)常能提高運(yùn)行效率。具體情況 自行取舍。
下面的例子都是比較通用的sql, 其實(shí)針對(duì)特定的數(shù)據(jù)庫(kù)，需要學(xué)的也挺多，比如 oracle db的decode函數(shù), rowid, rownum, connect by 雖然不通用，但是很實(shí)用。

數(shù)據(jù)可以在這里下載，只是用作練習(xí)，沒(méi)做任何外鍵關(guān)聯(lián)：http://xiazai.jb51.net/database/20120626051553698.txt

整理的sql在下面：
Student(S#,Sname,Sage,Ssex) 學(xué)生表
Course(C#,Cname,T#) 課程表
SC(S#,C#,score) 成績(jī)表
Teacher(T#,Tname) 教師表

1. 選出每門(mén)功課都及格的學(xué)號(hào)
select distinct `s#` from sc where `s#` not in (select `s#` from sc where score <60)
2. 查詢“1”課程比“2”課程成績(jī)高的所有學(xué)生的學(xué)號(hào)；
SELECT c01.`s#` from (select `s#`, `score` from sc where `c#`=1) c01,
(select `s#`, `score` from sc where `c#`=2) c02
where c01.`s#` = c02.`s#` and c01.score > c02.score
3. 查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)；
select `s#`, avg(score) from sc group by `s#` having avg(score) > 60
4. 查詢所有同學(xué)的學(xué)號(hào)、姓名、選課數(shù)、總成績(jī)；
select student.`s#`, student.`Sname`, count(`c#`), sum(score) from student left outer join sc on student.`s#` = sc.`s#` group by student.`s#`, sc.`s#`

5.查詢沒(méi)學(xué)過(guò)“葉平”老師課的同學(xué)的學(xué)號(hào)、姓名；
select student.`s#`, student.`Sname` from student where student.`s#` not in (select distinct(sc.`s#`) from teacher, course, sc where Tname='葉平' and teacher.`t#` = course.`t#` and sc.`c#`= course.`c#` )
6. 查詢學(xué)過(guò)“001”并且也學(xué)過(guò)編號(hào)“002”課程的同學(xué)的學(xué)號(hào)、姓名
select student.`s#`, student.sname from student, sc where student.`s#` = sc.`s#` and sc.`c#` = 1 and exists (select * from sc sc_2 where sc_2.`c#`=2 and sc.`s#`=sc_2.`s#`)
7. 查詢學(xué)過(guò)“葉平”老師所教的所有課的同學(xué)的學(xué)號(hào)、姓名 (巧妙)
select `s#`, sname from student where `s#` in
(select `s#` from sc, teacher, course where tname='葉平' and teacher.`t#`=course.`t#` and course.`c#`= sc.`c#` group by `s#` having count(sc.`c#`)=
(select count(`c#`) from teacher, course where tname='葉 平' and teacher.`t#`=course.`t#`) )

8. 查詢課程編號(hào)“002”的成績(jī)比課程編號(hào)“001”課程低的所有同學(xué)的學(xué)號(hào)、姓名 (有代表性)
select `s#`, sname from (select student.`s#`, student.sname, score, (select score from sc sc_2 where student.`s#`=sc_2.`s#` and sc_2.`c#`=2) score2 from student , sc where
sc.`s#`=student.`s#` and sc.`c#`=1) s_2 where score2 < score
9.查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名
select student.`S#`, Sname from student, sc where student.`s#` = sc.`s#` group by `s#`, sname having count(`c#`) < (select count(`c#`) from course)

10. 查詢至少有一門(mén)課與學(xué)號(hào)為“002”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號(hào)和姓名；
select distinct(sc.`s#`), sname from student, sc where student.`s#`=sc.`s#` and `c#` in (select `c#` from sc where `s#`=002)
11. 把“SC”表中“葉平”老師教的課的成績(jī)都更改為此課程的平均成績(jī)；

update sc inner join
(select sc2.`c#`, avg(sc2.score) score from sc sc2, teacher, course where
sc2.`c#`=course.`c#` and tname='葉平' and teacher.`t#` = course.`t#` and course.`c#`=sc2.`c#` group by course.`c#`) sc3 on sc.`c#`=sc3.`c#` set sc.score=sc3.score
12. 查詢2號(hào)的同學(xué)學(xué)習(xí)的課程他都學(xué)了的同學(xué)的學(xué)號(hào)；（注意理解：where語(yǔ)句的 第一個(gè)條件過(guò)濾掉不滿足c#的記錄，再group by，就比較清晰）
select `S#` from SC where `C#` in (select `C#` from SC where `S#`=2)
group by `S#` having count(*)=(select count(*) from SC where `S#`=2);

作者 人在江湖

最新評(píng)論