Mysql的insert语句常用方法

来源：互联网发布：apache官网下载tomcat 编辑：程序博客网时间：2024/06/05 09:09

表的结构为

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)

0 0