【MySQL篇】：MySQL表的增删查改操作的基础语法与实用示例-EW帮帮网

✨感谢您阅读本篇文章，文章内容是个人学习笔记的整理，如果哪里有误的话还请您指正噢✨
✨ 个人主页：余辉zmh–CSDN博客
 ✨ 文章所属专栏：MySQL篇–CSDN博客

在这里插入图片描述

表的增删查改

一.Create 创建

1.基本语法

INSERT INTO 表名 (列名1, 列名2, 列名3, ...) 
VALUES (值1, 值2, 值3, ...);

2.常用示例

先创建示例学生表：

-- 创建表
mysql> CREATE TABLE students(
    -> id int unsigned PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(30) NOT NULL,
    -> age int unsigned,
    -> grade varchar(10)
    -> );
Query OK, 0 rows affected (0.04 sec)

-- 查看表结构
mysql> DESC students;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)  | NO   |     | NULL    |                |
| age   | int unsigned | YES  |     | NULL    |                |
| grade | varchar(10)  | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

单行数据+全列插入：

mysql> INSERT INTO students values (1, '张三', 18, '高一');
Query OK, 1 row affected (0.00 sec)    -- 全列插入时省略列名

mysql> INSERT INTO students values (2, '李四', 19, '高二');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO students (id, name, age, grade) values (3, '王五', 20, '高一');                       -- 显示列名插入
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM students;
+----+--------+------+--------+
| id | name   | age  | grade  |
+----+--------+------+--------+
|  1 | 张三   |   18 | 高一   |
|  2 | 李四   |   19 | 高二   |
|  3 | 王五   |   20 | 高一   |
+----+--------+------+--------+
3 rows in set (0.00 sec)

如果插入的数据包含表的所有列，可以省略列名；

多行数据+部分列插入：

mysql> INSERT INTO students (name, age, grade) values
    -> ('张飞', 17, '高一'),
    -> ('吕布', 20, '高三'),
    -> ('赵云', 19, '高二');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM students;
+----+--------+------+--------+
| id | name   | age  | grade  |
+----+--------+------+--------+
|  1 | 张三   |   18 | 高一   |
|  2 | 李四   |   19 | 高二   |
|  3 | 王五   |   20 | 高一   |
|  4 | 张飞   |   17 | 高一   |
|  5 | 吕布   |   20 | 高三   |
|  6 | 赵云   |   19 | 高二   |
+----+--------+------+--------+
6 rows in set (0.00 sec)

插入多行数据时，只需要用逗号隔开即可；部分列插入时不能省略列名，必须显示的指定所插入的列。

3.插入失败：更新/替换

如果主键或者唯一键冲突导致插入失败时，比如下面这种情况：

mysql> INSERT INTO students values (6, '刘备', 18, '高一');
ERROR 1062 (23000): Duplicate entry '6' for key 'students.PRIMARY'

还想要继续插入，就需要借助其他的处理方式了：更新或者替换；

1.更新数据：

这是最常用的方式，当遇到主键或者唯一键冲突时，会更新现有记录而不是插入新记录；

基本语法：

INSERT INTO 表名 (列名1, 列名2, ...) 
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE 
    列名1 = VALUES(列名1),
    列名2 = VALUES(列名2);

示例：

mysql> INSERT INTO students values (6, '刘备', 18, '高一')
    -> ON DUPLICATE KEY UPDATE
    -> name = values(name),     -- 等价于 name = '刘备'
    -> age = values(age),       -- 等价于 age = 18
    -> grade = values(grade);   -- 等价于 grage = '高一'
Query OK, 2 rows affected, 3 warnings (0.01 sec)

mysql> SELECT * FROM students;
+----+--------+------+--------+
| id | name   | age  | grade  |
+----+--------+------+--------+
|  1 | 张三   |   18 | 高一   |
|  2 | 李四   |   19 | 高二   |
|  3 | 王五   |   20 | 高一   |
|  4 | 张飞   |   17 | 高一   |
|  5 | 吕布   |   20 | 高三   |
|  6 | 刘备   |   18 | 高一   |
+----+--------+------+--------+
6 rows in set (0.00 sec)

更新数据时，输出的数字 rows affected含义：

= 0：表中有冲突数据，但冲突数据的值和update的值相等；

= 1：表中没有冲突数据，数据被插入；

= 2：表中有冲突数据，并且数据已经被更新；

2.替换数据：

这种方式会先删除冲突的记录，然后插入新记录；

基本语法：

只需要把关键字INSERT替换成REPLACE即可，使用方式还是一样的

REPLACE INTO 表名 (列名1, 列名2, ...) 
VALUES (值1, 值2, ...);

示例：

mysql> REPLACE INTO students values (6, '曹操', 20, '高三');
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM students;
+----+--------+------+--------+
| id | name   | age  | grade  |
+----+--------+------+--------+
|  1 | 张三   |   18 | 高一   |
|  2 | 李四   |   19 | 高二   |
|  3 | 王五   |   20 | 高一   |
|  4 | 张飞   |   17 | 高一   |
|  5 | 吕布   |   20 | 高三   |
|  6 | 曹操   |   20 | 高三   |
+----+--------+------+--------+
6 rows in set (0.00 sec)

