存储过程1

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    sex ENUM('M', 'F', 'O') NOT NULL,
    introduce TEXT
);

DML

DELIMITER //

CREATE PROCEDURE InsertStudentData(IN p_userName VARCHAR(255), IN p_introduce TEXT)
BEGIN
    -- 检查userName和introduce是否为空
    IF p_userName IS NULL OR p_introduce IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName and introduce cannot be null';
    END IF;

    -- 检查userName是否以周、吴、郑、王开头
    IF LEFT(p_userName, 1) = '周' OR LEFT(p_userName, 1) = '吴' OR LEFT(p_userName, 1) = '郑' OR LEFT(p_userName, 1) = '王' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName cannot start with 周, 吴, 郑, or 王';
    END IF;

    -- 其他数据随机生成
    DECLARE random_age INT;
    DECLARE random_sex ENUM('M', 'F', 'O');
    DECLARE random_create_date DATE;

    -- 生成随机年龄
    SET random_age = FLOOR(18 + RAND() * 100); -- 假设年龄在18到117之间

    -- 随机选择性别
    SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');

    -- 生成随机创建日期（例如：过去10年内的随机日期）
    SET random_create_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY);

    -- 插入数据到student表
    INSERT INTO student (createDate, userName, age, sex, introduce)
    VALUES (random_create_date, p_userName, random_age, random_sex, p_introduce);
END //

DELIMITER ;

DQL

SELECT 
    id,
    createDate,
    userName,
    age,
    sex,
    CASE sex
        WHEN 'F' THEN '姑娘'
        WHEN 'M' THEN '大老爷们'
        WHEN 'O' THEN '泰国妹妹'
        ELSE '未知' -- 可选，用于处理除'M', 'F', 'O'之外的其他情况
    END AS sex_description,
    introduce
FROM 
    student;