MySQL 学习八——视图、存储过程

一、mysql视图
1.1 什么是mysql视图
——虚拟表
——内容与真实的表相似，有字段有记录
——视图并不在数据库中以存储的数据形式存在
——行和列的数据来自定义视图时查询所引用的基表，并且在具体引用视图时动态生成
——更新视图的数据，就是更新基表的数据
——更新基表数据，视图的数据也会跟着改变

1.2 视图优点
简单——用户不需要关系视图中的数据如何查询获得；
——视图中的数据已经是过滤好的符合条件的结果集
安全 ——用户只看到视图中的数据
数据独立 ——一旦视图结构确定，可以屏蔽表结构对用户的影响

1.2 视图使用限制
——不能在视图上创建索引
——在视图的FROM子句中不能使用子查询
——以下情形中的视图是不可更新的：
-----包含以下关键字的SQL语句：聚合函数（SUM \MIN \MAX \COUNT等），DISTINCT
\GROUP BY \ HAVING \ UNION 或UNION ALL
——常量视图、JOIN \ FROM 一个不能更新的视图
——WHERE子句的子查询引用了FROM子句中的表
——使用了临时表

1.4 视图的基本使用
创建视图:
—— create view 视图名称 as SQL查询；
——create view 视图名称（字段名列表） as SQL查询；
（注意：在视图表中不定义字段名的话，默认使用基表的字段名，
若定义字段名的话，视图表中的字段必须和基表的字段个数相等）

查看视图:
查看当前库下所有的表的状态信息：
—— show table status;
——show table status where comment=“view”\G;
查看创建视图具体命令：
——show create view 视图名；

使用视图：（对视图操作即为基本操作，反之亦然）
查询记录：-select 字段名列表 from 视图名 where 条件；
插入记录：-insert into 视图名（字段名列表） values(字段名列表)；
更新记录：-update 视图名 set 字段名=值 where 条件；
删除记录：-delete from 视图名 where 条件；

删除视图
—— -drop view 视图名；

1.5 AS定义视图中字段名称（创建视图的完全格式）
命令格式：
-create
[or replace] ： 替换试图
定义处理视图的方式：
[algorithm={undefined | merge | temptable}]
[definer = {user | surrent_user}]
[sql security {definer | invoker}]
view view_name [(column_list)]
as select_statement
[with [cascaded |local] check option]

设置字段别名：
关联查询的视图默认不允许修改视图字段的值，
视图中的字段名不可以重名所以要定义别名
- create view 视图名
as 用来设置别名的
select 表别名.源字段名 as 字段别名
from 源表名 表别名 left join 源表名 表别名
on 条件；
or replace 替换原先创建的同名视图，起到修改视图的作用

1.6 OR REPLACEX 重要选项的使用
——create or replace view 视图名 as select 查询；
创建时，若视图已存在，会替换已有的视图。

algorithm
——alogrithm = {undefined | merge temptable } ——定义处理视图的方式
–merage ,替换方式：
– 视图名直接使用视图公式替换掉，把视图公式合并到了select中；
–temptable ,具体化方式 ：
– 先得到视图的执行结果，该结果形成一个中间的结果暂时存在内存中，
之后外面的select 语句就调用了这些中间结果；
–undefined ,未定义即默认模式：
– algorithm 选项的值是undenfined表示使用的是merage 替换方式。

with check option 当创建的视图是根据另一个视图定义时，对视图更新：
local 和 cascaded 关键字决定检查的范围
–local（默认值） 仅检查当前视图的限制
–cascaded 同时要满足基表的限制
格式：mysql> create view 视图名 as
-> select x,x,x from 表/视图
-> with [cascaded | local] check option;

二、mysql存储过程
存储过程，相当于是MySQL语句组成的脚本
——指的是数据库中保存的一系列SQL命令的集合
——可以在存储过程中使用变量/条件判断/流程控制等
——存储过程，就是MySQL中的脚本

存储过程的有点：
——提高性能
——可减轻网络负担
——可以防止对表的直接访问
——避免重复编写SQL操作

