常用sql语句、语法、正则匹配、开窗函数

目录

函数-基础类

①IFNULL(X1,X2)

②ROUND()

③count()

④char_length() 、length()

⑤SUBSTRING(column_name, start, length)

⑥UPPER(expression)

⑦LOWER(expression)

⑧CONCAT(string1, string2, ...)

函数-时间类

①DATE_FORMAT(date, format) 

②DATE()

③DATEDIFF(enddate，startdate)

④TIMESTAMPDIFF()

⑤DATE_ADD()

进阶函数用法

①Sum( if ( 条件，1，0 ) )

②lag() over()：

③lead() over()：

④COALESCE()

⑤GROUP_CONCAT()

开窗函数

1，聚合开窗函数

2，分组开窗函数

语法

①join连接

②distinct

③LIMIT

④CASE…WHEN…

正则表达式

基本符号

---

函数-基础类

①IFNULL(X1,X2)

如果x1为null，返回x2，否则返回x1

②ROUND()

ROUND(X)： 返回参数X的四舍五入的一个整数。

ROUND(X,D)： 返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

③count()

COUNT是一个聚合函数，你不能直接在 WHERE子句中使用它，而应该在 HAVING子句中使用。其次，你应该在 GROUP BY子句中包含所有未被聚合的列。

④char_length() 、length()

用于计算字符串中字符数的最佳函数是 CHAR_LENGTH(str)，它返回字符串 str 的长度。

LENGTH(str)返回字符串 str 的字节数，某些字符包含多于 1 个字节。

以字符 '¥' 为例：CHAR_LENGTH()返回结果为 1，而 LENGTH()返回结果为 2，因为该字符串包含 2 个字节。

length()中一个汉字会算3个字符，而char_length()一个汉字算一个字符。

⑤SUBSTRING(column_name, start, length)

这将从列的值中提取一个子字符串，从指定的起始位置开始，直到指定的长度。

length参数：可选。子串中的字符数，必须是数值，可省略。如果省略了该参数，那么返回从 stringObject 的开始位置到结尾的字串。

⑥UPPER(expression)

这会将字符串表达式转换为大写。

⑦LOWER(expression)

这会将字符串表达式转换为小写。

⑧CONCAT(string1, string2, ...)

这会将两个或多个字符串连接成一个字符串。

函数-时间类

①DATE_FORMAT(date, format) 

用于以不同的格式显示日期/时间数据。

date 参数是合法的日期，format 规定日期/时间的输出格式。

数据表中的 trans_date 是精确到日，我们可以使用 DATE_FORMAT() 函数将日期按照年月 %Y-%m 输出。比如将 2019-01-02 转换成 2019-01 。

DATE_FORMAT(trans_date, '%Y-%m')

②DATE()

如果您想查询所有在2022年1月的记录，并且您的时间列是YYYY-MM-DD格式的日期类型，您可以使用以下WHERE语句：

SELECT*
FROM your_table_name  
WHERE DATE(time_column) BETWEEN'2022-01-01'AND'2022-01-31';

DATE()函数用于确保 即使时间列包含时间信息（例如 2022-01-02 15:30:00 ） ，查询也会仅基于日期部分进行匹配。

如果您的时间列已经是日期类型（不包含时间部分），那么您可以直接比较日期，而不需要使用DATE()函数：

SELECT*
FROM your_table_name  
WHERE time_column BETWEEN'2022-01-01'AND'2022-01-31';

③DATEDIFF(enddate，startdate)

函数返回两个日期之间的时间。

④TIMESTAMPDIFF()

TIMESTAMPDIFF可以计算相差天数、小时、分钟和秒，相比于datediff函数要灵活很多。格式是时间小的前，时间大的放在后面。 计算相差天数。

select w1.Id
from Weather as w1, Weather as w2
where TIMESTAMPDIFF(DAY, w2.RecordDate, w1.RecordDate) = 1
AND w1.Temperature > w2.Temperature

day = DiffDate = enddate − startdate = 1

把示例的day换成year，就是按年计算。

⑤DATE_ADD()

 函数将 时间/日期间隔 添加到日期，然后返回日期。DATE_ADD(date, INTERVAL value addunit)

SELECT

DATE_ADD("2017-06-15", INTERVAL  10DAY );

进阶函数用法

①Sum( if ( 条件，1，0 ) )

记录符合条件的总数，比方说Sum(if(a>0,1,0))，如果a>0，则加一，否则加0，最后会将所有符合a>0的记录数加和，即为符合条件的总数。

②lag() over()：

查询当前行向上偏移n行对应的结果

该函数有三个参数：第一个为待查询的参数列名，第二个为向上偏移的位数，第三个参数为超出最上面边界的默认值。

SELECT id
FROM
(SELECT id,
   temperature,
   recordDate,
   lag(recordDate,1) over(order by recordDate) as last_date,
   lag(temperature,1) over(order by recordDate) as last_temperature
  FROM Weather) a
WHERE temperature > last_temperature and datediff(recordDate, last_date) = 1

③lead() over()：

查询当前行向下偏移n行对应的结果

该函数有三个参数：第一个为待查询的参数列名，第二个为向下偏移的位数，第三个参数为超出最下面边界的默认值。

④COALESCE()

COALESCE(value1, value2, ..., valueN)6

COALESCE多参数，ifnull双参数

