MySql练习03

#创建数据库db_practices03
CREATE DATABASE IF NOT EXISTS `db_practices03`;

#使用数据库db_practices03
USE `db_practices03`;

#创建学生表tb_student
CREATE TABLE IF NOT EXISTS `tb_student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

#创建课程表tb_course
CREATE TABLE IF NOT EXISTS `tb_course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

#创建教师表tb_teacher
CREATE TABLE IF NOT EXISTS `tb_teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

#创建成绩表tb_score
CREATE TABLE IF NOT EXISTS `tb_score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
#--------------------------------------------------------------------------------

#向tb_student表中插入数据
INSERT INTO `tb_student` VALUES('01' , '赵雷','1990-01-01','男'); 
INSERT INTO `tb_student` VALUES('02' , '钱电','1990-12-21','男'); 
INSERT INTO `tb_student` VALUES('03' , '孙风','1990-05-20','男'); 
INSERT INTO `tb_student` VALUES('04' , '李云','1990-08-06','男'); 
INSERT INTO `tb_student` VALUES('05' , '周梅','1991-12-01','女'); 
INSERT INTO `tb_student` VALUES('06' , '吴兰','1992-03-01','女'); 
INSERT INTO `tb_student` VALUES('07' , '郑竹','1989-07-01','女'); 
INSERT INTO `tb_student` VALUES('08' , '王菊','1990-01-20','女');

#向tb_course表中插入数据
INSERT INTO `tb_course` VALUES('01' , '语文' , '02'); 
INSERT INTO `tb_course` VALUES('02' , '数学' , '01'); 
INSERT INTO `tb_course` VALUES('03' , '英语' , '03');

#向tb_teacher表中插入数据
INSERT INTO `tb_teacher` VALUES('01' , '张三'); 
INSERT INTO `tb_teacher` VALUES('02' , '李四'); 
INSERT INTO `tb_teacher` VALUES('03' , '王五');

#向tb_score表中插入数据
INSERT INTO `tb_score` VALUES('01' , '01' , 80);
INSERT INTO `tb_score` VALUES('01' , '02' , 90);
INSERT INTO `tb_score` VALUES('01' , '03' , 99);
INSERT INTO `tb_score` VALUES('02' , '01' , 70);
INSERT INTO `tb_score` VALUES('02' , '02' , 60);
INSERT INTO `tb_score` VALUES('02' , '03' , 80);
INSERT INTO `tb_score` VALUES('03' , '01' , 80);
INSERT INTO `tb_score` VALUES('03' , '02' , 80);
INSERT INTO `tb_score` VALUES('03' , '03' , 80);
INSERT INTO `tb_score` VALUES('04' , '01' , 50);
INSERT INTO `tb_score` VALUES('04' , '02' , 30);
INSERT INTO `tb_score` VALUES('04' , '03' , 20);
INSERT INTO `tb_score` VALUES('05' , '01' , 76);
INSERT INTO `tb_score` VALUES('05' , '02' , 87);
INSERT INTO `tb_score` VALUES('06' , '01' , 31);
INSERT INTO `tb_score` VALUES('06' , '03' , 34);
INSERT INTO `tb_score` VALUES('07' , '02' , 89);
INSERT INTO `tb_score` VALUES('07' , '03' , 98);
#--------------------------------------------------------------------------------

#01、查询“01”课程比“02”课程成绩高的学生的信息及课程分数
SELECT s.*, s1.s_score '01课程成绩',s2.s_score '02课程成绩'
FROM tb_student s, tb_score s1, tb_score s2
WHERE s.s_id = s1.s_id
AND s.s_id = s2.s_id 
AND s1.c_id ='01' 
AND s2.c_id = '02' 
AND s1.s_score > s2.s_score;

#02、查询“01”课程比“02”课程成绩低的学生的信息及课程分数(题目 1 是成绩高)
SELECT s.*, s1.s_score '01课程成绩',s2.s_score '02课程成绩'
FROM tb_student s, tb_score s1, tb_score s2
WHERE s.s_id = s1.s_id
AND s.s_id = s2.s_id 
AND s1.c_id ='01' 
AND s2.c_id = '02' 
AND s1.s_score < s2.s_score;

