前期準備
由于我使用的是win10系統(tǒng)兴垦，所以提前把一些要注意的點給標注出來侵歇；
在windows下因為windows使用的默認編碼問題自娩，會導致你如果是在cmd下操作sql命令款熬，到了mysql-fron查看時中文會出行亂碼現(xiàn)象产阱。因為我們需要提前輸入以下命令：（在創(chuàng)建表的時候需要聲明utf8編碼）
set names 'gbk' ;
創(chuàng)建數(shù)據(jù)表
一共有 四 張表：學生表婉称，課程表，教師表构蹬，成績表
1. 學生表
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10),PRIMARY key(SId))charset=utf8;
insert into Student values('01' , '趙雷' , '1990-01-01' , '男');
insert into Student values('02' , '錢電' , '1990-12-21' , '男');
insert into Student values('03' , '孫風' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吳蘭' , '1992-01-01' , '女');
insert into Student values('07' , '鄭竹' , '1989-01-01' , '女');
insert into Student values('09' , '張三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '趙六' , '2013-06-13' , '女');
insert into Student values('13' , '孫七' , '2014-06-01' , '女');
2. 課程表
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10),PRIMARY KEY(CId))charset=utf8;
insert into Course values('01' , '語文' , '02');
insert into Course values('02' , '數(shù)學' , '01');
insert into Course values('03' , '英語' , '03');
3. 教師表
create table Teacher(TId varchar(10),Tname varchar(10),PRIMARY key (Tid))charset=utf8;
insert into Teacher values('01' , '張三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
4. 成績表
create table SC(skey int ,SId varchar(10),CId varchar(10),score decimal(18,1),PRIMARY key(skey))charset=utf8;
insert into SC values('1','01' , '01' , 80);
insert into SC values('2','01' , '02' , 90);
insert into SC values('3','01' , '03' , 99);
insert into SC values('4','02' , '01' , 70);
insert into SC values('5','02' , '02' , 60);
insert into SC values('6','02' , '03' , 80);
insert into SC values('7','03' , '01' , 80);
insert into SC values('8','03' , '02' , 80);
insert into SC values('9','03' , '03' , 80);
insert into SC values('10','04' , '01' , 50);
insert into SC values('11','04' , '02' , 30);
insert into SC values('12','04' , '03' , 20);
insert into SC values('13','05' , '01' , 76);
insert into SC values('14','05' , '02' , 87);
insert into SC values('15','06' , '01' , 31);
insert into SC values('16','06' , '03' , 34);
insert into SC values('17','07' , '02' , 89);
insert into SC values('18','07' , '03' , 98);
insert into SC values('19','08' , '01' , 88);
題目
1. 求每門課程的學生人數(shù)
select Course.cname'課程名稱',count(*)'人數(shù)' from SC,Course
where SC.CId=Course.CId
GROUP BY SC.CId
2. 查詢課程編號為 01 且課程成績在 80 分及以上的學生的學號和姓名
select student.sid,student.sname from student,sc
where student.sid = sc.sid
and sc.cid = '01'
and sc.score >= 80;
3. 統(tǒng)計每門課程的學生選修人數(shù)（超過 5 人的課程才統(tǒng)計）
select course.cname,count(*) from sc,course where course.cid=sc.cid
group by course.cid
having count(*) > 5;
4. 檢索至少選修兩門課程的學生學號
select sc.sid from sc
group by sid
having count(cid) > 2;
5. 選修了全部課程的學生信息
select student.* from student, sc
where sc.sid = student.sid
group by student.sid
having count(cid) = (select count(*) from course);
6. 查詢存在不及格的課程
select DISTINCT course.cname from course,sc
where sc.cid = course.cid
and sc.score < 60;
DISTINCT（僅列出不同的值）
7. 查詢?nèi)魏我婚T課程成績在 70 分以上的學生姓名王暗、課程名稱和分數(shù)
select student.sname, course.cname, sc.score from student, course, sc
where student.sid = sc.sid and course.cid = sc.cid and sc.score > 70;
8. 查詢所有學生的課程及分數(shù)情況（存在學生沒成績，沒選課的情況）
select student.sname, course.cname, sc.score from student
left join sc on sc.sid = student.sid
left join course on course.cid = sc.cid;
9. 查詢課程名稱為「數(shù)學」庄敛，且分數(shù)低于 60 的學生姓名和分數(shù)
select student.sname, sc.score from student, course, sc
where student.sid = sc.sid
and course.cid = sc.cid
and sc.score < 60
and course.cname = '數(shù)學';
10. 查詢平均成績大于等于 85 的所有學生的學號俗壹、姓名和平均成績
select student.sid, student.sname, avg(sc.score) from student, sc
where student.sid = sc.sid
group by student.sid
having avg(sc.score) >= 85;
11. 查詢每門課程的平均成績，結果按平均成績降序排列藻烤，平均成績相同時策肝，按課程編號升序排列
select cid, avg(score) from sc
group by cid
order by avg(score) desc, cid asc;
（desc為降序，asc為升序）
12. 查詢各科成績最高分隐绵、最低分和平均分
以如下形式顯示：課程 ID之众，課程 name，最高分依许，最低分棺禾，平均分，及格率峭跳，中等率膘婶，優(yōu)良率，優(yōu)秀率
及格為>=60蛀醉，中等為：70-80悬襟，優(yōu)良為：80-90，優(yōu)秀為：>=90
要求輸出課程號和選修人數(shù)拯刁，查詢結果按人數(shù)降序排列脊岳，若人數(shù)相同，按課程號升序排列
select sc.cid, course.cname, max(sc.score), min(sc.score), avg(sc.score), count(sc.sid),
sum(case when score < 60 then 1 else 0 end)/count(sc.cid) '合格率',
sum(case when score < 80 and score >= 60 then 1 else 0 end)/count(sc.cid) '中等率',
sum(case when score >= 90 then 1 else 0 end)/count(sc.cid) '優(yōu)秀率'
from sc, course
where sc.cid = course.cid
group by sc.cid;
13. 查詢男生垛玻、女生人數(shù)
select ssex, count(*) from student
group by ssex;
14. 檢索" 01 "課程分數(shù)小于 60割捅，按分數(shù)降序排列的學生信息
select student.* from student, sc
where student.sid = sc.sid
and sc.cid = '01'
and sc.score < 60
order by sc.score desc;
15. 按平均成績從高到低顯示所有學生的所有課程的成績以及平均成績
select sc.sid, sc.score, t.ascore from sc
right join (select sid,avg(score) ascore from sc group by sid) t on sc.sid = t.sid
order by t.ascore desc;
16. 查詢沒學過"張三"老師講授的任一門課程的學生姓名
select student.sname from student where student.sid not in
(select distinct student.sname from student, teacher, course, sc
where student.sid = sc.sid
and course.cid = sc.cid
and teacher.tid = course.tid
and teacher.tname = "張三");
not in 不在某個區(qū)間內(nèi)
17.成績不重復，查詢選修「張三」老師所授課程的學生中帚桩，成績最高的學生信息及其成績
select student.*, sc.score from student, sc, teacher, course
where student.sid = sc.sid
and sc.cid = course.cid
and course.tid = teacher.tid
and teacher.tname = "張三"
order by sc.score desc
limit 1;
18. 成績有重復的情況下亿驾，查詢選修「張三」老師所授課程的學生中，成績最高的學生信息及其成績
UPDATE sc SET score=90
where skey=17
select student.*, sc.score from student, sc, course, teacher
where student.sid = sc.sid
and sc.cid = course.cid
and course.tid = teacher.tid
and teacher.tname = "張三"
and sc.score = (
select max(sc.score) from student, sc, course, teacher
where student.sid = sc.sid
and sc.cid = course.cid
and course.tid = teacher.tid
and teacher.tname = "張三"
);
19.查詢不同課程成績相同的學生的學生編號账嚎、課程編號莫瞬、學生成績
select a.sid, a.cid, a.score from sc as a
inner join
sc as b
on a.sid = b.sid
and a.cid != b.cid
and a.score = b.score
GROUP BY a.cid, b.sid;
20. 查詢每門功成績最好的前兩名
方法一
select a.sid,a.cid,a.score from sc a
left join sc b on a.cid = b.cid
and a.score < b.score
group by a.cid,a.sid
having count(b.score)<2
order by a.cid;
方法二
select a.cid,a.sid,a.score from sc as a
where
(select count(1) from sc as b where
a.cid=b.cid and b.score >= a.score)<=2
order by a.cid,a.score desc;
21. 查詢每門課程被選修的學生數(shù)
select course.cname,count(*) from course,sc
where course.cid = sc.cid
group by sc.cid;
22. 查詢出只選修兩門課程的學生學號和姓名
select student.sid,student.sname from student,sc
where student.sid = sc.sid
group by sid
having count(*) = 2;
23. 查詢同名學生名單儡蔓，并統(tǒng)計同名人數(shù)
select student.sname, count(*) from student
group by student.sname
having count(*) > 1;
24. 查詢 1990 年出生的學生名單
select * from student
where year(sage)=1990;
25. 查詢各學生的年齡
select student.sid, student.sname, TIMESTAMPDIFF(year,sage,CURDATE()) '年齡' from student;
timestampdiff（參數(shù)1，參數(shù)2疼邀，參數(shù)3）喂江。參數(shù)1 可以選擇小時hour，秒second檩小，月month，年year烟勋。參與2规求，比較的時間數(shù)據(jù)（小的哪一個），參數(shù)3卵惦，比較的時間數(shù)據(jù)cutdate()用來返回今天的時間（大的那一個）阻肿。
26. 查詢本周過生日的學生
select * from student
where(curdate()) = week(sage);
27. 查詢本月過生日的學生
select * from student
where(curdate()) = month(sage);
28. 查詢「李」姓老師的數(shù)量
select count(*) from teacher
where tname like '李%';
29. 查有成績的學生信息
方法一：
select * from student
where sid in (select sc.sid from sc);
方法二：
select student.* from student,sc
where sc.sid = student.sid
group by sc.sid;
30. 查詢所有同學的學生編號、學生姓名沮尿、選課總數(shù)丛塌、所有課程的成績總和
select student.sid, student.sname, count(sc.cid),sum(sc.score)
from student
left join sc on student.sid = sc.sid
group by student.sid;
31. 查詢在 SC 表存在成績的學生信息
select * from student
where sid in (select sid from sc);
32. 查詢平均成績大于等于 60 分的同學的學生編號和學生姓名和平均成績
select student.sid,student.sname,avg(sc.score)
from student,sc
where student.sid = sc.sid
group by student.sid
having avg(sc.score) >= 60;
33. 查詢不存在" 01 "課程但存在" 02 "課程的情況
select * from sc where cid = "02"
and sid not in(
select sid from sc where cid = "01"
);
34. 查詢存在" 01 "課程但可能不存在" 02 "課程的情況
select * from sc where cid = "01";
35. 按各科成績進行排序，并顯示排名畜疾， Score 重復時保留名次空缺
select a.cid, a.sid, a.score, count(b.sid)+1
from sc a left join sc b on a.cid = b.cid
and a.score < b.score
group by a.cid, a.sid
order by a.cid, count(b.sid)+1;
36. 查詢" 01 "課程比" 02 "課程成績高的學生的信息及課程分數(shù)
select student.*, a.score, b.score
from student,
(select score,sid from sc where cid = "01") a,
(select score,sid from sc where cid = "02") b
where a.sid = b.sid
and b.sid = student.sid
and a.score > b.score;
37. 查詢學過「張三」老師授課的同學的信息
select student.* from student, teacher, course, sc
where student.sid = sc.sid
and sc.cid = course.cid
and course.tid = teacher.tid
and teacher.tname = "張三";
（考察的是多表聯(lián)查的運用）
38. 查詢沒有學全所有課程的同學的信息
select student.* from student
left join sc on student.sid = sc.sid
group by student.sid
having count(sc.cid) < (select count(*) from course);
??的方法是?的赴邻，查詢出的同學也只是成績表中有登記過成績的學生中沒學全課程的學生
? 的寫法應該是這個??
select student.* from student
left join sc on sc.sid = student.sid
group by student.sid
having count(sc.cid) < (select count(*) from course);
39. 查詢至少有一門課與學號為" 01 "的同學所學相同的同學的信息
select student.* from student, sc
where student.sid = sc.sid
and sc.cid in (select cid from sc where sid = "01")
group by student.sid;
40. 查詢和" 01 "號的同學學習的課程完全相同的其他同學的信息
select * from Student where sid in
(select sid from sc where sid not in
(select sid from sc where cid not in
(select cid from sc where sid ='01'))
GROUP BY sid
having count(*)=(select count(cid) from sc where sid ='01')and sid<>'01')
