SQL简明数据分析教程

SQL与MySQL简介

数据库基础

从SQL的角度来看，数据库就是一个以某种有组织的方式存储的数据集合。我们可以采用数据库对数据进行有效的存储与管理，并运用数据库进行合理的处理与分析,使其转化为有价值的数据信息。

理解数据库的一种简单办法是将其想象为一个存放数据的文件柜， 往文件柜里存放数据资料时，先在文件柜中创建文件，然后将相关的数据资料放入特定的文件中，这种存储某种特定类型数据的结构化的文件就称为表。每个表都有唯一的表名（在同一数据库中不能有两个相同的表名）。表由列组成，每一列存储着某种特定的信息，并且具有相应的数据类型。表中的数据是按行存储的，所保存的每个记录存储在自己的行内，并且应尽量保证每一行都有一列（或几列）能够唯一标识该行的主键。表中的任何列都可以作为主键，只要它满足以下条件：任意两行主键值不同、主键列不允许NULL值，且习惯上不更新或重用主键值。

什么是SQL

SQL(发音为字母S-Q-L或sequel)的全称为“Structured Query Language” (结构化查询语言)，是一种专门用来与数据库沟通的语言，用以查询关系数据库表的内容，以及插入、更新和删除数据。SQL简洁易学，功能强大，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

SQL不是某个特定数据库供应商专有的语言，几乎所有主要的DBMS(数据库管理系统)都支持SQL，因此掌握该语言使你几乎能与所有数据库打交道。标准SQL由ANSI标准委员会管理，从而称为ANSI SQL。所有主要的DBMS即使有自己的扩展（提供执行特定操作的额外功能或简化方法），也都支持ANSI SQL。本教程主要使用标准SQL，提供的SQL示例代码在MySQL 5.7.19环境下测试通过，然而本教程所探讨的概念也适用于其他SQL环境。

下载与安装MySQL

SQL不是一个应用，而是一种语言。因此，为了学习SQL，我们还需要一个支持SQL语句执行的应用程序。实际上，数据的所有存储、检索、管理和处理都是由数据库软件——DBMS（数据库管理系统）完成的。较为流行的DBMS包括MySQL、 Oracle、 Microsoft SQL Sever、 PostgreSQL等，以下主要介绍MySQL。

MySQL是一个开源的关系型数据库管理系统，由瑞典MySQL AB公司开发，目前为 Oracle 旗下产品，是世界上最受欢迎的数据库管理系统之一。MySQL官网下载地址为： https://dev.mysql.com/downloads/mysql/。在Windows环境下以MSI方式安装步骤如下：
1) 在官网https://dev.mysql.com/downloads/windows/installer/5.7.html 下载最新5.7社区版的安装包之后，双击MSI安装文件，出现安装协议界面，同意协议，并单击【next】；

2) 选择安装类型。默认安装【developer Default】（包含有MySQL Workbench），此外，可以根据需要选择 【server only】（仅安装服务器）、 【client only】（仅安装客户端）、 【full】 （安装全部功能） 或者 【custom】 （自定义安装），右侧的【Setup Type Description】给出了每一种安装类型所包含的安装项目。

3) Check Requirements。如果提示电脑上缺少相关组件（如python），可以根据提示下载安装，也可以选择直接单击【next】进入下一步；

4) 选择【Execute】开始正式安装，并且可以看到安装进度；

5) 安装完成后根据提示点击【next】按钮进入如下产品配置面板，选择【next】；

6) 根据需要选择服务器类型（默认为 【Developer Machine】），TCP/IP默认端口为3306；

7) 进入【账户和角色】面板。根据提示设置root账户密码，并可根据需要添加用户；

8) 之后可根据安装向导保持默认选项，选择【next】及【execute】直到出现以下界面表示配置完成，并根据需要选择是否启动MySQL Workbench及MySQL Shell。

使用MySQL

有两种方式使用MySQL。在安装MySQL时会自带一个名为mysql命令行实用程序，这是一个纯文本工具，可以用来执行任何SQL语句。另外，MySQL 官方发布了一个名为MySQL Workbench的可视化管理工具，用户可以在安装MySQL时一起选择安装，也可以独立下载安装。较为流行的MySQL数据库可视化管理工具包括MySQL Workbench、 Navicat、 phpMyAdmin、Sequel Pro等。在Windows平台学习SQL时，推荐使用MySQL Workbench。

（1）mysql命令行实用程序
- 有两种方式使用mysql命令行实用程序。第一种方式是从开始菜单栏中打开MySQL 5.7 Command Line Client，输入密码，出现如下界面表示数据库连接成功：

• 第二种方式：如果已经将MySQL Sever安装目录下的bin子目录加入到了windows的环境变量中，可以直接在命令行中输入mysql -u root -p 命令，回车后输入密码即可连接成功。如下图所示：
  

• 数据库连接成功后，在mysql>提示下输入USE database 打开数据库，例如USE world就是打开world数据库。

