mysql_day09 MySQL视图 MySQL存储过程

mysql_day09 MySQL视图 MySQL存储过程
一.  mysql视图
1.1  什么是视图

* 虚拟存在的表，数据来自定义视图时查询的基本表

1.2 视图的优点
* 简单
* 安全
* 数据独立

1.3 视图的基本使用
* 创建

create view 视图名称  as  SQL查询；

        create  view  视图名称 （字段名列表） as  SQL查询；

mysql> create view t11 as select  * from  t1;
Query OK, 0 rows affected (0.05 sec)

注意：
在视图表中不定义字段名的话，默认使用表中的字段名，

若定义字段名的话视图表中的字段名个数必须和基本中的字段个数相等。

+++++++++++++++++++++++++++++++++++++++++++++

* 查看

show  table  status;
show table status where comment="view"\G;
* 使用
select  字段名列表   from  视图名 where 条件；
insert into 视图名(字段名列表)  values(字段值列表)；
update  视图名 set  字段名=值 where 条件；
delete  from  视图名 where 条件；

* 删除

drop  view  视图名；

mysql> drop view t11;

Query OK, 0 rows affected (0.00 sec)

1.3 创建视图完全格式
 create [or replace]
 [algorithm={undefined | merage | temptable}]
 [definer={user | current_user}]
 [sql security {definer | invoker}]
 view  视图名 [(列表名)] as 
 select查询语句 [with [local | cascaded] check option]

**设置字段别名

mysql> create view v2 
as  
select  a.name as aname , b.name as bname , a.uid as auid , b.uid as  buid 

from user a left join info b on a.uid=b.uid;

1.4 重要选项说明

or replace      //创建视图时，若视图已存在，会替换已有的视图

mysql> create  view  v2 as select * from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create  view  v2 as select * from t1;
ERROR 1050 (42S01): Table ‘v2’ already exists //提示已存在
mysql> 
mysql> create  or  replace view  v2 as select * from t1;
Query OK, 0 rows affected (0.00 sec)


** algorithm 定义出来视图的方式
   algorithm={undefined | merge | temptable}
merage (替换方式)--视图名直接使用视图的公式替换掉，把视图公式合并到了select中
   temptable(具体化方式)
——先得到视图的执行结果，该结果形成一个中间结果暂时存在内存中，之后，外面的select语句就调用了这些中间结果
   undefined(未定义)
——algorithm 选项的值是undefined 表示用的是 merage 替换方式

** with check option 的关键字决定了检查的范围（更新时）
local (默认值)  仅检查当前视图的限制
cascaded 同时要满足基表的限制

二. mysql存储过程
2.1 什么是存储过程？
* 数据库中保存的一系列sql命令的集合
* mysql中的脚本
* 编写存储过程时，可以使用 变量、条件判断、变量
存储过程优点
* 提高性能
* 可减轻网络负担
* 可以防止对表的直接访问
* 避免重复的sql操作

2.2 基本使用
* 创建存储过程
mysql> delimiter //       # delimiter关键字声明当前段分隔符
mysql> create procedure seluser()
begin 
show databases;   ## begin 和 end 之间 为执行的sql命令
            ## begin 和 end 之间 为执行的sql命令
  end
//
mysql> delimiter ;


* 查看存储过程（2种方法）
mysql> show  procedure  status; 

mysql> select db,name,type from mysql.proc where name=“存储过程名";
* 调用存储过程
mysql> call 存储过程名();

* 删除存储过程
mysql> drop procedure 存储过程名;

2.3 参数类型
mysql存储过程，有三种参数类型 in，out，inout
in  输入参数  默认类型是in
——传递值给存储过程，必须在调用存储过程时指定，在存储过程中修改该参数的值不能；

out   输出参数 该值可在存储过程内部被改变，并可返回

inout 输入/输出参数 调用时指定，并且可被改变和返回

mysql> delimiter //
mysql> create procedure say1(in username char(20))
    -> begin
    -> select username;
    -> select * from db9.passwd where user=username;
    -> end
    -> //
mysql> delimiter ;
mysql> call say1("mysql");

2.4 变量类型
+++++++++++++++++++++++++++++++++++++++++++++++++++++
* 全局变量 | 全局变量的修改会影响到整个服务器
       |=系统变量，使用set命令定义
* 会话变量 | 会话变量的修改，只会影响到当前的会话

* 用户变量 定义 set @变量名=值  
输出select  @变量名
在客户端连接到数据库服务的整个过程中都是有效的。当当前连接断开后所有用户变量失效。

* 局部变量 declare专门用来定义局部变量   在存储过程中的begin/end 中生效
+++++++++++++++++++++++++++++++++++++++++++++++++++++

mysql> show global variables\G;
mysql> show global variables like “%关键字%”; //查看全局变量

mysql> show session variables\G;  //查看会话变量

mysql> set @y = 3;   //用户自定义变量，直接赋值
mysql> select @y;


2.5算数运算
##########################################################
+ 加法运算 SET var1=2+2;      4
- 减法运算 SET var2=3-2;      1
* 乘法运算 SET var3=3*2；   6
/ 除法运算 SET var4=10/3;  3.333333333
DIV 整除运算（必须大写） SET var5=10 DIV 3;  3
% 取模 SET var6=10%3 ;     1
##########################################################

2.6 流程控制
* 条件判断
+++++++++++++++++++++++++++++++++++++++++++++++++++++
类  型 用  途
or 、and 、! 逻辑或、逻辑与、逻辑非
in .. 、not in ..         在 .. 范围内、不在 .. 范围内
is null         字段的值为空
is not null         字段的值不为空
like         模糊匹配
regexp         正则匹配
+++++++++++++++++++++++++++++++++++++++++++++++++++++
流程控制:
if 条件测试 then
代码.....
.........
end if;

当“条件成立”时执行命令序列
否则，不执行任何操作
+++++++++++++++++++++++

if 条件测试 then
代码1 ....
.........
else
代码2 ....
.........
end if;

当“条件成立”时执行代码1
否则，执行代码2
+++++++++++++++++++++++

drop procedure say222;
delimiter //
create procedure say222(in x int(1))
begin
if  x<=10 then
select * from db9.passwd where id <= x;
else
select @@hostname;
select database();
end if;
end
//
delimiter ;

mysql> call say222(10);
mysql> call say222(15);

+++++++++++++++++++++++++++++++++++++++++++++
drop procedure say222;
delimiter //
create procedure say222(in x char(10))
begin
if x is null then
set x="mysql";
select x;
select * from db9.passwd where user=x;
else
select * from db9.passwd where user=x;
end if;
end
//
delimiter ;

mysql> call say222(null);
mysql> call say222("bin");
++++++++++++++++++++++++++++++++++++++++++++++