一、重要函数(别名:记不住函数)
1、时间和日期处理函数
MySQL基础查询篇(8)-日期和时间函数的应用 - 知乎 (zhihu.com)
DATE_ADD("2022-01-01", INTERVAL 1 YEAR)
DATEDIFF('week',"2022-01-01", "2021-12-31")
2、聚合窗口函数——OVER
OVER ([PARTITION BY column][ORDER BY culumn][<ROWS orRANGE clause>BETWEEN <Start expr>AND <End expr>])
SELECT *,
SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
SUM(Salary) OVER(ORDER BY ID) 累计工资,
SUM(Salary) OVER() 总工资
from Employee
来自 SQL窗口函数OVER详细用法,一学就会-CSDN博客
OVER开窗函数必须与聚合函数或排序函数一起使用,聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()五个。排序函数【窗口函数】一般指RANK(),ROW NUMBER(),DENSE_RANK(),NTILE()等。
3、窗口函数
3.1、排序函数:
- ROW_NUMBER()是排序,当存在相同成绩的学生时,ROW_NUMBER()会依次进行排序,他们序号不相同
- Rank()如果出现相同的,他们的排名是一样的。【记忆:排名要公平】
- DENSE_RANK()排名是连续的【记忆:dense浓所以不跳跃】,RANK()是跳跃的排名
- NTILE()函数是将有序分区中的行分发到指定数目的组中,各个组有编号,编号从1开始
3.2、滑动窗口函数——rows&range
[<ROWS orRANGE clause>BETWEEN <Start expr>AND <End expr>]
ROWS: 表示按照行的范围进行定义框架,根据order by子句排序后,取的前N行及后N行的数据计算(与当前行的值无关,只与排序后的行号相关)。常用:rows n perceding表示从当前行到前n行(一共n+1行)
RANGE:表示按照值的范围进行定义框架,根据order by子句排序后,指定当前行对应值的范围取值,行数不固定,只要行值在范围内,对应行都包含在内。适用于对日期、时间、数值排序分组
来自MYSQL窗口函数(Rows & Range)——滑动窗口函数用法_mysql滑动窗口函数-CSDN博客
感觉比lag()方便
3.3lag()函数
Lag()可以用于在查询结果集中访问前一行数据。即获取前一行的数据。
该函数可用于计算当前行和前一行之间的差异,或者将前一行的数据与当前行数据进行比较。
LAG(expression [,offset] [,default]) OVER ( [partition_by_clause] order_by_clause )
SQL Server LAG() 函数使用指南 (sjkjc.com)
3.4LEAD()函数
LEAD (scalar_expression [, offset] [, default]) OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC|DESC], ... )
4、REGEXP
# \\表示转义字符 转标点符号
例:select * from Users where mail regexp '^[a-zA-Z][a-zA-Z0-9\\_\\.\\-]*@leetcode\\.com$'
MySQL 正则表达式 | 菜鸟教程 (runoob.com)
LIKE 和 REGEXP 都是用于模式匹配的操作符,主要区别在于:
- LIKE 是基于通配符(比如`%`表示零个或多个字符和 `_`表示单个字符)的模式匹配,而 REGEXP 使用正则表达式进行模式匹配,因此 REGEXP 更灵活。
- 在某些情况下,LIKE 可能比 REGEXP 更快,因为 LIKE 比 REGEXP 更简单,但在需要复杂模式匹配时,REGEXP 更适合。
- 正则表达式提供了更多的匹配和操作选项,如字符类、量词、分组等,因此更适合处理复杂的匹配需求。
5、用户自定义函数
6、TRIM()移除修建
啥都没有就是移除空格
拓展——LTRIM(),RTRIM()
TRIM([removed_characters FROM] input_string)
SELECT
TRIM('.$' FROM '$$$Hello..') result
二、别名
三、关于视图
1、视图理解:
虚拟表,它是一个基于查询结果的可视化表格,具有和物理表相似的结构。视图存储的不是实际的数据,而是查询的结果集【理解时:视图只是储存起来的自动化的查询】。通过视图,用户可以以类似于查询表格的方式来查询视图,而不必直接操作底层的数据表。视图可以对数据进行筛选、组合和转换,使得数据的访问更加方便(简化查询、提供相同数据的不同视图)和安全(限制访问、数据独立性)。
2、视图特点【创增删查改】:
视图的建立和删除不影响基本表。
对视图内容的更新(添加,删除和修改)直接影响基本表。【表改视图也改】
当视图来自多个基本表时,不允许添加和删除数据。
- 单表视图:一般用于查询和修改,会改变基本表的数据;
- 多表视图:一般用于查询,不会改变基本表的数据。
3、区分
3.1视图与表的主要区别:
- 存储内容:表存储的是实际的数据,而视图存储的是查询语句的结果集。
- 数据的可见性:表中的数据可以直接进行增删改查操作,而视图只是查询结果的展示,不能直接对视图进行增删改操作。对视图的修改操作实际上是对基础表的影响。
- 数据的物理存储:表中的数据是实际存储在数据库中的,而视图本身并不存储数据,它只是一种动态生成的结果。
- 数据的更新性:视图的数据更新性取决于视图的定义,一些复杂的视图可能不支持直接的更新操作,而表则可以直接进行数据更新。
3.2视图(view)与查询语句的区别:
查询语句(select)查询出来的信息仅仅存在于内存中,断电后文件丢失;view是真正的物理文件是存储起来的,断电开机后依然存在。
4、关于视图的操作:
创建:【CREATE VIEW sales_by_client AS】
删除:【DROP VIEW sales_by_client】
修改:【CREATE OR REPLACE VIEW clients_balance AS】 /【先删再创】
5、可更新视图
如果一个视图的原始查询语句中没有如下元素:
1. GROUP BY 分组、聚合函数、HAVING 分组聚合后筛选 (即分组聚合筛选三兄弟)
2. DISTINCT 去重
3. UNION 纵向合并
则该视图是可更新视图(Updatable Views),可以用在 INSERT DELETE UPDATE 语句中进行增删改,否则只能 用在 SELECT 语句中进行查询。(1好理解,2和3需要记一下)
另外,增(INSERT)还要满足附加条件:视图必须包含底层原表的所有必须字段(也很好理解) 【因为不会接受必须某个字段为空的记录】
总之,一般通过原表修改数据,但当出于安全考虑或其他原因没有某表的直接权限时,可以通过视图来修改数据, 前提是视图是可更新的。
6、视图刷题笔记
视图可以被嵌套,一个视图中可以嵌套另一个视图。这意味着你可以在一个视图中引用另一个视图,从而创建视图的层次结构。
当SELECT语句的选择列表有TOP子句时,视图可以包含ORDER BY子句。
sp_helptext 存储过程用于显示存储过程、视图、触发器等对象的定义文本,即用于获取自定义视图创建的T_SQL文本,但它不会更新数据。更新视图数据可用sp_refreshview。
视图不可以对临时表或表变量进行引用。视图只能引用永久性表,不能引用临时表或表变量。临时表和表变量的生命周期与会话或作用域相关,而视图是在数据库中永久存储的。