触发器是与表有关的数据库对象，在 insert/update/delete 之前或者之后，触发并执行触发器中定义的SQL语句集合。

触发器适合在数据库端确保数据的完整性，日志记录，数据校验 现在的触发器还只支持行级触发，不支持语句触发。

创建查看删除：

-- 创建 --
CREATE TRIGGER [trigger_name]
BEFORE/AFTER INSERT/UPDATE/DELETE
ON [table_name] FOR EACH ROW -- 指定表并加入触发器
BEGIN
    ...
END;
-- 查看 --
SHOW TRIGGERS;
-- 删除 不指定数据库.名称，默认删除当前数据库下的 --
DROP TRIGGER [database_name.]trigger_name

示例：日志表

为hso表建立日志表hso_logs，并通过触发器记录该表的数据变更日志。

-- 建表语句 --
CREATE TABLE hso_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的id',
    operate_params varchar(500) comment '操作参数',
    primary key(id)
)engine=innodb default charset=utf8;

-- insert触发器 --
create trigger hso_insert_trigger
    after insert on hso for each row
begin
    insert into hso_logs(id,
                         operation,
                         operate_time,
                         operate_id,
                         operate_params) 
                       values(null,'insert',now(),new.id,concat(
    '插入的数据内容为id=',new.id,',var1=',new.var1
    ))     
end;


-- update触发器 --
create trigger hso_insert_trigger
    after update on hso for each row
begin
    insert into hso_logs(id,
                         operation,
                         operate_time,
                         operate_id,
                         operate_params) 
                       values(null,'update',now(),new.id,concat(
                           '更新之前的数据：id=',old.id,',var1=',old.var1,
    ' | 更新的数据内容为id=',new.id,',var1=',new.var1
    ))
end;

-- delete触发器 --
create trigger hso_insert_trigger
    after delete on hso for each row
begin
    insert into hso_logs(id,
                         operation,
                         operate_time,
                         operate_id,
                         operate_params) 
                       values(null,'delete',now(),old.id,concat(
    '删除的数据内容为id=',new.id,',var1=',new.var1
    ))
    -- 删除前的ID --
end;

在一个业务逻辑中，可能需要查询多次数据库（多次网络请求） 可以将多条SQL语句封装在一个集合当中，调用业务对应的SQL集合。

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化开发人员的重复工作，减少网络请求的数量，提升安全（更细粒度的权限控制），从而提高开发和数据处理的效率。

存储过程思想上，就是数据库SQL语言层面的代码封装与重用。

创建存储过程：

CREATE PROCEDURE [name]([参数列表])
begin
    --SQL语句;
end;

在以终端方式创建存储过程

-- 先将结束符修改成其他符号，以避免误解析SQL语句
DELIMITER //
CREATE PROCEDURE [name]([参数列表])
BEGIN
    --SQL语句;
END //
-- 定义结束，将结束符恢复成分号 ;
DELIMITER ;

调用存储过程：

CALL [name];

查看存储过程：

-- 查询指定数据库的存储过程以及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = '[database]'
SHOW CREATE PROCEDURE [name]; --查询某个存储过程的定义sql

删除存储过程：

DROP PROCEDURE [if exists][name]; --删除存储过程

变量：

系统变量：

非用户定义，属于服务器层面。分为global（全局变量）和session（会话/局部变量）

查看设置系统变量：

SHOW [SESSION|GLOBAL] VARIABLES; -- 查询所有系统变量
SHOW [SESSION|GLOBAL] VARIABLES LIKE '...'; -- 查看指定系统变量
SELECT @@[SESSION|GLOBAL] [系统变量名]; -- @@代表的是系统变量
-- 设置系统变量 --
SET [SESSION|GLOBAL] [系统变量名] = ...;
set @@[SESSION.|GLOBAL.][系统变量名] = ...;

在重启后，全局变量会恢复成my.cnf中的配置。

用户自定义变量：

格式：@变量名 作用域为当前session

赋值和查看：

set @[var_name] = ...;
set @[var_name] := ...; -- 为了区分，建议使用此方式
select @[var_name] := ...;
SELECT ... into @[var_name] from [table_name];
-- 查看 --
select @[var_name] := 表达式;

局部变量：

用DECLARE声明，局部变量的范围是在其声明的BEGIN … END块。

声明赋值

