CREATE TABLE City (
cid INT NOT NULL,
city VARCHAR ( 50 ) PRIMARY KEY,
pid INT,
constraint `faask` foreign key (pid) references Provincial(pid)
);
二、插入数据
插入省份数据
insert into Provincial values(1,'北京市');
insert into Provincial values(2,'天津市');
insert into Provincial values(3,'上海市');
insert into Provincial values(4,'重庆市');
insert into Provincial values(5,'河北省');
insert into Provincial values(6,'山西省');
insert into Provincial values(7,'台湾省');
insert into Provincial values(8,'辽宁省');
insert into Provincial values(9,'吉林省');
insert into Provincial values(10,'黑龙江省');
insert into Provincial values(11,'江苏省');
insert into Provincial values(12,'浙江省');
insert into Provincial values(13,'安徽省');
insert into Provincial values(14,'福建省');
insert into Provincial values(15,'江西省');
insert into Provincial values(16,'山东省');
insert into Provincial values(17,'河南省');
insert into Provincial values(18,'湖北省');
insert into Provincial values(19,'湖南省');
insert into Provincial values(20,'广东省');
insert into Provincial values(21,'甘肃省');
insert into Provincial values(22,'四川省');
insert into Provincial values(23,'贵州省');
insert into Provincial values(24,'海南省');
insert into Provincial values(25,'云南省');
insert into Provincial values(26,'青海省');
insert into Provincial values(27,'陕西省');
insert into Provincial values(28,'广西壮族自治区');
insert into Provincial values(29,'西藏自治区');
insert into Provincial values(30,'宁夏回族自治区');
insert into Provincial values(31,'新疆维吾尔自治区');
insert into Provincial values(32,'内蒙古自治区');
insert into Provincial values(33,'澳门特别行政区');
insert into Provincial values(34,'香港特别行政区');
插入城市数据
-- 插入各个省的城市数据
-- 4个直辖市
insert into City values(1,'北京市',1);;
insert into City values(1,'天津市',2);;
insert into City values(1,'上海市',3);;
insert into City values(1,'重庆市',4);;
-- select * from City where pid=4
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- 5河北省 11个地级市
insert into City values(1,'石家庄市',5);
insert into City values(2,'唐山市',5);
insert into City values(3,'秦皇岛市',5);
insert into City values(4,'邯郸市',5);
insert into City values(5,'邢台市',5);
insert into City values(6,'保定市',5);
insert into City values(7,'张家口市',5);
insert into City values(8,'承德市',5);
insert into City values(9,'沧州市',5);
insert into City values(10,'廊坊市',5);
insert into City values(11,'衡水市',5);
-- select * from City where pid=5 order by cid
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- 6山西省 11个城市
insert into City values(1,'太原市',6);
insert into City values(2,'大同市',6);
insert into City values(3,'阳泉市',6);
insert into City values(4,'长治市',6);
insert into City values(5,'晋城市',6);
insert into City values(6,'朔州市',6);
insert into City values(7,'晋中市',6);
insert into City values(8,'运城市',6);
insert into City values(9,'忻州市',6);
insert into City values(10,'临汾市',6);
insert into City values(11,'吕梁市',6);
-- select * from City where pid=6 order by cid
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- 7台湾省(台湾本岛和澎湖共设7市、16县,其中台北市和高雄市为“院辖市”,直属“行政院”,其余属台湾省;市下设区,县下设市(县辖市)、镇、乡,合称区市镇乡。);
insert into City values(1,'台北市',7);
insert into City values(2,'高雄市',7);
insert into City values(3,'基隆市',7);
insert into City values(4,'台中市',7);
insert into City values(5,'台南市',7);
insert into City values(6,'新竹市',7);
insert into City values(7,'嘉义市',7);
insert into City values(8,'台北县',7);
insert into City values(9,'宜兰县',7);
insert into City values(10,'桃园县',7);
insert into City values(11,'新竹县',7);
insert into City values(12,'苗栗县',7);
insert into City values(13,'台中县',7);
insert into City values(14,'彰化县',7);
insert into City values(15,'南投县',7);
insert into City values(16,'云林县',7);
insert into City values(17,'嘉义县',7);
insert into City values(18,'台南县',7);
insert into City values(19,'高雄县',7);
insert into City values(20,'屏东县',7);
insert into City values(21,'澎湖县',7);
insert into City values(22,'台东县',7);
insert into City values(23,'花莲县',7);
-- select * from City where pid=7 order by cid
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- 8辽宁省 14个地级市
insert into City values(1,'沈阳市',8);
insert into City values(2,'大连市',8);
inse