MySQL数据库，视图、存储过程与存储函数

数据库对象：

常见的数据库对象：

视图：

• 视图是一种虚拟表，本身是不具有数据的占用很少的内存空间。

• 视图建立在已有表的基础上，视图赖以建立的这些表称为基表。

• 视图的创建和删除只影响视图本身，不影响对应的基表。但是对视图中的数据进行增加、删除和修改操作时，数据表（基表）中的数据会相应地发生变化。 当对基表中的数据进行增加，删除和操作时，与之对应的视图中的数据记录也会发生相对应的变化。

• 在数据库中，视图不会保存数据，数据真正保存在数据表（基表）中。

创建视图：

精简版：

CREATE  VIEW  视图名称

AS  查询语句;

关于视图中字段的字段名：（若不起字段名，视图中的字段的字段名即为查询字段的字段名）

①可以在查询语句的查询的字段后起别名，查询字段的别名即为视图中字段的字段名。

②在视图名称后加上括号( )，在括号中写上相应的字段的字段名（要一一匹配）。

创建单表视图：

例：

CREATE VIEW view_emp1-- 方式一ASSELECT empno id,ename `name`,deptno dept_idFROM emp;

CREATE VIEW view_emp2(emp_id,`name`,dept_id)-- 方式二ASSELECT empno,ename,deptnoFROM emp;

创建多表视图：

例：

CREATE VIEW view_emp4ASSELECT e.empno,e.ename,e.deptno,d.locFROM emp eJOIN dept dON e.deptno = d.deptno;

利用视图对数据进行格式化：

比如想显示员工的信息的格式为 员工号--姓名--部门号--部门所在地：

CREATE VIEW view_emp5ASSELECT CONCAT(e.empno,'--',e.ename,'--',e.deptno,'--',d.loc) '员工信息'FROM emp eJOIN dept dON e.deptno = d.deptno;

或

CREATE VIEW view_emp6(员工信息)ASSELECT CONCAT(e.empno,'--',e.ename,'--',e.deptno,'--',d.loc)FROM emp eJOIN dept dON e.deptno = d.deptno;

基于视图创建视图：

例：

CREATE VIEW view_emp7(id,`name`)ASSELECT empno,enameFROM view_emp1;

查看视图：

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

SHOW TABLES；

查看视图的结构：

DESC / DESCRIBE 视图名称;

查看视图的属性信息

SHOW TABLE STATUS LIKE 视图;

查看视图的详细信息：

SHOW CREATE VIEW 视图名称;

更新视图中的数据：

类似于更新表的数据

例：

UPDATE view_emp7SET `name` = 'Tony'WHERE id = 7936;DELETE FROM view_emp7WHERE id = 7936;

• 完成UPDATE操作后，不仅视图view_emp7中的id为7936的员工的名字改为了Tony，基表emp中的id为7936的员工的名字也改为了Tony。通过视图删除了id为7936的员工，基表中的id为7936的员工的信息也会跟着删除。

• 基表中的更新删除操作当然也会同步到视图。

• 如果视图中的字段是基表数据的组函数，更新和删除便不会成功。要使得视图可以更新，视图中的行与基表中的行必须保证有一一对应的关系。

• 虽然可以更新视图数据，但是总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图的数据。

• 对视图的修改，都是通过对实际基表中的数据的操作来完成的。

修改视图：

方式一：

使用CREATE OR REPLACE VIEW子句修改视图

CREATE OR REPLACE VIEW view_emp1-- 方式一

AS

SELECT 语句;

方式二：

使用ALTER VIEW的方式

ALTER VIEW 视图名

AS 

SELECT 语句;

删除视图：

DROP VIEW 视图名;

也可以删除多个视图：

DROP VIEW 视图名，视图名……;

假如基于一个或多个视图创建了额外的视图，将此一个或多个视图删除后，会导致此额外的视图的查询的失败。

视图的优点：

1. 操作简单。

2. 减少数据冗余。

3. 数据更安全，用户不需要查询数据表，可以直接通过视图来获取数据表中的信息。

4. 适应灵活多变的需求。

5. 能够分解复杂的查询逻辑。

视图的缺点：

