docker pull mysql:5.7
docker run --restart=unless-stopped -d -p 3306:3306 --name common-mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7 --lower_case_table_names=1

cancat : 当拼接的字段有 null 的时候，结果都为 null

left(str,x) : 返回从 0 开始的 x 个字符，当 x 为 null，返回 null

CURDATE() : 返回当前日期，只包含年月日 2020-04-27

CURTIME() : 返回当前日期，只包含时分秒 12：12：12

NOW() : 返回当前时间，年-月-日 时：分：秒

>

create table Student(SId varchar(10),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-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

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);

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

SELECT
	s.SId,
	s.Sname,
	temp1.class1,
	temp1.class2
FROM
## 构建出子查询的数据
	(
SELECT
	SId1,
	class1,
	class2
FROM
	( SELECT SId SId1, score class1 FROM SC WHERE CId = '01' ) t1,
	( SELECT SId SId2, score class2 FROM SC WHERE CId = '02' ) t2
WHERE
	t1.SId1 = t2.SId2
	AND t1.class1 > t2.class2
	) temp1
	LEFT JOIN Student s ON s.SId = temp1.SId1

1.1 查询同时存在" 01 “课程和” 02 "课程的情况

SELECT
	s.SId,
	s.Sname,
	temp1.class1,
	temp1.class2
FROM
	(
SELECT
	SId1,
	class1,
	class2
FROM
	( SELECT SId SId1, score class1 FROM SC WHERE CId = '01' ) t1,
	( SELECT SId SId2, score class2 FROM SC WHERE CId = '02' ) t2
WHERE
	t1.SId1 = t2.SId2
	) temp1
	LEFT JOIN Student s ON s.SId = temp1.SId1

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )

SELECT
	s.SId,
	s.Sname,
	temp1.class1,
	temp1.class2
FROM
	(
SELECT
	SId1,
	class1,
	class2
FROM
	( SELECT SId SId1, score class1 FROM SC WHERE CId = '01' ) t1 left join
	( SELECT SId SId2, score class2 FROM SC WHERE CId = '02' ) t2
on
	t1.SId1 = t2.SId2
	) temp1
	LEFT JOIN Student s ON s.SId = temp1.SId1

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

SELECT
	s.SId,
	s.Sname,
	temp1.class1,
	temp1.class2
FROM
	(
SELECT
	SId2,
	class1,
	class2
FROM
	( SELECT SId SId1, score class1 FROM SC WHERE CId = '01' ) t1
	RIGHT JOIN ( SELECT SId SId2, score class2 FROM SC WHERE CId = '02' ) t2 ON t1.SId1 = t2.SId2
	) temp1
	LEFT JOIN Student s ON s.SId = temp1.SId2
WHERE
	temp1.class1 IS NULL

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

SELECT
	s.SId,
	s.Sname,
	t1.avg
FROM
	( SELECT SId sid, SUM( score ) / count( 1 ) avg FROM SC GROUP BY SId ) t1
	LEFT JOIN Student s ON t1.sid = s.SId
WHERE
	t1.avg >= 60;

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

SELECT
	*
FROM
	Student
WHERE
	SId IN ( SELECT SId FROM SC GROUP BY SId );

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

SELECT
	s.SId,
	s.Sname,
	t1.sum,
	t1.num
FROM
	( SELECT SId sid, SUM( score ) sum, count( 1 ) num FROM SC GROUP BY SId ) t1
	RIGHT JOIN Student s ON t1.sid = s.SId;

4.1 查有成绩的学生信息

SELECT
	*
FROM
	Student
WHERE
	SId IN ( SELECT SId FROM SC GROUP BY SId );

5. 查询「李」姓老师的数量

SELECT
	count( 1 )
FROM
	teacher
WHERE
	Tname LIKE '李%';

6. 查询学过「张三」老师授课的同学的信息

SELECT
	*
FROM
	student
WHERE
	SId IN (
SELECT DISTINCT
	SId
FROM
	sc
WHERE
	CId IN ( SELECT DISTINCT CId FROM Course WHERE TId IN ( SELECT DISTINCT TId FROM teacher WHERE Tname = '张三' ) )
	);

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

