MySQL

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;