跟着项目学sql(四) DML

数据操纵语言（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功能的自定义函数，创建一个导出库中所有表名、字段、条数等的存储过程，等等，很有意义。