文章目录
视图
用于创建动态表的静态定义
根据预定义的选择条件从一个或多个行集中生成行列组合
- 虚拟的表
- 视图中的数据并不像表、索引那样需要占用存储空间
优点
- 保护数据安全 ) 为不同的用户分配不同的视图
- 简化操作 —— 隐藏了表与表之间的复杂的连接操作
- 使分散数据集中
- 提高数据的逻辑独立性
缺点
- 性能上,视图查询数据较慢
- 关系上存在表依赖现象:改数据库表结构要修改视图
MySQL视图上需要注意
- 不能视图上创建索引
- 一个简单的视图可以更新表中数据
- 如果有连接等复杂select创建的视图无法插入、更新
- 不支持物理视图
创建视图
具有 CREATE VIEW 权限和 及针对由select语句选择的每一列上的某些权限
如果还有or replace子句,必须在视图上具有drop权限
create
[or replace]
[algorithm={undefined |merge | temptable }]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
or replace
可选项, 替换同名视图, 需要DROP
权限algorithm
可选值: undefined merge temptable, 默认undefinedview_name
唯一column_list
指定试图列名称,省略则引用基础表select_statement
指定创建视图的select语句,这个select语句给出了视图的定义with check option
可更新视图上所进行的修改要符合select_statement中所指定的限制条件 —— 默认cascaded,对所有视图检查, local则只对定义视图检查
多源表一般只拿来查询不用于修改
create view scs_view(sno,sname,cname,grade,credit)
as select sno,sname,cname,grade,credit
from student,course,sc
where student.sno=sc.sno and c.cno=sc.cno;
- 可以在在已有视图上创建新视图
- 基本数据经过各种计算派生出的数据一般是不存储的。派生属性由于在基本表中并不实际存在,为虚拟列
create view student_avg(sno,avggrade)
as
select sno,avg(grade)from sc
group by sno;
创建的注意事项
- 要有创建权限,如果加上 or replace 需要drop权限
- 5.7版本前 select语句不能包含from子句的子查询
- select不能引用系统或者用户变量
- select语句不能引用预处理语句参数
- 在存储子程序内,定义不能引用子程序参数或者局部变量。
- 引用的表和视图必须存在。创建了视图后,能够舍弃定义引用的表或者视图。可使用check table语句
- 定义中不能引用临时表,不能创建 temporary视图
- 视图定义中命名的表必须已经存在
- 不能将触发程序与视图关联在一起
- 允许使用order by,但特定试图中已经有自己order by语句则被新的视图order by将被忽略
查看视图
describe view_name;
desc view_name;
show table status like 'view_name';
show create view 'view_name';
修改视图
使用alter view语句用于修改一个先前创建好的视图,包括索引视图,但不影响相关的存储过程或触发器,也不更改权限
alter [algorithm={undefined |merge | temptable }]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
参数含义与create view表达式中参数含义相同
删除语句
drop view view_name
修改视图中的数据时,可以对基于两个以上基表或者视图的视图进行修改,但是不能同时影响两个或者多个基表,每次修改都只能影响一个基表(如非聚集部分)
存储过程/函数
事先经过编译并存储在数据库中的一套SQL语句(函数也是)
优点
- 提高APP性能,存储过程按需编译
- 减少少应用程序和数据库服务器之间的流量 —— 发送存储过程名字和参数 instead of 多个冗长SQL语句
- 透明的,可重用的 —— 数据库接口给应用程序,开发人员不必重复开发
- 安全的 —— manager 可以给应用程序授权而不向基础数据库表提供任何权限
缺点
- 存储过程的数量增加 ——> 存储过程连接的内存使用大大增加
- 复杂业务逻辑的存储过程导致很难调试(MySQL不提供调试存储过程的方法)
- 开发、维护、移植较难
语法
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([func_parameter[,...]])RETURNS type
[characteristic ...] routine_body
- proc_parameter 格式 [ IN | OUT | INOUT ] param_name type
- func_parameter 格式 param_name type(Any valid MySQL data type)
- characteristic 格式
- LANGUAGE SQL
- [NOT] DETERMINISTIC
- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
- SQL SECURITY { DEFINER | INVOKER }
- COMMENT ‘string’
- routine_body 合法的SQL语句
说明
权限
- 存储子程序 —— CREATE ROUTINE 权限
- 修改、移除 需要 ALTER ROUTINE权限,自动赋予创建者
- 执行子程序需要 EXECUTE权限
关联
子程序与当前数据库关联。要明确地把子程序与给定数据库关联起来,在创建子程序时指定其名字为db_name.sp_name
- 子程序被调用时,一个隐含的USE db_name 被执行(存储子程序内不能使用 USE语句)
- 可以使用数据库名限定子程序名,引用不在当前数据库的子程序 (类似C++中的 class和 namespace) CALL test.a() 或 CALL test.b()
- DB移除的时候,存储子程序也被移除
参数
- ()包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列( )
- 参数默认是IN参数。要指定为其它参数,可在参数名之前使用关键词 OUT或INOUT
RETURNS
只适用于 FUNCTION,且强制性- 指定参数为IN, OUT, 或INOUT 只适用于 PROCEDURE
- IN 输入参数:调用者 ——> 过程传入值(传入值可以是字面量或变量)
- OUT 输出参数:过程 ——>调用者传出值(可以返回多个值)(传出值只能是变量)
- INOUT 输入输出参数:IN和OUT的结合(值只能是变量)
- Characteristic
- (默认)
CONTAINS SQL
表示子程序包含 SQL读或写数据的语句 NO SQL
表示子程序不包含SQL语句READS SQL DATA
表示子程序仅包含读数据的语句MODIFIES SQL DATA
表示子程序仅包含写数据的语句
- (默认)
SQL SECURITY
指定子程序该用创建子程序者的许可来执行,还是使用调用者的许可来执行 —— 默认 DEFINER- COMMENT子句是一个MySQL的扩展,它可以被用来描述存储程序 —— 被SHOW CREATE ROCEDURE和 SHOW CREATE FUNCTION
过程体
- MySQL允许子程序包含DDL语句,如CREATE和DROP。MySQL也允许存储过程(但不是函数)包含SQL 交互select语句
- 不包含明确、绝对的 commit 或 rollback
- 不可以用 LOAD DATA INFILE
- 返回结果包的语句不能被用在存储函数中
- 块,选择,循环
BEGIN … END 复合语句
[begin_label:] BEGIN
[statement_list]
END [end_label]
包含多个语句(类似C中的花括号)
¤ statement_list代表一个或多个语句的列表,每个语句都必须用分号(;)来结尾。
¤ 复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的
调用
修改语法
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
删除语法
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
查看
-- SHOW CREATE {PROCEDURE | FUNCTION} sp_name
SHOW CREATE PROCEDURE getrecord;
-- SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SHOW PROCEDURE status like
'getrecord';
-- 查看系统表information_schema.Routines
SELECT * FORM Routines where routine_name= 'getrecord';
使用变量
DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]
- declare只能在 BEGIN…END复合语句,且只能放在开头部分
- DEFAULT子句指定默认值,不指定初始为NULL
赋值
SET var_name = expr [, var_name = expr] ...
SET可以同时给多个var赋值
SELECT col_name[,...] INTO var_name[,...] table_expr
- 把选定的多个字段直接存储到变量。
- 只有一条记录的字段可以被取回。
流程控制 IF CASE LOOP REPEAT WHILE LEAVE ITERATE
IF的三种使用方法
if expression
then statement;
end if;
if expression
then statement;
else
else-statements;
end if;
if expression
then statement;
elseif expression
then elseif-statements;
...
else
else_statements;
end if;
CASE的两种用法
# 第一种类似switch
case case_expression
when a then commands
when b then commands
..
else commands;
end case;
# 第二种更像if else的变种
case
when condition1 then commands
when condition2 then commands
...
else commands
end case;
CASE和IF的选择
- 单个表达式与唯一值的范围进行比较时,简单CASE语句比IF语句更易读
- 设计复杂表达式,IF更好
- 使用CASE要确保至少有一个CASE条件匹配,否则要定义错误处理程序
WHILE
WHILE EXPRESSION
do statements
end while
REPEAT
REPEAT
statements
UNTIL expression
end repeat
其他语句补充
- LEAVE,退出循环,等价于break
- ITERATE 跳过剩下代码开始新的迭代,等价于CONTINUE
- LOOP 反复执行代码块
定义条件和处理
能够事先定义程序执行过程中有可能遇到的问题,并采用一定的机制解决相关问题:继续或退出当前代码块的执行,并发出有意义的错误消息(类似 try catch机制)
- 定义
DECLARE condition_name CONDITION FOR condition_value
-- condition_name 条件名称
-- value 可取sqlstate_value / mysql_error_code
- 条件处理
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type 可选 CONTINUE | EXIT | UNDO(未支持)
condition_value 可选 sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION | mysql_error_code
- sqlwarning 是对以01开头的SQLSTATE代码的速记
- NOT FOUND 以02开头的SQLSTATE代码的速记
- SQLEXCEPTION 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记
example:在procedure的开头定义如下内容
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
可以把句柄改成 EXIT
DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
其他条件形式
- 捕获mysql_error_code
- DECLARE CONTINUE HANDLER FOR 1062 SET @x2 = 1;
- 事先定义condition_name
- DECLARE DuplicateKey CONDITION FOR SQLSTATE ‘23000’;
- DECLARE CONTINUE HANDLER FOR DuplicateKey SET @x2 = 1;
- 捕获SQLEXCEPTION
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET @x2 = 1;
示范
返回单个数据
delimiter $$
create procedure qq_cnt(OUT num INT)
begin
select count(*) into num from qq;
end
end;
delimiter ;
数据集
delimiter $$
create procedure get_record()
begin
select * from qq;
end
end;
delimiter ;
创造函数
delimiter $$
create function id_email(id int) returns varchar(20)
reads sql data
begin
declare email varchar(20);
select email into email from qq where tid = id;
return email;
end;
delimiter ;