安装mysql (结构化查询语言(Structured Query Language)) - 数据库学习50题 - 《数据库》

分组聚集（group by）
        聚集函数：平均值（avg）、最小值（min）、最大值（max）、总和（sum）、计数（count）。
        出现在select语句中但没有被聚集的属性只能是出现在group by子句中的属性。
        错误查询
having子句（出现group by时使用）
        有group by时，select 和 having子句中出现的属性有所限制。
        任何出现在having子句中，但没有被聚集的属性必须出现在group by子句中。
        正确查询

数据库创建
CREATE TABLE `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`)
);

题目源自：
https://zhuanlan.zhihu.com/p/43289968
INNER JOIN 交集
LEFTJOIN

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
SELECT a.s_id "s_no",a.s_score"01",b.s_score"02",c.s_name FROM
(
SELECT s_id,c_id,s_score FROM score WHERE c_id='01'
) AS a
INNER JOIN
(
SELECT s_id,c_id,s_score FROM score WHERE c_id='02'
) as b on a.s_id=b.s_id
INNER JOIN student as c on c.s_id=a.s_id
WHERE a.s_score>b.s_score

图片.png

2.查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
SELECT s_id ,AVG(s_score)
FROM score
GROUP BY s_id HAVING AVG(s_score) > 60

3、查询所有学生的学号、姓名、选课数、总成绩（不重要）>>>SELECT中的字段最好是统计值
SELECT a.s_id,a.s_name,count(b.c_id),
SUM(CASE WHEN b.s_score is NULL THEN 0 ELSE b.s_score END)
FROM student AS a
LEFT JOIN score AS b on a.s_id=b.s_id
GROUP BY s_id,a.s_name

图片.png

4、查询姓“猴”的老师的个数（不重要）
SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE'张%'
找姓张的老师，去重
SELECT COUNT(DISTINCT t_name)
FROM teacher
WHERE t_name LIKE'张%'

5、查询没学过“张三”老师课的学生的学号、姓名（重点）
SELECT s_id,s_name FROM student
WHERE s_id NOT IN (
SELECT s_id FROM score
WHERE c_id=(
SELECT c_id FROM course
WHERE t_id=(
SELECT t_id FROM teacher
WHERE t_name='张三'
)
) 
)
思路：
1.先在teacher 表中找张三老师教的t_id
2.根据t_id 在课程表中找它教的课的C_ID
3.根据C_ID，在分数表中找到学生学号
4.根据学号找到学过该课的学生
5.找到没学过的学生
方法2：
SELECT s_id,s_name FROM student
WHERE s_id NOT in
(
SELECT s_id FROM score AS a
INNER JOIN course AS c on a.c_id=c.c_id
INNER JOIN teacher AS t on t.t_id=c.t_id
WHERE t.t_name='张三'
)

6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）
NEW:ORDER BY 语句用于根据指定的列对结果集进行排序。
    ORDER BY 语句默认按照升序对记录进行排序。
SELECT st.s_id,st.s_name,c.c_id,c.c_name,t.t_id,t.t_name FROM
student as st
INNER JOIN score AS s on st.s_id=s.s_id
INNER JOIN course as c on c.c_id=s.c_id
INNER JOIN teacher as t on t.t_id=c.t_id
WHERE t.t_name='张三'
ORDER BY s.s_id

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（重点）
SELECT s_id,s_name FROM student
WHERE s_id in 
(
SELECT a.s_id FROM
(SELECT s_id FROM score WHERE c_id='01' ) as a
INNER JOIN
(SELECT s_id FROM score WHERE c_id='02') as b 
on a.s_id=b.s_id
)

补充：SELECT LENGTH(null )

8、查询课程编号为“02”的总成绩（不重点）
SELECT SUM(s_score),AVG(s_score),COUNT(s_score),COUNT(DISTINCT s_id)FROM score
WHERE c_id='02'

