2.4.1工具篇-SQL——快速掌握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中,单行注释使用两个连字号(–)后跟注释内容,例如:
-
多条语句分割:
在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;
- 好比对图书管理员说:“我只想要关于科幻类且出版年份在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条记录。
- 类似于“请给我前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;
- MySQL从版本8.0开始全面支持窗口函数,提供了诸如
-
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%
- SQL Server自较早版本就开始支持窗口函数,并且包含了一些其他数据库可能没有的高级窗口函数,如
-
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;
- Oracle同样具有丰富的窗口函数支持,包括上述提到的基本窗口函数以及
-
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;
需要注意的是,不同数据库对于窗口函数的支持程度、性能优化策略以及可选参数可能存在差异,因此在编写跨数据库兼容的SQL语句时,需要针对特定数据库进行适当调整。