注册 登录
LUPA开源社区 返回首页

zhull1052的个人空间 http://www.lupaworld.com/?73681 [收藏] [复制] [分享] [RSS]

我的博客

各种mysql的数据表的拷贝方法

热度 3已有 1617 次阅读2011-5-6 16:51 |个人分类:sql类|系统分类:IT技术|

1、复制表

方法1

mysql> create table aa select * from commodity;

Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

其中,aa为新表,commodity为旧表,复制了commodity表中的3条记录到aa表中。注意,此操作只是在同一个数据库里进行拷贝,且不会复制主键与索引。以下是进行两数据库中进行表的拷贝操作。


如,在store数据库中有一个名为commodity表,现在要将其复制到bak数据库中,并命名为表aa,操作如下:

mysql> create table bak.aa select * from store.commodity;

其中,bak.aa格式为“数据库.表名”。此操作表示将store数据库中的表commodity拷贝到bak数据库中,并命名为表aa


方法2

mysql> create table commodity_new like commodity;

Query OK, 0 rows affected (0.12 sec)

mysql> insert commodity_new select * from commodity;

Query OK, 3 rows affected (0.02 sec)

Records: 3 Duplicates: 0 Warnings: 0


mysql> select * from commodity_new;

+------------+--------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| ID | name | price | stocks | unit | supplier | address | in_time | deadtime |

+------------+--------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| HZ07A00002 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00001 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00003 | 牙刷 | 5.00 | 5 | | 托托生活用品制造有限公司 | NULL | 2011-05-05 | 2020-01-05 |

+------------+--------+-------+--------+------+--------------------------------------+--------------+------------+------------+

3 rows in set (0.00 sec)


方法3

mysql> create table commodity_new1 select * from commodity where 0;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from commodity_new1;

Empty set (0.00 sec)

mysql> desc commodity_new1;

+----------+--------------+------+-----+------------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+--------------+------+-----+------------+-------+

| ID | varchar(10) | NO | | 0 | |

| name | varchar(20) | NO | | 0 | |

| price | decimal(7,2) | YES | | NULL | |

| stocks | int(5) | NO | | 0 | |

| unit | varchar(4) | YES | | NULL | |

| supplier | varchar(50) | YES | | NULL | |

| address | varchar(40) | YES | | NULL | |

| in_time | date | NO | | 0000-00-00 | |

| deadtime | date | NO | | 0000-00-00 | |

+----------+--------------+------+-----+------------+-------+

9 rows in set (0.06 sec)


注意:不复制数据,只复制了字段与其结构,且不复制原表的主键与索引。我们可以与以下语句进行比较:

mysql> create table commodity_1 like commodity;

注意:它也不复制数据,只复制字段与其结构,但是它同时也复制原表的主键与索引。


方法4

要完全复制某个表,那就要应用以下操作:

mysql> create table commodity_bak like commodity;

Query OK, 0 rows affected (0.08 sec)

mysql> insert into commodity_bak select * from commodity;

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0



2、只拷贝一个表中其中的一些字段,操作如下:

mysql> create table commodity_bak as (select ID,name,price from commodity);

