SQL基础语法及进阶索引优化、常考面试题

数据库介绍

1.数据库介绍

数据库就是存储和管理数据的仓库，数据按照一定的格式进行存储，用户可以对数据库中的数据进行增删改查。

2.数据库的分类

• 关系型数据库：二维表格模型，类似Excel表格

• 非关系型数据库：key-value的方式存储数据

2.1关系型数据库

关系型数据库核心元素

• 数据行
• 数据列
• 数据表
• 数据库（数据表的集合）

常用关系型数据库

• Oracle（银行、金融）
• Microsoft SQL Server
• MySQL(阿里，京东)开源的免费
• SQLite嵌入性数据库

2.2非关系型数据库

常用的非关系型数据库

• MongoDB
• Redis

3.数据库的特点

• 持久化存储
• 读写速度高
• 保证数据的有效性

linux数据库命令

安装mysql命令行客户端

sudo apt-get install mysql-client

查看Ubuntu虚拟机的IP地址

ifconfig

查找mysql位置

ps -aux | grep mysql
-ps查看当前系统进程
-a表示所有用户
-u表示显示用户名
-x表示显示所有执行程序

查看 Mysql 运行状态

sudo service mysql status

启动Mysql服务

sudo service mysql start
sudo service mysql restart  重启mysql

停止Mysql服务

sudo service mysql stop

进客户端

#sudo mysql -u用户 -p密码
sudo mysql -uroot -pchenming852,

切换数据库

use new_database;

找到默认的用户密码

sudo cat /etc/mysql/debian.cnf

查看已有用户

 #进入sql后  mysql>
 use mysql; #切换到mysql
 select user,host from user; #查看user列表

创建一个新的mysql用户并授予其对所有数据库的访问权限

sudo mysql -uroot -pchenming852,
CREATE USER 'new_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'%';
FLUSH PRIVILEGES;

显示当前 MySQL 服务器上所有的数据库

show databases;

退出mysql

ctrl+d

基础语法

创建表

要在SQL中创建表，可以使用CREATE TABLE语句。语法如下：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    table_constraints
);

其中，table_name是要创建的表名，column1、column2等是表中的列名，datatype是列的数据类型，constraints是列的约束条件，table_constraints是表的约束条件。

例如，要创建一个名为students的表，包含id、name、age和gender四个列，可以使用以下语句：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    gender VARCHAR(10)
);

这将创建一个名为students的表，包含id、name、age和gender四个列。其中，id列被指定为主键，数据类型为INT；name和gender列的数据类型为VARCHAR，长度分别为50和10；age列的数据类型为INT。

增加数据

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

删除数据

要在SQL中删除数据，可以使用DELETE FROM语句。语法如下：

DELETE FROM table_name
WHERE condition;

其中，table_name是要删除数据的表名，condition是一个可选的条件，用于指定要删除哪些数据。

例如，要删除名为students的表中age大于30的数据，可以使用以下语句：

DELETE FROM students
WHERE age > 30;

这将删除students表中所有age大于30的数据。如果不指定条件，将删除表中所有数据。

更改数据

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

其中，table_name是要更新数据的表名，column1、column2等是要更新的列名，value1、value2等是要更新的实际数值，condition是一个可选的条件，用于指定要更新哪些数据。

例如，要将名为students的表中age大于30的数据的gender改为’Male’，可以使用以下语句：

UPDATE students
SET gender = 'Male'
WHERE age > 30;

这将更新students表中所有age大于30的数据的gender列为’Male’。如果不指定条件，将更新表中所有数据。

查询数据

全表查询

select * from 表名

当我们使用 select * from 表名 这样的 SQL 语句时，就是在进行全表查询，它会返回数据表中的所有行，让我们可以全面了解表中的数据。

选择查询

选择查询的 SQL 语法是 SELECT {列名...}，它告诉数据库我们想要查看哪些列的数据。

数据表 students：

name	age	gender	score
John	18	Male	90
Alice	17	Female	88
Bob	19	Male	78
Kate	18	Female	95

现在，我们使用"选择查询"来获取所有学生的姓名（name）和性别（gender）信息，SQL 语句如下：

select name, gender from students;

查询结果：

name	gender
John	Male
Alice	Female
Bob	Male
Kate	Female

别名

数据表 employees：

name	age	position
John	30	Project Manager
Alice	28	Software Engineer
Bob	32	Product Designer
Kate	26	Marketing Specialist

现在，我们使用 “别名” 来获取所有团队成员的姓名（name）和职位（position）信息，并为它们取别名为 员工姓名 和 职位名称：

select name as 员工姓名, position as 职位名称 from employees;

上述代码中的 as 也可以省略，比如 name 员工姓名 也是 ok 的。

查询结果，注意表格头的列名从英文变为了中文：

员工姓名	职位名称
John	Project Manager
Alice	Software Engineer
Bob	Product Designer
Kate	Marketing Specialist

常量和运算

通过常量和运算，我们可以在查询语句中对数据进行加减乘除、取平均值、连接文本等操作，从而得到我们想要的查询结果。

让我们来看一个具体的 SQL 代码和结果示例，假设有一张名为 orders 的数据表，它存储了订单信息，包括订单编号（order_id）、商品单价（unit_price）、购买数量（quantity）等：

数据表orders：

order_id	unit_price	quantity
1001	10.00	5
1002	20.00	3
1003	15.00	2
1004	25.00	4

现在，我们需要计算每个订单的总金额（total_amount），即商品单价（unit_price）乘以购买数量（quantity）。

SQL 查询语句如下：

select order_id, unit_price, quantity, unit_price * quantity as total_amount from orders;

查询结果如下，其中 total_amount 是计算出来的新列：

order_id	unit_price	quantity	total_amount
1001	10.00	5	50.00
1002	20.00	3	60.00
1003	15.00	2	30.00
1004	25.00	4	100.00

条件查询

where

通过条件查询，你可以从数据库中筛选出 满足特定条件 的数据行，而不是返回表中的所有数据。

主要使用 where 子句在查询中设置过滤条件，只有满足这些条件的数据行才会被返回。

where 子句的语法如下：

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