2.1 基本使用：创建 查看 调用 删除
创建存储过程：
语法格式：- > **delimiter //
create procedure 存储过程名(参数类型 变量名 类型)
begin
… … 功能代码
end
// 结束存储过程
delimiter ; ** （注意：delimiter 与 ； 之间有空格，改行要求缺少就会一致在脚本中）
（delimiter 关键字用来指定存储过程的分隔符；因为MySQL默认以“；”为分隔符，
若没有指定分隔符，编译器会把存储过程当成SQL语句进行处理，从而执行出错）

查看存储过程：方法一：—mysql >show procedure status\G;
方法二：—mysql > select db,name,type from mysql.proc where name=“存储过程名”；

调用/删除存储过程：
调用存储过程： call 存储过程名（）；
——存储过程没有参数时，（）可以省略
——存储过程有参数时，调用时必须传给参数；
删除存储过程： drop procedure 存储过程名； （注意：后面不用加括号）

2.2 存储过程调用类型： in out inout

格式：create procedure 存储过程名（{in | out | inout} 参数变量名 参数变量类型 ）
in 输入参数：作用是给存储过程传值，必须在调用存储过程中赋值，在存储过程中该参数变量的值不允许修改；
默认类型是 :in
out 输出参数 该值可在存储过程内部被改变，并可返回。
inout 输入/输出参数 ： 调用时指定，并且可被改变和返回
-create procedure 名称（ 类型 参数变量名 参数变量类型 ，类型 参数变量名 参数变量类型）
注意：调用了几个变量名，最后call时候就要给与同等数量对应的变量值。
——调用类型之间用“，” 逗号间隔；
——调用变量时，有@符号的变量 例如@x 调用的是用户自定义的变量；
——没有@符号时，例如 x 调用的是存储过程的参数变量

2.3、mysql 变量类型： 会话变量 全局变量 用户变量 局部变量
会话变量和全局变量 叫系统变量，使用set命令定义，
全局变量的修改会影响整个服务器，但是对会话变量的修改，只会影响当前的会话。
—— > select @@hostname; （系统变量用@@，用户自定义变量用@）
用户变量 ：在客户端连接到数据库服务的整个过程中都是有效，当前连接断开后所有用户变量失效。
定义 set @变量名=值；
输出 select @变量名；
局部变量 ：存储过程中的begin/end 。其有效范围仅限于该语句执行中，语句块执行完毕后，变量失效。
declare 专门用来定义局部变量，局部变量不需要@。

mysql > show global variliables; //查看全局变量
mysql > show session variables; //查看会话变量
mysql > set session sort_buffer_size = 40000; //设置会话变量
mysql > show session variables like “sort_buffer_size” //设置会话变量
mysql> show global variables like “关键字”； 查看全局变量
mysql> set @y=3; 用户自定义变量，直接赋值；
mysql> select max(uid) into @y from usertab; 使用sql命令查询结果赋值

2.4、 mysql运算符号 : + - * / DIV %
—— + 加法运算
—— - 减法运算
—— * 乘法运算
—— / 除法运算
—— DIV （必须大写DIV）整除运算
—— % 取余

2.5 条件判断符号： （> >= < <= = != or and ! like regexp ）
数值比较
—— =
—— > 、 >=
—— <、 <=
—— !=
—— between … and …

条件测试：（逻辑比较、范围、空、非空、模糊、正则）
or 、 and、 ! 逻辑或、逻辑与、逻辑非
in … 、 not in 在什么范围内，不在什么范围内
is null 字段的值为空
in not null 字段值不为空
like 模糊匹配
regexp 正则匹配

2.6 流程控制：
单分支选择结构——当“条件成立”时执行命令序列
if 顺序结构
if 条件判断 then
代码
…
end if ;

双分支选择架构——当“条件成立”时，执行代码1；否则执行代码2；
if 条件判断 then
代码
…
else
代码
…
end if;

循环结构
while 条件式循环——反复测试条件，只要成立就执行命令序列
while 条件判断 do
循环体
end while;
loop 死循环——无条件，反复执行某一段代码
loop
循环体
end loop;
repeat 条件式循环——当条件成立结束循环
repeat
循环体
until 条件判断
end repeat;