替换数据时，输出的数字 rows affected含义：

= 1：表中没有冲突数据，数据被插入；

= 2：表中有冲突数据，删除后重新插入；

两种方式的区别：

更新数据：

只更新指定的列；

保留其他列的原值；

更灵活，可以只更新部分字段；

替换数据：

删除原记录，插入新记录；

如果某些列没有指定值，会设置为NULL或默认值；

相当于先DELETE再INSERT；

4.插入的注意事项

数据类型匹配：
- 字符串用单引号或双引号包围；
- 数字直接写，不需要引号；
- 日期格式：‘YYYY-MM-DD’；
列名和值对应：
- 列名的顺序要和值的顺序一致；
- 值的数量要和列名的数量一致；
特殊值：
- NULL - 表示空值；
- DEFAULT - 使用默认值；

二.Retrieve 读取

1.SELECT基本查询

基本语法：

SELECT 列名1, 列名2, ... FROM 表名;
SELECT * FROM 表名;  -- 读取所有列

常用示例：

先创建一个示例成绩表

-- 创建表
mysql> CREATE TABLE exam_result(
    -> id int unsigned PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(30) NOT NULL,
    -> chinese float DEFAULT 0.0,
    -> math float DEFAULT 0.0,
    -> english float DEFAULT 0.0
    -> );
Query OK, 0 rows affected (0.04 sec)

-- 查看表结构
mysql> DESC exam_result;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30)  | NO   |     | NULL    |                |
| chinese | float        | YES  |     | 0       |                |
| math    | float        | YES  |     | 0       |                |
| english | float        | YES  |     | 0       |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

-- 插入数据用于测试
INSERT INTO exam_result (name, chinese, math, english) VALUES 
('张三', 85.5, 92.0, 78.5),
('李四', 92.0, 88.5, 85.0),
('王五', 78.0, 95.5, 90.0),
('赵六', 88.5, 76.0, 82.5),
('钱七', 95.0, 89.0, 87.5),
('孙八', 82.0, 91.5, 94.0),
('周九', 90.5, 85.0, 88.0),
('吴十', 76.5, 93.0, 91.5),
('郑十一', 87.0, 79.5, 85.5),
('王十二', 93.5, 87.0, 89.0);

示例一：全列查询

mysql> SELECT * FROM exam_result;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  1 | 张三      |    85.5 |   92 |    78.5 |
|  2 | 李四      |      92 | 88.5 |      85 |
|  3 | 王五      |      78 | 95.5 |      90 |
|  4 | 赵六      |    88.5 |   76 |    82.5 |
|  5 | 钱七      |      95 |   89 |    87.5 |
|  6 | 孙八      |      82 | 91.5 |      94 |
|  7 | 周九      |    90.5 |   85 |      88 |
|  8 | 吴十      |    76.5 |   93 |    91.5 |
|  9 | 郑十一    |      87 | 79.5 |    85.5 |
| 10 | 王十二    |    93.5 |   87 |      89 |
+----+-----------+---------+------+---------+
10 rows in set (0.00 sec)

示例二：指定列查询

-- 查询所有学生的数学成绩
mysql> SELECT name, math FROM exam_result;
+-----------+------+
| name      | math |
+-----------+------+
| 张三      |   92 |
| 李四      | 88.5 |
| 王五      | 95.5 |
| 赵六      |   76 |
| 钱七      |   89 |
| 孙八      | 91.5 |
| 周九      |   85 |
| 吴十      |   93 |
| 郑十一    | 79.5 |
| 王十二    |   87 |
+-----------+------+
10 rows in set (0.01 sec)

示例三：查询字段为表达式

-- 查询所有学生英语成绩+10后的结果
mysql> SELECT name, english + 10 FROM exam_result;
+-----------+--------------+
| name      | english + 10 |
+-----------+--------------+
| 张三      |         88.5 |
| 李四      |           95 |
| 王五      |          100 |
| 赵六      |         92.5 |
| 钱七      |         97.5 |
| 孙八      |          104 |
| 周九      |           98 |
| 吴十      |        101.5 |
| 郑十一    |         95.5 |
| 王十二    |           99 |
+-----------+--------------+
10 rows in set (0.00 sec)

-- 查询所有学生的总成绩
mysql> SELECT name, chinese + math +english FROM exam_result;
+-----------+-------------------------+
| name      | chinese + math +english |
+-----------+-------------------------+
| 张三      |                     256 |
| 李四      |                   265.5 |
| 王五      |                   263.5 |
| 赵六      |                     247 |
| 钱七      |                   271.5 |
| 孙八      |                   267.5 |
| 周九      |                   263.5 |
| 吴十      |                     261 |
| 郑十一    |                     252 |
| 王十二    |                   269.5 |
+-----------+-------------------------+
10 rows in set (0.00 sec)

示例四：查询结果指定别名

