MySQL练习题、面试题、SQL语句训练，涵盖各种聚合函数用法、常见SQL写法逻辑错误和SQL语句优化_fo float(m.d) double(m,d) m must be >=d-CSDN博客

本文链接：https://blog.csdn.net/qq_42713539/article/details/133881690

MySQL练习题

MySQL 在执行 SQL 语句时，通常会按照以下顺序进行：

FROM：指定数据表或子查询来源。
JOIN：将数据表连接起来形成一个虚拟的数据集合。
WHERE：筛选符合条件的数据。
GROUP BY：按照指定的字段对数据进行分组。
HAVING：筛选符合分组条件的数据。
SELECT：选择所需的列或计算所需的信息。
DISTINCT：筛选不重复的数据。
UNION：合并多个 SELECT 语句的结果集。
ORDER BY：按照指定条件对结果集进行排序。
LIMIT：限制结果集返回的数据行数。

需要注意的是，以上步骤中的不同操作不一定都会出现在一个 SQL 查询语句中，具体使用视业务需求而定。此外，MySQL 在执行 SQL 语句时，还会根据查询数据表的大小、索引使用情况、数据库系统硬件配置等情况，进行优化执行，加快 SQL 查询的速度。

题目1

用一条SQL 语句查询出每门课都大于80 分的学生姓名
table name :score;
name course grade
张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100
王五英语 90

-- 建表语句
drop table if exists  `score`;
create table `score` (
`id` int auto_increment,
`name` varchar(255),
`course` varchar(255),
`grade` float,
PRIMARY key (`id`)
);
INSERT into `score` 
values 
(null,'张三','语文',81.0),
(null,'张三','数学',75.0),
(null,'李四','语文',81.0),
(null,'李四','数学',81.0),
(null,'王五','语文',81.0),
(null,'王五','数学',90.0),
(null,'王五','英语',55.0),
(null,'刘二','语文',55.0),
(null,'刘二','英语',55.0),
(null,'王小美','语文',81.0),
(null,'王小美','数学',99.0),
(null,'王小美','英语',59.0)

用一条SQL 语句查询出每门课都大于80 分的学生姓名

select `name`
from `score`
GROUP BY `name`
having min(`grade`) > 80;

现有学生表student如下:
id number name course_number course_name score
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同, 其他都相同的学生冗余信息

-- 建表语句
drop table if exists `student`;
create table `student`(
`id` int PRIMARY key auto_increment,
`number` BIGINT,
`name` varchar(255),
`course_number` BIGINT,
`course_name` varchar(255),
`score` float);
insert into `student` 
values 
(null,2005001,'张三',0001,'数学',69),
(null,2005001,'李四',0001,'数学',89),
(null,2005001,'张三',0001,'数学',69),
(null,2005001,'李四',0001,'数学',89),
(null,2005001,'王五',0001,'数学',89);

删除除了自动编号不同, 其他都相同的学生冗余信息

-- 找出冗余数据：
select * 
from `student`
GROUP BY student.number,student.`name`,student.course_name,student.course_number,student.score
having count(*) > 1;
-- 找出没有冗余数据的记录
select * 
from `student`
GROUP BY student.number,student.`name`,student.course_name,student.course_number,student.score
having COUNT(*) = 1;
-- 找出所有冗余数据
select * 
from `student` 
where student.id not in (
    select `id` 
    from `student` 
    GROUP BY student.course_name, student.course_number, student.`name`, student.number, student.score 
    having count(*) = 1
);
-- 找出所有不含应保留数据的冗余数据（冗余数据并非冗余数据中的第一条数据）
select * 
from `student` 
where student.id not in (
    select min(`id`) 
    from `student` 
    GROUP BY student.course_name, student.course_number, student.`name`, student.number, student.score 
    having count(*) >= 1
);
-- 临时表删除冗余数据
/*
create TEMPORARY table `temp_student`
SELECT min(`id`) as `min_id` from `student` group by student.course_name, student.course_number, student.`name`, student.number, student.score;

delete from `student`
where `id` not in (
select `min_id` from `temp_student`
);

drop TABLE `temp_student`;
*/
-- 删除冗余数据
DELETE FROM `student`
WHERE id NOT IN (
    SELECT id
    FROM (
        SELECT MIN(id) id
        FROM `student`
        GROUP BY number, `name`, course_number, course_name, student.score
    ) t
);

select * from `student`;

一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球队进行比赛，用一条sql 语句显示所有可能的比赛组合

-- 建表语句
drop table if exists `team`;
create table `team`(
`id` int PRIMARY KEY auto_increment,
`name` varchar(255)
);
insert into `team`
VALUES
(null,'a'),
(null,'b'),
(null,'c'),
(null,'d');

