SQL50题分析思路与知识点总结

1.数据库的一般构造：数据库就是多张表的集合：

1）每张表都必须有一个能标注出每条记录的字段，即保证不存在完全相同的两条记录

2）表中每个字段都必须有清晰的类型定义，且同一列中的所有单元格类型必须一致

3）表中行的内容可以被更新，但表的字段需要长期稳定，且一般只能增加字段而不能删减字段

4）表与表之间通过某些字段联系在一起，这些字段称为“键”----所有数据都存储在一张张表中，每张表都会有某些字段在其他表中出现，因此表之间可以通过“键”产生联系----比如：A表与B表之间有个共同的字段--用户ID；B表与C表之间有个共同的字段---日期字段；A表与C表有某些共同的字段---表B;所以：“用户ID、日期字段、表B”都属于“键”。

2.能够阅读基本的取数代码：SQL语言三要点：函数、限制和串表查询。

SQL语句的基本构成：每个SQL语句都要解决“要什么”、“从哪里要”、“怎么要”这三个问题，所以都具备“select、from、where”这三个单词。

1）要什么：对应“select xxxx”,即要哪些字段或者哪些字段经过转换后的结果（转换：即使用函数）

2）从哪里要：“from xxxx”,即要的东西在哪个表里面（数据来源有些并不只是一个表，因此从多个来源中获取数据时，就需要串表查询）

3）怎么要“where xxxx”,where后面写着限制条件，就是告诉数据库怎么要，按什么条件要（如果是无条件的全要，where语句部分可以不写）

3.SQL语句中最常见的函数

1）聚合函数：需要由多条记录汇总计算而成的函数，比如：算术平均数、求和、计数、最大值、最小值等

2）标量函数：针对一条记录转化成某种结果的函数，比如：日期转换、计算字符长度、取字符、格式转化等

3）最常使用的聚合函数有：求和函数sum()、算术平均数avg();计数函数count(),最大值/最小值max()/min()

4)聚合函数在使用时，SQL语句最后必须跟上“group by”语句来表明聚合的条件----“group by”的实质是按条件将表中所有记录进行分组，然后在每个组中通过聚合函数得出一个值----需要仔细审核“group by”的汇总条件与取数要求是否一致-----“group by”是聚合函数的定语（表明聚合条件）

比如：表A性别对应的用户数-----“用户数”前面的定语是“性别”

select count(用户ID) as '人数','性别'

from 表A

group by '性别'（聚合条件）

4.标量函数：

日期转换函数：DATA()

取部分字符函数：MID()/SUBSTRING()

小数点舍入函数：round()

格式转换函数：format()

获得系统当前日期和时间：getdate()/now()

5.限制条件时最容易出错的环节：and，or，大于，小于，大于等于>=，小于等于<=，不等于<>，之间 between........and，在........内in()，不在........内not in()这几个

（对于文本型字段，由于不存在数量关系，要用“like”标签来匹配字符）

6.串表查询一定要注意先后顺序：

“join”就意味着串表查询

1）交集合并:inner join:就是两个表在字段合并后，合并完的表中的记录必须是两个表中都存在（串表的语法中，两张表一定要有能对应上的“键”，写法就是在“on”关键字后面写上对应键的关系）

2）表A左合并（left join）表B：表A的信息会全部保留，表B中能与表A记录匹配上的信息会保留下来。（尽量在一段代码中只选择一种合并方向，若在SQL代码中既有“left join”又有"right join "说明取数人的思路是混乱的），要使用“left join”或“right join”，要先思考清楚主表是哪个，确定主表之后，之后的表作为补充主表信息的依据，逐步连接（join）进去。

7.语句：

select [列]

from [表]-------------从某个表中取其中的某列

where 条件筛选=like,between.......and,and/or,in

order by asc ,desc

limit 限制行数

查询步骤：查询----新建---运行

比如：

1.select s-name from t_student;

2.select s_name,s_hobby from t_student;

3.select* from t_student;-------（*代表“整张表格中的所有信息”）

4.select * from t_student where s_gender=1;

1.where +限定条件----“=”精确查询，可以替换为“模糊查询”：like,between .....and;and/or/in.

2.“between.....and”根据一个范围来选择----where +列+between A and B(范围从A到B----“and”一般为“数字、生日”之类)

3.从表中选择“生日年份”（要加YEAR）

4.and/or(多条件逻辑判断)-------and:两个条件必须同时满足；or：满足任意条件即可5.select *

from t_student

where s_gender=1(“=”是精确查询)

6.select *

from t_student

where s_hobby like"%吃肉%"（字段中包含“吃肉”，“%”是通配符，只要包含“吃肉”即可，无论顺序在哪儿）

7.select *

from t_student

where s_id between 103 and 107;

8.select *

from t_student

where year(s_birthday) between 1991 and 1992;

9.select *

from t_student

where s_gender=1 and s_id=901

10.select *

from t_student

where s_gender=1 and s_hobby like'%吃肉%'；

11.select *

from t_student

where s_name='乔巴' or s_name='娜美'；

12.select *

from t_student

where s_name in ('娜美','乔巴');

order by :排序：默认升序----ASC----默认“升序”-----DESC----降序

limit:限制行数-----限制行数：当表格数据达百万行，速度会很慢，当只想随意浏览表格时，就可以限制行数。

1.按s_id的顺序排序：

select *

from t_student

order by s_id

2.先按“班级”排序，后按“名字”排序：

select *

from t_student

order by c_id,s_name

3.select *

from t_student

limit 3

group by :当用到合计函数时，求和函数（SUM）、平均数（AVG）

模板语句：写SQL语句时，一定要按照这样的顺序写：

select----------------列

from-----------------表

where---------------条件筛选‘=’、like、between........and,and/or,in------这个语句只能作用于“原始表格”，当语句中使用“聚合函数”时，不能用where

group by --------对聚合函数进行分组----表明聚合条件----是聚合函数的定语

