mysql省市区级联表

-- 创建省份表
CREATE TABLE Province (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 创建城市表
CREATE TABLE City (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    province_id INT,
    FOREIGN KEY (province_id) REFERENCES Province(id)
);

-- 创建区县表
CREATE TABLE District (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES City(id)
);

SELECT 
    p.name AS province_name,
    c.name AS city_name,
    d.name AS district_name
FROM 
    Province p
LEFT JOIN 
    City c ON p.id = c.province_id
LEFT JOIN 
    District d ON c.id = d.city_id
WHERE 
    p.name = '广东省';

-- 添加省份
INSERT INTO Province (name) VALUES ('湖南省');

-- 获取新添加的省份ID
SET @province_id = LAST_INSERT_ID();

-- 添加城市
INSERT INTO City (name, province_id) VALUES ('长沙市', @province_id);

-- 获取新添加的城市ID
SET @city_id = LAST_INSERT_ID();

-- 添加区县
INSERT INTO District (name, city_id) VALUES ('岳麓区', @city_id);