select * from `team`;
-- C24六种而不是A24十二种
select concat(a.`name`,' VS ', b.`name`) as VS
from `team` as a, `team` as b
where a.`name` < b.`name`;
-- 或者
select concat(a.`name`,' VS ', b.`name`) as VS
from `team` as a, `team` as b
where a.`name` > b.`name`;
-- !=是错误的
select concat(a.`name`,' VS ', b.`name`) as VS
from `team` as a, `team` as b
where a.`name` != b.`name`;

请用SQL 语句实现：从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。
请注意：TestDB 中有很多科目，都有1~12月份的发生额。
AccID ：科目代码，
Occmonth ：发生额月份，
DebitOccur ：发生额。
数据库名：JcyAudit ，
数据集：Select * from TestDB

-- 建表语句
DROP table if exists `TestDB`;
create table `TestDB`(
`id` int PRIMARY KEY auto_increment,
`AccID` int,
`Occmonth` int,
`DebitOccur` int
);

INSERT into `TestDB` 
VALUES
(null,101,1,90),
(null,101,2,90),
(null,101,3,90),
(null,101,4,90),
(null,101,5,90),
(null,101,6,90),
(null,101,7,90),
(null,101,8,90),
(null,101,9,90),
(null,101,10,90),
(null,101,11,90),
(null,101,12,90),

(null,102,1,80),
(null,102,2,80),
(null,102,3,80),
(null,102,4,80),
(null,102,5,80),
(null,102,6,80),
(null,102,7,80),
(null,102,8,80),
(null,102,9,80),
(null,102,10,80),
(null,102,11,80),
(null,102,12,80),

(null,100,1,100),
(null,100,2,100),
(null,100,3,100),
(null,100,4,100),
(null,100,5,100),
(null,100,6,100),
(null,100,7,100),
(null,100,8,100),
(null,100,9,100),
(null,100,10,100),
(null,100,11,100),
(null,100,12,100),

(null,104,1,104),
(null,104,2,104),
(null,104,3,104),
(null,104,4,104),
(null,104,5,104),
(null,104,6,104),
(null,104,7,104),
(null,104,8,104),
(null,104,9,104),
(null,104,10,104),
(null,104,11,104),
(null,104,12,104);

select * from `TestDB`;

从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。

select a.AccID, min(a.DebitOccur)
from `testdb` as a
where a.AccID != 101
GROUP BY a.AccID
having min(a.DebitOccur) > (select max(testdb.DebitOccur) from `testdb` where testdb.AccID = 101);

怎么把这样一个数据表mount中的数据
year month amount
1 1991 1 1.55
2 1991 2 1.20
3 1991 3 1.30
4 1991 4 1.40
5 1992 1 2.10
6 1992 2 2.20
7 1992 3 2.30
8 1992 4 2.40
查成这样一个结果？
year m1 m2 m3 m4
1991 1.55 1.20 1.30 1.40
1992 2.10 2.20 2.30 2.40

drop table if exists `mount`;
create table `mount` (
`id` int PRIMARY key auto_increment,
`year` char(4),
`month` int,
`amount` float(3,2)
);
insert into `mount` 
values 
(null,1991,1,1.5549),
(null,1991,2,1.2),
(null,1991,3,1.3),
(null,1991,4,1.4),
(null,1992,1,2.1),
(null,1992,2,2.2),
(null,1992,3,2.3),
(null,1992,4,2.4);
select * from `mount`;
-- 直接查出，如果数据有精度问题，比如字段类型为纯float，则会查出预期之外的值
SELECT 
    year, 
    MAX(CASE WHEN month = 1 THEN amount ELSE NULL END) m1,
    MAX(CASE WHEN month = 2 THEN amount ELSE NULL END) m2,
    MAX(CASE WHEN month = 3 THEN amount ELSE NULL END) m3,
    MAX(CASE WHEN month = 4 THEN amount ELSE NULL END) m4
FROM 
    mount 
GROUP BY year;
-- ROUND函数保留小数
SELECT 
    year, 
    Round(MAX(CASE WHEN month = 1 THEN amount ELSE NULL END), 1) m1,
    ROUND(MAX(CASE WHEN month = 2 THEN amount ELSE NULL END), 1) m2,
    ROUND(MAX(CASE WHEN month = 3 THEN amount ELSE NULL END), 1) m3,
    ROUND(MAX(CASE WHEN month = 4 THEN amount ELSE NULL END), 1) m4
FROM 
    mount 
GROUP BY year;
-- 转为decimal并指定小数位
SELECT 
    year, 
    CAST(MAX(CASE WHEN month = 1 THEN amount ELSE NULL END) as DECIMAL(2,1)) m1,
    CAST(MAX(CASE WHEN month = 2 THEN amount ELSE NULL END) as DECIMAL(2,1)) m2,
    CAST(MAX(CASE WHEN month = 3 THEN amount ELSE NULL END) as DECIMAL(2,1)) m3,
    CAST(MAX(CASE WHEN month = 4 THEN amount ELSE NULL END) as DECIMAL(2,1)) m4
