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 county (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES city(id)
);

INSERT INTO province (name) VALUES ('广东省');
INSERT INTO city (name, province_id) VALUES ('广州市', 1);
INSERT INTO county (name, city_id) VALUES ('天河区', 1);

SELECT p.name AS province, c.name AS city, k.name AS county
FROM province p
JOIN city c ON p.id = c.province_id
JOIN county k ON c.id = k.city_id
WHERE p.name = '广东省';