2021-04-22-mysql的视图、索引、触发器、存储过程、函数总结

视图

• 视图是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表。视图是存储在数据字典里的一条select语句。 通过创建视图可以提取数据的逻辑上的集合或组合。
• 视图的优点：
  1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
  2.可以把复杂的查询变的简单
  3.维护数据的独立性，视图可从多个表检索数据。
  4.对于相同的数据可产生不同的视图。
  5.权限的控制

创建视图

• 语法如下

CREATE  VIEW 视图名称 AS 子查询

• 创建一张基本工资大于2000元的员工信息视图
  
• 创建一张可以统计部门编号，部门名称，部门人数，部门平均工资的视图
  

查询视图

• 1.查询刚才创建的视图V_EMP
  语法如下：

SELECT * FROM VIEW 视图名称

修改视图

• 语法如下：

create or replace view 视图名称 as 查询语句 
或者
alter view 视图名称  as 查询语句;

• 该语句用于更改已有视图的定义。其语法与CREATE VIEW类似。当视图不存在时创建，存在时进行修改
  

删除视图

• 如果一个视图不在使用时，可以直接通过DROP命令删除。
  语法如下：

drop view 视图名;

查看视图定义

• 语法如下：

DESC 视图名称;

索引

• 索引用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

创建索引

• 语法如下：

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

• 例子：给emp表中的sql字段添加索引

alter table emp add index sal_index (sal);

删除索引

• 语法如下：

Alter table 表 drop index 索引名；

• 例子：删除emp_sal_ind索引

alter table emp drop index sal_index;

说明

• 给字段添加索引是为了提高查询效率，但不是所有的字段都可以添加索引，创建索引有以下特点。

• 创建建索引列的特点：
  1.在经常需要搜索的列上，可以加快搜索的速度；

  2.在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

  3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

  4.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

触发器

• 在MySQL里面也就是对某一个表的一定的操作，触发某种条件（Insert,Update,Delete 等），从而自动执行的一段程序。和Web中学过的监听器很像。

创建触发器

• 语法如下：

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

• 例子:创建两种表t1,t2两种表中都有id,name字段，新加t1表记录后自动添加t2

CREATE TRIGGER triggen1
after
insert ON t1
FOR EACH ROW
insert into t2(name) values(new.name);

说明：NEW 是新值，OLD 是旧值
INSERT 只有NEW
UPDATE有NEW和OLD
DELETE只有OLD

• 注意：一般情况下，Mysql默认是以;号作为结束执行语句。在创建触发器过程中需要用到;号。为了解决这个问题，可以用到delimiter语句。例如delimiter //，可以将结束符号变成//，创建完触发器后再执行delimiter ;将结束符号改回成;号
• 例2:创建t3表演示NEW和OLD的区别

delimiter //
CREATE TRIGGER triggen2
after
update ON t1
FOR EACH ROW
begin
insert into t2(name) values(new.name);
insert into t3(name) values(old.name);
end //
delimiter ;

查看触发器

• 语法如下：

show TRIGGERS

删除触发器

DROP TRIGGER 触发器的名字

存储过程

• 存储过程是能完成一定操作的一组SQL语句，它在创建后以被有权用户在任何需要的地方调用。
  通过使用过程，不仅可以简化客户端应用程序的开发和维护，而且可以提高应用程序的运行性能。(假如某应用程序需要经常向某张表中插入数据，并且在插入数据时需要对数据进行检查验证，为了简化客户端的维护，可以使用存储过程)

创建储存过程

• 创建储存过程语法如下：

CREATE PROCEDURE 存储过程名字
(
[IN] 参数1参数类型,
OUT参数2 参数类型
) 
BEGIN
 程序代码块
END；


IN:代表输入参数(如果没有注明, 参数默认的类型为 in)
OUT:输出参数,不管有没有传值里面都是null
IN OUT;既可以做为输入参数，也可以做为输出参数
MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”

• 调用储存过程语法如下：

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

• 删除储存过程语法：

drop procedure 储存过程名称 ;

• 查看储存过程

show create procedure 过程名称;

参数列表有IN

• 1. 用储存过程来实现向t_user表中插入一条数据，首先创建一个储存过程。

DELIMITER // -- 修改sql语句的结束符为//
create PROCEDURE pro1 (
		in u_id int, -- -- 输入参数 用户id,
		in u_name varchar(30), --  输入参数用户name, 字符串类型必须要指定长度
		in u_age int -- -- 输入参数用户年龄 
		)
BEGIN
	-- 执行插入操作
	insert into t_user(id,name,age) values(u_id,u_name,u_age);
end;
 //
DELIMITER ; -- 结束符修改成;

• 2. 调用储存过程pro1
     

参数列表有OUT

• 定义一个过程传递两个参数，过程里面把这两个参数相加的结果返回出来

create PROCEDURE pro4(
in a int, -- 输入参数
  in b int, -- 输入参数
  out c int -- 输出参数
)
BEGIN
-- 结果相加赋值给c
set c = a+b;
END;

• 调用储存过程
  
• 把调用过程的返回值用@sum来接收，最后显示@sum的值就可以看到过程的返回值了
  @后面跟个名字，在MySQL中 表示自定义变量
  
• 根据员工编号查询员工名称，用储存过程来实现。

CREATE PROCEDURE pro2(
		IN u_id INT, -- 输入参数
	  OUT u_name VARCHAR(30) -- 输出参数
	)
BEGIN
	-- 执行查询语句,将name的值赋给u_name
select u.`name` into u_name from t_user u where u.id = u_id;
END;

• 调用储存过程
  

参数列表有INOUT

create PROCEDURE pro5(
	inout x int -- 即可作输入参数，也可作输出参数 
)
BEGIN
set x = x+10;
end

• 调用pro3储存过程
  

参数列表啥也没有

create PROCEDURE pro6(
	x int,
  inout y int,
  out z int
)
BEGIN
set z = x+y;
set y = x;
END

• 调用储存过程
  

函数

创建函数

• 创建函数语法如下：

CREATE  FUNCTION 函数名(参数1 参数类型)  
RETURNS 返回值类型  
BEGIN  
RETURN 返回值;  
END;

• 调用函数语法如下：

SELECT函数名称(参数列表)

• 删除函数语法：

DROP FUNCTION 函数名称

案例

• 创建一个函数要求传递两个参数，在函数中将两个参数相加并返回

create FUNCTION fun1(
	x int,-- 不写参数类型默认是IN
    y int
)returns INT -- 返回类型注意要加s
BEGIN
   return x+y; -- 函数一定要return
END;

• 调用函数
  
• 创建一个函数，要求传入一个id返回这个id对应的名称

CREATE FUNCTION fun2(
	p_id int
)returns varchar(30)
BEGIN
	return (select name from t_user where id = p_id);
end;

• 调用函数
  
• 创建一个函数，要求传入两个参数x,y进行比较，x>y返回x 否则返回y

create function fun3(
	x int,
	y int 
)returns int 
BEGIN
	if x > y THEN
		return x;
  else 
		return y;
	end if;
end;

• 调用函数