视图介绍
什么是视图(view)
- 虚拟表
- 内容与真实的表相似,有字段有记录
- 视图并不在数据库中以存储的数据形式存在
- 行和列的数据来自定义视图时查询所引用的基表,并且在具体引用视图时动态生成
- 更新视图的数据,就是更新基表的数据
- 更新基表数据,视图的数据也会跟着改变
视图优点
- 简单
- 用户不需要关心视图中的数据如何查询获得
- 视图中的数据已经是过滤好的符合条件的结果集
- 安全
- 用户只能看到视图中的数据
- 数据独立
- 一旦视图结果确定,可以屏蔽表结构对用户的影响
视图使用限制
- 不能在视图上创建索引
- 在视图的from子句中不能使用子查询
- 以下情形中的视图是不可更新的
包含以下关键字的SQL语句:聚合函数(SUM、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL - 常量视图、JOIN、FROM一个不能更新的视图
- WHERE子句的子查询引用了FROM子句中的表
- 使用了临时表
视图的基本使用
创建视图
- 语法格式
- create view 视图名称 as SQL查询;
- create view 视图名称(字段名列表) as SQL查询;
注:在视图中不定义字段名的话,默认使用基表的字段名,若定义字段名的话,视图表中的字段必须和基表的字段个数相等
mysql> create view v1 as select name,uid from userdb; //创建视图v1
mysql> create view(NAME,UID,SHELL) v2 as select name,uid from userdb; //创建视图v2
mysql> create view v3 as select * from v1; //创建视图v3
对视图做修改删除数据,基表也会跟着改变,对基表进行修改删除操作,视图也会跟着改变
使用视图
- 查询视图
select 字段名列表 from 视图名 where 条件; - 插入记录
insert into 视图名(字段名列表) values(字段值列表); - 更新记录
update 视图名 set 字段名=值 where; - 删除记录
delete from 视图名 where 条件; - 删除视图
语法格式
drop view 视图名;
查看视图
查看当前库下所有表的状态信息
- show table status;
- show table status where comment=“view”\G;
查看创建视图具体命令
- show create view 视图名;
mysql> show create view v1\G;
视图进阶
创建视图的完全格式
- 命令格式
create
[or replace]
[algorithm={undefined|merge|temptable}]
[definer={user|current_user}]
[sql security{definer|invoke}]
view view_name [(column_list)]
as select_statement
[with[cascaded|local] check option]
设置字段别名
视图中的字段名不可以重复 所以要定义别名
创建视图时定义字段别名
create view 视图名
as
select 表别名.源字段名 as 字段名
from 源表名 表别名 left join 源表名 表别名
on 条件;
mysql> create view v4
-> as
-> select a.name x1 , a.uid x2, a.shell x3,
-> b.name x4,b.uid x5,b.password x6,b.homedir x7
-> from
-> t2 a left join t3 b on a.uid=b.uid;
重要选项说明
-
or replace
-
create or replace view 视图名 as select 查询
创建时,若视图已存在,会替换已有的视图 -
algorithm 创建视图时的算法
[algorithm={undefined|merge|temptable}] //分别是未定义、替换方式(默认),具体化方式
mysql> create algorithm=temptable view v5 SQL查询语句
-
with check option 限制视图操作
-
local 和 cascaded关键字决定检查的范围
-
local仅检查当前视图的限制
-
cascaded 同时满足基表的限制(默认值)
创建一个t5表
mysql> create table t5 select name,uid,gid,shell from userdb where uid>=20 and uid<=1000;
mysql> create or replace view v6 as select * from t5 where uid<=500;
mysql> create or replace view v6 as select * from t5 where uid<=500 with local check option; //加local选项,视图在执行操作的时候,受本地uid<=500的条件限制
mysql> update v6 set uid=600 where name="nobody"; //报错条件受限
mysql> create view v9 as select * from userdb where gid<=100 with cascaded check option;
mysql> update v9 set gid=101 where name="root";
ERROR 1369 (HY000): CHECK OPTION failed 'db9.v9'
mysql> create view v10 as select * from t5 where gid>=100 with cascaded check option;
mysql> create view v11 as select * from v10 where gid > 30 with local check option;
视图v11既受基表v10的gid>=100的限制,也受本身gid>30的条件限制
存储过程概述
存储过程介绍
- 存储过程,相当于是MySQL语句组成的脚本
(1) 指的是数据库保存的一系列SQL命令集合
(2)可以在存储过程中使用变量、条件判断、流程控制等
存储过程优点
- 提高性能
- 可减轻网络负担
- 可以防止对表的直接访问
- 避免重复编写SQL操作
基本使用
- 语法格式
mysql> delimiter // 这个写法是定义以//做为语句的结束符
mysql> create procedure 名称()
-> begin
-> .. ..功能代码
-> end
-> //
mysql> delimiter ; 把语句的结束符改回来
- delimiter关键字用来指定存储过程的分隔符(默认为;)
- 若没有指定分隔符,编译器会把存储过程当成SQL语句进行处理,从而执行出错
创建存储过程:
mysql> delimiter //
mysql> create procedure test()
-> begin
-> select * from db9.userdb;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
查看存储过程
- 方法1
mysql> show procedure status; - 方法2
mysql> select db,name,type from mysql.proc where name=“存储过程名”;
mysql> select db,name,body from mysql.proc where type="PROCEDURE" and name="test"\G; //查看存储过程
调用/删除存储过程
- 调用存储过程
call 存储过程名; - 删除存储过程
drop procedure 存储过程名;
调用存储过程
mysql> call test()
存储过程进阶
变量类型
- 变量类型:
名称 | 描述 |
---|---|
会话变量 | 会话变量和全局变量叫系统变量,使用set命令定义 |
全局变量 | 全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话。 select @@hostname; |
用户变量 | 在客户端连接到数据库服务的整个过程中都是有效的,当当前连接断开后所有用户变量失效 定义 set @变量名=值 输出 select @变量名; |
局部变量 | 存储过程中的begin/end,其有效范围仅限于该语句块中,语句块执行完毕后,变量失效,declare专门用来定义局部变量 |
注意:局部变量 和 参数变量 调用时 变量名前面不需要加@
- 会话变量
mysql> show session variables;//查看会话变量
mysql> set session sort_buffer_size=40000;//设置会话变量
mysql> show session variables like "sort_buffer_size"; //查看会话变量
- 全局变量
mysql> show global variables;//查看全局变量
mysql> select @@version; //查看某个全局变量的值
mysql> show global variables like "%关键字%";// 查看全局变量
- 用户变量
mysql> set @y=3;
mysql> set @name="bob";
mysql> select @y,@name;
- 局部变量(在存储过程里定义)
mysql> delimiter //
mysql> create procedure p2()
-> begin
-> declare x int;
-> declare y int;
-> set x=99;
-> set y=11;
-> select x,y;
-> end
mysql> delimiter ;
mysql> call p2;
使用查询结果给变量赋值
(1)
mysql> select count(name) into @变量名 from db9.userdb;
mysql> select count(name) into @x from db9.userdb;
mysql> select @x;
(2)
mysql> delimiter //
mysql> create procedure p3()
-> begin
-> declare x int;
-> declare y int;
-> select count(name) into x from db9.userdb where gid<=1000;
-> select count(name) into y from db9.userdb where gid>1000;
-> select x,y;
-> end
-> //
mysql> delimiter ;
mysql> call p3;
参数类型
- 调用参数时,名称前也不需要加@
create procedure 名称(
类型 参数名 数据类型,
类型 参数名 数据类型
)
算术运算
mysql> delimiter //
mysql> create procedure p4()
-> begin
-> declare x int;
-> declare y int;
-> declare z int;
-> select count(*) into x from db9.userdb where gid<=500;
-> select count(*) into y from db9.userdb where gid>500;
-> set z=x+y;
-> select x,y,z;
-> end
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call p4;
关键字 | 名称 | 描述 |
---|---|---|
in | 输入参数 | 作用是给存储过程中传值,必须在调用存储过程时赋值,在存储过程中该参数的值不允许修改;默认类型是in |
out | 输出参数 | 该值可以在存储过程内部被改变,并可返回 |
inout | 输入/输出参数 | 调用时指定,并且可被改变和返回 |
输入用户名称,查找该用户在userdb表中的信息
mysql> delimiter //
mysql> create procedure p9(in username char(10))
-> begin
-> selec * from user where name=username;
-> end
-> //
mysql> delimiter ;
mysql> call p9("admin");
输出x+y的值z
mysql> delimiter //
mysql> create procedure p10(in x int, in y int,out z int)
-> begin
-> set z = x + y;
-> end
-> //
mysql> delimiter ;
mysql> call p10(19,37,@w);
mysql> select @w;
输出符合条件的i+j的值
mysql> create procedure p11(in uid_num int,in shell_name char(50),out x int)
-> begin
-> declare i int;
-> declare j int;
-> select count(shell) into i from userdb where shell=shell_name;
-> select count(uid) into j from userdb where uid<=uid_num;
-> set x=i+j;
-> select x;
-> end
-> //
mysql> delimiter ;
mysql> call p11(6,"/bin/bash",@p);
mysql> select @p;
inout用法
mysql> delimiter //
mysql> create procedure p12(inout x char(30))
-> begin
-> select name from db9.userdb where name=x;
-> select count(*) into x from userdb;
-> selec x;
-> end
-> //
mysql> delimiter ;
mysql> set @i="root";
mysql> call p12(@i);
mysql> select @i;
流程控制
条件判断,数值比较
逻辑比较、范围、空、非空、模糊、正则
(1)if选择格式
if 条件测试 end
代码
end if;
if 条件测试 then
代码
else
代码
end if;
mysql> create procedure p13(in line_num int);
-> begin
-> if line_num > 10 then
-> select * from userdb where id >= line_num;
-> else
-> select * from userdb where id <= line_num;
-> end if;
-> end
-> //
mysql> delimiter ;
mysql> call p13(5);
mysql> call p13(11)
(2)while循环结构
- 反复测试条件,只要成立就执行命令序列
while 条件判断 do
循环体
… …
end while;
mysql> delimiter //
mysql> create procedure p15()
-> begin
-> declare x int;
-> set x=1;
-> while x<=10 do
-> select x;
-> set x= x+1;
-> end while;
-> end
-> //
mysql> delimiter ;
mysql> call p15();
(3)loop死循环
- 无条件、反复执行某一段代码
loop
循环体
… …
end loop;
mysql> delimiter //
mysql> create procedure p16()
-> begin
-> loop
-> select * from userdb limit 1;
-> end loop;
-> end
-> //
mysql> delimiter ;
mysql> call p16();
(4)repeat条件式循环
- 当条件成立时结束循环
repeat
循环体
… …
until 条件判度
end repeat;
mysql> delimiter //
mysql> create procedure p17()
-> begin
-> declare x int;
-> set x = 1;
-> repeat
-> set x = x + 1;
-> select x;
-> until x >= 10
-> end repeat;
-> end
-> //
mysql> delimiter ;
mysql> call p17();
(5)leave控制循环,适用于while,loop,repeat循环体
- leave 标签名 //跳出xunhuan
- iterate 标签名 //放弃本次循化
mysql> delimiter //
mysql> create procedure p18()
-> begin
-> declare x int;
-> set x = 1;
-> abc:while x<=10 do //定义标签名abc
-> select x;
-> set x=x+1;
-> if x=3 then
-> iterate abc; //跳出本次循环
-> end if;
-> if x=7 then
-> leave abc; //跳出循环
-> end if;
-> end while;
-> end
-> //
mysql> delimiter ;