当前位置:首页 > 有云笔记 > RDBMS1 > 正文内容

增量恢复 、 数据查询扩展

小白3年前 (2022-03-16)RDBMS1230250

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

 增量恢复 、 数据查询扩展  您阅读本篇文章共花了: 

分享到:

    扫描二维码推送至手机访问。

    版权声明:本文由有云转晴发布,如需转载请注明出处。

    本文链接:https://yyzq.cf/?id=143

    分享给朋友:

    发表评论

    访客

    ◎欢迎参与讨论,请在这里发表您的看法和观点。