数据库基础之09（视图介绍、存储过程、流程控制）

目录

1、什么是视图(View)

1-1、视图优点

1-2、视图使用限制

1-3、创建视图

1-4、查看视图

1-5、 查看创建视图的具体命令

1-6、使用视图

1-7、删除视图

1-8、创建视图的完整格式

1-9、多表联合创建视图

2、存储过程概述

2-1、什么是存储过程：

2-2、存储过程优点

2-3、创建存储过程

2-4、查看存储过程

2-5、调用存储过程

2-6、删除存储过程

2-7、参数类型

2-7-1、关键字        in

2-7-2、关键字      out

2-7-3、关键字        inout

2-8、算数运算

2-9、条件判断

2-10、逻辑比较、范围、空、非空、模糊、正则

3、流程控制

3-1、顺序结构(两种)

 3-2、循环结构(三种)

 

3-3、无循环条件

3-4、until条件判断，条件成立时结束循环

3-5、循环结构控制语句，控制循环结构的执行

---

1、什么是视图(View)

– 虚拟表
– 内容与真实的表相似，包含一系列带有名称的列和行数据。
– 视图并不在数据库中以存储的数据的形式存在,视图表只有对应的表结构文件*.frm。
– 行和列的数据来自定义视图时查询所引用的基表，并且在具体引用视图时动态生成。
– 更新视图的数据，就是更新基表的数据
– 更新基表数据，视图的数据也会跟着改变
 

1-1、视图优点
 

• 简单
– 使用视图的用户完全不需要关心视图中的数据是通过什么查询得到的。
– 视图中的数据对用户来说已经是过滤好的符合条件的结果集。
• 安全
– 用户只能看到视图中的数据。
• 数据独立
– 一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响。
 

1-2、视图使用限制
 

1. 不能在视图上创建索引
2. 在视图的FROM子句中不能使用子查询
3. 以下情形中的视图是不可更新的
4. 包含以下关键字的SQL语句：聚合函数(SUM、MIN、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL
5. 常量视图
6. JOIN
7. FROM一个不能更新的视图
8. WHERE子句的子查询引用了FROM子句中的表
9. 使用了临时表，视图是不可更新

 

1-3、创建视图
 

• 语法格式
– create view 视图名称 as SQL查询；
– create view 视图名称(字段名列表) as SQL查询；
 
注意：
在视图表中不定义字段名的话，默认使用表中的字段名，若定义字段名的话，视图表中的字段名个数必须和基表中的段个数相等。
mysql> create view t11 asselect * from t1;
Query OK, 0rows affected (0.05 sec)
 

1-4、查看视图

• 查看当前库下所有表的状态信息--------------看其是不是视图
– show table status;
– show table status where comment="view"\G;
mysql> show table status where comment=“view”\G;
*************************** 1. row ***************************
Name: t11
Engine: NULL
Auto_increment: NULL
…… Create_options: NULL
Comment: VIEW //视图表
 

1-5、 查看创建视图的具体命令

– show create view 视图名；
mysql> show create view t11\G;
*************************** 1. row ***************************
View: t11
Create View: CREATE ALGORITHM=UNDEFINED
DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `t11` AS
select `t1`.`name` AS `name` from `t1`
character_set_client: utf8
collation_connection: utf8_general_ci
 

1-6、使用视图

• 查询记录
– Select 字段名列表 from 视图名 where 条件；
• 插入记录
– Insert into 视图名(字段名列表) values(字段值列
表)；
• 更新记录
– Update 视图名 set 字段名= 值 where 条件；
• 删除记录
– Delete from 视图名 where 条件；
注意：对视图操作即是对基表操作，反之亦然！！！

1-7、删除视图

• 语法格式
– drop view 视图名；
mysql> drop view t11;
Query OK, 0 rows affected (0.00 sec)
mysql>

1-8、创建视图的完整格式

• 命令格式
– CREATE----------------------创建
[OR REPLACE]---------------或替换------创建时，若视图已存在，会替换已有的视图
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]-----定义处理视图的方式
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]------创建的是是视图不是表  创建的视图名是什么 以及创建的视图包含哪些字段
AS select_statement-------AS是固定格式  从基表中select什么内容
[WITH [CASCADED | LOCAL] CHECK OPTION]------对视图更新/删除/插入时受什么方面的限制