如果实际数据表的结构变了，就需要及时对相关的视图进行相应的维护。视图过多，会导致数据库运维成本的问题。

---

存储过程与存储函数：

存储过程与存储函数能将复杂的SQL逻辑封装在一起，供以调用。

存储过程

存储过程（Stored Proceduce）是一组经过预先编译的SQL语句的封装。

执行过程：存储函数预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以将预先存储好的这一系列SQL语句全部执行。

优点：

• 简化操作，提高了SQL语句的复用性。

• 减少网络传输量。

• 提高了数据查询的安全性。

关于参数前的符号

存储过程的参数类型可以是IN、OUT、INOUT，也可以不带参数。

IN：当前参数为输入参数，也就是表示入参。如果没有定义参数种类，默认是IN。

OUT：当前参数为输出参数，也就是表示出参。

INOUT：当前参数既可以为输入参数，也可以为输出参数。

DELIMITER：

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

格式：DELIMITER 新的结束符

例：

在存储过程的存储过程体中：

DELIMITER $   -- 将结束符设置为‘$’CREATE PROCEDURE 存储过程名(参数列表)BEGIN……（具体的存储过程的操作）END $DELIMITER ;   -- 将结束符设置为‘;’，以方便后面的操作使用‘;’为结束符。

注：使用DELIMITER时，应该注意避免使用‘\’为结束符，因为其为MySQL的转义字符。

存储过程的创建：

格式：

DELIMITER 新结束符

CREATE PROCEDURE 存储过程名(参数列表)

[ characteristics]

BEGIN

……（具体的存储过程的操作）

END 新结束符

DELIMITER ;

characteristics

指定存储过程的特性，有以下取值：

• LANGUAGE SQL：

说明存储过程体部分是由SQL语句组成的，当前系统支持的语言为 SQL。

• DETERMINISTIC和NOT DETERMINISTIC：指明存储过程执行的结果是否正确.

  • DETERMINISTIC表示结果是确定的每次执行存储过程时，相同的输入会得到相同的输i出。

  • NOT DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。

如果没有指定任意一个值，默认 为 NOT DETERMINISTIC。

• { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使 用SQL语句的限制。

  • CONTAINS SQL表明子程序包含SQL语句，但是不包括读写数据的语句；

  • NO SQL表明子程序不包含SQL语句；

  • READS SQL DATA说明子程序包含读数据 的语句；

  • MODIFIES SQL DATA表明子程序包含写数据的语句。

默认情况下，系统会指定 为 CONTAINS SQL。

• SQL SECURITY {DEFINER | INVOKER }：

表示执行当前存储过程的权限，指明哪些用户有权限来执行。

• • DEFINER表示只有定义者才能执行。

  • INVOKER表示拥有权限的调用者可以执行。

默认情况下，系统指定为 DEFINER。

• COMMENT ‘string’：注释信息，可以用来描述存储过程或函数。

存储过程体是SQL代码的内容，用BEGIN…END来表示存储过程体开始和结束。

存储过程的调用

使用CALL关键字：

CALL 存储过程名(参数列表);

①无参的存储过程的使用：

例：创建一个查看所有部门的平均工资的存储过程并调用：

DELIMITER $CREATE PROCEDURE dept_avg_sal()BEGINSELECT deptno,AVG(sal) avg_salFROM test_pro_1GROUP BY deptno;END $DELIMITER ;CALL dept_avg_sal;-- 因为无参，也可以不写括号

②带有传出参数（带有OUT）的存储过程的使用：

例：输出工资最低的员工的工资：

DELIMITER $CREATE PROCEDURE show_min_salary(OUT ms DECIMAL(7,2))BEGINSELECT MIN(sal) INTO msFROM emp;END $DELIMITER ;CALL show_min_salary(@ms);-- 调用show_min_salary过程并将输出值赋值给@ms变量。SELECT @ms;-- 查看@ms变量。

③带有传入参数（带有IN）的存储过程的使用：

例：输入员工的姓名，根据输入的姓名查询员工的工资：

DELIMITER $CREATE PROCEDURE show_someone_sal(IN empname VARCHAR(50))BEGINSELECT salFROM empWHERE ename = empname;END $DELIMITER ;CALL show_someone_sal('KING');-- 调用，输出名字为KING的员工的工资。

④带有输入函数与输出函数（带IN和OUT）的存储过程的使用：

例：输入员工的姓名，根据输入的姓名输出员工的工资：

DELIMITER $CREATE PROCEDURE show_sal(IN emp_name VARCHAR(50),OUT emp_salary DECIMAL(7,2))BEGINSELECT sal INTO emp_salaryFROM empWHERE ename = emp_name;END $DELIMITER ;CALL show_sal('KING',@es);-- 调用show_sal存储过程，将输出值赋值给变量@es。SELECT @es;-- 查看@es。

⑤带INOUT的参数的存储过程的使用：

例：查询某个员工领导的姓名，并用INOUT参数输入员工姓名，输出领导的姓名。

DELIMITER $CREATE PROCEDURE show_mgr_name(INOUT emp_name VARCHAR(50))BEGINSELECT ename INTO emp_nameFROM empWHERE empno = (SELECT mgrFROM empWHERE ename = emp_name);END $DELIMITER ;SET @James = 'JAMES';CALL show_mgr_name(@James);SELECT @James;

注：由于show_mgr_name存储过程不仅要对参数emp_name进行读取，还要对参数emp_name进行赋值，代入的参数不能是一个字符串值（只能读取，不能赋值），而应该是一个变量。

存储过程的缺点：

可移植性差、调试困难、版本管理困难、不适合高并发的场景。

存储函数

函数可以对数据进行各种处理操作，提高用户对数据库的管理效率。MySQL支持自定义函数（存储函数），自定义的调用方式与MySQL预定义的系统函数一样。

格式：

DELIMITER 新结束符

CREATE FUNCTION 函数名(参数列表)

RETURNS 返回值类型

[ characteristics]

BEGIN

……（函数体）

END 新结束符

DELIMITER ;

注：

• 函数中所有的参数都是IN类型的。

• 函数体中必定包含RETURN语句。

• 函数体用BEGIN和END来表示函数体中的SQL语句的开始和结束。如果函数体只有一条语句，可省略BEGIN和END。建议统一加上，作为习惯。

• [ characteristics]部分和存储过程的一致。

• 如果创建存储函数时出现‘you *might* want to use the less safe log_bin_trust_function_creators variable’的报错，有两种方式解决：

  • ①加上必要的函数特性“DETERMINISTIC和NOT DETERMINISTIC”与“{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }”例：

  • DELIMITER $CREATE FUNCTION job_by_name()RETURNS VARCHAR(50)NOT DETERMINISTICCONTAINS SQLREADS SQL DATABEGINRETURN (SELECT job FROM emp WHERE ename = 'KING');END $DELIMITER ;

  • ②执行：SET GLOBAL log_bin_trust_function_creators = 1;

关于带参数的存储函数的创建与调用：（与存储过程类似）

DELIMITER $CREATE FUNCTION job_by_id(emp_id INT)RETURNS VARCHAR(50)BEGINRETURN (SELECT job FROM emp WHERE empno = emp_id);END $DELIMITER ;SELECT job_by_id(empno)FROM emp;

对比存储函数和存储过程：

• 存储函数可以放在查询语句中使用，存储过程不行。

• 存储过程的功能更强大，包括能对表的操作和事务操作。

存储过程和存储函数的查看、修改、删除

查看：

①使用SHOW CREATE语句查看存储过程和存储函数的创建信息。

SHOW CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名;

②使用SHOW STATUS语句查看存储过程和存储函数的状态信息。

SHOW PROCEDURE/FUNCTION [LIKE 'patten'];

③从infomation_schema.Routines表中查看存储函数和存储过程的信息。

SELECT *

FROM information_schema.Routines

WHERE ROUTINE_NAME = '存储函数名或存储过程名' [AND ROUTINE_TYPE = 'FUNCTION'/'PROCEDURE'];

修改：

修改存储过程或函数，不影响存储函数或存储过程的功能（不能修改存储函数体或存储过程体），只是修改相关特性。使用ALTER语句实现。

ALTER CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名 [ characteristics];

删除：

DROP CREATE PROCEDURE/FUNCTION [IF EXISTS] 存储过程名/存储函数名;