SQL-进阶-2

最新推荐文章于 2021-07-20 12:32:47 发布

breadmakerQQQ

最新推荐文章于 2021-07-20 12:32:47 发布

阅读量194

点赞数

本文链接：https://blog.csdn.net/breadmakerQQQ/article/details/104626473

版权

SQL-进阶-2

5.1 Stored Procedure（储存过程）
存储过程（Stored Procedure）是：大型数据库系统中，一组为了完成特定功能的 SQL 语句集，这些SQL语句集存储在数据库中，经过第一次编译后，后续调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库的一个重要对象。

使用存储过程的原因，通常也可以用存储过程的优点来讲述。如下：

当对数据库进行复杂操作时（如对多个表进行增删改查），可将这些复杂操作使用存储过程封装起来，与数据库提供的事务处理结合起来使用。
存储过程只在创造时进行编译，以后每次执行存储过程都不需重新编译；而一般SQL语句每执行一次就需要编译一次，所以使用存储过程可提高数据库执行速度。
存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只需要发送存储过程的名称（和参数）。
存储过程对任何应用程序都是可重用的和透明的。存储过程可以重复使用。存储过程将数据库接口暴露给所有应用程序，开发人员不需要重复开发存储过程中已支持的功能，从而可以减少数据库开发人员的工作量。
存储过程是安全的。参数化的存储过程可以防止SQL注入式攻击；同时，数据库管理员可以为访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

5.2 创造存储过程

DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
	body
END //
DELIMITER ;

命令“DELIMITER //”，与存储过程语法无关。DELIMITER语句将标准分隔符分号“;”更改为双反斜杠“//”。更改分隔符的原因，在于我们想将存储过程的内容作为一个整体传递给数据库服务器，而不是让MySQL工具单独解释每个SQL语句（在上面的示例中，假如不更改分隔符，那么SELECT语句就会单独执行，倘若存储过程中存在多个类似的语句，那么每条语句都会被单独执行，这显然不是我们想要的）。在END关键字之后，使用分隔符“//”来表示存储过程的内容到此结束。最后的DELIMITER命令将分隔符更改回分号“;”
使用“CREATE PROCEDURE”语句创建一个新的存储过程。在CREATE PROCEDURE语句之后指定待创建的存储过程的名称。
BEGIN和END之间的部分称为存储过程的主体。将声明性SQL语句放在主体之中以处理业务逻辑。主体中的每条语句结束均需使用“;”
调用过程：CALL procedure_name()
快捷创造方法：左侧导航栏，右击Stored Procedures -> Creat Stored Procedure

5.4 删除存储过程

导航栏快捷删除
使用语句：DROP PROCEDURE IF EXISTS procedure_name;，IF EXISTS关键字防止在过程不存在情况下执行时的报错
在每次创建一个新的存储过程时首先通过语句将可能的同名过程删除后再进行创造，达到数据库的同步

-- 创建过程的模板：
DROP PROCEDURE IF EXISTS procedure_name;
DELIMITER //
CREATE PROCEDURE procedure_name
(
	parameter1 typename,
	parameter2 typename,
	...
)
BEGIN
	body
END //
DELIMITER ;

5.5 参数
解决参数与字段同名的几个方法：
1、给予参数名前后缀：eg." p_client “、” pClient “、” client_para "
2、给予表格别名以在判断时区分
3、sql没有像C++那样的缺省参数值，在调用函数时必须传递所有值，一种实现理想情况的“缺省”思想为传递NULL空值，并在过程中进行IF或IFNULL判断
4、点击查看MySQL所有数据类型
5、一个精妙的缺省思想设计：WHERE c.state = IFNULL(state, c.state) --当state参数缺省为空时返回所有州信息

IF expression THEN 
	...
ELSE 
	...
END IF;  --IF语句格式

6、参数合法性
在对表格进行插入、更新、删除的过程中，应格外注意参数的合法性，在进行操作前，先对参数进行合法性检测：

IF expression THEN
	SIGNAL SQLSTATE 'errorcode'
		SET MESSAGE_TEXT = 'errorinformation'
END IF;

