2020-12-20-mysql高级

mysql

学习笔记

发布日期: 2020-12-20

更新日期: 2021-01-12

文章字数: 6.1k

阅读时长: 25 分

阅读次数:

一、MySQL安装.

1、下载安装包.

下载地址：https://downloads.mysql.com/archives/community/ ，这里下载linux版本:mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

2、上传安装包到linux设备.

使用xftp上传即可，上传的目录为 /app

# 没有app目录，就创建
mkdir /app
# 进入/app
cd /app

3、安装.

# 0.查看系统是否已安装MariaDB
rpm -qa | grep mariadb
    mariadb-libs-5.5.68-1.el7.x86_64
    # 0.1 MariaDB存在，就先卸载（防止文件冲突）
    yum -y remove mariadb*
# 1.解压压缩包 到 /usr/local/mysql
mkdir /usr/local/mysql
tar -zxvf /app/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql

# 2.安装目录改名
mv /usr/local/mysql/mysql-5.7.31-linux-glibc2.12-x86_64 /usr/local/mysql/mysql-5.7.31

# 3.创建MySQL用户和用户组
groupadd mysql
useradd -g mysql mysql

# 4.修改MySQL目录的归属用户
cd /usr/local/mysql/mysql-5.7.31
chown -R mysql:mysql ./

# 5.在 /etc 下创建 my.conf 配置文件
vim /etc/my.conf
## 文件内容如下

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
socket=/var/lib/mysql/mysql.sock
[mysqld]
skip-name-resolve
#设置3306端⼝(根据实际设置)
port = 3306
socket=/var/lib/mysql/mysql.sock
# 设置mysql的安装⽬录(根据实际设置)
basedir=/usr/local/mysql/mysql-5.7.31
# 设置mysql数据库的数据的存放⽬录(根据实际设置)
datadir=/usr/local/mysql/mysql-5.7.31/data
# 允许最⼤连接数(根据实际设置)
max_connections=200
# 服务端使⽤的字符集默认为8⽐特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使⽤的默认存储引擎
default-storage-engine=INNODB
# 是否区分大小写，1表示存储时表名为小写，操作时不区分大小写；0表示区分大小写；不能动态设置，修改后，必须重启才能生效
lower_case_table_names=1
max_allowed_packet=16M

# 6.创建/var/lib/mysql,并修改权限
mkdir /var/lib/mysql
chmod 777 /var/lib/mysql

# 7.执行安装命令
cd /usr/local/mysql/mysql-5.7.31/bin
## 执行初始化 ./mysqld --verbose --help | more 查看帮助
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql-5.7.31 --datadir=/usr/local/mysql/mysql-5.7.31/data

## 初始化后，生成密码，如下图(根据实际生成为准)
8us+6-q5ok?X

# 8.MySQL服务开机自启
## 1.修改mysql.server内容
vim /usr/local/mysql/mysql-5.7.31/support-files/mysql.server
### 第46行
basedir=/usr/local/mysql/mysql-5.7.31/
datadir=/usr/local/mysql/mysql-5.7.31/data
## 2.
ln -s /usr/local/mysql/mysql-5.7.31/support-files/mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig --list mysqld

# 9.配置环境变量
vim /etc/profile
## 配置内容
MYSQL_HOME=/usr/local/mysql/mysql-5.7.31
export MYSQL_HOME
export PATH=$PATH:$MYSQL_HOME/bin
## 保存 source /etc/profile

# 10.启动服务
service mysqld start

4、登录.

# 登录
mysql -u root -p
Enter password:8us+6-q5ok?X（之前初始化生成的密码）

5、修改密码.

# 第一种
mysqladmin -uroot -p"8us+6-q5ok?X" password 新密码

# 第二种
mysql -uroot -p
Enter password:8us+6-q5ok?X
...
mysql> set password for root@localhost=password('新密码');

# 第三种
mysql -uroot -p
Enter password:8us+6-q5ok?X
...
mysql> use mysql;
mysql> update mysql.user set authentication_string=password('新密码') where user='用户名' and Host ='localhost';
mysql> flush privileges;

# 第四种
mysql -uroot -p
Enter password:8us+6-q5ok?X
...
mysql> alter user user() identified by "新密码";
mysql> flush privileges;

# 第五种 (旧版本mysql5.6)
mysql> set password = password('新密码');