having-----------聚合函数条件筛选，把where换成having(对聚合函数实行的筛选）

order by ----------排列顺序-----ASC,DESC

limit-------------限制行数

聚合函数是系统计算后生成的表格，而非原始表格

1.求每个人的总分

select student_name,sum(score)

from t_point_score

group by student_name(每个人的总分-----总分的定语是“每个人”)

2.求每个人的总分，并按从大到小的顺序排列：

select student_name,sum(score)

from t_point_score

group by student_name

order by sum(score) desc

3.求每个人的总分，并按从大到小的顺序排列，且选择总分>150

select student_name,sum(score)

from t_point_score

group by student_name

having sum(score)>150

order by sum(score) desc

4.求1班同学的总分，并按从大到小的顺序排序，且选择总分大于150

select student_name,sum(score)

from t_point_score

where class_name=''1班----------对原始表格里的条件进行筛选

group by student_name

having sum(score)>150----------对聚合函数进行条件筛选

order by sum(score) desc---------降序排列

5.求1班同学的总分，并按从大到小的顺序排序，且总分>150，只看筛选结果中的1行

select student_name,sum(score)

from t_point_score

where class_name='1班'

group by student_name

having sum(score)>150

order by sum(score) desc

limit 1

6.将“student_name”改名为“学生名称”，sum(score)改名为“分数”

select student_name as '学生名称'，sum(score) as '分数'

from t_point_score

group by student_name

多表连接：

“left join”=“right join”，两表的位置互换了下，在实际中“left join”以及“inner join”应用最多。

表A:

表B:

1.left join :表A左合并（left join）表B：表A的信息会全部保留，表B中能与表A记录匹配上的信息会保留下来，串表的语法中，两张表一定要有能对应上的“键”，写法就是在“on”关键字后面写上对应键的关系）------在表B中，能与表A对应上的“键”所带有的信息保留，匹配不上的“键”对应信息为“null”。

left join :

inner join :

outer join :

是将表A 有、仅表B有、AB表都存在的部分全部表示出来,匹配不上的地方用null表示

语句：

select -------------列

from---------------表

join----------------联结表-------left join 和inner join (right join 和outer join运用得比较少)------以两表中对应的“键”为联结点

（这三个语句是将“几个表”合并成“1张表后”，下面的语句是对“合并表”进行处理）

where--------------条件筛选---------------------=,like,between......and,and/or,in

group by -----------分组，表明聚合函数的条件----为聚合函数的定语

having -------------对聚合函数进行筛选

order by----------排列-------DESC,ASC

limit---------------限制行数

1）复杂写法：

select *

from t_score--------取出的第一个表

left join t_student on t_score.s_id=t_student.s_id --------t_student为联结的第二张表，红色部分为两表对应的“键”，为联结点

2）精简写法：

select *

from t_score as a

left join t_student as b on a.s_id=b.s_id

3)选取班级为901

select *

from t_score as a

left join t_student as b on a.s_id=b.s_id

where b.c_id=901

4)选取id,name,course,score

select a.s_id,b.s_name,a.course_name,a.score

from t_score as a

left join t_student as b on a.s_id=b.s_id---------红色部分为两个表，现将这两个表联结，然后对合并表进行选取

5）知道“学生名字”找出“班级名称”----（要“联结”第3张表，t_class）-----要联结多张表时，可以先写from语句，对多张表进行联结，最后再写select 语句。

select a.s_id,b.c_id,b.s_name,a.course_name,a.score,c.c_name

from t_score as a

left join t_student as b on a.s_id=b.s_id

left join t_class as c on b.c_id=c.c_id

6） 知道“学生名字”找出“班级名称”，只要“数学成绩”并且“分数”大于40分

select a.s_id,b.s_name,a.course_name,a.score,c.c_name

from t_score as a

left join t_student as b on a.s_id=b.s_id

left join t_class as c on b.c _id=c.c_id

where a.course_name in ('数学') and a.score>40

7

select *

from t_score as a

left join (select * from t_student where c_id=901) as b on a.s_id=b.s_id

1.单表查询：

查询仅涉及一个表：

1）选择表中的若干列---------select子句

2)指定查询对象（基本表或视图）--------from子句

3）指定查询条件--------where[条件表达式]

5）对查询结果分组-----------group by 子句，对查询结果按指定列的值分组

6）对分组之后结果进行筛选-----having 子句

3）对查询结果排序------------order by 子句，只能对最终结果进行排序，对查询结果按指定列值的升序或降序排列

1.选择表中的若干列

1）属投影运算----不消除重复行

2）select子句查询指定属性列-----查询全部列，查询经过计算的值

3）查询指定列：在select子句中指定要查询的属性列

1.各个列的先后顺序可以与表中的逻辑顺序不一致

2.用户可以根据应用的需求改变列的显示顺序

SQL函数：

year():返回指定日期的“年”日期部分的整数

getdate():返回当前的系统日期和时间

消除取值重复的行：

在select子句中使用distinct短语

distinct短语的作用范围是所有目标列

2.比较大小：

在where子句的condition中使用比较运算符：

=，<,>，<=,>=,!=或<>，！>,!<

逻辑运算符not+含上述比较运算的表达式

确定范围：

使用谓词：

between .........and

not between......and.......

between后：范围的下限（即低值）；and后:范围的上限（即高值）

用多重条件查询实现

确定条件：

使用谓词：

in【值表】

not in 【值表】

【值表】：用逗号分隔的一组取值

比如：

1.查询信息系（IS）、数学系（MA）和计算机系（CS）学生的姓名和性别

select sname,ssexx

from student