#03、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT a.s_id '学生编号', a.s_name '学生姓名', AVG(b.s_score) '平均成绩'
FROM tb_student a,tb_score b
WHERE a.s_id=b.s_id
GROUP BY b.s_id
HAVING AVG(b.s_score)>=60;

#04、查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SELECT a.`s_id` '学生编号',a.`s_name` '学生姓名',IF(avg_score IS NULL, 0, ROUND(avg_score, 2)) AS '平均成绩'
FROM tb_student a
LEFT JOIN (SELECT s_id,AVG(s_score) AS avg_score FROM tb_score GROUP BY s_id) b ON a.`s_id` = b.`s_id`
WHERE avg_score IS NULL OR avg_score < 60;

#05、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT a.`s_id` '学生编号',a.`s_name` '学生姓名',COUNT(b.`c_id`) '选课总数',SUM(b.`s_score`) '总成绩'
FROM tb_student a,tb_score b
WHERE a.`s_id`=b.`s_id`
GROUP BY b.`s_id`
#06、查询“李”姓老师的数量
SELECT COUNT(t_id) '“李”姓老师的数量'
FROM tb_teacher
WHERE t_name LIKE '李%';

#07、查询学过张三老师授课的同学的信息
SELECT a.*
FROM tb_student a
LEFT JOIN tb_score b ON a.`s_id`=b.`s_id`
LEFT JOIN tb_course c ON b.`c_id`=c.`c_id`
LEFT JOIN tb_teacher d ON c.`t_id`=d.`t_id`
WHERE d.`t_name`='张三';

#08、找出没有学过张三老师课程的学生
SELECT * 
FROM tb_student 
WHERE s_id NOT IN(
    SELECT a.`s_id`
    FROM tb_student a
    LEFT JOIN tb_score b ON a.`s_id`=b.`s_id`
    LEFT JOIN tb_course c ON b.`c_id`=c.`c_id`
    LEFT JOIN tb_teacher d ON c.`t_id`=d.`t_id`
    WHERE d.`t_name`='张三'
    );

#09、查询学过编号为 01，并且学过编号为 02 课程的学生信息
SELECT a.*
FROM tb_student a,tb_score b1,tb_score b2
WHERE a.`s_id`=b1.`s_id`=b2.`s_id`
AND b1.`c_id`='01'
AND b2.`c_id`='02';

#10、查询学过 01 课程，但是没有学过 02 课程的学生信息(注意和上面9题目的区别)
SELECT *
FROM tb_student a
WHERE a.s_id IN (SELECT s_id FROM tb_score WHERE c_id = '01')
AND a.s_id NOT IN (SELECT s_id FROM tb_score WHERE c_id = '02');

#11、查询没有学完全部课程的同学的信息
SELECT a.*
FROM tb_student a
LEFT JOIN tb_score b ON b.`s_id`=a.`s_id`
GROUP BY a.`s_id`
HAVING COUNT(DISTINCT b.`c_id`)<(SELECT COUNT(*) FROM tb_course);

#12、查询至少有一门课与学号为 01 的同学所学相同的同学的信息
SELECT b.*
FROM tb_score a 
JOIN tb_student b ON a.`s_id`=b.`s_id` AND a.`s_id`<>'01' AND a.c_id IN(SELECT c_id FROM tb_score WHERE s_id='01')
GROUP BY b.s_id;

#13、查询和 01 同学学习的课程完全相同的同学的信息
SELECT s.*
FROM tb_score sc 
JOIN tb_student s ON sc.s_id=s.s_id
WHERE sc.s_id IN(SELECT s_id FROM tb_score WHERE s_id <>'01' GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(SELECT COUNT(DISTINCT c_id) FROM tb_score WHERE s_id='01'))
AND c_id IN(SELECT c_id FROM tb_score WHERE s_id='01') GROUP BY s.s_id HAVING COUNT(DISTINCT sc.c_id)=(SELECT COUNT(DISTINCT c_id) FROM tb_score WHERE s_id='01');

