MYSQL知识总结(md格式)

MYSQL知识

触发器

作用：
在某张表发生INSERT、UPDATE、DELETE事件，触发程序逻辑，要是程序报错，可以定义数据的更改失败。

语法：
CREATE TRIGGER triggerName
BEFORE|AFTER INSERT|UPDATE|DELETE ON tableName
FOR EACH ROW
触发器执行的语句块;

说明：
触发器执行的语句块:可以是单条SQL语句，也可以是由BEGIN...END结构组成的复合语句块。

例子：
当创建的员工的工资大于其领导时，就报错(NEW表示插入的数据):
DELIMITER $
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    #查询到要添加的数据的manager的薪资
    DECLARE mgr_sal DOUBLE;
    SELECT salary INTO mgr_sal FROM employees
    WHERE employee_id = NEW.manager_id;
    IF NEW.salary > mgr_sal
            THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '错误，插入的薪资高于其领导';
    END IF;
END $

查看数据库中定义的所有触发器：
 - 命令行中建议用： SHOW TRIGGERS\G （能够有很好的格式化效果，图形化界面不能使用\G,只能用;结尾）
查看数据库中某个触发器的定义：
 - SHOW CREATE TRIGGER 触发器名\G  
删除触发器：
 - DROP TRIGGER IF EXISTS 触发器名;

DELIMITER的作用

mysql默认是;作为语句的结束，我们在写触发器或者存储过程的时候语句中间要是有;就会导致语句块提前结束，此时我们并未写完代码块，就需要使用delimiter来重新定义一下语句的真正结束符号，例如：delimiter $ ，这样我们在书写语句块的时候就能写;了。

InnoDB存储引擎

表会生成ibd一个文件

聚簇索引（InnoDB）

聚簇索引的叶子结点存储的就是数据记录，非聚簇索引的叶子结点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。
一个表只能有一个聚簇索引，可以有多个非聚簇索引。
聚簇索引查询效率高，不需要回表。但是对数据的插入、删除、更新等操作，效率比非聚簇索引低

二级索引（辅助索引、非聚簇索引）（InnoDB）

就是帮助数据进行查找，当需要查询的数据不在二级索引范围，还需要根据叶子结点存储的主键

联合索引

两个及以上的字段联合作为索引

索引的代价

空间上的代价
每建立一个索引就要为它创建一个B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那就是很大一片存储空间。

时间上的代价
每次对表中的数据增、删、改操作时会需要去维护相应的B+树，比如记录移位、页面分裂、页面回收等。

磁盘与内存交互的基本单位：页

也就是最少从磁盘中读取16KB的内容到内存中，也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载，数据库I/O操作的最小单位是页。

页的内部结构

按照类型划分页
 - 数据页（目录页和数据页都可以叫数据页）
 - 系统页
 - Undo页
 - 事务数据项页

数据页大小 16K,被划分成7个部分
 - File Header 文件头（38B）描述页的信息
 - Page Header 页头（56B）页的状态信息
 - Infimum + supermum 最大最小记录（26B）这是两个虚拟的行记录
 - User Records 用户记录 （不确定）存储行记录内容
 - Free Space 空闲空间 （不确定）页中还没被使用的空间
 - Page Directory 页目录 （不确定）存储用户记录的相对位置
 - File Tailer 文件尾 （8B）检验页是否完整

存储过程和存储函数对比

存储类型	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL存储过程()	0个或多个	一般用于更新
存储函数	FUNCTION	SELECT函数()	只能是1个	一般用于查询结果为一个值并返回

注意：
存储函数可以放在查询语句中，而存储过程不行。
存储过程功能更强大，包括执行对表新增、删除和事务等操作，而存储函数不行

查看存储过程或函数

SHOW CREATE PROCEDURE | FUNCTION 存储过程或函数名\G    （注意\G只在命令行能使用，一般使用;结束）

查看全局或者会话级别的系统变量

可以不写查询的系统变量的范围，默认是SESSION
SHOW GLOBAL | SESSION VARIABLES; 查看全局或者会话级别的所有系统变量
SHOW GLOBAL | SESSION VARIABLES LIKE 'admin_%'; 查询部分全局或者会话级别的系统变量
SELECT @@global.变量名; | @@session.变量名; ｜ @@变量名;（优先从会话级别去找，不存在再去全局找）;    （注意：系统变量用@@变量名表示，用户自定义变量用@变量名）

设置系统变量

SET @@global.变量名 ｜ GLOBAL ｜ @@session.变量名 ｜ SESSION 变量名 = 具体值

注意：
会话级别的系统变量更改，只在当前会话中有效，重新开启一个客户端登录，值就会恢复
全局级别的系统变量更改，只要重启就会恢复默认值，或者是配置文件my.ini中的值。

用户变量

分类：
会话用户变量：对当前连接有效
局部变量：只在BEGIN和END语句块中有效，局部变量只能在存储过程和函数中使用。

会话用户变量：
定义：
  方式一：“=” ｜ “:=”
    - SET @变量名 = 值;
    - SET @变量名 := 值;
  方式二：“:=” | INTO关键字
    - SELECT @变量名 := 表达式 [FROM 等子句];
    - SELECT 表达式 INTO @变量名 [FROM 等子句];
      例子：
        - SELECT @count := count(*) FROM employees;
        - SELECT AVG(salary) INTO @avg_sal FROM employees;
  查看用户变量的值：
  SELECT @变量名

局部变量：
定义：
  使用DECLARE语句定义一个局部变量
作用域：
  仅仅在定义它的BEGIN...END中有效
位置：
  只能放在BEGIN...END中，而且只能放在第一句
例子：
  BEGIN
      #声明局部变量
      DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值];
      DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
      
      #为局部变量赋值
      SET 变量名1 = 值;
      SELECT 值 INTO 变量名2 [FROM 子句];
      
      #查看局部变量的值
      SELECT 变量1,变量2,变量3;
  END