MySQL08-高级特性
分区表
什么是分区表
对用来来说，分区表是一个独立的逻辑表，但底层是由多个物理子表组成。
分区是将一个表的数据按照某种方式，比如按照时间上的月份，分成多个较小的，更容易管理的部分。
使用分区表的限制
一个表最多1024个分区
分区表中不能使用外键
分区的字段必须包含在该表的主键索引或唯一索引中
不能只对表中的一部分数据进行分区
作用、好处
（1）存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据
（2）优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。
（3）分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。
（4）避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等
分区类型
https://cloud.tencent.com/developer/article/1005835
range
list
hash
key
RANGE分区
按照字段的范围分区，要求区间连续且不重复。分区字段必须是整数或转换为整数，例如可以将一个表通过年份划分成若干个分区
例子：
partition by range(YEAR(date))(
partition p1 values less than (2020),
partition p2 values less than (2021),
);
LIST分区
这种模式允许系统通过预定义的列表的值(test_id)来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。
例子：
partition by list(test_id) (
partition p1 values in (2,4,7),
partition p2 values in (3,5,6),
);
HASH分区
这种模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。
在CREATE TABLE 语句上添加一个PARTITION BY HASH (expr)子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。
此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num是一个非负的整数，它表示表将要被分割成分区的数量。
数据均匀分布
例子：
partition by hash(test_id)
partitions 4
KEY分区
不同的是，如果表有主键或者唯一键的时候无需指定key的列名，key分区自动根据键值进行分区。
分区表查询优化
where 的条件不能是函数，只能是表本身的列才能使用分区查询
游标
【简介】
游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
游标充当指针的作用。
尽管游标能遍历结果中的所有行，但他一次只指向一行。
游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。
【用法】
一、声明一个游标: declare 游标名称 CURSOR for table;(这里的table可以是你查询出来的任意集合)
二、打开定义的游标:open 游标名称;
三、获得下一行数据:FETCH 游标名称 into testrangeid,versionid;
四、需要执行的语句(增删改查):这里视具体情况而定
五、释放游标:CLOSE 游标名称;
注:mysql存储过程每一句后面必须用;结尾，使用的临时字段需要在定义游标之前进行声明。
视图
视图概述
什么是视图
视图是一个逻辑表，是用户基于需求定义的查询结果集
用途
通过视图，可以展现基表的部分数据
优点
(1)简单
用户不需关心视图中的数据如何查询获取，视图中的数据已经是过滤好的符合条件的结果集
(2)安全
使用视图的用户只能访问到视图定义的结果集。实现对基表列的访问控制
(3)数据独立
一旦视图结构确定，可以屏蔽基表结构对用户的影响。源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响
视图和表的区别以及联系
（1）表有物理记录并占用物理存储空间而视图没有
（2）表可以及时对它进行修改，但视图只能用创建的方式来进行修改。
（3）视图的建立和删除只影响视图本身，不影响对应的基本表。
（4）一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
（5）视图是依懒与基本表的存在而存在。
如果一张表没有.frm(表结构文件),它可能就是视图表
另一种可能就是这张表的存储引擎是MEMORY，其表的内容存在内存中，表结构存在硬盘中
视图使用限制
(1)不能在视图上创建索引
(2)在视图的from子句中不能使用子查询
(3)以下情形中的视图是不可更新的
包含以下关键字的sql语句：聚合函数(SUM、MIN、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL
(4)视图的建立和删除不影响基本表；
(5)对视图内容的更新(添加、修改和删除)直接影响基本表；
(6)当视图来自多个基本表时，不允许添加和删除数据。
视图基本使用
//创建视图
//(num, name)表示视图表中的字段名列表，必须与后边基表定义的字段列表相匹配
create view test_view1(num, name) as select * from test where id = 1;
//查看视图
show table status; # 查看所有库中所有表的信息
show tables status where comment='view'\G; # 查看视图表的信息
show create view test_view; # 查看创建视图的命令(可以看到基表)
//查询记录
Select 字段名列表 from 视图名 where 条件；
//插入记录
Insert into 视图名(字段名列表) values(字段值列表);
//更新记录
Updatae 视图名 set 字段名=值 where 条件；
//删除记录
Delete from 视图名 where 条件；
！！！对视图操作即是对基本表操作，反之亦然；
//删除视图
drop view 视图名；
创建视图的完全格式
create
[or replace]
[algorithm = {undefined|merge|temptable}]
[definer={user|current_user}]
[sql security {definer|invoker}]
view view_name[(column_list)]
as select_statement
[with [cassaded|local] check option]
视图中的字段名不可以重复，所以要定义别名
(1)给字段定义别名的具体操作见步骤，有两种方法(在查询时定义别名，在创建视图时定义别名)
(2)给表名定义别名的格式
create view 视图名
as
select 表别名.源字段名 as 字段别名 #在这应用表的别名
from 源表名 表别名 left join 源表名 表别名 #表名的别名在这定义
on 条件；
重要选项说明
(1)or replace:创建时，若视图已存在，会替换已有的视图
语法格式：create or replace view 视图名as select 查询； //达到修改已有视图的目的
(2)指定处理视图的算法[merge|temptable|undefined ] 默认MERGE
create algorithm=temptable view v8 as select name from user;
当使用merge时:mysql将对视图的操作根据视图的定义进行展开
当使用temptable时：执行视图操作时将结果存储在临时表(内存)中，然后再返回结果
(3)with check option对视图作操作时受限制,LOCAL和CASCADED关键字决定检查的范围
local 只要满足视图本身限制即可
cascaded(默认)要满足视图本身的，同时要满足基表的限制
存储过程
存储过程概述
存储过程介绍
(1)存储过程：相当于是mysql语句组成的脚本
(2)指的是数据库中保存的一系列sql命令的集合
优点
(1)相比客户端的操作，执行速度快，因为无需网络通信、解析和优化的开销
(2)可减轻网络负担
(3)可以防止对表的直接访问
(4)避免重复编写sql操作
存储过程的使用
//语法
create procedure 存储过程名称(参数 变量 数据类型,参数 变量 数据类型）
//创建存储过程
delimiter // # 将命令行的结束符换为 //
create procedure showall() # 定义存储过程名为showall()
begin
select * from testtable; # 存储过程中的代码,注意；
end //
delimiter ; # 将命令提示符还原
//调用存储过程
call 存储过程名(); # 创建存储过程时，如果括号中有值，调用时必须指定值
call showall();
//删除存储过程名
drop procedure showall;
//查看存储过程
show procedure status\G; # 查看所有存储过程，内容很多
show create procedure showall; # 查看名字为showall的存储过程
// 从mysql库的proc表中查看名字为showall的指定字段
select db,name,type from mysql.proc where name = 'showall';
存储过程中的参数
IN 参数
IN 参数用于将数据从调用方传递到存储过程。存储过程只能读取 IN 参数，不能修改它的值。IN 参数是默认参数类型。
DELIMITER //
CREATE PROCEDURE example_in(IN param1 INT)
BEGIN
SELECT param1; -- 读取 IN 参数的值
END //
DELIMITER ;
调用：
CALL example_in(100); -- 传递值 100 给 IN 参数
OUT 参数
OUT 参数用于将数据从存储过程传递回调用方。存储过程可以修改 OUT 参数的值，调用方可以读取这个修改后的值。
DELIMITER //
CREATE PROCEDURE example_out(OUT param1 INT)
BEGIN
SET param1 = 200; -- 修改 OUT 参数的值
END //
DELIMITER ;
调用：
CALL example_out(@out_param);
SELECT @out_param; -- 读取 OUT 参数的值（应为 200）
INOUT 参数
INOUT 参数用于双向传递数据。调用方可以传递值给存储过程，存储过程可以读取和修改 INOUT 参数的值，并将修改后的值传递回调用方。
DELIMITER //
CREATE PROCEDURE example_inout(INOUT param1 INT)
BEGIN
SET param1 = param1 + 100; -- 修改 INOUT 参数的值
END //
DELIMITER ;
调用：
SET @inout_param = 100; -- 设置初始值
CALL example_inout(@inout_param);
SELECT @inout_param; -- 读取 INOUT 参数的值（应为 200）
存储过程中的变量
会话变量
会话变量在存储过程中定义，生命周期在存储过程的执行过程中有效。
DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
DECLARE session_var INT DEFAULT 0; -- 定义会话变量
SET session_var = 100; -- 设置会话变量的值
SELECT session_var; -- 使用会话变量
END //
DELIMITER ;
调用存储过程
CALL example_procedure();
用户变量
用户变量在会话级别有效，可以在存储过程内外使用，前缀为 @。
DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
SET @user_var = 200; -- 设置用户变量的值
SELECT @user_var; -- 使用用户变量
END //
DELIMITER ;
调用存储过程并使用用户变量
CALL example_procedure();
SELECT @user_var; -- 可以在存储过程外使用
全局变量
全局变量是系统变量，作用域是整个 MySQL 服务器。全局变量前缀为 @@global.
在存储过程中无法定义全局变量，但可以读取和修改现有的全局变量
DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
SET @@global.max_connections = 500; -- 设置全局变量
SELECT @@global.max_connections; -- 使用全局变量
END //
DELIMITER ;
调用存储过程并使用全局变量
CALL example_procedure();
综合使用
以下示例展示了如何在一个存储过程中综合使用会话变量、用户变量和全局变量。
DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
DECLARE session_var INT DEFAULT 0; -- 定义会话变量
-- 设置变量值
SET session_var = 100;
SET @user_var = 200;
SET @@global.max_connections = 500;
-- 使用变量
SELECT session_var AS session_var_value;
SELECT @user_var AS user_var_value;
SELECT @@global.max_connections AS global_max_connections;
END //
DELIMITER ;
调用存储过程并使用变量
CALL example_procedure();
-- 调用存储过程后，用户变量仍然有效
SELECT @user_var AS user_var_value;
注意事项
会话变量在存储过程内部定义并使用。
用户变量在整个会话中有效，可以在存储过程内外使用。
全局变量是服务器级别的系统变量，通常用于配置和调整 MySQL 服务器的行为。
通过这些示例，你应该能够理解如何在 MySQL 中定义和调用会话变量、用户变量和全局变量。
变量的相关命令
show global variables; # 查看全局变量
show session variables; # 查看会话变量
set session sort_buffer_size=40000; # 设置会话变量
show session variables like 'sort_buffer_size'; # 查看会话变量：
算术运算
+ 加法运算，set @var1=2+2; #结果为4
- 减法运算，set @var2=3-2; #结果为1
* 乘法运算，set @var3=3*2; #结果为6
/ 除法运算，set @var4=10/3; #结果为3.333333
DIV 整除运算，set @var5=10 DIV 3; #结果为3
% 取模 ，set @var6=10%3; #结果为1
in 参数示例
//创建一个测试的表
create table test(
id int(10),
name char(10),
age int(10),
addr char(40));
//创建名为say的存储过程，可以接收用户输入的名字，列出该名字的数据
delimiter //
create procedure say(in username char(10))
begin
select * from test where name = username;
end //
delimiter ;
call say('tom'); # 为username赋值
out参数示例
//创建名为hello的存储过程，可以接收用户输入的名字，统计test表中用户输入名字的个数
delimiter //
create procedure hello(out number int) # 输出的参数为number
begin
select count(name) into @number from user where shell!="/bin/bash";
# @numer表示用户变量，即当前用户可以使用，退出数据库后失效
select @number;
end //
delimiter ;
call p2(@number); #用来占位
inout 参数示例
测试inout,输入和输出都是x，输入赋给x为/sbin/nologin，最后将总行数又to给了x
delimiter //
create procedure db9.p6( inout x char(30) )
begin
select name,shell from db9.user where shell=x;
select count(*) into x from db9.user;
select x;
end //
delimiter ;
set @name="/sbin/nologin";
call db9.p6(@name);
3）定义名称为p3的存储过程，用户可以自定义显示user表记录的行数，若调用时用户没有输入行数，默认显示第1条记录
delimiter //
create procedure p3(in linenum char(10) )
begin
if linenum is null then
set @linenum=1;
select * from user where id=@linenum;
else
select linenum;
select * from user where id=linenum;
end if;
end
//
delimiter ;
call p3(null); //不输入查看的行数
call p3(3); //输入查看的行数
综合应用：
输出db9库里user表中 uid号 是偶数 的用户名 及对应的uid号并统计 uid号 是偶数的用户用户个数。
delimiter //
create procedure db9.p28()
begin
declare z int default 0;
declare j int;
declare y int default 1;
declare x int ;
select count(*) into x from db9.user;
while y <= x do
select uid into j from db9.user where id = y;
if j % 2 = 0 then
select name,uid from db9.user where id = y;
set z = z + 1 ;
end if;
set y = y + 1;
end while;
select z;
end //
delimiter ;
call db9.p28();
触发器
https://www.jianshu.com/p/6b694637fd99
什么是触发器
在执行insert、updata、delete之前或之后，执行一些特定操作
特点
触发器本身没有返回值
一张表上不能建立两个相同类型的触发器
优点
减少客户端和服务器之间的通信，简化应用逻辑提高性能
执行顺序
brfore触发器执行失败，sql不执行
sql执行失败，after触发器不执行
after触发器执行失败，sql回滚
创建触发器
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_stmt
trigger_name 触发器名称，用户自行指定
trigger_time 触发时机，取值BEFORE、AFTER
trigger_event 触发事件，INSERT、UPDATE、DELETE
table_name 需要建立触发器的表名
trigger_stmt 触发程序体，可以是一条SQL语句或是BEGIN和END包含的多条语句
trigger_event
INSERT型触发器 ： 插入某一行时激活触发器，可能INSERT、LOAD DATA、REPLACE语句触发。
UPDATE型触发器 ： 更改某一行时激活触发器，可能通过UPDATE语句触发。
DELETE型触发器 ： 删除某一行时激活触发器，可能通过DELETE、REPLACE语句触发。
LOAD DATA
语句用于将一个文件装入到一个数据表中，相当于一系列的INSERT操作。
REPLACE
语句和INSERT语句很像，只是在表中有primary key或unique索引时，如果插入的数据和原来的primary key或unique索引一致时，会先删除原来的数据，然后增加一条新的数据。
触发器的类型
BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE
AFTER INSERT、 AFTER UODATE、 AFTER DELETE
