MySQL查询语句

DDL

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATE NOT NULL,
    userName VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    age INT,
    sex VARCHAR(10),
    introduce TEXT
);

DML

INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-07-01', '张三', '13800138000', 20, '男', '我是张三，喜欢读书。'),
('2023-07-02', '李四', '13900139000', 22, '男', '我是李四，喜欢运动。'),
('2023-07-03', '王五', '13700137000', 21, '女', '我是王五，喜欢音乐。'),
('2023-07-04', '赵六', '13600136000', 19, '女', '我是赵六，喜欢画画。'),
('2023-07-05', '孙七', '13500135000', 23, '男', '我是孙七，喜欢旅行。'),
('2023-07-06', '周八', '13400134000', 20, '女', '我是周八，喜欢摄影。'),
('2023-07-07', '吴九', '13300133000', 21, '男', '我是吴九，喜欢编程。');

DQL

DELIMITER //

CREATE PROCEDURE InsertStudentData(num_rows INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE v_createDate DATE;
    DECLARE v_userName VARCHAR(50);
    DECLARE v_phone VARCHAR(20);
    DECLARE v_age INT;
    DECLARE v_sex VARCHAR(10);
    DECLARE v_introduce TEXT;

    -- 设置随机种子，以便生成随机数据
    SET @@RAND_SEED1 = RAND() * (999999999 - 100000000) + 100000000;

    WHILE counter < num_rows DO
        -- 生成随机数据，这里仅作为示例，您可以根据需要调整
        SET v_createDate = DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY);
        SET v_userName = CONCAT('User', FLOOR(RAND() * 100000));
        SET v_phone = CONCAT('1', FLOOR(RAND() * (999999999 - 100000000)) + 100000000);
        SET v_age = FLOOR(RAND() * (30 - 18)) + 18;
        SET v_sex = IF(RAND() > 0.5, '男', '女');
        SET v_introduce = CONCAT('介绍文本 ', FLOOR(RAND() * 10000));

        -- 插入数据
        INSERT INTO student (createDate, userName, phone, age, sex, introduce)
        VALUES (v_createDate, v_userName, v_phone, v_age, v_sex, v_introduce);

        -- 更新计数器
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;
CALL InsertStudentData(200000);
DELIMITER //

CREATE PROCEDURE InsertStudent(
    IN p_userName VARCHAR(50),
    IN p_introduce TEXT
)
BEGIN
    -- 声明变量用于检查userName是否以指定字符开头
    DECLARE invalid_prefix VARCHAR(10) DEFAULT '张王李赵';
    DECLARE prefix_length INT DEFAULT 1; -- 单个姓的字符长度，这里是1（代表一个字符）
    DECLARE userName_valid BOOLEAN DEFAULT TRUE;
    DECLARE current_char CHAR(1);
    DECLARE i INT DEFAULT 1;

    -- 检查userName是否以无效的前缀开头
    WHILE i <= CHAR_LENGTH(invalid_prefix) DO
        SET current_char = SUBSTRING(invalid_prefix, i, prefix_length);
        IF LEFT(p_userName, prefix_length) = current_char THEN
            SET userName_valid = FALSE;
            LEAVE; -- 跳出循环
        END IF;
        SET i = i + 1;
    END WHILE;

    -- 如果userName有效，则插入数据
    IF userName_valid THEN
        INSERT INTO student (createDate, userName, introduce)
        VALUES (NOW(), p_userName, p_introduce);
    ELSE
        -- 用户名无效，可以记录日志或抛出错误
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户名以非法字符开头';
    END IF;
END //

DELIMITER ;
CALL InsertStudent('陈小明', '我是陈小明，喜欢学习。');
SELECT
    id,
    createDate,
    userName,
    phone,
    age,
    sex,
    introduce,
    CASE
        WHEN sex = '男' THEN '爷们'
        WHEN sex = '女' THEN '姑娘'
        ELSE '未知' -- 可选，用于处理除了'男'和'女'之外的其他情况
    END AS gender_description
FROM
    student;