《SQL必知必会》万字精华-第1到13章

2021-03-21 18:43:36 浏览数 (1)

《SQL必知必会》万字精华

本文是《SQL必知必会》一书的精华总结，帮助读者快速入门SQL或者MySQL，主要内容包含：

  • 数据库基础知识
  • 库表的相关操作
  • 检索数据的方法

<!--MORE-->

思维导图

下面的思维导图中记录了这本书的整体目录结构，包含内容有：

  • 数据的检索
  • 汇总数据
  • 分组数据
  • …….

一、了解SQL

本章中主要是介绍了数据库和SQL相关的基本知识和术语。

数据库

数据库是一个以某种有组织的方式存储的数据集合。数据库是一个保存有组织的数据容器，通常是一个文件或者一组文件

表

表示一种结构化的文件，可以用来存储某种特定的数据类型。表是某种特定类型数据的结构化清单。

存储在表中的数据是同一种类型的数据或者清单

数据库中的每个表都有自己的名字，并且这个表是唯一的。

列

表是由列组成的，列存储表中某部分的信息。列是表中的某个字段。所有的表都是由一个或者多个列组成的。

数据库中的每个列都应该是具有的相同数据类型datatype。数据类型定义了列可以存储哪些数据类型。

行

表中的数据是按照行来进行存储的，所保存的每个记录存储在自己的行内。如果把表想象成一个网格，那么网格中垂直的列则为表列，水平则为表行。

行表示的是一个记录。行有时候也称之为记录。

主键

表中每一行都应该都有一列或者几列来唯一标识自己。主键用来表示一个特定的行。

主键：一列或者几列，其值能够标识表中每行。

如果表中的列可以作为主键，则它必须满足：

  • 任意两行都不具有相同的主键值（主键列不允许NULL值）
  • 每行都必须有一个主键值
  • 主键列中的值不允许修改或者更新
  • 主键值不能重用（如果某行从表中删除，则它的主键不能赋给以后的行记录）
什么是SQL

首先我们看一段来自百度百度的解释：

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL是一种专门和数据库沟通的语言

SQL特点

1、SQL不是某个特定数据库供应商专有的语言，几乎所有的DBMS都是支持SQL

2、SQL简单易学。它的语句都是由简单的、具有描述性的英文单词组成的

3、SQL虽然简单，但是实际上是一种很强有力的语言，灵活使用去语言元素，可以进行复杂和高级的数据库操作

二、检索数据

本章中介绍的是如何使用select语句从表中检索一个或者多个数据列。

每个SQL语句多有一个或者多个关键字组成，最经常使用的就是select关键字。它的用途是从一个或者多个表中检索出来信息。为了使用select检索数据，必须至少给出两个信息：

  • 检索什么（字段，列）
  • 从哪里检索（表）
检索单个列
代码语言：txt复制
SELECT prod_name   -- 检索什么
FROM Products;  -- 从哪里检索

下面关于SQL的语句做几点笔记：

  1. 多条SQL语句必须是以分号;结尾；如果是单条不加分号也是可以的，但是最好加上
  2. SQL语句不区分大小写，即：SELECT和select是相同的。但是一般规范的写法是：SQL关键字进行大写，列名和表名是小写的
  3. 在处理SQL语句的时候，其中所有的空格都是忽略的；但是分行写，语句更清晰，更好理解
代码语言：txt复制
-- 写法1
SELECT prod_name   -- 检索什么
FROM Products;  -- 从哪里检索

-- 写法2
SELECT prod_name FROM Products;  

-- 写法3
SELECT 
prod_name 
FROM 
Products; 

-- 写法：个人习惯写法
SELECT 
	prod_name 
FROM Products;
检索多个列
代码语言：txt复制
SELECT pro_id,prod_name,prod_price
FROM Products;

-- 个人写法
SELECT 
	pro_id,
	prod_name,
	prod_price
FROM Products;
检索所有列
代码语言：txt复制
SELECT *  -- *代表所有列
FROM Products;
检索不同的值
代码语言：txt复制
SELECT DISTINCT vend_id  
FROM products;
  • SQL中的DISTINCT关键字表示的是去重，只返回不同的值。它必须放在列的前面。
  • 不能部分使用DISTINCT关键字。它是作用于所有的列
