数据库的练习

#1、创建DDL

CREATE TABLE student (
    id INT PRIMARY KEY,
    createDate DATE,
    userName VARCHAR(255),
    age INT,
    sex VARCHAR(10),
    introduce TEXT
);

DELIMITER //

CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    WHILE counter < num_students DO
        INSERT INTO student (id, createDate, userName, age, sex, introduce)
        VALUES (
            -- 假设id是自增的，可以省略，或者根据需要生成唯一的id
            0, -- 或者使用某个生成唯一ID的逻辑
            -- 随机生成创建日期，这里假设从过去10年内随机选择
            FROM_UNIXTIME(UNIX_TIMESTAMP('2013-01-01') + FLOOR(RAND() * (UNIX_TIMESTAMP('2023-01-01') - UNIX_TIMESTAMP('2013-01-01') + 1))),
            -- 随机生成用户名
            CONCAT('User', FLOOR(RAND() * 1000000)),
            -- 随机生成年龄（假设范围在18到30岁）
            FLOOR(RAND() * (30 - 18 + 1)) + 18,
            -- 随机分配性别
            IF(RAND() > 0.5, 'Male', 'Female'),
            -- 随机生成介绍文本（这里为了简化，只使用简单的文本）
            CONCAT('Introduction for User ', FLOOR(RAND() * 1000000))
        );
        SET counter = counter + 1;
    END WHILE;
END //

DELIMITER ;

#2、插入20万条DML

CALL InsertRandomStudents(200000);

#3、if判断如何使用

DELIMITER //

CREATE PROCEDURE InsertStudentData(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;
    
    -- 随机生成id（假设id不是自增，否则可以省略）
    SET @random_id := FLOOR(RAND() * 1000000);
    
    -- 随机生成createDate
    SET @random_createDate := FROM_UNIXTIME(UNIX_TIMESTAMP('2000-01-01') + FLOOR(RAND() * (UNIX_TIMESTAMP(CURDATE()) - UNIX_TIMESTAMP('2000-01-01') + 1)));
    
    -- 随机生成age
    SET @random_age := FLOOR(RAND() * (30 - 18 + 1)) + 18;
    
    -- 随机分配sex
    SET @random_sex := IF(RAND() > 0.5, 'Male', 'Female');
    
    -- 插入数据
    INSERT INTO student (id, createDate, userName, age, sex, introduce)
    VALUES (@random_id, @random_createDate, p_userName, @random_age, @random_sex, p_introduce);
END //

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

#4、case语句如何使用

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