MySQL：日常刷题（经典50题--部分）

1. MySQL基础:

   • 数据库、表的创建和删除。
   • 数据类型和字段属性（如VARCHAR, INT, PRIMARY KEY等）。

2. 数据插入与更新:

   • 使用INSERT INTO语句添加数据。
   • 使用UPDATE语句修改数据。

3. 数据查询:

   • 基本的SELECT语句。
   • 使用WHERE子句进行条件查询。
   • 使用ORDER BY进行排序。

4. 连接查询（JOIN）:

   • 内连接（INNER JOIN）、左连接（LEFT JOIN）等。
   • 多表查询的实现。

5. 子查询:

   • 在SELECT, INSERT, UPDATE, DELETE语句中使用子查询。

6. 聚合函数:

   • COUNT(), SUM(), AVG(), MAX(), MIN()等。

7. 分组查询:

   • 使用GROUP BY进行数据分组。

8. 窗口函数:

   • ROW_NUMBER(), RANK(), DENSE_RANK()等。

9. HAVING子句:

   • 对分组后的结果进行过滤。

10. 字符串函数:

    • LIKE, CONCAT()

---

CREATE TABLE course (
  c_id varchar(20) NOT NULL,
  c_name varchar(20) NOT NULL DEFAULT '',
  t_id varchar(20) NOT NULL,
  PRIMARY KEY (c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO course (c_id, c_name, t_id) VALUES
    ('01', '语文', '02'),
    ('02', '数学', '01'),
    ('03', '英语', '03');
SELECT * FROM course;

// 
CREATE TABLE IF NOT EXISTS score (
  s_id varchar(20) NOT NULL,
  c_id varchar(20) NOT NULL,
  s_score int DEFAULT NULL,
  PRIMARY KEY (s_id,c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO score (s_id, c_id, s_score) VALUES
    ('01', '01', 80),
    ('01', '02', 90),
    ('01', '03', 99),
    ('02', '01', 70),
    ('02', '02', 60),
    ('02', '03', 80),
    ('03', '01', 80),
    ('03', '02', 80),
    ('03', '03', 80),
    ('04', '01', 50),
    ('04', '02', 30),
    ('04', '03', 20),
    ('05', '01', 76),
    ('05', '02', 87),
    ('06', '01', 31),
    ('06', '03', 34),
    ('07', '02', 89),
    ('07', '03', 98);
SELECT * FROM score;

//    
CREATE TABLE student (
  s_id varchar(20) NOT NULL,
  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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

INSERT INTO student (s_id, s_name, s_birth, s_sex) VALUES
    ('01', '赵雷', '1990-01-01', '男'),
    ('02', '钱电', '1990-12-21', '男'),
    ('03', '孙风', '1990-05-20', '男'),
    ('04', '李云', '1990-08-06', '男'),
    ('05', '周梅', '1991-12-01', '女'),
    ('06', '吴兰', '1992-03-01', '女'),
    ('07', '郑竹', '1989-07-01', '女'),
    ('08', '王菊', '1990-01-20', '女');
SELECT * FROM student;

//    
CREATE TABLE teacher (
  t_id varchar(20) NOT NULL,
  t_name varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;    
    
INSERT INTO teacher (t_id, t_name) VALUES
    ('01', '张三'),
    ('02', '李四'),
    ('03', '王五');    
SELECT * FROM teacher;
    
//进阶题

//1142
SELECT COUNT(*) FROM teacher WHERE t_name LIKE '李%'; 

//1143查询课程编号为”01“的课程比”02“的课程成绩高的所有学生的学号、姓名和成绩
SELECT a.s_id,b.s_name,a.s_score AS '01成绩',b.s_score AS '02成绩' FROM 
(SELECT s_id,s_score FROM score WHERE c_id='01') AS a
INNER JOIN (SELECT student.s_id,student.s_name,s_score FROM score INNER JOIN student ON student.s_id=score.s_id WHERE c_id='02') AS b
ON a.s_id=b.s_id WHERE a.s_score>b.s_score;

//1144查询平均成绩大于60分的学生的学号和平均成绩
SELECT s_id,AVG(s_score) FROM score GROUP BY s_id HAVING AVG(s_score)>60;

//1145（1）查询所有学生的学号、姓名、选课数、总成绩
SELECT st.s_id,st.s_name,COUNT(sc.c_id),
CASE WHEN SUM(sc.s_score) IS NULL THEN 0 ELSE ROUND(SUM(sc.s_score))  END AS '总成绩'    
FROM  student st LEFT JOIN score sc 
ON sc.s_id=st.s_id GROUP BY st.s_id;
// 1145（2）
SELECT a.s_id,a.s_name,COUNT(b.c_id) AS '选课数',IFNULL(SUM(b.s_score),0) AS '总成绩' FROM student AS a 
LEFT JOIN score AS b
ON a.s_id=b.s_id GROUP BY s_id,s_name;

// 1146(1)查询没学过“张三”老师课的学生的学号、姓名
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="张三")));
 1146(2)
SELECT s_id,s_name FROM student WHERE s_id NOT IN(SELECT s_id FROM score WHERE c_id='02');

// 1147(1)查询学过“张三”老师所教的所有课的同学的学号、姓名
SELECT s_id,s_name 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="张三")));
 1147(2)
SELECT s_id,s_name FROM student WHERE s_id IN(SELECT s_id FROM score WHERE c_id='02');

