索引，存储过程，事件，游标，触发器

最新推荐文章于 2023-06-04 17:21:56 发布

.西瓜.

最新推荐文章于 2023-06-04 17:21:56 发布

阅读量241

点赞数

分类专栏： sql 文章标签： mysql

本文链接：https://blog.csdn.net/lllllljr/article/details/116947455

版权

sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

索引

在MYSQL中，索引（index）也叫做键（key），它是存储引擎用于快速找到记录的一种数据结构。
索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就预发重要。
索引优化应该是对查询性能优化最有效的手段。

--  create index indexName ON mytable(uesrname);
create Index index_dept_dname on dept(dname);
drop index index_dept_dname on dept;

在这里插入图片描述

索引
视图

视图

- MYSQL视图（view）是一种虚拟存在的表，同真实表一样，视图也有列和行构成，但视图并不实际存在于数据库中。
- 行和列的数据来自于定义视图的查询中所使用的表，并且还是使用视图时动态生成的。
优点
创建视图
- create new <视图名> as <select语句>;
  
  eg：
```
CREATE VIEW view_students_info AS SELECT * FROM tb_students_info;
CREATE VIEW v_students_info(s_id,s_name,d_id,s_age,s_sex,s_height,s_date) AS SELECT id,name,dept_id,age,sex,height,login_date
```
  语法说明如下。
  - <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
  - <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
- SELECT 语句不能引用系统或用户变量。
- SELECT 语句不能包含 FROM 子句中的子查询。
- SELECT 语句不能引用预处理语句参数。
查询视图
- describe 视图名；
- 查看视图的详细信息： show create view 视图名；
- 所有视图的定义都是存储在 information_schema 数据库下的 views 表中，也可以在这个表中查看所有视图的详细信息，SQL 语句如下：
  
  SELECT * FROM information_schema.views;
修改视图: alter view <视图名> as <select语句>
删除视图： drop view <视图名1> <视图名2> …

存储过程

创建存储过程： create procedure <过程名>（过程参数1， … , … ,） <过程体>
- eg：
```
create procedure p_hello_world(in var_empno int)
begin
	select * from emp e where e.empno = var_empno;
end;
```
- 过程参数格式： in 、 out、 inout <参数名> <类型>
- 输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识
- 在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若存储过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。
- 拓展：通常使用 DELIMITER 命令将结束命令修改为其他字符。 eg：delimiter $$
调用存储过程：call 存储过程名;

eg：
```
		call p_hello_world(7788);
```
show procedure status like 存储过程名;
- LIKE 存储过程名用来匹配存储过程的名称，LIKE 不能省略。
- 查看存储过程的定义：show create procedure 存储过程名；
  
  show status 语句只能查看存储过程是操作的哪一个数据库、存储过程的名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是，这个语句不能查询存储过程的集体定义，如果需要查看详细定义，需要使用 show create 语句。
- 扩展：存储过程的信息都存储在 information_schema 数据库下的 Routines 表中，可以通过查询该表的记录来查询存储过程的信息，SQL 语句如下：
  
  select * from information_schema.routines where routine_name = 存储过程名;
  
  在 information_schema 数据库下的 routines 表中，存储着所有存储过程的定义。所以，使用 SELECT 语句查询 routines 表中的存储过程和函数的定义时，一定要使用 routine_name 字段指定存储过程的名称，否则，将查询出所有的存储过程的定义。
删除存储过程：drop procedure if exists <存储过程名>;
- 注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。
修改存储过程：alter procedure 存储过程名特征
- 特征指定了存储过程的特性，可能的取值有：
  - CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
  - NO SQL 表示子程序中不包含 SQL 语句。
  - READS SQL DATA 表示子程序中包含读数据的语句。
  - MODIFIES SQL DATA 表示子程序中包含写数据的语句。
  - SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
  - DEFINER 表示只有定义者自己才能够执行。
  - INVOKER 表示调用者可以执行。
  - COMMENT ‘string’ 表示注释信息.
存储函数：create function
存储函数

变量的定义与赋值

定义变量：用declare声明变量， **declare 变量名数据类型 default value **

-- 下面定义变量 my_sql，数据类型为 INT 类型，默认值为 10。SQL 语句如下：
delcare my_sql int default 10;

