MySQL数据库从入门到精通全攻略

NoSQL有如下优点：易扩展，NoSQL数据库种类繁多，但是一个共同的特点都是去掉关系数据库的关系型特性。数据之间无关系，这样就非常容易扩展。无形之间也在架构的层面上带来了可扩展的能力。大数据量，高性能，NoSQL数据库都具有非常高的读写性能，尤其在大数据量下，同样表现优秀。这得益于它的无关系性，数据库的结构简单。

数据库管理系统（DBMS）

数据库管理系统（Database Management System，简称DBMS）是为管理数据库而设计的电脑软件系统，一般具有存储、截取、安全保障、备份等基础功能。

数据库管理系统是数据库系统的核心组成部分，主要完成对数据库的操作与管理功能，实现数据库对象的创建、数据库存储数据的查询、添加、修改与删除操作和数据库的用户管理、权限管理等。

常见的数据库管理系统有：MySQL、Oracle、DB2、MS SQL Server、SQLite、PostgreSQL、Sybase等。

SQL

结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。

SQL的重要分类

DQL

数据查询语言（Data Query Language, DQL）是SQL语言中，负责进行数据查询而不会对数据本身进行修改的语句，这是最基本的SQL语句。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有FROM，WHERE，GROUP BY，HAVING和ORDER BY。这些DQL保留字常与其他类型的SQL语句一起使用。

DDL

数据定义语言 (Data Definition Language, DDL) 是SQL语言集中，负责数据结构定义与数据库对象定义的语言，由CREATE、ALTER与DROP三个语法所组成，最早是由 Codasyl (Conference on Data Systems Languages) 数据模型开始，现在被纳入 SQL 指令中作为其中一个子集。

DML

数据操纵语言（Data Manipulation Language, DML）是SQL语言中，负责对数据库对象运行数据访问工作的指令集，以INSERT、UPDATE、DELETE三种指令为核心，分别代表插入、更新与删除。

DCL

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成。DCL以控制用户的访问权限为主，GRANT为授权语句，对应的REVOKE是撤销授权语句。

TPL

数据事务管理语言（Transaction Processing Language）它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。

CCL

指针控制语言（Cursor Control Language），它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

MySQL命令行基本命令

show databases;        # 列出当前数据库管理系统中有哪些数据库

create database bjpowernode;    # 创建数据库，起名bjpowernode

use bjpowernode;        # 使用bjpowernode数据库

select database();    # 查看用的是哪个数据库

show tables;            # 查看当前数据库中有哪些表

drop database bjpowernode;    # 删除数据库bjpowernode

select version();        # 查看当前mysql版本

数据库表的概述

表是数据库存储数据的基本单元，数据库存储数据的时候，是将数据存储在表对象当中的。

为什么将数据存储在表中呢？因为表存储数据非常直观。

任何一张表都有行和列：

行：记录（一行就是一条数据）
列：字段（name字段、age字段、gender字段）

每个字段包含以下属性：

字段名：name、age、gender都是字段的名字
字段的数据类型：每个字段都有数据类型，比如：字符类型、数字类型、日期类型
字段的数据长度：每个字段有可能会有长度的限制
字段的约束：比如某些字段要求该字段下的数据不能重复、不能为空等，用来保证表格中数据合法有效

二、DQL语言（重点）

查询时字段起别名

select ename, sal * 12 as yearsal from emp;

# 其中as关键字可以省略，但是别名中不能有空格


# 如果别名中有空格的话，可以将这个别名使用双引号或者 单引号 将其括起来
select ename, sal * 12 "year sal" from emp;
select ename, sal * 12 'year sal' from emp;

条件查询

select 
  ...
from
  ...
where
  过滤条件;

过滤条件放在where子句当中，以上语句的执行顺序是：

第一步：先执行from
第二步：再通过where条件过滤
第三步：最后执行select，查询并将结果展示到控制台

条件	说明
=	等于
<>或!=	不等于
>=	大于等于
<=	小于等于
>	大于
<	小于
between...and...	等同于 >= and <=，必须是左小右大
is null	为空
is not null	不为空
<=>	安全等于（可读性差，很少使用了）。
and 或 &&	并且
or 或 \|\|	或者
in	在指定的值当中
not in	不在指定的值当中
exists	用于检查子查询的查询结果行数是否大于0，如果行数大于0，那么EXISTS条件为真。
not exists
like	模糊查询

and和or的优先级问题

and和or同时出现时，and优先级较高，会先执行，如果希望or先执行，这个时候需要给or条件添加小括号。

is null 和 is not null

判断某个数据是否为null，不能使用等号，只能使用 is null

判断某个数据是否不为null，不能使用不等号，只能使用 is not null

在数据库中null不是一个值，不能用等号和不等号衡量，null代表什么也没有，没有数据，没有值。

in 和 not in

select
  ename,sal,job
from
  emp
where
  job in('MANAGER', 'SALESMAN');

in的执行原理实际上是采用 = 和 or 的方式。

select * from emp where comm = NULL or comm = 300;

其中NULL不能用等号=进行判断，所以comm = NULL结果是false，然而中间使用的是or，所以comm = NULL被忽略了。因此可知 in是自动忽略NULL，但是not in是不会自动忽略NULL的。

in 和 or的效率对比

or的效率为O(n)，而in的效率为O(log n), 当n越大的时候效率相差越明显（也就是说数据量越大的时候，in的效率越高）。

模糊查询 like

select .. from .. where 字段 like '通配符表达式';

# 例子
select ename from emp where ename like 'S%';

在模糊查询中，通配符主要包括两个：一个是%，一个是下划线。其中%代表任意多个字符。下划线代表任意一个字符。

排序操作

select .. from .. order by 字段 asc/desc


select ename,sal from emp where job = 'MANAGER' order by sal asc;

默认采用升序排列；

where先执行，order by语句是最后执行的

distinct 去重

select distinct job from emp;

数据处理函数

字符串相关

转大写upper和ucase

# 查询所有员工名字，以大写形式展现
select upper(ename) as ename from emp;

select ucase(ename) as ename from emp;


select ename, job, sal from emp where upper(ename) = 'SMITH';

转小写lower和lcase

# 查询员工姓名，以小写形式展现
select lower(ename) as ename from emp;
select lcase(ename) as ename from emp;

截取字符串substr

语法：substr('被截取的字符串', 起始下标, 截取长度)

有两种写法：第一种：substr('被截取的字符串', 起始下标, 截取长度)

第二种：substr('被截取的字符串', 起始下标)，当第三个参数“截取长度”缺失时，截取到字符串末尾

注意：起始下标从1开始，不是从0开始。（1表示从左侧开始的第一个位置，-1表示从右侧开始的第一个位置。）

select ename from emp where substr(ename, 2, 1) = 'A';

获取字符串长度length

一个汉字是2个长度；

获取字符的个数char_length

一个汉字是1个长度；

字符串拼接

语法：concat('字符串1', '字符串2', '字符串3'....)拼接的字符串数量没有限制。

去除字符串前后空白trim

select concat(trim('    abc    '), 'def');

# 默认是去除前后空白，也可以去除指定的前缀后缀


# 去除前置0
select trim(leading '0' from '000111000');

# 去除后置0
select trim(trailing '0' from '000111000');


# 前置0和后置0全部去除
select trim(both '0' from '000111000');

数字相关

rand()和rand(x)

rand()生成0到1的随机浮点数。

rand(x)生成0到1的随机浮点数，通过指定整数x来确定每次获取到相同的浮点值。

round(x)和round(x,y)四舍五入

round(x) 四舍五入，保留整数位，舍去所有小数

round(x,y) 四舍五入，保留y位小数

truncate(x, y)舍去，只剩y位小数

ceil与floor

ceil函数：返回大于或等于数值x的最小整数（向上取整）
floor函数：返回小于或等于数值x的最大整数（向下取整）

空处理

ifnull(x, y)，空处理函数，当x为NULL时，将x当做y处理。

ifnull(comm, 0)，表示如果员工的津贴是NULL时当做0处理。

在SQL语句中，凡是有NULL参与的数学运算，最终的计算结果都是NULL：

# 因为有津贴comm是NULL,所以要空处理

select ename,(sal + ifnull(comm, 0)) * 12 as yearsal from emp;

日期和时间相关函数

获取当前日期和时间

now()和sysdate()的区别：

now()：获取的是执行select语句的时刻。
sysdate()：获取的是执行sysdate()函数的时刻。

select now();

select sysdate();

select now(), sleep(2), sysdate();

获取当前日期

curdate()
current_date()
current_date

获取当前时间

curtime()
current_time()
current_time

获取单独的年、月、日、时、分、秒

select year(now());

select month(now());

一次性提取一个给定日期的“年月日”部分，可以使用date()函数

select date(now());

一次性提取一个给定日期的“时分秒”部分，可以使用time()函数

select time(now());

date_add函数

date_add函数的作用：给指定的日期添加间隔的时间，从而得到一个新的日期。

date_add函数的语法格式：date_add(日期, interval expr 单位)。

select date_add('2025-07-25', interval 3 day)

