MYSQL面试题

1、什么是事务

事务就是让一个执行单元满足事务的4个特性

2、四个特性

• 原子性：满足原子操作单元，对数据的操作，要么全部执行，要么全部失效
• 一致性：事务开始和完成，数据都必须保持一致
• 隔离性：事务之间是相互独立的，中间状态对外不可见
• 持久性：数据的修改是永久的（不受外界环境影响）

3、隔离级别

3.1、并发情况下事务引发的问题

一般情况下，多个单元操作并发执行，会出现这么几个问题

• 脏读：A事务还未提交，B事务就读取到了A事务的结果（破坏了隔离性）
• 不可重复读：A事务在本次事务中，对自己未操作过的数据进行了多次读取。结果出现了不一致或记录不存在的情况（破坏了一致性。update、delete）
• 幻读：A事务在本次事务中，对自己未操作过的数据进行了多次读取。第一次读取时，记录不存在，第二次读取时，记录出现了（insert）

3.2、解决

为了权衡隔离和并发的矛盾，ISO定义了四个事务的隔离级别，每个级别的隔离程度不同，允许出现的副作用也不同

• 未提交读：最低级别，基本只保证持久性；会出现脏读，不可重复读，幻读的问题。
• 已提交读：语句级别；会出现不可重复读，幻读的问题。
• 可重复读：事务级别；只会出现幻读问题。
• 串行化：最高级别，也就是事务与事务完全串行化执行，无并发可言，性能低；但不会出现任何问题。

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	会	会	会
读已提交（read-committed）	-	会	会
可重复读（repeatable-read）	-	-	会
串行化（serializable）	-	-	-

4、索引

4.1、什么是索引

• 官方定义: 一种帮助mysql提高查询效率的数据结构
• 索引的优点:
  1、大大加快数据查询速度
• 索引的缺点:
  1、维护索引需要耗费数据库资源
  2、索引需要占用磁盘空间
  3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响

4.2、索引分类

innodb 四大索引

• a.主键索引
  设定为主键后数据库会自动建立索引，innodb为聚簇索引。主键索引列值不能为空

• b.单值索引（单列索引、普通索引）
  即一个索引只包含单个列，一个表可以有多个单列索引 id name index（为name创建） age index（为age创建） bir
  按照单个属性name、age查询时会用到索引，为多个列（name、age）查则不会用到单值索引

• c.唯一索引
  索引列的值必须唯一，但允许有空值（null），但是只能存在一个null

• d.复合索引
  即一个索引包含多个列 id （name age）index bir
  同时查询name、age时，就会利用复合索引

• e.Full Text 全文索引 (My5.7版本之前 只能由于MYISAM引擎)
  全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

4.3、索引的基本操作

1）主键索引 自动创建

-- 1.主键索引 自动创建
DROP TABLE t_user;
-- 建表 主键自动创建主键索引
CREATE TABLE t_user(
id VARCHAR(20) PRIMARY KEY,
NAME VARCHAR(20)
);
-- 查看索引
SHOW INDEX FROM t_user;

2）单列索引

-- 2.单列索引(普通索引|单值索引)
DROP TABLE t_user;
-- 建表时创建
CREATE TABLE t_user(
id VARCHAR(20) PRIMARY KEY,
NAME VARCHAR(20),KEY(NAME)
);  
	'注意:随表一起建立的索引索引名同列名一致'
-- 建表后创建
CREATE INDEX nameindex ON t_user(NAME);
-- 删除索引
DROP INDEX * ON t_user
-- 查看索引
SHOW INDEX FROM t_user;

3.唯一索引

-- 3.唯一索引
DROP TABLE t_user;
-- 建表时创建
CREATE TABLE t_user(
id VARCHAR(20) PRIMARY KEY,
NAME VARCHAR(20),UNIQUE(NAME)
);
-- 建表后创建
CREATE UNIQUE INDEX nameindex ON t_user(NAME);
-- 查看索引
SHOW INDEX FROM t_user;

4.复合索引

DROP TABLE t_user;
-- 建表时创建
 CREATE TABLE t_user(
 id VARCHAR(20) PRIMARY KEY,
 NAME VARCHAR(20),age INT,KEY(NAME,age)
 );