where sddept in ('IS','MA',''CS')

2.查询既不是信息系、数学系，也不是计算机系学生的姓名和性别

select sname,ssexx

from student

where sddept not in ('IS','MA',''CS')

字符串匹配：

使用谓词like或not like

格式：not like '【字符串】'

1.【匹配串】：指定匹配模板

2.匹配模板：固定字符串或含通配符的字符串

3.当匹配模板为固定字符串时，可以用=运算符取代like，用！=或<>运算符取代not like 谓select sname,ssexx

from student

where sdept in ('IS','MA',''CS')select sname,ssexx

from student

where sdept in ('IS','MA',''CS')词。

通配符：

%（百分号）代表任意长度（长度可以为0）的字符串，比如a%b表示以a开头，以b结尾的任意长度的字符串；_（下横线）代表任意单个字符，比如：a_b表示以a开头，以b结尾的长度为3的任意字符串。

比如：

1.查询姓“欧阳”且全名为三个汉字的学生的姓名

select sname

from student

where sname like '欧阳_'

2.查询名字中第2个字为“阳”字的学生的姓名和学号

select sname

from student

where sname like ‘_阳%'

3.涉及空值的查询：

使用谓词is null或is not null

“is null”不能用“=null”代替

比如：

1.某些学生选修课程后没有参加考试，所以有选课记录，但是没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

select sno,cno

from sc

where grade is null

2.查所有有成绩的学生学号和课程号

select sno,cno

from sc

where grade is not null

3.多重条件查询：

用逻辑运算符and 和or来连接多个查询：

1.and的优先级高于or

2.可以用括号改变优先级

可以来实现多种其他谓词：

1. (not)in

2.（not）between......and.......

比如：

1.查询计算机系年龄在20岁以下的学生姓名

select sname

from student

where sage<20 and sdept='CS'

2.查询信息系（IS）、数学系（MA）和计算机科学系（CS）的学生的姓名和性别

select sname,ssex

from student

where sdept in（'IS','MA','CS'）

(三个条件只要满足一个条件即可)

3.对查询结果排序：

1.使用order by 子句：

可以按一个或多个属性列排序；升序ASC，降序DESC

2.当排序列含空值时

ASC：排序列为空值的元组最后显示

DESC:排序列为空值的元组最先显示

4.使用聚合函数：-----统计时不计null值

1）统计计数：count(列名)

2)计算列总和：sum(列名)

3）计算列平均值：avg(列名)

4）求列最大值、最小值：max()、min()

5）count(*):统计行数

6）distinct:在列名前加distinct，则统计时去掉重复值

1.聚合函数查询之后结果只有1行结果意义不大，很多时候需要对查询结果进行分组显示。

2.对查询结果分组：用途：细化集函数的作用对象

3.使用group by子句分组：分组方法：按指定的一列或多列值分组，值相等的为一组（按指定条件进行分组，符合同一条件的归为一组，然后每一组分别应用聚合函数---分成几组，最后就统计出几个聚合函数的结果）

4.使用group by子句后，select子句的列名列表中只能出现分组属性（group by后面的列名）和聚合函数-----出现其他的字段没有意义，在mysql中容扩能力比较强，可以执行；在sqlserver中不能执行，即使可以执行也没有任何意义。

5.group by 子句的作用对象是查询的中间结果表

比如：求各个课程号及相应的课程成绩在90分以上的学生人数

select cno,count(sno)-----按什么条件进行分组，sselect语句中首先有分组条件，然后再写聚合函数。

from sc

where grade>=90

group by cno

6.使用having短语筛选最终输出结果---只有满足having短语指定条件的组才输出

7.having短语与where子句的区别：作用对象不同：

where子句作用于基表或视图，从中选择满足条件的元组；having短语作用于组，从中选择满足条件的组；where子句后面可以直接接列名，不可以直接跟“聚合函数”。

8.“统计什么”即“统计”后面跟着什么，就按什么进行分组----按统计对象进行分组。

9.'having'子句是附属于“group by”的，只有“group by”存在，才可能会有having子句。

比如：

1.查询选修了3门以上课程的学生学号

select sno

from sc

group by sno

having count(*)>=3

2.查询有3门以上课程在90分以上的学生的学号以及90分以上的课程数

select sno,count(*)

from sc

where grade>=90-----90分以上是先行条件，参加统计的学生都必须是在90分以上的

group by sno

having count(*)>=3

3.统计每门课程的最高分------按统计对象进行分组

select cno,max(grade)

from sc

group by cno;

SQL50题：

第一部分：聚合函数：

知识要点与易错点总结：

1.聚合函数主要包括：sum,avg,count,max,min等。

2.聚合函数通常配合group by 使用，按一个或多个列对结果集进行分组，但是初学者对where/group by /having的使用经常会出现问题：

1）where在返回结果集之前起作用，即拿一个筛选条件对整个表进行初步筛选，此时，并没有任何分组，即在分组之前过滤数据，所以where中不能包含聚合函数。

2）group by 在返回结果集之后起作用：按照某个字段或表达式对结果集进行分组，获得一组组的集合，然后从每组中取出一个指定字段或表达式的值。

3）having是对where和group by查询出来的结果集分组进一步筛选，过滤出满足条件的分组结果。所以，having可以用聚合函数，但是having 只能写在group by后面。

如果SQL语句同时含有where,group by ,having ，聚合函数，顺序如下：

执行where进行初步筛选

使用group by 对返回的结果集进行分组，若select中有聚合函数，则group by形成的每个组运行聚合函数计算每一组的值，最后用having对group by 的结果进一步过滤；若having中有聚合函数，则继续运行聚合函数计算过滤的值。

如果还有order by，最后使用order by 和limit。

最后总结顺序为：select----from----where--group by --having -order by ---limit ;

用SQL解决问题：

1.翻译成大白话

2.写出分析思路

3.写出对应的SQL语言子句

比如：

如何求各个科目的平均成绩：

1）翻译成大白话：计算每个科目的平均成绩：

2）分析思路：每个课程（分组：group by）、平均成绩（汇总函数avg）、from(从成绩表)、where(指定条件没有)、group by (分组)、having(没有)

聚合函数题目整理：

19.查询每门课程被选修的学生数

翻译：计算每门课程被选修的学生人数

查询字段：

学生人数count(sid)----涉及表：成绩表：sc

课程：课程号cid------涉及表：成绩表：sc

分组条件：课程cid

select cid,count(sid) as '学生人数'

from sc

group by cid

21.查询男生、女生人数

翻译：计算男生的人数，女生的人数

查询字段：

学生人数：count(sid)------涉及的表：学生表：student

性别：ssex

分组条件：性别：男女

select ssex as '性别' ,count(sid) as '学生人数'

from student

group by ssex

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

翻译：计算每门课程的平均成绩

查询字段：

平均成绩avg(cscore)-----涉及表：student

课程编号：cid

分组条件：课程：cid;

排序条件：先按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select cid ,avg(csore) as '平均成绩'

from student

group by cid

order by avg(csore)desc,cid asc

32.每门课程的学生人数：

翻译：计算每门课程的学生人数：

查询字段：

学生人数：count(sid)-----涉及的表sc

分组条件：每门课程---课程编号cid----涉及的表sc

筛选条件：无

select cid,count(sid) as‘选修人数’

from sc

group by cid

37.统计每门课程的学生选修人数（超过5人的课程才统计）

翻译：计算每门课程的选修人数，限定条件：选修人数超过5人

查询字段：

选修人数：count(sid)>5----涉及的表：成绩表sc

分组条件：每门课程：课程编号cid-----涉及的表：成绩表sc

筛选条件：count(sid)>5

select cid, count(sid) as '选修人数'

from sc

group by cid

having count(sid)>5

38.检索至少选修两门课程的学生学号

翻译：选出学生学号，条件是：这些学生选修了至少两门课程-----group by (分组条件是聚合函数的限定语)，作用对象

查询字段：

学生学号（sid）--涉及的表：学生表：student,成绩表sc

至少两门课程：count(cid)>=2------涉及的表：成绩表：sc

分组条件：学生----按学生编号----sid

select sid

from sc

group by sid

having count(cid)>=2

第二部分：表连接：

1.表连接是基于不同表中的相同字段，把不同的表结合起来，以下是常见的连接类型：

1）inner join (可以简写为join，交集)：只返回两个表中有匹配的行

2）left join (左连接)：以左表为主表，即使右表中没有匹配，也从左表返回所有的行

3）right join （右连接）：与left join相反

4）full outer join (mysql不支持，并集)：只要其中一个表存在匹配，则返回所有行。

另外还有一些进阶表连接类型：

1.union：union语句中的每个select语句必须具有相同的列数，这些列也必须具有相似的数据类型，每个select语句中的列也必须以相似的顺序排列，总的来说就是从column，到data type,到select顺序，三者都需要完全一致。

2.self join (自交)：这个本质上就是上面四种join，比如说需要对比同一张表上同一列信息时可以用。

3.left join excluding inner join ：可以只返回左表独有的数据，写法就是left join +where列名=is null

举例：假设左表A包含联合国常任理事国（中/美/英/法/俄），右表B包含G7（英/法/美/德/日/意/加），那么left join excluding inner join的结果就是（中/俄），因为（英/美/法）是两个表的交集（删掉），（德/意/加/日）是右表有但是左表没有的数据（删掉），所以最后只留下了左表独有的记录。

4.full outer join excluding inner join ：返回左表和右表里各自独有的记录，写法就是full join +where 列名1=is null or 列名2=is null

举例：假设左表包含联合国常任理事国 （中/美/英/法/俄），右边表B包含G7 （美/英/法/德/日/意/加），那么full outer join excluding inner join的结果就是 【中/俄/德/日/意/加】,因为 【美/英/法]是两个表的交集（删掉），所以最后留下了只有两个表各自独有的记录，即左表的 【中/俄】和右表的 【德/日/意/加】

附注：因为mysql不支持full join，所以要用替代写法， 本质上就是第一步left join +where子句，第二步right join +where 子句，第三步用union把前两步的结果连起来。2.连接查询:

1）同时涉及多个表的查询称为连接查询

2）用来连接两个表的条件称为连接条件或连接谓词

3）SQL中连接查询的主要类型：

1.交叉连接（广义笛卡尔积）

2.等值连接

3.自身连接

4.复合条件连接

5.内连接

6.外连接

1）交叉连接：（广义笛卡尔积）-----不带连接谓词的连接----很少使用---无论两表是否匹配都筛选出来了

2）等值连接：

连接运算符为=的连接操作：【表名1】.【列名1】=【表名2】.【列名2】---实际上是执行广义笛卡尔积的条件筛选。

比如：查询每个学生及其选修课程的情况:

select student.*,sc.*

from student,sc

where student.sno=sc.sno

表名前缀：

任何子句中引用表1和表2中同名属性时，都必须加表名前缀；引用唯一属性名时可以加也可以省略表名前缀，起了别名之后，在其他的子句中要使用别名，因为系统先执行from子句，

将前面广义笛卡尔积的结果按where子句中的条件进行筛选。

2.查询计算机系（CS）学生的学号，姓名，所在系，选修的课程号，课程名和成绩

select sc.sno,sname,sdept,sc.cno,cname,grade----等值连接不去掉重复的列，有两个sno,两个cno，此时一定要标注出是哪个表的sno和cno，如果不标注出来系统将无法识别，就会提示出错。

from student,sc,course

where student.sno=sc.sno

and course.cno=sc.cno

and sdept='CS'

3）自身连接：

一个表与其自己进行连接，称为表的自身连接

表示方法：

需要给表起别名以表示区别，由于所有属性名都是同名属性，因此必须使用别名前缀

1.查询每一门课的直接先修课的课程名：

要对同一个表查询（操作）两次，首先要给这个表起别名，因为from后面是两张同样的表，系统无法区分。

select a1.cname,a2.cname

from course as a1,course as a2

where a1.cpno=a2.cno

(当from后面有多个表名时，一定要有条件将两个表连接起来)

2.查询每一门课的间接先修课的课程号（即先修课的先修课）

select a1.cno,a2.cpno

from course as a1,course as a2

where a1.cpno=a2.cno

自身连接有个特点是：对同一个字段执行了两次查询

3.查询同时选修2号课程和3号课程学生的学号：

select a.sno

from sc as a,sc as b

where a.sno=b.sno

and a.cno<>b.cno

and a.cno=2

and b.cno=3

(按照学号相等连接，将课程号不相等的筛选出来，最后在筛选出来的课程号中选择：cno=2号课程和3号课程)

自身连接的特点：是对同一张表中的同一个字段进行了2次筛选查询

表连接题目整理：

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

找到学生的信息和课程分数，筛选条件：“01”课程比“02”课程成绩高

select sc.sid,sname,sage,ssex,score

from student

inner join (select * from score where cid=01) as t1 on student.sid=t1.sid

inner join (select * from score where cid=02) as t2 on t1.sid=t2.sid

where t1.score=t2.score

红色部分是两张表，表是不能直接进行比较的，可以通过联结，将2张表统一成一张表后，再进行某些字段的比较。

1.2查询同时上过“01”课程和“02”课程的学生

翻译：找到学生姓名，学生编号，筛选条件上过‘01’课程和“02”课程

学生姓名：sname,学生编号：sid-----涉及表格：学生表student

课程编号cid------涉及表格：成绩表：sc

筛选条件：cid=01 and cid=02

select sname,sc.sid

from sc,student

where sc.sid=student.sid

and cid in(01,02)

1.3查询上过“01”课程但可能没上过“02”课程的学生（这种情况显示为null）

自交题目：用left join会把左表有但是右表没有的数据（即学过01课程但是没有学过02课程的学生）也加进来，并且如果是这种情况，左表有01课的数据，右表02课的三列数据都显示null

select *

from (select * from score where cid=01) as a

left join (select * from score where cid=02) as b

on a.sid=b.sid

1.4查询没上过“01”课程，只上过“02”课程

自交题目，注意和上题的区别---可能没上过“02”课程

此题的条件是必定上过02课程，所以和left join相反，可以用right join ,但是我们需要找的是没上过01课程只上过02课程的学生，所以在join后的联合表中，这个学生原属于t1表的数据（即：有关01课程的记录）一定是null，因为这个学生并没有t1表的数据，所以join之后t1表的字段都是null

select *

from (select * from score where cid=01)as t1

right join (select * from score where cid=02) as t2

on t1.sid=t2.sid

where t1.cid is null

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

翻译：找出学生编号、学生姓名、平均成绩，筛选条件： 平均成绩大于等于60；

查询字段：

学生编号：sid,学生姓名sname------涉及表格：学生表：student

平均成绩avg(score)-----涉及表格：成绩表sc

筛选条件：avg(score)>=60

分组条件：学生的平均成绩-----聚合函数的限定对象为“学生”-----sid

学生表和成绩表的相同字段为：sid

select sc.sid,sname,avg(score)

from sc,student

where sc.sid=student.sid

group by sid

having avg(score)>=60

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

翻译：所有学生的学生编号、学生姓名、选课总数、所有课程的总成绩；没成绩的显示为null，因为有的学生没有选课或参加考核（成绩表上就不会有该名学生的sid），当学生表(student)与成绩表（sc）两表联合left join，共同键（sid）匹配不上的地方就会显示null。

查询字段：

学生编号sid，学生姓名sname-------涉及表格：学生表：student

选课总数:count(cid),sum(score)------涉及表格：成绩表sc

分组条件：所有学生的选课总数、总成绩------聚合函数的限定条件为“所有学生”---sid

select sc.sid,sname,count(cid),sum(score)

from sc,student

where sc.sid=student.sid

group by sc.sid

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

翻译：找出学号、姓名和平均成绩，筛选条件：两门以上课程且成绩不及格

查询字段：

学号：sid,姓名：sname-----涉及表格:学生表：student

平均成绩：avg(score),成绩score，课程数量count(cid)-----涉及表格：成绩表

筛选条件：count(cid)>=2 and score<60

学生表与成绩表的相同字段为sid

select sc.sid,sname,avg(score)

from sc,student

where sc.sid=student.sid

group by sid

having count(cid)>=2 and score<60

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

翻译：找到学生信息，按分数降序排列，筛选条件：“01”课程分数小于60。

查询字段：

学生信息：sid,sname,sage,ssex-------涉及表格：学生表：student

分数：score---涉及表格：成绩表：sc

课程编号：cid

成绩表和学生表的相同字段为sid

select sc.sid ,sname,sage,ssex

from student,sc

where student.sid=sc.sid

and cid=01

and score<60

order by score desc

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

翻译：找出所有学生的所有课程的成绩和平均成绩，按降序排列

查找字段：

学生：sid----涉及表格：学生表student;成绩表：sc

课程成绩：score----涉及表格：成绩表sc

平均成绩：avg(score)------涉及表格：成绩表sc

分组条件：sid------所有学生的平均成绩-----聚合函数的限定条件为学生sid

select sid,score ,avg(score)

from sc

group by sid

order by avg(score) desc;

20.查询出只选修两门课程的学生学号和姓名

翻译：找出学生学号、姓名，筛选条件：只选修两门课程，即成绩表上只有2门课程成绩

查询字段：

学生学号：sid；学生姓名：sname-----涉及表格：学生表：student

课程编号：cid---涉及表格：成绩表：sc

筛选条件：count(cid)=2----聚合函数，使用having做筛选

分组条件：sid------只选修两门课程的学生-----聚合函数的限定对象是学生

select sid,sname

from sc,student

where sc.sid=student.sid

group by sid

having count(cid)=2

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

翻译：找出学生的学号，姓名和平均成绩，条件：平均成绩大于等于85分，分组条件：学生编号

查询字段：

学生学号：sid；学生姓名：sname-----涉及表：学生表student

平均成绩：avg(score)------涉及表：成绩表：sc

学生表和成绩表的相同字段为sid

筛选条件：

avg(score)>=85-------聚合函数用having 进行筛选

分组条件：sid-----所有学生的平均成绩----聚合函数的限定条件是“所有学生”

select sid,sname,avg(score)

from student ,sc

where student.sid=sc.sid

group by sc.sid

having avg(score)>=85

27.查询课程名称为【数学】，且分数低于60的学生姓名和分数

翻译：找出学生姓名和分数，条件为课程名称为【数学】，且分数低于60分

查询字段：

学生姓名：sname------涉及表：学生表：student

分数：score--------涉及表：成绩表：sc

课程名称：cname------涉及表：课程表：course

筛选条件：cname='数学' and score<60

学生表与成绩表的相同字段为sid

成绩表与课程表的相同字段为cid

select sname,score

from student as a,sc as b ,course as c

where a.sid=b.sid

and b.cid=c.cid

and cname='数学' and score<60

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

翻译：找出姓名，分数和课程名称，条件：任何一门课程成绩在70分以上

查询字段：

学生姓名：sname------涉及表：学生表:student

分数：score-----涉及表：成绩表sc

课程名称：cname-----涉及表：课程表course

筛选条件：只要有一门课程成绩在70分以上

学生表与成绩表的相同字段为sid;

成绩表与课程表的相同字段为cid;

select sname,score,cname

from student as a,sc as b,course as c

where a.sid=b.sid

and b.cid=c.cid

and score>=70;

where 直接筛选的效率最高，从成绩表、课程表、学生表中，同时查询符合条件的数据，有三个条件：

成绩表和学生表的sid相同（效果类似于inner join ）

成绩表和课程表的cid相同（效果类似于inner join ）

成绩表的分数score>=70分

30.查询不及格的课程及学生名，学号，按课程号从大到小排列

翻译：找出课程名和学生名，学号，并且按课程号从大到小排列，条件：课程不及格

查询字段：

课程名：cname---涉及表：课程表course

学生名：sname;学号：sid;----涉及表：学生表students

课程编号：cid------涉及表：课程表course,sc成绩表

分数：score-----涉及表：sc:成绩表

学生表和成绩表的相同字段为sid;

成绩表与课程表的相同字段为cid;

select cname,sname, a.sid

from sc as a,student as b,course as c

where a.sid=b.sid

and a.cid=c.cid

and score<60

order by a.cid desc;

31.查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名

翻译：找到学生的学号、姓名，条件是课程编号为01并且课程成绩在80分及以上

查询字段：

课程编号：cid----涉及的表:成绩表sc;

学生姓名sneme,学生学号：sid------涉及的表：student

课程成绩：csore-----涉及的表：成绩表：sc

筛选条件：cid=01 and csore>=80-----没有聚合函数使用where做筛选

学生表和成绩表的相同字段为sid

select sid,sname

from sc,student

where sc.sid=student.sid

and cid=01

and csore>=80

第三部分：窗口函数+limit+变量

知识点与易错点总结：

在mysql中，limit用于指定要返回的记录数量：

limit有两个参数，前一个数字是开始行号，后一个数字是限制条数

比如：

1）limit2,1指的是从第2行开始，再多选择1行，也就是说只选择了第3行

2）limit3指的是选择前3条记录，也就是说省略了第二个参数

因为SQL不能Group by以后取limit，所以为了进行每组内排名，就需要用到窗口函数了，常见使用使用场景比如：

1）排名问题：显示各科成绩的排名

2）topN问题：按照总成绩进行排名，并奖励班级前三名

窗口函数的语法规则如下：

分析函数 over (partition by [用于分组的列名] order by [用于排序的列名])

[分析函数]的位置，可以写以下两种函数：

1.排序函数，包括rank ,dense_rank ,row_number 等

2.聚合函数，如sum ,avg ，count ,max ,min等

3.解释下几个主要的排序函数：

1）rank()是跳跃排序，两个并列第二名下来就是第四名，即1,2,2,4,5

2）dense_rank()是连续排序，两个并列第二名后仍然跟着第三名，即1,2,2,3,4

3）row_number()是没有重复值的排序，可以利用它来实现分页，永远是1,2,3,4,5（即使原本的数据有重复值）

注意：因为窗口函数是对where或者group by子句处理后的结构进行操作，所以窗口函数原则上只能写在select子句中

over()才是窗口函数，而sum、row_number、count只是与over()搭配的分析函数（除了这三个函数之外，还有其他函数）

1.over()窗口函数的语法结构：

分析函数 over (partition by（相当于group by的作用） 用于分组的列名，order by 用于排序的列名，rows between 开始位置 and 结束位置)

2.over()函数中包括三个函数：包括分组 partition by列名，排序 order by列名，指定窗口范围 rows between开始位置 and 结束位置。在使用over()窗口函数时，over()函数中的这三个函数可组合使用，也可以不使用----这三个函数是可以自由组合的。

over()函数中如果不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据。

3.over()函数中的三个函数讲解：

1）order by是该窗口中的排序

2）partition by 可理解为group by分组，over(partition by 分组列名)搭配分析函数时，分析函数按照每一组每一组的数据进行计算

3）rows between开始位置 and 结束位置

是指定窗口范围，比如第1行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。

4.常于over()一起使用的分析函数：

1.聚合类：avg()、sum()、min()、max()

2.排名类:

1）row_number(）按照值排序时产生一个自增编号，不会重复（1,2,3,4,5,6）

2）rank()按照排序时产生一个自增编号，值相等时会重复，会产生空位（1、2、3、3、3，6）

3）dense_rank()按照值排序时产生一个自增编号，值相等时会重复，不会产生空位（1、2、3、3、3、4）

3.其他类函数：

1）lag(列名，往前的行数,[行数为null时的默认值，不指定为null])，可以计算用户上次购买时间，或者用户下次购买时间。

2）lead(列名，往后的行数，[行数为null时的默认值，不指定为null])。

3）ntile(n)把有序分区中的行分发到指定的数据的组中，各个组有编号，编号从1开始，对于每1行，ntile返回此行所属的组的编号。

（分析函数只作用于当前窗口）

窗口函数题目：

18 查询各科成绩前三名的记录(保留名次空缺)

select * from

(select sc.*,rank()over(partition by cid order by score desc)as 排名

from sc ) as t1

where 排名<=3

先进行子查询，找出各科成绩排名和成绩的表，然后在新表中筛选前3名。

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

select sc.*,rank() over(partition by cid order by score desc) as '排名'

from sc

保留名次空缺，使用rank函数，以课程编号cid分组，按分数score排名，rank函数在有重复值时使用跳跃排序，即有并列第二时，显示1,2,2,4,5

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

select sc.*,dense_rank() over (partition by cid order by score desc) as '排名'

from sc

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

select sid,sum(score),rank() over (order by sum(score desc)) as '排名'

from sc

group by sid

省略partition by子句就是不指定分组，只指定按总分排序

36.查询所有课程成绩第二名到第三名的学生信息及课程成绩

可以翻译为：查询所有课程成绩的学生信息和课程成绩，最后选择所有课程成绩在第二名和第三名的学生。(窗口函数只能出现在select子句中)

select student.* from student

left join (select score ,dense_rank() over (partition by cid order by score )as '排名' from sc) as t1 on student.sid=t1.sid

where '排名' in (2,3)

order by cid,'排名'

(最后按课程和成绩2名到3名进行排名)

第一步：使用子查询通过dense_rank函数查询排序情况，并且将查询结果保存为新表t1

第二步：通过学生编号sid连接学生表和新表t1

第三步：where条件筛选，查找排名为第二名和第三名的数据，（2,3）代表排名=2或排名=3，不是一个区间范围

最后用两个条件进行排序，先用cid排序（按各科成绩排序），再用排名排序（按各科成绩排名排序），排序默认规则为asc。

第四部分：子查询

知识要点与易错点总结：

什么情况下需要子查询？

1.用某个查询结果作为另一个查询的条件的时候

2.不能直接join，需要先统计出中间数据的时候

3.多表联合查询的时候或者是检索指定数据的时候

比较运算符：

http:// 1.in ：在范围内的值，只要有就是true

2.只用于子查询：any（和子查询返回的任何一个值比较为true，则返回true）

3.只用于子查询：all（和子查询返回的所有值比较为true，则返回true）

嵌套查询概述：

1.查询块：一个select语句称为一个查询块

2.嵌套查询：将一个查询块嵌套在另一个查询块的where子句中或having短语的条件中称为嵌套查询（即：在一个select语句中嵌套着另一个selsect语句

where condition

having condition）

比如：

查询选修2号课程的学生姓名

select sname

from student

where sno in------------黑色部分为外层查询/父查询

（select sno

from sc

where cno=2)-------红色部分为内层查询/子查

解释：典型的外部表现就是有个括号，但是否括号里面的查询就先做呢？有两种情况：

1.不相关子查询：内层查询与外层查询之间没有关联（子查询可以单独查询出结果，对外层查询的表没有依赖），内层查询里面返回多个值，括号前要加in----这种查询的执行方式是：从里向外逐层处理，即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其福查询的查找条件。

select sname

from student

where exists-------黑色部分为外层查询/父查询

(select*

from sc

where cno=2 and sno=student.sno)------内层查询/子查询----括号里面的内容与外层相关，在内层查询中没有表student，单独无法执行查询语句；

2.相关子查询：子查询的执行语句与外层查询相关，单独执行子查询无法得出结果（内层的执行结果依赖于外层）；首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若where子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组，重复这一过程，直至外层表全部检查完为止。

3.子查询的限制：

1）不能使用order by子句

2）层层嵌套方式反映了SQL语言的结构化

3）有些嵌套查询可以用连接运算替代

不相关子查询：子查询的查询条件不依赖于父查询

相关子查询：子查询的查询条件依赖于父查询

4.嵌套查询的谓词：

1）带有in谓词的子查询

2）带有比较运算的子查询

3）带有any 或all谓词的子查询

4）带有exists谓词的子查询

in,比较运算，any,all都是不相关子查询。

1.带有in谓词的子查询：

父查询和子查询之间用in进行连接，子查询的结果是一个集合。

比如：查询与“刘晨”在同一个系的学生

1）确定“刘晨”所在系名：

select sdept

from student

where sname='刘晨'----得到其所在系名为is系----信息系

2）查找所有在is系学习的学生

select sno,sname,sdept

from student

where sdept='is'

3）构造带有in谓词的子查询：

select sno,sname,sdept

from student

where sdept in

(select sdept

from student

where sname='刘晨')

将第一步放在子查询，将第二步放在外查询，注意：子查询返回的值是与sdept相匹配的，是sdept所有的取值都在后面子查询的结果里面。（父查询里面的筛选字段，要与子查询字面的查询字段保持一致）

用自身连接完成上面的查询要求

查询与“刘晨”在同一个系学习的学生

select s1.sno,s1.sname,s1.sdept

from student s1,student s2

where s1.sdept=s2.sdept and s2.sname='刘晨'

查询选修了课程名为“信息系统”的学生学号和姓名

select sno,sname

from student

where sno in

（select sno

from sc

where cno in

（select cno

from course

where cname='信息系统'））

思考步骤：

1.首先在course关系中找出“信息系统”的课程号，结果为3号

2.然后在sc关系中找出选修了3号的学生学号

3.最后在student关系中取出sno和sname

(在这种查询方式中嵌套了3层查询，这样的情况下用等值连接会比较方便)

等价的连接查询：

select sc.sno,sname

from student,sc,course

where cname='信息系统'

and sc.cno=course.cno

and student.sno=sc.sno

2.带有比较运算符的子查询：

使用范围：当能确切知道内层查询返回单值时，可用比较运算符（<,>,=,>=,<=,!=,<>,!>,<!）-----返回结果为单值时，可以用比较运算符，但是如果返回结果为多值时，要使用in

与any或all谓词配合使用

1）找出每个学生超过他选修课程平均成绩的课程号

select sno,cno

from sc as x

where grade>

(select avg(grade)

from sc as y

where y.sno=x.sno)

带有比较运算符的子查询，子查询一定要跟在比较符之后

错误的例子：

select sno,sname,sdept

from student

where (select sdept

from student

where sname='刘晨')=sdept

正确的写法：

select sno,sname,sdept

from student

where sdept=

(select sdept

from student

where sname='刘晨')

1.查其他系中比is系任意学生年龄小的学生姓名和年龄

select sname,sage

from student

where sage<any

(select sage

from student

where sdept='is')

and sdept<>'is'

1.首先在student关系中找出所有所在系为“is”人的年龄

2.最后在student关系中取出sname和sage

2.查询其他系中比is系所有学生年龄都大的学生姓名和年龄

select sname,sage

from student

where sage>all

(select sage

from student

where sdept='is')]

order by sage desc;

思路：

1.首先在student关系中找出所有所在系为“is”人的年龄

2.最后在student关系中取出sname和sage

4)带有exists谓词的子查询：exists：存在