# 在这个时间基础上添加-1微秒，也就是减去1微秒
select date_add('2025-07-25 10:10:10', interval -1 microsecond)

select date_sub('2025-07-25 10:10:10', interval 1 microsecond)

# 复合型时间
select date_add('2025-07-25 10:10:10', interval '3.2' day_hour)

date_format日期格式化函数

select date_format(now(), '%Y-%m-%d %H:%i:%s')

# %Y：四位年份    %y：两位年份

str_to_date函数

该函数的作用是将char类型的日期字符串转换成日期类型date，通常使用在插入和修改操作当中。（char类型转换成date类型）

drop table if exists t_student;
create table t_student(
  name varchar(255),
  birth date
);
desc t_student;


insert into t_student(name, birth) values('zhangsan', str_to_date('10/01/1985', '%m/%d/%Y'))

# str_to_date函数可以省略
insert into t_student(name, birth) values('zhangsan', '1985-01-01');

insert into t_student(name, birth) values('zhangsan', '1985/01/01');

dayofweek、dayofmonth、dayofyear函数

dayofweek：一周中的第几天（1~7），周日是1，周六是7。

dayofmonth：一个月中的第几天（1~31）

dayofyear：一年中的第几天（1~366）

last_day函数

获取给定日期所在月的最后一天的日期：

select last_day(now());

datediff函数

计算两个日期之间所差天数 (时分秒不算，只计算日期部分相差的天数)。

select date_diff('1970-02-01 20:10:30', '1970-01-01')

timediff函数

计算两个日期之间所差时间，例如日期1和日期2所差10:20:30，表示差10小时20分钟30秒。

select time_diff('1970-02-01 20:10:30', '1970-01-01')

if 函数

如果条件为TRUE则返回“YES”，如果条件为FALSE则返回“NO”

SELECT IF(500<1000, "YES", "NO");

select ename, if(ename='SMITH', sal * 1.1, sal) as sal from emp;

select ename, job, if(job='MANAGER', sal * 1.1, if(job='SALESMAN', sal * 1.2, sal) as sal from emp;

select ename, job 
case job 
when 'MANAGER' then sal * 1.1 
when 'SALESMAN' then sal * 1.2 
else sal 
end 
as sal 
from emp;

cast函数

cast函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型

语法：cast(值 as 数据类型)

可用数据类型

date：日期类型

time：时间类型

datetime：日期时间类型

signed：有符号的int类型（有符号指的是正数负数）

char：定长字符串类型

decimal：浮点型

select cast('2025-07-25 10:10:10' as date)    # 2025-07-25

select cast('123.456' as decimal(5, 1))        # 123.5

加密函数

md5函数，可以将给定的字符串经过md5算法进行加密处理，字符串经过加密之后会生成一个固定长度32位的字符串，md5加密之后的密文通常是不能解密的：

select md5('powernode');

分组函数

分组函数的执行原则：先分组，然后对每一组数据执行分组函数。

如果没有分组语句group by的话，整张表的数据自成一组。

分组函数包括五个：

max：最大值
min：最小值
avg：平均值
sum：求和
count：计数

/* 统计岗位数量 */
select count(distinct job) from emp;

分组函数组合使用

select count(*),max(sal),min(sal),avg(sal),sum(sal) from emp;

分组函数不能直接使用在where子句当中

分组查询

group by的执行是在where之后执行。

语法：group by 字段

group by 字段1,字段2,字段3....

select job, avg(sal) from emp group by job;


select deptno,max(sal) from emp group by deptno;

/* 找出每个部门不同岗位的平均薪资 */
select deptno,job,avg(sal) from emp group by deptno,job;


/*当select语句中有group by的话，select后面只能跟分组函数或参加分组的字段*/
select ename,deptno,avg(sal) from emp group by deptno; // 这个SQL执行后会报错。

having

having写在group by的后面，当你对分组之后的数据不满意，可以继续通过having对分组之后的数据进行过滤。

where的过滤是在分组前进行过滤。

使用原则：尽量在where中过滤，实在不行，再使用having。越早过滤效率越高。

/*查询每个部门平均薪资，找出平均薪资高于2000的*/

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

组内排序

/*substring_index函数*/
select substring_index('http://www.baidu.com','.', 1);    # http://www



/*group_concat函数*/
select group_concat(empno order by sal desc) from emp group by job;



/*案例：找出每个工作岗位的工资排名在前两名的*/
select substring_index(select group_concat(empno order by sal desc),',', 2) from emp group by job;

连接查询

笛卡尔积

当两张表进行连接查询时，如果没有任何条件进行过滤，最终的查询结果条数是两张表条数的乘积。为了避免笛卡尔积现象的发生，需要添加条件进行筛选过滤。
需要注意：添加条件之后，虽然避免了笛卡尔积现象，但是匹配的次数没有减少。
为了SQL语句的可读性，为了执行效率，建议给表起别名。

内连接

等值连接

连接时，条件为等量关系。案例：查询每个员工所在的部门名称，要求显示员工名、部门名。

select
	e.ename,d.dname
from
	emp e
inner join
	dept d
on
	e.deptno = d.deptno;

/*inner可省略*/

非等值连接

自连接

select
	e.ename 员工名, l.ename 领导名
from
	emp e
join
	emp l
on
	e.mgr = l.empno;

外连接

内连接是满足条件的记录查询出来。也就是两张表的交集。外连接是除了满足条件的记录查询出来，再将其中一张表的记录全部查询出来，另一张表如果没有与之匹配的记录，自动模拟出NULL与其匹配。

select 
  e.ename 员工名,l.ename 领导名 
from 
  emp e 
left join 
  emp l 
on
  e.mgr = l.empno;

全连接

MySQL不支持full join。oracle数据库支持。

/*查询所有的客户和订单*/
select 
 c.*,o.* 
from 
 t_customer c 
full join 
 t_order o 
on 
 c.cid = o.cid;

多张表连接

select 
 e.ename,d.dname,s.grade 
from 
 emp e 
join 
 dept d 
on 
 e.deptno = d.deptno 
join 
 salgrade s 
on 
 e.sal between s.losal and s.hisal;

子查询

select语句中嵌套select语句就叫做子查询。
select语句可以嵌套在哪里？
where后面、from后面、select后面都是可以的。

EXISTS

EXISTS 可以与 SELECT、UPDATE、DELETE 一起使用，用于检查另一个查询是否返回任何行；
EXISTS 可以用于验证条件子句中的表达式是否存在；
EXISTS 常用于子查询条件过滤，例如查询有订单的用户等。

exists与in区别

IN 操作符是根据指定列表中的值来判断是否满足条件，而 EXISTS 操作符则是根据子查询的结果是否有返回记录集来判断。
EXISTS 操作符通常比 IN 操作符更快，尤其是在子查询返回记录数很大的情况下。因为 EXISTS 只需要判断是否存在符合条件的记录，而 IN 操作符需要比对整个列表，因此执行效率相对较低。
IN 操作符可同时匹配多个值，而 EXISTS 只能匹配一组条件。

SELECT *
FROM products
WHERE product_name IN ('手机', '平板电脑')
AND EXISTS (
  SELECT *
  FROM orders
  WHERE orders.product_id = products.product_id
  AND order_amount > 1000
);



SELECT *
FROM products
WHERE product_name IN ('手机', '平板电脑')
AND product_id IN (
  SELECT product_id
  FROM orders
  WHERE order_amount > 1000
);

UNION和UNION ALL

不管是union还是union all都可以将两个查询结果集进行合并。

union会对合并之后的查询结果集进行去重操作。union all是直接将查询结果集合并，不进行去重操作。（union all和union都可以完成的话，优先选择union all，union all因为不需要去重，所以效率高一些。）

两个结果集合并时，列数量要相同。

Limit

limit作用：查询第几条到第几条的记录。通常是因为表中数据量太大，需要分页显示。
limit语法格式：
limit 开始下标, 长度

/*查询员工表前5条记录*/
select ename,sal from emp limit 0, 5;


/*如果下标是从0开始，可以简写为*/
select ename,sal from emp limit 5;

/*查询工资排名在前5名的员工（limit是在order by执行之后才会执行的）*/
select ename,sal from emp order by sal desc limit 5;

表格相关

创建表

create table t_user(
  no int,
  name varchar(10),
  gender char(1) default '男'
);

插入数据

insert into 表名(字段名1, 字段名2, 字段名3,......) values (值1,值2,值3,......);

字段名和值要一一对应。类型要一一对应，数量要一一对应。字段名也可以省略，如果字段名省略就表示把所有字段名都写上去了，并且顺序和建表时的顺序相同。

删除表

drop table 表名;

三、MySQL数据类型

数据类型（data_type）是指系统中所允许的数据的类型。数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL 的数据类型可以分为整数类型、浮点数类型、定点数类型、日期和时间类型、字符串类型、二进制类型等。

整数类型

tinyint：1个字节（微小整数）

smallint：2个字节（小整数）

mediumint：3个字节（中等大小的整数）

int（integer）：4个字节（普通大小整数）

bigint：8个字节（大整数）

浮点数类型

float：4个字节，单精度（最多5位小数）多余四舍五入；

double：8个字节，双精度（最多16位小数）

定点数类型

decimal：定点数类型。底层实际上采用 字符串 的形式存储数字。

精确值，基于十进制存储，无精度损失。

语法：decimal(m, d)例如：decimal(3, 2) 表示3个有效数字，2个小数。（有效数字最多65个，小数位最多30个）同样四舍五入；

日期和时间类型

year：1个字节，只存储年，格式YYYY

time：3个字节，只存储时间，格式HH:MM:SS / HHMMSS

date：3个字节，只存储年月日，格式：YYYY-MM-DD

datetime：8个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1000年~公元9999年）

timestamp：4个字节，存储年月日+时分秒，格式：YYYY-MM-DD HH:MM:SS（从公元1980年~公元2040年）或者格式为 YYYYMMDDHHMMSS（采用这种格式不需要使用单引号，当然你使用单引号也可以）

字符串类型

char

char(m)：m长度是0~255个字符。固定长度字符串，在定义时指定字符串列长。当保存时，在 右侧填充空格 以达到指定的长度。m表示列的长度，范围是 0～255 个字符。例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。

当插入的字符长度大于4，则报错（除非超过4个长度之后都是空格字符，则空格字符会自动被删除用来保证插入的成功）。

varchar

varchar(m)：m长度是0~16383个字符长度可变的字符串。varchar 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。例如，varchar(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。varchar在值保存和检索时尾部的空格仍保留。

text

tinytext 表示长度为 255字符的 TEXT 列。
text 表示长度为 65535字符的 TEXT 列。
mediumtext 表示长度为 16777215字符的 TEXT 列。
longtext 表示长度为 4294967295 或 4GB 字符的 TEXT 列。

enum

语法：<字段名> enum('值1','值2',...)
该字段插入值时，只能是指定的枚举值。

set

语法：<字段名> set('值1','值2','值3',...) 注意：值不可重复。
该字段插入值时，只能是指定的值。

二进制类型

BLOB类型：二进制大对象，可以存储图片、声音、视频等文件。

blob：小的，最大长度65535个字节
mediumblob：中等的，最大长度16777215个字节
longblob：大的，最大长度4GB的字节

DDL增删改表结构

/*创建一个学生表*/
create table t_student(
  no bigint,
  name varchar(255),
  age int comment '年龄'
);

/*查看建表语句*/
show create table 表名;

/*修改表名*/
alter table 表名 rename 新表名;

/*新增字段*/
alter table 表名 add 字段名 数据类型;

/*修改字段名*/
alter table 表名 change 旧字段名 新字段名 数据类型;

/*修改字段数据类型*/
alter table 表名 modify column 字段名 数据类型;

/*删除字段*/
alter table 表名 drop 字段名;

四、DML语句（表中的数据进行增删改）

insert

insert into t_stu(no,name,age) values(1,'jack',20),(2,'lucy',30);

delete

# 将所有记录全部删除
delete from 表名;

# 删除符合条件的记录
delete from 表名 where 条件;

下面这种方式不支持事务，不可以回滚，删了之后数据是永远也找不回来了。这种删除叫做：表被截断。注意：这个语句删除效率非常高，巨大的表，瞬间干掉所有数据。但不可恢复。

# 不属于DML操作
truncate table 表名;

update

update 表名 set 字段名1=值1, 字段名2=值2, 字段名3=值3 where 条件;

五、Constraint约束

创建表时，可以给表的字段添加约束，可以保证数据的完整性、有效性。

约束通常包括：

非空约束：not null
检查约束：check
唯一性约束：unique
主键约束：primary key
1. 主键：primary key，简称PK
2. 主键约束的字段不能为NULL，并且不能重复。
3. 任何一张表都应该有主键，没有主键的表可以视为无效表。
4. 主键值是这行记录的身份证号，是唯一标识。在数据库表中即使两条数据一模一样，但由于主键值不同，我们也会认为是两条完全的不同的数据。
5. 主键分类：
  根据字段数量分类：
  1. 复合主键（2个或2个以上的字段作为主键）
  2. 单一主键（1个字段作为主键）==>建议的

根据业务分类：

自然主键（主键和任何业务都无关，只是一个单纯的自然数据）===>建议的

业务主键（主键和业务挂钩，例如：银行卡账号作为主键）

外键约束：foreign key

/*非空约束*/
create table t_stu(
  no int,
  name varchar(255) not null,
  age int
);


/*检查约束*/
create table t_stu(
  no int,
  name varchar(255),
  age int,
  check(age > 18)
);


/*唯一性约束*/
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255) unique （not null 中间不用，连接）
);
/*email字段设置为唯一性，唯一性的字段值是可以为NULL的。但不能重复。*/

