SQL函数和特殊用法

SQL聚合函数

注意：聚合函数不能嵌套，例如：min(avg(字段))将不被允许

AVG - 计算一组值或表达式的平均值。

SELECT AVG(score) FROM table_name;

COUNT - 计算表中的行数。

SELECT COUNT(*) FROM table_name;

INSTR - 返回字符串中第一次出现的子字符串的位置。

SELECT INSTR('hello world', 'world') FROM table_name;

SUM - 计算一组值或表达式的总和。

SELECT SUM(score) FROM table_name;

MIN - 在一组值中找到最小值

SELECT MIN(score) FROM table_name;

MAX - 在一组值中找到最大值

SELECT MAX(score) FROM table_name;

MySQL字符串函数

CONCAT - 将两个或多个字符串组合成一个字符串。

SELECT CONCAT('hello', 'world') FROM table_name;
--helloworld

LENGTH＆CHAR_LENGTH - 获取字符串的长度，以字节和字符为单位。

SELECT LENGTH('hello') FROM table_name;
--5
SELECT CHAR_LENGTH('hello') FROM table_name;
--5

LEFT - 获取具有指定长度的字符串的左侧部分。

SELECT LEFT('hello', 2) FROM table_name;
--he

REPLACE - 搜索并替换字符串中的子字符串。

SELECT REPLACE('hello', 'h', 'H') FROM table_name;
--Hello

SUBSTRING - 从具有特定长度的位置开始提取子字符串。

SELECT SUBSTRING('hello', 2, 3) FROM table_name;
--ell

TRIM - 从字符串中删除不需要的字符。

SELECT TRIM(' hello ') FROM table_name;
--hello

FIND_IN_SET - 在以逗号分隔的字符串列表中查找字符串。

SELECT FIND_IN_SET('hello', 'hello,world') FROM table_name;
--1

FORMAT - 格式化具有特定区域设置的数字，四舍五入到小数位数

SELECT FORMAT(12345.6789, 2) FROM table_name;
--12,345.68

MySQL控制流功能

CASE - THEN-WHEN满足分支中的条件，则返回分支中的相应结果，否则返回ELSE分支中的结果。

SELECT CASE WHEN 1 > 0 THEN ‘true’ ELSE ‘false’ END;
--true

IF - 根据给定条件返回值。

SELECT IF(1 > 0, ‘true’, ‘false’);
--true

IFNULL - 如果它不是NULL则返回第一个参数，否则返回第二个参数。

SELECT IFNULL(NULL, ‘null value’);
--null value
--第一个参数是NULL，因此返回第二个参数，即“null value”。

NULLIF - 如果第一个参数等于第二个参数，则返回NULL，否则返回第一个参数。

SELECT NULLIF(1, 1);
--NULL

MySQL日期和时间函数

CURDATE - 返回当前日期。

SELECT CURDATE();
--当前日期

DATEDIFF - 计算两个DATE值之间的天数。

SELECT DATEDIFF(‘2022-12-14’, ‘2022-12-01’);
--13

DAY - 获取指定日期的月份日期。

SELECT DAY(‘2022-12-14’);
--14

DATE_ADD - 将日期值添加到日期值。

SELECT DATE_ADD(‘2022-12-14’, INTERVAL 1 DAY);
--2022-12-15

DATE_SUB - 从日期值中减去时间值。

SELECT DATE_SUB(‘2022-12-14’, INTERVAL 1 DAY);
--2022-12-13

DATE_FORMAT - 根据指定的日期格式格式化日期值。

SELECT DATE_FORMAT(‘2022-12-14’, ‘%Y-%m-%d’);
--2022-12-14

DAYNAME - 获取指定日期的工作日名称。

SELECT DAYNAME(‘2022-12-14’);
--Wednesday

DAYOFWEEK - 返回日期的工作日索引。

SELECT DAYOFWEEK(‘2022-12-14’);
--4

EXTRACT - 提取日期的一部分。

