MySQL 学习记录系列（二）

妖狐艹你老母 2023-03-13 09:18 39阅读 0赞

**目录**

3、视图

3.1、视图概述

3.2、创建或者修改视图

3.3、查看视图

3.4、删除视图

4、存储过程和函数

4.1、存储过程和函数概述

4.2、创建存储过程

4.3、调用存储过程

4.4、查看存储过程

4.5、删除存储过程

4.6、语法

4.6.2、if条件判断

4.6.3、传递参数

4.6.4、case结构

4.6.5、while循环

4.6.6、repeat结构

4.6.7、loop语句

4.6.8、leave语句

4.6.9、游标/光标

4.7、存储函数

5、触发器

5.1、介绍

5.2、创建触发器

5.3、删除触发器

5.4、查看触发器

--------------------

## 3、视图 ##

### 3.1、视图概述 ###

> 视图（View）是一种虚拟存在的表。
> 
> 视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
> 
> 视图就是一条SELECT语句执行后返回的结果集。
> 
> 所以在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

> 视图相对于普通的表的优势主要包括以下几项：
> 
>  *  简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
>  *  安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
>  *  数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

### 3.2、创建或者修改视图 ###

创建视图的语法为：

> CREATE \[OR REPLACE\] \[ALGORITHM=\{UNDEFINED I MERGE | TEMPTABLE\}\]
> 
> VIEW view\_name \[(column\_list)\]
> 
> AS select\_statement
> 
> \[WITH \[CASCADED I LOCAL\] CHECK OPTION\]

**修改视图的语法为：（不建议更新视图）**

> ALTER \[ALGORITHM = \{UNDEFINED I MERGE I TEMPTABLE\}\]
> 
> VIEW view\_name \[(column\_list)\]
> 
> AS select\_statement
> 
> \[WITH \[CASCADED I LOCAL\] CHECK OPTION\]

> 选项：  
> WITH \[CASCADED | LOCAL\] CHECK OPTION 决定了是否允许更新数据，使记录不再满足视图的条件。
> 
> LOCAL：只要满足本视图的条件就可以更新。
> 
> CASCADED：必须满足所有针对该视图的所有视图的条件才可以更新。（默认机制）

示例，创建city\_country\_view视图，执行如下SQL：

> create or replace view city\_country\_view
> 
> as
> 
> select t.\*,c.country\_name from country c, city t where c.country\_id=t.country\_id;

查询视图

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70][]

### 3.3、查看视图 ###

> 从MySQL 5.1版本开始，使用SHOW TABLES命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的SHOW VIEWS命令。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70 1][]

> 同样，在使用SHOW TABLE STATUS命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70 2][]

如果需要查询某个视图的定义，可以使用SHOW CREATE VIEW命令进行查看：

> show create view view\_city\_country

### 3.4、删除视图 ###

> DROP VIEW \[IF EXISTS\] view\_name \[，view\_name\]...\[RESTRICT I CASCADE\]

示例，删除视图city\_country\_view：

> DROP VIEW city\_country\_view;

## 4、存储过程和函数 ##

### 4.1、存储过程和函数概述 ###

> 存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
> 
> 存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
> 
> 函数：是一个有返回值的过程；
> 
> 过程：是一个没有返回值的函数；

### 4.2、创建存储过程 ###

> CREATE PROCEDURE procedure\_name（\[proc\_parameter\[,...\]\]）  
> begin  
>         --sQL语句  
> end；

示例：

delimiter $
    create procedure pro_test1()
    begin 
    
    select 'Hello Mysql';
    
    end$
    
    delimiter;

DELIMITER

> 该关键字用来声明SQL语句的分隔符，告诉MySQL解释器，该段命令是否已经结束了，mysql是否可以执行了。
> 
> 默认情况下，delimiter是分号；。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

### 4.3、调用存储过程 ###

> call pro\_test1()

### 4.4、查看存储过程 ###

