MySQL插入记录技巧-优快云博客

本文链接：https://blog.youkuaiyun.com/openUrl/article/details/52769992

本文介绍MySQL中插入记录的三种方法，包括使用VALUES批量插入多条记录、使用SET插入单条记录以及通过SELECT从其他表中选择性地插入数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

插入记录的几种方法

INSERT [INTO] tbl_name [(col_name,…)] {VALUES|VALUE} ({expr | DEFAULT}), (),….
- 可以插入多条。
- INSERT users VALUES(DEFAULT, ‘Json’,’123’,23,1),(NULL,’Rose’,md5(‘123’),DEFAULT,0);
INSERT [INTO] tbl_name SET col_name={expr | DEFAULT} ,…
- ps:与第一种方式的区别在于，此方法可以使用子查询（SubQuery）, 一次只能插入一条记录。
- INSERT users SET username=’Ben’,password=’456’;
INSERT [INTO] tbl_name [(col_name,…)] SELECT…
- 此方法可以将查询结果插入到指定数据表中
- INSERT test2(username) SELECT username FROM users WHERE age>=25

// 自增修饰的值可以设置为null 或default
// 插入的值可以是表达式、函数。

1

mysql> USE test;
Database changed
mysql> CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL,
    -> password VARCHAR(32) NOT NULL,
    -> age TINYINT UNSIGNED NOT NULL DEFAULT 10,
    -> sex BOOLEAN
    -> );
Query OK, 0 rows affected (0.21 sec)

//
//
//
mysql> INSERT users VALUES(NULL, 'Tom','123',23,1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  23 |    1 |
+----+----------+----------+-----+------+
1 row in set (0.00 sec)

//
//
//
mysql> INSERT users VALUES(NULL, 'Json','123',23,1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  23 |    1 |
|  2 | Json     | 123      |  23 |    1 |
+----+----------+----------+-----+------+
2 rows in set (0.00 sec)

//
//
//
mysql> INSERT users VALUES(DEFAULT, 'Json','123',23,1);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  23 |    1 |
|  2 | Json     | 123      |  23 |    1 |
|  3 | Json     | 123      |  23 |    1 |
+----+----------+----------+-----+------+
3 rows in set (0.00 sec)

//
//
//
mysql> SELECT * FROM users;
+----+----------+----------+-----+------+
| id | username | password | age | sex  |
+----+----------+----------+-----+------+
|  1 | Tom      | 123      |  23 |    1 |
|  2 | Json     | 123      |  23 |    1 |
|  3 | Json     | 123      |  23 |    1 |
|  4 | Json     | 123      |  10 |    1 |
+----+----------+----------+-----+------+
4 rows in set (0.00 sec)

//
//
//
mysql> INSERT users VALUES(DEFAULT, 'Json','123',23,1),(NULL,'Rose',md5('123'),DEFAULT,0);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  23 |    1 |
|  2 | Json     | 123                              |  23 |    1 |
|  3 | Json     | 123                              |  23 |    1 |
|  4 | Json     | 123                              |  10 |    1 |
|  5 | Json     | 123                              |  23 |    1 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |
+----+----------+----------------------------------+-----+------+
6 rows in set (0.00 sec)

mysql>

2

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  23 |    1 |
|  2 | Json     | 123                              |  23 |    1 |
|  3 | Json     | 123                              |  23 |    1 |
|  4 | Json     | 123                              |  10 |    1 |
|  5 | Json     | 123                              |  23 |    1 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |
+----+----------+----------------------------------+-----+------+
6 rows in set (0.00 sec)

mysql> INSERT users SET username='Ben',password='456';
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  23 |    1 |
|  2 | Json     | 123                              |  23 |    1 |
|  3 | Json     | 123                              |  23 |    1 |
|  4 | Json     | 123                              |  10 |    1 |
|  5 | Json     | 123                              |  23 |    1 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  10 |    0 |
|  7 | Ben      | 456                              |  10 | NULL |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

3

把users表中年龄大于25的用户名写入到test2表中

users表：

mysql> SELECT * FROM users;
+----+----------+----------------------------------+-----+------+
| id | username | password                         | age | sex  |
+----+----------+----------------------------------+-----+------+
|  1 | Tom      | 123                              |  27 |    0 |
|  3 | Json     | 123                              |  25 |    0 |
|  4 | Json     | 123                              |  21 |    0 |
|  5 | Json     | 123                              |  23 |    0 |
|  6 | Rose     | 202cb962ac59075b964b07152d234b70 |  19 |    0 |
|  7 | Ben      | 456                              |   8 |    0 |
|  8 | Hek      | 11111                            |  33 | NULL |
+----+----------+----------------------------------+-----+------+
7 rows in set (0.00 sec)

test2 表：

mysql> CREATE TABLE test2(
    -> id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.19 sec)

操作

mysql> INSERT test2(username) SELECT username FROM users WHERE age>=25;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2
    -> ;
+----+----------+
| id | username |
+----+----------+
|  1 | Tom      |
|  2 | Json     |
|  3 | Hek      |
+----+----------+