城市区号SQL

今天写代码的时候需要用到全国城市区号,网上找了好久没有现成的SQL,于是自己录数据写了一个,和大家共享!

目前还只有300个城市的区号

文件下载地址放在最后!

GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE id=OBJECT_ID('y_City') AND xtype='u')
BEGIN
    DROP TABLE dbo.y_City
END
GO

CREATE TABLE dbo.y_City(
        CityID INT PRIMARY KEY IDENTITY(1,1),
        Company VARCHAR(100),
        CityName VARCHAR(100),
        CityCode VARCHAR(100)
    )
GO
DELETE FROM dbo.y_City

INSERT INTO dbo.y_City(Company,CityName,CityCode)
SELECT '1','北京','010' UNION ALL 
SELECT '2','鄂州','0711' UNION ALL 
SELECT '2','恩施','0718' UNION ALL 
SELECT '2','黄冈','0713' UNION ALL 
SELECT '2','黄石','0714' UNION ALL 
SELECT '2','荆门','0724' UNION ALL 
SELECT '2','荆州','0716' UNION ALL 
SELECT '2','十堰','0719' UNION ALL 
SELECT '2','随州','0722' UNION ALL 
SELECT '2','武汉','027' UNION ALL 
SELECT '2','咸宁','0715' UNION ALL 
SELECT '2','襄樊','0710' UNION ALL 
SELECT '2','孝感','0712' UNION ALL 
SELECT '2','宜昌','0717' UNION ALL 
SELECT '3','潮州','0768' UNION ALL 
SELECT '3','东莞','0769' UNION ALL 
SELECT '3','佛山','0757' UNION ALL 
SELECT '3','广州','020' UNION ALL 
SELECT '3','河源','0762' UNION ALL 
SELECT '3','惠州','0752' UNION ALL 
SELECT '3','江门','0750' UNION ALL 
SELECT '3','揭阳','0663' UNION ALL 
SELECT '3','茂名','0668' UNION ALL 
SELECT '3','梅州','0753' UNION ALL 
SELECT '3','清远','0763' UNION ALL 
SELECT '3','汕头','0754' UNION ALL 
SELECT '3','汕尾','0660' UNION ALL 
SELECT '3','韶关','0751' UNION ALL 
SELECT '3','阳江','0662' UNION ALL 
SELECT '3','云浮','0766' UNION ALL 
SELECT '3','湛江','0759' UNION ALL 
SELECT '3','肇庆','0758' UNION ALL 
SELECT '3','中山','0760' UNION ALL 
SELECT '3','珠海','0756' UNION ALL 
SELECT '4','上海','021' UNION ALL 
SELECT '5','巴中 ','0827' UNION ALL 
SELECT '5','成都','028' UNION ALL 
SELECT '5','成都郊县','' UNION ALL 
SELECT '5','达州','0818' UNION ALL 
SELECT '5','德阳','0838' UNION ALL 
SELECT '5','甘孜','0836' UNION ALL 
SELECT '5','广安','0826' UNION ALL 
SELECT '5','广元','0839' UNION ALL 
SELECT '5','乐山','0833' UNION ALL 
SELECT '5','凉山','0834' UNION ALL 
SELECT '5','泸州','0830' UNION ALL 
SELECT '5','眉山','0833' UNION ALL 
SELECT '5','绵阳','0816' UNION ALL 
SELECT '5','内江','0832' UNION ALL 
SELECT '5','南充','0817' UNION ALL 
SELECT '5','攀枝花','0812' UNION ALL 
SELECT '5','遂宁','0825' UNION ALL 
SELECT '5','雅安','0835' UNION ALL 
SELECT '5','宜宾','0831' UNION ALL 
SELECT '5','资阳','0832' UNION ALL 
SELECT '5','自贡','0813' UNION ALL 
SELECT '6','鞍山','0412' UNION ALL 
SELECT '6','本溪','0414' UNION ALL 
SELECT '6','朝阳','0421' UNION ALL 
SELECT '6','丹东','0415' UNION ALL 
SELECT '6','抚顺','0413' UNION ALL 
SELECT '6','阜新','0418' UNION ALL 
SELECT '6','葫芦岛','0429' UNION ALL 
SELECT '6','锦州','0416' UNION ALL 
SELECT '6','辽阳','0419' UNION ALL 
SELECT '6','盘锦','0427' UNION ALL 
SELECT '6','沈阳','024' UNION ALL 
SELECT '6','铁岭','0410' UNION ALL 
SELECT '6','营口','0417' UNION ALL 
SELECT '7','安康','0915' UNION ALL 
SELECT '7','宝鸡','0917' UNION ALL 
SELECT '7','汉中','0916' UNION ALL 
SELECT '7','商洛','0914' UNION ALL 
SELECT '7','铜川','0929' UNION ALL 
SELECT '7','渭南','0913' UNION ALL 
SELECT '7','西安','029' UNION ALL 
SELECT '7','咸阳','0910' UNION ALL 
SELECT '7','延安','0911' UNION ALL 
SELECT '7','榆林','0912' UNION ALL 
SELECT '8','海南','0898' UNION ALL 
SELECT 'A','常州','0519' UNION ALL 
SELECT 'A','淮安','0517' UNION ALL 
SELECT 'A','连云港','0518' UNION ALL 
SELECT 'A','南京','025' UNION ALL 
SELECT 'A','南通','0513' UNION ALL 
SELECT 'A','苏州','0512' UNION ALL 
SELECT 'A','宿迁','0527' UNION ALL 
SELECT 'A','泰州','0523' UNION ALL 
SELECT 'A','无锡','0510' UNION ALL 
SELECT 'A','徐州','0516' UNION ALL 
SELECT 'A','盐城','0515' UNION ALL 
SELECT 'A','扬州','0514' UNION ALL 
SELECT 'A','镇江','0511' UNION ALL 
SELECT 'B','杭州','0571' UNION ALL 
SELECT 'B','湖州','0572' UNION ALL 
SELECT 'B','嘉兴','0573' UNION ALL 
SELECT 'B','金华','0579' UNION ALL 
SELECT 'B','丽水','0578' UNION ALL 
SELECT 'B','衢州','0570' UNION ALL 
SELECT 'B','绍兴','0575' UNION ALL 
SELECT 'B','台州','0576' UNION ALL 
SELECT 'B','温州','0577' UNION ALL 
SELECT 'B','舟山','0580' UNION ALL 
SELECT 'C','滨州','0543' UNION ALL 
SELECT 'C','德州','0534' UNION ALL 
SELECT 'C','东营','0546' UNION ALL 
SELECT 'C','菏泽','0530' UNION ALL 
SELECT 'C','济南','0531' UNION ALL 
SELECT 'C','济宁','0537' UNION ALL 
SELECT 'C','莱芜','0634' UNION ALL 
SELECT 'C','聊城','0635' UNION ALL 
SELECT 'C','临沂(yi)','0539' UNION ALL 
SELECT 'C','日照','0633' UNION ALL 
SELECT 'C','泰安','0538' UNION ALL 
SELECT 'C','威海','0631' UNION ALL 
SELECT 'C','潍坊','0536' UNION ALL 
SELECT 'C','烟台','0535' UNION ALL 
SELECT 'C','枣庄','0632' UNION ALL 
SELECT 'C','淄博','0533' UNION ALL 
SELECT 'D','安阳','0372' UNION ALL 
SELECT 'D','鹤壁','0392' UNION ALL 
SELECT 'D','济源','0391' UNION ALL 
SELECT 'D','焦作','0391' UNION ALL 
SELECT 'D','开封','0378' UNION ALL 
SELECT 'D','洛阳','0379' UNION ALL 
SELECT 'D','漯河','0395' UNION ALL 
SELECT 'D','南阳','0377' UNION ALL 
SELECT 'D','平顶山','0375' UNION ALL 
SELECT 'D','濮阳','0393' UNION ALL 
SELECT 'D','三门峡','0398' UNION ALL 
SELECT 'D','商丘','0370' UNION ALL 
SELECT 'D','新乡','0373' UNION ALL 
SELECT 'D','信阳','0376' UNION ALL 
SELECT 'D','许昌','0374' UNION ALL 
SELECT 'D','郑州','0371' UNION ALL 
SELECT 'D','周口','0394' UNION ALL 
SELECT 'D','驻马店','0396' UNION ALL 
SELECT 'E','天津','022' UNION ALL 
SELECT 'F','重庆','023' UNION ALL 
SELECT 'G','福州','0591' UNION ALL 
SELECT 'G','龙岩','0597' UNION ALL 
SELECT 'G','南平','0599' UNION ALL 
SELECT 'G','宁德','0593' UNION ALL 
SELECT 'G','莆田','0594' UNION ALL 
SELECT 'G','泉州','0595' UNION ALL 
SELECT 'G','三明','0598' UNION ALL 
SELECT 'G','厦门','0592' UNION ALL 
SELECT 'G','漳州','0596' UNION ALL 
SELECT 'H','长沙','0731' UNION ALL 
SELECT 'H','常德','0736' UNION ALL 
SELECT 'H','郴州','0735' UNION ALL 
SELECT 'H','衡阳','0734' UNION ALL 
SELECT 'H','怀化','0745' UNION ALL 
SELECT 'H','吉首','0743' UNION ALL 
SELECT 'H','娄底','0738' UNION ALL 
SELECT 'H','邵阳','0739' UNION ALL 
SELECT 'H','湘潭','0732' UNION ALL 
SELECT 'H','益阳','0737' UNION ALL 
SELECT 'H','永州','0746' UNION ALL 
SELECT 'H','岳阳','0730' UNION ALL 
SELECT 'H','张家界','0744' UNION ALL 
SELECT 'H','株洲','0733' UNION ALL 
SELECT 'I','深圳','0755' UNION ALL 
SELECT 'J','安庆','0556' UNION ALL 
SELECT 'J','蚌埠','0552' UNION ALL 
SELECT 'J','巢湖','0565' UNION ALL 
SELECT 'J','池州','0566' UNION ALL 
SELECT 'J','滁州','0550' UNION ALL 
SELECT 'J','阜(fu)阳','0558' UNION ALL 
SELECT 'J','毫州','0558' UNION ALL 
SELECT 'J','合肥','0551' UNION ALL 
SELECT 'J','淮北','0561' UNION ALL 
SELECT 'J','淮南','0554' UNION ALL 
SELECT 'J','黄山','0559' UNION ALL 
SELECT 'J','六(lu)安','0564' UNION ALL 
SELECT 'J','马鞍山','0555' UNION ALL 
SELECT 'J','宿州','0557' UNION ALL 
SELECT 'J','铜陵','0562' UNION ALL 
SELECT 'J','芜湖','0553' UNION ALL 
SELECT 'J','宣城','0563' UNION ALL 
SELECT 'K','大连','0411' UNION ALL 
SELECT 'L','青岛','0532' UNION ALL 
SELECT 'M','宁波','0574' UNION ALL 
SELECT 'N','保定','0312' UNION ALL 
SELECT 'N','沧州','0317' UNION ALL 
SELECT 'N','承德','0314' UNION ALL 
SELECT 'N','邯郸','0310' UNION ALL 
SELECT 'N','衡水','0318' UNION ALL 
SELECT 'N','廊坊','0316' UNION ALL 
SELECT 'N','秦皇岛','0335' UNION ALL 
SELECT 'N','石家庄','0311' UNION ALL 
SELECT 'N','唐山','0315' UNION ALL 
SELECT 'N','邢台','0319' UNION ALL 
SELECT 'N','张家口','0313' UNION ALL 
SELECT 'O','大庆','0459' UNION ALL 
SELECT 'O','大兴安岭','0457' UNION ALL 
SELECT 'O','哈尔滨','0451' UNION ALL 
SELECT 'O','鹤岗','0468' UNION ALL 
SELECT 'O','黑河','0456' UNION ALL 
SELECT 'O','鸡西','0467' UNION ALL 
SELECT 'O','佳木斯','0454' UNION ALL 
SELECT 'O','牡丹江','0453' UNION ALL 
SELECT 'O','齐齐哈尔','0452' UNION ALL 
SELECT 'O','绥化','0455' UNION ALL 
SELECT 'O','伊春','0458' UNION ALL 
SELECT 'P','保山','0875' UNION ALL 
SELECT 'P','楚雄','0878' UNION ALL 
SELECT 'P','大理','0872' UNION ALL 
SELECT 'P','德宏','0692' UNION ALL 
SELECT 'P','红河','0873' UNION ALL 
SELECT 'P','景洪','0691' UNION ALL 
SELECT 'P','昆明','0871' UNION ALL 
SELECT 'P','丽江','0888' UNION ALL 
SELECT 'P','临沧','0883' UNION ALL 
SELECT 'P','曲靖','0874' UNION ALL 
SELECT 'P','思茅','0879' UNION ALL 
SELECT 'P','文山','0876' UNION ALL 
SELECT 'P','玉溪','0877' UNION ALL 
SELECT 'P','昭通','0870' UNION ALL 
SELECT 'Q','白城','0436' UNION ALL 
SELECT 'Q','长春','0431' UNION ALL 
SELECT 'Q','吉林','0432' UNION ALL 
SELECT 'Q','辽源','0437' UNION ALL 
SELECT 'Q','四平','0434' UNION ALL 
SELECT 'Q','松原','0438' UNION ALL 
SELECT 'Q','通化','0435' UNION ALL 
SELECT 'Q','延吉','0433' UNION ALL 
SELECT 'R','长治','0355' UNION ALL 
SELECT 'R','大同','0352' UNION ALL 
SELECT 'R','晋城','0356' UNION ALL 
SELECT 'R','晋中','0354' UNION ALL 
SELECT 'R','临汾','0357' UNION ALL 
SELECT 'R','吕梁','0359' UNION ALL 
SELECT 'R','朔州','0349' UNION ALL 
SELECT 'R','太原','0351' UNION ALL 
SELECT 'R','忻州','0350' UNION ALL 
SELECT 'R','阳泉','0353' UNION ALL 
SELECT 'R','运城','0359' UNION ALL 
SELECT 'S','阿克苏','0997' UNION ALL 
SELECT 'S','博州','0909' UNION ALL 
SELECT 'S','昌吉','0994' UNION ALL 
SELECT 'S','哈密','0902' UNION ALL 
SELECT 'S','喀什(kashi)','0998' UNION ALL 
SELECT 'S','克拉玛依','0990' UNION ALL 
SELECT 'S','库尔勒','0996' UNION ALL 
SELECT 'S','奎屯','0992' UNION ALL 
SELECT 'S','石河子','0993' UNION ALL 
SELECT 'S','塔城','0901' UNION ALL 
SELECT 'S','吐鲁番','0995' UNION ALL 
SELECT 'S','乌鲁木齐','0991' UNION ALL 
SELECT 'S','伊犁','0999' UNION ALL 
SELECT 'T','抚州','0794' UNION ALL 
SELECT 'T','赣gan州','0797' UNION ALL 
SELECT 'T','吉安','0796' UNION ALL 
SELECT 'T','景德镇','0798' UNION ALL 
SELECT 'T','九江','0792' UNION ALL 
SELECT 'T','南昌','0791' UNION ALL 
SELECT 'T','萍乡','0799' UNION ALL 
SELECT 'T','上饶','0793' UNION ALL 
SELECT 'T','新余','0790' UNION ALL 
SELECT 'T','宜春','0795' UNION ALL 
SELECT 'T','鹰潭','0701' UNION ALL 
SELECT 'U','百色','0776' UNION ALL 
SELECT 'U','北海','0779' UNION ALL 
SELECT 'U','桂林','0773' UNION ALL 
SELECT 'U','河池','0778' UNION ALL 
SELECT 'U','贺州','0774' UNION ALL 
SELECT 'U','来宾','0772' UNION ALL 
SELECT 'U','柳州','0772' UNION ALL 
SELECT 'U','南宁','0771' UNION ALL 
SELECT 'U','钦州','0777' UNION ALL 
SELECT 'U','梧州','0774' UNION ALL 
SELECT 'U','玉林','0775' UNION ALL 
SELECT 'V','巴彦淖尔','0478' UNION ALL 
SELECT 'V','包头','0472' UNION ALL 
SELECT 'V','赤峰','0476' UNION ALL 
SELECT 'V','鄂尔多斯','0477' UNION ALL 
SELECT 'V','呼和浩特','0471' UNION ALL 
SELECT 'V','呼伦贝尔','0470' UNION ALL 
SELECT 'V','通辽','0475' UNION ALL 
SELECT 'V','乌海','0473' UNION ALL 
SELECT 'V','乌兰察布','0474' UNION ALL 
SELECT 'V','锡林郭勒','0479' UNION ALL 
SELECT 'W','白银','0943' UNION ALL 
SELECT 'W','酒泉','0937' UNION ALL 
SELECT 'W','兰州','0931' UNION ALL 
SELECT 'W','天水','0938' UNION ALL 
SELECT 'W','武威','0935' UNION ALL 
SELECT 'W','张掖','0936' UNION ALL 
SELECT 'X','宁夏','0951' UNION ALL 
SELECT 'Y','安顺','0853' UNION ALL 
SELECT 'Y','毕节','0857' UNION ALL 
SELECT 'Y','贵阳','0851' UNION ALL 
SELECT 'Y','六盘水','0858' UNION ALL 
SELECT 'Y','黔东南','0855' UNION ALL 
SELECT 'Y','黔南','0854' UNION ALL 
SELECT 'Y','黔西南(兴义)','0859' UNION ALL 
SELECT 'Y','铜仁','0856' UNION ALL 
SELECT 'Y','遵义','0852' UNION ALL 
SELECT 'Z','青海','0971' 

