Home Database Mysql Tutorial mysql跨数据库复制表(在同一IP地址中)示例_MySQL

mysql跨数据库复制表(在同一IP地址中)示例_MySQL

Jun 01, 2016 pm 01:18 PM
s Database development Database Table Programming

bitsCN.com

数据库表间数据复制分类

在利用数据库开发时,常常会将一些表之间的数据互相导入。当然可以编写程序实现,但是,程序常常需要开发环境,不方便。最方便是利用sql语言直接导入。既方便而修改也简单。以下就是导入的方法。

1、 表结构相同的表,且在同一数据库(如,table1,table2)

Sql :

insert into table1 select   *    from table2 (完全复制)
insert into table1 select   distinct   *   from table2(不复制重复纪录)
insert into table1 select   top 5 *   from   table2 (前五条纪录)

2、不在同一数据库中(如,db1 table1,db2 table2)

sql:       
[code]
insert into db1.table1 select   *    from db2.table2 (完全复制)
insert into db1.table1 select   distinct   *   from db2table2(不复制重复纪录)
insert into tdb1.able1 select   top 5 *   from   db2table2 (前五条纪录)

3、表结构不同的表或复制部分纪录(如,dn_user,dn_user2)

a.  建一个新表[DN_UserTemp](在老表dn_user上增加一列)

CREATE TABLE [DN_UserTemp] ( [Num] [numeric](18, 0) IDENTITY (1, 1) NOT NULL)
[Id] [idtype] NOT NULL ,
[Name] [fntype] NOT NULL ,
[Descript] [dstype] NULL ,
[LogonNm] [idtype] NOT NULL ,
[Password] [idtype] NULL ,
[Gender] [char] (1) NULL ,
[Quited] [booltype] NOT NULL,
[OffDuty] [booltype] NOT NULL ,
[Stopped] [booltype] NOT NULL,
[OSBind] [booltype] NOT NULL,
[Domain] [idtype] NULL ,
[EMail] [fntype] NULL ,
[UnitId] [idtype] NULL ,
[BranchId] [idtype] NULL ,
[DutyId] [idtype] NULL ,
[LevelId] [idtype] NULL ,
[ClassId] [idtype] NULL ,
[TypeId] [idtype] NULL ,
[IP] [varchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[ExpireDT] [datetime] NULL ,
[Sort] [int] NOT NULL ,
[AllowDel] [booltype] NOT NULL,
[UnitChief] [booltype] NOT NULL,
[BranchChief] [booltype] NOT NULL ,
[UnitDeputy] [booltype] NOT NULL ,
[BranchDeputy] [booltype] NOT NULL ,

[Num] [numeric](18, 0) IDENTITY (1, 1) NOT NULL

) ON [PRIMARY]

b. 将dn_uer2的数据拷入dn_usertemp

sql:insert into dn_usertemp select * from dn_user2

c.将dn_usertemp 拷入dn_user

sql:

declare   @i int
declare   @j int
declare   @Name fntype
set @i=1
select @j=count(*) from dn_usertemp
while @ibegin

select @Name=Name from dn_usertemp where Num=@i
print @Name
insert into dn_user (Name) values (@Name) where Num=@i
select @i=@i 1
end


MySql数据库复制表数据

将 production 数据库中的 mytbl 表快速复制为 mytbl_new,2个命令如下:

CREATE TABLE mytbl_new LIKE production.mytbl;
INSERT mytbl_new SELECT * FROM production.mytbl;

第一个命令是创建新的数据表 mytbl_new ,并复制 mytbl 的数据表结构。

第二个命令是讲数据表 mytbl 中的数据复制到新表 mytbl_new 。

注:production.mytbl是指定要复制表的数据库名称为 production 。它是可选的。

假如没有production. ,MySQL数据库将会假设mytbl在当前操作的数据库。

另外:在mysql数据库中复制数据为:

select * into desTable from sourceTable在mssql中支持,在mysql中不支持
insert into desTable select * from sourceTable

bitsCN.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Steps and tips for programming with Golang on Mac Steps and tips for programming with Golang on Mac Mar 03, 2024 am 08:30 AM

Title: Steps and techniques for using Golang programming on Mac In the current field of software development, Golang (also known as Go), as an efficient, concise, and highly concurrency programming language, has attracted the attention of more and more developers. use. When programming Golang on the Mac platform, you can use some tools and techniques to improve development efficiency. This article will introduce the steps and techniques of using Golang programming on Mac, and provide specific code examples to help readers better understand and apply. Step 1: Install Gol