FROM 
    mount 
GROUP BY year;
-- 可能会有精度问题
select year, 
    (select amount from mount m where month=1 and m.year=mount.year) as m1,
    (select amount from mount m where month=2 and m.year=mount.year) as m2,
    (select amount from mount m where month=3 and m.year=mount.year) as m3,
    (select amount from mount m where month=4 and m.year=mount.year) as m4
from mount group by year

有表testa，结构如下：
p_ID p_Num s_id
1 10 01
1 12 02
2 8 01
3 11 01
3 8 03
其中：p_ID为产品ID，p_Num为产品库存量，s_id为仓库ID请用SQL语句。
实现将上表中的数据合并，合并后的数据为：
p_ID s1_num s2_num s3_num
1 10 12 0
2 8 0 0
3 11 0 8
其中：s1_num为仓库1的库存量，s2_num为仓库2的库存量，s3_num为仓库3的库存量。如果该产品在某仓库中无库存量，那么就是0代替。

drop table if exists `testa`;
create table `testa` (
`id` int PRIMARY KEY auto_increment,
`p_id` int,
`p_num` int,
`s_id` int
);
insert into `testa`
VALUES
(null,1,10,01),
(null,1,12,02),
(null,2,8,01),
(null,3,11,01),
(null,3,8,03);
select * from `testa`;
-- 错误的
select testa.p_id,
case when testa.s_id = 1 then testa.p_num else 0 end as s1_num,
case when testa.s_id = 2 then testa.p_num else 0 end as s2_num,
case when testa.s_id = 3 then testa.p_num else 0 end as s3_num
from `testa`
GROUP BY testa.p_id;
-- 正确的
select testa.p_id,
SUM(case when testa.s_id = 1 then testa.p_num else 0 end) as s1_num,
SUM(case when testa.s_id = 2 then testa.p_num else 0 end) as s2_num,
SUM(case when testa.s_id = 3 then testa.p_num else 0 end) as s3_num
from `testa`
GROUP BY testa.p_id;

错误原因：在执行 GROUP BY 查询时，如果需要在 SELECT 子句中使用多个列，那么就需要使用聚合函数来计算每个分组的结果，例如使用 COUNT、SUM、AVG 等聚合函数。这是因为 GROUP BY 查询中，数据库需要将分组后的每一组数据进行聚合计算，最终得到每个分组的结果，然后再对结果进行显示或者进行其他操作。

在本题中，需要将不同仓库中的库存数量进行合并，并且对于每个产品 ID 进行分组。在SELECT 子句中使用了 CASE WHEN 来判断是否满足条件，然后对不同的值进行赋值，但是这样得到的还是原表的结果，并没有进行合并。

而使用 SUM 聚合函数，可以将分组中的多个值进行相加，并得到最终的结果。因此，在使用 GROUP BY 子句时，必须在 SELECT 子句中使用聚合函数才能得到正确的结果。

题目2

-- 建表语句
drop table if exists `Student`;
create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
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' , '女');
drop table if exists `SC`;
create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
drop table if exists `Course`;
create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
drop table if exists `Teacher`;
create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

经典练习 50 题
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
6、查询"李"姓老师的数量
7、询学过"张三"老师授课的同学的信息
8、查询没学过"张三"老师授课的同学的信息
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
11、查询没有学全所有课程的同学的信息
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
16、检索"01"课程分数小于60，按分数降序排列的学生信息
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
18、查询各科成绩最高分、最低分和平均分，以如下形式显示：
19、按各科成绩进行排序，并显示排名
20、查询学生的总成绩并进行排名
21、查询不同老师所教不同课程平均分从高到低显示
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
24、查询学生平均成绩及其名次
25、查询各科成绩前三名的记录
26、查询每门课程被选修的学生数
27、查询出只有两门课程的全部学生的学号和姓名
28、查询男生、女生人数
29、查询名字中含有"风"字的学生信息
30、查询同名同性学生名单，并统计同名人数
31、查询1990年出生的学生名单
32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
35、查询所有学生的课程及分数情况
36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数
37、查询课程不及格的学生
38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
39、求每门课程的学生人数
40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
42、查询每门课程成绩最好的前三名
43、统计每门课程的学生选修人数（超过5人的课程才统计）
44、检索至少选修两门课程的学生学号
45、查询选修了全部课程的学生信息
46、查询各学生的年龄(周岁)
47、查询本周过生日的学生
48、查询下周过生日的学生
49、查询本月过生日的学生
50、查询12月份过生日的学生

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

-- 问题1：不能处理null值，如果成绩表中的成绩列中存在NULL值，就会导致查询出现问题。这是因为在进行子表查询时如果sc表中的学生没有某一门课的成绩，则不会被查出到子表中。
select student.*, a.score as 01_score, b.score as 02_score
from `student`,
(select sc.Sid, sc.score from `sc` where sc.Cid = '01') as a,
(select sc.Sid, sc.score from `sc` where sc.Cid = '02') as b
where a.score > b.score and a.Sid = b.Sid and student.Sid = a.Sid;