> \--查询db\_name数据库中的所有的存储过程
> 
> select name from mysql.proc where db='db\_name'；
> 
> \--查询存储过程的状态信息
> 
> show procedure status；
> 
> \--查询某个存储过程的定义
> 
> show create procedure test.pro\_test1\\G；

### 4.5、删除存储过程 ###

> DROP PROCEDURE \[IF EXISTS\] sp\_name;

### 4.6、语法 ###

> 存储过程是可以编程的，意味着可以使用变量，表达式，控制结构，来完成比较复杂的功能。

**DECLARE**

> 通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN..END块中。
> 
> DECLARE var\_name\[,...\] type \[ DEFAULT value\]

delimiter $ 
    
    create procedure pro_test2()
    begin 
    
    declare num int default 5; 
    
    select num+10; 
    
    end$
    
    delimiter ;

**SET**

> 直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下：
> 
> SET var\_name=expr \[, var\_name=expr\]..…

DELIMITER $
    CREATE PROCEDURE pro_test3)
    BEGIN 
    DECLARE NAME VARCHAR(20); 
    SET NAME='MYSQL'; 
    SELECT NAME; 
    END$
    DELIMITER;

也可以通过select...into 方式进行赋值操作：

DELIMITER $
    
    CREATE PROCEDURE pro_test5()
    
    BEGIN declare countnum int; 
    
    select count(*) into countnum from city; 
    
    select countnum;
    
    end$
    
    delimiter ;

### 4.6.2、if条件判断 ###

语法结构：

> if search\_condition then statement\_list  
> \[elseif search\_condition then statement\_list\]..…  
> \[else statement\_list\]  
> end if;

需求：

> 根据定义的身高变量，判定当前身高的所属的身材类型
> 
> 180及以上-------—->身材高挑  
> 170-180------—->标准身材  
> 170以下------->一般身材

示例：

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`()
    BEGIN
    	DECLARE height int(100) DEFAULT 175;
    	DECLARE description varchar(100) default '';
    	
    	if height >= 180	then 
    		set description='身材高挑';
    	elseif  height >= 170 and  height <  180 then 
    		set description='标准身材';
    	else
    		set description='有点矮';
    	end if;
    	
    	select CONCAT('身高', height, '对应的身材类型为', description);
    	
    END

### 4.6.3、传递参数 ###

语法格式：

> create procedure procedure\_name（\[in/out/inout\]  参数名参数类型）
> 
> IN：该参数可以作为输入，也就是需要调用方传入值，默认
> 
> OUT：该参数作为输出，也就是该参数可以作为返回值
> 
> INOUT：既可以作为输入参数，也可以作为输出参数

**IN-输入**

需求：

> 根据定义的身高变量，判定当前身高的所属的身材类型

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`(in height int)
    BEGIN
    	DECLARE description varchar(100) default '';
    	
    	if height >= 180	then 
    		set description='身材高挑';
    	elseif  height >= 170 and  height <  180 then 
    		set description='标准身材';
    	else
    		set description='有点矮';
    	end if;
    	
    	select CONCAT('身高', height, '对应的身材类型为', description);
    	
    END

**OUT-输出**

需求：

> 根据传入的身高变量，获取当前身高的所属的身材类型

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`(in height int(100),  description varchar(1024))
    BEGIN
    	
    	if height >= 180	then 
    		set description='身材高挑';
    	elseif  height >= 170 and  height <  180 then 
    		set description='标准身材';
    	else
    		set description='有点矮';
    	end if;
    	
    	
    END

调用：

> call pro\_test3(199, @description)；
> 
> select @description

> @description：这种变量要在变量名称前面加上“@”符号，叫做用户会话变量，代表整个会话过程都是有作用的，这个类似于全局变量一样。
> 
> @@global.sort\_buffer\_size：这种在变量前加上“@@”符号，叫做系统变量

### 4.6.4、case结构 ###

方式一：

> CASE case\_value
> 
> WHEN when\_value THEN statement\_list
> 
> \[WHEN when\_value THEN statement\_list\]..…
> 
> \[ELSE statement\_list\]
> 
> END CASE；

方式二：

> CASE WHEN search\_condition THEN statement\_list
> 
> \[WHEN search\_condition THEN statement\_list\]..…
> 
> \[ELSE statement\_1ist\]
> 
> END CASE；

需求：

> 给定一个月份，然后计算出所在的季度

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`(in mon int(100))
    BEGIN
    	DECLARE result varchar(1024) default '';
    	
    	CASE 
    	WHEN mon >= 1  and mon < 4 THEN
    		set result = '第一季度';
    	WHEN mon >= 4  and mon < 7 THEN
    		set result = '第二季度';
    	WHEN mon >= 7  and mon < 10 THEN
    		set result = '第三季度';
    	ELSE
    		set result = '第四季度';
    END CASE;
    
    	select result;
    	
    END