其中，列1, 列2, ...是你要选择的列，可以是具体的列名，也可以是*表示选择所有列。表名是你要从中查询数据的表名。条件是指定的查询条件，可以使用比较运算符（如=、<、>等）、逻辑运算符（如AND、OR等）、IN 操作符、LIKE 操作符等来设置条件。

示例

让我们来看一个具体的SQL代码和结果示例，假设有一张名为products的数据表，它存储了产品信息，包括产品名称（name）、单价（price）、库存（stock）等：

数据表products：

name	price	stock
A	10.00	50
B	20.00	30
C	15.00	20
D	25.00	10

现在，我们使用 “WHERE” 来筛选出库存小于等于 20 的产品：

-- SQL查询语句
select name, price, stock from products where stock <= 20;

查询结果：

name	price	stock
C	15.00	20
D	25.00	10

通过上述 SQL 查询语句，我们筛选出了库存小于等于 20 的产品，从而得到了符合条件的产品信息。

运算符

运算符是 SQL 中用于在条件查询中进行条件判断的特殊符号，比如 =、 !=、<、> 等。通过使用不同的运算符，我们可以在查询语句中设定多样化的条件，从而根据数据的不同属性进行灵活的筛选和过滤。

假设你是一名招聘官，而数据表中的数据就像是你面试的候选人。不同的运算符就像是你设定的不同面试条件，例如你可以使用 “!=” 运算符来筛选出不符合某个条件的候选人，使用 “>” 运算符来筛选出成绩优秀的候选人，使用 “BETWEEN” 运算符来筛选出年龄在某个范围内的候选人。

示例

让我们来看一个具体的SQL代码和结果示例，假设有一张名为 employees 的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、工资（salary）等：

数据表 employees：

name	age	salary
小明	25	5000
小红	30	6000
小李	28	5500
小张	22	4500

现在，我们使用不同的运算符来进行条件查询：

1）使用 “!=” 运算符筛选出 name 不是 ‘小张’ 的员工：

-- SQL查询语句
select name, age, salary from employees where name != '小张';

查询结果：

name	age	salary
小明	25	5000
小红	30	6000
小李	28	5500

2）使用 “>” 运算符筛选出工资高于 5500 的员工：

-- SQL查询语句
select name, age, salary from employees where salary > 5500;

查询结果：

name	age	salary
小红	30	6000

3）使用 “BETWEEN” 运算符筛选出年龄在 25 到 30 之间的员工：

-- SQL查询语句
select name, age, salary from employees where age between 25 and 30;

查询结果：

name	age	salary
小明	25	5000
小红	30	6000
小李	28	5500

通过上述 SQL 查询语句，我们可以根据不同的运算符进行条件查询，得到了符合不同条件的员工信息。

空值

在数据库中，有时候数据表的某些字段可能没有值，即为空值（NULL）。

空值表示该字段的值是未知的、不存在的或者没有被填写的。在SQL查询中，我们可以使用 “IS NULL” 和 “IS NOT NULL” 来判断字段是否为空值或非空值。

空值的应用场景：假设你是一名考试老师，而数据表中的数据就像是你学生们的考试成绩。当某个学生没有参加考试或者成绩尚未出来时，他的考试成绩就是空值。你可以使用 “IS NULL” 来找出没有参加考试的学生，使用 “IS NOT NULL” 来找出已经有成绩的学生。

示例

假设有一张名为 employees 的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、入职日期（hire_date）等：

数据表 employees ：

name	age	hire_date
小明	25	2020-01-01
小红	30	2020-02-15
小李	28	NULL
小张	NULL	2020-03-20

现在，我们使用 “IS NULL” 来查询出入职日期未填写的员工：

-- SQL查询语句
select name, age from employees where hire_date is null;

查询结果：

name	age
小李	28

模糊查询

模糊查询是一种特殊的条件查询，它允许我们根据模式匹配来查找符合特定条件的数据，可以使用 LIKE 关键字实现模糊查询。

在 LIKE 模糊查询中，我们使用通配符来代表零个或多个字符，从而能够快速地找到匹配的数据。

有如下 2 种通配符：

• 百分号（%）：表示任意长度的任意字符序列。
• 下划线（_）：表示任意单个字符。

模糊查询的应用场景：假设你是一名侦探，你需要根据目标人物的一部分线索信息来找到匹配的目标，比如你可以根据目标的名字中包含的关键字或字符来查找。

示例

假设有一张名为employees的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、职位（position）等：

数据表employees：

name	age	position
张三	25	软件工程师
李四	30	数据分析师
王五	28	产品经理
小明	22	软件测试工程师

现在，我们使用 LIKE 模糊查询来找出姓名（name）中包含关键字 “张” 的员工信息：

-- SQL查询语句
select name, age, position from employees where name like '%张%';

查询结果：

name	age	position
张三	25	软件工程师

还可以使用模糊查询匹配开头和结尾：

-- 只查询以 "张" 开头的数据行
select name, age, position from employees where name like '张%';

-- 只查询以 "张" 结尾的数据行
select name, age, position from employees where name like '%张';

同理，可以使用 not like 来查询不包含某关键字的信息。

逻辑运算

逻辑运算是一种在条件查询中使用的运算符，它允许我们结合多个条件来过滤出符合特定条件的数据。

在逻辑运算中，常用的运算符有：

• AND：表示逻辑与，要求同时满足多个条件，才返回 true。
• OR：表示逻辑或，要求满足其中任意一个条件，就返回 true。
• NOT：表示逻辑非，用于否定一个条件（本来是 true，用了 not 后转为 false）

示例

假设有一张名为employees的数据表，它存储了员工信息，包括员工姓名（name）、年龄（age）、工资（salary）等：

数据表employees：

name	age	salary
张三	25	10000
李四	30	12000
李五	28	15000
小明	22	8000

现在，我们使用逻辑运算来找出姓名中包含关键字 “李” 且 年龄小于 30 岁的员工信息：

-- SQL查询语句
select name, age, salary from employees where name like '%李%' and age < 30;

查询结果：

name	age	salary
李五	28	15000

上述查询语句中，我们使用 and 逻辑与运算得到了符合要求的员工信息。

去重

在数据表中，可能存在重复的数据记录，但如果我们想要过滤掉重复的记录，只保留不同的记录，就要使用 SQL 的去重功能。