/*表级约束*/
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(email)
);

/*表级约束可以为多个字段添加联合唯一*/
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  unique(name,email)
);

/*约束名字*/
create table t_stu(
  no int,
  name varchar(255),
  email varchar(255),
  constraint t_stu_name_email_unique unique(name,email)
);



/*主键约束 + 主键自增*/
create table t_student(
  id bigint primary key auto increment,
  sno varchar(255) unique,
  sname varchar(255) not null
)

外键约束

外键约束：foreign key，简称FK。
添加了外键约束的字段中的数据必须来自其他字段，不能随便填。
假设给a字段添加了外键约束，要求a字段中的数据必须来自b字段，b字段不一定是主键，但至少要有唯一性。
外键约束可以给单个字段添加，叫做单一外键。也可以给多个字段联合添加，叫做复合外键。复合外键很少用。
a表如果引用b表中的数据，可以把b表叫做父表，把a表叫做子表。
1. 创建表时，先创建父表，再创建子表。
2. 插入数据时，先插入父表，在插入子表。
3. 删除数据时，先删除子表，再删除父表。
4. 删除表时，先删除子表，再删除父表。

create table t_school( 
  sno int primary key, 
  sname varchar(255) 
); 

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) 
);

级联删除

create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete cascade 
);


###删除约束
alter table t_student drop foreign key t_student_sno_fk;
###添加约束
alter table t_student add constraint t_student_sno_fk foreign key(sno) references t_school(sno) on delete cascade;


/*级联更新*/
create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on update cascade 
);


/*级联置空*/
create table t_student( 
  no int primary key, 
  name varchar(255), 
  age int, 
  sno int, 
  constraint t_school_sno_fk foreign key(sno) references t_school(sno) on delete set null 
);

六、数据库设计三范式

第一范式

任何一张表都应该有主键，每个字段是原子性的不能再分（必须遵守）

第二范式

建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖

第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段

七、视图

只能将select语句创建为视图。
创建视图

create or replace view 
	v_emp 
as 
	select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;

3.修改视图

alter view v_emp as 
	select e.ename,d.dname,d.deptno from emp e join dept d on e.deptno = d.deptno;

4.删除视图

drop view if exists v_emp;

5.对视图增删改（DML：insert delete update）可以影响到原表数据。

八、事务

事务是一个最小的工作单元。在数据库当中，事务表示一件完整的事儿。

用了事务机制之后，在同一个事务当中，多条DML语句会同时成功，或者同时失败，不会出现一部分成功，一部分失败的现象。

事务只针对DML语句有效：因为只有这三个语句是改变表中数据的。

insert
delete
update

事务四大特性ACID

原子性（Atomicity）：是指事务包含的所有操作要么全部成功，要么同时失败。
一致性（Consistency）：是指事务开始前，和事务完成后，数据应该是一致的。例如张三和李四的钱加起来是5000，中间不管进行过多少次的转账操作(update)，总量5000是不会变的。这就是事务的一致性。
隔离性（Isolation）：隔离性是当多个⽤户并发访问数据库时，⽐如操作同⼀张表时，数据库为每⼀个⽤户开启的事务，不能被其他事务的操作所⼲扰，多个并发事务之间要相互隔离。
持久性（Durability）：持久性是指⼀个事务⼀旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

在dos命令窗口中开启MySQL事务：start transaction; 或者：begin;

回滚事务：rollback;

提交事务：commit;

只要执行以上的rollback或者commit，事务都会结束。

MySQL默认情况下采用的事务机制是：自动提交。所谓自动提交就是只要执行一条DML语句则提交一次。

事务隔离级别

隔离级别从低到高排序：读未提交 < 读提交 < 可重复读 < 串行化

读未提交：A事务与B事务，A事务可以读取到B事务未提交的数据。这是最低的隔离级别。几乎两个事务之间没有隔离。

读提交：A事务与B事务，A事务可以读取到B事务提交之后的数据。Oracle数据库默认的就是这种隔离级别。

可重复读：A事务和B事务，A事务开启后，读取了某一条记录，然后B事务对这条记录进行修改并提交，A事务读取到的还是修改前的数据。这种隔离级别称为可重复读。

串行化：隔离级别最高，避免了所有的问题，缺点是效率低，因为这种隔离级别会导致事务排队处理，不支持并发。

不同隔离级别会存在不同的现象，现象按照严重性从高到低排序：脏读 > 不可重复读 > 幻读

查看与设置隔离级别

mysql默认的隔离级别：可重复读（REPEATABLE READ）。

查看当前会话的隔离级别：select @@transaction_isolation;
查看全局的隔离级别：select @@gobal.transaction_isolation;

