MySQl知识点梳理以及常见的Sql题总结

MySQl知识点梳理以及常见的Sql题总结

MySQL基本语法回顾

SQL的语言分类

DDL：数据库定义语言（主要对数据库，以及数据库的表进行创建，删除）

关键字：create，alter，drop，show等等

DDL操作数据库

创建一个数据库：

create database 数据库名;   #默认使用默认的编码格式
create database 数据库名 charset utf8mb4;

删除一个数据库：

drop database 数据库名;

使用数据库：

use database 数据库名;

查询数据库：

select database();  #查询正在使用的数据库
show databases;  #查询所有的数据库

DDL操作表

创建一个表：

create table tablename {
	column_name1 数据类型(长度) 约束，
	column_name2 数据类型(长度) 约束，
	...
	column_namen 数据类型(长度) 约束    #最后一个字段无需逗号分隔
};

举例:
CREATE TABLE `tf_equipment` (
  `equipment_id` int(32) NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `equipment_name` varchar(255) NOT NULL COMMENT '器材名称',
  `icon` varchar(255) DEFAULT NULL COMMENT '器材图标',
  `url` varchar(255) DEFAULT NULL COMMENT '购买url',
  `permission` varchar(255) DEFAULT NULL COMMENT '权限',
  `specification` varchar(255) DEFAULT NULL COMMENT '规格',
  `type` varchar(255) NOT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` int(1) DEFAULT '0' COMMENT '逻辑删除',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态',
  PRIMARY KEY (`equipment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4;

删除表：

drop table 表名

举例:
DROP TABLE IF EXISTS `tf_equipment`;

查看表：

desc 表名;  #查看一个表的详细信息
show tables;  #查看当前数据库中的所有表

更改表的名字：

rename table 表名 to 新的表名;

增加一列：

alter table 表名 add column_name 类型(长度) 约束;

删除一列：

alter table 表名 drop 列名

查看表的结构:

desc 表名;

修改列：

alter table 表名 change 旧列名 新列名 类型(长度) 约束;  #修改列名，以及对应的类型和长度，约束
alter table 表名 modify 列名 类型(长度) 约束; #修改列的类型和约束

DCL：数据库操纵语言（控制数据库访问权限）

DML：数据库操作语言（主要是对表中数据进行增，删，改）

关键字：insert，delete，update

举例：insert操作：

insert into 表名(column1,column2,...) values(value1,value2,...);
其中:
1) 列名可以在表中选择一列或者几列
2) 后面的value值必须与前面的列名一一对应
3) 在SQL中除了int类型以外，其他类型需要用‘’（单引号）引起来
4) 如果插入的数据为所有字段，则以上的(column1,column2,...)可以省略，即:
insert into 表名 values(value1,value2,...);

DQL：数据库查询语言（主要对表中数据进行查询）

关键字：select

select * from 表名;  #查询表中的所有列数据
select column1,column2,... from 表名 where 查询条件; #根据查询条件查询所需要的列数据
select [distinct]  column1,column2,... from 表名 where 查询条件;  #去重查询

SQL通用语法

1）SQL语句可以单行或者多行书写，以分号结尾

2）可以使用空格和缩进增强语句的可读性

3）关键字建议大写（不区分大小写）

4）注释 /* */，#，–空格

常见的数据类型

int 整型

varchar 字符串型

double 浮点型

data 日期类型（yyyy-mm-dd）

delete和truncate的区别

• delete from 表名；这种方式删除表中所有数据，但是自动增长至不会重置为1
• truncate table 表名；这种方式删除表中所有数据，并且重置自动增长值为1，因为truncate底层是摧毁表，再重新创建表！

数据库的乱码问题

• windows ：修改安装目录下的my.ini文件；而Linux：修改/etc/my.cnf文件

添加:
default-character-set = utf-8
如果是mysql8以上版本:
character-set-server = utf8mb4;

SQL约束：

添加数据的完整性	添加表的约束
实体完整性	主键约束，唯一约束
域完整性	字段数据的完整性（默认约束，非空约束）
引用完整性	表与表之间的关系（外键约束）

主键约束：primary key

被主键约束的列必须具有唯一值，而且不能为null值（空）

我们可以选择直接在创建表的时候，在字段的后面进行添加主键约束，或者在定义完字段以后，添加约束（使用constraint）

如何删除主键约束？

alter table 表名 drop primary key(字段名)

自动增长约束：auto_increment

被自动增长约束的列，值可以不用管它，自动增长，必须修饰的是int类型的列

当我们给某一个字段添加自动增长约束，该字段必须是一种键，一般来说为主键！

唯一约束：unique

保证该字段的唯一性，可以为null

我们添加唯一约束的方法：

1）在创建表的时候直接在字段后面写上唯一约束

2）在约束区域声明唯一

3）在创建表以后，修改表结构，声明字段唯一

非空约束 not null

被not null约束的字段，不能为null值

默认约束：default

给该字段添加一个默认值（如果添加时没有赋值的话）

