2.4.1工具篇-SQL——快速掌握DQL

原创已于 2024-05-30 16:48:09 修改 · 1k 阅读

25 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库 #oracle

于 2024-02-28 17:44:06 首次发布

在数据分析的路上越走越嗨专栏收录该内容

12 篇文章

订阅专栏

本文介绍了SQL数据库的基础概念，包括数据库原理、基本术语和SQL语言分类。重点讲解了DQL的使用，包括关键词如SELECT、FROM、WHERE等的用法，以及不同数据库间的语法、函数和窗口函数差异。最后讨论了SQL查询优化的原则和方法。

2.4.1工具篇-SQL——快速掌握DQL

一、数据库的基本概念与语法
二、快速掌握DQL
三. 不同数据库之间的差异
四、优化原则及方法

这篇文章主要跟大家介绍关系型数据库的结构化查询语言，主要目的是让大家可以快速掌握DQL，提高数据获取和数据处理的能力！

一、数据库的基本概念与语法

1. 数据库原理

在这里我想用类比的方式尝试讲一下数据库到底是什么：
想象一下你是一个图书管理员，负责管理一个庞大的图书馆。在这个类比中：

图书馆（Database）：相当于一个数据库，它是一个存储和管理信息的系统，里面包含了各种各样的“书籍”（数据）。
书架（Table）：每个书架代表一张表，用来存放一类特定的数据，比如科幻小说、历史书籍等。每本书的位置（行）是唯一的，并且有确定的标签（列），如书名、作者、出版年份等。
书本（Row）：每一本书就对应着表中的一行记录，也就是一条具体的数据项，包含所有列的信息。
书签（Column）：书架上的书都有对应的标签，就像表格中的列一样。例如，“作者”这一列会列出每本书的作者名字。
索引（Index）：为了快速找到某本书，你会在目录或者电子检索系统中创建索引。同样地，在数据库中，对某个或某些列创建索引可以帮助我们迅速定位到所需数据。
主键（Primary Key）：就像每本书都有唯一的ISBN号，确保唯一标识每本书，表中也会有一列（或一组列）作为主键，保证每一行数据都是独一无二的。
查询（Query）：当你需要找一本特定的书时，就会向图书馆员提出请求，这就好比用户通过SQL语句（DQL）向数据库发出查询指令，获取所需数据。
事务（Transaction）：当图书管理员进行一系列操作，如借书、还书、更新记录等，这些操作必须作为一个整体成功完成或全部撤销，不能部分完成，这就类似于数据库中的事务概念，即保证数据的完整性和一致性。
并发控制（Concurrency Control）：多个读者同时借阅书籍时，图书管理员需要确保不会发生冲突，例如同一本书被多人借走。数据库同样如此，当多个用户同时修改数据时，需要采取措施避免数据不一致，这就是并发控制的作用。
备份与恢复（Backup and Recovery）：为了防止火灾、水灾等意外导致书籍丢失，图书馆会定期备份书籍并制定恢复计划。数据库也需要定期备份，并设计相应的策略来应对硬件故障或系统崩溃等情况下的数据恢复。
综上所述，数据库原理就是围绕如何安全、高效、可靠地存储和访问海量数据展开的一系列技术和规则。

2. 基本概念

通过上文的类比，提取几个基本概念：

库（Database）：是数据存储的核心单元，包含一个或多个表。
表（Table）：由行（Record）和列（Column）组成的数据集合，每一列代表一种属性，每行则表示一个实体实例的所有属性值。
列（Column）：定义了表中某一类数据的属性名和数据类型，如“员工ID”、“姓名”等。也会称作”字段“
行（Row）：记录或元组，是一条完整的数据记录，包含了表中所有列的值。
主键（Primary Key）：唯一标识表中一行记录的列或列组合，确保数据完整性及引用的唯一性。

3. 数据库语法概述：