在 SQL 中，我们可以使用 DISTINCT 关键字来实现去重操作。

举个应用场景：假设你是班长，要统计班级中有哪些不同的学生，而不关心他们重复出现的次数，就可以使用去重。

示例

假设有一张名为students的数据表，它存储了学生信息，包括学生姓名（name）、班级ID（class_id）、考试编号（exam_num）、成绩（score）等：

数据表students：

name	class_id	exam_num	score
张三	1	1	90
李四	2	2	85
王五	1	1	92
李四	2	3	88

现在，我们使用DISTINCT关键字来找出不同的班级 ID：

-- SQL 查询语句
select distinct class_id from students;

查询结果：

class_id
1
2

除了按照单字段去重外，DISTINCT 关键字还支持根据多个字段的组合来进行去重操作，确保多个字段的组合是唯一的。

示例语法如下：

distinct 字段1, 字段2, 字段3, ...

排序

在查询数据时，我们有时希望对结果按照某个字段的值进行排序，以便更好地查看数据。

在 SQL 中，我们可以使用 ORDER BY 关键字来实现排序操作。ORDER BY 后面跟上需要排序的字段，可以选择升序（ASC）或降序（DESC）排列。

ASC是Ascending Order的缩写，表示升序排列，即按照指定列的值从小到大排序。

DESC是Descending Order的缩写，表示降序排列，即按照指定列的值从大到小排序。

示例

假设有一张名为 students 的数据表，它存储了学生信息，包括学生姓名（name）、年龄（age）和成绩（score）等：

数据表 students ：

name	age	score
张三	18	90
李四	20	85
王五	19	92
赵六	20	88

现在，我们使用ORDER BY关键字来对学生表进行排序：

-- SQL 查询语句 1
select name, age from students order by age asc;

-- SQL 查询语句 2
select name, score from students order by score desc;

查询语句 1 结果，按照年龄升序（从小到大）：

name	age
张三	18
王五	19
李四	20
赵六	20

查询语句 2 结果，按照分数降序（从大到小）：

name	score
王五	92
张三	90
赵六	88
李四	85

在排序的基础上，我们还可以根据多个字段的值进行排序。当第一个字段的值相同时，再按照第二个字段的值进行排序，以此类推。

示例语法如下：

order by 字段1 [升序/降序], 字段2 [升序/降序], ...

截断和偏移

我们先用一个比喻来引出截断和偏移的概念。

假设你有一张待办事项清单，上面有很多任务。当你每次只想查看其中的几个任务时，会怎么办呢？

1）你可以使用手指挡住不需要看的部分（即截断）

2）根据任务的编号，直接翻到需要查看的位置（即偏移）

在 SQL 中，我们使用 LIMIT 关键字来实现数据的截断和偏移。

截断和偏移的一个典型的应用场景是分页，即网站内容很多时，用户可以根据页号每次只看部分数据。

示例

假设有一张名为 tasks 的数据表，它存储了待办事项信息，包括任务名称（task_name）和截止日期（due_date）等。

数据表tasks：

task_name	due_date
完成报告	2023-08-05
预约医生	2023-08-08
购买礼物	2023-08-10
安排旅行	2023-08-15

现在，我们使用LIMIT关键字来进行分页查询：

-- LIMIT 后只跟一个整数，表示要截断的数据条数（一次获取几条）
select task_name, due_date from tasks limit 2;

-- LIMIT 后跟 2 个整数，依次表示从第几条数据开始、一次获取几条
select task_name, due_date from tasks limit 2, 2;

查询语句 1 结果，只获取了 2 条数据：

task_name	due_date
完成报告	2023-08-05
预约医生	2023-08-08

查询语句 2 结果，从下标为 2（第 3 条）数据的位置开始获取 2 条数据：从索引为0开始

task_name	due_date
购买礼物	2023-08-10
安排旅行	2023-08-15

通过上述 SQL 查询语句，我们分别选取了待办事项表中的前两个任务和从第三个任务开始的两个任务，实现了数据的截断和偏移。

条件分支

条件分支 case when 是 SQL 中用于根据条件进行分支处理的语法。它类似于其他编程语言中的 if else 条件判断语句，允许我们根据不同的条件选择不同的结果返回。

使用 case when 可以在查询结果中根据特定的条件动态生成新的列或对现有的列进行转换。

示例

• case when 支持同时指定多个分支，示例语法如下：

CASE WHEN (条件1) THEN 结果1
	   WHEN (条件2) THEN 结果2
	   ...
	   ELSE 其他结果 END

假设有一个学生表 student，包含以下字段：name（姓名）、age（年龄）。请你编写一个 SQL 查询，将学生按照年龄划分为三个年龄等级（age_level）：60 岁以上为 “老同学”，20 岁以上（不包括 60 岁以上）为 “年轻”，20 岁及以下、以及没有年龄信息为 “小同学”。返回结果应包含学生的姓名（name）和年龄等级（age_level），并按姓名升序排序。

SELECT
  name,
  CASE
    WHEN (age > 60) THEN '老同学'
    WHEN (age > 20) THEN '年轻'
    ELSE '小同学'
  END AS age_level
FROM
  student
ORDER BY
  name asc;

name	age_level
孙加加	年轻
摸FISH	小同学
李变量	年轻
李阿巴	小同学
热dog	年轻
王加瓦	年轻
老李	年轻
赵派森	老同学
鱼皮	小同学
鸡哥	年轻

• IF语句用于根据条件执行不同的操作。语法如下：

IF(condition, value_if_true, value_if_false)

其中，condition是一个逻辑表达式，value_if_true是在条件为真时返回的值，value_if_false是在条件为假时返回的值。

例如，下面的SQL语句将根据条件返回不同的值：

SELECT IF(age > 18, '成年人', '未成年人') AS age_group FROM users;

在这个例子中，如果用户的年龄大于18岁，则返回’成年人’，否则返回’未成年人’。

函数

时间函数

在 SQL 中，时间函数是用于处理日期和时间的特殊函数。它们允许我们在查询中操作和处理日期、时间、日期时间数据，从而使得在数据库中进行时间相关的操作变得更加方便和灵活。

常用的时间函数有：

