(七)MySQL视图、索引、存储过程和触发器
视图(view)
- 是一种虚拟存在的表,同真实表一样,视图也由列和行构成。
- 如果经常需要从多个表查询指定字段的数据,可以在这些表上建立一个视图,通过这个视图显示这些字段的数据。
- 视图不能索引,也不能有关联的触发器、默认值或规则。
- 视图不包含数据,所以每次使用视图时,都必须执行查询中所需的任何一个检索操作。如果用多个连接和过滤条件创建了复杂的视图或嵌套了视图,可能会发现系统运行性能下降得十分严重。因此,在部署大量视图应用时,应该进行系统测试。
CREATE VIEW <视图名> AS <SELECT语句>
<视图名>
:指定视图的名称。该名称在数据库中必须是唯一的,不能与其他表或视图同名。<SELECT语句>
:不能引用系统或用户变量,不能包含 FROM 子句中的子查询,不能引用预处理语句参数。
索引(INDEX)
存储过程
-
为以后的使用而保存的一条或多条MySQL语句的集合。存储过程思想上就是数据库 SQL 语言层面的代码封装与重用。类似c语言里的函数的概念。
创建存储过程
DELIMITER // CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) BEGIN <过程体> END// DELIMITER ; #(过程参数[,…] )格式: ( [ IN | OUT | INOUT ] <参数名> <类型>)
- 过程名:默认在当前数据库中创建,若在特定数据库中创建,则要在名称前面加上数据库的名称,即 db_name.sp_name。
- 过程参数:
IN,输入参数,调用者向过程传入值;
OUT,输出参数表示过程向调用者传出值(可以返回多个值)(传出值只能是变量);
INOUT,输入/输出参数既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量),尽量少用。
- DELIMITER:存储过程体可能包含有多条 SQL 语句,这些 SQL 语句如果仍以分号作为语句结束符,那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符,而不再去处理存储过程体中后面的 SQL 语句。使用 DELIMITER 命令将结束命令修改为其他字符。
变量赋值:
MySQL存储过程中,定义变量有两种方式:
1、使用set或select直接赋值,变量名以@开头,可以在一个会话的任何地方声明,作用域是整个会话,称为用户变量。
set @var=1;
2、以declare关键字声明的变量,只能在存储过程中使用,称为存储过程变量,例
declare var1 int default 0;
两者的区别是:
在调用存储过程时,以declare声明的变量都会被初始化为null。而会话变量(即@开头的变量)则不会被再初始化,在一个会话内,只须初始化一次,之后在会话内都是对上一次计算的结果,就相当于在是这个会话内的全局变量。
查看存储过程
SHOW PROCEDURE STATUS LIKE 存储过程名; #存储过程的创建时间、修改时间和字符集等 SHOW CREATE PROCEDURE 存储过程名; #详细定义,包括过程代码
使用存储过程
CALL 存储过程名();
一个实例:删除给定球员参加的所有比赛
mysql> delimiter $$ #将语句的结束符号从分号;临时改为两个$$ mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter; mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 7 | 1 | 57 | 3 | 0 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 5 rows in set (0.00 sec) mysql> call delete_matches(57); Query OK, 1 row affected (0.03 sec) mysql> select * from MATCHES; +---------+--------+----------+-----+------+ | MATCHNO | TEAMNO | PLAYERNO | WON | LOST | +---------+--------+----------+-----+------+ | 1 | 1 | 6 | 3 | 1 | | 8 | 1 | 8 | 0 | 3 | | 9 | 2 | 27 | 3 | 2 | | 11 | 2 | 112 | 2 | 3 | +---------+--------+----------+-----+------+ 4 rows in set (0.00 sec)