MySQL 知识归纳整理

SQL 是一种用于管理和操作关系型数据库的标准化语言。常用的有以下几个语句：

SELECT / select

select 语句是最常用的语句，用来从一个或多个表格中查询数据。

-- 显示表的所有数据
select * from 表名;

select * from student;

-- 显示某列的所有数据
select 列1, 列2... from 表名;

select name, score from student;

-- 以别名显示某列的所有数据
select 列1 as 别名1, 列2 as 别名2... from 表名;

select name as 名字, score as 成绩 from student;
select name 名字, score 成绩 from student;

WHERE / where

可通过 where 来进行条件过滤。

例如，假设有一个名为 Employees 的表，其中包含有关员工的信息，包括员工ID、姓名、年龄和所属部门。检索年龄 (age) 大于 30 岁的员工信息。

EmployeeID	EmployeeName	Age	Department
1	John	28	HR
2	Mary	35	Sales
3	David	42	IT
4	Lisa	31	Marketing

select *
from employees
where age > 30;

结果如下：

EmployeeID	EmployeeName	Age	Department
2	Mary	35	Sales
3	David	42	IT
4	Lisa	31	Marketing

除了使用比较运算符外，还可以使用逻辑运算符（如 and、or 和 not）来构建更复杂的查询条件。

OR / or 运算符

假设我们需要检索所有年龄小于 30 岁或所属部门为 "HR" 的员工信息。

select *
from employees
where age < 30 or department = 'HR';

结果如下：

EmployeeID	EmployeeName	Age	Department
1	John	28	HR
2	Mary	35	Sales

上述查询返回了年龄小于 30 岁或所属部门为 "HR" 的员工信息，因此 John 和 Mary 的信息被检索出来。

AND / and 运算符

假设我们需要检索所有年龄大于 30 岁且所属部门为 "IT" 的员工信息。

select *
from employees
where age > 30 and department = 'IT';

结果如下：

EmployeeID	EmployeeName	Age	Department
1	John	28	HR
2	Mary	35	Sales

上述查询返回了年龄大于 30 岁且所属部门为 "IT" 的员工信息，因此只有 David 的信息被检索出来。

通过使用逻辑运算符，可以在 where 子句中组合多个条件，从而更灵活地筛选所需的数据。请根据实际需求使用适当的逻辑运算符来构建查询条件。

GROUP BY / group by

group by 子句是用来对查询结果进行分组的，它通常和聚合函数 (SUM 求和, AVG 平均, COUNT 计数, MIN 最小, MAX 最大) 一起使用，对每个分组进行统计。

例如，假设有一个学生表 student，它包含以下字段和数据：

id	name	gender	score
1	张三	男	85
2	李四	女	90
3	王五	男	80
4	赵六	女	95
5	钱七	男	75

如果要查询每个性别的学生人数，可以使用以下语句：

select gender, COUNT(*) as stu_count
from student
group by gender;

结果如下：

gender	stu_count
女	2
男	3

使用 group by 将学生按照性别分组：

id	name	gender	score
1	张三	男	85
3	王五	男	80
5	钱七	男	75

id	name	gender	score
2	李四	女	90
4	赵六	女	95

使用 count(*) 来每个分组的获取数据行数。

HAVING / having

having 语句是用来对分组后的结果进行过滤的，它通常和 group by 语句以及聚合函数一起使用。having 语句解决了 where 关键字不能与聚合函数一起使用的不足。

select column1, column2
from table1, table2
where [conditions]
group by column1, column2
having [conditions]
order by column1, column2

其中，[] 括起来的表示可选内容。

假设有一个订单表orders，包含以下字段和数据：

O_Id	OrderDate	OrderPrice	Customer
1	2020-12-29	1000	Bush
2	2020-11-23	1600	Carter
3	2020-10-05	700	Bush
4	2020-09-28	300	Bush
5	2020-08-06	2000	Adams
6	2020-07-21	100	Carter

如果要查询每个客户的订单总金额，可以使用以下语句：

select Customer, sum(OrderPrice) as TotalPrice
from orders
group by Customer;

通过 group by 把所有 customer 分组，使用 sum 函数将所有金额 price 加起来。

查询结果如下：

Customer	TotalPrice
Adams	2000
Bush	2000
Carter	1700

如果要查询订单总金额大于1500的客户，可以在上面的语句中增加一个having子句：

select Customer, sum(OrderPrice) as TotalPrice
from orders
group by Customer
having sum(OrderPrice) > 1500;

因为使用了聚合函数 (sum) ，此处应该用 having 而不是 where

查询结果如下：

Customer	TotalPrice
Adams	2000
Bush	2000

注意，having 子句是在分组后进行过滤，而 where 子句是在分组前进行过滤。如果同时有 where和 having 子句，那么执行顺序是先 where 后 having。

group by 的难点主要有以下几个方面：

• group by 的执行原理和顺序，以及它如何使用临时表和排序。
• group by 和 having 的区别，以及它们和 where 的执行顺序和作用范围。
• group by 的优化思路，如何避免全表扫描和排序，如何利用索引和覆盖索引。
• group by 和聚合函数的配合使用，以及聚合函数的特性和限制。
• group by 和多表联查的结合使用，以及不同类型的连接对分组的影响。

group by的易错点主要有以下几个方面：

