mysql基础逻辑50题

1-- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
-- 先成绩表自连接 再与 学生信息连接
SELECT s1.s_id,s3.* from score s1,score s2,student s3
WHERE s1.c_id=1 and s2.c_id=2 and s1.s_id=s2.s_id and s1.s_score>s2.s_score
and s3.s_id=s1.s_id

-- 选修01号和02号课程的学生 id
-- 两个查询语句再进行内连接
SELECT * from student
INNER JOIN (
SELECT * from score WHERE c_id=1) t1 on student.s_id=t1.s_id
INNER JOIN (
SELECT * from score WHERE c_id=2) t2 on student.s_id=t2.s_id
WHERE t1.s_score>t2.s_score


-- 方法二 变成宽型数据
-- 获取每个里面01 或者02课程的最大值 作为中间结果值
SELECT * from student 
left join 
(SELECT t1.s_id as s_id,
			 max(case when t1.c_id=01 then t1.s_score  ELSE NULL end ) s01,
			 max(case when t1.c_id=02 then t1.s_score  ELSE NULL end ) s02
from score t1
group by t1.s_id) r1
on student.s_id=r1.s_id
where r1.s01>r1.s02

2-- 既选择编号01 又选择编号02的学号
-- 先找到同时选择两门课程的人
SELECT * from student
where student.s_id in 
(
SELECT s_id from score
where c_id in(1,2) 
GROUP BY s_id
HAVING count(c_id)>=2
) 

3-- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT student.s_id,student.s_name,round(sc,2) from student 
INNER JOIN
(SELECT s_id,avg(s_score) as sc from score
GROUP BY s_id
HAVING sc>=60
) r1
on student.s_id=r1.s_id

4-- 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
select s.s_id,s.s_name,ifnull(round(avg_score, 2), 0) as avg_score
from student s
left join (
	select s_id,avg(s_score) as avg_score
	from score
	group by s_id
) t1
on s.s_id = t1.s_id
where avg_score is null or avg_score < 60;

-- 查询和"01"号的同学学习的课程完全相同的其他同学的信息
-- 找到有交叉数值的同学  再去统计有交叉数为3的同学
-- select c_id  from score where s_id=1
select *
from student t
where s_id in (
			 select s_id 
			 from score 
			 where s_id <> 1 
			 group by s_id 
			 having  GROUP_CONCAT(c_id Separator'')  
			 = 
			 (select GROUP_CONCAT(c_id Separator'') from score where s_id=1)
)

5-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
-- 尽量把要计算的写在（查询语句中）
-- 想要最后的结果值里面没有null  要在最后的select层里面输入ifnull
SELECT student.s_id,student.s_name,IFNULL(nums,0),IFNULL(scores,0) from student
LEFT JOIN(
SELECT s_id,COUNT(c_id)as nums,SUM(s_score) as scores from score
GROUP BY s_id)r1 
on student.s_id=r1.s_id


6-- 查询"李"姓老师的数量
-- coun(*) 后面有别名才不会出错

-- 错误示例-- SELECT coun(*) from teacher
-- -- where t_name like '李%'

select count(*) as cnt_name_li
from teacher
where t_name like '李%';


7-- 询学过"张三"老师授课的同学的信息
-- 1 张三老师课的课程id 2 score找学过这个的s_id 3 关联学生表
SELECT * from student
where student.s_id in (
SELECT DISTINCT(score.s_id) FROM score
LEFT JOIN course on score.c_id=course.c_id
LEFT JOIN teacher ON course.t_id= teacher.t_id 
WHERE teacher.t_name = '张三')

8-- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 找到分别分别学了两门课的人
-- 傻瓜方法 在1 也在2
-- SELECT * from student 
-- where s_id in (
-- SELECT s_id from score where c_id=1)
-- and s_id in (SELECT s_id from score where c_id=2)
-- 方法二 区间在两个数据库中间 统计数为2
SELECT * from student
where s_id in(
SELECT s_id from score
where c_id in(1,2)
GROUP BY s_id 
HAVING COUNT(*)=2)

10-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
-- 找到分别分别学了两门课的人
-- 傻瓜方法 在1 不在2
SELECT * from student 
where s_id in (
SELECT s_id from score where c_id=1)
and s_id  not in (SELECT s_id from score where c_id=2)

11-- 查询没有学全所有课程的同学的信息
SELECT * from student
where s_id not in (
SELECT DISTINCT(s_id) from score
GROUP BY s_id
HAVING COUNT(s_id)=(SELECT COUNT(*) FROM course)
)