1.带有exists谓词的子查询不返回任何实际数据

2.它值产生逻辑真值“true”或逻辑假值“false”

1）比如：查询所有没选修1号课程的学生姓名

select sname

from student

where not exists

(select *

from sc

where s.sno=sno and cno=1)

exists括号中的子查询的特点：select后面不带任何的列名，就接“*---全选”，where 条件中：子查询表中一列的列名，等于父查询中一列的列名，子查询中返回的结果是多行，值就为真，如果返回的结果是空值，结果就为假-------这种是属于相关子查询---查询步骤：先从student表中第一个学生，之后执行到where子句中执行子查询，查sc里面有没有学生学号与student表中学生学号相同，且选修课程号为1号，如果有，exists值即为真，not exists的值即为假；再从父查询的第二个学生循环此操作。

2）查询所有选修了1号课程的学生姓名：

select sname

from student

where exists

（select *

from sc

where s.sno=sno and cao=1）

4.集合查询：

标准sql直接支持的集合操作种类----并操作（union）

一般商用数据库支持的集合操作种类：

并操作（union）

交操作（intersect）

差操作（minius）

1)并操作：

[查询块]union[查询块]------前面一个查询，后面一个查询，中间用union连接，将两个查询的结果用union连接，两个查询的结果的结构是相同的，参加union操作的各结果表的列数必须相同，对应项的数据类型也必须相同。