单表操作

排序操作

order by 字段名 ASC|DESC;   #其中ASC表示升序(默认)，DESC为降序

聚合查询

之前我们的查询都是横向记录查询

而聚合查询则是纵向个数查询

常见的聚合查询函数有：

• count：求记录数的聚合函数，count函数会自动忽略空值（null）
• max：求最大值
• min：求最小值
• sum：求和
• avg：求平均值
• 等等

分组查询 group by

即将查询数据分为若干个组进行查询

注意 ：

• 如果查询中，要对数据进行过滤，我们使用having，而不使用where；

having和where的区别

1）having是在分组后对数据进行过滤；而where是在分组前对数据进行过滤的

2）having后面可以使用统计函数过滤数据；where后面不能使用统计函数；

分页查询 limit

只查询记录中的一部分数据

查询的公式：

第一页: limit (1 - 1) * n,n   --->   limit 0,n
第二页: limit (2 - 1) * n,n   --->   limit n,2n
第三页: limit (3 - 1) * n,n   --->   limit 2n,3n
...
第m页: limit (m - 1) * n,n   --->   limit (m - 1) * n,n

故从上面我们归纳以下公式:
$$limit(pageNumber-1)\ast pageSize,pageSize;$$

其中pageNumber表示当前第几页，pageSize表示每页显示的记录数

多表操作

一对多的建表原则：建立两张表

其中一张表为主表，一张表为从表，其中从表中必须有一个字段，引用主表中的主键，这个字段我们称为外键

注意：

• 主表的主键中有的值，从表中的外键可以没有
• 主表中主键没有的值，从表中的外键绝对不能有！

