省市级联数据

--5河北省 11个地级市
insert into city values(5,'石家庄市',5);
insert into city values(6,'唐山市',5);
insert into city values(7,'秦皇岛市',5);
insert into provincial values(12,'浙江省');
insert into provincial values(13,'安徽省');
insert into provincial values(14,'福建省');
--6山西省 11个城市
insert into city values(16,'太原市',6);
insert into city values(17,'大同市',6);
insert into city values(18,'阳泉市',6);
---------------------------------------------------------------
--插入各个省的城市数据
--4个直辖市
insert into city values(1,'北京市',1);
insert into city values(2,'天津市',2);
insert into city values(11,'张家口市',5);
insert into city values(12,'承德市',5);
insert into city values(13,'沧州市',5);
insert into city values(14,'廊坊市',5);
insert into city values(19,'长治市',6);
insert into city values(20,'晋城市',6);
insert into city values(21,'朔州市',6);
insert into city values(22,'晋中市',6);
insert into provincial values(24,'海南省');
insert into provincial values(25,'云南省');
insert into provincial values(26,'青海省');
insert into city values(23,'运城市',6);
insert into city values(24,'忻州市',6);
insert into city values(25,'临汾市',6);
insert into city values(26,'吕梁市',6);
insert into provincial values(4,'重庆市');
insert into provincial values(5,'河北省');
insert into provincial values(6,'山西省');
insert into provincial values(7,'台湾省');
--城市 city
create table city
(
cityID int not null,
cityName varchar(50) not null,
provincialID int not null,
primary key (cityID)
)
alter table city add constraint FK_pro_city_provincialID foreign key (provincialID)
--省级 provincial
create table provincial
(
provincialID int,
provincialName varchar(50),
primary key (provincialID)
insert into provincial values(27,'陕西省');
insert into provincial values(28,'广西壮族自治区');
insert into provincial values(29,'西藏自治区');
insert into city values(15,'衡水市',5);
--select * from city where provincialID=5 order by cityID
----------------------------------------------------------------
insert into city values(35,'宜兰县',7);
insert into city values(36,'桃园县',7);
insert into city values(37,'新竹县',7);
insert into city values(38,'苗栗县',7);
insert into city values(27,'台北市',7);
insert into city values(28,'高雄市',7);
insert into city values(29,'基隆市',7);
insert into city values(30,'台中市',7);
insert into city values(8
,'邯郸市',5);
insert into city values(9,'邢台市',5);
insert into city values(10,'保定市',5);
references provincial (provincialID);
----------------------------------------------------------------------------------------------------------------------------------------
insert into provincial values(8,'辽宁省');
insert into provincial values(9,'吉林省');
insert into provincial values(10,'黑龙江省');
insert into provincial values(11,'江苏省');
insert into provincial values(15,'江西省');
insert into provincial values(16,'山东省');
insert into provincial values(17,'河南省');
insert into city values(31,'台南市',7);
insert into city values(32,'新竹市',7);
insert into city values(33,'嘉义市',7);
insert into city values(34,'台北县',7);
insert into city values(39,'台中县',7);
insert into city values(40,'彰化县',7);
insert into city values(41,'南投县',7);
insert into city values(42,'云林县',7);
insert into provincial values(21,'甘肃省');