mysql高阶语句（二）

---

一、 CREATE VIEW（视图）

可以被当作是虚拟表或存储查询

（1）视图跟表格的不同是，表格中有实际储存资料，而视图是建立在表格之上的一个架构，它本身并不实际储存资料。

（2）临时表在用户退出或同数据库的连接断开后就自动消失了，而视图不会消失。

（3）视图不含有数据，只存储它的定义，它的用途一般可以简化复杂的查询。比如你要对几个表进行连接查询，而且还要进行统计排序等操作，写SQL语句会很麻烦的，用视图将几个表联结起来，然后对这个视图进行查询操作，就和对一个表查询一样，很方便。

#语法：
CREATE VIEW “视图表名” AS “SELECT 语句”;
#示例1：
create view new_students as select a.fs fs,a.id id,b.name name from scores a inner join studedents b on a.id=b.id;

#删除视图
drop view new_students;

二、联集

联集，将两个SQL语句的结果合并起来，两个SQL语句所产生的栏位需要是同样的资料种类

2.1UNION
生成结果的资料值将没有重复，且按照字段的顺序进行排序

#语法：
[SELECT 语句 1] UNION [SELECT 语句 2];
#示例：搜索两个表中courses列的所有记录，先安装原有顺序列出第一个select语句中的数据，再将第二个表中的数据对照第一个表的数据去重后按源于的顺序列出
select id,name from students union select id,name from scores;

2.2UNION ALL
将生成结果的资料值都列出来，无论有无重复

#语法：
[SELECT 语句 1] UNION ALL [SELECT 语句 2];

三、存储过程

存储过程是组为了完成特定功能的SQL语句集合。

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

10.1 存储过程的优点

封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码

可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

提高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能

提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

使数据独立

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

10.2 创建、调用和查看存储的过程

1.创建存储过程
#语法：
CREATE PROCEDURE <存储过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

1. 过程名 存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称，即 db_name.sp_name。 需要注意的是，名称应当尽量避免选取与 MySQL 内置函数相同的名称，否则会发生错误。

2. 过程参数 存储过程的参数列表。其中，<参数名>为参数名，<类型>为参数的类型（可以是任何有效的 MySQL 数据类型）。当有多个参数时，参数列表中彼此间用逗号分隔。存储过程可以没有参数（此时存储过程的名称后仍需加上一对括号），也可以有 1 个或多个参数。 MySQL 存储过程支持三种类型的参数，即输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字标识。其中，输入参数可以传递给一个存储过程，输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

3. 过程体 存储过程的主体部分，也称为存储过程体，包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束 在 MySQL 中，服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而，在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些 SQL 语句如果仍以分号作为语句结束符，那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句，这样显然不行。

为解决以上问题，通常使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下： delimiter $ $

语法说明如下：
$$ 是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“?”或两个“￥”等。
当使用 DELIMITER 命令时，应该避免使用反斜杠“\”字符，因为它是 MySQL 的转义字符

成功执行这条 SQL 语句后，任何命令、语句或程序的结束标志就换为两个??
mysql > DELIMITER ??
若希望换回默认的分号“;”作为结束标志，则在 MySQL 命令行客户端输入下列语句即可
mysql > DELIMITER ;
注意：DELIMITER 和分号“;”之间一定要有一个空格
示例：

delimiter KaTeX parse error: Expected 'EOF', got '#' at position 6: #̲将语句的结束符号从分号;临时改… (可以是自定义)
create procedure proc1() #创建存储过程，过程名为proc， 不带参数
-> begin #过程体以关键字BEGIN开始
-> select * from students; #过程体语句（自己根据需求进行编写）
-> end $$ #过程体以关键字END结束
delimiter ; #将语句的结束符号恢复为分号

2.调用存储过程
#语法
call 过程名

#示例：
call proc1

3.查看存储过程
#查看某个存储过程的具体信息（如果在指定库中，库名可以省略）
SHOW CREATE PROCEDURE [数据库.] 存储过程名;
##示例1：
show create procedure hellodb.proc5\G;

##示例2：
show create procedure proc5\G;

4.删除存储过程
#语法
DROP PROCEDURE IF EXISTS 过程名;

#示例：
drop procedure if exists proc1;

10.3 存储过程的参数

IN输入参数: 表示调用者向过程传入值(传入值可以是字面量或变量)

OUT输出参数: 表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)

INOUT输入输出参数: 既表示调用者向过程传入值，又表示过程向调用者传出值(值只能是变量)

#示例：
delimiter $$createprocedureproc11(ininnamechar(40))->begin->select\ast fromstudentswherename=inname;->end$$
mysql> delimiter ;
call proc11(‘Xi Ren’);

10.4 存储过程的控制语句

10.4.1 条件控制语句 if-then-else … end if
delimiter KaTeX parse error: Expected 'EOF', got '#' at position 12: #̲修改默认结束符为
create procedure proc2(in pro int) #创建存储过程proc2，参数为pro，类型为int
-> begin #过程体以关键字begin开始
-> declare var int; #定义变量var为int类型
-> set var=pro*3; #设置变量var等于传入参数的3倍
-> if var>=10 then #如果变量var大于10，则执行下面过程体
-> update students set age=age+10; #设置表students中的age加10
-> else #如果变量var不大于10，则执行下面过程体
-> update students set age=age-10; #设置表students中的age减10
-> end if; #结束if语句
-> end $$ #创建存储过程结束
delimiter ; #重新修改默认结束符为原始的;
CALL proc2(4); #调用proc2存储过程，并传入参数4

10.4.2 循环语句 while … end while
#创建表
create table nametest(id int auto_increment primary key,name char(40),age int default 20);

delimiter KaTeX parse error: Expected 'EOF', got '#' at position 12: #̲修改默认结束符为
-> create procedure proc3() #创建存储过程
-> begin #过程体以关键字begin开始
-> declare i int; #定义变量i为int类型（长度最大为10）
-> set i = 1; #设置i = 1;
-> while i <= 100 #使用while循环，i要小于100
-> do insert into nametest(name,age) values
#满足条件则进行添加数据，内容为变量i
-> set i=i+1; #变量i每次循环后加1
-> end while; #结束while循环
-> end $$ #创建存储过程结束
delimiter ; #重新修改默认结束符为原始的;
CALL proc3; #调用存储过程