设置事务隔离级别：

会话级：set session transaction isolation level read committed;
全局级：set global transaction isolation level read committed;

事务现象

脏读

一个事务读取了另一个事务尚未提交的数据，即读取了另一个事务中的脏数据（Dirty Data）。在此情况下，如果另一个事务回滚了或者修改了这些数据，那么读取这些脏数据的事务所处理的数据就是不准确的。

不可重复读

在一个事务内，多次读取同一个数据行，得到的结果可能是不一样的。这是由于其他事务对数据行做出了修改操作，导致数据的不一致性。

幻读

在事务执行过程中，前后两次相同的查询条件得到的结果集不一致，可能会变多或变少。

可重复读的幻读问题

针对快照读（普通 select 语句），是 通过 MVCC 方式解决了幻读 ，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好的避免了幻读问题。
针对 当前读 （select ... for update 等语句），是 通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

快照读

什么是快照读？普通的select语句都是采用的快照读。顾名思义：在整个事务的处理过程中，执行相同的一个select语句时，每次都是读取的快照。（快照指的是固定的某个时刻的数据，就像现实世界中的拍照一样，把那个美好的时刻留下来）。也就是说，当事务隔离级别是可重复读，并且执行的select语句是一个普通的select语句时，都会采用快照读的方式读取数据，底层实现原理是：

底层由 MVCC（多版本并发控制）实现，实现的方式是开始事务后，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，所以事务过程中每次查询的数据都是一样的，即使中途有其他事务插入了新纪录，是查询不出来这条数据的，所以就很好的避免了幻读问题。

当前读

每一次都读取最新的数据。当前读包括：update、delete、insert、select...for update。这个很好理解，因为增删改的时候都要基于最新的数据进行增删改。而select...for update原理是：对查询范围内的数据进行加锁，不允许其它事务对这个范围内的数据进行增删改。也就是说这个select语句范围内的数据是不允许并发的，只能排队执行，从而避免幻读问题。

select...for update加的锁叫做：next-key lock。我们可以称其为：间隙锁 + 记录锁。

间隙锁用来保证在锁定的范围内不允许insert操作。记录锁用来保证在锁定的范围内不允许delete和update操作。

出现幻读的情况

在同一个事务处理过程中，如果前后两次都采用快照读，或者都采用当前读，则不会出现幻读问题。如果第一次使用快照读，后面使用了当前读，则会出现幻读问题。

九、DBA命令

新建用户

/*创建一个用户名为java1，密码设置为123的本地用户*/
create user 'java1'@'localhost' identified by '123';

/*创建一个用户名为java2，密码设置为123的外网用户*/
create user 'java2'@'%' identified by '123';

使用root用户查看系统中当前用户有哪些？

select user,host from mysql.user;

给用户授权

授权语法：grant [权限1，权限2...] on 库名.表名 to '用户名'@'主机名/IP地址';

给本地用户授权：grant [权限1，权限2...] on 库名.表名 to '用户名'@'localhost';

给外网用户授权：grant [权限1，权限2...] on 库名.表名 to '用户名'@'%';

所有权限：all privileges

细粒度权限：select、insert、delete、update、alter、create、drop、index(索引)、usage(登录权限)......

库名可以使用 * ，它代表所有数据库

表名可以采用 * ，它代表所有表

授权后必须刷新权限，才能生效：flush privileges

/*将所有库所有表的查询权限赋予本地用户java1*/
grant select,insert,delete,update,create on *.* to 'java1'@'localhost';
flush privileges;

/*将powernode库中所有表的所有权限赋予本地用户java1*/
grant all privileges on powernode.* to 'java1'@'localhost';

/*查看某个用户拥有哪些权限*/
show grants for 'java1'@'localhost'
show grants for 'java2'@'%'

/*with grant option*/
/*with grant option的作用是：java2用户也可以给其他用户授权了。*/
grant select,insert,delete,update on *.* to 'java2'@'%' with grant option;

撤销用户权限

revoke 权限 on 数据库名.表名 from '用户'@'IP地址';

/*撤销本地用户java1的insert、update、delete权限*/
revoke insert, update, delete on powernode.* from 'java1'@'localhost'

/*撤销外网用户java2的insert权限*/
revoke insert on powernode.* from 'java2'@'%'

revoke grant option on powernode.* from 'java2'@'%'

撤销权限后也需要刷新权限：flush privileges

修改用户的密码

具有管理用户权限的用户才能修改密码，例如root账户可以修改其他账户的密码：

/* 本地用户修改密码*/
alter user 'java1'@'localhost' identified by '456';

/* 外网用户修改密码*/
alter user 'java2'@'%' identified by '456';

修改密码后，也需要刷新权限才能生效：flush privileges

修改用户名

rename user '原始用户名'@'localhost' to '新用户名'@'localhost';
rename user '原始用户名'@'localhost' to '新用户名'@'%';

rename user 'java1'@'localhost' to 'java11'@'localhost';
rename user 'java11'@'localhost' to 'java123'@'%';

flush privileges

删除用户

drop user 'java123'@'localhost';

drop user 'java2'@'%';

flush privileges

数据备份

导出数据（登录mysql数据库之前进行）

/* 导出powernode这个数据库中所有的表*/
mysqldump powernode > e:/powernode.sql -uroot -p1234 --default-character-set=utf8

/* 导出powernode中emp表的数据*/
mysqldump powernode emp > e:/powernode.sql -uroot -p1234 --default-character-set=utf8

导入数据

在登录mysql之前进行

/* 先在登录mysql状态下新建一个数据库*/
create database powernode;

/* 在登录mysql之前执行以下命令*/
mysql powernode < e:/powernode.sql -uroot -p1234 --default-character-set=utf8

在登录mysql之后操作

create  database powernode;
use powernode;
source d:/powernode.sql

十、存储过程

存储过程可称为过程化SQL语言，是在普通SQL语句的基础上增加了编程语言的特点，把数据操作语句(DML)和查询语句(DQL)组织在过程化代码中，通过逻辑判断、循环等操作实现复杂计算的程序语言。

换句话说，存储过程其实就是数据库内置的一种编程语言，这种编程语言也有自己的变量、if语句、循环语句等。在一个存储过程中可以将多条SQL语句以逻辑代码的方式将其串联起来，执行这个存储过程就是将这些SQL语句按照一定的逻辑去执行，所以一个存储过程也可以看做是一组为了完成特定功能的SQL 语句集。

每一个存储过程都是一个数据库对象，就像table和view一样，存储在数据库当中，一次编译永久有效。并且每一个存储过程都有自己的名字。客户端程序通过存储过程的名字来调用存储过程。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

存储过程的代码

/*创建第一个存储过程*/
create procedure p1()
begin
	select empno,ename from emp;
end;

/*调用存储过程*/
call.p1();

/*删除存储过程*/
drop PROCEDURE if EXISTS p1;

/*查看创建存储过程的语句*/
show create procedure p1;

/*
通过系统表information_schema.ROUTINES查看存储过程的状态信息；这个系统表中的记录是专门描述该存储过程  对象状态  的。

同时该表还包含函数对象，触发器对象等的状态信息
*/
select 
	SPECIFIC_NAME, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE,
	ROUTINE_DEFINITION, CREATED, LAST_ALTERED, DATA_TYPE
from 
	information_schema.routines 
where 
	routine_name = 'p1';

delimiter命令

delimiter 命令用于改变 MySQL 解释语句的定界符。MySQL 默认使用分号 ; 作为语句的定界符。而使用 delimiter 命令可以将分号 ; 更改为其他字符，从而可以在 SQL 语句中使用分号 ;。

例如，假设需要创建一个存储过程。在存储过程中通常会包括多条 SQL 语句，而这些语句都需要以分号 ; 结尾。但默认情况下，执行到第一条语句的分号 ; 后，MySQL 就会停止解释，导致后续的语句无法执行。解决方式就是使用 delimiter 命令将分号改为其他字符，使分号 ; 不再是语句定界符。

delimiter //

CREATE PROCEDURE my_proc ()
BEGIN
SELECT * FROM my_table;
INSERT INTO my_table (col1, col2) VALUES ('value1', 'value2');
END //

delimiter ;

MySQL变量

mysql中的变量包括：系统变量、用户变量、局部变量。

系统变量

MySQL 系统变量是指在 MySQL 服务器运行时控制其行为的参数。这些变量可以被设置为特定的值来改变服务器的默认设置，以满足不同的需求。MySQL 系统变量可以具有全局（global）或会话（session）作用域。

全局作用域是指对所有连接和所有数据库都适用；
会话作用域是指只对当前连接和当前数据库适用。

/*查看系统变量*/
show [global|session] variables;

show [global|session] variables like '';

select @@[global|session.]系统变量名;

/*没有指定session或global时，默认是session*/


/*设置系统变量*/
set [global | session] 系统变量名 = 值;

set @@[global | session.]系统变量名 = 值;

用户变量

用户自定义的变量。只在当前会话有效。所有的用户变量'@'开始。

set @name = 'jackson';
set @age := 30;
set @gender := '男', @addr := '北京大兴区';
select @email := 'jackson@123.com';
select sal into @sal from emp where ename ='SMITH';