查询选修1号课程和2号课程学生的学号

select sno from sc where cno=1

union

select sno from sc where cno=2

2)交操作：

标准sql中没有提供集合交操作，但可用其他方法间接实现

比如：

查询计算机科学系的学生与年龄不大于19岁的学生的交集(本例实际上就是查询计算机科学系中年龄不大于19岁的学生)

select *

from student

where sdept='cs'and sage<=19

4）对集合操作结果的排序：

1.order by子句只能用于对最终查询结果排序，不能对中间结果排序

2.任何情况下，order by子句只能出现在最后

3.对集合操作结果排序时，order by子句只能用数字指定排序属性

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

select student.*

from student,sc

where student.sid=sc.sid

(以学生id--sid为主键来链接两表，在学生成绩表中有学生学号就证明学生存在成绩)

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

select *

from student

left join sc on student.sid=sc.sid

(查询 )

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

查询内容：张三老师的教师代码----教师代码对应的课程代码----课程代码对应的科目代码----科目代码对应的学生学号代码----学生学号代码对应的学生信息

方法1：

使用等价查询：

select student.*

from student,sc,course,teacher

where student.sid=sc.sid

and sc.cid=course.cid

and course.tid=teacher.tid

and tname='张三'

方法2：

使用子查询：

select student.* from student where student.sid in