#14、查询没有修过张三老师讲授的任何一门课程的学生姓名
SELECT s_name
FROM tb_student
WHERE s_id NOT IN(
SELECT s_id FROM tb_score
WHERE c_id IN(SELECT c_id FROM tb_course WHERE t_id=(SELECT t_id FROM tb_teacher WHERE t_name='张三')));

#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT a.`s_id`,a.`s_name`,AVG(b.`s_score`) AS '平均成绩'
FROM tb_student a
JOIN tb_score b ON a.`s_id`=b.`s_id` 
WHERE a.s_id IN (SELECT s_id FROM tb_score WHERE s_score<60 GROUP BY s_id HAVING COUNT(c_id)>=2)
GROUP BY b.`s_id`;

#16、检索 01 课程分数小于 60，按分数降序排列的学生信息
SELECT a.*
FROM tb_score a
JOIN tb_student b ON a.`s_id`=b.`s_id`
WHERE c_id='01'
AND s_score<60
ORDER BY s_score DESC

#17、按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
SELECT s.s_name,c.c_name,sc.s_score,sc2.avg_score
FROM tb_student s 
JOIN tb_course c
LEFT JOIN tb_score sc ON s.s_id=sc.s_id AND c.c_id=sc.c_id
LEFT JOIN (SELECT s_id,ROUND(AVG(s_score),2) AS avg_score FROM tb_score GROUP BY s_id) sc2 ON s.s_id=sc2.s_id
ORDER BY sc2.avg_score DESC;

#18、查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID，课程 name，最高分，最低分，平均分，及格 率，中等率，优良率，优秀率;及格:>=60，中等为:70-80，优良为:80-90，优秀为:>=90
SELECT a.`c_id` '课程 ID',a.`c_name` '课程name',
MAX(b.`s_score`) '最高分',
MIN(b.`s_score`) '最低分',
ROUND(AVG(b.`s_score`),2) '平均分',
CONCAT(ROUND(SUM(IF(b.`s_score`>=60,1,0))/COUNT(b.`s_id`)*100,2),'%') '及格率',
CONCAT(ROUND(SUM(IF(b.`s_score`>=70 AND b.`s_score`<80,1,0))/COUNT(b.`s_id`)*100,2),'%') AS '中等率',
CONCAT(ROUND(SUM(IF(b.`s_score`>=80 AND b.`s_score`<90,1,0))/COUNT(b.`s_id`)*100,2),'%') AS '优良率',
CONCAT(ROUND(SUM(IF(b.`s_score`>=90,1,0))/COUNT(b.`s_id`)*100,2),'%') '优秀率'
FROM tb_course a
JOIN tb_score b ON a.`c_id`=b.`c_id`
GROUP BY a.`c_id`

#19、按照各科成绩进行排序，并且显示排名
SELECT c.c_name,s.s_name,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS '排名'
FROM tb_score sc
JOIN tb_student s ON sc.s_id=s.s_id
JOIN tb_course c ON sc.c_id=c.c_id;

#20、查询学生的总成绩，并进行排名
SELECT s.s_name,SUM(sc.s_score) AS '总成绩',
ROW_NUMBER()OVER(ORDER BY SUM(sc.s_score) DESC) AS '排名'
FROM tb_student s 
LEFT JOIN tb_score sc ON s.s_id=sc.s_id
GROUP BY s.s_name;

#21、查询不同老师所教不同课程平均分从高到低显示
SELECT t.t_name '教师姓名',c.c_name '课程名称',ROUND(AVG(sc.s_score),2) '平均分'
FROM tb_teacher t
JOIN tb_course c ON t.t_id=c.t_id
JOIN tb_score sc ON c.c_id=sc.c_id
GROUP BY t.t_name,c.c_name
ORDER BY AVG(sc.s_score) DESC;

#22、查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩
SELECT r.c_name,r.rank_num,s.s_name,r.s_score
FROM (SELECT c.c_name,sc.s_id,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num FROM tb_course c JOIN tb_score sc ON c.c_id=sc.c_id) r
JOIN tb_student s ON r.s_id=s.s_id and r.rank_num in(2,3);