• 在mysql>提示下输入SQL语句，每条语句须以分号（;）结束。结果将显示在屏幕上。
• 输入\h可以显示可能用到的命令列表，输入\s可以显示状态信息（如MySQL版本信息）。

• 输入\q或quit可以退出程序。

  （2）可视化管理工具 MySQL Workbench

  尽管我们可以在命令提示符下通过一行行的输入或者通过重定向文件来执行mysql语句，但该方式效率较低， 且由于没有执行前的语法自动检查， 输入失误造成的一些错误的可能性会大大增加。使用MySQL Workbench 可以通过可视化的方式直接管理数据库中的内容, 并且 MySQL Workbench 的 SQL 脚本编辑器支持语法高亮以及输入时的语法检查，方便我们学习SQL。可通过以下操作使用MySQL Workbench：

  • 运行MySQL Workbench。界面左下角列出了可用的MySQL数据库连接，可直接点击打开，输入密码便可连接。如果没有在此列出，可选择【MySQL Connections】右侧的加号按钮，创建新的连接。
    

  • 登陆成功后的Workbench界面概览如下图所示：
    
    其中，区域1显示的是数据库服务器中已经创建的数据库列表。区域2是关于数据库的操作列表。区域3是sql的编辑器和执行环境，区域4是执行结果的列表。

  • 输入SQL语句后，点击Execute(带有闪电图片) 或使用快捷键【ctrl+enter】运行SQL，将结果显示在下面。如下图所示：
    

导入样例表

完成前面的安装与设定工作后，MySQL中已经有一个内建的范例数据库world。但这个数据库比较简单，为了更好地练习SQL语句，我们还需要做最后一项准备工作：导入样例表。后续教程将会基于此样例表编写各式SQL语句。

（1）样例表描述
本教程采用著名畅销书Sams Teach Yourself SQL in 10 Minutes 一书中所提供的样例表。样例表描述的是一个随身物品推销商使用的订单录入系统，下图显示了5张表之间的关系：

其中，Customers表存储所有顾客信息；Vendors表存储销售产品的供应商，每个供应商在这个表中都有一个记录，包含了供应商名字、地址、所在城市等数据元素，其中使用vend_id作为其主键；Products表包含产品目录，每行一个产品，并且借助vend_id（供应商的唯一ID）与供应商相关联；Orders表存储顾客订单，每个订单都有唯一编号（order_num列），且根据cust_id列关联到相应的顾客；OrderItems表则存储每个订单中的实际物品，每个订单的每个物品一行。对于Orders表的每一行，在OrderItems表中有一行或多行与之对应。每个订单物品由订单号加订单物品（第一个物品、第二个物品等）唯一标识。订单物品用order_num列与其相应的订单相关联。此外，每个订单物品还包含该物品的产品ID（把物品关联到Products表）。上图中表之间的连线便说明了表之间的关系。

（2）导入样例表

从http://www.forta.com/books/0672336073/ 下载适用于MySQL的SQL脚本，含有两个文件：
- create.txt包含创建5个数据库表（包括定义所有主键和外键约束）的SQL语句。
- populate.txt包含用来填充这些表的SQL INSERT语句。

导入步骤如下：
- 根据上述教程，运行MySQL Workbench，并连接到MySQL。新建一个数据库，点击【Create a New Schema】按钮，出现如下对话框。输入新建数据库的名称，选择【apply】。

• 在弹出的确认窗口中选择【Apply】及【Finish】。然后在SCHEMAS一栏中双击创建成功的tjsql，表示选中该数据库，可以看到tjsql一栏变为黑体。
  

• 将create.txt中的内容复制粘贴到SQL窗口中，并选择执行，用以创建5个数据库表；
  同样，将populate.txt中的内容复制粘贴到SQL窗口中并执行，用以填充5个数据库表。

• 使用SELECT * FROM Customers; 语句测试结果如下图表示导入成功。
  

查看数据库和表

（1）查看数据库

在MySQL中可以建立许多数据库，当不知道可以使用哪些数据库时，可用MySQL的SHOW命令显示当前可用的数据库列表：

SHOW DATABASES;

显示当前服务器的所有数据库

在MySQL Workbench中输出结果为：

包含在这个列表中的可能是MySQL内部使用的数据库（如information_schema）。当然，你自己的数据库列表可能看上去与这里的不一样。

（2）选择数据库
在执行任何数据库操作前，都需要选择一个数据库，才能读取其中的数据。方法是使用USE关键字：

USE tjsql;

选择使用tjsql数据库

USE 语句并不返回任何结果。如果是在MySQL Workbench中运行该语句，可以在SCHEMAS一栏看到被选中的数据库名称变为黑体（与双击该数据库名称等效）；如果是在mysql 命令行实用程序中执行该语句，则会显示输出“Database changed” 表示数据库选择成功。

