增量恢复 、 数据查询扩展
RDBMS1_DAY05
mysql
1.1 视图基础
1） 视图介绍（什么是视图）
视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表。
视图是一张虚拟表，是从数据库中一个或多个表中导出来的表，其内容由查询定义。
同真实表一样，视图包含一系列带有名称的列和行数据
数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
一旦表中的数据发生改变，显示在视图中的数据也会发生改变。
2） 视图的优点（为什么要使用视图）
简单
用户无需关心视图中的数据如何查询获得的
视图中的数据已经是过滤好的符合条件的结果集
安全：用户只能看到视图中的数据
数据独立
一旦视图结构确定，可以屏蔽表结构对用户的影响
创建视图的语法格式 （需要牢记）
create view 库.视图名称 as SQL查询;
create view 库.视图名称(字段名列表) as SQL查询;
例子
mysql> create database viewdb; mysql> create view viewdb.v1 as select name , uid from tarena.user ; mysql> create view viewdb.v2(姓名,家目录,登录状态) as select name,homedir,shell from tarena.user; mysql> use tarena; mysql> create view emp_view as select name, email, dept_name from employees as e inner join departments as d on e.dept_id=d.dept_id;
//查看结构
desc tarena.emp_view; desc viewdb.v1; desc viewdb.v1;
// 查询视图中的数据
mysql> select * from viewdb.v1; mysql> select * from viewdb.v2; mysql> select * from tarena.emp_view;
查看视图
use viewdb;
查看当前库里哪些表是视图
mysql> show table status where comment = "view" \G Comment: VIEW
查看视图表里的数据是从哪个基表获取的
mysql> show create view viewdb.v1 \G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `tarena`.`user`.`name` AS `name`,`tarena`.`user`.`uid` AS `uid` from `tarena`.`user` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec)
操作视图 select insert update delete
当对视图里的数据做 insert 、update 、 delete ，
对应的基本数据也会跟着改变，反之亦然
mysql> select * from viewdb.v1 where uid is null ; mysql> delete from viewdb.v1 where uid is null ; mysql> select * from tarena.user where uid is null ;
通过视图表对数据做删除 更新 插入操作 对应基表数据也会改变，（因为视图里的数据就是基本里数据）
mysql> delete from viewdb.v1 where uid is null ;
mysql> update viewdb.v1 set uid=100 where name="root";
mysql> insert into tarena.user(name,uid) values("nb2",8888);查看基本里的数据
mysql> select * from tarena.user where uid is null ; mysql> select count(*) from tarena.user; mysql> select name from tarena.user where name="nb"; mysql> select * from viewdb.v1 where name="nb2";
删除已有视图
mysql> drop table viewdb.v1; ERROR 1051 (42S02): Unknown table 'viewdb.v1' mysql> mysql> drop view viewdb.v1; #使用删除视图的命令drop view
视图进阶
设置查询语句中的字段别名（select 命令查询的表里有同名的字段时）
create table tarena.t3 select name,uid from tarena.user limit 3; create table tarena.t4 select name,shell from tarena.user limit 5; mysql> use tarena; mysql> create view v3 as select * from t3 inner join t4 on t3.name = t4.name; 报错
定义别名
mysql> create view v3 as select t3.name as username , t4.name as 姓名 from t3 inner join t4 on t3.name = t4.name; mysql> select * from v3;
覆盖的方式创建视图 （达到修改已有视图的目的）
mysql> create view viewdb.v2 as select name,uid,gid from tarena.user; ERROR 1050 (42S01): Table 'v2' already exists mysql> create or replace view viewdb.v2 as select name,uid,gid from tarena.user; Query OK, 0 rows affected (0.04 sec)
with check option (支持的检查选项)
选项 local 首先满足自身的限制 ，同时要满足基本的限制
选项 cascaded (默认值 ) 满足视图自身限制即可
默认情况下 通过视图修改数据是不受限制
可以设置通过视图修改数据受限制：限制的方式如下
mysql> create view tarena.v21 as select name , uid from tarena.user where uid > 10 with check option;
mysql> update v21 set uid=1 where name="root"; #条件不成立不会执行修改 Query OK, 0 rows affected (0.01 sec) Rows matched: 0 Changed: 0 Warnings: 0 mysql> select * from v21 where name="root"; Empty set (0.01 sec) mysql> select * from v21 where name="mysql"; +-------+------+ | name | uid | +-------+------+ | mysql | 27 | +-------+------+ 1 row in set (0.00 sec) mysql> update v21 set uid=7 where name="mysql"; 不满足 >10 的限制 ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v21' mysql> mysql> update v21 set uid=37 where name="mysql"; 满足 >10 的限制 Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> create view v31 as select name,uid from tarena.user where uid <= 100 ; mysq> create view v45 as select name ,uid from v31 where uid>=10 with local check option; mysql> update v45 set uid=8 where name="ftp"; ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v45' mysql>
mysql> update v45 set uid=800 where name="ftp";
虽然超出基表v31 限制 但还改成了 因为基表v31没加限制 with check option
修改视图v31 加修改限制
mysql> create or REPLACE view v31 as select name,uid from tarena.user where uid <= 100 with check option ;
mysql> update v45 set uid=6 where name="sshd"; ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v45' 没有满足自身限制 mysql> mysql> update v45 set uid=600 where name="sshd";没有满足基表v31限制 ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v45'
mysql> update v45 set uid=60 where name="sshd"; #既满足自身限制又满足基表限制 Query OK, 1 row affected (0.04 sec)
mysql存储过程
1、什么是存储过程
说白了就是mysql服务的脚本，登录服务器后 要重复执行的命令写成存储过程
存储过程就是mysql服务的脚本。
2、创建存储过程的命令格式（需牢记）
******演示delimiter 命令的作用
命令行的结束符号 默认是 ；
mysql> delimiter // 把命令行的结束符号 改为// mysql> desc tarena.user // 执行命令是得使用//结束命令 mysql> delimiter ; 再改回默认的 ； mysql> desc tarena.user ;
存储过程的使用
创建存储过程 pria()
mysql> use tarena; mysql> delimiter // mysql> create procedure pria() begin select count(*) from tarena.salary ; select count(*) from tarena.employees ; end // mysql> delimiter ;
执行 存储过程 pria()
mysql> call pria(); 或 call tarena.pria(); +----------+ | count(*) | +----------+ | 8055 | +----------+ 1 row in set (0.00 sec) +----------+ | count(*) | +----------+ | 135 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> call pria; 存储创建时括号（） 里没有参数 ，执行可以省略()
查当前所在库 已有的存储过程
mysql> use tarena; mysql> show procedure status \G Db: tarena Name: pria Type: PROCEDURE
或
mysql库里proc表存放所有的存储过程
列出服务器上所有的存储过程
mysql> select db, name , type from mysql.proc where type="PROCEDURE"; mysql> select db, name , type from mysql.proc where type="PROCEDURE" and name="pria"; 查看是否有名字叫pria的存储过程 +--------+------+-----------+ | db | name | type | +--------+------+-----------+ | tarena | pria | PROCEDURE | +--------+------+-----------+ 1 row in set (0.00 sec)
#查看存储过程的功能代码
mysql> select name , body from mysql.proc where name="pria" and type="PROCEDURE"; +------+------------------------------------------------------------------------------------------------+ | name | body | +------+------------------------------------------------------------------------------------------------+ | pria | begin select count(*) from tarena.salary ; select count(*) from tarena.employees ; end | +------+------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
删除存储过程
mysql> drop procedure 库.存储过程名 ;
mysql> drop procedure tarena.pria; Query OK, 0 rows affected (0.00 sec)
mysql> call tarena.pria; ERROR 1305 (42000): PROCEDURE tarena.pria does not exist mysql> select name from mysql.proc where name="pria"; Empty set (0.00 sec)
存储过程进阶
1 变量的使用
1.1 变量的分类 ：
系统变量： mysql服务定义包括：
全局变量（任意用户连接服务查看到值都一样的）
会话变量：连接服务器的用户登录期间使用的变量
自定义变量： 连接数据库服务的用户定义包括：
用户变量：用户登录数据库服务器，自己定义的变量
局部变量 ：在begin 和 end 定义的变量
仅存储过程执行中有效
全局变量使用演示
[root@host50 ~]# mysql -uroot -p123456
mysql> show global variables \G #查看所有的全局变量 Variable_name: version_compile_os Value: Linux mysql> show global variables like "version_compile_os" ;
仅查看一个全局变量
修改全局变量的值
set global 变量名="值"；
mysql> set global wait_timeout = 20000; mysql> show global variables like "wait_timeout"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 20000 | +---------------+-------+ 1 row in set (0.00 sec) mysql> select @@version_compile_os; #输出某个全局变量的值
+----------------------+ | @@version_compile_os | +----------------------+ | Linux | +----------------------+ 1 row in set (0.00 sec) mysql>
会话变量管理 ：当前连接用户使用的变量，会话变量只在连接过程中有效
mysql> show session variables \G 查看连接的所有变量 mysql> show session variables like "%关键字%" \G mysql> show session variables like "%cache%" ; 仅查看与内存相关的变量 mysql> set session sort_buffer_size=50000; 修改 mysql> show session variables like "sort_buffer_size"; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | sort_buffer_size | 50000 | +------------------+-------+ 1 row in set (0.00 sec)
局部变量的使用
declare 命令 用来的 定义局部变量
例子：
mysql> use tarena; mysql> delimiter // mysql> create procedure say48() begin declare x int default 9; declare y char(10); set y = "plj"; select x , y ; end // mysql> delimiter ; 调用存储过程 mysql> call say48; +------+------+ | x | y | +------+------+ | 9 | plj | +------+------+ 1 row in set (0.00 sec)
使用任意一种方式调用局部变量都会报错 因为局部变量只在 存储过程执行中有效
mysql> select x , y ; ERROR 1054 (42S22): Unknown column 'x' in 'field list' mysql> mysql> select @x , @y ; +------+------+ | @x | @y | +------+------+ | NULL | NULL | +------+------+ 1 row in set (0.00 sec) mysql> call say48; +------+------+ | x | y | +------+------+ | 9 | plj | +------+------+ 1 row in set (0.00 sec)
用户变量的使用
#定义并赋值
mysql> set @age=19 , @name="pangljing" ; Query OK, 0 rows affected (0.00 sec) mysql> select @age , @name; +------+-----------+ | @age | @name | +------+-----------+ | 19 | pangljing | +------+-----------+ 1 row in set (0.00 sec)
使用查询结果赋值
mysql> select count(name) into @numbers from tarena.user where shell = "/bin/bash"; mysql> select count(*) into @lines from tarena.user; mysql> select @lines as 总行数 , @numbers as 登录系统用户数; +-----------+-----------------------+ | 总行数 | 登录系统用户数 | +-----------+-----------------------+ | 26 | 1 | +-----------+-----------------------+ 1 row in set (0.00 sec) mysql> select max(uid) , min(uid) into @b,@s from tarena.user; Query OK, 1 row affected (0.00 sec) mysql> select @b,@s; +-------+------+ | @b | @s | +-------+------+ | 65535 | 1 | +-------+------+ 1 row in set (0.00 sec) mysql>
2 参数的使用
create procedure 名(参数,参数,......)
参数定义的语法格式 参数类型 变量名 数据类型
参数类型分3种： in out inout
in in类型的参数负责把数据传给存储过程
例如 create proucedure p2( in x int )
begin
......
end
例子
mysql> delimiter // mysql> create procedure tarena.p3(in dept_no int) begin select dept_id , count(*) as 总人数 from tarena.employees where dept_id=dept_no group by dept_id; end // mysql> delimiter ; mysql> call p3() ; 不给参数会报错 mysql> call p3(1) ; 查看部门编号1 的员工人数 mysql> call p3(3) ; 查看部门编号3 的员工人数
out类型的参数 负责接收存储过程的处理结果。
存储过程执行结束后 可以调用 out类型的参数 获取存储过程的处理结果
例如
create proucedure tarena.p31( out x int )
begin
......
end
call tarena.p31(@名)；
select @名；
编写存储过程tarena.p4 功能获取员工表里指定用户的邮箱
mysql> delimiter //
mysql> create procedure tarena.p4( in emp_name varchar(10) , OUT mail varchar(25))
begin
select email into mail from employees where name=emp_name;
end //
mysql> delimiter ;
insert into employees(name,email) 插入做测试的员工
values("john","john@163.com"),("jerry","jerry@tedu.cn");mysql> call tarena.p4("jerry",@m); 执行存储过程
Query OK, 1 row affected (0.00 sec)mysql> select @m; 查看变量 看 员工的邮箱 +---------------+ | @m | +---------------+ | jerry@tedu.cn | +---------------+ 1 row in set (0.00 sec)
存储过程 归属的库 使用的不是中文字符集时 ,创建的存储过程 无法识别中文
mysql> call tarena.p4("王小红",@m);
ERROR 1366 (HY000): Incorrect string value: '\xE7\x8E\x8B\xE5\xB0\x8F...' for column 'emp_name' at row 1
mysql>修改库使用的字符集
alter database tarena default CHARACTER SET utf8; 修改库使用的字符集
drop procedure tarena.p4; 删除已经的存储过程重新创建 ， 因为字符集 对已经存储的存储过程无效
mysql> delimiter //
mysql> create procedure tarena.p4( in emp_name varchar(10) , OUT mail varchar(25))
begin select email into mail from employees where name=emp_name;
end
//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call tarena.p4("王小红",@m);
Query OK, 1 row affected (0.00 sec)
mysql> select @m;
+----------------------+
| @m |
+----------------------+
| wangxiaohong@tedu.cn |
+----------------------+
1 row in set (0.00 sec)使用INOUT参数(既有in参数的功能又有out参数的功能)
mysql> delimiter // mysql> create procedure tarena.myadd(INOUT i int) -> begin -> set i=i+100; -> end // mysql> delimiter ; mysql> set @x = 8 , @y = 9 ; Query OK, 0 rows affected (0.00 sec) mysql> call tarena.myadd(@x); Query OK, 0 rows affected (0.00 sec) mysql> call tarena.myadd(@y); Query OK, 0 rows affected (0.00 sec) mysql> select @x , @y; +------+------+ | @x | @y | +------+------+ | 108 | 109 | +------+------+ 1 row in set (0.00 sec) mysql>
存储过程里的判断语句 if语句
格式1 一个判断条件
IF 条件 THEN
语句;
END IF;
格式2 条件不成立的时候执行什么操作
IF 条件 THEN
语句1;
ELSE
语句2;
END IF;
格式3 有多个判断条件
IF 条件1 THEN
语句1;
ELSEIF 条件2 THEN
语句2;
ELSE
语句3;
END IF;
例子
mysql> delimiter // mysql> create procedure tarena.deptype_pro(IN no int, OUT dept_type varchar(5)) -> begin -> declare type varchar(5); -> select dept_name into type from departments where dept_id=no; -> if type='运维部' then -> set dept_type='技术部'; -> elseif type='开发部' then -> set dept_type='技术部'; -> elseif type='测试部' then -> set dept_type='技术部'; -> else -> set dept_type='非技术部'; -> end if; -> end // Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; mysql> call deptype_pro(2,@t); Query OK, 1 row affected (0.00 sec) mysql> select @t; +--------------+ | @t | +--------------+ | 非技术部 | +--------------+ 1 row in set (0.00 sec) mysql> call deptype_pro(4,@t); Query OK, 1 row affected (0.00 sec) mysql> select @t; +-----------+ | @t | +-----------+ | 技术部 | +-----------+ 1 row in set (0.00 sec) mysql>
case语句
语法格式
CASE 变量|表达式|字段
WHEN 判断的值1 THEN 返回值1;
WHEN 判断的值2 THEN 返回值2;
... ...
ELSE 返回值n;
END CASE;
例子
delimiter // create procedure tarena.deptype_pro2(IN no int, OUT dept_type varchar(5)) begin declare type varchar(5); select dept_name into type from departments where dept_id=no; case type when '运维部' then set dept_type='技术部'; when '开发部' then set dept_type='技术部'; when '测试部' then set dept_type='技术部'; else set dept_type='非技术部'; end case; end // delimiter ;
调用存储过程tarena.deptype_pro2
call tarena.deptype_pro2(1,@t); @使用自定义变量接收out参数的值
select @t; 查看查看自定义变量@t的值
mysql> call deptype_pro2(4,@t); Query OK, 1 row affected (0.00 sec)
mysql> select @t; +-----------+ | @t | +-----------+ | 技术部 | +-----------+ 1 row in set (0.00 sec) mysql>
MySQL存储过程的循环结构的例子：
while 循环结构 条件判断成立时 重复执行同一段代码
如果第1次判断条件就不成立 会直接结束循环
语法格式
while 判断条件 do
代码
end while;
while 循环结构的例子：
条件判断成立就执行do下边的命令 反之执行end while 结束循环
delimiter //
create procedure tarena.while_pro(IN i int)
begin
declare j int default 1;
while j<i do
insert into tarena.departments(dept_name) values('hr');
set j=j + 1;
end while;
end //
delimiter ;mysql> select * from departments; mysql> call tarena.while_pro(3); mysql> select * from departments;
当首次判断条件就没成立，while是不会执行的。
mysql> call tarena.while_pro(0); mysql> call tarena.while_pro(1); mysql> select * from departments;
loop 循环结构
没有判断条件 重复执行同一段代码 只要不人为结束就一直执行 所以被称为死循环
语法格式
loop
代码
end loop；
loop循环结构例子
delimiter // create procedure tarena.loop2() begin declare i int default 1; loop select sleep(1) , i; end loop； end // delimiter ; call tarena.loop1();
终止循环
在mysql登录状态下 查看正在执行的命令
mysql> show processlist;
在mysql登录状态下终止命令的执行
mysql> kill id号;
repeat循环的例子:
至少循环一次
因为先执行循环体 ，再判断条件（当判断条件成立时继续执行循环体（判断条件不成立为为真），反之结束循环）
语法格式
repeat
循环体
until 判断条件
end repeat;
例子
delimiter //
create procedure tarena.repeat_pro(IN i int)
begin
declare j int default 1;
repeat
set j=j+1;
insert into tarena.departments(dept_name) values('sales');
until j>i #判断条件不成立执行循环体，反之循环结束
end repeat;
end //
delimiter ;mysql> call tarena.repeat_pro(4); mysql> select * from tarena.departments;
验证repeat 是先执行循环体 再判断条件的
mysql> call tarena.repeat_pro(0); 判断条件成立了 也执行了添加部门的insert into 命令 mysql> select * from tarena.departments;
循环控制语句
leave (结束循环)
iterate 终止当前循环并开始下次循环
leave例子
delimiter // create procedure tarena.p0() begin loop select sleep(1); select "one"; end loop; end // delimiter ;
mysql> call tarena.p0 ; 一直在输出
drop procedure tarena.p0 ;
delimiter //
create procedure tarena.p0()
begin
p:loop
leave p;
select sleep(1);
select "one";
end loop p;
end
//
delimiter ;
mysql> call tarena.p0 ; 没有输出
iterate例子
delimiter //
create procedure tarena.while_pro3(IN i int)
begin
declare j int default 0;
a:while j<i do
set j=j+1;
if mod(j,2)=0 then iterate a; #变量j 存储的是偶数时，开始下一次循环
end if;
insert into tarena.departments(dept_name) values(concat('hr', j));
end while a;
end //
delimiter ;
mysql> call tarena.while_pro3(10);
mysql> select * from tarena.departments;over
