MySQL 编程基础

第十章：MySQL 编程基础

💡 MySQL 支持存储过程、函数、触发器等编程功能，本章介绍MySQL编程基础

10.1 MySQL 编程概述

10.1.1 MySQL vs SQL Server 编程对比

10.2 变量与数据类型

10.2.1 用户变量

-- 用户变量（以@开头，会话级别）
SET @name = '张三';
SET @age = 20;

-- 查询中赋值
SELECT @total := COUNT(*) FROM 学生;

-- 使用用户变量
SELECT @name, @age, @total;

10.2.2 局部变量

-- 局部变量只能在BEGIN...END中使用
-- 必须先声明后使用

DELIMITER //
CREATE PROCEDURE test_var()
BEGIN
    -- 声明局部变量
    DECLARE v_name VARCHAR(20);
    DECLARE v_age INT DEFAULT 0;
    DECLARE v_count INT;

    -- 赋值
    SET v_name = '李四';
    SET v_age = 25;
    SELECT COUNT(*) INTO v_count FROM 学生;

    -- 输出
    SELECT v_name, v_age, v_count;
END //
DELIMITER ;

10.3 常用函数

10.3.1 字符串函数

-- ASCII 和 CHAR
SELECT ASCII('A');           -- 65
SELECT ASCII('AB');          -- 65（只返回第一个字符的ASCII码）
SELECT CHAR(65);             -- 'A'

-- 大小写转换
SELECT LOWER('HELLO WORLD'); -- 'hello world'
SELECT UPPER('hello world'); -- 'HELLO WORLD'

-- 去空格
SELECT LTRIM('  hello  ');   -- 'hello  '
SELECT RTRIM('  hello  ');   -- '  hello'
SELECT TRIM('  hello  ');    -- 'hello'

-- 截取字符串
SELECT LEFT('江西服装学院', 2);    -- '江西'
SELECT RIGHT('江西服装学院', 2);   -- '学院'
SELECT SUBSTRING('江西服装学院', 3, 2); -- '服装'（从第3个字符开始，取2个）

-- 字符串长度
SELECT LENGTH('hello');       -- 5（字节数）
SELECT CHAR_LENGTH('你好');   -- 2（字符数）
SELECT LENGTH('你好');        -- 6（UTF-8编码，每个汉字3字节）

-- 字符串查找
SELECT LOCATE('world', 'hello world');  -- 7（返回位置，从1开始）
SELECT INSTR('hello world', 'world');   -- 7
SELECT POSITION('world' IN 'hello world'); -- 7

-- 字符串替换
SELECT REPLACE('hello world', 'world', 'MySQL'); -- 'hello MySQL'

-- 字符串连接
SELECT CONCAT('hello', ' ', 'world');   -- 'hello world'
SELECT CONCAT_WS('-', '2024', '01', '15'); -- '2024-01-15'

-- 重复字符串
SELECT REPEAT('ABC', 3);     -- 'ABCABCABC'

-- 反转字符串
SELECT REVERSE('hello');     -- 'olleh'

-- 格式化输出
SELECT FORMAT(12345.6789, 2); -- '12,345.68'

10.3.2 数学函数

-- 三角函数
SELECT SIN(PI()/2), COS(0), TAN(PI()/4);

-- 取整函数
SELECT CEIL(1.1);    -- 2（向上取整）
SELECT CEILING(1.1); -- 2
SELECT FLOOR(1.9);   -- 1（向下取整）
SELECT ROUND(2.567, 2); -- 2.57（四舍五入，保留2位小数）
SELECT TRUNCATE(2.567, 2); -- 2.56（截断，保留2位小数）

-- 绝对值
SELECT ABS(-125);    -- 125

-- 符号函数
SELECT SIGN(5);      -- 1（正数）
SELECT SIGN(-5);     -- -1（负数）
SELECT SIGN(0);      -- 0

-- 幂运算
SELECT POWER(2, 3);  -- 8
SELECT POW(2, 3);    -- 8
SELECT SQRT(16);     -- 4

-- 随机数
SELECT RAND();       -- 0到1之间的随机数
SELECT FLOOR(RAND() * 100); -- 0到99的随机整数

-- 取模
SELECT MOD(10, 3);   -- 1
SELECT 10 % 3;       -- 1

