MYSQL笔记（全）__窗⼝函数FIRST 阶段⼀
⼀、MYSQL中查询五⼦句
lect*from数据表①where⼦句
②group by分组⼦句
③having⼦句
④order by⼦句
⑤limit⼦句
注意事项：①②③④⑤五⼦句是SQL中最重要的五个⼦句，⽽且其顺序不能颠倒
⼆、查询语句
lect*from tb_student;
lect id,name,address from tb_student;
1、回顾where⼦句
（1）等于 = ; 不等于 !=,<>
# 查询tb_student表中姓名为‘貂蝉’的同学的所有信息
lect*from tb_student where name ='貂蝉'；
# 查询tb_student表中姓名不为‘貂蝉’的同学的所有信息
lect*from tb_student where name !='貂蝉';
lect*from tb_student where name <>'貂蝉';
# 查询tb_student表中年龄⼤于20岁的同学信息
lect*from tb_student where age >20;
本草中华第一季（2）查询结合与and、或or、⾮not
# 查询年龄为34岁且性别为male的同学信息
lect*from tb_student where age =34and gender ='male';
# 查询id编号为1或3或5的同学信息
lect*from tb_student where id =1or id =3or id =5;
绝经的症状表现有哪些
# 查询id编号不为1或3或5的同学信息
lect*from tb_student where id not in(1,3,5);
（3）where⼦句与like模糊查询，like模糊查询有两个符合 %百分号和 _下划线
# %代表任意个任意字符
# _代表任意的某个字符（只能表⽰⼀个字符）
# 查询班级中姓“孙”的同学信息
lect*from tb_student where name like'孙%';
# 查询班级中姓'刘'且姓名为两位数的同学的信息
lect*from tb_student where name like'孙_';
# 查询班级中以'婵'字结尾的同学信息
lect*from tb_student where name like'%婵';
# 查询班级中包含'乔'字的同学信息
lect*from tb_student where name like'%乔%';
（4）空值判断（NULL）
# 查询age年龄为NULL的同学信息
理工科专业lect * from tb_student where age is null;
# 查询age年龄不为NULL的同学信息
lect * from tb_student where age is not null;
2、order by ⼦句，（排序⼦句，升序12345、降序54321）
# order by 不仅可以对数字进⾏排序，还可以对字母进⾏排序
# (升序12345，降序54321)
# order by 字段名称 asc
# order by 字段名称 desc
# 查询班级中所有同学信息，按年龄从⼩到⼤排序
lect*from tb_student order by age asc;
# 注-在排序时，null⼀般被认为是最⼩值
# 多个字段进⾏排序，先排order最左侧的字段，当字段值相同时，则继续按右侧的字段进⾏排序（了解）lect*from tb_student order by age asc,height asc;
3、limit⼦句（五⼦句的最后⼀个）
# 只要最⾼的
lect*from tb_student order by age desc limit1;
# 要第⼆第三
lect*from tb_student order by age desc limit1,2;
三、常见的聚合函数
2.max(col): 表⽰求指定列的最⼤值
3.min(col): 表⽰求指定列的最⼩值
4.sum(col): 表⽰求指定列的和
5.avg(col): 表⽰求指定列的平均值
# 求班级中所有同学的数量
lect count(id)from tb_student;
# 求班级中年龄最⼤值
lect max(age)from tb_student;
# 求班级中年龄最⼩值
lect min(age)from tb_student;
# 求班级中所有年龄总和
lect sum(age)from tb_student;
# 求班级中年龄平均值
lect avg(age)from tb_student;
# 扩展对null处理的函数ifnull（列名，默认值），如果某个列的值为null，则系统绘⾃动将其设置为右边的默认值# 求班级中所有同学的平均值
lect avg(age)from tb_student;
lect avg(ifnull(age,0)from tb_student;
四、分组函数
# group by 分组⼦句：顾名思义，要把分析的数据通过某种形式进⾏分组，如：按学科、性别、车次、部门等
# 为什么要进⾏分组？为了更好地进⾏数据地统计操作（group by结合5个聚合函数）
lect*from tb_student group by gender;------------错误
# > 1055 - Expression #1 of SELECT list is not in GROUP BY clau and contains nonaggregated column 'db_itheima.tb_student.id' which is not functionall y dependent on columns in GROUP BY clau; this is incompatible with sql_mode=only_full_group_by
lect x from tb_student group by x;
# 统计班级中男⼥同学的⽐例（男同学多少⼈，⼥同学多少⼈）
lect count(id)from tb_student where gender ='male';
lect count(id)from tb_student where gender ='female';
hobbieslect gender,count(*)from tb_student group by gender;
# lect从数据表中读取数据就是⼀个循环遍历的过程，⾸先读取第⼀组数据，然后读取第⼆条数据，以此类推--直⾄数据遍历结束
# 求每个学科中，年龄最⼤的
lect subject,max(age)from tb_student group by subject;
# having⼦句，对查询的结果进⾏过滤和筛选
# 普通查询中，having⼦句可以替代where⼦句（含义相同）
lect*from tb_student where age >20;
lect*from tb_student having age >20;
# 在有group by分组⼦句中，having和where有很⼤的不同
# 对分组后的结果进⾏过滤和筛选
# 根据gender字段进⾏分组，统计分组条数⼤于2条以上的分组信息
lect gender,count(id)from tb_student group by gender having count(id)>2;
SECONDE阶段⼆
⼀、MySQL8.0窗⼝函数概述
# ØMYSQL 8.0 之后，加⼊了窗⼝函数功能，简化了数据分析⼯作中查询语句的书写
# 在没有窗⼝函数之前，我们需要通过定义临时变量和⼤量的⼦查询才能完成的⼯作，使⽤窗⼝函数会更加简洁⾼效
# 窗⼝函数是数据分析⼯作中必须掌握的⼯具，在SQL笔试中也是⾼频考点
1、什么是窗⼝函数
文眼
窗⼝函数是类似于可以返回聚合值的函数，例如SUM()，COUNT()，MAX()。但是窗⼝函数⼜与普通的聚合函数不同，它不会对结果进⾏分组，使得输出中的⾏数与输⼊中的⾏数相同。
2、窗⼝函数长这样
SELECT SUM()OVER(PARTITION BY ___ ORDER BY___)FROM Table
聚合功能：在上述例⼦中，我们⽤了SUM()，但是你也可以⽤COUNT(), AVG()之类的计算功能
PARTITION BY：你只需将它看成GROUP BY⼦句，但是在窗⼝函数中，你要写PARTITION BY
ORDER BY：ORDER BY和普通查询语句中的ORDER BY没什么不同。注意，输出的顺序要仔细考虑
3、窗⼝函数的优点（⼩结）
简单
窗⼝函数更易于使⽤。在上⾯的⽰例中，与使⽤聚合函数然后合并结果相⽐，使⽤窗⼝函数仅需要多⼀⾏就可以获得所需要的结果。
快速
这⼀点与上⼀点相关，使⽤窗⼝函数⽐使⽤替代⽅法要快得多。当你处理成百上千个千兆字节的数据时，这⾮常有⽤。
多功能性
最重要的是，窗⼝函数具有多种功能，⽐如，添加移动平均线，添加⾏号和滞后数据，等等。
⼆、窗⼝函数基本语法
窗⼝函数基本语法
<window_function>OVER(...)
/*
<window_function>：这⾥可以是我们之前已经学过的聚合函数，⽐如（`COUNT()`, `SUM()`, `AVG()` 等）。也可以是其他函数，⽐如ranking 排序函数，分析函数等，后⾯的课程中会介绍。
OVER(...)：窗⼝函数的窗框通过`OVER(...)` ⼦句定义，窗⼝函数中很重要的部分就是通过`OVER(...)` 定义窗框 (开窗⽅式和⼤⼩)
*/
三、窗⼝函数中OVER()⼦句详解
over（）基本语法
Ø⾸先看`OVER(...)`的最基本⽤法：`OVER()`意思是所有的数据都在窗⼝中，看下⾯的SQL
SELECT
first_name,
last_name,
salary,
AVG(salary)OVER()
FROM employee;
# SQL并不复杂，主要看跟 `OVER()` 相关的部分
AVG(salary)OVER()
# `AVG(salary)` 意思是要计算平均⼯资，加上 `OVER()` 意味着对全部数据进⾏计算，所以就是在计算所有⼈的平均⼯资。
案例演⽰
# 创建报表，除了查询每个⼈的⼯资之外，还要统计出公司每⽉的⼯资⽀出
SELECT
first_name,
last_name,
salary,
SUM(salary)OVER()
FROM employee;蒸饺做法
# 查询结果：
# 统计采购表中的平均采购价格，并与明细⼀起显⽰（每件物品名称，价格）
SELECT
item,
price,
AVG(price)OVER()
FROM purcha;
# 通常，`OVER()`⽤于将当前⾏与⼀个聚合值进⾏⽐较，例如，我们可以计算出员⼯的薪⽔和平均薪⽔之间的差。
SELECT
first_name,
last_name,
salary,
AVG(salary)OVER(),
salary -AVG(salary)OVER()as difference
FROM employee;
# 上⾯查询结果的最后⼀列显⽰了每名员⼯的⼯资和平均⼯资之间的差额，这就是"窗⼝函数的典型应⽤场景：将当前⾏与⼀组数据的聚合值进⾏⽐较"。/*
挽回妻子
需求：创建报表统计每个员⼯的⼯龄和平均⼯龄之间的差值。
报表中包含如下字段：
员⼯的名字，员⼯的姓⽒，员⼯的⼯龄，所有员⼯的平均⼯龄，员⼯⼯龄和平均⼯龄之间的差值
*/
SELECT
first_name,
last_name,
years_worked,
AVG(years_worked)OVER(),
years_worked -AVG(years_worked)OVER()as difference
FROM employee;
# 接下来我们看⼀下， `OVER()` 与 `COUNT()` 如何组合使⽤：
SELECT
地奥心血康id,
name,
COUNT(id)OVER()
FROM department
ORDER BY name ASC;
# 在上⾯的SQL中，我们查询出每个部门的 `id` 和 `name` 部门名称，以及所有部门的数量，最后通过部门名字排序。
# 需要注意：窗⼝函数在`WHERE` ⼦句后执⾏！
SELECT
first_name,
last_name,
salary,
AVG(salary)OVER(),
salary -AVG(salary)OVER()
FROM employee
WHERE department_id =1;
SELECT
first_name,
last_name,
salary,
AVG(salary)OVER()as avg
FROM employee
WHERE department_id IN(1,2,3);
四、OVER( PARTITION BY )的使⽤
