创建20万条SQL数据库

DDL

CREATE TABLE 亿万富翁 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birthday DATE NOT NULL,
    userName VARCHAR(100) NOT NULL,
    countMoney DECIMAL(15, 2) NOT NULL,
    age INT NOT NULL,
    sex VARCHAR(10) NOT NULL,
    address VARCHAR(255) NOT NULL,
    introduce TEXT
);

DML

INSERT INTO 亿万富翁 (birthday, userName, countMoney, age, sex, address, introduce) VALUES
('1964-01-12', '杰夫·贝索斯', 98765432109.87, 59, '男', '美国华盛顿州', '亚马逊创始人，全球首富。'),
('1955-02-24', '埃隆·马斯克', 87654321098.76, 68, '男', '美国加利福尼亚州', '特斯拉和SpaceX的首席执行官，创新企业家。'),
('1969-08-03', '伯纳德·阿尔诺特', 76543210987.65, 54, '男', '法国巴黎', 'LVMH集团老板，奢侈品大王。'),
('1930-08-28', '沃伦·巴菲特', 65432109876.54, 92, '男', '美国内布拉斯加州', '伯克希尔·哈撒韦公司董事长，投资大师。'),
('1973-09-12', '比尔·盖茨', 54321098765.43, 49, '男', '美国华盛顿州', '微软联合创始人，慈善家。'),
('1984-03-14', '马克·扎克伯格', 43210987654.32, 39, '男', '美国加利福尼亚州', 'Facebook创始人和CEO，科技创业者。'),
('1960-11-20', '拉里·佩奇', 32109876543.21, 62, '男', '美国加利福尼亚州', '谷歌公司联合创始人，搜索引擎巨头。');

DQL

DELIMITER //

CREATE PROCEDURE random_addinfo()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE random_birthday DATE;
    DECLARE random_userName VARCHAR(100);
    DECLARE random_countMoney DECIMAL(15, 2);
    DECLARE random_age INT;
    DECLARE random_sex VARCHAR(10);
    DECLARE random_address VARCHAR(255);
    DECLARE random_introduce TEXT;

    WHILE i < 200000 DO
        -- Generate random birthday between 1950-01-01 and 2000-12-31
        SET random_birthday = '1950-01-01' + INTERVAL FLOOR(RAND() * (TIMESTAMPDIFF(DAY, '1950-01-01', '2000-12-31') + 1)) DAY;
        
        -- Generate random userName (example: UserXXXXX, where XXXXX is a random number)
        SET random_userName = CONCAT('User', LPAD(FLOOR(RAND() * 100000), 5, '0'));
        
        -- Generate random countMoney between 1 billion and 100 billion
        SET random_countMoney = FLOOR(1000000000 + RAND() * 90000000000);
        
        -- Generate random age between 30 and 80
        SET random_age = FLOOR(30 + RAND() * 51);
        
        -- Randomly select sex (either '男' or '女')
        SET random_sex = IF(RAND() > 0.5, '男', '女');
        
        -- Generate random address (example: Address-XXXXX, where XXXXX is a random number)
        SET random_address = CONCAT('Address-', LPAD(FLOOR(RAND() * 100000), 5, '0'));
        
        -- Generate random introduce (example: Some random text)
        SET random_introduce = CONCAT('Introduction for ', random_userName);
        
        -- Insert the random data into the billionaires table
        INSERT INTO 亿万富翁 (birthday, userName, countMoney, age, sex, address, introduce)
        VALUES (random_birthday, random_userName, random_countMoney, random_age, random_sex, random_address, random_introduce);
        
        SET i = i + 1;
    END WHILE;
END //

DELIMITER ;

CALL random_addinfo();