MySQL基础（3）

MySQL索引

概念

• 索引就是快速查询数据的数据结构
• 类似于书中的目录, 通过目录就可以快速查找到想要查看的内容
• 分类
  • 单列索引: 表中的一个字段作为索引列
  • 组合索引: 表中的多个字段作为索引列
  • 普通索引
  • 唯一索引
• 创建索引的注意点
  • 不能随意创建索引, 索引也是保存到mysql数据表中, 占存储空间
  • 插入,修改,更新数据 索引也是需要进行维护的, 如果索引太多, 维护会很复杂
  • where条件中经常使用的字段可以创建对应的索引
  • 查询过程中查询速度比较慢可以创建索引
• 索引的优缺点
  • 优点：提高查询效率
  • 缺点：降低增删改效率

索引操作

# 创建索引
# 方式一: 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';

事务

1. MySQL三种引擎

• 默认innodb是支持事务的
• 事务要么全成功，要么全失败

2. 事务功能

• 用于管理insert，update，delete

• 回滚到事务开始前的状态

3. 事务的特点

• 原子性
• 一致性
• 隔离性
• 持久性

开始事务：begin

事务提交：commit

事务回滚：rollback

窗口函数

• 分组聚合

# 统计各部门总工资金额, 查询字段部门id, 总金额
SELECT
    deptid,
    SUM(salary) total_salary
FROM
    employee
GROUP BY
    deptid;

• 开窗函数：组内分组，组内排序
• 只能对聚合函数开窗
• 语法: 开窗函数 over(partition by 分组字段1, 分组字段2, … order by 排序字段1 排序方式, …)
• 窗口规定结果表的数据行, 不会改变结果表的条目数
• partition by: 将分组聚合的结果添加到各组的数据后
• order by: 对各组的数据进行排序
• MySQL5.0版本：定义变量，循环实现

# 使用场景: 查询各组的第一名的数据信息,  统计各组员工的工资占总工资的比例

# 统计各部门总工资金额,并且查询部门中每个人的所有信息
# 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;

小结：

• sum()，结合group by，over,括号内case when/if
• select sum()与select sum() over ()区别
  • sum()是对表中某一字段求和，结果只有一个值
  • sum() over () 也是对全表的某一个字段求和，结果有一列值（都是最大值）
  • sum想要实现sum over的效果 可以笛卡尔积
• group by 某个字段之后也可以对其他字段分组后求和select 字段，sum(其他字段)
• 想对全表计数或求和，count/sum() over ()
• group by 将字段相同的分成一组

• 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;

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;

小结：

case when 可以自定义分组
group by 根据某个字段不同值分组
group by 可以使用case when自定义分组，如果要输出分组后的计算值，case when还要在select里写一遍
case when 可以和聚合函数组合使用
group by 和聚合函数count 和 case when/if 组合使用可以对group by分组后的成员进一步分组，group by 分组以某一字段不同行呈现，count(if/case when)以不同列（字段）呈现
case when … then … when … then …else… end 可以给不同自定义的分组和该列起别名
count(case when … then …else … end) ，count(if(判断条件,成立,不成立)) 可以创建多个列
聚合函数，case when ，开窗函数都可以创建列
将count变为sum then后面不能用主键字段名 要改成1
count中不计数的用null，sum中不计数的用0

group by分组sum聚合后可以使用聚合的函数或别名排序
注：count中不计数不能用0
涉及到不同值的累加，SUM不能通过COUNT计数替代
case when 里面也可以使用聚合函数
1.条件分支，匹配条件的返回一个值
2.添加新列，自定义分组
3.group by子句根据case when分组，select也必须含有同样的case when分组
4.在COUNT()或SUM()函数内使用CASE WHEN来创建业务对象的自定义计数

mysql函数

• 函数: 实现特定功能的代码段

• 使用函数可以快速的实现计算

  • sum() 求和

• 经常使用的函数

  • 字符串函数

    • 字符串拼接：concat
    • 指定字符拼接：concat_ws
    • 格式化：format()
    • 替换：replace
    • 截取字符串：substr/substring
    • 从第几个分隔符开始截取：substring_index

  • 日期函数

    • 两个日期作差：datediff
    • date_sub
    • date_add
    • 按表达式要求显示日期：date_format
    • last_date
    • 返回当天日期：curdate
    • 返回当前年月日时分秒：current_time /now
    • 字符串转日期：str_to_time

  • 数值函数

    • 最大：max
    • 最小：min
    • 求和：sum
    • 计数：count
    • 平均值：avg
    • 四舍五入保留小数位：round
    • 绝对值：abs
    • 整除：div
    • 向上取整：ceil
    • 向下取整：floor
    • 一行数据的最大值：greatest
    • 取余：mod

  • 其他函数

    • case

    • if

    • ifnull

sql执行语句的顺序

select 查询内容

from 表

where 分组前的条件

group by 分组字段

having 分组后的条件

order by 排序字段

limit [m,]n

from > where > group by > 函数计算 > having > select > order by > limit