前言

hello，前面讲了很多mysql东西，但是没讲到基础篇，例如sql怎么写，ddl、dml、以及约束等等知识点

一、SQL的概念

1.1、什么是SQL

结构化查询语言(Structured Query Language)简称SQL,SQL语句就是对数据库进行操作的一种语言。

1.2、SQL作用

通过SQL语句我们可以方便的操作数据库中的数据库、表、数据。
SQL是数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持SQL语句，但都有特有内容。

1.3、分类

• DDL(Data Definition Language)数据定义语言
  用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter等
• DML(Data Manipulation Language)数据操作语言
  用来对数据库中表的数据进行增删改。关键字：insert, delete, update等
• DQL(Data Query Language)数据查询语言
  用来查询数据库中表的记录(数据)。关键字：select, where等
• DCL(Data Control Language)数据控制语言(了解)
  用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE等

1.4、常用数据库

• 关系型数据库：MySQL，Oracle，sqlserver等；
• 非关系型数据库：redis，memcache，mogodb，hadoop等。

区别：

• 关系型数据库通过外键关联来建立表与表之间的关系；
• 非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。

二、字段含义

char与varchar 的区别

• char(n) ：固定长度，会删除尾部空格。
• varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。不会删除尾部空格。

varchar(50)中50的涵义

字段最多存放 50 个字符
如 varchar(50) 和 varchar(200) 存储 "jay" 字符串所占空间是一样的，后者在排序时会消耗更多内存

text与blob 的区别

• text，只能保存字符数据，比如一篇文章，tinytext、text、mediumtext、longtext。
• blob，能用来保存二进制数据，比如照片，tinyblob、blob、mediumblob、longblob

float与double 的区别

• float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
• double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

UNION与UNION ALL的区别？

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
• UNION的效率低于 UNION ALL

DATETIME与TIMESTAMP的区别

DATETIME 的日期范围是 1001——9999 年；TIMESTAMP 的时间范围是 1970——2038 年
DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)
datetime类型适合用来记录数据的原始的创建时间，修改记录中其他字段的值，datetime字段的值不会改变，除非手动修改它。
timestamp类型适合用来记录数据的最后修改时间，只要修改了记录中其他字段的值，timestamp字段的值都会被自动更新。

drop、delete与truncate的区别

	delete	truncate	drop
类型	DML	DML	DML
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据 从数据库中	删除表，所有的数据行，索引和权限也会被删除
删除速度	删除速度慢，逐行删除	删除速度快	删除速度最快

三、DDL语句

获取当前数据库版本：select version()
创建数据库：create database dbname
显示数据库：show databases
进入数据库：use dbname
删除数据库：drop database dbname
显示表：show tables
创建表：create table tablename(column_name_1 column_type_1 constraints,column_name_2 column_type_2 constraints,...,column_name_n column_type_n constraints)
查看表的定义：desc tablename
查看表的定义(还可以看到表的engine(存储引擎)和charset(字符集))：show create table tablename \G（\G:使得记录能够按照字段竖向排列）
删除表：drop table tablename
修改表类型：alter table tablename modify column_name column_type
新增表字段：alter table tablename add column column_name column_type
删除表字段：alter table tablename drop column column_name
修改表字段：alter table tablename change old_column_name column_name column_type（modify和change都可以修改表的定义，change后面需要写两次列名,change可以修改列名，modify不能）
修改字段排列顺序：alter table tablename modify column_name column_type first;alter table tablename add column column_name_1 column_type after column_name_2
更改表名：alter table tablename rename new_tablename

3.1、操作库

• 1、创建数据库

-- 创建数据库
create databases 数据库名

-- 判断是否存在并创建
create databases if not exists 库名

-- 创建数据库并设置字符集
create databases 数据库名 character set 字符集;

• 2、查看数据库

--  查看所有数据库
show databases;
-- 查看某个数据库定义信息
show create databases 库名;

