learn

MySQL作为一种广泛使用的关系型数据库管理系统，是每个数据分析师必备技能，下面让我们来探讨如何利用MySQL进行高效的数据分析。

基础语句

为了帮助小伙伴们清晰理解SQL查询的语法结构和运行顺序，我们应该知道下面的：

  • 语法结构：select–from–where–group by–having–order by–limit
  • 运行顺序：from–where–group by–having–order by–limit–select

SELECT & FROM

基本语法

在MySQL中，使用 SELECT 语句来指定要查询的字段。语法如下：

1
SELECT 字段名1, 字段名2, ...

使用 FROM 语句来指定数据来源的表。语法如下：

1
FROM 表名

示例：

假设有一个名为 students 的表，包含以下字段：id, name, age, gender。

  1. 查询所有字段

要查询 students 表中的所有字段，可以使用以下语句：

1
2
SELECT id, name, age, gender
FROM students

或者简化为：

1
2
SELECT *
FROM students
  1. 查询特定字段

如果只需要查询 name 和 age 字段，可以使用以下语句：

1
2
SELECT name, age
FROM students

通过这些基础的 SELECT 和 FROM 语句，我们就可以从数据库表中检索所需的数据。

除此之外，如果我们需要连续执行两三个简单的查询语句，只需要中间用;分开，执行的时候就不会报错了。

字段名的——别名

1
2
SELECT name AS 姓名, age AS 年龄
FROM students

AS可以省略，但是字段名后面需要加一个空格才可以加上别名

使用AS我们就可以改变我们的字段名，很简单，并且支持中文。

去重

在MySQL中，去重操作通常指的是从表中删除重复的记录或查询时排除重复的记录。

以下是两种常见的去重操作：

  1. 查询时去重：

    • 使用SELECT DISTINCT语句来查询不重复的记录。
    1
    2
    SELECT DISTINCT column1, column2, ...
    FROM table_name;

    这条语句将返回指定列中不重复的所有记录。

  2. 删除重复记录：

    • 删除表中的重复记录稍微复杂一些，需要使用临时表或子查询来实现。以下是一个常见的步骤：
      • 创建一个临时表，将不重复的记录插入到临时表中。
      • 删除原表中的所有记录。
      • 将临时表中的记录插入回原表。
      • 删除临时表。

    示例代码如下：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 创建临时表
    CREATE TEMPORARY TABLE temp_table AS
    SELECT DISTINCT * FROM original_table;

    -- 删除原表中的所有记录
    DELETE FROM original_table;

    -- 将临时表中的记录插入回原表
    INSERT INTO original_table SELECT * FROM temp_table;

    -- 删除临时表
    DROP TEMPORARY TABLE temp_table;

    另一种方法是使用子查询和DELETE语句来删除重复记录：

    1
    2
    3
    DELETE t1 FROM original_table t1
    INNER JOIN original_table t2
    WHERE t1.id > t2.id AND t1.column = t2.column;

    这条语句将删除original_table中所有重复的记录，保留每个重复组中的一个记录。

哦对，去重操作可能会影响数据的完整性和表之间的关系，因此在执行这些操作之前，请确保备份数据并了解其潜在影响。

SELECT之中简单的计算

在MySQL的SELECT语句中，我们可以进行各种简单的计算操作，比如加法、减法、乘法、除法等。这些计算可以直接在SELECT子句中进行，从而在查询结果中显示计算后的值：

操作类型 SQL 示例代码 描述
加 法 SELECT column1, column2, column1 + column2 AS sum_column FROM table_name; 返回column1和column2的值，并在结果中显示它们的和，命名为sum_column。
减 法 SELECT column1, column2, column1 - column2 AS difference_column FROM table_name; 返回column1和column2的值，并在结果中显示它们的差，命名为difference_column。
乘 法 SELECT column1, column2, column1 * column2 AS product_column FROM table_name; 返回column1和column2的值，并在结果中显示它们的乘积，命名为product_column。
除 法 SELECT column1, column2, column1 / column2 AS quotient_column FROM table_name; 返回column1和column2的值，并在结果中显示它们的商，命名为quotient_column。注意：如果column2的值为0，将会导致除以零的错误。
混合计算 SELECT column1, column2, (column1 + column2) * 2 AS calculated_column FROM table_name; 返回column1和column2的值，并在结果中显示它们的和乘以2的结果，命名为calculated_column。

WHERE

在MySQL中，WHERE子句用于在SELECT、UPDATE、DELETE语句中指定条件，从而对数据进行筛选。WHERE子句允许我们根据一个或多个条件过滤数据，只返回满足这些条件的记录。以下是对WHERE子句的详细补充：

基本语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition;

这里的condition是一个逻辑表达式，用于评估每一行数据是否满足条件。

常见的条件运算符：

