MYSQL基础训练加强

源码来源-----链接

四个表：

student:

teacher:

course:

sc(score):

1，如何统计同一字段相同数据数量：

select sid,count(*) as count from sc group by sid;

group by

group by的作用是将同一个表中指定的字段进行分组，字段值相同的分为一个组，相当于把字段值相同的行的数据都拿出来形成新表，然后可以对最高表进行操作，如统计数量等。

2，查询"01"课程比"02"课程成绩高的学生的信息及课程分数：

解题的关键---同一个字段相同数据就是新表的连接条件

子查询的嵌套

临时表的生成

筛选条件：01课程，02课程

select stu.*,t3.score from student stu
join(
    select t1.sid,t1.score from (
        select sid,score from sc where cid='01') as t1
	join (
        select sid,score from sc where cid='02') as  t2 
	on t1.sid=t2.sid where t1.score>t2.score 
) as t3
on stu.sid=t3.sid;

3，查询学生选课存在" 01 "课程但可能不存在" 02 "课程的情况（不存在时显示为 null）。

有01的全显示，，02存在显示，不存在显示NULL，那肯定是连接查询。

筛选条件：01课程，02课程。

select * from
(select * from sc where cid= "01") as t1
left join
(select * from sc where cid= "02") as t2
ON t1.sid=t2.sid;

4，查询可能没有选择02课程的学生。

select s.* from student s
left join
(select sid from sc where cid='02') as t
on s.sid=t.sid where t.sid is null;

连接查询

连接查询的返回值：

返回与连接条件相匹配的两个表或多个表中的数据。

注意：不要把连接查询的返回数据和你自己选择输出的数据搞一起了，连接查询返回与连接条件相匹配的两个表或多个表中的数据，而第一个select之后的字段是你对连接查询返回的结果进行的选择输出。

select * from student s
join sc on sc.sid=s.sid;

连接的条件

等值连接返回两个表中这两个字段相等的数据

非等值连接：

指定一个范围---between...and，<，>

连接条件可以是两个表不相等的字段吗

不相等的字段，不就是为了查询非交集的部分吗。

一个表的一个字段不可能只有一个值，使用不等于，虽然不会报错，但是矛盾。

连接条件不要用不等于

连接条件无连接：

如果连接条件中不涉及两个表的连接条件，那么返回满足每个表指定的条件的数据，没有指定条件的表，全返回。

select * from student s
join sc on sc.sid>3
where s.sid>4;

 

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

select s.sid,s.sname,av from student s
join 
(select sid,avg(score) as av from sc group by sid) as t
on s.sid=t.sid
where av>=60;

#多表联合查询
SELECT  sc.sid,student.sname,avg(sc.score) FROM sc ,student
WHERE sc.sid = student.sid  GROUP BY  sc.sid  HAVING avg(sc.score) >= 60;
#多表连接查询
SELECT  sc.sid,student.sname,avg(sc.score) FROM sc 
JOIN student on sc.sid = student.sid  GROUP BY  sc.sid  HAVING avg(sc.score) >= 60;

#三种方法

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

#sql92
select * from student,sc where student.sid=sc.sid group by sc.sid;

#sql99
select * from student s
join sc
on s.sid=sc.sid group by sc.sid;

7，查询所有同学的学生编号、学生姓名、选课总数、所有课程的成绩总和.

#sql92
select st.sid,st.sname,count(*) all_c,sum(score) sum from student st,sc where st.sid=sc.sid group by sc.sid;
#sql99
select st.sid,st.sname,选课总数,总成绩 from student st
join 
(select sid,count(*) 选课总数,sum(score) 总成绩 from sc group by sid) as t
on st.sid=t.sid;

注意：如果创建临时表，那么用于连接的字段必须select出存在于临时表中，不存在怎么比较呢。

8，查询「李」姓老师的数量

#sql92
select count(*) from teacher where tname like '李%';

多表连接查询

从外层表向里层，层层推进

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

#tid--cid--sid-->
select st.* from student st
join 
( 
  select sc.sid from sc
  join
  (
    select co.cid from course co
    join teacher te
    on te.tid=co.tid 
    where te.tname='张三'
  ) cd on cd.cid=sc.cid
) as sd
on sd.sid=st.sid;

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

