视图

概念

可以将视图理解为存储起来的 SELECT 语句。可以很好的控制数据的访问权限，方便调用。

使用视图就像使用表一样，视图会保存在当前数据库下。直接：

1
SELECT * FROM 视图名;

创建视图

完全版：

1
2
3
4
5
CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)]
AS 查询语句
[WITH [CASCADED|LOCAL] CHECK OPTION]

精简版：

1
2
CREATE VIEW 视图名称
AS 查询语句;

比如：

1
2
3
4
5
CREATE VIEW salvu50
AS 
SELECT  employee_id as ID_NUMBER, last_name as `NAME`,salary*12 as ANN_SALARY -- 别名会作为视图表里面的列名
FROM    employees
WHERE   department_id = 50;
1
2
3
4
CREATE VIEW emp_year_salary (ename,year_salary) -- 也可以在视图名后面重命名视图表里面的列名
AS 
SELECT ename,salary*12*(1+IFNULL(commission_pct,0))
FROM t_employee;

查看视图属性

语法1：查看数据库的表对象、视图对象

1
SHOW TABLES;

语法2：查看视图的结构

1
DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息

1
2
# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等）
SHOW TABLE STATUS LIKE '视图名称'\G

执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表。

语法4：查看视图的详细定义信息

1
SHOW CREATE VIEW 视图名称;

修改视图

1
2
CREATE OR REPLACE VIEW 被修改视图名
AS 查询语句;

删除视图

就是把保存的 SELECT 语句给删掉了。

1
DROP VIEW IF EXISTS 视图名称;

存储过程

一段没有返回的应用程序，调用就执行。

创建语法

1
2
3
4
5
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END

定义属性

1、关于 IN OUT INOUT。

  • IN：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。
  • OUT：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
  • INOUT：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL数据库中的任意类型。

3、characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

1
2
3
4
5
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
  • LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
  • [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
  • { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。
    • CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
    • NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
    • READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
    • MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
    • 默认情况下，系统会指定为CONTAINS SQL。
  • SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
    • DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
    • INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
    • 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
  • COMMENT 'string'：注释信息，可以用来描述存储过程。

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

过程体

  • 会用到下面的语句：
1
2
3
4
1. BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT A,B INTO C,D：把从数据表中查询的结果存放到变量中，也就是为变量赋值（C=A D=B）。
  • 需要设置新的结束标记：
1
DELIMITER 新的结束标记

因为 MySQL 默认的语句结束符号为分号 ;。为了避免与存储过程中 SQL 语句结束符相冲突，需要使用 DELIMITER 改变存储过程的结束符，比如改为 $。

例子：

1
2
3
4
5
6
7
8
9
DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM emps;
	
END $

DELIMITER ;

调用存储过程

使用 CALL 调用，存储过程和数据库绑定，如果执行其他数据库中的存储过程，要加上数据库名前缀 databaseName.procedureName：

1
CALL 存储过程名(参数);

格式：

1、调用in模式的参数：

1
CALL sp1('值');

2、调用out模式的参数：

1
2
CALL sp1(@name);
SELECT @name;

3、调用inout模式的参数：

1
2
3
SET @name=值;
CALL sp1(@name);
SELECT @name;
1
2
3
4
5
6
7
8
9
10
11
12
-- 创建存储过程 get_phone(),实现传入女神编号，返回女神姓名和女神电话。
DELIMITER $
CREATE PROCEDURE get_phone(IN girl_id INT, OUT girl_name VARCHAR(15),OUT girl_phone VARCHAR(15))
BEGIN
	SELECT `NAME`, phone INTO girl_name,girl_phone FROM beauty WHERE id = girl_id;
END $
DELIMITER ;

-- 调用存储过程并查看返回值。
SET @girl_id = 1;
CALL get_phone(@girl_id,@girl_name,@girl_phone);
SELECT @girl_name,@girl_phone;

存储函数

可以在 SELECT 语句中使用，会有固定的一个返回值。

创建语法

1
2
3
4
5
6
7
8
9
10
CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体   #函数体中肯定有 RETURN 语句

END

-- 调用
SELECT 函数名(参数...)

说明：

1、参数列表：FUNCTION 中总是默认为 IN 参数。

2、RETURNS type 语句表示函数返回数据的类型。RETURNS 子句只能对 FUNCTION 做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。

4、函数体也可以用 BEGIN…END 来表示 SQL 代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END。

注意：

若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”，有两种处理方法：

  • 方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”

  • 方式2：

1
mysql> SET GLOBAL log_bin_trust_function_creators = 1;

举例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DELIMITER $

CREATE FUNCTION `add_num`(n INT)
RETURNS INT
    -- 不加就会报错
	DETERMINISTIC
	CONTAINS SQL
BEGIN
       DECLARE i INT;
       DECLARE SUM INT;
       
       SET i = 1;
       SET SUM = 0;
       WHILE i <= n DO
              SET SUM = SUM + i;
              SET i = i +1;
       END WHILE;
       RETURN SUM;
END $

DELIMITER ;

--调用

select add_num();

关于存储 过程和函数

对比

关键字 调用语法 返回值 应用场景
存储过程 PROCEDURE CALL 存储过程() 理解为有0个或多个 一般用于更新，可以执行强大功能
存储函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时

查看

1
SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
1
SHOW {PROCEDURE | FUNCTION} STATUS LIKE 'pattern'

修改

1
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic 指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

1
2
3
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
  • CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
  • NO SQL，表示子程序中不包含SQL语句。
  • READS SQL DATA，表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA，表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
    • DEFINER，表示只有定义者自己才能够执行。
    • INVOKER，表示调用者可以执行。
  • COMMENT 'string'，表示注释信息。

修改存储过程使用 ALTER PROCEDURE 语句，修改存储函数使用 ALTER FUNCTION 语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。

删除

删除存储过程和函数，可以使用 DROP 语句，其语法结构如下：

1
DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名。