数据操纵语言(Data Manipulation Language, DML)是SQL语言中,负责对数据库对象运行数据访问工作的指令集,以INSERT、UPDATE、DELETE三种指令为核心,分别代表插入、更新与删除,是开发以数据为中心的应用程序必定会使用到的指令,因此有很多开发人员都把加上SQL的SELECT语句的四大指令以“CRUD”来称呼。
接上文,在测试服务器完成对数据库NewsDB的创建。
1)在每个表中插5条测试数据,这里以权限表(permission)和用户表为例
INSERT INTO `permission`
(`id`,`name`)
VALUES
('001','网站基本信息'),
('002','搜索引擎优化配置'),
('003','栏目管理'),
('004','用户管理'),
('005','管理员');
INSERT INTO `user` (`id`,`username`,`password`,`permission`)
VALUES
('001','test1','123456','005'),
('002','test2','123456',''),
('003','test3','123456','002'),
('004','test4','123456','001,002'),
('005','test5','123456','001,003');
2)删除用户004
DELETE FROM `user` WHERE id='004'
3)权限表中的项【搜索引擎优化配置】太长了,要求改为【SEO设置】
UPDATE `permission` SET `name` = 'SEO设置' WHERE `name` = '搜索引擎优化配置';
4)找出有SEO权限的人,find_in_set是locate函数的一种,专门针对逗号隔开的字段
SELECT *from user where find_in_set((select id from permission where name='SEO设置'), permission)>0
上文我们在讲DDL的时候提到,DDL常用的对象有:
DATABASE(数据库)、TABLE(表)、VIEW(视图)、INDEX(索引)、PROCEDURE(存储过程)、TRIGGER(触发器)、FUNCTION(函数),我们当时只用到了数据库、表和视图,
关于索引,我们后面的文章会用着重讲解,一方面是因为章节有限,另一方面是因为我们目前掌握的基础知识暂时还不足以去撼动它。
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
视图、存储过程、触发器、函数本质上来说,都是对DML语句的封装。
1)函数
函数存储着一系列sql语句,调用函数就是一次性执行这些语句。所以函数可以降低语句重复。【但注意的是函数注重返回值,不注重执行过程,所以一些语句无法执行。所以函数并不是单纯的sql语句集合。】
为什么要用函数:
在查找拥有某权限的用户时,我们用到的find_in_set函数,就是mysql的内置函数。我们只需要传入参数,就可以获取结果,而不必去关注实现细节。
函数是一个自我包含的完成一定相关功能的执行代码段,它是实现某种功能的算法集合,有助于sql的可重用性。
自定义函数(user-defined function UDF):
语法:
/*如果函数体只有一条语句, begin和end可以省略, 同时delimiter也可以省略*/
delimiter $$ /*自定义符号*/
create function 函数名([参数列表]) returns 数据类型
begin
sql语句;
return 值;
end;
$$
delimiter ;
通过自定义函数实现find_in_set功能:针对逗号隔开的字符串,返回目标所在位置
CREATE FUNCTION `func_comma`(f_target varchar(255),f_string varchar(1000)) RETURNS varchar(255)
BEGIN
return (locate(concat(f_target,','),concat(f_string,','))+1)/2;
END
2)存储过程
存储过程Procedure是一组为了完成特定功能的SQL语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。
上面的自定义函数func_comma可以返回目标在字符串中的位置。如果我们现在需要一个类似split的功能,输入字符串和分割符,得到分割后的数组,并返回数组长度。
用函数实现的话,有一个问题,那就是函数只能有一个返回值,如果把返回值定义为int类型,那无法获得数组结果集,反之亦然。
这就涉及到存储过程和函数的区别了:
1)存储过程可以使用select返回结果集,通过call调用,而函数通常在select等DML中被调用。
2)存储过程可以有多个返回值,而函数只能有一个返回值。
创建split功能的存储过程:
CREATE PROCEDURE `sp_split`(IN f_string varchar(1000),IN f_delimiter varchar(5),out cnt int)
BEGIN
# 拆分结果
declare i int default 0;
set cnt = 1+(LENGTH(f_string)-LENGTH(replace(f_string,f_delimiter,'')));
DROP TABLE IF EXISTS `tmp_split`;
create temporary table `tmp_split` (`result` varchar(128) not null) DEFAULT CHARSET=utf8;
while i < cnt
do
set i = i + 1;
insert into tmp_split(`result`) values (REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,i)),f_delimiter,1)));
end while;
select *from tmp_split;
END
调用存储过程,获取结果集和返回值。
set @output=0;
call sp_split('1,2,3,4,5,6',',',@output);
select @output;
3)触发器
触发器是一种特殊类型的存储过程,它不同于我们前面介绍过的存储过程。触发器主要是通过事件触发,当对某一表进行诸如Update、 Insert、 Delete 这些操作时执行,而存储过程可以通过存储过程名字而被直接调用。
总结:
存储过程、触发器和函数等功能十分强大,能够为我们提供很多便利,但是如果站在整个单个项目的角度来考量,如果将业务逻辑所需的功能函数全放到数据库来处理,无法采用面向对象的方式将业务逻辑进行封装,我们将面临面向过程的一系列问题,可移植性差,难维护,不易扩展 。。。
说到底还是应用场景的问题,好用但不能用错地方。函数这么好用,但mysql内置函数也才几十个而已,就已经足够帮我们解决很多问题了。试想一下,如果您在一个项目中,自定义了一大堆函数,是不是滥用了呢?
所以在实际开发过程中,还是要把复杂的业务逻辑写在应用程序中,可以存储过程、触发器和函数解决数据库级别的问题,比如开发一个split功能的自定义函数,创建一个导出库中所有表名、字段、条数等的存储过程,等等,很有意义。