1.创建表
代码:
# 学生表 Student:
CREATE DATABASE IF NOT EXISTS sss;
CREATE TABLE Student(
SId VARCHAR(10) ,
Sname VARCHAR(10),
Sage DATETIME,
Ssex VARCHAR(10));
# 教师表 Teacher
CREATE TABLE Teacher(
TId VARCHAR(10),
Tname VARCHAR(10));
# 科目表 Course
CREATE TABLE Course(
CId VARCHAR(10),
Cname NVARCHAR(10),
TId VARCHAR(10));
# 成绩表 SC
CREATE TABLE SC(
SId VARCHAR(10),
CId VARCHAR(10),
score DECIMAL(18,1));
SHOW TABLES;
# 学生表 Student:
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('09' , '张三' , '2017-12-20' , '女');
INSERT INTO Student VALUES('10' , '李四' , '2017-12-25' , '女');
INSERT INTO Student VALUES('11' , '李四' , '2017-12-30' , '女');
INSERT INTO Student VALUES('12' , '赵六' , '2017-01-01' , '女');
INSERT INTO Student VALUES('13' , '孙七' , '2018-01-01' , '女');
# 科目表 Course
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
# 教师表 Teacher
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
# 成绩表 SC
INSERT INTO SC VALUES('01' , '01' , 80);
INSERT INTO SC VALUES('01' , '02' , 90);
INSERT INTO SC VALUES('01' , '03' , 99);
INSERT INTO SC VALUES('02' , '01' , 70);
INSERT INTO SC VALUES('02' , '02' , 60);
INSERT INTO SC VALUES('02' , '03' , 80);
INSERT INTO SC VALUES('03' , '01' , 80);
INSERT INTO SC VALUES('03' , '02' , 80);
INSERT INTO SC VALUES('03' , '03' , 80);
INSERT INTO SC VALUES('04' , '01' , 50);
INSERT INTO SC VALUES('04' , '02' , 30);
INSERT INTO SC VALUES('04' , '03' , 20);
INSERT INTO SC VALUES('05' , '01' , 76);
INSERT INTO SC VALUES('05' , '02' , 87);
INSERT INTO SC VALUES('06' , '01' , 31);
INSERT INTO SC VALUES('06' , '03' , 34);
INSERT INTO SC VALUES('07' , '02' , 89);
INSERT INTO SC VALUES('07' , '03' , 98);
创建结果:
# 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT *
FROM student a
JOIN sc b
ON a.sid=b.sid
JOIN sc c
ON a.sid=c.sid AND b.cid=01 AND c.cid=02
WHERE b.score > c.score;
# 两个分数在同一表格内且列名一样,无法提取,需要分开表写
# 或者说 查询同一表中的同一列的信息时就需要自联结。
# 1.1 .查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT *
FROM (SELECT * FROM sc WHERE cid='01') s1
LEFT JOIN (SELECT * FROM sc WHERE cid='02') s2
ON s1.sid=s2.sid;
SELECT *
FROM sc s1
LEFT JOIN sc s2
ON s1.sid=s2.sid AND s2.cid='02'
WHERE s1.cid='01';
# 要保留2表的null,可以把2的筛选条件放在on中。
# 1.2 .查询同时存在01和02课程的情况
SELECT *
FROM (SELECT * FROM sc WHERE cid='01') s1
JOIN (SELECT * FROM sc WHERE cid='02') s2
ON s1.sid=s2.sid;
SELECT *
FROM sc s1
JOIN sc s2
ON s1.sid=s2.sid
WHERE s1.cid='01' AND s2.cid='02';
# 1.3 .查询选择了02课程但没有01课程的情况
SELECT *
FROM (SELECT * FROM sc WHERE sid NOT IN (SELECT sid FROM sc WHERE cid='01')) s1
INNER JOIN sc s2
ON s1.sid=s2.sid AND s2.cid='02';
# 表连接,必须是个表,所以要再加一层子连接
SELECT *
FROM sc
WHERE sid NOT IN (SELECT sid FROM sc WHERE cid='01')
AND cid='02';
#2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.sid,s.`Sname`,AVG(c.score) avg_score
FROM student s
JOIN sc c
ON s.sid=c.sid
GROUP BY s.sid
HAVING avg_score>60;
SELECT s.sid,s.`Sname`,b.avg_score
FROM student s
JOIN
(SELECT sid,AVG(score) avg_score
FROM sc
GROUP BY sid
HAVING avg_score>60) b
ON s.sid=b.sid;
#3.查询在 SC 表存在成绩的学生信息
SELECT s.*
FROM student s
RIGHT JOIN (SELECT sid FROM sc GROUP BY sid) a
ON s.`SId`=a.sid;
--4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和
# 查询所有学生
-- 提示:所有同学存在于学生表,课程数、成绩情况在成绩表,
-- 就是要学生表左关联成绩汇总表得到记录,关联不上的说明就是没成绩。
SELECT s.sid,s.sname,c.`选课总数`,c.`总成绩`
FROM student s
LEFT JOIN
(SELECT sid,COUNT(1) '选课总数',SUM(score) '总成绩'
FROM sc
GROUP BY sid) c
ON s.sid=c.sid;
SELECT s.sid,s.sname,COUNT(s.sid) AS cons,SUM(c.score) AS sum_score
FROM student s
LEFT JOIN sc c
ON s.sid=c.sid
GROUP BY s.sid;
-- 5.查询「李」姓老师的数量
SELECT COUNT(1)
FROM teacher
WHERE tname LIKE '李%';
6.查询学过「张三」老师授课的同学的信息
/*
教师表: teacher tid+tname
课程表:course tid+cid
戍绩表:sc cid + sid
学生表: student sid
*/
# 查询张三老师教的课
SELECT c.cid,t.tname
FROM teacher t
JOIN course c
ON t.tid=c.tid
WHERE t.tname='张三';
# 查询有这门课的同学
SELECT sid
FROM sc
WHERE cid IN (SELECT c.cid
FROM teacher t
JOIN course c
ON t.tid=c.tid
WHERE t.tname='张三');
# 查询信息
SELECT *
FROM student
WHERE sid IN (SELECT sid
FROM sc
WHERE cid IN (SELECT c.cid
FROM teacher t
JOIN course c
ON t.tid=c.tid
WHERE t.tname='张三'));
7.查询没有学全所有课程的同学的信息
# 所有课程在course中,查询全部课程的数量
SELECT COUNT(1) FROM course;
# 查询学生的课程数量,查询数量小于count的学生的信息
SELECT s.*,COUNT(s.sid) cons
FROM student s
LEFT JOIN sc c
ON s.sid = c.sid
GROUP BY s.sid
HAVING cons<(SELECT COUNT(1) FROM course);
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
# '01'号同学的课程
SELECT cid FROM sc
WHERE sid='01';
# 查询其他学生的课程
SELECT DISTINCT s.*
FROM student s
INNER JOIN sc c
ON s.`SId`=c.`SId`
WHERE c.cid IN (
SELECT cid FROM sc
WHERE sid='01'
);
# 第二种
SELECT * FROM student s
INNER JOIN (
SELECT c.sid
FROM sc c
WHERE c.cid IN (SELECT cid FROM sc
WHERE sid='01')
GROUP BY c.sid
) d
ON s.`SId`=d.sid;
- 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
# 没有学习其他课程且和‘01’ 学习的课程数量一致
# ① 学习其他课程的同学
SELECT sid
FROM sc
WHERE cid NOT IN (SELECT cid FROM sc
WHERE sid='01');
# ② 没有学习其他课程的同学
SELECT *
FROM student
WHERE sid NOT IN
(SELECT sid
FROM sc
WHERE cid NOT IN (SELECT cid FROM sc
WHERE sid='01'));
#③ ‘01’号学习的课程数量
SELECT COUNT(1) FROM sc
WHERE sid='01'
# ④
SELECT s.*
FROM student s
INNER JOIN sc c
ON s.sid=c.sid
WHERE s.sid NOT IN
(SELECT sid
FROM sc
WHERE cid NOT IN (SELECT cid FROM sc
WHERE sid='01') )AND s.sid!='01'
GROUP BY s.sid
HAVING COUNT(s.sid)=(SELECT COUNT(1) FROM sc
WHERE sid='01');
- 查询没学过"张三"老师讲授的任一门课程的学生姓名
# 张三老师教的课
SELECT c.cid
FROM course c
INNER JOIN teacher t
ON c.tid = t.`TId`
WHERE t.`Tname`='张三';
# 学过张三老师的课的同学,即学的课在老师教的课中
SELECT sid FROM sc
WHERE `CId` IN (SELECT c.cid
FROM course c
INNER JOIN teacher t
ON c.tid = t.`TId`
WHERE t.`Tname`='张三');
# 没学过的同学
SELECT * FROM student
WHERE sid NOT IN (SELECT sid FROM sc
WHERE `CId` IN (SELECT c.cid
FROM course c
INNER JOIN teacher t
ON c.tid = t.`TId`
WHERE t.`Tname`='张三'));
# 第二种
SELECT
d.sid
FROM sc a
LEFT JOIN course b
ON a.cid = b.`CId`
LEFT JOIN teacher c
ON b.tid=c.`TId`
LEFT JOIN student d
ON a.sid=d.`SId`
WHERE c.`Tname`='张三';
SELECT * FROM student
WHERE sid NOT IN (SELECT
d.sid
FROM sc a
LEFT JOIN course b
ON a.cid = b.`CId`
LEFT JOIN teacher c
ON b.tid=c.`TId`
LEFT JOIN student d
ON a.sid=d.`SId`
WHERE c.`Tname`='张三');
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
# 成绩平均值
SELECT sid,AVG(score)
FROM sc
GROUP BY sid;
# 课程不及格的数量超过2
SELECT sid
FROM sc
WHERE score<60
GROUP BY sid
HAVING COUNT(*)>=2;
# 学生信息
SELECT s.sid,s.sname,a.avg_score
FROM student s
INNER JOIN (SELECT sid,AVG(score) avg_score
FROM sc
GROUP BY sid) a
ON s.`SId`=a.sid
WHERE s.sid IN (SELECT sid
FROM sc
WHERE score<60
GROUP BY sid
HAVING COUNT(*)>=2)
- 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
# 当限制条件量表中都有时,可以直接在on中添加
SELECT s.*
FROM student s
JOIN sc c
ON s.`SId`=c.sid AND c.cid='01'
WHERE c.score<60
ORDER BY c.score DESC;
# 第二种
SELECT a.*,b.*
FROM (SELECT sid,score
FROM sc
WHERE cid='01' AND score<60) a
LEFT JOIN student b
ON a.sid=b.`SId`
ORDER BY a.score DESC;
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
# 平均成绩
SELECT sid,AVG(score) avg_score
FROM sc
GROUP BY sid;
# 关联排序
SELECT s.*,c.avg_score
FROM sc s
INNER JOIN (SELECT sid,AVG(score) avg_score
FROM sc
GROUP BY sid) c
ON s.sid = c.sid
ORDER BY avg_score DESC;
SELECT sc.sid,
SUM(CASE WHEN cid=01 THEN score ELSE NULL END) '01课程',
SUM(CASE WHEN cid=02 THEN score ELSE NULL END) '02课程',
SUM(CASE WHEN cid=03 THEN score ELSE NULL END) '03课程',
ss.avg_score
FROM sc
INNER JOIN (SELECT sid,AVG(score) avg_score
FROM sc
GROUP BY sid) ss
ON sc.`SId`=ss.sid
GROUP BY sid
# 我的sql没有这个关键字,出不来,呜呜
SELECT * FROM sc
pivot
(
SUM(score)
FOR cid IN ([01],[02],[03])
) AS d
14.查询各科成绩最高分、最低分和平均分,及格率等等
SELECT c.cid '课程ID',c.cname '课程name',MAX(score) '最高分',
MIN(score) '最低分',AVG(score) '平均分',COUNT(*) '选修人数',
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END)/COUNT(1) AS '及格率',
SUM(CASE WHEN score>=70 AND score<80 THEN 1 ELSE 0 END)/COUNT(1) AS '中等率',
SUM(CASE WHEN score>=80 AND score<90 THEN 1 ELSE 0 END)/COUNT(1) AS '优良率',
SUM(CASE WHEN score>=90 THEN 1 ELSE 0 END)/COUNT(1) AS '优秀率'
FROM sc s
INNER JOIN course c
ON s.cid=c.cid
GROUP BY s.cid
ORDER BY '选修人数' DESC,s.cid ASC;
15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
SELECT b.cid,a.sname,b.score,rank() over(PARTITION BY b.cid ORDER BY b.score DESC) cnt
FROM student a
RIGHT JOIN sc b
ON a.`SId`=b.`SId`
16.查询学生的总成绩,并进行排名,总分重复时保留名次空缺
SELECT sid,a.sum_score,rank() over(ORDER BY a.sum_score DESC) cnt
FROM( SELECT sid,SUM(score) sum_score
FROM sc
GROUP BY sid
) a
-- 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
SELECT sid,a.sum_score,dense_rank() over(ORDER BY a.sum_score DESC) cnt
FROM( SELECT sid,SUM(score) sum_score
FROM sc
GROUP BY sid
) a
17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT s.cid,c.cname,
SUM(CASE WHEN s.score >85 THEN 1 ELSE 0 END)/COUNT(1) AS '(85-100]',
SUM(CASE WHEN s.score >70 AND s.score<=85 THEN 1 ELSE 0 END)/COUNT(1) AS '(70-85]',
SUM(CASE WHEN s.score >60 AND s.score<=70 THEN 1 ELSE 0 END)/COUNT(1) AS '(60-70]',
SUM(CASE WHEN s.score <=60 THEN 1 ELSE 0 END)/COUNT(1) AS '[0-60]'
FROM sc s
INNER JOIN course c
ON s.cid = c.cid
GROUP BY s.cid
18. 查询各科成绩前三名的记录
SELECT ss.cid,ss.sid,ss.score,ss.cnt
FROM(
SELECT *,rank() over(PARTITION BY cid ORDER BY score DESC) cnt
FROM sc) ss
WHERE cnt<4;
# 前三名转化为大于此成绩的数量少于3
# 其实就是笛卡尔积的写法,只不过灵活好用,但是不好查看排名
SELECT a.*
FROM sc a
WHERE (SELECT COUNT(1) FROM sc b WHERE a.cid = b.cid AND b.score>a.score)<3
ORDER BY cid
19. 查询每门课程被学习的学生数
# 按课程id分组统计数据量
SELECT cid,COUNT(1) cnt
FROM sc
GROUP BY cid
20. 查询出只选修两门课程的学生学号和姓名
SELECT a.sid,a.sname,COUNT(1) cnt
FROM student a
INNER JOIN sc b
ON a.sid=b.sid
GROUP BY sid
HAVING cnt=2
21. 查询男生、女生人数
SELECT ssex,COUNT(1) cnt
FROM student
GROUP BY ssex
22. 查询名字中含有「风」字的学生信息
SELECT *
FROM student
WHERE sname LIKE '%风%'
23.查询同名同性学生名单,并统计同名人数
# 查询同名同姓学生名单,并统计同名人数
SELECT sname,COUNT(1) cnt
FROM student
GROUP BY sname
HAVING cnt>1
# 查询同名同性学生名单,并统计同名人数
SELECT a.sname,COUNT(1) cnt
FROM student a
INNER JOIN student b
ON a.sname = b.sname AND a.ssex = b.ssex AND a.sid != b.sid
GROUP BY a.sname
24.查询1990年出生的学生名单
SELECT sname,sage
FROM student
WHERE YEAR(sage)=1990
25.查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
SELECT cid,AVG(score) avg_score
FROM sc
GROUP BY cid
ORDER BY avg_score DESC,cid ASC
26.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT a.sid,a.sname,AVG(b.score) avg_score
FROM student a
INNER JOIN sc b
ON a.sid = b.sid
GROUP BY a.sid
HAVING avg_score>85
27.查询课程名称为「数学」,且分数低于60的学生姓名和分数
SELECT
a.sname,b.score
FROM student a
INNER JOIN sc b
ON a.sid=b.sid
INNER JOIN course c
ON b.cid=c.cid AND c.cname='数学'
WHERE b.score<60
# 第二种
SELECT a.sname,b.score
FROM student a
INNER JOIN sc b
ON a.sid=b.sid
WHERE cid IN (SELECT cid FROM course WHERE cname='数学')
AND b.score <60
28.查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
SELECT a.*,b.cid,b.score
FROM student a
LEFT JOIN sc b
ON a.sid = b.sid
29. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SELECT a.sname,c.cname,b.score
FROM student a
INNER JOIN (
SELECT sid,cid,score
FROM sc
WHERE score>70) b
ON a.sid = b.sid
INNER JOIN course c
ON b.cid=c.cid
30. 查询存在不及格的课程
# 筛选出小于60分的成绩
SELECT cid
FROM sc
WHERE score<60
# 和课程表关联
SELECT a.cid,b.cname
FROM (SELECT cid
FROM sc
WHERE score<60) a
INNER JOIN course b
ON a.`CId`=b.cid
GROUP BY a.cid
31. 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT sid,sname
FROM student
WHERE sid IN(
SELECT sid
FROM sc
WHERE cid=01 AND score>=80)
- 求每门课程的学生人数
SELECT cid,COUNT(1)
FROM sc
GROUP BY cid
33.假设成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
# 连接一个表,in里面两个表,可根据思路分开写
SELECT a.*,b.score
FROM student a
INNER JOIN (
SELECT sid,score,MAX(score)
FROM sc
WHERE cid IN (SELECT cid
FROM course c
INNER JOIN teacher t
ON c.tid=t.tid
WHERE t.tname='张三')) b
ON a.sid = b.sid
# 表全部关联再选择
SELECT b.*,a.score
FROM sc a
LEFT JOIN student b
ON a.sid = b.sid
LEFT JOIN course c
ON a.cid=c.cid
LEFT JOIN teacher d
ON c.tid=d.tid
WHERE d.tname = '张三'
ORDER BY a.score DESC
LIMIT 1
34.假设成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
# 先找出最高成绩,然后找成绩等于这个成绩的学生
SELECT c.sid,MAX(score)
FROM sc a
LEFT JOIN course c
ON a.cid=c.cid
LEFT JOIN teacher d
ON c.tid=d.tid
WHERE d.tname = '张三'
SELECT s.*,ss.score
FROM student s
INNER JOIN sc ss
ON s.sid = ss.sid
WHERE ss.score =(SELECT MAX(score)
FROM sc a
LEFT JOIN course c
ON a.cid=c.cid
LEFT JOIN teacher d
ON c.tid=d.tid
WHERE d.tname = '张三')
35.查询同一个学生、不同课程、成绩相同的学生的学生编号、课程编号、学生成绩
SELECT a.sid,a.cid,b.cid,a.score,b.score
FROM sc a
INNER JOIN sc b
ON a.sid = b.sid
WHERE a.`score`=b.score AND a.cid != b.cid
GROUP BY a.sid,a.cid
36.查询每门功成绩最好的前两名
SELECT a.cid,a.sid,a.cnt
FROM(
SELECT cid,sid,rank() over(PARTITION BY cid ORDER BY score DESC) cnt
FROM sc) a
WHERE a.cnt<3
37.统计每门课程的学生选修人数(超过5人的课程才统计)
SELECT cid,COUNT(1) cnt
FROM sc
GROUP BY cid
HAVING cnt>5
- 检索至少选修两门课程的学生学号
SELECT sid,COUNT(1) cnt
FROM sc
GROUP BY sid
HAVING cnt>1
39.查询选修了全部课程的学生信息
SELECT a.*
FROM student a
INNER JOIN (
SELECT sid,COUNT(1) cnt
FROM sc
GROUP BY sid
HAVING cnt = (SELECT COUNT(1)
FROM course
)) b
ON a.`SId`=b.sid
SELECT a.*
FROM student a
WHERE (SELECT COUNT(1) FROM sc b WHERE a.sid=b.sid)
=(SELECT COUNT(1) FROM course)
40.查询各学生的年龄,只按年份来算
SELECT sid,sname,YEAR(NOW())-YEAR(sage) age
FROM student
41.按照出生日期来算,当前月日<出生年月的月日则,年龄减一
# SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2003-04-01') #0
# SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2003-06-01') #1
# SELECT TIMESTAMPDIFF(DAY,'2002-05-01','2003-06-01') # 天数
SELECT *,TIMESTAMPDIFF(YEAR,sage,NOW())
FROM student
42.查询本月过生日的学生
SELECT sname
FROM student
WHERE MONTH(sage) = MONTH(NOW())
43.查询下月过生日的学生
SELECT sname
FROM student
WHERE MONTH(sage) = MONTH(NOW())
分组函数和窗口函数
SELECT sid,cid,rank() over(PARTITION BY sid)
FROM sc
SELECT sid,cid
FROM sc
GROUP BY sid,cid