目录
🌈前言🌈
📁 创建Creator
📂 插入数据
📂 插入否则更新
📂 替换
📁 读取Retrieve
📂 select列
📂 where条件
📂 结果排序
📂 筛选分页结果
📁 删除Delete
📂 删除指定行
📂 删除整张表
📂 截断表
📁 更新Update
📁 插入查询结果
📁 聚合函数
📁 group by子句的使用
📁 总结
🌈前言🌈
本期【MySQL】，主要讲解关于表的内容的操作，包含了如何在表中插入数据，查找数据，删除以及更新表的内容，此外在查找数据时，还会介绍聚合函数以及group by子句的使用。
关于表结构的基本操作，在下面这篇文章中会有介绍：
【MySQL】数据库和表的操作-CSDN博客
📁 创建Creator
在MySQL中使用insert into(可省略)来插入一行或者多行数据。
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
table_name: 表名
column_: 列名
value_: 具体数值
📂 插入数据
如果我们想要插入全部的列，即全列插入，那么column_是可以省略的；指定列插入则必须写明要插入的列有哪些。
values后面可以是插入的单行数据，也可以是多行数据。
mysql> create table student(
-> id int(10) primary key auto_increment,
-> name varchar(20),
-> age int(10));
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
我们首先创建一个学生的表格，接下来插入数据。
//全列 + 单行 插入
mysql> insert into student value (1,'刘备',18);
Query OK, 1 row affected (0.01 sec)
//全列 + 多行插入
mysql> insert into student value (2,'关羽',18),(3,'张飞',18);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
//指定列 + 单行插入
mysql> insert into student (name,age) value ('曹操',19);
Query OK, 1 row affected (0.00 sec)
//指定列 + 多行插入
mysql> insert into student (name,age) value ('孙权',21),('孙策',20);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 18 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 孙策 | 20 |
+----+--------+------+
6 rows in set (0.00 sec)
📂 插入否则更新
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
由于主键或者唯一键冲突，导致无法插入数据而导致插入失败。
--0 row affected:表中有冲突数据，但冲突数据的值和update后的值相等。
--1 row affected:表中没有冲突数据，直接插入。
--2 row affected:表中有冲突数据，更新冲突数据。
//主键冲突
mysql> insert into student value (1,'董卓',30);
ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'
//唯一键冲突
如果age设置成唯一键unique,插入两个相同值的age就会产生唯一键冲突，这里就不演示了
产生了主键冲突，那么我们就可以使用on duolicate key update，插入否则更新。
//插入id为1的单行数据,如果表内有冲突,就更新冲突行
mysql> select * from student;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 18 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 孙策 | 20 |
+----+--------+------+
6 rows in set (0.00 sec)
mysql> insert into student value (1,'董卓',30) on duplicate key update age = 19;
Query OK, 2 rows affected (0.02 sec)
mysql> select * from student;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 孙策 | 20 |
+----+--------+------+
6 rows in set (0.00 sec)
📂 替换
主键或者唯一键产生冲突，就删除冲突行，再插入新的行；没有冲突，直接插入。
mysql> replace into student value (7,'董卓',30);
Query OK, 1 row affected (0.01 sec)
mysql> replace into student value (6,'吕布',25);
Query OK, 2 rows affected (0.00 sec)
mysql> select * from student;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 7 | 董卓 | 30 |
+----+--------+------+
7 rows in set (0.00 sec)
--1 row affected:表中没有冲突数据，直接插入
--2 row affected:表中有冲突数据，删除后再插入。
📁 读取Retrieve
在MySQL中使用select语句来查询数据。
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];
● column1, column2, ... 是你想要选择的列的名称，如果使用 * 表示选择所有列。
● table_name 是你要从中查询数据的表的名称。
● WHERE condition 是一个可选的子句，用于指定过滤条件，只返回符合条件的行。
● ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC）。
● LIMIT number 是一个可选的子句，用于限制返回的行数。
📂 select列
全列查询
不建议使用，但平常练习时可以使用。 1. 查询的列越多，意味着需要传输的数据量越大；2. 可能会影响到索引的使用。
select * from table_name;
mysql> select * from student;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 7 | 董卓 | 30 |
+----+--------+------+
7 rows in set (0.00 sec)
指定列插入，指定列的顺序不需要按定义表的顺序。
select column1[,column2...] from table_name;
mysql> select id,name from student;
+----+--------+
| id | name |
+----+--------+
| 1 | 刘备 |
| 2 | 关羽 |
| 3 | 张飞 |
| 4 | 曹操 |
| 5 | 孙权 |
| 6 | 吕布 |
| 7 | 董卓 |
+----+--------+
7 rows in set (0.00 sec)
查询字段为表达式。
mysql> select 10;
+----+
| 10 |
+----+
| 10 |
+----+
1 row in set (0.00 sec)
mysql> select 10 + 10;
+---------+
| 10 + 10 |
+---------+
| 20 |
+---------+
1 row in set (0.00 sec)
mysql> select id,name,age+100 from student;
+----+--------+---------+
| id | name | age+100 |
+----+--------+---------+
| 1 | 刘备 | 119 |
| 2 | 关羽 | 118 |
| 3 | 张飞 | 118 |
| 4 | 曹操 | 119 |
| 5 | 孙权 | 121 |
| 6 | 吕布 | 125 |
| 7 | 董卓 | 130 |
+----+--------+---------+
7 rows in set (0.00 sec)
为查询结果指定别名
SELECT column [AS] alias_name [...] FROM table_name;
mysql> select id,name,age 年龄 from student;
+----+--------+--------+
| id | name | 年龄 |
+----+--------+--------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 7 | 董卓 | 30 |
+----+--------+--------+
7 rows in set (0.00 sec)
mysql> ^C
mysql> select id,name,age as 年龄 from student;
+----+--------+--------+
| id | name | 年龄 |
+----+--------+--------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 7 | 董卓 | 30 |
+----+--------+--------+
7 rows in set (0.00 sec)
结果去重
需要读取不重复的数据可以在 select 语句中使用 distinct 关键字来过滤重复数据。
mysql> select distinct age from student;
+------+
| age |
+------+
| 19 |
| 18 |
| 21 |
| 25 |
| 30 |
+------+
5 rows in set (0.00 sec)
📂 where条件
where类似于高级语言中的if语句。
比较运算符：
运算符说明>, >=, <, <=大于，大于等于，小于，小于等于=等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL<=>等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)!=, <>不等于BETWEEN a0 AND a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)IN (option, ...)如果是 option 中的任意一个，返回 TRUE(1)IS NULL是 NULLIS NOT NULL不是 NULLLIKE模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符
逻辑运算符：
运算符说明AND多个条件必须都为 TRUE(1)，结果才是 TRUE(1)OR任意一个条件为 TRUE(1), 结果为 TRUE(1)NOT条件为 TRUE(1)，结果为 FALSE(0)
关于like的使用：
% ：匹配任意多个（包括 0 个）任意字符 _ ：匹配严格的一个任意字符
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 7 | 董卓 | 30 |
| 8 | 孙尚香 | 18 |
+----+-----------+------+
8 rows in set (0.00 sec)
mysql> select * from student where name like '孙_';
+----+--------+------+
| id | name | age |
+----+--------+------+
| 5 | 孙权 | 21 |
+----+--------+------+
1 row in set (0.00 sec)
mysql> select * from student where name like '孙%';
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 5 | 孙权 | 21 |
| 8 | 孙尚香 | 18 |
+----+-----------+------+
2 rows in set (0.00 sec)
其他运算符非常简单，有C/C++基础的同学能做到见名知义，只需要在where后面加上这些运算符即可。
📂 结果排序
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
mysql> select * from student order by age desc;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 7 | 董卓 | 30 |
| 6 | 吕布 | 25 |
| 5 | 孙权 | 21 |
| 1 | 刘备 | 19 |
| 4 | 曹操 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 8 | 孙尚香 | 18 |
+----+-----------+------+
8 rows in set (0.00 sec)
mysql> select * from student order by age asc;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 8 | 孙尚香 | 18 |
| 1 | 刘备 | 19 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 7 | 董卓 | 30 |
+----+-----------+------+
8 rows in set (0.00 sec)
📂 筛选分页结果
-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。
mysql> select * from student limit 5
-> ;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
+----+--------+------+
5 rows in set (0.00 sec)
mysql> select * from student limit 0,3;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> select * from student limit 5 offset 0;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
+----+--------+------+
5 rows in set (0.00 sec)
📁 删除Delete
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
📂 删除指定行
mysql> delete from student where name='董卓';
Query OK, 1 row affected (0.01 sec)
mysql> select * from student where name='董卓';
Empty set (0.00 sec)
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 8 | 孙尚香 | 18 |
+----+-----------+------+
7 rows in set (0.00 sec)
📂 删除整张表
mysql> select * from delete_table;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
mysql> delete from delete_table;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from delete_table;
Empty set (0.00 sec)
📂 截断表
truncate [TABLE] table_name
截断表的操作类似于delete删除整张表的操作，但是不能像delete一样针对部分数据操作；
实际上MySQL不对数据操作，所以比delete更快，但是truncate在删除数据的时候，并不经过真正的事务，所以无法回滚。
truncate会重置auto_increment，delete不会重置auto_increment。
📁 更新Update
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
对查询到的结果进行列值更新。
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 刘备 | 19 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 8 | 孙尚香 | 18 |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> update student set age=20 where name='刘备';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | 刘备 | 20 |
| 2 | 关羽 | 18 |
| 3 | 张飞 | 18 |
| 4 | 曹操 | 19 |
| 5 | 孙权 | 21 |
| 6 | 吕布 | 25 |
| 8 | 孙尚香 | 18 |
+----+-----------+------+
7 rows in set (0.00 sec)
📁 插入查询结果
insert into table_name [(column [, column ...])] select ...
删除表中的的重复复记录，重复的数据只能有一份。
-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));
-- 插入测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
-- 创建一张空表 no_duplicate_table，结构和 duplicate_table 一样
CREATE TABLE no_duplicate_table LIKE duplicate_table;
-- 将 duplicate_table 的去重数据插入到 no_duplicate_table
INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table
-- 通过重命名表，实现原子的去重操作
RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;
SELECT * FROM duplicate_table;
+------+------+
| id | name |
+------+------+
| 100 | aaa |
| 200 | bbb |
| 300 | ccc |
+------+------+
📁 聚合函数
函数说明COUNT([DISTINCT] expr)返回查询到的数据的 数量SUM([DISTINCT] expr)返回查询到的数据的 总和，不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的 平均值，不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的 最大值，不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的 最小值，不是数字没有意义
mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
其他聚合函数也类似于count()函数一样，这里就不展示了。
指定列名，那么实际分组的时候，用该列不同的数据进行分组。组内一定是相同的，因此可以被聚合压缩。
分组，就是将一组按条件拆分成多组，进行各自组内的聚合统计。即一张表按照条件在逻辑上拆分成多个子表，分别对各自的子表进行聚合统计
📁 group by子句的使用
select column1, column2, .. from table group by column;
在select中使用group by子句可以对指定列进行分组查询，分组的目的就是方便聚合统计。
mysql> select * from student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 9 | 刘备 | 18 |
| 10 | 关羽 | 18 |
| 11 | 张飞 | 18 |
| 12 | 曹操 | 19 |
| 13 | 孙策 | 20 |
| 14 | 孙权 | 20 |
| 15 | 孙尚香 | 20 |
+----+-----------+------+
7 rows in set (0.00 sec)
mysql> select age 年龄,max(id) from student group by age;
+--------+---------+
| 年龄 | max(id) |
+--------+---------+
| 18 | 11 |
| 19 | 12 |
| 20 | 15 |
+--------+---------+
3 rows in set (0.00 sec)
mysql> select age 年龄,max(id) from student group by age having age > 18;
+--------+---------+
| 年龄 | max(id) |
+--------+---------+
| 19 | 12 |
| 20 | 15 |
+--------+---------+
2 rows in set (0.00 sec)
having是对聚合后的统计数据，进行条件筛选。
where是具体的任意列进行条件筛选；having是对分组聚合之后的结果进行条件筛选。
📁 总结
以上，就是本期内容了，主要讲解了MySQL中表的内容的操作CRUD，即插入数据，读取数据，更新数据，删除数据等内容。
如果感觉本期内容对你有帮助，欢迎点赞，关注，收藏Thanks♪(･ω･)ﾉ