（3）查看数据表
进入到某个数据库后，我们可以使用 SHOW TABLES;来显示该数据库有多少个数据表：

SHOW TABLES;

显示当前数据库下所有的表

例如，选择tjsql数据库后运行该语句的输出为：

同样，SHOW 也可以用来显示表列：

SHOW COLUMNS FROM customers;

显示customers表每个字段的数据类型、是否允许 NULL 、键信息、默认值以及其他信息

输出为：

此外，MySQL还支持用 DESCRIBE 作为 SHOW COLUMNS FROM 的一种快捷方式。例如，DESCRIBE customers; 与SHOW COLUMNS FROM customers; 有着相同的输出，两者完成同样的功能。

MySQL所支持的其他 SHOW 语句还有：
- SHOW STATUS ：用于显示广泛的服务器状态信息；
- SHOW CREATE DATABASE和SHOW CREATE TABLE ：用来显示创建特定数据库或表的MySQL语句；
- SHOW GRANTS ：用来显示授予用户的安全权限；
- SHOW ERRORS 和 SHOW WARNINGS ：用来显示服务器错误或警告消息。

SELECT基础查询

在第一部分，我们介绍了数据库和SQL的概念，以及MySQL安装和使用的一些相关知识，并导入了准备好的样例表，完成了SQL环境的基本搭建。在第二部分，我们将会介绍SELECT语句的基础知识，并用它来进行一些基本的SQL查询。

检索数据

在数据分析过程中，SELECT语句是最经常使用的SQL语句。它的用途是从一个或多个表中检索数据。为此，至少需要给出两条信息——想选择什么，以及从什么地方选择。
（1）检索单个列

最基础的SELECT 语句如下所示：

SELECT prod_name
FROM Products;

从 Products 表中检索一个名为prod_name 的列

在上述语句中，所需的列名在 SELECT 关键字之后给出， FROM关键字指出从其中检索数据的表名。返回的数据没有过滤，也没有经过排序。输出如下所示：

（2）检索多个列
从一个表中检索多个列，只需在 SELECT 关键字后给出多个列名，列名之间以逗号分隔：

SELECT prod_id, prod_name, prod_price
FROM Products;

从 Products 表中检索prod_id, prod_name, prod_price三个列

（3）检索所有列
使用星号（*）通配符可以检索所有列而不必逐个列出所有的列名：

SELECT *
FROM Products;

返回 Products 表中所有列

注：使用通配符的好处是比较方便，且能检索出名字未知的列。但通常会降低检索和应用程序的性能，除非确实需要表中的每个列，否则最好别使用*通配符。

（4）检索不同的行
如前所述，SELECT语句会返回所有匹配的行。如果不希望相同的记录重复出现，可使用 DISTINCT 关键字：

SELECT DISTINCT vend_id
FROM Products;

返回 Products 表中不同（唯一）的vend_id 行

该语句只返回三行不重复值的vend_id。如下所示：

与之相对的，如果不指定DISTINCT关键字，只输入SELECT vend_id FROM Products;则会返回9行（分别对应Products 表中9种产品）：

注：不能部分使用 DISTINCT关键字。DISTINCT作用于所有的列，不仅仅是跟在其后的那一列。例如，指定SELECT DISTINCT vend_id, prod_price，除非指定的两列完全相同，否则所有的行都会被检索出来。

（5）限制结果

若只需要返回第一行或前几行，可使用 LIMIT 子句：

SELECT prod_name
FROM Products
LIMIT 5;

只返回 Products 表中前5行记录

除了使用LIMIT关键字指定返回的行数之外，还可以使用OFFSET 关键字指定从哪一行开始检索：

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

返回 Products 表中从第5行起的5行数据

可以发现，在这个例子中只返回了4行，这是因为在Products表中只有9种产品，而第一个被检索的产品称为第0行，最后一个被检索的产品是第8行，所以最终只返回了4行数据。

注：MySQL还支持简化版的“LIMIT 4O FFSET 3”，即“LIMIT 3,4”，表示从行3开始取4行。

排序检索数据

这个部分会简单介绍如何使用 SELECT 语句的 ORDER BY 子句对检索出来的数据在一个或多个列上进行排序。

（1）按单个列排序
使用ORDER BY子句进行排序方法如下：

SELECT prod_name
FROM Products
ORDER BY prod_name;

检索 Products 表的prod_name列，并按prod_name列字母顺序排序

与之对比，若不指定顺序，输入“SELECT prod_name FROM Products”语句则输出结果为：

如果不排序，数据一般以它在底层表中出现的顺序显示，这有可能是数据最初添加到表中的顺序。但如果数据进行过更新或删除，则这个顺序将会受到DBMS重用回收存储空间的方式的影响。因此，关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。

（2）按多个列排序

要按多个列排序，只需指定多个列名，列名之间用逗号分开即可：

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
检索Products 表中的3个列，先按价格排序，然后按名称排序

注： ORDER BY子句允许选用非检索的列作为排序依据。