SELECT * FROM dbo.y_City

城市区号

下载地址:城市区号

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Netkiller

Oracle show 语句

33.4. parameter 33.4.1. db SQL> show parameter db; NAME ...

3114
来自专栏乐沙弥的世界

oracle imp导入时出现skipping table

    最近有同事在使用传统的imp工具导入数据时,总是提示收到skipping table的提示,也就是表被跳过,而不是被重建。即使是将目标数据库上的表对象删...

821
来自专栏大内老A

Audit Logging-Stored Procedure

1. T_ORDER For Insert: sp_order_i IF EXISTS (SELECT * FROM sysobjects WHERE type...

1907
来自专栏乐沙弥的世界

SQL基础--> 数据处理(DML、RETURNING、MERGE INTO)

--=================================================

712
来自专栏YoungGy

HIVE入门_1

RUNNING HIVE $ $HADOOP_HOME/bin/hadoop fs -mkdir /tmp $ $HADOOP_HOME/bin/h...

1948
来自专栏Spring相关

mysql 查询当天、本周,本月,上一个月的数据

DATE_ADD(createTime,INTERVAL 1 DAY) 得到指定日期后一天的日期/把1改为任意数字就可以得到后N天的日期

1023
来自专栏Netkiller

PostgreSQL·国家地区表的设计

本文节选自《Netkiller Architect 手札》 4.22. PostgreSQL 所特有数据库设计 4.22.1. 国家地区表的设计 +-----...

2894
来自专栏杨建荣的学习笔记

关于long类型的转换(r3笔记第84天)

在oracle中对于long类型的处理时很纠结的。最开始引入这个数据类型的时候是对原有数据类型的补充,但是后面发现还是碰到了一些问题,使用Lob类型代替了。但是...

28410
来自专栏一个会写诗的程序员的博客

查看域名服务器命令: $ dig -t NS xvideos.com

; <<>> DiG 9.8.3-P1 <<>> -t NS xvideos.com ;; global options: +cmd ;; Got answ...

1.9K2
来自专栏沃趣科技

MySQL分析函数实现

--SQL 执行顺序 ,FROM ,JOIN ,WHERE ,GROUP BY,HAVING ,ORDER BY ,SELECT,

1053

扫码关注云+社区