SQL学习笔记

SQL

SQL脚本执行流程

先查询缓存，有则返回结果。没有就经过SQL解析器、预处理、优化器生成对应的执行计划，然后调用存储引擎执行，返回结果。

执行顺序

sql执行顺序：from (=> on => join) => where => group by => having => select => distinct => order => limit（但是在mysql中，having后可以用select中的别名）=> union /union all

distinct：distinct 必须放在select 后面第一位；
where：筛选行；不可用聚合函数；
group by：ONLY_FULL_GROUP_BY模式中，group by后的字段需要是主键或者有唯一非空性。
having：筛选组（原始表不适用group by相当于整个表是一个组）；可以用聚合函数；

Sql题思路

为了解决问题，你希望表是什么样子的

反向思维：最终表结构---->中间表结构----->原始表结构。一般最终表结构就是问题所需要的字段。问题变成由原始表怎么经过变换后形成最终表结构。你需要构造什么样子的中间表来帮助你实现这个过程。

1. 公式。为了得到结果，对字段使用什么样的公式才能解决。
2. 函数。使用函数来满足你的公式。
3. 分组。怎么分组才能满足，也有可能不需要分组。
4. 整合。如果我们只求一个字段，那么很简单，你一定可以写出来。大多数情况下不止让你求一个字段。所以需要有个整合的过程。即你所写的这些公式和函数，能不能在一个组内实现。

（为了实现高效简洁，最好在一个语句中实现。）

例如：统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。（网约车）

拆解问题：每个城市，评分最高，司机，平均评分，日均接单量，日均行驶里程数。

语句理解：城市和司机、评分最高是属于限定词，后面的字段都是在此基础。评分最高是修饰司机得，可最后用where过滤的，所以这个最起码的分组得是城市和司机。

最低分组：（城市&司机）。

• 评分最高：意思是总分最高。sum函数求总分。然后取最大值，可以用排名或者max。如果用排名，只需要按（城市&司机）分组，然后排序就可以，如果按max，需要按（城市&司机）分组后，再按（城市）分组。
  因为是聚合函数，所以需要考虑怎么分组，前提有每个城市的司机，所以可以按（城市&司机）分组。
• 平均评分：根据总分用avg函数，同样考虑怎么分组，可以按（城市&司机）分组。
• 日均接单量：总接单数/天数，个数统计一般用count，总接单数用count函数，按（城市&司机）分组，天数用count函数，不能只按（城市&司机）分组，因为有一天可以接多个单，在数据中会有很多个重复日期。所以他的分组需要按日期去重（城市&司机）。
• 日均行驶里程数：和日均接单量类似。总里程用count函数，按（城市&司机）分组，天数需要（城市&司机）。

评分最高：用（城市&司机）分组，然后排序一步到位，或者分两步，按（城市&司机）分组、按城市分组使用max。
平均评分：（城市&司机）分组。
日均接单量：（城市&司机）分组，日期去重（城市&司机）分组。
日均行驶里程数：（城市&司机）分组，日期去重（城市&司机）分组。

整合：能不能放一起处理。如果使用count（distinct ）那么日均接单量、日均行驶里程数就可以按（城市&司机）分组。那么评分最高也选择（城市&司机）分组，再排序取第一个就可以了。

select sum(grade) sum_grade
		,avg(grade) avg_grade
		,count(order)/count(distinct date(day)) day_order_num
		,count(mileage)/count(distinct date(day)) day_mileage_num
from table
group by city,driver
order by sum_grade
limit 1

窗口函数

函数名() Over(partition by 分区字段 order by 排序字段 <desc/asc> <rows/range> between 开始 and 结束)

排名窗口函数	含义
row_number()	不可重复，顺序排名
rank()	可重复排名，跳跃式排名
dense_rank()	可重复排名，顺序排名

LAG 函数：

LAG(column, offset, default) 函数返回在当前行之前指定偏移量位置的行的值。其中，column 是要获取值的列，offset 是偏移量（默认为1，即前一行），default 是当没有前一行时的默认值。
举例：如果你有一个包含销售数据的表，可以使用 LAG 函数来计算每个产品在上个月的销售数量。

LEAD 函数：
LEAD(column, offset, default) 函数返回在当前行之后指定偏移量位置的行的值。参数与 LAG 函数类似。
举例：如果你有一个包含温度数据的表，可以使用 LEAD 函数来计算每个时间点之后的温度值。

