sql题目

一、选择题

（1）基础题

1、要求删除商品表中价格大于3000的商品，下列SQL语句正确的是（）

A、DELETE FROM 商品 WHERE 价格>3000

B、DELETE * FROM 商品 WHERE 价格>3000

C、DELETE FROM 商品

D、UPDATE 商品 SET * =NULL WHERE 价格>3000

正确答案: A

答案解析：这道题考察的是delete语法

DELETE FROM table_name

WHERE some_column=some_value;

2、在book表中，将工具书类型（tool）的书的书架序号都减少2，下列语句正确的是（）

A、UPDATE books SET shelf = shelf - 2 WHERE type IS ‘tool’;

B、INSERT books SET shelf = shelf - 2 WHERE type IS ‘tool’;

C、UPDATE books SET shelf = shelf - 2 WHERE type = ‘tool’;

D、UPDATE books INTO shelf = shelf - 2 WHERE type = ‘tool’;

正确答案: C

答案解析：这道题考察的是update语法。

UPDATE table_name

SET column1=value1,column2=value2,…

WHERE some_column=some_value;

where后面语句，查找NUll值的时候可以用 type IS NUll，否则要用等于=

3、为职员表添加列，列名为年末奖金，允许为空值,数据类型为货币数据类型。下列SQL语句正确的是（）

A、ATER TABLE 职员 ADD 年末奖金 NULL

B、ALTER TABLE 职员 ADD 年末奖金 Money NULL

C、ALTER TABLE 职员 ADD 年末奖金 Money NOT NULL

D、ALTER TABLE 职员 ADD 年末奖金 INT NULL

正确答案: B

答案解析：这道题考察的是alter语法。如果你想在已有的表中，添加、删除或修改列，就需要用到alter语句。

添加列： ALTER TABLE table_name ADD column_name datatype

修改列：在SQL Server中与MySQL中修改列的语句有所差异，这里要注意~

My SQL / Oracle：ALTER TABLE table_name MODIFY COLUMN column_name datatype

SQL Server：ALTER TABLE table_name ALTER COLUMN column_name datatype

删除列：ALTER TABLE table_name DROP COLUMN column_name

mysql没有money类型 sql server才有money类型，所以在读题的时候要注意是否提到在mysql中。

money其实跟float是同类型数据。money类型只是显示在数据表的时候前面多加一个$样式的符号。

4、小李在创建完一张数据表后，发现少创建了一列，此时需要修改表结构，应该用哪个语句进行操作？

A、MODIFY TABLE

B、INSERT TABLE

C、ALTER TABLE

D、UPDATE TABLE

正确答案: C

答案解析：巩固一下上一题，修改已有的表，就用alter语句。

ALTER TABLE 表名 add 字段名

5、SQL语言可以分为多个类别，那么不属于数据操纵语言DML的是（）

A、update

B、grant

C、delete

D、insert

正确答案: B

答案解析：grant语句是用来赋予用户权限的，是数据控制语言（DCL）。其他3个都是数据操纵语言（DML）

数据查询语言（DQL）：是由SELECT子句，FROM子句，WHERE子句组成的查询块