假设我们有一个名为employees的表，其中存储了员工的年龄（age）、部门（department）、工资（salary）和电子邮件（email）等信息。

我们可以使用各种条件运算符来查询和筛选这个表中的数据：

运算符 描述 示例 说明
= 等于 SELECT * FROM employees WHERE age = 30; 查询年龄等于30岁的所有员工。
<> 或 != 不等于 SELECT * FROM employees WHERE department <> 'Sales'; 查询部门不是"Sales"的所有员工。
> 大于 SELECT * FROM employees WHERE salary > 5000; 查询工资大于5000的所有员工。
< 小于 SELECT * FROM employees WHERE age < 40; 查询年龄小于40岁的所有员工。
>= 大于等于 SELECT * FROM employees WHERE salary >= 5000; 查询工资大于或等于5000的所有员工。
<= 小于等于 SELECT * FROM employees WHERE age <= 40; 查询年龄小于或等于40岁的所有员工。
BETWEEN ... AND ... 在某个范围内 SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000; 查询工资在5000到10000之间的所有员工（包括5000和10000）。
IN (...) 在某个集合中 SELECT * FROM employees WHERE department IN ('Sales', 'Marketing'); 查询部门是"Sales"或"Marketing"的所有员工。
LIKE 匹配某个模式 SELECT * FROM employees WHERE name LIKE 'A%'; 查询名字以"A"开头的所有员工（%表示任意字符序列）。
LIKE 匹配某个模式 SELECT * FROM employees WHERE name LIKE '_A%'; 查询名字以"A"第二个字母的所有员工（第一个字符可以是任意字符（用_表示））
IS NULL 为空 SELECT * FROM employees WHERE email IS NULL; 查询没有电子邮件地址的所有员工。
IS NOT NULL 不为空 SELECT * FROM employees WHERE email IS NOT NULL; 查询有电子邮件地址的所有员工。
AND 逻辑与 SELECT * FROM employees WHERE age > 30 AND department = 'Sales'; 查询年龄大于30岁且部门是"Sales"的所有员工。
OR 逻辑或 SELECT * FROM employees WHERE age > 30 OR salary > 10000; 查询年龄大于30岁或工资大于10000的所有员工。
NOT 逻辑非 SELECT * FROM employees WHERE NOT department = 'Sales'; 查询部门不是"Sales"的所有员工。

组合条件：

  • 使用AND和OR组合多个条件：
    1
    SELECT * FROM employees WHERE age > 30 AND department = 'Sales';
  • 使用括号来明确优先级：
    1
    SELECT * FROM employees WHERE (age > 30 AND department = 'Sales') OR salary > 10000;

简化

1
2
3
4
5
6
7
8
select name 
from world 
where name like '%a%'
and name like '%e%' 
and name like '%i%'
and name like '%o%' 
and name like '%u%' 
and name not like '% %'

对于上面的SQL语句，我们可以进行简化。

通过正则表达式（Regular Expression）来简化这个查询。MySQL支持使用REGEXP或RLIKE运算符来进行正则表达式匹配：

1
2
3
4
SELECT name
FROM world
WHERE name REGEXP '[aeiou]'
  AND name NOT LIKE '% %';

解释：

  • name REGEXP '[aeiou]'：这个条件检查名字中是否包含至少一个元音字母（a, e, i, o, u）。
  • name NOT LIKE '% %'：这个条件确保名字中不包含空格。

WHERE子句是SQL查询中非常强大和灵活的工具，它允许我们精确地控制返回的数据集，从而满足各种数据分析和操作的需求。

ORDER BY

ORDER BY 是 SQL 中用于对查询结果进行排序的关键字。通过 ORDER BY，我们可以根据一个或多个列的值对结果集进行升序（ASC）或降序（DESC）排序。以下是 ORDER BY 的基本用法和一些示例：

基本语法

1
2
3
4
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
  • column1, column2, …：指定用于排序的列。
  • ASC：表示升序排序（默认）。
  • DESC：表示降序排序。

示例

  1. 单列升序排序：

    1
    2
    3
    SELECT name, age
    FROM employees
    ORDER BY age;

    这个查询将返回所有员工的名字和年龄，并按年龄升序排序。

  2. 单列降序排序：

    1
    2
    3
    SELECT name, age
    FROM employees
    ORDER BY age DESC;

    这个查询将返回所有员工的名字和年龄，并按年龄降序排序。

  3. 多列排序：

    1
    2
    3
    SELECT name, department, salary
    FROM employees
    ORDER BY department, salary DESC;

    这个查询将首先按部门升序排序，然后在每个部门内按工资降序排序。

  4. 使用列别名排序：

    1
    2
    3
    SELECT name, age AS employee_age
    FROM employees
    ORDER BY employee_age;

    这个查询将返回所有员工的名字和年龄（使用别名 employee_age），并按年龄升序排序。

  5. 使用列位置排序：

    1
    2
    3
    SELECT name, age
    FROM employees
    ORDER BY 2;

    这个查询将返回所有员工的名字和年龄，并按第二列（即 age）升序排序。

  6. 使用IN：

    1
    2
    3
    SELECT name, age, address
    FROM employees
    ORDER BY address IN ('上海', '北京'), name, age;

