mysql50解法与思考过程

萧木易

已于 2022-02-27 17:12:54 修改

阅读量197

点赞数 1

分类专栏： mysql 文章标签：数据库

于 2021-10-21 23:19:26 首次发布

本文链接：https://blog.csdn.net/weixin_43997319/article/details/120896997

版权

mysql 专栏收录该内容

45 篇文章 5 订阅

订阅专栏

解法千千万，我的解法是接近思路的想法，没有优化的SQL，方便理解，如果有错误的地方欢迎评论区指正

--建表
--学生表
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)
);

--课程表
CREATE TABLE Course(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL DEFAULT '',
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

--教师表
CREATE TABLE Teacher(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

--成绩表
CREATE TABLE Score(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id,c_id)
);

#学生表
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('08' , '王菊' , '1990-01-20' , '女');

#课程表
insert into course values('01' , '语文');
insert into course values('02' , '数学');
insert into course values('03' , '英语');

#教师表
insert into teacher values('01' , '张三','01');
insert into teacher values('02' , '李四','02');
insert into teacher values('03' , '王五','03');

#成绩表
insert into grade values(1,80,'01' , '01');
insert into grade values(2,90,'01' , '02');
insert into grade values(3,99,'01' , '03');
insert into grade values(4,70,'02' , '01');
insert into grade values(5,60,'02' , '02');
insert into grade values(6,80,'02' , '03');
insert into grade values(7,80,'03' , '01');
insert into grade values(8,80,'03' , '02');
insert into grade values(9,80,'03' , '03');
insert into grade values(10,50,'04' , '01');
insert into grade values(11,30,'04' , '02');
insert into grade values(12,20,'04' , '03');
insert into grade values(13,76,'05' , '01');
insert into grade values(14,87,'05' , '02');
insert into grade values(15,31,'06' , '01');
insert into grade values(16,34,'06' , '03');
insert into grade values(17,89,'07' , '02');
insert into grade values(18,98,'07' , '03');

#1、查询"语文"课程比"数学"课程成绩高的学生的信息及课程分数

解题思路：从成绩表分别取出数学和语文成绩；加上学生表多表关联后判断语文高于数学并通过学生id关联；在查询需要输出的字段

select t1.s_score as '语文',t2.s_score as '数学',t1.s_id,t3.* from
(select s_score,s_id from score where c_id ='01') t1,
(select s_score,s_id from score where c_id ='02') t2, student t3 where t1.s_id=t2.s_id and t1.s_id=t3.s_id and t1.s_score <t2.s_score

SELECT
st.*,
sc.s_score AS '语文',
sc2.s_score '数学'
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
AND sc.c_id = '01'
LEFT JOIN score sc2 ON sc2.s_id = st.s_id
AND sc2.c_id = '02'
WHERE
sc.s_score > sc2.s_score
#2、查询"语文"课程比"数学"课程成绩低的学生的信息及课程分数

与上题一致改下大于小于号

select t1.s_score as '语文',t2.s_score as '数学',t1.s_id,t3.s_name from
(select s_score,s_id from score where c_id ='01') t1,
(select s_score,s_id from score where c_id ='02') t2, student t3 where t1.s_id=t2.s_id and t1.s_id=t3.s_id and t1.s_score <t2.s_score

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

主要考验group by和having的使用

mysql group by没有字段限制，即group by的字段与映射列无约束，注意having和group by 和where的位置关系

select avg(s_score),t1.s_id,t3.s_name from `score` t1,student t3 where t1.s_id=t3.s_id group by t1.s_id having avg(s_score)>60;

SELECT
st.s_id,
st.s_name,
ROUND( AVG( sc.s_score ), 2 ) "平均成绩"
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
AVG( sc.s_score )>= 60
#4查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
select avg(s_score),t1.s_id,t3.s_name from `score` t1,student t3 where t1.s_id=t3.s_id group by t1.s_id having avg(s_score)<60;
SELECT
st.s_id,
st.s_name,(
CASE

WHEN
ROUND( AVG( sc.s_score ), 2 ) IS NULL
THEN
0
ELSE
ROUND( AVG( sc.s_score ), 2 )
END
) "平均成绩"
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
GROUP BY
st.s_id
HAVING
AVG( sc.s_score )< 60
OR AVG( sc.s_score ) IS NULL

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

根据学生id
select sum(t1.s_score),t1.s_id,count(t1.c_id),t2.* from `score` t1 ,student t2 where t1.s_id=t2.s_id group by t1.s_id;
SELECT
st.s_id,
st.s_name,
count( sc.c_id ) "选课总数",
sum( CASE WHEN sc.s_score IS NULL THEN 0 ELSE sc.s_score END ) "总成绩"
FROM
student st
LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
st.s_id
#6、查询"墨"姓老师的数量

SELECT
t.t_name,
count( t.t_id )
FROM
teacher t
GROUP BY
t.t_id
HAVING
t.t_name LIKE "墨%";
# 7、查询学过"墨白"老师授课的同学的信息
select *from student where s_id 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='墨白')))

SELECT
st.*
FROM
student st
LEFT JOIN score sc ON sc.s_id = st.s_id
LEFT JOIN course c ON c.c_id = sc.c_id
LEFT JOIN teacher t ON t.t_id = c.t_id
WHERE
t.t_name = "墨白"
# 8、查询没学过"柠檬"老师授课的同学的信息
select *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='柠檬')))


