1.两张表格分别如下 MariaDB [test]> desc t; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | percent | int(11) | YES | | NULL | | | dt | datetime | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 2 rows in set (0.01 sec) MariaDB [test]> desc t2 -> ; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+----------+------+-----+---------+-------+ | studentid | int(11) | YES | | NULL | | | name | char(10) | YES | | NULL | | +-----------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) MariaDB [test]> MariaDB [test]> select * from t; +---------+---------------------+ | percent | dt | +---------+---------------------+ | 0 | 2017-09-12 16:24:49 | | 2 | 2017-09-12 16:24:56 | | 0 | 2017-09-12 16:25:03 | | 0 | 2017-09-12 16:34:50 | | 0 | 2017-09-12 16:34:58 | | 99 | 2017-09-12 16:35:04 | | 100 | 2017-09-12 16:35:11 | | 99 | 2017-09-14 11:27:00 | | 99 | 2017-09-14 11:28:00 | | 99 | 2017-09-14 11:29:00 | | 99 | 2017-09-14 11:30:00 | | 99 | 2017-09-14 11:35:00 | | 99 | 2017-09-14 11:36:00 | | 99 | 2017-09-14 11:37:00 | | 99 | 2017-09-14 11:38:00 | | 99 | 2017-09-14 11:39:00 | | 99 | 2017-09-14 11:40:00 | +---------+---------------------+ 17 rows in set (0.00 sec) MariaDB [test]> select * from t2; +-----------+----------+ | studentid | name | +-----------+----------+ | 1 | s_name1 | | 2 | s_name2 | | 3 | s_name3 | | 4 | s_name4 | | 5 | s_name5 | | 6 | s_name6 | | 7 | s_name7 | | 8 | s_name8 | | 9 | s_name9 | | 10 | s_name10 | | 11 | s_name11 | | 12 | s_name12 | | 13 | s_name13 | | 14 | s_name14 | | 15 | s_name15 | | 16 | s_name16 | | 17 | s_name17 | | 18 | s_name18 | | 19 | s_name19 | | 20 | s_name20 | 2.目标要求把t的dt列 所有数据 按从上到下的顺序 整个复制到t2 2.1 先在t2加dt列 MariaDB [test]> alter table t2 add dt datetime; Query OK, 99 rows affected (0.03 sec) Records: 99 Duplicates: 0 Warnings: 0 MariaDB [test]> 2.2 在表t加一列studentid并且设为自增长主键 MariaDB [test]> alter table t add studentid int first; Query OK, 17 rows affected (0.05 sec) Records: 17 Duplicates: 0 Warnings: 0 MariaDB [test]> select * from t; +-----------+---------+---------------------+ | studentid | percent | dt | +-----------+---------+---------------------+ | NULL | 0 | 2017-09-12 16:24:49 | | NULL | 2 | 2017-09-12 16:24:56 | | NULL | 0 | 2017-09-12 16:25:03 | | NULL | 0 | 2017-09-12 16:34:50 | | NULL | 0 | 2017-09-12 16:34:58 | | NULL | 99 | 2017-09-12 16:35:04 | | NULL | 100 | 2017-09-12 16:35:11 | | NULL | 99 | 2017-09-14 11:27:00 | | NULL | 99 | 2017-09-14 11:28:00 | | NULL | 99 | 2017-09-14 11:29:00 | | NULL | 99 | 2017-09-14 11:30:00 | | NULL | 99 | 2017-09-14 11:35:00 | | NULL | 99 | 2017-09-14 11:36:00 | | NULL | 99 | 2017-09-14 11:37:00 | | NULL | 99 | 2017-09-14 11:38:00 | | NULL | 99 | 2017-09-14 11:39:00 | | NULL | 99 | 2017-09-14 11:40:00 | +-----------+---------+---------------------+ MariaDB [test]> alter table t change studentid studentid int not null auto_increment primary key; Query OK, 17 rows affected (0.03 sec) Records: 17 Duplicates: 0 Warnings: 0 MariaDB [test]> MariaDB [test]> select * from t -> ; +-----------+---------+---------------------+ | studentid | percent | dt | +-----------+---------+---------------------+ | 1 | 0 | 2017-09-12 16:24:49 | | 2 | 2 | 2017-09-12 16:24:56 | | 3 | 0 | 2017-09-12 16:25:03 | | 4 | 0 | 2017-09-12 16:34:50 | | 5 | 0 | 2017-09-12 16:34:58 | | 6 | 99 | 2017-09-12 16:35:04 | | 7 | 100 | 2017-09-12 16:35:11 | | 8 | 99 | 2017-09-14 11:27:00 | | 9 | 99 | 2017-09-14 11:28:00 | | 10 | 99 | 2017-09-14 11:29:00 | | 11 | 99 | 2017-09-14 11:30:00 | | 12 | 99 | 2017-09-14 11:35:00 | | 13 | 99 | 2017-09-14 11:36:00 | | 14 | 99 | 2017-09-14 11:37:00 | | 15 | 99 | 2017-09-14 11:38:00 | | 16 | 99 | 2017-09-14 11:39:00 | | 17 | 99 | 2017-09-14 11:40:00 | +-----------+---------+---------------------+ 17 rows in set (0.00 sec) MariaDB [test]> 3.用t的dt列按顺序覆盖t2的dt列 MariaDB [test]> update t2,t set t2.dt=t.dt where t2.studentid=t.studentid; Query OK, 17 rows affected (0.01 sec) Rows matched: 17 Changed: 17 Warnings: 0 MariaDB [test]> MariaDB [test]> select * from t2; +-----------+----------+---------------------+ | studentid | name | dt | +-----------+----------+---------------------+ | 1 | s_name1 | 2017-09-12 16:24:49 | | 2 | s_name2 | 2017-09-12 16:24:56 | | 3 | s_name3 | 2017-09-12 16:25:03 | | 4 | s_name4 | 2017-09-12 16:34:50 | | 5 | s_name5 | 2017-09-12 16:34:58 | | 6 | s_name6 | 2017-09-12 16:35:04 | | 7 | s_name7 | 2017-09-12 16:35:11 | | 8 | s_name8 | 2017-09-14 11:27:00 | | 9 | s_name9 | 2017-09-14 11:28:00 | | 10 | s_name10 | 2017-09-14 11:29:00 | | 11 | s_name11 | 2017-09-14 11:30:00 | | 12 | s_name12 | 2017-09-14 11:35:00 | | 13 | s_name13 | 2017-09-14 11:36:00 | | 14 | s_name14 | 2017-09-14 11:37:00 | | 15 | s_name15 | 2017-09-14 11:38:00 | | 16 | s_name16 | 2017-09-14 11:39:00 | | 17 | s_name17 | 2017-09-14 11:40:00 |
转载于:https://blog.51cto.com/goome/1965456