基础语法

查询

全表查询

select
  * 
from
  表名

选择查询

select 
  {列名...} 
from
  表名

列名之间有顺序，且用逗号间隔

别名

{原始字段名} as {别名}

常量和运算

在 SQL 查询中，常量指的是固定的数值或文本（比如 "1"），而运算则是对这些常量进行数学运算或字符串操作。
通过常量和运算，我们可以在查询语句中对数据进行加减乘除、取平均值、连接文本等操作，从而得到我们想要的查询结果。
此外，SQL 可以直接把常量作为列名。

条件查询

where

select 
  列1, 列2, ... 
from
  表名
where
  条件

其中，列1, 列2, ...是你要选择的列，可以是具体的列名，也可以是*表示选择所有列。表名是你要从中查询数据的表名。条件是指定的查询条件，可以使用比较运算符（如=、<、>等）、逻辑运算符（如AND、OR等）、IN 操作符、LIKE 操作符等来设置条件。

运算符

运算符是 SQL 中用于在条件查询中进行条件判断的特殊符号，比如 =、 !=、<、> 等。通过使用不同的运算符，我们可以在查询语句中设定多样化的条件，从而根据数据的不同属性进行灵活的筛选和过滤。

between运算符来筛选出在某个范围内的目标。

空值

在数据库中，有时候数据表的某些字段可能没有值，即为空值NULL。

空值表示该字段的值是未知的、不存在的或者没有被填写的。在SQL查询中，我们可以使用IS NULL和IS NOT NULL来判断字段是否为空值或非空值。

模糊查询

模糊查询是一种特殊的条件查询，它允许我们根据模式匹配来查找符合特定条件的数据，可以使用like关键字实现模糊查询。

在like模糊查询中，我们使用通配符来代表零个或多个字符，从而能够快速地找到匹配的数据。

有如下 2 种通配符：

百分号（%）：表示任意长度的任意字符序列。
下划线（_）：表示任意单个字符。

可以使用某某%或%某某查询匹配的开头和结尾。

同理，可以使用not like来查询不包含某关键字的信息。

逻辑运算

逻辑运算是一种在条件查询中使用的运算符，它允许我们结合多个条件来过滤出符合特定条件的数据。

在逻辑运算中，常用的运算符有：

AND：表示逻辑与，要求同时满足多个条件，才返回 true。
OR：表示逻辑或，要求满足其中任意一个条件，就返回 true。
NOT：表示逻辑非，用于否定一个条件（本来是 true，用了 not 后转为 false）

去重

在 SQL 中，我们可以使用distinct关键字来实现去重操作。

除了按照单字段去重外，distinct关键字还支持根据多个字段的组合来进行去重操作，确保多个字段的组合是唯一的。

排序

在 SQL 中，我们可以使用order by关键字来实现排序操作。order by后面跟上需要排序的字段，可以选择升序ASC或降序DESC排列。

在排序的基础上，我们还可以根据多个字段的值进行排序。当第一个字段的值相同时，再按照第二个字段的值进行排序，以此类推。

order by 字段1 [升序/降序], 字段2 [升序/降序], ...

截断和偏移

在 SQL 中，我们使用limit关键字来实现数据的截断和偏移。

截断和偏移的一个典型的应用场景是分页，即网站内容很多时，用户可以根据页号每次只看部分数据。

limit后只跟一个整数，表示要截断的数据条数（一次获取几条）；limit后跟 2 个整数，依次表示从第几条数据（n为第n+1条）开始、一次获取几条。

条件分支

条件分支 case when 是 SQL 中用于根据条件进行分支处理的语法。它类似于其他编程语言中的 if else 条件判断语句，允许我们根据不同的条件选择不同的结果返回。

使用 case when 可以在查询结果中根据特定的条件动态生成新的列或对现有的列进行转换。

case when 支持同时指定多个分支：

CASE WHEN (条件1) THEN 结果1
	   WHEN (条件2) THEN 结果2
	   ...
	   ELSE 其他结果 END

函数

时间函数

在 SQL 中，时间函数是用于处理日期和时间的特殊函数。它们允许我们在查询中操作和处理日期、时间、日期时间数据，从而使得在数据库中进行时间相关的操作变得更加方便和灵活。

常用的时间函数有：

DATE：获取当前日期
DATETIME：获取当前日期时间
TIME：获取当前时间

示例-使用时间函数获取当前日期、当前日期时间和当前时间：

-- 获取当前日期
SELECT DATE() AS current_date

-- 获取当前日期时间
SELECT DATETIME() AS current_datetime

-- 获取当前时间
SELECT TIME() AS current_time

日期、日期时间和时间将根据当前的系统时间来生成，实际运行结果可能会因为当前时间而不同。

还有很多时间函数，比如计算两个日期的相差天数、获取当前日期对应的毫秒数等，实际运用时自行查阅即可。

字符串处理

在 SQL 中，字符串处理是一类用于处理文本数据的函数。它们允许我们对字符串进行各种操作，如转换大小写（UPPER、LOWER）、计算字符串长度（LENGTH）以及搜索和替换子字符串等。字符串处理函数可以帮助我们在数据库中对字符串进行加工和转换，从而满足不同的需求。

