MySQL 学习记录系列（二）-阿里云开发者社区

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;

查询视图

3.3、查看视图

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

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

如果需要查询某个视图的定义，可以使用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块中。

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来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。

现在触发器还只支持行级触发，不支持语句级触发。

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；

MySQL 学习记录系列（二）

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、查看触发器

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL 学习记录系列（二）

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、查看触发器

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像