大小写敏感性：
- 在SQL中，关键字通常不区分大小写，但具体规则取决于所使用的数据库系统。例如，在MySQL中，SQL语句默认是大小写不敏感的，但在某些数据库系统（如Oracle、SQL Server等）中，虽然关键词可以不区分大小写，但是表名、列名和变量名则可能区分大小写。
注释方式：
- 单行注释：在SQL中，单行注释使用两个连字号（–）后跟注释内容，例如：
```
-- 这是一个单行注释
SELECT * FROM table_name;
```
- 多行注释：可以使用起始于/，终止于/的块注释格式，例如：
```
/* 这是一个
   多行注释示例 */
SELECT * FROM table_name;
```

多条语句分割：
在SQL脚本中，通常每条语句结束时用分号;进行分割。比如：

SELECT * FROM customers; -- 第一条语句
UPDATE orders SET status = 'completed'; -- 第二条语句

4. SQL语言分类：

在这里插入图片描述

DQL (Data Query Language) 数据查询语言：主要负责从数据库中检索数据，最常用的DQL语句就是SELECT语句，用于查询特定条件下的数据集合。
DDL (Data Definition Language) 数据定义语言：用于创建、修改或删除数据库对象，包括表、视图、索引等。例如CREATE TABLE、ALTER TABLE、DROP TABLE等。
DML (Data Manipulation Language) 数据操作语言：用于插入、更新或删除数据库中的数据，而不涉及数据库结构的变化。常见的DML语句有INSERT INTO、UPDATE、DELETE等。
DCL (Data Control Language) 数据控制语言：主要用于管理和控制数据库用户的访问权限，如GRANT、REVOKE等命令，用来授权或撤销用户对数据库对象的操作权限。
TCL (Transaction Control Language) 事务控制语言：用于管理数据库事务，保证数据的一致性和完整性。常见命令有COMMIT（提交事务）、ROLLBACK（回滚事务）和SAVEPOINT（设置保存点）。通过这些命令，可以在多个操作完成后确保它们要么全部成功，要么全部失败。

对于数据分析师而言，重点掌握DQL，下面就一起来学习DQL

二、快速掌握DQL

1. 认识关键词

SELECT：
- 就像你在图书馆挑选书籍一样，SELECT关键字用来指定你想要从数据库中提取哪些信息或列。
```
SELECT column1, column2 FROM table;
```
这意味着你想从一个表中选择column1和column2两列的数据。
FROM：
- 类似于告诉图书管理员你要在哪个书架上找书，FROM关键字定义了要从中检索数据的表或视图。
```
SELECT * FROM Books;
```
*代表所有列，即表示你希望从名为“Books”的表中获取所有记录的所有列。
WHERE：
- 好比对图书管理员说：“我只想要关于科幻类且出版年份在2010年以后的书。”WHERE条件用于筛选满足特定要求的行。
```
SELECT * FROM Books WHERE Genre = '科幻' AND Year > 2010;
```
ORDER BY：
- 如果你要求图书按照作者姓氏字母顺序排列，那么在数据库中这就相当于使用ORDER BY关键字排序结果。
```
SELECT * FROM Books ORDER BY Author ASC;
```
上述语句会按Author列升序（ASC）排列查询结果。
ASC / DESC：
- 用于ORDER BY子句中指定排序方式，ASC表示升序排列（按字母或数字从小到大），DESC表示降序排列（从大到小）。

SELECT * FROM table ORDER BY column ASC;
SELECT * FROM table ORDER BY column DESC;