(select sc.sid from sc where sc.cid in

(select course.cid from course where course.tid=

(select teacher.tid from teacher where tname='张三')))

（使用带有in谓词的子查询，父查询里面的筛选字段，要与子查询字面的查询字段保持一致，父查询和子查询之间用in进行连接，子查询的结果是一个集合，最后一层子查询，使用比较运算符查询---当能确切知道内层查询返回单值时，可用比较运算符（<,>,=,>=,<=,!=,<>,!>,<!）-----返回结果为单值时，可以用比较运算符，但是如果返回结果为多值时，要使用in与any或all谓词配合使用-----------------写法：

select sno,sname,sdept

from student

where sdept= (select sdept from student where sname='刘晨')）

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

select student.* from student where student.sid not in

(select sc.sid from sc where sc.cid in

(select course.cid from course where course.tid=

(select teacher.tid from teacher where tname='张三')))

使用子查询----not in：总的思路是反向思考，即先找到所有上过张三老师课的学生，取反即为没上过课的学生，写SQL子查询的思路，越先查的东西越放在后面，第一步：在teacher表中查到张三老师的tid---第二步：在第一步中找到的tid，在course表中查到这个tid对应的cid，即这个老师教的可是哪一门---第三步：用第二步中找到的cid，在score表中查到学过这门课的学生sid---第四步：用第二步中找到的sid，在student表中查找对应学生，反向思考，没上过这门课就用not in