数据操纵语言（DML）: SELECT(查询) INSERT(插入) UPDATE(更新) DELETE(删除）

数据定义语言（DDL）：CREATE(创建数据库或表或索引）ALTER(修改表或者数据库）DROP(删除表或索引）

数据控制语言（DCL）：GRANT(赋予用户权限） REVOKE(收回权限） DENY(禁止权限)

事务控制语言（TCL）：SAVEPOINT (设置保存点）ROLLBACK (回滚) COMMIT(提交)

6、下列选项中使用别名的方法不正确的是（）

A、字段名称=别名

B、字段名称 AS 别名

C、字段名称 别名

D、别名=字段名称

正确答案: D

答案解析：给字段名称起别名，B、C选项没有疑义，as可以省略

A选项，字段名称=别名，在SQL Server中可以这样使用，MySQL中则不可以。

如果没有特别指明是在mysql中，我们默认就在SQL Server中。

（多选题）7、在MySql中进行数据查询时，如果要对查询结果的列名重新命名，将sno列重新命名为学号，则下列语句正确的是（ ）

A、select sno as 学号 from T

B、select 学号= sno from T

C、select sno 学号 from T

D、select sno=学号 from T

正确答案: A C

答案解析：巩固一下上一题，在SQL Server 中可以使用’=’，MySQL中则不可以

8、在SQL中用条件表示价格在在30至40之间，应该如何表达？

A、in (30,40)

B、BETWEEN 30 AND 40

C、BETWEEN 30 OR 40

D、BETWEEN 30 TO 40

正确答案: B

答案解析：这道题考察的是BETWEEN … AND …知识点

9、SQL语句中与Having子句同时使用的语句是？（）

A、Group By

B、联盟链

C、left Join

D、Where

正确答案: A

答案解析：having和where作用差不多，都是用来筛选数据的。

having作用在分组之后，也就是要放在group by的后面

10、已知数据表STU，现需创建视图view_s，显示所有男同学的信息。下列SQL语句正确的是（）

A、CREATE VIEW AS SELECT * FROM STU

B、CREATE VIEW view_s AS SELECT * FROM STU WHERE 性别=‘男’

C、CREATE view_s SELECT * FROM STU WHERE 性别=‘男’

D、CREATE view_s AS SELECT * FROM STU

正确答案: B

答案解析：这道题考察的是VIEW 创建视图知识点

将查询语句放在create views view_name as 后面，这样查询语句的结果就会存储在视图中，后面可以继续调用这个视图数据，或者直接在视图中查询。

视图可以当成表格来用。

CREATE VIEW view_name AS

SELECT column_name(s) FROM table_name

WHERE condition

CREATE后面要加上VIEW，不然数据库怎么知道你要创建啥~

11、Mysql中表student_info(id,name,birth,sex)，字段类型都是varchar，插入如下记录：(‘1014’ , ‘张三’ , ‘2002-01-06’ , ‘男’); 下面SQL错误的是（）？

A、insert into student_info values(‘1014’ , ‘张三’ , ‘2002-01-06’ , ‘男’);

B、insert into table student_info values(‘1014’ , ‘张三’ , ‘2002-01-06’ , ‘男’);

C、insert into student_info(id,name,birth,sex) values(‘1014’ , ‘张三’ , ‘2002-01-06’ , ‘男’);

D、insert into student_info(id,name,sex,birth,) values(‘1014’ , ‘张三’ , ‘男’,‘2002-01-06’ );

正确答案：B

答案解析：这道题比较简单，考察的知识点是：insert插入数据的语法。

insert into 后面直接跟表名。

insert into table，这种语法就不对。

有些同学可能会选择D，觉得顺序不对。但前面指明的列顺序，与后面是对应的(id,name,sex,birth,) values(‘1014’ , ‘张三’ , ‘男’,'2002-01-06’ )

12、将成绩表（grade）按成绩（point）升序排列，下列语句错误的是（）

A、SELECT * FROM grade ORDER BY point;

B、SELECT point FROM grade ORDER BY point;

C、SELECT * FROM grade ORDER BY point ASC;

D、SELECT * FROM grade ORDER BY point DESC;

正确答案: D

答案解析：这道题考察的是group by语句中是升序，降序

A、B选项没有特意指定，默认是升序。

ASC 表示升序，DESC 表示降序，所以D是错的

（2）进阶题

1、Mysql中表student_table(id,name,birth,sex)，插入如下记录：

(‘1001’ , ‘’ , ‘2000-01-01’ , ‘男’);

(‘1002’ , null , ‘2000-12-21’ , ‘男’);

(‘1003’ , NULL , ‘2000-05-20’ , ‘男’);

(‘1004’ , ‘张三’ , ‘2000-08-06’ , ‘男’);

(‘1005’ , ‘李四’ , ‘2001-12-01’ , ‘女’);

查询2001年及之后出生的男生、女生总数，正确的SQL是（）？

A、select sex,count() from student_table group by sex where birth >=‘2001’ ;

B、select count() from student_table where birth >=‘2001’ group by sex ;

C、select sex,count() from student_table where birth >=‘2001’ group by sex ;

D、select sex,count() from student_table group by sex having birth >='2001’ ;

正确答案: C

答案解析：这道题考察的是select语句中group by分组知识点。

where应该放在group by 的前面，排除A

group by后面的字段都要在select后面出现，B选择中的sex，没有在select后面出现，排除B

Having后面可以跟字段和聚合函数，having后面的字段，也是只能使用在select后面出现的字段。排除D

2、某IT公司人事管理采用专门的人事管理系统来实现。后台数据库名为LF。新来的人事部张经理新官上任，第一件事是要对公司的员工做全面的了解。可是他在访问员工信息表EMPL里的工资和奖金字段的时被拒绝，只能查看该表其他字段。作为LF的开发者你将如何解决这一问题：（ ）

A、废除张经理的数据库用户帐户对表EMPL里的工资列和奖金列的SELECT权限

B、添加张经理到db_datareader角色

C、添加张经理到db_accessadmin角色

D、授予张经理的数据库用户帐户对表EMPL里的工资列和奖金列的SELECT权限。

正确答案: D

db_accessadmin

可以添加、删除用户的用户

db_datareader

可以从所有数据表中读取所有数据，人事经理只是想访问员工信息表，不需要给这么大权限。

3、下列函数语句得不到相同数值结果的选项是（）

A、SELECT ROUND(2.35)

B、SELECT ROUND(1.96,1)

C、SELECT TRUNCATE(1.99,1)

D、SELECT TRUNCATE(2.83,0)

正确答案: C

ROUND() 函数用于把数值字段舍入为指定的小数位数，第二个参数表示保留几位小数。ROUND(2.35)结果为2，ROUND(1.96,1)结果为2.0

TRUNCATE() 函数是按照小数位数进行数值截取，没有四舍五入。第二个参数表示保留几位小数。TRUNCATE(1.99,1)结果为1.9，TRUNCATE(2.83,0)结果为2

4、在STUDENT表中按class_type统计数据行数分组情况后，筛选出数据行数为大于10行的组

A、SELECT class_type，COUNT（） FROM STUDENT GROUP BY class_type HAVING COUNT()>10

B、SELECT class_type，COUNT（） FROM STUDENT GROUP BY class_type WHERE COUNT()=10

C、SELECT class_type，COUNT（） FROM STUDENT HAVING COUNT()>10 GROUP BY class_type

D、SELECT class_type，COUNT（） FROM STUDENT WHERE COUNT()>10 GROUP BY class_type

正确答案：A

答案解析：这道题是根据class_type分组后，找出每组大于10行的数据，考察的知识点是：where和having的区别。

where和having作用差不多，都是用来筛选数据的。

having作用在分组之后，也就是要放在group by的后面，where放在group by的前面。这样就排除了B、C

count(*)是聚合函数，意思是查询每组有多少条数据记录。having后面可以跟聚合函数，where后面不能跟聚合函数。排除了D

5、查询语句select stuff(‘lo ina’,3, 1, ‘ve ch’)结果为？

A、love

B、love china

C、china love

D、china

正确答案: B

答案解析：stuff(原字符, 开始位置, 删除长度, 插入字符)

stuff函数是从指定的起点处开始删除指定长度的字符，并在此处插入另一组字符

6、已知某期刊信息库中有作家信息表author（作者编号aid，作者姓名aname，作者笔名ausername，作者邮箱aemail，备注remarks），稿件表manuscript（稿件编号mid，标题mtitle，作者编号aid，交稿时间mtime）现需要设置外键作者编号，下列语句正确的是（）

A、ALTER TABLE manuscript

ADD CONSTRAINT FK_aid

FOREIGN KEY (aid) REFERENCES author (aid)

B、ALTER TABLE manuscript

ADD CONSTRAINT FK_aid

FOREIGN KEY (manuscript.aid) REFERENCES author (aid)

C、ALTER TABLE manuscript

ADD FOREIGN KEY (manuscript.aid)

REFERENCES author (aid)

D、ALTER TABLE manuscript

ADD FOREIGN KEY (aid)

REFERENCES author (aid)

正确答案: B

答案解析：这道题考察的是设置外键的语法。

alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

（多选题）7、使用SQL语句建个存储过程proc_stu，然后以student表中的学号Stu_ID为输入参数@s_no，返回学生个人的指定信息。下面创建存储过程语句正确的是：（ ）

A、CREATE PROCEDURE [stu].[proc_student]

@s_no AS int

AS

BEGIN

select * from stu.student where Stu_ID=@s_no

END

B、CREATE PROCEDURE [stu].[proc_student]

@s_no int

AS

BEGIN

select * from stu.student where Stu_ID=@s_no

END

C、CREATE PROCEDURE [stu].[proc_student]

@s_no int

AS

BEGIN

select * from stu.student where s_no=@s_no

END

D、CREATE PROCEDURE [stu].[proc_student]

@s_no AS int

AS

BEGIN

select * from stu.student where Stu_ID=@Stu_ID

END

正确答案: A B

答案解析：这道题主要考的是where后面跟的参数怎样赋值。where 表字段=参数。Stu_ID是表格字段，@s_no是参数。

8、请取出 BORROW表中日期(RDATE字段)为当天的所有记录？(RDATE字段为datetime型，包含日期与时间)。SQL语句实现正确的是：（ ）

A、select * from BORROW where datediff(dd,RDATE,getdate())=0

B、select * from BORROW where RDATE=getdate()

C、select * from BORROW where RDATE-getdate()=0

D、select * from BORROW where RDATE > getdate()

正确答案：A

答案解析：这道题考察的是datediff() 函数。datediff() 函数的意思是返回两个日期之间的时间差。

datediff(dd,RDDATE,getdate())==0是意思是表中日期（RDDATE）与当前日期（getdate()）这两个日期相同，就为true。就会取出这天的记录。

dd是日，ww是周，mm是月，hh小时，mi分钟，ss秒

9、Mysql中表student_table(id,name,birth,sex)，插入如下记录：

(‘1001’ , ‘’ , ‘2000-01-01’ , ‘男’);

(‘1004’ , ‘张三’ , ‘2000-08-06’ , ‘男’);

(‘1005’ , NULL , ‘2001-12-01’ , ‘女’);

(‘1006’ , ‘张三’ , ‘2001-12-02’ , ‘女’);

执行

select t1.name from

(select * from student_table where sex = ‘女’)t1

left join

(select * from student_table where sex = ‘男’)t2

on t1.name = t2.name;

的结果行数是（）？

A、4

B、3

C、2

D、1

正确答案：C

答案解析：这道题考察的是left join 左连接，

左连接就是不管有没有符合条件的数据，都会将左边表的数据全部展示。

这里左边的表是t1 ，查出了所有性别为女的数据，就是两条。后面不管跟上面查询条件，这两条数据都会显示出来。

（多选题）10、下面有关sql 语句中 delete、truncate的说法正确的是？（）（多选题）

A、论清理表数据的速度，truncate一般比delete更快

B、truncate命令可以用来删除部分数据。

C、truncate只删除表的数据不删除表的结构

D、delete能够回收高水位(自增ID值)

正确答案：A、C

答案解析：这道题考察的是trustcate与delete的区别

在处理效率方面：drop>trustcate>delete

删除记录方面：drop删除整个表、trustcate删除全部记录但不删除表、delete删除部分记录高高水线方面delete不影响所用extent，高水线保持原位置不动；trustcate会将高水线复位。

11、检索销量表中销量最好的商品id和销量，下列SQL语句正确的是（）

A、SELECT 商品id,销量 FROM 销量表 WHERE 销量=MAX(销量)

B、SELECT 商品id,MAX(销量) FROM 销量表 GROUP BY 销量

C、SELECT 商品id,MAX(销量) FROM 销量表 GROUP BY 商品id

D、SELECT 商品id,销量 FROM 销量表 WHERE 销量=(SELECT MAX(销量) FROM 销量表）

正确答案：D

答案解析：最后表格输出的结果最好的商品id和销量，

A选项语法错了，where后面不能跟聚合函数，having后面可以跟

B选项语法错了，group by后面的字段，需要再select后面字段中提到

C选项把商品id字段分类，再对销量取最大值，实际作用是将商品id去重+查询商品id和对应销量,如果分类字段没有重复数据，效果等同‘ select * from 销量表 ’,不符合题意。

D选项是对的，先用子查询找到最大的销量的具体值，在找对应具体值的商品名。

12、Mysql中表student_table(id,name,birth,sex)，查询不重复的姓名总数，错误的是（）？

A、select count(distinct name) from student_table ;

B、select count(name) from (select distinct name from student_table) t1

C、select count(name) from (select name,count(*) as c1 from student_table group by name having c1 > 1)t1

D、select count(name) from (select name from student_table group by name) t1;

正确答案：C

答案解析：因为我们要查询不重复的表名，having c1>1这里错了，c1 >1不就相当于有多个姓名，这里应该是c1 = 1。

（多选题）13、在SQL中语法规范中，having子句的使用下面描述正确的是：（ ）

A、having子句即可包含聚合函数作用的字段也可包括普通的标量字段

使用having的同时不能使用where子句

B、having子句必须于group by 子句同时使用，不能单独使用

使用having子句的作用是限定分组条件

C、having子句和where子句是等同的

D、having子句后面必须使用聚合函数

正确答案: A C

答案解析：

D选项，group by的作用限定分组条件，不是having

E选项：having子句和where子句作用差不多，但是也有区别，并不是等同的。

F选择：没有函数，也可以使用having，但是要注意，having里面包含的字段，必须在select语句中提到。

14、假设创建新用户nkw，现在想对于任何IP的连接，仅拥有user数据库里面的select和insert权限，则列表语句中能够实现这一要求的语句是（)

A、grant select ,insert on . to ‘nkw’@’%’

B、grant select ,insert on user.* to ‘nkw’@’%’

C、grant all privileges on . to ‘nkw’@’%’

D、grant all privileges on user.* to ‘nkw’@’%’

正确答案: B

答案解析：这道题考察知识点是数据库授权命令：

GRANT<权限> on 表名(或列名) to 用户

补充知识点-回收权限

REVOKE <权限> on 表名(或列名) FROM 用户

15、下列关于数据库系统三级模式结构的表述正确的是（）

A、内模式是面向数据库用户或应用程序的局部数据视图

B、索引的组织方式是B+树索引，还是Hash索引与数据库的内模式有关

C、逻辑模式是数据库在逻辑级上的视图，涉及数据的物理存储细节

D、外模式/模式映像保证了数据库具有较高的物理独立性

正确答案: B

答案解析：这道题主要考察的是数据库的设计步骤，以及对应的内模式、外模式、逻辑模式

数据库设计步骤主要分为以下几方面:

1）需求分析，需求分析是通过用户需求，将数据流程图、数据字典描述出来

2）数据库设计，这种设计和具体的数据库管理系统没有之间关联，而是抽象出各用户所需要的数据视图，对应外模式概念

3）将用ER模型或对象模型表示的数据视图，转换为关系模式，并对所得关系模式进行优化处理，这就是所谓的数据库逻辑设计（这一步仍然对应于外模式和模式）

4）第四步，在逻辑设计的基础上，将所得的数据模式组织存储到物理介质上，这就是数据库的物理设计（这一步对应于内模式）

16、Mysql(版本8.0.25)中表student_table(id,name,birth,sex)，插入如下记录：

(‘1004’ , ‘张三’ , ‘2000-08-06’ , ‘男’);

(‘1005’ , NULL , ‘2001-12-01’ , ‘女’);

(‘1006’ , ‘张三’ , ‘2000-08-06’ , ‘女’);

(‘1007’ , ‘王五’ , ‘2001-12-01’ , ‘男’);

(‘1008’ , ‘李四’ , NULL, ‘女’);

(‘1009’ , ‘李四’ , NULL, ‘男’);

(‘1010’ , ‘李四’ , ‘2001-12-01’, ‘女’);

执行

select count(t2.birth) as c1

from (

select * from student_table where sex = ‘男’ ) t1

full join

(select * from student_table where sex = ‘女’) t2

on t1.birth = t2.birth and t1.name = t2.name ;

的结果行数是（）？

A、2

B、3

C、执行报错

D、4

正确答案: C

答案解析：mysql不支持full join，应该用outer join

17、积分result表中有A B C D四列，要求：

1）当A列值大于等于B列时，选择A列否则选择B列

2）当C列值大于等于D列时，选择C列否则选择D列

用SQL语句实现正确的是：（ ）

A、select ( when A>=B then A else B ) MAX_AB, ( when C>=D then C else D ) MAX_CD from result

B、select (case when A>=B then A else B ) MAX_AB, (case when C>=D then C else D ) MAX_CD from result

C、select (case when A>=B then A else B end) MAX_AB, (case when C>=D then C else D end) MAX_CD from result

D、select case when A>=B then A else B end MAX_AB, case when C>=D then C else D end MAX_CD from result

正确答案: C

答案解析：case when 判断条件 then 成功结果 then 不成功结果 end

18、Mysql中表student_table(id,name,birth,sex)，插入如下记录：

(‘1001’ , ‘’ , ‘2000-01-01’ , ‘男’);

(‘1002’ , null , ‘2000-12-21’ , ‘男’);

(‘1003’ , NULL , ‘2000-05-20’ , ‘男’);

(‘1004’ , ‘张三’ , ‘2000-08-06’ , ‘男’);

(‘1005’ , ‘李四’ , ‘2001-12-01’ , ‘女’);

执行 select * from student_table where length(name) >= 0 的结果行数是（）？

A、1

B、2

C、3

D、4

正确答案：C

答案解析：1001和1002是不一样的，一个是空字符串，一个是NULL。针对这两种数据使用length()函数结果是不相同的。

针对1001 length(name)结果是0，针对1002、1003 length(name)结果是空值NULL

因此题干where length(name) >= 0 会筛选出 1001、1004、1005三条数据

19、有一张Person表包含如下信息：

Id Name Address Career

1 Bob China Town Chef

2 Carter Oxford Street Teacher

3 Anna Fourteen Avenue Dancer

现要选取居住地址Address不以’C’或’O’开头的人员信息，下列MySQL查询语句正确的是：

A、SELECT * FROM Person Address REGEXP ‘[CO]’;

B、SELECT * FROM Person Address LIKE ‘[!CO]%’;

C、SELECT * FROM Person Address LIKE ‘[^CO]%’;

D、SELECT * FROM Person Address REGEXP ‘1’;

正确答案: A

答案解析：此题考的是SQL里的正则表达式查询：

SELECT * FROM Person Address REGEXP ‘^[^CO]’;

[^]匹配不在括号中的任何字符，’[^CO]‘匹配任何不包含’C’或’O’的字符，其实就是匹配除了’C’或’O’之外的所有字符。

^匹配文本的开始字符，’^[^CO]’ 匹配不以’C’或’O’之外开头的所有字符。

20、”确保事务可以多次从一个字段中读取相同的值，在此事务持续期间，禁止其他事务对此字段的更新”是对下列选项哪一个事务隔离级别的描述（）

A、Read uncommitted

B、Read committed

C、Repeatable Read

D、Serializable

正确答案：C

答案解析：这道题考察的是4个事务隔离级别，面试中经常考。

确保事务可以多次从一个字段中读取相同的值，就是要保证可重复读取，可重复读取的意思是，一端在读取数据的同时，另一端在修改数据。在高并发环境中，很容易出现，读取数据的一方，两次读取结果不一致的情况。

要防止这种情况，就是要在读取数据事务存续时，禁止写事务，用人话说就是“可读，不可修改”。这样保证重复读取数据的结果是一样的。

这一事务级别就是可重复读取（Repeatable Read）。

具体的4个事务隔离级别，可以查看问答题第12题

21、关于解决事务的脏读的最简单的方法，下列选项正确的是（）

A、修改时加排他锁，直到事务提交后释放，读取时加共享锁

B、读取数据时加共享锁，写数据时加排他锁，都是事务提交才释放锁

C、修改时加共享锁，直到事务提交后释放，读取时加排他锁

D、读取数据时加排他锁，写数据时加共享锁，都是事务提交才释放锁

正确答案: A

答案解析：共享锁是立即释放的。排除B、D

脏读是意思是一个数据还没有提交，就会出现在读取结果中，万一数据因为异常原因没有成功提交，查询结果中就会出现不存在的数据。可以通过在修改时加“排他写锁”实现。

二、问答题

1、如何优化MySQL？

按照以下顺序优化：

优化查询语句

优化索引、事务处理

优化数据表结构

优化系统配置

优化硬件

2、如何优化SQL查询语句？

（1）对查询进行优化，首先应尽量避免全表扫描，在比较频繁使用的字段上面加上索引。

（2）尽量避免在 where 子句中对索引列使用计算或者进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

（3）Where子句中：where表之间的连接必须写在其他 Where 条件之前，那些可以过滤掉最大数量

（4）用EXISTS替代IN，用NOT EXISTS替 NOT IN。

3、什么情况下设置了索引但无法使用？

（1）在 where 子句中使用 or 来连接没有同时使用索引的条件,会使引擎放弃使用索引而进行全表扫描

（2）在 where 子句中对索引列使用计算或者进行 null 值判断

（3）在 where 子句中，使用以“%”开头的 LIKE 语句，进行模糊匹配

（4）数据类型出现隐式转化（如 varchar 不加单引号的话可能会自动转换为 int 型）

4、锁的优化策略？

（1）多个线程尽量以相同的顺序去获取资源。

（2）不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

（3）尽量减少锁持有的时间

（4）分段加锁

（5）读写分离

5、索引对数据库系统的负面影响是什么？

索引需要占用物理空间，增加数据库的大小，降低正常的运行速度

当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

6、什么情况下不适合建立索引？

（1）对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。

（2）对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

7、为数据表建立索引的原则有哪些？

（1）在最频繁使用的、用以缩小查询范围的字段上建立索引

（2）不应该基于表来创建索引，应该基于查询来创建索引

8、索引的底层实现原理和优化？

索引的底层实现原理是B+树，优化是经过优化的 B+树。主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

9、简单描述 MySQL 中索引、唯一索引、主键、联合索引的区别，对数据库的性能有什么影响（从读写两方面）？

索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

（1）普通索引的唯一任务是加快对数据的访问速度，允许被索引的数据列包含重复的值。

（2）唯一索引中，每条数据记录都是唯一的，创建唯一索引用关键字 UNIQUE

（3）主键是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY来创建。

（4）联合索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。

10、什么是事务

数据库事务( transaction)是一个数据库操作序列，这些操作要么全部成功,要么全部失败，是一个不可分割的工作单位。

11、什么是数据库的ACID原则

这个是数据库事务标准、ACID，表示原子性(Atomicity) 、一致性(Consistency)、隔离性(Isolation)、持久化(Durability)。

（1）原子性是指在一个数据库事务中所有的操作要么全部都做完，要么全部都不做。

（2）一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏，比如小明有1500元，小红有100元，小明给小红转账100元，转账完成后，小明和小红一共有1600元这个不能变。

（3）隔离性：各事务之间的操作不受影响

（4）持久化：事务一旦成功提交成功后，所有这个事务对数据库的更改全部被保留下来

12、4个事务隔离级别，分别是什么？

隔离级别从低到高。

（1）未授权读取（Read Uncommitted）：允许脏读，脏读的意思是，一个数据还没有提交，就会出现在读取结果中，万一数据因为异常原因没有成功提交，查询结果中就会出现不存在的数据。该隔离级别可以通过“排他写锁”实现。

（2）授权读取（Read Committed）：允许不可重复读取，但不允许脏读。不可重复读取的意思是，允许一端在读取数据的同时，另一端在修改数据，读取数据的一方重复读取时，数据结果不同是被允许的。可以通过“瞬间共享读锁”和“排他写锁”实现。

（3）可重复读取（Repeatable Read）：禁止不可重复读取和脏读，但是有时可能出现幻读。幻读的意思是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的数据行。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

（4）序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

13、SQL中的视图是什么？

视图可以当成表格来用，可以将查询结果存储在视图中，后面可以直接调用这个视图数据，或者继续在视图中查询。

查询语句放在create views 后面，就可以建立视图

14、视图的优点有哪些？

（1）视图可以限定访问表格的列。

（2）视图可以访问多个表格。

15、什么是存储过程？用什么来调用？

存储过程是一组SQL语句，用作访问数据库的函数。如果将sql代码与Python代码写在一起，或很混乱并且难以维护，用存储过程来代替，管理维护更加方便。

可以用一个命令对象来调用存储过程。

16、什么是Trigger（触发器）？

触发器是在插入、更新、删除语句前后自动执行的一堆SQL代码，用于对表执行特定操作。

比如在交易表中添加一条交易，在日志表中就自动添加一条记录，这样就要用到触发器。

17、什么是事件

事件是在特定时刻自动执行任务，操作数据库。

比如我们想在每天都执行删除过期日志，就可以创建一个事件，在每天特定时刻来自动执行这一操作。

18、怎样创建事件？

用 create event 语句来创建事件。

create event 事件名称

on schedule

every 1 year

do begin

删除语句

end

19、下面这条语句怎么优化，MySQL 如何优化 DISTINCT？比如优化 SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

在数据量比较大的时候，使用distinct函数加索引列，会使索引失效，并扫描全表。

这个时候，应该将 DISTINCT 在所有列，都转换为 GROUP BY

SELECT t1.a FROM t1,t2 WHERE t1.a=t2.a GROUP BY t1.a;

20、char和varchar的区别

CHAR 和 VARCHAR 类型在存储和检索方面有所不同

存储方面：CHAR 列长度固定，存储为创建表时声明的长度，如果插入的长度小于定义长度，则可以用空格进行填充。而varchar是一种可变长度的类型，当插入的长度小于定义长度时，插入多长就存多长。

查找效率：char查找效率会很高，varchar查找效率会更低

21、如何通俗地理解三个范式，说说范式化设计优缺点？

第一范式(1NF)：是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式(2NF)：是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式(3NF)：是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

范式化的优点: 可以尽量得减少数据冗余，使得更新快，体积小。

缺点：对于查询需要多个表进行关联，减少写操作的效率和增加读操作的效率，更难进行索引优化

22、MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年,怎么优化？

（1）设计良好的数据库结构，不要过度颗粒化，也不要使数据过渡冗余。

（2）选择合适的表字段数据类型和存储引擎，适当的添加索引。

（3）MySQL 库主从读写分离。

（4）找规律分表，减少单表中的数据量提高查询速度。

（5）添加缓存机制，比如 memcached，apc 等。

（6）不经常改动的页面，生成静态页面。

23、窗口函数是什么？和普通聚合函数的区别有哪些？

窗口函数，也叫OLAP(Online Anallytical Processing,联机分析处理）,可以对数据库数据进行实时分析处理

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

（1）聚合函数是将多条记录聚合为⼀条；窗⼝函数是每条记录都会执行，不会改变记录的行数

（2）聚合函数也可以⽤于窗⼝函数。

24、专用窗口函数有哪些？说一说基本用法

专用窗口函数有：rank、dense_rank、row_number

基本语法：‹窗口函数› over (partition by ‹用于分组的列名› order by ‹用于排序的列名›)

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

（1）partition by子句：窗口按照哪些字段进⾏分组,窗⼝函数在不同的分组上分别执⾏

（2）order by子句：按照哪些字段进⾏排序,窗⼝函数将按照排序后的记录顺序进⾏编号

（3）frame子句：frame是当前分区的⼀个子集,子句⽤来定义子集的规则,通常⽤来作为滑动窗⼝使⽤

（4）window_name:给窗口指定⼀个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读

25、使用窗口函数需要注意什么

窗⼝函数的执⾏顺序是在FROM，JOIN，WHERE， GROUP BY，HAVING之后的

所以如果这些语句，需要用到窗口函数作为条件，需要在窗口函数外面套⼀层查询

选择题和问答题都更新完了，有不完善的地方可以留言我们一起探讨~

三、sql语句题

创建表格代码

# Student 学生表
CREATE TABLE Student
(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL, 
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_id)
);

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', '女');


# Course 课程表
CREATE TABLE Course
(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

INSERT INTO Course VALUES('01', '语文', '02');
INSERT INTO Course VALUES('02', '数学', '01');
INSERT INTO Course VALUES('03', '英语', '03');


# Teacher 教师表
CREATE TABLE Teacher
(
t_id VARCHAR(20),
t_name VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(t_id)
);

INSERT INTO Teacher VALUES('01', '张三');
INSERT INTO Teacher VALUES('02', '李四');
INSERT INTO Teacher VALUES('03', '王五');


# Score 分数表
CREATE TABLE Score
(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id, c_id)  # 注意这里是联合主键
);

INSERT INTO Score VALUES('01', '01', 80);
INSERT INTO Score VALUES('01', '02', 90);
INSERT INTO Score VALUES('01', '03', 99);
INSERT INTO Score VALUES('02', '01', 70);
INSERT INTO Score VALUES('02', '02', 60);
INSERT INTO Score VALUES('02', '03', 80);
INSERT INTO Score VALUES('03', '01', 80);
INSERT INTO Score VALUES('03', '02', 80);
INSERT INTO Score VALUES('03', '03', 80);
INSERT INTO Score VALUES('04', '01', 50);
INSERT INTO Score VALUES('04', '02', 30);
INSERT INTO Score VALUES('04', '03', 20);
INSERT INTO Score VALUES('05', '01', 76);
INSERT INTO Score VALUES('05', '02', 87);
INSERT INTO Score VALUES('06', '01', 31);
INSERT INTO Score VALUES('06', '03', 34);
INSERT INTO Score VALUES('07', '02', 89);
INSERT INTO Score VALUES('07', '03', 98);

# 四张表
SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Teacher;
SELECT * FROM Score;

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号和成绩

SELECT
    student.*,
    m.score1,
    m.score2 
FROM
    student
    INNER JOIN (
SELECT
    s1.s_id,
    s1.s_score score1,
    s2.s_score score2 
FROM
    score s1
    JOIN score s2 ON s1.s_id = s2.s_id 
    AND s1.c_id = 1 
    AND s2.c_id = 2 
    AND s1.s_score > s2.s_score 
    ) m ON student.s_id = m.s_id

2、查询平均成绩大于60分的学生的学号和平均成绩

SELECT
	* 
FROM
	( SELECT s.s_id id, AVG( s.s_score ) avg_score FROM score s GROUP BY s.s_id ) m 
WHERE
	m.avg_score > 60
	
-- HAVING 子句
-- 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。

SELECT s_id, AVG(s_score) avg_score 
FROM Score
GROUP BY s_id
HAVING avg_score > 60;

2.1、所有成绩小于60分的学生信息

SELECT
	student.* 
FROM
	student
	INNER JOIN ( SELECT s_id, MAX( s_score ) max FROM score GROUP BY s_id HAVING max < 60 ) m ON student.s_id = m.s_id
-- 用in
SELECT
	student.* 
FROM
	student
WHERE
	student.s_id IN
	 ( SELECT s_id FROM score GROUP BY s_id HAVING MAX( s_score ) < 60 ) 

2.2、查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况

SELECT
	t.id,
	AVG( t.score ) avgscore 
FROM
	(
SELECT
	st.s_id id,
	IFNULL( sc.s_score, 0 ) score 
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id 
	) t 
GROUP BY
	t.id 
HAVING
	avgscore < 60

3、查询所有学生的学号、姓名、选课数、总成绩

SELECT
	st.s_id,
	st.s_name,
	COUNT( sc.c_id ),
	SUM( IFNULL( sc.s_score, 0 ) ) totalscore
FROM
	student st
	LEFT JOIN score sc ON st.s_id = sc.s_id
GROUP BY
	st.s_id

4、查询姓“猴”的老师的个数

SELECT
	COUNT( t_id ) 
FROM
	teacher 
WHERE
	t_name LIKE '猴%'

5、查询没学过“张三”老师课的学生的学号、姓名

SELECT
	st.s_id,
	st.s_name 
FROM
	student st 
WHERE
	st.s_id NOT IN (
SELECT
	s_id 
FROM
	score 
WHERE
	c_id IN ( SELECT c_id FROM course WHERE t_id IN ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) 
	)

6、查询学过“张三”老师所教的所有课的同学的学号、姓名

SELECT
	s_id,
	s_name 
FROM
	student t 
WHERE
	t.s_id IN (
SELECT
	score.s_id 
FROM
	course
	RIGHT JOIN teacher ON course.t_id = teacher.t_id
	LEFT JOIN score ON course.c_id = score.c_id 
WHERE
	teacher.t_name = '张三' 
	)

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

SELECT
	s_id,
	s_name 
FROM
	student 
WHERE
	s_id IN (
SELECT
	s1.s_id 
FROM
	score s1
	JOIN score s2 ON s1.s_id = s2.s_id 
WHERE
	s1.c_id = '01' 
	AND s2.c_id = '02' 
	)

7.1、查询学过编号为“01”的课程但没有学过编号为“02”的课程的学生的学号、姓名

SELECT
	student.s_id,
	student.s_name 
FROM
	student 
WHERE
	student.s_id IN (
SELECT
	sc1.s_id 
FROM
	score sc1 
WHERE
	sc1.c_id = '01' 
	AND sc1.s_id NOT IN ( SELECT sc2.s_id FROM score sc2 WHERE sc2.c_id = '02' ) 
	)

8、查询课程编号为“02”的总成绩

SELECT
	SUM( s_score ) 
FROM
	score 
WHERE
	c_id = '02'
# ------------------------OR---------------------------
SELECT SUM(s_score)
FROM Score
GROUP BY c_id
-- WHERE c_id = '02'  # 考察 HAVING，聚合条件限制不能使用WHERE
HAVING c_id = '02'

9、查询所有课程成绩小于60分的学生的学号、姓名

# 同2.1题
## 【所有】这个条件使用边界值进行限定
SELECT DISTINCT st.s_id, st.s_name
FROM Student st 
JOIN 
		(
		SELECT s_id, 
					 MIN(s_score) min_score
		FROM Score s
		GROUP BY s.s_id
		HAVING min_score < 60
		) s # 满足条件的学生
ON st.s_id = s.s_id

10、查询没有学全所有课的学生的学号、姓名 **

SELECT
	stu.s_id,
	stu.s_name 
FROM
	student stu 
WHERE
	stu.s_id NOT IN ( SELECT s_id FROM score GROUP BY s_id HAVING COUNT( c_id ) = 3 )

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

SELECT DISTINCT
	stu.s_id,
	stu.s_name 
FROM
	student stu
	LEFT JOIN score ON stu.s_id = score.s_id 
WHERE
	score.c_id IN ( SELECT c_id FROM score WHERE s_id = '01' ) 
	AND stu.s_id != '01'

12、查询和“01”号同学所学课程完全相同的其他同学的学号（重点！）

# 查找数量等于01学生的课程数量，再查询与01不相同的学生再取反
# 感觉不严谨。。。
SELECT DISTINCT
	s_id 
FROM
	score 
WHERE
	s_id IN (
SELECT
	s_id 
FROM
	score 
WHERE
	s_id != '01' 
GROUP BY
	s_id 
HAVING
	COUNT( DISTINCT c_id ) = ( SELECT count( DISTINCT c_id ) FROM score WHERE s_id = '01' ) 
	) 
	AND s_id NOT IN ( SELECT DISTINCT s_id FROM score WHERE c_id NOT IN ( SELECT c_id FROM score WHERE s_id = '01' ) )

用group_concat函数

SELECT
	s_id 
FROM
	(
SELECT
	s_id,
	GROUP_CONCAT( c_id ORDER BY c_id ) a 
FROM
	score 
WHERE
	s_id != '01' 
GROUP BY
	s_id 
HAVING
	a = ( SELECT GROUP_CONCAT( c_id ORDER BY c_id ) a FROM score WHERE s_id = '01' GROUP BY s_id ) 
	) t

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

SELECT DISTINCT
	s_id 
FROM
	score 
WHERE
	s_id NOT IN (
SELECT DISTINCT
	s_id 
FROM
	score 
WHERE
	c_id IN ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) 
	)

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

SELECT
	sc.s_id,
	st.s_name,
	AVG( sc.s_score ) 
FROM
	score sc
	JOIN student st ON sc.s_id = st.s_id 
WHERE
	sc.s_id IN ( SELECT s_id FROM score WHERE s_score < 60 GROUP BY s_id HAVING COUNT( s_score ) >= 2 ) 
GROUP BY
	sc.s_id

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

SELECT
	score.s_id,
	s_name 
FROM
	score
	JOIN student ON score.s_id = student.s_id 
WHERE
	c_id = '01' 
	AND s_score < 60 
ORDER BY
	s_score DESC

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

SELECT
	sc.s_id,
	sc.s_score,
	t.avgscore 
FROM
	score sc
	JOIN ( SELECT s_id, AVG( s_score ) avgscore FROM score GROUP BY s_id ) t ON sc.s_id = t.s_id 
ORDER BY
	t.avgscore DESC

18、查询各科成绩最高分、最低分和平均分

以如下形式显示：

– 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– （及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90） （！！）

SELECT
	course.c_name,
	tem2.*,
	tem1.maxscore,
	tem1.minscore,
	tem1.avgscore 
FROM
	( SELECT c_id, MAX( s_score ) maxscore, MIN( s_score ) minscore, AVG( s_score ) avgscore FROM score GROUP BY c_id ) tem1
	JOIN (
SELECT
	p1.c_id,
	p2.sum_jige / p1.count jige_rate,
	p2.sum_zhongdeng / p1.count zhongdeng_rate,
	p2.sum_youliang / p1.count youliang_rate,
	p2.sum_youxiu / p1.count youxiu_rate 
FROM
	( SELECT c_id, COUNT( s_score ) count FROM score GROUP BY c_id ) p1
	JOIN (
SELECT
	t1.c_id c_id,
	SUM( t1.jige ) sum_jige,
	SUM( t1.zhongdeng ) sum_zhongdeng,
	SUM( t1.youliang ) sum_youliang,
	SUM( youxiu ) sum_youxiu 
FROM
	(
SELECT
	c_id,
IF
	( s_score >= 60 AND s_score < 70, 1, 0 ) jige,
IF
	( s_score >= 70 AND s_score < 80, 1, 0 ) zhongdeng,
IF
	( s_score >= 80 AND s_score < 90, 1, 0 ) youliang,
IF
	( s_score >= 90, 1, 0 ) youxiu 
FROM
	score 
	) t1 
GROUP BY
	t1.c_id 
	) p2 ON p1.c_id = p2.c_id 
	) tem2 ON tem1.c_id = tem2.c_id
	JOIN course ON tem1.c_id = course.c_id

19、按各科成绩进行排序，并显示排名

使用Row_number()和Rank()实现

SELECT
	*,
	dense_rank ( ) over ( ORDER BY sumscore DESC ) 
FROM
	( SELECT s_id, SUM( s_score ) sumscore FROM score GROUP BY s_id ) t

20、查询学生的总成绩并进行排名

SELECT
	*,
	dense_rank ( ) over ( ORDER BY sumscore DESC ) 
FROM
	( SELECT s_id, SUM( s_score ) sumscore FROM score GROUP BY s_id ) t

21 、查询不同老师所教不同课程平均分从高到低显示

SELECT
	teacher.t_name,
	course.c_name,
	t.avgscore 
FROM
	course
	JOIN ( SELECT c_id, AVG( s_score ) avgscore FROM score GROUP BY c_id ) t ON course.c_id = t.c_id
	JOIN teacher ON course.t_id = teacher.t_id 
ORDER BY
	t.avgscore DESC

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT DISTINCT
	stu.s_name,
	cor.c_name,
	t.s_score,
	t.rk 
FROM
	( SELECT c_id, s_id, s_score, dense_rank ( ) over ( PARTITION BY c_id ORDER BY s_score DESC ) AS rk FROM score ) t
	JOIN student stu ON t.s_id = stu.s_id
	JOIN course cor ON cor.c_id = t.c_id 
WHERE
	t.rk IN ( 2, 3 )

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

MySQL case语句

SELECT
	course.c_name,
	p.* 
FROM
	course
	JOIN (
SELECT
	t.c_id,
	SUM( IF ( t.LEVEL = 'level1', 1, 0 ) ) '[100-85]',
	SUM( IF ( t.LEVEL = 'level2', 1, 0 ) ) '[85-70]',
	SUM( IF ( t.LEVEL = 'level3', 1, 0 ) ) '[70-60]',
	SUM( IF ( t.LEVEL = 'level4', 1, 0 ) ) '[<60]',
	COUNT( t.s_id ) sumstu 
FROM
	(
SELECT
	c_id,
	s_id,
	s_score,
CASE
	
	WHEN s_score BETWEEN 85 
	AND 100 THEN
	'level1' 
WHEN s_score BETWEEN 70 
AND 85 THEN
'level2' 
WHEN s_score BETWEEN 60 
AND 70 THEN
'level3' ELSE 'level4' 
END LEVEL 
FROM
	score 
	) t 
GROUP BY
	t.c_id 
) p ON course.c_id = p.c_id

24、查询学生平均成绩及其名次

SELECT
	*,
	dense_rank ( ) over ( ORDER BY t.avgscore DESC ) rk 
FROM
	(
SELECT
	stu.s_name,
	AVG( sc.s_score ) avgscore 
FROM
	student stu
	JOIN score sc ON stu.s_id = sc.s_id 
GROUP BY
	sc.s_id 
	) t

25、查询各科成绩前三名的记录（不考虑成绩并列情况）

SELECT
	t.* 
FROM
	( SELECT c_id, s_id, s_score, row_number ( ) over ( PARTITION BY c_id ORDER BY s_score DESC ) rk FROM score ) t 
WHERE
	t.rk IN ( 1, 2, 3 )

26、查询每门课程被选修的学生数

SELECT
	course.c_name,
	COUNT( score.s_id ) stucount 
FROM
	score
	JOIN course ON score.c_id = course.c_id 
GROUP BY
	course.c_name

27、查询出只有两门课程的全部学生的学号和姓名

SELECT
	student.s_id,
	student.s_name 
FROM
	student
	JOIN score ON student.s_id = score.s_id 
GROUP BY
	s_id 
HAVING
	count( c_id ) =2

28、查询男生、女生人数

SELECT
	s_sex,
	COUNT( s_id ) 
FROM
	student 
GROUP BY
	s_sex

29、查询名字中含有"风"字的学生信息

SELECT
	* 
FROM
	student 
WHERE
	s_name LIKE '%风%'

31、查询1990年出生的学生名单

SELECT
	* 
FROM
	student 
WHERE
	s_birth LIKE '%1990%'
-- -------------or----------------------
SELECT
	* 
FROM
	Student 
WHERE
	YEAR ( s_birth ) = '1990'

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

SELECT
	t.s_id,
	stu.s_name,
	t.avgscore 
FROM
	student stu
	RIGHT JOIN ( SELECT s_id, AVG( s_score ) avgscore FROM score GROUP BY s_id HAVING avgscore >= 85 ) t ON stu.s_id = t.s_id

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

（1）在MySql中，使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。

（2）尤其非常特别重要：默认按升序（ASC）排列。

（3）order by 后可加2个（或多个）字段，字段之间用英文逗号隔开。

（4）若A用升序，B用降序，SQL该这样写：order by A ASC, B DESC; 默认同理，也可以这样写：order by A, B DESC;

（5）若A、B都用降序，必须用两个DESC，order by A DESC, B DESC;

（6）多个字段时，优先级按先后顺序而定。

SELECT
	c_id,
	AVG( s_score ) avgscore 
FROM
	score 
GROUP BY
	c_id 
ORDER BY
	avgscore ASC,
	c_id DESC

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT
	stu.s_name,
	t.s_score 
FROM
	student stu
	JOIN ( SELECT s_id, s_score FROM score WHERE c_id = ( SELECT c_id FROM course WHERE c_name = '数学' ) AND s_score < 60 ) t ON stu.s_id = t.s_id

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

SELECT
	stu.s_name,
	co.c_name,
	sc.s_score 
FROM
	student stu
	LEFT JOIN score sc ON stu.s_id = sc.s_id
	LEFT JOIN course co ON sc.c_id = co.c_id

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

SELECT
	stu.s_name,
	co.c_name,
	sc.s_score 
FROM
	student stu
	LEFT JOIN score sc ON stu.s_id = sc.s_id
	LEFT JOIN course co ON sc.c_id = co.c_id 
WHERE
	sc.s_id IN ( SELECT s_id FROM score GROUP BY s_id HAVING MAX( s_score ) > 70 )

37、查询学生不及格的课程并按课程号从大到小排列

SELECT
	* 
FROM
	score 
WHERE
	s_score < 60 
ORDER BY
	c_id DESC

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名

SELECT
	s_id,
	s_name 
FROM
	student 
WHERE
	s_id IN ( SELECT s_id FROM score WHERE c_id = '03' AND s_score > 80 )

39、求每门课程的学生人数

SELECT
	co.c_name,
	t.stucount 
FROM
	course co
	JOIN ( SELECT c_id, COUNT( s_id ) stucount FROM score GROUP BY c_id ) t ON co.c_id = t.c_id

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩

SELECT
	stu.s_name,
	t.maxscore 
FROM
	student stu
	JOIN (
SELECT
	s_id,
	MAX( s_score ) maxscore 
FROM
	score 
WHERE
	c_id IN ( SELECT c_id FROM course JOIN teacher ON course.t_id = teacher.t_id WHERE teacher.t_name = '张三' ) 
	) t ON stu.s_id = t.s_id

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

-- 自连接
SELECT
	sc1.s_id,
	sc1.c_id,
	sc1.s_score 
FROM
	score sc1
	JOIN score sc2 ON sc1.s_score = sc2.s_score 
	AND sc1.s_id != sc2.s_id 
	AND sc1.c_id != sc2.c_id

42、查询每门课成绩最好的前两名

SELECT
	* 
FROM
	( SELECT c_id, s_id, row_number ( ) over ( PARTITION BY c_id ORDER BY s_score DESC ) scrank FROM score ) t 
WHERE
	t.scrank < 3

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

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

SELECT
	course.c_name,
	t.* 
FROM
	( SELECT c_id, COUNT( s_id ) count FROM score GROUP BY c_id HAVING count > 5 ) t
	JOIN course 
WHERE
	course.c_id = t.c_id 
ORDER BY
	t.c_id

44、检索至少选修两门课程的学生学号

SELECT
	s_id,
	COUNT( c_id ) count 
FROM
	score 
GROUP BY
	s_id 
HAVING
	count >=2

45、查询选修了全部课程的学生信息

SELECT
	s_id,
	COUNT( c_id ) count 
FROM
	score 
GROUP BY
	s_id 
HAVING
	count = ( SELECT COUNT( DISTINCT c_id ) count FROM score )

46、查询各学生的年龄（精确到月份）mysql获取当前时间

SELECT
	s_name,
IF
	(
	WEEK ( CURRENT_DATE ) >= WEEK ( s_birth ),
	YEAR ( CURRENT_DATE ) - YEAR ( s_birth ),
	YEAR ( CURRENT_DATE ) - YEAR ( s_birth ) - 1 
	) s_age 
FROM
	student

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

SELECT
	s_name 
FROM
	student 
WHERE
	s_id NOT IN (
SELECT DISTINCT
	s_id 
FROM
	score 
WHERE
	c_id IN ( SELECT c_id FROM course WHERE t_id = ( SELECT t_id FROM teacher WHERE t_name = '张三' ) ) 
	)

48、查询两门以上不及格课程的同学的学号及其平均成绩

SELECT
	s_id,
	AVG( s_score ) 
FROM
	score 
WHERE
	s_id IN (
SELECT
	t.s_id 
FROM
	( SELECT s_id, s_score FROM score WHERE s_score < 60 ) t 
GROUP BY
	t.s_id 
HAVING
	COUNT( t.s_score ) > 2 
	)

49、查询本月过生日的学生

SELECT
	s_name 
FROM
	student 
WHERE
	MONTH ( s_birth ) = MONTH ( CURRENT_DATE )

49.1、查询下月过生日的学生MySQL If(函数)

-- 考虑12月的下一月应该是1月的情况
SELECT
	s_name 
FROM
	student 
WHERE
	MONTH ( s_birth ) = ( SELECT IF ( MONTH ( CURRENT_DATE ) = 12, 1, MONTH ( CURRENT_DATE ) + 1 ) )

50、查询本周过生日的学生

SELECT
	s_name 
FROM
	student 
WHERE
	WEEK ( s_birth ) = ( SELECT WEEK ( CURRENT_DATE ) )

50.1、查询下周过生日的学生 MySQL间隔值主要用于日期和时间计算

-- 用interval来解决期末与期初的问题
SELECT
	s_name 
FROM
	student 
WHERE
	WEEK ( s_birth ) = ( SELECT WEEK ( CURRENT_DATE + INTERVAL 1 WEEK ) )