这个查询首先是根据address列是否为’上海’或’北京’进行排序。

这里的address IN ('上海', '北京')会返回一个布尔值（TRUE或FALSE），TRUE会被视为1，FALSE会被视为0。

因为默认是升序排序，所以地址为’上海’或’北京’的记录会被排在后面。

注意事项

  • ORDER BY 通常放在 SELECT 语句的最后。
  • 如果不指定排序顺序（即不使用 ASC 或 DESC），默认是升序排序。
  • 多列排序时，先按第一个列排序，然后在第一个列的每个值内按第二个列排序，依此类推。

LIMIT

LIMIT是SQL中用于限制查询结果返回行数的子句。它通常用于以下几种情况：

  1. 限制返回的行数：当你只需要查询结果的前几行时，可以使用LIMIT。
  2. 分页查询：在实现分页功能时，LIMIT和OFFSET结合使用可以指定从哪一行开始返回多少行数据。

基本语法

1
2
3
SELECT column1, column2, ...
FROM table_name
LIMIT number;

示例:

假设我们有一个employees表，我们只想获取前5名员工的信息：

1
2
3
SELECT name, age, address
FROM employees
LIMIT 5;

结合OFFSET使用

OFFSET用于指定从哪一行开始返回数据。通常用于分页查询。

1
2
3
SELECT column1, column2, ...
FROM table_name
LIMIT number OFFSET offset;

或者更常见的写法是：

1
2
3
SELECT column1, column2, ...
FROM table_name
LIMIT offset, number;

示例:

假设我们有一个employees表，我们想获取第7到第10名员工的信息：

1
2
3
SELECT name, age, address
FROM employees
LIMIT 4 OFFSET 6;

或者：

1
2
3
SELECT name, age, address
FROM employees
LIMIT 6, 4;

从6+1开始，然后获取4个数据的意思（即获取第7到第10名员工的信息）

注意事项

  1. 性能考虑：使用LIMIT可以提高查询性能，尤其是在处理大数据集时。
  2. 排序：在使用LIMIT时，通常需要结合ORDER BY子句来确保结果的顺序是确定的。

示例:

假设我们想获取年龄最大的前5名员工的信息：

1
2
3
4
SELECT name, age, address
FROM employees
ORDER BY age DESC
LIMIT 5;

聚合函数 & group by

聚合函数在SQL中用于对一组值进行计算，并返回单个值。它们通常与GROUP BY子句一起使用，以便对数据进行分组并计算每个组的聚合值。下面是一些常用的聚合函数及其使用示例：

常用的聚合函数

  1. COUNT：计算行数。
  2. SUM：计算总和。
  3. AVG：计算平均值。
  4. MAX：找出最大值。
  5. MIN：找出最小值。

示例：

假设我们有一个名为sales的表，包含以下列：product_id、sale_date、quantity和revenue。

  1. COUNT

计算总行数：

1
2
SELECT COUNT(*) AS total_rows
FROM sales;

计算特定产品的销售记录数：

1
2
3
SELECT product_id, COUNT(*) AS sales_count
FROM sales
GROUP BY product_id;
  1. SUM

计算总销售额：

1
2
SELECT SUM(revenue) AS total_revenue
FROM sales;

计算每个产品的总销售额：

1
2
3
SELECT product_id, SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_id;
  1. AVG

计算平均销售额：

1
2
SELECT AVG(revenue) AS average_revenue
FROM sales;

计算每个产品的平均销售额：

1
2
3
SELECT product_id, AVG(revenue) AS average_revenue
FROM sales
GROUP BY product_id;
  1. MAX

找出最大销售额：

1
2
SELECT MAX(revenue) AS max_revenue
FROM sales;

找出每个产品的最大销售额：

1
2
3
SELECT product_id, MAX(revenue) AS max_revenue
FROM sales
GROUP BY product_id;
  1. MIN

找出最小销售额：

1
2
SELECT MIN(revenue) AS min_revenue
FROM sales;

找出每个产品的最小销售额：

1
2
3
SELECT product_id, MIN(revenue) AS min_revenue
FROM sales
GROUP BY product_id;

结合GROUP BY和HAVING

HAVING子句用于对分组后的结果进行筛选。例如，找出销售额总和超过1000的产品：

1
2
3
4
SELECT product_id, SUM(revenue) AS total_revenue
FROM sales
GROUP BY product_id
HAVING SUM(revenue) > 1000;