-- 问题2：不能处理null值，如果成绩表中的成绩列中存在NULL值，就会导致查询出现问题。这是因为在进行比较操作时，如果操作数其中之一是NULL值，那么结果为NULL，而不是True或False，这可能导致查询的结果中不包括那些具有NULL值的行。
select student.*, a.score as 01_score, b.score as 02_score
from `student`
left join `sc` as a on a.Cid = '01' and a.Sid = student.Sid
left join `sc` as b on b.Cid = '02' and b.Sid = student.Sid
where a.score > b.score;
-- 修改1：使用COALESCE函数来将NULL值替换为一个非NULL值，coalesce(value1, value2, ...)返回第一个不为null的值
select student.*, COALESCE(a.score, 0) as 01_score, COALESCE(b.score, 0) as 02_score
from `student`
left join `sc` as a on a.Cid = '01' and a.Sid = student.Sid
left join `sc` as b on b.Cid = '02' and b.Sid = student.Sid
GROUP BY student.Sid
having 01_score > 02_score;
-- 修改2：使用ifnull函数来将NULL值替换为一个非NULL值，ifnull(value1, value2)如果value1为null，则返回value2的值
select student.*, ifnull(a.score, 0) as 01_score, ifnull(b.score, 0) as 02_score
from `student`
left join `sc` as a on a.Sid = student.Sid and a.Cid = '01'
left join `sc` as b on b.Sid = student.Sid and b.Cid = '02'
group by student.Sid
having 01_score > 02_score;

-- 暂无问题，但要注意else后面不能写null
select Sname,
	sum(case when sc.Cid = '01' then sc.score else 0 end) as a,
	sum(case when sc.Cid = '02' then sc.score else 0 end) as b
from sc, student
where sc.Sid = student.Sid
GROUP BY sc.Sid
having a > b;

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

select student.Sid, student.Sname, AVG(sc.score)
from `student`
join `sc` on sc.Sid = student.Sid
GROUP BY student.Sid
having AVG(sc.score) >= 60;

select student.Sid, student.Sname, AVG(sc.score)
from `student`,`sc`
where sc.Sid = student.Sid
GROUP BY student.Sid
having AVG(sc.score) >= 60;

查询在 SC 表存在成绩的学生信息

select student.Sname, student.Sage, student.Ssex
from `student`
where student.Sid in (
    SELECT sc.Sid from `sc` where sc.score is not NULL
);

select student.*
from `student`
right join `sc` on student.Sid = sc.Sid
where sc.score is not NULL
GROUP BY sc.Sid;

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT student.Sid, student.Sname, COUNT(sc.Cid), SUM(sc.score)
from `student`
left join `sc` on sc.Sid = student.Sid
GROUP BY student.Sid;

查有成绩的同学的学生编号、学生姓名、选课总数、所有课程的总成绩、还有各科成绩

select student.Sid, student.Sname, student.Ssex, student.Sage, count(sc.Cid), sum(sc.score), 
sum(CASE when sc.Cid = '01' then sc.score else null end) as course01_score,
sum(CASE when sc.Cid = '02' then sc.score else null end) as course02_score,
sum(CASE when sc.Cid = '03' then sc.score else null end) as course03_score,
sum(CASE when sc.Cid = '04' then sc.score else null end) as course04_score
from `student`
right join `sc` on sc.Sid = student.Sid
GROUP BY sc.Sid;

查询姓李的老师的数量

select count(*)
from `teacher`
where teacher.Tname like '李%';

查询上过“张三”老师的课的学生信息

select student.Sid, student.Sname, student.Ssex, student.Sage
from `student`
join `sc` on sc.Sid = student.Sid
where sc.Cid in (
    select course.Cid 
    from `course` join `teacher` on teacher.Tid = course.Tid 
    where teacher.Tname = '张三'
);

select student.Sid, student.Sname, student.Ssex, student.Sage
from `student`
join `sc` on sc.Sid = student.Sid
where EXISTS (
    select course.Cid 
    from `course` 
    join `teacher` on teacher.Tid = course.Tid 
    where teacher.Tname = '张三' 
    and sc.Cid = course.Cid
);

select student.Sid, student.Sname, student.Ssex, student.Sage
from `student`,`sc`,`course`
where sc.Sid = student.Sid 
and sc.Cid = course.Cid 
and course.Tid = (
    select teacher.Tid 
    from `teacher` 
    where teacher.Tname = '张三'
);

select student.*
from `student`
join `sc` on sc.Sid = student.Sid
join `course` on course.Cid = sc.Cid
join `teacher` on teacher.Tid = course.Tid
where teacher.Tname = '张三';