### 4.6.5、while循环 ###

语法结构：

> while search\_condition do
> 
> statement\_list
> 
> end while;

需求：

> 计算从1加到n的值

示例：

delimiter$
    create procedure pro_test8(n int)
    begin 
    
    declare total int default 0;
    
    declare start int default 0; 
    
    while start <= n do 
    
    set total=total+start; 
    
    set start=start +1; 
    
    end while;

### 4.6.6、repeat结构 ###

> 有条件的循环控制语句，当满足条件的时候退出循环。while是满足条件才执行，repeat是满足条件就退出循环。

语法结构：

> REPEAT
> 
> statement\_list
> 
> UNTIL search\_condition
> 
> END REPEAT;

需求：

> 计算从1加到n的值

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`(in n int(100))
    BEGIN
    	DECLARE total int(200) default 0;
    	DECLARE num int(200) default 1;
    	
    	REPEAT
    	set total = total+n;
    	set n = n - 1;
    UNTIL n=0 
    END REPEAT;
    
    	select total;
    	
    END

### 4.6.7、loop语句 ###

> LOOP实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用LEAVE语句实现，具体语法如下：
> 
> \[begin\_label:\]Loop  
> statement\_list  
> END LoOP \[end\_label\]

如果不在statement\_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环。

### 4.6.8、leave语句 ###

> 用来从标注的流程构造中退出，通常和BEGIN...END或者循环一起使用。
> 
> 下面是一个使用LOOP和LEAVE的简单例子，退出循环：
> 
> label ：可以为 loop 起一个别名

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`(in n int(100))
    BEGIN
    	DECLARE total int(200) default 0;
    	DECLARE num int(200) default 1;
    	
    	label: LOOP
    	  set total = total + n;
    		set n = n - 1;
    		
    
    	IF n < 0 THEN
    		LEAVE label; 
    	END IF; 
    END LOOP label;
    
    
    	select total;
    	
    END

### 4.6.9、游标/光标 ###

> 游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理。
> 
> 光标的使用包括光标的声明、OPEN、FETCH和CLOSE，其语法分别如下。
> 
> 等于说，游标就是用于存储结果集的

声明光标：

> DECLARE cursor\_name CURSOR FOR select\_statement;

OPEN光标：

> OPEN cursor\_name;

FETCH光标：

> CLOSE cursor\_name;

示例：

初始化脚本：

CREATE TABLE emp(
        id int(11) NOT NULL auto_increment,
        `name` varchar( 50) NOT NULL COMMENT '姓名',
        age int(11)comment '年龄',
        salary int(11)comment '薪水',
        primary key (id)
    )engine=innodb default charset=utf8;

insert into emp(id,name,age,salary)values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800)