//1149 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
SELECT s_id FROM score WHERE c_id='01';
SELECT s_id FROM score WHERE c_id='02';

SELECT a.s_id,b.s_name FROM (SELECT s_id FROM score WHERE c_id='01') AS a
INNER JOIN (SELECT student.s_id,student.s_name FROM score INNER JOIN student ON student.s_id=score.s_id WHERE c_id='02' GROUP BY s_id) AS b
ON a.s_id=b.s_id;

// 1149 查询课程编号为“02”的总成绩
SELECT c_id,SUM(s_score) FROM score WHERE c_id='02';

// 1150 查询所有的课程成绩都小于60分的学生的学号、姓名
SELECT s_id,s_name FROM student WHERE s_id NOT IN(SELECT s_id FROM score WHERE s_score >= 60);
    
    
//1151 查询没有学全所有课的学生的学号、姓名
SELECT s_id,s_name FROM student WHERE s_id NOT IN(SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id)>2);

//1152 查询至少有一门课与学号为“01”的学生所学课程相同的其它学生的学号和姓名
SELECT c_id FROM score WHERE s_id='01';
SELECT s_id FROM score WHERE c_id IN(SELECT c_id FROM score WHERE s_id='01') GROUP BY s_id;

SELECT a.s_id,a.s_name FROM student AS a
INNER JOIN (SELECT s_id FROM score WHERE c_id IN(SELECT c_id FROM score WHERE s_id='01') AND s_id NOT IN('01') GROUP BY s_id) AS b
ON a.s_id=b.s_id;

//1156
select * from student where s_id in(select s_id from score where s_score<60 and c_id='01' group by s_id ORDER BY s_score desc);

//1163
select
    c.c_id as 课程编号,c.c_name as 课程名称,COUNT(*),
    sum(case when s_score between 85 and 100 then 1 else 0 end) as '[100-85]',
    sum(case when s_score between 70 and 85 then 1 else 0 end) as '[85-70]',
    sum(case when s_score between 60 and 70 then 1 else 0 end) as '[70-60]',
    sum(case when s_score <60 then 1 else 0 end) as '[0-60]'
from course c
inner join score sc
on c.c_id = sc.c_id
group by c.c_id;

1164
SELECT s_id,AVG(s_score),RANK() OVER(ORDER BY AVG(s_score) DESC) AS m_rank FROM score GROUP BY s_id;

//1166
select c.c_id,cnt_student
from course c
inner join (
    select c_id,count(*) as cnt_student
    from score
    group by c_id
) t1
on c.c_id = t1.c_id;

//1171
select s.s_id,s.s_name,avg_score
from student s
inner join (
    select s_id,avg(s_score) as avg_score
    from score
    group by s_id
    having avg_score >= 85
) t1
on s.s_id = t1.s_id;

题号1162 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 ?
SELECT c.c_id,s.s_name,n.s_score,m_rank
FROM (
SELECT *FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) m_rank
FROM score
) m
WHERE m.m_rank between 2 and 3
) n 
JOIN student s ON s.s_id = n.s_id
JOIN course c ON n.c_id = c.c_id;
        
1165
select c.c_id,s.s_id,s_score,rank() over (ORDER BY s_score DESC) AS '排名'
from (
    select *
    from score sc
    where (
        select count(*)
        from score sc1
        where sc.c_id = sc1.c_id
        and sc.s_score < sc1.s_score
    ) < 3
)
t1
inner join student s on t1.s_id = s.s_id 
inner join course c on t1.c_id = c.c_id 
order by c.c_id,s_score desc;

题号1167 查询出只有两门课程的全部学生的学号和姓名
select s_id,s_name
from student
where s_id in (
    select s_id
    from score
    group by s_id
    having count(*) = 2
);

题号1172 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c.c_id,c.c_name,avg_score
from course c
inner join (
    select c_id,avg(s_score) as avg_score
    from score
    group by c_id
) t1
on c.c_id = t1.c_id
order by avg_score desc,c.c_id;

题号1173 查询课程名称为"数学"，且分数低于60的学生姓名和分数。
select s_name,s_score
from student s
inner join (
    select s_id,s_score
    from score sc
    inner join course c on sc.c_id = c.c_id
    where c_name = '数学'
    and s_score < 60
) t1
on s.s_id = t1.s_id;

//1176
select s.s_id,c.c_id,s_score
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 < 60 ORDER BY c_id desc;

题号1177 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
 .s_id,ss.s_name,s_score
from student s
where s_id  
    select s_id
    from score
    where c_id = '03'
    AND s_score > 80
);

题号1187 查询两门以上不及格课程的同学的学号及其平均成绩
select s.s_id,s.s_name,avg(s_score) as avg_score
from student s
inner join (
    select s_id,AVG(s_score)
    from score
    where s_score < 60
    group by s_id
    having count(*) > 2
) t1
on s.s_id = t1.s_id
inner join score sc on s.s_id = sc.s_id
group by s.s_id;

题号1185 查询各学生的年龄（精确到月份） ?

题号1188 查询12月过生日的学生
SELECT s_name FROM student WHERE month(s_birth) = '12';

题号1184 查询选修了全部课程的学生信息
SELECT s_id,COUNT(c_id) FROM score GROUP BY s_id HAVING COUNT(c_id)>2;