（3）按列位置排序
ORDER BY支持按相对列位置进行排序：

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;
先按SELECT清单中第二个列prod_price（价格）进行排序，然后按第三个列prod_name（名称）排序

按列相对位置排序好处在于不用重新输入列名，但需要特别注意每个列名的确切位置，以免在对SELECT清单进行更改时错用列名排序。

（4）指定排序方向
排序时，默认为升序排序（从A到Z），但可以指定DESC关键字降序排序（从Z到A）：

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

对prod_price列降序排序，对prod_name列仍按升序排序。

从上述例子中可以看出，DESC关键字只作用于直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。

使用 ORDER BY 和 LIMIT 的组合，能够找出一个列中最高或最低的值。下面的例子演示如何找出最昂贵物品的值：

SELECT prod_price
FROM Products
ORDER BY prod_price DESC
LIMIT 1;

注：需要注意ORDER BY子句的位置。在给出 ORDER BY 子句时，应该保证它位于 FROM 子句之后。如果使用 LIMIT关键字 ，则它须位于 ORDER BY之后。一般来说，ORDER BY 子句必须是SELECT 语句中的最后一条子句。

过滤数据

本节会介绍如何使用SELECT语句的WHERE子句指定搜索条件过滤返回的数据。

（1）使用WHERE子句

在 SELECT 语句中，WHERE子句在FROM子句之后给出，返回满足指定搜索条件的数据：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

从products表中检索两个列，但只返回prod_price值为3.49的行

除了上述例子的相等条件判断，WHERE子句还支持以下条件操作符：

例如，使用<>操作符（或!=）进行不匹配检查：

SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

从Products表中列出所有不是供应商DLL01制造的产品

再如，使用BETWEEN操作符可匹配处于某个范围内的值。需要指定范围的端点值：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5.99 AND 9.49;

检索价格在5.99美元和9.49美元之间的所有产品(包括5.99美元和9.49美元)

此外，还有一个特殊的IS NULL子句，可用来检查具有 NULL 值的列：

SELECT cust_name
FROM CUSTOMERS
WHERE cust_email IS NULL;

返回没有邮件地址为NULL的顾客。

（2）组合WHERE子句

SQL还允许使用AND或OR操作符组合出多个WHERE子句，例如使用AND操作符：

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格（需同时满足两个条件）

同理，可使用OR操作符检索匹配任一条件的行：

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

检索由供应商DLL01或供应商BRS01制造的所有产品的名称和价格（只需满足任一条件即可）

（3）求值顺序

WHERE子句允许包含任意数目的AND和OR操作符以进行复杂、高级的过滤。但需要注意求值顺序：AND操作符优先级高于OR操作符，但可以使用圆括号明确地指定求值顺序：

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = ‘BRS01’)
AND prod_price <= 10;

选择由供应商DLL01或BRS01制造的，且价格在10美元及以下的所有产品

（4）IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。条件之间用逗号分隔：

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

检索由供应商DLL01和BRS01制造的所有产品

注：IN操作符完成了与OR相同的功能，但与OR相比，IN操作符有如下优点：
- 有多个条件时，IN操作符比一组OR操作符更清楚直观且执行得更快；
- 在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理；
- IN操作符的最大优点是可以包含其他SELECT语句，能动态地建立WHERE子句。

（4）NOT操作符

NOT操作符用来否定跟在它之后的条件：

SELECT vend_id,prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

用来检索除了供应商DLL01之外制造的所有产品

上面的例子也可以使用<>操作符来完成。但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行：

SELECT vend_id,prod_name
FROM Products
WHERE vend_id NOT IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

用来检索除了供应商DLL01和BRS01之外制造的所有产品

注：和多数其他 DBMS允许使用 NOT 对各种条件取反不同，MySQL支持使用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反。

（5）LIKE操作符

使用LIKE操作符和通配符可以进行模糊搜索，以便对数据进行复杂过滤。最常使用的通配符是百分号（ % ），它可以表示任何字符出现任意次数：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

检索产品名含有[bean bag]字段的所有产品

另一个有用的通配符是下划线（_）。它的用途与%一样，但只匹配单个字符，而不是多个或0个字符：

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '_ inch teddy bear';

检索产品名含有[ inch teddy bear]字段的产品，且[ inch teddy bear]字段前有且只能有一个字符

注：通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索。

2.4 函数

这部分将会介绍什么是计算字段以及MySQL支持的部分数据处理函数。

（1）计算字段
有时候从数据库中直接检索出来的数据并不是我们想要的，我们希望得到的是经过转换、计算或格式化过的数据。例如，在物品订单表中存储的是物品的价格和数量，但我们需要的是每个物品的总价格（用价格乘以数量即可）。这时候计算字段就可以排上用场了。计算字段并不实际存在于数据库表中，而是运行时在SELECT语句内创建的。

SELECT prod_id,
       quantity,
       item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