-- 查询所有学生的总成绩total
mysql> SELECT name, chinese + math +english as total FROM exam_result;              -- 注意取别名时as可以省略
+-----------+-------+
| name      | total |
+-----------+-------+
| 张三      |   256 |
| 李四      | 265.5 |
| 王五      | 263.5 |
| 赵六      |   247 |
| 钱七      | 271.5 |
| 孙八      | 267.5 |
| 周九      | 263.5 |
| 吴十      |   261 |
| 郑十一    |   252 |
| 王十二    | 269.5 |
+-----------+-------+
10 rows in set (0.00 sec)

示例五：查询结果去重

-- 对总成绩去重
mysql> SELECT DISTINCT chinese + math +english as total FROM exam_result;
+-------+
| total |
+-------+
|   256 |
| 265.5 |
| 263.5 |
|   247 |
| 271.5 |
| 267.5 |
|   261 |
|   252 |
| 269.5 |
+-------+
9 rows in set (0.00 sec)

注意：在MySQL中，DISTINCT 关键字必须放在 SELECT 之后，列名之前，不能放在中间。

2.WHERE条件

比较运算符：

运算符
`>, >=, <, <=`	大于，大于等于，小于，小于等于
`=`	等于，对NULL不安全，例如NULL=NULL结果是NULL
`<=>`	等于，对NULL安全，例如NULL=NULL结果是TRUE
`!=, <>`	不等于
`BETWEEN a0 AND a1`	范围匹配，[a0, a1]，如果a1 <= value <= a1，返回TRUE
`IN (option, ...)`	如果是option中的任意一个，返回TRUE
`IS NULL`	是NULL
`IS NOT NULL`	不是NULL
`LIKE`	模糊匹配，%表示任意多个（包括0个）任意字符；_表示任意一个字符

逻辑运算符：

运算符	说明
`AND`	多个条件必须都为`TRUE`，结果才是`TRUE`
`OR`	任意一个条件都为`TRUE`，结果才是`TRUE`
`NOT`	条件为`TRUE`，结果为`FALSE`

示例：

这里还继续使用上面创建的那个学生成绩表

示例一：数学成绩<80的同学

mysql> SELECT name, math FROM exam_result where math < 80;
+-----------+------+
| name      | math |
+-----------+------+
| 赵六      |   76 |
| 郑十一    | 79.5 |
+-----------+------+
2 rows in set (0.00 sec)

示例二：英语成绩在[70,90]分的同学以及数学成绩>90

-- 使用 AND 进行条件连接
mysql> SELECT name, math, english FROM exam_result where 
    -> english >= 70
    -> and english <= 90
    -> and math >90;
+--------+------+---------+
| name   | math | english |
+--------+------+---------+
| 张三   |   92 |    78.5 |
| 王五   | 95.5 |      90 |
+--------+------+---------+
2 rows in set (0.00 sec)

-- 使用 BETWEEN ... AND ... 条件
mysql> SELECT name, math, english FROM exam_result where
    -> english between 70 AND 90
    -> and math > 90;
+--------+------+---------+
| name   | math | english |
+--------+------+---------+
| 张三   |   92 |    78.5 |
| 王五   | 95.5 |      90 |
+--------+------+---------+
2 rows in set (0.00 sec)

示例三：英语成绩是80或者85或者90或者95分的同学

-- 使用 OR 进行条件连接
mysql> SELECT name, english FROM exam_result where 
    -> english = 80
    -> OR english = 85
    -> OR english = 90
    -> OR english = 95;
+-----------+---------+
| name      | english |
+-----------+---------+
| 李四      |      85 |
| 王五      |      90 |
| 孙悟空    |      80 |
+-----------+---------+
3 rows in set (0.00 sec)

-- 使用 IN 条件
mysql> SELECT name, english FROM exam_result where
    -> english IN(80, 85, 90, 95);
+-----------+---------+
| name      | english |
+-----------+---------+
| 李四      |      85 |
| 王五      |      90 |
| 孙悟空    |      80 |
+-----------+---------+
3 rows in set (0.00 sec)

示例四：姓孙的同学和孙某同学

-- 再插入一个姓孙的作比较
INSERT INTO exam_result values (11, '孙悟空', 80, 90, 80);

-- % 匹配任意多个（包括 0 个）任意字符
mysql> SELECT name FROM exam_result WHERE name LIKE '孙%';
+-----------+
| name      |
+-----------+
| 孙八      |
| 孙悟空    |
+-----------+
2 rows in set (0.00 sec)

-- _ 匹配严格的一个任意字符
mysql> SELECT name FROM exam_result WHERE name LIKE '孙_';
+--------+
| name   |
+--------+
| 孙八   |
+--------+
1 row in set (0.00 sec)

示例五：语文成绩比英语成绩好的同学

-- WHERE 条件中比较运算符两侧都是字段
mysql> SELECT name, chinese, english FROM exam_result where chinese > english;
+-----------+---------+---------+
| name      | chinese | english |
+-----------+---------+---------+
| 张三      |    85.5 |    78.5 |
| 李四      |      92 |      85 |
| 赵六      |    88.5 |    82.5 |
| 钱七      |      95 |    87.5 |
| 周九      |    90.5 |      88 |
| 郑十一    |      87 |    85.5 |
| 王十二    |    93.5 |      89 |
+-----------+---------+---------+
7 rows in set (0.00 sec)

