原文:http://blog.csdn.net/liupeng900605/article/details/7059799
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 commodityunion 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 commodityunion 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";