-- INT/BIGINT/CHAR/VARCHAR/DATE/TIME等 default是默认值
DECLARE [变量名] [变量类型] [default ...]
-- 赋值
set [变量名] = ...;
set [变量名] := ...;
select ... into [变量名] from [table_name];

语法

参数：

参数分为三种类型

IN : 作为输入，需要调用时传入值（默认） OUT：作为输出，参数可以作为返回值 INOUT：既可以作为输入参数，也可以作为输出参数

create procedure [存储过程名称](IN/OUT/INOUT [变量名] [变量类型])

条件控制语句：

if判断：

语法：

IF 条件1 THEN ...
ELSEIF 条件2 THEN ...
...
ELSE ...
END IF;

例：

create procedure judging(in score int, out result varchar(4))
begin
    if score >= 85 then
        set result := 'A';
    elseif score>=60 then
        set result := 'B';
    else
        set result := 'C';
    END IF;
end;

使用：

call judging(59 , @result);

case：

语法

-- value == vx时 执行对应代码
CASE [value]
    WHEN [v1] THEN ...
    WHEN [v2] THEN ...
    ELSE ...
END CASE;
-- 表达式成立时
CASE
    WHEN [表达式1] THEN ...
    WHEN [表达式2] THEN ...
    ELSE ...
END CASE;

循环语句：

while：

在一开始就进行判断。

WHILE [表达式] DO
    ...
END WHILE;

repeat：

repeat在执行完逻辑，判断满足条件时，才退出循环（至少执行一次）

REPEAT
    ...
    UNTIL [表达式]
END REPEAT;

loop：

实现简单的循环，退出循环需要自行加入 LEAVE 或者 ITERATE 需要在首位指定loop的label。

LEAVE ：退出循环（break） ITERATE ：开始下一次循环（continue）

这两条语句需要加入循环的label来控制

语法：

[begin_label:] LOOP
    ...
    LEAVE [label]
    ITERATE [label]
END LOOP [end_label];

游标：

游标（CURSOR) 是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch和close

声明游标要在声明普通变量之后。否则会报错

语法：

-- 声明游标 --
DECLARE [cursor_name] CURSOR FOR [查询语句];
-- 打开游标 --
OPEN [cursor_name];
-- 获取游标记录 --
FETCH [cursor_name] INTO [变量1],[变量2];
-- 关闭游标 --
CLOSE [cursor_name];

条件处理程序：

条件处理程序（Handler）可以用来定义并自动执行在流程控制结构执行过程中遇到定义的问题时的相应处理步骤。

在出现对应SQL状态时，会自动执行。

语法：

DECLARE [handler_action] HANDLER FOR [condition_value] [sqlstate_value];

-- handler_action --
    CONTINUE: 继续执行当前程序
    EXIT: 终止执行当前程序
-- condition_value --
    SQLSTATE ：状态码（sqlstate_value）
    SQLWARNING：所有以01开头的sqlstate代码简写
    NOT FOUND：所有以02开头的sqlstate代码简写
    SQLEXCEPTION：其他sqlstate代码简写

用来处理死循环：

declare exit handler for sqlstate '02000' close u_cursor;
while true do
    fetch u_cursor into uname,ujob;
end while;

可以在官方文档查找所有 sql 状态码的具体描述。

存储函数：

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的

语法：

CREATE FUNCTION [存储函数名称]([参数列表])
RETURNS [数据类型] [characteristic]
BEGIN
    ...SQL
    RETURN ...;
END;
-- 必须指定的函数特性 --
characteristic:
    DETERMINISTIC 相同的输入参数总是产生相同的结果
    NO SQL 不包含SQL语句
    READS SQL DATA 包含数据数据的语句，但不包含写入数据的语句

MySQL 5.0版本之后引入视图。

视图本身是一个虚拟表，不存放任何数据。使用SQL语句访问视图时，它返回的数据都是从其他表里生成的。视图和表是在同一个命名空间，MySQL对于二者大多是同样对待的。

不过也有不同，例如：不能对视图创建触发器，也不能使用DROP TABLE命令删除视图。

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。 那些被经常使用的查询可以被定义为视图，从而使用户不必为之后的操作指定全部的条件。同时通过视图，用户只能查询和修改指定的数据。 且视图可帮助用户屏蔽真实表结构变化带来的影响。

创建视图：

CREATE [or replace] view [view_name] as [table];

视图查询：

视图是一张虚拟存在的表。大多数表的操作也可以对视图执行。

视图修改：

alter view [view_name] as [table];

视图删除：