#23、统计各科成绩各分数段人数:课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60] 及所占百分比
SELECT sc.c_id '课程编号',c.c_name '课程名称',
SUM(if(sc.s_score>=85,1,0)) AS '[100-85]人数',
SUM(if(sc.s_score>=70 AND sc.s_score<85,1,0)) AS '[85-70]人数',
SUM(if(sc.s_score>=60 AND sc.s_score<70,1,0)) AS '[70-60]人数',
SUM(if(sc.s_score<60,1,0)) AS '[0-60]人数',
CONCAT(ROUND(SUM(if(sc.s_score>=85,1,0))/COUNT(*)*100,2),'%') AS '[100-85]百分比',
CONCAT(ROUND(SUM(if(sc.s_score>=70 AND sc.s_score<85,1,0))/COUNT(*)*100,2),'%') AS '[85-70]百分比',
CONCAT(ROUND(SUM(if(sc.s_score>=60 AND sc.s_score<70,1,0))/COUNT(*)*100,2),'%') AS '[70-60]百分比',
CONCAT(ROUND(SUM(if(sc.s_score<60,1,0))/COUNT(*)*100,2),'%') AS '[0-60]百分比'
FROM tb_score sc 
JOIN tb_course c ON sc.c_id=c.c_id
GROUP BY sc.c_id;

#24、查询学生的平均成绩及名次
SELECT s.s_name '学生姓名',AVG(sc.s_score) AS '平均成绩',
ROW_NUMBER()OVER(ORDER BY AVG(sc.s_score) DESC) AS '排名'
FROM tb_student s 
LEFT JOIN tb_score sc ON s.s_id=sc.s_id
GROUP BY s.s_name;

#25、查询各科成绩前三名的记录
SELECT r.c_name, r.s_name, r.s_score, r.rank_num 
FROM (SELECT c.c_name, s.s_name, sc.s_score, ROW_NUMBER() OVER (PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num FROM tb_course c JOIN tb_score sc ON c.c_id = sc.c_id JOIN tb_student s ON sc.s_id = s.s_id) r 
WHERE r.rank_num <= 3;

#26、查询每门课被选修的学生数
SELECT c.c_name,COUNT(DISTINCT sc.s_id) AS s_num
FROM tb_score sc 
JOIN tb_course c ON sc.c_id=c.c_id
GROUP BY c.c_name;

#27、查询出只有两门课程的全部学生的学号和姓名
SELECT s.s_id '学号',s.s_name '姓名'
FROM tb_score sc
JOIN tb_student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT sc.c_id)=2;

#28、查询男女生人数
SELECT s_sex '性别',COUNT(s_id) AS '人数' FROM tb_student GROUP BY s_sex;

#29、查询名字中含有 风 字的学生信息
SELECT *  FROM tb_student WHERE s_name LIKE '%风%';

#30、查询同名同性的学生名单，并统计同名人数
SELECT s1.s_name,s1.s_sex,s2.num '同名人数'
FROM tb_student s1 
JOIN (SELECT s_name,COUNT(*) AS num FROM tb_student GROUP BY s_name HAVING COUNT(*)>=2) s2 ON s1.s_name=s2.s_name
GROUP BY s1.s_name,s1.s_sex
HAVING COUNT(*)>=2;

#31、查询 1990 年出生的学生信息
SELECT * FROM tb_student WHERE YEAR(DATE(s_birth))=1990;

#32、查询每门课程的平均成绩，结果按平均成绩降序排列;平均成绩相同时，按课程编号 c_id 升序排列
SELECT c.c_name '课程名称',ROUND(AVG(sc.s_score),2) '平均分'
FROM tb_score sc 
JOIN tb_course c ON sc.c_id=c.c_id
GROUP BY c.c_id
ORDER BY AVG(sc.s_score) DESC,c.c_id ASC;

#33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT s.s_id '学号',s.s_name '姓名',ROUND(AVG(sc.s_score),2)'平均成绩'
FROM tb_score sc 
JOIN tb_student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score) >= 85;

#34、查询课程名称为数学，且分数低于 60 的学生姓名和分数
SELECT s.s_name,sc.s_score
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id=(SELECT c_id FROM Course WHERE c_name='数学')
AND s_score < 60;

