mysql查询省份下的城市

CREATE TABLE provinces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE cities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    province_id INT,
    FOREIGN KEY (province_id) REFERENCES provinces(id)
);

SELECT cities.name 
FROM cities 
JOIN provinces ON cities.province_id = provinces.id 
WHERE provinces.name = '广东省';

CREATE INDEX idx_province_id ON cities(province_id);
CREATE INDEX idx_province_name ON provinces(name);

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

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

-- 插入示例数据
INSERT INTO provinces (name) VALUES ('广东省');
INSERT INTO cities (name, province_id) VALUES ('广州市', 1), ('深圳市', 1);

-- 查询广东省下的所有城市
SELECT cities.name 
FROM cities 
JOIN provinces ON cities.province_id = provinces.id 
WHERE provinces.name = '广东省';