• 3、修改数据库

alter database 库名 default character set 字符集;

• 4、删除数据库

drop database 库名;

3.2、操作表

• 创建表

CREATE TABLE student (
     id INT,
     name VARCHAR(20),
     birthday DATE
);

• 查看表

-- 查看某个数据库中的所有表
SHOW TABLES;
-- 查看表结构
DESC 表名;
-- 查看创建表的sql
show create table 表名;

• 拷贝表

 CREATE TABLE 新表名 LIKE 旧表名;

• 删除表

-- 删除表
DROP table 表
-- 判断表是否存在并删除表
DROP TABLE IF EXISTS 表名;

• 修改表结构

-- 添加列
ALTER TABLE 表名 ADD 列名 类型;
-- 修改列类型
ALTER TABLE 表名 MODIFY 列名 类型;
-- 修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
-- 删除列
ALTER TABLE 表名 DROP 列名;
-- 修改表名
RENAME TABLE 表名 TO 新表名;
-- 修改字符集
ALTER TABLE 表名 CHARACTER SET 字符集;

四、DML语句

用来对数据库中表的数据进行增删改。关键字：insert, delete, update等

插入记录：insert into tablename (field1,field2...,fieldn) values (value1,value2...,valuen);

插入多条记录：insert into tablename (field1,field2...,fieldn) values (value1,value2...,valuen),(value1,value2...,valuen),(value1,value2...,valuen);

replace into：跟 insert 功能类似，不同点在于：replace into 首先尝试插入数据到表中，如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据。否则，直接插入新数据。

更新记录：update tablename set field1=value1,field2=value2 where condition;

同时更新多表记录：update tablename1,tablename2 set tablename1.field1=tablename2.field1 where condition;

删除记录：delete from tablename where condition;
同时删除多表记录：delete t1,t2 from t1,t2 where condition;（如果from后的表名用别名,则delete后也要用相应的别名）

查询记录：select * from tablename where condition;

查询不重复的记录：select distinct name from user;（distinct只能返回它的目标字段,而无法返回其他字段）select distinct name,id from user;（会认为要过滤掉name和id两个字段都重复的记录）select id,distinct name from user;（mysql会报错,因为distinct必须放在要查询字段的开头）

统计不重复字段的条数：select count(distinct name) from user;

排序和限制：select * from tablename where condition order by field1 [DESC/ASC],field2 [DESC/ASC],... [LIMIT offset_start,row_count];
offset_start表示记录的起始偏移量,row_count表示显示的行数

聚合：select [field1,field2...] fun_name from tablename where condition group by field1,field2... with rollup having where condition;
fun_name表示要做的聚合操作，sum/count/avg/max/min
group by表示要进行分类聚合的字段
with rollup是可选参数,表示是否对分类聚合后的结果进行再汇总
having关键字表示对分类后的结果再进行条件的过滤

表连接:内连接选出两张表中互相匹配的记录,外连接选出其他不匹配的记录：
select field1,field2 from tablename1,tablename2 where tablename1.field3=tablename2.field3;
select field1,field2 from tablename1 left/right join tablename2 on tablename1.field3=tablename2.field3;

子查询in/not in/=/!=/exists/not exists等
select * from emp where deptno in(select deptno from dept);
select emp.* from emp,dept where emp.deptno=dept.deptno;

表连接在很多情况下用于优化子查询

组合查询
使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。每个查询必须包含相同的列、表达式和聚集函数。默认会去除相同行，如果需要保留相同行，使用 UNION ALL。只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

select * from tablename1
union/union all
select * from tablename2

4.1、插入记录

• 插入全部

INSERT INTO 表名 – 表示往哪张表中添加数据
(字段名1, 字段名2, …)  --  要给哪些字段设置值
VALUES (值1, 值2, …); -- 设置具体的值

• 插入部分字段

