2021/10/11 _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 table viewdb.v1;   #使用删除视图的命令drop view  

***********视图进阶


设置查询语句中的字段别名（select 命令查询的表里有同名的字段时）

mysql> select t2.* , 
user.name , user.uid  from t2 inner join user on t2.uid = user.uid;

mysql> create view viewdb.v3 as select t2.* , 
user.name , user.uid  from t2 inner join user on t2.uid = user.uid;
ERROR 1060 (42S21): Duplicate column name 'uid' ； 报错

mysql> create view viewdb.v3 as select t2.* , 
user.name , user.uid as number from t2 inner join user on t2.uid = user.uid; 正确



覆盖的方式创建视图 （达到修改已有视图的目的）
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 v21  as 
    -> select name , uid from  tarena.user where uid > 10 
    -> with check option;

 mysql> update v21 set uid = 9 where name="root" ;
ERROR 1369 (HY000): CHECK OPTION failed 'tarena.v21'
mysql> 
       
mysql> update v21 set uid = 19 where name="root" ;
mysql> select name ,uid  from  tarena.user where name="root";


~~~~~~~~~~~~~~
mysql> create view  v31 as select name,uid from tarena.user
    -> where uid <= 100 ;
				练习10分钟到  14:24 																				
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  命令 用来的 定义局部变量      休息到 16:02 
例子：
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;              练习到  16:09 
+------+------+
| 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> 
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      in类型的参数负责把数据传给存储过程
例如    create     proucedure  p2(  in   x   int )
          begin
			 ......
	       end
			//

mysql> use tarena;
mysql> delimiter //
mysql> create procedure 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    in类型的参数负责接收存储输出的数据
例如    create     proucedure  p3(  out   x   int )
          begin
			 ......
	       end
			//
			
		call   p3(@名)；
		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)

使用INOUT参数(既有in参数的功能又有out参数的功能)
mysql>  delimiter //
mysql>  create procedure tarena.myadd(INOUT i int)
    -> begin
    -> set i=i+100;
    -> end //
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql>  set  @x = 8 ;
mysql>  set  @y = 101;
mysql> call  tarena.myadd(@x);
mysql> call  tarena.myadd(@y);
mysql> select  @x , @y ;
~~~~~~~~~~~~~~~~~~~~~~~
		8 存储过程里的判断语句
				8.1 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;

修改tarena库使用的字符集 utf8
mysql> alter database tarena default character  set utf8;

查看库使用的字符集
mysql> show create database tarena \G


通过配置文件修改库使用的字符集（在库下创建的表会继承库的字符集）
[root@host50 tarena]# cat /var/lib/mysql/tarena/db.opt
default-character-set=utf8
default-collation=utf8_general_ci
[root@host50 tarena]# 

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 ;

call  tarena.deptype_pro(1,@t);
select  @t;
call  tarena.deptype_pro(7,@t);
select  @t;


8.2 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存储过程的循环结构的例子：

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循环结构例子
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循环的例子:至少循环一次
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);  判断条件成立了 也添加了1给部门
mysql> select  * from tarena.departments;


流程控制语句例子
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;