代码语言：txt复制
SELECT DISTINCT vend_id, prod_price  -- DISTINCT作用于所有的列，并不仅仅是后面的列
限制结果

如果不加限制条件，SQL返回的是全部数据。如果我们想只显示部分数据，该如何实现？

1、SQL Server 和 Access

代码语言：txt复制
SELECT TOP 5 prod_name  -- 最多返回5行
FROM Products;

2、DB2

代码语言：txt复制
SELECT prod_name
FROM Products 
FETCH FIRST 5 ROWS ONLY;   -- 字面意思：显示前5行

3、Oracle

代码语言：txt复制
SELECT prod_name
FROM Products 
WHERE ROWNUM <= 5;

4、MySQL、MariaDB、PostgreSQL、SQLite

使用关键字limit

代码语言：txt复制
SELECT prod_name
FROM Products 
LIMIT 5;   -- 使用LIMIT5

关于LIMIT的笔记：

代码语言：txt复制
SELECT prod_name
FROM Products 
LIMIT 4 OFFSET 5;  -- 第5行开始显示4行数据

-- 简化版本
SELECT prod_name
FROM Products 
LIMIT 5,4  -- 效果同上
  • 第一个数字表示显示多少行数据
  • 第二个数字表示从哪里开始显示
SQL注释问题

SQL中的注释分为两种：单行注释和多行注释

单行注释使用—符号，后面跟上注释的内容：

代码语言：txt复制
SELECT prod_name  -- 这里是一条注释，你可以写点注释
FROM Products 
LIMIT 4 OFFSET 5;

多行注释使用一对/*，符号之间的内容就是注释：

代码语言：txt复制
/*
注释1：SQL语句的作用是什么
注释2：SQL语句谁在什么时候写的
*/
SELECT prod_name  
FROM Products 
LIMIT 4 OFFSET 5;

三、排序检索数据

排序数据（单个列）

本节中介绍的是如何利用order by子句来对select检索的结果进行排序。为了明确地排序用select语句检索出来的数据，可使用order by子句取一个或者多个列的名字，来对输出结果进行排序。

  • 使用关键词order by
  • 排序的结果默认是升序ASC，降序是DESC
代码语言：txt复制
SELECT prod_name
FROM Products 
ORDER BY prod_name;  -- 根据产品名称的字母进行排序

笔记：我们需要注意order by子句的位置，一定要保证它是select语句的最后一条子句。如果它不是最后的子句，那么就会报错。

按多个列排序

在实际的需求中，我们经常会遇到根据多个列进行排序。比如根据员工的姓名排序，如果姓相同，再根据名字进行排序。

要按多个列进行排序，指定列名即可，列名之间使用逗号隔开。

代码语言：txt复制
SELECT prod_id,prod_price,prod_name   -- 选择3个列
FROM Products
ORDER BY prod_price,prod_name;  -- 先根据价格排序，如果有相同的价格再根据姓名排序

笔记：只有当prod_price有相同的值，才会根据prod_name进行排序

按列位置进行排序

除了可以使用列名指出排序顺序外，order by还支持使用相对位置进行排序。

代码语言：txt复制
SELECT 
	prod_id,
	prod_price,
	prod_name   -- 选择3个列
FROM Products
ORDER BY 2,3;     -- 2,3就是相对位置
  • 2表示的是第2个列（prod_price）
  • 3表示的是第3个列名（prod_name）

如果想在多个列上进行降序排列，则对每个列都要指定DESC关键词：

代码语言：txt复制
-- 正确写法
SELECT 
	prod_id,
	prod_price,
	prod_name   
FROM Products
ORDER BY prod_price DESC, prod_name DESC;  -- 每个列都指定DESC

-- 错误写法！！！
SELECT 
	prod_id,
	prod_price,
	prod_name   
FROM Products
ORDER BY prod_price, prod_name DESC;  -- DESC只对最近的prod_name起作用，那么prod_price仍然是升序排列的

四、过滤数据

本节中讲解的是使用where关键词来过滤数据。数据库中一般存在大量的数据，一般我们只需要检索表中少量的行。只检索所需数据需要指定搜索条件，搜索条件也称之为过滤条件。