• group by 不一定要配合聚合函数使用，但是如果没有聚合函数，就相当于去重。
• group by 的字段不一定要出现在 select 中，但是如果出现在 select 中的字段不在 group by中，就会报错或者返回随机值。
• group by 不能使用别名作为条件，例如 having a > b，其中 a 和 b 是 select 中的列别名。
• group by 不能返回多个值给单值运算符，例如 where a = (select b, c from t)。
• group by 不能返回多列给多值运算符，除非使用行子查询，例如 where (a, b) = (select c, d from t)。

好的，我会尝试按照这个文笔，续写 insert 语句的案例和易错点。请看以下内容：

INSERT / insert

insert 语句是用来向表中插入新的记录的，它可以有两种编写形式。

-- 向表中插入所有列的值，不需要指定列名，但是要保证值的顺序和表中列的顺序相同
insert into 表名 values (值1, 值2, ...);

insert into student values (6, '周八', '女', 88);

-- 向表中插入指定列的值，需要指定列名和对应的值，可以省略不需要插入的列
insert into 表名 (列1, 列2, ...) values (值1, 值2, ...);

insert into student (name, score) values ('刘九', 92);

假设有一个商品表 product，包含以下字段和数据：

id	name	price	category
1	苹果	5.0	水果
2	香蕉	3.0	水果
3	牛奶	4.5	饮品
4	面包	6.0	食品

如果要向 product 表中插入一条新的记录，可以使用以下语句：

-- 插入所有列的值
insert into product values (5, '鸡蛋', 2.5, '食品');

-- 插入指定列的值
insert into product (name, price) values ('橙子', 4.0);

插入后的 product 表如下：

id	name	price	category
1	苹果	5.0	水果
2	香蕉	3.0	水果
3	牛奶	4.5	饮品
4	面包	6.0	食品
5	鸡蛋	2.5	食品
null	橙子	4.0	null

在使用 insert 语句时，需要注意以下几个易错点：

1. 如果插入所有列的值，要保证值的个数和顺序与表中列的个数和顺序相同，否则会报错或者插入错误的数据。
2. 如果插入指定列的值，要保证列名和值一一对应，并且没有重复或者遗漏。如果没有为某些列指定值，那么这些列会被赋予默认值或者 null 值。
3. 如果插入的值与表中已有的主键或者唯一索引冲突，那么会报错或者更新已有的记录。
4. 如果插入的值不符合表中列的数据类型或者约束条件，那么会报错或者截断数据。例如，如果插入一个超过长度限制的字符串，那么会被截断为最大长度。
5. 如果插入的值是从其他表中查询得到的，那么要保证查询结果只有一行或者使用子查询。例如，如果要插入一个商品的平均价格，那么可以使用以下语句：

-- 插入所有列的值
insert into product values (6, '平均价格', (select avg(price) from product), null);

-- 插入指定列的值
insert into product (name, price) values ('平均价格', (select avg(price) from product));

UPDATE语句：UPDATE语句用于更新表格中的现有数据行。UPDATE语句必须指定要更新的表格名称和要更新的列名称，然后指定要更新的新值。常见易错点包括：UPDATE必须包含WHERE子句以限制要更新的数据行，使用错误的WHERE条件会导致更新错误的数据行，忘记在列名之间使用逗号。

DELETE语句：DELETE语句用于从表格中删除指定的数据行。DELETE语句必须指定要删除的表格名称和要删除的数据行。常见易错点包括：DELETE必须包含WHERE子句以限制要删除的数据行，使用错误的WHERE条件会导致删除错误的数据行。

WHERE子句：WHERE子句用于筛选要从表格中检索、更新或删除的数据行。WHERE子句指定了一个或多个条件，这些条件必须与数据行的值匹配，才能被检索、更新或删除。常见易错点包括：忘记在WHERE子句中使用逻辑运算符（如AND、OR），使用错误的比较运算符（如=和==）。

GROUP BY语句：GROUP BY语句用于将表格中的数据行分组，并在每个组中执行汇总函数（如SUM、AVG、COUNT）计算。GROUP BY语句必须在SELECT语句中使用，通常用于创建报告和统计数据。常见易错点包括：忘记在GROUP BY子句中包含所有未汇总的列名。

ORDER BY语句：ORDER BY语句用于按照指定的列对结果进行排序，可以按升序或降序排列。ORDER BY子句必须在SELECT语句中使用。常见易错点包括：在ORDER BY子句中未使用正确的列名或使用错误的列名，忘记在ORDER BY子句中指定升序或降序排列。

JOIN语句：JOIN语句用于将两个或多个表格中的数据行合并在一起，从而创建一个新的结果集。JOIN语句必须使用ON子句指定两个表格之间的关联条件。常见易错点包括：使用不正确的关联条件，忘记指定关联条件，使用错误的JOIN类型（如LEFT JOIN、RIGHT JOIN、INNER JOIN、FULL OUTER JOIN）。

SQL语句的使用规范包括：清晰和简洁、使用正确的大小写、遵循标准的命名惯例、注释代码以增加可读性、使用参数化查询以避免SQL注入。

SQL语句的应用规则包括：避免在关键词和标识符中使用保留字符、避免使用空格和特殊字符进行标识符命名、避免使用复杂的嵌套查询和过多的子查询。

SQL语句的应用实战包括：使用LIMIT进行分页查询、使用索引来加速查询、使用事务处理来保证数据完整性、使用存储过程来封装和重用数据库逻辑。