--不足：23山东和16山东重复
--创建DBPromary数据库
create database DBPromary
use DBPromary
go

--创建promary表
create table promary
(
proID int primary key,
proName varchar(50) not null
)

----------------------------------------------------------------------------------------------------------------------------------------
--中国34个省级行政单位 23个省 5个自治区 4个直辖市 2特别行政区
insert into promary values(1,'北京市')
insert into promary values(2,'天津市')
insert into promary values(3,'上海市')
insert into promary values(4,'重庆市')
insert into promary values(5,'河北省')
insert into promary values(6,'山西省')
insert into promary values(7,'台湾省')
insert into promary values(8,'辽宁省')
insert into promary values(9,'吉林省')
insert into promary values(10,'黑龙江省')
insert into promary values(11,'江苏省')
insert into promary values(12,'浙江省')
insert into promary values(13,'安徽省')
insert into promary values(14,'福建省')
insert into promary values(15,'江西省')
insert into promary values(16,'山东省')
insert into promary values(17,'河南省')
insert into promary values(18,'湖北省')
insert into promary values(19,'湖南省')
insert into promary values(20,'广东省')
insert into promary values(21,'甘肃省')
insert into promary values(22,'四川省')
--insert into promary values(23,'山东省')
insert into promary values(24,'贵州省')
insert into promary values(25,'海南省')
insert into promary values(26,'云南省')
insert into promary values(27,'青海省')
insert into promary values(28,'陕西省')
insert into promary values(29,'广西壮族自治区')
insert into promary values(30,'西藏自治区')
insert into promary values(31,'宁夏回族自治区')
insert into promary values(32,'新疆维吾尔自治区')
insert into promary values(33,'内蒙古自治区')
insert into promary values(34,'澳门特别行政区')
insert into promary values(35,'香港特别行政区')
----------------------------------------------------------------------------------------------------------------------------------------

--创建city表
create table city
(
cityID int not null,
cityName varchar(50) primary key,
proID int foreign key references promary(proID)
)


----------------------------------------------------------------------------------------------------------------------------------------
--插入各个省的城市数据
--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)
--5河北省(2005年辖：11个地级市，36个市辖区、22个县级市、108个县、6个自治县)
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)
--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)
--7台湾省(台湾本岛和澎湖共设7市、16县，其中台北市和高雄市为“院辖市”，直属“行政院”，