数据库实践LAB大纲 03 视图存储过程函数

最新推荐文章于 2024-10-30 22:57:10 发布

JamSlade

最新推荐文章于 2024-10-30 22:57:10 发布

阅读量377

点赞数

分类专栏： DataBase 文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/JamSlade/article/details/128951310

版权

DataBase 专栏收录该内容

25 篇文章 13 订阅

订阅专栏

文章详细介绍了MySQL中的视图概念，包括其优点（如数据安全、简化操作）和缺点（如性能影响、表依赖），以及创建、查看和修改视图的语法和注意事项。此外，还讨论了存储过程和函数的使用，强调它们在提升性能和代码复用方面的优势，同时提到了可能的缺点和基本语法。

摘要由CSDN通过智能技术生成

视图

用于创建动态表的静态定义

根据预定义的选择条件从一个或多个行集中生成行列组合

虚拟的表
视图中的数据并不像表、索引那样需要占用存储空间

优点

保护数据安全 ）为不同的用户分配不同的视图
简化操作 —— 隐藏了表与表之间的复杂的连接操作
使分散数据集中
提高数据的逻辑独立性

缺点

性能上，视图查询数据较慢
关系上存在表依赖现象：改数据库表结构要修改视图

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，默认undefined
view_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(未支持)

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 ;