没有连接但是要用两个表的数据查询------子查询

为什么from 之后的别名用不了---不是用不了，而是我语法上是要进行连接查询，但是没有连接条件

#1
select st.* from student st
join
(
  select sid from 
  (
    select count(sc.sid) as n_course,sid from sc
    group by sc.sid having n_course<(select count(*) from course)
  ) as d
) as s
on s.sid=st.sid;

#2
select st.* from student st
join
(
  select sid from 
  (
    select count(sc.sid) as n_course,sid from sc,course as c
    group by sc.sid having n_course< count(c.cid)
  ) as d
) as s
on s.sid=st.sid;

第二种情况说明我想利用from之后的表进行连接查询
但是我并没有建立连接，因为不需要，我只是想利用这两个表的字段进行比较。但是却用来sql92的连接查询的方法，而没有连接条件，产生错误。

注意：from之后的表不是告诉系统：你要查询使用的表，而是要进行连接查询。
如果不需要连接查询，但是涉及多个表，那么用子查询 

以上第一种方法弊端：

一节课也没选的查询不出来

SELECT a.*,count(b.cid) AS 所学课程数
FROM student AS a
    left JOIN sc AS b
    ON a.sid = b.sid
        GROUP BY b.sid
            HAVING COUNT(b.cid)< (SELECT COUNT(c.cid) FROM course as c);

这个方法可以正确查询出结果，但是做查询不应该查询出所有主表的数据吗，为什么选了课，但是没选全的会过滤掉呢。

注意：千万注意，什么是独有：

就是A表中有这个数据，而B表中没有，连参与连接匹配都没有参与匹配。

比如：学号为1的学生在student表中，也在score表中，当连接匹配时，这个学生就不是student表中或者score表中独有的，这个学生不满足匹配条件时，就会被过滤，左查询，有查询都不会查询出。

而学号为2的学生只在sudent表中，不在score表中，这种才叫独有。

比较以下代码：

#查询没有学全所有课程的同学的信息
select st.*,n_course from student st
left join
(
    select count(sc.sid) as n_course,sid from sc
    group by sc.sid having n_course<(select count(*) from course)
) as s
on s.sid=st.sid;




SELECT a.*,count(b.cid) AS 所学课程数 FROM student AS a
left JOIN sc AS b
ON a.sid = b.sid
GROUP BY b.sid
HAVING COUNT(b.cid)< (SELECT COUNT(c.cid) FROM course as c);
            

两个代码的区别：

1：s临时表中只有几个被筛选出来的数据和student表中的所有数据连接匹配，所以student表中的所有都会输出。(student表中的不匹配的项都是独有的)

2：第二种情况只有8号学生才是student表的独有。

子查询和连接查询

子查询和连接查询都是利用表之间的关系进行查询

注意连接查询一定要有连接条件，子查询不需要连接条件，不需要在两个表之间建立关系。

使用时注意选择。

where的两不能

不能用select里的别名

不能用聚合函数

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

#查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
#先筛选，再进行匹配
select st.* from student st
join
(
  select sc2.sid from
  (
    select cid from sc where sid='01'
  ) as cs
  join sc sc2 on sc2.cid=cs.cid group by sc2.sid
) as sd
on sd.sid=st.sid
where st.sid!='01';

#先找出学号相等的，再筛选
SELECT b.* FROM student AS b
    JOIN sc AS a 
    ON b.sid  = a.sid   
        WHERE a.cid in 
                    (SELECT a.cid FROM sc AS a WHERE a.sid = '01') 
        GROUP BY b.sid 
             HAVING b.sid != '01';

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

难点：找所有学课一 一对应的学生的学号。

MYSQL语句有没有办法异步实现比较两个字段所含的值是否完全相同。

#先筛选，再匹配
select st.* from student st
join 
(
  select sc.sid from sc
  join
  (
    select cid from sc where sid='01'
  ) as tem2 on tem2.cid=sc.cid group by sc.sid having count(sc.cid)=(select count(cid) from sc where sid='01')
) as tem1
on tem1.sid=st.sid
where st.sid <> '01';

