# 复合操作
# 内置函数
# 关于字符串
SELECT ASCII('hello'); -- 返回第一个字符的 ASCII 码
-> 104
SELECT RTRIM(' hello '); -- 去除字符串右侧的空字符
SELECT LTRIM(' hello '); -- 去除字符串左侧的空字符
SELECT TRIM(' hello '); -- 去除字符串两侧的空字符
SELECT UPPER('Hello'); -- 将每个字符转换为大写
-> HELLO
SELECT LOWER('Hello'); -- 将每个字符转换为小写
-> hello
SELECT LENGTH('hello'); -- 返回字符串的长度
-> 5
SELECT SUBSTRING('hello', 1); -- 返回字符串的子串，从第 1 个字符开始的所有字符
-> hello
SELECT SUBSTRING('hello', 1, 2); -- 返回字符串的子串，从第 1 个字符开始的最多 2 个字符
-> he
SELECT CONCAT('a', 'b', 'c'); -- 将多个字符串拼接为一个字符串
-> a,b,c
SELECT CONCAT_WS(',', 'a', 'b', 'c'); -- 用分隔符拼接字符串
-> abc
# 关于数学
SELECT RAND(); -- 返回一个随机数，取值范围为 0~1
-> 0.5708063892748266
SELECT ABS(-3.14); -- 计算绝对值
-> 3.14
SELECT ABS('-3.14'); -- MySQL 支持将字符串自动转换成数值
-> 3.14
SELECT ABS('hello'); -- 如果字符串不能转换成数值，则当作 0 处理
-> 0
SELECT ROUND(3.5); -- 取整，默认保留 0 位小数，多余的小数位会四舍五入
-> 4
SELECT ROUND(3.146, 1); -- 保留 n 位小数时，只会对第 n+1 位进行四舍五入，不会考虑全部小数位
-> 3.1
SELECT FORMAT(3.15, 1); -- 格式化数值，保留 1 位小数，多余的小数位会四舍五入
-> 3.12
SELECT FORMAT(3.15, 3); -- 保留 3 位小数，不足的小数位会用 0 填充
-> 3.150
SELECT TRUNCATE(3.15, 1); -- 保留 1 位小数，不会四舍五入
-> 3.1
SELECT CEIL(3.14); -- 返回大于等于 3.14 的最小整数
-> 4
SELECT FLOOR(3.14); -- 返回小于等于 3.14 的最小整数
-> 3
SELECT POW(9, 0.5); -- 计算 2 的 0.5 次幂
-> 3
SELECT POW(4, -0.5); -- 幂指数可以是负数、小数
-> 0.5
SELECT COUNT(*) FROM tb1; -- 计算数据表中有多少行数据。此时会自动使用聚集索引，或开销更小的二级索引，避免全表扫描
SELECT COUNT(*) FROM tb1 WHERE name='one'; -- COUNT(*) 加 WHERE 子句时，需要分析查询条件是否使用索引
SELECT COUNT(id) FROM tb1; -- 计算 id 字段有多少个取值，不包括取值为 null 的情况
SELECT COUNT(DISTINCT id) FROM tb1; -- 计算 id 字段有多少个不重复的取值，不包括取值为 null 的情况
SELECT AVG(id) FROM tb1; -- 计算 id 字段的平均值
SELECT SUM(id) FROM tb1; -- 总和
SELECT MAX(id) FROM tb1; -- 最大值
SELECT MIN(id) FROM tb1; -- 最小值
SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM tb1; -- 可同时计算多个统计值
# 关于时间
SELECT NOW(); -- 返回当前的时间字符串，采用默认格式
-> 2020-01-01 12:15:05
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 返回指定格式的时间字符串
-> 2020-01-01 12:15:05
SELECT DAY('2020-01-01'); -- 提取时间中的年份，类似的函数有 YEAR()、MONTH()、DAY()、WEEKDAY()、HOUR()、SECOND()
-> 2020
SELECT TO_DAYS('2020-01-01'); -- 将指定时间转换成天数，以公元 0 年为起点
-> 737790
SELECT UNIX_TIMESTAMP(NOW()); -- 将指定时间转换成 Unix 时间戳，单位为秒
-> 1577852105
SELECT ROUND((UNIX_TIMESTAMP('2020-01-01 12:15:05') - UNIX_TIMESTAMP('2020-01-01 11:15:05')) / 60); -- 计算两个时间相差的秒数
-> 60
SELECT TIMESTAMPDIFF(HOUR, '2020-01-01', '2020-01-02'); -- 计算第二个时间减去第一个时间的差值，单位为 HOUR
-> 24
SELECT DATE_ADD('2020-01-01', INTERVAL 10 DAY); -- 在指定时刻的基础上，增减一段时间
-> 2020-01-11
SELECT DATE_ADD('2020-01-01', INTERVAL -1 HOUR);
-> 2019-12-31 23:00:00
# 其它函数
SELECT VERSION(); -- 返回服务器的版本号
-> 8.0.15-12
SELECT DATABASE(); -- 返回当前数据库
-> mysql
SELECT CURRENT_USER(); -- 返回当前登录的账户名
-> test@%
SELECT SESSION_USER(); -- 返回当前会话的客户端地址
-> test@10.42.1.2
SELECT IF(1>0, 'true', 'false'); -- 如果表达式为真，则返回第一个字符串，否则返回第二个字符串
-> true
# 存储函数
- 除了 MySQL 的内置函数，用户可以自定义一些函数，称为存储函数（stored function）。
- 例：
DROP FUNCTION IF EXISTS increase; delimiter // -- 声明 SQL 命令的结束符，默认为分号 ; CREATE FUNCTION increase (x int) -- 创建一个函数，并声明形参列表 RETURNS int DETERMINISTIC -- 声明函数的返回值类型 BEGIN DECLARE a int default 0; -- 创建一个局部变量 SET a = 1; RETURN a + x; -- 返回一个值 END// delimiter ; SELECT increase(2); -- 调用函数 SHOW CREATE FUNCTION increase; SHOW FUNCTION STATUS LIKE 'increase';- 每个 database 之下可以创建多个存储函数。例如
SELECT increase(2);是调用当前数据库的存储函数，SELECT db1.increase(2);是调用指定数据库的存储函数。 - DETERMINISTIC 表示函数的返回值是确定的，输入相同的参数，总是返回相同的值。不过 MySQL 并不会检查函数是否真的 DETERMINISTIC 。
- 每个 database 之下可以创建多个存储函数。例如
- 函数内可使用条件控制语句：
IF x > 1 THEN SET a = 2; ELSEIF (0 < x AND x <= 1) THEN SET a = 1; ELSEIF x <= 0 THEN SET a = 0; END IF;
# 存储过程
存储过程（stored procedure）的用法像存储函数，区别如下：
- 存储过程用
CREATE PROCEDURE创建，而存储函数用CREATE FUNCTION创建。 - 存储过程用 CALL 关键字调用，而存储函数用 SELECT 调用，因此用途更广。
- 存储过程可以声明多个输出变量，而存储函数只有一个返回值。
- 存储过程一般用于打包执行多条 SQL ，像批处理脚本。而函数一般用于加工某个数据，比如数学运算。
- 存储过程用
例：
DROP PROCEDURE IF EXISTS get_book_id; delimiter // CREATE PROCEDURE get_book_id ( -- 创建存储过程 IN book_name varchar(255), -- 声明 IN 输入变量 IN author_name varchar(255), OUT book_id int(10) -- 声明 OUT 输出变量 ) BEGIN DECLARE author_id int(10) default 0; SELECT id FROM author WHERE name = author_name INTO author_id; SELECT id FROM books WHERE author_id = author_id INTO book_id; -- 进行查询，并赋值给输出变量 END // delimiter ; CALL get_book_id('cookbook', 'alice', @book_id); -- 调用存储过程，并用一个用户变量，接收输出变量 SELECT @book_id; -- 查看输出变量的值 SHOW CREATE PROCEDURE get_book_id; SHOW PROCEDURE STATUS LIKE 'get_book_id';在存储过程、存储函数中，可以创建游标（cursor）类型的局部变量，用于逐行遍历 SELECT 的查询结果。
- 游标只能从上往下单向遍历。比如第 1 次调用游标，是读取第 1 行，第 2 次调用游标，是读取第 2 行。
- 例：
delimiter // CREATE PROCEDURE get_book_id () BEGIN DECLARE book_id int; DECLARE cur cursor FOR SELECT id from books; -- 创建一个 cursor 类型的变量 OPEN cur; -- 打开游标，允许读取数据 FETCH cur INTO book_id; -- 通过游标读取一行数据，赋值给局部变量 book_id CLOSE cur; -- 关闭游标，释放其占用的内存。如果没有主动关闭游标，执行 END 语句时会自动关闭 END //
# 视图
- 视图（View）：根据一条 SELECT 语句的查询结果，生成虚拟的数据表，方便以后复用该 SELECT 语句的查询结果。
- 每个 database 之下可以创建多个视图。
- 视图像 table ，但不会存储到磁盘，也不能创建索引、触发器。
- 视图的 SELECT 源表可以是一个或多个数据表或视图，但不能是 temporary 表。
- 在视图的 SELECT 源表中添加、修改数据行时，会自动更新视图生成的数据。但添加、修改字段时，需要重新创建视图。
- 用法：
SHOW TABLES; -- 显示所有数据表、视图 CREATE VIEW <view> AS SELECT ...; -- 创建一个视图 SELECT * FROM <view>; -- 查看视图的内容 ALTER VIEW <view> AS SELECT ...; -- 修改视图的 SELECT 语句 DROP VIEW <view>; -- 删除视图
# 触发器
- 触发器（trigger）：用于在数据表发生某种事件时，自动执行特定的操作。
- 每个数据表可以创建多个触发器。不过建议在同一个 database 中，每个触发器的名称唯一。
- 拷贝一个数据表的结构时，会拷贝其中的所有触发器。
- 例：
CREATE TRIGGER trigger1 -- 创建一个触发器，名为 trigger1 AFTER -- 执行 trigger 的时间。 BEFORE 是在事件之前， AFTER 是在事件之后 INSERT -- 触发 trigger 的数据表事件的类型，可以是 INSERT、DELETE、UPDATE ON tb1 -- 该触发器所属的数据表 FOR EACH ROW SELECT NOW() INTO @add_time; -- 对 INSERT 的每行数据，执行一条 SQL DROP TRIGGER IF EXISTS trigger1; -- 删除触发器 SHOW TRIGGERS; -- 显示当前数据库的所有触发器- 触发器的执行顺序是：BEFORE 触发器 -> 数据表的事件 -> AFTER 触发器
- 如果某一阶段执行失败，则之后的阶段不会执行。此时如果数据表采用 InnoDB 存储引擎，还会自动回滚整个事务。
- INSERT 类型的事件，不会触发 UPDATE 类型的触发器。
- 触发器执行 SQL 时，不能通过 SELECT 直接打印数据，但可以通过 SELECT INTO 将数据保存到其它地方。
- 触发器的执行顺序是：BEFORE 触发器 -> 数据表的事件 -> AFTER 触发器
- 例：访问 NEW、OLD 数据行
CREATE TRIGGER trigger1 BEFORE INSERT ON tb1 FOR EACH ROW SET NEW.name = UPPER(NEW.name); CREATE TRIGGER trigger1 BEFORE UPDATE ON tb1 FOR EACH ROW SET NEW.name = UPPER(NEW.name);- INSERT 类型的触发器中，可通过 NEW 读取被插入的数据行，还可在 BEFORE 阶段修改 NEW 的字段，在 AFTER 阶段则不能修改。
- DELETE 类型的触发器中，可通过 OLD 读取原数据行，不可修改。
- UPDATE 类型的触发器中，可通过 OLD 读取原数据行，通过 NEW 读取修改之后的数据行，还可在 BEFORE 阶段修改 NEW 的字段。