33.成绩不重复，查询选修[张三]老师所授课程的学生中，成绩最高的学生及其成绩

使用相等查询---查询到张三老师所教授课程的学生---张三老师的tid---在coursr表中找到tid对应的cid---在score中找到cid对应的sid---在student表中找到对应的学生，然后对这些学生的成绩进行降序排序，取最前面1行的值即为成绩最高的学生

方法1：（使用排序和限制行数）

select student.sid,student.sname,sc.score

from student,sc,course,teacher

where student.sid=sc.sid

and sc.cid=course.cid

and course.tid=teacher.tid

and tname='张三'

order by sc.score desc

方法2：使用max()函数，直接取最大值即可：------此处不可以使用student.sid进行分组，如果分组了，计算逻辑就变成了：先按学号对张三老师所教授的学生进行分组，然后计算每组内的最高成绩-----分组的计算规则是：先分组，再统计。

select student.sid,student.sname,max(distinct sc.score)

from student,sc,course,teacher

where student.sid=sc.sid

and sc.cid=course.cid

and course.tid=teacher.tid

and tname='张三'

多表联合查询，直接用where多条件筛选最快---使用max()函数，只返回成绩最高的第一条记录；方法1：使用成绩从高到低排序后，使用limit关键字返回第一条记录。

34.成绩有重复的情况下，查询选修[张三]老师所授课程的学生中，成绩最高的学生信息及其成绩