-- 建表后创建
 CREATE INDEX nameageindex ON t_user(NAME,age);
-- 查看索引
SHOW INDEX FROM t_user;

复合索引面试题：最左前缀匹配

按照name age bir创建复合索引

当查询时按照 name bir age 能否利用索引 可以
当查询时按照 name age bir 能否利用索引 可以
当查询时按照 age bir 能否利用索引
当查询时按照 bir age name 能否利用索引 可以
当查询时按照 age bir 能否利用索引

1）只有顺序与最左前缀匹配，才能利用索引（包含name即可）
2）mysql 引擎在查询时为了更好地利用索引，在查询过程中会动态调整查询字段顺序

4.4、索引的底层原理（底层数据结构）

-- 建表
CREATE TABLE t_emp(
id INT PRIMARY KEY,
NAME VARCHAR(20),
age INT
);

-- 插入数据
INSERT INTO t_emp VALUES(5,'d',22);
INSERT INTO t_emp VALUES(6,'d',22);
INSERT INTO t_emp VALUES(7,'e',21);
INSERT INTO t_emp VALUES(1,'a',23);
INSERT INTO t_emp VALUES(2,'b',26);
INSERT INTO t_emp VALUES(3,'c',27);
INSERT INTO t_emp VALUES(4,'a',32);
INSERT INTO t_emp VALUES(8,'f',53);
INSERT INTO t_emp VALUES(9,'v',13);

-- 查询
SELECT * FROM t_emp;
-- 查询索引
SHOW INDEX FROM t_emp;

不按顺序插入，查询出的结果仍是有顺序的。因为主键id有索引

为什么数据没有按顺序插入，查询时却有顺序？

• 原因是:mysql底层为主键自动创建索引,一定创建索引会进行排序
• 也就是mysql底层真正存储是这样的
• 为什么要排序呢?因为排序之后在查询就相对比较快了 如查询 id=3的我只需要按照顺序找到3就行啦(如果没有排序大海捞针,全靠运气😸!)
  

进一步优化 引入页目录

为了进一步提高效率mysql索引又进行了优化

• 就是基于页的形式进行管理索引
• 如 查询id=4的 直接先比较页 先去页目录中找,再去数据目录中找

1）innodb引擎一页默认存储16KB
2）页目录存储主键值和指针，存储大小也为16KB

4.5、B树和B+树

B+Tree是在B Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。
在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。

• InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿 条记录。
• 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。
• MYSQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

面试问索引
索引底层实现是一个B+树。放入数据时，基于数据去进行排序。排序后用指针将数据链接起来（链表的形式）。
一页page默认存储16KB
指针大小为4-8Byte 默认是6Byte

4.6、聚簇索引和非聚簇索引

• 聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（聚簇索引不一定是主键索引，但是主键索引一定是聚簇索引）
• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

为什么辅助索引是根据主键查找而不是直接存地址？
数据需要有删改操作。比如1、3、5，插入一个2，会根据主键索引重新排序，此时地址就会发生变化，导致辅助索引(非聚簇索引)也需要修改

1. InnoDB中

• InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

• 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

• 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

2. MYISAM

• MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
  

4.7、使用聚簇索引的优势

• 问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

• 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

• 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

4.8、聚簇索引需要注意什么?

• 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
• 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

4.9、为什么主键通常建议使用自增id

• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

4.10、什么情况下无法利用索引呢?

• 查询语句中使用LIKE关键字
  在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

• 查询语句中使用多列索引
  多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

• 查询语句中使用OR关键字
  查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

4.2、索引优化策略

1）优化COUNT()查询

2）优化关联查询

3）优化GROUP BY

4）优化LIMIT分页

5）优化UNION查询

6）使用用户自定义变量

7）优化INSERT
、

查询语句

SQL语句格式

select（输出） xxx from（获取数据） xxx
where（过滤） xxx group by（分组） xxx
having（过滤） xxx order by（排序） xxx
limit（限定个数） xxx;

执行顺序

