srcmini在本教程中, 你将详细研究以下主题：
SQL和数据库简介
SQL数据类型
SQL和数据报告
- 编写SQL SELECT语句
- 项目, 选择, 加入
- 级联
- 别名
- 使用DISTINCT消除重复的行
- 限制和排序数据
- WHERE子句, 像IN, LIKE这样的通配符
- 比较运算符, 逻辑运算符(AND, OR, NOT)
- ORDER BY子句
- 使用单行功能自定义输出
- 角色功能
- 数字功能
- 日期功能
- 转换功能
- 一般功能
对你的SQL结果进行分组
使用组功能报告汇总数据
- AVG, COUNT, MIN, MAX …
- 使用GROUP BY和HAVING子句
显示来自多个表的数据
- 笛卡尔积
- 等值联接, 内部联接, 右外部联接, 左外部联接, 完全外部联接
使用子查询解决查询
- 什么是子查询
- 自加入
- 单行子查询
- 子查询中的组函数
使用SET运算符
- 联合, 相交, 减号
在本教程中, 你将重点研究适用于每个数据库(例如Oracle, MySQL, Microsoft SQL Server等)的ANSI(美国国家标准学会)SQL！让我们先介绍一下SQL(结构化查询语言)以及为什么数据科学家可能需要它。
SQL与数据科学
在本部分中, 你将了解为什么数据科学家应该学习SQL。让我们看一下SQL将如何在你作为数据科学家的职业生涯中为你提供帮助：
- SQL已成为大多数数据科学工作的必备条件, 其中包括：数据分析师, BI(商业智能)开发人员, 程序员, 数据库程序员。 SQL将使你与数据库进行通信并处理数据。
- 如果你使用过Tableau之类的软件或任何其他数据报告或可视化软件, 则可能已经了解了如何将项目连接到数据库, 然后将图形和图表拖放到报告中, 仅指定字段, 其余的用于该软件。你。因此, 当你执行数据属性的拖放活动或使用图形用户界面单击一次即可完成所有操作时, 该软件的背面将运行与数据库交互的SQL。通过学习SQL, 你可以直接与数据库进行交互。
- SQL可以与所有应用程序编程语言(例如PHP, Java)一起使用。你可以通过将SQL集成到应用程序中来创建自己的数据可视化, 或者从数据库中获取数据并转换成XML, JSON格式以在Web服务或API中使用它们。
- 多年来, 数据库已经得到了发展, 随着大数据成为人们谈论的话题以及日常生活中使用的数据, NoSQL数据库正变得越来越流行。学习SQL将帮助你在数据库中打下坚实的基础, 并有助于了解何时使用结构化数据库以及何时使用NoSQL数据库并了解它们之间的差异。
在这里, 我将介绍一些用于SQL和数据库的介绍和术语, 以帮助你更快地学习编码！如果你已经知道什么是关系数据库以及什么是SQL, 则可以跳过进行编码。
SQL和数据库简介
数据库是信息的有组织的集合。要管理数据库, 我们需要数据库管理系统(DBMS)。 DBMS是一个程序, 可根据要求存储, 检索和修改数据库中的数据。
一段时间以来, 数据库的类型有所不同：分层数据库, 网络数据库, 关系数据库以及现在的NoSQL数据库。关系数据库是关系或二维表的集合。
以下是RDBMS中使用的术语：
| 术语 | 描述 |
|---|---|
| 表 | 表是RDBMS的基本存储结构。表格存储了现实世界中所有必要的所有数据。示例：员工。 |
| 单行或元组 | 代表特定员工所需的所有数据。表格中的每一行都可以由主键标识, 该主键不允许重复的行。 |
| 列或属性 | 通常指实体的特征 |
| 首要的关键 | 唯一标识行的字段 |
| 外键 | 外键是一列, 用于标识表之间的关系。外键是指另一个表中的主键。 |
你可以使用主键和外键关联多个表。关系数据库中的每一行都由主键(PK)唯一标识。你可以使用外键(FK)引用另一个表。例如：
可以使用结构化查询语言或SQL访问关系数据库。每个数据库都将支持ANSI SQL(这是标准SQL), 但也将具有自己的语法以方便进行某些操作。在本教程中, 你将学习ANSI SQL, 以便可以使用所有数据库。 ANSI SQL可以分为五个部分。我将全部命名, 但这里只有两个与数据检索和DML相关的部分：
- 资料撷取：
- 选择。
- 数据处理语言(DML)：
- 插入, 更新, 删除, 合并
- 数据定义语言(DDL)：
- 创建, 更改, 删除, 重命名, 截断。
- 数据控制语言(DCL)：
- 格兰特, 撤销。
- 交易控制：
- 提交, 回滚, 保存点。
RDBMS有不同的供应商。最常见和最常用的是：
- 甲骨文(Oracle Corporation)
- Microsoft SQL Server(Microsoft)
- MySQL(Oracle公司)
- PostgreSQL(PostgreSQL全球发展小组)
- SQLite(由D. Richard Hipp开发)
那么, 你在哪里运行SQL查询呢？可能是：
- 一种报告软件, 可让你从数据库中读取并查看数据(例如：Tableau, Microsoft BI)
- 数据库管理的GUI(例如：TOAD, Oracle的SQL开发人员, MySQL的phpmyadmin)
- 基于控制台的数据库直接接口(例如：SQL plus, 用于Oracle数据库)
如果你具有登录数据库的凭据, 则可以使用查询或GUI来查看数据库对象, 具体方法是根据数据库供应商使用查询或GUI来查看数据库对象。
而已！让我们开始学习SQL …
SQL数据类型
数据库中的每一列都有一个名称及其数据类型, 有时还具有与其关联的大小。数据库开发人员的工作是设计数据库并根据需求和数据量决定应使用哪种数据类型。
作为数据科学家, 你需要熟悉数据类型, 因为它将帮助你正确使用数据库功能并准确地编写查询。数据库中每种列的类型都有一个数据类型, 例如一个人的名字, 一些存储的文本, 数字, 存储在数据库中的图像等等。
这里将为你显示Oracle服务器, SQL服务器和MySQL服务器的基本数据类型：
进一步阅读：
- Oracle数据类型
- MySQL数据类型
- Microsoft SQL Server数据类型
SQL和数据报告
对于本教程中使用的所有SQL, 使用以下示例数据库架构：
考虑一个具有两个名为emp的表的数据库, 该表保存员工数据, 而dept表则保存有关部门的记录。
emp表包含员工编号(empno), 员工姓名(ename), 工资(sal), 佣金(comm), 职位名称(job), 经理ID(mgr), 雇用日期(hiredate)和部门编号(deptno) 。由于Manager也是雇员, 并且将具有雇员编号, 因此mgr来自其工作是” MANAGER”的empno之一。
部门表具有部门编号(deptno), 部门名称(dname)和部门位置(loc)。
请注意, 不同的数据库具有不同的日期格式。此处的DD-MON-YY是Oracle数据库的默认日期格式。 Microsoft SQL Server和MySQL的默认格式为YYYY-MM-DD。
你的数据库表可能有所不同, 因此你仅需根据表名和属性名进行调整。在本教程中, 你将仅从数据库中读取数据, 而不是写入, 更新或创建新表和对象。因此, 不用担心任何数据丢失或更改！
信息：模式是属于用户的对象的集合, 例如表, 函数, 过程, 视图
在上面的数据库架构中, 你可以看到emp表具有用于实体Employee的六个属性。表部具有实体部门的三个属性。
使用SELECT语句检索数据
SELECT语句从数据库中检索信息。使用SELECT语句, 你可以执行以下操作：
1.投影：你可以使用SQL中的投影功能在表中选择要由查询返回的列。你可以根据需要在表中选择尽可能少的列。
2.选择：你可以使用SQL中的选择功能来选择要查询返回的表中的行。
你可以使用各种条件来限制看到的行。
3.联接：你可以使用SQL中的联接功能, 通过在表之间创建链接来将存储在不同表中的数据聚集在一起。
基本的SELECT语句允许你指定要从哪个表中的哪些列。 SELECT子句指定列, 表达式, 而FROM子句指定从哪个表获取数据。
例如, 所有雇员的名字是什么, 他们的工作是什么？上面的SQL查询将根据上述数据库模式为你提供以下输出：
| 名字 | 工作 |
|---|---|
| 一个 | 推销员 |
| 乙 | 经理 |
| C | 经理 |
要从表*运算符中选择所有属性和所有行, 请执行以下操作：
SELECT *
FROM employee;
The output will be:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
编写SQL语句的一些技巧：SQL语句不区分大小写将子句写在单独的行上以提高可读性你可以在一行或多行上写SQL语句
你还可以在日期和数字数据上使用+, -, /, *运算符创建表达式以生成所需的数据。例如, 要求你找出所有雇员工资的20％。查询将如下所示：
SELECT ename, sal*(20/100)
FROM employee;
Output:
ENAME SAL*(20/100)
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
信息：括号用于说明表达式除法和乘法的优先级高于加法和减法。如果出现相同的优先级, 则从左到右评估表达式
NULL值在数据库中的处理方式有所不同。 NULL值表示该值是未知的。使用NULL执行的任何操作都将导致NULL。不同的数据库具有不同的功能来处理null。在MySQL, Microsoft SQL Server和Oracle中使用一个通用函数来处理NULL。
SELECT ename, sal+COALESCE(comm, 0)
FROM employee;
Output:
ENAME SAL+COALESCE(COMM, 0)
---------- --------------------
SMITH 800
ALLEN 1900
WARD 1750
JONES 2975
BLAKE 2850
列别名和串联
你可以在上面的输出中看到, 列名与数据库字段或你选择的表达式相同。有时, 在生成报告时, 你想要给标题指定自己的名称。这可以使用别名来完成。
SELECT ename AS "Emp Name", sal*(20/100) as "20% of Salary"
FROM employee;
Output:
Emp Name 20% of Salary
---------- ------------
SMITH 160
ALLEN 320
WARD 250
JONES 595
BLAKE 570
当你给别名提供空格时, 必须使用双引号。否则, 无需在AS后面加上双引号。有时, 你也可以省略关键字AS。
你可以通过串联格式化输出。你可以使用CONCAT函数或||等运算符在输出中添加自己的语句。或+(取决于供应商数据库)：
- Oracle支持CONCAT()和||但是CONCAT()仅接受两个参数。你必须使用嵌套的CONCAT()。
- MySQL使用CONCAT()
- Microsodt SQL Server使用’+’运算符和CONCAT()。
甲骨文：SELECT”|| ename ||’薪水的20％是来自员工的’|| sal *(20/100)作为”工资的20％”；
从雇员中选择CONCAT(CONCAT(‘工资的20％, ename), CONCAT(‘是’, sal(20/100)))作为”工资的20％”； MySQL和Microsoft SQL Server：SELECT CONCAT(‘, ename, ‘的’20％工资是’, sal(20/100))作为来自员工的” 20％工资”；所有将导致相同的输出：
20% of salary
-----------------------------------------------------------------------
20% of salary of SMITH is 160
20% of salary of ALLEN is 320
20% of salary of WARD is 250
20% of salary of JONES is 595
20% of salary of BLAKE is 570
使用DISTINCT消除重复的行
SELECT deptno
FROM employee;
Above query will result in:
DEPTNO
----------
20
30
30
20
30
这里的deptno 20和30在重复。你可以通过在SELECT子句中使用DISTINCT关键字来消除此类行。
SELECT distinct ename, deptno, job
FROM employee;
DEPTNO
----------
30
20
限制和排序数据
WHERE子句用于在某些条件下过滤数据。
查找所有有工作秘书的员工：
SELECT ename, job
FROM employee
WHERE job='CLERK';
ENAME JOB
---------- ---------
SMITH CLERK
你可能想用不同的条件过滤结果。对于此运算符, 使用条件符号和特定关键字：
如上面使用=的查询所示, 使用=, <>, ！=, > =, <=, >, <很简单。
AND＆OR语法：SELECT column1, column2, .. FROM table_name WHERE condition1 AND condition2 AND condition 3 …;
SELECT column1, column2, .. FROM table_name WHERE condition1 OR condition2 OR condition 3 …;查找职位为MANAGER并且属于部门30的员工的姓名：
SELECT ename
FROM employee
WHERE job='MANAGER' AND deptno=30;
ENAME
----------
BLAKE
SELECT ename
FROM employee
WHERE job='MANAGER' OR deptno=30;
ENAME
----------
ALLEN
WARD
JONES
BLAKE
NOT语法：SELECT column1, column2, … FROM table_name WHERE NOT条件；查找所有非SALESMAN工作的员工：
SELECT ename, job
from employee
WHERE NOT job='SALESMAN';
ENAME JOB
---------- ---------
SMITH CLERK
JONES MANAGER
BLAKE MANAGER
你可以使用所有三个AND, OR和NOT运算符来创建复杂条件。优先级是：
- 不
- 和
- OR
查找所有工作不是秘书且属于部门20的雇员：
SELECT ename, job
from employee
WHERE NOT job='SALESMAN' AND sal>800;
ENAME JOB
---------- ---------
JONES MANAGER
BLAKE MANAGER
在这里, NOT首先执行, NOT之后执行, 并且被评估。
其他方便的条件运算符如下所示：
之间：
SELECT *
FROM employee
WHERE sal BETWEEN 1000 AND 2000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
喜欢：
LIKE使用两个通配符：百分比％和下划线_表示模式中的字符数。
- ％表示任何零个, 一个或多个字符
- ％M％：匹配在任何位置具有M的任何字符串
- M％：匹配值开头为M
- ％M：匹配值以M结尾
- M％A：以M开头, 以A结尾
模式区分大小写。
- _指定在已知字符之前或之后的未知字符数。下划线是一个字符。
- _r％：匹配值, r在第二位置。
获取以” B”开头的所有员工的姓名：
SELECT *
FROM employee
WHERE ename LIKE 'B%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
获取所有以” A”开头并且在” A”之后的任何位置都具有” E”的员工的姓名：
SELECT *
FROM employee
WHERE ename LIKE 'A%E%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
IN(值1, 值2, 值3 ..)：
IN()函数可以采用一个, 两个或多个值, 并允许你将列与WHERE子句中括号中的给定值进行匹配：
SELECT ename, job, hiredate
FROM employee
WHERE job IN ('CLERK', 'SALESMAN');
ENAME JOB HIREDATE
---------- --------- ---------
SMITH CLERK 17-DEC-80
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
你还可以在IN()中使用SELECT语句, 该语句将返回一些值。例如：
SELECT ename, job, hiredate
FROM employee
WHERE deptno IN (select deptno FROM department WHERE loc='CHICAGO');
ENAME JOB HIREDATE
---------- --------- ---------
ALLEN SALESMAN 20-FEB-81
WARD SALESMAN 22-FEB-81
BLAKE MANAGER 01-MAY-81
IN()中的SELECT语句也称为子查询。本教程后面的内容将进一步介绍子查询！
一片空白：
IS NULL用于检查给定属性中的NULL值。例如, 查找所有没有佣金的员工：
SELECT ename, job, sal
FROM employee
WHERE comm IS NULL;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
BLAKE MANAGER 2850
如果要获取获得佣金的雇员的姓名, 则将使用IS NOT NULL：
SELECT ename, job, sal, comm
FROM employee
WHERE comm IS NOT NULL;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 300
WARD SALESMAN 1250 500
要过滤使用日期列的结果, 你必须使用默认日期格式。如果要使用其他格式, 则需要应用日期函数, 你将在本教程的后面看到。查找1981年2月21日之后被雇用的所有雇员：
(此处使用Oracle默认日期格式)
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81';
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
JONES MANAGER 2975
BLAKE MANAGER 2850
可以结合使用以上所有运算符来生成复杂的条件表达式, 但是你必须小心使用优先级。优先级是评估运算符的顺序。以下是不同数据库的优先级规则：
- Microsoft Transact-SQL运算符优先级
- Oracle 10g条件优先级
- Oracle MySQL 9运算符优先级
- PostgreSQL运算符优先级
- SQLite运算符优先级
有两个有用的函数：ANY(), ALL()可以在条件中使用。例如：
SELECT ename, job, sal, comm
FROM employee
WHERE deptno=ANY(SELECT deptno from dept WHERE loc='NEW YORK');
SELECT ename, job, sal, comm
FROM employee
WHERE deptno=ALL(SELECT deptno from dept WHERE dname='SALES');
请注意, 在上面的示例中使用了子查询。稍后, 你将了解有关子查询的更多信息。
使用ORDER BY CLAUSE排序结果
你可以按表的任何属性或多个属性对结果升序(ASC)或降序(DESC)进行排序。你还可以按在SELECT子句中指定的别名进行排序：
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal desc;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
WARD SALESMAN 1250 500
注意：默认顺序由ASC升序, 你无需指定ASC。
SELECT ename, job, sal, comm
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY sal;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
WARD SALESMAN 1250 500
BLAKE MANAGER 2850
JONES MANAGER 2975
你可以在ORDER BY子句中指定多个列, 这些列将按照指定的列的顺序执行。例如, 首先按升序对员工进行排序, 然后按降序命名：
SELECT ename, job, sal, comm, deptno
FROM employee
WHERE hiredate>'21-FEB-81'
ORDER BY deptno ASC, ename DESC;
ENAME JOB SAL COMM DEPTNO
---------- --------- ---------- ---------- ----------
JONES MANAGER 2975 20
WARD SALESMAN 1250 500 30
BLAKE MANAGER 2850 30
使用单行功能自定义输出
所有RDBMS中都有许多函数可以帮助你执行常见任务, 例如获取字符串的长度, 连接字符串, 格式化函数, 数学函数等。SQL中有两种类型的行函数：
- 单行功能
- 多行功能
单行功能：
这些函数应用于每行, 并每行返回结果。 CONCAT()是字符操作单行函数。这些可以在SELECT, WHERE和ORDER BY子句中使用。所有RDBMS中都有以下单行函数：
- 角色操作功能
- 日期和时间功能
- 数字功能
- 转换功能
不同的数据库具有相同功能的不同功能名称。在这里, 我将告诉你Microsoft SQL Server, Oracle和MySQL之间的常见有用功能。你已经在上一节中学习了其中一些。在本节的最后, 你将找到这些供应商提供的完整功能列表的链接, 以进行进一步的练习。
让我们浏览每个类别：
角色操纵功能
LOWER()：将字符串转换为小写。
SELECT lower(ename) as ename
FROM employee;
ENAME
----------
smith
allen
ward
jones
blake
UPPER()：将字符串转换为小写。
SELECT upper(ename) as ename
FROM employee;
ENAME
----------
SMITH
ALLEN
WARD
JONES
BLAKE
SUBSTR()[Oracle, MySQL]：返回指定的子字符串SUBSTR(string, start-position, length)
SUBSTRING()[SQL Server]：返回指定的子字符串SUBSTRING(字符串, 开始位置, 长度)。
SELECT SUBSTR(ename, 2, 3) as substr_ename
FROM employee;
对于SQL Server, 只需将函数名称替换为SUBSTRING即可。
SUBSTR_ENAME
------------
MIT
LLE
ARD
ONE
LAK
LENGTH()[Oracle, MySQL]：以括号返回字符串的长度
LEN()[SQL Server]：在括号中返回字符串的长度
SELECT LENGTH(ename) as len_ename
FROM employee;
对于SQL Server, 只需将函数名称替换为LEN。
LEN_ENAME
----------
5
5
4
5
5
诸如将字符串填充到左侧或右侧, 替换等功能在不同数据库的语法上开始有所不同。三个供应商数据库的字符和字符串函数列表：
- Oracle字符和字符串函数
- Microsoft SQL服务器
- MySQL字符和字符串函数
数字功能
| 功能名称 | 函数 |
|---|---|
| 轮数(m, n)： | 将值m舍入为指定的n个小数位。 |
| ABS(m, n)： | 返回数字的绝对值 |
| 楼层(n)： | FLOOR返回等于或小于n的最大整数。 |
| MOD(m, n)： | 返回m除以n的余数(在SQL Server中不可用, 但使用％运算符：35％6) |
甲骨文：
SELECT ROUND(45.926, 2), MOD(11, 5), FLOOR(34.4), ABS(-24) from dual;
OUTPUT:
ROUND(45.926, 2) MOD(11, 5) FLOOR(34.4) ABS(-24)
--------------- ---------- ----------- ----------
45.93 1 34 24
MySQL：
SELECT ROUND(45.926, 2), MOD(11, 5), FLOOR(34.4), ABS(-24);
SQL Server：
SELECT ROUND(45.926, 2) as round, 11%5 as mod, FLOOR(34.4) as floor, ABS(-24) as abs;
三个供应商数据库的数字功能列表：
- Oracle Number函数
- Microsoft SQL服务器
- MySQL数字函数
转换功能
转换函数用于将一种数据类型转换为另一种数据类型。转换功能因服务器而异。在这里, 我将为你提供一个使用to_char函数的Oracle服务器查询的示例。这将帮助你了解如何使用转换功能来自定义输出。 Oracle的默认日期格式为DD-MON-YY
SELECT ename, to_char(hiredate, 'DD, MONTH YYYY') as Hiredate, to_char(hiredate, 'DY') as Day
from employee;
ENAME Hiredate DAY
---------- -------------------------------------------- ------------
SMITH 17, DECEMBER 1980 WED
ALLEN 20, FEBRUARY 1981 FRI
WARD 22, FEBRUARY 1981 SUN
JONES 02, APRIL 1981 THU
BLAKE 01, MAY 1981 FRI
每个数据库服务器都有很多转换功能。为了进一步阅读和探索, 以下是指向不同数据库服务器转换功能的链接：
- Microsoft SQL Server转换功能
- Oracle服务器转换功能
- MySQL转换功能
日期和时间功能
日期和时间功能允许你通过添加日期, 计算两个日期之间的月份等来操纵日期。此类功能在生成报告时很有用。日期函数也因数据库而异。一个功能可能执行相同的功能, 但在另一个数据库中可能具有不同的名称。在这里, 你将看到一些示例, 然后为你提供指向最常见的数据库(Oracle, SQL Server和MySQL)的日期函数的链接。继续并在数据库服务器上尝试这些示例！
- Oracle日期和时间功能
- MySQL日期和时间函数
- Microsoft SQL Server日期和时间功能
将你的SQL结果分组
分组功能或多行功能应用于组, 并且每组返回一个结果。当你想了解诸如每季度的总销售额, 一段时间内产品的平均价格, 公司本月获得的最高投资等事实时, 将需要使用小组功能。
使用组功能报告汇总数据
GROUP BY子句用于对经常与COUNT, MAX, MIN, AVG和SUM之类的函数一起使用的结果进行分组。 GROUP BY的语法为：
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
你必须记住GROUP BY子句的一些要点：
- 别名不能在GROUP BY子句中使用
- WHERE子句始终位于GROUP BY之前
- HAVING子句将在GROUP BY之后, 并将对组函数应用条件。
- ORDER BY子句始终会最后出现
COUNT是一个组函数, 它根据条件或无条件对数据的行数进行计数。 COUNT函数将忽略空值。
计算所有以” A”开头的员工：
SELECT count(ename)
FROM employee
WHERE ename LIKE 'A%';
Output:
COUNT(ENAME)
------------
1
查找表员工的总薪水, 平均薪水, 最低薪水和最高薪水：
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL"
FROM employee;
TOTAL SAL AVG SAL MIN SAL MAX SAL
---------- ---------- ---------- ----------
9475 1895 800 2975
你可能希望将每个部门的上述结果分组：
SELECT sum(sal) as "TOTAL SAL", avg(sal) as "AVG SAL", min(sal) as "MIN SAL", max(sal) as "MAX SAL", deptno
FROM employee
GROUP BY deptno;
TOTAL SAL AVG SAL MIN SAL MAX SAL DEPTNO
---------- ---------- ---------- ---------- ----------
5700 1900 1250 2850 30
3775 1887.5 800 2975 20
你还可以使用以下函数来计算值的方差和标准偏差：
| 对于Oracle和MySQL | MS SQL服务器 |
|---|---|
| 从table_name中选择STDDEV(column_name); | 从table_name中选择STDEV(column_name); |
| SELECT VARIANCE(column_name)FROM table_name; | 从表名中选择VAR(column_name); |
你可以将结果按不止一列进行分组。
查找每个部门中每个工作的薪水总和：
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job;
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
800 20 CLERK
2850 30 SALESMAN
2975 20 MANAGER
2850 30 MANAGER
你可以使用HAVING子句限制分组结果。它仅用于组条件。
注意：请勿使用WHERE在组功能上应用条件。
SELECT sum(sal) as "TOTAL SAL", deptno, job
FROM employee
GROUP BY deptno, job
HAVING sum(sal)>1000
ORDER BY sum(sal);
TOTAL SAL DEPTNO JOB
---------- ---------- ---------
2850 30 SALESMAN
2850 30 MANAGER
2975 20 MANAGER
显示来自多个表的数据
在本节中, 将讨论以下联接：
- 笛卡尔积/交叉联接
- 内联/ EquiJoins
- 自然连接
- 外连接(左, 右, 全)
- 自我加入
你将需要显示从多个表中获取数据的报告。请参见下面的示例：
要生成上述报告, 你需要链接员工表和部门表并从两者中获取数据。为此, 在SQL中使用联接。
笛卡尔积/交叉联接：
当关系R的每个元组与关系S的每个元组创建一个组合时, 就会形成笛卡尔积。
笛卡尔积运算(也称为”交叉联接”)将两个表相乘以形成一个关系, 该关系由两个表中所有可能的元组对组成。如果一个关系说, R具有I个具有M个属性的元组, 而另一个关系说S具有J个元组N个属性, 笛卡尔积将具有IxJ个具有M + N个属性的元组。也可以使用SQL投诉CROSS JOIN来形成笛卡尔乘积。
SELECT empno, ename, dname
FROM employee, department;
OR
SELECT empno, ename, dname
FROM employee CROSS JOIN department;
EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7698 BLAKE ACCOUNTING
7369 SMITH RESEARCH
7499 ALLEN RESEARCH
7521 WARD RESEARCH
7566 JONES RESEARCH
7698 BLAKE RESEARCH
7369 SMITH SALES
EMPNO ENAME DNAME
---------- ---------- --------------
7499 ALLEN SALES
7521 WARD SALES
7566 JONES SALES
7698 BLAKE SALES
已选择15行。
由于雇员表包含5个元组, 部门包含3个元组, 因此笛卡尔积具有5×3 = 15行。在以下情况下会生成笛卡尔积：
- 不使用联接条件
- 连接条件无效或格式不正确
当需要两个或多个表中的数据时, 将使用链接所涉及表的公共属性来创建连接条件。最常见的是主键和外键。
笛卡尔积用于仿真大量数据以进行测试。
*内部加入/等额加入：
内部联接或等联接(Oracle开发人员使用的术语)使用主键和外键关系联接两个或多个表：
SELECT ename, dname
FROM employee e, department d
WHERE e.deptno=d.deptno;
OR
SELECT ename, dname
FROM employee e
JOIN department d
ON e.deptno=d.deptno;
ENAME DNAME
---------- --------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
BLAKE SALES
在上面的查询中, deptno是部门表中的主键, 而雇员表中的外键。你可以使用逻辑操作来添加其他条件, 如上节所述。
记住JOINS的要点：如果同一列名出现在多个表中, 则该列名必须以表名为前缀。否则, 最好这样做也是为了提高清晰度。要将n个表连接在一起, 你至少需要n-1个连接条件。例如, 要联接四个表, 至少需要三个联接。
表别名：表别名(例如在FROM员工e, 部门d中使用), 其中e和d是表别名, 以使数据库引擎更容易识别列来自哪个表, 尤其是当多个列中存在相同的列时一个涉及的表, 因为否则将给出歧义列的错误。并且节省了一次又一次写大表名的时间。
你还可以创建基于相等条件以外的条件的连接表的无连接。考虑另一个表salgrade, 该表具有有关基于薪金的雇员级别的信息：
你想根据薪水找出每个员工的等级, 而等级存储在salgrade表中, 而薪水存储在employee表中：
SELECT e.ename, e.sal, s.grade
FROM employee e, salgrade s
WHERE e.sal between s.losal AND s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
JONES 2975 4
BLAKE 2850 4
ALLEN 1600 3
WARD 1250 2
SMITH 800 1
示例：要求你提供雇员的姓名, 工资, 职级和部门名称。现在, 你知道, 薪水存储在员工表中, 薪金存储在薪级表中, 部门名称存储在部门表中, 你将需要将三个表结合在一起：
SELECT e.ename, e.sal, d.dname, s.grade
FROM employee e, department d, salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;
Output:
ENAME SAL DNAME GRADE
---------- ---------- -------------- ----------
JONES 2975 RESEARCH 4
BLAKE 2850 SALES 4
ALLEN 1600 SALES 3
WARD 1250 SALES 2
SMITH 800 RESEARCH 1
在上面的示例中, 涉及到三个表, 并且两个连接条件之一是非静态连接。
自然加入：
自然联接允许数据库通过匹配具有相同名称的列来自动联接表。如果列的名称相同但数据类型不同, 则将给出错误。
语法：SELECT FROM table1 NATURAL JOIN table2;从员工NATURAL JOIN部门中选择；由于自然联接本身会找到匹配的列, 因此它可能会找到多个具有相同名称的匹配列, 但可能具有不同的数据类型, 这可能会导致错误。因此, USING子句用于指定在其上进行均等联接的列。
注意：NATURAL JOIN和USING是两个不同的子句, 分别使用。一个不能在另一个互斥的情况下使用。
SELECT e.ename, d.dname, e.sal
FROM employee e JOIN department d
USING (deptno)
WHERE deptno=20;
output:
ENAME DNAME SAL
---------- -------------- ----------
SMITH RESEARCH 800
JONES RESEARCH 2975
USING子句中的列不能在SQL语句中的任何地方使用表名或表前缀。例如以下不正确：
SELECT e.ename, d.dname, e.sal
FROM employee e JOIN department d
USING (d.deptno)
WHERE d.deptno=20;
d.deptno错误。仅应使用deptno。外连接：
有三个外部联接：
- 左外部联接：两个表之间的联接返回内部联接的结果以及左表的不匹配行, 称为左外部联接
- 右外部联接：两个表之间的联接返回内部联接的结果以及右表的不匹配行, 称为右外部联接。
- 完全外部联接：两个表之间的联接返回内部联接的结果以及右表的不匹配行, 称为RIGHT OUTER JOIN。
让我们一一看一下：
SELECT e.ename, s.grade
FROM salgrade s LEFT OUTER JOIN employee e
ON e.sal BETWEEN s.losal AND s.hisal;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
JONES 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
BLAKE 30 SALES.sal BETWEEN s.losal AND s.hisal;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
JONES 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
BLAKE 30 SALESSELECT e.ename, d.deptno, d.dname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptno=d.deptno;
output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTINGSELECT e.ename, d.deptno, d.dname
FROM employee e FULL OUTER JOIN department d
ON e.deptno=d.deptno;
Output:
ENAME DEPTNO DNAME
---------- ---------- --------------
SMITH 20 RESEARCH
ALLEN 30 SALES
WARD 30 SALES
JONES 20 RESEARCH
BLAKE 30 SALES
10 ACCOUNTING
部门表在deptno 10上有一个不匹配的行, 在雇员表中没有找到不匹配的行。同样, 五年级的薪水表中有一行不匹配。
自加入：
在某些情况下, 你想将表与其自身联接。例如, employee表包含有关所有员工(包括经理)的信息(请参阅上面的表employee)。如果要求你找出员工及其经理, 那么你将需要将一个表自身连接起来。通过以下方式进行自我联接：
SELECT e.ename as Employee, m.ename as Manager
FROM employee e, employee m
WHERE e.mgr=m.empno;
EMPLOYEE MANAGER
---------- ----------
ALLEN BLAKE
WARD BLAKE
使用子查询解决查询
子查询是查询中的查询。子查询可用于将大型查询细分为多个段。子查询可以嵌套, 并且可以在以下SQL子句中使用：
- 其中
- 从
- 拥有
考虑一个例子；你被要求找到一个薪水高于另一个雇员(在本例中为James)的雇员。你将如何处理？该查询可以细分为：
- 找到詹姆斯的薪水
- 比较詹姆斯与所有员工的薪水
可以使用子查询解决。子查询(内部查询)将在主(外部)查询之前执行一次。
SELECT empno, ename
FROM employee
WHERE sal>(SELECT sal from employee where ename='JAMES');
子查询的类型：
- 单行子查询：仅从内部SELECT语句返回一行的查询。
- 多行子查询：从内部SELECT语句返回多行的查询。
要记住的要点：将子查询括在括号中。将子查询放在比较运算符的右侧。对单行子查询使用单行运算符(>, <, > =, <=, <>)。对多个行子查询使用多个行运算符(IN, ANY, ALL)。
单行子查询：
要求你查找职位名称与empno 7521相同的员工姓名
SELECT ename, job
FROM employee
WHERE job=(SELECT job FROM employee WHERE empno=7521);
ENAME JOB
---------- ---------
ALLEN SALESMAN
WARD SALESMAN
选择最高薪水高于部门20最高薪水的部门最高薪水
SELECT deptno, max(sal)
FROM employee
GROUP BY deptno
HAVING max(sal)>=(SELECT max(sal) FROM employee WHERE deptno=20);
DEPTNO MAX(SAL)
---------- ----------
20 2975
多行子查询：
查找薪水等于担任经理职位的员工的薪水的员工。这将是一个多行子查询, 因为该子查询可以返回多个行。
SELECT ename, job, sal
FROM employee
WHERE sal IN (SELECT sal FROM employee WHERE job='MANAGER');
ENAME JOB SAL
---------- --------- ----------
JONES MANAGER 2975
BLAKE MANAGER 2850
在FROM子句中使用子查询时, 它充当临时表, 该表在存储上实际上不存在, 但是是数据视图。例如：
SELECT e.ename, e.job, e.sal
FROM employee e, (SELECT deptno FROM department WHERE loc='DALLAS') d
WHERE e.deptno=d.deptno;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JONES MANAGER 2975
使用SET运算符
在SQL Set中, 运算符用于将多个查询的结果合并为一个结果。它在数学及其运算中使用了集合论, 例如UNION, MINUS, INTERSECT。在这里, 你将看到如何使用SET运算符来优化查询。在本教程中, 将讨论以下SET运算符。
信息：使用SET运算符的查询称为复合语句。
- UNION和UNION ALL
- 相交
- EXCEPT(SQL标准)和MINUS(特定于Oracle)
联盟
考虑R和S的两个关系(表), 然后UNION从R中选择所有行, 从S中选择所有行, 以消除重复。返回的行数可以是最大r + s, 其中r是R中的行数, s是S中的行数。
选择所有部门名称, 包括在1999年10月23日之前雇用的员工的部门名称
SELECT dname
FROM department
UNION
SELECT dname
FROM department, employee
WHERE department.deptno=employee.deptno AND employee.hiredate<to_date('23-OCT-1999');
DNAME
--------------
ACCOUNTING
OPERATIONS
RESEARCH
SALES
要记住UNION的要点：在查询中使用的所有SELECT语句中, 所选的列数和数据类型必须相同。列的名称不必相同。输出以SELECT子句的第一列的升序排序。在重复检查期间不会忽略NULL值。
UNION ALL合并一个或多个查询的结果, 并且不会删除重复项, 因此不能使用DISTINCT关键字。考虑另一个表emp, 其中包含2000年以来的雇员。从emp和employee表中获取所有雇员编号, 姓名和职位：
SELECT empno, ename, job
FROM employee
UNION ALL
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
EMPNO ENAME JOB
---------- ---------- ---------
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7876 ADAMS CLERK
7900 JAMES CLERK
相交
交集遵循与集合论相同的规则, 以给出两个集合的共同值。考虑两个关系R和S, 然后INTERSECT将返回在R和S中都通用的所有元组的集合。
SELECT empno, ename, job
FROM employee
INTERSECT
SELECT empno, ename, job
FROM emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
INTERSECT要记住的要点：在查询中使用的所有SELECT语句中, 所选的列数和数据类型必须相同。列的名称不必相同。 INTERSECT不会忽略NULL值
减号：
EXCEPT(在SQL Server中使用)和MINUS(针对Oracle)具有相同的功能。该功能是选择第一个查询而不是第二个查询选择的所有不同行。要了解此运算符, 请考虑下表：
说, 你需要找到数量在1到100之间但数量在50-75之间的产品。如果你使用的是SQL Server, 则在这里使用EXCEPT, 如果你使用的是Oracle服务器, 则使用MINUS。
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
EXCEPT
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;
对于Oracle服务器：
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 1 AND 100
MINUS
SELECT prod_name, qty
FROM products
WHERE qty BETWEEN 50 AND 75;
PROD_NAME QTY
------------- ----------
COLGATE 1
SENSODYNE 100
SENSODYNE TOOTHBRUSH 30
EXCEPT / MINUS要记住的要点：在查询中使用的所有SELECT语句中, 所选的列数和数据类型必须相同。列的名称不必相同。 WHERE子句中的所有列都必须在SELECT子句中, MINUS运算符才能起作用。
恭喜你！
教程到此结束。在本教程中, 你学习了很多SQL, 可帮助你在数据科学之旅中掌握SQL。 SQL用于从数据库生成报告。你已在本教程中学习了数据库和SQL的基础, 常用的数据类型, 可帮助你实现格式报告的功能, 汇总创建结果的结果以及如何根据自己的需要从数据库中的不同表收集数据的方法要求！本教程是专门为数据科学学习者设计的, 它们不仅可以帮助他们使用关系数据库, 而且可以顺利地学习NoSQL数据库以及如何在大数据研究中使用SQL技能。
如果你想了解有关SQL的更多信息, 请参加srcmini的免费SQL for Data Science入门课程。
参考文献
- Tanveer Zahid Khan(Bahria大学计算机科学系高级助理教授)对Oracle 10g-SQL讲座的介绍
- https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators005.htm
- https://en.wikipedia.org/wiki/Set_operations_(SQL)#EXCEPT_operator
- https://www.w3schools.com/sql/sql_datatypes.asp
- https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT012
评论前必须登录！
注册