插入，更新与删除记录的操作_vfp 删除多行记录-CSDN博客

本文链接：https://blog.csdn.net/weixin_50565623/article/details/108723152

DML操作插入数据的操作

不指定具体字段名称
– INSERT [INTO] tbl_name VALUES/VALUE(…)

mysql>  CREATE TABLE IF NOT EXISTS user1(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE,
    -> password CHAR(50) NOT NULL,
    -> email VARCHAR(50) NOT NULL,
    -> age TINYINT UNSIGNED DEFAULT 18
    -> );
Query OK, 0 rows affected (0.77 sec)

mysql> INSERT INTO user1 VALUES(1, "zhouruc16","12345","zhouruc16@gmail.com",DEFAULT);
Query OK, 1 row affected (0.54 sec)

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |   18 |
+----+-----------+----------+---------------------+------+

列出指定字段
– INSERT [INTO] tbl_name(字段名称1,…) VALUE/VALUES (插入内容,…)
– 注意，值和字段顺序一一对应
– 注意，不插入内容的字段要有DEFAULT VALUE

mysql> INSERT INTO user1 (username,email) VALUES ("AAA", "AAA@gmail.com");
ERROR 1364 (HY000): Field 'password' doesn't have a default value
mysql> INSERT INTO user1 (username,email, password) VALUES ("AAA", "AAA@GMAIL.COM","abcde");
Query OK, 1 row affected (0.45 sec)

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |   18 |
|  2 | AAA       | abcde    | AAA@GMAIL.COM       |   18 |
+----+-----------+----------+---------------------+------+
2 rows in set (0.00 sec)

同时插入多条记录
– INSERT [INTO] tbl_name (字段名称） VALUES（插入内容1), (插入内容2),…