6、设置远程主机登录.

mysql> use mysql;
mysql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;

二、索引.

1、索引概述.

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下面的示意图所示 :

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

2、索引优劣.

优势.

类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

劣势.

实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行 insert、update、delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。（更新表的同时，也会更新索引）

3、索引结构.

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL目前提供了以下4种索引：

BTREE索引 ：最常见的索引类型，大部分索引都支持 B 树索引。
HASH索引：只有Memory引擎支持，使用场景简单。
R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。
Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

MyISAM、INNODB、Memory三种存储引擎对各种索引类型的支持

索引	INNODB引擎	MyISAM引擎	Memory引擎
`BTREE`索引	支持	支持	支持
`HASH`索引	不支持	不支持	支持
`R-tree`索引	不支持	支持	不支持
`Full-text`	5.6版本后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

BTREE结构.

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

树中每个节点最多包含m个孩子。
除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点，则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

BTree演变过程.

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

1). 插入前4个字母 C N G A.

2). 插入H，n>4，中间元素G字母向上分裂到新的节点.

3). 插入E，K，Q不需要分裂.

4). 插入M，中间元素M字母向上分裂到父节点G.

5). 插入F，W，L，T不需要分裂.

6). 插入Z，中间元素T向上分裂到父节点中.

7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂.

8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂.

到此，该BTREE树就已经构建完成了， BTREE树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

B+TREE结构.

B+Tree为BTree的变种，B+Tree与BTree的区别为：

n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。
B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。
所有的非叶子节点都可以看作是key的索引部分。

由于B+Tree只有叶子节点保存key信息，查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

MySQL中的B+Tree.

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

MySQL中的 B+Tree 索引结构

4、索引分类.

单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引 ：索引列的值必须唯一，但允许有空值
复合索引 ：即一个索引包含多个列

5、索引语法.

索引在创建表的时候，可以同时创建，也可以随时增加新的索引。

0.准备环境:.

create database if not exists demo_01 default charset=utf8mb4;

use demo_01; 

CREATE TABLE `city` ( 
    `city_id` int(11) NOT NULL AUTO_INCREMENT,
    `city_name` varchar(50) NOT NULL,
    `country_id` int(11) NOT NULL,
    PRIMARY KEY (`city_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `city` (`city_id`, `city_name`, `country_id`) values
(1,'西安',1),
(2,'NewYork',2),
(3,'北京',1),
(4,'上海',1);

CREATE TABLE `country` ( 
    `country_id` int(11) NOT NULL AUTO_INCREMENT,
    `country_name` varchar(100) NOT NULL,
    PRIMARY KEY (`country_id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `country` (`country_id`, `country_name`) values
(1,'China'),
(2,'America'),
(3,'Japan'),
(4,'UK');

1.创建索引.

语法：.

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
[USING index_type]
ON tbl_name(index_col_name,...) 

index_col_name = tbl_column_name[(length)][ASC | DESC]

为city表的city_name字段创建索引

create index idx_city on city(city_name);

2.查看索引.

语法：.

show index from table_name;

查看city表上的索引

show index from city\G;

3.删除索引.

语法：.

DROP INDEX index_name ON tbl_name;

删除city表上的索引idx_city

drop index idx_city on city;

-- 再次查看
show index from city\G;

4.alter命令（创建）.

1). alter table tb_name add primary key(column_list); 
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL 

2). alter table tb_name add unique index_name(column_list); 
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次） 

3). alter table tb_name add index index_name(column_list); 
添加普通索引， 索引值可以出现多次。

4). alter table tb_name add fulltext index_name(column_list); 
该语句指定了索引为FULLTEXT， 用于全文索引

6、索引设计原则.

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

对查询频次较高，且数据量比较大的表建立索引。
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。(经常查询的条件字段)
使用唯一索引，区分度越高，使用索引的效率越高。
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。（索引建立不是越多越好，频繁修改的表，不建议建立索引）
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

-- 创建复合索引:
CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS); 
-- 就相当于
    -- 对name 创建索引 ; 
    -- 对name , email 创建了索引 ;
    -- 对name , email, status 创建了索引 ;

三、视图.

1、视图概述.

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

2、视图语法.

1.创建视图.

语法：.

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW view_name [(column_list)] AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

对city表和country表的联合查询结果建立视图

