CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
phone VARCHAR(15),
age INT,
sex CHAR(1),
introduce TEXT
);
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '13800138000', 20, '男', '我是张三,喜欢读书。'),
('2023-01-02', '李四', '13900139000', 21, '男', '我是李四,喜欢运动。'),
('2023-01-03', '王五', '13700137000', 22, '女', '我是王五,喜欢音乐。'),
('2023-01-04', '赵六', '13600136000', 23, '男', '我是赵六,喜欢旅行。'),
('2023-01-05', '孙七', '13500135000', 24, '女', '我是孙七,喜欢画画。'),
('2023-01-06', '周八', '13400134000', 25, '男', '我是周八,喜欢摄影。'),
('2023-01-07', '吴九', '13300133000', 26, '女', '我是吴九,喜欢烹饪。');
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
DECLARE v_counter INT DEFAULT 0;
DECLARE v_userName VARCHAR(255);
DECLARE v_phone VARCHAR(15);
DECLARE v_age INT;
DECLARE v_sex CHAR(1);
DECLARE v_introduce TEXT;
-- 设置事务,以提高插入效率并确保数据一致性
START TRANSACTION;
WHILE v_counter < num_students DO
-- 生成随机用户名(示例:UserXXXXX)
SET v_userName = CONCAT('User', LPAD(FLOOR(RAND() * 100000), 5, '0'));
-- 生成随机电话号码(示例:13800000000 到 13899999999)
SET v_phone = CONCAT('138', LPAD(FLOOR(RAND() * 100000000), 8, '0'));
-- 生成随机年龄(示例:18 到 30)
SET v_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 随机分配性别(男或女)
SET v_sex = IF(RAND() > 0.5, '男', '女');
-- 生成随机介绍文本(示例:我是UserXXXXX)
SET v_introduce = CONCAT('我是', v_userName);
-- 插入数据
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (NOW(), v_userName, v_phone, v_age, v_sex, v_introduce);
-- 更新计数器
SET v_counter = v_counter + 1;
END WHILE;
-- 提交事务
COMMIT;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE InsertStudent(IN p_userName VARCHAR(255), IN p_introduce TEXT)
BEGIN
-- 检查userName是否以"王"、"李"、"兆"开头
IF LEFT(p_userName, 1) NOT IN ('王', '李', '兆') THEN
-- 插入数据,因为userName满足条件
INSERT INTO student (createDate, userName, introduce)
VALUES (NOW(), p_userName, p_introduce);
ELSE
-- 可以记录错误、抛出异常或进行其他操作
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'UserName cannot start with 王, 李, or 兆';
END IF;
END //
DELIMITER ;
SELECT
id,
createDate,
userName,
phone,
age,
sex,
introduce,
CASE
WHEN sex = '男' THEN '爷们'
WHEN sex = '女' THEN '姑娘'
ELSE '未知' -- 可选,用于处理除“男”和“女”之外的其他情况
END AS gender_description
FROM
student;
【无标题】
最新推荐文章于 2024-08-21 23:28:12 发布