备注:count(*) 和 count(1)和count(指定字段)区别
count(*)包括了所有的列，相当于行数，在统计结果的时候， 不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候， 不会忽略列值为NULL
count(字段)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。

DAY-2

IN与 inner join区别
从查询结果中可以看出，in的结果是不会有重复的，对非主键进行join时，join的结果是有重复的。如果说还有另一个区别的话就是join会产生一个两表合并的临时表，in不会产生两表合并的临时表。

1.-- 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT * FROM student
INNER  JOIN (
SELECT s_id from score
where c_id in 
(SELECT c_id from score where s_id=1)
GROUP BY s_id
HAVING COUNT(1)=(SELECT COUNT(1) from score where s_id=1)
) t on student.s_id=t.s_id
where student.s_id!=1

2.-- 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
-- SELECT* from student
-- INNER JOIN(
-- SELECT DISTINCT s_id from score
-- WHERE c_id in(
-- SELECT c_id from score where s_id=1)) t 
-- on student.s_id=t.s_id
-- 不知道两者的区别
select *
from student
where s_id in (
	select distinct s_id
	from score s
	inner join (
		select c_id from score where s_id = 1
	) t1
	on s.c_id = t1.c_id
)

3.-- 查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT * from student
where s_id not in (
SELECT s_id from score WHERE
c_id=(
SELECT c_id from course
inner JOIN (
		SELECT t_id from teacher
		where t_name='张三') t 
		on course.t_id=t.t_id
)
)

4.-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT * FROM student
INNER JOIN(
SELECT s_id,count(c_id),ROUND(avg(s_score),2)  from  score
where  s_score<60 
GROUP BY s_id
HAVING COUNT(c_id)>=2
) t
on student.s_id=t.s_id

5-- 检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT * from student
INNER JOIN (
SELECT s_id,s_score from score where c_id=1 and s_score<60
) t on student.s_id=t.s_id
ORDER BY s_score desc
rank使用方法
rank() over(order by s_score desc)

6-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- SELECT * from student
-- INNER JOIN(
-- SELECT s_id,ROUND(IFNULL(avg(s_score),0),2 )as avg_sc from score
-- GROUP BY s_id)
-- t on student.s_id=t.s_id
-- ORDER BY avg_sc desc

-- avg不包含null函数
-- SELECT s_id,avg(s_score) from score
-- GROUP BY s_id
-- 要考虑到null的情况

7-- 每门课的平均成绩
select
	s.s_id as 学号,
	sum(case c_id when 1 then s_score else 0 end) as 语文,
	sum(case c_id when 2 then s_score else 0 end) as 数学,
	sum(case c_id when 3 then s_score else 0 end) as 英语,
	ifnull(round(avg(s_score), 2), 0) as 平均成绩
from student s
left join score sc on s.s_id = sc.s_id
group by s.s_id
order by 平均成绩 desc;

 DAY-3

1.-- 查询各科成绩最高分、最低分和平均分 是后续要重写的题目
-- 先根据课程编号求每门课的最高分最低分
-- – 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select
  c.c_id as 课程ID,
	max(s_score) as 最高分,
	min(s_score) as 最低分,
	round(avg(s_score), 2) as 平均分,
	concat(round(sum(case when s_score >= 60 then 1 else 0 end) / count(*) * 100, 2), '%') as 及格率,
	concat(round(sum(case when s_score BETWEEN 80 and 90  then 1 ELSE 0 END)/COUNT(*)*100),'%') as 中等率,
	concat(sum(case when s_score BETWEEN 70 and 80 then 1 ELSE 0 end )/count(*)*100,'%') as 优良率,
	concat(round(sum(case when s_score >= 90 then 1 ELSE 0 end  ) / count(*) * 100, 2), '%') as 优秀率
from score s
inner join course c on c.c_id = s.c_id
group by c.c_id;

2-- 查询男生、女生人数
-- 根据性别划分 男的为一组 女的为一组
SELECT s_sex,count(*) from student
GROUP BY s_sex

3-- 查询不同老师所教不同课程平均分从高到低显示
SELECT teacher.t_id,sc.c_id,ROUND(avg(s_score),2)  as avg_score from score sc
INNER JOIN course on sc.c_id=course.c_id
INNER JOIN teacher on course.t_id=teacher.t_id
GROUP BY t_id,c_id
order by avg_score desc 
多字段组合

4-- 查询每门课程被选修的学生数
SELECT c.c_id,c.c_name,cnt_student from course c
INNER JOIN (
SELECT c_id,count(*) as cnt_student from score
GROUP BY c_id) t on c.c_id=t.c_id

