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;