求得订单号20008中每个物品的数量、单价以及总价格（单价乘数量）

在该例中，quantity*item_price 表示对检索出的数据进行算术计算， AS expanded_price指示SQL创建了一个包含指定计算结果的名为expanded_price的计算字段。其中expanded_price叫做别名，用AS关键字赋予，客户端应用可以像使用其他列一样引用这个新计算列。

SQL支持+、-、*、/（加减乘除）四种基本算术操作符。此外，圆括号可用来区分优先顺序。

除了算术运算，我们还经常使用拼接字段。例如，vendors 表的两个列 vend_name 和 vend_country分别存储了供应商的名字和位置信息，假如要生成一个供应商报表，需要按照 name(location) 这样的格式列出供应商的信息，即把vend_name和 vend_country两个列拼接起来。在MySQL中，可使用Concat() 函数来拼接两个列：

SELECT Concat(vend_name,’(’,vend_country,’)’)
 AS vend_title
FROM Vendors
ORDER BY vend_name;

将vend_name和 vend_country两个列拼接成 name(location) 的格式，并叫做别名vend_title

（2）数据处理函数

与大多数其他计算机语言一样，SQL支持利用用函数来处理数据。上述用来拼接两个列的Concat()就是函数的一个例子 。大多数SQL实现支持4种类型的函数：文本处理函数、日期和时间处理函数、数值处理函数以及返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。一下简要介绍前三种数据处理函数。
- 文本处理函数：用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。例如将文本转换为大写的Upper() 函数：

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

将vend_name列转换为大写

下表列出了一些常用的文本处理函数。

上表中的SOUNDEX 需要做进一步的解释。 SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

例如，Customers表中有一个顾客Kids Place，其联系名为Michelle Green。但如果这是 错误的输入，此联系名实际上应该是Michael Green，如果使用SOUNDEX()函数进行搜索，它就有匹配所有发音类似于Michael Green的联系名：

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

匹配所有发音类似于Michael Green的联系名

• 日期和时间处理函数：用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。目前为止，我们都是用比较数值和文本的 WHERE 子句过滤数据，但数据经常需要用日期进行过滤。

下表列出了一些常用的日期和时间处理函数。

需要注意MySQL使用的日期格式必须为yyyy-mm-dd，例如2017年1月1日，应写成2017-01-01。一般如果要对日期进行比较，需使用Date()函数：

SELECT cust_id, order_num,order_date
FROM Orders
WHERE Date(order_date) BETWEEN '2012-01-01' AND '2012-01-31';

匹配2012年1月份下的所有订单

还有另外一种办法是使用Year()和Month()函数：

SELECT cust_id, order_num,order_date
FROM Orders
WHERE Year(order_date) = 2012 AND Month(order_date) = 1;

匹配2012年1月份下的所有订单

• 数值处理函数：用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。这些函数主要用于代数、三角或几何运算，因此没有串或日期时间处理函数的使用那么频繁。但在主要DBMS的函数中，数值函数是最一致最统一的函数，而文本处理函数、日期和时间处理函数在不同的DBMS中差别却很大。以下是一些常用的数值处理函数：

（3）聚集函数

有时我们实际需要的是表中数据的汇总信息，而不是实际数据本身，比如确定表中行数、表列的最大值、最小值和平均值等。为此，MySQL给出了5个聚集函数：

• AVG()函数：返回所有列的平均值，也可以用来返回特定列或行的平均值。如：

SELECT AVG(prod_price) AS avg_price
FROM Products;

返回Products表中所有产品的平均价格

• COUNT()函数：确定表中行的数目或符合特定条件的行的数目。COUNT()函数有两种使用方式。第一种是使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值：

SELECT COUNT(*) AS num_cust
FROM Customers;     

返回Customers表中顾客的总数

另一种方式是使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值：

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

返回Customers表中具有电子邮件地址的顾客的总数

在此例子中，使用COUNT(cust_email)对cust_email列中有值的行进行计数，输出结果为3表示5个顾客中只有3个顾客有电子邮件地址，忽略NULL值。

• MAX()函数：返回指定列中的最大值，且要求指定列名：

SELECT MAX(prod_price) AS max_price
FROM Products;

返回Products表中最贵物品的价格

注：MAX()一般用来找出最大的数值或日期值，在用于文本数据时，如果数据按相应的列排序，则 MAX() 返回最后一行。
- MIN()函数：与MAX()函数相反，返回指定列的最小值。
- SUM()函数：返回指定列值的和（总计）。例如：

SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

返回订单中所有物品价钱之和

在使用以上聚集函数时，如果只想聚集不同值，可指定 DISTINCT 参数去重：

SELECT AVG(DISTINCT prod_price) AS price_avg,
        MAX(DISTINCT prod_price) AS price_max
FROM Products
WHERE vend_id = 'DLL01';

返回产品的平均价格及最高价格，但只考虑各个不同的价格