//主表：user用户表
CREATE TABLE `tf_user` (
  `id` int(32) NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `username` varchar(255) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `nick_name` varchar(255) NOT NULL COMMENT '用户昵称',
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` int(1) DEFAULT '0' COMMENT '逻辑删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4;

//从表：实验记录表
CREATE TABLE `tf_experiment` (
  `id` int(32) NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `name` varchar(255) NOT NULL COMMENT '记录名称',
  `content` longtext COMMENT '记录内容',
  `word` varchar(255) DEFAULT NULL COMMENT 'word链接',
  `type` varchar(255) DEFAULT NULL COMMENT '类型',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `user_id` int(32) DEFAULT NULL COMMENT '绑定的用户id',
  `deleted` int(1) DEFAULT '0' COMMENT '逻辑删除',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态',
  PRIMARY KEY (`id`),
  KEY `FK292449gwg5yf7ocdlmswv9w4j` (`user_id`),
  CONSTRAINT `FK292449gwg5yf7ocdlmswv9w4j` FOREIGN KEY (`user_id`) REFERENCES `tf_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=105 DEFAULT CHARSET=utf8mb4;

其中：我们在从表中定义了一个外键字段user_id指向主表(tf_user)中的id字段

CONSTRAINT FOREIGN KEY (`user_id`) REFERENCES `tf_user` (`id`)

多对多关系的建表原则：建立三种表

除了两方各自的表结构以外，我们还需要建立一张中间表：

• 必须有自己的主键
• 必须有两个外键，且这两个外键分别引用了这两张关联的表的主键

//tf_role表
CREATE TABLE `tf_role` (
  `id` int(32) NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `rolename` varchar(255) NOT NULL COMMENT '名称',
  `description` varchar(255) DEFAULT NULL COMMENT '描述',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

//tf_user表
CREATE TABLE `tf_user` (
  `id` int(32) NOT NULL AUTO_INCREMENT COMMENT 'id值',
  `username` varchar(255) NOT NULL COMMENT '用户名',
  `password` varchar(255) NOT NULL COMMENT '密码',
  `nick_name` varchar(255) NOT NULL COMMENT '用户昵称',
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` int(1) DEFAULT '0' COMMENT '逻辑删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4;

//tf_role_user 中间表
CREATE TABLE `tf_role_user` (
  `user_id` int(32) NOT NULL COMMENT '用户id',
  `role_id` int(32) NOT NULL COMMENT '角色id',
  KEY `FK5feau0gb4lq47fdb03uboswm8` (`user_id`),
  KEY `FKh4pacwjwofrugxa9hpwaxg6mr` (`role_id`),
  CONSTRAINT `FK5feau0gb4lq47fdb03uboswm8` FOREIGN KEY (`user_id`) REFERENCES `tf_user` (`id`),
  CONSTRAINT `FKh4pacwjwofrugxa9hpwaxg6mr` FOREIGN KEY (`role_id`) REFERENCES `tf_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

添加外键约束的sql：

alter table table1 add constraint 外键名(约束名) foreign key (table1中的外键列名) references table2 (table2中绑定的主键名);

内连接查询 inner join

内连接分为两种：

• 隐式内连接 ，即不写关键字inner join

select * from 主表，从表 where 主表的.主键 = 从表.外键；

• 显示内连接，写关键字inner join

select * from 主表 inner join 从表 on 主表.主键 = 从表.外键;

外连接查询 outer join

左外连接 left outer join

其中左外连接以左表为准，左表中的数据必须每条都有！！！

如果左表有而右表没有的，则补空！！！

右外连接 right outer join

同理，右外连接以右表为准，右表中的数据必须每条都有！！！

子查询 即一个查询的结果作为另一个查询的条件

select * from products where category_id = (select cid from category where cname = "xxx");

后面对于这些外连接查询我们还会深入探究！！！

MySQL高级部分

MySQL目录结构

路径	解释	备注
/var/lib/mysql	mysql数据库文件的存放路径
/usr/share/mysql	配置文件目录	mysql.server命令及配置文件
/usr/bin	相关命令目录	mysqladmin,mysqldump等命令
/etc/init.d/mysql	启动，停止等相关脚本

修改配置文件

如果我们是采用yum命令安装的mysql，可以直接读取/etc/my.cnf作为配置文件，故我们可以修改其中的一些配置信息：

字符集utf-8的修改：

show variables like 'character%';  #查看字符集

在/etc/my.cnf配置文件中，添加：

character-set-server = utf8mb4;

其他配置文件

配置文件	描述
二进制日志文件 log-bin	主从复制的二进制文件
错误日志文件 log-error	默认是关闭的，记录严重的警告和错误信息，每一次启动和关闭的详细信息等等
查询日志文件 log	默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的

MySQL的逻辑架构介绍

和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层

Management Serveices & Utilities	系统管理和控制工具
SQL Interface: SQL 接口	接受用户的SQL 命令，并且返回用户需要查询的结果。比如select from 就是调用SQL Interface
Parser 解析器	SQL 命令传递到解析器的时候会被解析器验证和解析
Optimizer 查询优化器	SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有where 条件时，优化器来决定先投影还是先过滤。
Cache 和Buffer 查询缓存	如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等

引擎层

存储引擎层，存储引擎真正的负责了MySQL 中数据的存储和提取，服务器通过API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

开启profile

我们可以通过show profile查看我们的sql的执行周期

如何使用profile？

查看profile是否处于开启状态：

show variables like '%profiling%';

如果没有开启的话，我们可以执行以下命令将其开启：

set profiling=1

使用profile

执行show prifiles 命令，可以查看最近的几次查询

我们可以根据Query_ID进一步地执行：

show profile cpu,block io for query Query_id;

来查看sql的具体执行步骤！！！

mysql 的查询流程

• 第一步：mysql 客户端通过协议与mysql 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT 语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
• 第二步： 语法解析器和预处理：首先mysql 通过关键字将SQL 语句进行解析，并生成一颗对应的“解析树”。mysql 解析器将使用mysql 语法规则验证和解析查询；预处理器则根据一些mysql 规则进一步检查解析数是否合法。
• 第三步：查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。

我们手写一个SQL：

它的实际的执行过程为：

MyISAM 和InnoDB数据库引擎对比

show engines ： 查看数据库引擎命令

show variables like ‘%storage_engine%’ 查看默认的数据库引擎

常见的Join查询

其中，上面的full outer join 在mysql中是不支持的，我们需要使用union进行查询

inner join

select * from tableA A inner join tableB B on A.key = B.key;

left join 第一种情况

select * from tableA A left join tableB B on A.key = B.key;

left join 第二种情况【A独占】

select * from tableA A left join tableB B on A.key = B.key where B.key = null;

right join 第一种情况

select * from tableA A right join tableB B on A.key = B.key;

right join 第二种情况【B独占】

select * from tableA A right join tableB B on A.key = B.key where A.key = null;

A & B[使用left join + union + right join]

select * from tableA A left join tableB B on A.key = B.key 
union
select * from tableA A right join tableB B on A.key = B.key;

其中union自带去重功能！！！

A独有 & B独有

select * from tableA A left join tableB B on A.key = B.key where B.key = null
union
select * from tableA A right join tableB B on A.key = B.key where A.key = null;

索引

索引的定义

MySQL 官方对索引的定义为：索引（Index）是帮助MySQL 高效获取数据的数据结构。

可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，
这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

注意：

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

索引的优点

• 提高数据检索的效率，降低数据库的IO成本。
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

索引的缺点

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

索引底层数据结构

MySQL 使用的是Btree 索引，即B树或者B+树

B树：

B+树：

B+Tree 与B-Tree 的区别

1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。

2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。

为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？

1) B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO 读写次数也就降低了。

2) B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

聚簇索引和非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的好处

• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO 操作。即支持方便我们的范围查询。

聚簇索引的限制

• 对于mysql 数据库目前只有innodb 数据引擎支持聚簇索引，而Myisam 并不支持聚簇索引。
• 由于数据物理存储排序方式只能有一种，所以每个Mysql 的表只能有一个聚簇索引。一般情况下就是该表的主键。
• 为了充分利用聚簇索引的聚簇的特性，所以innodb 表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid 这种。

Mysql 索引分类

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

create index index_name on table_name(column_name);
ALTER TABLE table_name ADD INDEX index_name (column_name):

唯一索引

索引列的值必须唯一，但允许有空值

create unique index index_name on table_name(column_name);

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

当然我们也可以单独地创建主键索引：

#单独创建主键索引
ALTER TABLE table_name add PRIMARY KEY table_name(主键字段);
#删除主键索引
ALTER TABLE table_name drop PRIMARY KEY;
#修改建主键索引
先删除主键索引，再重建创建主键索引。

复合索引

即一个索引包含多个列

CREATE INDEX index_name ON table_name(cloumn1_name,cloumn2_name,cloumn3_name);
ALTER TABLE table_name ADD INDEX index_name (cloumn1_name,cloumn2_name,cloumn3_name):

全文索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

基本语法汇总：

索引的创建时机

适合创建索引的情况

• 主键自动建立唯一索引；
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题， 组合索引性价比更高
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

不适合创建索引的情况

• 表记录太少

• 经常增删改的表或者字段

• Where 条件里用不到的字段不创建索引

• 过滤性不好的不适合建索引

总结：

我们定义索引的选择性为索引中不同值的数目与表的记录数的比，如果一个索引的选择性越接近于1，则表示这个索引的效率就越高！！！

Explain 性能分析（重点！！！）

使用EXPLAIN 关键字可以模拟优化器执行SQL 查询语句，从而知道MySQL 是如何处理你的SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

用法: Explain+SQL 语句

Explain执行后返回的信息：

Explain的作用

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引会被用到
• 哪些索引被实际使用
• 表之间的引用关系
• 每张表有多少行被优化器查询

id：select 查询的序列号,包含一组数字，表示查询中执行select 子句或操作表的顺序。

(1) 当我们的id相同时，执行顺序由上到下执行

(2) 当我们的id不同时，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

(3) id有相同也有不同

id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行衍生= DERIVED

select_type：代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

table 这个数据是基于哪张表的

type 查询的访问类型。是较为重要的一个指标

type值从最好到最坏依次为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

其中：

type类型	描述
system	表只有一行记录（等于系统表），这是const 类型的特列，平时不会出现，这个也可以忽略不计
const	表示通过索引一次就找到了,const 用于比较primary key 或者unique 索引。因为只匹配一行数据，所以很快
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref	非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range	只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where 语句中出现 了between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引。
index	sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。
all	Full Table Scan，将遍历全表以找到匹配的行
index_merge	在查询过程中需要多个索引组合使用，通常出现在有or 的关键字的sql 中。
ref_or_null	对于某个字段既需要关联条件，也需要null 的情况下。查询优化器会选择用ref_or_null 连接查询。
index_subquery	利用索引来关联子查询，不再全表扫描
unique_subquery	该联接类型类似于index_subquery。子查询中的唯一索引。

我们在进行sql优化时，优化之后至少使得type位于range级别，最好位于eq_ref或者ref级别！！！

possible_keys ： 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际使用的索引。如果为NULL，则没有使用索引。

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows：显示MySQL认为它执行查询时必须检查的行数。越少越好！

Extra：其他的额外重要的信息，包含了不适合在其他列中显示的十分重要的信息

额外信息	信息描述
Using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序
Using temporary	使用了临时表保存中间结果
Using index	表示相应的select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错
Using where	使用了where过滤
Using join buffer	使用了连接缓存
impossible where	where 子句的值总是false，不能用来获取任何元组。
select tables optimized away	在没有GROUPBY 子句的情况下，基于索引优化MIN/MAX 操作或者对于MyISAM 存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化distinct操作在找到第一个匹配的元组后立即停止找同样的值的动作

SQL优化篇

Join语句的优化

索引失效问题（重点！！）

1. 最佳左前缀原则：全值匹配我最爱，带头大哥不能少，中间索引不能断

如果索引了多列（即定义了复合索引），必须查询从索引的最前列开始，并且不能跳过中间列，否则后面的字段都无法被使用！！！

举个例子：加入我们的table表中存在字段name，color，length等字段，我们按照name，color，length的顺序定义了一个复合索引idx_name_color_length，则此时我们查询如果想使用索引，就必须带上name，必须不能跳过color字段！！！

2. 索引列上少计算

不要在索引列上进行任何操作（例如：计算，函数，自动或者手动的类型转换等），都会导致索引失效而转向全表扫描。

3. 范围之后全失效

存储引擎不能使用索引中范围条件以后的列，即范围查询以后的索引将全部失效

4. 尽量使用覆盖索引

即我们使得查询的列和索引列保持一致性！

5. mysql在使用不等于（!=）或者<，>的时候无法使用索引，会导致全表扫描

6. mysql在使用is null，is not null的时候也会无法使用索引

7. like以通配符开头（%abc），mysql索引将会失效，也会变成全表扫描，故我们使用like，应该将%加在右边

8. 字符串不加单引号会导致索引失效，故我们使用字符串应该加上引号

9. 少用or，用它进行连接时，会导致我们的索引失效

总结我们上面的索引失效问题，即为下面的几句口诀：

全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
like百分写最后，覆盖索引不写*
不等空值还有or，索引失效要少用

SQL调优步骤

1. 慢查询的开启并捕获
2. explain + 慢SQL分析
3. show profile 查询SQL在MySQL服务器里面执行的细节以及生命周期情况
4. SQL数据库服务器的参数调优（运维经理、DBA）

查询优化

1. 永远是小表驱动大表，即小的数据集驱动大的数据集

其中我们主要来看我们的exists和in的使用情况

select * from table where EXISTS (subquery);
我们可以理解为将主查询的数据放到子查询中做条件验证，根据验证结true和false来决定主查询的数据结果是否得以保留！
我们采取的策略为:
子查询表大的用exists，子查询表小的用in

2. 子查询优化：在范围判断时，尽量不要使用not in 和not exists，使用left join on xxx is null 代替。

3. Order by关键字优化

尽量使用Index（索引）方式进行排序，避免使用File Sort方式排序

MySQL支持两种方式的排序：

• FileSort：效率较低
• Index：效率比较高（MySQL底层扫描索引本身完成排序）

Order by满足两种情况，将会使用Index方式进行排序

• order by语句使用索引最左前缀原则
• 使用where子句与Order by子句列组合满足索引的最左前缀原则

其中的File Sort方式存在两种算法：单路排序算法和双路排序算法

其中双路排序算法需要两次扫描磁盘最终才能得到我们的的数据，我们怎样才能优化？

增大Sort-buffer-size参数的设置
增大max-length-for-sort-data参数的设置

总结：

为排序使用索引：

假设表table中我们为字段a，b，c建立覆盖索引（复合索引）：

此时，我们单独使用order by能够用到索引的情况如下所示：

select * from table order by a;
select * from table order by a,b;
select * from table order by a,b,c;
select * from table order by a DESC,b DESC,c DESC;

如果我们使用where子句 + order by，必须联合满足最左前缀原则，order by才能使用到索引

where a = const order by b,c;
where a = const and b = const order by c;
where a = const and b > const order by c;

而接下来的sq语句 order by均未使用到索引

order by a ASC,b DESC,c DESC; 	#排序不一致
where g = const order by b,c; 	#丢失带头大哥a
where a = const order by c; 	#丢失b索引
where a = const order by a,d;   #d不是索引的一部分

4. group by关键字优化

第一点：group by实质上是先排序，然后进行分组，遵照索引的最佳左前缀原则

第二点：当无法使用索引列的时候，我们可以通过以下设置：

增大max-length-for-sort-data参数的设置
增大sort-buffer-size参数的设置

第三点：where高于having，能在where的限定条件中写就不要去having限定了

慢查询日志

什么是慢查询日志？

（1）MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具
体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
（2）具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为
10，意思是运行10秒以上的语句。
（3）由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能
收集超过5秒的sql，结合之前explain进行全面分析。

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
慢查询日志支持将日志记录写入文件。

慢查询日志常见命令

开启设置

命令	描述	备注
SHOW VARIABLES LIKE ‘%slow_query_log%’;	查看慢查询日志是否开启	slow_query_log 默认为OFF，表示慢查询日志是禁用的
set global slow_query_log=1;	开启慢查询日志
SHOW VARIABLES LIKE ‘long_query_time%’;	查看慢查询设定阈值	单位秒
set long_query_time=1	设置慢查询设定阈值	单位秒

如永久生效需要修改配置文件**my.cnf 中[mysqld]**下配置

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/xxx.log  #慢查询日志文件位置
long_query_time=3
log_output=FILE

日志分析工具 mysqldumpslow

查看mysqldumpslow的相关帮助信息：

参数	描述
-s	是表示按照何种方式进行排序
c	访问次数
l	锁定时间
r	返回记录
t	查询时间
al	平均锁定时间
ar	平均返回记录数
at	平均查询时间
-t	即返回前面多少条的数据
-g	后边搭配一个正则表达模式，大小写不敏感

我们举例：

得到返回记录集最多的10 个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/feng-slow.log
得到访问次数最多的10 个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/feng-slow.log
得到按照时间排序的前10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/feng-slow.log
另外建议在使用这些命令时结合| 和more 使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/feng-slow.log | more

SHOW PROCESSLIST

查询mysql 进程列表，可以杀掉故障进程

数据库的锁机制

数据库的锁可以按照数据操作的类型来划分：

• 读锁（共享锁）：即针对同一份数据，多个读操作可以同时进行而不会相互影响
• 写锁：当前写操作没有完成之前，它会阻断其他写锁和度读锁

如果我们按照数据操作的粒度来分：

• 行锁：偏向于写
• 表锁：偏向于读

读锁会阻塞写，但是不会阻塞读，而写锁会把读和写都阻塞

表锁

偏向于MyISAM的存储引擎，开销小，加锁快，无死锁问题，锁定的粒度大，发生锁冲突的概率最高，并发度比较低。

行锁

偏向于InnoDB的存储引擎，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高。

其中，我们说InnoDB和MyISAM最大的两个不同点在于：

• InnoDB支持事务（Transaction）
• InnoDB采用了行锁

MySQL主从复制

复制的步骤：

• master将改变记录到二进制日志（binary log），这些记录过程称为二进制日志事件（binary log events）
• slave将master中的binary log events拷贝到它的**中继日志（relay log）**中
• slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制时异步的且串行化的

复制的规则：

• 每一个slave只有一个mater
• 每一个slave只能由唯一的服务器ID
• 每一个master可以拥有多个slave

复制的最大问题：

延迟问题。。。

一主一从配置：主数据库配置在Windows系统下，从数据库配置在Linux系统下

配置前提：MySQL的版本一定保持一致！！！

主从配置在**[mysqld]**结点下，都是小写

主数据库配置：修改my.ini配置文件

其中主要配置项为：

server-id = 1 #【必须】配置主服务器的唯一ID
log-bin = 自己本地的路径/mysqlbin  #【必须】启用二进制日志
log-err = 自己本地的路径/mysqlerr  #【可选】启动错误日志
basedir = 自己本地路径  #【可选】根路径
tmpdir = 自己本地路径   #【可选】临时目录
datadir = 自己本地路径/Data #【可选】数据目录
read-only = 0  #设置主机读写都可以
binlog-ignore-db = mysql  #【可选】设置不要复制的数据库
binlog-do-db = 需要复制的主数据库的名字  #【设置需要复制的数据库】

从数据库配置：修改my.cnf配置文件

其中的配置项为：

#从机服务id
server-id = 2
#注意my.cnf中有server-id = 1，我们将其注释掉
#设置中继日志
relay-log=mysql-relay

然后我们需要重启主机和从机的mysql服务

主机从机都关闭防火墙、安全工具

在主机服务器上建立账号并授权slave：

GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从机器数据库IP' IDENTIFIED BY '密码';
flush privilege;

查询master的状态，并记录下File和Position的值：

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

在Linux 从机上配置需要复制的主机

#查询master 的状态
CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='创建用户名',MASTER_PASSWORD='创建的密码',
MASTER_LOG_FILE='File 名字',MASTER_LOG_POS=Position 数字;

启动从服务器复制功能

start slave;
#查看从机状态
show slave status\G;

下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

如何停止从服务复制功能?

stop slave;

MySQL集群

暂无…

MySQL常见的SQL题

175. 组合两个表

https://leetcode-cn.com/problems/combine-two-tables/

SQL架构

表1: Person

+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

FirstName, LastName, City, State

//使用左连接查询:
select FirstName,LastName,City,State 
from Person
left join Address on Person.PersonId = Address.PersonId;

176. 第二高的薪水

https://leetcode-cn.com/problems/second-highest-salary/

SQL架构

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

题解1:

我们使用聚合函数max进行求解，首先我们获取Salary中最大的薪水

select max(Salary) from Employee;

然后将其作为查询条件

select max(Salary) as SecondHighestSalary

from Employee

where salary < (select max(Salary) from Employee);

select max(Salary)  as SecondHighestSalary
from Employee 
where salary < (select max(Salary) from Employee);

题解2：

使用 limit + offset

其中我们回顾一下分页 limit m，n

表示从第m页开始读取n条数据

而我们这里的limit n offset m

表示**从第m条数据（包括第m条数据自身），读取n条数据，即我们的读取的数据为（ m，m + 1 + n ]