使用where子句
代码语言：txt复制
SELECT prod_name, prod_price
FROM Products
WHERE prod_price=5;  -- 指定条件

笔记：当ORDER BY 和WHERE子句同时存在的时候，ORDER BY子句应该位于WHERE子句之后。

WHERE子句操作符

常用的where子句操作符：

操作符

说明

操作符

说明

=

等于

大于

<>

不等于

>=

大于等于

!=

不等于

!>

不大于

<

小于

BETWEEN…AND...

在指定的两个值之间

大于

IS NULL

为NULL值

!<

不小于

注：上面表格中的某个操作符是等价的。

检查单个值
代码语言：txt复制
SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 5;  -- 指定1个条件
代码语言：txt复制
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id <> 'DLL01'   -- 指定不等于条件

笔记：

1、上面where子句中，可以看到有的只会在单引号内，但是有的没有括起来。

2、单引号用来限定字符串。如果将值和字符串类型的比较，需要使用限定符号

3、用来与数值列进行比较的值，则不用括号。

不匹配检查
代码语言：txt复制
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id <> 'DLL01'  -- 不匹配检查条件
范围值检查

要检查某个范围的值，可以使用BETWEEN操作符。BETWEEN操作符要搭配AND同时使用，指定范围的最大值和最小值：

代码语言：txt复制
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 100 AND 600    -- BETWEEN ... AND...联合使用
空值检查

当我们创建表的时候，可以指定其中的列是否包含空值。在一个列不包含值时，称其包含空值NULL。

注：NULL（无值，no value），它和字段包含0、空字符串或仅仅包含空格是不同的。

SELECT中一个特殊的HERE子句用来检查具有NULL值的列：

代码语言：txt复制
SELECT prod_name, prod_price
FROM Products
WHERE prod_price IS NULL;   -- 找出价格为NULL的数据

五、高级数据过滤

本节中介绍的是如何组合WHERE子句以建立功能更强、更高级的搜索条件

组合WHERE子句

操作符operator：用来联结或改变WHERE子句中的子句的关键字，也称之为逻辑操作符logical operator。

AND操作符

同时满足AND操作符两边的条件

代码语言：txt复制
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id >= 'DLL01' AND prod_price <= 20;  -- AND操作符指定2个条件
OR操作符

满足OR操作符两边的一个条件即可

代码语言：txt复制
SELECT vend_id, prod_name, prod_price
FROM Products
WHERE vend_id >= 'DLL01' OR prod_price <= 20;  -- AND操作符指定2个条件

注：当第一个条件满足的时候，第二个条件便不会执行了。

AND和OR联用

在WHERE子句中同时使用AND和OR操作符：

⚠️：AND操作符的优先级是高于OR操作符

⚠️：AND操作符的优先级是高于OR操作符

⚠️：AND操作符的优先级是高于OR操作符

代码语言：txt复制
SELECT prod_name,prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' AND prod_price >= 10;

上面的语句原本表达的含义是先挑选满足两个vend_id的条件；但是SQL在执行的时候，AND操作符先处理。如果我们想先执行AND前面的部分，可以加上括号：

代码语言：txt复制
SELECT prod_name,prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

括号具有比AND或者OR更高的优先级，能够消除歧义。

IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

代码语言：txt复制
SELECT prod_name,prod_price
FROM Products
WHERE vend_id IN ('DLL01','BRS01')   -- 功能类似于OR操作符
ORDER BY name：

笔记：IN操作符的主要优点

1、IN操作符的语法更清楚、更直观

2、使用了IN操作符更容易管理求值顺序

3、IN操作符一般比OR操作符执行的更快

4、在IN操作符中可以包含其他SELECT子句，能够动态地建立where子句

NOT操作符

NOT操作符只有一个功能：就是否定后面所跟的任何条件。

代码语言：txt复制
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'   -- 找出不是DLLO1名字的数据
ORDER BY prod_name

上面的语句的功能也可以用<>来实现：

代码语言：txt复制
SELECT prod_name
FROM Products
WHERE vend_id <> 'DLLO1'   -- 不等于，效果同上
ORDER BY prod_name

六、使用通配符进行过滤

什么是通配符

通配符wildcard是用来匹配值的一部分的特殊字符；利用通配符，可以创建比较特定数据的搜索模式。