示例六：总成绩在260分以下的同学

mysql> SELECT name, chinese+math+english as total FROM exam_result where chinese+math+english < 260
;
+-----------+-------+
| name      | total |
+-----------+-------+
| 张三      |   256 |
| 赵六      |   247 |
| 郑十一    |   252 |
| 孙悟空    |   250 |
+-----------+-------+
4 rows in set (0.00 sec)

-- 错误写法
mysql> SELECT name, chinese+math+english as total FROM exam_result where total < 260;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'

为什么这里会报错？这是因为执行顺序的不同：

执行过程：

从 exam_result 表获取数据；

WHERE 子句在 SELECT 之前执行，此时别名 total 还不存在；

所以必须使用原始表达式 chinese+math+english 而不是别名 total；

WHERE 是行级过滤，需要在选择列之前完成；

示例七：数学成绩>80分并且不姓孙的同学

mysql> SELECT name, math FROM exam_result where
    -> math > 80
    -> and name NOT LIKE '孙%';
+-----------+------+
| name      | math |
+-----------+------+
| 张三      |   92 |
| 李四      | 88.5 |
| 王五      | 95.5 |
| 钱七      |   89 |
| 周九      |   85 |
| 吴十      |   93 |
| 王十二    |   87 |
+-----------+------+
7 rows in set (0.00 sec)

示例八：孙某同学，否则总成绩>250并且语文成绩<英语成绩并且数学成绩>80的同学

mysql> SELECT name, chinese, math, english, chinese+math+english as total FROM exam_result where
    -> (name LIKE '孙%')
    -> OR
    -> (chinese+math+english > 250
    -> AND chinese < english
    -> AND math > 80);
+-----------+---------+------+---------+-------+
| name      | chinese | math | english | total |
+-----------+---------+------+---------+-------+
| 王五      |      78 | 95.5 |      90 | 263.5 |
| 孙八      |      82 | 91.5 |      94 | 267.5 |
| 吴十      |    76.5 |   93 |    91.5 |   261 |
| 孙悟空    |      80 |   90 |      80 |   250 |
+-----------+---------+------+---------+-------+
4 rows in set (0.00 sec)

示例九：NULL的查询

-- 插入空数据
mysql> INSERT INTO exam_result (name, chinese, math, english) values('猪八戒', NULL, NULL, NULL);
Query OK, 1 row affected (0.01 sec)

-- 查询数学成绩为空的同学
mysql> SELECT name, math FROM exam_result where math is NULL;
+-----------+------+
| name      | math |
+-----------+------+
| 猪八戒    | NULL |
+-----------+------+
1 row in set (0.00 sec)

-- NULL 和 NULL 的比较，= 和 <=> 的区别
mysql> SELECT NULL = NULL, NULL = 1, NULL = 0;
+-------------+----------+----------+
| NULL = NULL | NULL = 1 | NULL = 0 |
+-------------+----------+----------+
|        NULL |     NULL |     NULL |
+-------------+----------+----------+
1 row in set (0.00 sec)

mysql> SELECT NULL <=> NULL, NULL <=> 1, NULL <=> 0;
+---------------+------------+------------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> 0 |
+---------------+------------+------------+
|             1 |          0 |          0 |
+---------------+------------+------------+
1 row in set (0.00 sec)

3.结果排序

基本语法：

-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为ASC升序

SELECT ... FROM table_name [where ...] ORDER BY 列名 [ASC/DESC]...

示例：

示例一：数学成绩按升序显示

mysql> SELECT name, math FROM exam_result ORDER BY math ASC;
+-----------+------+
| name      | math |
+-----------+------+
| 猪八戒    | NULL |
| 赵六      |   76 |
| 郑十一    | 79.5 |
| 周九      |   85 |
| 王十二    |   87 |
| 李四      | 88.5 |
| 钱七      |   89 |
| 孙悟空    |   90 |
| 孙八      | 91.5 |
| 张三      |   92 |
| 吴十      |   93 |
| 王五      | 95.5 |
+-----------+------+
12 rows in set (0.00 sec)

示例二：查询各门成绩，依次按照数学降序，英语升序，语文升序的方式显示

mysql> SELECT name, math, english, chinese FROM exam_result
    -> ORDER BY math DESC,
    -> english ASC,
    -> chinese ASC;
+-----------+------+---------+---------+
| name      | math | english | chinese |
+-----------+------+---------+---------+
| 王五      | 95.5 |      90 |      78 |
| 吴十      |   93 |    91.5 |    76.5 |
| 张三      |   92 |    78.5 |    85.5 |
| 孙八      | 91.5 |      94 |      82 |
| 孙悟空    |   90 |      80 |      80 |
| 钱七      |   89 |    87.5 |      95 |
| 李四      | 88.5 |      85 |      92 |
| 王十二    |   87 |      89 |    93.5 |
| 周九      |   85 |      88 |    90.5 |
| 郑十一    | 79.5 |    85.5 |      87 |
| 赵六      |   76 |    82.5 |    88.5 |
| 猪八戒    | NULL |    NULL |    NULL |
+-----------+------+---------+---------+
12 rows in set (0.00 sec)