1. from
2. where
3. gourp by
4. select
5. having
6. order by
7. limit

1、group by 分组

group by 只输出第一行数据

如果不使用聚合函数，则select的所有属性都必须包含在group by中
原因：

比如 我们要group by 性别 ，男的有三个 后面的姓名信息都不一样 没法合并成一条 。

SELECT NAME,grade,COUNT(*)
FROM stu
WHERE grade % 2 = 0
GROUP BY NAME,grade;

2、count()

count(1)和count(属性)的区别

如果属性中有null值，count(1)记录null值，count(属性)不记录null值

count(distinct 属性名) 去重，不重复的个数

名字不重复的个数

其他聚集函数

1. count()
2. sum()
3. max()
4. min()
5. avg()
6. group_concat() 字符串集合

4、having 分组

在 group by 之后的过滤

查询相同分数的人，且人数大于2的分数

SELECT NAME,grade,COUNT(grade) 总数
FROM stu
GROUP BY NAME,grade
HAVING 总数 > 2;
或 count(1)，不需要显示单独一列
SELECT NAME,grade
FROM stu
GROUP BY NAME,grade
HAVING COUNT(1) > 2;

5、order by 排序

6、limit

7、case when 表达式

类似if-elif-else

case when 表达式
then 输出
when 表达式
then 输出
else 输出
end

8、join on

on 和 where 的区别

left join 和 join 的区别
1）left join 必须有 on 条件
2）left join 会检查左边表的数据是否都包含在新生成的表中（条件不为真也会返回左表的数据）

right join
类似left join
条件不为真也会返回右表的数据

SQL50题

创建语句

#–1.学生表 
#Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
CREATE TABLE `Student` (
    `s_id` VARCHAR(20),
    s_name VARCHAR(20) NOT NULL DEFAULT '',
    s_brith VARCHAR(20) NOT NULL DEFAULT '',
    s_sex VARCHAR(10) NOT NULL DEFAULT '',
    PRIMARY KEY(s_id)
);

#–2.课程表 
#Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 
create table Course(
    c_id varchar(20),
    c_name VARCHAR(20) not null DEFAULT '',
    t_id VARCHAR(20) NOT NULL,
    PRIMARY KEY(c_id)
);

/*
–3.教师表 
Teacher(t_id,t_name) –教师编号,教师姓名 
*/
CREATE TABLE Teacher(
    t_id VARCHAR(20),
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

/*
–4.成绩表 
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
*/
Create table Score(
    s_id VARCHAR(20),
    c_id VARCHAR(20) not null default '',
    s_score INT(3),
    primary key(`s_id`,`c_id`)
);

插入语句

#--插入学生表测试数据#('01' , '赵雷' , '1990-01-01' , '男')
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' , '女');
#--课程表测试数据
INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');
#--教师表测试数据
INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');
#--成绩表测试数据
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);

题目
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT stu.s_name,a.s_id,a.s_score '课程1',b.s_score '课程2'
FROM score a,score b,student stu
WHERE a.c_id = 01
AND b.c_id = 02
AND a.s_id = b.s_id
AND a.s_score > b.s_score
AND stu.s_id = a.s_id;-- 连接表a表b都可以

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT stu.s_id,stu.s_name,stu.s_brith,stu.s_sex,a.s_score '课程1成绩',b.s_score '课程2成绩'
FROM score a,score b,student stu
WHERE a.c_id = 1
AND b.c_id = 2
AND a.s_id = b.s_id
AND a.s_score < b.s_score
AND stu.s_id = a.s_id;

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

-- 取出平均成绩需要使用group by分组，然后having筛选。再用做student表的连接
SELECT sc.s_id,stu.s_name,AVG(sc.s_score) 平均成绩
FROM score sc,student stu
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING AVG(sc.s_score) > 60

也可以不做两表连接，直接写select语句

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)UNION连接

SELECT sc.s_id,stu.s_name,AVG(sc.s_score) 平均成绩
FROM score sc,student stu
WHERE sc.s_id = stu.s_id
GROUP BY sc.s_id
HAVING AVG(sc.s_score) < 60

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