GROUP BY：
- 当你需要将相似的书籍归类时，比如计算每个作者的总销量，可以使用GROUP BY关键字。
```
SELECT Author, SUM(Sales) FROM Books GROUP BY Author;
```
这将展示每位作者及其作品的总销量。
HAVING：
- 在进行了GROUP BY操作后，如果还需要进一步过滤分组后的结果，就像只想看销量超过一定数量的作者，这时就需要用到HAVING。
```
SELECT Author, SUM(Sales) FROM Books GROUP BY Author HAVING SUM(Sales) > 10000;
```
这个例子展示了只显示销量总和超过10000本的作者及其对应的销量。
LIMIT / OFFSET：
- 类似于“请给我前10本书”，LIMIT关键字用于限制返回结果的数量。
```
SELECT * FROM Books LIMIT 10;
```
而OFFSET则指定了跳过多少条记录后开始选取数据。
```
SELECT * FROM Books LIMIT 10 OFFSET 20;
```
这个例子是从第21条记录开始，取出接下来的10条记录。
JOIN … ON：
在SQL查询语句中，JOIN关键字用于合并两个或多个表中的行，基于这些表之间的相关列的值。以下是一些常用的JOIN类型及其含义：

INNER JOIN（内连接）：
- 返回两个表中具有匹配关联条件的行的交集。
```
SELECT * 
FROM TableA
INNER JOIN TableB
ON TableA.key = TableB.key;
```
这个查询结果只包含TableA和TableB中key相等的行对。

LEFT JOIN（左连接）：
- 返回左表（第一个表）的所有记录以及右表（第二个表）中与之匹配的记录。如果右表没有匹配项，则对应的右表字段填充NULL值。
```
SELECT * 
FROM TableA
LEFT JOIN TableB
ON TableA.key = TableB.key;
```
结果包括TableA的所有行，即使在TableB中找不到匹配项。

RIGHT JOIN（右连接）：
- 反过来，返回右表的所有记录以及左表中与之匹配的记录。如果左表没有匹配项，则对应的左表字段填充NULL值。
```
SELECT * 
FROM TableA
RIGHT JOIN TableB
ON TableA.key = TableB.key;
```
结果包括TableB的所有行，即使在TableA中找不到匹配项。

FULL OUTER JOIN（全外连接）：
- 返回左表和右表中所有匹配和不匹配的记录组合。即返回两表的并集，任何一方有数据而另一方没有对应数据时，用NULL填充缺失的一方。
```
SELECT * 
FROM TableA
FULL OUTER JOIN TableB
ON TableA.key = TableB.key;
```
结果包含了两个表中所有的行，无论它们是否在对方表中有匹配项。

以上四种JOIN操作都是通过ON子句来指定连接条件，即根据哪些列的值进行匹配。

DISTINCT：

用于去除重复行，只返回唯一值。

SELECT DISTINCT column FROM table;

AS：

对查询结果中的列进行重命名或别名设置。

SELECT column AS alias_name FROM table;

LIKE：

用于对文本字段进行模式匹配搜索。

SELECT * FROM table WHERE column LIKE 'pattern%';

IS NULL / IS NOT NULL：

检查某个字段是否为空值或者非空值。

SELECT * FROM table WHERE column IS NULL;
SELECT * FROM table WHERE column IS NOT NULL;

IN：

判断某个字段的值是否在指定列表内。

SELECT * FROM table WHERE column IN (value1, value2, ...);

NOT：

与其它条件逻辑关键字结合使用，表示否定条件。

SELECT * FROM table WHERE column NOT IN (value1, value2);

OR：

逻辑运算符，用于连接两个或多个条件。当至少一个条件满足时返回真（True）。

SELECT * FROM table WHERE condition1 OR condition2;

AND：

逻辑运算符，用于连接两个或多个条件。只有当所有条件都满足时才返回真（True）。

SELECT * FROM table WHERE condition1 AND condition2;

BETWEEN：

检查某个值是否在一个范围之内。

SELECT * FROM table WHERE column BETWEEN value1 AND value2;

UNION / UNION ALL：

将两个或更多SELECT语句的结果集合并成一个，UNION ALL会保留所有记录包括重复行，而UNION则会去除重复行。

SELECT column FROM table1
UNION
SELECT column FROM table2;