注意：以上内容都可以通过show create view (viewname)来查看
实例：
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)
mysql>
 

1-9、多表联合创建视图

create view v4 as select t1.name as aname,-----创建视图v4，包含t1表的name字段并且将t1表的name字段名 改为aname

t2.name,------包含t2表的name字段

t1.uid as auid,--------包含t1表的uid字段并将t1表的uid字段名 改为auid

t2.uid------包含t2表的uid字段

from t1 left join t2 on t1.uid=t2.uid;----左连接查询，并且只列出符合t1.uid=t2.uid条件的
 
mysql> select * from v4;
+--------+--------+------+------+
| aname  | name   | auid | uid  |
+--------+--------+------+------+
| root   | root   |    0 |    0 |
| bin    | bin    |    1 |    1 |
| daemon | daemon |    2 |    2 |
 
ALGORITHM
• 定义处理视图的方式
– ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}
• MERGE （替换方式）
– 视图名直接使用视图的公式替换掉，把视图公式合并到了select中。
• TEMPTABLE （具体化方式）
– 先得到视图的执行结果，该结果形成一个中间结果暂时存在内存中，之后，外面的select语句就调用了这些中间结果。
• UNDEFINED （未定义）
– ALGORITHM选项的值是UNDEFINED 表示使用的是MERAGE替换方式。
 
 
WITH CHECK OPTION
• 当视图是根据另一个视图定义时,对视图更新/删除/插入时受什么方面的限制
– LOCAL和CASCADED关键字决定了检查的范围。
– LOCAL 仅检查当前视图的限制。
– CASCADED 同时要满足基表的限制-------------(默认值)
 

2、存储过程概述

2-1、什么是存储过程：

数据库中保存的一系列sql命令的集合，编写存储过程时，可以使用变量、条件判断、流程控制等存储过程，就是MySQL中的脚本

2-2、存储过程优点

1.    提高性能
2.    可减轻网络负担
3.    可以防止对表的直接访问
4.    避免重复的sql操作

2-3、创建存储过程

• 语法格式
delimiter //-------------将执行符号更换为 //
create procedure 名称()
begin    
功能代码
……
end
// -----------------------结束存储过程
delimiter ;---------------将执行符号更换会 ；
注：delimiter关键字声明当前段分隔符
MySQL默认以“;”为分隔符，没有声明分割符，编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错。
 
 

2-4、查看存储过程

• 方法1
– mysql> show procedure status;
• 方法2
– mysql> select db,name,type from mysql.proc where name=“存储过程名";-------------创建的所有存储过程都被收录到了mysql.proc表中
mysql> select db,name,type from mysql.proc where name="say";
+---------+------+-------------+
| db        | name | type             |
+---------+------+-------------+
| studydb | say  | PROCEDURE     |
+---------+------+-------------+

2-5、调用存储过程

Call 存储过程名();
注：存储过程没有参数时，（）可以省略有参数时，在调用存储过程时，必须传参。

mysql> call say();
+------+----------+------+------+---------+---------+-----------+
| name | password | uid  | gid  | comment | homedir | shell  |
+------+----------+------+------+---------+---------+-----------+
| root | x        | 0    | 0    | root    | /root   | /bin/bash |
+------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)

2-6、删除存储过程

 drop procedure 存储过程名；
 
mysql> drop procedure say;
Query OK, 0 rows affected (0.00 sec)
 

2-7、参数类型

mysql存储过程，共有三种变量类型in，out，inout
Create procedure 名称（类型 变量名 数据类型 ，类型 变量名  数据类型）
 

2-7-1、关键字        in

名称        输入参数
描述        作用是给存储过程传值，必须在调用存储过程时赋值，在存储过程中该参数的值不允许修改；默认类型是in。

实例：
mysql> delimiter //
mysql> create procedure p6(in shellname char(20))

begin

select count(name) from user where shell=shellname; ----索引shell类型为shellname变量的

end

//
Query OK, 0 rows affected (0.00 sec)
mysql> set @x="/bin/bash";
mysql> call p6(@x)\G;-----------------调用时给固定的值
*************************** 1. row ***************************
count(name): 1
1 row in set (0.00 sec)
 

