1 MySQL索引
2.1 概念
-
索引就是快速查询数据的数据结构
-
类似于书中的目录, 通过目录就可以快速查找到想要查看的内容
-
分类
-
单列索引: 表中的一个字段作为索引列
-
组合索引: 表中的多个字段作为索引列
-
-
创建索引的注意点
-
不能随意创建索引, 索引也是保存到mysql数据表中, 占存储空间
-
插入,修改,更新数据 索引也是需要进行维护的, 如果索引太多, 维护会很复杂
-
where条件中经常使用的字段可以创建对应的索引
-
查询过程中查询速度比较慢可以创建索引
-
2.2 索引操作
# 创建索引
# 方式一: create (unique) index 索引名 on 表名 (列名1 排序方式, 列名2 排序方式, ...)
# 向category表中以cname字段创建对应索引
CREATE INDEX idx_cname ON category (cname);
# 方式二: 通过修改表结构添加索引
# alter table 表名 add index 索引名 (列名1 排序方式, 列名2 排序方式, ...)
# 向products表中以price字段创建对应索引
ALTER TABLE products
ADD INDEX idx_price (price DESC);
# 方式三: 创建表时,创建索引
CREATE TABLE category2
(
cid VARCHAR(20) PRIMARY KEY,
cname VARCHAR(200),
INDEX idx_cname (cname) -- 创建索引
);
# 删除索引
# 方式一: DROP INDEX 索引名 ON 表名;
DROP INDEX idx_cname ON category2;
# 方式二: ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE products
DROP INDEX idx_price;
# 展示索引
SHOW INDEX FROM category;
# 索引信息存储在mysql库中的innodb_index_stats表中
SELECT *
FROM
mysql.innodb_index_stats
WHERE
database_name = 'bigdata_db'
AND table_name = 'category'
AND index_name = 'idx_cname';
2 窗口函数
-
创建表和插入数据
# 创建表和插入数据
CREATE TABLE employee
(
empid INT,
ename VARCHAR(20),
deptid INT,
salary DECIMAL(10, 2)
);
INSERT INTO
employee
VALUES
(1, '刘备', 10, 5500.00);
INSERT INTO
employee
VALUES
(2, '赵云', 10, 4500.00);
INSERT INTO
employee
VALUES
(2, '张飞', 10, 3500.00);
INSERT INTO
employee
VALUES
(2, '关羽', 10, 4500.00);
INSERT INTO
employee
VALUES
(3, '曹操', 20, 1900.00);
INSERT INTO
employee
VALUES
(4, '许褚', 20, 4800.00);
INSERT INTO
employee
VALUES
(5, '张辽', 20, 6500.00);
INSERT INTO
employee
VALUES
(6, '徐晃', 20, 14500.00);
INSERT INTO
employee
VALUES
(7, '孙权', 30, 44500.00);
INSERT INTO
employee
VALUES
(8, '周瑜', 30, 6500.00);
INSERT INTO
employee
VALUES
(9, '陆逊', 30, 7500.00);
# 分组聚合
# 统计各部门总工资金额, 查询字段部门id, 总金额
SELECT
deptid,
SUM(salary) total_salary
FROM
employee
GROUP BY
deptid;
-
基本使用
# 开窗函数
# 语法: 开窗函数 over(partition by 分组字段1, 分组字段2, ... order by 排序字段1 排序方式, ...)
# 窗口规定结果表的数据行, 不会改变结果表的条目数
# partition by: 将分组聚合的结果添加到各组的数据后
# order by: 对各组的数据进行排序
# 使用场景: 查询各组的第一名的数据信息, 统计各组员工的工资占总工资的比例
# 统计各部门总工资金额,并且查询部门中每个人的所有信息
# PARTITION BY: 分组 类似与group by
SELECT *,
SUM(salary) OVER () total_salary, -- 所有人的总工资
AVG(salary) OVER () avg_salary, -- 所有人的平均工资
avg(salary) OVER (PARTITION BY deptid) dept_avg_salary, -- 各部门平均工资
avg(salary) OVER (PARTITION BY deptid order by salary DESC) dept_avg_salary -- 各部门平均工资, 根据工资字段对各组数据进行排序
FROM
employee;
# 查询所有员工信息
SELECT *
FROM
employee;
# 查询所有员工的总工资
SELECT
SUM(salary) total_salary
FROM
employee;
# 多表连接实现开窗函数需求
# 交叉连接
SELECT *
FROM
employee, (SELECT
SUM(salary) total_salary
FROM
employee) total;
-
TOPN代码实现
# 开窗函数实现查询各组TOPN
/*
实现步骤:
第一步: 通过开窗函数,计算一个各组排序之后的序号列, 结果表中有一列是序号列
第二步: 查询结果表,过滤序号列=1(最大/最小), <=3(前三/后三名)
*/
/*
row_number(): 序号列的值连续不重复 1,2,3,4,5
rank(): 序号列的值重复不连续 1,2,2,4,4,6
dense_rank(): 序号列的值重复且连续 1,2,2,3,4,4,5
*/
# 生成带有序号列的结果表
SELECT *,
ROW_NUMBER() OVER (PARTITION BY deptid ORDER BY salary DESC) row_rk,
RANK() OVER (PARTITION BY deptid ORDER BY salary DESC) rk,
DENSE_RANK() OVER (PARTITION BY deptid ORDER BY salary DESC) dense_rk
FROM
employee;
# 根据结果表中的序号列进行过滤, 获取TOPN数据
SELECT *
FROM
(SELECT *,
ROW_NUMBER() OVER (PARTITION BY deptid ORDER BY salary DESC) row_rk,
RANK() OVER (PARTITION BY deptid ORDER BY salary DESC) rk,
DENSE_RANK() OVER (PARTITION BY deptid ORDER BY salary DESC) dense_rk
FROM
employee) temp
WHERE
row_rk = 1;
3 mysql函数
-
函数: 实现特定功能的代码段
-
使用函数可以快速的实现计算
-
sum() 求和
-
-
经常使用的函数
-
字符串函数
-
concat
-
concat_ws
-
replace
-
substr/substring
-
substring_index
-
-
日期函数
-
datediff
-
date_sub
-
date_add
-
date_format
-
last_date
-
查看当前日期时间
-
-
数值函数
-
max
-
min
-
sum
-
count
-
avg
-
round
-
abs
-
-
其他函数
-
cast
-
if
-
ifnull
-
-
4 case when语法
-
基本语法
select
case
when 条件 then 返回的值(列名)
when 条件 then 返回的值(列名)
...
else 返回的值(列名)
end as 列别名
from 表名
-
使用场景
-
对某列/某些列进行判断,生成一列新的类别列
-
# - 需求: 创建一个报表,统计员工的经验水平
# - 显示字段:`first_name`, `last_name`, `hire_date`, 和 `experience`
# - 经验字段(`experience` ):
# - `'junior'` 2014年1月1日以后雇用的员工
# - `'middle'` 在2013年1月1日之后至2014年1月1日之前雇用的员工
# - `'senior'` 2013年1月1日或之前雇用的员工
SELECT
first_name,
last_name,
hire_date,
CASE
WHEN hire_date >= '2014-01-01' THEN 'junior'
WHEN hire_date >= '2013-01-01' AND hire_date < '2014-01-01' THEN 'middle'
WHEN hire_date < '2013-01-01' THEN 'senior'
END experience
FROM
employees;
SELECT
first_name,
last_name,
hire_date,
CASE
WHEN hire_date >= '2014-01-01' THEN 'junior'
WHEN hire_date >= '2013-01-01' AND hire_date < '2014-01-01' THEN 'middle'
ELSE 'senior'
END experience
FROM
employees;