CRUD : Create(创建)，Retrieve(读取)，Update(更新)，Delete（删除）
1.Create
INSERT [INTO] table_name [(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...
-- 创建一张学生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
1.1.单行数据+全列插入
mysql> create table student(
-> id int primary key auto_increment,
-> sn int unique comment "学号",
-> name varchar(20) not null, qq varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into student values(100, 10000, "zhangsan",'11111');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(101, 10001, "lisi",'22222');
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+-----+-------+----------+-------+
| id | sn | name | qq |
+-----+-------+----------+-------+
| 100 | 10000 | zhangsan | 11111 |
| 101 | 10001 | lisi | 22222 |
+-----+-------+----------+-------+
2 rows in set (0.00 sec)
1.2.多行数据+指定列插入
value_list 数量必须和指定列数量及顺序一致
mysql> insert into student (id, sn, name) values
-> (102,10002,'wangwu'),
->(103,10003,'zhaoliu');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select *from student;
+-----+-------+----------+-------+
| id | sn | name | qq |
+-----+-------+----------+-------+
| 100 | 10000 | zhangsan | 11111 |
| 101 | 10001 | lisi | 22222 |
| 102 | 10002 | wangwu | NULL |
| 103 | 10003 | zhaoliu | NULL |
+-----+-------+----------+-------+
4 rows in set (0.00 sec)
1.3.插入是否更新
由于 主键 或者 唯一键 对应的值已经存在而导致插入失败，可以选择性的进行同步更新操作语法：
INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...)
ON DUPLICATE KEY UPDATE
列1 = 新值1,
列2 = 新值2,
...;
-- ON DUPLICATE KEY 当发生重复key的时候
mysql> insert into student (id, sn, name) values
-> (100,10010,'孙悟空')
-> on duplicate key update sn=10010, name='孙悟空';
Query OK, 2 rows affected (0.00 sec)
mysql> select *from student;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 孙悟空 | 11111 |
| 101 | 10001 | lisi | 22222 |
| 102 | 10002 | wangwu | NULL |
| 103 | 10003 | zhaoliu | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
插入同一段数据：
mysql> insert into student (id, sn, name)
-> values (100,10010,'孙悟空')
-> on duplicate key update sn=10010, name='孙悟空';
Query OK, 0 rows affected (0.00 sec)
-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，并且数据已经被更新
1.4.替换
REPLACE INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...);
mysql> replace student(id, sn, name) values(101, 10001, '唐僧');
Query OK, 2 rows affected (0.00 sec)
mysql> select *from student;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 孙悟空 | 11111 |
| 101 | 10001 | 唐僧 | NULL |
| 102 | 10002 | wangwu | NULL |
| 103 | 10003 | zhaoliu | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
-- 1 row affected: 表中没有冲突数据，数据被插入
-- 2 row affected: 表中有冲突数据，删除后重新插入
2.Retrieve
通常我们用 SELECT 语句来检索（retrieve） 表中的数据。SELECT 是用于从一个或多个表中查询数据的核心命令
SELECT 列名1, 列名2, ...
FROM 表名
[WHERE 条件]
[GROUP BY 列名]
[HAVING 条件]
[ORDER BY 列名 [ASC|DESC]]
[LIMIT 数量];
CREATE TABLE exam (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);
INSERT INTO exam (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);
2.1.select列
（1）全列查询
-- 通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多，意味着需要传输的数据量越大；
-- 2. 可能会影响到索引的使用。
mysql> select * from exam;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.00 sec)
（2）指定列查询
-- 指定列的顺序不需要按定义表的顺序来
mysql> select id, name, english from exam;
+----+-----------+---------+
| id | name | english |
+----+-----------+---------+
| 1 | 唐三藏 | 56 |
| 2 | 孙悟空 | 77 |
| 3 | 猪悟能 | 90 |
| 4 | 曹孟德 | 67 |
| 5 | 刘玄德 | 45 |
| 6 | 孙权 | 78 |
| 7 | 宋公明 | 30 |
+----+-----------+---------+
7 rows in set (0.00 sec)
（3）查询字段为表达式
-- 表达式不包含字段
mysql> select id, name, 10 from exam;
+----+-----------+----+
| id | name | 10 |
+----+-----------+----+
| 1 | 唐三藏 | 10 |
| 2 | 孙悟空 | 10 |
| 3 | 猪悟能 | 10 |
| 4 | 曹孟德 | 10 |
| 5 | 刘玄德 | 10 |
| 6 | 孙权 | 10 |
| 7 | 宋公明 | 10 |
+----+-----------+----+
7 rows in set (0.00 sec)
-- 表达式包含一个字段
mysql> select id, name,english+10 from exam;
+----+-----------+------------+
| id | name | english+10 |
+----+-----------+------------+
| 1 | 唐三藏 | 66 |
| 2 | 孙悟空 | 87 |
| 3 | 猪悟能 | 100 |
| 4 | 曹孟德 | 77 |
| 5 | 刘玄德 | 55 |
| 6 | 孙权 | 88 |
| 7 | 宋公明 | 40 |
+----+-----------+------------+
7 rows in set (0.00 sec)
-- 表达式包含多个字段
mysql> select id, name, chinese + math + english from exam;
+----+-----------+--------------------------+
| id | name | chinese + math + english |
+----+-----------+--------------------------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------------------------+
7 rows in set (0.00 sec)
（4）为查询结果指定别名
SELECT column [AS] alias_name [...] FROM table_name;
mysql> select id, name, chinese + math + english 总分 from exam;
+----+-----------+--------+
| id | name | 总分 |
+----+-----------+--------+
| 1 | 唐三藏 | 221 |
| 2 | 孙悟空 | 242 |
| 3 | 猪悟能 | 276 |
| 4 | 曹孟德 | 233 |
| 5 | 刘玄德 | 185 |
| 6 | 孙权 | 221 |
| 7 | 宋公明 | 170 |
+----+-----------+--------+
7 rows in set (0.00 sec)
（5）结果去重
mysql> select math from exam;
+------+
| math |
+------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
7 rows in set (0.00 sec)
mysql> select distinct math from exam;
+------+
| math |
+------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+------+
6 rows in set (0.00 sec)
2.2.where
比较运算符：
运算符 说明
>, >=, <, <= 大于，大于等于，小于，小于等于
= 等于，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)
案例：
数学成绩在 [80, 90] 分的同学及语文成绩
mysql> select name math from exam where math >= 80 and math <= 90;
+-----------+
| math |
+-----------+
| 曹孟德 |
| 刘玄德 |
+-----------+
2 rows in set (0.00 sec)
mysql> select name math from exam where math between 80 and 90;
+-----------+
| math |
+-----------+
| 曹孟德 |
| 刘玄德 |
+-----------+
2 rows in set (0.00 sec)
数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
mysql> select name math from exam where math=58
-> or math=59 or math=98 or math=99;
+-----------+
| math |
+-----------+
| 唐三藏 |
| 猪悟能 |
+-----------+
2 rows in set (0.00 sec)
mysql> select name math from exam where math IN(58,59,98,99);
+-----------+
| math |
+-----------+
| 唐三藏 |
| 猪悟能 |
+-----------+
2 rows in set (0.00 sec)
% 匹配任意多个（包括 0 个）任意字符
_ 匹配严格的一个任意字符
mysql> select name from exam where name LIKE '孙%';
+-----------+
| name |
+-----------+
| 孙悟空 |
| 孙权 |
+-----------+
2 rows in set (0.00 sec)
mysql> select name from exam where name LIKE '孙_';
+--------+
| name |
+--------+
| 孙权 |
+--------+
1 row in set (0.00 sec)
mysql> select name math from exam where math > 90 and name NOT LIKE '孙%';
+-----------+
| math |
+-----------+
| 唐三藏 |
| 猪悟能 |
+-----------+
2 rows in set (0.00 sec)
孙某同学，或者要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
-- 别名不能用在 WHERE 条件中
mysql> SELECT name, chinese, math, english,
-> chinese + math + english 总分 FROM exam
-> WHERE name LIKE '孙_' OR ( chinese + math + english > 200
-> AND chinese < math AND english > 80 );
+-----------+---------+------+---------+--------+
| name | chinese | math | english | 总分 |
+-----------+---------+------+---------+--------+
| 猪悟能 | 88 | 98 | 90 | 276 |
| 孙权 | 70 | 73 | 78 | 221 |
+-----------+---------+------+---------+--------+
2 rows in set (0.00 sec)
NULL 的查询
mysql> select * from student;
+-----+-------+-----------+-------+
| id | sn | name | qq |
+-----+-------+-----------+-------+
| 100 | 10010 | 孙悟空 | 11111 |
| 101 | 10001 | 唐僧 | NULL |
| 102 | 10002 | wangwu | NULL |
| 103 | 10003 | zhaoliu | NULL |
+-----+-------+-----------+-------+
4 rows in set (0.00 sec)
mysql> select name, qq from student where qq is not null;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
+-----------+-------+
1 row in set (0.00 sec)
=与<=>
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)
2.3.排序
-- ASC 为升序（从小到大）
-- DESC 为降序（从大到小）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];
没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。
按数学成绩升序显示
mysql> select name, math from exam order by math;
+-----------+------+
| name | math |
+-----------+------+
| 宋公明 | 65 |
| 孙权 | 73 |
| 孙悟空 | 78 |
| 曹孟德 | 84 |
| 刘玄德 | 85 |
| 唐三藏 | 98 |
| 猪悟能 | 98 |
+-----------+------+
7 rows in set (0.00 sec)
NULL 视为比任何值都小，升序出现在最上面，降序在最下面。
mysql> SELECT name, qq FROM student ORDER BY qq desc;
+-----------+-------+
| name | qq |
+-----------+-------+
| 孙悟空 | 11111 |
| 唐僧 | NULL |
| wangwu | NULL |
| zhaoliu | NULL |
+-----------+-------+
4 rows in set (0.00 sec)
多字段排序，排序优先级随书写顺序
mysql> select name, math, chinese, english from exam
-> order by math, chinese, english;
+-----------+------+---------+---------+
| name | math | chinese | english |
+-----------+------+---------+---------+
| 宋公明 | 65 | 75 | 30 |
| 孙权 | 73 | 70 | 78 |
| 孙悟空 | 78 | 87 | 77 |
| 曹孟德 | 84 | 82 | 67 |
| 刘玄德 | 85 | 55 | 45 |
| 唐三藏 | 98 | 67 | 56 |
| 猪悟能 | 98 | 88 | 90 |
+-----------+------+---------+---------+
7 rows in set (0.00 sec)
ORDER BY 子句中可以使用表达式和列别名
mysql> SELECT name, chinese + english + math FROM exam
-> order by chinese + math + english;
+-----------+--------------------------+
| name | chinese + english + math |
+-----------+--------------------------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
+-----------+--------------------------+
7 rows in set (0.00 sec)
mysql> SELECT name, chinese + english + math "总分" from exam order by 总分;
+-----------+--------+
| name | 总分 |
+-----------+--------+
| 宋公明 | 170 |
| 刘玄德 | 185 |
| 唐三藏 | 221 |
| 孙权 | 221 |
| 曹孟德 | 233 |
| 孙悟空 | 242 |
| 猪悟能 | 276 |
+-----------+--------+
7 rows in set (0.00 sec)
2.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;
对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数
按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页
mysql> SELECT id, name, chinese, math, english from exam order by id limit 3 OFFSET 0;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 67 | 98 | 56 |
| 2 | 孙悟空 | 87 | 78 | 77 |
| 3 | 猪悟能 | 88 | 98 | 90 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> SELECT id, name, chinese, math, english from exam order by id limit 3 OFFSET 3;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 4 | 曹孟德 | 82 | 84 | 67 |
| 5 | 刘玄德 | 55 | 85 | 45 |
| 6 | 孙权 | 70 | 73 | 78 |
+----+-----------+---------+------+---------+
3 rows in set (0.00 sec)
mysql> SELECT id, name, chinese, math, english from exam order by id limit 3 OFFSET 6;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 7 | 宋公明 | 75 | 65 | 30 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
3.Update
UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ...
WHERE 条件;
一次更新多个列
mysql> update exam set chinese = 69, math = 95, english = 50 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from exam where id = 1;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 69 | 95 | 50 |
+----+-----------+---------+------+---------+
1 row in set (0.00 sec)
更新值为原值基础上变更
mysql> select name, math, math+chinese+english 总分 from exam order by 总分 limit 3;
+-----------+------+--------+
| name | math | 总分 |
+-----------+------+--------+
| 宋公明 | 65 | 170 |
| 刘玄德 | 85 | 185 |
| 唐三藏 | 95 | 214 |
+-----------+------+--------+
3 rows in set (0.00 sec)
mysql> update exam set math = math + 30 order by chinese + math + english limit 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select name, math, math+chinese+english 总分 from exam order by 总分 limit 3;
+-----------+------+--------+
| name | math | 总分 |
+-----------+------+--------+
| 宋公明 | 95 | 200 |
| 刘玄德 | 115 | 215 |
| 孙权 | 73 | 221 |
+-----------+------+--------+
3 rows in set (0.00 sec)
将所有同学的语文成绩更新为原来的 2 倍。没有 WHERE 子句，则更新全表，注意：更新全表的语句慎用。
mysql> update exam set chinese = chinese * 2;
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7 Changed: 7 Warnings: 0
mysql> select * from exam;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 1 | 唐三藏 | 138 | 125 | 50 |
| 2 | 孙悟空 | 174 | 78 | 77 |
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 164 | 84 | 67 |
| 5 | 刘玄德 | 110 | 115 | 45 |
| 6 | 孙权 | 140 | 73 | 78 |
| 7 | 宋公明 | 150 | 95 | 30 |
+----+-----------+---------+------+---------+
7 rows in set (0.01 sec)
4.Delete
4.1.delete
DELETE FROM 表名
WHERE 条件;
mysql> delete from exam where name = '孙悟空';
Query OK, 1 row affected (0.00 sec)
mysql> select * from exam where name = '孙悟空';
Empty set (0.00 sec)
注意：删除整表操作要慎用！
mysql> create table for_delete(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into for_delete values(1, 'a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into for_delete(name) values('b');
Query OK, 1 row affected (0.00 sec)
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
+----+------+
2 rows in set (0.00 sec)
mysql> delete from for_delete;
Query OK, 2 rows affected (0.00 sec)
mysql> select * from for_delete;
Empty set (0.00 sec)
再插入一条数据，自增 id 在原值上增长
mysql> insert into for_delete(name) values('c');
Query OK, 1 row affected (0.00 sec)
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 3 | c |
+----+------+
1 row in set (0.00 sec)
-- 查看表结构，会有 AUTO_INCREMENT=n 项
SHOW CREATE TABLE for_delete\G
*************************** 1. row ***************************
Table: for_delete
Create Table: CREATE TABLE `for_delete` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
4.2.truncate
TRUNCATE TABLE 表名;
- 清空数据但保留表结构：删除表中所有记录，但表的结构、列、约束等保持不变
- 速度快：相比 DELETE FROM 表名（无 WHERE 条件），TRUNCATE 执行速度更快，因为它不逐行删除，而是直接重建表结构
- 无法回滚：在默认情况下，TRUNCATE 是不可回滚的（属于隐式提交操作），而 DELETE 可以通过事务回滚
mysql> create table for_truncate(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into for_truncate(name) values ('A'), ('B'), ('C');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+------+
3 rows in set (0.00 sec)
mysql> truncate for_truncate;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from for_truncate;
Empty set (0.01 sec)
- 重置自增 ID：如果表中有自增列（如 AUTO_INCREMENT），TRUNCATE 会将自增计数器重置为初始值（通常是 1）
mysql> insert into for_truncate values('D');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into for_truncate(name) values('D');
Query OK, 1 row affected (0.00 sec)
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
+----+------+
1 row in set (0.00 sec)
5.插入查询结果
mysql> select * from for_delete;
+----+------+
| id | name |
+----+------+
| 3 | c |
| 5 | c |
| 6 | b |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into for_truncate select * from for_delete;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from for_truncate;
+----+------+
| id | name |
+----+------+
| 1 | D |
| 3 | c |
| 5 | c |
| 6 | b |
+----+------+
4 rows in set (0.00 sec)
6.聚合函数
函数 说明
COUNT ([DISTINCT] expr) 返回查询到的数据的 数量
SUM ( [DISTINCT] expr) 返回查询到的数据的 总和，不是数字没有意义
AVG ( [DISTINCT] expr) 返回查询到的数据的 平均值，不是数字没有意义
MAX ( [DISTINCT] expr) 返回查询到的数据的 最大值，不是数字没有意义
MIN ( [DISTINCT] expr) 返回查询到的数据的 最小值，不是数字没有意义
（1）count
mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
mysql> select count(1) from exam;
+----------+
| count(1) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
COUNT(*)：统计所有行的数量，包括包含 NULL 值的行（无论列中是否有 NULL，只要该行存在就会被计数）。
COUNT(1)：本质上是对常量 1 进行计数，因为每一行都会对应一个 1（常量不会为 NULL），所以结果也是统计所有行的数量，包括包含 NULL 值的行。
统计数学成绩个数
mysql> select count(math) from exam;
+-------------+
| count(math) |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
NULL 不会计入结果
统计数学成绩总分
mysql> select sum(math) from exam;
+-----------+
| sum(math) |
+-----------+
| 590 |
+-----------+
1 row in set (0.00 sec)
--统计不及格的总分，没有结果，返回 NULL
mysql> select sum(math) from exam where math < 60;
+-----------+
| sum(math) |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
统计平均总分
mysql> select avg(chinese+math+english) from exam;
+---------------------------+
| avg(chinese+math+english) |
+---------------------------+
| 304.6666666666667 |
+---------------------------+
1 row in set (0.00 sec)
返回数学最高分、最低分
mysql> select max(math) from exam;
+-----------+
| max(math) |
+-----------+
| 125 |
+-----------+
1 row in set (0.00 sec)
mysql> select min(math) from exam where math < 80;
+-----------+
| min(math) |
+-----------+
| 73 |
+-----------+
1 row in set (0.00 sec)
7.group by子句的使用
GROUP BY 是 MySQL 中用于对查询结果进行分组的关键字，通常与聚合函数（如 COUNT、SUM、AVG 等）配合使用，实现对数据的分组统计分析。
SELECT 分组列, 聚合函数(列)
FROM 表名
WHERE 条件
GROUP BY 分组列;
mysql> select * from exam;
+----+-----------+---------+------+---------+--------+
| id | name | chinese | math | english | class |
+----+-----------+---------+------+---------+--------+
| 1 | 唐三藏 | 138 | 125 | 50 | 一班 |
| 3 | 猪悟能 | 176 | 98 | 90 | 二班 |
| 4 | 曹孟德 | 164 | 84 | 67 | 三班 |
| 5 | 刘玄德 | 110 | 115 | 45 | 一班 |
| 6 | 孙权 | 140 | 73 | 78 | 一班 |
| 7 | 宋公明 | 150 | 95 | 30 | 四班 |
+----+-----------+---------+------+---------+--------+
6 rows in set (0.00 sec)
显示每个班级的平均成绩、和各科最高分
mysql> select class, avg(chinese+math+english) 平均成绩,
-> max(math), max(chinese), max(english)
-> from exam group by class order by 平均成绩 desc;
+--------+-------------------+-----------+--------------+--------------+
| class | 平均成绩 | max(math) | max(chinese) | max(english) |
+--------+-------------------+-----------+--------------+--------------+
| 二班 | 364 | 98 | 176 | 90 |
| 三班 | 315 | 84 | 164 | 67 |
| 一班 | 291.3333333333333 | 125 | 140 | 78 |
| 四班 | 275 | 95 | 150 | 30 |
+--------+-------------------+-----------+--------------+--------------+
4 rows in set (0.00 sec)
HAVING 子句必须跟在 GROUP BY 之后，用于过滤分组后的结果。
显示平均分低于300的班级和它的平均成绩
mysql> select class, avg(chinese+math+english) 平均成绩
-> from exam group by class having 平均成绩 < 300;
+--------+-------------------+
| class | 平均成绩 |
+--------+-------------------+
| 一班 | 291.3333333333333 |
| 四班 | 275 |
+--------+-------------------+
2 rows in set (0.00 sec)
SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit
FROM：首先确定要查询的表，是整个查询的基础。
JOIN：根据连接类型（INNER JOIN、LEFT JOIN 等）关联其他表。
ON：指定多表连接的条件（与 JOIN 配合使用）。
WHERE：对关联后的记录进行筛选（排除不满足条件的行，此时还未分组）。
GROUP BY：将筛选后的记录按指定列分组。
HAVING：对分组后的结果进行筛选（可以使用聚合函数，筛选整个组）。
SELECT：提取需要的列（包括聚合函数计算结果）。
DISTINCT：对 SELECT 后的结果去重（基于查询的列）。
ORDER BY：对最终结果按指定列排序（升序 ASC 或降序 DESC）。
LIMIT：限制返回的行数（用于分页等场景）。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