select student.*
from student
where Sid in (
	select sc.Sid
	from sc
	join course on course.Cid = sc.Cid
	join teacher on course.Tid = teacher.Tid
	where teacher.Tname = '张三'
);

MySQL 中的 EXISTS 是一个逻辑运算符，可以用于判断子查询是否返回结果，并返回 true 或 false。具体来说，EXISTS 用于测试一个子查询返回行的存在性。当子查询返回至少一行时，EXISTS 返回 true，否则返回 false。

在 SQL 查询中，EXISTS 通常用于 WHERE 子句或 HAVING 子句中，用于过滤不需要的数据行。例如，在一个 SELECT 语句中使用 EXISTS 时，会使查询引擎仅考虑满足条件的行，而不考虑非必要条件。这有助于优化查询性能和减少查询时间。

在使用 EXISTS 时需要注意以下几点：

子查询必须放在括号中，并作为 EXISTS 运算符的参数；
子查询最好使用 SELECT 1 FROM … 或 SELECT * FROM … 的形式；
当子查询返回的结果集非常大时，使用 EXISTS 会更加高效，因为 EXISTS 可以在子查询返回第一行后就停止执行；

综合来看，EXISTS 在 SQL 查询中起到了优化查询性能和减少查询时间的作用，能够提高查询效率，特别是在子查询返回大量数据时。

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

select student.*, count(sc.Cid)
from `student`
LEFT join `sc` on sc.Sid = student.Sid
GROUP BY student.Sid
HAVING count(sc.Cid) < (select count(course.Cid) from `course`);

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

select student.Sid, student.Sname, student.Ssex, student.Sage
from `student`,`sc`
where student.Sid = sc.Sid
GROUP BY sc.Sid
having GROUP_CONCAT(sc.Cid) = (
    select group_concat(sc.Cid)
    from `sc`
    where sc.Sid = '01'
    GROUP BY sc.Sid
);

SELECT student.*
from `student`
join `sc` on sc.Sid = student.Sid
GROUP BY student.Sid
HAVING GROUP_CONCAT(sc.Cid) = (
    select GROUP_CONCAT(sc.Cid) 
    from `sc` 
    where sc.Sid = '01' 
    GROUP BY sc.Sid
);

查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT student.Sname
FROM `student`
where student.Sid not in (
    select student.Sid
	from `student`
	left join `sc` on student.Sid = sc.Sid
	where sc.Cid in (
        select course.Cid 
        from `course` 
        where course.Tid in (
            select teacher.Tid 
            from `teacher` 
            where teacher.Tname = '张三'
        )
    )
GROUP BY student.Sid
);

SELECT Sname FROM Student WHERE Sid NOT IN(
    SELECT Sid FROM SC WHERE Cid IN(
        SELECT Cid FROM Course WHERE Tid = (
            SELECT Tid FROM Teacher WHERE Tname = '张三'
        )
    )
);

查询两门及其以上不及格课程的同学的学号，姓名及其所有课程（包含不及格成绩和及格成绩）的平均成绩

select student.*, avg(sc.score), count(sc.Cid)
from `student`
RIGHT JOIN `sc` on sc.Sid = student.Sid
where sc.Sid in (
    select sc.Sid
	from `sc`
	where sc.score < 60
	GROUP BY sc.Sid
	HAVING COUNT(sc.Cid) >= 2
)
GROUP BY student.Sid;

检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select student.Sid, student.Sname, student.Ssex, student.Sage, sc.score, sc.Cid
from `student`, `sc`
where student.Sid = sc.Sid and sc.Cid = '01' and sc.score < 60
GROUP BY sc.Sid
ORDER BY sc.score desc;

select student.*, sc.Cid, sc.score
from `student`
join `sc` on sc.Sid = student.Sid
where sc.Cid = '01' and sc.score < 60
GROUP BY sc.Sid
ORDER BY sc.score desc;

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select student.*, 
sum(case when sc.Cid = '01' then sc.score else 0 end) as course_01_score,
sum(case when sc.Cid = '02' then sc.score else 0 end) as course_02_score,
sum(case when sc.Cid = '03' then sc.score else 0 end) as course_03_score,
IF(AVG(sc.score) is NULL,0,avg(sc.score)) as 平均分
from `student`
left join `sc` on sc.Sid = student.Sid
GROUP BY sc.Sid
ORDER BY avg(sc.score) desc;

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

select course.Cid, course.Cname, count(sc.Cid) as 选修人数, max(sc.score), min(sc.score), avg(sc.score),
sum(case when sc.score > 60 then 1 else 0 end) / count(sc.Cid) as 及格率,
sum(case when sc.score >= 70 and sc.score < 80 then 1 else 0 end) / count(sc.Cid) as 中等率,
sum(case when sc.score >= 80 and sc.score < 90 then 1 else 0 end) / count(sc.Cid) as 优良率,
sum(case when sc.score >= 90 then 1 else 0 end) / count(sc.Cid) as 优秀率
from `sc`
RIGHT JOIN `course` on sc.Cid = course.Cid
GROUP BY course.Cid;