#select * from sc;
#先匹配再筛选
select st.* from student st
join sc
on st.sid=sc.sid
where st.sid<>'01'
group by sc.sid
having count(sc.cid)=(select count(cid) from sc where sid='01');

SQL语句的执行原理和顺序

写SQL语句不要按你所想的执行顺序去写，而是要按SQL语法的执行顺序和原理去写。

SELECT `name`,COUNT(`name`) AS num FROM student 
WHERE grade < 60 GROUP BY `name` 
HAVING num >= 2 ORDER BY num DESC,`name` ASC LIMIT 0,2;

分析以上语句的执行顺序和原理：

sql语句先执行的是 FROM student 负责把数据库的表文件加载到内存中去，因为表是存储在磁盘上的。

1、FROM  table1 先执行的是 FROM 负责把数据库from之后的表文件加载到内存中去，因为表是存储在磁盘上的。

2、JOIN table2  把数据库join之后的表文件加载到内存中去，两个表建立连接，但是此时没有连接条件，产生笛卡尔积现象---m*n,生成临时表temp1。

3、ON table1.column = table2.columu 确定表的绑定条件 ,对temp1进行筛选，产生临时表Temp2

4、WHERE  对临时表Temp2产生的结果进行过滤  产生临时表Temp3

5、GROUP BY 对临时表Temp3进行分组，产生一个内部被分组的临时表Temp4

(注意：这里不要理解为分成多个临时表)

6、HAVING  对分组后的临时表temp4进行再次筛选， 产生临时表Temp5

7、SELECT  对中间表Temp5进行列筛选，产生临时表 Temp6

8、DISTINCT 对临时表 Temp6进行去重，产生临时表 Temp7

9、ORDER BY 对Temp7中的数据进行排序，产生临时表Temp8

10、LIMIT 对临时表Temp8进行分页，产生临时表Temp9

having的使用限制

having不可以用两个聚合函数的结果进行比较，可以用聚合函数和常量值，或者用聚合函数和子查询。

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

select st.sname from student st
where st.sid not in(
  select sc.sid from sc
  join course tem1 on tem1.cid=sc.cid
  join teacher te on tem1.tid=te.tid
  where tname='张三'
  group by sc.sid
);

不要用张三所没有受的课程的编号去匹配学生学号，这样，匹配出来的学生就包括学了张三的和没学张三的。

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

#查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
#先筛选再链接
select st.sname,st.sid from student st
join
(
    select sc.sid from sc where score<60 group by sc.sid having count(sc.sid)>=2
) as tem1 on tem1.sid=st.sid;


#先链接再筛选
select st.sname,st.sid from student st
join sc on sc.sid=st.sid
where score<60
group by sc.sid
having count(sc.cid)>=2;

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

#查询" 01 "课程分数小于 60，按分数降序排列的学生信息
#先链接再筛选
select st.*,sc.score from student st 
join sc on sc.sid=st.sid
where sc.cid='01' and sc.score<60 order by sc.score desc;


#先筛选再链接
select st.*,tem1.score from student st
join
(
  select sc.sid,sc.score from sc where sc.cid='01' and sc.score<60
) as tem1
on tem1.sid=st.sid
order by tem1.score desc;

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

select sc.score,tem1.av as 平均成绩 from sc
join 
(
  select avg(sc.score) as av,sc.sid from sc group by sc.sid
) as tem1 on tem1.sid=sc.sid
order by 平均成绩;

from之后的别名使用限制

group by之后都不可以使用

但是有解决的办法，就是把别名种要使用的字段在select种设置一个别名，这样就可以用了。 

17，查询各科成绩最高分、最低分和平均分。

select min(sc.score) as min_grade,max(sc.score) as max_grade,avg(sc.score) as avg_grade
from sc group by sc.cid; 

18，17以如下情况显示：

       课程 id，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
　　（及格为>=60，中等为：[70,80)，优良为：[80-90)，优秀为：>=90
　　要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序）

分组之后分段统计