9、查询所有课程成绩小于60分的学生的学号、姓名
思路：先查询小于60分的学生的课程数
再查所有学生的课程数，如果2者相等，就行
SELECT a.s_id from 
(SELECT s_id,COUNT(c_id) AS cnt
FROM score
WHERE s_score<60
GROUP BY s_id
) as a
INNER JOIN
(SELECT s_id,COUNT(c_id) as cnt FROM score
GROUP BY s_id
)as b on a.s_id=b.s_id
WHERE a.cnt=b.cnt

10.查询没有学全所有课的学生的学号、姓名(重点)
注: 
1.最全的课程要从course中选择，而不是 score
2.select可以没有和HAVING一样的聚合函数
SELECT s_id,s_name FROM student
WHERE s_id in (
SELECT s_id FROM score
GROUP BY s_id HAVING COUNT(DISTINCT c_id)<(SELECT COUNT(DISTINCT c_id)FROM course)
)
法二
SELECT st.s_id,st.s_name
FROM student as st
LEFT JOIN score as sc on st.s_id=sc.s_id
GROUP BY st.s_id HAVING COUNT(DISTINCT sc.c_id)<(SELECT COUNT(DISTINCT c_id) FROM course)

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名（重点）
注: and在括号外用
疑问;distinct 不知道什么时候用？
法1：
SELECT s_id,s_name FROM student
WHERE s_id in 
(
SELECT DISTINCT s_id FROM score
WHERE c_id in
(
SELECT c_id FROM score
WHERE s_id='01'
) AND s_id!='01'
)
法2：
SELECT a.s_id,a.s_name FROM student as a
INNER JOIN(
SELECT DISTINCT s_id FROM score
WHERE c_id in
(SELECT c_id FROM score WHERE s_id='01' ) AND s_id!='01'
)as b on a.s_id=b.s_id

12.查询和“01”号同学所学课程完全相同的其他同学的学号(重点)
SELECT * FROM student WHERE s_id in 
(
SELECT s_id FROM score
WHERE s_id!='01'
GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(SELECT COUNT(DISTINCT c_id) FROM score WHERE s_id='01')
)
and s_id NOT in
(
SELECT s_id FROM score
WHERE c_id not in (
SELECT c_id FROM score
WHERE s_id='01') and s_id!='01'
)

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
SELECT a.s_id,a.s_name ,AVG(DISTINCT s_score)FROM student as a
INNER JOIN score as b
on a.s_id=b.s_id
WHERE a.s_id in
(
SELECT s_id  FROM score
WHERE s_score<60
GROUP BY s_id HAVING COUNT(DISTINCT c_id)>=2
)
GROUP BY s_id,s_name HAVING AVG(DISTINCT s_score)

16 检索"01"课程分数小于60，按分数降序排列的学生信息（和34题重复，不重点）
SELECT t.*,s.s_score FROM student as t
INNER JOIN score as s on t.s_id=s.s_id
WHERE s.c_id='01' and s.s_score<60
ORDER BY s.s_id DESC  >>升序ASC

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样)
法1：
SELECT s.s_id,s.c_id,s.s_score,b.avg_s_score FROM score as s
INNER JOIN
(
SELECT s_id,AVG(s_score) as avg_s_score FROM score
GROUP BY s_id
) as b on s.s_id=b.s_id
ORDER BY avg_s_score DESC
法2：SELECT s_id '学号'
,MAX(CASE WHEN c_id='01' THEN s_score ELSE NULL END ) '语文'
,MAX(CASE WHEN c_id='02' THEN s_score ELSE NULL END )'数学'
,MAX(CASE WHEN c_id='03' THEN s_score ELSE NULL END )'英语'
,AVG(s_score) '平均成绩'
FROM score
GROUP BY s_id
ORDER BY AVG(s_score) DESC

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 (超级重点)SELECT s.c_id,c.c_name,max(s.s_score),min(s.s_score),AVG(s.s_score)
,sum(case when s.s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_id) '及格'
,sum(case when s.s_score>=70 and s.s_score<80 THEN 1 ELSE 0 END)/COUNT(s_id)'中等'
,sum(case when s.s_score>=80 and s.s_score<90 THEN 1 ELSE 0 END)/COUNT(s_id)'优良'
,sum(case when s.s_score>=80 and s.s_score>=90 THEN 1 ELSE 0 END)/COUNT(s_id)'优秀'
FROM score as s
INNER JOIN course AS c on s.c_id=c.c_id
GROUP BY c_id