循环控制参数

例子：准备一张表，把系统用户信息存储到db9库下的usertab表里，并在多有列前添加行号字段id，要求自动增长。
192.168.4.51 导入数据
]# cp /etc/passwd /var/lib/mysql-files/
]# mysql -u root -p
mysql> show variables like “secure_file_priv”;
mysql> create database db9;
mysql> create table db9.usertab(
-> username char(50),
-> password char(1),
-> uid int(2),
-> gid int(2),
-> comment char(100),
-> homedir char(100),
-> shell char(50)
-> );
mysql> load data infile “/var/lib/mysql-files/passwd” into table db9.usertab fields terminated by “:” lines
terminated by “\n”;
mysql> alter table db9.usertab add id int(3) primary key auto_increment first;
mysql> select * from db9.usertab;
mysql> desc db9.usertab;

创建、使用视图的基本使用：
mysql> use db9;
mysql> select database(); 查看当前在那个库内；
mysql> create view v1 as select * from usertab;
mysql> show tables;
mysql> desc v1;
mysql> select * from v1;
]# ls /var/lib/mysql/db9 （v1.frm 视图文件，视图没有真正的数据db）
mysql> select * from v1 where username=“root”;
mysql> update v1 set username=“admin” where username=“root”; 修改视图，基表里的数据也会发生变动；
mysql> select * from usertab where username=“admin”;
mysql> insert into v1 (username ,uid) values(“jerry”,988); 插入记录，基表也会发生改变
mysql> select * from v1 where username=“jerry”;
mysql> select * from usertab where username=“jerry”;
mysql> create view v2 as select uid ,shell from usertab;
mysql> desc v2;
mysql> select * from v2;
mysql> create view v3 (vuid,vshell) as select uid,shell from usertab; 视图表中的字段必须和基表的字段个数相等；
mysql> select * from v3;
mysql> show tables;

查看视图：
mysql> show table status\G; 显示当前库所有表状态信息，comment 是view 则说明该表是视图。
mysql> show table status where comment=“view”\G; 显示当前库下的视图
mysql> show create view v3\G; 查看是基于哪个基表创建的视图

删除视图：
mysql> drop view v3;
mysql> show create view v3\G; （Table ‘db9.v3’ doesn’t exist）视图删除后对应的文件也没有了。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AS定义视图中字段名称：
mysql> create table user2 select username,uid,gid from usertab limit 3; 快速建表
mysql> create table info select username,uid,gid,homedir,shell from usertab limit 5;
mysql> show tables;
mysql> select * from user2;
mysql> select * from info;
mysql> select * from user2 left join info on user2.uid=info.uid; （有重复的字段名）
（username | uid | gid | username | uid | gid | homedir | shell）
mysql> create view v4 as select * from user2 left join info on user2.uid=info.uid;
ERROR 1060 (42S21): Duplicate column name ‘username’ 提示错误，有重复字段名
mysql> create view v5 as select a.username as aname,b.username as bname,a.uid as auid ,b.uid as buid
from user2 a left join info b on a.uid=b.uid; 设置基表同一字段对应的不同视图里面字段名的别名。
mysql> select * from v5;
mysql> show create view v1\G; 查看视图v1
mysql> create or replace view v1 替换 v1,起到修改视图的作用
-> as select a.username as aname,b.username as bname,a.uid as auid ,b.uid as buid
->from user2 a left join info b on a.uid=b.uid;
mysql> show tables;
mysql> desc v1;
mysql> show create view v1\G;
mysql> select * from v1;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
local 和 cascaded检查范围的选择：
mysql> create table a select * from usertab where uid<20; 快速建表a;
mysql> select * from a;
mysql> create view va1 as 基于a表根据where条件创建视图
-> select * from a
-> where uid <10 with local check option;
mysql> create view va2 as 基于 va1表根据where条件创建视图
-> select * from va1
-> where uid >=5 with local check option;
mysql> select * from va1;
mysql> select * from va2;
mysql> create view va3 as 基于a 表 和va1视图 上根据where条件创建视图
-> select * from va1 where uid >=5
-> with cascaded check option;
mysql> update va3 set uid=15 where username=“sync”; 受制于基表里条件的限制
ERROR 1369 (HY000): CHECK OPTION failed ‘db9.va3’
mysql> update va3 set uid=7 where username=“sync”;
mysql> select * from va3;
mysql> update va1 set uid=8 where username=“admin”;
mysql> select * from va1;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
存储过程例子：
创建名称为say的存储过程，功能查看库下db9.usertab表的前10条记录信息。
mysql> delimiter //
mysql> create procedure say()
-> begin
-> select * from db9.usertab where id <=10;
-> end
-> //
mysql> delimiter ; （delimiter 与 ； 之间有空格）
调用存储过程：
mysql> call say;
查看存储过程：mysql >show procedure status\G;
mysql> select db,name,type from mysql.proc where name=“say”\G；
mysql> select name from mysql.proc where name=“say”\G；
删除存储过程：
mysql> drop procedure say(); （加了括号出错了）
ERROR 1064 (42000): You have an error in your SQL syntax;
mysql> drop procedure say;
Query OK, 0 rows affected