SELECT stu.s_id,stu.s_name,COUNT(sc.c_id) 课程总数,SUM(sc.s_score) 总成绩
FROM score sc
INNER JOIN student stu
ON stu.s_id = sc.s_id
GROUP BY stu.s_id;

SELECT stu.s_id,stu.s_name,COUNT(sc.c_id) 课程总数,SUM(sc.s_score) 总成绩
FROM score sc,student stu
WHERE sc.s_id = stu.s_id
GROUP BY stu.s_id;

6、查询"李"姓老师的数量

SELECT COUNT(t_name)
FROM teacher
WHERE t_name LIKE '李%';

7、查询学过"张三"老师授课的同学的信息

SELECT stu.s_id,stu.s_name
FROM student stu,teacher,course,score sc
WHERE stu.s_id = sc.s_id
AND course.t_id = teacher.t_id
AND sc.c_id = course.c_id
AND teacher.t_name = '张三'

8、查询没学过"张三"老师授课的同学的信息

SELECT *
FROM student
WHERE s_id NOT IN(
SELECT stu.s_id
FROM student stu,teacher,course,score sc
WHERE stu.s_id = sc.s_id
AND course.t_id = teacher.t_id
AND sc.c_id = course.c_id
AND teacher.t_name = '张三'
);

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 自连接、UNION

SELECT stu.s_id,stu.s_name
FROM student stu,score sc1,score sc2
WHERE sc1.s_id = stu.s_id
AND sc2.s_id = stu.s_id
AND sc1.c_id = '01'
AND sc2.c_id = '02'

也可以使用UNION
SELECT stu.*
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '01'
UNION
SELECT stu.*
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '02';

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT stu.*
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '01'
AND sc.s_id NOT IN(
SELECT stu.s_id
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id = '02'
);

11、未学完所有课程的学生数
分组统计score表中每个学生所学的课程数，与course表中的总课程数对比

SELECT * FROM score;
SELECT stu.*,COUNT(sc.c_id) 课程总数
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING 课程总数 < (SELECT COUNT(c_id) FROM course);

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

SELECT stu.*
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
AND sc.s_id
IN (SELECT sc.c_id
FROM score sc
WHERE sc.s_id = '01');

13、查询和"01"号同学学习的课程完全相同的其他同学的信息 ***
思路：排除学了其他课程的人、排除学习课程数量不相同的人

SELECT stu.*
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
AND stu.s_id NOT IN 
-- 学了其他课程的
(SELECT s_id
FROM score
WHERE c_id NOT IN (
SELECT c_id FROM score WHERE s_id = '01'))
AND stu.s_id NOT IN(
-- 学习课程数量不同的
SELECT s_id
FROM score
GROUP BY s_id
HAVING COUNT(c_id) != (
SELECT COUNT(c_id)
FROM score
WHERE s_id = '01'));

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

-- 查询学过张三老师课程的同学，然后取反
SELECT stu.s_name
FROM student stu
WHERE stu.s_id NOT IN (
	SELECT stu.s_id
	FROM student stu,score sc
	WHERE stu.s_id = sc.s_id
	AND sc.c_id IN (SELECT c_id
		FROM teacher tea,course c
		WHERE tea.t_id = c.t_id
		AND tea.t_name = '张三')
)

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

case when XXX then XXX
when XXX then XXX
else XXX end

SUM(CASE WHEN sc.s_score >= 60 THEN 0 ELSE 1 END)

SELECT stu.s_name,AVG(sc.s_score)
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
GROUP BY stu.s_id
HAVING SUM(CASE WHEN sc.s_score >= 60 THEN 0 ELSE 1 END) >= 2

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

select stu.*,sc.s_score 分数
from score sc,student stu
where sc.s_id = stu.s_id
and sc.c_id = '01'
and sc.s_score < 60
order by sc.s_score desc

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

SELECT a1.*,a2.avgs 平均成绩
FROM 
	-- 所有成绩
	(SELECT * FROM score) a1,
	-- 平均成绩
	(SELECT stu.*,AVG(sc.s_score) avgs
	FROM student stu,score sc
	WHERE stu.s_id = sc.s_id
	GROUP BY stu.s_id) a2