-- 19、按各科成绩进行排序，并显示排名(重点row_number)
row_number(）over （order by 列），
row_number 没有重复值的排序，经历相等也是不重复的，可以进行分页使用，同样分数的人，排序也不一样
rank 跳跃排序，同样分数的人排序一样，但下一个不同分数的人排名加N，
dense_rank 连续排序，同样分数的人排序一样，但下一个不同分数的人排名加1
》》》https://yq.aliyun.com/articles/593698
法一：row_number(),根据课程分组，在课程里面再进行排序
select *,row_number()  over(partition by c_id order by s_score desc) as 'rank' from score

图片.png

法二：
select *,rank()  over(partition by c_id order by s_score desc) as 'rank' from score

图片.png

法三：select *,dense_rank() over(partition by c_id order by s_score desc) as 'rank' from score

图片.png

20、查询学生的总成绩并进行排名（不重点）
SELECT a.s_id '学号',a.s_name '姓名',b.总成绩 FROM student AS a
INNER JOIN
(
SELECT s_id,SUM(s_score)'总成绩' FROM score
GROUP BY s_id
ORDER BY SUM(s_score) DESC
) as b on a.s_id=b.s_id

21 、查询不同老师所教不同课程平均分从高到低显示(不重点)
法一：以课程为主题求平均分
SELECT c.t_id,c.t_name,a.c_id,b.c_name,AVG(s_score)  FROM score  as a
INNER JOIN course as b on a.c_id=b.c_id
INNER JOIN teacher as c on c.t_id=b.t_id
GROUP BY a.c_id
ORDER BY AVG(s_score) DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）
法一
SELECT * FROM
(select b.s_id,b.s_name,a.s_score,row_number()  over(partition by c_id order by s_score desc) as 'rank1' from score as a
INNER JOIN student as b  on a.s_id=b.s_id
) as c
WHERE rank1 in (2,3)
法二
SELECT * FROM
(SELECT b.s_id,b.s_name,a.s_score,ROW_NUMBER() over(PARTITION by c_id ORDER BY s_score DESC) m FROM score  as a
INNER JOIN student as b on b.s_id=a.s_id ) c
WHERE m in (2,3)

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点和18题类似)
备注：count（） else后为null 不能为0
SELECT b.c_id,b.c_name,min(s_score),max(s_score),AVG(s_score)
,count(case when a.s_score<60 THEN 1 ELSE NULL end ) '<60人数',avg(case when a.s_score<60 THEN 1 ELSE NULL end )'<60率'
,count(case when a.s_score>=60 and a.s_score<70 THEN 1 else NULL end ) '60-70人数',avg(case when a.s_score>=60 and a.s_score<70 THEN 1 else 0 end )'60-70人率'
,count(case when a.s_score>=70 and a.s_score<85 THEN 1 else NULL end ) '70-85人数',avg(case when a.s_score>=70 and a.s_score<85 THEN 1 else 0 end )'70-85率'
,count(case when a.s_score>=85 and a.s_score<=100 THEN 1 else NULL end ) '85-100人数',avg(case when a.s_score>=85 and a.s_score<=100 THEN 1 else 0 end )'85-100率'
FROM score as a
INNER JOIN course as b on a.c_id=b.c_id
GROUP BY b.c_id,b.c_name

24、查询学生平均成绩及其名次（同19题，重点）
SELECT s_id,AVG(s_score),ROW_NUMBER() over( ORDER BY AVG(s_score) DESC) FROM score
GROUP BY s_id

