#1、创建DDL
问题:请帮我创建一个student要求有id,createDate,userName,age,sex,introduce,要返回给我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
);
#2、插入20万条DML
问题:根据DDL帮我生成一个可以添加20万条数据的存储过程。
CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE random_username VARCHAR(255);
DECLARE random_age INT;
DECLARE random_sex ENUM('M', 'F', 'O');
DECLARE random_introduce TEXT;
DECLARE random_createDate DATE;
WHILE counter < num_students DO
-- 生成随机用户名(示例:User123)
SET random_username = CONCAT('User', FLOOR(RAND() * 1000));
-- 生成随机年龄(示例:18-30岁)
SET random_age = FLOOR(18 + (RAND() * 13));
-- 随机选择性别
SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
-- 生成随机介绍文本(示例:随机长度的文本)
SET random_introduce = SUBSTRING(MD5(RAND()) ,1, FLOOR(10 + (RAND() * 50)));
-- 生成随机创建日期(示例:过去5年内的随机日期)
SET random_createDate = CURDATE() - INTERVAL FLOOR(RAND() * 1825) DAY;
-- 插入随机生成的数据
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (random_createDate, random_username, random_age, random_sex, random_introduce);
-- 增加计数器
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudents(200000);
#3、if判断如何使用
问题:根据DDL生成一个添加数据的存储过程,要求输入2个参数userName与introduce,判断不允许传入空值,其他数据随机写入。以及userName不允许使用
周吴郑王开头的名称。
DELIMITER //
CREATE PROCEDURE InsertStudent(
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;
-- 随机生成createDate(过去5年内的随机日期)
SET @randomCreateDate = CURDATE() - INTERVAL FLOOR(RAND() * 1825) DAY;
-- 随机生成age(18-30岁)
SET @randomAge = FLOOR(18 + (RAND() * 13));
-- 随机生成sex
SET @randomSex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
-- 插入数据
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (@randomCreateDate, p_userName, @randomAge, @randomSex, p_introduce);
END //
DELIMITER ;
CALL InsertStudent('JohnDoe', 'This is John Doe.');
#4、case语句如何使用
问题:帮我生成一条通过case语句查询的sql,要求如果性别是F输出姑娘,如果性别是M输出大老爷们,如果性别是O输出云龙。
SELECT
id,
createDate,
userName,
age,
sex,
CASE
WHEN sex = 'F' THEN '姑娘'
WHEN sex = 'M' THEN '大老爷们'
WHEN sex = 'O' THEN '云龙'
ELSE '未知' -- 可选,用于处理不在'M', 'F', 'O'范围内的其他情况
END AS description,
introduce
FROM
student;