/*读取用户变量的值*/
select @name, @age, @gender, @addr, @email, @sal;

/*mysql中用户变量不需要声明。直接赋值就行。如果没有声明用户变量，直接读取该变量，返回null*/

局部变量

在存储过程中可以使用局部变量。使用declare声明。在begin和end之间有效。

变量的数据类型就是表字段的数据类型，例如：int、bigint、char、varchar、date、time、datetime等。注意：declare通常出现在begin end之间的开始部分。

create PROCEDURE p2()
begin 
	/*声明变量*/
	declare emp_count int default 0;
	/*声明变量*/
	declare sal double(10,2) default 0.0;
	
	/*给变量赋值*/
	select count(*) into emp_count from emp;
	/*给变量赋值*/
	set sal := 5000.0;
    /*给变量赋值*/
	set sal = 3000.0;

	/*读取变量的值*/
	select emp_count;
	/*读取变量的值*/
	select sal;
end;

if语句

/*员工月薪sal，超过10000的属于“高收入”，6000到10000的属于“中收入”，少于6000的属于“低收入”*/
create procedure p3(               )
begin
	declare sal int default 5000;
	declare grade varchar(20);
	if sal > 10000 then
  	set grade := '高收入';
	elseif sal >= 6000 then
  	set grade := '中收入';
	else
  	set grade := '低收入';
	end if;
	select grade;
end;

参数

存储过程的参数包括三种形式：

in：入参（未指定时，默认是in）
out：出参
inout：既是入参，又是出参

create procedure p4(in sal int, out grade varchar(20))
begin
	if sal > 10000 then
  	set grade := '高收入';
	elseif sal >= 6000 then
  	set grade := '中收入';
	else
  	set grade := '低收入';
	end if;
end;

create procedure p5(inout sal int)
begin
	set sal := sal * 1.1;
end;

/*调用存储过程*/
set @sal := 10000;
call p5(@sal);
select @sal;

case语句

create procedure mypro(in month int, out result varchar(100))
begin 
	case 
		when month = 3 or month = 4 or month = 5 then 
			set result := '春季';
		when  month = 6 or month = 7 or month = 8  then 
			set result := '夏季';
		when  month = 9 or month = 10 or month = 11  then 
			set result := '秋季';
		when  month = 12 or month = 1 or month = 2  then 
			set result := '冬季';
		else 
			set result := '非法月份';
	end case;
end;

/*调用存储过程*/
call mypro(9, @season);
select @season;

while循环

/*传入一个数字n，计算1~n中所有偶数的和。*/
create procedure mypro(in n int)
begin
	declare sum int default 0;
	while n > 0 do
  		if n % 2 = 0 then
    		set sum := sum + n;
  		end if;
  		set n := n - 1;
	end while;
	select sum;
end;

call mypro(10);

repeat循环

/*传入一个数字n，计算1~n中所有偶数的和。*/
create procedure mypro(in n int, out sum int)
begin 
	set sum := 0;
	repeat 
		if n % 2 = 0 then 
		  set sum := sum + n;
		end if;
		set n := n - 1;
		until n <= 0
	end repeat;
end;

call mypro(10, @sum);
select @sum;

loop循环

create procedure p9()

begin 
	declare i int default 0;
  mylp:loop 
		set i := i + 1;
		if i = 5 then 
			iterate mylp;    /*相当于continue,跳出本次循环*/
		end if;
		if i = 10 then 
		  leave mylp;    /*相当于break*/
		end if;
		select i;
	end loop;
end;

call p9();

游标cursor

游标（cursor）可以理解为一个指向结果集中某条记录的指针，允许程序逐一访问结果集中的每条记录，并对其进行逐行操作和处理。

使用游标时，需要在存储过程或函数中定义一个游标变量，并通过 DECLARE 语句进行声明和初始化。然后，使用 OPEN 语句打开游标，使用 FETCH 语句逐行获取游标指向的记录，并进行处理。最后，使用 CLOSE 语句关闭游标，释放相关资源。游标可以大大地提高数据库查询的灵活性和效率。

/*从dept表查询部门编号和部门名，创建一张新表dept2，将查询结果插入到新表中*/
create procedure p11()

BEGIN
	/*声明变量*/
	declare dept_no int;
	declare dept_name varchar(255);

	/*声明游标, 这样的语句必须放在普通变量后面*/
	declare dept_cursor cursor for select deptno,dname from dept;
	
	/*添加异常处理机制*/
-- 	declare exit handler for not found close dept_cursor;
	
	/*删除表dept2*/
	drop table if exists dept2;
	
	/*创建表dept2*/
	create table dept2(
		deptno int primary key,
		dname varchar(100)
	);
	
	/*打开游标*/
	open dept_cursor;
	
	/*通过游标取数据*/
-- 	fetch dept_cursor into dept_no, dept_name;
	
	/*查看变量的值*/
-- 	select dept_no, dept_name;
	
	/*使用循环从游标中取数据*/
	while true do
			fetch dept_cursor into dept_no, dept_name;
-- 			select dept_no, dept_name;
			insert into dept2(deptno, dname) values(dept_no, dept_name);
	end while;
	
	/*关闭游标*/
	close dept_cursor;

end;

call p11();

声明局部变量和声明游标有顺序要求，局部变量的声明需要在游标声明之前完成。

异常捕捉并处理

DECLARE handler_name HANDLER FOR condition_value action_statement

declare exit handler for not found close dept_cursor

handler_name 表示异常处理程序的名称，重要取值包括：
1. CONTINUE：发生异常后，程序不会终止，会正常执行后续的过程。(捕捉)
2. EXIT：发生异常后，终止存储过程的执行。（上抛）
condition_value 是指捕获的异常，重要取值包括：
1. SQLSTATE sqlstate_value，例如：SQLSTATE '02000'
2. SQLWARNING，代表所有01开头的SQLSTATE
3. NOT FOUND，代表所有02开头的SQLSTATE
4. SQLEXCEPTION，代表除了01和02开头的所有SQLSTATE
action_statement 是指异常发生时执行的语句，例如：CLOSE cursor_name

存储函数

存储函数：带返回值的存储过程。参数只允许是in（但不能写显示的写in）。没有out，也没有inout。

CREATE FUNCTION 存储函数名称(参数列表) RETURNS 数据类型 [特征]
BEGIN
	--函数体
	RETURN ...;
END;

“特征”的可取重要值如下：

deterministic：用该特征标记该函数为确定性函数（什么是确定性函数？每次调用函数时传同一个参数的时候，返回值都是固定的）。这是一种优化策略，这种情况下整个函数体的执行就会省略了，直接返回之前缓存的结果，来提高函数的执行效率。
no sql：用该特征标记该函数执行过程中不会查询数据库，如果确实没有查询语句建议使用。告诉 MySQL 优化器不需要考虑使用查询缓存和优化器缓存来优化这个函数，这样就可以避免不必要的查询消耗产生，从而提高性能。
reads sql data：用该特征标记该函数会进行查询操作，告诉 MySQL 优化器这个函数需要查询数据库的数据，可以使用查询缓存来缓存结果，从而提高查询性能；同时 MySQL 还会针对该函数的查询进行优化器缓存处理。

/*计算1-n所有偶数之和*/

-- 删除函数
drop function if exists sum_fun;

-- 创建函数
create function sum_fun(n int)
returns int deterministic 
begin 
	declare result int default 0;
	while n > 0 do 
		if n % 2 = 0 then 
			set result := result + n;
		end if;
		set n := n - 1;
	end while;
	return result;
end;

-- 调用函数
set @result = sum_fun(100);
select @result;

触发器

MySQL 触发器是一种数据库对象，它是与表相关联的特殊程序。它可以在特定的数据操作（例如插入（INSERT）、更新（UPDATE）或删除（DELETE））触发时自动执行。MySQL 触发器使数据库开发人员能够在数据的不同状态之间维护一致性和完整性，并且可以为特定的数据库表自动执行操作。

触发器的作用主要有以下几个方面：

强制实施业务规则：触发器可以帮助确保数据表中的业务规则得到强制执行，例如检查插入或更新的数据是否符合某些规则。
数据审计：触发器可以声明在执行数据修改时自动记日志或审计数据变化的操作，使数据对数据库管理员和 SQL 审计人员更易于追踪和审计。
执行特定业务操作：触发器可以自动执行特定的业务操作，例如计算数据行的总数、计算平均值或总和等。

MySQL 触发器分为两种类型: BEFORE 和 AFTER。BEFORE 触发器在执行 INSERT、UPDATE、DELETE 语句之前执行，而 AFTER 触发器在执行 INSERT、UPDATE、DELETE 语句之后执行。

CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW
BEGIN
-- 触发器执行的 SQL 语句
END;

其中：

trigger_name：触发器的名称
BEFORE/AFTER：触发器的类型，可以是 BEFORE 或者 AFTER
INSERT/UPDATE/DELETE：触发器所监控的 DML 调用类型
table_name：触发器所绑定的表名
FOR EACH ROW：表示触发器在每行受到 DML 的影响之后都会执行
触发器执行的 SQL 语句：该语句会在触发器被触发时执行