SELECT EXTRACT(YEAR FROM ‘2022-12-14’);
--2022

NOW - 返回执行语句的当前日期和时间。

SELECT NOW();
--当前日期和时间

MONTH - 返回表示指定日期月份的整数。

SELECT MONTH(‘2022-12-14’);
--12

STR_TO_DATE - 根据指定的格式将字符串转换为日期和时间值。

SELECT STR_TO_DATE(‘2022-12-14’, ‘%Y-%m-%d’);
--2022-12-14

SYSDATE - 返回当前日期。

SELECT SYSDATE();
--当前日期和时间

TIMEDIFF - 计算两个TIME或DATETIME值之间的差异。

SELECT TIMEDIFF(‘2022-12-14 14:30:00’, ‘2022-12-14 14:00:00’);
--00:30:00

TIMESTAMPDIFF - 计算两个DATE或DATETIME值之间的差异。

SELECT TIMESTAMPDIFF(DAY, ‘2022-12-01’, ‘2022-12-14’);
--13

WEEK - 返回一个星期的日期。

SELECT WEEK(‘2022-12-14’);
--50

WEEKDAY - 返回日期的工作日索引。

SELECT WEEKDAY(‘2022-12-14’);
--2

YEAR -返回日期值的年份部分。

SELECT YEAR(‘2022-12-14’);
--2

MySQL比较功能

COALESCE - 返回第一个非null参数，这对于替换null非常方便。

SELECT COALESCE(NULL, ‘null value’, ‘not null value’);
--not null value
--在这个例子中，第一个参数是NULL，因此返回第二个参数，即“null value”。

GREATEST＆LEAST - 取n个参数并分别返回n个参数的最大值和最小值。

SELECT GREATEST(1, 2, 3, 4, 5);
--5
SELECT LEAST(1, 2, 3, 4, 5);
--1

ISNULL - 如果参数为null，则返回1，否则返回零。

SELECT ISNULL(NULL);
--1
--在这个例子中，参数是NULL，因此返回1。

MySQL数学函数

ABS - 返回数字的绝对值。

SELECT ABS(-1);
--1

CEIL - 返回大于或等于输入数字的最小整数值。

SELECT CEIL(1.5);
--2

FLOOR - 返回不大于参数的最大整数值。

SELECT FLOOR(1.5);
--1

MOD - 返回数字的余数除以另一个。

SELECT MOD(10, 3);
--1

ROUND - 将数字四舍五入到指定的小数位数。

SELECT ROUND(1.5);
--2

TRUNCATE - 将数字截断为指定的小数位数。

SELECT TRUNCATE(1.5, 0);
--1

其他MySQL功能

CAST - 将任何类型的值转换为具有指定类型的值。

CAST()函数用于将任何类型的值转换为具有指定类型的值。目标类型可以是以下类型之一：BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED。CAST()函数通常用于返回具有指定类型的值，以便在WHERE，JOIN和HAVING子句中进行比较。

CAST("12" AS INT)
--将文本字符串”12“转换为整型
CAST('2022-03-20' AS DATE)
--将字符串’2022-03-20’转换为日期类型

窗口函数

窗口函数定义

窗口函数是一种特殊的函数，它可以在查询结果中的一组行上执行计算，并返回单个结果集中的多个行。窗口函数的参数包括：分区、排序和框架。

窗口函数的组成

分区：将数据集合分成多个分区，每个分区都有自己的行集合。分区是可选的，如果没有指定分区，则整个数据集合被视为一个分区。
排序：指定在分区内对行进行排序的列。排序是必需的，因为窗口函数需要知道如何对行进行排序，以便确定哪些行应包括在框架中。
框架：指定要在排序行集合中包含哪些行。框架由两个部分组成：行偏移量和行范围。行偏移量指定框架的起始行，行范围指定框架的结束行。行偏移量和行范围都是可选的，如果没有指定它们，则整个排序行集合都被视为框架。

--调用窗口函数的一般语法如下：
window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )

