省市数据库？求一完整的全国省市级联的oracle数据库建表脚本

大家好，今天来为大家解答省市数据库这个问题的一些问题点，包括求一完整的全国省市级联的oracle数据库建表脚本也一样很多人还不知道，因此呢，今天就来为大家分析分析，现在让我们一起来看看吧！如果解决了您的问题，还望您关注下本站哦，谢谢~

怎么把中国的省市区数据导入sqlserver数据库

把本地sql server数据库导入到服务器的方法步骤（SQL 2000为例）：

首先打开本地的SQL 2000的企业管理器,然后根据数据库开通信中的地址、用户名密码,建立远程SQL注册,连上远程服务器上的数据库:

然后登录到远程数据库服务器:

1、右击准备导入数据的数据库,选择“所有任务”下的“导入数据”

2、进入DTS导入/导出向导,点击“下一步”按钮继续

3、选择数据源,输入数据源所在的数据库服务器IP、用户名、密码和要复制数据的源数据库,点击“下一步”按钮

4、选择目的,输入目的数据库所在的数据库服务器名称、用户名、密码和要复制数据的目的数据库(这个信息在用户区,数据库管理中可以看到),点击“下一步”按钮

5、选择“在SQL Server数据库之间复制对象和数据”方式,点“下一步”继续

6、注意取消安全措施选项

7、设定调度方式,一般选“立即运行”就可以,然后点“下一步”继续

8、点“完成”执行

省市区 地址联动 数据怎么创建

1、在做这个小项目前的准备工作：

1.1、分析：

由于省、市、县城（区）这些数据是存储到了MySQL数据库中的，我们要通过后台servlet获取数据库中的数据，然后再通过转发或者重定向的方式将数据呈现到前台页面中。

1.2、需要导入的jar包有:

mysql驱动包：mysql-connector-Java-5.1.7-bin.jar

c3p0数据库连接池：c3p0-0.9.2.1.jar、mysql-connector-java-5.1.7-bin.jar（c3p0依赖包）

前台c标签（需要通过遍历的方式呈现——c:forEach）：jstl-1.0.2.jar、standard-1.0.1.jar（jstl依赖包）

将集合或者数组转换成json数据格式（Jackson包）：jackson-annotations-2.2.1.jar、jackson-core-2.2.1.jar、jackson-databind-2.2.1.jar

前台页面需要用到jQuery，故还需要导入jquery-1.7.2.js库文件

1.3、该小项目用到的技术：

jdbc的知识、servlet的知识、jquery的知识、Ajax的知识（局部刷新）、标签的知识、EL表达式的知识、JSP的知识

2、开发过程：

2.1、准备数据源

创建一个数据库，命名为thereaction并创建三个表——province（省）、city（市）、county（县/区）

2.2后台开发

创建三个Javabean分别是Province、City、County。（由于太过简单，这里就不粘代码了）

创建Java类和c3p0连接池实现与数据库的连接：DAO.java(实现获取数据库数据的功能)、jdbctools.java（实现获取数据库连接、释放连接的功能）、c3p0-config.xml

jdbctools.java代码如下：

[java] view plain copy

package com.xiaojie.dao;