示例三：查询同学及总分，由高到低

mysql> SELECT name, chinese+math+english as total FROM exam_result
    -> ORDER BY total DESC;
+-----------+-------+
| name      | total |
+-----------+-------+
| 钱七      | 271.5 |
| 王十二    | 269.5 |
| 孙八      | 267.5 |
| 李四      | 265.5 |
| 王五      | 263.5 |
| 周九      | 263.5 |
| 吴十      |   261 |
| 张三      |   256 |
| 郑十一    |   252 |
| 孙悟空    |   250 |
| 赵六      |   247 |
| 猪八戒    |  NULL |
+-----------+-------+
12 rows in set (0.00 sec)

注意：这里和WHERE不同的是，ORDER BY这里可以使用别名，这是因为：

执行过程：

从 exam_result 表获取数据；

SELECT 子句执行，创建别名 total；

ORDER BY 在 SELECT 之后执行，此时别名 total 已经存在；

ORDER BY 是对最终结果集的排序，在列选择之后执行，所以可以使用别名 total；

示例四：查询姓孙的同学或者姓张的同学的数学成绩，结构按照数学成绩由高到低显示

mysql> SELECT name, math FROM exam_result where 
    -> name LIKE '孙%'
    -> OR name LIKE '张%'
    -> ORDER BY math DESC;
+-----------+------+
| name      | math |
+-----------+------+
| 张三      |   92 |
| 孙八      | 91.5 |
| 孙悟空    |   90 |
+-----------+------+
3 rows in set (0.00 sec)

4.筛选分页结果

基本语法：

-- 起始下标为0
-- 从0开始，筛选n条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从s开始，筛选n条结果(第一种写法)
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从s开始，筛选n条结果（第二种写法，比第一种更明确，建议使用）
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

示例：

按id进行分页，每页三条记录，按页分别显示

-- 第一页
mysql> SELECT id, name, chinese, math, english FROM exam_result
    -> ORDER BY id ASC
    -> LIMIT 3 OFFSET 0;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  1 | 张三   |    85.5 |   92 |    78.5 |
|  2 | 李四   |      92 | 88.5 |      85 |
|  3 | 王五   |      78 | 95.5 |      90 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
-- 第二页
mysql> SELECT id, name, chinese, math, english FROM exam_result
    -> ORDER BY id ASC
    -> LIMIT 3 OFFSET 3;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
|  4 | 赵六   |    88.5 |   76 |    82.5 |
|  5 | 钱七   |      95 |   89 |    87.5 |
|  6 | 孙八   |      82 | 91.5 |      94 |
+----+--------+---------+------+---------+
3 rows in set (0.00 sec)
-- 第三页
mysql> SELECT id, name, chinese, math, english FROM exam_result
    -> ORDER BY id ASC
    -> LIMIT 3 OFFSET 6;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
|  7 | 周九      |    90.5 |   85 |      88 |
|  8 | 吴十      |    76.5 |   93 |    91.5 |
|  9 | 郑十一    |      87 | 79.5 |    85.5 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
-- 第四页
mysql> SELECT id, name, chinese, math, english FROM exam_result
    -> ORDER BY id ASC
    -> LIMIT 3 OFFSET 9;
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
| 10 | 王十二    |    93.5 |   87 |      89 |
| 11 | 孙悟空    |      80 |   90 |      80 |
| 12 | 猪八戒    |    NULL | NULL |    NULL |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)

三.Update 更新

1.基本语法

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2 [WHERE ...] [ORDER BY ...] [LIMIT ...];

对查询到的结果进行列值更新；

2.常用示例

示例一：将张三同学的数学成绩更改为80分

-- 查看更改前的数据
mysql> SELECT name, math FROM exam_result WHERE name = '张三';
+--------+------+
| name   | math |
+--------+------+
| 张三   |   92 |
+--------+------+
1 row in set (0.00 sec)

-- 更改
mysql> UPDATE exam_result SET math = 80 WHERE name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看更改后的数据
mysql> SELECT name, math FROM exam_result WHERE name = '张三';
+--------+------+
| name   | math |
+--------+------+
| 张三   |   80 |
+--------+------+
1 row in set (0.00 sec)

示例二：将猪八戒的语文，数学和英语成绩都更改为90分

-- 查看更改前的数据
mysql> SELECT name, chinese, math, english FROM exam_result WHERE name = '猪八戒';
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 猪八戒    |    NULL | NULL |    NULL |
+-----------+---------+------+---------+
1 row in set (0.00 sec)