在这个语法中：

首先，指定窗口函数名称，后跟表达式。
其次，指定OVER具有三个可能元素的子句：分区定义，顺序定义和帧定义。

OVER子句后面的开括号和右括号是强制性的，即使没有表达式，例如：

window_function_name(expression) OVER()

partition_clause 句法

将partition_clause行分成块或分区。两个分区由分区边界分隔。

窗口函数在分区内执行，并在跨越分区边界时重新初始化。

partition_clause语法如下所示：

PARTITION BY <expression>[{,<expression>...}]

您可以在PARTITION BY子句中指定一个或多个表达式。多个表达式用逗号分隔。

order_by_clause 句法

order_by_clause语法如下：

ORDER BY <expression> [ASC|DESC], [{,<expression>...}]

ORDER BY子句指定行在分区中的排序方式。可以在多个键上的分区内对数据进行排序，每个键由表达式指定。多个表达式也用逗号分隔。

与PARTITION BY子句类似ORDER BY，所有窗口函数也支持子句。但是，仅对ORDER BY顺序敏感的窗口函数使用子句才有意义。

frame_clause 句法

帧是当前分区的子集。要定义子集，请使用frame子句，如下所示：

frame_unit {<frame_start>|<frame_between>}

相对于当前行定义帧，这允许帧根据其分区内当前行的位置在分区内移动。

帧单位指定当前行和帧行之间的关系类型。它可以是ROWS或RANGE。当前行和帧行的偏移量是行号，如果帧单位是ROWS行值，则行值是帧单位RANGE。

所述frame_start和frame_between定义帧边界。

将frame_start包含下列之一：

UNBOUNDED PRECEDING：frame从分区的第一行开始。
N PRECEDING：第一个当前行之前的物理N行。N可以是文字数字或计算结果的表达式。
CURRENT ROW：当前计算的行

frame_between如下：

BETWEEN frame_boundary_1 AND frame_boundary_2

frame_boundary_1和frame_boundary_2可各自含有下列之一：

frame_start：如前所述。
UNBOUNDED FOLLOWING：框架结束于分区的最后一行。
N FOLLOWING：当前行之后的物理N行。

如果未frame_definition在OVER子句中指定，则MySQL默认使用以下帧：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

MySQL窗口函数列表

以下是每个窗口函数的简要说明及具体案例

假设有一个学生成绩表sc，包含三个字段：s_id（学生编号）、c_id（课程编号）、score（成绩）。表中有如下数据：

s_id	c_id	score
1001	2001	78
1001	2002	83
1002	2001	83
1002	2002	92
1003	2001	65
1003	2002	88

排序函数：

ROW_NUMBER()-唯一排序

为每个分区中的行分配唯一的数字，从1开始，按照ORDER BY子句中指定的列排序。

-- ROW_NUMBER(): 给每一行按照成绩降序排名，并按照课程编号分区
SELECT s_id, c_id, score, 
ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY score DESC) AS rank
FROM sc;
-- 结果：
--      s_id	c_id	score	rank
--	1002	2001	83	1
--	1001	2001	78	2
--	1003	2001	65	3
--	1002	2002	92	1
--	1003	2002	88	2
--	1001	2002	83	3

RANK()-并列跳过

为每个分区中的行分配唯一的数字，从1开始，按照ORDER BY子句中指定的列排序。如果两个或多个行具有相同的值，则它们将被分配相同的排名，并且下一个排名将被跳过。

SELECT s_id, c_id, score, 
RANK() OVER (PARTITION BY c_id ORDER BY score DESC) AS rank
FROM sc;

-- 结果：
--  s_id    c_id  score	rank
--   1002   2001   83   1
--   1001   2001   78   2
--

DENSE_RANK()-并列不跳过

为每个分区中的行分配唯一的数字，从1开始，按照ORDER BY子句中指定的列排序。如果两个或多个行具有相同的值，则它们将被分配相同的排名，但下一个排名将不会被跳过。