聚合函数注意事项

  1. NULL值：聚合函数通常会忽略NULL值。例如，COUNT(*)会计算所有行，而COUNT(column_name)会忽略该列中的NULL值。
  2. 数据类型：确保聚合函数应用于正确的数据类型。例如，SUM和AVG通常用于数值类型。

GROUP BY基本语法

GROUP BY子句在SQL中用于将行分组，通常与聚合函数（如COUNT、SUM、AVG、MAX、MIN等）一起使用，以便对每个组进行计算。

1
2
3
SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2;

示例：

假设我们有一个名为orders的表，包含以下列：customer_id、order_date、product_id和quantity。

  1. 按单个列分组

计算每个客户的订单总数：

1
2
3
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
  1. 按多个列分组

计算每个客户和每个产品的订单总数：

1
2
3
SELECT customer_id, product_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, product_id;
  1. 结合聚合函数

计算每个客户的订单总数量：

1
2
3
SELECT customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY customer_id;
  1. 结合HAVING子句

筛选出订单总数超过10的客户：

1
2
3
4
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10;

GROUP BY注意事项

  1. 选择列：在SELECT子句中，除了聚合函数外，所有列都必须出现在GROUP BY子句中。
  2. NULL值：GROUP BY会将所有NULL值视为相同的组。
  3. 性能考虑：GROUP BY操作可能会消耗较多资源，尤其是在处理大数据集时。可以考虑使用索引优化性能。

示例：结合多个聚合函数

计算每个客户的订单总数、总数量和平均数量：

1
2
3
4
5
6
SELECT customer_id, 
       COUNT(*) AS order_count, 
       SUM(quantity) AS total_quantity, 
       AVG(quantity) AS average_quantity
FROM orders
GROUP BY customer_id;

HAVING

在SQL中，HAVING子句用于对聚合函数的结果进行筛选。它通常与GROUP BY子句一起使用，以便在分组后对组进行条件过滤：

语法

1
2
3
4
SELECT 列1, 列2, 聚合函数(列3)
FROM 表名
GROUP BY 列1, 列2
HAVING 聚合函数(列3) 条件;

示例:
假设我们有一个名为sales的表，其中包含以下列：id, product, quantity, price。我们想要找出每个产品的总销售额，并且只显示总销售额大于1000的产品。

1
2
3
4
SELECT product, SUM(quantity * price) AS total_sales
FROM sales
GROUP BY product
HAVING SUM(quantity * price) > 1000;

在这个示例中：

  1. SELECT product, SUM(quantity * price) AS total_sales：选择产品名称和总销售额。
  2. FROM sales：从sales表中获取数据。
  3. GROUP BY product：按产品名称分组。
  4. HAVING SUM(quantity * price) > 1000：筛选出总销售额大于1000的产品。

注意事项

  • HAVING子句只能用于包含聚合函数的查询。
  • HAVING子句在GROUP BY之后执行，因此它用于对分组后的结果进行筛选。
  • 如果不需要对分组后的结果进行筛选，可以直接使用WHERE子句对原始数据进行筛选。

where是对原始数据进行筛选(数据不改变)，而having是针对聚合函数的筛选(数据改变)

常见函数

数学函数

  1. round(x,y)——四舍五入函数：
  • round函数对x值进行四舍五入，精确到小数点后y位
  • y为负值时，保留小数点左边相应的位数为0，不进行四舍五入

下面是示例：

表达式 结果 说明
ROUND(3.15, 1) 3.2 对小数部分的第一位进行四舍五入，结果是3.2
ROUND(14.15, -1) 10 对整数部分的十位进行四舍五入，结果是10
ROUND(152.15, 0) 152 对小数部分的第一位进行四舍五入，结果是152
ROUND(155.15, -1) 160 对整数部分的十位进行四舍五入，结果是160
ROUND(152.15, -2) 200 对整数部分的百位进行四舍五入，结果是200
ROUND(152.15, -3) 0 对整数部分的千位进行四舍五入，结果是0

字符串函数

  1. concat(s1,s2,…)——连接字符串函数：
  • concat函数返回连接的参数s1,s2等产生的字符串
  • 任以参数为null时，返回null
    • 示例1：concat('My',' ','SQL')
    • 结果：My SQL(中间有空格哦)
    • 示例2：concat('My',null,'SQL')
    • 结果：null(只要有一个为null结果就返回null)
  1. replace(s,s1,s2)——替换函数：
  • replace函数使用字符串s2替代s中所有的s1
    • 示例：replace('MySQLMySQL','SQL','sql')
    • 结果：MysqlMysql
  1. left(s,n)、right(s,n)&substring(s,n,len)——截取字符串一部分的函数：
  • left函数返回字符串s最左边n个字符
    • 示例：left("Hello, World!", 5)
    • 结果："Hello"
  • right函数返回字符串s最右边n个字符
    • 示例：right("Hello, World!", 6)
    • 结果："World!"
  • substring函数返回字符串s从第n个字符起取长度为len的子字符串，n可以做为负值，则从倒数第n个字符起取长度为len的子字符串，没有len值则从第n个字符取到最后一位。
    • 示例1：substring("Hello, World!", 7, 5)
    • 结果："World"
    • 示例2：substring("Hello, World!", -6, 5)
    • 结果："World"
    • 示例3：substring("Hello, World!", 7)
    • 结果："World!"
  1. length()——统计字符串长度的函数：
  • 用于返回字符串的长度（即字符串中字符的数量）
    • 示例：select ... where length(content) > 15
    • 结果：返回content列字符串数量大于15的列
  • MySQL和SQLite使用length函数。
    PostgreSQL使用length函数。
    SQL Server使用len函数。
    Oracle使用length函数。