SELECT column FROM table1
UNION ALL
SELECT column FROM table2;

SUM：

聚合函数，用于计算某一列所有数值的总和。

SELECT SUM(column) FROM table;

COUNT：

另一个聚合函数，用于统计表中符合条件的行数或者某列非空值的数量。

SELECT COUNT(*) FROM table; -- 统计所有行数
SELECT COUNT(column) FROM table WHERE column IS NOT NULL; -- 统计column非空值数量

CASE WHEN：

条件表达式，根据不同的条件返回不同的结果。

SELECT 
    CASE WHEN condition1 THEN result1
         WHEN condition2 THEN result2
         ELSE default_result
    END AS alias_name
FROM table;

INTERSECT / EXCEPT：

INTERSECT返回两个查询结果的交集，EXCEPT返回第一个查询结果减去第二个查询结果后的差集。

SELECT column FROM table1
INTERSECT
SELECT column FROM table2;

SELECT column FROM table1
EXCEPT
SELECT column FROM table2;

WITH（Common Table Expression, CTE）：

WITH关键字允许用户定义临时的结果集（称为公共表表达式），在查询中可被多次引用，有助于提高代码的可读性和复用性。

WITH SalesSummary AS (
    SELECT CustomerID, SUM(Sales) as TotalSales
    FROM Orders
    GROUP BY CustomerID
)
SELECT * FROM SalesSummary WHERE TotalSales > 5000;

上述例子中，首先使用WITH创建了一个名为SalesSummary的临时结果集，它包含了每个客户的总销售额。然后，在主查询中再次引用这个临时结果集，筛选出总销售额超过5000的客户信息。

2. 书写顺序

上面是最常用的10个关键词对应的抒写顺序，接下来我们通过一个案例来实战体会下
有两个表如下，我们要计算总销量超过20的前三个省份及对应销量，但要剔除江苏省
在这里插入图片描述
在这里，也顺便回顾一下，如果是用Excel一般会怎么处理？

我们再看一下用SQL怎么写

3. 执行顺序

当数据库收到我们输入的代码后，执行顺序是怎样的呢？其实和我们人为操作的思路和顺序是一致的。
在这里插入图片描述

三. 不同数据库之间的差异

关系型数据库（如MySQL、SQL Server、Oracle、PostgreSQL等）在DQL（Data Query Language，数据查询语言）的语法结构上大体遵循ANSI SQL标准，这意味着它们都支持基本的SELECT语句来从表中检索数据，并使用WHERE子句进行条件筛选，以及GROUP BY、ORDER BY、JOIN等操作。然而，在具体实现和某些高级特性上，不同数据库系统之间存在一些区别：

1. 语法差异：

LIMIT与OFFSET vs. ROW_NUMBER() / FETCH FIRST / TOP

MySQL使用LIMIT和OFFSET关键字来实现数据分页，例如：
```
SELECT * FROM table LIMIT 10 OFFSET 20;
```

SQL Server和Oracle通常不直接支持LIMIT和OFFSET。SQL Server中，可以使用TOP结合ORDER BY和ROW_NUMBER()函数实现类似功能：

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY some_column) AS RowNum
    FROM table
) t
WHERE RowNum BETWEEN 21 AND 30; -- 获取第21至第30条记录

在Oracle中，可以使用ROWNUM或FETCH FIRST（从12c版本开始）：

SELECT *
FROM (
    SELECT /*+ FIRST_ROWS(10) */ *
    FROM table
    ORDER BY some_column
)
WHERE ROWNUM <= 10; -- 获取前10条记录

-- Oracle 12c及更高版本
SELECT *
FROM table
ORDER BY some_column
FETCH FIRST 10 ROWS ONLY;

2. 函数差异：

函数差异主要体现在不同数据库系统提供的内置函数种类、名称以及功能实现上。以下是一些常见函数类别及其在不同数据库中的示例：