SELECT s_id, c_id, score, 
DENSE_RANK() OVER (PARTITION BY c_id ORDER BY score DESC) AS rank
FROM sc;

-- 结果：
--  s_id    c_id  score	rank
--   1002   2001   83   1
--   1001   2001   78   2
--   ... ...

分布函数：

PERCENT_RANK()-占比

计算当前行在排序行集合中的相对排名，返回一个介于0和1之间的值。

SELECT s_id, c_id, score, 
PERCENT_RANK() OVER (PARTITION BY c_id ORDER BY score DESC) AS pr
FROM sc;

-- 结果：
--      s_id	c_id	score	pr
--	1002	2001	83	0.0 -- 第一名占0%
--	1001	2001	78	0.5 -- 第二名占50%
--	1003	2001	65	1.0 -- 第三名占100%
-- ... ...

CUME_DIST()-累计占比

计算当前行在排序行集合中的相对排名，返回一个介于0和1之间的值。与PERCENT_RANK()不同的是，CUME_DIST()返回的值是累积的。

SELECT s_id, c_id, score, 
CUME_DIST() OVER (PARTITION BY c_id ORDER BY score DESC) AS cd
FROM sc;

-- 结果：
--      s_id	c_id	score	cd
--	1002	2001	83	0.3333333333333333 -- 第一名超过33.33%的数据
-- ... ...

前后函数：

LAG()-向前偏移

返回当前行之前的指定偏移量的行的值。如果没有指定偏移量，则默认为1。

SELECT s_id, c_id, score, 
LAG(score) OVER (PARTITION BY c_id ORDER BY score DESC) AS prev_score
FROM sc;

-- 结果：
--      s_id	c_id	score	prev_score
--	1002	2001	83	NULL -- 第一名没有前一名
--	1001	2001	78	83 -- 第二名的前一名是83分
-- ... ...

LEAD()-向后偏移

返回当前行之后的指定偏移量的行的值。如果没有指定偏移量，则默认为1。

SELECT s_id, c_id, score, 
LEAD(score) OVER (PARTITION BY c_id ORDER BY score DESC) AS next_score
FROM sc;

-- 结果：
--      s_id	c_id	score	next_score
--	1002	2001	83	78 -- 第一名的后一名是78分
-- ... ...

头尾函数：

FIRST_VALUE()-返回排序行集合中的第一行的值。

SELECT s_id, c_id, score, 
FIRST_VALUE(score) OVER (PARTITION BY c_id ORDER BY score DESC) AS first_score
FROM sc;

-- 结果：
--      s_id	c_id	score	first_score
--	1002	2001	83	83 -- 第一名的成绩是83分
-- ... ...

LAST_VALUE()-返回排序行集合中的最后一行的值。