数据类型转换函数

  1. cast(x as type)——转换数据类型的函数
  • cast函数将一个类型的x值转换为另一类型的值
  • type参数可以填写char(n)、date、time、datetime、decimal等转换为对应的数值类型
  1. 将整数转换为字符串
    - 示例：cast(123 as char(5))
    - 结果：" 123"（注意：这里假设char(5)会在左侧填充空格以达到5个字符的长度）
  2. 将字符串转换为日期
    - 示例：cast("2023-04-05" as date)
    - 结果：2023-04-05（日期类型）
  3. 将字符串转换为时间
    - 示例：cast("14:23:55" as time)
    - 结果：14:23:55（时间类型）
  4. 将字符串转换为日期时间
    - 示例：cast("2023-04-05 14:23:55" as datetime)
    - 结果：2023-04-05 14:23:55（日期时间类型）
  5. 将浮点数转换为十进制数
    - 示例：cast(123.456 as decimal(10, 2))
    - 结果：123.46（十进制数类型，保留两位小数）

日期时间函数

  1. 获取年月日函数
    • year(date)
      • 示例：year('2023-04-05')
      • 结果：2023
    • month(date)
      • 示例：month('2023-04-05')
      • 结果：4
    • day(date)
      • 示例：day('2023-04-05')
      • 结果：5
  2. 对指定起始时间进行加减操作
    • date_add(date, interval expr type)
      • 示例：date_add('2023-04-05', interval 10 day)
      • 结果：2023-04-15
    • date_sub(date, interval expr type)
      • 示例：date_sub('2023-04-05', interval 5 day)
      • 结果：2023-03-31
  3. 计算两个日期之间间隔的天数
    • datediff(date1, date2)
      • 示例：datediff('2023-04-10', '2023-04-05')
      • 结果：5
  4. 将日期和时间格式化
    • date_format(date, format)
      • 示例：date_format('2023-04-05 14:23:55', '%Y-%m-%d %H:%i:%s')
      • 结果：2023-04-05 14:23:55
      • 示例：date_format('2023-04-05 14:23:55', '%d/%m/%Y')
      • 结果：05/04/2023
  5. 格式化符号说明：
格式化符号 说明 格式化符号 说明
%Y 4位数的年份（例如：2023） %y 2位数的年份（例如：23）
%m 2位数的月份（01到12） %c 月份的数字表示（1到12）
%d 2位数的日期（01到31） %e 日期的数字表示（1到31）
%H 2位数的小时（00到23） %k 小时（0到23）
%h 2位数的小时（01到12） %I 2位数的小时（01到12）
%l 小时（1到12）
%i 2位数的分钟（00到59）
%S 2位数的秒（00到59） %s 秒（从1970-01-01 00:00:00 UTC开始的秒数）
%f 微秒（000000到999999）
%p AM或PM
%r 时间，12小时制（hh:mm:ss AM/PM） %T 时间，24小时制（hh:mm:ss）
%W 星期几的全名（例如：Sunday） %a 星期的缩写名（例如：Sun）
%w 星期几的数字表示（0=Sunday, 6=Saturday）
%j 一年中的第几天（001到366）
%U 一年中的第几周（00到53），星期天是一周的第一天 %u 一年中的第几周（00到53），星期一是一周的第一天
%V 一年中的第几周（01到53），星期天是一周的第一天，用于ISO 8601 %v 一年中的第几周（01到53），星期一是一周的第一天，用于ISO 8601

条件判断函数

  1. if(expr,v1,v2)
  • 如果表达式expr是true返回v1，否则返回v2
    • 示例：if(5 > 3, 'Yes', 'No')
    • 结果：'Yes'
    • 示例：if(length('hello') > 10, 'Long', 'Short')
    • 结果：'Short'
  1. case when
  • case expr when v1 then r1 [when v2 then r2]…[else rn] end
    • 例如：case 2 when 1 then 'one' when 2 then 'two' else 'more' end
    • 结果：two
  • case when v1 then r1 [when v2 then r2]…[else rn] end
    • 例如：case when 1<0 then 'T' else 'F' end
    • 结果：F
  1. 详细说明case when
    case when 语句用于多条件判断，可以根据不同的条件返回不同的结果。它有两种常见的形式,第一种形式是基于表达式的判断，第二种形式是基于条件的判断。
