mysql50解法与思考过程

解法千千万,我的解法是接近思路的想法,没有优化的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;

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

萧木易

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值