触发器的NEW和OLD关键字

在 MySQL 触发器中，NEW 和 OLD 是两个特殊的关键字，用于引用在触发器中受到修改的行的新值和旧值。具体而言：

NEW：在触发 INSERT 或 UPDATE 操作期间，NEW 用于引用将要插入或更新到表中的新行的值。
OLD：在触发 UPDATE 或 DELETE 操作期间，OLD 用于引用更新或删除之前在表中的旧行的值。

通俗的讲，NEW 是指触发器执行的操作所要插入或更新到当前行中的新数据；而 OLD 则是指当前行在触发器执行前原本的数据。

在MySQL 触发器中，NEW 和 OLD 使用方法是相似的。在触发器中，可以像引用表的其他列一样引用 NEW 和 OLD。例如，可以使用 OLD.column_name 从旧行中引用列值，也可以使用 NEW.column_name 从新行中引用列值。

/*假设有一个名为 my_table 的表，其中包含一个名为 quantity 的列。
当在该表上执行 UPDATE 操作时，以下触发器会将旧值 OLD.quantity 累加到新值 NEW.quantity 中*/
CREATE TRIGGER my_trigger
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
SET NEW.quantity = NEW.quantity + OLD.quantity;
END;

触发器操作日志

操作日志表

create table oper_log(
  id bigint primary key auto_increment,
	
  table_name varchar(100) not null comment '操作的哪张表',
  oper_type varchar(100) not null comment '操作类型包括insert delete update',
  oper_time datetime not null comment '操作时间',
  oper_id bigint not null comment '操作的那行记录的id',
  oper_desc text comment '操作描述'
);

触发器记录日志

/*
触发器：当向dept表中insert插入数据之后(after),在oper_log表中记录日志。
*/
create trigger dept_trigger_insert 
after insert on dept
for each row
begin
	insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','insert',now(),new.deptno,concat('插入数据：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc));
end;


/*
触发器：当向dept表中update更新数据之后(after),在oper_log表中记录日志。
*/

drop trigger if EXISTS dept_trigger_update;

create trigger dept_trigger_update
after update on dept
for each row
begin
	insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','update',now(),new.deptno,concat('更新前：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc, 
                                              ',更新后：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc));
end;


/*
触发器：当向dept表中删除数据之后(after),在oper_log表中记录日志。
*/

drop trigger if EXISTS dept_trigger_delete;

create trigger dept_trigger_delete
after delete on dept
for each row
begin
	insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','delete',now(),old.deptno,concat('删除了数据：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc));
end;

十一、存储引擎

MySQL常见的存储引擎包括InnoDB、MyISAM、Memory、Archive等。每个存储引擎都有自己的特点和适用场景。

例如，

InnoDB引擎支持事务和行级锁定，适用于需要高并发读写的应用；（未指定就是默认InnoDB）
MyISAM引擎不支持事务，但适用于读操作较多的应用；
Memory引擎数据全部存储在内存中，适用于对读写速度要求很高的应用等等。

指定和修改存储引擎

指定存储引擎

CREATE TABLE my_table (column1 INT, column2 VARCHAR(50)) ENGINE = InnoDB;

默认情况下，MySQL 8的默认存储引擎是InnoDB。

修改存储引擎

ALTER TABLE my_table ENGINE = MyISAM;

常用存储引擎

InnoDB：①MySQL默认的事务型存储引擎 ②支持ACID事务 ③具有较好的并发性能和数据完整性 ④支持行级锁定 ⑤适用于大多数应用场景，尤其是需要事务支持的应用。
MyISAM： ①MySQL早期版本中常用的存储引擎 ②支持全文索引和表级锁定 ③不支持事务 ④由于其简单性和高性能，在某些特定的应用场景中会得到广泛应用，如读密集的应用。
MEMORY： ①称为HEAP，是将表存储在内存中的存储引擎 ②具有非常高的读写性能，但数据会在服务器重启时丢失。 ③适用于需要快速读写的临时数据集、缓存和临时表等场景。
CSV： ①将数据以纯文本格式存储的存储引擎 ②适用于需要处理和导入/导出CSV格式数据的场景。
ARCHIVE： ①将数据高效地进行压缩和存储的存储引擎 ②适用于需要长期存储大量历史数据且不经常查询的场景。

十二、索引

什么是索引？

索引是一种能够提高检索（查询）效率的提前排好序的数据结构。

例如：书的目录就是一种索引机制。索引是解决SQL慢查询的一种方式。

没有索引的时候：全表扫描。

针对表中的某个列（字段）生成一个index索引对象。

mysql的数据库中的索引对应的是B+树的数据结构。

索引的创建和删除

主键字段会自动添加索引，不需要程序员干涉，主键字段上的索引被称为主键索引
unique约束的字段也会自动添加索引，不需要程序员干涉，这种字段上添加的索引称为唯一索引

给指定的字段添加索引

/*建表时添加索引*/
CREATE TABLE emp (
    ...
    name varchar(255),
    ...
    INDEX idx_name (name)
);

/*表已经创建好了，后期给字段添加索引*/
ALTER TABLE emp ADD INDEX idx_name (name);

create index idx_name on emp(name);


/*删除指定字段上的索引*/
ALTER TABLE emp DROP INDEX idx_name;


/*查看某张表上添加了哪些索引*/
show index from 表名;

索引的分类

不同的存储引擎有不同的索引类型和实现：

按照数据结构分类：
- B+树索引（mysql的InnoDB存储引擎采用的就是这种索引）采用 B+树的数据结构
- Hash 索引（仅 memory 存储引擎支持）：采用哈希表的数据结构
按照物理存储分类：
- 聚集索引：索引和表中数据在一起（表中字段就是索引），数据存储的时候就是按照索引顺序存储的。一张表只能有一个聚集索引。
- 非聚集索引：索引和表中数据是分开的，索引是独立于表空间的，一张表可以有多个非聚集索引。
按照字段特性分类：
- 主键索引（primary key）
- 唯一索引（unique）
- 普通索引（index）
- 全文索引（fulltext：仅 InnoDB和MyISAM 存储引擎支持）：要求字段的类型都是文本内容。
按照字段个数分类：
- 单列索引、联合索引（也叫复合索引、组合索引）

MySQL索引采用B+树数据结构

B+ Trees 相较于 B Trees改进了哪些？

B+树将数据都存储在叶子节点中。并且叶子节点之间使用链表连接，这样很适合范围查询。
B+树的非叶子节点上只有索引值，没有数据，所以非叶子节点可以存储更多的索引值，这样让B+树更矮更胖，提高检索效率。

经典面试题：mysql为什么选择B+树作为索引的数据结构，而不是B树？

非叶子节点上可以存储更多的键值，阶数可以更大，更矮更胖，磁盘IO次数少，数据查询效率高。
所有数据都是有序存储在叶子节点上，让范围查找，分组查找效率更高。
数据页之间、数据记录之间采用链表链接，让升序降序更加方便操作。

经典面试题：如果一张表没有主键索引，那还会创建B+树吗？

当一张表没有主键索引时，默认会使用一个隐藏的内置的聚集索引（clustered index）。这个聚集索引是基于表的物理存储顺序构建的，通常是使用B+树来实现的。

其他索引

Hash索引

支持Hash索引的存储引擎有：

InnoDB（不支持手动创建Hash索引，系统会自动维护一个自适应的Hash索引）
- 对于InnoDB来说，即使手动指定了某字段采用Hash索引，最终show index from 表名的时候，还是BTREE。
Memory（支持Hash索引）

Hash索引底层的数据结构就是哈希表。一个数组，数组中每个元素是链表。和java中HashMap一样。哈希表中每个元素都是key-value结构。key存储索引值，value存储行指针。

Hash索引优缺点：

优点：只能用在等值比较中，效率很高。例如：name='孙悟空'
缺点：不支持排序，不支持范围查找。

聚集索引和非聚集索引

按照数据的物理存储方式不同，可以将索引分为聚集索引（聚簇索引）和非聚集索引（非聚簇索引）。

存储引擎是InnoDB的，主键上的索引属于聚集索引。

存储引擎是MyISAM的，任意字段上的索引都是非聚集索引。

InnoDB的物理存储方式：当创建一张表t_user，并使用InnoDB存储引擎时，会在硬盘上生成这样一个文件：

t_user.ibd （InnoDB data表索引 + 数据）
t_user.frm （存储表结构信息）

MyISAM的物理存储方式：当创建一张表t_user，并使用MyISAM存储引擎时，会在硬盘上生成这样一个文件：

t_user.MYD （表数据）
t_user.MYI （表索引）
t_user.frm （表结构）

注意：从MySQL8.0开始，不再生成frm文件了，引入了数据字典，用数据字典来统一存储表结构信息，例如：

information_schema.TABLES （表包含了数据库中所有表的信息，例如表名、数据库名、引擎类型等）
information_schema.COLUMNS（表包含了数据库中所有表的列信息，例如列名、数据类型、默认值等）

聚集索引的优点和缺点：

