ddl,dml
CREATE TABLE world_rich_list (
id INT AUTO_INCREMENT PRIMARY KEY,
birthday DATE,
username VARCHAR(255),
countmoney BIGINT,
age INT,
sex VARCHAR(10),
address VARCHAR(255),
introduce TEXT
);
INSERT INTO world_rich_list (birthday, username, countmoney, age, sex, address, introduce) VALUES
('1964-01-12', 'Jeff Bezos', 189800000000, 59, 'Male', 'USA', 'Founder of Amazon'),
('1955-02-24', 'Bill Gates', 130000000000, 68, 'Male', 'USA', 'Co-founder of Microsoft'),
('1973-08-30', 'Bernard Arnault', 107000000000, 50, 'Male', 'France', 'CEO of LVMH'),
('1954-02-14', 'Warren Buffett', 96000000000, 69, 'Male', 'USA', 'CEO of Berkshire Hathaway'),
('1949-07-12', 'Larry Ellison', 93000000000, 74, 'Male', 'USA', 'Co-founder of Oracle Corporation'),
('1962-03-18', 'Amancio Ortega', 81000000000, 61, 'Male', 'Spain', 'Founder of Inditex'),
('1940-05-26', 'Mark Zuckerberg', 79000000000, 43, 'Male', 'USA', 'Founder of Facebook');
添加随机数据
CREATE PROCEDURE random_addinfo()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 200000 DO
INSERT INTO world_rich_list (
birthday,
username,
countmoney,
age,
sex,
address,
introduce
) VALUES (
-- 使用RAND()函数和日期函数生成随机生日
DATE_ADD('1900-01-01', INTERVAL FLOOR(RAND() * 365 * (YEAR(CURDATE()) - 1900)) DAY),
-- 生成随机用户名(这里使用随机数字作为示例)
CONCAT('User', FLOOR(RAND() * 1000000)),
-- 生成随机财富数额
FLOOR(RAND() * 1000000000000),
-- 生成随机年龄(假设年龄在18到100之间)
FLOOR(18 + RAND() * 82),
-- 随机选择性别
IF(RAND() > 0.5, 'Male', 'Female'),
-- 生成随机地址(这里使用随机文字作为示例)
CONCAT('Address', FLOOR(RAND() * 100000)),
-- 生成随机介绍文本(这里使用随机长度的字符串作为示例)
SUBSTRING(MD5(RAND()) FROM 1 FOR FLOOR(5 + RAND() * 50))
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
call random_addinfo();
实验索引能力
SELECT count(*)FROM world_rich_list;
# 实验索引能力
SELECT *FROM world_rich_list WHERE username='21966';
-- SELECT *FROM world_rich_list WHERE username='21966'
-- > OK 无索引
-- > 时间: 0.114s
--
-- SELECT count(*)FROM world_rich_list
-- > OK 有索引
-- > 时间: 0.022s