IFNULL() 函数主要用于处理两个参数的情况。它接受两个参数，第一个参数是要检查的值，第二个参数是在第一个参数为 NULL 时要返回的值。例如，如果你想要将某个字段中的 NULL 值替换为 0，你可以使用 IFNULL() 函数来实现这个目的。这个函数在处理只有两个可能值（一个可能为 NULL）的场景时非常直观和简单。

COALESCE() 函数则更加灵活，它可以接受任意数量的参数。这个函数会按照参数的顺序进行检查，返回第一个非 NULL 的值。如果所有参数都是 NULL，则返回 NULL。这使得 COALESCE() 在处理多个可能的 NULL 值或需要从多个列中选择非 NULL 值时非常有用。

⑤GROUP_CONCAT()

 将多行中的多个值组合成一个字符串。

GROUP_CONCAT( DISTINCT expression1 ORDER BY expression2 SEPARATOR sep );

GROUP_CONCAT( DISTINCT product SEPARATOR ',' );  用逗号分隔

开窗函数

1，聚合开窗函数

      即 聚合函数 sum()，count()，max()，min()， avg() + over(partition by … order by …)

2，分组开窗函数

      即row_number()，rank()，dense_rank()，ntile() + over(partition by … order by …)

1，partition by  字段 相当于group by 字段 起到分组作用

2，order by 字段 即根据某个字段进行排序，默认包含该分组的所有行的数据，进行聚合或排序操作

3，ROWS|RANGE 窗口子句，跟在 order by 子句后面用来限制当前行聚合或排序操作的范围

4，range和rows的区别：

rows   是物理窗口，是哪一行就是哪一行，与当前行的值（order by key的key的值）无关，只与排序后的行号相关，就是我们常规理解的那样。

range 是逻辑窗口，与当前行的值有关（order by key的key的值）,在key上操作range范围  

5，窗口子句的几个范围语法的格式：

          current row :当前行

          unbounded proceding  窗口上边界不设限(即区间的第一行)

          unbounded  following   窗口下边界不设限(即区间的最后一行)

          N proceding   当前行之前的N行，可以是数字也可以是能计算数字的表达式

          N  following   当前行之后的N行 ，同上

语法

①join连接

A inner join B 取交集。

A left join B 取 A 全部，B 没有对应的值为 null。

A right join B 取 B 全部 A 没有对应的值为 null。

A full outer join B 取并集，彼此没有对应的值为 null。

对应条件在 on 后面填写。

②distinct

select distinct 访客id ,浏览时间 from 淘宝日销售数据表；

1）distinct语法规定对单字段、多字段去重，必须放在第一个查询字段前。

2）如果对表中多列字段进行去重，去重的过程就是将多字段作为整体去重，比如上面的例子，我们将访客id和浏览时间为整体去去重，而不是对访客id单独去重后再对姓名单独去重，所以会出现相同的访客id对应不同的浏览时间。

③LIMIT

在某些情况下，如果明知道查询结果只有一个，SQL语句中使用LIMIT 1会提高查询效率。

DESC LIMIT 1

返回最后一条查询结果

下面的 SQL 语句从 "Websites" 表中选取头两条记录：

实例

SELECT * FROM Websites LIMIT 2;

④CASE…WHEN…

SELECT x, y, z,
CASE
WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
ELSE 'No'
END AS 'triangle'
FROM triangle

正则表达式

基本符号

^          表示一个字符串或行的开头

[a-z]    表示一个字符范围，匹配从 a 到 z 的任何字符。

[0-9]    表示一个字符范围，匹配从 0 到 9 的任何字符。

[a-zA-Z]    这个变量匹配从 a 到 z 或 A 到 Z 的任何字符。请注意，你可以在方括号内指定的字符范围的数量没有限制，您可以添加想要匹配的其他字符或范围。

[^a-z]        这个变量匹配不在 a 到 z 范围内的任何字符。请注意，字符 ^ 用来否定字符范围，它在方括号内的含义与它的方括号外表示开始的含义不同。

[a-z]*        表示一个字符范围，匹配从 a 到 z 的任何字符 0 次或多次。

[a-z]+        表示一个字符范围，匹配从 a 到 z 的任何字符 1 次或多次。

.

匹配任意一个字符。 表示匹配几乎任何单一字符（除了换行符）

\.

表示句点字符。请注意，反斜杠用于转义句点字符，因为句点字符在正则表达式中具有特殊含义。还要注意，在许多语言中，你需要转义反斜杠本身，因此需要使用\\.。

$

表示一个字符串或行的结尾。

*

表示前面的元素（在这里是 .）可以出现零次或多次。

.* 的组合表示匹配任何数量的任何字符（除了换行符）。

SELECTpatient_id, patient_name, conditions  
FROMPatients  
WHEREconditions REGEXP '\\bDIAB1.*';

这个查询是用来从 Patients 表中选择 patient_id、patient_name 和 conditions 字段，但仅当 conditions 字段的值包含以 "DIAB1" 开头的字符串时。

• \\b 是一个单词边界，确保 "DIAB1" 是一个完整的单词，而不是其他单词的一部分（例如 "DIAB10" 或 "ADIAB1"）。
• DIAB1 是要匹配的文字字符串。
• .* 确保匹配从 "DIAB1" 开始的任何后续字符。
• 因此，这个查询会返回所有 conditions 字段中包含以 "DIAB1" 开头的任何文本的记录。例如，它可以匹配 "DIAB1", "DIAB1TYPE2", "DIAB1 SOMEOTHERCONDITION" 等。