• DATE：获取当前日期
• DATETIME：获取当前日期时间
• TIME：获取当前时间
• Datediff()函数返回两个日期之间的天数

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate       "1"
SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate       "-1"

示例

假设有一个订单表 orders，包含以下字段：order_id（订单号）、order_date（下单日期）、order_time（下单时间）。数据如下：

order_id	order_date	order_time
1	2023-08-01	12:30:45
2	2023-08-01	14:20:10
3	2023-08-02	09:15:00
4	2023-08-02	18:05:30

使用时间函数获取当前日期、当前日期时间和当前时间：

-- 获取当前日期
SELECT DATE() AS current_date;

-- 获取当前日期时间
SELECT DATETIME() AS current_datetime;

-- 获取当前时间
SELECT TIME() AS current_time;

查询结果：

为了方便对比，放到同一个表格

current_date	current_datetime	current_time
2023-08-01	2023-08-01 14:30:00	14:30:00

注意，这里的日期、日期时间和时间将根据当前的系统时间来生成，实际运行结果可能会因为当前时间而不同。

还有很多时间函数，比如计算两个日期的相差天数、获取当前日期对应的毫秒数等，实际运用时自行查阅即可，此处不做赘述。

字符串处理

在 SQL 中，字符串处理是一类用于处理文本数据的函数。它们允许我们对字符串进行各种操作，如转换大小写、计算字符串长度以及搜索和替换子字符串等。字符串处理函数可以帮助我们在数据库中对字符串进行加工和转换，从而满足不同的需求。

示例

假设有一个员工表 employees，包含以下字段：id（员工编号）、name（员工姓名）。数据如下：

id	name
1	小明
2	热dog
3	Fish摸摸
4	鸡哥

1）使用字符串处理函数 UPPER 将姓名转换为大写：

-- 将姓名转换为大写
SELECT name, UPPER(name) AS upper_name
FROM employees;

查询结果：

name	upper_name
小明	小明
热dog	热DOG
Fish摸摸	FISH摸摸
鸡哥	鸡哥

2）使用字符串处理函数 LENGTH 计算姓名长度：

-- 计算姓名长度
SELECT name, LENGTH(name) AS name_length
FROM employees;

查询结果：

name	name_length
小明	2
热dog	4
Fish摸摸	6
鸡哥	2

3）使用字符串处理函数 LOWER 将姓名转换为小写：

-- 将姓名转换为小写并进行条件筛选
SELECT name, LOWER(name) AS lower_name
FROM employees;

查询结果：

id	name
1	小明
2	热dog
3	fish摸摸
4	鸡哥

聚合函数

在 SQL 中，聚合函数是一类用于对数据集进行 汇总计算 的特殊函数。它们可以对一组数据执行诸如计数、求和、平均值、最大值和最小值等操作。聚合函数通常在 SELECT 语句中配合 GROUP BY 子句使用，用于对分组后的数据进行汇总分析。

常见的聚合函数包括：

• COUNT：计算指定列的行数或非空值的数量。

• SUM：计算指定列的数值之和。

• AVG：计算指定列的数值平均值。

• MAX：找出指定列的最大值。

• MIN：找出指定列的最小值。

• MOD()函数：MOD函数用于返回两个数相除的余数，MOD(10, 3)将返回1，因为10除以3的余数是1

• ROUND() 函数用于将一个数字四舍五入为指定的小数位数。

  ROUND(number, decimals)
  

  其中，number 是要四舍五入的数字，decimals 是要保留的小数位数。如果 decimals 参数被省略，则默认为 0。

  例如，要将 123.456 四舍五入为两位小数，可以使用以下 SQL 语句：

  SELECT ROUND(123.456, 2);
  

  执行上述 SQL 语句后，将返回 123.46。

示例

假设有一个订单表 orders，包含以下字段：order_id（订单号）、customer_id（客户编号）、amount（订单金额）。数据如下：

order_id	customer_id	amount
1	A001	100
2	A002	200
3	A001	150
4	A003	50

1）使用聚合函数 COUNT 计算订单表中的总订单数：

SELECT COUNT(*) AS order_num
FROM orders;

查询结果：

order_num
4

2）使用聚合函数 COUNT(DISTINCT 列名) 计算订单表中不同客户的数量：

SELECT COUNT(DISTINCT customer_id) AS customer_num
FROM orders;

查询结果：

customer_num
3

3）使用聚合函数 SUM 计算总订单金额：

SELECT SUM(amount) AS total_amount
FROM orders;

查询结果：

total_amount
500

分组聚合

单字段分组

教程

在 SQL 中，分组聚合是一种对数据进行分类并对每个分类进行聚合计算的操作。它允许我们按照指定的列或字段对数据进行分组，然后对每个分组应用聚合函数，如 COUNT、SUM、AVG 等，以获得分组后的汇总结果。

举个例子：某个学校可以按照班级将学生分组，并对每个班级进行统计。查看每个班级有多少学生、每个班级的平均成绩。这样我们就能够对学校各班的学生情况有一个整体的了解，而不是单纯看个别学生的信息。

在 SQL 中，通常使用 GROUP BY 关键字对数据进行分组。

示例

假设有一个订单表 orders，包含以下字段：order_id（订单号）、customer_id（客户编号）、amount（订单金额）。数据如下：

order_id	customer_id	amount
1	A001	100
2	A002	200
3	A001	150
4	A003	50

1）使用分组聚合查询中每个客户的编号：

SELECT customer_id
FROM orders
GROUP BY customer_id;

查询结果：

customer_id
A001
A002
A003

2）使用分组聚合查询每个客户的下单数：

SELECT customer_id, COUNT(order_id) AS order_num
FROM orders
GROUP BY customer_id;

查询结果：

customer_id	order_num
A001	2
A002	1
A003	1

多字段分组

教程

有时，单字段分组并不能满足我们的需求，比如想统计学校里每个班级每次考试的学生情况，这时就可以使用多字段分组。

多字段分组和单字段分组的实现方式几乎一致，使用 GROUP BY 语法即可。

示例

假设有一个订单表 orders，包含以下字段：order_id（订单号）、product_id（商品编号）、customer_id（客户编号）、amount（订单金额）。