形式一：基于表达式的 case when
1
2
3
4
5
6
CASE expr 
  WHEN v1 THEN r1 
  [WHEN v2 THEN r2]
  ...
  [ELSE rn] 
END
  • 示例：
    1
    2
    3
    4
    5
    6
    SELECT 
      CASE 2 
        WHEN 1 THEN 'one' 
        WHEN 2 THEN 'two' 
        ELSE 'more' 
      END AS result;
  • 结果：two
形式二：基于条件的 case when
1
2
3
4
5
6
CASE 
  WHEN v1 THEN r1 
  [WHEN v2 THEN r2]
  ...
  [ELSE rn] 
END
  • 示例：
    1
    2
    3
    4
    5
    SELECT 
      CASE 
        WHEN 1 < 0 THEN 'T' 
        ELSE 'F' 
      END AS result;
  • 结果：F
    case when语句在处理复杂逻辑和多分支场景时非常有用：

高级语句

SQL高级语句可以用于数据处理、分析和转换等，是数据岗工作所必备的技能。

窗口函数

窗口函数是SQL中用于对数据进行分组、排序和计算的高级功能。它们可以在不改变数据行数的情况下，对数据进行复杂的分析和处理。

标准语法

窗口函数的标准语法如下：

1
2
3
4
5
<窗口函数> OVER (
    [PARTITION BY <分区列>]
    [ORDER BY <排序列>]
    [<窗口框架>]
)
  • 窗口函数：可以是聚合函数（如 SUM、AVG）或者是其他的窗口函数（如 ROW_NUMBER）。
  • PARTITION BY：可选，指定分区列。
  • ORDER BY：可选，指定排序列。
  • 窗口框架：可选，定义窗口的范围，如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

滑动窗口：rows&range用法

1
<ROWS or RANGE clause> BETWEEN <Start expr> AND <End expr>

ROWS: 表示按照行的范围进行定义框架，根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。

常用：rows n perceding表示从当前行到前n行（一共n+1行）

RANGE：表示按照值的范围进行定义框架，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。适用于对日期、时间、数值排序分组

边界可取值 (Start expr & End expr) 说明
CURRENT ROW 当前行
N PRECEDING 前 n 行，n 为数字，比如 2 PRECEDING 表示前2行
UNBOUNDED PRECEDING 开头
N FOLLOWING 后 N 行，n 为数字，比如 2 FOLLOWING 表示后2行
UNBOUNDED FOLLOWING 结尾
RANGE 取特定日期区间 说明
RANGE INTERVAL '7-1' DAY PRECEDING 最近7天的值
RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING 前后一天和当天的值

示例1：

1
2
3
4
5
6
7
8
9
10
11
SELECT
    id,
    name,
    salary,
    departmentld,
    sum(salary)over(order by id) as '累计工资',
    sum(salary)over(order by id rows 1 preceding) as '本行加前一行',
    sum(salary)over(order by id rows 2 preceding) as '本行加前两行',
    sum(salary)over(order by id rows between 2 preceding and 1 following ) as '前两行加到后一行',
    sum(salary)over(order by id rows between current row and 1 unbounded following ) as '当前行加到后一行'
FROM employees;

这段 SQL 查询使用了窗口函数来计算不同范围内的工资累计值。如下图：

1

下面来让我们逐行解释每个窗口函数的含义：

  1. 累计工资： 
    1
    sum(salary) over (order by id) as '累计工资'
    这个窗口函数计算从第一行到当前行的工资累计值。由于没有指定 ROWS 或 RANGE 子句，默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即从分区的第一行到当前行的累计值。
  2. 本行加前一行： 
    1
    sum(salary) over (order by id rows 1 preceding) as '本行加前一行'
    这个窗口函数计算当前行和前一行的工资总和。ROWS 1 PRECEDING 表示窗口从当前行的前一行开始，到当前行结束。
  3. 本行加前两行： 
    1
    sum(salary) over (order by id rows 2 preceding) as '本行加前两行'
    这个窗口函数计算当前行和前两行的工资总和。ROWS 2 PRECEDING 表示窗口从当前行的前两行开始，到当前行结束。
  4. 前两行加到后一行： 
    1
    sum(salary) over (order by id rows between 2 preceding and 1 following) as '前两行加到后一行'
    这个窗口函数计算从当前行的前两行到当前行的后一行的工资总和。ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING 表示窗口从当前行的前两行开始，到当前行的后一行结束。
  5. 当前行加到后一行： 
    1
    sum(salary) over (order by id rows between current row and 1 unbounded following) as '当前行加到后一行'
    这个窗口函数计算从当前行到分区的最后一行的工资总和。ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 表示窗口从当前行开始，到分区的最后一行结束。