#查询各科成绩最高分、最低分和平均分
#及格为>=60，中等为：[70,80)，优良为：[80-90)，优秀为：>=90
select sc.cid,min(sc.score) as min_grade,max(sc.score) as max_grade,avg(sc.score) as avg_grade,
sum(case when score>=60 then 1 else 0 end)/count(sc.sid) as 及格率,
sum(case when score>=70 and score<80 then 1 else 0 end)/count(sc.sid) as 中等率,
sum(case when score>=80 and score<90 then 1 else 0 end)/count(sc.sid) as 优良率,
sum(case when score>=90 then 1 else 0 end)/count(sc.sid) as 优秀率
from sc group by sc.cid; 

sum(case when score>=60 then 1 else 0 end)分析：

因为是以学课分组的，就像min,max这些函数，会遍历组里的每一个score,当遍历完成这个score得到一个结构----1或者0，保留，因为要求为总和，所以会把所有遍历之后的结构加起来。

所有的函数都是循环遍历表或者组中的字段，只要指定字段，一定会遍历完表或者组中的字段，并进行指定的操作。

不要看到复杂就搞不懂了

循环结构和分支结构的使用限制

这些结构虽然不能单独使用，但是也不是只能在begin...end种使用，可以嵌套在其他语句种使用。

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

set @str_cid=' ';
set @rank=0;
select score,(case when cid=@str_cid then @rank:=@rank+1 else @rank:=1 end) as rank,@str_cid:=cid
from 
(
  select * from sc order by cid,score desc
) as tem;

select score,(case when cid=@str_cid then @rank:=@rank+1 else @rank:=1 end) as rank,@str_cid:=cid
from 
(
  select * from sc order by cid,score desc
) as tem,
(
  select @str_cid='',@rank=0
) as tem1;

1）select sc.* from sc group by cid order by score desc;

对这个语句，不是说按cid进行分组，然后select所有字段，再排序输出。

这个语句只会选择每一个分组的第一条语句进行select，再排序输出。

2）select sc.* from sc group by cid having cid='01';

输出：

只有一行，原意是想输出cid='01'的那个分组的所有数据(所有行)，但是只输出一行。

group by进行分组的结构是可以理解的，难理解的是

3）select sc.* from sc group by cid;

输出：

只输出每一个分组的第一行数据，，原意是按分组输出所有行数据。 

如何让一个分组的数据按指定数目或者全部显示出来---多级分组

select * from sc group by cid,sid;

进行分组之后的分组，以cid分组之后，每一个cid组中的sid都不相同，再按每一个组中的sid分组，这样，就查询得到所有结果。

MYSQL的分组排序问题

MYSQL8.0以下版本没有实现这种功能的窗口函数和关键字

可以用另外一种方法来实现----------变量

set @str_cid=' ';
set @rank=0;
select score,(case when cid=@str_cid then @rank:=@rank+1 else @rank:=1 end) as rank,@str_cid:=cid
from 
(
  select * from sc order by cid,score desc
) as tem;

select score,(case when cid=@str_cid then @rank:=@rank+1 else @rank:=1 end) as rank,@str_cid:=cid
from 
(
  select * from sc order by cid,score desc
) as tem,
(
  select @str_cid='',@rank=0
) as tem1;

解释：

先把表中的数据排序分组好，再把需要进行排序的字段在第一次排序的基础上再排序，生成临时表；其他的看代码。

group by实质

看图识原理：

group by的目的就是为了什么--------------聚合（汇合）

也就是将一个组中的数据汇合成一条数据

所以经过分组之后得到的表都是每一个分组所得每一条数据的汇合表 

分组是为了统计每个组或者指定组内的数据

如果分组之后进行输出，就只能输出每个分组或者指定组的第一行。

group by的多级分组

group by 后可加多个字段，也就是进行多级分组，也就是在分组中再进行分组。

order by的多级排序

order by也可以有多级排序，先按第一个字节进行排序，再在排序好的临时表中再对每一部分中第一个排序字段相同的部分按第二个字段排序。

每一个字段可以指定不同的排序方法：

select cid,avg(score) as avg_score from sc group by cid order by avg_score desc,cid asc;

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

#查询学生的总成绩，并进行排名.
select sid,avg(score) as 平均成绩 from sc group by sid order by 平均成绩 desc;

21，统计各科成绩各分数段人数，课程编号，[100-85)，[85-70)，[70-60)，[60-0] 及所占百分比。

分组之后还要分段统计