赋值变量：
- 用set关键字来为变量赋值， set 变量名 = 值，变量2 = 值 , …
```
set my_sql = 30;
```
- 用select …into为变量赋值，select 查询的字段名称 into 变量名 from 表名 where 数据查询条件
  - ```
  select id into mt_sql from tb_student where id = 2;
```
拓展：declare 和 set定义变量的区别 [(1条消息) mysql存储过程中 declare 和 set 定义变量的区别_seteor的专栏-CSDN博客](
拓展：declare 和 set定义变量的区别 (1条消息) mysql存储过程中 declare 和 set 定义变量的区别_seteor的专栏-CSDN博客
流程控制语句详解
- if 语句: 用来进行条件判断。格式：**if 条件判断语句 then 条件满足被执行的SQL语句 ; **
  
  **elseif 条件判断语句 then 被执行的SQL语句 ; **
  
  **else 被执行的SQL语句 ; **
  
  end if ;
```
if age > 20 then set count1 = count1 + 1;
elseif age = 20 then count2 = count2 + 1;
else count3 = count3 + 1;
end  if;
```
- case 语句: 用来进行条件判断。格式： **case 用来进行条件判断的变量 **
  
  when 变量的取值 then 执行的SQL语句 ;
  
  **when 变量的曲子2 then 执行的SQL语句 ; **
  
  … *n
  
  **else 执行的SQL语句 ; **
  
  **end case ; **
```
case age
	when 20 then set count1 = count1 + 1;
	else set count2 = count2 + 1;
end case;

-- 或者
case 
  when age = 20 then set count1 = count1 + 1;
```
- loop 语句 ：可以是某些特定的语句重复执行。（本身没有停止循环的语句，必须只用leave语句停止循环）
  
  格式：开始标志： loop
  
  **执行的sql语句 ; **
  
  **end loop 结束标志 ; **
  
  开始和结束标志必须相同，而且都可以省略。
```
-- 死循环一直加1
add_num : loop
	set count = count + 1;
end loop add_num;
```
- leave 语句 ：用于跳出控制。
  - eg： leave lable 其中 lable 参数表示循环的表值， leave语句必须跟在循环标志的前面
```
add_num : loop
	set count = count + 1;
	if count = 100 then leave add_num;
end loop add_num;
```
- iterate 语句 ：用来跳出本次循环，直接进入下一次循环。
  
  格式： iterate label 其中label 参数表示循坏的标志， iterate语句必须跟在循环标志前面。 mod(变量，数字) = 0 变量的值被数字整除.
```
add_num : loop
	set count = count + 1;
	if count = 100 then leave add_num;
	else if mod(count, 3) = 0 then iterate add_num;
	select * from employee;
end loop add_num;
```
- repeat 语句 ：每次语句执行完毕后，会对条件表达式进行判断，如果表达式返回值为 TRUE，则循环结束，否则重复执行循环中的语句。
  
  格式： 标注名称（可省） repeat
  
  **循环的执行语句 ; **
  
  **until 结束循环的条件 **
  
  **end repeat 标注名称（可省）； **
```
repeat
	set count = count + 1;
    until count = 100
end repeat;
```
- while 语句 ： WHILE 语句是当满足条件时，执行循环内的语句，否则退出循环。
  
  格式： 标注名称（可省） while 循环执行的条件 do
  
  **sql循环执行语句； **
  
  end while ；
```
while count < 100 do
	set count = count + 1;
end while;
```

定义条件和处理程序 [](MySQL定义条件和处理程序 (biancheng.net))

游标

含义：游标就是一个标识，用来标识数据取到了什么地方，如果你了解编程语言，可以把他理解成数组中的下标。
注意：MySQL 游标只能用于存储过程和函数。

结果集是符合 SQL 语句的所有记录的集合。

声明游标：declare 游标名 cursor for select语句
```
delimiter //
create procedure processnames()
	begin
	declare namecursor cursor for  select  name from tb_student;
	end//
```
游标只局限于存储过程中，存储过程处理完成后，游标就消失了。

打开游标：open 游标名；
- 打开一个游标时，游标并不指向第一条记录，而是指向第一条记录的前边。
使用游标：fetch 游标名 into 变量参数；
- 将游标 cursor_name 中 SELECT 语句的执行结果保存到变量参数中
- MySQL 的游标是只读的，也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。
关闭游标：close 游标名;
- CLOSE 释放游标使用的所有内部内存和资源，因此每个游标不再需要时都应该关闭。
- 在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明，用 OPEN 语句打开它就可以了。
  
  如果你不明确关闭游标，MySQL 将会在到达 END 语句时自动关闭它。游标关闭之后，不能使用 FETCH 来使用该游标。
触发器

1) INSERT 触发器

在 INSERT 语句执行之前或之后响应的触发器。

使用 INSERT 触发器需要注意以下几点：
- 在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
- 在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
- 对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。
2) UPDATE 触发器

在 UPDATE 语句执行之前或之后响应的触发器。

