mysql世界国家数据库

mysql -u your_username -p your_database < path/to/world.sql

CREATE DATABASE world_db;
USE world_db;

CREATE TABLE countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    iso_code CHAR(2) NOT NULL,
    population INT,
    area FLOAT,
    continent VARCHAR(50)
);

CREATE TABLE cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country_id INT,
    population INT,
    FOREIGN KEY (country_id) REFERENCES countries(id)
);

INSERT INTO countries (name, iso_code, population, area, continent) VALUES
('China', 'CN', 1409517397, 9596961, 'Asia'),
('India', 'IN', 1339180127, 3287263, 'Asia'),
('United States', 'US', 324459463, 9833517, 'North America'),
('Indonesia', 'ID', 263991379, 1904569, 'Asia'),
('Brazil', 'BR', 209288278, 8515767, 'South America');

INSERT INTO cities (name, country_id, population) VALUES
('Beijing', 1, 21516000),
('Shanghai', 1, 24256800),
('Mumbai', 2, 12442373),
('New York', 3, 8175133),
('Los Angeles', 3, 3792621),
('Jakarta', 4, 10770487),
('São Paulo', 5, 12106920);

CREATE TABLE geonames_countries (
    iso_alpha2 CHAR(2) PRIMARY KEY,
    iso_alpha3 CHAR(3),
    iso_numeric INT,
    fips_code CHAR(2),
    name VARCHAR(200),
    capital VARCHAR(200),
    area DOUBLE,
    population INT,
    continent CHAR(2),
    tld VARCHAR(10),
    currency_code CHAR(3),
    currency_name VARCHAR(50),
    phone VARCHAR(20),
    postal_code_format VARCHAR(100),
    postal_code_regex VARCHAR(255),
    languages VARCHAR(200),
    geoname_id INT,
    neighbours VARCHAR(50),
    equivalent_fips_code VARCHAR(10)
);

LOAD DATA LOCAL INFILE 'path/to/countryInfo.txt'
INTO TABLE geonames_countries
FIELDS TERMINATED