字符串函数
- MySQL：
  - CONCAT(str1, str2,...)用于连接多个字符串。
  - SUBSTRING(str, pos, length)提取字符串的一部分。
  - REPLACE(str, find_string, replace_with)替换字符串中所有出现的子串。
- SQL Server：
  - CONCAT(str1, str2,...)与MySQL类似，但在SQL Server 2012以前版本需要使用+进行拼接。
  - SUBSTRING(str, start, length)与MySQL基本一致。
  - REPLACE(str, search_for, replace_with)也与MySQL相同。
- Oracle：
  - CONCAT(str1, str2)，但通常推荐使用||操作符进行字符串连接以提高兼容性。
  - SUBSTR(str, start, length)是Oracle中的等价函数。
  - REPLACE(str, pattern, replacement)用法类似。
日期和时间函数
- MySQL：
  - NOW()或CURRENT_TIMESTAMP获取当前日期和时间。
  - DATE_FORMAT(date, format)格式化日期。
- SQL Server：
  - GETDATE()获取当前日期和时间。
  - FORMAT(date, 'format')（SQL Server 2012及以后）或CONVERT(varchar, date, style)用于格式化日期。
- Oracle：
  - SYSDATE获取当前日期和时间。
  - TO_CHAR(date, 'format')用于格式化日期。
数值函数
- MySQL：
  - ABS(number)计算绝对值。
  - ROUND(number, decimals)四舍五入数字。
- SQL Server：
  - ABS(number)同样计算绝对值。
  - ROUND(number, digits, [function])提供更多的选项，如指定舍入方式。
- Oracle：
  - ABS(n)同样计算绝对值。
  - ROUND(n[, m] [, fmt_model])可以指定小数位数和舍入模式。

3. 窗口函数：

这边将窗口函数单独拎出来，因为窗口函数是一种特殊类型的SQL聚合函数，它可以在数据集的一个“窗口”（一组行，根据某种定义）内执行计算，同时保持查询结果的整体行结构不变。

MySQL
- MySQL从版本8.0开始全面支持窗口函数，提供了诸如ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG()等功能。
- 例如，计算每个部门员工的排名：
```
SELECT 
    employee_id, department_id, salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees;
```
SQL Server
- SQL Server自较早版本就开始支持窗口函数，并且包含了一些其他数据库可能没有的高级窗口函数，如PERCENT_RANK(), CUME_DIST(), NTILE()等。
- 示例：找出每个分组内工资最高的前10%员工：
```
SELECT 
    employee_id, department_id, salary,
    NTILE(10) OVER (PARTITION BY department_id ORDER BY salary DESC) as percentile_rank
FROM employees
WHERE percentile_rank = 1; -- 取得最高10%
```

Oracle

Oracle同样具有丰富的窗口函数支持，包括上述提到的基本窗口函数以及FIRST_VALUE(), LAST_VALUE(), NTH_VALUE()等。

示例：查询每部门连续两次升职之间的间隔天数：

SELECT 
    employee_id, department_id, promotion_date,
    LAG(promotion_date, 1) OVER (PARTITION BY department_id ORDER BY promotion_date) as prev_promotion_date,
    promotion_date - LAG(promotion_date, 1) OVER (PARTITION BY department_id ORDER BY promotion_date) as days_between
FROM promotions;

PostgreSQL

PostgreSQL也提供了非常全面的窗口函数支持，并且有很强的灵活性，比如允许在窗口函数中使用复杂的分析表达式和多行范围定义。

示例：统计每一年每个用户首次购买到最近一次购买的天数差：

SELECT 
    user_id, date_trunc('year', purchase_date) as year,
    MIN(purchase_date) OVER (PARTITION BY user_id, year) as first_purchase,
    MAX(purchase_date) OVER (PARTITION BY user_id, year) as last_purchase,
    last_purchase - first_purchase AS days_diff
FROM purchases;