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 |
mysql不同表一列覆盖一列
原创
©著作权归作者所有:来自51CTO博客作者am2012的原创作品,请联系作者获取转载授权,否则将追究法律责任
![](https://ucenter.51cto.com/images/noavatar_middle.gif)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
mysql比较两张表中A 不含的表 mysql对比两张表
前言上篇文章我们介绍了MySQL体系结构以及存储引擎的相关介绍。接下来,我们给大家介绍SQL语句的优化以及具体的sql优化步骤。接下来我们介绍优化sql的具体步骤。 其实,无论在开发中还是在人工智能方面,都脱离不了数据库,用来存储我们需要的数据。由于初期的时候,数据量还小,因此,我们在初期写SQL语句的时候更重视功能上的实现,但是,随着项目的真正投入使用的时候,数据量呈指数型递增,很
mysql比较两张表中A 不含的表 mysql 只展示两张表不同的数据 mysql 查看锁表 mysql性能调优与架构设计 mysql查看锁表