\--查询emp表中数据，并逐行获取进行展示

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`()
    BEGIN
    	DECLARE e_id int(20);
    	DECLARE e_name VARCHAR(200);
    	DECLARE e_age int(100);
    	DECLARE e_salary int(11);
    	DECLARE emp_result CURSOR for select * from emp;
    	
    	OPEN emp_result;
    		FETCH emp_result into e_id,e_name,e_age,e_salary;
    		SELECT CONCAT('id=',e_id,'name=',e_name,'age=',e_age, 'salary=', e_salary);
            
            FETCH emp_result into e_id,e_name,e_age,e_salary;
    		SELECT CONCAT('id=',e_id,'name=',e_name,'age=',e_age, 'salary=', e_salary);
    	
    	CLOSE emp_result;
    
    END

循环游标

> DECLARE emp\_result CURSOR for select \* from emp;  
> DECLARE EXIT HANDLER FOR NOT FOUND set has\_data=0;
> 
> \-- 必须声明在游标之后  
> \-- 否则出现  
> ERROR 1337 (42000):Variable or condition declaration after cursor or handler declaration

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test3`()
    BEGIN
    	DECLARE e_id int(20);
    	DECLARE e_name VARCHAR(200);
    	DECLARE e_age int(100);
    	DECLARE e_salary int(11);
    	DECLARE has_data int DEFAULT 1;
    	
    	DECLARE emp_result CURSOR for select * from emp;
    	DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;
    	
    	
    	
    	OPEN emp_result;
    		REPEAT
    			FETCH emp_result into e_id,e_name,e_age,e_salary;
    			SELECT CONCAT('id=',e_id,'name=',e_name,'age=',e_age, 'salary=', e_salary);
    		
    			UNTIL has_data=0		
    		END REPEAT;
    	
    	CLOSE emp_result;
    
    END

### 4.7、存储函数 ###

语法结构：

> CREATE FUNCTION function\_name(\[ param type..\])  
> RETURNS type
> 
> BEGIN
> 
> ..............
> 
> END;

案例：

> 定义一个存储过程，请求满足条件的总记录数；

CREATE FUNCTION `func_test1`(`countryId` int)
    	RETURNS int
    BEGIN
    	DECLARE num int;
    	
    	select COUNT(*) into num  from city where country_id = countryId;
    	
    	RETURN num;
    END;

## 5、触发器 ##

### 5.1、介绍 ###

> 触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。
> 
> 触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
> 
> 使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。
> 
> 现在触发器还只支持行级触发，不支持语句级触发。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70 3][]

### 5.2、创建触发器 ###

语法结构：

> create trigger trigger\_name
> 
> before/after insert/update/delete
> 
> on tbl\_name
> 
> \[ for each row\]   -- 当前操作的是行级触发器
> 
> begin
> 
> .......
> 
> end
> 
> trigger\_stmt;

示例：

> 通过触发器记录emp表的数据变更日志，包含增加，修改，删除；

首先创建一张日志表：

create table emp_logs(
    	id int(11)not null auto_increment,
    	operation varchar(20)not null comment'操作类型,insert/update/delete',
    	operate_time datetime not null comment '操作时间',
    	operate_id int(11)not null comment '操作表的ID',
    	operate_params varchar(500) comment '操作参数',
    	primary key(id)
    )engine=innodb default charset=utf8;

创建insert型触发器，完成插入数据时的日志记录：

CREATE TRIGGER emp_insert_trigger AFTER INSERT ON emp FOR EACH ROW
    BEGIN
    	INSERT INTO emp LOGS( id,operation,operate time, operate id,operate params) 
    	VALUES(NULL, 'insert', now(), new.id, concat( 1插入后( id : ',new.id,', NAME : ',new.name,', age : ',new.age,', salary : ',new.salary,') "))；
    ends

### 5.3、删除触发器 ###

语法结构：

> drop trigger \[schema\_name.\]trigger\_name

如果没有指定 schema\_name，默认为当前数据库。

### 5.4、查看触发器 ###

可以通过执行 SHOW TRIGGERS命令查看触发器的状态、语法等信息。

语法结构：

> show triggers；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70]: /images/20230312/1759faf22b154ceaadd96a3c768d9d4a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70 1]: /images/20230312/82aa5505d623470984c37504770f230e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70 2]: /images/20230312/c29d377106a44045ae8e2bee06d97339.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2OTc1MzA3_size_16_color_FFFFFF_t_70 3]: /images/20230312/3281bcd095f343ada79884d6a5fb7070.png