优点：聚集索引将数据存储在索引树的叶子节点上。可以减少一次查询，因为查询索引树的同时可以获取数据。

缺点：对数据进行修改或删除时需要更新索引树，会增加系统的开销。

二级索引

二级索引也属于非聚集索引。也有人把二级索引称为辅助索引。有表t_user，id是主键。age是非主键。在age字段上添加的索引称为二级索引。（所有非主键索引都是二级索引）

select * from t_user where age = 30;  

/*此时底层会再执行一个select * from t_user where id = 2*/

为什么会“回表”？因为使用了select *

避免“回表【回到原数据表】”是提高SQL执行效率的手段。例如：select id from t_user where age = 30; 这样的SQL语句是不需要回表的。

覆盖索引

覆盖索引（Covering Index），顾名思义，是指某个查询语句可以通过索引的覆盖来完成，而不需要回表查询真实数据。其中的覆盖指的是在执行查询语句时，查询需要的所有列都可以从索引中提取到，而不需要再去查询实际数据行获取查询所需数据。当使用覆盖索引时，MySQL可以直接通过索引，也就是索引上的数据来获取所需的结果，而不必再去查找表中的数据。这样可以显著提高查询性能。

假设有一个用户表（user）包含以下列：id, username, email, age。

常见的查询是根据用户名查询用户的邮箱。如果为了提高这个查询的性能，可以创建一个覆盖索引，包含（username, email）这两列。

创建覆盖索引的SQL语句可以如下：

CREATE INDEX idx_user_username_email ON user (username, email);			# 二级索引

当执行以下查询时：

SELECT email FROM user WHERE username = 'lucy';

MySQL可以直接使用覆盖索引（idx_user_username_email）来获取查询结果，而不必再去查找用户表中的数据。这样可以减少磁盘I/O并提高查询效率。而如果没有覆盖索引，MySQL会先使用索引（username）来找到匹配的行，然后再回表查询获取邮箱，这个过程会增加更多的磁盘I/O和查询时间。

索引下推

索引下推（Index Condition Pushdown）是一种 MySQL 中的优化方法，它可以将查询中的过滤条件下推到索引层级中处理，从而减少回表次数，优化查询性能。

具体来说，在使用索引下推时，MySQL 会在索引的叶节点层级执行查询的过滤条件，过滤掉无用的索引记录，仅返回符合条件的记录的主键，这样就可以避免查询时回表读取表格的数据行，从而缩短了整个查询过程的时间。

索引下推通常基于多列索引

id	name	age	city
1	John	25	New York
2	Alice	30	London
3	Bob	40	Paris
4	Olivia	35	Berlin
5	Michael	28	Sydney

ALTER TABLE users ADD INDEX idx_name_city_age (name, city, age);

假设我们要查询年龄大于30岁，并且所在城市是"London"的用户，假设只给age字段添加了索引，它就不会使用索引下推。传统的查询优化器会将所有满足年龄大于30岁的记录读入内存，然后再根据城市进行筛选。

使用索引下推优化后，在索引范围扫描的过程中，优化器会判断只有在城市列为"London"的情况下，才会将满足年龄大于30岁的记录加载到内存中。这样就可以避免不必要的IO和数据传输，提高查询性能。

SELECT * FROM users WHERE age > 30 AND city = 'London';

单列索引

SELECT * FROM student WHERE student_id = 123456;

复合索引（组合索引）

复合索引（Compound Index）也称为多列索引（Multi-Column Index），是指对数据库表中多个列进行索引创建。

与单列索引不同，复合索引可以包含多个列。这样可以将多个列的值组合起来作为索引的键，以提高多列条件查询的效率。

相对于单列索引，复合索引有以下几个优势：

减少索引的数量：复合索引可以包含多个列，因此可以减少索引的数量，减少索引的存储空间和维护成本。
提高查询性能：当查询条件中涉及到复合索引的多个列时，数据库可以使用复合索引进行快速定位和过滤，从而提高查询性能。
覆盖查询：如果复合索引包含了所有查询需要的列，那么数据库可以直接使用索引中的数据，而不需要再进行表的读取，从而提高查询性能。
排序和分组：由于复合索引包含多个列，因此可以用于排序和分组操作，从而提高排序和分组的性能。

索引的优缺点

索引是数据库中一种重要的数据结构，用于加速数据的检索和查询操作。它的优点和缺点如下：

优点：

提高查询性能：通过创建索引，可以大大减少数据库查询的数据量，从而提升查询的速度。
加速排序：当查询需要按照某个字段进行排序时，索引可以加速排序的过程，提高排序的效率。
减少磁盘IO：索引可以减少磁盘IO的次数，这对于磁盘读写速度较低的场景，尤其重要。

缺点：

占据额外的存储空间：索引需要占据额外的存储空间，特别是在大型数据库系统中，索引可能占据较大的空间。
增删改操作的性能损耗：每次对数据表进行插入、更新、删除等操作时，需要更新索引，会导致操作的性能降低。
资源消耗较大：索引需要占用内存和CPU资源，特别是在大规模并发访问的情况下，可能对系统的性能产生影响。

索引类型	通俗解释	现实类比
聚簇索引	数据实际存储顺序按索引排序，索引即数据	电话簿按姓名拼音排序（姓名和号码直接存储）
非聚簇索引	索引单独存储，需通过地址回表查数据	教科书目录（只标页码，需翻到正文）
主键索引	特殊的聚簇索引（必须唯一且非空）	身份证号（唯一标识一个人，且不重复）
唯一索引	确保字段值唯一（可为空），可聚簇或非聚簇	车牌号（每辆车唯一，但新车可暂时无牌）
普通索引	无唯一性约束的基本索引	图书馆书籍分类标签（同一标签可有多本书）
全文索引	对文本内容关键词搜索	百度搜索（输入关键词匹配文章内容）
单列索引	仅包含一列的索引	字典按单字拼音索引（只能查单个字）
复合索引	多列组成的联合索引（）	地图坐标（需同时查经度+纬度定位）
二级索引	所有非聚簇索引的统称	书的附录/目录（不改变正文存储顺序）
覆盖索引	查询所需字段全在索引中，无需回表	菜单图片直接印在目录（不用翻页看详情）
索引下推	在存储引擎层提前过滤数据	快递分拣站先筛同城件（无需送到总仓再筛）

十三、MySQL优化

MySQL数据库的优化手段通常包括但不限于：

SQL查询优化：这是最低成本的优化手段，通过优化查询语句、适当添加索引等方式进行。并且效果显著。
库表结构优化：通过规范化设计、优化索引和数据类型等方式进行库表结构优化，需要对数据库结构进行调整和改进
系统配置优化：根据硬件和操作系统的特点，调整最大连接数、内存管理、IO调度等参数
硬件优化：升级硬盘、增加内存容量、升级处理器等硬件方面的投入，需要购买和替换硬件设备，成本较高

SQL性能分析工具

通过以下命令可以查看当前数据库在SQL语句执行方面的整体情况：

show global status like 'Com_select';
show global status like 'Com_insert';
show global status like 'Com_delete';
show global status like 'Com_update';

show global status like 'Com_______';

这些结果反映了从 MySQL 服务器启动到当前时刻，所有的 SELECT 查询总数。对于 MySQL 性能优化来说，通过查看 Com_select 的值可以了解 SELECT 查询在整个 MySQL 服务期间所占比例的情况：

如果 Com_select 次数过高，可能说明查询表中的每条记录都会返回过多的字段，表示该数据库属于 读密集型 的数据库。
如果 Com_select 次数很少，同时insert或delete或update的次数很高，可能说明服务器运行的应用程序过于依赖写入操作和少量读取操作，表示该数据库属于 写密集型 的数据库。

慢查询日志

注意：slow_query_log=1表示开启慢查询日志功能，long_query_time=3表示：只要SELECT语句的执行耗时超过3秒则将其记录到慢查询日志中。

show profiles

通过show profiles可以查看一个SQL语句在执行过程中具体的耗时情况。

/*查看当前数据库是否支持profile操作*/
select @@have_profiling;

/*查看 profiling 开关是否打开*/
select @@profiling;

/*将 profiling 开关打开*/
set profiling = 1;

/*查看某个(被 profiling 记录的第 40 条 SQL 语句)SQL语句语句在执行过程中，每个阶段的耗时情况*/
show profile for query 40;


/*想查看执行过程中cpu的情况*/
show profile cpu for query 40;

explain

explain命令可以查看一个DQL语句的执行计划，根据执行计划可以做出相应的优化措施。提高执行效率。

/*查看一个DQL语句的执行计划*/
explain select * from emp where empno=7369;


explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal;


explain select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.sal=(select sal from emp where ename='ford');

id反映出一条select语句执行顺序，id越大优先级越高。id相同则按照自上而下的顺序执行。

select_type

反映了mysql查询语句的类型。常用值包括：

SIMPLE：表示查询中不包含子查询或UNION操作。这种查询通常包括一个表或是最多一个联接（JOIN）
PRIMARY：表示当前查询是一个主查询。（主要的查询）
UNION：表示查询中包含UNION操作
SUBQUERY：子查询
DERIVED：派生表（表示查询语句出现在from后面）

table