5-- 查询出只有两门课程的全部学生的学号和姓名
SELECT * from student 
INNER JOIN (
SELECT s_id  from score
GROUP BY s_id
HAVING COUNT(*)=2 )t on student.s_id=t.s_id

6-- 查询名字中含有"风"字的学生信息
SELECT * from student 
WHERE student.s_name like'%风%'

7-- 查询1990年出生的学生名单
SELECT * from student
where YEAR(s_birth)=1990

DAY-4

1-- 查询同名同性学生名单，并统计同名人数
-- 同名同姓人数大于1
SELECT s_name,s_sex,COUNT(*) from student
GROUP BY s_name,s_sex
HAVING COUNT(*)>1

2-- 查询1990年出生的学生名单
SELECT * from student
where YEAR(s_birth)=1990

3-- 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT* from course
INNER JOIN (
SELECT c_id,ROUND(AVG(s_score),2) as avg_score FROM score
GROUP BY c_id) t 
on course.c_id=t.c_id

ORDER BY avg_score desc,course.c_id

4-- 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
-- 大于85不会有空的情况
SELECT * from student
INNER JOIN(
SELECT s_id  from score
GROUP BY s_id
HAVING ROUND(AVG(s_score),2)>=85) t
on student.s_id=t.s_id

5-- 查询课程名称为"数学"，且分数低于60的学生姓名和分数
-- SELECT * from score
-- where c_id=(
-- SELECT
-- (SELECT c_id from course
-- where c_name='数学'))
-- and s_score<60
-- 用inner join 方式
SELECT * from student
INNER JOIN (
SELECT s_id from score
INNER JOIN course 
on score.c_id=course.c_id
where course.c_name='数学' and score.s_score<60
) t on student.s_id=t.s_id

6-- 查询所有学生的课程及分数情况
-- 为什么没有王菊
-- select 
-- 	s.s_id,
-- 	sum(case when c_id=1 then s_score ELSE 0 end) as a,
-- 	sum(case when c_id=2 then s_score ELSE 0 end)as b,
-- 	sum(case when c_id=3 then s_score ELSE 0 end) as c
-- from score s
-- left  JOIN student on student.s_id=s.s_id
-- GROUP BY s.s_id

select
	s.s_id,
	sum(case c_id when 1 then s_score else 0 end) as 语文,
	sum(case c_id when 2 then s_score else 0 end) as 数学,
	sum(case c_id when 3 then s_score else 0 end) as 英语
from student s
left join score sc on s.s_id = sc.s_id
group by s.s_id;

7-- 查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
select sc.s_id
from score sc
inner join student s on sc.s_id =  s.s_id
inner join course c on sc.c_id = c.c_id
where s_score > 70
GROUP BY sc.s_id

困扰问题 分组后数据不可见+空值计算考虑

8-- 查询课程不及格的学生
-- 左连接 左边的数值一定要存在
SELECT * FROM student
LEFT JOIN score on score.s_id=student.s_id
where s_score<60 or s_score is null

9-- 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
SELECT * from student
INNER JOIN (
SELECT s_id from score
where c_id=1 and s_score>=80)
t 
on student.s_id=t.s_id

10-- 求每门课程的学生人数
SELECT* from course
INNER JOIN(
	SELECT score.c_id,COUNT(*)as nums from score 
  GROUP BY c_id
)t
on course.c_id=t.c_id

11-- 查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT * from student
INNER JOIN
(SELECT s_id,s_score from score 
INNER  JOIN (
SELECT c_id from course
INNER JOIN teacher on course.t_id=teacher.t_id
WHERE teacher.t_name='张三'
) t on score.c_id=t.c_id
ORDER BY s_score desc
LIMIT 1
)t1
on student.s_id=t1.s_id

12-- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT score.s_id ,student.s_name,score.c_id,score.s_score  from score
INNER JOIN student on score.s_id=student.s_id
where s_score=(
SELECT s_score  from score
GROUP BY s_score
HAVING  COUNT(*)>2)

13-- 统计每门课程的学生选修人数
-- SELECT c_id,count(*) FROM score
-- GROUP BY c_id
-- HAVING count(*)>5
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT * from course
INNER JOIN (
SELECT c_id,count(*) as cnt FROM score
GROUP BY c_id
HAVING count(*)>5) t
on course.c_id=t.c_id
ORDER BY cnt,course.c_id

14-- 检索至少选修两门课程的学生学号
SELECT * from student
INNER JOIN (
SELECT s_id from score
GROUP BY s_id
HAVING COUNT(1)=(SELECT COUNT(1) from course)
) t on student.s_id=t.s_id

一周50基础题结束，后续冲刷一遍+刷力扣+存储过程学习