文章详细介绍了MySQL中的视图，包括创建、查询、修改和删除，强调了视图在数据安全和简化查询中的作用。同时，讨论了存储过程的概念、特点、创建、调用和删除，以及变量的使用，包括系统变量、用户定义变量和局部变量。此外，还涵盖了流程控制语句如if判断、循环和游标，以及触发器的应用，展示了如何通过触发器实现数据变更日志记录。
目录
视图
介绍
视图是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]查询
--查看创建视图语句
SHOW CREATE VIEW 视图名称;
--查看视图数据
SELECT * FROM 视图名称...;修改
--方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
--方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称]...案例
--创建视图
create or replace view stu_v_1 as select id, name from student where id <= 10;
--查询视图
show create view stu_v_1;
select * from stu_v_1;
select * from stu_v_1 where id < 3;
--修改视图
create or replace view stu_v_1 as select id, name, no from student where id <= 10;
alter view stu_v_1 as select id, name from student where id <= 10;
--删除视图
drop view if exists stu_v_1;视图的检查选项
当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED和LOCAL，默认值为CASCADED。
CASCADED
LOCAL
视图的更新
要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：
- 聚合函数或窗口函数(SUM()、MIN()、MAX()、COUNT()等)
- DISTINCT
- GROUP BY
- HAVING
- UNION或者UNION ALL
视图的作用
- 简单
视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
- 安全
数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
- 数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
案例
--根据如下需求，定义视图
--1.为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽手机号和邮箱两个字段。
create view tb_user_view as select id, name, profession, age, gender, status, createtime from tb_user;
select * from tb_user_view;
--2.查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操作，定义一个视图。
(1)select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;
(2)create view tb_stu_course_view as select s.name student_name, s.no student_no, c.name course_name from student s, student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;
(3)select * from tb_stu_course_view;存储过程
介绍
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。
特点
- 封装，复用
- 可以接收参数，也可以返回数据
- 减少网络交互，效率提升
基本语法
创建
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
--SQL语句
END;调用
CALL 名称([参数]);查看
--查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'XXX';
--查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称;删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符。
案例
--创建
create procedure p1()
begin
select count(*) from student;
end;
--调用
call p1();
--查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;
--删除
drop procedure if exists p1;变量
系统变量
是MySQL服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。
查看系统变量
--查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES;
--可以通过LIKE模糊匹配方式查找变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '......';
--查看指定变量的值
SELECT @@[SESSION | GLOBAL] 系统变量名;设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION | GLOBAL] 系统变量名 = 值;注意：
- 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
- mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中设置。
案例
--查看系统变量
show session variables;
show session variables like 'auto%';
show global variables like 'auto%';
select @@global.autocommit;
--设置系统变量
set session autocommit = 0;用户定义变量
是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用"@变量名"使用就可以。其作用域为当前连接。
赋值
SET @var_name = expr [, @var_name = expr]...;
SET @var_name ：= expr [, @var_name = expr]...;SELECT @var_name := expr [, @var_name := expr]...;
SELECT 字段名 INTO @var_name FROM 表名;使用
SELECT @var_name;案例
--变量：用户变量
--赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男', @myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
--使用
select @myname, @myage, @mygender, @myhobby;
select @mycolor, @mycount;
select @abc;局部变量
局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN...END块。
声明
DECLARE 变量名 变量类型[DEFAULT...];变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。
赋值
SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名...;案例
--变量：局部变量
--声明 - declare
--赋值 -
create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;
call p2();if判断
语法
IF 条件1 THEN
...
ELSEIF 条件2 THEN --可选
....
ELSE --可选
....
END IF;案例
--定义存储过程，完成如下需求
--根据定义的分数score变量，判定当前分数对应的分数等级。
--1.score >= 85分，等级为优秀。
--2.score >= 60分 且score < 85分，等级为及格。
--3.score < 60分，等级为不及格。
create procedure p3()
begin
declare score int default 58;
declare result varchar(10);
if score >= 85 then
set result := ‘优秀’;
elseif score >= 60 then
set result := ‘及格’;
else
set result := '不及格';
end if;
select result;
end;参数（IN、OUT、INOUT）
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
用法
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
--SQL语句
END;--定义存储过程，完成如下需求
--1.根据传入(in)参数score，判定当前分数对应的分数等级，并返回(out)。
--(1)score >= 85分，等级为优秀。
--(2)score >= 60分 且 score <85分，等级为及格。
--(3)score < 60分，等级为不及格。
create procedure p4(in score int, out result varchar(10))
begin
if score >= 85 then
set result := ‘优秀’;
elseif score >= 60 then
set result := ‘及格’;
else
set result := '不及格';
end if;
end;
call p4(68,@result);
select @result;
--2.将传入(in)的200分制的分数，进行换算，换算成百分制，然后返回分数-->inout。
create procedure p5(inout score double)
begin
set score := score * 0.5;
end;
set @score = 78;
call p5(@score);
select @score;case
语法一
CASE case_value
WHEN when_value1 THEN statement_list1
[WHEN when_value2 THEN statement_list2]...
[ELSE statement_list]
END CASE;语法二
CASE
WHEN seach_condition1 THEN statement_list1
[WHEN seach_condition2 THEN statement_list2]...
[ELSE statement_list]
END CASE;案例
create procedure p6(in month int)
begin
declare result varchar(10);
case
when month >= 1 and month <= 3 then
set result := '第一季度';
when month >= 4 and month <= 6 then
set result := '第二季度';
when month >= 7 and month <= 9 then
set result := '第三季度';
when month >= 10 and month <= 12 then
set result := '第四季度';
else
set result := '非法参数';
end case;
select concat('您输入的月份为：', month, ',所属的季度为：', result);
end;
call p6(4);while循环
while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。
语法
--先判定条件，如果条件为true，则执行逻辑；否则不执行逻辑
WHILE 条件 DO
SQL逻辑...
END WHILE;案例
--计算从1累加到n的值，n为传入的参数值。
--步骤：
--（1）定义局部变量，记录累加之后的值；
--（2）每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure p7(in n int)
begin
declare total int default 0;
while n > 0 do
set total := total + n;
set n := n - 1;
end while;
select total;
end;
call p7(10);repeat循环
repeat 是有条件的循环控制语句，当满足条件的时候退出循环。
语法
--先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环。
REPEAT
SQL逻辑...
UNTIL条件
END REPEAT;案例
--计算从1累加到n的值，n为传入的参数值。
--步骤：
--（1）定义局部变量，记录累加之后的值；
--（2）每循环一次，就会对n进行减1，如果n减到0，则退出循环
create procedure p8(in n int)
begin
declare total int default 0;
repeat
set total := total + n;
set n := n - 1;
until n <= 0;
end repeat;
select total;
end;
call p8(10);loop循环
LOOP实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用：
- LEAVE：配合循环使用，退出循环。
- ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[begin_label:]LOOP
SQL逻辑...
END LOOP [end_label];LEAVE label;--退出指定标记的循环体
ITERATE label;--直接进入下一次循环案例
--1.计算从1累加到n的值，n为传入的参数值。
--步骤：
--（1）定义局部变量，记录累加之后的值；
--（2）每循环一次，就会对n进行减1，如果n减到0，则退出循环------>leave XX
create procedure p9(in n int)
begin
declare total int default 0;
sum:loop
if n <= 0 then
leave sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;
call p9(10);
--2.计算从1到n之间偶数累加的值，n为传入的参数值。
--步骤：
--（1）定义局部变量，记录累加之后的值；
--（2）每循环一次，就会对n进行减1，如果n减到0，则退出循环 ------>leave XX
--（3）如果当次累加的数据是奇数，则直接进入下一次循环 ------>iterate XX
create procedure p10(in n int)
begin
declare total int default 0;
sum:loop
if n <= 0 then
leave sum;
end if;
if n % 2 = 1 then
set n := n - 1;
iterate sum;
end if;
set total := total + n;
set n := n - 1;
end loop sum;
select total;
end;
call p10(10);
cursor（游标）
游标(cursor)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下：
声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;打开游标
OPEN 游标名称;获取游标记录
FETCH 游标名称 INTO 变量[,变量];关闭游标
CLOSE 游标名称；案例
--定义存储过程，完成如下需求
--根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id，name，profession)中。
--逻辑：
--(1)声明游标，存储查询结果集
--(2)准备：创建表结构
--(3)开启游标
--(4)获取游标中的记录
--(5)插入数据到新表中
--(6)关闭游标
create procedure p11(in uage int)
begin
declare name varchar(100);
declare pro varchar(100);
declare u_cursor cursor for select name, profession from tb_user where age <= age;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into name,upro;
insert into tb_user_pro values(null, uname, upro);
end while;
close u_cursor;
end;
call p11(40);handler(条件出来程序)
条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。
语法
DECLARE handler_action HANDLER FOR condition_value [,condition_value]... statement;
handler_action
CONTINUE:继续执行当前程序
EXIT:终止执行当前程序
condition_value
SQLSTATE sqlstate_value:状态码，如02000
SQLWARNING:所有以01开头的SQLSTATE代码的简写
NOT FOUND:所有以02开头的SQLSTATE代码的简写
SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写案例
--定义存储过程，完成如下需求
--根据传入的参数uage，来查询用户表tb_user中，所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id，name，profession)中。
--逻辑：
--(1)声明游标，存储查询结果集
--(2)准备：创建表结构
--(3)开启游标
--(4)获取游标中的记录
--(5)插入数据到新表中
--(6)关闭游标
create procedure p11(in uage int)
begin
declare name varchar(100);
declare pro varchar(100);
declare u_cursor cursor for select name, profession from tb_user where age <= age;
declare exit handler for SQLSTATE '02000' close u_cursor;
drop table if exists tb_user_pro;
create table if not exists tb_user_pro(
id int primary key auto_increment,
name varchar(100),
profession varchar(100)
);
open u_cursor;
while true do
fetch u_cursor into name,upro;
insert into tb_user_pro values(null, uname, upro);
end while;
close u_cursor;
end;
call p11(40);存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法如下：
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic...]
BEGIN
--SQL语句
RETURN...;
END;
characteristic说明：
·DETERMINISTIC：相同的输入参数总是产生相同的结果
·NO SQL：不包含SQL语句
·READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句。案例
--计算从1累加到n的值，n为传入的参数值。
--步骤：
--（1）定义局部变量，记录累加之后的值；
--（2）每循环一次，就会对n进行减1，如果n减到0，则退出循环
create function fun1(n int)
returns int deterministic
begin
declare total int default 0;
while n > 0 do
set total := total + n;
set n := n - 1;
end while;
return total;
end;
select fun1(100);触发器
介绍
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
| 触发器类型 | NEW和OLD |
|---|---|
| INSERT型触发器 | NEW表示将要或者已经新增的数据 |
| UPDATE型触发器 | OLD表示修改之前的数据，NEW表示将要或已经修改后的数据 |
| DELETE型触发器 | OLD表示将要或者已经删除的数据 |
语法
创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW --行级触发器
BEGIN
trigger_stmt;
END;查看
SHOW TRIGGERS;删除
DROP TRIGGER [schema_name.]trigger_name;--如果没有指定schema_name,默认为当前数据库。案例
--定义触发器，完成如下需求
--通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加，修改，删除；
create table user_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型,insert/update/delete',
operate_time date time not null comment '操作时间',
operate_id int(11) not null comment '操作的ID',
operate_params varchar(500) comment '操作参数',
primary key('id')
)engine = innodb default charset = uft8;
--插入数据触发器
create trigger tb_user_insert_trigger
after insert
on tb_user fro each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values (null, 'insert', now(), new.id, concat('插入的数据内容为:id=',new.id,',name=',new.name, ', phone= ', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;
--查看
show triggers;
--删除
drop trigger tb_user_insert_trigger;
--插入数据到tb_user
insert into tb_user(id, name,phone,email,profession,age,gender,status,createtime)values(25,'二皇子','18809091212','erhuangzi@163.com','软件工程',23,'1','1',now());
--修改数据触发器
create trigger tb_user_update_trigger
after update
on tb_user fro each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values (null, 'update', now(), new.id, concat('更新之前的数据:id=',old.id,',name=',old.name, ', phone= ', OLD.phone, ', email=', OLD.email, ', profession=', OLD.profession,'| 更新之后的数据:id=',new.id,',name=',new.name, ', phone= ', NEW.phone, ', email=', NEW.email, ', profession=', NEW.profession));
end;
show triggers;
update tb_user set age = 20 where id = 23;
update tb_user set age = 20 where id <= 5;
--删除数据触发器
create trigger tb_user_delete_trigger
after delete
on tb_user fro each row
begin
insert into user_logs(id, operation, operate_time, operate_id, operate_params) values (null, 'delete', now(), old.id, concat('更新之前的数据:id=',old.id,',name=',old.name, ', phone= ', OLD.phone, ', email=', OLD.email, ', profession=', OLD.profession));
end;
show triggers;
delete from tb_user where id = 25;
1778
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