反映了这个查询操作的是哪个表。

type

反映了查询表中数据时的访问类型，常见的值：

NULL：效率最高，一般不可能优化到这个级别，只有查询时没有查询表的时候，访问类型是NULL。例如：select 1;
system：通常访问系统表的时候，访问类型是system。一般也很难优化到这个程序。
const：根据主键或者唯一性索引查询，索引值是常量值时。explain select * from emp where empno=7369;
eq_ref：根据主键或者唯一性索引查询。索引值不是常量值。
ref：使用了非唯一的索引进行查询。
range：使用了索引，扫描了索引树的一部分。
index：表示用了索引，但是也需要遍历整个索引树。
all：全表扫描

效率最高的是NULL，效率最低的是all，从上到下，从高到低。

possible_keys

这个查询可能会用到的索引

key

实际用到的索引

key_len

反映索引中在查询中使用的列所占的总字节数。

rows

查询扫描的预估计行数。

Extra

给出了与查询相关的额外信息和说明。这些额外信息可以帮助我们更好地理解查询执行的过程。

索引优化

最左前缀原则

/*创建表*/
create table t_customer(
	id int primary key auto_increment,
	name varchar(255),
	age int,
	gender char(1),
	email varchar(255)
);

/*添加复合索引*/
create index idx_name_age_gender on t_customer(name,age,gender);

最左前缀原则：当查询语句条件中包含了这个复合索引最左边的列 name 时，此时索引才会起作用。

当缺少另外两个name或者gender时都会使用部分索引。

explain select * from t_customer where name='zhangsan' and gender='M';
/*name和gender中间差了一个age,会出现性能折损，导致gender没有使用索引*/


/*范围查询时，在“范围条件”右侧的列索引会失效*/
explain select * from t_customer where name='zhangsan' and age>20 and gender='M';

/* 怎么解决？建议范围查找时带上“=” */
explain select * from t_customer where name='zhangsan' and age>=20 and gender='M';

索引失效情况

create table t_emp(
  id int primary key auto_increment,
  name varchar(255),
  sal int,
  age char(2)
);

insert into t_emp values(null, '张三', 5000,'20');
insert into t_emp values(null, '张飞', 4000,'30');
insert into t_emp values(null, '李飞', 6000,'40');

create index idx_t_emp_name on t_emp(name);
create index idx_t_emp_sal on t_emp(sal);
create index idx_t_emp_age on t_emp(age);

索引列参加了运算，索引失效

/*使用索引*/
explain select * from t_emp where sal > 5000;

/*索引失效*/
explain select * from t_emp where sal*10 > 50000;

索引列进行模糊查询时以 % 开始的，索引失效

/*使用索引*/
explain select * from t_emp where name like '张%';

/*索引失效*/
explain select * from t_emp where name like '%飞';

索引列是字符串类型，但查询时省略了单引号，索引失效

/*使用索引*/
explain select * from t_emp where age='20';

/*索引失效*/
explain select * from t_emp where age=20;

查询条件中有or，只要有未添加索引的字段，索引失效

/*使用索引*/
explain select * from t_emp where name='张三' or sal=5000;

/*删除表sal字段上的索引*/
alter table t_emp drop index idx_t_emp_sal;

/*索引失效*/
explain select * from t_emp where name='张三' or sal=5000;

当查询的符合条件的记录在表中占比较大，索引失效

/*复制一张新表*/
create table emp2 as select * from emp;

/*给sal添加索引*/
alter table emp2 add index idx_emp2_sal(sal);

/*大于800占大多数，所以索引失效*/
explain select * from emp2 where sal > 800;

is null和is not null的索引失效问题

走索引还是索引失效，根数据分布有很大关系，如果符合条件的记录占比较大，会考虑使用全表扫描，而放弃走索引。

指定索引

当一个字段上既有单列索引，又有复合索引时，我们可以通过以下的SQL提示来要求该SQL语句执行时采用哪个索引：

use index(索引名称)：建议使用该索引，只是建议，底层mysql会根据实际效率来考虑是否使用你推荐的索引。
ignore index(索引名称)：忽略该索引
force index(索引名称)：强行使用该索引

/*查看t_customer表上的索引*/
show index from t_customer;

/*先给name, age, gender三列添加一个复合索引*/
/*给name字段添加一个单列索引*/
create index idx_name on t_customer(name);


explain select * from t_customer where name='zhangsan';
/*默认使用了联合索引*/


/*建议使用单列索引idx_name*/
explain select * from t_customer use index(idx_name) where name='zhangsan';

覆盖索引

覆盖索引强调的是：在select后面写字段的时候，这些字段尽可能是索引所覆盖的字段，这样可以避免回表查询。尽可能避免使用 select *，因为select * 很容易导致回表查询。（本质就是：能在索引上检索的，就不要再次回表查询了。）

using where 同样表示的也是回表查询。

前缀索引

如果一个字段类型是varchar或text字段，字段中存储的是文本或者大文本，直接对这种长文本创建索引，会让索引体积很大，怎么优化呢？可以将字符串的前几个字符截取下来当做索引来创建。这种索引被称为前缀索引 。

/*前缀索引*/
drop table if exists emp4;
create table emp4 as select * from emp;
create index idx_emp4_ename_2 on emp4(ename(2));

/*使用前缀索引时，需要通过以下公式来确定使用前几个字符作为索引*/
select count(distinct substring(ename,1,前几个字符)) / count(*) from emp4;
/*结果越接近1，表示索引的效果越好。
	原理：做索引值的话，索引值越高具有唯一性效率越高
*/

索引创建原则

表数据量庞大，通常超过百万条数据。
经常出现在where，order by，group by后面的字段建议添加索引。
创建索引的字段尽量具有很强的唯一性。
如果字段存储文本，内容较大，一定要创建前缀索引。
尽量使用复合索引，使用单列索引容易回表查询。
如果一个字段中的数据不会为NULL，建议建表时添加not null约束，这样优化器就知道使用哪个索引列更加有效。
不要创建太多索引，当对数据进行增删改的时候，索引需要重新重新排序。
如果很少的查询，经常的增删改不建议加索引。

SQL优化

order by 优化

explain查看一个带有order by的语句时，Extra列会显示：using index 或者 using filesort，区别是什么？

using index: 表示使用索引，因为索引是提前排好序的。效率很高。
using filesort：表示使用文件排序，这就表示没有走索引，对表中数据进行排序，排序时将硬盘的数据读取到内存当中，在内存当中排好序。这个效率是低的，应避免。

特殊：通过两个字段进行排序

最好给age和sal两个字段添加复合索引。

同时升序或降序，就在order by之后添加asc或desc就行。

如果一个升序，一个降序（创建对应索引）

create index idx_workers_ageasc_saldesc on workers(age asc, sal desc);

order by 优化原则总结：

排序也要遵循最左前缀法则。
使用覆盖索引。
针对不同的排序规则，创建不同索引。（如果所有字段都是升序，或者所有字段都是降序，则不需要创建新的索引）
如果无法避免filesort，要注意排序缓存的大小，默认缓存大小256KB，可以修改系统变量 sort_buffer_size ：

group by优化

同样支持最左前缀法则。

limit优化

使用覆盖索引，加子查询。

主键优化

主键值不要太长，二级索引叶子结点上存储的是主键值，主键值太长，容易导致索引占用空间较大。
尽量使用auto_increment生成主键。尽量不要使用uuid做主键，因为uuid不是顺序插入。
最好不要使用业务主键，因为业务的变化会导致主键值的频繁修改，主键值不建议修改，因为主键值修改，聚集索引一定会重新排序。
在插入数据时，主键值最好是顺序插入，不要乱序插入，因为乱序插入可能会导致B+树叶子结点频繁的进行页分裂与页合并操作，效率较低。
- 主键值对应聚集索引，插入主键值如果是乱序的，B+树叶子结点需要不断的重新排序，重排过程中还会频繁涉及到页分裂和页合并的操作，效率较低。
- B+树上的每个节点都存储在页（page）中。一个页面中存储 B+树的一个节点。
- MySQL的InnoDB存储引擎一个页可以存储16KB的数据。

insert优化

insert优化原则：

批量插入：数据量较大时，不要一条一条插入，可以批量插入，当然，建议一次插入数据不超过1000条。
mysql默认是自动提交事务，只要执行一条DML语句就自动提交一次，因此，当插入大量数据时，建议手动开启事务和手动提交事务。不建议使用数据库事务自动提交机制。
主键值建议采用顺序插入，顺序插入比乱序插入效率高。
超大数据量插入可以考虑使用mysql提供的load指令，load指令可以将csv文件中的数据批量导入到数据库表当中，并且效率很高，过程如下：

/*登录mysql时指定参数*/
mysql --local-infile -uroot -p1234

/*开启local_infile功能*/
set global local_infile = 1;

/*执行load指令*/
use powernode;

create table t_temp(
  id int primary key,
  name varchar(255),
  password varchar(255),
  birth char(10),
  email varchar(255)
);

load data local infile 'E:\\powernode\\05-MySQL高级\\resources\\t_temp-100W.csv' into table t_temp fields terminated by ',' lines terminated by '\n';