-- 更改
mysql> UPDATE exam_result SET chinese = 90, math = 90, english = 90 WHERE name = '猪八戒';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查看更改后的数据
mysql> SELECT name, chinese, math, english FROM exam_result WHERE name = '猪八戒';
+-----------+---------+------+---------+
| name      | chinese | math | english |
+-----------+---------+------+---------+
| 猪八戒    |      90 |   90 |      90 |
+-----------+---------+------+---------+
1 row in set (0.00 sec)

示例三：将总成绩倒数后三个的数学成绩加上10分

-- 查看倒数后三个
mysql> SELECT name, math, chinese+math+english as total FROM exam_result
    -> ORDER BY total ASC
    -> LIMIT 3;
+-----------+------+-------+
| name      | math | total |
+-----------+------+-------+
| 张三      |   80 |   244 |
| 赵六      |   76 |   247 |
| 孙悟空    |   90 |   250 |
+-----------+------+-------+
3 rows in set (0.00 sec)

-- 更改
mysql> UPDATE exam_result SET math = math + 10 ORDER BY chinese+math+english ASC LIMIT 3;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

-- 查看更改后的所有数据
mysql> SELECT name, math, chinese+math+english as total FROM exam_result
    -> ORDER BY total ASC
    -> ;
+-----------+------+-------+
| name      | math | total |
+-----------+------+-------+
| 郑十一    | 79.5 |   252 |
| 张三      |   90 |   254 |
| 赵六      |   86 |   257 |
| 孙悟空    |  100 |   260 |
| 吴十      |   93 |   261 |
| 王五      | 95.5 | 263.5 |
| 周九      |   85 | 263.5 |
| 李四      | 88.5 | 265.5 |
| 孙八      | 91.5 | 267.5 |
| 王十二    |   87 | 269.5 |
| 猪八戒    |   90 |   270 |
| 钱七      |   89 | 271.5 |
+-----------+------+-------+
12 rows in set (0.00 sec)

3.更新的注意事项

WHERE子句很重要：没有WHERE条件会更新所有行；

可以同时更新多个列；

可以使用表达式：SET math = math + 10，但是不允许使用 SET math += 10；

可以结合ORDER BY和LIMIT进行限制性更新

四.Delete 删除

1.基本语法

DELETE FROM 表名 [WHERE ..] [ORDER BY ...] [LIMIT ...];

2.常用示例

示例一：删除孙悟空同学的所有成绩

-- 删除前查看
mysql> SELECT * FROM exam_result WHERE name = '孙悟空';
+----+-----------+---------+------+---------+
| id | name      | chinese | math | english |
+----+-----------+---------+------+---------+
| 11 | 孙悟空    |      80 |  100 |      80 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)

-- 删除
mysql> DELETE FROM exam_result WHERE name = '孙悟空';
Query OK, 1 row affected (0.01 sec)

-- 删除后查看
mysql> SELECT * FROM exam_result WHERE name = '孙悟空';
Empty set (0.00 sec)

示例二：删除整张表（慎用）

-- 创建一个测试表
mysql> CREATE TABLE test_delete(
    -> a int,
    -> b int);
Query OK, 0 rows affected (0.04 sec)

-- 插入数据
mysql> INSERT INTO test_delete values(1,1), (2,2), (3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查看插入的数据
mysql> SELECT * FROM test_delete;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
+------+------+
3 rows in set (0.00 sec)

-- 删除整张测试表
mysql> DELETE FROM test_delete;
Query OK, 3 rows affected (0.00 sec)

-- 查看删除后的表
mysql> SELECT * FROM test_delete;
Empty set (0.00 sec)

3.截断表

基本语法：

TRUNCATE [TABLE] table_name;

注意：这个操作慎用

只能对整表操作，不能像DELETE一样针对部分数据操作；

实际上MySQL不对数据操作，所以比DELETE更快，但是TRUNCATE在删除数据的时候，并不经过正真的事物，所以无法回滚；

会重置AUTO_INCREMENT项；

示例：

准备工作

-- 创建一个测试表
mysql> CREATE TABLE test_truncate(
    -> id int unsigned PRIMARY KEY AUTO_INCREMENT,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.04 sec)

-- 插入数据
mysql> INSERT INTO test_truncate (name) values ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查看插入结果
mysql> SELECT * FROM test_truncate;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.01 sec)

-- 查看表 其中AUTO_INCREMENT=4 表示下一个自增长值是4
mysql> SHOW CREATE TABLE test_truncate \G;
*************************** 1. row ***************************
       Table: test_truncate
Create Table: CREATE TABLE `test_truncate` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

开始测试：

-- 截断表 注意影响行数是 0，所以实际上没有对数据真正操作
mysql> TRUNCATE test_truncate;
Query OK, 0 rows affected (0.06 sec)

-- 查看表中的数据
mysql> SELECT * FROM test_truncate;
Empty set (0.00 sec)

-- 重新插入一条数据 id从1开始
mysql> INSERT INTO test_truncate (name) values ('D');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM test_truncate;
+----+------+
| id | name |
+----+------+
|  1 | D    |
+----+------+
1 row in set (0.00 sec)