SELECT s_id, c_id, score, 
LAST_VALUE(score) OVER (PARTITION BY c_id ORDER BY score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_score
FROM sc;

-- 结果：
--      s_id	c_id	score	last_score
--	1002	2001	83	65 -- 最后一名的成绩是65分
-- ... ...

聚合函数+窗口函数联合：

SUM()

计算指定列的总和，但是它不会将所有行的总和作为单个值返回。相反，它将计算每个行的总和，并将其添加到结果集中的每个行中。

SELECT s_id, c_id, score, 
SUM(score) OVER (PARTITION BY c_id ORDER BY score DESC) AS sum_score
FROM sc;

-- 结果：
--      s_id	c_id	score	sum_score
--	1002	2001	83	83 -- 第一名的成绩总和是83分
-- ... ...

AVG()

计算指定列的平均值，但是它不会将所有行的平均值作为单个值返回。相反，它将计算每个行的平均值，并将其添加到结果集中的每个行中。

SELECT s_id, c_id, score, 
AVG(score) OVER (PARTITION BY c_id ORDER BY score DESC) AS avg_score
FROM sc;

-- 结果：
--      s_id	c_id	score	avg_score
--	1002	2001	83	83.0 -- 第一名的成绩平均值是83分
-- ... ...

COUNT()

计算指定列的行数，但是它不会将所有行的行数作为单个值返回。相反，它将计算每个行的行数，并将其添加到结果集中的每个行中。

SELECT s_id, c_id, score, 
COUNT(*) OVER (PARTITION BY c_id ORDER BY score DESC) AS count_row
FROM sc;

-- 结果：
--      s_id	c_id	score	count_row
--	1002	2001	83	1 -- 第一名所在的窗口有1行
-- ... ...

MAX()&MIN()

返回指定列的最大值和最小值。


SELECT s_id, c_id, score, 
MAX(score) OVER (PARTITION BY c_id ORDER BY score DESC) AS max_score
FROM sc;

-- 结果：
--   s_id   c_id  score	max_score
--   1002   2001   83   83 -- 第一名所在的窗口最大成绩是83分

子查询

MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在任何使用表达式的地方使用，并且必须在括号中关闭。

子查询必须用圆括号括起来。
子查询应该放在比较条件的右边，以提高可读性。
子查询不能包含ORDER BY子句，除非同时指定TOP子句。
子查询最多可以嵌套到32层，但有些情况可能不支持这么多层。
子查询可以根据返回的行数和列数分为标量子查询、列子查询、行子查询和表子查询。
子查询可以根据出现的位置分为SELECT后面的子查询、FROM后面的子查询、WHERE或HAVING后面的子查询和EXISTS后面的子查询。
子查询可以使用单行操作符（如=,<,>等）或多行操作符（如IN,ANY,ALL等）进行比较。
子查询可以引用外部表的别名，但不能引用外部表的列名。

SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA'); 
/*在这个例子中：
子查询返回位于美国的办事处的所有办公代码。
外部查询选择在办公室代码位于子查询返回的结果集中的办公室中工作的员工的姓氏和名字。*/

子查询出现的位置

EXISTS语句-判断结果是否为布尔值，再供查询使用

用于返回一个值或一行作为过滤条件的一部分，EXISTS的结果是一个布尔值，如果嵌套查询返回一条或多条记录，那么EXISTS返回TRUE；如果嵌套查询返回空集，那么EXISTS返回FALSE。

假设我们有以下三张表：

Student学生信息表
Sno	Sname	Ssex	Sage
1	Alice	F	20
2	Bob	M	21
3	Cathy	F	19

Course课程的信息
Cno	Cname
1	Math
2	English
3	Computer

SC学生选课信息
Sno	Cno
1	1
1	2
2	1

查询没有选修任何课程的学生姓名

SELECT Sname FROM Student 
WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno);
--Cathy

这条SQL语句的查询过程是这样的：

首先，从Student表中取出第一条记录，得到Sno为1，Sname为Alice。
然后，将Sno为1代入到子查询中，执行子查询：SELECT * FROM SC WHERE Sno = 1。
子查询返回两条记录：(1,1)和(1,2)，说明Sno为1的学生选修了两门课程。
因此，子查询结果集不为空，EXISTS返回True。但是因为前面有NOT关键字，所以最终结果为False。
这时候，父查询判断WHERE条件是否成立。因为结果为False，所以不成立。因此不将Sname为Alice放入结果集中。
接着，从Student表中取出第二条记录，得到Sno为2，Sname为Bob。
然后，将Sno为2代入到子查询中，执行子查询：SELECT * FROM SC WHERE Sno = 2。
子查询返回一条记录：(2,1)，说明Sno为2的学生选修了一门课程。
因此，子查询结果集不为空，EXISTS返回True。但是因为前面有NOT关键字，所以最终结果为False。
这时候，父查询判断WHERE条件是否成立。因为结果为False，所以不成立。因此不将Sname为Bob放入结果集中。
最后，从Student表中取出第三条记录，得到Sno为3,Sname为Cathy。
然后,将Sno为3代入到子查询中,执行子查询:SELECT * FROM SC WHERE Sno =3.
子查询没有返回任何记录,说明Sno为3的学生没有选修任何课程.
因此,子查询结果集为空,EXISTS返回False.但是因为前面有NOT关键字,所以最终结果集只有一条记录：Sname为Cathy。
说明只有Cathy没有选修任何课程，其他学生都选修了至少一门课程。
这就是使用EXISTS关键字的查询过程和结果。