2-7-2、关键字      out

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

实例：
delimiter //
mysql> create procedure p8(out number int(2))  
begin
set number=9;  
select number;
select count(id) into number from user;----------将select到的count（name）值传给number变量
select number;  
end  
//
Query OK, 0 rows affected (0.00 sec)
mysql> select @y\G;----先查看以下任意一个要用的变量是否之前赋过值，需要没有赋过值的变量
*************************** 1. row ***************************
@y: NULL
1 row in set (0.00 sec)
 
mysql> call p8(@y)\G;------用该变量来存储out出来的值
*************************** 1. row ***************************
number: 9
1 row in set (0.00 sec)
 
*************************** 1. row ***************************
number: 42
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 

2-7-3、关键字        inout

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

实例：
mysql> delimiter //
mysql> create procedure hi(in bash char(20),in nologin char(20),out x int(2),out y int(2))
    -> begin
    -> declare z int(2);
    -> set z = 0;
    -> select count(shell) into x from user where shell=bash;
    -> select count(shell) into y from user where shell=nologin;
    -> set z = x + y;
    -> select z;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> call hi("/bin/bash","/sbin/nologin",@l,@k)\G;
*************************** 1. row ***************************
z: 38
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @l\G;
*************************** 1. row ***************************
@l: 1
1 row in set (0.00 sec)
 
mysql> select @k\G;
*************************** 1. row ***************************
@k: 37
1 row in set (0.01 sec)
注意：此三种类型的变量在存储过程中调用时不需要加@符号，存储过程中自定义的局部变量也不需要加@符号，此外定义或使用变量都需要加@！！！
 
变量类型
变量的种类：全局变量、会话变量、用户变量、局部变量名称描述
会话变量    会话变量和全局变量叫系统变量 使用set 命令定义；
全局变量    全局变量的修改会影响到整个服务器，但是对会话变量的修改，只            会影响到当前的会话。
用户变量    在客户端连接到数据库服务的整个过程中都是有效的。当前连接            断开后所有用户变量失效。
定义 set @ 变量名= 值；
输出 select @ 变量名；
局部变量    存储过程中的begin/end 。其有效范围仅限于该语句块中，语句块            执行完毕后，变量失效。
declare 专门用来定义局部变量。
注意：局部变量 和 参数变量 调用时 变量名前不需要加@

实例：
mysql> set @x = 9;-------------用户变量
mysql> delimiter //
mysql> create procedure p4()

begin

declare x int default 9; ----定义局部变量且x默认值为9

declare y char(10);

set y = "jim";

select x;

select y;

end

//
Query OK, 0 rows affected (0.00 sec)
 
mysql> call p5\G;------------------------调用存储过程可以看到局部变量有效
*************************** 1. row ***************************
x: 99
1 row in set (0.00 sec)
 