create or replace view view_city_country as 
select city.*,country.country_name from city, country where city.country_id = country.country_id;

-- 使用视图进行查询
select * from view_city_country;

可以使用视图进行更新操作（不推荐）

-- 使用视图进行更新操作
update view_city_country set city_name='武汉' where city_id=1;
-- 使用视图进行查询
select * from view_city_country;

2.修改视图.

语法：.

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW view_name [(column_list)] AS select_statement 
[WITH [CASCADED | LOCAL] CHECK OPTION]

选项 :
WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。 
    LOCAL ： 只要满足本视图的条件就可以更新。 
    CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

修改view_city_country中城市限定为中国城市

alter view view_city_country as 
select city.*,country.country_name from city, country where city.country_id = country.country_id and country.country_name='China';

-- 使用视图进行查询
select * from view_city_country;

3.查看视图.

语法：show.

SHOW TABLES;

-- 查询视图信息 下面 视图名使用 单引号包裹
SHOW TABLE STATUS [LIKE 'table_name'|'view_name'] \G;
-- 查看视图定义
SHOW CREATE VIEW view_name\G;

4.删除视图.

语法：.

DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]

删除视图view_city_country

DROP VIEW view_city_country;

-- 查看视图
SHOW TABLES;

四、存储过程和函数.

1、存储过程和函数概述.

存储过程和函数是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

函数 function：是一个有返回值的过程；

过程 procedure(/prəˈsiːdʒər/)：是一个没有返回值的函数；

2、存储过程操作.

1.创建.

CREATE PROCEDURE procedure_name ([proc_parameter[,...]]) 
BEGIN
-- SQL语句
END;

创建过程proc1

delimiter ;;

create procedure proc1()
begin
    select * from city;
end;;

delimiter ;

delimiter

该关键字用来声明SQL语句的分隔符 , 告诉 MySQL 解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

2.调用.

CALL procedure_name([参数]);

3.查看.

-- 查询db_name数据库中的所有的存储过程
SELECT name FROM mysql.proc WHERE db='db_name' AND type='PROCEDURE';

-- 查询存储过程的状态信息
SHOW PROCEDURE STATUS;

-- 查询某个存储过程的定义 
SHOW CREATE PROCEDURE proc_name  \G;

4.删除.

DROP PROCEDURE [IF EXISTS] proc_name ；

5.语法.

存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

5.1.变量.

定义：`declare`.

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中

DECLARE var_name[,...] TYPE [DEFAULT value];

赋值：`set`.

直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = const|expr;

也可以通过select ... into 方式进行赋值操作 :

delimiter ;;

create procedure proc()
begin
-- select ... into 赋值
select count(*) into num from city;
select num;
end;;

delimiter ;

5.2.if条件判断.

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list]
END IF;

5.3.传递参数.

create procedure proc_name([IN|OUT|INOUT] argu_name argu_type)
begin
-- 
end;



IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认 
OUT: 该参数作为输出，也就是该参数可以作为返回值 
INOUT: 既可以作为输入参数，也可以作为输出参数

输入：IN.

delimiter ;;

create procedure proc_in(in num int)
begin 
    select concat('打印数字：',num);
end;;

delimiter ;

-- 调用
call proc_in(10);

输出：OUT.

delimiter ;;

create procedure proc_out(in num int, out result varchar(20))
begin 
    set result = concat('result: ',num);
end;;

delimiter ;

-- 调用
call proc_out(10,@result);

select @result;

输入输出：INOUT.

delimiter ;;

create procedure proc_inout(inout num int)
begin 
    set num=num+1;
end;;

delimiter ;

-- 调用
set @num = 1; 
call proc_inout(@num);
select @num;

5.4.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 proc_case1(num int)
begin
    case num
        when 1 then select 'YES';
        when 0 then select 'NO';
        else select 'ERROR';
    end case;
end;;


delimiter ;

-- 调用
call proc_case1(1);

方式二测试：

delimiter ;;

create procedure proc_case2(num int)
begin
    declare n int default 5;
    case
        when num > n then select '大了';
        when num < n then select '小了';
        else select '√';
    end case;
end;;


delimiter ;

-- 调用
call proc_case2(1);

5.5.while循环.

WHILE search_condition DO
    statement_list
END WHILE;

delimiter ;;