笔记：由字面值、通配符或者两者组合构成的搜索条件。

为了在搜索子句中使用通配符，必须使用LIKE操作符

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

百分号%

功能：匹配任意字符出现的任意次数，即任意内容

代码语言：txt复制
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE "Fish%"  -- 找出所有Fish开头的产品，不管后面是什么内容

通配符可以在任意位置使用，可以使用多次：

代码语言：txt复制
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE "�an bag%"   -- 匹配名字中包含bean bag的任意名字的数据，不管前后

通配符出现在中间：

代码语言：txt复制
SELECT prod_name
FROM Products 
WHERE prod_name LIKE 'F%y'   -- 找出F开头y结尾的数据

⚠️：百分号%能够匹配任意位置的0个、1个或者多个字符，但是不能匹配NULL

下划线_

下划线通配符的用途和百分号类似，但是它只能匹配一个字符，百分号是匹配多个字符，这是二者的区别。

代码语言：txt复制
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear'   --  前面是两个下划线_

-- 结果
prod_id           prod_name
-------           ---------
BR02              12 inch teddy bear   -- 12或者18刚好是两个字符
BR03              18 inch teddy bear

一个下划线刚好只能匹配一个字符，不能多也不能少

方括号[]

方括号[]通配符用来指定一个字符集，它必须匹配指定位置的一个字符。

代码语言：txt复制
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'    -- 匹配JM当中一个字母开头，后面是任意字符的内容
ORDER BY cust_contact
  • JM：匹配其中一个字符
  • %：匹配任意内容
取反字符^

使用取反符号^来否定内容：

代码语言：txt复制
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'   -- 匹配不是JM开头的任意内容
ORDER BY cust_contact;

使用NOT操作符可以得到类似上面的结果：

代码语言：txt复制
SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'   -- 匹配不是JM开头的任意内容；NOT表示取反，否定内容
ORDER BY cust_contact;
通配符使用技巧

⚠️通配符使用的几点注意事项：

  1. 不要过度使用通配符
  2. 如果确实需要使用通配符，尽可能不要将它们放在搜索模式的开始位置。通配符处于开始处，搜索是最慢的。
  3. 仔细注意通配符的位置。如果放错地方，可能得不到我们想要的结果。
  4. 通配符应该要细心使用，不要过度使用。

七、创建计算字段

计算字段

存储在数据库表中的字段一般不是应用程序中所需要的格式。我们需要直接从数据库中检索出来进行转换、计算或者格式化过的数据。计算字段并不实际存在于数据库表中。计算字段是运行时在select语句内创建的。

拼接字段

将多个字段联结在一起构成单个字段。根据不同的DBMS，使用 或者||来进行联结。但是在MySQL中必须使用特殊的函数来进行操作。

拼接字段函数Concat的使用：

代码语言：txt复制
SELECT 
   Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;

代码解释：

  • 存在列vend_name列中的名字
  • 包含一个空格和一个左圆括号的字符串
  • 存在vend_country列中的国家
  • 包含一个右圆括号的字符串

小知识：MySQL中如何去掉空格？

代码语言：txt复制
RTRIM(col)  -- 去掉值右边的所有空格
LTRIM(col)  -- 去掉值左边的所有空格
TTRIM(col)  -- 去掉字符串左右两边的所有空格
使用别名

别名（alias）是一个字段或者值的替换明，别名是使用关键词AS来赋予的。

代码语言：txt复制
SELECT 
   Concat(vend_name, ' (', vend_country, ')')  AS vend_title    -- 使用别名
FROM Vendors
ORDER BY vend_name;

笔记：AS关键词语是可选的，但是最好使用

执行算术运算

计算字段的另一个常见运算是对检索出来的数据进行算术运算。

代码语言：txt复制
SELECT
	prod_id
	,quantity
	,item_price
	,quantity * item_price AS expanded_price   -- 计算字段执行算术运算，并使用别名
FROM OrderItems
WHERE order_num = 2008;
SQL算术操作符

SQL中支持的算术操作符：

操作符

说明

加

减

乘

/

除

使用函数处理数据

常用函数

