视图、触发器、存储过程

视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用。

1
2
3
4
5
6
7
8
9
10
11
12

# 创建视图
CREATE VIEW 视图名称 AS  SQL语句
# 使用视图
select * from 视图名称
# 修改视图
ALTER VIEW 视图名称 AS SQL语句
# 删除视图
DROP VIEW 视图名称
# 更新视图中的数据，原始表中的记录也会跟着修改
update 视图名称 set ……
insert into 视图名称 values(……)
# 但是我们不应该修改视图中的记录，而且在涉及多个表的情况下是根本无法修改视图中的记录的

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：不包括查询的情况

触发器无法由用户直接调用，而是由于对表的【增/删/改】操作被动引发的。

1
2
3
4
5
6
7
8
9
10
11
12

# 创建操作前触发器,操作行为包括： INSERT DELETE UPDATE
CREATE TRIGGER 触发器名称 BEFORE 操作行为 ON tb1 FOR EACH ROW
BEGIN
    ...
END
# 创建操作后触发器
CREATE TRIGGER 触发器名称 AFTER 操作行为 ON tb1 FOR EACH ROW
BEGIN
    ...
END
# 删除触发器
drop trigger 触发器名称

一个触发器的例子

1
2
3
4
5
6
7
8

delimiter //        # 这里需要先定义语句的分隔符号，避免跟默认的 ; 冲突，导致无法判断触发器语句的起始
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
    IF NEW.success = 'no' THEN #等值判断只有一个等号
            INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
      END IF ; #必须加分号
END//
delimiter ;

存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql语句。存储过程可以接收参数，其参数有三类：in/out/inout，用于传入参数或充当作返回值，这里不再细述。

使用存储过程的优点：

1. 用于替代程序写的SQL语句，实现程序与sql解耦
2. 基于网络传输，传别名的数据量小，而直接传sql数据量大

使用存储过程的缺点：

1. 程序员扩展功能不方便

1
2
3
4
5
6
7
8
9

# 创建存储过程(无参数)
create procedure 存储过程名称()
BEGIN
    ...
END
# 使用存储过程
call 存储过程名称();
# 删除存储过程
drop procedure 存储过程名称;

一个存储过程的例子：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

delimiter //
create procedure p1()
BEGIN
    select * from blog;
    INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;

#在mysql中调用
call p1() 

#在python中基于pymysql调用
cursor.callproc('p1') 
print(cursor.fetchall())

函数

MySQL中提供了许多内置函数

• 数学函数

  1 2 3 4

  # 返回参数x的四舍五入的有y位小数的值 ROUND(x,y) # 返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。 RAND()

• 聚合函数

  1 2 3 4 5 6 7 8 9 10 11 12

  # 返回指定列的平均值 AVG(col) # 返回指定列中非NULL值的个数 COUNT(col) # 返回指定列的最小值 MIN(col) # 返回指定列的最大值 MAX(col) # 返回指定列的所有值之和 SUM(col) # 返回由属于一组的列值连接组合而成的结果 GROUP_CONCAT(col)

• 字符串函数

  字符串函数太多，仅列举几个简单的

  1 2 3 4 5 6 7 8 9 10

  # 字符串拼接，如有任何一个参数为NULL，则返回值为 NULL CONCAT(str1,str2,...) # 字符串拼接（自定义连接符）,会忽略所有的 NULL CONCAT_WS(separator,str1,str2,...) # 返回值为字符串str的长度，长度的单位为字符。一个多字节字符算作一个单字符。 CHAR_LENGTH(str) # 变小写 LOWER(str) # 变大写 UPPER(str)

• 日期和时间函数

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

  # 返回当前日期 CURRENT_DATE() 或 CURDATE() # 返回当前时间 CURTIME() 或 CURRENT_TIME() # 将date字符串格式化为想要的格式 DATE_FORMAT(date,format) mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -> 'Sunday October 2009' mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s'); -> '22:23:00' mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j'); -> '4th 00 Thu 04 10 Oct 277' mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w'); -> '22 22 10 10:23:00 PM 22:23:00 00 6' mysql> SELECT DATE_FORMAT('2006-06-00', '%d'); -> '00'

• 加密函数

  1 2 3 4

  # 计算字符串str的MD5校验和 MD5() # 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。 PASSWORD(str)

• 控制流函数

  1 2 3 4 5 6 7 8 9 10

  # 如果test是真，则返回result，否则返回default CASE WHEN[test] THEN [result]...ELSE [default] END # 如果test和val相等，则返回result，否则返回default CASE [test] WHEN[val1] THEN [result]...ELSE [default]END # 如果test是真，返回t；否则返回f IF(test,t,f) # 如果arg1不是空，返回arg1，否则返回arg2 IFNULL(arg1,arg2) # 如果arg1=arg2返回NULL；否则返回arg1 NULLIF(arg1,arg2)

• 自定义函数

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

  # 创建函数（例子） # 函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能 delimiter // create function f1( i1 int, i2 int) returns int BEGIN declare num int; set num = i1 + i2; return(num); END // delimiter ; # 删除函数 drop function 函数名; # 执行函数 select UPPER('egon') into @res; SELECT @res;

流程控制

if语句、while循环、repeat循环、loop等，不再赘述