WHERE a1.s_id = a2.s_id
ORDER BY 平均成绩 DESC

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：
课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

SELECT c.c_name,
MAX(sc.s_score) maxs,
MIN(sc.s_score) mins,
AVG(sc.s_score) avgs,
SUM(CASE WHEN sc.s_score > 60 THEN 1 ELSE 0 END) / COUNT(sc.s_score) 及格率,
SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 80 THEN 1 ELSE 0 END) / COUNT(sc.s_score) 中等率,
SUM(CASE WHEN sc.s_score >= 80 AND sc.s_score < 90 THEN 1 ELSE 0 END) / COUNT(sc.s_score) 优良率,
SUM(CASE WHEN sc.s_score >= 90 THEN 1 ELSE 0 END) / COUNT(sc.s_score) 优秀率
FROM score sc,course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id

19、按各科成绩进行排序，并显示排名(实现不完全)

SELECT a.c_id,a.s_score,
(SELECT COUNT(s_score) FROM score b WHERE a.c_id = b.c_id AND a.s_score < b.s_score) + 1 rk
FROM score a
ORDER BY a.c_id,a.s_score DESC

20、总成绩排名（未实现排名）

SELECT stu.s_name,SUM(s_score) 总成绩
FROM score sc,student stu
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
ORDER BY SUM(s_score) DESC

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

SELECT t.t_name,c.c_name,AVG(sc.s_score) avgs
FROM teacher t,course c,score sc
WHERE t.t_id = c.t_id
AND sc.c_id = c.c_id
GROUP BY t.t_name,c.c_name
ORDER BY avgs DESC;

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

SELECT stu.*,SUM(sc.s_score)
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
GROUP BY stu.s_id
ORDER BY SUM(sc.s_score) DESC
LIMIT 1,2

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

SELECT c.c_id,c.c_name,
SUM(CASE WHEN sc.s_score >= 85 AND sc.s_score <= 100 THEN 1 ELSE 0 END) /  COUNT(sc.s_score) '[100-85]',
SUM(CASE WHEN sc.s_score >= 70 AND sc.s_score < 85 THEN 1 ELSE 0 END) /  COUNT(sc.s_score) '[85-70]',
SUM(CASE WHEN sc.s_score >= 60 AND sc.s_score < 70 THEN 1 ELSE 0 END) /  COUNT(sc.s_score) '[70-60]',
SUM(CASE WHEN sc.s_score >= 0 AND sc.s_score < 60 THEN 1 ELSE 0 END) /  COUNT(sc.s_score) '[0-60]'
FROM course c,score sc
WHERE c.c_id = sc.c_id
GROUP BY sc.c_id

RANK() OVER()

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

SELECT r.*,
RANK() OVER(ORDER BY avgs DESC) rk
FROM
(SELECT sc.s_id,AVG(s_score) avgs
FROM score sc
GROUP BY sc.s_id) r

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

-- 25、查询各科成绩前三名的记录
SELECT rk.*
FROM (SELECT sc.c_id,sc.s_score,
ROW_NUMBER() OVER(PARTITION BY sc.c_id ORDER BY sc.s_score DESC) rk
FROM score sc) rk
WHERE rk IN (1,2,3)

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

SELECT c.c_name,COUNT(sc.s_score) 人数
FROM score sc,course c
WHERE sc.c_id = c.c_id
GROUP BY c.c_name

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

SELECT stu.s_name
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
GROUP BY sc.s_id
HAVING
COUNT(sc.c_id) = 2

28、查询男生、女生人数

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

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

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

30、查询同名同性学生名单，并统计同名人数

SELECT s_name,s_sex,COUNT(s_name) 个数
FROM student
GROUP BY s_name,s_sex
HAVING COUNT(s_name) > 1;

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

SELECT *
FROM student
WHERE YEAR(s_brith) = 1990;

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

SELECT c.c_id,c.c_name,AVG(sc.s_score)
FROM score sc,course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id
ORDER BY AVG(sc.s_score) DESC, c.c_id ASC

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

