mysql中insert into语句的5种写法(上)

时间:2020-11-29 04:42:41

insert into是mysql中最常用的插入语句,它有5种写法。下面让我们一起来看一看吧:

mysql中insert into语句的5种写法(下)

 http://blog.csdn.net/number1killer/article/details/77842472

1向原表中某些字段中插入一条记录。

语法:insert into +表名(表中的字段,,)value(字段所对应的记录,,);


mysql> select * from joke;
+------+-----------+-------+------+
| gid  | name      | sex   | age  |
+------+-----------+-------+------+
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan22 | 1male |   22 |
+------+-----------+-------+------+
4 rows in set (0.02 sec)
mysql> insert into joke (gid,name)value(0,"joker");
Query OK, 1 row affected (0.09 sec)
mysql> select * from joke;
+------+-----------+-------+------+
| gid  | name      | sex   | age  |
+------+-----------+-------+------+
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan22 | 1male |   22 |
|    0 | joker     | NULL  | NULL |
+------+-----------+-------+------+
5 rows in set (0.00 sec)
2向原表中字段中插入多条记录的方法一。
语法:insert into +表名(表中的字段,,)values(字段所对应的记录,,)(字段所对应的记录);

mysql> insert into joke (gid,name)values(0,"joker"),(1,"jhj");
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from joke;
+------+-----------+-------+------+
| gid  | name      | sex   | age  |
+------+-----------+-------+------+
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan2  | male  |   22 |
|    3 | xiaowan22 | 1male |   22 |
|    0 | joker     | NULL  | NULL |
|    0 | joker     | NULL  | NULL |
|    1 | jhj       | NULL  | NULL |
+------+-----------+-------+------+
7 rows in set (0.00 sec)
3向原表中字段中插入多条记录的方法二。
语法:insert into+表名 select v1,v2 union all
          select v1,v2;
mysql> insert into book select "abc","bdd" union  all
    -> select "add","dsf";
Query OK, 2 rows affected (0.16 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from book;
+------+------+
| a    | b    |
+------+------+
| abc  | bdd  |
| add  | dsf  |
+------+------+
2 rows in set (0.00 sec)

如果插入的记录是数字的话要在数字的逗号后面加n:

mysql>  insert into Student select 1,N'刘一',18,N'男' union all
    ->  select 2,N'钱二',19,N'女';
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from student;
+------+--------+------+------+
| s    | sname  | sage | ssex |
+------+--------+------+------+
|    1 | 刘一   |   18 | 男   |
|    2 | 钱二   |   19 | 女   |
|    3 | 张三   |   17 | 男   |
|    4 | 李四   |   18 | 女   |
|    5 | 王五   |   17 | 男   |
|    6 | 赵六   |   19 | 女   |
|    1 | 刘一   |   18 | 男   |
|    2 | 钱二   |   19 | 女   |
+------+--------+------+------+
8 rows in set (0.00 sec)