Query OK, 3 rows affected (0.06 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from commodity_bak;

+------------+--------+-------+

| ID | name | price |

+------------+--------+-------+

| HZ07A00002 | 酸奶 | 2.50 |

| HZ07A00001 | 酸奶 | 2.50 |

| HZ07A00003 | 牙刷 | 5.00 |

+------------+--------+-------+

3 rows in set (0.00 sec)


当然,也可以改变字段名称,操作如下:

mysql> create table commodity_bak as (select ID as '编号',name as '商品名',price as '价格' from commodity);

Query OK, 3 rows affected (0.07 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from commodity_bak;

+------------+-----------+--------+

| 编号 | 商品名 | 价格 |

+------------+-----------+--------+

| HZ07A00002 | 酸奶 | 2.50 |

| HZ07A00001 | 酸奶 | 2.50 |

| HZ07A00003 | 牙刷 | 5.00 |

+------------+-----------+--------+

3 rows in set (0.00 sec)



3、使用union union All语句的区别

将两个或更多查询的结果组合为单个结果集,该结果集包含联合查询中的所有查询的全部行

mysql> create table aa like commodity;

Query OK, 0 rows affected (0.13 sec)

mysql> desc aa;

+----------+--------------+------+-----+------------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+--------------+------+-----+------------+-------+

| ID | varchar(10) | NO | PRI | 0 | |

| name | varchar(20) | NO | | 0 | |

| price | decimal(7,2) | YES | | NULL | |

| stocks | int(5) | NO | | 0 | |

| unit | varchar(4) | YES | | NULL | |

| supplier | varchar(50) | YES | | NULL | |

| address | varchar(40) | YES | | NULL | |

| in_time | date | NO | | 0000-00-00 | |

| deadtime | date | NO | | 0000-00-00 | |

+----------+--------------+------+-----+------------+-------+

9 rows in set (0.02 sec)

mysql> select * from aa;

+------------+-----------+-------+--------+------+-----------------------------+--------------+------------+------------+

| ID | name | price | stocks | unit | supplier | address | in_time | deadtime |

+------------+-----------+-------+--------+------+-----------------------------+--------------+------------+------------+

| 110500001 | 圆珠笔 | 2.50 | 100 | | 华鸿制笔有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| 110500002 | 上好佳 | 2.50 | 100 | | 上好佳食品有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| HZ07A00002 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| HZ07A00001 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

+------------+-----------+-------+--------+------+-----------------------------+--------------+------------+------------+

4 rows in set (0.00 sec)


mysql> select * from commodity;

+------------+--------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| ID | name | price | stocks | unit | supplier | address | in_time | deadtime |

+------------+--------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| HZ07A00002 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00001 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00003 | 牙刷 | 5.00 | 5 | | 托托生活用品制造有限公司 | NULL | 2011-05-05 | 2020-01-05 |

+------------+--------+-------+--------+------+--------------------------------------+--------------+------------+------------+

3 rows in set (0.00 sec)


mysql> select * from commodity union select * from aa;

+------------+-----------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| ID | name | price | stocks | unit | supplier | address | in_time | deadtime |

+------------+-----------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| HZ07A00002 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00001 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00003 | 牙刷 | 5.00 | 5 | | 托托生活用品制造有限公司 | NULL | 2011-05-05 | 2020-01-05 |

| 110500001 | 圆珠笔 | 2.50 | 100 | | 华鸿制笔有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| 110500002 | 上好佳 | 2.50 | 100 | | 上好佳食品有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| HZ07A00002 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | NULL | 2011-05-05 | 2012-01-05 |

+------------+-----------+-------+--------+------+--------------------------------------+--------------+------------+------------+

6 rows in set (0.00 sec)


mysql> select * from commodity union all select * from aa;

+------------+-----------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| ID | name | price | stocks | unit | supplier | address | in_time | deadtime |

+------------+-----------+-------+--------+------+--------------------------------------+--------------+------------+------------+

| HZ07A00002 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00001 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

| HZ07A00003 | 牙刷 | 5.00 | 5 | | 托托生活用品制造有限公司 | NULL | 2011-05-05 | 2020-01-05 |

| 110500001 | 圆珠笔 | 2.50 | 100 | | 华鸿制笔有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| 110500002 | 上好佳 | 2.50 | 100 | | 上好佳食品有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| HZ07A00002 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | NULL | 2011-05-05 | 2012-01-05 |

| HZ07A00001 | 酸奶 | 2.50 | 100 | | 伊利乳液有限公司 | 新疆伊犁 | 2011-05-05 | 2012-01-05 |

+------------+-----------+-------+--------+------+--------------------------------------+--------------+------------+------------+

7 rows in set (0.00 sec)


从以上语句可知:使用UNION组合两个表时,将重复的记录删除;而使用UNION ALL组合两多个表时,不考虑结果集中是否存在重复记录。


4、将数据表拷贝到本地目录下,操作如下:

mysql> select * from store.commodity into outfile "/tmp/commodity_out";

以上是将store数据库中的commodity表,拷由到本地的/tmp目录下。

刚表态过的朋友 (0 人)

评论 (0 个评论)

facelist

您需要登录后才可以评论 登录 | 注册
验证问答 换一个 验证码 换一个

关于LUPA|人才芯片工程|人才招聘|LUPA认证|LUPA教育|LUPA开源社区 ( 浙B2-20090187 浙公网安备 33010602006705号   

返回顶部