MySql高级

MySql高级

1.视图（view）

**什么是视图：**视图就是基于查询的虚拟表，就是一条select执行后的结果集。

​ 之后查询时只需要查询视图，若修改了基表数据，视图随之改变。（视图并不存储数据）

为什么使用视图：是为了方便复杂的查询语句，基本思路就是将复杂的查询语句定义在视图的内部，然后对视图进行查询，从而简化复杂的查询语句。

• 创建视图

CREATE VIEW 视图名 AS SELECT 列1，列2... FROM 表(查询语句);

• 使用视图

SELECT * FROM 视图名

• 删除视图

drop view 视图名

2.存储过程（PROCEDURE）

**什么是存储过程：**将逻辑处理的代码事先存储在数据库中，然后在使用时直接调用即可。

**为什么使用存储过程：**可以减少应用程序与数据库的交互次数。

• 创建存储过程的语法格式

  -- 开始位置
  DELIMITER$$
  CREATE PROCEDURE test()
  BEGIN
  -- 声明变量
  DECLARE v_name VARCHAR(20) DEFAULT 'jim';
  SET v_name = 'tom'; -- 变量赋值
  SELECT v_name; -- 测试输出语句
  END$$ -- 结束位置
  -- 调用存储过程
  CALL test1()
  

语法解析：

1.存储过程的参数分为 in，out，

inout 三种类型。

2.in 代表输入参数（默认情况下为 in 参数），表示该参数的值必须由调用程序

指定。

3.out 代表输出参数，表示该参数的值经存储过程计算后，将 out 参数的计算结

果返回给调用程序。

4.inout 代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序

指定，又可以将 inout 参数的计算结果返回给调用程序。

5.存储过程中的语句必须包含在 begin 和 end 之间。

6.declare 中用来声明变量,变量默认赋值使用 default,语句块中改变变量值,使用 set 变量=值;

• 定义一个有参的存储过程

DELIMITER$$
CREATE PROCEDURE findUserCount(IN p_type INT,OUT p_count INT)
BEGIN
-- 把 sql 中查询的结果赋给 变量
SELECT COUNT(*) INTO p_count FROM USER WHERE TYPE = p_type;
SELECT p_count;
END$$
测试
CALL findUserCount(1,@p_count); -- @p_count 测试输出参数

3.函数（function）

1.参数列表包含两部分：参数名 参数类型

2.函数体：肯定会有 return 语句，如果没有会报错

3.函数体中仅有一句话，则可以省略 begin end

4.使用 delimter 语句设置结束标记

设置函数可以没有参数

SET GLOBAL log_bin_trust_function_creators=TRUE; 

删除函数

DROP FUNCTION 函数名;

• 定义一个不带参数的函数

DELIMITER$$
CREATE FUNCTION test() RETURNS INT
BEGIN
DECLARE v_num INT;
SELECT COUNT(*) INTO v_num FROM t_user;
RETURN v_num;
END$$
测试
select test() from dual;

• 定义一个带参数的函数

DELIMITER$$
CREATE FUNCTION findDeptNameById(p_id INT) RETURNS VARCHAR(10)
BEGIN
DECLARE v_name VARCHAR(10);
SELECT NAME INTO v_name FROM dept WHERE id = p_id;
RETURN v_name;
END$$
SELECT account,findDeptNameById(dept_id) FROM user

4.触发器（trigger）

**什么是触发器：**触发器是一种特殊的存储过程，其特殊性就在于，它不需要用户去直接调用，而是在使用对表的增删改之前或之后自动执行的存储过程。

触发器具有以下特点：

1.与表相关联

触发器定义在特定的表上，这个表称为触发器表。

2.自动激活触发器

当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这 个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

3.不能直接调用

与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

4.作为事务的一部分

触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚 。

语法解析：

1.触发器名称：是用来标识触发器的，由用户自定义。

2.触发时机：其值是 before 或 after。

3.触发事件：其值是 insert，update 和 delete

4.表名称：标识建立触发器的表名，即在哪张表上建立触发器

5.语句：是触发器程序体，触发器程序可以使用 begin 和 end 作为开始和结束， 中间包含多条语句；

• 案例删除用户时,自动触发删除用户菜单关系

DELIMITER $$ 

CREATE TRIGGER delete_user_menu BEFORE DELETE 

ON t_user 

FOR EACH ROW 

BEGIN 

DELETE FROM t_user_menu WHERE user_id = old.id; 

END$$;

在行级触发器代码中，可以使用 old 和 new 访问到该行的旧数据和新数据，old 和 new 是对应表的行记录类型变量。