插入记录的几种方法
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 |
+----+----------+