数据如下：

order_id	product_id	customer_id	amount
1	1	A001	100
2	1	A002	200
3	1	A001	150
4	1	A003	50
5	2	A001	50

要查询使用多字段分组查询表中 每个客户 购买的 每种商品 的总金额，相当于按照客户编号和商品编号分组：

-- 查询每个班级每次考试的学生人数
SELECT customer_id, product_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, product_id;

查询结果：

customer_id	product_id	total_amount
A001	1	250
A001	2	50
A002	1	200
A003	1	50

having 子句

教程

在 SQL 中，HAVING 子句用于在分组聚合后对分组进行过滤。它允许我们对分组后的结果进行条件筛选，只保留满足特定条件的分组。

HAVING 子句与条件查询 WHERE 子句的区别在于，WHERE 子句用于在 分组之前 进行过滤，而 HAVING 子句用于在 分组之后 进行过滤。

where后的条件表达式不可以使用聚合函数，而having可以

示例

假设有一个订单表 orders，包含以下字段：order_id（订单号）、customer_id（客户编号）、amount（订单金额）。数据如下：

order_id	customer_id	amount
1	A001	100
2	A002	200
3	A001	150
4	A003	50

1）使用 HAVING 子句查询订单数超过 1 的客户：

SELECT customer_id, COUNT(order_id) AS order_num
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

查询结果：

customer_id	order_num
A001	2

2）使用 HAVING 子句查询订单总金额超过 100 的客户：

-- 查询总成绩超过200的班级
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

查询结果：

customer_id	total_amount
A001	250
A002	200

查询进阶

关联查询

cross join交叉连接、笛卡尔积连接

教程

在之前的教程中，我们所有的查询操作都是在单个数据表中进行的。但有时，我们可能希望在单张表的基础上，获取更多额外数据，比如获取学生表中学生所属的班级信息等。这时，就需要使用关联查询。

在 SQL 中，关联查询是一种用于联合多个数据表中的数据的查询方式。

其中，CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积。

197. 上升的温度 - 力扣（LeetCode）

示例

假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_id	emp_name	department	salary
1	小明	技术部	5000
2	鸡哥	财务部	6000
3	李华	销售部	4500

假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

department	manager	location
技术部	张三	上海
财务部	李四	北京
销售部	王五	广州

使用 CROSS JOIN 进行关联查询，将员工表和部门表的所有行组合在一起，获取员工姓名、工资、部门名称和部门经理，示例 SQL 代码如下：

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
CROSS JOIN departments d;

注意，在多表关联查询的 SQL 中，我们最好在选择字段时指定字段所属表的名称（比如 e.emp_name），还可以通过给表起别名（比如 employees e）来简化 SQL 语句。

查询结果：

emp_name	salary	department	manager
小明	5000	技术部	张三
小明	5000	财务部	李四
小明	5000	销售部	王五
鸡哥	6000	技术部	张三
鸡哥	6000	财务部	李四
鸡哥	6000	销售部	王五
李华	4500	技术部	张三
李华	4500	财务部	李四
李华	4500	销售部	王五

inner join内连接

教程

在 SQL 中，INNER JOIN 是一种常见的关联查询方式，它根据两个表之间的关联条件，将满足条件的行组合在一起。

注意，INNER JOIN 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

示例

假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_id	emp_name	department	salary
1	小明	技术部	5000
2	鸡哥	财务部	6000
3	李华	销售部	4500

假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

department	manager	location
技术部	张三	上海
财务部	李四	北京
销售部	王五	广州
摸鱼部	赵二	吐鲁番

使用 INNER JOIN 进行关联查询，根据员工表和部门表之间的公共字段 部门名称（department） 进行匹配，将员工的姓名、工资以及所属部门和部门经理组合在一起：

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
JOIN departments d ON e.department = d.department;

查询结果如下：

emp_name	salary	department	manager
小明	5000	技术部	张三
鸡哥	6000	财务部	李四
李华	4500	销售部	王五

我们会发现，使用 INNER_JOIN 后，只有两个表之间存在对应关系的数据才会被放到查询结果中。

outer join左连接

教程

在 SQL 中，OUTER JOIN 是一种关联查询方式，它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。

在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

示例

假设有一个员工表 employees，包含以下字段：emp_id（员工编号）、emp_name（员工姓名）、department（所属部门）、salary（工资）。数据如下：

emp_id	emp_name	department	salary
1	小明	技术部	5000
2	鸡哥	财务部	6000
3	李华	销售部	4500

假设还有一个部门表 departments，包含以下字段：department（部门名称）、manager（部门经理）、location（所在地）。数据如下：

department	manager	location
技术部	张三	上海
财务部	李四	北京
人事部	王五	广州
摸鱼部	赵二	吐鲁番

使用 LEFT JOIN 进行关联查询，根据员工表和部门表之间的部门名称进行匹配，将员工的姓名、工资以及所属部门和部门经理组合在一起，并包含所有员工的信息：

SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
LEFT JOIN departments d ON e.department = d.department;

查询结果：

emp_name	salary	department	manager
小明	5000	技术部	张三
鸡哥	6000	财务部	李四
李华	4500	销售部	NULL

关注下表格的最后一条数据，李华所属的销售部并没有在部门表中，但仍然返回在了结果集中，manager 为 NULL。

有些数据库并不支持 RIGHT JOIN 语法，那么如何实现 RIGHT JOIN 呢？

其实只需要把主表（from 后面的表）和关联表（LEFT JOIN 后面的表）顺序进行调换即可！

多表联查（三表）

1280. 学生们参加各科测试的次数 - 力扣（LeetCode）

子查询（嵌套查询）

教程

子查询是指在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。

当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

打个比方，子查询就像是在一个盒子中的盒子，外层查询是大盒子，内层查询是小盒子。执行查询时，我们首先打开小盒子获取结果，然后将小盒子的结果放到大盒子中继续处理。

示例

假设我们有以下两个数据表：orders 和 customers，分别包含订单信息和客户信息。

orders 表：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

customers 表：

customer_id	name	city
101	Alice	New York
102	Bob	Los Angeles
103	Charlie	Chicago

现在，我们希望查询出订单总金额 > 200 的客户的姓名和他们的订单总金额，示例 SQL 如下：