所以我们可以对Employee表进行按照Salary降序排序，然后使用limit取出第二大数据，即为第二高的薪水（Salary）

但是存在第一高具有重复值，故我们需要使用distinct进行去重处理

select 
(select DISTINCT Salary from Employee Order by Salary DESC limit 1 offset 1)
 as SecondHighestSalary;

其实我们使用分页也可以实现，返回第二高，就是上面去重以后，从第二个取一个数据即可

select 
(select DISTINCT Salary from Employee Order by Salary DESC limit 1,1)
 as SecondHighestSalary;

177. 第N高的薪水

https://leetcode-cn.com/problems/nth-highest-salary/

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

题解：单表查询

由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：

1. 同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
2. 排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。因为可能传入的N为0，此时N - 1为负数
3. 注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。
   注：这种解法形式最为简洁直观，但仅适用于查询全局排名问题，如果要求各分组的每个第N名，则该方法不适用；而且也不能处理存在重复值的情况。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N:=N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT 
            salary
      FROM 
            employee
      GROUP BY 
            salary
      ORDER BY 
            salary DESC
      LIMIT N, 1    
  );
END

178. 分数排名

https://leetcode-cn.com/problems/rank-scores/

SQL架构

编写一个 SQL 查询来实现分数排名。

如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

**重要提示：**对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 Rank

