MySQL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    age INT,
    sex CHAR(1),
    introduce TEXT
);
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-04-01', '张三', '13800138000', 20, '男', '热爱学习，成绩优异。'),
('2023-04-02', '李四', '13900139000', 21, '男', '喜欢运动，篮球高手。'),
('2023-04-03', '王五', '13700137000', 22, '女', '性格开朗，乐于助人。'),
('2023-04-04', '赵六', '13600136000', 19, '女', '文静内敛，喜欢阅读。'),
('2023-04-05', '孙七', '13500135000', 20, '男', '爱好音乐，擅长乐器。'),
('2023-04-06', '周八', '13400134000', 21, '女', '热心公益，志愿者达人。'),
('2023-04-07', '吴九', '13300133000', 22, '男', '编程高手，多次获奖。');

DELIMITER //
CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE random_userName VARCHAR(50);
    DECLARE random_phone VARCHAR(20);
    DECLARE random_age INT;
    DECLARE random_sex CHAR(1);
    DECLARE random_introduce TEXT;
    
    WHILE counter < num_students DO
        -- 生成随机用户名（示例：使用现有的用户名作为基础，并添加一个随机数）
        SET random_userName = CONCAT('User', FLOOR(RAND() * 100000));
        
        -- 生成随机电话号码（示例：11位数字）
        SET random_phone = LPAD(FLOOR(RAND() * 10000000000), 11, '0');
        
        -- 生成随机年龄（示例：18到30岁）
        SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
        
        -- 生成随机性别（M或F）
        SET random_sex = IF(RAND() > 0.5, 'M', 'F');
        
        -- 生成随机介绍（示例：简单的文本）
        SET random_introduce = CONCAT('introduce for', random_userName);
        
        -- 插入数据
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (CURDATE(), random_userName, random_phone, random_age, random_sex, random_introduce);
        
        -- 更新计数器
        SET counter = counter + 1;
    END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudents(200000);

DELIMITER //
CREATE PROCEDURE InsertStudent(IN p_userName VARCHAR(50), IN p_introduce TEXT)
BEGIN
    -- 声明变量用于错误处理
    DECLARE invalid_user_name CONDITION FOR SQLSTATE '45000';
    DECLARE CONTINUE HANDLER FOR invalid_user_name
    BEGIN
        -- 可以在这里定义错误处理逻辑，例如输出错误信息
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid user name: cannot start with specified characters.';
    END;

    -- 检查userName是否以指定汉字开头
    IF LEFT(p_userName, 1) IN ('张', '王', '李', '赵') THEN
        SIGNAL invalid_user_name; -- 触发错误处理
    END IF;

    -- 插入数据到student表中
    INSERT INTO student (createDate, userName, introduce)
    VALUES (CURDATE(), p_userName, p_introduce);
END //
DELIMITER ;
CALL InsertStudent('合法的用户名', '这里是用户的介绍信息');

SELECT 
    id,
    createDate,
    userName,
    phone,
    age,
    sex,
    CASE 
        WHEN sex = '男' THEN '爷们'
        WHEN sex = '女' THEN '姑娘'
        ELSE '未知' -- 这个ELSE分支是可选的，用于处理sex字段不是'男'或'女'的情况
    END AS description,
    introduce
FROM 
    student;