mysql> INSERT INTO user1 (username, password, email) VALUES ("BBB","123","BBB@GMAIL.COM"),("CCC","456","CCC@GMAIL.COM"),("DDD","123","DDD@GMAIL.COM");
Query OK, 3 rows affected (0.46 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |   18 |
|  2 | AAA       | abcde    | AAA@GMAIL.COM       |   18 |
|  3 | BBB       | 123      | BBB@GMAIL.COM       |   18 |
|  4 | CCC       | 456      | CCC@GMAIL.COM       |   18 |
|  5 | DDD       | 123      | DDD@GMAIL.COM       |   18 |
+----+-----------+----------+---------------------+------+
5 rows in set (0.00 sec)

通过SET形式插入记录
– INSERT [INTO] tbl_name SET 字段名称1=值1，名称2=值2…

mysql> INSERT INTO user1 SET username = "EEE", password = "789", email = "EEE@GMAIL.COM";
Query OK, 1 row affected (0.58 sec)

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |   18 |
|  2 | AAA       | abcde    | AAA@GMAIL.COM       |   18 |
|  3 | BBB       | 123      | BBB@GMAIL.COM       |   18 |
|  4 | CCC       | 456      | CCC@GMAIL.COM       |   18 |
|  5 | DDD       | 123      | DDD@GMAIL.COM       |   18 |
|  6 | EEE       | 789      | EEE@GMAIL.COM       |   18 |
+----+-----------+----------+---------------------+------+
6 rows in set (0.00 sec)

将查询结果插入到表中
– 将其他表中的内容插入一张表中
– INSER [INTO] tbl_name (字段名称）SELECT (字段名称) FROM tbl_name2 [WHERE 条件]
– 要保证数据和数据类型复合，字段数目匹配。字段数目不匹配将会报错

mysql> CREATE TABLE IF NOT EXISTS test1(
    -> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    -> username VARCHAR(20) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.76 sec)
mysql> INSERT INTO test1(username) SELECT username FROM user1;
Query OK, 6 rows affected (0.46 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT*FROM test1;
+----+-----------+
| id | username  |
+----+-----------+
|  1 | AAA       |
|  2 | BBB       |
|  3 | CCC       |
|  4 | DDD       |
|  5 | EEE       |
|  6 | zhouruc16 |
+----+-----------+
6 rows in set (0.00 sec)

DML更新数据操作

UPDATE tbl_name SET 字段名称=值,… [WHERE 条件][ORDER BY 字段名称][LIMIT限制条件]
– 不加入where条件时所有记录都会更新，比如，将所有年龄更新为10
这里我们以user1 为例子

mysql> UPDATE user1 SET age = 10;
Query OK, 6 rows affected (0.13 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |   10 |
|  2 | AAA       | abcde    | AAA@GMAIL.COM       |   10 |
|  3 | BBB       | 123      | BBB@GMAIL.COM       |   10 |
|  4 | CCC       | 456      | CCC@GMAIL.COM       |   10 |
|  5 | DDD       | 123      | DDD@GMAIL.COM       |   10 |
|  6 | EEE       | 789      | EEE@GMAIL.COM       |   10 |
+----+-----------+----------+---------------------+------+
6 rows in set (0.00 sec)

– 更新多个字段

mysql> UPDATE user1 SET email = "zhouruc16@gmail.com",age=5;
Query OK, 6 rows affected (0.14 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |    5 |
|  2 | AAA       | abcde    | zhouruc16@gmail.com |    5 |
|  3 | BBB       | 123      | zhouruc16@gmail.com |    5 |
|  4 | CCC       | 456      | zhouruc16@gmail.com |    5 |
|  5 | DDD       | 123      | zhouruc16@gmail.com |    5 |
|  6 | EEE       | 789      | zhouruc16@gmail.com |    5 |
+----+-----------+----------+---------------------+------+
6 rows in set (0.00 sec)

– 更新符合条件的记录，比如将第一条记录的邮箱，年龄，密码改变。这里我们用where查询第一条记录的主键，也就是id=1

mysql> UPDATE user1 SET age = 10 WHERE id=1;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |   10 |
|  2 | AAA       | abcde    | zhouruc16@gmail.com |    5 |
|  3 | BBB       | 123      | zhouruc16@gmail.com |    5 |
|  4 | CCC       | 456      | zhouruc16@gmail.com |    5 |
|  5 | DDD       | 123      | zhouruc16@gmail.com |    5 |
|  6 | EEE       | 789      | zhouruc16@gmail.com |    5 |
+----+-----------+----------+---------------------+------+
6 rows in set (0.00 sec)

– 也可以通过运算来限制更新条件

mysql> UPDATE user1 SET age = 8 where id>3;
Query OK, 3 rows affected (0.14 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT*FROM user1;
+----+-----------+----------+---------------------+------+
| id | username  | password | email               | age  |
+----+-----------+----------+---------------------+------+
|  1 | zhouruc16 | 12345    | zhouruc16@gmail.com |   10 |
|  2 | AAA       | abcde    | zhouruc16@gmail.com |    5 |
|  3 | BBB       | 123      | zhouruc16@gmail.com |    5 |
|  4 | CCC       | 456      | zhouruc16@gmail.com |    8 |
|  5 | DDD       | 123      | zhouruc16@gmail.com |    8 |
|  6 | EEE       | 789      | zhouruc16@gmail.com |    8 |
+----+-----------+----------+---------------------+------+
6 rows in set (0.00 sec)

– 之后会讲ORDER BY 和LIMIT 的用法

删除数据操作

DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

mysql> SELECT*FROM test1;
+----+-----------+
| id | username  |
+----+-----------+
|  1 | AAA       |
|  2 | BBB       |
|  3 | CCC       |
|  4 | DDD       |
|  5 | EEE       |
|  6 | zhouruc16 |
+----+-----------+
6 rows in set (0.00 sec)
mysql> DELETE FROM test1 where id = 1;
Query OK, 1 row affected (0.14 sec)

mysql> SELECT*FROM test1;
+----+-----------+
| id | username  |
+----+-----------+
|  2 | BBB       |
|  3 | CCC       |
|  4 | DDD       |
|  5 | EEE       |
|  6 | zhouruc16 |
+----+-----------+
5 rows in set (0.00 sec)

– 通过delet删除的记录没有重置AUTO_INCREMENT 的值，比如我这时候再插入记录

mysql> INSERT INTO test1 SET id=DEFAULT, username = "FFF";
Query OK, 1 row affected (0.14 sec)

mysql> SELECT*FROM test1;
+----+-----------+
| id | username  |
+----+-----------+
|  2 | BBB       |
|  3 | CCC       |
|  4 | DDD       |
|  5 | EEE       |
|  8 | FFF       |
|  6 | zhouruc16 |
+----+-----------+
6 rows in set (0.00 sec)

就算清空之后自增长也还是1.

彻底清空数据表
– TRUNCATE [TABLE] tbl_name
– 彻底清空 TRUNCATE 是可以重置自增长值得
– 彻底清空不能带有where 条件

mysql> TRUNCATE test1;
Query OK, 0 rows affected (0.25 sec)

mysql> SELECT*FROM test1;
Empty set (0.00 sec)

插入，更新与删除记录的操作

DML操作 插入数据的操作

DML更新数据操作

删除数据操作

DML操作插入数据的操作