按平均成绩进行排序，显示总排名和各科排名，Score 重复时保留名次空缺

SELECT student.Sid, student.Sname, student.Ssex,
sum(case when sc.Cid = '01' then (select count(*) from sc as a where a.score > sc.score and a.Cid = sc.Cid) + 1 else null end) as rank_01,
sum(case when sc.Cid = '02' then (select count(*) from sc as a where a.score > sc.score and a.Cid = sc.Cid) + 1 else null end) as rank_02,
sum(case when sc.Cid = '03' then (select count(*) from sc as a where a.score > sc.score and a.Cid = sc.Cid) + 1 else null end) as rank_03,
sum(case when sc.Cid = '04' then (select count(*) from sc as a where a.score > sc.score and a.Cid = sc.Cid) + 1 else null end) as rank_04,
(SELECT COUNT(*) + 1 FROM (SELECT AVG(score) avg_score FROM sc GROUP BY Sid) a WHERE a.avg_score > AVG(sc.score)) as rank_total
from `student` 
LEFT JOIN `sc` on student.Sid = sc.Sid
GROUP BY student.Sid
ORDER BY rank_total;

select student.Sid, student.Sname, student.Ssex, rank_01, rank_02, rank_03, rank_04, rank_total
from `student`
left join (select sc.Sid as Sid, rank() over(order by sc.score desc) as rank_01 from `sc` where sc.Cid = '01') as sc1 on sc1.Sid = student.Sid
left join (select sc.Sid as Sid, rank() over(order by sc.score desc) as rank_02 from `sc` where sc.Cid = '02') as sc2 on sc2.Sid = student.Sid
left join (select sc.Sid as Sid, rank() over(order by sc.score desc) as rank_03 from `sc` where sc.Cid = '03') as sc3 on sc3.Sid = student.Sid
left join (select sc.Sid as Sid, rank() over(order by sc.score desc) as rank_04 from `sc` where sc.Cid = '04') as sc4 on sc4.Sid = student.Sid
left join (select sc.Sid as Sid, rank() over(ORDER BY avg(sc.score) desc) as rank_total from `sc` GROUP BY sc.Sid) as sc5 on sc5.Sid = student.Sid
GROUP BY student.Sid
ORDER BY rank_total;

select s.*, rank_01, rank_02, rank_03, rank_04, rank_total
from student s
left join (select sid, rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, rank() over(partition by cid order by score desc) as rank_04 from sc where cid=04) E on s.sid=E.sid
left join (select sid, rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc;

按平均成绩进行排序，显示总排名和各科排名，Score 重复时合并名次

select s.*, rank_01, rank_02, rank_03, rank_04, rank_total
from student s
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_01 from sc where cid=01) A on s.sid=A.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_02 from sc where cid=02) B on s.sid=B.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_03 from sc where cid=03) C on s.sid=C.sid
left join (select sid, dense_rank() over(partition by cid order by score desc) as rank_04 from sc where cid=04) E on s.sid=E.sid
left join (select sid, dense_rank() over(order by avg(score) desc) as rank_total from sc group by sid) D on s.sid=D.sid
order by rank_total asc;

select student.Sid, student.Sname, student.Ssex, rank_01, rank_02, rank_03, rank_04, rank_total
from `student`
left join (select sc.Sid as Sid, dense_rank() over(order by sc.score desc) as rank_01 from `sc` where sc.Cid = '01') as sc1 on sc1.Sid = student.Sid
left join (select sc.Sid as Sid, dense_rank() over(order by sc.score desc) as rank_02 from `sc` where sc.Cid = '02') as sc2 on sc2.Sid = student.Sid
left join (select sc.Sid as Sid, dense_rank() over(order by sc.score desc) as rank_03 from `sc` where sc.Cid = '03') as sc3 on sc3.Sid = student.Sid
left join (select sc.Sid as Sid, dense_rank() over(order by sc.score desc) as rank_04 from `sc` where sc.Cid = '04') as sc4 on sc4.Sid = student.Sid
left join (select sc.Sid as Sid, dense_rank() over(ORDER BY avg(sc.score) desc) as rank_total from `sc` GROUP BY sc.Sid) as sc5 on sc5.Sid = student.Sid
GROUP BY student.Sid
ORDER BY rank_total;

查询各科成绩前三名的记录

select a.Cid, student.Sname, a.score
from (
    SELECT sc1.Cid, sc1.Sid, sc1.score, (
        select count(DISTINCT sc2.score) 
        from sc as sc2 
        where sc2.score >= sc1.score and sc2.Cid = sc1.Cid) as ranknum
	from sc as sc1
) as a
join student on a.Sid = student.Sid
where a.ranknum <= 3
ORDER BY a.Cid, a.score desc;