EXISTS和IN操作符的区别

--IN用法
SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100);
--EXISTS用法
SELECT * FROM Orders o WHERE EXISTS 
(SELECT * FROM Products p WHERE p.ProductNumber = o.ProductNumber);

当子查询返回的结果集较小，主查询中的表较大且有索引时，应该使用IN操作符，因为它可以利用索引进行快速比较。
当主查询中的记录较少，子查询中的表较大且有索引时，应该使用EXISTS操作符，因为它可以在找到匹配时停止扫描过程。
总结：父查询表大但是有索引时，用IN；父查询表小、子查询表大，用EXISTS，因为可以遇到FALSE后停止扫描，节省性能。

SELECT语句

用于返回一个值或一行一列的值作为表达式的一部分

SELECT employee_id, first_name, last_name, salary,
(SELECT ROUND(AVG(salary), 0) FROM employees) AS avg_salary
FROM employees;
--子查询中返回的值为一行一列的值

FROM语句

用于返回一个表作为派生表的一部分

SELECT * FROM
(SELECT name, age FROM students) AS derived_table
WHERE age > 18;

JOIN语句

用于返回一个表作为连接条件的一部分

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN (SELECT department_id, department_name FROM departments) d
ON e.department_id = d.department_id;

WHERE语句

用于返回一个值或一行作为过滤条件的一部分

SELECT name, age FROM students
WHERE age > (SELECT AVG(age) FROM students);

公共表表达式CTE-WITH AS

公共表表达式是只存在一个单一的SQL语句例如执行范围内的一个命名的临时结果集，与派生表类似，CTE不作为对象存储，仅在执行查询期间持续存在。与派生表不同，CTE可以是自引用（递归CTE），也可以在同一查询中多次引用。此外，与派生表相比，CTE提供了更好的可读性和性能。

CTE结构

使用WITH关键字开始的一个查询，它包含以下主要组成部分：

CTE名称：一个唯一的标识符，用于在外层查询中引用CTE。
CTE列列表：一个可选的列名列表，用于指定CTE返回的列。如果省略，则默认使用内部查询中的列名或别名。
CTE查询：一个SELECT语句，用于从源表中获取数据并返回结果集。CTE查询可以包含其他CTE或子查询。
外层查询：一个使用CTE名称作为数据源的SELECT、INSERT、UPDATE、DELETE或MERGE语句。

--注意：查询中的列数必须与column_list中的列数相同。如省略column_list，CTE将使用定义CTE的查询的列列表
--CTE definition 公用表表达式定义
WITH CTE_Name (CTE_Column1, CTE_Column2) -- CTE name and column list 公用表表达式名称和列列表
AS 
(
    SELECT Column1, Column2 -- CTE query 公用表表达式查询
    FROM Table1
    WHERE Condition1
)
-- Outer query 外层查询
SELECT * FROM CTE_Name; -- Using CTE name as data source 使用公用表表达式名称作为数据源

递归CTE-查询合并查询

本质上递归CTE就是将已查询到的数据a和根据a查询到的数据进行合并，再查询的过程。

-- 定义一个递归CTE，命名为subordinates
WITH RECURSIVE subordinates AS (
  -- 基本查询：选择员工id为1（假设为总经理）的记录
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- 递归查询：选择与基本查询结果中员工id相同的上级id的记录，并与基本查询结果合并
  SELECT e.id, e.name, e.manager_id FROM employees e 
  INNER JOIN subordinates s ON e.manager_id = s.id
)
-- 使用CTE查询所有下属信息
SELECT * FROM subordinates;

