MySQL
数据库系统
关系型数据库(RDBMS)
- Oracle
- MySQL
- SQL server
- PostgreSQL
- SQLite
非关系型数据库(NoSQL)
- Redis（缓存数据库）
- MongoDB（文档型数据库）
- Elasticsearch（搜索服务）
- Cassandra（列式数据库）
- HBase（分布式、列式数据库）
DDL
定义
DDL(Data Definition Language)，数据定义语言，该语言部分包括以下内容：
- 对数据库的常用操作
- 对表结构的常用操作
- 修改表结构
常用操作
show databases;
create database [if not exists] mydb1 [charset=utf8];
use mydb1;
drop database [if exists] mydb1;
alter database mydb1 character set utf8;
创建表
格式：
create table [if not exists]表名(
字段名1 类型[(宽度)] [约束条件] [comment '字段说明'],
字段名2 类型[(宽度)] [约束条件] [comment '字段说明'],
字段名3 类型[(宽度)] [约束条件] [comment '字段说明']
)[表的一些设置];
例子：
use mydb1;
create table if not exists student (
sid int,
name varchar(20),
gender varchar(20),
age int,
birth date,
address varchar(20)
);
数据类型
原则：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。
数值类型
日期和时间类型
字符串类型
其他操作
-- 查看当前数据库的所有表名称
show tables;
-- 查看指定某个表的创建语句
show create table 表名；
-- 查看表结构
desc 表名
-- 删除表
drop table 表名
修改表结构
-- 添加列
alter table 表名 add 列名 类型(长度) [约束];
ALTER TABLE student ADD `dept` VARCHAR(20);
-- 修改列名和类型
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
ALTER TABLE student change `dept` department VARCHAR(30);
-- 删除列
alter table 表名 drop 列名;
ALTER TABLE student DROP department;
-- 修改表名
rename table 表名 to 新表名;
rename table `student` to stu;
DML
定义
DML是指数据操作语言，英文全称是Data Manipulation Language，用来对数据库中表的数据记录进行更新。
- 插入 insert
- 删除 delete
- 更新 update
插入
insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...)； //向表中插入某些
insert into 表 values (值1,值2,值3...); //向表中插入所有列
insert into student(sid,name,gender,age,birth,address,score)
values(1001,'男',18,'1996-12-23','北京',83.5);
insert into student values(1001,'男',18,'1996-12-23','北京',83.5);
修改
update 表名 set 字段名=值,字段名=值...;
update 表名 set 字段名=值,字段名=值... where 条件;
-- 将所有学生的地址修改为重庆
update student set address = '重庆';
-- 将id为1004的学生的地址修改为北京
update student set address = '北京' where id = 1004
-- 将id为1005的学生的地址修改为北京，成绩修成绩修改为100
update student set address = '广州', score = 100 where id = 1005
删除
delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解为是将整个表删除，然后再创建该表。
delete from 表名 [where 条件];
truncate table 表名 或者 truncate 表名
-- 删除sid为1004的学生数据
delete from student where sid = 1004;
-- 删除表所有数据
delete from student;
-- 清空表数据
truncate table student;
truncate student;
DQL
格式：
select
[all|distinct]
<目标列的表达式1> [别名],
<目标列的表达式2> [别名]...
from <表名或视图名> [别名],<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名>
[having <条件表达式>]]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];
简单查询
-- 1.查询所有的商品
select * from product;
-- 2.查询商品名和商品价格
select pname,price from product;
-- 3.别名查询.使用的关键字是as（as可以省略的）
-- 3.1表别名
select * from product as p;
-- 3.2列别名
select pname as pn from product;
-- 4.去掉重复值
-- 当distinct后面跟两个字段时，去除两个字段完全相同的字段
select distinct price from product;
-- 5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
select pname, price + 10 from product;
运算符
算术运算符
比较运算符
在求最值时，如果一个值为null，则不会进行比较，结果直接为null。
逻辑运算符
位运算符
排序查询
格式：
select
字段名1, 字段名2……
from 表名
order by 字段名1 [asc|desc]，字段名2[asc|desc]……
特点：
- asc代表升序，desc代表降序，如果不写默认升序。
- order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名。多个字段时，如果字段1相同，则按照字段2排序。
- 字段必须为数值、英文、数字字符串、日期类型。
- order by子句，放在查询语句的最后面。LIMIT子句除外。
例子：
-- 1.使用价格排序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上，以分类排序(降序)
select * from product order by price desc,category_id asc;
-- 3.显示商品的价格(去重复)，并排序(降序)
select distinct price from product order by price desc;
聚合查询
之前我们做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值；另外聚合函数会忽略空值。
对null的处理：
- count函数对null值的处理 如果count函数的参数为星号（*），则统计所有记录的个数。而如果参数为某字段，不统计含null值的记录个数。
- sum和avg函数对null值的处理：忽略null值的存在，就好象该条记录不存在一样。
- max和min函数对null值的处理：同样忽略null值的存在。
分组查询
分组查询是指使用group by字句对查询信息进行分组。
格式：
select 字段1, 字段2… from 表名 group by 分组字段 having 分组条件;
注：
group by后可跟多个字段，只有都相同才会分到同一个组。- 如果要进行分组的话，则
select子句之后，只能出现分组的字段和统计函数，其他的字段不能出现。
分组之后的条件筛选having：
- 分组之后对统计结果进行筛选的话必须使用
having，不能使用where。 where子句用来筛选from子句中指定的操作所产生的行。group by子句用来分组where子句的输出。having子句用来从分组的结果中筛选行。
格式：
select 字段1, 字段2… from 表名 where 条件 group by 分组字段 having 分组条件;
分页查询
格式：
-- 方式1，显示前n条
select 字段1, 字段2... from 表明 limit n
-- 方式2，分页显示
select 字段1, 字段2... from 表明 limit m, n
-- m: 整数，表示从第几条索引开始，计算方式（当前页-1）* 每页显示条数
-- n: 整数，表示查询多少条数据
例子：
-- 查询product表的前5条记录
select * from product limit 5
-- 从第4条开始显示，显示5条
select * from product limit 3, 5
-- 分页显示
select * from product limit 0, 60; -- 第一页(1-1)*60
select * from product limit 60, 60; -- 第二页(2-1)*60
select * from product limit 120, 60;
...
select * from product limit (n - 1) * 60, 60;
INSERT INTO SELECT
将一张表的数据导入到另一张表中，可以使用INSERT INTO SELECT语句 。
要求目标表Table2必须存在。
格式：
insert into Table2(field1, field2…) select value1,value2,… from Table1;
insert into Table2 select * from Table1;
SELECT INTO FROM
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。
格式：
SELECT vale1, value2 into Table2 from Table1
正则表达式
正则表达式(regular expression)描述了一种字符串匹配的规则，正则表达式本身就是一个字符串，使用这个字符串来描述、用来定义匹配规则，匹配一系列符合某个句法规则的字符串。在开发中，正则表达式通常被用来检索、替换那些符合某个规则的文本。
MySQL通过REGEXP关键字支持正则表达式进行字符串匹配。
例子：
-- ^ 在字符串开始处进行匹配
SELECT 'abc' REGEXP '^a';
-- $ 在字符串末尾开始匹配
SELECT 'abc' REGEXP 'a$';
SELECT 'abc' REGEXP 'c$';
-- . 匹配任意字符
SELECT 'abc' REGEXP '.b';
SELECT 'abc' REGEXP '.c';
SELECT 'abc' REGEXP 'a.';
-- [...] 匹配括号内的任意单个字符
SELECT 'abc' REGEXP '[xyz]';
SELECT 'abc' REGEXP '[xaz]';
-- [^...] 注意^符合只有在[]内才是取反的意思，在别的地方都是表示开始处匹配
SELECT 'a' REGEXP '[^abc]';
SELECT 'x' REGEXP '[^abc]';
SELECT 'abc' REGEXP '[^a]';
-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据
SELECT 'stab' REGEXP '.ta*b';
SELECT 'stb' REGEXP '.ta*b';
SELECT '' REGEXP 'a*';
-- a+ 匹配1个或者多个a,但是不包括空字符
SELECT 'stab' REGEXP '.ta+b';
SELECT 'stb' REGEXP '.ta+b';
-- a? 匹配0个或者1个a
SELECT 'stb' REGEXP '.ta?b';
SELECT 'stab' REGEXP '.ta?b';
SELECT 'staab' REGEXP '.ta?b';
-- a1|a2 匹配a1或者a2，
SELECT 'a' REGEXP 'a|b';
SELECT 'b' REGEXP 'a|b';
SELECT 'b' REGEXP '^(a|b)';
SELECT 'a' REGEXP '^(a|b)';
SELECT 'c' REGEXP '^(a|b)';
-- a{m} 匹配m个a
SELECT 'auuuuc' REGEXP 'au{4}c';
SELECT 'auuuuc' REGEXP 'au{3}c';
-- a{m,n} 匹配m到n个a,包含m和n
SELECT 'auuuuc' REGEXP 'au{3,5}c';
SELECT 'auuuuc' REGEXP 'au{4,5}c';
SELECT 'auuuuc' REGEXP 'au{5,10}c';
-- (abc) abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号，所以括号适合上面的所有情况。
SELECT 'xababy' REGEXP 'x(abab)y';
SELECT 'xababy' REGEXP 'x(ab)*y';
SELECT 'xababy' REGEXP 'x(ab){1,2}y';
SQL执行顺序
书写顺序：
select -> distinct -> from -> join -> on -> where -> group by -> having -> order by -> limit
执行顺序：
from -> on -> join -> where -> group by(开始使用select中的别名，后面的语句中都可以使用别名) -> sum、count、max、avg -> having -> select -> distinct -> order by -> limit
约束
定义
作用：表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性。
分类：
- 主键约束(primary key) PK
- 自增长约束(auto_increment)
- 非空约束(not null)
- 唯一性约束(unique)
- 默认约束(default)
- 零填充约束(zerofill)
- 外键约束(foreign key) FK
主键约束
定义
- MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行，方便在RDBMS中尽快的找到某一行。
- 主键约束相当于
唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。 - 每个表最多只允许一个主键。
- 主键约束的关键字是：
primary key - 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
添加单列主键
创建单列主键的方式：
- 在定义字段的同时指定主键。
- 定义完字段之后指定主键。
- 创建表结束后添加主键。
方式1，格式：
create table 表名(
...
<字段名> <数据类型> primary key
...
)
方式1，例子：
create table emp1(
eid int primary key,
name VARCHAR(20),
deptId int,
salary double
);
方式2，格式：
-- 在定义字段之后再指定主键
create table 表名(
-- 字段
...
[constraint <约束名>] primary key [字段名]
);
方式2，例子：
create table emp2(
eid INT,
name VARCHAR(20),
deptId INT,
salary double,
-- constraint pk1 可以省略
constraint pk1 primary key(id)
);
方式3，格式：
alter table <表名> add primary key（字段列表);
添加多列主键(联合主键)
联合主键，就是这个主键是由一张表中多个字段组成的。
注意：
- 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束。
- 一张表只能有一个主键，联合主键也是一个主键。
- 只要联合主键的字段不完全相同即合法。
格式：
create table 表名(
...
primary key (字段1，字段2，…,字段n)
);
例子：
create table emp3(
name varchar(20),
deptId int,
salary double,
primary key(name,deptId)
);
删除主键约束
格式：
alter table <数据表名> drop primary key;
例子：
-- 删除单列主键
alter table emp1 drop primary key;
-- 删除联合主键
alter table emp5 drop primary key;
自增长约束
定义
当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。
例子：
create table t_user1(
id int primary key auto_increment,
name varchar(20)
);
特点
- 默认情况下，auto_increment的初始值是 1，每新增一条记录，字段值自动加 1。
- 一个表中只能有一个字段使用 auto_increment约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）。
- auto_increment约束的字段必须具备 NOT NULL 属性。
- auto_increment约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT）等。
- auto_increment约束字段的最大值受该字段的数据类型约束，如果达到上限，auto_increment就会失效。
指定自增字段初始值
-- 方式1，创建表时指定
create table t_user2 (
id int primary key auto_increment,
name varchar(20)
)auto_increment=100;
-- 方式2，创建表之后指定
create table t_user3 (
id int primary key auto_increment,
name varchar(20)
);
alter table t_user2 auto_increment=100;
delete和truncate在删除后自增列的变化：
- delete数据之后自动增长从断点开始
- truncate数据之后自动增长从默认起始值开始
非空约束
格式：
-- 创建非空约束
-- 方式1：
<字段名><数据类型> not null;
-- 方式2：
alter table 表名 modify 字段 类型 not null;
-- 删除非空约束
-- alter table 表名 modify 字段 类型
alter table t_user7 modify name varchar(20);
唯一约束
MySQL中NULL与任何值都不相同，甚至和自己都不相同。
-- 方式1：
<字段名> <数据类型> unique
-- 方式2：
alter table 表名 add constraint 约束名 unique(列);
-- 删除唯一约束
alter table <表名> drop index <唯一约束名>;
默认约束
-- 方式1：
<字段名> <数据类型> default <默认值>;
-- 方式2：
alter table 表名 modify 列名 类型 default 默认值;
-- 删除默认约束
alter table <表名> modify column <字段名> <类型> default null;
零填充约束
- 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0。
- zerofill默认为int(10)。
- 当使用zerofill时，默认会自动加unsigned（无符号）属性，使用unsigned属性后，数值范围是原值的2倍，例如，有符号为
-128~+127，无符号为0~256。
create table t_user12 (
id int zerofill , -- 零填充约束
name varchar(20)
);
-- 删除零填充约束
alter table t_user12 modify id int;
多表查询
多表关系：一对一、一对多/多对一关系，多对多
外键约束
定义
MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。
特点：
- 主表必须已经存在于数据库中，或者是当前正在创建的表。
- 必须为主表定义主键。
- 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这 个外键的内容就是正确的。
- 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
- 外键中列的数目必须和主表的主键中列的数目相同。
- 外键中列的数据类型必须和主表主键中对应列的数据类型相同。
- 在外键约束下，只能先在主表添加数据，先删除从表数据。
创建外键约束
在创建表时设置外键约束
格式：
[constraint <外键名>] foreign key 字段名 [，字段名2，…] references <主表名> 主键列1 [，主键列2，…]
例子：
create database mydb3;
use mydb3;
-- 创建部门表
create table if not exists dept(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
create table if not exists emp(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20), -- 员工所属部门
-- constraint emp_fk 可省略
constraint emp_fk foreign key (dept_id) references dept (deptno) –- 外键约束
);
在创建表后设置外键约束：
格式：
alter table <数据表名> add constraint <外键名> foreign key(<列名>) references
<主表名> (<列名>);
例子：
-- 创建部门表
create table if not exists dept2(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno);
删除外键约束
alter table <表名> drop foreign key <外键约束名>;
多对多关系
在多对多关系中，A表的一行对应B的多行，B表的一行对应A表的多行，我们要新增加一个中间表，来建立多对多关系。
-- 学生表和课程表(多对多)
-- 1 创建学生表student(左侧主表)
create table if not exists student(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);
-- 2 创建课程表course(右侧主表)
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);
-- 3 创建中间表student_course/score(从表)
create table score(
sid int,
cid int,
score double
);
-- 4 建立外键约束(2次)
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
-- 5给学生表添加数据
insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
-- 6给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
-- 7给中间表添加数据
insert into score values(1,1),(1,2),(2,1),(2,3),(3,2),(3,3);
多表联合查询
分类
多表查询就是同时查询两个或两个以上的表。
- 交叉连接查询(产生笛卡尔积)
- 语法：
select * from A, B;
- 语法：
- 内连接查询(使用的关键字
inner join，inner可以省略)- 隐式内连接（SQL92标准）：
select * from A,B where 条件; - 显示内连接（SQL99标准）：
select * from A inner join B on 条件;
- 隐式内连接（SQL92标准）：
- 外连接查询(使用的关键字
outer join，outer可以省略)- 左外连接：
left outer join select * from A left outer join B on 条件; - 右外连接：
right outer join select * from A right outer join B on 条件; - 满外连接:
full outer join select * from A full outer join B on 条件;
- 左外连接：
- 子查询
select的嵌套
- 表自关联
- 将一张表当成多张表来用
交叉连接查询
- 交叉连接查询返回被连接的两个表所有数据行的笛卡尔积。
- 笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配。
- 假如A表有m行数据，B表有n行数据，则返回m*n行数据。
- 笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选。
格式：
select * from 表1, 表2, 表3…;
内连接查询
内连接查询求多张表的交集。
格式：
隐式内连接（SQL92标准）：select * from A,B where 条件;
显示内连接（SQL99标准）：select * from A inner join B on 条件;
例子：
-- 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
外连接查询
外连接分为左外连接（left outer join）、右外连接(right outer join)、满外连接(full outer join)。
注：oracle里面有full join,可是在MySQL对full join支持的不好。我们可以使用union来达到目的。
格式：
-- 左外连接：left outer join
select * from A left outer join B on 条件;
-- 右外连接：right outer join
select * from A right outer join B on 条件;
-- 满外连接: full outer join
select * from A full outer join B on 条件;
例子：
-- 外连接查询
-- 查询哪些部门有员工，哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查询哪些员工有对应的部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
子查询
子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。
子查询可以返回的数据类型一共分为四种：
- 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据；
- 单行多列：返回一行数据中多个列的内容；
- 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围；
- 多行多列：查询返回的结果是一张临时表。
子查询关键字：
- ALL关键字
- ANY关键字
- SOME关键字
- IN关键字
- EXISTS关键字
ALL
格式：
select …from …where c > all(查询语句)
-- 等价于：
select ...from ... where c > result1 and c > result2 and c > result3
特点：
- ALL：与子查询返回的所有值比较为true 则返回true
- ALL可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
- ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。
例子：
-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');
-- 查询不属于任何一个部门的员工信息
select * from emp3 where dept_id != all(select deptno from dept3);
ANY和SOME
格式：
select …from …where c > any(查询语句)
-- 等价于：
select ...from ... where c > result1 or c > result2 or c > result3
特点：
- ANY：与子查询返回的任何值比较为true 则返回true
- ANY可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的任何一个数据。
- 表示制定列中的值要大于子查询中的任意一个值，即必须要大于子查询集中的最小值。同理可以推出其它的比较运算符的情况。
- SOME和ANY的作用一样，SOME可以理解为ANY的别名
例子：
-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003');
IN
格式：
select …from …where c in(查询语句)
-- 等价于：
select ...from ... where c = result1 or c = result2 or c = result3
特点：
- IN关键字，用于判断某个记录的值，是否在指定的集合中
- 在IN关键字前边加上not可以将条件反过来
例子：
-- 查询研发部和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;
EXISTS
格式：
select …from …where exists(查询语句)
特点：
- 该子查询如果“有数据结果”(至少返回一行数据)，则该EXISTS() 的结果为“true”，外层查询执行
- 该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行
- EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立
- 注意，EXISTS关键字，比IN关键字的运算效率高，因此，在实际开发中，特别是大数据量时，推荐使用EXISTS关键字
例子：
-- 查询公司是否有大于60岁的员工，有则输出
-- 注意此处的a.age
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
表自关联
MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。注意自关联时表必须给表起别名。
格式：
select 字段列表 from 表1 a , 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;
例子：
-- 创建表,并建立自关联约束
create table t_sanguo(
eid int primary key,
ename varchar(20),
manager_id int,
-- 添加自关联约束
foreign key (manager_id) references t_sanguo (eid)
);
-- 添加数据
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
-- 进行关联查询
-- 1. 查询每个三国人物及他的上级信息
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;
select a.ename, b.ename from t_sanguo a join t_sanguo b on a.manager_id = b.eid;
-- 2. 查询所有三国人物及他的上级信息
select a.ename, b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3. 查询所有三国人物及上级、上上级
select a.ename, b.ename, c.ename
from t_sanguo a
left join t_sanguo b on a.manager_id = b.eid
left join t_sanguo c on b.manager_id = c.eid;
函数
分类
- 聚合函数
- 数学函数
- 字符串函数
- 日期函数
- 控制流函数
- 窗口函数
聚合函数
在MySQL中，聚合函数主要由：count, sum, min, max, avg。
这里学习：group_concat(），该函数用户实现行的合并。
group_concat()函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果。
格式：
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])
例子：
-- 将所有员工的名字合并成一行
select group_concat(emp_name) from emp;
-- 指定分隔符合并
select department,group_concat(emp_name separator ';' ) from emp group by department;
-- 指定排序方式和分隔符
select department,group_concat(emp_name order by salary desc separator ';' ) from emp group by department;
数学函数
字符串函数
SELECT LENGTH('你好') -- 6
SELECT LENGTH('hello') -- 5
SELECT CHAR_LENGTH('你好') -- 2
SELECT CHAR_LENGTH('hello') -- 5
日期函数
| 名称 | 描述 |
|---|---|
| ADDDATE() | 增加日期 |
| ADDTIME() | 增加时间 |
| CONVERT_TZ() | 将当前时区更改为另一时区 |
| CURDATE() | 返回当前日期 |
| CURRENT_DATE(), CURRENT_DATE | CURDATE() 的别名 |
| CURRENT_TIME(), CURRENT_TIME | CURTIME() 的别名 |
| CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | NOW() 的别名 |
| CURTIME() | 返回当前时间 |
| DATE_ADD() | 将两个日期相加 |
| DATE_FORMAT() | 按照指定格式格式化日期 |
| DATE_SUB() | 将两个日期相减 |
| DATE() | 从 date 或者 datetime 表达式中提取出日期部分 |
| DATEDIFF() | 将两个日期相减 |
| DAY() | DAYOFMONTH() 的别名 |
| DAYNAME() | 返回某天在用星期中的名称 |
| DAYOFMONTH() | 返回某天是当月的第几天 （1-31） |
| DAYOFWEEK() | 返回某天是该星期的第几天 |
| DAYOFYEAR() | 返回某天是一年中的第几天（1-366） |
| EXTRACT | 提取日期中的某一部分 |
| FROM_DAYS() | 将天数转换为日期 |
| FROM_UNIXTIME() | 将某个日期格式化为 UNIX 时间戳 |
| HOUR() | 提取小时 |
| LAST_DAY | 返回参数日期所在月份的最后一天 |
| LOCALTIME(), LOCALTIME | NOW() 的别名 |
| LOCALTIMESTAMP, LOCALTIMESTAMP() | NOW() 的别名 |
| MAKEDATE() | 利用年份和某天在该年所处的天数来创建日期 |
| MAKETIME | MAKETIME() |
| MICROSECOND() | 由参数返回微秒 |
| MINUTE() | 由参数返回分钟 |
| MONTH() | 返回日期参数的月份 |
| MONTHNAME() | 返回月份的名字 |
| NOW() | 返回当前日期和时间 |
| PERIOD_ADD() | 向年月格式的日期数据之间添加一段时间 |
| PERIOD_DIFF() | 返回两个年月格式的日期数据之间的月份数 |
| QUARTER() | 返回日期参数所在的季度 |
| SEC_TO_TIME() | 将秒数转换为 ‘HH:MM:SS’ 格式 |
| SECOND() | 返回参数中的秒数 (0-59) |
| STR_TO_DATE() | 将字符串转换为日期数据 |
| SUBDATE() | 以三个参数调用的时候是 DATE_SUB() 的同义词 |
| SUBTIME() | 减去时间 |
| SYSDATE() | 返回函数执行的时的时刻 |
| TIME_FORMAT() | 格式化时间 |
| TIME_TO_SEC() | 将时间参数转换为秒数 |
| TIME() | 返回参数表达式中的时间部分 |
| TIMEDIFF() | 将两个时间相减 |
| TIMESTAMP() | 只有一个参数时，该函数返回 date 或者 datetime 表达式。当有两个参数时，将两个参数相加。 |
| TIMESTAMPADD() | 在 datetime 表达式上加上一段时间 |
| TIMESTAMPDIFF() | 在 datetime 表达式上减去一段时间 |
| TO_DAYS() | 将日期参数转换为天数 |
| UNIX_TIMESTAMP() | 返回 UNIX 时间戳 |
| UTC_DATE() | 返回当前 UTC 日期 |
| UTC_TIME() | 返回当前 UTC 时间 |
| UTC_TIMESTAMP() | 返回当前 UTC 日期和时间 |
| WEEK() | 返回参数的星期数 |
| WEEKDAY() | 返回日期参数时一个星期中的第几天 |
| WEEKOFYEAR() | 返回日期参数是日历上的第几周 (1-53) |
| YEAR() | 返回日期参数中的年份 |
| YEARWEEK() | 返回年份和星期 |
控制流函数
IF
CASE WHEN
例子：
-- 方式1
select
* ,
case
when payType=1 then '微信支付'
when payType=2 then '支付宝支付'
when payType=3 then '银行卡支付'
else '其他支付方式'
end as payTypeStr
from orders;
-- 方式2
select
* ,
case payType
when 1 then '微信支付'
when 2 then '支付宝支付'
when 3 then '银行卡支付'
else '其他支付方式'
end as payTypeStr
from orders;
窗口函数
定义
MySQL 8.0 新增窗口函数，窗口函数又被称为开窗函数，与Oracle 窗口函数类似。
格式：
window_function ( expr ) OVER (
PARTITION BY ...
ORDER BY ...
frame_clause
)
其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；OVER子句包含三个选项：
- 分区（PARTITION BY）：PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
- 排序（ORDER BY）：OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似
- 窗口大小（frame_clause）：frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。
分类：
另外还有开窗聚合函数: SUM,AVG,MIN,MAX。
序号函数
序号函数有三个：ROW_NUMBER()、RANK()、DENSE_RANK()，可以用来实现分组排序，并添加序号。
格式：
row_number()|rank()|dense_rank() over (
partition by ...
order by ...
)
例子：
-- 对每个部门的员工按照薪资排序，并给出排名
select
dname,
ename,
salary,
row_number() over(partition by dname order by salary desc) as rn
from employee;
开窗聚合函数
在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值。
例子：
select
dname,
ename,
hiredate,
salary,
sum(salary) over(partition by dname order by hiredate) as c1
from employee;
select
dname,
ename,
salary,
sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row) as c2
from employee;
分布函数
CUME_DIST和PERCENT_RANK
CUME_DIST
- 用途：分组内小于、等于当前rank值的行数 / 分组内总行数
- 应用场景：查询小于等于当前薪资（salary）的比例
PERCENT_RANK
- 用途：每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
- 应用场景：不常用
前后函数
LAG和LEAD
- 用途：返回位于当前行的前n行(LAG(expr,n))或后n行(LEAD * (expr,n))的expr的值
- 应用场景：查询前1名同学的成绩和当前同学成绩的差值
头尾函数
FIRST_VALUE和LAST_VALUE
- 用途：返回第一个(FIRST_VALUE(expr))或最后一个(LAST_VALUE(expr))expr的值
- 应用场景：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资
其他函数
NTH_VALUE(expr, n)
- 用途：返回窗口中第n个expr的值。expr可以是表达式，也可以是列名
- 应用场景：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资
NTILE(n)
- 用途：将分区中的有序数据分为n个等级，记录等级数
- 应用场景：将每个部门员工按照入职日期分成3组
视图
简述
介绍：
- 视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。
- 数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
- 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
作用：
- 简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用。
- 安全原因，如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图视，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可以对不同的用户，设定不同的视图。
创建视图
格式：
create [or replace] [algorithm = {undefined | merge | temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
参数说明：
（1）algorithm：可选项，表示视图选择的算法。
（2）view_name ：表示要创建的视图名称。
（3）column_list：可选项，指定视图中各个属性的名词，默认情况下与SELECT语句中的查询的属性相同。
（4）select_statement：表示一个完整的查询语句，将查询记录导入视图中。
（5）[with [cascaded | local] check option]：可选项，表示更新视图时要保证在该视图的权限范围之内。
例子：
create or replace view view1_emp
as
select ename,job from emp;
-- 查看表和视图
show full tables;
修改视图
修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER VIEW语句来修改视图。
格式：
alter view 视图名 as select语句
例子：
alter view view1_emp
as
select a.deptno, a.dname, a.loc, b.ename, b.sal from dept a, emp b where a.deptno = b.deptno;
更新视图
某些视图是可更新的。也就是说，可以在UPDATE、DELETE或INSERT等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种，那么它就是不可更新的：
- 聚合函数（SUM(), MIN(), MAX(), COUNT()等）
- DISTINCT
- GROUP BY
- HAVING
- UNION或UNION ALL
- 位于选择列表中的子查询
- JOIN
- FROM子句中的不可更新视图
- WHERE子句中的子查询，引用FROM子句中的表。
- 仅引用文字值（在该情况下，没有要更新的基本表）
注：视图中虽然可以更新数据，但是有很多的限制。一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据。因为，使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。
例子：
-- 更新视图
create or replace view view1_emp
as
select ename,job from emp;
update view1_emp set ename = '周瑜' where ename = '鲁肃'; -- 可以修改
insert into view1_emp values('孙权','文员'); -- 不可以插入
其他操作
重命名视图
-- rename table 视图名 to 新视图名;
rename table view1_emp to my_view1
删除视图
-- drop view 视图名[,视图名…];
drop view if exists view_student;
删除视图时，只能删除视图的定义，不会删除数据。
存储过程
简述
介绍：
- MySQL 5.0 版本开始支持存储过程。
- 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；
- 存储过就是数据库 SQL 语言层面的代码封装与重用。
特点：
- 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；
- 函数的普遍特性：模块化，封装，代码复用；
- 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤。
格式：
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)
begin
sql语句
end 自定义的结束符合
delimiter;
例子：
delimiter $$
create procedure proc01()
begin
select empno, ename from emp;
end $$
delimiter;
-- 调用存储过程
call proc01();
定义变量
局部变量
用户自定义，在begin/end块中有效。
格式：declare var_name type [default var_value];
例子：declare nickname varchar(32);
例子：
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default ‘aaa’; -- 定义局部变量
-- 赋值
set var_name01 = ‘zhangsan’;
select var_name01;
end $$
delimiter;
-- 调用存储过程
call proc02();
MySQL 中还可以使用 SELECT..INTO 语句为变量赋值。其基本语法如下：
select col_name [...] into var_name[...]
from table_name wehre condition
其中：
col_name 参数表示查询的字段名称；
var_name 参数是变量的名称；
table_name 参数指表的名称；
condition 参数指查询条件。
注意：当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
例子：
delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20) ;
select ename into my_ename from emp where empno=1001;
select my_ename;
end $$
delimiter;
-- 调用存储过程
call proc03();
用户变量
用户自定义，当前会话（连接）有效。不需要提前声明，使用即声明。
格式：
@var_name
例子：
delimiter $$
create procedure proc04()
begin
set @var_name01 = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01 ; -- 可以看到结果
系统变量
- 系统变量又分为
全局变量与会话变量。 - 全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改
my.ini这个文件来更改。 - 会话变量在每次建立一个新的连接的时候，由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份，来做为会话变量。
- 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
- 全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
- 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。
全局变量
由系统提供，在整个数据库有效。
格式：
@@global.var_name
例子：
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;
会话变量
由系统提供，当前会话（连接）有效。
格式：
@@session.var_name
例子：
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000;
传参
in
in 表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内。
例子：
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter;
call dec_param01('1001');
out
out 表示从存储过程内部传值给调用者。
例子：
-- 封装有参数的存储过程，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in empno int, out out_ename varchar(50))
begin
select ename into out_ename from emp where emp.empno = empno;
end $$
delimiter;
call proc08(1001, @o_ename);
select @o_ename;
inout
inout 表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完）。
流程控制
IF
格式：
if search_condition_1 then statement_list_1
[elseif search_condition_2 then statement_list_2] ...
[else statement_list_n]
end if
例子：
-- 输入学生的成绩，来判断成绩的级别：
/*
score < 60 :不及格
score >= 60 , score <80 :及格
score >= 80 , score < 90 :良好
score >= 90 , score <= 100 :优秀
score > 100 :成绩错误
*/
delimiter $$
create procedure proc_12_if(in score int)
begin
if score < 60
then
select '不及格';
elseif score < 80
then
select '及格' ;
elseif score >= 80 and score < 90
then
select '良好';
elseif score >= 90 and score <= 100
then
select '优秀';
else
select '成绩错误';
end if;
end $$
delimiter;
call proc_12_if(120)
CASE
格式：
-- 语法一：
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case
-- 语法二：
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case
例子：
-- 语法一
delimiter $$
create procedure proc14_case(in pay_type int)
begin
case pay_type
when 1
then
select '微信支付' ;
when 2
then
select '支付宝支付' ;
when 3
then
select '银行卡支付';
else
select '其他方式支付';
end case;
end $$
delimiter;
-- 语法二
delimiter $$
create procedure proc_15_case(in score int)
begin
case
when score < 60
then
select '不及格';
when score < 80
then
select '及格' ;
when score >= 80 and score < 90
then
select '良好';
when score >= 90 and score <= 100
then
select '优秀';
else
select '成绩错误';
end case;
end $$
delimiter;
循环
简述
循环分类：
- while
- repeat
- loop
循环控制：
- leave 类似于 break，跳出，结束当前所在的循环
- iterate类似于 continue，继续，结束本次循环，继续下一次
while
格式：
[标签:] while 循环条件 do
循环体;
end while [标签];
例子：
-- -------存储过程-while + leave
TRUNCATE TABLE USER;
delimiter $$
CREATE PROCEDURE proc16_while2 ( IN insertcount INT ) BEGIN
DECLARE
i INT DEFAULT 1;
label :
WHILE
i <= insertcount DO
INSERT INTO USER ( uid, username, `password` )
VALUES
( i, concat( 'user-', i ), '123456' );
IF
i = 5 THEN
LEAVE label;
END IF;
SET i = i + 1;
END WHILE label;
END $$
delimiter;
CALL proc16_while2 ( 10 );
-- -------存储过程-while + iterate
TRUNCATE TABLE USER;
delimiter $$
CREATE PROCEDURE proc16_while3 ( IN insertcount INT ) BEGIN
DECLARE
i INT DEFAULT 1;
label :
WHILE
i <= insertcount DO
SET i = i + 1;
IF
i = 5 THEN
ITERATE label;
END IF;
INSERT INTO USER ( uid, username, `password` )
VALUES
( i, concat( 'user-', i ), '123456' );
END WHILE label;
END $$
delimiter;
CALL proc16_while3 ( 10 );
repeat
格式：
[标签:] repeat
循环体;
until 条件表达式
end repeat [标签];
例子：
-- -------存储过程-repeat
USE mysql7_procedure;
TRUNCATE TABLE USER;
delimiter $$
CREATE PROCEDURE proc18_repeat ( IN insertCount INT ) BEGIN
DECLARE
i INT DEFAULT 1;
label :
REPEAT
INSERT INTO USER ( uid, username, PASSWORD )
VALUES
( i, concat( 'user-', i ), '123456' );
SET i = i + 1;
UNTIL i > insertCount
END REPEAT label;
SELECT
'循环结束';
END $$
delimiter;
CALL proc18_repeat ( 100 );
loop
格式：
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
例子：
-- -------存储过程-loop
TRUNCATE TABLE USER;
delimiter $$
CREATE PROCEDURE proc19_loop ( IN insertCount INT ) BEGIN
DECLARE
i INT DEFAULT 1;
label :
LOOP
INSERT INTO USER ( uid, username, PASSWORD )
VALUES
( i, concat( 'user-', i ), '123456' );
SET i = i + 1;
IF
i > 5 THEN
LEAVE label;
END IF;
END LOOP label;
SELECT
'循环结束';
END $$
delimiter;
CALL proc19_loop ( 10 );
游标
游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.
定义：
-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name
例子：
USE mysql7_procedure;
delimiter $$
CREATE PROCEDURE proc20_cursor (
IN in_dname VARCHAR ( 50 )) BEGIN
-- 定义局部变量
DECLARE
var_empno VARCHAR ( 50 );
DECLARE
var_ename VARCHAR ( 50 );
DECLARE
var_sal DECIMAL ( 7, 2 );
-- 声明游标
DECLARE
my_cursor CURSOR FOR SELECT
empno,
ename,
sal
FROM
dept a,
emp b
WHERE
a.deptno = b.deptno
AND a.dname = in_dname;
-- 打开游标
OPEN my_cursor;
-- 通过游标获取每一行数据
label :
LOOP
FETCH my_cursor INTO var_empno,
var_ename,
var_sal;
SELECT
var_empno,
var_ename,
var_sal;
END LOOP label;
-- 关闭游标
CLOSE my_cursor;
END
-- 调用存储过程
CALL proc20_cursor ( '销售部' );
异常处理
MySQL存储过程也提供了对异常处理的功能：通过定义HANDLER来完成异常声明的实现。
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
格式：
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
-- 继续执行
CONTINUE
-- 终止程序
| EXIT
-- MySQL不支持
| UNDO
}
condition_value: {
mysql_error_code
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
例子：
USE mysql7_procedure;
DROP PROCEDURE
IF
EXISTS proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
CREATE PROCEDURE proc20_cursor (
IN in_dname VARCHAR ( 50 )) BEGIN
-- 定义局部变量
DECLARE
var_empno INT;
DECLARE
var_ename VARCHAR ( 50 );
DECLARE
var_sal DECIMAL ( 7, 2 );
-- add
DECLARE
flag INT DEFAULT 1;
-- 声明游标
DECLARE
my_cursor CURSOR FOR SELECT
empno,
ename,
sal
FROM
dept a,
emp b
WHERE
a.deptno = b.deptno
AND a.dname = in_dname;
-- add
-- 定义句柄，当数据未发现时将标记位设置为0
DECLARE
CONTINUE HANDLER FOR NOT FOUND
SET flag = 0;
-- 打开游标
OPEN my_cursor;
-- 通过游标获取值
label :
LOOP
FETCH my_cursor INTO var_empno,
var_ename,
var_sal;
-- 判断标志位
IF
-- add
flag = 1 THEN
SELECT
var_empno,
var_ename,
var_sal;
ELSE LEAVE label;
END IF;
END LOOP label;
-- 关闭游标
CLOSE my_cursor;
END $$;
delimiter;
CALL proc21_cursor_handler ( '销售部' );
练习
/*
创建下个月的每天对应的表user_2021_12_01、user_2022_12_02、...
需求描述：
我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！
*/
-- 思路：循环构建表名 user_2021_11_01 到 user_2020_11_30；并执行create语句。
CREATE DATABASE mydb18_proc_demo;
USE mydb18_proc_demo;
DROP PROCEDURE
IF
EXISTS proc22_demo;
delimiter $$
CREATE PROCEDURE proc22_demo () BEGIN
DECLARE
next_year INT;-- 下一个月的年份
DECLARE
next_month INT;-- 下一个月的月份
DECLARE
next_month_day INT;-- 下一个月最后一天的日期
DECLARE
next_month_str VARCHAR ( 2 );-- 下一个月的月份字符串
DECLARE
next_month_day_str VARCHAR ( 2 );-- 下一个月的日字符串
-- 处理每天的表名
DECLARE
table_name_str VARCHAR ( 10 );
DECLARE
t_index INT DEFAULT 1;-- declare create_table_sql varchar(200);
-- 获取下个月的年份
SET next_year = YEAR (
date_add( now(), INTERVAL 1 MONTH ));-- 2021
-- 获取下个月是几月
SET next_month = MONTH (
date_add( now(), INTERVAL 1 MONTH ));-- 11
-- 下个月最后一天是几号
SET next_month_day = dayofmonth(
LAST_DAY(
date_add( now(), INTERVAL 1 MONTH )));-- 30
IF
next_month < 10 THEN
SET next_month_str = concat( '0', next_month );-- 1 ---》 01
ELSE
SET next_month_str = concat( '', next_month );-- 12
END IF;
WHILE
t_index <= next_month_day DO
IF
( t_index < 10 ) THEN
SET next_month_day_str = concat( '0', t_index );
ELSE
SET next_month_day_str = concat( '', t_index );
END IF;-- 2021_11_01
SET table_name_str = concat( next_year, '_', next_month_str, '_', next_month_day_str );-- 拼接create sql语句
SET @create_table_sql = concat( 'create table user_', table_name_str, '(`uid` INT ,`uname` varchar(50) ,`information` varchar(50)) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB' );-- FROM后面不能使用局部变量！
PREPARE create_table_stmt
FROM
@create_table_sql;
EXECUTE create_table_stmt;
DEALLOCATE PREPARE create_table_stmt;
SET t_index = t_index + 1;
END WHILE;
END $$delimiter;
CALL proc22_demo ();
SELECT YEAR
(
date_add( now(), INTERVAL 1 MONTH )) SELECT
dayofmonth(
LAST_DAY(
date_add( now(), INTERVAL 1 MONTH )));
存储函数
定义：
MySQL存储函数（自定义函数），函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。
存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。
存储函数与存储过程的区别：
- 存储函数有且只有一个返回值，而存储过程可以有多个返回值，也可以没有返回值。
- 存储函数只能有输入参数，而且不能带in, 而存储过程可以有多个in, out, inout参数。
- 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert, update, delete, create等语句。
- 存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
- 存储过程可以调用存储函数，但存储函数不能调用存储过程。
- 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。
格式：
create function func_name ([param_name type[,...]])
returns type
[characteristic ...]
begin
routine_body
end;
参数说明：
（1）func_name ：存储函数的名称。
（2）param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。
（3）RETURNS type：指定返回值的类型。
（4）characteristic：可选项，指定存储函数的特性。
（5）routine_body：SQL代码内容。
例子：
-- 创建存储函数-没有输输入参数
drop function if exists myfunc1_emp;
delimiter $$
create function myfunc1_emp() returns int
begin
declare cnt int default 0;
select count(*) into cnt from emp;
return cnt;
end $$
delimiter;
-- 调用存储函数
select myfunc1_emp();
-- 创建存储过程-有输入参数
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin
declare out_name varchar(50);
select ename into out_name from emp where empno = in_empno;
return out_name;
end $$
delimiter;
select myfunc2_emp(1008);
允许创建函数权限信任：
set global log_bin_trust_function_creators = TRUE;
触发器
简述
介绍：
- 触发器，就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动条用。
- 在MySQL中，只有执行
insert, delete, update操作时才能触发触发器的执行。 - 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。
- 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
特性：
- 什么条件会触发：I、D、U。
- 什么时候触发：在增删改前或者后。
- 触发频率：针对每一行执行。
- 触发器定义在表上，附着在表上。
注意：
- MySQL中触发器中不能对本表进行
insert, delete, update操作，以免递归循环触发。 - 尽量少使用触发器，假设触发器触发每次执行1s，insert table 500条数据，那么就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了。
- 触发器是针对每一行的，对增删改非常频繁的表上切记不要使用触发器，因为它会非常消耗资源。
创建触发器
格式：
-- 1、创建只有一个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;
-- 2、创建有多个执行语句的触发器
create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin
执行语句列表
end;
例子：
-- 创建触发器trigger_test1
CREATE TRIGGER trigger_test1 AFTER INSERT ON USER
-- 触发时机：当添加user表数据时触发
FOR EACH ROW
INSERT INTO user_logs
VALUES
( NULL, now(), '有新用户注册' );
-- 创建触发器trigger_test2
delimiter $$
CREATE TRIGGER trigger_test2 AFTER UPDATE ON USER
-- 触发时机：当修改user表数据时触发
FOR EACH ROW
-- 每一行
BEGIN
INSERT INTO user_logs
VALUES
( NULL, now(), '用户修改发生了修改' );
END $$
delimiter;
NEW和OLD
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的哪一行数据，来引用触发器中发生变化的记录内容。
使用方法：NEW.columnName（columnName为相应数据表某一列名）。
例子：
CREATE TRIGGER trigger_test3 AFTER INSERT ON USER FOR EACH ROW
INSERT INTO user_logs
VALUES
(
NULL,
now(),
concat( '有新用户添加，信息为:', NEW.uid, NEW.username, NEW.PASSWORD ));
查看和删除
查看：
show triggers;
删除：
drop trigger [if exists] trigger_name;
-- 例子：
drop trigger if exists trigger_test1;
索引
简述
介绍：
- 索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行。
- 不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。
- 如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
数据结构分类：
- Hash索引
- B+Tree索引
功能分类：
- 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引;
普通索引
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
创建
-- 方式1-创建表的时候直接指定
create table student(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
index index_name(name) -- 给name列创建索引
);
-- 方式2-直接创建
-- create index indexname on tablename(columnname);
create index index_gender on student(gender);
-- 方式3-修改表结构(添加索引)
-- alter table tablename add index indexname(columnname)
alter table student add index index_age(age);
查看
-- 1、查看数据库所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名’;
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';
-- 2、查看表中所有索引
-- select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' and a.table_name like '%表名%’;
select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';
-- 3、查看表中所有索引
-- show index from table_name;
show index from student;
删除
格式：
drop index 索引名 on 表名
-- 或
alter table 表名 drop index 索引名
例子：
drop index index_gender on student
-- 或
alter table student drop index index_name
唯一索引
唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建
-- 方式1-创建表的时候直接指定
create table student2(
sid int primary key,
card_id varchar(20),
name varchar(20),
gender varchar(20),
age int,
birth date,
phone_num varchar(20),
score double,
unique index_card_id(card_id) -- 给card_id列创建索引
);
-- 方式2-直接创建
-- create unique index 索引名 on 表名(列名)
create unique index index_card_id on student2(card_id);
-- 方式3-修改表结构(添加索引)
-- alter table 表名 add unique [索引名] (列名)
alter table student2 add unique index_phone_num(phone_num)
删除
drop index index_card_id on student2
-- 或
alter table student2 drop index index_phone_num
主键索引
每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引。
主键是具有唯一性并且不允许为NULL，所以他是一种特殊的唯一索引。
组合索引
介绍：
- 组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段。
- 例如同时使用身份证和手机号建立索引，同样的可以建立为普通索引或者是唯一索引。
- 复合索引的使用复合最左原则。
格式：
create index indexname on table_name(column1(length),column2(length));
例子：
create index index_phone_name on student(phone_num, name);
最左原则：
-- 对于组合索引：
create index index_phone_name on student(phone_num, name);
select * from student where name = '张三';
select * from student where phone_num = '15100046637';
select * from student where phone_num = '15100046637' and name = '张三';
select * from student where name = '张三' and phone_num = '15100046637';
/*
三条sql只有 2、3、4能使用的到索引index_phone_name，因为条件里面必须包含索引前面的字段才能够进行匹配。
而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为MySQL本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在MySQL眼中是等价的。
*/
全文索引
简述
介绍：
-
全文索引的关键字是
fulltext -
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配。
-
用
like + %就可以实现模糊匹配了，为什么还要全文索引？like + %在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比like + %快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。 -
MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。这两个的默认值可以使用以下命令查看：
show variables like '%ft%';
要求：
- MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
- MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
- 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引；
- 在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用create index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多；
- 测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
创建
-- 1. 创建表的时候添加全文索引
create table t_article (
id int primary key auto_increment ,
title varchar(255) ,
content varchar(1000) ,
writing_date date,
fulltext (content) -- 创建全文检索
);
-- 2. 修改表结构添加全文索引
alter table t_article add fulltext index_content(content)
-- 3. 直接添加全文索引
create fulltext index index_content on t_article(content);
使用
使用全文索引和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字。
格式：
match (col1,col2,...) against(expr [search_modifier])
例子：
select * from t_article where match(content) against('yo'); -- 没有结果 单词数需要大于等于3
select * from t_article where match(content) against('you'); -- 有结果
空间索引
介绍：
- MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型
- 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是
GEOMETRY、POINT、LINESTRING、POLYGON。 - MySQL使用
SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。 - 创建空间索引的列，必须将其声明为
NOT NULL。 - 空间索引一般是用的比较少，了解即可。
数据类型：
例子：
create table shop_info (
id int primary key auto_increment comment 'id',
shop_name varchar(64) not null comment '门店名称',
geom_point geometry not null comment '经纬度',
spatial key geom_index(geom_point)
);
索引特点
优点
- 大大加快数据的查询速度
- 使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
- 创建唯一索引，能够保证数据库表中每一行数据的唯一性
- 在实现数据的参考完整性方面，可以加速表和表之间的连接
索引的缺点
- 创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
- 索引需要占据磁盘空间
- 对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度
原则
- 更新频繁的列不应设置索引
- 数据量小的表不要使用索引
- 重复数据多的字段不应设为索引（比如性别，只有男和女。一般来说：重复的数据超过百分之15就不该建索引）
- 首先应该考虑对
where和order by涉及的列上建立索引
索引原理
简述
- 一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
- 这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
- 索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
Hash算法
- 优点：通过字段的值计算的hash值，定位数据非常快。
- 缺点：不能进行范围查找，因为散列表中的值是无序的，无法进行大小的比较。
BTREE树
目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，Btree结构可以有效的解决之前的相关算法遇到的问题。
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
InnoDB引擎使用B+Tree，InnoDB的叶节点的data域存放的是数据，相比MyISAM效率要高一些，但是比较占硬盘内存大小。
存储引擎
定义
- 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。
- 不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
- 用户可以根据不同的需求为数据表选择不同的存储引擎。
- 可以使用
SHOW ENGINES命令可以查看MySQL的所有引擎和默认的存储引擎。
分类
- MyISAM：MySQL 5.5之前的默认数据库引擎，最为常用。拥有较高的插入，查询速度，但不支持事务。
- InnoDB：事务型速记的首选引擎，支持ACID事务，支持行级锁定，MySQL5.5成为默认数据库引擎。
- Memory：所有数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在MySQL重新启动是会丢失。
- Archive：非常适合存储大量的独立的，作为历史记录的数据。因为它们不经常被读取。Archive 拥有高效的插入速度，但其对查询的支持相对较差。
- Federated：将不同的 MySQL 服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用。
- CSV：逻辑上由逗号分割数据的存储引擎。它会在数据库子目录里为每个数据表创建一个 .csv 文件。这是一种普通文本文件，每个数据行占用一个文本行。CSV 存储引擎不支持索引。
- BlackHole：黑洞引擎，写入的任何数据都会消失，一般用于记录 binlog 做复制的中继
- ERFORMANCE_SCHEMA：该引擎主要用于收集数据库服务器性能参数。
- Mrg_Myisam Merge：是一组MyIsam的组合，也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。
操作
-- 查询当前数据库支持的存储引擎：
show engines;
-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;
-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
show create table student;
-- 创建新表时指定存储引擎：
create table(...) engine=MyISAM;
-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;
修改MySQL默认存储引擎方法:
- 关闭MySQL服务
- 找到MySQL安装目录下的my.ini文件：
- 找到
default-storage-engine=INNODB改为目标引擎。 - 启动MySQL服务
事务
定义
- 在MySQL中的事务（Transaction）是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 DDL、DML、DCL 操作，比如 insert, update, delete 语句，默认是自动提交的。
操作
MySQL的事务操作主要有以下三种：
-
开启事务：Start Transaction
- 任何一条DML语句(insert, update, delete)执行，标志事务的开启
- 命令：BEGIN 或 START TRANSACTION
-
提交事务：Commit Transaction
- 成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
- 命令：COMMIT
-
回滚事务：Rollback Transaction
- 失败的结束，将所有的DML语句操作历史记录全部清空
- 命令：ROLLBACK
之前的所有SQL操作其实也有事务，只是MySQL自动帮我们完成的，每执行一条SQL时MySQL就帮我们自动提交事务，因此如果想要手动控制事务，则必须关闭MySQL的事务自动提交。
在MySQL中直接用 SET 来改变 MySQL 的自动提交模式:
set autocommit=0 禁止自动提交
set autocommit=1 开启自动提交
例子：
-- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit;
set autocommit = 0;
-- 模拟账户转账
-- 开启事务
begin;
update account set money = money - 200 where name = 'zhangsan';
update account set money = money + 200 where name = 'lisi';
-- 提交事务
commit;
-- 如果转账中的任何一条出现问题，则回滚事务
rollback;
特性(ACID)
- Atomicity（原子性）：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
隔离级别
Isolate，顾名思义就是将事务与另一个事务隔离开，为什么要隔离呢？如果一个事务正在操作的数据被另一个事务修改或删除了，最后的执行结果可能无法达到预期。如果没有隔离性还会导致其他问题。
从上到下，事物之间的关联程度紧密递减，越紧密各个事务可能形成干扰，反之效率降低。
- 读未提交(Read uncommitted)：一个事务可以读取另一个未提交事务的数据，最低级别，任何情况都无法保证,会造成脏读。
- 读已提交(Read committed)：一个事务要等另一个事务提交后才能读取数据，可避免脏读的发生，会造成不可重复读。
- 可重复读(Repeatable read)：就是在开始读取数据（事务开启）时，不再允许修改操作，可避免脏读、不可重复读的发生，但是会造成幻读。
- 串行(Serializable)：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
MySQL的默认隔离级别是Repeatable read。
操作：
-- 查看隔离级别
show variables like '%isolation%';
-- 设置隔离级别
/*
set session transaction isolation level 级别字符串
级别字符串：read uncommitted、read committed、repeatable read、serializable
*/
-- 设置read uncommitted
set session transaction isolation level read uncommitted;
-- 设置read committed
set session transaction isolation level read committed;
-- 设置repeatable read
set session transaction isolation level repeatable read;
-- 设置serializable
set session transaction isolation level serializable;
锁机制
简述
- 锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。
- 在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
分类：
从对数据操作的粒度分：
- 表锁：操作时，会锁定整个表。
- 行锁：操作时，会锁定当前操作行。
从对数据操作的类型分：
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。
支持情况：
特点：
- 表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用。
- 行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。
MyISAM表锁
MyISAM 存储引擎只支持表锁。
MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
加读锁：lock table table_name read;
加写锁：lock table table_name write;
特点：
- 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
- 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
InnoDB行锁
特点：
- 偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
- InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X)；对于普通SELECT语句，InnoDB不会加任何锁。
模式：
- 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
- 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
操作：
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE
日志
简述
在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件。
分类：
- 错误日志
- 二进制日志
- 查询日志
- 慢查询日志
错误日志
错误日志是 MySQL 中最重要的日志之一，它记录了当 MySQL 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。
该日志是默认开启的，默认存放目录为 MySQL 的数据目录，默认的日志文件名为 hostname.err（hostname是主机名）。
查看日志位置指令：
show variables like 'log_error%';
二进制日志
二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的。
二进制日志，MySQl8.0默认已经开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式。
- Windows系统：my.ini
- Linux系统:my.cnf
# 配置开启binlog日志，日志的文件前缀为 mysqlbin
# 生成的文件名如 : mysqlbin.000001,mysqlbin.000002
log_bin=mysqlbin
# 配置二进制日志的格式
binlog_format=STATEMENT
日志格式：
- STATEMENT：该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。
- ROW：该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句：update tb_book set status=‘1’ , 如果是STATEMENT日志格式，在日志中会记录一行SQL文件；如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。
- MIXED：混合了STATEMENT 和 ROW两种格式。
操作：
-- 查看MySQL是否开启了binlog日志
show variables like 'log_bin';
-- 查看binlog日志的格式
show variables like 'binlog_format';
-- 查看所有日志
show binlog events;
-- 查看最新的日志
show master status;
-- 查询指定的binlog日志
show binlog events in 'binlog.000010';
select * from mydb1.emp2;
select count(*) from mydb1.emp2;
update mydb1.emp2 set salary = 8000;
-- 从指定的位置开始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from 156;
-- 从指定的位置开始,查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 156 limit 2;
--从指定的位置开始，带有偏移，查看指定的Binlog日志,限制查询的条数
show binlog events in 'binlog.000010' from 666 limit 1, 2;
-- 清空所有的 binlog 日志文件
reset master
查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。
默认情况下，查询日志是未开启的。如果需要开启查询日志，可以设置以下配置：
# 该选项用来开启查询日志，可选值 ： 0 或者 1 ； 0 代表关闭，1 代表开启
general_log=1
# 设置日志的文件名，如果没有指定，默认的文件名为 host_name.log
general_log_file=file_name
操作：
-- 查看MySQL是否开启了查询日志
show variables like 'general_log';
-- 开启查询日志
set global general_log=1;
select * from mydb1.emp2;
select * from mydb6_view.emp;
select count(*) from mydb1.emp2;
select count(*) from mydb6_view.emp;
update mydb1.emp2 set salary = 9000;
慢查询日志
慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0，精度可以到微秒。
# 该参数用来控制慢查询日志是否开启，可取值： 1 和 0 ，1 代表开启，0 代表关闭
slow_query_log=1
# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
# 该选项用来配置查询的时间限制，超过这个时间将认为值慢查询，将需要进行日志记录，默认10s
long_query_time=10
# 该参数用来控制慢查询日志是否开启，可取值： 1 和 0 ，1 代表开启，0 代表关闭
slow_query_log=1
# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
# 该选项用来配置查询的时间限制，超过这个时间将认为值慢查询，将需要进行日志记录，默认10s
long_query_time=10
优化
简述
在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。
MySQL的优化方式有很多，大致我们可以从以下几点来优化MySQL:
- 从设计上优化
- 从查询上优化
- 从索引上优化
- 从存储上优化
查看SQL执行频率
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。
下面的命令显示了当前 session 中所有统计参数的值：
show session status like 'Com_______'; -- 查看当前会话统计结果
show global status like 'Com_______'; -- 查看自数据库上次启动至今统计结果
show status like 'Innodb_rows_%'; -- 查看针对Innodb引擎的统计结果
定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的 SQL 语句：
- 慢查询日志：通过慢查询日志定位那些执行效率较低的 SQL 语句。
- show processlist：该命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
慢日志查询
-- 查看慢日志配置信息
show variables like '%slow_query_log%';
-- 开启慢日志查询
set global slow_query_log=1;
-- 查看慢日志记录SQL的最低阈值时间
show variables like 'long_query_time%';
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time=4;
show processlist
show processlist;
| 列 | 定义 |
|---|---|
| id | 用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看 |
| user | 显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句 |
| host | 显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户 |
| db | 显示这个进程目前连接的是哪个数据库 |
| command | 显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等 |
| time | 显示这个状态持续的时间，单位是秒 |
| state | 显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成 |
| info | 显示这个sql语句，是判断问题语句的一个重要依据 |
explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
例子：
explain select * from user where uid = 1;
id：
- id 相同表示加载表的顺序是从上到下。
- id 不同id值越大，优先级越高，越先被执行。
- id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
select_type：
type：
结果值从最好到最坏以此是：system > const > eq_ref > ref > range > index > ALL
extra：
show profile分析SQL
MySQL从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
通过 have_profiling 参数，能够看到当前MySQL是否支持profile：
select @@have_profiling;
set profiling=1; -- 开启profiling 开关；
通过profile，我们能够更清楚地了解SQL执行的过程。首先，我们可以执行一系列的操作。
show databases;
use mydb13_optimize;
show tables;
select * from user where id < 2;
select count(*) from user;
执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：
show profiles;
通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：
show profile for query 8;
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间：
show profile cpu for query 133;
trace分析优化器执行计划
MySQL5.6提供了对SQL的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A计划，而不是选择B计划。
打开trace，设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
执行SQL语句：
select * from user where uid < 2;
最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：
select * from information_schema.optimizer_trace\G;
使用索引优化
索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。
-- 创建组合索引
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
以下为避免索引失效应用。
全值匹配
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
和字段匹配成功即可，和字段顺序无关。
最左前缀法则
-- 最左前缀法则
-- 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
explain select * from tb_seller where name='小米科技'; -- 403
explain select * from tb_seller where name='小米科技' and status='1'; -- 410
explain select * from tb_seller where status='1' and name='小米科技'; -- 410
-- 违法最左前缀法则，索引失效。
explain select * from tb_seller where status='1'; -- nulll
-- 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效。
explain select * from tb_seller where name='小米科技' and address='北京市'; -- 403
-- 403，410...为索引长度
其他匹配原则
-- 1、范围查询右边的列，不能使用索引 。
-- 根据前面的两个字段name，status 查询是走索引的，但是最后一个条件address 没有用到索引。
explain select * from tb_seller where name='小米科技' and status >'1' and address='北京市';
-- 2、不要在索引列上进行运算操作，索引将失效。
explain select * from tb_seller where substring(name,3,2)='科技'
-- 3、字符串不加单引号，造成索引失效。
explain select * from tb_seller where name='小米科技' and status = 1 ;
-- 4、尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技' and address='北京市'; -- 效率低
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技' and address='北京市'; -- 效率高
explain select name,status,address from tb_seller where name='小米科技' and address='北京市'; -- 效率高
-- 如果查询列，超出索引列，也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技' and address='北京市'; -- 效率低
-- 尽量使用覆盖索引，避免select *
-- 需要从原表及磁盘上读取数据
explain select * from tb_seller where name='小米科技' and address='北京市'; -- 效率低
-- 从索引树中就可以查询到所有数据
explain select name from tb_seller where name='小米科技' and address='北京市'; -- 效率高
explain select name,status,address from tb_seller where name='小米科技' and address='北京市'; -- 效率高
-- 如果查询列，超出索引列，也会降低性能。
explain select name,status,address,password from tb_seller where name='小米科技' and address='北京市'; -- 效率低
-- 用or分割开的条件， 那么涉及的索引都不会被用到。
explain select * from tb_seller where name='程序员' or address = '西安市';
explain select * from tb_seller where name='程序员' or status = '1';
-- 以%开头的Like模糊查询，索引失效。
explain select * from tb_seller where name like '科技%'; -- 用索引
explain select * from tb_seller where name like '%科技'; -- 不用索引
explain select * from tb_seller where name like '%科技%';-- 不用索引
-- 弥补不足,不用*，使用索引列
explain select name from tb_seller where name like '%科技%';
-- 1、如果MySQL评估使用索引比全表更慢，则不使用索引。
-- 这种情况是由数据本身的特点来决定的
create index index_address on tb_seller(address);
-- 当表中只有一个西安市，其余都是北京市。
explain select * from tb_seller where address = '北京市'; -- 没有使用索引
explain select * from tb_seller where address = '西安市'; -- 没有使用索引
-- 2、is NULL，is NOT NULL 有时有效，有时索引失效。
create index index_address on tb_seller(nickname);
explain select * from tb_seller where nickname is NULL; -- 索引有效
explain select * from tb_seller where nickname is not NULL; -- 无效
-- 3、in使用索引，not in索引失效。
-- 普通索引如题，主键索引都使用索引
-- 4、单列索引和复合索引，尽量使用复合索引。
-- 例如在以下索引中
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
/*
等价于：
name
name + status
name + status + address
*/
-- 如果一张表中有多个单列索引，即使where都使用了这些索引，则只有一个最优索引生效。
SQL优化
大批量插入数据
当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：
主键顺序插入
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
-- 1、首先，检查一个全局系统变量 'local_infile' 的状态，如果得到如下显示 Value=OFF，则说明这是不可用的
show global variables like 'local_infile';
-- 2、修改local_infile值为on，开启local_infile
set global local_infile=1;
-- 3、加载数据
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
-- 关闭唯一性校验
SET UNIQUE_CHECKS=0;
truncate table tb_user;
load data local infile 'D:\\sql_data\\sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n';
SET UNIQUE_CHECKS=1;
优化insert语句
当进行数据的insert操作的时候，可以考虑采用以下几种优化方案：
-- 1、如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句。
-- 这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。
-- 原始方式为：
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
-- 优化后的方案为：
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');
-- 2、在事务中进行数据插入。
begin;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;
-- 3、数据有序插入
insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');
-- 优化后
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
insert into tb_test values(4,'Tim');
insert into tb_test values(5,'Rose');
优化order by语句
两种排序方式：
- 第一种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
- 第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。
-- order by后边的多个排序字段要求尽量排序方式相同
explain select id,age from emp order by age asc, salary desc; -- Using index; Using filesort
explain select id,age from emp order by age desc, salary desc; -- Backward index scan; Using index
-- order by后边的多个排序字段顺序尽量和组合索引字段顺序一致
explain select id,age from emp order by salary,age; -- Using index; Using filesort
Filesort 的优化：
通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort的排序操作。
对于Filesort，MySQL 有两种排序算法：
- 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buffer 中排序，如果sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
- 一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。
MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法，否则使用第一种。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。
优化group by
GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。
当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。
所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null禁止排序。如下：
drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;
explain select age,count(*) from emp group by age order by null;
create index idx_emp_age_salary on emp(age,salary)；
优化子查询
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。
连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
多表查询比子查询效率高。
explain select * from user where uid in (select uid from user_role);
explain select * from user u, user_role ur where u.uid = ur.uid;
优化limit查询
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是limit 900000,10，此时需要MySQL排序前900010记录，仅仅返回900000 - 900010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路：
- 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
- 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。
-- 方案1
explain select * from tb_user limit 900000,10; -- 0.684
explain select * from tb_user a, (select id from tb_user order by id limit 900000,10) b where a.id = b.id; -- 0.486
-- 方案2
explain select * from tb_user where id > 900000 limit 10;
参考链接：