mysql跨数据库复制表(在同一IP地址中)示例_MySQL
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

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

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. 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

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

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

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 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.

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.

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