通过SIGNAL关键字抛出错误中断过程，并在SQLSTATE后写上错误码（需要查询得到），并用SET自定义错误消息。值得注意的是，此两个值更多的是语义作用，因此还是很有必要选择正确的错误码并提供自定义报错信息。有一说一，大部分的参数合法性检查应该在应用程序中完成，而不应该等到调用数据库时再进行判断，数据库过程中的合法性判断应该少而重要
点击此处查看所有sqlstate错误码
7、输出参数
在定义输出参数时，在变量名前加上OUT关键字，OUT parameter typename，在调用过程传入输出参数时必须先进行声明。输出参数比较麻烦应避免使用
8、自定义变量：
（1）用户变量（user variables）在sql中，定义用户变量语句：SET @name = value，在定义变量时不需要声明类型但需要初始化，变量名必须以@开头，更新值：SET @name = value 或者 SELECT value FROM table INTO @name，用户变量会在当前连接中一直存在直到连接断开，可以在任何地方声明
（2）局部变量（local variables）只能在过程或函数中声明，作用域也仅限于它所在的BEGIN-END中，声明语句：DECLARE name TYPENAME [DEFAULT dafault_value]，更新方式：SET name = value/expression 或者 SELECT value INTO name FROM table

5.10 函数

函数只能也必须返回一个值
RETURNS指定返回类型，RETURN返回具体值
每个函数需要至少一个特性（attribute），支持function的有三个：NO SQL：没有sql语句、DETERMINISTIC：确定的，输出完全由输入参数决定、READS SQL DATA：会读取数据库数据。关于特性以及bin-log的深入探讨：特性及bin-log
导航栏快捷创建解君愁

-- 自定义函数格式
DROP FUNCTION IF EXISTS function_name;
DELIMITER //
CREATE FUNCTION function_name
(
	parameter1 typename,
	parameter2 typename,
	...
)
RETURNS typename  
ATTRIBUTE
BEGIN
	body
	RETURN value; 
END//
DELIMITER ;

6.1 触发器（Trigger）

-- 触发器创建模板
DROP TRIGGER IF EXISTS trigger_name;
DELIMITER //
CREATE TRIGGER trigger_name
	TIMING EVENT ON table_name
	FOR EACH ROW
BEGIN
	body
END//
DELIMITER ;

触发器是在表格发生变化（插删改）之前或之后自动执行的一段代码，经常用于保持表格数据的一致性，必须要注意死循环的发生，譬如更新一张表触发更新这张表的触发器，这将导致无限死循环的发生
创建格式中，TIMING包括BEFORE/AFTER而EVENT则包括INSERT/DELETE/UPDATE，分别对应不同事件，一个事件只能创建一个触发器
触发器的命名格式请遵守：table_timing_event这样的命名规则，对于查询触发器较为有利也易懂
FOR EACH ROW子句通知触发器，每隔一行执行一次动作，而不是对整个表执行一次。对于其它数据库支持只对整个表执行一次但目前MySQL并不支持
OLD和NEW关键字：用于返回更新之前的和更新之后的某条记录，相当于其别名，可使用’ . column’通配符选取对应列。一个很有用的同步筛选条件：WHERE primary_key = NEW.primary_key
目前MySQL对触发器并不支持像过程和函数那样的可视化操作，较为不利，但可使用SHOW TRIGGERS [LIKE 'string']查看所有或对应触发器，在导航栏对应表中也可找到触发器选项，但无法进行任何操作。也记得及时删除不适用的触发器
使用SELECT * FROM information_schema.TRIGGERS可查看触发器的详细信息，包括触发器的内部细节
触发器的一个很好的应用是作日志记录，把某张表的插入、删除、更新操作全部记录在另一张表中，记住相关记录信息和操作信息，形成完整日志

6.5 事件（Event）

-- 触发器创建模板
DROP EVENT IF EXISTS event_name;
DELIMITER //
CREATE EVENT event_name
ON SCHEDULE
	-- AT date 只执行一次的事件
	EVERY num YEAR/MONTH/DAY/... [STARTS date ENDS date]
DO BEGIN
	body
END//
DELIMITER ;