使用 UPDATE 触发器需要注意以下几点：
- 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
- 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
- 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
- OLD 中的值全部是只读的，不能被更新。
注意：当触发器设计对触发表自身的更新操作时，只能使用 BEFORE 类型的触发器，AFTER 类型的触发器将不被允许。

3) DELETE 触发器

在 DELETE 语句执行之前或之后响应的触发器。

使用 DELETE 触发器需要注意以下几点：
- 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
- OLD 中的值全部是只读的，不能被更新。
总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序。

创建触发器

格式：**create trigger 触发器名 before/after insert/update/delete on 表名 for each row 触发器主体 ; **

1) 触发器名

触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。

2) INSERT | UPDATE | DELETE

触发事件，用于指定激活触发器的语句的种类。

注意：三种触发器的执行时间如下。
- INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
- DELETE：从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
- UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。
3) BEFORE | AFTER

BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

4) 表名

与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。

5) 触发器主体

触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。

6) FOR EACH ROW

一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。

另外，在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用show triggers 语句。

[创建触发器](MySQL创建触发器（CREATE TRIGGER） (biancheng.net))

-- 创建一个名为 SumOfSalary 的触发器，触发的条件是向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算。
create trigger SumOfSalary
	before insert on tb_emp8 for each row
	set sum = sum + New.salary;

-- 创建一个名为 double_salary 的触发器，触发的条件是向数据表 tb_emp6 中插入数据之后，再向数据表 tb_emp7 中插入相同的数据，并且 salary 为 tb_emp6 中新插入的 salary 字段值的 2 倍。
create trigger double_salary
	after insert on tb_emp6 for each row insert into tb_emp7 values(new.id, new.name, deptid, 2*new.salary);

查看触发器

show triggers； 语句查看触发器信息
```
show triggers \G;
-- 在 SHOW TRIGGERS 命令后添加\G，这样显示信息会比较有条理
```
- 注意：SHOW TRIGGERS 语句用来查看当前创建的所有触发器的信息。因为该语句无法查询指定的触发器，所以在触发器较少的情况下，使用该语句会很方便。如果要查看特定触发器的信息或者数据库中触发器较多时，可以直接从 information_schema 数据库中的 triggers 数据表中查找。
在triggers表中查看触发器信息
- 格式：select * from information_schema.triggers where trigger_name = ’ 触发器名 ’ ;
  
  '触发器名'用来指定要查看的触发器的名称，需要用单引号引起来。
```
select * from information_schema.triggers where trigger_name = 'trigupdate'\G
```
- select * from information_schema.triggers \G
  
  这个语句会显示 triggers 数据表中所有的触发器信息。
- TRIGGER_SCHEMA 表示触发器所在的数据库；
  
  TRIGGER_NAME 表示触发器的名称；
  
  EVENT_OBJECT_TABLE 表示在哪个数据表上触发；
  
  ACTION_STATEMENT 表示触发器触发的时候执行的具体操作；
  
  ACTION_ORIENTATION 的值为 ROW，表示在每条记录上都触发；
  
  ACTION_TIMING 表示触发的时刻是 AFTER；

修改和删除触发器

修改触发器：修改触发器可以通过删除原触发器，再以相同的名称创建新的触发器。
删除触发器： drop trigger is exists 数据库名触发器名 ;

权限：执行 DROP TRIGGER 语句需要 SUPER 权限。

if exists : 可选项。避免在没有触发器的情况下删除触发器。

注意：删除一个表的同时，也会自动删除该表上的触发器。另外，触发器不能更新或覆盖，为了修改一个触发器，必须先删除它，再重新创建。

-- 删除double_salary触发器
drop trigger double_salary;

事件

事件：是MYSQL在相应的时刻调用过程式数据库对象，一个事件可调用一次，也可周期性的启动，它由一个特定的线程来管理的，也就是所谓的‘事件调度器’。

开启事件

查看是否开启

-- 是否开启
show variables like '%event_scheduler%';

-- 如果是关闭的 （临时开启）
set global event_scheduler = on;

-- 如果是关闭的（长久开启）
-- 修改my.ini中mysqld数据
event_scheduler = on

创建事件
- 每个事件两个部分
  1. 事件调度 : 表示事件何时启动以及按什么频率启动。
  2. 事件动作：事件启动时执行的代码。
- 一个事件可以是活动的或者停止的。
```
create event if not exists 事件名字 on schedule ;
```

在这里插入图片描述

事件

.西瓜.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
索引，存储过程，事件，游标，触发器

索引在MYSQL中，索引（index）也叫做键（key），它是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就预发重要。索引优化应该是对查询性能优化最有效的手段。-- create index indexName ON mytable(uesrname);create Index index_dept_dname on dept(dname);drop index index_dept_dname on
复制链接

扫一扫