SQL语句大全以及示例

首先建立表

student表

-- 建表语句`student`
create table if not exists `student`
(
    `id`       integer          not null primary key AUTOINCREMENT,
    `name`     varchar(256)     not null,
    `age`      int              null,
    `class_id`    bigint           not null,
    `score`    double default 0 null,
    `exam_num` int    default 0 null
);
 
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('鸡哥', 25, 1, 2.5, 1);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('鱼皮', 18, 1, 400, 4);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('热dog', 40, 2, 600, 4);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('摸FISH', null, 2, 360, 4);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('李阿巴', 19, 3, 120, 2);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('老李', 56, 3, 500, 4);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('李变量', 24, 4, 390, 3);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('王加瓦', 23, 4, 0, 4);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('赵派森', 80, 4, 600, 4);
insert into `student` (`name`, `age`, `class_id`, `score`, `exam_num`)
values ('孙加加', 60, 5, 100.5, 1);

1、全表查询：从student表中查询所有学生的信息

select * from student

2、常量和运算

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

/*从名为student的数据表中选择出所有学生的姓名（name）和分数（score），
*并且额外计算出分数的2倍（double_score）*/
select name, score, score * 2 as double_score from student;

3、条件查询（掌握语法）

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

4、运算符

1. 比较运算符：
- `=`：等于
- `<>`或`!=`：不等于
- `>`：大于
- `<`：小于
- `>=`：大于等于
- `<=`：小于等于
 
2. 逻辑运算符：
- `AND`：逻辑与
- `OR`：逻辑或
- `NOT`：逻辑非
 
3. 空值处理运算符：
- `IS NULL`：判断是否为空值
- `IS NOT NULL`：判断是否不为空值
 
4. 模糊匹配运算符：
- `LIKE`：模糊匹配
- `NOT LIKE`：模糊匹配的否定形式
- `%`：匹配任意数量的字符（通配符）
- `_`：匹配单个字符（通配符）
 
5. 算术运算符：
- `+`：加法
- `-`：减法
- `*`：乘法
- `/`：除法
- `%`：求余数（模运算）
 
6. 字符串连接运算符：
- `||`：将两个字符串连接起来
 
7. 范围运算符：
- `BETWEEN`：判断一个值是否在指定的范围之间，包括边界值，与'and'连用
- `NOT BETWEEN`：判断一个值是否不在指定的范围之间
 
8. 集合运算符：
- `UNION`：合并多个查询结果集，与'all'连用可以查询全部，单独使用时可以去重
- `INTERSECT`：获取多个查询结果集之间的交集
- `EXCEPT`：获取多个查询结果集之间的差集

5、去重

-- 利用distinct可以完成查询去重
select distinct class_id from students;

6、排序

--我们可以使用 ORDER BY 关键字来实现排序操作。ORDER BY 后面跟上需要排序的字段，可以选择升序（ASC）或降序（DESC）排列。
 
-- SQL 查询语句 1
select name, age from students order by age asc;
 
-- SQL 查询语句 2
select name, score from students order by score desc;

7、截断查询的数据

-- LIMIT 后只跟一个整数，表示要截断的数据条数（一次获取几条）
select task_name, due_date from tasks limit 2;
 
-- LIMIT 后跟 2 个整数，依次表示从第几条数据开始、一次获取几条
select task_name, due_date from tasks limit 2, 2;

8、条件分支

条件分支 case when 是 SQL 中用于根据条件进行分支处理的语法。它类似于其他编程语言中的 if else 条件判断语句，允许我们根据不同的条件选择不同的结果返回。使用 case when 可以在查询结果中根据特定的条件动态生成新的列或对现有的列进行转换。

--条件分支语法
/*CASE WHEN (条件1) THEN 结果1
*	   WHEN (条件2) THEN 结果2
*	   ...
*	   ELSE 其他结果 END
*/
--将学生按照年龄划分为三个年龄等级（age_level）
SELECT
  name,
  CASE
    WHEN (age > 60) THEN '老同学'
    WHEN (age > 20) THEN '年轻'
    ELSE '小同学'
  END AS age_level
FROM
  student
ORDER BY
  name asc;

9、时间函数

1、DATE()获取当前日期
2、DATETIME()获取当前日期和时间
3、TIME()获取当前时间
select name, date() as 当前日期 from student

10、字符串处理

1、CONCAT(str1, str2, ...): 连接两个或多个字符串，返回连接后的结果。
 
2、LENGTH(str): 返回字符串的长度。
 
3、UPPER(str): 将字符串转换为大写字母。
 
4、LOWER(str): 将字符串转换为小写字母。
 
5、SUBSTRING(str, start, length): 返回从指定位置开始的指定长度的子字符串。
 