SELECT sc.Cid, student.Sname, sc.score 
FROM (
    SELECT Cid, Sid, score,
    	IF(@prev_cid=Cid, @rank:=@rank+1, @rank:=1) AS ranknum, 
		@prev_cid:=Cid
    FROM sc
JOIN (SELECT @prev_cid:=NULL, @rank:=0) AS r
    ORDER BY Cid, score DESC
) AS sc
JOIN student ON sc.Sid = student.Sid
WHERE sc.ranknum <= 3;

查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select student.*, group_concat(a.Cid), group_concat(b.Cid)
from student
join sc as a on a.Sid = student.Sid and a.Cid = '01'
join sc as b on b.Sid = student.Sid and b.Cid = '02'
GROUP BY student.Sid;

问题

为什么MySQL数据库中数据类型float的字段存入的数值为1.1，查出来却是1.100000023841858

这是由于 float 数据类型在计算机内部的存储方式和精度问题导致的。在计算机内部，float 类型是采用二进制形式存储的，能够保证存储范围较大，但有时会存在精度丢失的问题。

在 MySQL 中，float 类型存储的是单精度浮点数，占用 4 个字节，能够表示大约 7 位有效数字。而浮点数存储在计算机的二进制中时，会受到计算机的存储结构和精度限制。因此，某些数值在被存储为二进制表达时会存在精度误差，例如在本例中，1.1 在二进制中的精度可能会有所丢失，所以查出来的结果会跟存入的值略有不同。

如果需要在 MySQL 中存储精度比较高的数值，可以考虑使用 decimal 数据类型，该数据类型可以精确存储浮点数，但是会占用更多的存储空间。

float可以使用round(n,m)来指定保留几位小数，n代表数字，m代表保留几位小数。

DECIMAL数据类型

MySQL中的DECIMAL数据类型是一种精确数字类型，可以可以存储较大范围的数值，并进行精确计算，例如对于货币计算等场景非常适用。

DECIMAL 存储的数值精度是由用户定义的，可以通过指定 DECIMAL(n, m) 的方式来指定其精度，其中 n 是 DECIMAL 的总位数，m 是小数部分的位数。

如果定义时不指定n和m，则默认是整型；

如果需要浮点数可以考虑使用 float 数据类型，如果需要精确计算可以考虑使用 decimal 数据类型，并在查询时使用 CAST 函数将其转换为指定的数据类型，例如：

SELECT CAST(column_name AS DECIMAL(10,2)) FROM table_name;

注意float转为decimal类型时，会有精度缺失问题，例如：float1.1转为decimal(1,1)时，1.1会被转换为0.9

如果要避免该问题，则至少应该转化为decimal(2,1)；

为避免这类问题出现，应该在定义表时就把字段定义为decimal类型；

float(M,D)、double(M,D)、decimal(M,D)必须M >= D

对于 MySQL 中定义的 float(M, D), double(M, D), decimal(M, D) 这些数据类型来说，它们分别表示浮点数、双精度浮点数和十进制数类型，其中 M 表示该数字的总长度（即整数位和小数位的位数之和），D 表示该数字的小数部分的长度（即小数点后的位数）。需要注意的是，对于 DECIMAL(M, D) 类型来说，必须满足 M >= D。

这是由于十进制数类型 DECIMAL 采用了基于定点数的存储方式，在存储与计算过程中保持高精度，因此 M 必须大于或等于 D 才能保证所有的位数都能存储并进行精确计算。如果 M 小于 D 的话，则意味着小数位数大于总位数，这样就无法对所有的位数进行存储和计算，会导致数据的精度丢失。

对于 FLOAT(M,D) 和 DOUBLE(M,D) 数据类型来说，虽然它们是使用浮点数的存储方式，但是仍然遵循 M >= D 的原则。这是因为 FLOAT 和 DOUBLE 数据类型采用 IEEE 754 标准来存储和计算浮点数，而这个标准要求 M 必须大于或等于 D。如果不满足这个条件，也会导致存储和计算过程中出现精度丢失。

在使用(M,D)时，M应该比实际插入的数据的整位数至少大一位，D没有要求，如果插入的数据位数比指定位数大，则指定位数最后一位会按照后面的一位遵循四舍五入的原则；比如：decimal(2,2)插入数据1.555，实际插入数据：1.56；插入数据：1.5549，实际插入数据：1.55；只看指定位数的后面那一位数字来进行四舍五入。

MySQL语句中的rank()和dense_rank()有什么区别?

在 MySQL 中，rank() 和 dense_rank() 都是用来计算排名的窗口函数。

rank() 和 dense_rank() 的区别在于如何处理并列排名。具体来说:

rank() 函数会跳过并列排名并继续累加排名数。例如，如果有两个学生得分都是第一，那么紧接着的排名是第三（而不是第二）。
dense_rank() 函数会将并列的排名都计算在内，不跳过。例如，如果有两个学生得分都是第一，那么紧接着的排名是第二（而不是第三）。

