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 表中所有列

注：使用通配符的好处是比较方