示例2：

1
2
3
4
5
6
7
8
SELECT
    id,
    name,
    salary,
    departmentld,
    count(*)over(order by salary range between 10000 preceding and 10000 following) as '员工人数（薪资在±10000）',
    count(*)over(order by salary range between 5000 preceding and 5000 following) as '员工人数（薪资在±5000）'
FROM employees;

这段 SQL 查询使用了窗口函数来计算在特定薪资范围内的员工人数。具体来说，它使用了 RANGE 子句来定义窗口，该窗口基于薪资的值范围。如下图：

2
让我们逐行解释每个窗口函数的含义：

  1. 员工人数（薪资在±10000）：

    1
    count(*) over (order by salary range between 10000 preceding and 10000 following) as '员工人数（薪资在±10000）'

    这个窗口函数计算薪资在当前行薪资的±10000范围内的员工人数。例如，如果当前员工的薪资是 15000 元，那么薪资在5000元到25000元之间的所有员工都会被统计在内。

    RANGE BETWEEN 10000 PRECEDING AND 10000 FOLLOWING 表示窗口从当前行薪资的前 10000 开始，到当前行薪资的后 10000 结束。

  2. 员工人数（薪资在±5000）：

    1
    count(*) over (order by salary range between 5000 preceding and 5000 following) as '员工人数（薪资在±5000）'

    这个窗口函数计算薪资在当前行薪资的±5000范围内的员工人数。例如，如果当前员工的薪资是 15000 元，那么薪资在10000元到20000元之间的所有员工都会被统计在内。

    RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING 表示窗口从当前行薪资的前 5000 开始，到当前行薪资的后 5000 结束。

排序窗口函数

排序窗口函数用于为结果集中的每一行分配一个唯一的序号或排名。常见的排序窗口函数包括：

  • ROW_NUMBER()：为每一行分配一个唯一的序号。
  • RANK()：为每一行分配一个排名，排名相同的行会有相同的序号，后续的排名会跳过。
  • DENSE_RANK()：与 RANK() 类似，但后续的排名不会跳过。
  • NTILE(n)：将结果集分成 n 个桶，并为每一行分配一个桶号。
  • PERCENT_RANK()：计算每一行的百分比排名，结果在0到1之间。
  • CUME_DIST()：计算每一行的累积分布，结果在0到1之间。
  1. 窗口函数只能写在select字句中
  2. 窗口函数中的partition by子句可以指定数据的分区，和group by要去重分组不同的是，partition by只分区不去重
  3. 窗口函数中没有partition by子句时，即不对数据分区，直接整个表为一个区
  4. 排序窗口函数中order by子句是必选项，窗口函数中order by子句在分区内，依据指定字段和排序方法对数据行排序
  5. rank()、dense_rank0、row_number()指定排序赋值方法，对比三个排序窗口函数的异同：
    • rank():跳跃式排序–比如数值为99，99，90，89，那么通过这个函数得到的排名为1，1，3，4
    • dense_rank():并列连续型排序–比如数值为99，99，90，89，那么通过这个函数得到的排名为1，1，2，3
    • row_number():连续型排序–比如数值为99，99，90，89，那么通过这个函数得到的排名为1，2，3，4
    • 根据对排序值的需求选择相应的排序窗口函数，由于值的不同特性(比如数值不重复)，这三个函数可以通用
      示例：
1
2
3
4
5
6
7
8
SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

在这个示例中，我们对每个部门的员工按工资从高到低进行排序，并为每个员工分配行号、排名和密集排名。

偏移分析函数

偏移分析函数用于访问窗口内其他行的数据，常见的偏移分析函数包括：

  • LAG(expression, offset, default)：用于访问当前行之前的第 offset 行的数据，如果没有找到则返回 default。
  • LEAD(expression, offset, default)：用于访问当前行之后的第 offset 行的数据，如果没有找到则返回 default。

示例：

1
2
3
4
5
6
7
SELECT
    employee_name,
    department,
    salary,
    LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS previous_salary,
    LEAD(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary) AS next_salary
FROM employees;

在这个示例中，我们使用 LAG 函数获取当前行的上一行的工资，使用 LEAD 函数获取当前行的下一行的工资。如果没有找到对应的行，则返回默认值 0。这样可以方便地进行比较分析。

表连接

表连接是关系型数据库中用于合并两个或多个表的数据的一种操作。表连接基于这些表之间的相关列进行操作。

内连接（INNER JOIN）：

内连接返回两个表中满足连接条件的记录。只有当连接条件为真时，记录才会被包含在结果集中。

1
2
3
4
SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