*************************** 1. row ***************************
y: jim
1 row in set (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @x\G;--------------------离开存储过程，将使用会话变量
*************************** 1. row ***************************
@x: 9
1 row in set (0.00 sec)
 
mysql> select @u,@v\G;------一个没有定义过的变量其值为空
*************************** 1. row ***************************
@u: NULL
@v: NULL
1 row in set (0.00 sec)
 
 
 

2-8、算数运算

+                加法运算        SET @var1=2+2；        4
-                减法运算        SET @var2=3-2;        1
*                乘法运算        SET @var2=3*2;        6
/                除法运算        SET @var2=10/3;        3.3333333333
DIV                整除运算        SET @var2=10  DIV   3;  3
%                取模            SET @var2=10%3;        1

2-9、条件判断

数值比较
=                等于
>                大于
>=                小于或等于
<                小于
<=                小于或等于
!=                不等于
between..and..    在 .. 与 .. 之间
 

2-10、逻辑比较、范围、空、非空、模糊、正则

Or                逻辑或
And                逻辑与
!                逻辑非
in ..                 在 .. 范围内
not in ..            不在..范围内
is null            字段的值为空
is not null            字段的值不为空
Like                模糊匹配
Regexp            正则匹配

 

3、流程控制

 

3-1、顺序结构(两种)

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

格式：
if 条件测试 then
代码……
……
end if ;
 
当“条件成立”时执行代码１
否则，执行代码２

格式：
if 条件测试 then
代码1 ……
…..
else
代码2……
…..
end if;
实例：
mysql> delimiter //
mysql> create procedure showlines1(in numbers int(2))
begin
if numbers is null then -----注意此处if判断没有分号间隔
select id,name,password from db9.user limit 1;
else
select id,name,password from db9.user limit numbers;
end if;------但最后end if 需要分号结尾
end//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> call showlines1(@ww);
+----+------+----------+
| id | name | password |
+----+------+----------+
|  1 | root | x        |
+----+------+----------+
1 row in set (0.00 sec)

mysql> call showlines1(3);
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | root   | x        |
|  2 | bin    | x        |
|  3 | daemon | x        |
+----+--------+----------+
3 rows in set (0.00 sec)

 
3-2、循环结构(三种)

反复测试条件，只要成立就执行命令序列

格式：
while 条件判断 do  
循环体
......
end while;
实例：
问题：请列出db9.user表中自定义的前numlines行中uid值为偶数的行的name字段和uid字段的值。
mysql> delimiter //
mysql> create procedure show2name(in numlines int(2))
    -> begin
    -> declare x int(2) default 1;----自定义局部变量
    -> declare useruid int(2);-------自定义局部变量
    -> while x <= numlines do-----此处不用加分号
    -> select uid into useruid from db9.user where id = x;---将查到的uid值传到useruid变量中
    -> if useruid % 2 = 0 then------如果useruid被赋的值为偶数
    -> select name,uid from user where id = x;------则列出该行的name字段和uid字段的值
    -> end if;----if判断结束
    -> set x = x + 1;
    -> end while;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call show2name(9);
+------+------+
| name | uid  |
+------+------+
| root |    0 |
+------+------+
1 row in set (0.01 sec)

+--------+------+
| name   | uid  |
+--------+------+
| daemon |    2 |
+--------+------+
1 row in set (0.01 sec)

+------+------+
| name | uid  |
+------+------+
| lp   |    4 |
+------+------+
1 row in set (0.01 sec)

+----------+------+
| name     | uid  |
+----------+------+
| shutdown |    6 |
+----------+------+
1 row in set (0.01 sec)

+------+------+
| name | uid  |
+------+------+
| mail |    8 |
+------+------+
1 row in set (0.01 sec)
...........................................

 
 

3-3、无循环条件

格式：

loop

循环体
......
end loop;
实例：
mysql> delimiter //
mysql> create procedure name()
begin
declare x int(2) default 1;
loop select x;
set x=x+1;
end loop;
end
//
mysql> delimiter ;
call name();-----将永不停止累加输出，直到电脑宕机
 

3-4、until条件判断，条件成立时结束循环

格式：

repeat
循环体
until 条件判断  
end  repeat;
实例1：
mysql> delimiter //
mysql> create procedure haha()
begin
declare x int(2) default 1;
repeat
select x;
set x=x+1;
until x=5 --------------此处没有分号
end repeat;
end
//
mysql> delimiter ;
实例2：
mysql> delimiter //
mysql> create procedure haha1()
begin
declare x int(2) default 0;
repeat select x;
set x=x+5;
until x=105
end repeat;
end
//
mysql> delimiter ；
call haha1;
 

3-5、循环结构控制语句，控制循环结构的执行

Leave        标签名 //跳出循环--------相当于shell中的break
Iterate    标签名 /放弃本次循环，执行下一次循环-------相当于shell中continue
实例1：
mysql> delimiter //
mysql> create procedure loop3()
    -> begin
    -> declare x int(2) default 1;
    -> label1:loop
    -> select x;
    -> set x=x+1;
    -> if x=5 then
    -> leave label1;-------跳出整个循环，执行循环语句以下的语句
    -> end if;
    -> end loop;
    -> end
    -> //
mysql> delimiter ;
call loop3;

实例2：
create procedure prinum2()
begin
declare x int(2) default 1;
label1:while x <=100 do
if x%5=0 or x regexp 5 then
select x;
else
set x=x+1;
iterate label1;-------仅跳出本次循环，继续循环
end if;
set x=x+1;
end while;
end
//