七、Mysql練習題目
https://zhuanlan.zhihu.com/p/38354000
1. 創建表
創建學生表
mysql> create table if not exists student(-> student_id varchar(255) not null,-> student_name varchar(255) not null,-> birthday date not null,-> gender varchar(255) not null,-> primary key(student_id)-> )default charset utf8;
創建成績表
mysql> create table score(-> student_id varchar(255) not null,-> course_id varchar(255) not null,-> score float(3) not null,-> primary key(student_id,course_id)-> )default charset utf8;
創建課程表
mysql> create table course(-> course_id varchar(255) not null,-> course_name varchar(255) not null,-> teacher_id varchar(255) not null,-> primary key(course_id)-> )default charset utf8;
創建教師表
mysql> create table teacher(-> teacher_id varchar(255) not null, -> teacher_name varchar(255) null, -> primary key(teacher_id) -> ) default charset utf8;
2. 插入數據
學生表插入數據
insert into student
values
('0001','猴子','1989-01-01','男'),
('0002','猴子','1990-12-21','女'),
('0003','馬云','1991-12-21','男'),
('0004','王思聰','1990-05-20','男');
成績表插入數據
insert into score
values
('0001','0001',80),
('0001','0002',90),
('0001','0003',99),
('0002','0002',60),
('0002','0003',80),
('0003','0001',80),
('0003','0002',80),
('0003','0003',80);
課程表插入數據
insert into course
values
('0001','語文','0002'),
('0002','數學','0001'),
('0003','英語','0003');
教師表插入數據
insert into teacher
values
('0001','孟扎扎'),
('0002','馬化騰'),
('0003',null),
('0004','');
3. 題目
1.查詢姓“猴”的學生名單
select * from student where student_name like "猴%";
2.查詢名字中最后一個字是猴的學生名單
select * from student where student_name like "%猴";
3.查詢名字中帶猴的學生名單
select * from student where student_name like "%猴%";
4.查詢名字中第二個字是猴的學生名單
select * from student where student_name like "_猴%";
5.查詢姓“孟”老師的個數
關鍵字：個數-count
select count(*) from teacher where teacher_name like "孟%";
6.查詢課程編號為“0002”的總成績
關鍵字：總成績-sum
select sum(score) from score where course_id='0002';
7.查詢選了課程的學生人數*
關鍵字：人數-count
select 學號，成績表里學號有重復值需要去掉
select count(distinct student_id) as 學生人數 from score;
8.查詢各科成績最高和最低的分
關鍵字：各科-分組；最高分-max ；最低分-min
select course_id,min(score) as 最低分,max(score) as 最高分 from score group by course_id;
9.查詢每門課程被選修的學生數
關鍵字：每門-分組；學生數-count
select course_id as 課程,count(student_id) as 學生個數 from score group by course_id;
10.查詢男生、女生人數
關鍵字：男生、女生（相當于每，因為性別只有男、女）-分組；人數-count
select gender,count(*) from student group by gender;
11.查詢平均成績大于60分學生的學號和平均成績
關鍵字：平均成績大于-having avg()>
select student_id,avg(score) from score group by student_id having avg(score)>60;
12.查詢至少選修兩門課程的學生學號
關鍵字：至少-count
select student_id from score group by student_id having count(course_id)>=2;
13.查詢同名同姓學生名單并統計同名人數*
關鍵字：人數-count
select student_name,count(student_id) from student group by student_name having count(student_id) >=2;
14.查詢不及格的課程并按課程號從大到小排列
select * from score where score<60 order by course_id desc;
15.查詢每門課程的平均成績，結果按平均成績升序排序，平均成績相同時，按課程號降序排列
關鍵字：每門-group by；平均成績-avg
select course_id,avg(score) as 平均成績 from score group by course_id order by 平均成績,course_id desc;
16.檢索課程編號為“0004”且分數小于60的學生學號，結果按按分數降序排列
select student_id student from score where course_id='0004' and score<60 order by score desc;
17.統計每門課程的學生選修人數(超過2人的課程才統計)
關鍵字：每門-group by；人數-count
select course_id,count(student_id) from score group by course_id having count(student_id)>2;
18.查詢兩門以上不及格課程的同學的學號及其平均成績*
19.查詢每個學生的總成績并進行排名
關鍵字：每個-group by；總成績-sum
select student_id,sum(score) as 總成績 from score group by student_id order by 總成績;
20.查詢平均成績大于60分的學生的學號和平均成績
關鍵字：平均成績大于-having avg()>60
select student_id,avg(score) as 平均成績 from score group by student_id having avg(score)>60;
21.查詢所有課程成績小于60分學生的學號、姓名
關鍵字：所有成績小于60-where score < 60
因為姓名在student表中，成績在score表中，所以要用到子查詢。
注意：如果(值1,值2,……)存在重復值時，in (值1,值2,……) 會從(值1,值2,……)中的重復值中選擇一個。即in會過濾掉重復數據
select student_id,student_name from student where student_id in (select student_id from score where score<60);
22.查詢沒有學全所有課的學生的學號、姓名*
select student_id,student_name from student where student_id in (select student_id from score group by student_id having count(course_id) < (select count(*) from course));
23.查詢出只選修了兩門課程的全部學生的學號和姓名
select student_id,student_name from student where student_id in (select student_id from score group by student_id having count(course_id)=2);
24.查詢各科成績前兩名的記錄*
關鍵字：各科-group by
https://mp.weixin.qq.com/s/MuxjlFV0gi1GydOrYfiSeQ
25.查找1990年出生的學生名單*
select * from student where year(birthday) = 1990;或select * from student where birthday like '1990-%';
26.查詢各學生的年齡（精確到年）*
select student_id, year(current_time)-year(birthday)+1 from student;
27.查詢各學生的年齡（精確到月份）*
select student_id,timestampdiff(month ,birthday ,now())/12 from student;
28.查詢本月過生日的學生*
select * from student where month(birthday)=month(current_date);
29.查詢所有學生的學號、姓名、選課數、總成績*
要顯示的列：學號，姓名，選課數，總成績，分布在兩個表中，所以應該用連表查詢，將兩個表連接起來
關鍵字：所有學生-左連接或右連接：左連接的話join左邊的表應該為student，右連接的話join右邊應該為student表。
注意：分組時，如果要用外連接的條件分組，則選擇的那個表中的列中的數據應該是唯一的，及s.student_id
select s.student_id as 學號,s.student_name as 姓名,count(c.course_id) as 選課數,sum(c.score) as 總成績 from student as s left join score as c on s.student_id=c.student_id group by s.student_id;
30.查詢平均成績大于85的所有學生的學號、姓名和平均成績*
select t1.student_id as '學號',t1.student_name as '姓名',avg(score) as '平均成績' from student as t1 left join score as t2 on t1.student_id = t2.student_id group by t1.student_id having avg(t2.score)>85;
31.查詢學生的選課情況：學號，姓名，課程號，課程名稱*
select t1.student_id,t1.student_name,t3.course_id,t3.course_name from student as t1 left join score as t2 on t1.student_id=t2.student_id left join course as t3 on t2.course_id=t3.course_id;
32.查詢出每門課程的及格人數和不及格人數*
關鍵字：及格和不及格-分類：case語句
select course_id, sum(case when score>=60 then 1 else 0 end) as 及格人數, sum(case when score<60 then 1 else 0 end) as 不及格人數 from score group by course_id;
33.使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱
select a.課程號,b.課程名稱,
sum(case when 成績 between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when 成績 >=70 and 成績<85 then 1 else 0 end) as '[85-70]',
sum(case when 成績>=60 and 成績<70 then 1 else 0 end) as '[70-60]',
sum(case when 成績<60 then 1 else 0 end) as '[<60]'
from score as a right join course as b
on a.課程號=b.課程號
group by a.課程號,b.課程名稱;
34.查詢課程編號為0003且課程成績在80分以上的學生的學號和姓名
select student_id,student_name from student where student_id in (select student_id from score where course_id='0003' and score>80);
35.檢索"0001"課程分數小于60，按分數降序排列的學生信息
select t1.*,t2.score from student as t1 left join score as t2 on t1.student_id=t2.student_id where t2.course_id = 0001 and t2.score>60 order by t2.score desc;
36.查詢不同老師所教不同課程平均分從高到低顯示**
注意：select只能是聚合函數或group by后面的字段，否則會報SELECT list is not in GROUP BY clause and contains nonaggregated column的錯誤。原因是sql_mode模式的限制。可以修改這個模式，修改方法：https://blog.csdn.net/weixin_42085125/article/details/115335503
因為我們要查詢t2.course_id，所以要在group by后面加上t2.course_id
select t1.teacher_name,t2.course_id,avg(t3.score) from teacher as t1 inner join course as t2 on t1.teacher_id=t2.teacher_id inner join score as t3 on t2.course_id=t3.course_id group by t1.teacher_id,t2.course_id order by avg(score) desc;
37.查詢課程名稱為"數學"，且分數低于60的學生姓名和分數
行列如何互換
sql面試題：行列如何互換？
)