SELECT
st.*
FROM
student st
WHERE
st.s_id NOT IN (
SELECT
sc.s_id
FROM
score sc
WHERE
sc.c_id IN ( SELECT c.c_id FROM course c LEFT JOIN teacher t ON t.t_id = c.t_id WHERE t.t_name = "柠檬" )
)
#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select s_id from `score` where s_id in
(select s_id from `score` where c_id in ('01')) and c_id in ('02');

select * from `student` where s_id in (
select s_id from `score` where s_id in
(select s_id from `score` where c_id in ('02')) and c_id in ('01'));

update student set s_name = '李嘉欣' , s_birth = '1979-09-12' , s_sex = '女',moble ='11111111111' where s_id = 18;
select * from course;
select * from score;
select * from student;
select * from student;
insert into student(s_name,s_birth,s_sex) values('李宗盛','1991-08-16','123456');

delete from student where s_id='8' and s_name ='李嘉欣';
select sum(t1.s_score),t1.s_id,count(t1.c_id),t2.* from score t1 ,student t2 where t1.s_id=t2.s_id group by t1.s_id

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

解题思路：查询学过02课程，在查询学过01课程并且不在02课程的id，再去拿着id去查学生表

select s_id from `score` where c_id in ('01') and s_id in(
select s_id from `score` where s_id not in(
select s_id from `score` where c_id in ('02')))

11、查询没有学全所有课程的同学的信息

解题思路：一共3门课，查询学过3们课同学的id后排除

select * from `student` where s_id not in(
select s_id from `score` group by s_id having count(c_id)=3)

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

解题思路：查询01同学的所有课程，再查询in这个课程的信息

select * from `student` where s_id in(
select s_id from `score` where c_id in(
select c_id from `score` where s_id ='01'))

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

解题思路：查询01同学的所有课程合并后与其他人对比，这一用到group_concat用法，

sql合并字段值_萧木易的博客-CSDN博客

第一步，查出所有同学课程

SELECT GROUP_CONCAT(c_id),s_id FROM `score` GROUP BY s_id

第二步查出学生01的课程

SELECT GROUP_CONCAT(c_id),s_id FROM `score` GROUP BY s_id HAVING s_id='01'

第三步查出等于01学生课程的学生

SELECT s_id FROM
(SELECT GROUP_CONCAT(c_id) all_c_id,s_id FROM `score` GROUP BY s_id) t1 WHERE all_c_id=(SELECT GROUP_CONCAT(c_id) all_c_id FROM `score` GROUP BY s_id HAVING s_id='01')

第四步查出学生信息，并排除01号学生

SELECT * FROM `student` WHERE s_id IN
(SELECT s_id FROM
(SELECT GROUP_CONCAT(c_id) all_c_id,s_id FROM `score` GROUP BY s_id) t1 WHERE all_c_id=(SELECT GROUP_CONCAT(c_id) all_c_id FROM `score` GROUP BY s_id HAVING s_id='01'))
AND s_id!='01'

14、查询各科成绩最高分、最低分和平均分：以如下形式显示：
课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

解题思路：

查询最高分、最低分和平均分

SELECT MAX(s_score),AVG(s_score),MIN(s_score),c_id FROM `score` GROUP BY c_id

查询成绩

SELECT
CASE WHEN s_score>=90 THEN '优秀'
WHEN s_score>=80 THEN '优良'
WHEN s_score>=70 THEN '中等'
WHEN s_score>=60 THEN '及格'
ELSE '不及格'
END '成绩',
c_id,s_id
FROM `score` GROUP BY c_id,s_id
查询各个课程各科总数

SELECT SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END ) '优秀',
SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END ) '优良',
SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END ) '中等',
SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END ) '及格',
SUM(CASE WHEN s_score<60 THEN 1 ELSE 0 END ) '不及格',
c_id FROM `score` GROUP BY c_id

计算各科count（）数，用各sum除以count

SELECT (SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END ))/COUNT(c_id) '优秀',
(SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END ))/COUNT(c_id) '优良',
(SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END ))/COUNT(c_id) '中等',
(SUM(CASE WHEN s_score>=60 AND s_score<70 THEN 1 ELSE 0 END ))/COUNT(c_id) '及格',
(SUM(CASE WHEN s_score<60 THEN 1 ELSE 0 END ))/COUNT(c_id) '不及格',
c_id,COUNT(c_id) FROM `score` GROUP BY c_id

和最高分、最低分和平均分sql合并

SELECT MAX(s_score),AVG(s_score),MIN(s_score),
(SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END ))/COUNT(c_id) '优秀',
(SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END ))/COUNT(c_id) '优良',
(SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END ))/COUNT(c_id) '中等',
(SUM(CASE WHEN s_score>=60 AND s_score<70 THEN 1 ELSE 0 END ))/COUNT(c_id) '及格',
(SUM(CASE WHEN s_score<60 THEN 1 ELSE 0 END ))/COUNT(c_id) '不及格',
c_id,COUNT(c_id) FROM `score` GROUP BY c_id

15、按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

解题思路：group by和order by组合使用

第一步查出各科成绩顺序
SELECT
CASE WHEN s_score>=90 THEN '优秀'
WHEN s_score>=80 THEN '优良'
WHEN s_score>=70 THEN '中等'
WHEN s_score>=60 THEN '及格'
ELSE '不及格'
END '成绩',s_score,
c_id
FROM `score` GROUP BY c_id,s_score ORDER BY c_id,s_score DESC

第二步多表的笛卡尔实现交集

SELECT
@i:=@i +1 AS i ,
score.`c_id`,
score.`s_score`,
score.`s_id`
FROM (SELECT @i:=0)s,(SELECT * FROM `score` GROUP BY c_id,s_id ORDER BY c_id,s_score DESC) score;