MySQL数据库实用指南：数据增删改查基本操作详解与示例

前言

本文主要讲解对数据库中表里的数据进行增删改查的相关操作。

可以关注我的云原生社区：云原生社区
也可以关注我的英语社区：从零开始学英语

一. 数据的插入操作

语法：insert into 表名values (字段值1,字段值2, 字段值3);

1.1 插入一条数据

创建张三丰的表：

CREATE TABLE zhang_san_feng (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  gender ENUM('男', '女') NOT NULL,
  birthdate DATE,
  style VARCHAR(50),
  description TEXT
);
1
2
3
4
5
6
7
8

注释：

• "style"字段：该字段用于存储张三丰的武学流派或风格，采用VARCHAR(50)数据类型。
• "description"字段：该字段用于存储张三丰的描述信息，采用TEXT数据类型，可容纳较长的文本。

插入数据测试：

INSERT INTO zhang_san_feng (name, gender, birthdate, style, description)
VALUES ('张三丰', '男', '1217-04-09', '太极拳', '张三丰是明朝末年著名武术家，被誉为武林宗师。');
1
2

MySQL [school]> select *from zhang_san_feng;
+----+-----------+--------+------------+-----------+--------------------------------------------------------------------+
| id | name      | gender | birthdate  | style     | description                                                        |
+----+-----------+--------+------------+-----------+--------------------------------------------------------------------+
|  1 | 张三丰    | 男     | 1217-04-09 | 太极拳    | 张三丰是明朝末年著名武术家，被誉为武林宗师。                       |
+----+-----------+--------+------------+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

1.2 插入多行数据

创建一个武术的表，并插入多行数据：

CREATE TABLE martial_arts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT,
  gender ENUM('男', '女'),
  skill VARCHAR(50),
  description TEXT
);
1
2
3
4
5
6
7
8

插入数据：

INSERT INTO martial_arts (name, age, gender, skill, description)
VALUES 
('小龙女', 18, '女', '玉女心经', '小龙女是郭靖妻子，身具绝世武功。'),
('杨过', 20, '男', '九阳真经', '杨过是小龙女的丈夫，精通九阳真经。'),
('金轮法王', 40, '男', '金轮寺武功', '金轮法王是佛教金轮寺的高僧，武功高强，擅使金轮。');
1
2
3
4
5

MySQL [school]> select * from martial_arts;
+----+--------------+------+--------+-----------------+--------------------------------------------------------------------------+
| id | name         | age  | gender | skill           | description                                                              |
+----+--------------+------+--------+-----------------+--------------------------------------------------------------------------+
|  1 | 小龙女       |   18 | 女     | 玉女心经        | 小龙女是郭靖妻子，身具绝世武功。                                         |
|  2 | 杨过         |   20 | 男     | 九阳真经        | 杨过是小龙女的丈夫，精通九阳真经。                                       |
|  3 | 金轮法王     |   40 | 男     | 金轮寺武功      | 金轮法王是佛教金轮寺的高僧，武功高强，擅使金轮。                         |
+----+--------------+------+--------+-----------------+--------------------------------------------------------------------------+
3 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10

二. 查询表中的记录

2.1 查询表中的全部记录

语法：select * from 表名称；

MySQL [school]> select * from martial_arts;
+----+--------------+------+--------+-----------------+--------------------------------------------------------------------------+
| id | name         | age  | gender | skill           | description                                                              |
+----+--------------+------+--------+-----------------+--------------------------------------------------------------------------+
|  1 | 小龙女       |   18 | 女     | 玉女心经        | 小龙女是郭靖妻子，身具绝世武功。                                         |
|  2 | 杨过         |   20 | 男     | 九阳真经        | 杨过是小龙女的丈夫，精通九阳真经。                                       |
|  3 | 金轮法王     |   40 | 男     | 金轮寺武功      | 金轮法王是佛教金轮寺的高僧，武功高强，擅使金轮。                         |
+----+--------------+------+--------+-----------------+--------------------------------------------------------------------------+

1
2
3
4
5
6
7
8
9

2.2 查询表中某个字段的内容

MySQL [school]> select name from martial_arts;
+--------------+
| name         |
+--------------+
| 小龙女       |
| 杨过         |
| 金轮法王     |
+--------------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10

MySQL [school]> select name,age from martial_arts;
+--------------+------+
| name         | age  |
+--------------+------+
| 小龙女       |   18 |
| 杨过         |   20 |
| 金轮法王     |   40 |
+--------------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9

2.3 查看别的数据库的表或者不在本数据库上进行查看

语法：SELECT 字段 FROM 数据库名.表名;
查看某个数据库下指定的表内容，数据库名.表名

MySQL [school]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [mysql]> select name,age from school.martial_arts;
+--------------+------+
| name         | age  |
+--------------+------+
| 小龙女       |   18 |
| 杨过         |   20 |
| 金轮法王     |   40 |
+--------------+------+
3 rows in set (0.00 sec)

MySQL [mysql]> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

三. 删除表中的记录

对于重要的数据，不能轻易执行delete语句进行删除，一旦删除，数据无法恢复，这时可以进行逻辑删除。

MySQL的逻辑删除是指将数据标记为已删除，但实际上并不从数据库中删除数据。这种删除方式通常会在数据表中添加一个列，例如is_deleted或者status，并将它的值设为1表示数据已删除。这种方式可以在需要时方便地恢复被删除的数据。

3.1 删除di为3的行

