SQL语句大全(几乎所有常用的示例,包括省市号码源码)

最新推荐文章于 2023-11-22 11:20:30 发布

ya1la2

最新推荐文章于 2023-11-22 11:20:30 发布

阅读量1.6k

点赞数

分类专栏： sql

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

csharp]view plaincopy

create table Student
(id int not null ,
name nvarchar(50),
age int null
)
drop table student
insert into Student(id,name,age)values(2,'Lucy',23)
---------------------------------------------------------------
--newid()方法获取唯一性的值(标识列/uniqueidentifier类型 newid()) GUID类型
select NEWID();
-----------------------------------------------------------------
create table Person
(id int not null,
name nvarchar(50),
age int null)
--删除 drop（删除表）和delete（清空数据）
drop table person
insert into person (name,age,adress)values ('lily',20,'Beijing')
insert into person values ('Jack',25,'Chongqing')/*可以省略*/
insert into student (id,name,age)values (NEWID(),'xiaodu',24)
------------------------------------------------------------------
insert into tbGuestBook(ID,UserName,PostTime,Message,IsReplied)values(NEWID(),'Jack',GETDATE(),'I love this game!','true')
insert into tbGuestBook(UserName,PostTime,Message,IsReplied)values('Lucy',GETDATE(),'My hobby is play basketball!','false')
insert into tbGuestBook(UserName,PostTime,Message,IsReplied)values('Mike',GETDATE(),'My hobby is play basketball!','true')
--可以不写newid() 因为可以增加自动--（即标识列），一般不要省略后面字段名列表,否则很麻烦
insert into tbGuestBook(UserName,PostTime,Message,IsReplied)values('royal',GETDATE(),'just do it!','true')
----------------------------------------------------------------------------------------------------
--更新一个列
update Person set age=30 ;
update Person set age=age+1 ;
update Person set adress='beijing' where age<30;
alter table person add Nickname varchar(50);
--提示列名无效，重启后问题解决，可能是缓存问题
--注意如果有汉字或者其他国家的字体最好加上 N
update Person set Nickname=N'青年人' where age<30;
--SQL中 ,只能一个=号
update Person set nickname=N'25岁' where age=25;
update Person set nickname=N'青年' where age=25 or age=35;
--与或非的用法
update Person set nickname=N'青年' where (age<25 and age>35)or age=80;
---------------------------------------------------------------------------------------------------
create table Employee(FNumber varchar(20),FName varchar(20),FAge int ,FSalary numeric(10,2),primary key(FNumber));
insert into Employee(FNumber,FName,FAge,FSalary)values('DEV001','Tom',26,2800.80);
insert into Employee(FNumber,FName,FAge,FSalary)values('DEV002','Lucy',20,27900.80);
insert into Employee(FNumber,FName,FAge,FSalary)values('SALE3001','Jack',25,8800.80);
insert into Employee(FNumber,FName,FAge,FSalary)values('SALE3002','Lily',27,9000.80);
insert into Employee(FNumber,FName,FAge,FSalary)values('DEV003','Leo',60,5800.80);
insert into Employee(FNumber,FName,FAge,FSalary)values('HR001','Duyu',35,27800.80);
insert into Employee(FNumber,FName,FAge,FSalary)values('IT001','Loyol',25,3800.80);
insert into Employee(FNumber,FName,FAge,FSalary)values('IT002','Mike',50,25600.10);
insert into Employee(FNumber,FAge,FSalary)values('HR002',500000.00,25600.10);
select * from Employee where FSalary<5000
--别名 as
select fname as 姓名,fage as 年龄,FSalary+100000 as 月薪 from Employee;
---------------------------------------------------------------------
select GETDATE();--日期
select @@VERSION;--版本号
---------------------------------------------------------------------
--可以写和表不相关的查询
select 1+1 as 列1,NEWID() as 编号,GETDATE() as 时间;
Go
--统计数据
select COUNT(*) as 数据条数 from Employee
select MAX(FSalary) as 最高薪资 from Employee
select MIN(fsalary) as 最低薪资 from Employee
select AVG(fsalary) as 平均薪资 from Employee
select SUM(fsalary) as 总金额 from Employee;
Go
select COUNT(*) from Employee where FSalary>5000;
------------------------------------------------------------------------
--数据排序(升序ASC （默认值）) 降序 DESC
select * from Employee order by FAge desc
-----------------------------------------------------------------------
--首先按FAge升序排列，再排好之后其中进行FSalary降序排列（用逗号隔开的多个 ‘父子’的优先级关系）
select * from Employee order by FAge asc,FSalary desc;
--where 在order by 之前
select * from Employee where FSalary>5000 order by FAge asc,FSalary desc;
----------------------------------------------------------------------------------
--通配符/文本过滤 */ 模糊匹配 %
select * from Employee where FName like '%L%';
--null 表示 ‘不知道’,
--‘不知道’+10>10? = ‘不知道 ’
select null +1;
select null +'abc';
--select 123+'abc'; c\出错，varchar 不能转化为int
select '123'+'abc';
-------------------------------------------------------------------
--基于以上原因，查不出数据
select * from Employee where FName=null;
select * from Employee where FName<>null;
-- SQL中的方法
select * from Employee where FAge is null;
--------------------------------------------------------------------------
--多值匹配
select * from Employee where FAge=23 or FAge=25 or FAge=28;
select * from Employee where FAge in(23,25,28);
select * from Employee where FAge>20 and FAge<30;
select * from Employee where FAge between 20 and 30;
---------------------------------------------------------------------------------
--数据分组,按每一个分组单独计算count(*) -- 取每组人数
select FAge,COUNT(*) as 数量 from Employee group by FAge;
--
select COUNT(*) as 数量 from Employee ;
----------------------------------------------------------------------------------------------
--select中不能出现group by中没有出现的列(聚合函数除外！！！),且group by要出现在where 后面
select FAge,fname ,COUNT(*) as 数量 from Employee group by FAge,FName;
--因为该列没有包含在聚合函数或 GROUP BY 子句中(错误！)
select FAge,fname ,COUNT(*) as 数量 from Employee group by FAge;
--但是可以取一个值，max /min
select FAge,MAX(FSalary) as 最大薪资 from Employee group by FAge;
select FAge,min(FSalary) as 最小薪资 from Employee group by FAge;
select FAge,avg(FSalary) as 平均薪资 from Employee group by FAge;
--------------------------------------------------------------
--Having 子句
--由于聚合函数不能出现在where子句中所以采用Having子句
select fage,COUNT(*) from Employee group by FAge Having COUNT(*)>1;
select fage,COUNT(*) from Employee where FSalary>2000 group by FAge Having COUNT(*)>1
--因为该列没有包含在聚合函数或 GROUP BY 子句中.
--Having 只能用于分组过后的过滤,where 是对原始数据的过滤 ,因此Having 不能代替Where
select fage,COUNT(*) from Employee group by FAge Having FSalary>2000;
--限制结果集行数
select * from Employee order by FSalary desc;
select TOP 3 * from Employee order by FSalary desc;--前三行
--查询不包括前2条数据的前5条
--注意子查询中'select top 5 *'指返回所有字段，这样写父查询不能识别
select top 5 *
from Employee
where FName not in(select top 2 fname from Employee order by FSalary desc)
order by FSalary desc
------------------------------------------------------------------------------
--去除重复数据
--alter 修改表
alter table employee add FsubCompany varchar(20);
alter table employee add FDepartment varchar(20);
update Employee set fsubcompany='Beijing',Fdepartment='Development' where FNumber='DEV001'
update Employee set fsubcompany='Shenzhen',Fdepartment='HumanResource' where FNumber='DEV002'
update Employee set fsubcompany='Chongqing',Fdepartment='Development' where FNumber='HR001'
update Employee set fsubcompany='HongKong',Fdepartment='InfoTech' where FNumber='IT001'
update Employee set fsubcompany='Qingdao',Fdepartment='Sales' where FNumber='IT002'
update Employee set fsubcompany='Guangdong',Fdepartment='Sales' where FNumber='SALE001'
--查询所有员工部门信息
select Fdepartment,Fsubcompany from Employee
--distinct指不重复的值 --> (Fdepartment,fsubcompany)同时不相同时,不是指消除单独的相同列
select distinct Fdepartment,fsubcompany from Employee
------------------------------------------------------------------------------------------------------
-- 联合union ,默认将重复数据去除
create table TempEmployee(FIdCardNumber varchar(20),FName varchar(20),FAge int ,primary key(FIdCardNumber));
insert into TempEmployee (FIdCardNumber, FName,FAge)values ('1234567890121','Sara',23);
insert into TempEmployee (FIdCardNumber, FName,FAge)values ('1234567890122','Tom',33);
insert into TempEmployee (FIdCardNumber, FName,FAge)values ('1234567890123','Lily',26);
insert into TempEmployee (FIdCardNumber, FName,FAge)values ('1234567890124','Lucy',38);
insert into TempEmployee (FIdCardNumber, FName,FAge)values ('1234567890125','Jack',26);
insert into TempEmployee (FIdCardNumber, FName,FAge)values ('1234567890126','Leo',29);
--两个查询结果合并到一个查询结果表返回（两个查询语句列数要相同，且字段的类型要相容如int和bigint是相容的）
select fname ,fage from TempEmployee
union
select fname,fage from Employee
select fnumber,fname,fage,FDepartment from Employee
union
--列数要补足
select fidcardnumber ,fname,fage,'临时工，无部门' from TempEmployee
--union all 不消除重复行 ,完全显示(默认和distinc 一样消除重复行)
select fname from Employee
union All
select fname from TempEmployee
--报表
select '正式员工最高年龄',MAX(fage) from Employee
union all
select '正式员工最低年龄' ,MIN(fage) from Employee
union all
select '临时工最高年龄',MAX(fage) from TempEmployee
union all
select '临时工最低年龄',MIN(fage) from TempEmployee
select fnumber,fsalary from Employee
union
select '工资合计',SUM(fsalary) from Employee
---------------------------------------------------2012-04-30-------------------------------------------------------------
--SQL内置库函数
select round (-3.61,0);
select Round (-3.1415926,3)//后面是小数的精度
select ABS(-110)//求绝对值
select ceiling(3.33)//舍入到最大整数
select floor(3.33)//舍入到最小整数
select len('asdfdfdfffdg5hfh54657')//计算长度
select len(adress) from student
//去掉左右空格
select ltrim(' aaa '), rtrim(' bbb '),len(ltrim(rtrim(' bbbaaa ')));
select substring('abcdefghi',2,3)//截取第二个字符起的三个字符
select adress ,substring(adress,2,2) as 截取地址 from student
-------------------------------------------------------------------
----------------------------日期函数-----------------
select getdate();
select dateadd(day,-3,getdate());//在当前日期上减去3天
select dateadd(hh,1,getdate()) ;//在当前日期上增加一小时
select datediff(hh,getdate(),dateadd(day,-3,getdate()))//计算日期差额
--获取职员入职时间
select fname,findate, datediff(year,findate,getdate())from employee;
--取出员工入职年份信息
select datediff(year,findate,getdate()) as 已入职__年, count(*) 人数
from employee
group by datediff(year,findate,getdate());
-----------------------------
--取出入职5年以上的员工信息
select datediff(year,findate,getdate()) as 已入职__年, count(*) as 人数
from employee
where datediff(year,findate,getdate())>8
group by datediff(year,findate,getdate())
--整体一个语句的话,中间不能有';'冒号,因为这是一个事务,用冒号则是两个事务
having count(*)>1;
--------------------------------
--获取时间特定部分
select datepart(yy,getdate())//写year也可以
select DATEPART(MM,getdate())
select datepart(mi,getdate())
select datepart(dd,getdate())
select datepart(hh,getdate())
select datepart(year,FinDate) as 入职年份,count(*) as 人数 from employee group by datepart(year,FinDate);
----------------------类型转换函数-------------------------
--convert 和 cast 差不多
select cast('123' as int ) ,cast('2008-08-08' as datetime),
convert(datetime,'2009-09-09'),convert (varchar (50),123);
--不好的写法
select '100'+1
--标准写法
select convert(int ,'100')+1
-----------------------空值处理函数------------------------
select isnull(fname,'佚名') as 姓名 from employee
-----------------------——--------------------------CASE函数---------------------------------------------------------------
select
FName, --此处有个逗号！
(
case FLevel
when 1 then '游客'
when 2 then '会员'
when 3 then 'VIP'
else '未知客户'
end
)as 客户类型
from Customer
-----------
select fname,
(
case --此处不能写表达式，因为when的表达式不是单值类型，而是一个判断表达式
when fsalary<2000 then '低收入'
when fsalary>=2000 and fsalary<5000 then '中等收入'
else '高收入'
end
) as 收入水平
from employee
-----------------------------------------------------------------------------
--练习1
select FNumber,
(
case
when FAmount>0 then FAmount
else 0
end
) as 收入 , --记得此处有个逗号
(
case
when FAmount <0 then abs(FAmount)
else 0
end
) as 支出
from T_Text;
--------------------------------------------------------------------------
--练习2
create table [Scores]
(
[Date] datetime null,
--COLLATE Chinese_PRC_CI_AS null,--这是字符集排列规则：
--大陆unicode码排序规则，当跨库连接表查询时，不同默认字符集排列规则会报错
Name nvarchar(50) COLLATE Chinese_PRC_CI_AS null,
Score nvarchar (50) COLLATE Chinese_PRC_CI_AS null
);
insert [Scores]([date],name,score)values (cast (0x00009AF200000000 as datetime),N'奇才',N'胜');
insert [Scores]([date],name,score)values (cast (0x00009AF300000000 as datetime),N'拜仁',N'负');
insert [Scores]([date],name,score)values (cast (0x00009AF400000000 as datetime),N'湖人',N'负');
insert [Scores]([date],name,score)values (cast (0x00009AF500000000 as datetime),N'小牛',N'负');
insert [Scores]([date],name,score)values (cast (0x00009AF600000000 as datetime),N'火箭',N'胜');
insert [Scores]([date],name,score)values (cast (0x00009AF700000000 as datetime),N'拜仁',N'负');
insert [Scores]([date],name,score)values (cast (0x00009AF800000000 as datetime),N'奇才',N'胜');
insert [Scores]([date],name,score)values (cast (0x00009AF900000000 as datetime),N'湖人',N'负');
insert [Scores]([date],name,score)values (cast (0x00009AF1000000000 as datetime),N'湖人',N'负');
insert [Scores]([date],name,score)values (cast (0x00009AF1100000000 as datetime),N'湖人',N'负');
-----------------写法1----------------------
select name,
count( --count 函数是有一次记录就累加一次
case score --when表达式不能有运算符！！！
when N'胜' then 1
else 0
end
)as 胜,
count(
case score
when N'负' then 1
else 0
end
)as 负
from scores
group by name;--按名字来分组
-------------写法2-----------------
select name,
sum( --sum函数是加法运算
case score --when表达式不能有运算符！！！
when N'胜' then 1
else 0
end
)as 胜,
sum(
case score
when N'负' then 1
else 0
end
)as 负
from scores
group by name;--按名字来分组
-------------------------------------------索引(Index)------------------------------------
--一个表可以建立多个索引，但占空间，insert,update,delete也需要同步更新索引，降低速度
--全表扫描select效率最差
--原则:经常需要查询的列建立索引
--即使有索引，也可能全表扫描。比如like 、函数、类型转换
select * from Employee where Fname='tom'
-------------------------------------------表连接Join------------------------
create table Customers
(
[Id] [int] not null,
[Name]nvarchar(50) collate Chinese_PRC_CI_AS null,
age int null
);
insert Customers (Id,Name,age) values(1,N'tom',10);
insert Customers(Id,Name,age) values (2,N'Jerry',15);
insert Customers(Id,Name,age) values (3,N'John',22);
insert Customers(Id,Name,age) values (4,N'Lily',18);
insert Customers(Id,Name,age) values (5,N'Lucy',18);
create table Orders
(
Id int not null,
BillNo nvarchar(50) collate Chinese_PRC_CI_AS null,
CustomerId int null
);
insert Orders(Id,BillNo,CustomerId)values(1,N'001',1);
insert Orders(Id,BillNo,CustomerId)values(2,N'002',1);
insert Orders(Id,BillNo,CustomerId)values(3,N'003',3);
insert Orders(Id,BillNo,CustomerId)values(4,N'004',4);
insert Orders(Id,BillNo,CustomerId)values(5,N'005',2);
insert Orders(Id,BillNo,CustomerId)values(6,N'006',5);
insert Orders(Id,BillNo,CustomerId)values(7,N'007',4);
insert Orders(Id,BillNo,CustomerId)values(8,N'008',6);
select * from Orders;
--Join ...on...
select o.BillNo ,c.Name,c.age
from Orders as o join Customers as c on o.CustomerId=c.Id
----
select o.BillNo ,c.Name,c.age
from Orders as o join Customers as c on o.CustomerId=c.Id
where c.age>15;
----
--显示年龄大于平均年龄的顾客购买订单
--返回单值的子查询
select o.BillNo ,c.Name,c.age
from Orders as o join Customers as c on o.CustomerId=c.Id
where c.age>(
select AVG(age) from Customers
);
-----------------子查询-----------------
--相当于把子查询当成一个表(单值/多值)
select * from
(
select * from Orders
)as o1
---------------(无表)子查询当做一个列/单值------
select 1 as F1,2 as F2,
(select min(FYearPublished) from T_Book)as F3,
(select MAX(FYearPublished) from Book)as F4;
---------------返回多值-----------
--多行单列子查询--其实是一个集合
select * from Reader
where FYearofJoin in(2001,2010);
--
select * from Reader
where FYearofJoin in //not in
(
select distinct FYearofJoin from Reader --不加distinct也会过滤掉重复值
);
-------内置函数 row_number()
-------注意此窗口函数不能用于where,只能出现在select和OrderBy
select * from
(
select ROW_NUMBER() over(order by FSalary desc) as rownum,
Fnumber,Fname,Fsalary,Fage from Employee
) as e1
where e1.rownum>3and e1.rownum<=5;
--------------------------综合练习------------------------------
create table CallRecords
(id int ,
CallerNumber int,
TelNum int,
StartDateime datetime ,
EndDatime datetime
)
insert CallRecords(id,CallerNumber,TelNum,StartDateime,EndDatetime)
values(1,001,02088888,'2009-06-15 00:00:00','2009-06-15 00:15:20');
insert CallRecords(id,CallerNumber,TelNum,StartDateime,EndDatetime)
values(2,001,02077777,'9/8/2002 08:00:00 AM','9/8/2002 08:25:00 AM');
insert CallRecords(id,CallerNumber,TelNum,StartDateime,EndDatetime)
values(3,002,02066666,'8/8/2002 12:00:00 AM','8/8/2002 12:10:00 AM');
insert CallRecords(id,CallerNumber,TelNum,StartDateime,EndDatetime)
values(4,001,02012345,'8/8/2002 12:00:00 AM','8/8/2002 12:45:00 AM');
insert CallRecords(id,CallerNumber,TelNum,StartDateime,EndDatetime)
values(5,003,020456789,'8/8/2008 12:00:00 AM','8/8/2008 12:50:00 AM');
insert CallRecords values(6,003,02055555,'2010-06-15 00:00:00','2010-06-15 00:50:20');
insert CallRecords values(7,004,0203232443,'2008-06-15 00:00:00','2008-06-15 08:50:20');
insert CallRecords values(8,005,564776757,'2010-06-11 00:00:00','2010-06-11 05:50:20');
insert CallRecords values(9,008,345466,'2010-06-15 11:00:00','2010-06-15 21:11:20');
insert CallRecords values(10,008,02564,'2010-07-15 02:00:00','2010-07-15 04:12:20');
--①输出所有数据中通话时间最长的5条记录. orderby() / datediff()
/*
select top 5 *
from CallRecords
where id not in(select top 2 id,datediff(mm,StartDateime,EndDatetime) as 通话时间,count(*)as 人数
from CallRecords
group by datediff(mm,StartDateime,EndDatetime),id
order by id desc)
order by id desc;
*/
--修改 --输出最长通话记录的前2~7条
select top 5 *
from CallRecords
where id not in(select top 2 id
from CallRecords
order by datediff(mm,StartDateime,EndDatetime) desc)
--order by id asc;
----结果---
select top 5 * --,count(*)as 人数
from CallRecords
--group by datediff(mm,StartDateime,EndDatetime)
order by datediff(mm,StartDateime,EndDatetime) desc;
--------参考案例------
select datediff(year,findate,getdate()) as 已入职__年, count(*) as 人数 --可以不要as
from employee
group by datediff(year,findate,getdate());
select top 5 *
from Employee
where FName not in(select top 2 fname from Employee order by FSalary desc)
order by FSalary desc;
-----------END---------
--②输出所有数据中拨打长途号码（TelNum号码0开头）总时长，like/sum
select * from CallRecords where TelNum like '0%';
select sum(datediff(ss,StartDateime,EndDatetime)) as 长途电话总时长 from CallRecords where TelNum like '0%';
--③输出本月通话总时长最多的前三个呼叫员的编号
--说明只要月份不同就算就不算同月,不按30天来计算
select datediff(mm,convert(datetime,'2011-06-01'),convert(datetime,'2011-07-02')),
datediff(mm,convert(datetime,'2011-06-15'),convert(datetime,'2011-07-02'));
select CallerNumber,TelNum,datediff(month,StartDateime,EndDatetime) from CallRecords;
select * from CallRecords where datediff(month,StartDateime,EndDatetime)=0;
--结果--
select top 3 CallerNumber from CallRecords--取出前三条呼叫员的编号
where datediff(month,StartDateime,EndDatetime)=0--取当前月
group by CallerNumber --按编号分组,消掉重复值
order by sum(datediff(second,StartDateime,EndDatetime)) desc;--按通话时间降序排列
--④本月打电话次数最多的前三个呼叫人员编号
select top 3 CallerNumber ,count(*) as 次数
from CallRecords
where datediff(month,StartDateime,EndDatetime)=0
group by CallerNumber
order by CallerNumber asc;
--⑤汇总[市内号码总时长][长途号码总时长]
select
sum(
case TelNum not like '0%' then DATEDIFF (SECOND,StartDateime,EndDatetime)
else 0
end
) as 市内通话,
sum(
case TelNum like '0%' then DATEDIFF (SECOND,StartDateime,EndDatetime)
else 0
end
) as 长途通话
from CallRecords;
--⑥呼叫员编号，对方号码，通话总时长
select CallerNumber,TelNum,DATEDIFF(SS,StartDateime,EndDatetime)
from CallRecords
union all
select '汇总',
convert(varchar(50),
sum((
case
when TelNum not like '0%' then DATEDIFF (SECOND,StartDateime,EndDatetime)
else 0
end
))
)as 市内通话,
sum((
case
when TelNum like '0%' then DATEDIFF (SECOND,StartDateime,EndDatetime)
else 0
end
)) as 长途通话
from CallRecords;
----------
--电话号码不能设置为int或者bigint 028... 会自动去除首位0
--电话号码一般用varchar
----------
-------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
--全国省市数据库
--创建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县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡。)
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)
--8辽宁省(2006年，辖：14个地级市；56个市辖区、17个县级市、19个县、8个自治县。)
insert into city values(1,'沈阳市',8)
insert into city values(2,'大连市',8)
insert into city values(3,'鞍山市',8)
insert into city values(4,'抚顺市',8)
insert into city values(5,'本溪市',8)
insert into city values(6,'丹东市',8)
insert into city values(7,'锦州市',8)
insert into city values(8,'营口市',8)
insert into city values(9,'阜新市',8)
insert into city values(10,'辽阳市',8)
insert into city values(11,'盘锦市',8)
insert into city values(12,'铁岭市',8)
insert into city values(13,'朝阳市',8)
insert into city values(14,'葫芦岛市',8)
--9吉林省(2006年，辖：8个地级市、1个自治州；20个市辖区、20个县级市、17个县、3个自治县。)
insert into city values(1,'长春市',9)
insert into city values(2,'吉林市',9)
insert into city values(3,'四平市',9)
insert into city values(4,'辽源市',9)
insert into city values(5,'通化市',9)
insert into city values(6,'白山市',9)
insert into city values(7,'松原市',9)
insert into city values(8,'白城市',9)
insert into city values(9,'延边朝鲜族自治州',9)
--10黑龙江省(2006年，辖：12地级市、1地区；64市辖区、18县级市、45县、1自治县)
insert into city values(1,'哈尔滨市',10)
insert into city values(2,'齐齐哈尔市',10)
insert into city values(3,'鹤岗市',10)
insert into city values(4,'双鸭山市',10)
insert into city values(5,'鸡西市',10)
insert into city values(6,'大庆市',10)
insert into city values(7,'伊春市',10)
insert into city values(8,'牡丹江市',10)
insert into city values(9,'佳木斯市',10)
insert into city values(10,'七台河市',10)
insert into city values(11,'黑河市',10)
insert into city values(12,'绥化市',10)
insert into city values(13,'大兴安岭地区',10)
--11江苏省(2005年辖：13个地级市；54个市辖区、27个县级市、25个县)
insert into city values(1,'南京市',11)
insert into city values(2,'无锡市',11)
insert into city values(3,'徐州市',11)
insert into city values(4,'常州市',11)
insert into city values(5,'苏州市',11)
insert into city values(6,'南通市',11)
insert into city values(7,'连云港市',11)
insert into city values(8,'淮安市',11)
insert into city values(9,'盐城市',11)
insert into city values(10,'扬州市',11)
insert into city values(11,'镇江市',11)
insert into city values(12,'泰州市',11)
insert into city values(13,'宿迁市',11)
--12浙江省(2006年，辖：11个地级市；32个市辖区、22个县级市、35个县、1个自治县。)
insert into city values(1,'杭州市',12)
insert into city values(2,'宁波市',12)
insert into city values(3,'温州市',12)
insert into city values(4,'嘉兴市',12)
insert into city values(5,'湖州市',12)
insert into city values(6,'绍兴市',12)
insert into city values(7,'金华市',12)
insert into city values(8,'衢州市',12)
insert into city values(9,'舟山市',12)
insert into city values(10,'台州市',12)
insert into city values(11,'丽水市',12)
--13安徽省(2005年辖：17个地级市；44个市辖区、5县个级市、56个县。)
insert into city values(1,'合肥市',13)
insert into city values(2,'芜湖市',13)
insert into city values(3,'蚌埠市',13)
insert into city values(4,'淮南市',13)
insert into city values(5,'马鞍山市',13)
insert into city values(6,'淮北市',13)
insert into city values(7,'铜陵市',13)
insert into city values(8,'安庆市',13)
insert into city values(9,'黄山市',13)
insert into city values(10,'滁州市',13)
insert into city values(11,'阜阳市',13)
insert into city values(12,'宿州市',13)
insert into city values(13,'巢湖市',13)
insert into city values(14,'六安市',13)
insert into city values(15,'亳州市',13)
insert into city values(16,'池州市',13)
insert into city values(17,'宣城市',13)
--14福建省(2006年辖：9个地级市；26个市辖区、14个县级市、45个县。)
insert into city values(1,'福州市',14)
insert into city values(2,'厦门市',14)
insert into city values(3,'莆田市',14)
insert into city values(4,'三明市',14)
insert into city values(5,'泉州市',14)
insert into city values(6,'漳州市',14)
insert into city values(7,'南平市',14)
insert into city values(8,'龙岩市',14)
insert into city values(9,'宁德市',14)
--15江西省(2006年全省辖：11个地级市；19个市辖区、10个县级市、70个县。)
insert into city values(1,'南昌市',15)
insert into city values(2,'景德镇市',15)
insert into city values(3,'萍乡市',15)
insert into city values(4,'九江市',15)
insert into city values(5,'新余市',15)
insert into city values(6,'鹰潭市',15)
insert into city values(7,'赣州市',15)
insert into city values(8,'吉安市',15)
insert into city values(9,'宜春市',15)
insert into city values(10,'抚州市',15)
insert into city values(11,'上饶市',15)
--16山东省(2005年，辖：17个地级市；49个市辖区、31个县级市、60个县。)
insert into city values(1,'济南市',16)
insert into city values(2,'青岛市',16)
insert into city values(3,'淄博市',16)
insert into city values(4,'枣庄市',16)
insert into city values(5,'东营市',16)
insert into city values(6,'烟台市',16)
insert into city values(7,'潍坊市',16)
insert into city values(8,'济宁市',16)
insert into city values(9,'泰安市',16)
insert into city values(10,'威海市',16)
insert into city values(11,'日照市',16)
insert into city values(12,'莱芜市',16)
insert into city values(13,'临沂市',16)
insert into city values(14,'德州市',16)
insert into city values(15,'聊城市',16)
insert into city values(16,'滨州市',16)
insert into city values(17,'菏泽市',16)
--17河南省（2005年辖：17个地级市；50个市辖区、21个县级市、88个县。）
insert into city values(1,'郑州市',17)
insert into city values(2,'开封市',17)
insert into city values(3,'洛阳市',17)
insert into city values(4,'平顶山市',17)
insert into city values(5,'安阳市',17)
insert into city values(6,'鹤壁市',17)
insert into city values(7,'新乡市',17)
insert into city values(8,'焦作市',17)
insert into city values(9,'濮阳市',17)
insert into city values(10,'许昌市',17)
insert into city values(11,'漯河市',17)
insert into city values(12,'三门峡市',17)
insert into city values(13,'南阳市',17)
insert into city values(14,'商丘市',17)
insert into city values(15,'信阳市',17)
insert into city values(16,'周口市',17)
insert into city values(17,'驻马店市',17)
insert into city values(18,'济源市',17)
--18湖北省（截至2005年12月31日，全省辖13个地级单位（12个地级市、1个自治州）；102县级单位（38个市辖区、24个县级市、37个县、2个自治县、1个林区），共有1220个乡级单位（277个街道、733个镇、210个乡）。）
insert into city values(1,'武汉市',18)
insert into city values(2,'黄石市',18)
insert into city values(3,'十堰市',18)
insert into city values(4,'荆州市',18)
insert into city values(5,'宜昌市',18)
insert into city values(6,'襄樊市',18)
insert into city values(7,'鄂州市',18)
insert into city values(8,'荆门市',18)
insert into city values(9,'孝感市',18)
insert into city values(10,'黄冈市',18)
insert into city values(11,'咸宁市',18)
insert into city values(12,'随州市',18)
insert into city values(13,'仙桃市',18)
insert into city values(14,'天门市',18)
insert into city values(15,'潜江市',18)
insert into city values(16,'神农架林区',18)
insert into city values(17,'恩施土家族苗族自治州',18)
--19湖南省（2005年辖：13个地级市、1个自治州；34个市辖区、16个县级市、65个县、7个自治县。）
insert into city values(1,'长沙市',19)
insert into city values(2,'株洲市',19)
insert into city values(3,'湘潭市',19)
insert into city values(4,'衡阳市',19)
insert into city values(5,'邵阳市',19)
insert into city values(6,'岳阳市',19)
insert into city values(7,'常德市',19)
insert into city values(8,'张家界市',19)
insert into city values(9,'益阳市',19)
insert into city values(10,'郴州市',19)
insert into city values(11,'永州市',19)
insert into city values(12,'怀化市',19)
insert into city values(13,'娄底市',19)
insert into city values(14,'湘西土家族苗族自治州',19)
--20广东省（截至2005年12月31日，广东省辖：21个地级市，54个市辖区、23个县级市、41个县、3个自治县，429个街道办事处、1145个镇、4个乡、7个民族乡。）
insert into city values(1,'广州市',20)
insert into city values(2,'深圳市',20)
insert into city values(3,'珠海市',20)
insert into city values(4,'汕头市',20)
insert into city values(5,'韶关市',20)
insert into city values(6,'佛山市',20)
insert into city values(7,'江门市',20)
insert into city values(8,'湛江市',20)
insert into city values(9,'茂名市',20)
insert into city values(10,'肇庆市',20)
insert into city values(11,'惠州市',20)
insert into city values(12,'梅州市',20)
insert into city values(13,'汕尾市',20)
insert into city values(14,'河源市',20)
insert into city values(15,'阳江市',20)
insert into city values(16,'清远市',20)
insert into city values(17,'东莞市',20)
insert into city values(18,'中山市',20)
insert into city values(19,'潮州市',20)
insert into city values(20,'揭阳市',20)
insert into city values(21,'云浮市',20)
--21甘肃省（2006年辖：12个地级市、2个自治州；17个市辖区、4个县级市、58个县、7个自治县。）
insert into city values(1,'兰州市',21)
insert into city values(2,'金昌市',21)
insert into city values(3,'白银市',21)
insert into city values(4,'天水市',21)
insert into city values(5,'嘉峪关市',21)
insert into city values(6,'武威市',21)
insert into city values(7,'张掖市',21)
insert into city values(8,'平凉市',21)
insert into city values(9,'酒泉市',21)
insert into city values(10,'庆阳市',21)
insert into city values(11,'定西市',21)
insert into city values(12,'陇南市',21)
insert into city values(13,'临夏回族自治州',21)
insert into city values(14,'甘南藏族自治州',21)
--22四川省（2006年辖：18个地级市、3个自治州；43个市辖区、14个县级市、120个县、4个自治县。）
insert into city values(1,'成都市',22)
insert into city values(2,'自贡市',22)
insert into city values(3,'攀枝花市',22)
insert into city values(4,'泸州市',22)
insert into city values(5,'德阳市',22)
insert into city values(6,'绵阳市',22)
insert into city values(7,'广元市',22)
insert into city values(8,'遂宁市',22)
insert into city values(9,'内江市',22)
insert into city values(10,'乐山市',22)
insert into city values(11,'南充市',22)
insert into city values(12,'眉山市',22)
insert into city values(13,'宜宾市',22)
insert into city values(14,'广安市',22)
insert into city values(15,'达州市',22)
insert into city values(16,'雅安市',22)
insert into city values(17,'巴中市',22)
insert into city values(18,'资阳市',22)
insert into city values(19,'阿坝藏族羌族自治州',22)
insert into city values(20,'甘孜藏族自治州',22)
insert into city values(21,'凉山彝族自治州',22)
/**//****************************************
--23山东省(2005年，辖：17个地级市；49个市辖区、31个县级市、60个县。)
insert into city values(1,'济南市',16)
insert into city values(2,'青岛市',16)
insert into city values(3,'淄博市',16)
insert into city values(4,'枣庄市',16)
insert into city values(5,'东营市',16)
insert into city values(6,'烟台市',16)
insert into city values(7,'潍坊市',16)
insert into city values(8,'济宁市',16)
insert into city values(9,'泰安市',16)
insert into city values(10,'威海市',16)
insert into city values(11,'日照市',16)
insert into city values(12,'莱芜市',16)
insert into city values(13,'临沂市',16)
insert into city values(14,'德州市',16)
insert into city values(15,'聊城市',16)
insert into city values(16,'滨州市',16)
insert into city values(17,'菏泽市',16)
*************************************/
--24贵州省(2006年辖：4个地级市、2个地区、3个自治州；10个市辖区、9个县级市、56个县、11个自治县、2个特区。)
insert into city values(1,'贵阳市',24)
insert into city values(2,'六盘水市',24)
insert into city values(3,'遵义市',24)
insert into city values(4,'安顺市',24)
insert into city values(5,'铜仁地区',24)
insert into city values(6,'毕节地区',24)
insert into city values(7,'黔西南布依族苗族自治州',24)
insert into city values(8,'黔东南苗族侗族自治州',24)
insert into city values(9,'黔南布依族苗族自治州',24)
--25海南省(2003－2005年　全省有2个地级市，6个县级市，4个县，6个民族自治县，4个市辖区，1个办事处（西南中沙群岛办事处，县级）。)
insert into city values(1,'海口市',25)
insert into city values(2,'三亚市',25)
insert into city values(3,'五指山市',25)
insert into city values(4,'琼海市',25)
insert into city values(5,'儋州市',25)
insert into city values(6,'文昌市',25)
insert into city values(7,'万宁市',25)
insert into city values(8,'东方市',25)
insert into city values(9,'澄迈县',25)
insert into city values(10,'定安县',25)
insert into city values(11,'屯昌县',25)
insert into city values(12,'临高县',25)
insert into city values(13,'白沙黎族自治县',25)
insert into city values(14,'昌江黎族自治县',25)
insert into city values(15,'乐东黎族自治县',25)
insert into city values(16,'陵水黎族自治县',25)
insert into city values(17,'保亭黎族苗族自治县',25)
insert into city values(18,'琼中黎族苗族自治县',25)
--26云南省(2006年辖：8个地级市、8个自治州；12个市辖区、9个县级市、79个县、29个自治县。)
insert into city values(1,'昆明市',26)
insert into city values(2,'曲靖市',26)
insert into city values(3,'玉溪市',26)
insert into city values(4,'保山市',26)
insert into city values(5,'昭通市',26)
insert into city values(6,'丽江市',26)
insert into city values(7,'思茅市',26)
insert into city values(8,'临沧市',26)
insert into city values(9,'文山壮族苗族自治州',26)
insert into city values(10,'红河哈尼族彝族自治州',26)
insert into city values(11,'西双版纳傣族自治州',26)
insert into city values(12,'楚雄彝族自治州',26)
insert into city values(13,'大理白族自治州',26)
insert into city values(14,'德宏傣族景颇族自治州',26)
insert into city values(15,'怒江傈傈族自治州',26)
insert into city values(16,'迪庆藏族自治州',26)
--27青海省(2006年辖：1个地级市、1个地区、6个自治州；4个市辖区、2个县级市、30个县、7个自治县。)
insert into city values(1,'西宁市',27)
insert into city values(2,'海东地区',27)
insert into city values(3,'海北藏族自治州',27)
insert into city values(4,'黄南藏族自治州',27)
insert into city values(5,'海南藏族自治州',27)
insert into city values(6,'果洛藏族自治州',27)
insert into city values(7,'玉树藏族自治州',27)
insert into city values(8,'海西蒙古族藏族自治州',27)
--28陕西省(2006年辖：10个地级市；24个市辖区、3个县级市、80个县。)
insert into city values(1,'西安市',28)
insert into city values(2,'铜川市',28)
insert into city values(3,'宝鸡市',28)
insert into city values(4,'咸阳市',28)
insert into city values(5,'渭南市',28)
insert into city values(6,'延安市',28)
insert into city values(7,'汉中市',28)
insert into city values(8,'榆林市',28)
insert into city values(9,'安康市',28)
insert into city values(10,'商洛市',28)
--29广西壮族自治区(2005年辖：14个地级市；34个市辖区、7个县级市、56个县、12个自治县。)
insert into city values(1,'南宁市',29)
insert into city values(2,'柳州市',29)
insert into city values(3,'桂林市',29)
insert into city values(4,'梧州市',29)
insert into city values(5,'北海市',29)
insert into city values(6,'防城港市',29)
insert into city values(7,'钦州市',29)
insert into city values(8,'贵港市',29)
insert into city values(9,'玉林市',29)
insert into city values(10,'百色市',29)
insert into city values(11,'贺州市',29)
insert into city values(12,'河池市',29)
insert into city values(13,'来宾市',29)
insert into city values(14,'崇左市',29)
--30西藏自治区(2005年辖：1个地级市、6个地区；1个市辖区、1个县级市、71个县。)
insert into city values(1,'拉萨市',30)
insert into city values(2,'那曲地区',30)
insert into city values(3,'昌都地区',30)
insert into city values(4,'山南地区',30)
insert into city values(5,'日喀则地区',30)
insert into city values(6,'阿里地区',30)
insert into city values(7,'林芝地区',30)
--31宁夏回族自治区
insert into city values(1,'银川市',31)
insert into city values(2,'石嘴山市',31)
insert into city values(3,'吴忠市',31)
insert into city values(4,'固原市',31)
insert into city values(5,'中卫市',31)
--32新疆维吾尔自治区(2005年辖：2个地级市、7个地区、5个自治州；11个市辖区、20个县级市、62个县、6个自治县)
insert into city values(1,'乌鲁木齐市',32)
insert into city values(2,'克拉玛依市',32)
insert into city values(3,'石河子市　',32)
insert into city values(4,'阿拉尔市',32)
insert into city values(5,'图木舒克市',32)
insert into city values(6,'五家渠市',32)
insert into city values(7,'吐鲁番市',32)
insert into city values(8,'阿克苏市',32)
insert into city values(9,'喀什市',32)
insert into city values(10,'哈密市',32)
insert into city values(11,'和田市',32)
insert into city values(12,'阿图什市',32)
insert into city values(13,'库尔勒市',32)
insert into city values(14,'昌吉市　',32)
insert into city values(15,'阜康市',32)
insert into city values(16,'米泉市',32)
insert into city values(17,'博乐市',32)
insert into city values(18,'伊宁市',32)
insert into city values(19,'奎屯市',32)
insert into city values(20,'塔城市',32)
insert into city values(21,'乌苏市',32)
insert into city values(22,'阿勒泰市',32)
--33内蒙古自治区(2006年，辖：9个地级市、3个盟；21个市辖区、11个县级市、17个县、49个旗、3个自治旗。)
insert into city values(1,'呼和浩特市',33)
insert into city values(2,'包头市',33)
insert into city values(3,'乌海市',33)
insert into city values(4,'赤峰市',33)
insert into city values(5,'通辽市',33)
insert into city values(6,'鄂尔多斯市',33)
insert into city values(7,'呼伦贝尔市',33)
insert into city values(8,'巴彦淖尔市',33)
insert into city values(9,'乌兰察布市',33)
insert into city values(10,'锡林郭勒盟',33)
insert into city values(11,'兴安盟',33)
insert into city values(12,'阿拉善盟',33)
--34澳门特别行政区
insert into city values(1,'澳门特别行政区',34)
--35香港特别行政区
insert into city values(1,'香港特别行政区',35)

ya1la2

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
SQL语句大全(几乎所有常用的示例,包括省市号码源码)

csharp] view plaincopycreate table Student (id int not null , name nvarchar(50), age int null ) drop table student insert into Student(id,name,age)values(
复制链接

扫一扫