-- 主查询
SELECT name, total_amount
FROM customers
WHERE customer_id IN (
    -- 子查询
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total_amount > 200
);

在上述 SQL 中，先通过子查询从订单表中过滤查询出了符合条件的客户 id，然后再根据客户 id 到客户信息表中查询客户信息，这样可以少查询很多客户信息数据。

上述语句的查询结果：

name	total_amount
Bob	350
Charlie	500

组合查询

教程

在 SQL 中，组合查询是一种将多个 SELECT 查询结果合并在一起的查询操作。

包括两种常见的组合查询操作：UNION 和 UNION ALL。

1. UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。
2. UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。

示例

假设我们有以下两个数据表：table1 和 table2，分别包含不同部门的员工信息。

table1 表：

emp_id	name	age	department
101	Alice	25	HR
102	Bob	28	Finance
103	Charlie	22	IT

table2 表：

emp_id	name	age	department
101	Alice	25	HR
201	David	27	Finance
202	Eve	24	HR
203	Frank	26	IT

现在，我们想要合并这两张表的数据，分别执行 UNION 操作和 UNION ALL 操作。

UNION 操作：

SELECT name, age, department
FROM table1
UNION
SELECT name, age, department
FROM table2;

UNION 操作的结果，去除了重复的行（名称为 Alice）：

name	age	department
Alice	25	HR
Bob	28	Finance
Charlie	22	IT
David	27	Finance
Eve	24	HR
Frank	26	IT

UNION ALL 操作：

-- UNION ALL操作
SELECT name, age, department
FROM table1
UNION ALL
SELECT name, age, department
FROM table2;

结果如下，保留了重复的行：

name	age	department
Alice	25	HR
Bob	28	Finance
Charlie	22	IT
Alice	25	HR
David	27	Finance
Eve	24	HR
Frank	26	IT

开窗函数

sum over

教程

在 SQL 中，开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

打个比方，可以将开窗函数想象成一种 “透视镜”，它能够将我们聚焦在某个特定的分组，同时还能看到整体的全景。

本节我们先讲第一个开窗函数：sum over。

该函数用法为：

SUM(计算字段名) OVER (PARTITION BY 分组字段名)

示例

假设我们有订单表 orders，表格数据如下：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

现在，我们希望计算每个客户的订单总金额，并显示每个订单的详细信息。

示例 SQL 如下：

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total_amount
FROM
    orders;

查询结果：

order_id	customer_id	order_date	total_amount	customer_total_amount
1	101	2023-01-01	200	320
3	101	2023-01-10	120	320
2	102	2023-01-05	350	350
4	103	2023-01-15	500	500

在上面的示例中，我们使用开窗函数 SUM 来计算每个客户的订单总金额（customer_total_amount），并使用 PARTITION BY 子句按照customer_id 进行分组。从前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单总金额。

sum over order by

教程

之前的教程中，我们讲到了 sum over 开窗函数，并且用它实现了分组统计。

本节教程我们将学习 sum over 函数的另一种用法：sum over order by，可以实现同组内数据的 累加求和 。

示例用法如下：

SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

举一个应用场景：老师在每个班级里依次点名，每点到一个学生，老师都会记录当前已点到的学生们的分数总和。

示例

假设我们有订单表 orders，表格数据如下：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

现在，我们希望计算每个客户的历史订单累计金额，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date ASC) AS cumulative_total_amount
FROM
    orders;

结果将是：

order_id	customer_id	order_date	total_amount	cumulative_total_amount
1	101	2023-01-01	200	200
3	101	2023-01-10	120	320
2	102	2023-01-05	350	350
4	103	2023-01-15	500	500

在上面的示例中，我们使用开窗函数 SUM 来计算每个客户的历史订单累计金额（cumulative_total_amount），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 order_date 进行排序。从结果的前两行可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的历史订单累计金额；相比于只用 sum over，同组内的累加列名称

rank

教程

本节我们来学习一个新的开窗函数 Rank。

Rank 开窗函数是 SQL 中一种用于对查询结果集中的行进行 排名 的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

Rank 开窗函数的常见用法是在查询结果中查找前几名（Top N）或排名最高的行。

Rank 开窗函数的语法如下：

RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column

其中，PARTITION BY 子句可选，用于指定分组列，将结果集按照指定列进行分组；ORDER BY 子句用于指定排序列及排序方式，决定了计算 Rank 时的排序规则。AS rank_column 用于指定生成的 Rank 排名列的别名。

示例

假设我们有订单表 orders，表格数据如下：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

现在，我们希望为每个客户的订单按照订单金额降序排名，并显示每个订单的详细信息。

SELECT 
    order_id, 
    customer_id, 
    order_date, 
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS customer_rank
FROM
    orders;

查询结果：

order_id	customer_id	order_date	total_amount	customer_rank
1	101	2023-01-01	200	1
3	101	2023-01-10	120	2
2	102	2023-01-05	350	1
4	103	2023-01-15	500	1

在上面的示例中，我们使用开窗函数 RANK 来为每个客户的订单按照订单金额降序排名（customer_rank），并使用 PARTITION BY 子句按照 customer_id 进行分组，并使用 ORDER BY 子句按照 total_amount 从大到小进行排序。

可以看到，开窗函数保留了原始订单的详细信息，同时计算了每个客户的订单金额排名。

存储引擎

在创建表时，指定存储引擎

默认存储引擎InnoDB

索引

索引概述

索引是一种数据结构，帮助mysql高效获取数据。数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。

索引原理

索引的存储原理大致可以概括为一句话：以空间换时间。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。

索引的优缺点

优点：

• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
• 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。

缺点：

• 索引会占据磁盘空间。
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
• 维护索引需要消耗数据库资源。

综合索引的优缺点：

• 数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!

索引的数据结构

MySQL索引使用的数据结构主要有B+Tree索引和hash索引

对于hash索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景建议选择B+Tree索引。

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持

二叉树

如果主键是顺序插入的，则会形成一个单向链表，结构如下：

二叉树缺点：顺序插入时会形成一个链表，查询性能大大降低。一个节点下最多包含两个子节点，大数据量情况下，层级较深，检索速度慢