create procedure proc_while()
begin 
    declare n int default 5;
    while n > 0 do
        select n;
        set n=n-1;
    end while;
end;;

delimiter ;

-- 调用
call proc_while;

5.6.repeat结构（循环）.

有条件的循环控制语句, 当满足条件的时候退出循环。while 是满足条件才执行，repeat 是满足条件就退出循环。

REPEAT 
    statement_list
    UNTIL search_condition -- 其后不能有 ;
END REPEAT;

delimiter ;;

create procedure proc_repeat()
begin 
    declare n int default 5;
    repeat    
        select n;
        set n=n-1;
        until n=3
    end repeat;
end;;

delimiter ;

-- 调用
call proc_repeat;

5.7.loop语句和leave语句.

LOOP.

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现

[begin_label:] LOOP
    statement_list
END LOOP [end_label];

如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

LEAVE.

用来从标注的流程构造中退出，通常和 BEGIN … END 或者循环一起使用。

下面是一个使用 LOOP 和 LEAVE 的简单例子 , 退出循环：

delimiter ;;

create procedure proc_loop_leave()
begin
    declare n int default 10;
    lbl: loop
        set n = n - 1;
        if n = 5 then leave lbl;
        else select n;
        end if;
    end loop lbl;
end;;

delimiter ;

-- 调用
call proc_loop_leave;

5.8.游标/光标.

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的DECLARE、OPEN、FETCH 和 CLOSE，其语法分别如下。

①声明光标：.

DECLARE cursor_name CURSOR FOR select_statement;

②OPEN光标：.

OPEN cursor_name;

③FETCH光标：.

使用一次，只能获得一条记录，若想获取全部数据，必须循环遍历

DECLARE EXIT HANDLER FOR NOT FOUND SET var_name...;:当没有内容可以获取时，该条语句会设置var_name的值

FETCH cursor_name INTO col_name[,col_name] ...;

④CLOSE光标：.

CLOSE cursor_name;

测试.

0.准备环境

create table emp(
    id int(11) not null auto_increment ,
    name varchar(50) not null comment '姓名',
    age int(11) comment '年龄', salary int(11) comment '薪水',
    primary key(`id`) 
)engine=innodb default charset=utf8 ;

insert into emp(id,name,age,salary) values
(null,'金毛狮王',55,3800),
(null,'白眉鹰 王',60,4000),
(null,'青翼蝠王',38,2800),
(null,'紫衫龙王',42,1800);

1.创建存储过程和调用

delimiter ;;

create procedure proc_cursor()
begin
    declare has_data int default 1;
    declare id,age,salary int;
    declare name varchar(20);

    declare resultset cursor for
    select * from emp;
    declare exit handler for not found set has_data=0;
    open resultset;
    repeat 
        fetch resultset into id, name, age, salary;
        select concat('id=',id,', name=',name,', age=',age,', salary=',salary);
        until has_data=0
    end repeat;
    close resultset;
end;;

delimiter ;

-- 调用
call proc_cursor;

3、存储函数操作.

存储函数与存储过程操作，相比多了返回值

创建.

CREATE FUNCTION func_name([param type ... ])
RETURNS type -- 注意是 RETURNS
BEGIN
-- 
END;

调用.

SELECT func_name([...]);

查看.

-- 查询db_name数据库中的所有的存储函数
SELECT name FROM mysql.proc WHERE db='db_name' AND type='FUNCTION';

-- 查询存储过程的状态信息
SHOW FUNCTION STATUS;

-- 查询某个存储过程的定义 
SHOW CREATE FUNCTION func_name  \G;

删除.

DROP FUNCTION func_name;

测试.

delimiter ;;

create function func()
returns int
begin
    declare num int;
    select count(*) into num from emp;
    return num;
end;;

delimiter ;

-- 查询
SELECT name FROM mysql.proc WHERE db='demo_01' AND type='FUNCTION';

-- 调用
select func();

-- 删除
drop function func;

五、触发器.

1、介绍.

触发器是与表有关的数据库对象，指在insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发(MySQL)。

行级触发器，改动多少行，就触发多少次

语句级触发器，不管该SQL影响多少条数据，你执行一条SQL只触发一次操作

触发器类型	NEW和OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

2、触发器操作.

1.创建.