import java.io.IOException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class Jdbctools{

private static DataSource ds=null;

//数据库连接池应只被初始化一次

static{

ds=new ComboPooledDataSource("helloc3p0");

}

//获取数据库连接

public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException{

return ds.getConnection();

}

public static void shifanglianjie(Connection ct, PreparedStatement ps,ResultSet rs){

if(rs!=null){

try{

rs.close();

} catch(SQLException e){

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if(ps!=null){

try{

ps.close();

} catch(SQLException e){

// TODO Auto-generated catch block

e.printStackTrace();

}

}

if(ct!=null){

try{

ct.close();

} catch(SQLException e){

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

}

DAO.java的代码如下

[java] view plain copy

package com.xiaojie.dao;

import java.io.IOException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.xiaojie.beans.City;

import com.xiaojie.beans.County;

import com.xiaojie.beans.Province;

public class DAO{

public List<Province> getprovince(String sql,Object...args) throws ClassNotFoundException, SQLException, IOException{

List<Province> provinces=new ArrayList<Province>();

//准备去查数据库

Connection ct=null;

ct=Jdbctools.getConnection();

System.out.println("获取到数据库的连接了");

PreparedStatement ps=null;

ResultSet rs=null;

ps=ct.prepareStatement(sql);

for(int i=0;i<args.length;i++){

ps.setObject(i+1, args[i]);

}

rs=ps.executeQuery();

while(rs.next()){

provinces.add(new Province(rs.getInt("province_id"),rs.getString("province_name")));

}

Jdbctools.shifanglianjie(ct, ps, rs);

return provinces;

}

public List<City> getcity(String sql,Object...args) throws SQLException, ClassNotFoundException, IOException{

List<City> cities=new ArrayList<City>();

//准备去查数据库

Jdbctools jt=new Jdbctools();

Connection ct=null;

ct=jt.getConnection();

PreparedStatement ps=null;

ResultSet rs=null;

ps=ct.prepareStatement(sql);

for(int i=0;i<args.length;i++){

ps.setObject(i+1, args[i]);

}

rs=ps.executeQuery();

while(rs.next()){

cities.add(new City(rs.getInt("city_id"),rs.getString("city_name")));

}

jt.shifanglianjie(ct, ps, rs);

return cities;

}

public List<County> getcounty(String sql,Object...args) throws SQLException, ClassNotFoundException, IOException{

List<County> counties=new ArrayList<County>();

//准备去查数据库

Jdbctools jt=new Jdbctools();

Connection ct=null;

ct=jt.getConnection();

PreparedStatement ps=null;

ResultSet rs=null;

ps=ct.prepareStatement(sql);

for(int i=0;i<args.length;i++){

ps.setObject(i+1, args[i]);

}

rs=ps.executeQuery();

while(rs.next()){

counties.add(new County(rs.getInt("county_id"),rs.getString("county_name")));

}

jt.shifanglianjie(ct, ps, rs);

return counties;

}

}

c3p0-config.xml的代码如下：

[html] view plain copy

<?xml version="1.0" encoding="UTF-8"?>

<c3p0-config>

<named-config name="helloc3p0">

<!--连接数据源的基本属性-->

<property name="user">root</property>

<property name="password"></property>

<property name="driverClass">com.mysql.jdbc.Driver</property>

<property name="jdbcUrl">jdbc:mysql:///thereaction</property>

<!--若数据库中连接数不足时，一次向数据库服务器申请多少个连接-->

<property name="acquireIncrement">5</property>

<!--初始化数据库连接池时连接的数量-->

<property name="initialPoolSize">5</property>

<!--数据库连接池中的最小的数据库连接数-->

<property name="minPoolSize">5</property>

<!--数据库连接池中的最大的数据库连接数-->

<property name="maxPoolSize">10</property>

<!-- c3p0数据库连接可以维护的statement的个数-->

<property name="maxStatements">20</property>

<!--每个连接同时可以使用的statement对象的个数-->

<property name="maxStatementsPerConnection">5</property>

</named-config>

</c3p0-config>

创建servlet.java文件

[java] view plain copy

package com.xiaojie.servlet;

import java.io.IOException;

import java.lang.reflect.Method;

import java.sql.SQLException;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.fasterxml.jackson.databind.ObjectMapper;

import com.xiaojie.beans.City;

import com.xiaojie.beans.County;

import com.xiaojie.beans.Province;

import com.xiaojie.dao.DAO;

/**

* Servlet implementation class ThreeactiondServlet

*/

@WebServlet("/threeactiondServlet")

public class ThreeactiondServlet extends HttpServlet{

private static final long serialVersionUID= 1L;

/**

*@see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{

String methodname=request.getParameter("method");

try{

Method method=getClass().getDeclaredMethod(methodname,HttpServletRequest.class,HttpServletResponse.class);

method.invoke(this, request,response);//调用各自的方法

} catch(Exception e){

e.printStackTrace();

}

}

private DAO dao=new DAO();

protected void province(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException{

System.out.println("province的servlet进入了");

String sql="select province_id,province_name from province";

List<Province> provinces=dao.getprovince(sql);

request.setAttribute("provinces", provinces);

System.out.println(provinces);

//注意：这里不能用重定向的形式，因为我们好不容易在request请求域中存储了省的信息，目的是在前台页面中能够从请求域中获取到我们存在数据库中的值

//故这里只能用转发的方式

request.getRequestDispatcher("/index2.jsp").forward(request, response);

}

protected void city(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException{

System.out.println("city的servlet进入了");

String province_id=request.getParameter("province_id");

String sql="select city_id,city_name from city where province_id=?";

List<City> cities=dao.getcity(sql,Integer.parseInt(province_id));

ObjectMapper mapper=new ObjectMapper();

String result=mapper.writeValueAsString(cities);

System.out.println(result);

response.setContentType("text/javascript");

response.getWriter().print(result);

}

protected void county(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ClassNotFoundException, SQLException{

System.out.println("county的servlet进入了");

String city_id=request.getParameter("city_id");

String sql="select county_id,county_name from county where city_id=?";

List<County> counties=dao.getcounty(sql,Integer.parseInt(city_id));

ObjectMapper mapper=new ObjectMapper();

String result=mapper.writeValueAsString(counties);

System.out.println(result);

response.setContentType("text/javascript");

response.getWriter().print(result);

}

}

求一完整的全国省市级联的oracle数据库建表脚本

--省级 provincial

create table provincial

(

provincialID int,

provincialName varchar(50),

primary key(provincialID)

)engine=INNODB default charset=gbk;

insert into provincial values(1,'北京市');

insert into provincial values(2,'天津市');

insert into provincial values(3,'上海市');

insert into provincial values(4,'重庆市');

insert into provincial values(5,'河北省');

insert into provincial values(6,'山西省');

insert into provincial values(7,'台湾省');

insert into provincial values(8,'辽宁省');

insert into provincial values(9,'吉林省');

insert into provincial values(10,'黑龙江省');

insert into provincial values(11,'江苏省');

insert into provincial values(12,'浙江省');

insert into provincial values(13,'安徽省');

insert into provincial values(14,'福建省');

insert into provincial values(15,'江西省');

insert into provincial values(16,'山东省');

insert into provincial values(17,'河南省');

insert into provincial values(18,'湖北省');

insert into provincial values(19,'湖南省');

insert into provincial values(20,'广东省');

insert into provincial values(21,'甘肃省');

insert into provincial values(22,'四川省');

insert into provincial values(23,'贵州省');

insert into provincial values(24,'海南省');

insert into provincial values(25,'云南省');

insert into provincial values(26,'青海省');

insert into provincial values(27,'陕西省');

insert into provincial values(28,'广西壮族自治区');

insert into provincial values(29,'西藏自治区');

insert into provincial values(30,'宁夏回族自治区');

insert into provincial values(31,'新疆维吾尔自治区');

insert into provincial values(32,'内蒙古自治区');

insert into provincial values(33,'澳门特别行政区');

insert into provincial values(34,'香港特别行政区');

select provincialID,provincialName from provincial;

--城市 city

create table city

(

cityID int not null,

cityName varchar(50) not null,

provincialID int not null,

primary key(cityID)

)engine=INNODB default charset=gbk; alter table city add constraint FK_pro_city_provincialID foreign key(provincialID)

references provincial(provincialID);

----------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------

--插入各个省的城市数据

--4个直辖市

insert into city values(1,'北京市',1);

insert into city values(2,'天津市',2);

insert into city values(3,'上海市',3);

insert into city values(4,'重庆市',4);

--select* from city where provincialID=4

----------------------------------------------------------------

--5河北省 11个地级市

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);

insert into city values(12,'承德市',5);

insert into city values(13,'沧州市',5);

insert into city values(14,'廊坊市',5);

insert into city values(15,'衡水市',5);

--select* from city where provincialID=5 order by cityID

----------------------------------------------------------------

--6山西省 11个城市

insert into city values(16,'太原市',6);

insert into city values(17,'大同市',6);

insert into city values(18,'阳泉市',6);

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 city values(23,'运城市',6);

insert into city values(24,'忻州市',6);

insert into city values(25,'临汾市',6);

insert into city values(26,'吕梁市',6);

--select* from city where provincialID=6 order by cityID

----------------------------------------------------------------

--7台湾省(台湾本岛和澎湖共设7市、16县，其中台北市和高雄市为“院辖市”，直属“行政院”，其余属台湾省；市下设区，县下设市（县辖市）、镇、乡，合称区市镇乡。);

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(31,'台南市',7);

insert into city values(32,'新竹市',7);

insert into city values(33,'嘉义市',7);

insert into city values(34,'台北县',7);

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(39,'台中县',7);

insert into city values(40,'彰化县',7);

insert into city values(41,'南投县',7);

insert into city values(42,'云林县',7);

insert into city values(43,'嘉义县',7);

insert into city values(44,'台南县',7);

insert into city values(45,'高雄县',7);

insert into city values(46,'屏东县',7);

insert into city values(47,'澎湖县',7);

insert into city values(48,'台东县',7);

insert into city values(49,'花莲县',7);

--select* from city where provincialID=7 order by cityID

----------------------------------------------------------------

--8辽宁省 14个地级市

insert into city values(50,'沈阳市',8);

insert into city values(51,'大连市',8);

insert into city values(52,'鞍山市',8);

insert into city values(53,'抚顺市',8);

insert into city values(54,'本溪市',8);

insert into city values(55,'丹东市',8);

insert into city values(56,'锦州市',8);

insert into city values(57,'营口市',8);

insert into city values(58,'阜新市',8);

insert into city values(59,'辽阳市',8);

insert into city values(60,'盘锦市',8);

insert into city values(61,'铁岭市',8);

insert into city values(62,'朝阳市',8);

insert into city values(63,'葫芦岛市',8);

--select* from city where provincialID=8 order by cityID

----------------------------------------------------------------

--9吉林省(2006年，辖：8个地级市、1个自治州；20个市辖区、20个县级市、17个县、3个自治县。);

insert into city values(64,'长春市',9);

insert into city values(65,'吉林市',9);

insert into city values(66,'四平市',9);

insert into city values(67,'辽源市',9);

insert into city values(68,'通化市',9);

insert into city values(69,'白山市',9);

insert into city values(70,'松原市',9);

insert into city values(71,'白城市',9);

insert into city values(72,'延边朝鲜族自治州',9);

--select* from city where provincialID=9 order by cityID

----------------------------------------------------------------

--10黑龙江省(2006年，辖：12地级市、1地区；64市辖区、18县级市、45县、1自治县);

insert into city values(73,'哈尔滨市',10);

insert into city values(74,'齐齐哈尔市',10);

insert into city values(75,'鹤岗市',10);

insert into city values(76,'双鸭山市',10);

insert into city values(77,'鸡西市',10);

insert into city values(78,'大庆市',10);

insert into city values(79,'伊春市',10);

insert into city values(80,'牡丹江市',10);

insert into city values(81,'佳木斯市',10);

insert into city values(82,'七台河市',10);

insert into city values(83,'黑河市',10);

insert into city values(84,'绥化市',10);

insert into city values(85,'大兴安岭地区',10);

--select* from city where provincialID=10 order by cityID

----------------------------------------------------------------

--11江苏省(2005年辖：13个地级市；54个市辖区、27个县级市、25个县);

insert into city values(86,'南京市',11);

insert into city values(87,'无锡市',11);

insert into city values(88,'徐州市',11);

insert into city values(89,'常州市',11);

insert into city values(90,'苏州市',11);

insert into city values(91,'南通市',11);

insert into city values(92,'连云港市',11);

insert into city values(93,'淮安市',11);

insert into city values(94,'盐城市',11);

insert into city values(95,'扬州市',11);

insert into city values(96,'镇江市',11);

insert into city values(97,'泰州市',11);

insert into city values(98,'宿迁市',11);

--select* from city where provincialID=11 order by cityID

----------------------------------------------------------------

--12浙江省(2006年，辖：11个地级市；32个市辖区、22个县级市、35个县、1个自治县。);

insert into city values(99,'杭州市',12);

insert into city values(100,'宁波市',12);

insert into city values(101,'温州市',12);

insert into city values(102,'嘉兴市',12);

insert into city values(103,'湖州市',12);

insert into city values(104,'绍兴市',12);

insert into city values(105,'金华市',12);

insert into city values(106,'衢州市',12);

insert into city values(107,'舟山市',12);

insert into city values(108,'台州市',12);

insert into city values(109,'丽水市',12);

--select* from city where provincialID=12 order by cityID

----------------------------------------------------------------没有写完,写不下了,你加我! 775790318

省市数据库和求一完整的全国省市级联的oracle数据库建表脚本的问题分享结束啦，以上的文章解决了您的问题吗？欢迎您下次再来哦！