平衡二叉树

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数 据，最终形成的数据结构也是一颗平衡的二叉树,结构如下:

**红黑树缺点：**解决了树的平衡问题，但是大数据量情况下，层级较深，检索速度慢

B-Tree

B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5 个指针：

树的度数指的是一个节点的子节点个数。

特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例

我们可以看到，两部分：

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在 hash表中。

**哈希冲突：**如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
• 无法利用索引完成排序操作
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

思考题： 为什么InnoDB存储引擎选择使用B+tree索引结构?

• 相对于二叉树，每层节点更多，层级更少，搜索效率高；
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少（一页大小固定16k），指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；B+tree双向链表便于范围搜索和排序
• 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

索引分类

分类	含义	特点	关键字
主键索引	是唯一索引的一种特殊类型,针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在数据库中，唯一索引通常用于要求某个列的值必须唯一的情况，比如用户的用户名、身份证号等；而常规索引则用于一般的索引需求，比如加速查询和连接操作等。

聚集索引&二级索引

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则:

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

索引语法

创建B+Tree索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (字段名,... );

创建索引因为是在构建B+TREE结构，也会比较慢

index_name取名规则：idx_tablename_字段名

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志-只查询超过时间指定参数的sql语句

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有 SQL语句的日志。

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log

show viriables like 'slow_query_log';

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试

systemctl restart mysqld

然后，再次查看开关情况

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

通过have_profiling 参数，能够看到当前MySQL是否支持profile操作：

SELECT @@have_profiling ;

查看profile开关是否开启

SELECT @@profiling ;

可以通过set语句在 session/global级别开启profiling：

SET profiling = 1;

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;#query_id为数字

explain

explain获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各个字段的含义:

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行)。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接 或者子查询）、PRIMARY（主查询，即外层的查询）、 UNION（UNION 中的第二个或者后面的查询语句）、 SUBQUERY（SELECT/WHERE之后包含了子查询）等
⭐type	表的连接类型，性能由好到差的连接类型为system、const、 eq_ref、ref、range、 index、all all：full table scan 将遍历全表以找到匹配的行。 index：全索引扫描 range:索引范围扫描，常出现在in（）、between、<、>、>=操作中 ref：非唯一性索引扫描，返回匹配某个单独值的所有行 const：通过索引一次就找到了，const用于比较主键索引或者唯一索引。因为只匹配一行数据，所以很快。
possible_key	显示可能应用在这张表上的索引，一个或多个。
key	实际使用的索引，如果为NULL，则没有使用索引。
key_len	表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好。
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值， 可能并不总是准确
filtered	表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

elplain是什么？有什么作用？

Explain是MySQL的关键字，显示mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

如何查看是否优化成功

1.检查执行计划中的“possible_keys”和“key”列，确认查询是否使用了正确的索引。如果没有使用到任何索引，则说明SQL语句没有被优化。

2.查看到索引优化前后type的值来判断是否优化了，还可以判断sql调优是否真正的被调优成功。

索引设计原则

• 针对于常作为**查询条件（where）、排序（order by）、分组（group by）**操作的字段建立索引。

针对以下情况不创建索引：

• 仅包含几个不同值的列
• 表中仅包含几行数据

索引失效

用上了索引并不意味着查询的时候会使用到索引，所以我们心里要清楚有哪些情况会导致索引失效，从而避免写出索引失效的查询语句。

1. 当我们使用模糊匹配的时候，也就是like%xx%会造成索引失效
2. 当我们在查询条件中对索引列做了计算count()、函数、类型转换操作，这些情况下都会造成索引失效；
3. 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。比如创建（name，id），如果光使用where id=1；的语句就会导致联合索引失效。
4. 在WHERE子句中，如果在OR前的条件列是索引列，而在OR后的条件列不是索引列，那么索引会失效。注：[id=1 or id=2]这种就不会失效 [id=1orage=32]就会失效

索引优化方法

本来创建索引就比没有创建索引要快的多，那优化了索引，比没有索引就更快了

前缀索引优化

前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

创建索引但没有优化索引：

explain SELECT * FROM products WHERE name LIKE'apple%‘ 
type:range

创建：

CREATE INDEX index_name ON products (name(5));

我们为name列创建了一个前缀长度为5的索引。这意味着MySQL只会对商品名称的前5个字符进行索引，从而减少了索引的存储空间和提高了查询的效率。

使用：

SELECT * FROM products WHERE name LIKE'apple%';

这将查询并返回商品名称以"apple"开头的所有商品信息。由于我们已经为name列创建了前缀索引，因此该查询将非常快速和有效。

证明是否优化：

explain SELECT* FROM products WHERE name LIKE'apple%';
type:range

覆盖索引优化

覆盖索引是指一个索引包含了查询所需的所有列，因此查询可以直接使用这个索引来获取所需的数据，而不必再去访问表的实际数据行。当MySQL使用覆盖索引时，它不需要读取表的实际数据，而只需要从索引中获取所需的信息，这会大大减少磁盘I/O开销，从而提升查询性能。

覆盖索引相当于进行一个二级索引查询，先把所需查询的所有列作为一个联合索引，然后能根据这个索引查询到联合索引的所有列还有二级索引叶子节点下的主键值

如果要查询所有状态为"completed"的订单的订单号和订单时间。这时候使用覆盖索引就非常有用。

未创建索引前：

SELECT order_id,order_time FROM orders WHERE status='completed';
type:all

创建：

CREATE INDEX index_orders_completed ON orders(status, order_time, order_id);
***我们把订单状态，订单号，订单时间作为一个联合索引。***

使用：

SELECT order_id,order_time FROM orders WHERE status='completed';

证明是否优化：

explain SELECT order_id,order_time FROM orders WHERE status='completed';
type:index

由于索引已经包含了所需的所有列信息，MySQL只需要从索引中读取这两列信息即可，无需进一步读取表的其他数据。这将极大地减少磁盘I/O操作，并提高查询性能。

主键索引优化

我们在建表的时候，都会默认将主键索引设置为自增的,只需要在创建id的时候设置为Auto_increment即可

ALTER TABLE student MODIFY COLUMN id INT AUTO_INCREMENT;

然后可以将id列设置为主键索引。