然而删除表和删除视图的SQL语句是不相同的

drop view [view_name];

视图检查选项

视图和表有绑定关系，向视图中添加数据，同时也会向表中添加数据。 但是如果视图表有条件，添加的数据如果不满足条件，在视图中则不会显示。

当使用检查选项字句创建视图时，MySQL会通过视图检查正在更改的每个行。以使其符合视图的定义。MySQL同时允许基于另一个视图创建视图，为了确定检查的范围mysql提供了 CASCADED(默认) 和 LOCAL 两个选项。

cascaded

在视图创建提供了语法 with cascaded check option 则对该视图进行写操作时，会对当前视图和依赖的所有视图进行检查，只有数据合法时，才能添加成功。

local

MySQL8.0 加入。会检查当前视图和依赖的所有视图。只有视图存在检查选项时，才对视图中的条件进行检查，否则不检查。

视图更新：

条件：

视图可更新的条件，视图中的行和基础表的行之间必须存在一对一的关系。

包含以下任何一项时，视图是不可更新的 聚合函数或窗口函数 DISTINCT GROUP BY HAVING UNION

批量插入 手动提交事务 主键顺序插入性能高于乱序插入

大批量插入数据，可以使用MySQL数据库提供的load指令进行插入：

Load指令：

#在连接服务器时，加上参数 --local-infile 表示读取本地文件。
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入的开关。
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中。
load data local infile 'path/to/file' into table [table_name] fields terminated by '[separator]' lines terminated by '\n';

用Load指令将本地文件导入数据库，速度会比insert语句快。

主键优化：

数据组织方式 在InnoDB中，表数据是根据主键顺序存放，这种存储方式的表称为聚集索引，（索引组织表）。

InnoDB的逻辑存储结构为 Tablespace – Segment – Extent – Page – Row

页分裂

基于聚集索引的表插入新行，或主键更新导致需要移动行的时候，可能会出现“页分裂”问题。

当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂操作会导致表占用更多的磁盘空间。

所以当主键写入时乱序时，InnoDB不得不频繁的做页分裂操作，以便为新的行分配空间。页分裂会导致大量移动数据，一次插入最少需要修改三个页而不是一个页。

页合并

当删除一行记录时，实际上该记录只是被标记为删除，并且它的空间变得允许被其他记录声明使用。 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%)，InnoDB会开始寻找最靠近的页，查看是否可以合并优化空间利用。

主键设计原则

满足业务需求的情况下，尽量降低主键的长度。 插入数据时，尽量选择顺序插入，选择 AUTO_INCREMENT 自增主键 尽量不要使用UUID做主键或者是其他自然主键（身份证） 业务操作时，尽量避免对主键的修改。

order by 优化

filesort排序方式

在排序缓冲区sort buffer中完成排序操作。

index排序方式

通过有序的索引直接返回有序数据，操作效率高。

使用联合索引时，需要满足最左前缀法则。

在对带有联合索引的字段进行order by排序时，对字段1升序，字段2降序。也会使用filesort排序。

在创建索引时，需要指定排序方式。

// 该联合索引可以用 a asc/desc, b desc/asc 的order by排序
create index idx1 on t1(a asc , b desc);

order by优化原则

根据排序字段建立合适的索引，多字段排序时，遵循最左前缀法则。 实现覆盖索引 多字段排序时，要注意联合索引的创建规则（ASC / DESC)。

出现不可避免的filesort时，为防止缓冲区溢出，可以修改缓冲区大小。 使用 set sort_buffer_size = [num] 完成。

group by优化：

使用group by时，对分组字段创建联合索引。 不满足最左前缀法则，索引失效会导致SQL低性能问题。

limit优化：

在大数据量情况下，搜索位置越靠后性能越低。 前缀数据都会被丢弃。

// 访问的位置越靠后，查询速度越慢。
select * from t1 limit st,num;

可以使用覆盖索引+子查询的方式进行优化

select * from table1 t1 , (select id from table1 order by id limit 9000000,10) temp where temp.id = t1.id;

count优化:

count() 是一个聚合函数，对于返回的结果集逐行判断，最后返回累计值。

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回数据，效率很高。

InnoDB引擎执行count(*)时，需要累积计数，效率并不高。

count类型：

count(主键)

InnoDB引擎会遍历整张表，将每一行的主键id都返回给服务层，服务层接收到主键后，计数+1。

count(字段)