WITH子句的用法

WITH子句是SQL中的一个功能，它可以让你定义一个临时的数据集，然后在后面的查询中引用它。这样可以简化复杂的查询，特别是那些包含JOIN和子查询的查询

在开始时使用SELECT：这是最常见的用法，就是在主查询之前定义一个或多个临时视图（也叫做公共表表达式CTE），然后在主查询中使用它们。例如：

-- 定义两个临时视图
WITH
  dept AS (SELECT * FROM departments),
  emp AS (SELECT * FROM employees)
-- 在主查询中使用它们
SELECT e.name, d.name AS department
FROM emp e
JOIN dept d ON e.dept_id = d.id;

在子查询或派生表子查询的开头使用：这是一种嵌套的用法，就是在主查询中的某个子查询或派生表子查询中再定义一个或多个临时视图，然后在该子查询或派生表子查询中使用它们。例如：

-- 在主查询中定义一个临时视图
WITH total_sales AS (
  SELECT product_id, SUM(quantity) AS total_quantity
  FROM sales
  GROUP BY product_id
)
-- 在主查询中的派生表子查询中再定义一个临时视图，并使用它和total_sales进行JOIN操作
SELECT p.name, s.total_quantity / t.total_quantity AS ratio
FROM products p
JOIN (
  -- 定义一个临时视图
  WITH category_sales AS (
    SELECT category_id, SUM(quantity) AS total_quantity
    FROM sales s JOIN products p ON s.product_id = p.id 
    GROUP BY category_id  
  )
  -- 使用它和total_sales进行JOIN操作，并计算每个产品销量占其类别销量的比例 
  SELECT s.product_id, s.total_quantity, c.total_quantity 
  FROM total_sales s JOIN category_sales c ON s.category_id = c.category_id  
) s ON p.id = s.product_id;

在SELECT包含SELECT子句的语句之前使用：这是一种特殊的用法，就是在某些支持SELECT包含SELECT子句（如INSERT INTO … SELECT）的语句之前定义一个或多个临时视图，然后在该语句中使用它们。例如：

-- 定义两个临时视图，并将其中一个作为源表插入到目标表中
WITH source_table (id,name,value) as (
   select id,name,value from some_other_table where id > 1000),
   target_table as (
   select id,name,value from final_table where id >1000)

insert into target_table(id,name,value)
select id,name,value from source_table;

SQL集运算符

三种运算符必须遵守的原则

所有SELECT语句中出现的列的数量和顺序必须相同。
列的数据类型必须相同或可转换
如果列名或别名不同，结果列将以第一个SELECT查询中的列名为准
UNION、INTERSECT和MINUS合并后的集合都会删除重复行，如果不删除重复行需要加ALL

UNION 操作符-并集删除重复行

SQL UNION 操作符合并两个或多个 SELECT 语句的结果。注意：UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL-并集不删除重复行

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

UNION	UNION ALL
1	1
2	2
3	2
	3

INTERSECT-交集-MYSQL不支持

INTERSECT运算符是一个集合运算符仅返回两个查询或多个查询的不同行。

(SELECT column_list 
FROM table_1)
INTERSECT
(SELECT column_list
FROM table_2);

--可以使用此方式模拟INTERSECT
SELECT t1.id, t1.name
FROM t1
JOIN t2 ON t1.id = t2.id AND t1.name = t2.name;

MINUS-左除交集部分-MYSQL不支持

MINUS 比较两个查询的结果，并返回第一个查询中不是由第二个查询输出的不同行。

SELECT column_list_1 FROM table_1
MINUS 
SELECT columns_list_2 FROM table_2;

--可以使用此方法模拟MINUS
SELECT t1.id, t1.name
FROM t1
WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.id = t1.id AND t2.name = t1.name);

等价iwebsec-sql函数iwebsec

示例函数常见sql

函数大全sql

函数over sql sum

only_full_group_by aggregated函数sql_mode