为什么要索引自增？

InnoDB创建主键索引默认为聚簇索引，数据被存放在了B+Tree的叶子节点上。也就是说，同一个叶子节点内的各个数据是按主键顺序存放的，因此，每当有一条新的数据插入时，数据库会根据主键将其插入到对应的叶子节点中。

如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因此这种插入数据的方法效率非常高。

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

SQL调优的方法

1.避免使用select*

select*会查询所以字段，实际业务场景中不需要所有的字段，可以不进行查询。

2.使用limit

在不需要获取全部记录的情况下，使用limit获取指定数量。

3.增量查询代替limit分页查询

select id,name,age from user limit 100,100;
type:all

mysql会查询200条，然后丢弃前面100条，这个比较浪费资源

可以优化成增量查询：

select id,name,age from user where id>100 limit 100;
type:range

4.用连接查询代替子查询

MySQL如果需要在两张以上表中查询数据的话，一般有两种实现方式子查询，连接查询

子查询可以通过in实现，优点：这样简单，但缺点是，MySQL执行子查询时，需要创建临时表，查询完成后再删除临时表，有一些额外开销。

5.控制索引数量

索引不是越多越好，索引需要额外的存储空间，B+树保存索引，额外的性能消耗。

单表索引数量尽量控制在5个以内，且单个索引字段数量控制在5个以内。

6.提升group by效率

先过滤数据，减少数据，再分组，而不能先分组再过滤数据

select id,username from user 
group by id 
having id<50;
type:index

这种写法就不好，建议以下写法：

select id,username from user 
where id<50
group by id;
type:range

7.索引优化

面试题

1.mysql你在工作中会怎么用？(70%)

​ 黑盒测试，接口测试、埋点测试、数据校验测试、白盒测试，查看开发创建的数据库索引，以及sql语句是否最优

2.在工作中会自己写一些sql语句来查询吗？写过哪些语句呢？（60%）

​ 一般不怎么使用图形化界面的工具来做增删改查，一般都是手写的mysql语句来做查询。

3.如果我要让查询出来的结果只展示4到10行，你怎么写sql语句呢？（50%）

select task_name, due_date from tasks limit 4, 6

4.左连接，右连接什么时候出现null，出现在哪个表呢？

​ 左连接：右表null

​ 右连接：左表null

5.什么是索引？（60%）

​ 索引是一种数据结构，帮助mysql高效获取数据,加快数据库的查询速度

6.什么是索引页？（20%）

​ 索引页是存储索引信息的物理页面，它包含了索引键值以及指向对应数据行的指针或者位置信息。

7.索引用的什么数据结构？为什么不用其他的数据结构呢？（30%）

• 相对于二叉树，每层节点更多，层级更少，搜索效率高；
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少（一页大小固定16k），指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；B+tree双向链表便于范围搜索和排序
• 相对Hash索引，B+tree支持范围匹配及排序操作；

8.索引分类？（20%）

• 主键索引
• 唯一索引
• 常规索引
• 全文索引
• 聚集索引
• 二级索引

9.你了解索引优化吗，索引优化有哪几种方法，分别介绍一下？（70%）

1. 前缀索引优化：取索引的前几个字符重新设置索引，让索引页能存储更多的索引，这样可以提高查询效率。
2. 覆盖索引优化：把要查询的数据列创建一个联合索引，这样可以直接从索引页中查询到我们想要的数据，减少磁盘io，可以提高查询效率。
3. 主键索引优化：将主键索引设置为自增
4. 防止索引失效：如果失效了，创建的索引就没有存在的价值了，所以写sql语句的时候不能让索引失效。

10.你作为测试，你会去做索引优化吗？（20%）

​ 如果你说没有做过，但是我自学过索引优化，平时偶尔也会看看后端sql和数据库表的索引是否为比较优的。

​ 如果你说你做过，有一次我在查询一个接口的时候，发现这个接口的数据返回有点慢，这个时候我去看了看后端的这个接口的代码，找到了对应的sql语句，我就尝试用explain关键字去查看了一下sql语句的执行计划，发现这个select的sql语句的type类型为all，原来这个接口的性能瓶颈就在这里，于是我写了一个type类型为index或者range的sql语句和索引发给了开发，让开发去改改这个sql语句，改改这个索引和sql语句。

11.简单讲讲有哪些sql调优的方法？（70%）

​ sql调优包括：索引优化、提高group by的效率、增量查询代分页查询、连接查询代替子查询，union all代替union等

12.你知道什么时候会创建索引吗？（30%）

​ 针对于常作为**查询条件（where）、排序（order by）、分组（group by）**操作的字段建立索引。

13.如何创建索引？（20%）

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (字段名,... );

14.如何查看你调优后的索引和sql语句是否被优化了。（80%）

​ 查看sql语句使用Explain关键字之后的执行计划中的type列的值

15.索引以及sql调优的sql语句是开发去数据库改呢还是测试去改呢？你去改过吗？（20%）

​ 开发去改，测试不能去懂数据库的表结构或是索引，除非测试比较懂数据库了才行。我没有改过或者说我改过测试环境的数据库索引和sql，但是都是有备份的。

16.你说说sql优化和索引调优的区别？（10%）

​ 索引调优是对索引的优化，让其可以查询数据查询得更快。

​ Sql优化是对sql语句的结构的优化和对索引的优化，索引优化属于sql优化，sql优化是一个更加宽泛的定义。

17.什么是慢查询？（40%）

​ 一个查询语句响应时间大于几百毫秒或者几秒这种情况一般都是慢查询

18.Mysql调优步骤？

​ 1、Mysql调优可以加索引，因为不加索引就会很慢，加了索引再去使用索引，那么查询效率会更高，但是呢，你加的索引还可以优化以下，让索引的查询效率更高，这个时候就要去优化索引的结构。

​ 2、当我们既加了索引，又把索引优化到最佳状态的时候，发现你写的sql语句查询效率还是很低，那这个时候就要去优化你的sql语句结构，比如把分页查询优化成增量查询。

​ 3、偶尔也会遇到慢查询的时候，这时候我们要检查慢查询的原因，再优化慢查询，可以使用索引优化，修改表结构，修改数据库配置等方法优化慢查询。