6、TRIM([leading | trailing | both] chars FROM str): 去除字符串开头或结尾（或两端）指定的字符。
 
7、REPLACE(str, search, replace): 将字符串中指定的内容替换为新的内容。
 
8、CHARINDEX(substring, str, [start_position]): 返回一个子字符串在字符串中的第一次出现的位置。
 
9、LEFT(str, length): 返回字符串的左边指定长度的子字符串。
 
10、RIGHT(str, length): 返回字符串的右边指定长度的子字符串。
 
11、REVERSE(str): 将字符串反转。
 
12、FORMAT(str, format): 格式化字符串，根据指定的格式返回格式化后的结果。

11、聚合函数

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

COUNT：计算指定列的行数或非空值的数量。
SUM：计算指定列的数值之和。
AVG：计算指定列的数值平均值。
MAX：找出指定列的最大值。
MIN：找出指定列的最小值。
--汇总学生表中所有学生的总成绩（total_score）、平均成绩（avg_score）、最高成绩（max_score）和最低成绩（min_score），汇总数据条数（total）
SELECT
  SUM(score) AS total_score,
  AVG(score) AS avg_score,
  MAX(score) AS max_score,
  MIN(score) AS min_score,
  COUNT(*) AS total
FROM
  student;

12、分组聚合

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

--单字段分组
--统计学生表中的班级编号（class_id）和每个班级的平均成绩（avg_score）
SELECT
  class_id,
  AVG(score) AS avg_score
FROM
  student
GROUP BY
  class_id;
--多字段分组
--统计学生表中每个班级每次考试的总学生人数（total_num）
SELECT
  class_id,
  exam_num,
  COUNT(*) AS total_num
FROM
  student
GROUP BY
  class_id,
  exam_num;

13、HAVING

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

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

--统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）
SELECT
  class_id,
  SUM(score) AS total_score
FROM
  student
GROUP BY
  class_id
HAVING
  SUM(score) > 150;

14、关联查询

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

--cross join示例：
--将学生表和班级表的所有行组合在一起，并返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）以及班级名称（class_name）
select
  s.name student_name,
  s.age student_age,
  s.class_id class_id,
  c.name class_name
from
  student s,
  class c;

在 SQL 中，INNER JOIN 是一种常见的关联查询方式，它根据两个表之间的关联条件，将满足条件的行组合在一起。注意，INNER JOIN 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

--inner join 示例
--根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）、班级名称（class_name）、班级级别（class_level）
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;

在 SQL 中，OUTER JOIN 是一种关联查询方式，它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

-- outer join示例
-- 根据学生表和班级表之间的班级编号进行匹配，
-- 返回学生姓名（student_name）、
-- 学生年龄（student_age）、
-- 班级编号（class_id）、
-- 班级名称（class_name）、
-- 班级级别（class_level），
-- 要求必须返回所有学生的信息（即使对应的班级编号不存在）
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;

15、子查询

子查询是指在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

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

--使用子查询的方式来获取存在对应班级的学生的所有数据
--返回学生姓名（name）、
--分数（score）、
--班级编号（class_id）字段
select
  name,
  score,
  class_id
from
  student
where
  class_id in (
    select distinct
      id
    from
      class
  );
 
--exists 相对的是 not exists，用于查找不满足存在条件的记录。
--使用 exists 子查询的方式来获取 不存在对应班级的 学生的所有数据，返回学生姓名（name）、年龄（age）、班级编号（class_id）字段。
select
  name,
  age,
  class_id
from
  student
where
  not exists (
    select
      class_id
    from
      class
    where
      class.id = student.class_id
  );

16、开窗函数

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

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

-- 基本语法
SUM(计算字段名) OVER (PARTITION BY 分组字段名)
SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)
--返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数升序的方式累加计算每个班级的学生总分（class_sum_score）
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 开窗函数是 SQL 中一种用于对查询结果集中的行进行 排名 的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

--基本语法
RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column
--返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式计算每个班级内的学生的分数排名（ranking）
SELECT
  id,
  name,
  age,
  score,
  class_id,
  RANK() OVER (
    PARTITION BY
      class_id
    ORDER BY
      score DESC
  ) AS ranking
FROM
  student;

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
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 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。

1）Lag 函数

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

2）Lead 函数

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

Lead 函数的语法如下：

-- 基本语法
LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)
LEAD(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子句可选，用于分组和排序数据。

返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（prev_name）、后一名学生姓名（next_name）
SELECT
  id,
  name,
  age,
  score,
  class_id,
  LAG(name) over (
    PARTITION BY
      class_id
    ORDER BY
      score DESC
  ) as prev_name,
  LEAD(name) OVER (
    PARTITION BY
      class_id
    ORDER BY
      score DESC
  ) AS next_name
FROM
  student;