聚合函数

在 SQL 中，聚合函数是一类用于对数据集进行汇总计算的特殊函数。它们可以对一组数据执行诸如计数、求和、平均值、最大值和最小值等操作。聚合函数通常在 SELECT 语句中配合 GROUP BY 子句使用，用于对分组后的数据进行汇总分析。

常见的聚合函数包括：

COUNT：计算指定列的行数或非空值的数量。
SUM：计算指定列的数值之和。
AVG：计算指定列的数值平均值。
MAX：找出指定列的最大值。
MIN：找出指定列的最小值。

分组聚合

单字段分组

在 SQL 中，分组聚合是一种对数据进行分类并对每个分类进行聚合计算的操作。它允许我们按照指定的列或字段对数据进行分组，然后对每个分组应用聚合函数，如 COUNT、SUM、AVG 等，以获得分组后的汇总结果。

在 SQL 中，通常使用 GROUP BY 关键字对数据进行分组。

多字段分组

有时，单字段分组并不能满足我们的需求，多字段分组和单字段分组的实现方式几乎一致，使用GROUP BY语法即可。

示例-使用group by进行多字段分组：

select 
  class_id, exam_num,
  count(name) as total_num
from
  student
group by
  class_id, exam_num

having子句

在 SQL 中，HAVING子句用于在分组聚合后对分组进行过滤。它允许我们对分组后的结果进行条件筛选，只保留满足特定条件的分组。

HAVING子句与条件查询WHERE子句的区别在于，WHERE子句用于在分组之前进行过滤，而HAVING子句用于在分组之后进行过滤。

示例-使用having子句进行筛选：

select
  class_id,
  sum(score) as total_score
from
  student
group by
  class_id
having
  total_score > 150;

查询进阶

关联查询

cross join

在之前的教程中，我们所有的查询操作都是在单个数据表中进行的。但有时，我们可能希望在单张表的基础上，获取更多额外数据，这时，就需要使用关联查询。

在 SQL 中，关联查询是一种用于联合多个数据表中的数据的查询方式。

示例-使用cross join查询：

select
  s.name as student_name, s.age as student_age, s.class_id as class_id, c.name as class_name
from
  student s
cross join
  class c

其中，CROSS JOIN是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的每一行与右表的每一行进行组合，返回的结果是两个表的笛卡尔积。

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的成员所有可能有序对。
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
设A,B为集合，用A中元素为第一元素，B中元素为第二元素构成有序对，所有这样有序对组成的集合叫做A与B的笛卡尔积，记作AxB。
笛卡尔积的符号化为：
A×B={(x,y)|x∈A∧y∈B}
例如，A={a,b}, B={0,1,2}，则
A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
B×A={(0, a), (0, b), (1, a), (1, b), (2, a), (2, b)}

笛卡尔积示意图.png

通过逗号分隔表名，隐式地实现了笛卡尔积，是 SQL 早期的写法，功能上与CROSS JOIN完全相同。

注意，在多表关联查询的 SQL 中，我们最好在选择字段时指定字段所属表的名称，还可以通过给表起别名来简化 SQL 语句。

示例-使用简化cross join查询：

select
  s.name student_name, s.age student_age, s.class_id class_id, c.name class_name
from
  student s, class c

inner join

在 SQL 中，INNER JOIN 是一种常见的关联查询方式，它根据两个表之间的关联条件，将满足条件的行组合在一起。

注意，INNER JOIN只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

使用INNER JOIN后，只有两个表之间存在对应关系的数据才会被放到查询结果中。

示例-使用inner join查询：

select
  s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level
from
  student s
join
  class c
on
  s.class_id = c.id

outer join

在 SQL 中，OUTER JOIN 是一种关联查询方式，它根据指定的关联条件，将两个表中满足条件的行组合在一起，并包含没有匹配的行。

在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

有些数据库并不支持 RIGHT JOIN 语法，那么如何实现 RIGHT JOIN 呢？

其实只需要把主表（from 后面的表）和关联表（LEFT JOIN 后面的表）顺序进行调换即可！

示例-使用outer join查询：

select
  s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level
from
  student s
left join
  class c
on
  s.class_id = c.id

子查询

子查询是指在一个查询语句内部嵌套另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。

当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

示例-使用子查询：

select
  name,score,class_id
from
  student
where class_id in (
    select
      distinct id
    from
      class
    where
      id = class_id
);

exists

子查询是一种强大的查询工具，它可以嵌套在主查询中，帮助我们进行更复杂的条件过滤和数据检索。

其中，子查询中的一种特殊类型是exists子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

exists 相对的是 not exists，用于查找不满足存在条件的记录。

示例-使用not exists子查询：

select
  name, age, class_id
from
  student
where not exists (
    select
      1
    from
      class
    where
      student.class_id = class.id
)

组合查询

在 SQL 中，组合查询是一种将多个 SELECT 查询结果合并在一起的查询操作。

union