INSERT INTO 表名 (字段名1, 字段名2, 字段名3…) VALUES (值1, 值2, 值3);

• 蠕虫复制

INSERT INTO 表名1 SELECT * FROM 表名2;

4.2、更新

-- 不带条件修改数据
UPDATE 表名 SET 字段名=值;

-- 带条件修改数据
UPDATE 表名 SET 字段名=值 WHERE 字段名=值;

4.2、删除

DELETE FROM 表名 WHERE 字段名=值;

五、DQL语句

5.1、简单语句

-- 查询所有列
SELECT * FROM 表名;
-- 查询部分列
SELECT 字段名1, 字段名2, 字段名3, ... FROM 表名;
-- 别名查询
SELECT NAME AS 姓名, age AS 年龄 FROM student;
-- 剔除重复
SELECT DISTINCT 字段名 FROM 表名;
-- 查询结果参与运算（参与列必须数值类型）
SELECT 列名1 + 列名2 FROM 表名;

5.2、条件查询

• 比较运算符

>大于
<小于
<=小于等于
>=大于等于
=等于
<>、!=不等于

• 逻辑运算符

not
and 
not

• 范围

BETWEEN 值1 AND 值2

• like

SELECT * FROM 表名 WHERE 字段名 LIKE '通配符字符串';

• 排序分组

order by [ASC/DESC]

• 分组

group by
group_concat()

• LIMIT

LIMIT offset,length; 或者limit length;

• 聚合函数

count： 统计指定列记录数，记录为NULL的不统计
sum： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
max： 计算指定列的最大值
min： 计算指定列的最小值
avg： 计算指定列的平均值，如果不是数值类型，那么计算结果为0

六、数据库约束

对表中的数据进行进一步的限制，保证数据的正确性、有效性和完整性。

6.1、种类

• PRIMARY KEY: 主键
• UNIQUE: 唯一
• NOT NULL: 非空
• DEFAULT: 默认
• FOREIGN KEY: 外键

6.2、主键

用来唯一标识一条记录，每个表都应该有一个主键，并且每个表只能有一个主键。

-- 添加
字段名 字段类型 PRIMARY KEY
-- 删除
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 主键自增
AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

6.3、唯一

在这张表中这个字段的值不能重复

字段名 字段类型 UNIQUE

6.4、非空

这个字段必须设置值,不能是NULL

字段名 字段类型 NOT NULL

6.5、外键

现在用的非常少，基本不用。他在删除时候有个约束，比喻A表有B表外键，删除A表某字段是删除不了的，除非先删除B表

七、8.0新特性

7.1、通用表达式（8.0）

1）非递归CTE

• AS
• WITH字句

WITH ct1(id) AS (SELECT 1),
ct2(id) AS (SELECT id + 1 FROM ct1)
SELECT * FROM ct2 JOIN ct1

2）递归CTE

WITH RECURSIVE ct(num) AS
(
SELECT 1 UNION ALL 
SELECT num + 1 FROM ct WHERE num < 10
)
SELECT * FROM ct

3）递归限制参数

-- 循环次数
show variables like 'cte_max%';
set session cte_max_recursion_depth = 10;
-- 最大执行时间
show variables like 'max_execution%';
set session max_execution_time=1000

7.2、分析函数（8.0）

和聚合函数类似，每行生成一个结果

SELECT `name`, is_overtime,
 sum(is_overtime) OVER (PARTITION by `name`) AS name_is_overtime
	FROM software_version 
	ORDER BY name_is_overtime DESC

1）种类

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• PERCENT_RANK()
• FIRST_VALUE()
• LAST_VALUE()
• LEAD()
• LAG()
• CUME_DIST()
• NTH_VALUE()
• NTILE()

2） 窗口定义

window function(expr)
	OVER(
    	PARTITION BY
        ORDER BY
        frame_clause
    )

7.3、InnoDB增强（8.0）

