例子1 复制完全相同的记录(条件是表结构中没有auto_increment,和uniq字段) mysql> select * from president; +------------+---------------+--------+---------------------+-------+------------+------------+ | last_name | first_name | suffix | city | state | birth | death | +------------+---------------+--------+---------------------+-------+------------+------------+ | Washington | George | NULL | Wakefield | VA | 1732-02-22 | 1799-12-14 | | Adams | John | NULL | Braintree | MA | 1735-10-30 | 1826-07-04 | | Jefferson | Thomas | NULL | Albemarle County | VA | 1743-04-13 | 1826-07-04 | | Madison | James | NULL | Port Conway | VA | 1751-03-16 | 1836-06-28 | mysql> desc president; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | last_name | varchar(15) | NO | MUL | NULL | | | first_name | varchar(15) | NO | | NULL | | | suffix | varchar(5) | YES | | NULL | | | city | varchar(20) | NO | | NULL | | | state | varchar(2) | NO | | NULL | | | birth | date | NO | | NULL | | | death | date | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 在这种情况下要在表中复制一条Washington George 的记录,输入以下命令即可: mysql> insert into president select * from president where last_name='Washington'; 例子2:新增部分字段相同的记录 mysql> select * from manager; +----+-------------+-------+-------------+----------+------------+---------------------+ | id | enable_flag | types | account | password | location | reg_date | +----+-------------+-------+-------------+----------+------------+---------------------+ | 1 | 1 | 1 | abcd | 1234567 | | 0000-00-00 00:00:00 | | 13 | 1 | 2 | anonymous | 654123 | 0100000000 | 2003-06-30 17:29:04 | 其表结构如下: mysql> desc manager; +-------------+-------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+----------------+ | id | int(4) | NO | PRI | NULL | auto_increment | | enable_flag | smallint(2) | NO | | 0 | | | types | smallint(2) | NO | | 0 | | | account | varchar(50) | NO | UNI | | | | password | varchar(50) | NO | | | | | location | varchar(10) | NO | | | | | reg_date | datetime | NO | | 0000-00-00 00:00:00 | | ------------+-------------+------+-----+---------------------+----------------+ 若想通过新增一条和manager 表中的anonymous这条记录,其它字段内容相同,但名字不同的记录(account为test)。则得用以下方法: 因为manager表的 id字段是auto_increment,而且account字段是具有唯一性的(UNI).所以我们得具体指定这两个字段的值。 mysql> insert into manager select '22',enable_flag,types,'test',password, location,reg_date from manager where account='anonymous';
mysql 复制表记录_mysql复制表记录
最新推荐文章于 2022-12-29 11:22:59 发布