select cid,
sum(case when score<=60 then 1 else 0 end ) as p1,
sum(case when score<=70 and score>60 then 1 else 0 end) as p2,
sum(case when score<=85 and score>70 then 1 else 0 end) as p3,
sum(case when score<=100 and score>85 then 1 else 0 end) as p4,
sum(case when score<=60 then 1 else 0 end )/count(sid) as '60-0百分比',
sum(case when score<=70 and score>60 then 1 else 0 end)/count(sid) as '70-60百分比',
sum(case when score<=85 and score>70 then 1 else 0 end)/count(sid) as '85-70百分比',
sum(case when score<=100 and score>85 then 1 else 0 end)/count(sid) as '100-85百分比'
from sc group by cid;

别名只是一个别名，不可以利用别名进行计算

select中的字段顺序

同一个select子句中字段A计算出来的结果不能用于B字段的计算。

select cid,
@v1:=sum(case when score<=60 then 1 else 0 end ) as p1,
@v2:=sum(case when score<=70 and score>60 then 1 else 0 end) as p2,
@v3:=sum(case when score<=85 and score>70 then 1 else 0 end) as p3,
@v4:=sum(case when score<=100 and score>85 then 1 else 0 end) as p4,
@v1/count(sid) as '60-0百分比',
@v2/count(sid) as '70-60百分比',
@v3/count(sid) as '85-70百分比',
@v4/count(sid) as '100-85百分比'
from sc,(select @v1=0,@v2=0,@v3=0,@v4=0) as t group by cid;

select cid,
@v1/count(sid) as '60-0百分比',
@v2/count(sid) as '70-60百分比',
@v3/count(sid) as '85-70百分比',
@v4/count(sid) as '100-85百分比',
@v1:=sum(case when score<=60 then 1 else 0 end ) as p1,
@v2:=sum(case when score<=70 and score>60 then 1 else 0 end) as p2,
@v3:=sum(case when score<=85 and score>70 then 1 else 0 end) as p3,
@v4:=sum(case when score<=100 and score>85 then 1 else 0 end) as p4
from sc,(select @v1=0,@v2=0,@v3=0,@v4=0) as t group by cid;

以上，两种结果都一样。说明什么：

select中的字段在select中一般是按先后顺序输出的，有先后顺序；但是。

变量在select字段中的应用

比较以下字段结果：

1：

set @v=1,@v2=2;
select @v,@v2;

输出：

结论：变量值可以在select中输出。

 2：

set @v=1,@v2=2;
select @v:=@v+3,@v2:=@v2+1;

输出：

结论：变量也可以在select中进行计算和赋值。 

分组排序分页输出

MYSQL8.0窗口函数

看书----链接

以下内容来自-----链接

窗口：记录集合
窗口函数：在满足某些条件的记录集合上执行的特殊函数，对于每条记录都要在此窗口内执行函数。有的函数随着记录的不同，窗口大小都是固定的，称为静态窗口；有的函数则相反，不同的记录对应着不同的窗口，称为滑动窗口。

1. 窗口函数和普通聚合函数的区别：

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。
②聚合函数也可以用于窗口函数。

2. 窗口函数的基本用法：

窗口函数  over(子句)

over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。
①window_name：给窗口指定一个别名。如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读；
②PARTITION BY 子句：窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行；
③ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号；
④FRAME子句：FRAME是当前分区的一个子集，子句用来定义子集的规则，通常用来作为滑动窗口使用。

3. 按功能划分可将MySQL支持的窗口函数分为如下几类：

①序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

• 用途：显示分区中的当前行号

row_number():

功能：为指定的行进行序号设置，每增加一行，序号加一，不会重复序号，不会少序号。

RANK():

功能：为指定字段的行进行序号设置,如果字段值有重复，序号也相同，下一个不同字段值的序号为重复字段值的序号加上重复字段个数。(序号跳过)

DENSE_RANK():

功能：为指定字段的行进行序号设置,如果字段值有重复，序号也相同，下一个不同字段值的序号为重复字段值的序号的下一个序号。(序号衔接)

rank和dense_rank的简单记忆：

rank--------------跳过

dense_rank----不跳过

看以下代码-----来源链接

