【MySQL】表的基本查询

目录

🌈前言🌈

📁 创建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: 0mysql> 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 UPDATEcolumn = 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	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 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: 0mysql> 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♪(･ω･)ﾉ