SELECT stu.s_id,stu.s_name,AVG(sc.s_score) avgs
FROM student stu,score sc,course c
WHERE stu.s_id = sc.s_id
AND sc.c_id = c.c_id
GROUP BY stu.s_id
HAVING AVG(sc.s_score) >= 85

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

SELECT stu.s_name,sc.s_score
FROM score sc,course c,student stu
WHERE sc.c_id = c.c_id
AND stu.s_id = sc.s_id
AND c.c_name = '数学'
AND sc.s_score < 60

35、查询所有学生的课程及分数情况

SELECT stu.s_name,c.c_name,sc.s_score
FROM student stu,score sc,course c
WHERE stu.s_id = sc.s_id
AND sc.c_id = c.c_id

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

SELECT stu.*,c.c_name,sc.s_score
FROM student stu,score sc,course c
WHERE stu.s_id = sc.s_id
AND sc.c_id = c.c_id
AND stu.s_id NOT IN (
SELECT s_id
FROM score
WHERE s_score <= 70
);

37、查询不及格的课程

SELECT sc.s_id,sc.s_score
FROM course c,score sc
WHERE c.c_id = sc.c_id
AND sc.s_score < 60

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

SELECT stu.s_id,stu.s_name
FROM student stu,score sc
WHERE stu.s_id = sc.s_id
AND sc.s_score >= 80
AND sc.c_id = '01';

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

SELECT sc.c_id,c.c_name,COUNT(sc.c_id)
FROM score sc,course c
WHERE sc.c_id = c.c_id
GROUP BY c.c_id

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT stu.s_name,sc.s_score
FROM teacher t,course c,score sc,student stu
WHERE t.t_id = c.t_id
AND sc.c_id = c.c_id
AND sc.s_id = stu.s_id
AND t.t_name = '张三'
ORDER BY sc.s_score DESC
LIMIT 0,1

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

SELECT DISTINCT sc1.s_id,sc1.c_id 课程,sc1.s_score
FROM score sc1,score sc2
WHERE sc1.c_id != sc2.c_id
AND sc1.s_score = sc2.s_score

42、查询每门课程成绩最好的前两名学生信息 开窗函数

SELECT *
FROM (SELECT s.*,sc.s_score,
RANK() OVER(PARTITION BY sc.c_id ORDER BY sc.s_score DESC) rk
FROM score sc,student s
WHERE sc.s_id = s.s_id) t
WHERE rk <= 2;

43、统计每门课程的学生选修人数（超过5人的课程才统计）。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT sc.c_id,COUNT(sc.c_id) 选课人数
FROM score sc,course c
WHERE sc.c_id = c.c_id
GROUP BY sc.c_id
HAVING COUNT(sc.c_id) > 5
ORDER BY 选课人数 DESC,sc.c_id ASC

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

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

45、查询选修了全部课程的学生信息
利用总数 count()

SELECT s.*,COUNT(sc.c_id) 选课数量
FROM student s,course c,score sc
WHERE s.s_id = sc.s_id
AND c.c_id = sc.c_id
GROUP BY s.s_id
HAVING 选课数量 = (
SELECT COUNT(c_id)
FROM course);

46、查询各学生的年龄

SELECT NOW()
SELECT YEAR(NOW())
SELECT DAYOFYEAR(NOW())

SELECT *,YEAR(NOW()) - YEAR(s_brith) age
FROM student

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

UPDATE student SET s_brith = '2021-10-05' WHERE s_id = '01'

SELECT *
FROM student
WHERE WEEK(NOW()) = WEEK(s_brith)

48、查询下周过生日的学生

UPDATE student SET s_brith = '2021-10-11' WHERE s_id = '01'

SELECT *
FROM student
WHERE WEEK(NOW()) + 1 = WEEK(s_brith)

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

UPDATE student SET s_brith = '1990-10-11' WHERE s_id = '02'

SELECT *
FROM student
WHERE MONTH(NOW()) = MONTH(s_brith)

50、查询下月过生日的学生

UPDATE student SET s_brith = '1990-11-11' WHERE s_id = '02'

SELECT *
FROM student
WHERE MONTH(NOW()) + 1 = MONTH(s_brith)