INSERT

最新推荐文章于 2024-08-28 06:11:17 发布

原创最新推荐文章于 2024-08-28 06:11:17 发布 · 686 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

21 篇文章

订阅专栏

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

插入记录的几种方法

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      |
+----+----------+