SQL練習(xí)50題
測試表格：
1) Student
+------+--------+---------------------+------+
| SId? | Sname?| Sage??????????????? | Ssex |
+------+--------+---------------------+------+
| 01?? |趙雷?? |1990-01-01 00:00:00 |男?? |
| 02?? |錢電?? |1990-12-21 00:00:00 |男?? |
| 03?? |孫風(fēng)?? |1990-05-20 00:00:00 |男?? |
| 04?? |李云?? |1990-08-06 00:00:00 |男?? |
| 05?? |周梅?? |1991-12-01 00:00:00 |女?? |
| 06?? |吳蘭?? |1992-03-01 00:00:00 |女?? |
| 07?? |鄭竹?? |1989-07-01 00:00:00 |女?? |
| 09?? |張三?? |2017-12-20 00:00:00 |女?? |
| 10?? |李四?? |2017-12-25 00:00:00 |女?? |
| 11?? |李四?? |2017-12-30 00:00:00 |女?? |
| 12?? |趙六?? |2017-01-01 00:00:00 |女?? |
| 13?? |孫七?? |2018-01-01 00:00:00 |女?? |
+------+--------+---------------------+------+
2）Teacher
+------+--------+
| TId? | Tname?|
+------+--------+
| 01?? |張三?? |
| 03?? |王五?? |
| 02?? |李四?? |
+------+--------+
3)Course
+------+--------+------+
| CId? | Cname?| TId? |
+------+--------+------+
| 01?? |語文?? |02?? |
| 02?? |數(shù)學(xué)?? |01?? |
| 03?? |英語?? |03?? |
+------+--------+------+
4)SC
+------+------+-------+
| SId? | CId?| score |
+------+------+-------+
| 01?? | 01??|? 80.0 |
| 01?? | 02??|? 90.0 |
| 01?? | 03??|? 99.0 |
| 02?? | 01??|? 70.0 |
| 02?? | 02??|? 60.0|
| 02?? | 03??|? 80.0 |
| 03?? | 01??|? 80.0 |
| 03?? | 02??|? 80.0 |
| 03?? | 03??|? 80.0 |
| 04?? | 01??|? 50.0 |
| 04?? | 02??|? 30.0 |
| 04?? | 03??|? 20.0 |
| 05?? | 01??|? 76.0 |
| 05?? | 02??|? 87.0 |
| 06?? | 01??|? 31.0 |
| 06?? | 03??|? 34.0 |
| 07?? | 02??|? 89.0 |
| 07?? | 03??|? 98.0 |
+------+------+-------+
SQL基本函數(shù)，聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算，并返回單個(gè)值，也被稱為組函數(shù)。?聚合函數(shù)經(jīng)常與SELECT 語句的GROUP BY 子句的HAVING一同使用。
Where 是一個(gè)約束聲明，使用Where約束來自數(shù)據(jù)庫的數(shù)據(jù)，Where是在結(jié)果返回之前起作用的，Where中不能使用聚合函數(shù)。?Having是一個(gè)過濾聲明，是在查詢返回結(jié)果集以后對(duì)查詢結(jié)果進(jìn)行的過濾操作，在Having中可以使用聚合函數(shù)。
1.? ?查詢"01 "課程比" 02"課程成績高的學(xué)生的信息及課程分?jǐn)?shù)
SELECT * FROM (SELECT SId,CId,score FROMSC WHERE CId='01') as t1, (SELECT SId,CId,score FROM SC WHERE CId='02') ast2? WHERE t1.SId=t2.SId andt1.score>t2.score;
1.1 查詢同時(shí)存在" 01 "課程和" 02 "課程的情況
select * from (select SId,score from scwhere sc.CId='01')as t1,(select SId,score from sc where sc.CId='02') as t2where t1.SId=t2.SId;
1.2? 查詢存在" 01 "課程但可能不存在" 02 "課程的情況(不存在時(shí)顯示為null )
select * from (select SId,score from scwhere sc.CId='01') as t1 LEFT JOIN (select SId,score from sc where sc.CId='02')as t2 on t1.SId=t2.SId;
1.3? 查詢不存在" 01 "課程但存在" 02 "課程的情況
select * from sc where sc.SId not in(select SId from sc where sc.CId='01') and sc.CId='02';
2. 查詢平均成績大于等于60分的同學(xué)的學(xué)生編號(hào)和學(xué)生姓名和平均成績
先算sc表中每個(gè)學(xué)生的平均值，構(gòu)成一個(gè)新表；再查詢student和這個(gè)新表。
1) selectstudent.*,t1.avgscore from student,
??? -> (select sc.SId,AVG(sc.score)
??? -> as avgscore from sc group by sc.Sidhaving avgscore>=60) as t1
-> wherestudent.SId=t1.SId;
2) selectstudent.*,t1.avgscore
from student inner JOIN(
select sc.SId,AVG(sc.score)as avgscore
from sc
GROUP BY sc.SId
HAVINGAVG(sc.score)>=60)as t1 on student.SId=t1.SId
3. 查詢在 SC 表存在成績的學(xué)生信息
select distinctstudent.* from student, SC where student.SId=SC.SId;
DISTINCT:用于返回唯一不同值，去重！
4.? 查詢所有同學(xué)的學(xué)生編號(hào)、學(xué)生姓名、選課總數(shù)、所有課程的總成績(沒成績的顯示為 null )(類似第2題)
select student.SId,student.Sname,t1.number,t1.sumscore
from student,(select sc.SId,COUNT(CID) as number, SUM(score) as sumscore from
SC group by sc.SId) as t1 where student.SId=t1.SId;
4.1 查有成績的學(xué)生信息
select * from studentwhere EXISTS (select * from sc where sc.SId=student.SId);
EXISTS:
EXISTS用于檢查子查詢是否至少會(huì)返回一行數(shù)據(jù)，該子查詢實(shí)際上并不返回任何數(shù)據(jù)，而是返回值True或False
EXISTS指定一個(gè)子查詢，檢測行的存在。
5.? 查詢「李」姓老師的數(shù)量
1）select count(*) from teacher where left(teacher.Tname,1)='李';
Left(b,1):取列字符的第一個(gè)字。
2）select * from teacher where teacher.Tname like ‘李%’;
Like模糊查詢
?6.? 查詢學(xué)過「張三」老師授課的同學(xué)的信息
select student.*
??? -> from teacher? ,course?,student,sc
??? -> where teacher.Tname='張三'
??? -> and??teacher.TId=course.TId
??? -> and??course.CId=sc.CId
??? -> and??sc.SId=student.SId;
7.? 查詢沒有學(xué)全所有課程的同學(xué)的信息
1) select student.* from (select count(CId) as num from course) ast1,student where student.SId not in (select sc.SId from sc group by sc.SIdhaving count(sc.CId)>=t1.num);
2) select student.* from student where student.SId not in (select
sc.SId from sc group by sc.SId having count(sc.CId)>=(select count(CId) from
course));
8.? 查詢至少有一門課與學(xué)號(hào)為" 01 "的同學(xué)所學(xué)相同的同學(xué)的信息
select DISTINCT student.* from student,sc where sc.SId=student.SId and sc.CId in (select CId from sc where SId='01');
9.? 查詢和" 01 "號(hào)的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)的信息
1) select
sc.CId from sc where sc.SId='01':查找‘01’號(hào)同學(xué)學(xué)過的課 ；
2）select student.SId,t.Cid from student ,(select sc.CId from sc where
sc.SId='01') as t：映射所有同學(xué)和‘01’學(xué)過的所有課；??????????????????
3）(selectstudent.SId,t.Cid from student ,(select sc.CId from sc where sc.SId='01') as t)as t1 left join sc ont1.SId=sc.SId and t1.CId=sc.CId where sc.CId is null )：沒有學(xué)過對(duì)應(yīng)CId的同學(xué)
4）not in：學(xué)過對(duì)應(yīng)CId的同學(xué)
select * from student wherestudent.SId not in (
select t1.Sid from (select student.SId,t.Cid fromstudent ,(select sc.CId from sc where sc.SId='01') as t )as t1 left join sc on t1.SId=sc.SIdand t1.CId=sc.CId where sc.CId is null ) and student.SId !='01'
10. 查詢沒學(xué)過"張三"老師講授的任一門課程的學(xué)生姓名
先查找張三老師教授的課；再用left join連接主表副標(biāo)表；再用not in查詢沒有學(xué)過的學(xué)生姓名
select *
??? -> from student
??? -> where student.SId not in (
??? -> select t1.SId
??? -> from
??? -> (select student.SId,t.CId
??? -> from student ,(select sc.CId from scwhere sc.SId='01') as t )as t1
??? -> left join sc on t1.SId=sc.SId andt1.CId=sc.CId
??? -> where sc.CId is null )
??? -> and student.SId !='01';
11. 查詢兩門及其以上不及格課程的同學(xué)的學(xué)號(hào)，姓名及其平均成績
select
student.SId,student.Sname,avg(sc.score) from student,sc WHERE
student.SId=sc.SId and sc.score<60 GROUPBY student.SId,student.Sname HAVING count(*)>=2;
12. 檢索" 01 "課程分?jǐn)?shù)小于 60，按分?jǐn)?shù)降序排列的學(xué)生信息
select student.* fromstudent,(select sc.score,sc.SId from sc where CId ='01') as t1 wheret1.SId=student.SId and t1.score<60 ORDER BY t1.score DESC;
13. 按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
selectsc.SId,sc.CId,sc.score,t1.avg from sc,(select sc.SId,avg(sc.score) as
avg from sc Group by sc.SId)
as t1 where sc.SId=t1.SIdORDER BY t1.avg DESC;
14. 查詢各科成績最高分、最低分和平均分：
以如下形式顯示：課程 ID，課程 name，最高分，最低分，平均分，及格率，中等率，優(yōu)良率，優(yōu)秀率
及格為>=60，中等為：70-80，優(yōu)良為：80-90，優(yōu)秀為：>=90
要求輸出課程號(hào)和選修人數(shù)，查詢結(jié)果按人數(shù)降序排列，若人數(shù)相同，按課程號(hào)升序排列
重點(diǎn)：sum(case when…then 1 else 0 end)語句，sum 中case when的else 需要為0，而count case when的else 只能為null，不能為0
select sc.CId,max(sc.score)as 最高分,min(sc.score)as 最低分,AVG(sc.score)as 平均分,count(*)as 選修人數(shù),sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,sum(case when sc.score>=70 and sc.score<80 then 1 else 0
end )/count(*)as 中等率,sum(case when
sc.score>=80 and sc.score<90 and sc.score<80 then 1 else 0 end
)/count(*)as 優(yōu)良率,sum(case when
sc.score>=90 then 1 else 0 end )/count(*)as 優(yōu)秀率
??? -> from sc
??? -> GROUP BY sc.CId
??? -> ORDER BY count(*)DESC,sc.CId asc;
15. 按各科成績進(jìn)行排序，并顯示排名，Score 重復(fù)時(shí)保留名次空缺
函數(shù)：Rank()
over(PARTITION BY cid order by score desc) as grade_rank
select cid, score, Rank()over(PARTITION BY cid order by score desc) as grade_rank from sc order by cid;
15.1 按各科成績進(jìn)行排序，并顯示排名， Score 重復(fù)時(shí)合并名次
函數(shù)：dense_rank() over(partition by CId order by score DESC) as grand_rank
select CId,score,dense_rank()over(partition by CId order by score DESC) as grand_rank from sc ORDER BY CId;
16. 查詢學(xué)生的總成績，并進(jìn)行排名，總分重復(fù)時(shí)保留名次空缺
注意：這里是對(duì)總成績排名，每個(gè)SId只對(duì)應(yīng)一個(gè)總成績，所以不需要PARTITION BY Sid去進(jìn)行分類
select student.sid,student.sname, sum(sc.score),rank() over (order by sum(sc.score) DESC) asgrade_rank from sc,student where sc.SId=student.SId GROUP BYstudent.SId,student.Sname ORDER BY sum(sc.score) DESC;
16.1 查詢學(xué)生的總成績，并進(jìn)行排名，總分重復(fù)時(shí)不保留名次空缺
select student.sid,student.sname, sum(sc.score),dense_rank() over (order b
y sum(sc.score) DESC) asgrade_rank from sc,student where sc.SId=student.SId GROUP
?BY student.SId,student.Sname ORDER BYsum(sc.score) DESC;
17. 統(tǒng)計(jì)各科成績各分?jǐn)?shù)段人數(shù)：課程編號(hào)，課程名稱，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
18. 查詢各科成績前三名的記錄
思路：前三名轉(zhuǎn)化為若大于此成績的數(shù)量少于3即為前三名。
select * from sc where(select count(*) from sc as a where sc.CId=a.CId and sc.score
19. 查詢每門課程被選修的學(xué)生數(shù)
select cid,count(*) from scGROUP BY cid;
20. 查詢出只選修兩門課程的學(xué)生學(xué)號(hào)和姓名
select student.sid,student.sname? from student, sc? where student.sid =
sc.sid group bystudent.SId,student.Sname having count(sc.cid) = 2;
21. 查詢男生、女生人數(shù)
select count(*) from studentGROUP BY Ssex;
+----------+
| count(*) |
+----------+
|??????? 4 |
|??????? 8 |
+----------+
22. 查詢名字中含有「風(fēng)」字的學(xué)生信息
select * from student where
student.Sname like '%風(fēng)%';
+------+--------+---------------------+------+
| SId? | Sname?| Sage??????????????? | Ssex |
+------+--------+---------------------+------+
| 03?? |孫風(fēng)?? | 1990-05-20 00:00:00 |男?? |
+------+--------+---------------------+------+
23. 查詢同名同性學(xué)生名單，并統(tǒng)計(jì)同名人數(shù)
select Sname,Ssex, count(*)as num from student GROUP BY Sname,Ssex having count(*)>1;
+--------+------+-----+
| Sname? | Ssex | num |
+--------+------+-----+
| 李四?? |女?? |?? 2 |
+--------+------+-----+
24. 查詢 1990 年出生的學(xué)生名單
關(guān)鍵字：YEAR
select * from student whereYEAR(Sage)=1990;
+------+--------+---------------------+------+
| SId? | Sname?| Sage??????????????? | Ssex |
+------+--------+---------------------+------+
| 01?? |趙雷?? | 1990-01-01 00:00:00 |男?? |
| 02?? |錢電?? | 1990-12-21 00:00:00 |男?? |
| 03?? |孫風(fēng)?? | 1990-05-20 00:00:00 |男?? |
| 04?? |李云?? | 1990-08-06 00:00:00 |男?? |
+------+--------+---------------------+------+
25. 查詢每門課程的平均成績，結(jié)果按平均成績降序排列，平均成績相同時(shí)，按課程編號(hào)升序排列
select avg(sc.score) from sc
GROUP BY sc.CId ORDER BY avg(sc.score) DESC,sc.CId
asc;
+---------------+
| avg(sc.score) |
+---------------+
|????? 72.66667 |
|????? 68.50000 |
|????? 64.50000 |
+---------------+
26. 查詢平均成績大于等于 85 的所有學(xué)生的學(xué)號(hào)、姓名和平均成績
1)selectstudent.SId,student.Sname,t1.avg from (select avg(sc.score) as avg,sc.SId fromsc GROUP BY sc.SId) as t1,student where student.SId=t1.SId and avg>=85;
+------+--------+----------+
| SId? | Sname?| avg????? |
+------+--------+----------+
| 01?? |趙雷?? | 89.66667 |
| 07?? |鄭竹?? | 93.50000 |
+------+--------+----------+
2)selectstudent.SId,student.Sname,avg(sc.score) from student,sc where student.SId=sc.SIdGROUP BY student.SId,student.Sname having avg(sc.score)>=85;
+------+--------+---------------+
| SId? | Sname?| avg(sc.score) |
+------+--------+---------------+
| 01?? |趙雷?? |?????89.66667 |
| 07?? |鄭竹?? |?????93.50000 |
+------+--------+---------------+
27. 查詢課程名稱為「數(shù)學(xué)」，且分?jǐn)?shù)低于 60 的學(xué)生姓名和分?jǐn)?shù)
selectstudent.Sname,sc.score,course.Cname from student, sc,course where
student.SId=sc.SId and
sc.CId=course.CId and course.Cname='數(shù)學(xué)' having sc.score
<60;
+--------+-------+--------+
| Sname? | score | Cname? |
+--------+-------+--------+
| 李云?? |? 30.0 |數(shù)學(xué)?? |
+--------+-------+--------+
28. 查詢所有學(xué)生的課程及分?jǐn)?shù)情況（存在學(xué)生沒成績，沒選課的情況）
select
student.Sname,student.SId,sc.score,sc.CId from student left joinsc on student.sid=sc.sid;
+--------+------+-------+------+
| Sname? | SId?| score | CId? |
+--------+------+-------+------+
| 趙雷?? | 01?? |?80.0 | 01?? |
| 趙雷?? | 01?? |?90.0 | 02?? |
| 趙雷?? | 01?? |?99.0 | 03?? |
| 錢電?? | 02?? |?70.0 | 01?? |
| 錢電?? | 02?? |?60.0 | 02?? |
| 錢電?? | 02?? |?80.0 | 03?? |
| 孫風(fēng)?? | 03?? |?80.0 | 01?? |
| 孫風(fēng)?? | 03?? |?80.0 | 02?? |
| 孫風(fēng)?? | 03?? |?80.0 | 03?? |
| 李云?? | 04?? |?50.0 | 01?? |
| 李云?? | 04?? |?30.0 | 02?? |
| 李云?? | 04?? |?20.0 | 03?? |
| 周梅?? | 05?? |?76.0 | 01?? |
| 周梅?? | 05?? |?87.0 | 02?? |
| 吳蘭?? | 06?? |?31.0 | 01?? |
| 吳蘭?? | 06?? |?34.0 | 03?? |
| 鄭竹?? | 07?? |?89.0 | 02?? |
| 鄭竹?? | 07?? |?98.0 | 03?? |
| 張三?? | 09?? |?NULL | NULL |
| 李四?? | 10?? |?NULL | NULL |
| 李四?? | 11?? |?NULL | NULL |
| 趙六?? | 12??|? NULL| NULL |
| 孫七?? | 13?? |?NULL | NULL |
+--------+------+-------+------+
29. 查詢?nèi)魏我婚T課程成績在 70 分以上的姓名、課程名稱和分?jǐn)?shù)
selectstudent.Sname,course.Cname,sc.score from student,sc,course wherestudent.sid=sc.sid and course.cid=sc.cid and sc.score>70;
+--------+--------+-------+
| Sname? | Cname?| score |
+--------+--------+-------+
| 趙雷?? |語文?? |? 80.0 |
| 趙雷?? |數(shù)學(xué)?? |? 90.0 |
| 趙雷?? |英語?? |? 99.0 |
| 錢電?? |英語?? |? 80.0 |
| 孫風(fēng)?? |語文?? |? 80.0 |
| 孫風(fēng)?? |數(shù)學(xué)?? |? 80.0 |
| 孫風(fēng)?? |英語?? |? 80.0 |
| 周梅?? |語文? ?|? 76.0|
| 周梅?? |數(shù)學(xué)?? |? 87.0 |
| 鄭竹?? |數(shù)學(xué)?? |? 89.0 |
| 鄭竹?? |英語?? |? 98.0 |
+--------+--------+-------+
30. 查詢不及格的課程
selectsc.SId,course.CId,course.Cname from sc,course where sc.CId=course.
CId and sc.score<60;
+------+------+--------+
| SId? | CId?| Cname? |
+------+------+--------+
| 04?? | 01??|語文?? |
| 04?? | 02??|數(shù)學(xué)?? |
| 04?? | 03??|英語?? |
| 06?? | 01??|語文?? |
| 06?? | 03??|英語?? |
+------+------+--------+
31. 查詢課程編號(hào)為 01 且課程成績在 80 分以上的學(xué)生的學(xué)號(hào)和姓名
selectstudent.Sname,student.SId from student,sc where student.SId=sc.SId and CId='01'and sc.score>80;
Empty set (0.02 sec)
32. 求每門課程的學(xué)生人數(shù)
select count(*) from sc GROUPBY CId;
+----------+
| count(*) |
+----------+
|??????? 6 |
|??????? 6 |
|??????? 6 |
+----------+
33. 成績不重復(fù)，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
1) select student.*,sc.score
from student,sc,course,teacher where student.SId=sc.SId and course.CId=sc.CId
and course.TId=teacher.TId and teacher.Tname='張三' ORDER BY sc.score DESCLIMIT 1;
+------+--------+---------------------+------+-------+
| SId? | Sname?| Sage??????????????? | Ssex |score |
+------+--------+---------------------+------+-------+
| 01?? |趙雷?? | 1990-01-01 00:00:00 |男?? |? 90.0 |
+------+--------+---------------------+------+-------+
select student.*,sc.score
from student,sc,course,teacher where student.SId=sc.SId and course.CId=sc.CId
and course.TId=teacher.TId and teacher.Tname='張三' ORDER BY sc.score DESCLIMIT 2;
+------+--------+---------------------+------+-------+
| SId? | Sname?| Sage??????????????? | Ssex |score |
+------+--------+---------------------+------+-------+
| 01?? |趙雷?? | 1990-01-01 00:00:00 |男?? |? 90.0 |
| 07?? |鄭竹?? | 1989-07-01 00:00:00 |女?? |? 89.0 |
+------+--------+---------------------+------+-------+
34. 成績有重復(fù)的情況下，查詢選修「張三」老師所授課程的學(xué)生中，成績最高的學(xué)生信息及其成績
selectstudent.*,sc.score,sc.CId from student,teacher,course,sc
??? -> where student.SId=sc.SId
??? -> and sc.CId=course.CId
??? -> and teacher.TId=course.TId
??? -> andsc.score=(select
max(sc.score) from sc,course,teacher where teacher.TId=course.TId and
sc.CId=course.CId and teacher.Tname='張三');
+------+--------+---------------------+------+-------+------+
| SId? | Sname?| Sage??????????? ????| Ssex | score | CId? |
+------+--------+---------------------+------+-------+------+
| 01?? |趙雷?? | 1990-01-01 00:00:00 |男?? |? 90.0 | 02??|
| 07?? |鄭竹?? | 1989-07-01 00:00:00 |女?? |? 90.0 | 02??|
+------+--------+---------------------+------+-------+------+
35. 查詢不同課程成績相同的學(xué)生的學(xué)生編號(hào)、課程編號(hào)、學(xué)生成績
1) select distinct t1.* from(select * from sc) as t1,(select * from sc) as
t2 where t1.score=t2.scoreand t1.CId!=t2.CId and t1.SId=t2.SId;
+------+------+-------+
| SId? | CId?| score |
+------+------+-------+
| 03?? | 02??|? 80.0 |
| 03?? | 03??|? 80.0 |
| 03?? | 01??|? 80.0 |
+------+------+-------+
[if !supportLists]2)?[endif]select DISTINCT t1.* from (select * from sc) as t1 INNER JOIN(select * f
rom sc) as t2 ont1.score=t2.score and t1.CId!=t2.CId and t1.SId=t2.SId;
+------+------+-------+
| SId? | CId?| score |
+------+------+-------+
| 03?? | 02??|? 80.0 |
| 03?? | 03??|? 80.0 |
| 03?? | 01??|? 80.0 |
+------+------+-------+
36. 查詢每門功成績最好的前兩名
select * from sc as t1 where (select count(*) from sc as t2 where t1.CId=t2.CId and
t1.score<t2.score)<2ORDER BY t1.CId;
+------+------+-------+
| SId? | CId?| score |
+------+------+-------+
| 01?? | 01??|? 80.0 |
| 03?? | 01??|? 80.0 |
| 01?? | 02??|? 90.0 |
| 07? ?|02?? |?90.0 |
| 01?? | 03??|? 99.0 |
| 07?? | 03??|? 98.0 |
+------+------+-------+
37. 統(tǒng)計(jì)每門課程的學(xué)生選修人數(shù)（超過 5 人的課程才統(tǒng)計(jì)）。
1) select sc.CId,count(*) from sc GROUP BY sc.CId HAVINGcount(*)>5;
+------+----------+
| CId? | count(*) |
+------+----------+
| 01?? |???????6 |
| 02?? |???????6 |
| 03?? |???????6 |
+------+----------+
2) select t1.CId,t1.num from (select sc.CId,count(*) as num from scGROUP BY
?sc.CId) as t1;
+------+-----+
| CId? | num |
+------+-----+
| 01?? |?? 6|
| 02?? |?? 6|
| 03?? |?? 6|
+------+-----+
38. 檢索至少選修兩門課程的學(xué)生學(xué)號(hào)
select count(*) from sc GROUPBY sc.SId HAVING count(*)>=2;
+----------+
| count(*) |
+----------+
|??????? 3 |
|??????? 3 |
|??????? 3 |
|??????? 3 |
|??????? 2 |
|??????? 2 |
|??????? 2 |
+----------+
39. 查詢選修了全部課程的學(xué)生信息
select student.* from studentwhere not exists (select * from course where not exists(select * from sc whereSId=student.SId and sc.CId=course.CId));
+------+--------+---------------------+------+
| SId? | Sname?| Sage??????????????? | Ssex |
+------+--------+---------------------+------+
| 01?? |趙雷?? | 1990-01-01 00:00:00 |男?? |
| 02?? |錢電?? | 1990-12-21 00:00:00 |男?? |
| 03?? |孫風(fēng)?? | 1990-05-20 00:00:00 |男?? |
| 04?? |李云?? | 1990-08-06 00:00:00 |男?? |
+------+--------+---------------------+------+
select * from sc
where SId=student.SId and sc.CId=course.Cid:所有已經(jīng)選擇過課程的學(xué)生及相應(yīng)課程；
select * from course where
not exists(select * from sc where SId=student.SId and sc.CId=course.CId): 所有沒有被選擇的課
select student.*
from student where not exists: 所有沒有未選擇課程的學(xué)生，即選擇了所有課程的學(xué)生信息。
40. 查詢各學(xué)生的年齡，只按年份來算
時(shí)間函數(shù)的使用
select
student.SId,student.Sname,(year(now())-year(Sage))as age fromstudent;
+------+--------+------+
| SId? | Sname?| age? |
+------+--------+------+
| 01?? |趙雷?? |?? 30|
| 02?? |錢電?? |?? 30|
| 03?? |孫風(fēng)?? |?? 30|
| 04?? |李云?? |?? 30|
| 05?? |周梅?? |?? 29|
| 06?? |吳蘭?? |?? 28|
| 07?? |鄭竹?? |?? 31|
| 09?? |張三?? |??? 3|
| 10?? |李四?? |??? 3|
| 11?? |李四?? |??? 3|
| 12?? |趙六?? |??? 3|
| 13?? |孫七?? |??? 2|
+------+--------+------+
41. 按照出生日期來算，當(dāng)前月日 < 出生年月的月日則，年齡減一
時(shí)間函數(shù)的使用
select
student.SId,student.Sname,TIMESTAMPDIFF(year,student.Sage,CURDATE())as age fromstudent;
+------+--------+------+
| SId? | Sname?| age? |
+------+--------+------+
| 01?? |趙雷?? |?? 30|
| 02?? |錢電?? |?? 29|
| 03?? |孫風(fēng)?? |?? 30|
| 04?? |李云?? |?? 29|
| 05?? |周梅?? |?? 28|
| 06?? |吳蘭?? |?? 28|
| 07?? |鄭竹?? |?? 30|
| 09?? |張三?? |??? 2|
| 10?? |李四?? |??? 2|
| 11?? |李四?? |??? 2|
| 12?? |趙六?? |??? 3|
| 13?? |孫七?? |??? 2|
+------+--------+------+
42. 查詢本周過生日的學(xué)生
select student.*
from student where WEEK(sage)=WEEK(now());
43. 查詢下周過生日的學(xué)生
select student.*
from student where WEEK(sage)=WEEK(now())+1;
44. 查詢本月過生日的學(xué)生
select student.* from student
where MONTH(sage)=MONTH(now());
45. 查詢下月過生日的學(xué)生
select student.* from student
where MONTH(sage)=MONTH(now())+1;
補(bǔ)充1：DELETE,DROP,TRUNCATE區(qū)別
（1）?? DELETE語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時(shí)將該行的刪除操作作為事務(wù)記錄在日志中保存以便進(jìn)行進(jìn)行回滾操作。TRUNCATE
TABLE 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨(dú)的刪除操作記錄記入日志保存，刪除行是不能恢復(fù)的。并且在刪除的過程中不會(huì)激活與表有關(guān)的刪除觸發(fā)器。執(zhí)行速度快。
（2）?? 表和索引所占空間。當(dāng)表被TRUNCATE 后，這個(gè)表和索引所占用的空間會(huì)恢復(fù)到初始大小，而DELETE操作不會(huì)減少表或索引所占用的空間。drop語句將表所占用的空間全釋放掉。
（3）?? 一般而言，drop > truncate > delete
（4）?? 應(yīng)用范圍。TRUNCATE 只能對(duì)TABLE；DELETE可以是table和view
（5）?? TRUNCATE 和DELETE只刪除數(shù)據(jù)，而DROP則刪除整個(gè)表（結(jié)構(gòu)和數(shù)據(jù)）。
（6）?? truncate與不帶where的delete ：只刪除數(shù)據(jù)，而不刪除表的結(jié)構(gòu)（定義）drop語句將刪除表的結(jié)構(gòu)被依賴的約束（constrain),觸發(fā)器（trigger)索引（index);依賴于該表的存儲(chǔ)過程/函數(shù)將被保留，但其狀態(tài)會(huì)變?yōu)椋篿nvalid。
（7）?? delete語句為DML（data maintain Language),這個(gè)操作會(huì)被放到 rollback segment中,事務(wù)提交后才生效。如果有相應(yīng)的tigger,執(zhí)行的時(shí)候?qū)⒈挥|發(fā)。
（8）?? truncate、drop是DLL（data define language),操作立即生效，原數(shù)據(jù)不放到 rollback segment中，不能回滾
（9）?? 在沒有備份情況下，謹(jǐn)慎使用 drop 與 truncate。要?jiǎng)h除部分?jǐn)?shù)據(jù)行采用delete且注意結(jié)合where來約束影響范圍?；貪L段要足夠大。要?jiǎng)h除表用drop;若想保留表而將表中數(shù)據(jù)刪除，如果于事務(wù)無關(guān)，用truncate即可實(shí)現(xiàn)。如果和事務(wù)有關(guān)，或老師想觸發(fā)trigger,還是用delete。
（10） Truncate table 表名 速度快,而且效率高,因?yàn)?
truncate table
在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但TRUNCATE TABLE 比 DELETE 速度快，且使用的系統(tǒng)和事務(wù)日志資源少。DELETE 語句每次刪除一行，并在事務(wù)日志中為所刪除的每行記錄一項(xiàng)。TRUNCATE
TABLE 通過釋放存儲(chǔ)表數(shù)據(jù)所用的數(shù)據(jù)頁來刪除數(shù)據(jù)，并且只在事務(wù)日志中記錄頁的釋放。
（11） TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識(shí)計(jì)數(shù)值，請(qǐng)改用DELETE。如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用DROP TABLE 語句。
（12） 對(duì)于由 FOREIGN
KEY 約束引用的表，不能使用TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發(fā)器。
補(bǔ)充2：count(1),count(*),count(列名)
執(zhí)行效果上：
count(*)包括了所有的列，相當(dāng)于行數(shù)，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
count(1)包括了忽略所有列，用1代表代碼行，在統(tǒng)計(jì)結(jié)果的時(shí)候，不會(huì)忽略列值為NULL
count(列名)只包括列名那一列，在統(tǒng)計(jì)結(jié)果的時(shí)候，會(huì)忽略列值為空（這里的空不是只空字符串或者0，而是表示null）的計(jì)數(shù)，即某個(gè)字段值為NULL時(shí)，不統(tǒng)計(jì)。
執(zhí)行效率上：
列名為主鍵，count(列名)會(huì)比count(1)快
列名不為主鍵，count(1)會(huì)比count(列名)快
如果表多個(gè)列并且沒有主鍵，則 count（1） 的執(zhí)行效率優(yōu)于count（*）
如果有主鍵，則select count（主鍵）的執(zhí)行效率是最優(yōu)的
如果表只有一個(gè)字段，則select count（*）最優(yōu)。
