中国省市县三表插入SQL语句

--创建省表

create table Provinces(

InternalID nvarchar(10) primary key , --内部ID,主键

GlobalID uniqueidentifier default(newid()) not null,

CreateTime datetime default(getdate()) not null, --创建时间 UpdateTime datetime default(getdate()) not null, --修改时间 ProvinceName nvarchar(50) not null, --省名 )

--创建市表

create table Cities(

InternalID nvarchar(10) primary key , --内部ID,主键

GlobalID uniqueidentifier default(newid()) not null,

CreateTime datetime default(getdate()) not null, --创建时间 UpdateTime datetime default(getdate()) not null, --修改时间 CityName nvarchar(50) not null, --市名

ProvinceInternalID nvarchar(10) not null constraint

ProvinceInternalID_FK --外键,依赖于省表

foreign key (ProvinceInternalID) references Provinces(InternalID) on delete cascade

on update cascade )

--创建县表

create table Prefectures(

InternalID nvarchar(10) primary key , --内部ID,主键

GlobalID uniqueidentifier default(newid()) not null,

CreateTime datetime default(getdate()) not null, --创建时间 UpdateTime datetime default(getdate()) not null, --修改时间 PrefectureName nvarchar(50) not null, --县名

CityInternalID nvarchar(10) not null constraint CityInternalID_FK foreign key (CityInternalID) references Cities(InternalID)

on delete cascade

on update cascade

)

insert into Provinces(InternalID ,ProvinceName ) values('1','北京') insert into Provinces(InternalID ,ProvinceName ) values('2','天津') insert into Provinces(InternalID ,ProvinceName ) values('3','河北') insert into Provinces(InternalID ,ProvinceName ) values('4','山西') insert into Provinces(InternalID ,ProvinceName ) values('5','内蒙古') insert into Provinces(InternalID ,ProvinceName ) values('6','辽宁') insert into Provinces(InternalID ,ProvinceName ) values('7','吉林') insert into Provinces(InternalID ,ProvinceName ) values('8','黑龙江')

insert into Provinces(InternalID ,ProvinceName ) values('9','上海') insert into Provinces(InternalID ,ProvinceName ) values('10','江苏') insert into Provinces(InternalID ,ProvinceName ) values('11','浙江') insert into Provinces(InternalID ,ProvinceName ) values('12','安徽') insert into Provinces(InternalID ,ProvinceName ) values('13','福建') insert into Provinces(InternalID ,ProvinceName ) values('14','江西') insert into Provinces(InternalID ,ProvinceName ) values('15','山东') insert into Provinces(InternalID ,ProvinceName ) values('16','河南') insert into Provinces(InternalID ,ProvinceName ) values('17','湖北') insert into Provinces(InternalID ,ProvinceName ) values('18','湖南') insert into Provinces(InternalID ,ProvinceName ) values('19','广东') insert into Provinces(InternalID ,ProvinceName ) values('20','广西') insert into Provinces(InternalID ,ProvinceName ) values('21','海南') insert into Provinces(InternalID ,ProvinceName ) values('22','重庆') insert into Provinces(InternalID ,ProvinceName ) values('23','四川') insert into Provinces(InternalID ,ProvinceName ) values('24','贵州') insert into Provinces(InternalID ,ProvinceName ) values('25','云南') insert into Provinces(InternalID ,ProvinceName ) values('26','西藏') insert into Provinces(InternalID ,ProvinceName ) values('27','陕西') insert into Provinces(InternalID ,ProvinceName ) values('28','甘肃') insert into Provinces(InternalID ,ProvinceName ) values('29','青海') insert into Provinces(InternalID ,ProvinceName ) values('30','宁夏') insert into Provinces(InternalID ,ProvinceName ) values('31','新疆') insert into Provinces(InternalID ,ProvinceName ) values('32','香港') insert into Provinces(InternalID ,ProvinceName ) values('33','澳门') insert into Provinces(InternalID ,ProvinceName ) values('34','台湾')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('1','1','北京市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('2','2','天津市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('3','3','石家庄市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('4','3','唐山市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('5','3','秦皇岛市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('6','3','邯郸市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('7','3','邢台市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('8','3','保定市')

values('9','3','张家口市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('10','3','承德市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('11','3','沧州市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('12','3','廊坊市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('13','3','衡水市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('14','4','太原市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('15','4','大同市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('16','4','阳泉市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('17','4','长治市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('18','4','晋城市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('19','4','朔州市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('20','4','晋中市')

insert into Cities(InternalID ,ProvinceInternalID,CityName ) values('21','4','运城市')

insert into C …… 此处隐藏：28536字，全部文档内容请下载后查看。喜欢就下载吧 ……