select *,
   rank() over (order by 成绩 desc) as ranking,
   dense_rank() over (order by 成绩 desc) as dese_rank,
   row_number() over (order by 成绩 desc) as row_num

from 班级表

序号函数的使用注意事项：

1》over中的关键字的设置只需要空格隔开，不要用逗号。

2》over关键字用来指定函数执行的窗口范围，若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空，则支持以下4中语法来设置窗口。

一定是满足where条件的所有行，不管你是否在其他地方进行了筛选：

#1
select distinct sc.sid,sc.cid,sc.score,row_number() over() as ranking from sc
join sc as tem on tem.score=sc.score
where sc.cid!=tem.cid;

#2
select sc.sid,sc.cid,sc.score,row_number() over() as ranking from sc
join sc as tem on tem.score=sc.score
where sc.cid!=tem.cid and sc.sid!=tem.sid;

#3
select sc.sid,sc.cid,sc.score,row_number() over() as ranking from sc
join sc as tem on tem.score=sc.score
where sc.cid!=tem.cid;


1，3相同
2不同

4，聚合函数作为窗口函数。

先看序号函数的本质意义：

序号函数：添加序号

over():在.......的准备工作上进行添加序号。

而聚合函数作为窗口函数，只需要将窗口函数的位置替换为聚合函数就可以了。

语法：

聚合函数  over()

聚合函数作为窗口函数的本质意义：

在.......的准备工作上进行求和，求平均值，求最大，最小值，求数量。

聚合函数作为窗口函数的执行过程：

1》排序的字段没有重复值：

从第一行开始得到最后一行，以当前所在行以及之前所有行为计算的组，进行计算作为本行的计算结果。

看代码以及结果：

select tem.*,
sum(score) over(order by sid) as 总分,
avg(score) over(order by sid) as 平均值,
min(score) over(order by sid) as min,
max(score) over(order by sid) as max,
count(*) over(order by sid) as 数量
from 
(
  select sc.* from sc where cid='01'
) as tem;

结果：

2 》排序的字段有重复值的：

以重复的值为单位进行计算。

select sc.*,
sum(score) over(order by sid) as 总分,
avg(score) over(order by sid) as 平均值,
min(score) over(order by sid) as min,
max(score) over(order by sid) as max,
count(*) over(order by sid) as 数量
from sc;

输出：

3》在分区和排序的基础上进行计算

以每个分区为单位进行计算，同2》

select sc.*,
sum(score) over(partition by sid order by sid) as 总分,
avg(score) over(partition by sid order by sid) as 平均值,
min(score) over(partition by sid order by sid) as min,
max(score) over(partition by sid order by sid) as max,
count(*) over(partition by sid order by sid) as 数量
from sc;

输出：

利用窗口函数解决之前的问题：

1）按各科成绩进行排序，并显示排名 。

select sc.*,dense_rank() over(partition by cid order by score desc) as ranking from sc;

使用8.0版本的窗口函数比使用低版本的变量实现分组排序简单太多。

1_1）按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺。

select sc.*,rank() over(partition by cid order by score desc) as ranking from sc;

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

select tem.*,rank() over(order by sid_sc desc) as ranking from 
(
  select sc.*, sum(score) as sid_sc from sc group by sid
) as tem;

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

分组排序选择输出

先排号序，再按序号选择输出即可。

select * from
(
  select sc.*,dense_rank() over(partition by cid order by score desc) as ranking from sc
) as tem
where ranking<=3;

窗口函数的别名使用权限

select后的别名可以在group by之后的语句使用，但是处理窗口函数的别名，同一级的查询的窗口函数的别名不可以在group by之后使用。

select tem.cid,st.sname,tem.score,rank() over(partition by tem.cid order by tem.score desc) as ran from student st
join
(
   select cid,sid,score,rank() over(partition by cid order by score desc) as ranking from sc 
   having ranking<2 
) as tem on tem.sid=st.sid;

#报错

解决办法：

将窗口函数的序号放在外层进行筛选就可以了：

select tem.cid,st.sname,tem.score,rank() over(partition by tem.cid order by tem.score desc) as ran from student st
join
(
   select cid,sid,score,rank() over(partition by cid order by score desc) as ranking from sc
) as tem on tem.sid=st.sid
where ranking<=2;