1）集成数据字典
删除了.frm .opt后缀文件
将系统mysql和数据字典表全部改成innoDB存储引擎
简化INFORMATION_SCHEMA实现，提升了访问性能

2）原子DDL操作
操作表时候，具备原子性。例如删除一张表，其中一张失败，其他也会失败。

3）自增列持久化

• 5.7及以前版本，自增列计数器储存在内存中，最后一条数据被删除后退出重新登陆，会覆盖删除自增列新增数据（bug）
• 8.0后不会这样，因为最大值会写入redo_log，并且每次检查并写入引擎私有系统表

4）死锁检查控制
增加了一个动态变量，用于控制系统是否执行INnoDB死锁检查，对并发系统，禁用死锁检查可能带来性能提升
innodb_deadlock_delect

5）锁定语句选项

select ... for share 和 select ... for update 中支持 nowait、skip locked选项

对于nowait，如果请求行被其他事务锁定时，语句即可返回
对于skip locked，从返回结果集中一处被锁定行

7.4、JSON增强（8.0）

-- 获取某个值
with doc(data) as ( select json_object('id', '3', 'name', 'JONY') )
select data->>'$.name' from doc
-- 路径表达式
select json_extract( '["a", "b", "c", "d"]' )

1）聚合函数

• JSON_ARRAYAGG() 生成json数组
• JSON_OBJECTAGG() 生成json对象

SELECT JSON_ARRAYAGG(name), is_overtime FROM software_version	
	GROUP BY is_overtime

SELECT JSON_OBJECTAGG(name, id), is_overtime FROM software_version
	GROUP BY is_overtime

2）实用函数

• JSON_PRETTY() JSON美化
• JSON_STRORAGE_SIZE() 查看json占用大小
• JSON_STRORAGE_FREE() 释放了大小

3）合并函数

• JSON_MERGE_PATCH()
• JSON_MERGE_PRESERV()

select JSON_MERGE_PATCH('{"a":1, "b": 2},{"a":3, "b": 4}')

4）表函数

• JSON_TABLE() 将json数据转换为关系表，可以将函数返回结果当成一个表，从而可以用sql查询

八、三范式

8.1、第一范式(确保每列保持原子性，一组数组不能出现多条)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，意思就是说要求数据库中的表都是二维表，不能存在一组数据用两条构成

8.2、第二范式(确保业务和表是有关的)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说不同业务应该存在多张表

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。
这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