格式：create procedure 存储过程名（{in | out | inout} 变量名 变量名类型 ） 例子：

1. mysql> delimiter //
   mysql> create procedure say01(in username char(10))
   -> begin
   -> select username;
   -> select * from db9.usertab where username=username;
   -> end
   -> //
   mysql -> delimiter ;
   mysql> call say01(“tom”);
   mysql> call say01(“root”);

2)mysql > delimiter //
mysql >create procedure say1(in username char(20), in x int(2) )
-> begin
-> select * from db9.usertab where username="@username" and uid=@x;
-> end
-> //
mysql -> delimiter ;

3)mysql > delimiter //
mysql >create procedure say2( out number int(2) )
-> begin
-> select count(id) into @number from db9.usertab where shell !="/bin/bash";
-> select @number;
-> end
-> //
mysql -> delimiter ;

mysql> delimiter //
mysql> create procedure say3()
-> begin
-> declare x int default 9; 定义局部变量并赋值
-> select x;
-> select sum(uid) into x from db9.usertab where uid <=100;
-> select x;
-> end
-> //
mysql> delimiter ;
mysql> call say3; 创建时没有设置参数，调用时也就可以不加（）。
mysql> select @x;
计算：
mysql> set @x=10/3;
mysql> select @x;
mysql> set @z=10 DIV 3;
mysql> select @z;
mysql> set @var1=10-3;
mysql> select @var1;
mysql> set @var2=10+3;
mysql> select @var2;
例子：
mysql> create procedure say4(
-> in bash char(20),in nologin char(25),out x int ,out y int)
-> begin
-> declare z int;
-> set z=0;
-> select count(username) into @x from db9.usertab where shell=bash;
-> select @x;
-> select count(username) into @x from db9.usertab where shell=nologin;
-> select @y;
-> set z=@x+@y;
-> select z;
-> end
-> //
mysql> delimiter ;
mysql> call say4("/bin/bash","/sbin/nologin",@z,@j);
mysql> call say4("/bin/bash","/sbin/nologin",@j,@z);
（ 调用类型一旦设置了，call 时一定要给与参数变量，至于参数变量是什么无所谓，等同于占位。）
循环结构：
单分支：m+ysql> delimiter //
mysql> create procedure say6(in x int(2))
-> begin
-> if x<=10 then
-> select * from db9.usertab where id<=x;
-> end if;
-> end
-> //
mysql> delimiter ;
mysql> call say6(11); 赋值不满足条件，直接结束；
mysql> call say6(3);