分组，分组排序，分组选择输出

如果查询只涉及分组--------group by

如果查询除了涉及分组，还有排序或者输出-----窗口函数。

23，查询每门课程被选修的学生数

select cid,count(*) from sc group by cid;

24，查询出只选修两门课程的学生学号和姓名。

#先筛选再链接
select st.sid,st.sname from student st
join
(
  select sid from sc group by sid having count(sid)=2
) as tem1 on tem1.sid=st.sid;


#先链接再筛选
select st.sid,st.sname from student st
join sc on sc.sid=st.sid
group by sc.sid
having count(sc.sid)=2;

25，查询男生、女生人数。

select ssex,count(*) from student group by ssex;

26，查询名字中含有「风」字的学生信息。

select * from student where sname like '%风%';

27，查询同名同性学生名单，并统计同名人数。

select *,count(*) as num from student group by sname having num>=2;

28，查询 1990 年出生的学生名单。

#字符串比较
select * from student where sage like '%1990%';
#类型转换进行整型比较
select * from student where year(sage)=1990;

从字符串中获取所需日期并转换：

year(date)
获取date中的年份

select year(now()) as 年;
select year('1998/12/9') 年;
select year('1998-12-9') 年;

month()/monthname()
用来获取月，功能同year，

month返回的是a阿拉伯数字，eg:9

monthname返回的是英文，eg:september

date()

hour()

second()

munite()

都是一样的功能
 

29，查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。

分组，多级排序的问题

select cid,avg(score) as avg_score from sc group by cid order by avg_score desc,cid asc;

30, 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩.

#先筛选再连接
select st.sid,st.sname,tem.avg_score from student st
join
(
  select sid,avg(score) as avg_score from sc group by sid having avg_score>=85
) as tem on tem.sid=st.sid;



#先连接再筛选
select st.sid,st.sname,avg(sc.score) as avg_score from student st
join sc on sc.sid=st.sid
group by sid
having avg_score>=85;

先筛选再连接和先连接再筛选的原理区别

先筛选再连接：

先从从表中筛选复合条件的行，拿这些行去和主表的所有行匹配。

先连接再筛选：

先让主表和从表按连接条件连接，形成笛卡尔积现象的行，再从这些行中筛选复合条件的行。

31，查询课程名称为「数学」，且分数低于 60 的学生姓名和分数。

#先连接再筛选
select st.sname,sc.score from student st
join sc on sc.sid=st.sid
join course tem on sc.cid=tem.cid
where cname='数学' and sc.score<60;

#先筛选再连接
select st.sname,tem.score from student st
join
(
  select sid,sc.score from sc
  join course tem1 on tem1.cid=sc.cid
  where cname='数学' and sc.score<60
) as tem on tem.sid=st.sid;

32，查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）。

#先连接再筛选
select st.sname,tem.cname,sc.score from student st
left join sc on sc.sid=st.sid
left join course as tem on tem.cid=sc.cid;


#先筛选再连接
select st.sname,tem.cname,tem.score from student st
left join
(
  select sc.sid,tem1.cname,sc.score from sc
  join course tem1 on tem1.cid=sc.cid
) as tem on tem.sid=st.sid;

多级左独有和右独有

多表同级连接左连接和有连接如果想最终保存左独有或者右独有，必须每一层连接都进行左孩子右连接，不至于独有被过滤。

33，查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数。

select st.sname,tem.cname,tem.score from student st
join
(
  select sc.sid,sc.score,tem1.cname from sc
  join course as tem1 on tem1.cid=sc.cid
  where sc.score>70
) as tem on tem.sid=st.sid;

#先连接再筛选
select st.sname,tem.cname,sc.score from student st
join sc on sc.sid=st.sid
join course as tem on tem.cid=sc.cid
where sc.score>70; 

34，查询不及格的课程。

select tem.cname,sc.score from sc
join course as tem on tem.cid=sc.cid
where sc.score<60; 

35，查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名。

#先筛选再连接
select st.sname,st.sid,tem.cname,tem.score from student st
join 
(
  select sc.score,sc.sid,c.cname from sc
  join course as c on c.cid=sc.cid
  where c.cid='01' and sc.score>60
) as tem on tem.sid=st.sid;