事件是一段在固定时间或以固定频率自动执行的sql代码，经常用于对于表格的更新（删除过时记录等等），实现对数据库维护的自动化。首次设置：SET GLOBAL event_scheduler = ON（查看系统变量：SHOW VARIABLES [LIKE 'string']）
事件的命名请尽量遵守：interval_name这样的规则，其中interval可以包括yearly/monthly/daily/once这样的明确频率的词汇方便理解与查询
事件比触发器还惨，没有任何可视化工具，只能通过SHOW EVENTS [LIKE 'string']来查询事件，而使用SELECT * FROM information_schema.EVENTS可查看事件详细信息及内部细节
ALTER EVENT event_name ENABLE/DISABLE：启用/禁用某事件，ALTER EVENT event_name ON SCHEDULE scheduler：修改事件频率

7.1 事务（Transactions）

事务，就是”要完成的一件事情”；做一件事情通常由多个步骤组成，如果某个步骤失败，那么这件事情将不会完成；事务是指作为一个基本工作单元执行的一系列SQL语句的操作，要么完全的执行，要么完全的都不执行。
事务四大特性（ACID）
1、Atomicity（原子性）：一个事务（transaction）中的所有操作，或者全部完成，或者全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback到事务开始前的状态，就像这个事务从来没有执行过一样。即事务不可分割、不可约简。
2、Consistency（一致性）：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束、触发器、级联回滚等。
3、Isolation（隔离性）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括未提交读（Read uncommitted）、提交读（Read committed）、可重复读（Repeatable read）和串行化（Serializable）。
4、Durability（持久性）：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
开始一个事务：START TRANSACTION;，结束一个事务：COMMIT;：提交结束，ROLLBACK;：回滚结束
并发一致性问题
1、修改丢失：T1和T2在同时修改同一条记录，由于修改数据不一致且提交时间有先后之分，导致先提交的修改被覆盖丢失。
2、脏读：T1修改了数据，T2在此时进行读数据，但随后T1撤销了修改，T2读的是从未真正出现过的脏数据。
3、不可重复读：T1多次读同一数据。在T1还没有结束时，T2也访问该同一数据。那么，在T1的两次读数据之间由于T2的修改，T1两次读到的的数据可能是不一样的。
4、幻读：T1对一个表中的部分数据进行操作。同时，T2也正在操作这个表中的数据，T2的这种修改新增了符合T1条件的需操作记录，但此时T1已经操作完了。那么之后就会发生T1发现表中还有没有操作的数据行，就好象发生了幻觉一样。
事务隔离级别
1、Read uncommitted：读取未提交，其他事务只要修改了数据，即使未提交，本事务也能看到修改后的数据值，就可能出现脏读；
2、Read committed：读取已提交，其他事务提交了对数据的修改后，本事务就能读取到修改后的数据值，避免了脏读，但是可能会造成不可重复读；
3、Repeatable read：重复读，无论其他事务是否修改并提交了数据，在这个事务中看到的数据值始终不受其他事务影响，可以避免不可重复读，但还有可能出现幻读；
4、Serializable：串行化，一个事务一个事务的执行。是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读。
5、自上往下，隔离级别越来越高，并发性问题越来越少、消耗资源越来越大。MySQL数据库默认使用可重复读（ Repeatable read），可通过语句SET TRANSACTION ISOLATION LEVEL transaction_isolation;设置事务隔离级别，查看目前事务隔离级别使用SHOW VARIABLES LIKE 'transaction_isolation';。
默认情况下, MySQL启用自动提交模式（变量autocommit为ON）。这意味着, 只要你执行DML操作的语句,MySQL会立即隐式提交事务
避免死锁的几个方法：(1)保证不同事务操作表的顺序一致，避免顺序交叉 (2)尽量保证事务短而小 (3)尽量避免高峰期操作大表
其他链接：事务概念参考、并发一致性问题及原理、DQL,DML,DDL等解释

breadmakerQQQ

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL-进阶-2

SQL-进阶-中5.1 Stored Procedure（储存过程）存储过程（Stored Procedure）是：大型数据库系统中，一组为了完成特定功能的 SQL 语句集，这些SQL语句集存储在数据库中，经过第一次编译后，后续调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库的一个重要对象。使用存储过程的原因，通常也可以用存储过程...
复制链接

扫一扫