【无标题】

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE,
    userName VARCHAR(255),
    phone VARCHAR(15),
    age INT,
    sex CHAR(1),
    introduce TEXT
);INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-10-23', '张三', '13800138000', 20, '男', '我是张三，喜欢读书。'),
('2023-10-22', '李四', '13900139000', 21, '男', '我是李四，喜欢运动。'),
('2023-10-21', '王五', '13700137000', 22, '女', '我是王五，喜欢音乐。'),
('2023-10-20', '赵六', '13600136000', 23, '男', '我是赵六，喜欢旅行。'),
('2023-10-19', '孙七', '13500135000', 20, '女', '我是孙七，喜欢画画。'),
('2023-10-18', '周八', '13400134000', 21, '男', '我是周八，喜欢摄影。'),
('2023-10-17', '吴九', '13300133000', 22, '女', '我是吴九，喜欢跳舞。');

DECLARE random_sex CHAR(1);
    DECLARE random_introduce TEXT;
    DECLARE random_date DATE;
    
    -- 设置随机数种子，可以根据需要调整
    SET @@rand_seed1 = RAND(12345);
    
    WHILE counter < num_students DO
        -- 生成随机用户名（示例：UserXXXX）
        SET random_username = CONCAT('User', LPAD(FLOOR(RAND() * 9000) + 1000, 4, '0'));
        
        -- 生成随机电话号码
        SET random_phone = CONCAT(
            FLOOR(RAND() * (9999999999 - 1000000000 + 1)) + 1000000000,
            FLOOR(RAND() * (9999 - 1000 + 1)) + 1000 -- 最后四位随机数
        );
        
        -- 生成随机年龄（假设范围在18到30岁）
        SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
        
        -- 随机分配性别
        SET random_sex = IF(RAND() > 0.5, 'M', 'F');
        
        -- 生成随机介绍文本（示例：Hello, I am a student.）
        SET random_introduce = CONCAT('Hello, I am a student. My ID is ', FLOOR(RAND() * 10000));
        
        -- 生成随机日期（过去5年内的日期）
        SET random_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 5 * 365) DAY);
        
        -- 插入数据
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (random_date, random_username, random_phone, random_age, random_sex, random_introduce);
        
        -- 更新计数器
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE InsertStudent(IN p_userName VARCHAR(255), IN p_introduce TEXT)
BEGIN
    -- 检查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 = 'Invalid userName: Cannot start with 张, 王, 李, or 兆';
    ELSE
        -- 如果不是，则继续插入数据
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (CURDATE(), p_userName, '12345678901', 20, 'M', p_introduce);
    END IF;
END //

DELIMITER ;CALL InsertStudent('合法用户名', '这是介绍信息');

SELECT 
    id,
    createDate,
    userName,
    phone,
    age,
    sex,
    introduce,
    CASE 
        WHEN sex = 'M' THEN '令们'
        WHEN sex = 'F' THEN '姑娘'
        ELSE '未知' -- 可选，用于处理除'M'和'F'之外的其他情况
    END AS gender_description
FROM 
    student;