SELECT
	*
FROM
	(
SELECT
	t.*,
	count( CId ) num
FROM
	student t
	LEFT JOIN sc sc ON t.SId = sc.SId
GROUP BY
	t.SId
	) temp,
	( SELECT count( CId ) count FROM course ) co
WHERE
	temp.num < co.count

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

SELECT
	*
FROM
	Student
WHERE
	SId IN ( SELECT DISTINCT SID FROM sc WHERE CId IN ( SELECT CId FROM sc WHERE SId = '01' ) )

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

SELECT
	*
FROM
	Student
WHERE
	SId IN (
SELECT
	SId
FROM
	(
SELECT
	*,
	count( 1 ) num
FROM
	(
SELECT
	*
FROM
	sc
WHERE
	SId NOT IN ( SELECT SId FROM sc WHERE CId NOT IN ( SELECT CId FROM sc WHERE SId = '01' ) )
	) t1
GROUP BY
	SId
	) tt1
WHERE
	num = ( SELECT count( 1 ) FROM sc WHERE SId = '01' )
	AND SId != '01'
	)

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

SELECT
	*
FROM
	student
WHERE
	SId NOT IN (
SELECT
	SId
FROM
	sc
WHERE
	CId IN ( SELECT CId FROM course WHERE TId IN ( SELECT TId FROM teacher WHERE Tname = "张三" ) )
	)

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT
	st.* ,avg
FROM
	(
SELECT
	SId,
	sum( score ) / count( 1 ) avg
FROM
	sc
WHERE
	SId IN (
SELECT
	SId
FROM
	( SELECT *, count( 1 ) num FROM sc WHERE score < 60 GROUP BY SId ) t1
WHERE
	num >= 2
	)
GROUP BY
	SId
	) tt1
	LEFT JOIN student st ON tt1.SId = st.SId

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

SELECT
	*
FROM
	student
WHERE
	SId IN ( SELECT SId FROM sc WHERE score < 60 AND CId = '01' ORDER BY score DESC )

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

SELECT
	t1.*,
	avg
FROM
	sc t1
	LEFT JOIN ( SELECT SId, sum( score ) / count( 1 ) avg FROM sc GROUP BY SId ) t2 ON t1.sId = t1.SId
ORDER BY
	avg DESC

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

select
sc.CId ,
max(sc.score)as 最高分,
min(sc.score)as 最低分,
AVG(sc.score)as 平均分,
count(*)as 选修人数,
sum(case when sc.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )/count(*)as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end )/count(*)as 优良率,
sum(case when sc.score>=90 then 1 else 0 end )/count(*)as 优秀率
from sc
GROUP BY sc.CId
ORDER BY count(*)DESC, sc.CId ASC

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

SELECT
	( @sn := @sn + 1 ) sn,
	CId,
	SId,
	score
FROM
	sc,
	( SELECT @sn := 0 ) b
ORDER BY
	CId,
	score DESC

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

SELECT
    sc.SId, sc.CId, sc.score, tp.ranks
FROM
    test.sc
    LEFT JOIN
        (SELECT
            SId,
            CId,
            (SELECT
                COUNT(DISTINCT sc2.score) + 1
                FROM
                    test.sc sc2
                WHERE
                    sc1.CId = sc2.CId
                AND sc2.score > sc1.score) ranks
         FROM test.sc sc1) tp
    ON sc.SId = tp.SId AND sc.CId = tp.CId
ORDER BY sc.CId , ranks

16. 询学生的总成绩，并进行排名，总分重复时保留名次空缺

select t1.*,@currank:= @currank+1 as rank
from (select sc.SId, sum(score)
from sc
GROUP BY sc.SId
ORDER BY sum(score) desc) as t1,(select @currank:=0) as t

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SET @crank = 0;
SELECT
	q.sid,
	total,
	@crank := @crank + 1 AS rank
FROM
	( SELECT sc.sid, sum( sc.score ) AS total FROM sc GROUP BY sc.sid ORDER BY total DESC ) q;

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比