MySQL [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| martial_arts     |
| student          |
| zhang_san_feng   |
+------------------+
3 rows in set (0.00 sec)

MySQL [school]> delete from martial_arts where id=3;
Query OK, 1 row affected (0.00 sec)

MySQL [school]> select id,name from martial_arts;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 小龙女    |
|  2 | 杨过      |
+----+-----------+
2 rows in set (0.00 sec)

MySQL [school]> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3.2 删除技能为空的行

mysql> delete from martial_arts where skill is null;
1

3.3 更新记录

当不指定是哪一行的时候，每行都会修改

MySQL [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| martial_arts     |
| student          |
| zhang_san_feng   |
+------------------+
3 rows in set (0.00 sec)

MySQL [school]> update martial_arts set description="小龙女是杨过的妻子，古墓派武功";
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MySQL [school]> select * from martial_arts;
+----+-----------+------+--------+--------------+-----------------------------------------------+
| id | name      | age  | gender | skill        | description                                   |
+----+-----------+------+--------+--------------+-----------------------------------------------+
|  1 | 小龙女    |   18 | 女     | 玉女心经     | 小龙女是杨过的妻子，古墓派武功                |
|  2 | 杨过      |   20 | 男     | 九阳真经     | 小龙女是杨过的妻子，古墓派武功                |
+----+-----------+------+--------+--------------+-----------------------------------------------+
2 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

当指定是哪一行的时候，只修改被指定的行

MySQL [school]> update martial_arts set description="杨过是小龙女的丈夫，自创武功：黯然销魂掌" where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [school]> select * from martial_arts;
+----+-----------+------+--------+--------------+--------------------------------------------------------------+
| id | name      | age  | gender | skill        | description                                                  |
+----+-----------+------+--------+--------------+--------------------------------------------------------------+
|  1 | 小龙女    |   18 | 女     | 玉女心经     | 小龙女是杨过的妻子，古墓派武功                               |
|  2 | 杨过      |   20 | 男     | 九阳真经     | 杨过是小龙女的丈夫，自创武功：黯然销魂掌                     |
+----+-----------+------+--------+--------------+--------------------------------------------------------------+
2 rows in set (0.00 sec)

MySQL [school]> 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 如果要同时更新多个字段需要用,隔开：

update students set stname='zhangsan',age=21 where uid=1;
1

3.4 逻辑删除示例

MySQL并没有内置的逻辑删除功能，但可以通过添加一个表示删除状态的字段来实现逻辑删除。一般情况下，可以使用一个名为"deleted"的布尔类型字段，其默认值为0表示未删除，1表示已删除。

我们来演示下这个逻辑删除的过程：

1. 向表中添加一个名为"deleted"的布尔类型字段：

MySQL [school]> alter table martial_arts add   deleted boolean not null default 0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [school]> select * from martial_arts;
+----+-----------+------+--------+--------------+--------------------------------------------------------------+---------+
| id | name      | age  | gender | skill        | description                                                  | deleted |
+----+-----------+------+--------+--------------+--------------------------------------------------------------+---------+
|  1 | 小龙女    |   18 | 女     | 玉女心经     | 小龙女是杨过的妻子，古墓派武功                               |       0 |
|  2 | 杨过      |   20 | 男     | 九阳真经     | 杨过是小龙女的丈夫，自创武功：黯然销魂掌                     |       0 |
+----+-----------+------+--------+--------------+--------------------------------------------------------------+---------+
2 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14

注释： 向"martial_arts"表中添加了一个名为"deleted"的布尔类型字段。该字段表示记录的删除状态，默认值设置为0，即未删除。

然后，通过更新字段来标记逻辑删除。例如，要逻辑删除"小龙女"这条记录：

MySQL [school]> update martial_arts set deleted=1 where name="小龙女";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL [school]> select * from martial_arts;
+----+-----------+------+--------+--------------+--------------------------------------------------------------+---------+
| id | name      | age  | gender | skill        | description                                                  | deleted |
+----+-----------+------+--------+--------------+--------------------------------------------------------------+---------+
|  1 | 小龙女    |   18 | 女     | 玉女心经     | 小龙女是杨过的妻子，古墓派武功                               |       1 |
|  2 | 杨过      |   20 | 男     | 九阳真经     | 杨过是小龙女的丈夫，自创武功：黯然销魂掌                     |       0 |
+----+-----------+------+--------+--------------+--------------------------------------------------------------+---------+
2 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14

上述示例使用UPDATE语句将"deleted"字段设置为1来标记"小龙女"这条记录为逻辑删除状态。

在查询数据时，需要在WHERE子句中排除已被逻辑删除的记录。例如，要查询未被逻辑删除的记录：

MySQL [school]> select * from martial_arts where deleted = 0;
+----+--------+------+--------+--------------+--------------------------------------------------------------+---------+
| id | name   | age  | gender | skill        | description                                                  | deleted |
+----+--------+------+--------+--------------+--------------------------------------------------------------+---------+
|  2 | 杨过   |   20 | 男     | 九阳真经     | 杨过是小龙女的丈夫，自创武功：黯然销魂掌                     |       0 |
+----+--------+------+--------+--------------+--------------------------------------------------------------+---------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8

上述示例使用SELECT语句查询"martial_arts"表中未被逻辑删除的记录，即"deleted"字段的值为0的记录。
通过上述步骤，您可以实现在MySQL数据库中进行逻辑删除。当需要删除记录时，只需将"deleted"字段设置为1即可，而不需要物理删除数据。这样就可以保留数据的完整性，并在需要时进行恢复。

总结

本文主要讲解的数据库中对表中的数据进行增删改查的相关操作，你学会了吗。