微电影网站源码,河北省建设部网站,娄底建设网站制作,专业制作门牌1 存储过程
存储过程是事先编写好、存储在数据库中的一组SQL命令集合。用来完成对数据库的指定操作。
1.1 优缺点
优点#xff1a;
1#xff09;提高系统性能。创建时进行编译#xff0c;随后存放在数据库服务器的过程高速缓存中#xff0c;之后不需要再次执行分析和编… 1 存储过程
存储过程是事先编写好、存储在数据库中的一组SQL命令集合。用来完成对数据库的指定操作。
1.1 优缺点
优点
1提高系统性能。创建时进行编译随后存放在数据库服务器的过程高速缓存中之后不需要再次执行分析和编译操作节省了分析、解析和优化SQL代码所需的时间。
2可自动完成需要预先执行的任务。
缺点
1每个连接的内存使用量将增加。
2开发和维护困难、可移植性差。
1.2 mysql变量
sql变量按范围可划分为三类
1全局变量系统变量对所有会话生效需具备super权限才能设置。SET GLOBAL 变量名。
注意全局变量不能被定义只能修改只能设置已存在的系统变量否则报错
2会话变量对当前会话生效。SET 变量名或者SET 变量名。
3局部变量作用返回在BEGIN与END之间。 DECLARE 1设置局部变量。 2不能以开始 SET 1可以以开始 2定义会话变量或设置全局变量。
表DECLARE 与 SET的区别
DROP VARIABLE 变量名删除变量。
1.2.1 变量赋值
1使用set。 set 变量名 变量值 或 set 变量名 : 变量值;
SET val1 123;
SET val2 : abc;
2使用SELECT。必须使用 “:”不能使用 “”(其此时代表比较是否相等)。 注意SELECT赋值变量时变量前面一定要是。
SELECT 变量名 : 变量值;
SELECT val3 : edf;
SELECT 变量名: 字段名 FROM table_name WHERE ... LIMIT 0,1; (如果查询结果不止一个则取结果的最后一个)
SELECT money : money FROM student WHERE money 0 LIMIT 0,1;
1.3 定义
存储过程有三种类型变量1IN输入参数2OUT输出参数3INOUT输入/输出参数。
其基本语法如下
1语句必须在BEGIN与END之间。
2DECLARE 来声明变量默认值在其后面加DEFAULT 值。
3改变变量值使用SET 变量值。
4调用使用CALL。
5mysql 不支持 CREATE OR REPLACE PROCEDURE 语法可以用DROP PROCEDURE IF EXISTS 存储过程名;来删除特定的存储过程。
DROP PROCEDURE IF EXISTS demo;CREATE PROCEDURE demo(IN p_age INT,OUT p_count INT)BEGINSELECT count : COUNT(*) FROM student WHERE age p_age;SET p_count count;END;CALL demo(18,count);SELECT count;
1.3.1 条件控制 IF ELSE 与 CASE
只能在BEGIN 及 END 之间使用IF ELSE。需要以 END IF 结尾。
DROP PROCEDURE IF EXISTS demo;CREATE PROCEDURE demo(IN p_num INT)BEGINDECLARE tempChar CHAR(24);IF p_num 1 THEN SET tempChar 一;ELSEIF p_num 2 THEN SET tempChar 二;ELSE SET tempChar 其他;END IF;SELECT tempChar;END;CALL demo(1);CALL demo(5);
注意不能在SELECT 中直接使用IF ELSE,下面代码是错误的
SELECTIF p_num 1 THEN 一;ELSE 其他;END IF;CASE WHEN使用范围更广可在SELECT中直接使用需要以END结尾。SELECTCASE ageWHEN 17 THEN 十七WHEN 18 THEN 十八ELSE 其他END AS ageFROM student
1.3.2 循环 LEAVE与ITERATE
循环需要有个标签LEVAE相当于Java的break而ITERATE相当于continue。循环体位于 标签名:LOOP 与 END LOOP 标签名之间。
DROP PROCEDURE IF EXISTS demo;CREATE PROCEDURE demo(IN p_num INT)BEGINDECLARE p_count INT DEFAULT 0;DECLARE p_pos INT DEFAULT 0;myLabel: LOOPSET p_pos p_pos 1;IF p_pos p_num THENLEAVE myLabel;END IF;IF MOD(p_pos,2) 1 THENITERATE myLabel;END IF;SET p_count p_count 1;END LOOP myLabel;SELECT p_count;END;
1.4 与函数对比 存储过程 函数 返回值 返回0个、一个或多个结果集 有且只有一个结果值。 调用方式 call调用 可以直接在SELECT中使用。 参数 有三种类型 IN、OUT、INOUT 只有类似IN类型参数 其他限制 可以用临时表 不能用临时表
表 存储过程与函数的对比
2 视图
视图是一种虚拟表逻辑表本身并不包含数据作为一个SELECT语句保存在数据字典中。由一个或多个表报告视图查询而动态生成的表。
2.1 优缺点
优点
简单、安全、数据独立。
缺点
1性能较低。
2维护复杂每当修改与视图相关的基表的表结构时都必须进行视图更改操作。
3修改限制当用户试图修改视图信息时数据库必须把它转化为对基表的某些信息的修改。对于比较复杂的试图可能就不能进行修改了。
2.2 定义
简单创建视图的语法为
CREATE VIEW 视图名 AS SELECT 查询语句;
对于单表的视图我们可以像对待基表一样对其进行增删改操作结果最终会作用于基表。
2.2.1 创建语法
CREATE[ALGORITHM {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
ALGORITHM创建视图使用的算法。UNDEFINED默认值不指定算法没指定时一般采用merge算法MERGE合并算法在基于视图创建新的视图时将创建旧视图所使用的SELECT语句与将要创建新的视图的SELECT语句进行合并处理效率更高TEMPTABLE临时表算法在基于视图创建新的视图时先执行旧视图的SELECT语句然后再执行新视图的SELECT语句效率较低。
DEFINER视图创建者。user 为指定创建的用户CURRENT_USER为当前登录用户。
SQL SECURITY视图的安全策略。DEFINER 默认值验证是否拥有对视图本身的权限INVOKER验证对视图的权限及对视图所涉及到的表的权限。
WITH更改视图数据时对更改的数据进行检查。基本检查策略是当更改视图中的数据时如更改之后的结果不符合创建该视图的权限则不允许。CASCADED和LOCAL都具备基础检查策略。LOCAL如果该视图关联了其他视图对视图进行数据操作时只需满足当前视图的创建条件即可。CASCADED既要符合当前视图的条件也要符合关联视图的条件。
CREATEALGORITHMMERGEDEFINERCURRENT_USERSQL SECURITY INVOKERVIEW student_viewAS SELECT * FROM studentWITH CASCADED CHECK OPTION;
通过GRANT 权限 ON 数据对象 TO 数据库用户 来进行授权
GRANT SELECT,UPDATE ON study.student_view TO rootlocalhost;
通过REVOKE 权限 ON 数据对象 FROM 数据库用户 来移除授权
REVOKE SELECT ON study.student_view FROM rootlocalhost;