CREATE TRIGGER trigger_name
BEFORE|AFTER  INSERT|UPDATE|DELETE
ON table_name
[FOR EACH ROW] -- 行级触发器
BEGIN
    trigger_stmt;
END;

2.查看.

show triggers;

3.删除.

-- 在mysql中schema和database概念一致
drop trigger [schema_name.]trigger_name;

示例.

通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

0.创建日志表.

create table emp_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型 insert|update|delete',
    operation_time datetime not null comment '操作时间',
    operation_id int(11) not null comment '操作表的ID',
    operation_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;
select * from emp_logs order by id desc limit 1;

1.创建insert触发器，完成插入时的日志记录：.

delimiter ;;

create trigger insert_trigger 
after insert on emp 
for each row
begin 
    insert into emp_logs(id, operation, operation_time, operation_id, operation_params) values(null,'insert',now(),new.id,concat('(name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end;;

delimiter ;

-- 测试
insert into emp(name, age, salary) values('liuyou', 18, 10000);

-- 查询
select * from emp_logs order by id desc limit 1;

2.创建update触发器，完成插入时的日志记录：.

delimiter ;;

create trigger update_trigger
after update on emp
for each row
begin
    insert into emp_logs(operation,operation_time,operation_id,operation_params) 
    values('update',now(),old.id,concat('更新前(name:',old.name,', age:',old.age,', salary:',old.salary,')','; 更新后(name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end;;

delimiter ;

-- 测试
update emp set name='liuminkai' where id = 5;

-- 查询
select * from emp_logs order by id desc limit 1;

3.创建delete触发器，完成插入时的日志记录：.

delimiter ;;

create trigger delete_trigger 
after delete on emp
for each row
begin
    insert into emp_logs(operation,operation_time,operation_id,operation_params) values('delete',now(),old.id,concat('(name:',old.name,', age:',old.age,',  salary:',old.salary,')'));
end;;

delimiter ;

-- 测试
delete from emp where id = 5;

-- 查询
select * from emp_logs order by id desc limit 1;

liuminkai

http://www.liuminkai.top/2020/12/20/2814.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 liuminkai !

mysql

Lombok注解简洁说明

本文来自：https://www.cnblogs.com/fxbin/p/11854281.html Lombok 注解简洁说明.Lombok注解： @Data：注解在类上，将类提供的所有属性都添加get、set方法，并添加equals

2020-12-28 转载

lombok annotation

设计模式

23种设计模式.

2020-12-19 学习笔记

java design pattern

一、MySQL安装.

1、下载安装包.

2、上传安装包到linux设备.

3、安装.

4、登录.

5、修改密码.

6、设置远程主机登录.

二、索引.

1、索引概述.

2、索引优劣.

优势.

劣势.

3、索引结构.

BTREE结构.

BTree演变过程.

1). 插入前4个字母 C N G A.

2). 插入H，n>4，中间元素G字母向上分裂到新的节点.

3). 插入E，K，Q不需要分裂.

4). 插入M，中间元素M字母向上分裂到父节点G.

5). 插入F，W，L，T不需要分裂.

6). 插入Z，中间元素T向上分裂到父节点中.

7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂.

8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂.

B+TREE结构.

MySQL中的B+Tree.

4、索引分类.

5、索引语法.

0.准备环境:.

1.创建索引.

语法：.

2.查看索引.

语法：.

3.删除索引.

语法：.

4.alter命令（创建）.

6、索引设计原则.

三、视图.

1、视图概述.

2、视图语法.

1.创建视图.

语法：.

2.修改视图.

语法：.

3.查看视图.

语法：show.

4.删除视图.

语法：.

四、存储过程和函数.

1、存储过程和函数概述.

2、存储过程操作.

1.创建.

2.调用.

3.查看.

4.删除.

5.语法.

5.1.变量.

定义：declare.

赋值：set.

5.2.if条件判断.

5.3.传递参数.

输入：IN.

输出：OUT.

输入输出：INOUT.

5.4.case结构.

5.5.while循环.

5.6.repeat结构（循环）.

5.7.loop语句和leave语句.

LOOP.

LEAVE.

5.8.游标/光标.

①声明光标：.

②OPEN光标：.

③FETCH光标：.

④CLOSE光标：.

测试.

3、存储函数操作.

创建.

调用.

查看.

定义：`declare`.

赋值：`set`.