从本例中可以看出，使用了DISTINCT后，排除了多个具有相同的较低价格的物品之后，price_avg相对比较高。但是将DISTINCT用于MIN()和MAX()并不会改变一个列中的最小值和最大值。此外，DISTINCT不能用于COUNT(*)。
注：与DISTINCT参数相对的是ALL参数，但ALL参数不需要指定，因为这是默认的。

2.5 分组数据

本小节将会介绍如何使用GROUP BY 子句和 HAVING 子句分组数据。

（1）使用GROUP BY 子句创建分组

GROUP BY 子句能把数据分为多个逻辑组，并使用聚集函数对每个组进行聚集计算，以解决诸如返回每个供应商提供的产品数目、返回只提供单项产品的供应商所提供的产品或返回提供10个以上产品的供应商等问题。考察以下例子：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;       

vend_id为产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立），GROUP BY 子句将根据vend_id进行数据分组

从输出中可以看到，供应商BRS01有3个产品，供应商DLL01有4个，供应商FNG01有2个产品。使用GROUP BY 子句时，应注意以下问题：
- GROUP BY子句可以包含任意数目的列，但每一列都必须是检索列或有效的表达式（但不能是聚集函数），且不能使用别名。
- 除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出。
- 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组

（2）使用HAVING 子句过滤分组

除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，在上例中，我们已使用GROUP BY子句根据供应商进行数据分组，并返回每个供应商有多少个产品。现如果只想返回有三个产品以上的供应商：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 3;

GROUP BY子句根据供应商进行数据分组，HAVING 子句过滤并返回三个产品以上的供应商

从以上例子可以看出，HAVING子句和WHERE子句功能相似，有关WHERE的所有用法（包括通配符条件和带多个操作符的子句）都适用于HAVING。唯一的差别是，WHERE过滤行，而HAVING过滤分组。

当然，我们也可以在一条语句中同时使用WHERE和HAVING子句：

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 3;

和上述例子相比，多增加了一条WHERE子句，过滤所有prod_price至少为4的行

加上WHERE子句，输出结果少了一行，因为供应商DLL01销售4个产品，但价格都在4以下。

至此，回顾一下SELECT语句中子句的顺序：

子查询

本小节将会介绍什么是子查询，如何使用它们。

（1）利用子查询进行过滤

首先考察以下问题：本教程中使用的数据库表都是关系表，其中关于订单的信息存储在两个表中：Orders表存储订单编号、客户ID、订单日期；OrderItems表存储各订单的具体包含物品；顾客的实际信息存储在Customers表中。

现在，假如需要列出订购物品RGAN01的所有顾客，应该怎样检索？下面列出了具体步骤：
1. 检索包含物品RGAN01的所有订单的编号；
2. 检索具有前一步骤列出的订单编号的所有顾客的ID；
3. 检索前一步骤返回的所有顾客ID的顾客信息。

上述每个步骤都可以单独作为一个查询来执行。可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句。第一条SELECT语句较为简单：

SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

对prod_id为RGAN01的所有订单物品，检索其order_num列

现在我们知道了订单编号，只需查询与订单20007和20008相关的顾客ID：

SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);

检索与订单20007和20008相关的顾客ID

现在，结合这两个查询，把第一个查询变为子查询：

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                        FROM OrderItems
                        WHERE prod_id = 'RGAN01');

检索订单中包含物品RGAN01的顾客ID

现在得到了订购物品RGAN01的所有顾客的ID，第三步是检索这些顾客ID的顾客信息：

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN ('1000000004','1000000005');

检索这些顾客ID为1000000004和1000000005的顾客信息

仿照上例，把其中的WHERE子句转换为子查询：

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                     FROM orders
                     WHERE order_num IN (SELECT order_num
                                            FROM OrderItems
                                            WHERE prod_id = 'RGAN01'));

检索订购物品RGAN01的所有顾客

为了执行上述SELECT语句，MySQ实际上必须执行三条SELECT语句。最里边的子查询返回订单号列表，此列表用于其外面的子查询的WHERE子句。外面的子查询返回顾客ID列表，此顾客ID列表用于最外层查询的WHERE子句。最外层查询返回所需的数据。

可见，在WHERE子句中使用子查询能够编写出功能很强且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。并且使用子查询并不总是执行这种类型的数据检索的最有效的方法，后续学习连接（JOIN）时我们还会遇到这个例子。

（2）作为计算字段使用子查询

使用子查询的另一方法是创建计算字段。假如需要显示Customers表中每个顾客的订单总数，其中订单与相应的顾客ID存储在Orders表中。执行这个操作，要遵循下面的步骤：
1. 从Customers表中检索顾客列表；
2. 对于检索出的每个顾客，统计其在Orders表中的订单数目。
实现如下：

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

SELECT COUNT(*)对表中的行进行计数，并且通过提供一条WHERE子句来过滤某个特定的顾客ID，仅对该顾客的订单进行计数。最外层外层查询对每个顾客执行COUNT(*)。

