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

MySQL  学习 同时被 2 个专栏收录
3 篇文章 0 订阅
39 篇文章 0 订阅

一、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”; #查看是否插入记录

  • 1
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值