函数datetrunc日期sql

526互联

SQL函数和特殊用法

SQL函数和特殊用法

SQL聚合函数

AVG - 计算一组值或表达式的平均值。

COUNT - 计算表中的行数。

INSTR - 返回字符串中第一次出现的子字符串的位置。

SUM - 计算一组值或表达式的总和。

MIN - 在一组值中找到最小值

MAX - 在一组值中找到最大值

MySQL字符串函数

CONCAT - 将两个或多个字符串组合成一个字符串。

LENGTH＆CHAR_LENGTH - 获取字符串的长度，以字节和字符为单位。

LEFT - 获取具有指定长度的字符串的左侧部分。

REPLACE - 搜索并替换字符串中的子字符串。

SUBSTRING - 从具有特定长度的位置开始提取子字符串。

TRIM - 从字符串中删除不需要的字符。

FIND_IN_SET - 在以逗号分隔的字符串列表中查找字符串。

FORMAT - 格式化具有特定区域设置的数字，四舍五入到小数位数

MySQL控制流功能

CASE - THEN-WHEN满足分支中的条件，则返回分支中的相应结果，否则返回ELSE分支中的结果。

IF - 根据给定条件返回值。

IFNULL - 如果它不是NULL则返回第一个参数，否则返回第二个参数。

NULLIF - 如果第一个参数等于第二个参数，则返回NULL，否则返回第一个参数。

MySQL日期和时间函数

CURDATE - 返回当前日期。

DATEDIFF - 计算两个DATE值之间的天数 。

DAY - 获取指定日期的月份日期。

DATE_ADD - 将日期值添加到日期值。

DATE_SUB - 从日期值中减去时间值。

DATE_FORMAT - 根据指定的日期格式格式化日期值。

DAYNAME - 获取指定日期的工作日名称。

DAYOFWEEK - 返回日期的工作日索引。

EXTRACT - 提取日期的一部分。

NOW - 返回执行语句的当前日期和时间。

MONTH - 返回表示指定日期月份的整数。

STR_TO_DATE - 根据指定的格式将字符串转换为日期和时间值。

SYSDATE - 返回当前日期。

TIMEDIFF - 计算两个TIME或DATETIME值之间的差异。

TIMESTAMPDIFF - 计算两个DATE或DATETIME值之间的差异。

WEEK - 返回一个星期的日期。

WEEKDAY - 返回日期的工作日索引。

YEAR -返回日期值的年份部分。

MySQL比较功能

COALESCE - 返回第一个非null参数，这对于替换null非常方便。

GREATEST＆LEAST - 取n个参数并分别返回n个参数的最大值和最小值。

ISNULL - 如果参数为null，则返回1，否则返回零。

MySQL数学函数

ABS - 返回数字的绝对值。

CEIL - 返回大于或等于输入数字的最小整数值。

FLOOR - 返回不大于参数的最大整数值。

MOD - 返回数字的余数除以另一个。

ROUND - 将数字四舍五入到指定的小数位数。

TRUNCATE - 将数字截断为指定的小数位数。

其他MySQL功能

CAST - 将任何类型的值转换为具有指定类型的值。

窗口函数

窗口函数定义

窗口函数的组成

​partition_clause​ 句法

​order_by_clause​ 句法

​frame_clause​ 句法

MySQL窗口函数列表

排序函数：

ROW_NUMBER()-唯一排序

RANK()-并列跳过

DENSE_RANK()-并列不跳过

分布函数：

PERCENT_RANK()-占比

CUME_DIST()-累计占比

前后函数：

LAG()-向前偏移

LEAD()-向后偏移

头尾函数：

FIRST_VALUE()-返回排序行集合中的第一行的值。

LAST_VALUE()-返回排序行集合中的最后一行的值。

聚合函数+窗口函数联合：

SUM()

AVG()

COUNT()

DATEDIFF - 计算两个DATE值之间的天数。

partition_clause 句法

order_by_clause 句法

frame_clause 句法