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 是对应表的行记录类型变量。