与前面几题的区别是，如果张三老师所授课程里有多个学生获得并列第一的成绩，需要全部找出来，此时用max(0函数只能找出其中一个，并且如果不知道到底有几个学生是并列第一的话，也没有办法使用limit。

方法：在上题基础上加一个子查询---查询到符合条件的最高分数是90分---然后将成绩等于90分的学生信息提取出即可

select student.sid,student.sname,sc.score

from student,sc,course,teacher

where student.sid=sc.sid

and sc.cid=course.cid

and course.tid=teacher.tid

and tname='张三'

and sc.score in

( select max(sc.score) as 最高分

from student,sc,course,teacher

where student.sid=sc.sid

and sc.cid=course.cid

and course.tid=teacher.tid

and tname='张三')

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

“没有学全所有课程”----可以先统计出cid个数为3(在course表中统计出所有的课程个数为3)，“没有学全所有课程”代表“该学生所对应的cid个数小于3----计算每个学生对应的cid（要先将cid按学生学号进行分组之后，再统计对应的cid个数）-----最后筛选出cid个数不等于3的学生的信息---分组之后再筛选使用having子句”

select student.*

from student,sc

where student.sid=sc.sid

group by student.sid

having count(cid)<>(select count(cid) from course)

39.查询选修了全部课程的学生信息

错误解法：

选取全体学生，筛选中排除掉没有学全所有课程的同学的信息(----这样的筛选方法，并不能得到选修了全部课程的学生，查询结果还包括了一门课程都没有选修的学生)

select student.*

from student

where student.sid not in

(select student.sid

from student,sc

where student.sid=sc.sid

group by sc.sid

having count(cid)<>(select count(cid) from course))

在查询结果中，8,9,10,11属于一门课程都没有修的学生

正确解答方法：

select student.*

from student,sc

where student.sid=sc.sid

group by sc.sid

having count(sc.cid)=(select count(course.cid) from course)

如何找全所有课程的学生？关键在于score表里的每个学生同时有3个cid(01/02/03),所以思路是对student表做如下过滤：

1.从course表中查找一共有几个cid(目前是3)，所以select count(course.cid) from course

2.从sc表中查找符合第一步中查到的cid count数量（目前是3）的学生的sid，所以having

3.从student表中查找到第二步中查到的sid的学生信息

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

1.“01”号同学学习的课程：

select cid from sc where sid='01'-----查询结果01/02/03

2.与01号同学不相同的学号的课程

select sid from sc where sid<>01and cid in(01,02,03)-----查询结果（02,03,04,05,06,07）

2.其他同学的信息

select student.* from student where sid in (02,03,04,05,06,07)

SQL多表查询是越后面的越先查询，所以要将最先找的表要放在最后面，所以：

1.从score表中查询sid=01的学生的所有cid

2.根据第一步找到的score表cid查询这些cid课的学生sid，同时sid不为01（排除掉sid=01的学生）

3.根据第二步找到的score表sid，去student表查询第二步找到的所有sid的学生信息

合并子查询：

select student.* from student

where student.sid in

(select sc.sid from sc where sc.sid<>01and sc.cid in

(select sc.cid from sc where sc.sid=01) )

35.查询不同课程课程成绩相同的学生的学生编号、课程编号、学生成绩

对同一字段执行了两次以上查询，可以用自身连接

自身连接+子查询：原样复制一遍成绩表，然后查询t1和t2中课程编号cid不同但是分数score相同的学生编号，最后再根据查询到的学生编号sid找到对应的学生的信息。

select * from sc where sid in

(select t1.sid

from sc as t1,sc as t2

where t1.sid=t2.sid

and t1.cid<>t2.cid

and t1.score=t2.score

方法2：

单独自身连接：

select distinct t1.sid ,t1.cid,t1.score

from sc as t1,sc as t2

where t1.sid=t2.sid

and t1.cid<>t2.cid

and t1.score=t2.score;-------cid和score的筛选条件，无论谁在前或谁在后对结果均无影响

方法3：

子查询+自身连接：使用join连接：

select * from sc where sid in

(select t1.sid from sc as t1

join sc as t2 on t1.sid =t2.sid

where t1.cid<>t2.cid

and t1.score=t2.score)

自身连接的形式与特点：

1.要对同一个表查询（操作）两次，首先要给这个表起别名，因为from后面是两张同样的表，系统无法区分。

(当from后面有多个表名时，一定要有条件将两个表连接起来)

2.select a1.cno,a2.cpno

from course as a1,course as a2

where a1.cpno=a2.cno

自身连接有个特点是：对同一个字段执行了两次查询

第五部分：case

知识要点与易错点总结：

case本质上就是if逻辑判断，可以对多种条件进行控制，有两种写法：

第一种简单case：case后面不带表达式

case when 条件1 then 结果1

when 条件２then 结果2

.............

else 结果n

end

第二种case搜索函数：case后面带表达式，此时when后面的表达式可能的值

case 表达式

when 条件的值1 then 结果1

when 条件的值2 then 结果2

............

else 结果n

end

注意：case函数只返回第一个符合条件的值，剩下的case部分将会被自动忽略

case 题目整理：

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

select a.cid,b.cname,

sum(case when a.score >=85 then 1 else 0 end) as '[100-85]',

sum(case when a.score >=70 and a.score<85 then 1 else 0 end) as '[85-70]',

sum(case when a.score>=60 and a.score<70 then 1 else 0 end) as '[70-60]',

sum(case when a.score<60 then 1 else 0 end) as '[60-0]'

from score as a ,course as b

where s.cid=b.cid

group by cid

(要统计各分数段人数，要用case+sum来完成，首先用case语句来创建分数段，比如第一个case语句，代表逐行扫描score表每一行数据，找到score>=85就加1（如果满足条件就被认为是1，不满足条件就是0，最后将所有的1和0相加得到的值，就是满足该分数段的学生人数），最后用sum函数来统计这个分数段有多少人，以此类推。为了显示课程名称，还要连接成绩表和课程表，最后用cid进行分组，返回三行结果，显示三门课程的各分数段人数)

14.查询各科成绩最高分、最低分和平均分：课程ID，课程name，最高分，最低分，平均分

成绩分段如下：A为优秀>=90，B为良好80-90，C为中等70-80，D为及格>=60，Fail为不及格<60

要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列】

select a.cid,b.cname,

count(a.sid) as '选修人数',

max(a.score) as '最高分',

min(a.score) as '最低分'，

avg(a.score) as '平均分',

sum(case when a.score>=90 then 1 else 0 end)/count(a.sid) as 'A',

sum(case when a.score>=80 and a.score<90 then 1 else 0 end)/count(a.sid) as 'B',

sum(case when a.score>=70 and a.score<80 then 1 else 0 end)/count(a.sid) as 'C',

sum(case when a.score>=60 and a.sore <70 then 1 else 0 end)/count(a.sid) as 'D',

sum(case when a.score<60 then 1 else 0 end)/count(a.sid) as 'fail'

from score as a,course as b

where a.cid=b.cid

group by a.cid

order by 选修人数 desc,cid asc;

计算百分比，比如及格率就是用case的结果/成绩表总人数，最后进行排序，先用选修人数降序排列，然后用课程编号cid升序排列。此题的a,b,c,d代表优秀率等。

第六部分：

mysql数据库的日期函数很多，一些常见的日期函数：

1.curdate()=current_date():返回当前年月日，比如：2020-04-28

2.curtime()=current_time():返回当前时间时分秒，比如：22:10:51

3.dateiff()：计算两个日期相差多少天，有两个参数：

第一个参数：begin date

第二个参数：end date

4.timestampdifff:在datetime表达式上减去一段时间，允许其参数具有混合类型（date或datetime都可以），有三个参数：

第一个参数unit，是确定end-begin结果的单位，可以是year，month,week,day等

第二个参数：begin date

第三个参数：end date

5.date():从date或者datetime表达式中提取出日期部分

6.week():返回日期参数是一年中的第几周，有两个参数：

第一参数date，即需要查询星期数的日期

第二参数mode是可选参数，用于指定一周起始于周日还是周一，以及返回值的范围应该是0到53，还是1到53.

7.month():返回日期参数的月份

8.year():返回日期参数中的年份

9.str_to_date():将字符串转换为日期数据

10.weekofyear()函数：给出一个日期参数，返回结果是该日期是那一年的第几周

题目总结：

40.查询各学生的年龄，只按年份来算

取“当前时间的年份”与“学生出生日期的年份”相减

select sid,sname,(year(curdate()-year(sage))as '年龄'

from student

注意：year()是一个函数，curdate()是函数，在写sql时括号要对应；

44.查询本月过生日的学生

筛选条件：当前日期的月份（）=学生生日的月份（）---返回月份用：month()函数；当前日期用curdate():函数是可以嵌套使用的

select sid,sname

from student

where month(curdate())=month(sage())

45.下月过生日的学生

下月生日：用当前时间的月份+1=生日的月份

select sid,sname

from student

where month(curdate())+1=month(sage)

24.查询1990年出生的学生名单

筛选条件：year(sage)=1990

select sid,sname

from student

where year(sage)=1990

其他:

like运算符：

1.常与通配符%和_配合使用

2.%代表任意个字符，可以是0个，1个或多个

3._代表1个字符，可以把_当成占位符

5.查询[李]姓老师的数量

select tid,tname,count(tid)

from teacher

where tname like'%李%'

22.查询名字中含有[风]字的学生信息

select *

from student

where sname like'%风%'