注意到子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名（Orders.cust_id和Customers.cust_id），将Orders表中的cust_id和当前正从Customers表中检索的cust_id进行比较。它的语法是用一个句点分隔表名和列名。当有可能混淆列名时，就必须使用完全限定列名来避免歧义。

JOIN和UNION查询

我们知道，作为关系数据库管理系统（RDBMS）一部分的关系数据库，是用相互之间存在关系的表来组织数据的。下图展示了本教程所使用的五张表之间的关系：

我们曾在“导入样例表”小节中对这五张表的内容及其关系的进行了详细地描述。虽然可以建一张更大的表来存储所有的订单和客户细节，但是使用五张表有其优点：

• 第一个优点是节省存储空间。同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；
• 另一个优点是容易进行变更与修正。如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可；
• 第三个优点是，数据不重复使得处理数据和生成报表更简单。如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。

简而言之，关系表的设计就是要把信息分解成多个表，各表通过某些共同的值互相关联，从而更有效地存储以及更方便地处理。但在实际当中，经常有必要一次性跨多个表来访问相关数据。为了完成这一任务，SQL查询语句使用JOIN语句来指定多个表之间的关系。

JOIN连接

（1）内连接

连接（JOIN）使数据库用户能够从2个或多个表中选择适当的列。下面的SQL查询给出了一个最常见类型的连接示例：内连接（inner join）.

SELECT vend_name, prod_name, prod_price
FROM Vendors AS V
INNER JOIN Products AS P
      ON V.vend_id = P.vend_id;     

从Vendors和Products两张表中返回各个供应商所提供的产品和价格

上面语句中，INNER JOIN将Vendors和Products两表关联，关联需要一个或多个字段作为连接桥梁。例子中的桥梁就是vend_id，我们使用on语句，将Vendors表的vend_id字段和Products的id字段匹配。

这里需要注意的是，因为字段可能重名，所以需要使用完全限定名以避免歧义。此外，表Vendors和Products分别使用AS关键字定义了别名V和P。用别名来代替完整的表明以提高查询的可读性。

在“子查询”该小节中，我们曾用以下子查询语句来检索订购物品RGAN01的所有顾客：

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                     FROM orders
                     WHERE order_num IN (SELECT order_num
                                            FROM OrderItems
                                            WHERE prod_id = 'RGAN01'));

现在我们使用连接三个表来执行相同查询：

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num
 AND prod_id = 'RGAN01';

检索订购物品RGAN01的所有顾客

这个查询中的返回数据需要使用3个表。但在这里，我们没有在嵌套子查询中使用它们，而是使用了两个WHERE子句条件来连接表。第三个WHERE子句条件过滤产品RGAN01的数据。

（2）外连接

如上所述，在所有连接类型中，Inner Join（内连接）最常见，可以缩写成Join，找的是两张表共同拥有的字段。但假设我们想对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客，这就需要包含那些在相关表中没有关联行的行。这种连接称为外连接（OUTER JOIN）。

外连接语法与内连接类似，但必须使用RIGHT或LEFT关键字指定包括其所有行的表（LEFT指出的是OUTER JOIN左边的表，而RIGHT指出的是OUTER JOIN右边的表）。下面的例子使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
 ON Customers.cust_id = Orders.cust_id;

检索包括没有订单顾客在内的所有顾客

输出结果中，cust_id为1000000002那一行的Order_num为空，就是因为cust_id无法匹配上，返回了Null。如果改成Inner Join，则不会返回整个cust_id为1000000002所在行。这是Inner Join和Left Join的区别。

另外一种基本的外连接形式是RIGHT OUTER JOIN。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。如A LEFT JOIN B 等价于 B RIGHT JOIN A因此，这两种外联结可以互换使用，哪个方便就用哪个。

UNION组合查询

多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是，SQL也允许利用UNION操作符来组合多条SELECT语句，并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

主要有两种情况需要使用组合查询：
- 在一个查询中从不同的表返回结构数据；
- 对一个表执行多个查询，按一个查询返回数据。

使用UNION很简单，只需在各条SELECT语句之间放上关键字UNION：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

检索Illinois、Indiana和Michigan等美国几个州的所有顾客的报表，以及包括不管位于哪个州的所有的Fun4All。

第一条SELECT把Illinois、Indiana、Michigan等州的缩写传递给IN子句，检索出这些州的所有行。第二条SELECT利用简单的相等测试找出所有Fun4All。两条SELECT语句之间用UNION关键字分隔。

上述UNION语句从查询结果集中自动去除了重复的行，例如Indiana州有一个Fun4All单位，所以两条SELECT语句都返回该行。使用UNION时，重复的行会被自动取消。但如果想返回所有的匹配行，可使用UNION ALL代替UNION：

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

检索Illinois、Indiana和Michigan等美国几个州的所有顾客的报表，以及包括不管位于哪个州的所有的Fun4All。不取消重复的行。