-- 再次查看表发现 AUTO_INCREMENT=2 表示下一个自增长值从2开始 也就是说在截断表之后，这个自增值发生了重置
mysql> SHOW CREATE TABLE test_truncate \G;
*************************** 1. row ***************************
       Table: test_truncate
Create Table: CREATE TABLE `test_truncate` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

4.删除的注意事项

同样需要WHERE条件，否则会删除所有数据；

删除操作不可逆，建议先SELECT确认要删除的数据；

扩展内容

插入查询结果

基本语法：

INSERT INTO new_table_name SELECT DISTINCt * FROM old_table_name

示例：

删除表中的重复数据，重复数据只能有一份；

准备工作：

-- 创建测试表
mysql> CREATE TABLE test(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.03 sec)

-- 插入测试数据
mysql> INSERT INTO test values
    -> (100, 'A'),
    -> (100, 'A'),
    -> (200, 'B'),
    -> (200, 'B'),
    -> (300, 'C'),
    -> (300, 'C');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

-- 查看
mysql> SELECT * FROM test;
+------+------+
| id   | name |
+------+------+
|  100 | A    |
|  100 | A    |
|  200 | B    |
|  200 | B    |
|  300 | C    |
|  300 | C    |
+------+------+
6 rows in set (0.00 sec)

开始测试：

-- 按照test表创建一个相同结构的备份表
mysql> CREATE TABLE no_test LIKE test;
Query OK, 0 rows affected (0.04 sec)

-- 将去重后的数据插入到备份表中
mysql> INSERT INTO no_test SELECT DISTINCt * FROM test;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 对两个表分别重命名 原表变为旧表 备份表变为原表
mysql> RENAME TABLE test TO old_test, no_test TO test;
Query OK, 0 rows affected (0.05 sec)

-- 查看旧表 数据没有去重
mysql> SELECT * FROM old_test;
+------+------+
| id   | name |
+------+------+
|  100 | A    |
|  100 | A    |
|  200 | B    |
|  200 | B    |
|  300 | C    |
|  300 | C    |
+------+------+
6 rows in set (0.00 sec)

-- 查看新的原表 数据去重
mysql> SELECT * FROM test;
+------+------+
| id   | name |
+------+------+
|  100 | A    |
|  200 | B    |
|  300 | C    |
+------+------+
3 rows in set (0.00 sec)

聚合统计

相关的常用函数：

函数	说明
`COUNT([DISTINCT] expr)`	返回查询到的数据的数量
`SUM([DISTINCT] expr)`	返回查询到的数据的综合，不是数字没有意义
`AVG([DISTINCT] expr)`	返回查询到的数据的平均值，不是数字没有意义
`MAX([DISTINCT] expr)`	返回查询到的数据的最大值，不是数字没有意义
`MIN([DISTINCT] expr)`	返回查询到的数据的最小值，不是数字没有意义

示例：

准备工作：

-- 在上面那个学生测试表中重新插入一些数据

mysql> SELECT * FROM exam_result;
+----+--------+---------+------+---------+
| id | name   | chinese | math | english |
+----+--------+---------+------+---------+
| 13 | 张三   |    85.5 |   92 |    78.5 |
| 14 | 李四   |      92 | 88.5 |      85 |
| 15 | 王五   |    NULL |   95 |      82 |
| 16 | 赵六   |      78 |   78 |    90.5 |
| 17 | 钱七   |    85.5 |   92 |    78.5 |
| 18 | 孙八   |      88 | NULL |      85 |
| 19 | 周九   |      92 | 88.5 |      85 |
+----+--------+---------+------+---------+
7 rows in set (0.00 sec)

示例一：统计班级共有多少个学生

-- 使用*做统计 不受NULL影响
mysql> SELECT COUNT(*) FROM exam_result;
+----------+
| COUNT(*) |
+----------+
|        7 |
+----------+
1 row in set (0.01 sec)

-- 使用表达式做统计
mysql> SELECT COUNT(1) FROM exam_result;
+----------+
| COUNT(1) |
+----------+
|        7 |
+----------+
1 row in set (0.01 sec)

示例二：统计数学成绩分数个数：

-- NULL不被统计
mysql> SELECT COUNT(math) FROM exam_result;
+-------------+
| COUNT(math) |
+-------------+
|           6 |
+-------------+
1 row in set (0.01 sec)

-- 统计去重后的
mysql> SELECT COUNT(DISTINCT math) FROM exam_result;
+----------------------+
| COUNT(DISTINCT math) |
+----------------------+
|                    4 |
+----------------------+
1 row in set (0.00 sec)

示例三：统计平均总分

mysql> SELECT AVG(chinese+math+english) avg_total FROM exam_result;
+-----------+
| avg_total |
+-----------+
|     257.9 |
+-----------+
1 row in set (0.00 sec)

示例四：返回英语最高分

mysql> SELECT MAX(english) FROM exam_result;
+--------------+
| MAX(english) |
+--------------+
|         90.5 |
+--------------+
1 row in set (0.00 sec)

示例五：返回>70分以上的数学最低分

mysql> SELECT MIN(math) FROM exam_result WHERE math > 70;
+-----------+
| MIN(math) |
+-----------+
|        78 |
+-----------+
1 row in set (0.00 sec)