-- 25、查询各科成绩前三名的记录（不考虑成绩并列情况）（重点 与22题类似）
法一：
SELECT b.c_id,a.s_id,a.s_name,b.s_score,b.c FROM student as a
INNER JOIN
(SELECT * FROM
(SELECT s_id,c_id,s_score,ROW_NUMBER() over (PARTITION by c_id ORDER BY s_score DESC) as c FROM score) e
WHERE c in (1,2,3)
) as b on a.s_id=b.s_id
法2：
SELECT s_id,s_score
,(case when c=1 THEN s_score ELSE NULL end)'第一'
,(case when c=2 THEN s_score ELSE NULL end) '第二'
,(case when c=3 THEN s_score ELSE NULL end) '第三'
FROM
(
SELECT bb.s_id,aa.c_id,aa.s_score,ROW_NUMBER() over (PARTITION by c_id ORDER BY s_score DESC)  c FROM score as aa 
INNER JOIN student as bb on aa.s_id=bb.s_id
) as b
WHERE c in (1,2,3)

-- 26、查询每门课程被选修的学生数(不重点)
SELECT c.c_id,c.c_name ,num from course as c 
INNER JOIN
(SELECT c_id,COUNT(DISTINCT s_id) num FROM score
GROUP BY c_id) b on c.c_id=b.c_id

-- 27、 查询出只有两门课程的全部学生的学号和姓名(不重点)
SELECT st.s_id,st.s_name,m FROM student as st 
INNER JOIN
(SELECT * FROM (
SELECT s_id ,count(c_id) m FROM score
GROUP BY s_id) mm
WHERE m=2) mmm on st.s_id=mmm.s_id

28、查询男生、女生人数(不重点)
SELECT s_sex,COUNT(s_id) FROM student
GROUP BY s_sex
法2：
SELECT 
sum(case when s_sex='男' THEN 1 ELSE 0 end) '男生',
sum(case when s_sex='女' THEN 1 ELSE 0 end) '女生'
FROM student

29 查询名字中含有"风"字的学生信息（不重点）
》》》》》》》》》》like 包含，% 表示通配符
SELECT * FROM student
WHERE s_name LIKE '%风%'

31、查询1990年出生的学生名单（重点year）》》》对时间的操作
SELECT Month(CURRENT_DATE)
格式：YYYY-MM-DD,YYYYMMDD,YYYY/
SELECT *
FROM student
WHERE s_birth like "1990%"
法二
SELECT *
FROM student
WHERE s_birth =1990

图片.png

-- 32查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩（不重要）
where 是分组前帅选
SELECT st.s_id,st.s_name,m FROM student as st
INNER JOIN
(SELECT * FROM
(SELECT s_id,AVG(s_score) m FROM score
GROUP BY s_id) b
WHERE m>=85) as mm on st.s_id=mm.s_id
法2：
SELECT st.s_id ,st.s_name,AVG(sc.s_score) FROM student as st
INNER JOIN score as sc on st.s_id=sc.s_id
GROUP BY st.s_id ,st.s_name
HAVING AVG(sc.s_score)>85

33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列（不重要）
SELECT sc.c_id ,c.c_name,AVG(s_score) as avg1 FROM score as sc 
INNER JOIN course as c on c.c_id=sc.c_id
GROUP BY sc.c_id 
ORDER BY avg1 asc,c_id DESC

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数（不重点）
SELECT st.s_id,st.s_name,sc.s_score FROM student as st 
INNER JOIN score as sc on st.s_id=sc.s_id
INNER JOIN course as c on sc.c_id=c.c_id
WHERE c.c_name='数学' and sc.s_score<60

35、查询所有学生的课程及分数情况（重点）
SELECT st.s_id,st.s_name
,max(case when c.c_name='语文' THEN s_score ELSE NULL end) '语文'
,max(case when c.c_name='数学' THEN s_score ELSE NULL end) '数学'
,max(case when c.c_name='英语' THEN s_score ELSE NULL end) '英语'
FROM student as st 
LEFT JOIN score as sc on st.s_id=sc.s_id
left JOIN course as c on sc.c_id=c.c_id
GROUP BY st.s_id

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数（重点）
SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student as st
INNER JOIN score as sc on st.s_id=sc.s_id
INNER JOIN course as c on sc.c_id=c.c_id
WHERE s_score>70