解释：

  • SELECT column1, column2, ...：选择需要查询的列。
  • FROM table1：指定主表。
  • INNER JOIN table2：指定要连接的表。
  • ON table1.common_field = table2.common_field：指定连接条件，即两个表中用于匹配的列。

左连接（LEFT JOIN 或 LEFT OUTER JOIN）：

左连接返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则结果集中对应的部分为 NULL。

1
2
3
4
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

解释：

  • SELECT column1, column2, ...：选择需要查询的列。
  • FROM table1：指定主表。
  • LEFT JOIN table2：指定要连接的表。
  • ON table1.common_field = table2.common_field：指定连接条件。

右连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：

右连接返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则结果集中对应的部分为 NULL。

1
2
3
4
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

解释：

  • SELECT column1, column2, ...：选择需要查询的列。
  • FROM table1：指定主表。
  • RIGHT JOIN table2：指定要连接的表。
  • ON table1.common_field = table2.common_field：指定连接条件。

全外连接（FULL OUTER JOIN 或 FULL JOIN）：

全外连接返回两个表中的所有记录，当没有匹配的记录时，结果集中对应的部分为 NULL。MySQL本身不支持FULL OUTER JOIN，但可以通过UNION操作实现类似效果。

1
2
3
4
5
6
7
8
9
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field
UNION
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

解释：

  • SELECT column1, column2, ...：选择需要查询的列。
  • FROM table1：指定主表。
  • LEFT JOIN table2：指定要连接的表。
  • ON table1.common_field = table2.common_field：指定连接条件。
  • UNION：合并两个查询结果。

交叉连接（CROSS JOIN）：

交叉连接返回两个表的笛卡尔积，即第一个表的每一行与第二个表的每一行组合。

1
2
3
SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

解释：

  • SELECT column1, column2, ...：选择需要查询的列。
  • FROM table1：指定主表。
  • CROSS JOIN table2：指定要连接的表。

子查询

子查询是嵌套在另一个查询中的查询，是优先于主查询的，它可以出现在SQL语句的多个部分，如SELECT、FROM、WHERE、HAVING等子句中。子查询可以返回单个值、一列值或一个表。

标量子查询（Scalar Subquery）：

标量子查询返回单个值，通常用在SELECT子句或WHERE子句中。

1
2
3
SELECT column1, column2, (SELECT scalar_column FROM table2 WHERE condition) AS alias
FROM table1
WHERE condition;

解释：

  • SELECT column1, column2, ...：选择需要查询的列。
  • (SELECT scalar_column FROM table2 WHERE condition)：标量子查询，返回单个值。
  • AS alias：为子查询结果指定别名。
  • FROM table1：指定主表。
  • WHERE condition：指定查询条件。

列子查询（Column Subquery）：

列子查询返回一列值，通常用在WHERE子句中，与IN、ANY、ALL等操作符一起使用。

1
2
3
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);

解释：

  • SELECT column1, column2：选择需要查询的列。
  • FROM table1：指定主表。
  • WHERE column1 IN (SELECT column1 FROM table2 WHERE condition)：列子查询，返回一列值。

行子查询（Row Subquery）：

行子查询返回一行值，通常用在WHERE子句中，与比较操作符一起使用。

1
2
3
SELECT column1, column2
FROM table1
WHERE (column1, column2) = (SELECT column1, column2 FROM table2 WHERE condition);

解释：

  • SELECT column1, column2：选择需要查询的列。
  • FROM table1：指定主表。
  • WHERE (column1, column2) = (SELECT column1, column2 FROM table2 WHERE condition)：行子查询，返回一行值。

表子查询（Table Subquery）：

表子查询返回一个表，通常用在FROM子句中，作为临时表使用。

1
2
3
SELECT a.column1, a.column2
FROM (SELECT column1, column2 FROM table2 WHERE condition) AS a
JOIN table1 ON a.common_field = table1.common_field;

解释：

  • SELECT a.column1, a.column2：选择需要查询的列。
  • FROM (SELECT column1, column2 FROM table2 WHERE condition) AS a：表子查询，返回一个表。
  • JOIN table1 ON a.common_field = table1.common_field：与主表进行连接。

相关子查询（Correlated Subquery）：

相关子查询依赖于外部查询的值，它会对每个外部查询的行进行一次评估。

1
2
3
SELECT column1, column2
FROM table1 a
WHERE column1 = (SELECT MAX(column1) FROM table2 WHERE table2.common_field = a.common_field);

解释：

  • SELECT column1, column2：选择需要查询的列。
  • FROM table1 a：指定主表，并为其指定别名。
  • WHERE column1 = (SELECT MAX(column1) FROM table2 WHERE table2.common_field = a.common_field)：相关子查询，依赖于外部查询的值。

本文作者：戴诺斯·拉姆 @ 拉姆的小树屋

本文链接：https://sherry14love.github.io/2024/07/25/learn/mysql/

本文版权：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！

留言
backtop