下面是一个示例来演示这两个函数的区别：

假设有如下学生成绩表：

Sid	Sname	Score
1	A	80
2	B	90
3	C	80
4	D	70

使用 rank() 和 dense_rank() 函数分别计算每个学生的排名：

SELECT Sid, Sname, Score, 
       RANK() OVER (ORDER BY Score DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY Score DESC) AS dense_rank
FROM student;

输出结果如下：

Sid	Sname	Score	rank	dense_rank
2	B	90	1	1
1	A	80	2	2
3	C	80	2	2
4	D	70	4	3

可以看到，使用 rank() 函数，第二名和第三名同样的分数，共同排名第二，但是D同学70分，排名直接是第四；

而使用了dense_rank()函数，D同学是第三名；

`rank()` 函数不能在`sum()` 函数中使用，会导致语法错误。正确的写法是先使用`rank()` 函数生成排名，之后再进行汇总（通过加上`sum()` 函数和`group by` 子句）。

MySQL中的null值

在比较时null值的特性

在MySQL语句中，如果比较时有一个值是NULL，那么会遇到以下问题：

NULL值不能与任何其他值进行比较。因此，任何与NULL进行比较的结果都是未知的（Unknown）。例如，NULL = 1与NULL != 1都是未知的。
算术运算符（+、-、*、/）与NULL相结合时，其结果也是未知的。
如果使用IS NULL或IS NOT NULL运算符来比较NULL值，那么结果将会是真（True）或假（False）。

为了避免这些问题，可以使用COALESCE函数将NULL值替换成一个常数，例如：

SELECT COALESCE(column1, 0) FROM my_table;

这个语句将会返回column1的值，如果它是NULL，那么将会返回0。这样可以确保比较结果正确，避免Unexpected的结果。

null的特性

MySQL中的NULL值具有以下特性：

NULL表示无值或未知值，与0或空字符串不同。因此，NULL是存在的值，与空不同。
在MySQL中，可以将NULL值与所有数据类型的值进行比较，但任何与NULL进行比较的结果都是未知的（Unknown）。
在任何使用数据类型的地方，都可以使用NULL值。例如，在CREATE TABLE语句中指定列时，可以使用NULL作为默认值。
MySQL中的聚合函数（如AVG、SUM、MAX和MIN）可以使用NULL值，并将其忽略。
在NULL值与非NULL值之间进行操作（如算术运算或字符串连接）时，结果为NULL。

然而，NULL值也可能会导致一些问题，例如：

如果在不正确的情况下使用NULL值或比较NULL值，可能会导致意想不到的结果。例如，NULL值与任何值进行比较的结果都是未知的，因此无法预测。
如果在使用NULL值时不小心，可能会导致数据不正确或无法使用。例如，在指定列时使用NULL值作为默认值，如果不注意，可能会导致数据的错误。
使用包含NULL值的索引时，可能需要特殊处理，因为NULL值可能不能参与索引查找。

因此，在MySQL中使用NULL值需要非常小心，需要注意数据处理的每一个环节，并且需要了解其行为和限制。

join on的类型

MySQL中的Join On类型有以下几种：

Inner Join On：返回两个表中匹配行的记录集合，即只返回两个表中都有匹配的行。默认join on等同于inner join on。
Left Join On：左连接是使用左侧表的所有记录和右侧表的匹配记录组成的。如果没有匹配，则相应的右侧表的列为NULL。
Right Join On：右连接是使用右侧表的所有记录和左侧表的匹配记录组成的。如果没有匹配，则相应的左侧表的列为NULL。
Full Outer Join On：全外连接返回所有的行，包括左边表和右边表中没有匹配的行，将这些没有匹配行上的列设置为NULL。MySQL中不支持Full outer join on，可以利用union来实现同样的效果，例如：

select * from student left join sc on sc.Sid = student.Sid
union
select * from student right join sc on sc.Sid = student.Sid;

Cross Join On：笛卡尔积。交叉连接返回两个表中所有可能的行组合。没有使用On字句的交叉连接将返回左侧表中的每一个行和右侧表中的每一个行组合。
Natural Join On：自然连接确定具有相同值的列，并自动匹配它们的所有匹配行。这是不带On子句的等值连接的变体。例如：

select *
from `student`
NATURAL join `sc`;
-- 和
select *
from `student`
join `sc` on sc.Sid = student.Sid;
-- 两个结果基本类似，只是自然连接将连接条件列合并

自然连接的缺点/隐患：

自然连接返回的结果集中可能包含重复的列，需要通过别名或者投影操作来消除这些重复。
自然连接只能匹配两张表中列名相同的列，不利于多表连接的情况。
自然连接可能在匹配时错误地将两个不相关的表连接起来。
自然连接可能会降低查询性能，因为它需要对所有的列名进行匹配操作。