题解：

首先我们按照分数进行降序排序，
select a.Score AS Score from Scores a Order by a.Score DESC;
然后我们需要得到分数的排名，此时存在难点，我们如何实现？
第一步：找到比当前分数大于等于的分数的个数（需要去重），则该分数的Rank值即为大于等于当前分数的DISTINCT值
举例说明:假如现在存在分数98,97,97,96,96,96
大于等于98的个数（去重）: 1
大于等于97的个数（去重）: 2
大于等于96的个数（去重）: 3
此时98,97,96的排名分为为1,2,3
select count(DISTINCT b.score) as 'Rank' from Scores b where b.score > x
其中x即为上面按照分数降序排序以后的分数值
select a.Score AS Score,
(select count(DISTINCT b.score) from Scores b where b.score >= a.score) as 'Rank'
from Scores a Order by a.Score DESC;

180. 连续出现的数字

https://leetcode-cn.com/problems/consecutive-numbers/

SQL架构

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

Logs 表：
+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
1 是唯一连续出现至少三次的数字。

题解：

刚开始 我写的sql如下所示：

select Counts.Num as ConsecutiveNums 
from (select Logs.Num as Num,count(Logs.Num) as Count from Logs) as Counts
where Counts.count >= 3;

但是这样不能不能保证连续性！！！