#先连接再筛选
select st.sname,st.sid,c.cname,sc.score from student st
join sc on sc.sid=st.sid
join course as c on c.cid=sc.cid
where c.cid='01' and sc.score>60;

36，求每门课程的学生人数。

select c.cname,count(*) as 人数 from sc
join course as c on c.cid=sc.cid
group by sc.cid;

37，成绩没有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩。

#先筛选再连接
select st.sname,st.sid,tem.score from student st
join 
(
  select sc.sid,sc.score from sc
  join course as c on c.cid=sc.cid
  join teacher as t on t.tid=c.tid
  where tname='张三'
  having sc.score=max(sc.score)
) as tem on tem.sid=st.sid;


#先连接再筛选
select st.sname,c.cname,sc.score from student st
join sc on sc.sid=st.sid
join course as c on c.cid=sc.cid
join teacher as t on t.tid=c.tid
where t.tname='张三'
having sc.score=max(sc.score);

38，查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。

#不同课程，而且所有结果，每个学生只显示一项
select distinct sc.sid,sc.cid,sc.score from sc
join sc as tem on tem.score=sc.score
where sc.cid!=tem.cid;


#课程不同，也不是同一个学生的
select sc.sid,sc.cid,sc.score,row_number() over() as ranking from sc
join sc as tem on tem.score=sc.score
where sc.cid!=tem.cid and sc.sid!=tem.sid;

#课程不同，包括同一个学生的相同分数不同课程的
select sc.sid,sc.cid,sc.score,row_number() over() as ranking from sc
join sc as tem on tem.score=sc.score
where sc.cid!=tem.cid;

39，查询每门课成绩最好的前两名。

select tem.cid,st.sname,tem.score,rank() over(partition by tem.cid order by tem.score desc) as ran from student st
join
(
   select cid,sid,score,rank() over(partition by cid order by score desc) as ranking from sc
) as tem on tem.sid=st.sid
where ranking<=2;

40，统计每门课程的学生选修人数（超过 5 人的课程才统计）。

#先连接再筛选
select c.cname,c.cid,count(sc.cid) as 人数 from course c
join sc on sc.cid=c.cid
group by sc.cid
having 人数>5;

#先筛选再连接
select c.cname,c.cid,tem.num from course as c
join
(
  select cid,count(*) as num from sc
  group by cid
  having num>5
) as tem on tem.cid=c.cid;

41，检索至少选修两门课程的学生学号。

select sid from sc group by sid having count(cid)>=2;

42，查询选修了全部课程的学生信息。

select st.*,sc.cid from student st
join sc on sc.sid=st.sid
group by sc.sid
having count(sc.cid)=(select count(*) from course);

select st.*,tem.选课数量 from student st
join
(
  select sc.sid,count(sc.cid) as 选课数量 from sc group by sid
  having 选课数量=(select count(*) from course)
) as tem on tem.sid=st.sid;

43，查询各学生的年龄，只按年份来算。

select st.sname,year(curdate())-year(sage),rank() over(order by sage) as ranking from student st;
select st.sname,year(now())-year(sage),rank() over(order by sage) as ranking from student st;

44，按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一。

select tem1.sname,(case when day(now())<day(sage) then tem1.年龄m-1 else tem1.年龄m end) as 年龄 from
(
  select tem2.*,(case when month(now())<month(sage) then tem2.年龄y-1 else tem2.年龄y end) as 年龄m from
  (
    select st.*,year(now())-year(sage) 年龄y from student st
  )as tem2
) as tem1;

#月如果相同，才进行日的比较:字符串比较
select sname,(case when (date_format(now(),"%m-%d")-date_format(sage,'%m-%d'))<0 then year(now())-year(sage)-1
else year(now())-year(sage) end) as 年龄 from student;

45，查询本周过生日的学生。

select st.sname,sage from student st where week(now())=week(sage);

46，查询下周过生日的学生。

select sname from student where week(sage)=week(now())+1;

47，查询本月过生日的学生。

select sname from student where month(now())=month(sage);

48，查询下月过生日的学生。

select sname from student where month(now())+1=month(sage);

表的赋值