10.3.3 日期时间函数

-- 获取当前日期时间
SELECT NOW();           -- '2024-01-15 10:30:00'
SELECT CURRENT_TIMESTAMP(); -- 同NOW()
SELECT CURDATE();       -- '2024-01-15'
SELECT CURRENT_DATE();  -- 同CURDATE()
SELECT CURTIME();       -- '10:30:00'
SELECT CURRENT_TIME();  -- 同CURTIME()

-- 日期时间提取
SELECT YEAR('2024-01-15');   -- 2024
SELECT MONTH('2024-01-15');  -- 1
SELECT DAY('2024-01-15');    -- 15
SELECT HOUR('10:30:45');     -- 10
SELECT MINUTE('10:30:45');   -- 30
SELECT SECOND('10:30:45');   -- 45
SELECT DAYOFWEEK('2024-01-15'); -- 2（1=周日，2=周一...）
SELECT DAYNAME('2024-01-15');   -- 'Monday'

-- 日期计算
SELECT DATE_ADD('2024-01-15', INTERVAL 10 DAY);   -- '2024-01-25'
SELECT DATE_ADD('2024-01-15', INTERVAL 1 MONTH);  -- '2024-02-15'
SELECT DATE_SUB('2024-01-15', INTERVAL 1 YEAR);   -- '2023-01-15'
SELECT DATEDIFF('2024-12-31', '2024-01-01');      -- 365（相差天数）

-- 日期格式化
SELECT DATE_FORMAT('2024-01-15', '%Y年%m月%d日'); -- '2024年01月15日'
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');   -- '2024-01-15 10:30:00'

-- 字符串转日期
SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y');     -- '2024-01-15'

日期格式化符号：

10.3.4 类型转换函数

-- CAST函数
SELECT CAST('123' AS SIGNED);          -- 123（转为整数）
SELECT CAST(123.456 AS CHAR);          -- '123.456'（转为字符串）
SELECT CAST('2024-01-15' AS DATE);     -- 2024-01-15
SELECT CAST(NOW() AS DATE);            -- 只保留日期部分

-- CONVERT函数
SELECT CONVERT('123', SIGNED);         -- 123
SELECT CONVERT(NOW(), DATE);           -- 2024-01-15

-- 字符集转换
SELECT CONVERT('你好' USING utf8mb4);

10.3.5 流程控制函数

-- IF函数
SELECT IF(1 > 0, '真', '假');          -- '真'
SELECT IF(score >= 60, '及格', '不及格') FROM 成绩;

-- IFNULL函数（空值替换）
SELECT IFNULL(NULL, '默认值');         -- '默认值'
SELECT IFNULL(成绩, 0) FROM 选课成绩;

-- NULLIF函数
SELECT NULLIF(1, 1);                   -- NULL（两个值相等返回NULL）
SELECT NULLIF(1, 2);                   -- 1（不相等返回第一个值）

-- COALESCE函数（返回第一个非NULL值）
SELECT COALESCE(NULL, NULL, '第三个'); -- '第三个'

-- CASE WHEN THEN
SELECT
    姓名,
    CASE
        WHEN 成绩 >= 90 THEN '优秀'
        WHEN 成绩 >= 80 THEN '良好'
        WHEN 成绩 >= 60 THEN '及格'
        ELSE '不及格'
    END AS 等级
FROM 学生成绩;

-- CASE 简单形式
SELECT
    姓名,
    CASE 性别
        WHEN 'M' THEN '男'
        WHEN 'F' THEN '女'
        ELSE '未知'
    END AS 性别
FROM 学生;

10.4 流程控制语句

10.4.1 IF 语句

-- IF 语句（只能在存储过程/函数中使用）
DELIMITER //
CREATE PROCEDURE check_score(IN p_score INT)
BEGIN
    IF p_score >= 90 THEN
        SELECT '优秀';
    ELSEIF p_score >= 80 THEN
        SELECT '良好';
    ELSEIF p_score >= 60 THEN
        SELECT '及格';
    ELSE
        SELECT '不及格';
    END IF;
END //
DELIMITER ;

-- 调用
CALL check_score(85);

10.4.2 CASE 语句

