DDL
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
creatDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
phone VARCHAR(15) NOT NULL,
age INT NOT NULL,
sex ENUM('M', 'F', 'O') NOT NULL, -- 假设'M'代表男,'F'代表女,'O'代表其他
introduce TEXT
);
生成20万随机数据的存储过程
DELIMITER //
CREATE PROCEDURE InsertRandomStudents()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 200000 DO
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES (
-- 随机日期(过去10年内)
DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY),
-- 随机用户名(假设格式为“user_XXXX”,其中XXXX是随机数)
CONCAT('user_', FLOOR(RAND() * 10000)),
-- 随机电话号码(假设格式为“13XXXXXXXXX”,其中X是随机数字)
CONCAT('13', LPAD(FLOOR(RAND() * 999999999), 9, '0')),
-- 随机年龄(18到30岁)
FLOOR(RAND() * (30 - 18 + 1)) + 18,
-- 随机性别(M, F, O中的一个)
ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O'),
-- 随机介绍(假设为“This is a random introduction.” 或 NULL)
IF(RAND() > 0.5, 'This is a random introduction.', NULL)
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudents();
select count(*)from student;
生成中文,添加索引
select char(34567);
select * from student where userName='user512591';
-- 无索引0.091s
-- 索引后0.03s
if满足特定需求
DELIMITER //
CREATE PROCEDURE InsertStudent(
IN p_userName VARCHAR(255),
IN p_age INT,
IN p_introduce TEXT
)
BEGIN
-- 检查输入的数据不为空
IF p_userName IS NULL OR p_age IS NULL OR p_introduce IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Input values must not 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 must not start with 张, 王, 李, or 兆';
END IF;
-- 插入数据,其中creatDate、phone、sex是随机生成的
INSERT INTO student (creatDate, userName, phone, age, sex, introduce)
VALUES (
-- 随机日期(当前日期之前的随机天数)
DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY),
-- 传入的用户名
p_userName,
-- 随机电话号码(假设格式为“13XXXXXXXXX”,其中X是随机数字)
CONCAT('13', LPAD(FLOOR(RAND() * 999999999), 9, '0')),
-- 传入的年龄
p_age,
-- 随机性别(M, F, O中的一个)
ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O'),
-- 传入的介绍
p_introduce
);
END //
DELIMITER ;
CALL InsertStudent('SampleName', 25, 'This is a sample introduction.');
case when语句
SELECT
id,
userName,
age,
sex,
CASE
WHEN sex = 'M' THEN '男性'
WHEN sex = 'F' THEN '女性'
WHEN sex = 'O' THEN '泰国人'
ELSE '未知' -- 可选,为了处理未来可能出现的其他性别代码
END AS display_sex
FROM
student
LIMIT
2000;