我们采取DISTINCT + where进行联表查询

select DISTINCT l1.Num AS ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1 AND l1.Num = l2.Num AND l2.Num = l3.Num;

181. 超过经理收入的员工

https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/

SQL架构

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

题解：联表查询

select e1.Name as Employee
from Employee e1,Employee e2
where e1.ManagerId = e2.Id AND e1.Salary > e2.Salary;

182. 查找重复的电子邮箱

https://leetcode-cn.com/problems/duplicate-emails/

SQL架构

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

**说明：**所有电子邮箱都是小写字母。

题解1：使用两表之间的where条件查询

select DISTINCT a.Email
from Person a,Person b
where a.Id != b.Id AND a.Email = b.Email; 

题解2：使用group by分组查询

#首先查询出相同Email出现的次数
select Email,count(Eamil) as num from Person group by Email;
#然后查询出其中num值大于1的Email,即为我们要搜索的值
select p.Email as Email
from
(select Email,count(Email) as num from Person group by Email) as p
where p.num > 1;

183. 从不订购的客户

https://leetcode-cn.com/problems/customers-who-never-order/

SQL架构

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如给定上述表格，你的查询应返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

题解：子查询

select a.Name as Customers 
from Customers a
where a.Id not in (select CustomerId from Orders);

184. 部门工资最高的员工