DELIMITER //
CREATE PROCEDURE grade_level(IN p_grade INT)
BEGIN
    CASE
        WHEN p_grade >= 90 THEN
            SELECT '优秀';
        WHEN p_grade >= 80 THEN
            SELECT '良好';
        WHEN p_grade >= 60 THEN
            SELECT '及格';
        ELSE
            SELECT '不及格';
    END CASE;
END //
DELIMITER ;

10.4.3 WHILE 循环

DELIMITER //
CREATE PROCEDURE sum_1_to_n(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;

    WHILE i <= n DO
        SET total = total + i;
        SET i = i + 1;
    END WHILE;

    SELECT total AS 总和;
END //
DELIMITER ;

-- 调用
CALL sum_1_to_n(100);  -- 结果：5050

10.4.4 LOOP 循环

DELIMITER //
CREATE PROCEDURE sum_loop(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;

    loop_label: LOOP
        IF i > n THEN
            LEAVE loop_label;  -- 跳出循环
        END IF;

        SET total = total + i;
        SET i = i + 1;
    END LOOP loop_label;

    SELECT total AS 总和;
END //
DELIMITER ;

10.4.5 REPEAT 循环

DELIMITER //
CREATE PROCEDURE sum_repeat(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total INT DEFAULT 0;

    REPEAT
        SET total = total + i;
        SET i = i + 1;
    UNTIL i > n END REPEAT;

    SELECT total AS 总和;
END //
DELIMITER ;

10.4.6 循环控制

-- LEAVE：跳出循环（类似break）
-- ITERATE：跳过本次循环（类似continue）

DELIMITER //
CREATE PROCEDURE loop_control_demo()
BEGIN
    DECLARE i INT DEFAULT 0;

    my_loop: LOOP
        SET i = i + 1;

        -- 跳过偶数
        IF i % 2 = 0 THEN
            ITERATE my_loop;
        END IF;

        -- 大于10跳出
        IF i > 10 THEN
            LEAVE my_loop;
        END IF;

        SELECT i;
    END LOOP my_loop;
END //
DELIMITER ;

10.5 实战示例

10.5.1 计算年龄

DELIMITER //
CREATE FUNCTION calc_age(birthday DATE)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE age INT;

    SET age = TIMESTAMPDIFF(YEAR, birthday, CURDATE());

    -- 如果今年生日还没到，年龄减1
    IF DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birthday, '%m%d') THEN
        SET age = age - 1;
    END IF;

    RETURN age;
END //
DELIMITER ;

-- 使用
SELECT calc_age('2000-06-15');
SELECT 姓名, 出生日期, calc_age(出生日期) AS 年龄 FROM 学生;

10.5.2 复利计算

DELIMITER //
CREATE PROCEDURE compound_interest(
    IN principal DECIMAL(10,2),   -- 本金
    IN rate DECIMAL(5,4),         -- 年利率
    IN target DECIMAL(10,2),      -- 目标金额
    OUT years INT,                -- 需要年数
    OUT final_amount DECIMAL(10,2) -- 最终金额
)
BEGIN
    DECLARE amount DECIMAL(10,2);

    SET amount = principal;
    SET years = 0;

    WHILE amount < target DO
        SET amount = amount * (1 + rate);
        SET years = years + 1;
    END WHILE;

    SET final_amount = amount;
END //
DELIMITER ;

-- 调用：本金10000，年利率5%，目标20000
CALL compound_interest(10000, 0.05, 20000, @years, @final);
SELECT @years AS 需要年数, @final AS 最终金额;

10.5.3 成绩等级转换

DELIMITER //
CREATE PROCEDURE convert_scores()
BEGIN
    SELECT
        学号,
        姓名,
        数学,
        CASE
            WHEN 数学 >= 85 THEN '优秀'
            WHEN 数学 >= 60 THEN '及格'
            ELSE '不及格'
        END AS 数学等级,
        英语,
        CASE
            WHEN 英语 >= 85 THEN '优秀'
            WHEN 英语 >= 60 THEN '及格'
            ELSE '不及格'
        END AS 英语等级,
        语文,
        CASE
            WHEN 语文 >= 85 THEN '优秀'
            WHEN 语文 >= 60 THEN '及格'
            ELSE '不及格'
        END AS 语文等级
    FROM 成绩表;
END //
DELIMITER ;

CALL convert_scores();

⬅️ 视图 🏠 00-数据库 ➡️ 存储过程与函数