数据库数据存储

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,  -- 假设'M'代表男，'F'代表女，'O'代表其他
    introduce TEXT
);

DML

CALL InsertRandomStudents(200000);




DELIMITER //

CREATE PROCEDURE InsertStudent(
    IN p_userName VARCHAR(255),
    IN p_introduce TEXT
)
BEGIN
    DECLARE v_createDate DATE;
    DECLARE v_age INT;
    DECLARE v_sex ENUM('M', 'F', 'O');
    DECLARE v_forbidden_prefix VARCHAR(4) DEFAULT '周吴郑王';
    
    -- 检查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, CHAR_LENGTH(v_forbidden_prefix)) = v_forbidden_prefix THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'userName cannot start with the specified forbidden prefix';
    END IF;
    
    -- 随机生成createDate（这里使用过去10年内的随机日期）
    SET v_createDate = CURDATE() - INTERVAL FLOOR(RAND() * 3650) DAY;
    
    -- 随机生成age（这里假设年龄在18到30岁之间）
    SET v_age = FLOOR(18 + RAND() * 13);
    
    -- 随机选择sex
    SET v_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
    
    -- 插入学生数据
    INSERT INTO student (createDate, userName, age, sex, introduce)
    VALUES (v_createDate, p_userName, v_age, v_sex, p_introduce);
END //

DELIMITER ;



DELIMITER //

CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE random_user_name VARCHAR(255);
    DECLARE random_age INT;
    DECLARE random_sex ENUM('M', 'F', 'O');
    DECLARE random_introduce TEXT;
    
    -- 确保num_students是一个正数
    IF num_students <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Number of students must be positive';
    END IF;
    
    -- 循环添加学生数据
    WHILE counter < num_students DO
        -- 生成随机用户名（这里简化为一个随机字符串，你可以根据需要自定义）
        SET random_user_name = CONCAT('User', FLOOR(RAND() * 100000));
        
        -- 生成随机年龄（这里假设年龄在18到30岁之间）
        SET random_age = FLOOR(18 + RAND() * 13);
        
        -- 随机选择性别
        SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
        
        -- 生成随机介绍（这里简化为一个随机长度的字符串，你可以根据需要自定义）
        SET random_introduce = CONCAT('Introduction ', FLOOR(RAND() * 1000));
        
        -- 插入学生数据
        INSERT INTO student (createDate, userName, age, sex, introduce) 
        VALUES (CURDATE(), random_user_name, random_age, random_sex, random_introduce);
        
        -- 更新计数器
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

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

DQL

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