MySQL

简介与基本命令

数据库基本概念

• 数据库管理系统（Database Management System，DBMS）：数据库系统中对数据进行管理的软件系统。
• 数据库（Database，DB）：按照特定的数据结构来组织、存储和管理数据的仓库。
• 表 (Table）：某种特定类型数据的结构化清单。
• 列 (Column）或字段：表由一个或多个列组成，每个列有对应的数据类型。
• 行 (Row）或记录：表中的数据是按行存储的，每行存储一条数据（记录）。
• 主键（Primary Key）：一列（或一组列），它的值能够唯一区分表中每一行。
• SQL（Structured Query Language）：结构化查询语言 ，专门用于与数据库通信的语言。

MySQL启动

• 软件的安装需要注意的问题：

  • 安装路径使用默认路径
  • 用户名：root 密码：123456

• 软件启动

  • 开始->MySQL->MySQL5.7 -> 123456
  • net start mysql57（关闭命令为 net stop mysql57，需要管理员权限打开cmd，以后如果无法连接数据库，先执行该命令）
  • Windows+r 打开cmd 输入：mysql -uroot -p123456

• 命令行中退出

  • Exit
  • Quit
  • \q

数据库基本命令

• 显示数据库

 show databases;

安装MySQL时会自动创建一些数据库

创建数据库：在系统盘上划分一块区域用于数据的存储和管理

 create database [if not exists] 数据库名;

Query OK：表示执行成功

1 row affected：影响数据库中一行记录

0.01 sec：操作执行的时间

• 使用数据库

use  数据库名;

• 查询当前数据库信息

# 查询当前连接的数据库

select database();

# 查询当前的数据库版本

select version();

# 查询当前的日期

select now();

# 查询当前的用户

select user();

• 删除数据库：将已经存在的数据库清除，数据库中的数据也将被清除

drop database [if exists] 数据库名;

0 rws affected：这个提示不用管，drop操作的结果都显示这个

注意：删除数据库会删除库中的所有表数据

• 显示数据库中的表

show tables;

• 导入数据库脚本（结尾不加分号，读取外部 SQL 脚本，SQL 脚本里的语句是以分号结尾的）

  • 导入的时候没有分号，路径中不能含有中文
  • 需要先创建数据库，然后 use 使用才能导入

source 脚本文件路径

source D:\Pointer\Lesson\MySQL\comment.sql

• 查看表的基本结构（describe 的缩写）

desc 表名;

desc emp;

• 查看数据库/表的创建语句

show create database 数据库名;

show create table 表名;

基本查询

• 查询指定字段

select 字段1 [[as] 别名], 字段2 [[as] 别名],...] from 表名 [[as] 别名];

select ename, sal from emp;

select ename from emp;

• 查询全部字段

select * from 表名;
  
select * from emp;

• 使用条件：使用 where 语句，放在 from 后

select * from emp where 条件;

select * from emp where empno>7500;

运算符

• 算术运算符：+ - * / %

# 员工年工资

select * from emp where sal * 12 > 20000;

• 比较运算符：

select * from emp where deptno <> 20;

select * from emp where deptno != 20;

select * from emp where sal between 1500 and 3000;

select * from emp where comm is null;

select * from emp where comm is not null;

select * from emp where deptno in (10, 30);

select * from emp where job like "%m%";

select * from emp where job like "_____m%";

• 逻辑运算符：and or not

select * from emp where job = 'salesman' and sal > 1500;

like通配符

通配符与 SQL LIKE 操作符一起使用。

别名

可以为表名称或列名称指定别名。

列的 SQL 别名语法 (as可以省略)

SELECT column_name [as] alias_name
FROM table_name;

表的 SQL 别名语法

SELECT column_name 
FROM table_name [as] alias_name;

char 和varchar区别

1. 存储容量 char最多只能存放的字符个数为255, varchar 最大65535
2. char如果给的长度不够会自动补空格
3. char在查询的时候效率更高并且会去掉空格
4. VARCHAR需要使用额外字节记录字符串的长度

使用=判断字符串

使用=判断字符串相等会忽略结尾空格，可以使用like判断则不会忽略。

关键字

exists (存在)
modify (修改)
between .. and .. (在.. 和.. 之间)
timestamp (时间戳)
auto_increment (自动增长)
constraint (约束)
distinct (去重)
primary key (主键)
foreign key ... references (外键)
regexp (正则表达式)
procedure (存储过程)
declare (声明)    
repeat (重复)
trigger (触发器)
explain (解释)
transaction (事务)
variables (变量)

MySQL默认存储引擎innoDB

• Server层：

  • 连接器：负责和客户端建立连接，管理连接
  • 查询缓存：（如果开启了查询缓存功能）先到缓存中查询是否查询过这条语句，缓存的失效非常频繁，只要对表有更新，该表的查询缓存都会被清空 ，MySQL8.0删除了该功能
  • 分析器：对SQL语句进行词法分析和语法分析，判断语句是否合法
  • 优化器：对SQL语句进行优化，选择索引
  • 执行器：调用存储引擎接口，返回结果
• 存储引擎层：数据的存储和提取，默认的存储引擎是InnoDB，5.5之前的默认是MyISAM

表

存储在表中的数据是一种类型的数据，不同类型的数据应放到不同的表中，否则会使以后的检索和访问很困难。

数据库中的每个表都有一个名字，用来标识自己。此名字是唯一的， 这表示数据库中没有其他表具有相同的名字。

表由一个或多个列组成。每一列存储一列特定的信息，列称为字段，每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。例如，某一列中需要存储数字，该列的数据类型应该定义为数值类型。

数据类型

• 整型，无符号：unsigned

• 浮点型和定点型

• 日期时间类型

• 字符型

DDL（数据定义语言）

Data Definition Language，用于定义和管理数据库中的对象和结构，如表、列、索引等。

• 创建表

语法：

create table [if not exists] 表名(

    字段1 字段类型 [列级约束条件],
    字段2 字段类型 [列级约束条件],
    ...
    [表级约束条件]

);

示例：

create table users(

  id int primary key, # 列级约束
  name varchar(20),
  primary key(id)    # 表级约束
);

修改表

理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

column可以省略

• 添加字段

alter table 表名
add column 新列名 数据类型 [约束条件] [first | after 列名];

• 修改字段的类型

alter table 表名 
modify column 列名 数据类型 [约束条件];

• 修改字段的位置

alter table 表名 
modify column 列名 数据类型 first | after 列名;

• 修改字段名

alter table 表名 
change column 旧列名 新列名 数据类型 ;

• 删除字段

alter table 表名 
drop column 列名;

重命名表

alter table 旧表名 
rename to 新表名;

删除表

drop table [if exists] 表1[, 表2, 表3...];

DML（数据操作语言）

Data Manipulation Language，用于操作数据库中的实际数据，如插入、更新、删除和查询数据。

插入数据

插入完整的行，所有字段，每一个字段都必须提供一个值，如果某个字段没有值，应该使用 NULL，每个字段必须以他们在表中定义的顺序给出。这种语法简单，但是不安全，应该避免使用。

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

更安全的方法是，在表名后的括号中给出字段名，values 中的值的顺序与前面给出的字段的顺序相同，一一对应。不需要与表中定义的顺序相同，没有值的字段可以不提供。

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

values 后面可以跟多个括号，括号间用逗号分隔，每个括号代表要插入的一条数据。

单条 insert 语句插入多条数据比多条 insert 语句快。

示例

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

更新数据

update 表名 
set 字段1 = 字段1的值, 
     字段2 = 字段2的值,
     ... 
where 限制条件;

记得加 where 限制条件，否则更新整个表中的每一条数据

删除数据

delete from 表名 
where 限制条件;

同样，不加限制条件会删除整个表中每一条数据。

注意：在对 update 或 delete 使用 where 前，应该先用 select 语句进行测试，保证它的过滤结果是正确的。update前最好用where条件select一下

约束

相同空值不违反唯一约束

constraint（约束）

非空约束

NOT NULL，是否不可以为空（NULL），NULL 值就是没有值或缺值，非空就是不可以在插入时不给出该列的值，该列必须有值。

• 创建表时设置非空约束

create table 表名(

  字段名 字段类型 not null,

  ...

);

示例

drop table if exists users;

create table users(

    id int not null,

    name varchar(20)

);

# 报错，id没有默认值，不允许为空

insert into users(name) values("李四");

• 已有字段添加非空约束

alter table 表名
modify column 字段名 字段类型 not null;

• 删除非空约束（修改为允许为空）

alter table 表名 
modify column 字段名 字段类型;

注意：不要混淆 NULL 值和空串，NULL值是没有值，空串是 ''（两个单引号，中间没有字符）

默认约束

​ DEFAULT，如果在插入行时没有给出值，通过 DEFAULT 指定此时使用的默认值。

create table 表名(
    字段名 字段类型 default 默认值,
    ...
);

示例

drop table if exists users;
create table users(
    id int not null default 666,
    name varchar(20)
);
# 不报错
insert into users(name) values("李四");
select * from users;

唯一约束

​ 用于确保特定列或列组合的唯一性，被约束的列的值在整个表中是唯一的，唯一约束默认允许空值（null），因此多个空值不违反唯一约束。

• 列级

create table 表名(
    字段名 字段类型 unique,
    ...
);

示例

drop table if exists users;
create table users(
    id int not null default 666,
    name varchar(20) unique
);
insert into users(id, name) values(1, "李四");
insert into users(id, name) values(2, "李四");
insert into users(id) values(3);
insert into users(id) values(4);

• 表级，可以给约束起名，可以创建多列的唯一约束（联合唯一约束）

create table 表名(
    字段1 字段类型,
    字段2 字段类型,
    ...
    [constraint 约束名] unique(字段1[, 字段2, ...])
);

示例

drop table if exists users;
create table users(
    id int not null default 666,
    name varchar(20),
    unique(id, name)
);
insert into users(id, name) values(1, "李四");
insert into users(id, name) values(2, "李四");
insert into users(id, name) values(1, "张三");
insert into users(id, name) values(1, "李四");#报错
insert into users(name) values("李四");
insert into users(name) values("李四");#报错
insert into users(id) values(5);
insert into users(id) values(5);

• 已有字段添加唯一约束

alter table 表名 
modify 字段名 字段类型 unique ;

alter table 表名 
add [constraint 约束名] unique(字段名) ;

• 删除唯一约束

alter table 表名 
drop index 约束名;

alter table 表名 
drop key 约束名;

主键约束

​ 唯一标识表中每行的这个列（或这组列）称为主键。主键用来表示一个特定的行。没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。

因此：

• 每一个表都应该定义主键
• 主键的值不应该修改
• 不使用可能会修改值的列作为主键（与业务无关，通常使用 id 作为主键），可以自己加一个与业务无关的id

特点：

• 唯一性：主键要求每一行数据的主键值都必须是唯一的，不允许有重复值。
• 非空性：主键要求主键列的值不能为空，即不能为 NULL。
• 单一性：每个表只能有一个主键。主键可以由一个列或多个列组成，形成复合主键

列级设置主键

create table 表名(
    字段名 字段类型 primary key,
    ...
);

示例

drop table if exists users;
create table users(
    id int primary key,
    name varchar(20) unique
);
insert into users(id, name) values(1, "张三");
insert into users(id, name) values(1, "李四");
insert into users(name) values("王五");
insert into users(id) values(2);

• 表级，可以给约束起名，可以创建多列的联合主键

create table 表名(
    字段1 字段类型,
    字段2 字段类型,
    ...
    [constraint 约束名] primary key(字段1[, 字段2, ...])
);

示例

create table users(
    id int ,
    name varchar(20) unique,
    primary key(id, name)
);

• 删除主键

alter table 表名 
drop primary key;

自动递增

auto_increment（自动增长）：设置 auto_increment 的列，从1开始，当每增加一行时自动增量。每个表只允许一个 auto_increment 列。如果你插入了一条数据，以后的数据会从你插入的这条开始自动递增。

create table 表名(
    字段1 字段类型 auto_increment
);

示例

drop table if exists users;
create table users(
    id int primary key auto_increment,
    name varchar(20) unique
);
insert into users(name) values("李四");
insert into users(name) values("张三");
insert into users(id,name) values(5, "王五");
insert into users(name) values("赵六");

外键约束

外键为表中的某一字段，该字段是另一表的主键值，外键用来在两个表的数据之间建立联结，一个表中可以有一个或多个外键。外键的作用是保持数据的一致性、完整性。

注意：

• 外键字段可以为null，外键为空的数据也叫孤儿数据

• 有了外键引用之后，表分为父表和子表

  • 创建表时先创建父表，再创建子表
  • 插入数据时，先插入父表数据，再插入子表数据
  • 删除时先删除子表，再删除父表
• 子表外键类型要与父表外键类型一致

在子表创建外键：

语法

create table 表名(
    字段1 字段类型 列级约束,
    .....,
    [constraint 约束名] foreign key(字段) references 父表(字段)
);

示例

# 表级约束创建外键 [constraint 外键名] foreign key (列名) references 主表名(主键);

create table student(
    id int primary key auto_increment,
    name varchar(20) unique
);

create table score(
    id int primary key auto_increment, 
    student_id int ,
    degree int,
    constraint fk1 foreign key (student_id) references student(id) 
);

insert into score(student_id, degree) values(1, 90);#报错
insert into student(name) values("李四");
insert into score(student_id, degree) values(1, 90);
insert into score(degree) values(90);

delete from student where id=1;#报错
delete from score where student_id=1;
delete from student where id=1;

MySQL表相关练习

高级查询

查询处理

排序

order by 子句：对查询结果按指定字段进行排序。也可以指定select列表中列的序号进行排序

asc 升序 desc 降序， 默认asc

语法

查询语句 order by 字段1[,字段2] [asc|desc]

示例

select * from emp order by sal desc;

order by 1[,2] [asc|desc]

select * from emp order by sal desc;

默认不是按主键排序的

create table users(

  id int primary key,

  name varchar(20) unique

);

insert into users values(1, 'zhangsan'), (2, 'wangwu'), (3, 'lisi');

alter table users add age int;

select * from users;

限制数量

limit 子句：select 语句返回所有匹配的行，它们可能是指定表中的每个行。为了前几行或中间几行，可使用 limit 子句。使用limit可以解决分页问题

语法

查询语句 limit 行数（从第一行开始）
查询语句 limit  开始行（从0开始）, 行数

select * from emp order by sal limit 1;
select * from emp order by sal limit 3, 5;

去重

distinct 关键字：用于返回唯一不同的值,放在字段名前面

DISTINCT关键字用于消除重复记录，并返回唯一的记录集。DISTINCT关键字可以用在SELECT语句的开头，并在查询结果中显示唯一的行。

空值也会显示

语法

select distinct column1, column2,.....columnN 
from table_name ...

示例

#列出所有岗位，先查询再去重
select distinct job from emp;
#同时作用两列，不能查询目标列以外的列
select distinct job, mgr from emp;

组合查询

union 操作符：执行多个查询（多条 select 语句），将结果合并为单个结果集返回。

语法

select 字段1[,字段2,...] from 表1
union  
select 字段1[,字段2,...] from 表2;

示例

select empno,ename from emp where empno > 7700
union
select empno,ename from emp where ename not like '%c';

select empno,ename from emp
union  
select deptno,dname from dept;

注意：

• 每个查询必须包含相同数量的列
• 列必须拥有相似的数据类型
• union 自动去除了重复的行，如果允许重复行，请使用 union all

函数

数值函数

• abs(x)：返回x的绝对值
• ceil(x)：向上取整，返回大于等于 x 的最小整数值
• floor(x)：向下取整，返回小于等于 x 的最大整数值
• round(x, y=0)：四舍五入，将 x 四舍五入 y 位小数，y 不传返回整数，y 为负数时，保留 x 值到小数点左边 y 位
• truncate(x, y)：截断函数，返回被舍去至小数点后 y 位的数字 x ，y 为负数时截断小数点左边 y 位
• mod(x, y)：返回 x 除以 y 的余数
• rand()：生成 0-1 的随机数

字符函数

• concat(s1, s2, ...)：字符串连接，如果任何一个参数为null，则返回值为null
• concat_ws(x, s1, s2, ...)：指定分隔符的字符连接函数，x是连接分隔符，如果分隔符为 null，则结果为 null。
• lower(str)：大写转小写
• upper(str)：小写转大写
• length(str)：字符串长度
• ltrim(str)：删除字符串左侧空格
• rtrim(str)：删除字符串右侧空格
• trim(str)：删除字符串两侧空格
• substr(str, n, len)：截取子字符串，字符串 str 从 n 的位置截取长度为 len 的字符串，如果n为负数，则子字符串的位置起始于字符串结尾的n个字符
• left(str, n)：返回字符串 str 的最左边 n 个字符
• right(str, n): 返回字符串 str 的最右边 n 个字符
• replace(str, from_str, to_str)：替换函数，字符串 str 中所有的字符串 from_str 均被 to_str 替换，然后返回这个字符串
• format (x, n)：将数字 x 格式化，并以四舍五入的方式保留小数点后 n 位，结果以字符串的形式返回。若 n 为0，则返回结果不含小数部分。

日期时间函数

• curdate()/current_date()：获取当前日期，YYYY-MM-DD 格式
• curtime()/current_time()：获取当前时间，HH:MM:SS 格式
• week(date)：返回date为一年中的第几周
• now()/sysdate()：获取当前日期和时间，YYYY-MM-DD HH:MM:SS 格式
• date_add(date, interval expr type)：执行日期的加运算，date是一个datetime或 date 值，指定起始时间。expr是时间间隔。type为关键词，如YEAR, MONTH, DAY, WEEK, HOUR等。
• datediff(date1, date2)：计算两个日期之间的间隔天数
• unix_timestamp(date)：返回date的UNIX时间戳
• form_unixtime(unix)：返回unix时间戳的日期值
• date_format(date, format)：日期格式化，按format格式化date值。format例如 "%Y %m %d"
• str_to_date(date, format)：将字符串转换成date类型

聚合函数（分组函数）

• avg(expression)：返回某列的平均值
• sum(expression)：返回某列值的和
• count(expression)：返回某列的行数
• max(expression)：返回某列的最大值
• min(expression)：返回某列的最小值

注意：

聚合函数会自动的忽略空值，不需要手动增加条件排除NULL

聚合函数不能作为 where 子句后的限制条件

分组查询

创建分组

group by 子句：根据一个或多个字段对结果集进行分组，在分组的字段上可以使用 count、sum、avg 等函数。

select 字段1[字段2, function(字段1), function(字段2)...]
from 表
group by 字段1;

#统计部门ID为20的员工数
select count(*) from emp where deptno=20;
#统计每个部门的人数
select deptno, count(*)
from emp
group by deptno;

注意：

• 如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回。如果列中有多行 NULL值，它们将分为一组。
• group by 子句必须出现在 where 子句之后，order by 子句之前

过滤分组

having 子句：having 非常类似于 where。唯一的差别是 where 过滤行，而 having 过滤分组。having 必须和 group by 一起使用。

having 和 where 的区别也可以理解为，where 是分组前过滤，having 是分组后过滤。

select deptno, count(*)
from emp
group by deptno
having count(*)> 5;

select deptno, count(*)
from emp
where sal>1000
group by deptno
having count(*)> 2;

select顺序

正则表达式

regexp 操作符，regexp 操作符后面跟的就是正则表达式，正则表达式的作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。

like 和 regexp 的区别`

like 匹配整个列，如果被匹配的文本仅在列值中出现（没有配合其他通配符），like将不会找到它。regexp 在列值内进行匹配，如果被匹配的文本在列值中出现，regexp将会找到它，相应的行将被返回。

#全字符串匹配
select * from emp where ename like "%s%";
#子字符串匹配
select * from emp where ename regexp "s";

匹配单个实例

• | : 表示匹配其中之一, 使用 | 从功能上类似 or
• [ - ]：匹配范围，使用 - 来定义一个范围。例如：[1-3]、[a-z]。

select * from emp where ename regexp "i|e";
select * from emp where ename regexp "[i|e]";
select * from emp where ename regexp "[i|e]n";
select * from emp where ename regexp "[a-c]";

• \\：转义字符，多数正则表达式使用单个反斜杠作为转义字符，但 MySQL 要求两个反斜杠（MySQL 自己解释一个，正则表达式库解释另一个）。

select "[" regexp "[";
select "[" regexp "\\[";
select "|" regexp "\\|";

• 匹配字符类：存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作，可以使用预定义的字符集，称为字符类。

匹配多个实例

• 常用元字符

• 重复元字符（修饰它前面的字符）：例如 .*表示任意字符任意次数匹配。

多表查询

连接查询

多表连接查询就是从多个表中获取数据，若两张表进行连接查询的时候没有任何条件限制，最终的查询结果总数是两张表记录的乘积，该现象称为笛卡儿积现象。

#展示员工信息和所在部门的信息，56条：14x4（笛卡儿积）
select * from emp,dept;

内连接

根据连接条件从多个表中查询选择数据，显示这些表中与连接条件相匹配的行，组合成新的记录。

分类：

• 等值连接：连接条件为相等判断的
• 非等值连接：连接条件不为相等判断的
• 自连接：在一个连接查询中，涉及的两个表都是同一张表的查询，自连接是一种特殊的连接查询，它指相互连接的表在物理上为同一张表，在逻辑上分为两张表

语法

from 表1 [inner] join 表2 on 条件

select * from emp join dept 
on emp.deptno=dept.deptno;

select * from emp, dept 
where emp.deptno=dept.deptno;

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

#非等值连接：查询员工的薪水等级
select e.ename, e.sal, s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal;

#自连接：查询员工所对应的领导名称
select e1.ename, e2.ename mgr_name 
from emp e1 join emp e2 
on e1.mgr=e2.empno;

外连接

A 表和B 表能够完全匹配的记录查询出来之外，将其中一张表的记录无条件的完全查询出来，对方表没有匹配的记录时，会自动模拟出 null 值与之匹配。

注意：外连接的查询结果条数 >= 内连接的查询结果条数

分类：

• 左外连接 left [outer] join 显示左表全部记录，右表满足连接条件的记录
  
• 右外连接 right [outer] join 显示右表全部记录，左表满足连接条件的记录
  

#查询员工所对应的领导名称
select e1.ename, e2.ename mgr_name 
from emp e1 left join emp e2 
on e1.mgr=e2.empno;

子查询

子查询，嵌套在其他 SQL 语句内的查询语句，且必须出现在圆括号内（查询一般指 select 语句）。

子查询的结果可以作为外层查询的过滤条件或计算字段。

使用子查询过滤

子查询一般与 [not] in 结合使用，也可也使用其他运算符：> < = !=

select 字段1[, 字段2...]
from 表1
where 字段1 [not] in (select 字段1 from 表2 where 条件);

#查找所在部门名包含 e 的员工信息
select * 
from emp
where deptno in (select deptno from dept where dname like "%e%");

all、any、some 是用于条件比较的关键字，用于比较一个值与子查询结果集中的多个值。

all：表示与子查询结果集中的所有值进行比较，需要满足条件的是所有值。

# 工资比其他人的佣金都高的员工
select *
from emp
where sal > all(select ifnull(comm, 0) from emp);

any、some : any 和some 含义相同，与子查询结果集中的值比较，有任意一个满足条件即可。

# 佣金比有的人工资还高的员工
select *
from emp
where ifnull(comm, 0) > any(select sal from emp);

子查询作为计算字段

语法

select 
    字段1,
    字段2,
    ..., 
    (select 聚合函数 from 表2 [where 表2.字段1=表1.字段1])
from 表1 [where 条件];

#查询部门名和部门人数
select dname, (select count(*) from emp where dept.deptno=emp.deptno) as emps
from dept;

子查询作为查询的来源表

select 字段... from (子查询)

存储过程和自定义函数

​ 存储过程，一组预编译的 SQL 语句和流程控制语句，被命名并存储在数据库中。存储过程可以用来封装复杂的数据库操作逻辑，并在需要时进行调用。类似于C语言函数。

使用存储过程

语法

#创建存储过程

create procedure 存储过程名()

begin

   -- 存储过程的逻辑代码

    -- 可以包含SQL语句、控制结构和变量操作等

end;

#执行存储过程

call 存储过程名();

#删除存储过程

drop procedure [if exists] 存储过程名;

示例

drop procedure if exists func;
create procedure func(in n int, out res int)
BEGIN
    
    set res = 0;
    
    while n > 0 DO
        
        set res = res + n;
        set n = n - 1;
    
    end while;
    
END; # 计算1....n的和

使用参数

语法

create procedure 存储过程名(

   [in|out|inout] 参数名1 参数的数据类型,

   [in|out|inout] 参数名2 参数的数据类型,

       ...

)

begin

    -- 存储过程的逻辑代码

    -- 可以包含SQL语句、控制结构和变量操作等

end;

参数类型：

in（默认）：输入参数，存储过程的输入值，从外部传递给存储过程，存储过程内部是只读的，不能修改它的值

out：输出参数，存储过程的返回值，存储过程可以修改它的值并将其返回

inout：输入和输出参数既可以作为输入值传递给存储过程，也可以由存储过程修改并返回

使用变量

声明语句必须放在最上面

语法

#在存储过程内定义变量

declare 变量名 变量的数据类型 [default 默认值];

#变量赋值

set 变量名=要赋的值;

#通过查询将结果赋值给变量

select 字段名 into 变量名 from 表名...
-- -----------
# 在外部定义变量
set @变量名 = 值;
使用变量的值也要加@

逻辑语句

• 条件语句（if、case）

if 判断条件 then

    逻辑代码;

[else if 判断条件 then 

    逻辑代码;]

[else 

    逻辑代码;]

end if;



case case_expression # 类似C语言 switch

   when when_expression_1  then

        逻辑代码

    when when_expression_2  then

        逻辑代码

    else

       逻辑代码

end case;

• 循环语句（while、repeat）

while 循环条件 do

    # 逻辑代码

end while;



repeat

    # 逻辑代码

until 循环条件 end repeat;

特点

• 优点：

  • 代码复用：存储过程可以被多个应用程序或脚本调用，实现了代码的复用。
  • 提高性能：MySQL 将编译后的存储过程放入缓存中。如果应用程序在单个连接中多次使用存储过程，直接使用编译版本。
  • 减少网络流量：存储过程可以一次执行多条 SQL 语句，减少了与数据库的交互次数。
  • 安全控制：存储过程可以对数据库中的数据进行严格的访问控制和权限管理。
  • 数据一致性：存储过程可以实现复杂的数据操作和事务处理，确保数据的一致性和完整性。

• 缺点：

  • 创建和维护成本高：SQL 是一种结构化查询语言，难以处理复杂的业务逻辑。
  • 开发调试复杂：需要通过特定的工具和技术进行，不方便调式。
  • 可移植性差：存储过程通常依赖于特定的数据库平台和版本，不同的数据库系统之间存储过程的语法和特性可能有差异，导致存储过程的可移植性较差。

自定义函数

关键字function，调用使用select， 可以使用自定义函数来扩展数据库的功能。

语法

#创建函数

create function 函数名([参数1 数据类型 [, 参数2 数据类型, ...]])

returns 返回值类型

begin

    函数逻辑代码(这里语法与存储过程相同)

end;

#调用函数

select 函数名([参数1, 参数2...]);

#删除函数

drop function [if exists] 函数名;

游标

关键字 cursor，fetch取数据

cursor，使用游标可以对存储过程或函数中的查询结果进行逐行处理。

创建游标后，可以使用 open 语句打开游标，开始执行游标指定的查询语句并生成结果集。在游标打开得到结果集后，可以使用 fetch 语句访问它的每一行。

游标处理完成后，应关闭游标，释放游标使用的内存和资源。

语法

#创建游标

declare 游标名 cursor for 查询语句;

#打开游标

open 游标名;
#读取游标数据到变量中

fetch 游标名 into 变量名1[,变量名2...];

#关闭游标

close 游标名;

• 检索单行数据：

#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin
    #声明变量
    declare emp_name varchar(20);
    #声明游标，查询emp表中的ename
    declare mycursor cursor
    for 
    select ename from emp;
    #打开游标
    open mycursor;
    #多次读取游标数据
    fetch mycursor into emp_name;
    select emp_name;
    fetch mycursor into emp_name;
    select emp_name;
    #关闭游标
    close mycursor;
end;
call testCursor;

• 循环检索数据

#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin
    #声明变量
    declare emp_name varchar(20);
    declare i int default 0;
    #声明游标，查询emp表中的ename
    declare mycursor cursor
    for 
    select ename from emp;
    #打开游标
    open mycursor;
    #多次读取游标数据
    while i<5 do
        fetch mycursor into emp_name;
        set i=i+1;
        select emp_name;
    end while;
    #关闭游标
    close mycursor;
end;
call testCursor;

循环检索数据需要设置结束条件：声明一个结束标志位的变量，声明一个句柄，当 not found（sqlstate '02000'）出现时，修改结束标志位

declare done int default 0;
#这里声明游标
declare continue handler for not found set done=1;

#创建存储过程
drop procedure if exists testCursor;
create procedure testCursor()
begin
    #声明变量
    declare emp_name varchar(20);
    declare done int default 0;
    #声明游标，查询emp表中的ename
    declare mycursor cursor
    for 
    select ename from emp;
    #声明句柄：not found 结束
    declare continue handler for not found set done=1;
    #打开游标
    open mycursor;
    #循环读取游标数据
    while done=0
    do
    fetch mycursor into emp_name;
    if done=0 then select emp_name;
    end if;
    end while;
    #关闭游标
    close mycursor;
end;
call testCursor;

注意：declare 的顺序为：局部变量、游标、句柄

触发器和视图

触发器

​ trigger，在表的插入（insert）、更新（update）、删除（delete）操作发生时自动执行 MySQL 语句

• 创建触发器时需要给出的信息：
• 触发器名
• 在操作前还是操作后触发（before/after）
• 被什么操作触发（insert/update/delete）
• 关联的表
• 使用触发操作的数据：
• insert：可通过 new 访问被插入的行，before insert 可以更新 new 中的值（允许更改被插入的值）。
• delete：可通过 old 访问被删除的行，old 中的值是只读的。
• update：old 访问更新前的值，new 访问更新后的值

语法

# 创建触发器
create trigger 触发器名 before|after update|insert|delete on 表名 for each row  
begin 
    触发器逻辑
end; 

#删除触发器
drop trigger [if exists] 触发器名;

示例

#更新部门，对应部门的员工涨工资
drop trigger if exists mytrigger;
create trigger mytrigger after update on dept
for each row
begin
  update emp
  set sal=sal + 100 where deptno=new.deptno;
end;

update dept
set loc="BEIJING"
where loc="DALLAS";

视图

view，由查询结果形成的一个虚拟的表。视图不能索引，也不能有关联的触发器。

当视图与原表对应时可更新视图影响原表，否则会报错

语法

create view 视图名 as 查询语句;

#修改视图

alter view 视图名 as 查询语句;

#删除视图

drop view [if exists] 视图名;

#查看创建视图语句

show create view 视图名;

示例

alter view v_emp as
select deptno, count(*) num from EMP GROUP BY deptno; 

select * from v_emp;

作用：

• 重用 SQL 语句

  • 简化查询语句，隐藏复杂的 SQL
  • 安全：使用视图的用户只能访问他们被允许查询的结果集

• 视图的实现原理：

  • 临时表算法：复杂SQL如distinct， group by将视图的查询结果存放到临时表里，需要访问视图时，直接访问这个临时表，优点是可以处理复杂查询，缺点是引入了创建表的性能开销
  • 合并算法：简单的SQL语句， 重写含有视图的查询，将视图的定义sql直接合并到查询sql里，性能更高。

MySQL 优化器根据查询的具体情况来选择使用哪种算法。如果视图中包含GROUY BY、DISTINCT、聚合函数等，只要无法在原表记录和视图记录中建立一一映射的场景中， MySQL都将使用临时表算法来实现视图。

可更新视图：可以通过更新这个视图来更新视图涉及的相关表。只要指定了合适的条件，就可以更新、删除甚至向视图中写入数据。

#合并算法

create view abc as select * from emp;

explain select * from abc;

#临时表算法

create view dept_emp as select * from emp group by deptno;

explain select * from dept_emp;

#可更新视图

insert into abc(empno) values (111);

update abc set empno=222 where empno=111;

insert into dept_emp(empno) values (111);

#不可更新视图

create view abc2 as select deptno from emp;

insert into abc2(deptno) values (111);



create view abc2 as select empno from emp;

insert into abc2(empno) values (111);



create view abc2 as select deptno * 2 from emp;

update abc2 set deptno=222 where deptno is NULL;

索引

index，是存储引擎用于快速找到数据的一种数据结构。

索引失效： 使用索引不会加快查询时，则不使用索引 ，索引字段参与计算或作为函数参数不能用索引

MySQL 默认使用 InnoDB 存储引擎，该存储引擎是最重要、使用最广泛的，除非有非常特别的原因需要使用其他存储引擎，否则优先考虑 InnoDB。

• 优点：

  • 减少服务器需要扫描的数据量

    • 帮助服务器避免排序和临时表
  • 索引可以将随机 I/O 变为顺序 I/O，提高查询性能

• 缺点：

  • 从空间角度考虑，建立索引需要占用物理空间
  • 从时间角度 考虑，创建和维护索引都需要花费时间，例如对数据进行增删改的时候都需要维护索引。

常见索引类型

• 哈希索引：基于哈希表实现，查找非常快，但不支持范围查找和排序操作，也不支持部分索引列的查找，只支持等值比较的查询。如果哈希冲突很多的话，索引的维护代价会很高。因此，哈希索引只适用某些特定场合。在InnoDB中，支持的哈希索引是自适应的，不能人为创建。
• 全文索引：用于全文搜索的索引类型（倒排索引），可以执行关键字搜索。全文索引有很多限制，例如当数据量很大，内存无法装载全部索引时，搜索速度可能会非常慢。全文索引的维护成本也很大。MyISAM支持全文索引，InnoDB从1.2版本（MySQL5.6）开始支持全文索引。
• B+树索引：B+树索引就是传统意义上的索引，是目前关系型数据库中查找最为常用和最为有效的索引。B+树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据。B+树索引是顺序组织存储的，所以很适合查找范围数据。B+树索引分为聚簇索引（主键索引）和非聚簇索引（二级索引）。

B树和B+树

平衡二叉树随着节点的增加，树的高度会越来越高，会增加磁盘的I/O次数，影响查询效率，从而引出了B树，B树不限制一个节点只能由2个子节点，从而降低树的高度。

​ B树可以将节点的大小优化为磁盘块的大小，每次读取可以有效加载多个节点，B树常用于数据库库等需要高效访问磁盘的场景。

​ B树的查询流程：

如上图我要从找到E字母，查找流程如下：

1. 获取根节点的关键字进行比较，当前根节点关键字为M，E<M（26个字母顺序），所以往找到指向左边的子节点（二分法规则，左小右大，左边放小于当前节点值的子节点、右边放大于当前节点值的子节点）。
2. 拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点。
3. 拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）。
4. 通过指针信息取出这条记录的所有信息。

B+树是对B树的升级，B+树只有叶子节点存数据，非叶子节点只存索引。叶子节点包含所有索引，叶子节点构成一个有序链表，范围查找更快。由于非叶子节点只存索引，B+树比B树的非叶子节点可以存更多索引，高度更低，磁盘I/O次数更少。

B+树和B树的区别是：

1. B树的节点(根节点/父节点/中间节点/叶子节点)中没有重复元素，B+树有。
2. B树的中间节点会存储数据指针信息，而B+树只有叶子节点才存储。
3. B+树的每个叶子节点有一个指针指向下一个节点，把所有的叶子节点串在了一起。

从下图我们可以直观的看到B树和B+树的区别：紫红色的箭头是指向被索引的数据的指针，大红色的箭头即指向下一个叶子节点的指针。

假设被索引的列是主键，现在查找主键为5的记录，模拟一下查找的过程：

　　B树，在倒数第二层的节点中找到5后，可以立刻拿到指针获取行数据，查找停止。

　　B+树，在倒数第二层的节点中找到5后，由于中间节点不存有指针信息，则继续往下查找，在叶子节点中找到5，拿到指针获取行数据，查找停止。

　　B+树每个父节点的元素都会出现在子节点中，是子节点的最大（或最小）元素。叶子节点存储了被索引列的所有的数据。

那B+树比起B树的优点：

1. 由于中间节点不存指针，同样大小的磁盘页可以容纳更多的节点元素，树的高度就小。（数据量相同的情况下，B+树比B树更加“矮胖”），查找起来就更快。
2. B+树每次查找都必须到叶子节点才能获取数据，而B树不一定，B树可以在非叶子节点上获取数据。因此B+树查找的时间更稳定。
3. B+树的每一个叶子节点都有指向下一个叶子节点的指针，方便范围查询和全表查询：只需要从第一个叶子节点开始顺着指针一直扫描下去即可，而B树则要对树做中序遍历。

B+树的缺点是数据冗余

创建索引

语法

#创建索引

create index 索引名 on 表名(列名);

create index 索引名 on 表名(列名(前缀长度));

#查看索引

show index from 表名;

#删除索引

drop index 索引名 on 表名;

#查看是否使用索引

explain 查询语句

desc 查询语句

explain 各属性含义：

• id: 查询的序列号

• select_type: 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询

  • SIMPLE：查询中不包含子查询或者 UNION
  • PRIMARY：查询中包含任何复杂的子部分
  • SUBQUERY：作为 SELECT 或 WHERE 列表中的子查询
• table: 输出的行所引用的表

• type: 访问类型

  • ALL: 扫描全表
  • index: 扫描全部索引树
  • range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between、<、>等的查询
  • ref: 使用非唯一索引或非唯一索引前缀进行的查找
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • const: 单表中最多有一个匹配行，查询起来非常迅速，例如根据主键或唯一索引查询。
  • system 是const 类型的特例：表中只有一条记录
  • NULL: 不用访问表或者索引，直接就能得到结果，如 select 1 from test where 1
• key: 显示 MySQL 实际决定使用的索引。如果没有索引被选择，是 NULL
• key_len: 使用到索引字段的长度，key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。
• ref: 显示哪个字段或常数与key一起被使用
• rows: 这个数表示 MySQL 要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在 innodb 上可能是不准确的

• Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。

  • Using index：表示使用索引，如果只有 Using index，说明他没有查询到数据表，只用索引表就完成了这个查询，这个叫覆盖索引。
  • Using where：表示条件查询，如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where。
  • Using index condition：索引条件下推（Index Condition Pushdown，ICP）是MySQL使用索引的情况的优化。 简单来说，在服务器需要扫描表的情况下 当没有ICP时，存储引擎扫描可以明确地使用索引的条件，将符合条件的记录返回给服务器。 当使用ICP时，只要条件可以在索引上判断出来，就由存储引擎在索引树上完成判断，再将符合条件的记录返回给服务器。 ICP可以减少存储引擎必须访问基本表的次数以及服务器必须访问存储引擎的次数，这是是否使用ICP的最可靠的判断条件

前缀索引

索引开头的部分字符，可以大大节约索引空间，提高索引效率。如 TEXT 数据类型必须使用前缀索引，因为 MySQL 不允许索引这些列的完整长度。InnoDB 索引最大长度为767字节。

但这样会降低索引的选择性（不重复的索引值与总行数的比值），唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。所以要选择足够长的前缀保证较高的选择性，同时又不能太长（节约空间）。

可以统计最常见值的数量，再统计前缀的数量，不断调整前缀长度，当两个结果集的数量接近时，就得到比较合适的前缀长度了。

聚簇索引

聚簇索引，一种数据存储方式，将数据放在索引的叶子页，索引和数据在同一个 B+ 树上。因为无法同时把数据放在两个地方，所以一个表只有一个聚簇索引。

在 InnoDB 中，这个索引是主键，如果没有定义主键，InnoDB 会选择一个唯一的非空索引代替，也没有的话 InnoDB 会隐式定义一个主键作为聚簇索引。

• 优点：

  • 可以把相关数据保存在一起。
  • 数据访问更快，索引和数据在同一个结构中。

• 缺点：

  • 插入速度严重依赖插入顺序。
  • 更新聚簇索引列的代价很高。
  • 插入数据或更新主键时可能面临“页分裂”问题。当主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来放该行，也分裂会导致表占用更多的磁盘空间。
  • 非聚簇索引需要两次索引查找

覆盖索引（索引覆盖）

​ 查询所需要的数据都可以从索引中获取，而不用再去查询数据表中的实际数据，那么这个索引就是一个覆盖索引。

​ 覆盖索引可以减少树的搜索次数，避免了回表，显著提升了查询性能。

示例

-- 创建索引
create index ename_idx on EMP(ENAME);

explain select empno from EMP;

CREATE INDEX ename_idx on EMP(ename);
CREATE INDEX sal_idx on EMP(sal);

explain select * from EMP;
explain select empno from EMP;
explain select empno from EMP where ename = "smith";
explain select ename from EMP where empno = 7396;
explain select * from EMP where ename = "smith" and job = "clerk";

最左前缀原则

​ 创建多列的联合索引时，满足最左前缀原则。例如创建（a, b, c）三列的索引，实际上相当于创建了 a、（a, b）、（a, b, c）三个索引。

当不需要考虑排序和分组时，将选择性最高的列放在前面。这时索引的作用只用于优化 where 条件的查找，这样设计可以最快过滤需要的行。

-- 最左前缀原则

show index from EMP;

drop index sal_idx on EMP;

create index ename_job_sal on EMP(ename, job, sal);

explain select * from EMP where ename = 'smith';

explain select * from EMP where job = 'clerk';

explain select * from EMP where  and job = 'clerk';

explain select * from EMP where  job = 'clerk' and sal = 5000;

explain select * from EMP where  ename = 'smith' and sal = 5000; # 索引下推    

索引设计原则

• 为常作为查询条件的字段建立索引：如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。
• 为经常需要排序、分组和联合操作的字段建立索引：经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。
• 创建唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
• 限制索引的数目：每个索引都需要占⽤用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。
• 小表不建议索引（如数量级在百万以内）：由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
• 尽量使用前缀索引：如果索引的值很长，那么查询的速度会受到影响。
• 删除不再使用或者很少使用的索引。- 减小维护代价

索引使用策略

• 独立的列：索引使用不当会导致索引失效（查询中实际没有使用索引）。如果查询中的列不是独立的，MySQL不会使用索引。独立的列指查询时索引列不能是表达式的一部分，也不能是函数的参数，这两种情况都会导致索引失效
• 使用前缀索引：使用前缀索引可以节约索引空间，从而提高索引效率，但是需要平衡索引的选择性
• 使用联合索引：使用联合索引可以避免回表，实现覆盖索引，可以减少大量I/O操作
• 合适的索引列顺序：创建联合索引时，不同的列顺序会影响索引的性能，通常将选择性高的列放在最前面
• 合适的主键：最好选择不会修改的列作为主键，不考虑分库分表的情况最好使用自增主键

索引失效的情况

1. 查询条件包含or，or的前后不都是索引
2. 使用like查询，%在前面且没有覆盖索引
3. 对索引列使用函数
4. 索引列使用运算
5. 索引列使用不等于（<> != not in）
6. 出现隐式类型转换，如字符型索引使用数字匹配
7. 联合索引没有使用最左匹配
8. 全表扫描比索引快（数量很少）

示例

explain select empno from EMP where empno = 7396 or job = "clerk"; # 查询条件包含or，or的前后不都是索引
explain select * from EMP where empno = 7396 or sal > 2000; # 查询条件包含or，or的前后不都是索引
explain select * from EMP where  sal != 5000; #索引列使用不等于（<> != not in）
explain select * from EMP where  sal > 5000 or sal < 5000;#索引列使用不等于（<> != not in）
explain select * from EMP where ename like "a%";
explain select * from EMP where ename like "%a"; # 使用like查询，%在前面且没有覆盖索引
explain select * from EMP where ename like "%a%"; # 使用like查询，%在前面且没有覆盖索引
explain select * from EMP where empno + 1 > 5000; # 索引字段参与计算

explain select * from EMP where ename =  5000; # 字符串向数字转换 索引字段参与计算
explain select * from EMP where sal =  "5000"; # 字符串向数字转换 索引字段没有参与计算

三范式

​ 范式是关系型数据库设计的一种规范，目标是消除冗余，建立结构合理的数据库，从而提升数据存储和使用的性能。

第一范式（1NF）

每列的原子性，表中的每一个字段都是不可分割的，同一列中不能有多个值。第一范式是对关系模式的基本要求，不满足第一范式的数据库不是关系型数据库。

• 不满足第一范式的示例：

注意：第一范式要根据实际需求来定。

考虑地址字段（黑龙江省大庆市高兴区行知街135号）是否符合第一范式，如果经常访问地址中的城市部分就不符合第一范式需要对地址进行拆分。如果不会访问拆分的部分，就符合第一范式，拆分反而不利于查询完整地址。

第二范式（2NF）

​ 确保唯一性和依赖性，每个表都有主键，且其他字段完全依赖主键。

第二范式是在第一范式的基础上，要求表中的每一条数据可以被唯一区分，通常使用主键实现，其他所有字段都完全依赖主键。

其他字段依赖主键是指，其他每个字段都与主键完全相关，当确定主键的值时就能确定其他所有字段的值。也就是说一个表只能存一种数据，不可以把多种数据存到一个表中。

完全依赖是指，联合主键时，其他字段不可以只依赖主键中的某个字段，必须依赖联合主键中的每一个字段。

• 不满足第二范式的示例：

第三范式（3NF）

在第二范式的基础上，非主键字段必须直接依赖于主键，不能存在传递依赖。

• 不满足第三范式的示例：

范式总结

• 1NF：每列原子性，字段不可分割
• 2NF：唯一性和依赖性，要有主键，且其他字段完全依赖主键

• 3NF：没有传递依赖

  范式的优缺点：

• 优点：

  • 重复数据很少或者没有
  • 表更小，可以更好的放在内存里，执行操作更快
  • 更新操作更快
  • 可以更少使用 group by 和 distinct
• 缺点：查询时通常需要多表关联查询，更难进行索引优化

事务和锁

事务

​ transaction，一组原子性的 SQL 查询，或者说是一个独立的工作单元。如果能够成功执行这组查询的全部语句，就会执行这组查询；如果其中任何一条语句无法成功执行，那么这组查询的所有语句都不会执行。

​ 也就是说，事务内部的语句，要么全部执行成功，要么全部执行失败。

使用事务

• 开始标志：任何一条 DML 语句的执行

• 结束标志：

  • 提交：成功的结束，将所有的DML语句操作记录和底层硬盘文件中数据进行同步
  • 回滚：失败的结束：将所有 DML 语句操作记录全部清除

MySQL 默认是自动提交（重新连接数据库会开启自动提交）

#开启事务
start transaction;

#提交事务
commit;
#保存回滚点
savepoint 回滚点名;
#回滚事务
rollback[ to 回滚点名];

#查看事务的提交方式
show variables like 'autocommit';
#启用和关闭自动提交模式
set autocommited=1;
set autocommited=0;

• 自动提交和手动提交，再开一个 MySQL-Front 窗口查看数据变化

#执行语句，再打开一个 MySQL-Front窗口，查询是否有变化
update account set money=money-1000 where name="lz";
update account set money=money+1000 where name="xz";
select * from account;

#查看提交方式，改为手动提交
show variables like 'autocommit';
set autocommit=off;
#手动提交
commit;

四个特性

ACID：

• 原子性（atomicity）：事务是最小工作单元，不可再分，事务的所有操作要么全部提交成功，要么全部失败回滚，不可能只执行其中的一部分操作。
• 一致性（consistency）：数据库总是从一个一致的状态转换成另一个一致状态，不会因为某条语句失败而出现其他状态。
• 隔离性（isolation）：通常来说，一个事务所做的修改在最终提交前，对其他事务是不可见的。
• 持久性（durability）：事务一旦提交，其做的修改会持久的保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。

一个兼容 ACID 的数据库系统很多复杂但可能用户并没有觉察到的工作。 相比没有实现 ACID 的数据库，通常会需要更强的 CPU 处理能力、更大的内 存和更多的磁盘空间。

并行事务带来的问题

数据一致性问题：

• 脏读（dirty read）：一个事务读到另一个事务未提交的数据就叫做脏读。一个事务对一条记录做修改，在这个事务提交前是有可能随时回滚的，这些数据就是脏数据。
• 不可重复读（non-repeatable read）：在一个事务内多次读取同一条记录，前后结果不一样就叫做不可重复读。事务 A 多次查询同一行数据，在多次查询中间，事务 B 对该行数据进行了修改，事务 A 多次查询同一行的数据就会不一致。
• 幻读（phantom read）：在一个事务内按相同条件多次查询，前后结果集的数量不同就叫做幻读。事务 A 按 id>5 的条件进行多次查询，在多次查询中间，事务 B 插入了一条 id 为6的数据，事务 A 多次查询的结果集数量就会不同。

影响：

• 脏读：读到其他事务未提交的数据
• 不可重复读：前后读取数据不一致
• 幻读：前后读取的结果集数量不同

隔离级别

在 SQL 标准中定义了四种隔离级别， 每一种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发， 系统的开销也更低。

• READ UNCOMMITTED（未提交读）：事务中的修改，没有提交也能被其他事务看到。这个级别会导致很多问题，性能也没有好太多，一般很少使用。
• READ COMMITTED（提交读）：事务中的修改，提交后才能被其他事务看到。有时也叫不可重复读，因为两次执行同样的查询，可能得到不同的结果。
• REPEATABLE READ（可重复读）：同一事务中多次读取同样记录的结果是一致的。该级别无法解决幻读问题。该级别是 MySQL 的默认隔离级别，并且 MySQL 在该级别就可以很大程度解决幻读问题。
• SERIALIZABLE（可串行化）：强制事务串行执行，避免幻读问题。该级别会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际很少用这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

InnoDB中的锁

共享锁和排他锁

• 共享锁（S Lock）：读锁，允许事务读取数据
• 排他锁（X Lock）：也叫独占锁，写锁，允许事务删除或更新数据

​ 如果事务获取了某个数据的共享锁，其他事务可以立即获得该数据的共享锁，这种情况叫锁兼容。如果事务获取了某个数据的共享锁或排他锁，其他事务想要获取该数据的排他锁，必须要等到该行的锁释放掉。

意向锁

​ InnoDB 允许事务在行锁和表锁同时存在。为支持在不同粒度上进行加锁，InnoDB 支持意向锁。

意向锁，将锁定的对象分为多个层级，意向锁意味着事务有意向在更细粒度上加锁。如果需要对行加锁，需要先对表加意向锁再对行加锁。

意向锁在 InnoDB 中就是表级别的锁，支持两种意向锁：

• 意向共享锁（IS Lock），事务有意向对表中某些行加共享锁
• 意向排他锁（IX Lock），事务有意向对表中某些行加排他锁

​ 表级锁的兼容性

注意：意向锁不会和行级锁冲突，意向锁之间也不会冲突，意向锁只会和共享表锁和排他表锁冲突

​ 意向锁的作用：如果没有意向锁，想要给一个表加表锁必须要检查该表是否有表锁和每一行是否有锁。而如果在加行锁前给这个表加上了意向锁，这时只需要检查表锁和意向锁就可以了，不需要检查每一行的锁。

一致性非锁定读

原理：快照读

​ 一致性非锁定读，读取正在执行 delete 和 update 操作的行时，不会等待该行上锁的释放，而是读取该行的一个快照数据（该行之前的版本）。非锁定读极大的提高了数据库的并发性，InnoDB 默认时这种读取方式，也就是说默认普通的 select 语句是不会加锁的，而是通过读取快照实现数据一致性。

​ 上面提到的快照数据就是该行数据的历史版本，由此带来的并发控制称为多版本并发控制（MVCC）。

​ READ COMMITTED（提交读）和 REPEATABLE READ（可重复读）在 InnoDB 中使用的是一致性非锁定读，但是读取的快照不同。提交读级别读取的是最新版本的快照，可重复读级别读取的是事务开始时数据的快照。

​ 可重复读级别通过读快照，可以解决前面提到的幻读问题，但是有些情况需要锁定读。

一致性锁定读

​ InnoDB 默认使用一致性非锁定读。某些情况用户需要显式加锁保证数据的一致性，支持两种一致性锁定读的操作

• select ... for update：对读取的行加一个排他锁
• select ... lock in share mode：对读取的行加一个共享锁

如果只对读取的行加锁会有幻读问题

​ 锁定读时使用键值间隙锁（Next-Key Lock），就是行锁加间歇锁，可以解决幻读问题。

• Record Lock：单行记录的锁
• Gap Lock：间歇锁，锁定一个范围，但不包含记录本身
• Next-Key Lock：Gap Lock + Record Lock，锁定一个范围，并锁定记录本身

非锁定读使用快照和锁定读使用间歇锁可以基本解决幻读问题，但是极特殊情况还是有可能发生幻读。