与其他计算机语言一样，SQL中也提供函数来处理数据。

  • 用于处理文本字符串：删除或填充值、转换值或者大小写转化
  • 用于在数值数据上进行算术操作：返回绝对值、代数运算等
  • 用于处理日期和时间，并从中提取出特定成分的日期和时间函数等
  • 返回DBMS正使用的特殊信息的系统函数
文本处理函数

函数

说明

LEFT()

返回字符串左边的字符

LENGTH()

返回字符串的长度

LOWER()

将字符串转换为小写

LTRIM()

去掉值左边的所有空格

RIGHT()

返回字符串右边的字符

RTRIM()

去掉值右边的所有空格

SOUNDEX()

返回字符串的SOUNDEX值

UPPER()

将字符串转换为大写

SOUNDEX是一个将任何文本串转成描述其语音表示的字母数字模式的算法。

代码语言：txt复制
SELECT
  cust_name
  ,cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green')   -- 转化成对应的值
日期和时间处理函数

日期和时间采用相应的数据类型存储在表中，以特殊的格式来存储。

代码语言：txt复制
SELECT  
	order_num
FROM Orders
WHERE YEAR(order_date) = 2012;   -- 提取年份
数值处理函数

MySQL中常用的数值处理函数：

函数

说明

ABS()

返回一个数的绝对值

COS()

返回一个角度的余弦值

EXP()

返回一个数的指数值

PI()

返回圆周率

SIN()

返回一个角度的正弦

SQRT()

返回一个数的平方根

TAN()

返回一个角度的正切值

汇总数据

聚集函数

聚集函数指的是对某些行运行的一个函数，并且返回一个值，常用的聚集函数有：

函数

作用

AVG()

返回列的平均值

COUNT()

返回列的函数

MAX()

返回列的最大值

MIN()

返回列的最小值

SUM()

返回某列值之和

1、AVG()函数

代码语言：txt复制
SELECT AVG(prod_price) AS avg_price   -- 求平均值
FROM Products;

上面求解的是所有行各自的平均值，也可以指定某个特定的行来求解：

代码语言：txt复制
SELECT AVG(prod_price) AS avg_price   -- 求平均值
FROM Products
WHERE vend_id = 'DLLO1';   -- 指定特定的行

笔记：AVG()函数会忽略掉值NULL的行

2、COUNT()函数

COUNT()函数进行计数，可以使用它来确定表中的函数或者符合特定条件的行的数目，两种使用情况：

  • count(*)：不管是空值（NULL）还是非空值，都会统计进去
  • count(column)：对特定的列进行计数，会忽略表该列的NULL值
代码语言：txt复制
SELECT COUNT(*) AS num_cust
FROM Customers;

num_cust
--------
5

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

num_cust
--------
3

笔记：如果指定列名，则COUNT()函数会忽略指定列的值为空的行，但是如果COUNT()函数使用的是星号，则不会忽略

3、MAX()/MIN()函数

返回指定列中的最大值或者最小值

代码语言：txt复制
SELECT MAX(prod_price) AS MAX_price   -- 求最大值
SELECT MAX(prod_price) AS MIN_price   -- 求最小值
FROM Products;

笔记：上面的两个最值函数会自动忽略掉值为NULL的行

4、SUM()函数

返回指定列值的和（总计）

代码语言：txt复制
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

SUM()函数也可以用来合计计算值：

代码语言：txt复制
SELECT SUM(item_price * quantity) AS total_price   -- 返回所有物品的价钱之和
FROM OrderItems
WHERE order_num = 20005;

笔记：SUM()函数会自动忽略值为NULL的行

聚集不同值

上面的5个聚集函数都可以如下使用：

  • 对所有的行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）
  • 只包含不同的值，指定DISTINCT参数，表示去重之后再进行计算

笔记：ALL参数不需要指定，是默认行为

代码语言：txt复制
SELECT AVG(DISTINCT prod_price) AS avg_price   -- 去重之后再求平均值
FROM Products
WHERE vend_id = 'DLLO1';   -- 指定特定的行

笔记：

1、DISTINCT不能用于COUNT(*)；如果指定列名，则DISTINCT只能用于COUNT()

2、DISTINCT必须使用列名，不能用于计算或者表达式

3、DISTINCT用于MAX()和MIN()意义不大，因为最值不管是否考虑去重，都是一样的