没有not null约束：InnoDB会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。 有not null约束：InnoDB引擎同样会会将数据返回给服务层，服务层按行进行累加。

count(常数)

InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，标记一个常数，按行进行累加。

count(*)

InnoDB并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

count优化原则

效率由高到低 count(*) ≈ count(常数) > count(主键) > count(字段)

尽量使用count(*)，数据库专门做了优化。

update优化：

如果字段没有索引，SQL语句就会上表锁 所以尽量对有索引的字段作为条件执行SQL语句，避免表锁导致性能下降。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。
如果索引失效，行锁会升级为表锁。

小结：

插入数据优化： 如果需要保存多条数据，批量插入，手动控制事务、主键顺序插入 大批量插入时，可以通过加载本地文件的方式构建表。

主键优化： 主键是乱序插入时，可能出现页分裂问题，一个操作可能会对几个页同时操作。 主键长度尽量短，顺序插入

order by优化： using index：通过索引返回数据、性能高 尽量使用覆盖索引，创建联合索引时，要考虑索引的字段顺序和排序方式。

group by优化： 多字段分组时，不满足最左前缀法则，会导致索引失效。 尽量覆盖索引。

limit优化： 访问的数据越靠后，查询速度越低 覆盖索引+子查询优化

count优化： 使用count(*)优化 或是自行计数，存储在专门的表中。

update优化： 写操作，覆盖索引，根据主键/索引字段来进行操作 避免表锁，导致数据库的并发性能下降。

在MySQL官网点击社区版下载，选择长期维护版本(LTS)和操作系统

点击 Install Using APT，复制下载链接

安装MySQL

在终端中执行：

// 下载MySQL APT 配置包
wget https://dev.mysql.com/get/mysql-apt-config_0.8.32-1_all.deb
// 安装MySQL APT 配置包
sudo dpkg -i mysql-apt-config_0.8.32-1_all.deb
需要确认MySQL的版本

// 更新apt包列表并安装MySQL
sudo apt update
sudo apt install mysql-server
需要设置root用户的密码

启动MySQL服务

systemctl start mysql //启动
systemctl restart mysql //重启
systemctl stop mysql //停止

创建一个新用户

create user 'root'@'本机ip' identified by '密码';

然后可以在IDEA中测试连接

采用MySQL的业务的体系结构主要为： 连接层、服务层、引擎层、存储层

存储引擎是基于表建立的。别称 表类型 MySQL 5.5 后默认的存储引擎为InnoDB（行级锁、事务）

查看mysql数据库支持的存储引擎

SHOW ENGINES;

Support DEFAULT 默认引擎 Transactions 是否支持事务

创建表时制定存储引擎

create table [name](
    ...
) ENGINE = [Engine_name]

InnoDB存储引擎

是由Oracle创建的下一代InnoDB引擎，拥有者是InnoDB而非MySQL。 Google、木下靖文、Percona、Facebook等也是重要贡献者、

特点：

DML操作（增删改）遵循 ACID 模型，支持事务 行级锁，提升高并发情景访问性能 支持外键 FOREIGN KEY约束（一般开发中不会使用）

文件：

[table_name].ibd

使用innoDB引擎的每张表都会对应一个二进制表空间文件，存储该表表结构、数据、索引。

表结构：frm、sdi
frm是早期的表结构

查看文件：

ibd2sdi xxx.ibd // 转化成sdi（json格式）

MyISAM

是MySQL早期的默认存储引擎

特点：

仅支持表锁，不支持行锁 不支持事务，不支持外键 访问速度快

文件：

MYI — 存储索引 MYD — 存储数据 sdi — 表结构存放文件（json格式数据）

Memory

数据存储在内存，作为临时表、缓存使用

特点：

内存存放 Hash索引（默认）

文件：

xxx.sdi

InnoDB 和 MyISAM

二者的区别

在引擎comments中也有提到

选择存储引擎

InnoDB：

对事务有需求，并发条件要求数据的一致性。 修改删除操作。

MyISAM：

以读操作和插入操作为主，对事务完整性要求不高，并发情况较少。

Memory：

保存在内存中，用于缓存和临时表。 对表的大小有限制，无法保障数据的安全性。

对MyISAM和Memory的需求都被非关系型数据库（Redis）替代了
Redis是基于内存的NoSQL数据库，适用于需要高性能读写的场景。
如果需要关系型数据库的特性和大量持久化的能力，
InnoDB反倒比MyISAM更稳定可靠，鲁棒性更高。