Use PHP to write programs to connect to Baidu Cloud Disk API Use PHP to write programs to connect to Baidu Cloud Disk API Aug 14, 2023 pm 02:21 PM

Use PHP to write programs to connect to Baidu Cloud Disk API. Baidu Cloud Disk is a powerful cloud storage service. Through the API, many automated operations can be realized, such as uploading files, downloading files, creating folders, etc. This article will introduce how to use PHP to write a program to connect to Baidu Cloud Disk API, and give corresponding code examples. Obtain Baidu Cloud's developer account and APIKey. Before using Baidu Cloud Disk API, we need to apply for a developer account and obtain the corresponding APIKey. Can log in to Baidu

The industry's first AI smart screen camera, Skyworth Smart Screen Camera S50, is launched for the first time The industry's first AI smart screen camera, Skyworth Smart Screen Camera S50, is launched for the first time Nov 18, 2023 pm 06:43 PM

Skyworth Security recently released its annual flagship new product - Skyworth Smart Screen Camera S50. As the first smart screen camera equipped with artificial intelligence technology in the industry, the biggest feature of Skyworth Smart Screen Camera S50 is that it cleverly combines a color smart screen with a camera to realize the innovative function of two-way visual calls; its powerful 2T computing power makes it The artificial intelligence capability has been greatly improved. This new flagship product of the year has a futuristic appearance, fine frosted material, and is equipped with a colorful touchable high-definition screen with smooth and smooth operation; two-way video intercom, WeChat video call; 500W Extremely clear picture quality, 360° monitoring without blind spots; extreme black light full-color night vision, no matter how dark it is, it is as colorful as daytime; 12x smart zoom, you can see every detail clearly when you zoom in; the rewritten content is: 2

PHP and SQL Server database development PHP and SQL Server database development Jun 20, 2023 pm 10:38 PM

With the popularity of the Internet, website and application development has become the main business of many companies and individuals. PHP and SQLServer database are two very important tools. PHP is a server-side scripting language that can be used to develop dynamic websites; SQL Server is a relational database management system developed by Microsoft and has a wide range of application scenarios. In this article, we will discuss the development of PHP and SQL Server, as well as their advantages, disadvantages and application methods. First, let's

How to distinguish database views and tables How to distinguish database views and tables Aug 22, 2023 am 11:27 AM

Database views and tables are two different concepts in the database, with different characteristics and uses. A table is an entity that actually stores data in the database, while a view is a virtual table derived from one or more tables, used to specify way to present and manipulate data. Tables have higher data persistence, while views provide more flexible and convenient data access.

How to create and manage database tables using PHP How to create and manage database tables using PHP Sep 09, 2023 pm 04:48 PM

How to use PHP to create and manage database tables With the rapid development of the Internet, databases have become an indispensable part of various websites and applications. In PHP, we can use a database management system (DBMS) such as MySQL to create and manage database tables. This article will teach you how to use PHP to implement this function, with corresponding code examples. Connect to the database First, we need to connect to the database in PHP. You can use the mysqli extension or PDO provided by PHP to achieve this function.

What are the differences between database views and tables? What are the differences between database views and tables? Sep 04, 2023 pm 03:13 PM

The differences between database views and tables are: 1. A table is a physical structure used to store data in a database, while a view is just a query result set based on a table or multiple tables; 2. A table is the physical storage unit of data, and a view only provides Rules for viewing and operating table data; 3. Views provide an advanced security mechanism for the database, and tables have no security mechanism; 4. Views are abstractions of tables; 5. Views can combine multiple tables in queries, and tables can only query a single table; 6. Tables are permanent structures in the database, views are not; 7. Views can create views with the same name, but tables cannot create tables with the same name, etc.

Write a program in Java to replace all characters in a file with '#' except specific words Write a program in Java to replace all characters in a file with '#' except specific words Sep 13, 2023 am 08:57 AM

The split() method of String class. Split the current string into matches of the given regular expression. The array returned by this method contains each substring of this string that is terminated by another substring that matches the given expression or terminates at the end of the string. The replaceAll() method of String class accepts two strings representing regular expressions and a replacement string and replaces the matching value with the given string. Replace all characters in the file except specific words with "#" (one way) - read the contents of the file into a string. Create an empty StringBuffer object. Use the split() method to split the obtained string into a String array. all over

See all articles