使用组合查询时需要注意的是：

• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）；
• 在上述简单的例子中，可使用WHERE子句代替UNION完成同样的功能。但对于较复杂的过滤条件，或者从多个表（而不是一个表）中检索数据的情形，使用UNION可能会使处理更简单。且如果需要每个条件的匹配行全部出现（包括重复行），就必须使用UNION ALL。

数据管理

毫无疑问，SELECT是在作数据库内分析时最常用的SQL语句，因此，我们在第二部分和第三部分着重介绍了SELECT语句的相关用法。在第四部分中，我们将继续介绍其他3个常用的SQL语句。

插入数据

INSERT用来将一行或多行插入到数据库表。插入有几种方式：
- 插入完整的行；
- 插入行的一部分；
- 插入某些查询的结果。

（1）插入完整的行
基本的INSERT语法要求指定表名和插入到新行中的值，例如：

INSERT INTO Customers
VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

这个例子将一个新顾客插入到Customers表中。存储到表中每一列的数据在VALUES子句中给出。如果某列没有值，如上面的cust_contact和cust_email列，则应该使用NULL值（如果表允许对该列指定空值）。

虽然这种语法很简单，但高度依赖于表中列的定义次序，并不安全，应该尽量避免使用。更安全的写法应在表名后的括号里明确给出列名。如下所示：

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
 VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

（2）插入部分行
如果已经在表名后的括号里明确给出了列名，则可以只插入部分行：

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
 VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

但需要注意省略的列必须满足以下条件：该列定义为允许NULL值或已给出默认值。否则MySQL将产生错误消息，相应的行不能成功插入。

（3）插入检索出的数据
INSERT一般用来给表插入具有指定列值的行。但可以利用它将SELECT语句的结果插入表中，这就是所谓的INSERT SELECT。顾名思义，它是由一条INSERT语句和一条SELECT语句组成的。假如想把另一表中的顾客列合并到Customers表中。不需要每次读取一行再将它用INSERT插入，可以如下进行：：

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                   cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

这个例子使用INSERT SELECT从CustNew中将所有数据导入Customers。SELECT中列出的每一列对应于Customers表名后所跟的每一列。为简单起见，这个例子在 INSERT 和SELECT 语句中使用了相同的列名。但其实MySQL使用的是列的位置，SELECT 中的第一列将用来填充表列中指定的第一个列，第二列对应第二列，以此类推。这对于从使用不同列名的表中导入数据是非常有用的。

更新和删除数据

（1）更新数据

为了更新表中的数据，可使用 UPDATE 语句。基本的UPDATE语句由三部分组成：
- 要更新的表；
- 列名和它们的新值；
- 确定要更新哪些行的过滤条件。

例如，客户1000000005现在有了电子邮件地址，因此他的记录需要更新，语句如下：

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

UPDATE语句总是以要更新的表名开始，SET命令用来将新值赋给被更新的列，最后以WHERE子句结束。没有WHERE子句，DBMS将会用这个电子邮件地址更新Customers表中的所有行，这不是我们希望的。

要更新多个列时，只需要使用一条SET命令，每个“列=值”对之间用逗号分隔：

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

要删除某个列的值时，可设置它为NULL（假如表定义允许NULL值）：

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

注：更新数据时一定要细心，不要省略WHERE子句，否则稍不注意，就会更新表中的所有行。

（2）删除数据

使用DELETE语句可从一个表中删除数据，包括从表中删除特定的行甚至所有行。例如，从Customers表中删除一位顾客：

DELETE FROM Customers
WHERE cust_id = '1000000006';

DELETE FROM 指定从中删除数据的表名。WHERE 子句过滤要删除的行。如果省略 WHERE 子句，它将删除表中每个客户。

DELETE 删除整行而不是删除列。如需删除指定的列，可使用上述 UPDATE 语句。

注：删除数据时同样需要小心，因为如果执行 DELETE 语句而不带 WHERE 子句，表的所有数据都将被删除且不可恢复。此外，在对 UPDATE 或 DELETE 语句使用 WHERE 子句前，应该先用 SELECT 进行测试，以保证它过滤的是正确的记录。

小结

SQL作为当前使用最为广泛的数据库语言，已经成为数据工程师的必备技能之一。这份学习指南的第一部分介绍了数据库、SQL和MySQL的基本概念，完成了SQL环境的基本搭建。第二部分和第三部分介绍了如何使用SELECT语句进行数据查询，包括排序、过滤、分组、子查询等基本方法、函数的使用以及如何使用JOIN和UNION查询一次性跨多个表来访问相关数据。最后一部分还介绍了数据管理的基本操作，包括插入、更新和删除数据等。

本文作者： heming
本文链接： SQL简明数据分析教程
版权声明： 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 3.0 许可协议。转载请注明出处！

转载自原文链接, 如需删除请联系管理员。

原文链接：SQL简明数据分析教程，转载请注明来源！