#35、查询所有学生的课程及分数情况
SELECT s.s_name '姓名',c.c_name '课程名称',sc.s_score '分数'
FROM tb_student s
JOIN tb_score sc ON s.s_id = sc.s_id
JOIN tb_course c ON sc.c_id = c.c_id
ORDER BY s.s_id,c.c_id;

#36、查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT s.s_name '姓名',c.c_name '课程名称',sc.s_score '分数'
FROM tb_score sc
JOIN tb_student s ON sc.s_id=s.s_id
JOIN tb_course c ON sc.c_id=c.c_id
WHERE s_score>70;

#37、查询不及格的课程
SELECT s.s_name '姓名', c.c_name '不及格课程',sc.s_score '分数'
FROM tb_score sc 
JOIN tb_student s ON sc.s_id=s.s_id
JOIN tb_course c ON sc.c_id=c.c_id
WHERE sc.s_score<60;

#38、查询课程编号为 01 且课程成绩大于等于 80 的学生的学号和姓名
SELECT s.s_id '学号',s.s_name '姓名'
FROM tb_score sc ,tb_student s
WHERE sc.s_id=s.s_id AND c_id='01' AND s_score>=80;

#39、每门课程的学生人数
SELECT c.c_name,count(sc.s_id) AS '人数'
FROM tb_score sc
JOIN tb_course c ON sc.c_id=c.c_id
GROUP BY c.c_name;

#40、查询选修“张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT s.*,s_score
FROM tb_score sc
JOIN tb_student s ON sc.s_id=s.s_id
WHERE c_id=(SELECT c_id FROM tb_course WHERE t_id=(SELECT t_id FROM tb_teacher WHERE t_name='张三'))
ORDER BY s_score DESC LIMIT 1;

#41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT *
FROM tb_score
WHERE s_score in(SELECT s_score FROM tb_score GROUP BY s_score HAVING COUNT(DISTINCT c_id)>=2)

#42、查询每门功成绩最好的前两名
SELECT r.c_name,r.rank_num,s.s_name,r.s_score
FROM (SELECT c.c_name,sc.s_id,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num FROM tb_course c JOIN tb_score sc ON c.c_id=sc.c_id) r
JOIN tb_student s ON r.s_id=s.s_id and r.rank_num in(1,2);

#43、统计每门课程的学生选修人数(超过 5 人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列， 若人数相同，按课程号升序排列
SELECT c.c_id, COUNT(sc.s_id) '人数'
FROM tb_score sc 
JOIN tb_course c ON sc.c_id = c.c_id 
GROUP BY c.c_id 
HAVING COUNT(DISTINCT s_id) > 5 
ORDER BY '人数' DESC, c.c_id ASC;

#44、检索至少选修两门课程的学生学号
SELECT s_id
FROM tb_score
GROUP BY s_id
HAVING COUNT(DISTINCT c_id)>=2;

#45、查询选修了全部课程的学生信息
SELECT s.*
FROM tb_score sc 
JOIN tb_student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING COUNT(c_id)=(SELECT COUNT(*) FROM tb_course);

#46、查询各学生的年龄:按照出生日期来算，当前月日 < 出生年月的月日则，年龄减 1
SELECT s.s_id,s.s_name, 
YEAR(CURDATE()) - YEAR(s.s_birth) - (MONTH(CURDATE()) < MONTH(s.s_birth) OR (MONTH(CURDATE()) = MONTH(s.s_birth) AND DAY(CURDATE()) < DAY(s.s_birth))) AS age
FROM tb_student s;

#47、查询本周过生日的学生
SELECT * FROM tb_student WHERE WEEK(DATE(s_birth))=WEEK(NOW());

#48、查询下周过生日的学生
SELECT * FROM tb_student WHERE WEEK(DATE(s_birth))=WEEK(NOW())+1;

#49、查询本月过生的同学
SELECT * FROM tb_student WHERE MONTH(DATE(s_birth))=MONTH(NOW());

#50、查询下月过生的同学
SELECT * FROM tb_student WHERE MONTH(DATE(s_birth))=MONTH(NOW())+1;