组合聚集函数

在SELECT子句中是可以包含多个聚集函数

代码语言：txt复制
SELECT 
	AVG(prod_price) AS avg_price   -- 求平均值
	,MAX(prod_price) AS max_price   -- 求最大值
	,MIN(prod_price) AS min_price   -- 求最小值
	,COUNT(*) AS num_items   -- 物品的数目
	
FROM Products;

分组数据

分组使用的是两个子句：

  • GROUP BY()
  • HAVING()
创建分组

分组是使用SELECT子句的GROUP BY子句建立的，看个例子：

代码语言：txt复制
SELECT 
	vend_id
	,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;   -- 分组的列

GROUP BY子句使用时候的常见规定：

  1. GROUP BY子句可以包含任意数目的列，可以对分组进行嵌套
  2. GROUP BY子句中列出的每一列都是检索列或者有效的表达式（但是不能是聚集函数）
  3. 如果在SELECT中使用表达式，则必须在GROUP BY子句中使用相同的表达式，而不是使用别名
  4. 除了聚集函数外，SELECT语句中的每列都必须在GROUP BY子句中列出
  5. 如果分组中包含具有NULL的行，则NULL将作为一个分组返回；如果列中出现多个NULL，它们将分成一个组
  6. GROUP BY子句必须在WHERE子句之后，ORDER BY子句之前
  7. GROUP BY子句中可以使用相对位置：GROUP BY 2, 1 表示先根据第二个列分组，再根据第一个列分组
过滤分组

在WHERE子句中指定过滤的是行而不是分组；实际上WHERE种并没有分组的概念。在SQL使用HAVING来实现过滤分组；

笔记：WHERE过滤行，HAVING过滤分组

代码语言：txt复制
SELECT
	cust_id
	,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;    -- 过滤分组

WHERE和HAVING的区别：

  • WHERE在数据过滤前分组，排除的行不在分组统计中
  • HAVING在数据分组后进行过滤
代码语言：txt复制
SELECT
	vend_id
	,COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4   -- 分组前先执行，找出符合条件的数据
GROUP BY vend_id
HAVING COUNT(*) >= 2;  -- 分组后再执行，找出数目大于2的数据
分组和排序

ORDER BY 和GROUP BY的差异：

ORDER BY

GROUP BY

对产生的输出排序

对行分组，但输出可能不是分组的顺序

任意列都可以使用（非选择的列也可以使用）

只可能使用选择列或者表达式列，而且必须使用每个选择列表达式

不一定需要

如果和聚集函数一起使用列，则必须使用

代码语言：txt复制
SELECT
	order_num
	,COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;   -- 先分组再过滤，最后排序输出
SELECT子句顺序

在这里总结一下SELECT子句的相关顺序：

子句

说明

是否必须使用

SELECT

要返回的列或者表达式

是

FROM

从中检索数据的表

仅在从表选择数据时使用

WHERE

行级过滤

否

GROUP BY

分组说明

仅在按照组计算聚集时使用

HAVING

组级过滤

否

ORDER BY

输出排序顺序

否

使用子查询

任何SELECT语句都是查询，SQL还允许在查询中嵌套查询。

代码语言：txt复制
SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
FROM Orders
WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

笔记：子查询总是从内向外处理

代码语言：txt复制
SELECT Customers    -- 最后根据找出的cust_id查询Customers
FROM cust_id IN(SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
                FROM Orders
                WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                                    FROM OrderItems
                                    WHERE prod_id = 'RGAN01'));
作为计算字段使用子查询

使用子查询的另一个方法是创建计算字段

代码语言：txt复制
SELECT 
	cust_name
	,cust_state
	,(SELECT COUNT(*)   -- 将子查询作为一个计算字段输出：统计每个cust_id的数量
    FROM Orders
    WHERE Orders.cust_id = Customers.cust_id) AS orders   -- Orders.cust_id = Customers.cust_id 使用完全限定列名来避免歧义
FROM Customers
ORDER BY cust_name;

联结表

SQL最强大的功能就是数据查询的过程中使用联结表（join）。

创建联结

通过指定要联结的表和它们的联结方式即可创建联结。