双分支mysql> delimiter //
mysql> create procedure p2(in x int(2))
-> begin
-> if x != 0 then
-> select @x;
-> select * from db9.usertab where id<=x;
-> else
-> set @x=1;
-> select * from db9.usertab where id=x;
-> end if;
-> end
-> //
mysql> delimiter ;
mysql> call p1(0);
mysql> call p1(1);
mysql> call p1(2);
mysql> set @z=4;
mysql> call p1(@z);
例子：在testdb库下创建存储过程p2 功能，根据用户输入的数字显示db9 库下的usertab表对应的记录的信息。若在调用存储过程p2 时不给查看行号，默认查看表中的第一条记录的内容。
mysql> create database testdb;
mysql> use testdb;
——( mysql> delimiter //
mysql>create procedure p5(in linenum int(2))
-> begin
-> if linenum is null then
-> set @linenum = 3;
-> select @linenum;
-> select linenum;
->end +if;
->end
->//
mysql> delimiter ;
mysql> call p5(null);
mysql> call p5(2);
验证 ——调用变量时，有@符号的变量 例如@x 调用的是用户自定义的变量；
——没有@符号时，例如 x 调用的是存储过程的参数变量 ）

mysql> delimiter //
mysql>create procedure p6(in linenum int(2) ）
-> begin
-> if linenum is null then
-> set @linenum = 1;
-> select * from db9.usertab where id = @linenum;
->else
-> select linenum;
-> select * from db9.usertab where id = linenum;
->end if;
->end
->//
mysql> delimiter ;
mysql> call p6(7);

while 循环：+++++++++++++++++++++++++++++++++++++++++++++++++++++++
例子：输出数字1到5：
mysql> delimiter //
mysql> create procedure p7()
-> begin
-> declare i int; （局部变量只在语句执行过程中有效）
-> set i=1;
-> while i<=5 do
-> select i;
-> set i=i+1;
-> end while;
-> end
-> //
mysql> delimiter ;
mysql> call p7();
输出5：mysql> delimiter //
mysql> create procedure p8()
-> begin
-> declare i int;
-> set i=1;
-> while i<=4 do
-> set i=i+1;
-> end while;
-> select i;
-> end
-> //
mysql> delimiter ;
mysql> call p7();
loop死循环++++++++++++++++++++++++++++++++++++++++++++++++++
mysql> delimiter //
mysql> create procedure p9()
-> begin
-> declare i int;
-> set i=1;
-> loop
-> select i;
-> set i=i+1;
-> end loop;
-> end
-> //
mysql> delimiter ;
mysql> call p9(); #不ctrl +c 结束，会一直继续下去。
++++++定义标签名称，条件匹配时结束循环+++++++++++++++
delimiter //
create procedure p10()
begin
declare i int;
set i=0;
loab1:loop
set i=i+1;
if i = 5 then
ITERATE loab1;
end if;
if i = 11 then
LEAVE loab1;
end if;
select i;
end loop;
end
//
delimiter ;

mysql> drop procedure say;
mysql> delimiter //
mysql> create procedure say()
-> begin
-> declare i int;
-> set i=1;
-> loab1:loop #给loop循环定义标签名称为 loab1
-> select i;
-> set i=i+1;
-> if i=3 then #变量i的值加到3的时候 结束循环
-> LEAVE loab1;
-> end if;
-> end loop;
-> end
-> //
mysql> delimiter ;
mysql> call say();
---------------------------使用repeat循环输出数字1到5-----------------------
delimiter //
create procedure p11()
begin
declare i int;
set i=1;
repeat
select i;
set i=i+1;
until i=6
end repeat;
end
//
delimiter ;
call p11();
drop procedure say;
delimiter //
create procedure say()
begin
declare i int;
set i=1;
repeat
select i;
set i=i+1;
until i=6
end repeat;
end
//
delimiter ;
-------------------一起向表中插入10条记录--------------------
mysql> delimiter //
mysql> create procedure adddata()
-> begin
-> declare n int default 0;
-> while n<10
-> do
-> insert into studydb.user(name,uid) values(“bob”,888);
-> set n = n+1;
-> end while;
-> end
-> //
mysql> call adddata();
mysql> select name,uid from user where name=“bob” and uid=888;
----------------------自定义插入记录的行数--------------------------
mysql> drop procedure say;
mysql> delimiter //
mysql> create procedure say(in num int(2) )
-> begin
-> declare i int;
-> set i=1;
-> while i <= num do
-> insert into userdb.user(name,gid)values(“bob”,i);
-> set i=i+1;
-> end while;
-> end
-> //
mysql> delimiter ;
mysql> call say(3);
mysql> select * from user where name=“bob”; #查看是否插入记录