rows between和range between开始和结束字段；当在开窗函数使用聚合时，如果排序的值有重复，相同值值会相同，可以用rows和range限制。

代码	含义
Current Row	当前行
N preceding	前 n 行，n 为数字， 比如 2 Preceding 表示前2行
unbounded preceding	开头
N following	后N行，n 为数字， 比如 2 following 表示后2行
unbounded following	结尾

行转列：pivot 、max(if())、sum(if())

select * from t pivot(聚合函数 for 原始列名 in (行转换后的列名（列表）))
select * from t pivot(sum(score) for subject in ('math','english','chinese'))

列转行：unpivot、union all

select * from t unpivot(值名 for 列转行后的列名 in (原始列名（列表）))
select * from t unpivot(score for subject in ('math','english','chinese'))

爆炸列

lateral view explore() 表别名 as 列别名（一个列中有多个字段，将它拆分开）

时间函数

时间戳：

SELECT UNIX_TIMESTAMP('2017-05-15 10:37:14');-- 指定日期时间的时间戳：1494815834
SELECT FROM_UNIXTIME(1494815834,'%Y-%m-%d %H:%i:%s')
SELECT TIMESTAMPDIFF(HOUR, '2017-06-01 08:12:25', '2016-06-15 00:00:00');-- -8432

日期：

select DATE_FORMAT('日期','%Y-%m-%d %H:%i:%s');--日期格式更改
SELECT YEAR('2017-05-15 10:37:14.123456');-- 获取年份
SELECT MONTH('2017-05-15 10:37:14.123456');-- 获取月份
SELECT DAY('2017-05-15 10:37:14.123456');-- 获取日
SELECT HOUR('2017-05-15 10:37:14.123456');-- 获取时
SELECT MINUTE('2017-05-15 10:37:14.123456');-- 获取分
SELECT SECOND('2017-05-15 10:37:14.123456');-- 获取秒

SELECT DATE_ADD('2017-05-15 10:37:14',interval 1 day);-- 日期加法
SELECT DATE_SUB('2017-05-15 10:37:14',interval 2 second); -- 日期减法

星期：

select WEEKDAY(now()) --0为周一，1为周二

日期加减：

# 日期增加1天
select date_add(now(), INTERVAL 1 day);
# 时间减少1小时（前一小时）
select date_sub(now(), INTERVAL 1 hour);

redo和undo？
redo：redo log是用来恢复数据的，用于保障已提交事务的持久化特性。
undo：undo log是用来回滚数据的，用于保障未提交事务的原子性。

A：事务的原子性是通过undolog来实现。
C：事务的一致性是通过原子性，持久性，隔离性共同实现
I：事务的隔离性是通过(读写锁+MVCC)来实现
D：事务的持久性性是通过redolog来实现

慢查询：

1. 索引没起作用：like%，多列索引没有第一个字段
2. 优化数据库结构：将字段很多的表分解成多个表（拆）；创建中间表（经常联合查询）
3. 分解关联查询；join的查询，可以分解成多个查询。
4. 优化limit页，先根据索引查找id，然后根据id继续查找符合条件的值。

常用函数

substr(字符串，开始位置（从1数），长度)

SQL底层

索引冲突：

原因：
索引列没有唯一约束。如果一个列没有唯一约束，那么在查询时可能会出现多个匹配的结果，导致索引冲突。

索引列的数据类型不兼容。如果索引列的数据类型与查询条件中的数据类型不兼容，那么在查询时也可能会出现索引冲突。

索引列的顺序不合理。如果索引列的顺序不合理，那么在查询时可能会出现多个匹配的结果，导致索引冲突。

处理:
使用唯一索引。如果一个列有唯一约束，那么最好为该列创建一个唯一索引，以避免出现多个匹配的结果。

避免创建嵌套索引。如果一个索引列定义了多个聚簇索引或非聚簇索引，那么这些索引会相互干扰，导致查询性能下降。因此，尽量避免创建嵌套索引。

使用WHERE子句。在使用WHERE子句进行查询时，可以使用NOT IN或LEFT JOIN等方法来过滤掉不需要的结果，避免与已有索引发生冲突。

优化查询语句。同时，可以优化查询语句的结构，使用更加简洁高效的表达方式。