https://leetcode-cn.com/problems/department-highest-salary/

SQL架构

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。对于上述表，您的 SQL 查询应返回以下行（行的顺序无关紧要）。

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

解释：

Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

题解：使用inner join + in（子查询）

select d.Name as Department,e.Name as Employee,e.Salary as Salary
from Employee e inner join Department d
on e.DepartmentId  = d.Id
where (e.DepartmentId,Salary) in (select DepartmentId,max(Salary) as Salary from Employee group by DepartmentId);

197. 上升的温度

https://leetcode-cn.com/problems/rising-temperature/

SQL架构

表 Weather

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| recordDate    | date    |
| temperature   | int     |
+---------------+---------+
id 是这个表的主键
该表包含特定日期的温度信息

编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 不要求顺序 。

查询结果格式如下例：

Weather
+----+------------+-------------+
| id | recordDate | Temperature |
+----+------------+-------------+
| 1  | 2015-01-01 | 10          |
| 2  | 2015-01-02 | 25          |
| 3  | 2015-01-03 | 20          |
| 4  | 2015-01-04 | 30          |
+----+------------+-------------+

Result table:
+----+
| id |
+----+
| 2  |
| 4  |
+----+
2015-01-02 的温度比前一天高（10 -> 25）
2015-01-04 的温度比前一天高（20 -> 30）

题解：

刚开始我的sql如下所示：

select b.id as id
from Weather a,Weather b
where a.Temperature < b.Temperature AND day(a.recordDate) = day(b.recordDate) - 1;

出现问题，而后我使用DATEDIFF(startDate，endDate)函数进行求解，默认为start Date - endDate的天数，例如：

DATEDIFF(2021-01-02,2021-01-01) = 1;

DATEDIFF(2021-01-01,2021-01-02) = -1;

select w1.id as id
from Weather w1 inner join Weather w2
on DATEDIFF(w1.recordDate,w2.recordDate) = 1 AND w1.Temperature > w2.Temperature;

596. 超过5名学生的课

https://leetcode-cn.com/problems/classes-more-than-5-students/

SQL架构

有一个courses 表 ，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如，表：

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

应该输出:

+---------+
| class   |
+---------+
| Math    |
+---------+

提示：

• 学生在每个课中不应被重复计算。

题解：使用group by + having

select class
from courses 
group by class 
having count(DISTINCT student) >= 5;

620. 有趣的电影

