Mysql的insert语句常用方法

最新推荐文章于 2023-12-15 12:28:04 发布

alphabeta12345

最新推荐文章于 2023-12-15 12:28:04 发布

阅读量736

点赞数

分类专栏： mysql 文章标签： mysql insert

本文链接：https://blog.csdn.net/u011402596/article/details/38086549

版权

mysql 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

表的结构为

mysql> desc sqoopTest;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id1   | int(11)     | YES  |     | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
| id2   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

第一种情况：

insert into sqoopTest values (1, 'abc', 4);

mysql> select * from sqoopTest where name = 'abc';
+------+------+------+
| id1  | name | id2  |
+------+------+------+
|    1 | abc  |    4 |
+------+------+------+
1 row in set (59.36 sec)

第二种情况：

insert into sqoopTest (id1, name, id2) values (1, 'abd', 10);

mysql> select * from sqoopTest where name = 'abd';
+------+------+------+
| id1  | name | id2  |
+------+------+------+
|    1 | abd  |   10 |
+------+------+------+

也可以这样

insert into sqoopTest (id1, name) values (1, 'abe');

第三种情况：

INSERT INTO sqoopTest SET id1 = 123, name = 'abf', id2 = 25;

mysql> select * from sqoopTest where name = 'abf';
+------+------+------+
| id1  | name | id2  |
+------+------+------+
|  123 | abf  |   25 |
+------+------+------+

至少为一列赋值

INSERT INTO sqoopTest SET name = 'abg', id2 = 25;

mysql> select * from sqoopTest where name = 'abg';
+------+------+------+
| id1  | name | id2  |
+------+------+------+
| NULL | abg  |   25 |
+------+------+------+

第四种情况

<pre name="code" class="sql">insert into sqoopTest( ,  , ) select ( , , ) from sqoopTest where ...;

insert into sqoopTest select * from sqoopTest where name = 'abg';

mysql> select * from sqoopTest where name = 'abg';

+------+------+------+
| id1  | name | id2  |
+------+------+------+
| NULL | abg  |   25 |
| NULL | abg  |   25 |
+------+------+------+
2 rows in set (56.96 sec)