37、查询不及格的课程并按课程号从大到小排列(不重点)
SELECT st.s_id,st.s_name,sc.c_id,c.c_name,sc.s_score FROM score as sc
INNER JOIN course as c on sc.c_id=c.c_id
RIGHT JOIN student as st on st.s_id=sc.s_id
WHERE sc.s_score<60 or sc.s_score is null
ORDER BY sc.c_id DESC

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名（不重要）
SELECT st.s_id,st.s_name,c.c_name,sc.s_score FROM student AS st
INNER JOIN score as sc on st.s_id=sc.s_id
INNER JOIN course as c on c.c_id=sc.c_id
WHERE sc.s_score>80 and c.c_id='03'

39、求每门课程的学生人数（不重要）
SELECT sc.c_id, c.c_name,count(s_id) FROM score as sc
INNER JOIN course as c on sc.c_id=c.c_id
GROUP BY sc.c_id, c.c_name

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩（重要top）
>>>SQL SERVER 中用top,MYSQL 用 limit,LIMIT 0,1 0 表示起始行，1 表示行数
SELECT st.s_id,st.s_name,sc.s_score FROM score as sc
INNER JOIN course as c on sc.c_id=c.c_id
INNER JOIN teacher as t on t.t_id-c.t_id
INNER JOIN student as st on st.s_id=sc.s_id
WHERE t.t_name='张三'
ORDER BY sc.s_score DESC LIMIT 0,1

41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
SELECT s_id FROM
(
SELECT a.s_id,a.s_score FROM score as a
INNER JOIN 
(SELECT s_id FROM score
GROUP BY s_id HAVING COUNT(DISTINCT c_id)>1
) as b on a.s_id=b.s_id
GROUP BY s_id,s_score
) as c
GROUP BY s_id HAVING COUNT(s_score)=1

42、查询每门功成绩最好的前两名（同22和25题）
SELECT * FROM
(SELECT a.s_id ,b.s_name,a.c_id,a.s_score,ROW_NUMBER() over (PARTITION by c_id ORDER BY s_score DESC) sc1
FROM score as a
INNER JOIN student as b on a.s_id=b.s_id
) as c
WHERE sc1 in (1,2)

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列（不重要）
SELECT c_id,COUNT(DISTINCT s_id) cc FROM score
GROUP BY c_id HAVING COUNT(DISTINCT s_id)>5
ORDER BY cc DESC,c_id ASC

44、检索至少选修两门课程的学生学号（不重要）
SELECT s_id,COUNT(DISTINCT c_id)
FROM score
GROUP BY s_id HAVING COUNT(c_id)>=2

45、 查询选修了全部课程的学生信息（重点划红线地方）
SELECT s_id,COUNT(DISTINCT c_id) FROM score
GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(SELECT count(c_id) FROM course)

没学过张三老师课的同学
SELECT * FROM student WHERE s_id not in
(
SELECT st.s_id FROM score as sc
INNER JOIN student as st on st.s_id=sc.s_id
INNER JOIN course as c on sc.c_id=c.c_id
INNER JOIN teacher as t on t.t_id=c.t_id
WHERE t.t_name ='张三'
)

46、查询各学生的年龄（精确到月份）
SELECT *, DATEDIFF(CURRENT_DATE,s_birth)/365 '年龄' FROM student

47、查询本月过生日的学生（无法使用week、date(now()）

-- 48查询下周过生日的同学,换算为同一年
SELECT * FROM student
WHERE WEEK(s_birth,1)=WEEK(CURRENT_DATE,1)+1

49查询本月过生日的同学
SELECT * FROM student
WHERE MONTH(s_birth)= MONTH(CURRENT_DATE)

查询下个月过生日的同学
SELECT * FROM student
WHERE MONTH(s_birth)=MONTH(NOW())+1

数据库 学习50题

数据库学习50题