包括两种常见的组合查询操作：UNION 和 UNION ALL。

UNION操作：它用于将两个或多个查询的结果集合并，并去除重复的行。即如果两个查询的结果有相同的行，则只保留一行。
UNION ALL 操作：它也用于将两个或多个查询的结果集合并，但不去除重复的行。即如果两个查询的结果有相同的行，则全部保留。

intersect

包括两种常见的组合查询操作：INTERSECT 和 INTERSECT ALL。

INTERSECT操作：它用于返回两个查询结果集的交集，并去除重复的行。即如果两个查询的结果有相同的行，则只保留一行。
INTERSECT ALL操作：它也用于返回两个查询结果集的交集，但不去除重复的行。即如果两个查询的结果有相同的行，则全部保留。

except

包括两种常见的组合查询操作：EXCEPT 和 EXCEPT ALL。

EXCEPT操作：它用于返回第一个查询结果集中不在第二个查询结果集中的行（即差集），并去除重复的行。即如果两个查询的结果有相同的行，则只保留一行。
EXCEPT ALL操作：它也用于返回第一个查询结果集中不在第二个查询结果集中的行（即差集），但不去除重复的行。即如果两个查询的结果有相同的行，则全部保留。

关联组合查询图例

四种join.jpg

窗口函数

在 SQL 中，窗口函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、同时保留原始行的详细信息。

窗口函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，窗口函数不会导致结果集的行数减少。

sum over

该函数用法为：

SUM(计算字段名) OVER (PARTITION BY 分组字段名)

示例-使用sum over函数：

select
  id, name, age, class_id, score, exam_num,
  avg(score) over (partition by class_id) as class_avg_score
from
  student

sum over order by

sum over 函数的另一种用法：sum over order by，可以实现同组内数据的累加求和。

该函数用法如下：

sum(计算字段名)
over
  (partition by 分组字段名 order by 排序字段 排序规则)

示例-使用sum over order by函数：

select
  id, name, age, score, class_id,
  sum(score) over (partition by class_id order by score asc) as class_sum_score
from
  student

rank

Rank 窗口函数是 SQL 中一种用于对查询结果集中的行进行排名的窗口函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

Rank 窗口函数的常见用法是在查询结果中查找前几名（Top N）或排名最高的行。

Rank 窗口函数的语法如下：

rank() over (
  partition by 列名1, 列名2, ... -- 可选，用于指定分组列
  order by 列名3 [asc|desc], 列名4 [asc|desc], ... -- 用于指定排序列及排序方式
) as rank_column

其中，PARTITION BY 子句可选，用于指定分组列，将结果集按照指定列进行分组；ORDER BY子句用于指定排序列及排序方式，决定了计算 Rank 时的排序规则。AS rank_column 用于指定生成的 Rank 排名列的别名。

示例-使用rank函数：

select
  id, name, age, score, class_id,
  rank() over (partition by class_id order by score desc) as ranking
from
  student

dense_rank

DENSE_RANK 是 SQL 中一种用于对查询结果集中的行进行密集排名的窗口函数。

它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

与 RANK 不同的是，DENSE_RANK 在处理并列时不会跳过后续排名。

当存在并列（相同排序值）时，DENSE_RANK 会为并列行分配相同的排名，但后续排名连续递增。

dense_rank() over (
  partition by 列名1, 列名2, ... -- 可选，用于指定分组列
  order by 列名3 [asc|desc], 列名4 [asc|desc], ... -- 用于指定排序列及排序方式
) as dense_rank_column

row_number

Row_Number 开窗函数是 SQL 中的一种用于为查询结果集中的每一行分配唯一连续排名的开窗函数。

它与之前讲到的 Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。

Row_Number 开窗函数的语法如下（几乎和 Rank 函数一模一样）：

row_number() over (
  partition by column1, column2, ... -- 可选，用于指定分组列
  order by column3 [asc|desc], column4 [asc|desc], ... -- 用于指定排序列及排序方式
) as row_number_column

其中，PARTITION BY子句可选，用于指定分组列，将结果集按照指定列进行分组。ORDER BY子句用于指定排序列及排序方式，决定了计算 Row_Number 时的排序规则。AS row_number_column用于指定生成的行号列的别名。

示例-使用row_number函数：

select
  id, name, age, score, class_id,
  row_number() over (partition by class_id order by score desc) as row_number
from
  student

lag/lead

开窗函数 Lag 和 Lead 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。

Lag 函数

Lag 函数用于获取当前行之前的某一列的值。它可以帮助我们查看上一行的数据。

Lag 函数的语法如下：

lag
  (column_name, offset, default_value)
over
  (partition by partition_column order by sort_column)

参数解释：

column_name：要获取值的列名。
offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
default_value：可选参数，用于指定当没有前一行时的默认值。
partition by和order by子句可选，用于分组和排序数据。

Lead 函数

Lead 函数用于获取当前行之后的某一列的值。它可以帮助我们查看下一行的数据。

Lead 函数的语法如下：

lead
  (column_name, offset, default_value)
over
  (partition by partition_column order by sort_column)