group by分组查询

基本语法：

SELECT 列名1, 列名2... FROM table_name group by 列名;

示例：

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

包含emp员工表：

在这里插入图片描述

dept部门表：

在这里插入图片描述

salgrade工资表：

在这里插入图片描述

示例一：按部门统计员工个数以及平均工资

mysql> SELECT deptno, COUNT(*) as '员工个数', AVG(sal) as '平均工资' FROM emp GROUP BY deptno;
+--------+--------------+--------------+
| deptno | 员工个数     | 平均工资     |
+--------+--------------+--------------+
|     10 |            3 |  2916.666667 |
|     20 |            5 |  2175.000000 |
|     30 |            6 |  1566.666667 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)

示例二：按职位统计员工个数以及平均工资

mysql> SELECT job, COUNT(*) as '员工个数', AVG(sal) as '平均工资' FROM emp GROUP BY job;
+-----------+--------------+--------------+
| job       | 员工个数     | 平均工资     |
+-----------+--------------+--------------+
| CLERK     |            4 |  1037.500000 |
| SALESMAN  |            4 |  1400.000000 |
| MANAGER   |            3 |  2758.333333 |
| ANALYST   |            2 |  3000.000000 |
| PRESIDENT |            1 |  5000.000000 |
+-----------+--------------+--------------+
5 rows in set (0.00 sec)

示例三：按部门统计不同职位的员工个数以及平均工资

mysql> SELECT deptno, job, COUNT(*) as '员工个数', AVG(sal) as '平均工资' FROM emp GROUP BY deptno, job ORDER BY deptno;
+--------+-----------+--------------+--------------+
| deptno | job       | 员工个数     | 平均工资     |
+--------+-----------+--------------+--------------+
|     10 | CLERK     |            1 |  1300.000000 |
|     10 | MANAGER   |            1 |  2450.000000 |
|     10 | PRESIDENT |            1 |  5000.000000 |
|     20 | ANALYST   |            2 |  3000.000000 |
|     20 | CLERK     |            2 |   950.000000 |
|     20 | MANAGER   |            1 |  2975.000000 |
|     30 | CLERK     |            1 |   950.000000 |
|     30 | MANAGER   |            1 |  2850.000000 |
|     30 | SALESMAN  |            4 |  1400.000000 |
+--------+-----------+--------------+--------------+
9 rows in set (0.00 sec)

示例四：统计每个部门的工资总额

mysql> SELECT deptno, SUM(sal) as '工资总额' FROM emp GROUP BY deptno;
+--------+--------------+
| deptno | 工资总额     |
+--------+--------------+
|     10 |      8750.00 |
|     20 |     10875.00 |
|     30 |      9400.00 |
+--------+--------------+
3 rows in set (0.00 sec)

示例五：按入职年份统计员工个数

mysql> SELECT YEAR(hiredate) as '入职年份', COUNT(*) as '员工个数' FROM emp GROUP BY YEAR(hiredate);
+--------------+--------------+
| 入职年份     | 员工个数     |
+--------------+--------------+
|         1980 |            1 |
|         1981 |           10 |
|         1987 |            2 |
|         1982 |            1 |
+--------------+--------------+
4 rows in set (0.00 sec)

示例六：查找平均工资低于2000的部门

mysql> SELECT deptno, AVG(sal) as avg_sal FROM emp GROUP BY deptno HAVING avg_sal < 2000;
+--------+-------------+
| deptno | avg_sal     |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

注意：这里的HAVING也是用来筛选，但是和WHERE有本质的区别：

WHERE vs HAVING 的区别

1. 执行顺序不同

SQL的执行顺序是：

FROM - 确定数据源

WHERE - 过滤行（在分组前）

GROUP BY - 分组

HAVING - 过滤分组（在分组后）

SELECT - 选择列

DISTINCT - 去重

ORDER BY - 排序

LIMIT - 分页

2. 使用场景不同

WHERE：过滤原始数据行

在 GROUP BY 之前执行

只能使用表中的列名

不能使用聚合函数

HAVING：过滤分组结果

在 GROUP BY 之后执行

可以使用聚合函数

可以使用 SELECT 中的别名

以上就是关于MySQL表的增删查改的基本讲解，如果哪里有错的话，可以在评论区指正，也欢迎大家一起讨论学习，如果对你的学习有帮助的话，点点赞关注支持一下吧！！！

【MySQL篇】：MySQL表的增删查改操作的基础语法与实用示例

文章目录

表的增删查改

一.Create 创建

1.基本语法

2.常用示例

3.插入失败：更新/替换

4.插入的注意事项

二.Retrieve 读取

1.SELECT基本查询

2.WHERE条件

3.结果排序

4.筛选分页结果

三.Update 更新

1.基本语法

2.常用示例

3.更新的注意事项

四.Delete 删除

1.基本语法

2.常用示例

3.截断表

4.删除的注意事项

扩展内容

插入查询结果

聚合统计

group by分组查询

网站公告

今日签到

热门文章

最新发布