数据储存。

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('Male', 'Female', 'Other') NOT NULL,
    introduce TEXT
);

dml

DELIMITER //

CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE random_user_name VARCHAR(255);
    DECLARE random_age INT;
    DECLARE random_sex ENUM('Male', 'Female', 'Other');
    DECLARE random_introduce TEXT;
    
    WHILE i < num_students DO
        -- 生成随机用户名（这里简单使用'User'后跟一个随机数）
        SET random_user_name = CONCAT('User', FLOOR(RAND() * 100000));
        
        -- 生成随机年龄（假设年龄在18到30岁之间）
        SET random_age = FLOOR(18 + (RAND() * (30 - 18 + 1)));
        
        -- 随机选择性别
        SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other');
        
        -- 生成随机介绍（这里使用简单的文本，您可以根据需要复杂化）
        SET random_introduce = CONCAT('Introduction for ', random_user_name);
        
        -- 插入数据到student表
        INSERT INTO student (createDate, userName, age, sex, introduce)
        VALUES (NOW(), random_user_name, random_age, random_sex, random_introduce);
        
        -- 增加计数器
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;





CALL InsertRandomStudents(200000);




DELIMITER //

CREATE PROCEDURE InsertStudent(
    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;
    
    -- 其他数据随机生成
    SET @randomAge = FLOOR(18 + (RAND() * (30 - 18 + 1))); -- 随机年龄18到30岁
    SET @randomSex = ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other'); -- 随机性别
    
    -- 插入数据到student表
    INSERT INTO student (createDate, userName, age, sex, introduce)
    VALUES (NOW(), p_userName, @randomAge, @randomSex, p_introduce);
END //

DELIMITER ;





CALL InsertStudent('JohnDoe', 'This is John Doe.');








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