8.3、第三范式(确保每列都和主键列直接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必在订单信息表中多次输入客户信息的内容，减小了数据冗余。

九、开发常用的数据库对象

9.1、视图

什么是视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。

为什么使用视图

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。

视图特点：

视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
视图是由基本表(实表)产生的表(虚表)。
视图的建立和删除不影响基本表。
对视图内容的更新(添加，删除和修改)直接影响基本表。
当视图来自多个基本表时，不允许添加和删除数据。

视图的常见使用场景：

重用SQL语句；
简化复杂的SQL操作。
使用表的组成部分而不是整个表；
保护数据
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的优点，缺点

查询简单化。视图能简化用户的操作
数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护
逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

视图用途

简化sql查询，提高开发效率，兼容老的表结构。

9.2、游标

标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

9.3、存储过程

存储过程，就是一些编译好了的SQL语句，这些SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后给这些代码块取一个名字，在用到这个功能的时候调用即可。
优点

• 过程是一个预编译的代码块，执行效率比较高
• 存储过程在服务器端运行，减少客户端的压力
• 允许模块化程序设计，只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用
• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
• 可以一定程度上确保数据安全

缺点

• 调试麻烦
• 植性不灵活
• 编译问题

9.4、触发器

触发器，指一段代码，当触发某个事件时，自动执行这些代码。
场景

• 可以通过数据库中的相关表实现级联更改。
• 实时监控某张表中的某个字段的更改而需要做出相应的处理。
• 例如可以生成某些业务的编号。
• 注意不要滥用，否则会造成数据库及应用程序的维护困难。
  MySQL中都有哪些触发器？
• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Delete

十、内连接、左连接、右连接

• 接是把匹配的关联数据显示出来；
• 左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。
• 外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

left join

A left join B on ... and ... 不会过滤结果记录条数，只会根据and后的条件是否显示 B表的记录，A表的记录一定会显示。

用left join时，on 和 where 条件的区别如下：

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

十一、ORDER BY

11.1、执行计划

• 计划的key这个字段，表示使用到索引idx_city
• Extra 这个字段的 Using index condition 表示索引条件
• Extra 这个字段的 Using filesort表示用到排序

11.2、全字段排序

MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。什么时候把字段放进去排序呢，其实是通过idx_city索引找到对应的数据，才把数据放进去啦。

加上order by之后，整体的执行流程就是：

1、SQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
2、引树idx_city， 找到第一个满足 city='深圳’条件的主键 id，也就是图中的id=9；
3、键 id 索引树拿到id=9的这一行数据， 取name、age、city三个字段的值，存到sort_buffer；
4、引树idx_city 拿到下一个记录的主键 id，即图中的id=13；
5、步骤 3、4 直到city的值不等于深圳为止；
6、5步已经查找到了所有city为深圳的数据，在 sort_buffer中，将所有数据根据age进行排序；
7、排序结果取前10行返回给客户端。

11.3、磁盘临时文件辅助排序

实际上，sort_buffer的大小是由一个参数控制的：sort_buffer_size。如果要排序的数据小于sort_buffer_size，排序在sort_buffer 内存中完成，如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序

如何确定是否使用了磁盘文件来进行排序呢？可以使用以下这几个命令

## 打开optimizer_trace，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
select name,age,city from staff where city = '深圳' order by age limit 10;
## 查询输出的统计信息
select * from information_schema.optimizer_trace 

11.4、order by的一些优化思路

• 数据是无序的，所以就需要排序。如果数据本身是有序的，那就不用排了。而索引数据本身是有序的，我们通过建立联合索引，优化order by 语句。
• 我们还可以通过调整max_length_for_sort_data等参数优化；

11.5、联合索引优化

索引本身是有序的，我们可以看下idx_city_age联合索引示意图
整个SQL执行流程变成酱紫：

• 从索引idx_city_age找到满足city='深圳’ 的主键 id
• 到主键 id索引取出整行，拿到 name、city、age 三个字段的值，作为结果集的一部分直接返回
• 从索引idx_city_age取下一个记录主键id
• 重复步骤 2、3，直到查到第10条记录，或者是不满足city='深圳’ 条件时循环结束。

十二、常用函数

12.1、字符串函数

concat(s1,s2,…,sn)：连接为一个字符串，有null时结果为null；
insert(str,x,y,instr)：将字符串str从x位置开始，y个字符长的子串替换为字符串instr；
lower(str)和upper(str)：把字符串转换成小写或大写；
left(str,x)和right(str,x)：返回字符串最左边和最右边x个字符，如果为null则不返回；
lpad(str,n,pad)和rpad(str,n,pad)：用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度；
ltrim(str)和rtrim(str)：去掉字符串str左侧和右侧空格；
repeat(str,x)：返回str重复x次的结果；
replace(str,a,b)：用字符串b替换字符串str中所有出现的字符串a；
strcmp(s1,s2)：比较字符串s1和s2的ASCII码值的大小(s1大返回1，相等返回0，s2大返回-1)；
trim(str)：去掉目标字符串开头和结尾的空格；
substring(str,x,y)：返回字符串str从x位置开始，y个字符长的字符串。

12.2、数值函数

abs(x)：返回x的绝对值；
ceil(x)：返回大于x的最小整数；
floor(x)：返回小于x的最大整数；
mod(x,y)：返回x/y的模(和x%y结果相同，若一个为null则结果为null)；
rand()：返回0~1的随机值；
round(x,y)：返回参数x的四舍五入的有y位小数的值(y默认为0)；
truncate(x,y)：返回数字x截断为y为小数的结果；

12.3、日期函数

curdate()：返回当前日期，只包含年、月、日；
curtime()：返回当前日期，只包含时、分、秒；
now()：返回当前日期，包含年、月、日、时、分、秒；
unix_timestamp(date)：返回日期date的UNIX时间戳；
from_unixtime(unixtime)：返回UNIXTIME时间戳的日期值；
week(date)和year(date)：返回所给日期是哪一周和哪一年；
hour(time)和minute(time)：返回所给时间的小时和分钟；
monthname(date)：返回date的英文月份名称；
date_format(date,fmt)：按字符串fmt格式化日期date值，；
date_add(date,interval expr type)：返回与所给日期date相差interval时间段的日期(interval是间隔类型关键字，expr是一个表达式，对应后面的类型，type是间隔类型)；
datediff(date1,date2)：计算两个日期之间相差的天数；

12.4、流程函数

-- 如果value是真，返回t，否则返回f；
if(value,t,f)
-- 如果value1不为空，返回value1，否则返回value2；
ifnull(value1,value2)
-- 如果value1是真，返回result1，否则返回default。这是case的简单函数用法，case后面跟列名或者列的表达式，when后面枚举这个表达式所有可能的值，但不能是值的范围。例：select userid,salary,case salary when 1000 then ‘low’ when 2000 then ‘low’ else ‘high’ end salary_level from salary;
case[expr]when[value1]then[result1]when[value2]then[result2]…else[default]end
-- 如果expr等于result1，否则返回default。这是case的搜索函数用法，直接在when后面写条件表达式，并且只返回第一个符合条件的值。例：select userid,salary,case when salary<=2000 then ‘low’ else ‘high’ end as salary_level from salary;
case when[expr]then[result1] …else[default]end：

12.5、JSON函数

创建JSON

-- 创建JSON数组；
json_array([val1,val2,…,valn])
-- 创建JSON对象；
json_object([key1,val1, key1,val1,…])
-- 加上/去掉JSON文档两边的双引号；
json_quote(string)/json_unquote(string)

查询JSON

-- 查询文档中是否包含指定的元素。查询指定元素candidate是否包含在目标JSON文档target中，包含返回1，否则返回0(path参数可选，可以在指定路径下查询)，如果有参数为null或path不存在，则返回null。如果JSON文档为对象，则路径格式为$.a(表示key为a)、$.a.b(通常用在value也是对象列表的情况，表示键a下层的键b)。如果JSON文档为数组，则路径格式为$[i](表示数组中第i个元素)。
json_contains(target,candidate[,path])
-- 查询文档中是否包含指定的路径。one_or_all只能取一个，one表示只要有一个存在即可，all表示所有的都存在才行。查询JSON文档中是否存在指定路径，存在返回1，否则返回0，如果有参数为null或path不存在，则返回null。
json_contains_path(json_doc,one_or_all,path[,path])
-- 根据条件提取文档中的数据如果有参数为null或path不存在，则返回null。如果提取多个path，则返回的数据合并在一个JSON ARRAT里。
json_extract(json_doc,path[,path])
-- 提取所有key的集合；
json_keys(json_doc[,path])
-- 返回所有符合条件的路径集合。one_or_all：one查询到一个即返回，all表示查询所有。search_str要查询的字符串，可以用like里的’%’或’_’匹配。
json_search(json_doc,one_or_all,search_str[,escape_char[,path]…])

查询JSON元数据

json_depth(json_doc)：JSON文档的深度；
json_length(json_doc[,path]) ：JSON文档的长度；
json_type(json_val)：JSON文档类型
json_valid(val)：JSON格式是否合法，字符串两边不加双引号是无效的JSON格式。

修改JSON

json_array_append(json_doc,path,val[,path,val]…)：数组尾部追加元素；
json_array_insert(json_doc,path,val[,path,val]…)：在数组的指定位置插入元素；
json_remove(json_doc,path [,path]…)：删除文档中指定位置的元素；
json_replace(json_doc,path,val[,path,val]…)：替换文档中指定位置的元素；
json_set(json_doc,path,val[,path,val]…)：给稳定中指定位置的元素设置新值，如果元素不存在，则进行插入；
json_merge_preserve(json_doc, json_doc[,json_doc]…)：将两个文档合并。

JSON工具函数

json_pretty(json_val)：梅花JSON格式；
json_storage_size(json_val)：JSON文档占用的存储空间；
json_storage_free(json_val)：JSON文档更新操作后剩余的空间；
json_table(expr,path columns(column_list) [as] alias)：将JSON文档转换为表格。expr即JSON对象数组，path是过滤路径
json_arrayagg(col_or_expr)：将聚合后参数中的多个值转换为JSON数组；
json_objectagg(key,value)：把两个列或者是表达式解释为一个key和一个value，返回一个JSON对象；

12.6、其他常用函数

函数	功能
content1	content2
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登陆用户名
inet_aton(IP)	返回IP地址的数字表示
inet_ntoa(num)	返回数字代表的IP地址
password(str)	返回字符串str的加密版本(41位)
MD5()	返回字符串str的MD5值

其他知识点

1、having与where的区别

having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合函数

2、SQL语句执行顺序
在实际执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的数据。

FROM > ON > JOIN > WHERE > GROUP BY > AGG_FUNC > WITH > HAVING > SELECT > UNION > DISTINCT > ORDER BY > LIMIT

3、mit 1000000加载很慢如何解决

• 如果id连续，可以返回上次查询的最大记录（偏移量），再往下limit

select id，name from employee where id>1000000 limit 10. 

• 业务允许情况下，没必要访问后面页数，限制页数
• order by + 索引（id索引）

select id，name from employee order by id  limit 1000000，10

• 利用延迟关联或者子查询优化超多分页场景（先找id段，再关联查询）

SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id

4、SQL数据库cpu飙升的话，要怎么处理呢？

• 排查思路：

  • 使用top诊断mysql还是其他原因
  • mysql导致就要看线程，看看是哪条sql出现问题
  • 找出消耗高的sql。用执行计划，优化sql，以及是都数据量过大导致的

• 处理：

  • kill这些线程，观察是否下降
  • 进行相应调整（索引、sql优化、内存参数）
  • 重跑SQL

• 其他情况：

  • 可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就
  • 跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

5、order by的一些优化思路

• 因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不用排了。而索引数据本身是有序的，我们通过建立联合索引，优化order by 语句。
• 还可以通过调整max_length_for_sort_data等参数优化

6、索引存储顺序与order by不一致，如何优化？

假设有联合索引 idx_age_name, 我们需求修改为这样：查询前10个员工的姓名、年龄，并且按照年龄小到大排序，如果年龄相同，则按姓名降序排。对应的 SQL 语句就可以这么写：
select name,age from staff  order by age ,name desc limit 10;

我们看下执行计划，发现使用到Using filesort。
这是因为，idx_age_name索引树中，age从小到大排序，如果age相同，再按name从小到大排序。而order by 中，是按age从小到大排序，如果age相同，再按name从大到小排序。也就是说，索引存储顺序与order by不一致。

• 如果MySQL是8.0版本，支持Descending Indexes

7、使用了in条件多个属性时，SQL执行是否有排序过程

如果我们有联合索引idx_city_name，执行这个SQL的话，是不会走排序过程的
这是因为:in有两个条件，在满足深圳时，age是排好序的，但是把满足上海的age也加进来，就不能保证满足所有的age都是排好序的。因此需要Using filesort。