# 复合操作

# 内置函数

# 关于字符串

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();  -- 返回当前登录的账户名
-> [email protected]%
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 。
  • 函数内可使用条件控制语句:
    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)的用法像存储函数,区别如下:

    • 存储过程用 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 将数据保存到其它地方。
  • 例:访问 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 的字段。