城市区号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 条评论
登录 后参与评论

相关文章

  • Quartz Cron表达式 在线生成器

    Cron Expressions——Cron 表达式  按顺序依次为 秒(0~59) 分钟(0~59) 小时(0~23) 天(月)(0~31,...

    用户1168362
  • 使用作业自动清理数据库日志文件

           在上一篇文章中介绍了如何删除数据库日志文件,但是想想还是不是不方便需要手工操作,于是想结合作业实现自动清理日志文件,在清理日志文件时我加上了条件,...

    用户1168362
  • 删除数据库日志文件的方法

           你曾经有在执行SQL的时候,数据库报事务日志已满,然后执行报错。然后纠结于怎么删除数据库日志,捣鼓半天吗,现在就提供两种删除日志文件的方法,希望能...

    用户1168362
  • SQL---UNION 和 UNION ALL 操作符

    请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。

    IT云清
  • 机器学习中的Bias(偏差),Error(误差),和Variance(方差)有什么区别和联系?

    首先 Error = Bias + Variance + Noise Error反映的是整个模型的准确度,Bias反映的是模型在样本上的输出与真实值之间的误差...

    MachineLP
  • 关于ics lab8 performance中的smooth

    其实smooth比rotate水很多。 首先看一下avg的定义,发现是二层循环,最大遍历3*3最小遍历2*2的元素,用...

    ApacheCN_飞龙
  • PHP算法——四大基础算法

    对于大多数业务开发来说,平时很少需要自己实现数据结构与算法,都是利用已经封装好的现成接口,类库来推测、翻译业务逻辑,但是,不需要自己实现,并不代表什么都不需要了...

    猿哥
  • UML之包图

       当对一个比较复杂的软件系统进行建模时,会有大量的类、接口、组件、节点和图需要处理;如果放在同一个地方的话,信息量非常的大,显得很乱,不方便查询,所以就对这...

    令仔很忙
  • CSS伪类与伪元素

    CSS引入伪类和伪元素概念是为了格式化文档树以外的信息。也就是说,伪类和伪元素是用来修饰不在文档树中的部分,比如,一句话中的第一个字母,或者是列表中的第一个元素...

    WindrunnerMax

扫码关注云+社区

领取腾讯云代金券