https://leetcode-cn.com/problems/not-boring-movies/

SQL架构

某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。

作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

例如，下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

对于上面的例子，则正确的输出是为：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

题解：

select id,movie,description,rating
from cinema
where 
description != 'boring' AND id % 2 = 1 
Order by rating DESC;

626. 换座位

https://leetcode-cn.com/problems/exchange-seats/

SQL架构

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

假如数据输入的是上表，则输出结果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

题解：我们需要使用到case条件选择

对于所有座位 id 是奇数的学生，修改其 id 为 id+1，如果最后一个座位 id 也是奇数，则最后一个座位 id 不修改。对于所有座位 id 是偶数的学生，修改其 id 为 id-1

select 
(case
 when mod(id,2) != 0 AND counts != id then id + 1
 when mod(id,2) != 0 AND counts = id then id
 else id -1
end) as id,student
from seat,(select count(*) as counts from seat) as b
Order by id ASC;

627. 变更性别

https://leetcode-cn.com/problems/swap-salary/

SQL架构

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

运行你所编写的更新语句之后，将会得到以下表:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

题解：使用if条件语句

$$if(条件，条件满足的结果，条件不满足的结果)$$

if (条件，条件满足的结果，条件不满足的结果)

$$if(条件，条件满足的结果，条件不满足的结果)$$

update salary set sex = if(sex = "m","f","m");

1179. 重新格式化部门表

https://leetcode-cn.com/problems/reformat-department-table/

SQL架构

部门表 Department：

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| revenue       | int     |
| month         | varchar |
+---------------+---------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]。

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 每个月 的收入（revenue）列。

查询结果格式如下面的示例所示：

Department 表：
+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
| 1    | 8000    | Jan   |
| 2    | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+------+---------+-------+

查询得到的结果表：
+------+-------------+-------------+-------------+-----+-------------+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1    | 8000        | 7000        | 6000        | ... | null        |
| 2    | 9000        | null        | null        | ... | null        |
| 3    | null        | 10000       | null        | ... | null        |
+------+-------------+-------------+-------------+-----+-------------+

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

题解：使用 case条件选择

select id,
    sum(case month when 'Jan' then revenue end) as Jan_Revenue,
    sum(case month when 'Feb' then revenue end) as Feb_Revenue,
    sum(case month when 'Mar' then revenue end) as Mar_Revenue,
    sum(case month when 'Apr' then revenue end) as Apr_Revenue,
    sum(case month when 'May' then revenue end) as May_Revenue,
    sum(case month when 'Jun' then revenue end) as Jun_Revenue,
    sum(case month when 'Jul' then revenue end) as Jul_Revenue,
    sum(case month when 'Aug' then revenue end) as Aug_Revenue,
    sum(case month when 'Sep' then revenue end) as Sep_Revenue,
    sum(case month when 'Oct' then revenue end) as Oct_Revenue,
    sum(case month when 'Nov' then revenue end) as Nov_Revenue,
    sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id;

---------±--------+
| id | int |
| revenue | int |
| month | varchar |
±--------------±--------+
(id, month) 是表的联合主键。
这个表格有关于每个部门每月收入的信息。
月份（month）可以取下列值 [“Jan”,“Feb”,“Mar”,“Apr”,“May”,“Jun”,“Jul”,“Aug”,“Sep”,“Oct”,“Nov”,“Dec”]。

编写一个 SQL 查询来重新格式化表，使得新的表中有一个部门 id 列和一些对应 **每个月** 的收入（revenue）列。

查询结果格式如下面的示例所示：

Department 表：
±-----±--------±------+
| id | revenue | month |
±-----±--------±------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
±-----±--------±------+

查询得到的结果表：
±-----±------------±------------±------------±----±------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue |
±-----±------------±------------±------------±----±------------+
| 1 | 8000 | 7000 | 6000 | … | null |
| 2 | 9000 | null | null | … | null |
| 3 | null | 10000 | null | … | null |
±-----±------------±------------±------------±----±------------+

注意，结果表有 13 列 (1个部门 id 列 + 12个月份的收入列)。

### 题解：使用 case条件选择

```sql
select id,
    sum(case month when 'Jan' then revenue end) as Jan_Revenue,
    sum(case month when 'Feb' then revenue end) as Feb_Revenue,
    sum(case month when 'Mar' then revenue end) as Mar_Revenue,
    sum(case month when 'Apr' then revenue end) as Apr_Revenue,
    sum(case month when 'May' then revenue end) as May_Revenue,
    sum(case month when 'Jun' then revenue end) as Jun_Revenue,
    sum(case month when 'Jul' then revenue end) as Jul_Revenue,
    sum(case month when 'Aug' then revenue end) as Aug_Revenue,
    sum(case month when 'Sep' then revenue end) as Sep_Revenue,
    sum(case month when 'Oct' then revenue end) as Oct_Revenue,
    sum(case month when 'Nov' then revenue end) as Nov_Revenue,
    sum(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id;