代码语言：txt复制
SELECT
	vend_name,
	prod_name,
	prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;    -- 指定联结条件

如果上面的代码中没有WHERE子句来指定联结条件，则返回的是笛卡尔积，返回出来数的行就是第一个表中的行乘以第二个表中的行。

笔记：返回笛卡尔积的联结，也称做叉联结cross join

内联结inner join

使用最广泛的联结是等值联结，也称之为内联结inner join。实现上面语句的内联结代码：

代码语言：txt复制
SELECT
	vend_name,
	prod_name,
	prod_price
FROM Vendors
INNER JOIN Products   -- 内联结
ON Vendors.vend_id = Products.vend_id;    -- 指定联结条件
联结多个表
代码语言：txt复制
SELECT
	vend_name,
	prod_name,
	prod_price
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id   -- 多个表的联结
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

我们通过联结方式来实现子查询的结果：

代码语言：txt复制
-- 子查询
SELECT Customers    -- 最后根据找出的cust_id查询Customers
FROM cust_id IN(SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
                FROM Orders
                WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的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'

创建高级联结

使用表别名

在SQL语句中可以给表取别名：

代码语言：txt复制
SELECT 
	cust_name,
	cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI   -- 取别名，看上去更简洁
WHERE C.cust_id = O.cust_id   -- 多个表联结查询
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'
使用不同类型的联结

介绍3种不同的联结：

  • 自联结self join
  • 自然联结natural join
  • 外联结outer join

1、自联结self join

代码语言：txt复制
-- 子查询
SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');
                   
-- 内联结
SELECT c1.cust_id, c2.cust_name, c1.cust_contact
FROM Customers AS c1,  Customers AS c2   -- 相同的表使用两次
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

上面使用了Customers表两次，为了避免歧义，必须使用不同的别名加以区分。

2、自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。自然联结排除多次出现，是每一列只返回一次。

代码语言：txt复制
SELECT 
	C.*
	,O.order_num
	,O.order_date
	,OI.prod_id
	,OI.quantity
	,OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id   -- 多个表联结查询
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'

3、外联结

有时候我们需要将一个表中的行和另一个表中行相关联，但是有时候也需要包含那些没有关联行的行记录，比如下面的场景中：

  • 对每个顾客下的订单数进行统计，包含那些至今尚未下单的顾客
  • 列出所有产品以及订购数量，包含没有人订购的产品
  • 计算平均销售规模，包含那些至今尚未下订单的顾客

当联结中包含了那些在相关表中没有关联行的行，这种联结称之为外联结。比如：检索出包括没有订单顾客在内的所有顾客。

代码语言：txt复制
SELECT 
	C.cust_id
	,O.order_num
FROM Customers AS C
LEFT OUTER JOIN Orders AS O   -- 外连接
ON Customers.cust_id = Orders.cust_id

上面的代码中表示包含左边所有行的记录；如果是右边，使用RIGHT OUTER。因此外联结实际上有两种形式，它们之间可以互换

  • 左外联结
  • 右外联结

还有一种比较特殊的外联结，叫做全外联结full outer join，它检索的是两个表中的所有行并关联那些可以关联的行。全外联结包含两个表的不关联的行

代码语言：txt复制
SELECT 
	C.cust_id
	,O.order_num
FROM Customers AS C
FULL OUTER JOIN Orders AS O   -- 外连接
ON Customers.cust_id = Orders.cust_id
带有聚集函数的联结

检索所有顾客及每个顾客所有的订单数：

代码语言：txt复制
SELECT
	C.cust_id
	,COUNT(O.order_num) AS num_ord   -- 使用聚集函数统计订单数
FROM Customers AS C
INNER JOIN Orders   
ON C.cust_id = O.cust_id   -- 关联两个表
GROUP BY Customers.cust_id   -- 分组
使用联结和联结条件

总结一下联结和使用要点：

  1. 注意使用联结的类型：一般是使用内联结，有时候外联结有有效
  2. 要保证使用正确的联结条件，否则会返回不正确的数据
  3. 记得提供联结条件，否则返回的是笛卡尔积
  4. 一个联结中可以包含多个表，甚至可以对不同的表使用不同的联结类型。要注意测试每个联结
数据库 云数据库MySQL 云数据库SQLServer

0 人点赞