SQL必知必会
了解SQL
数据库的概念和术语
数据库:保存有组织的数据的容器
表:某种特定类型数据的结构化清单
模式:关于数据库和表的布局及特性的信息
列:表中的一个字段,所有表都是由一个或多个列组成的
数据类型:允许什么类型的数据。每个表都有相应的数据类型,它限制(或允许)该列中存储的数据
行(row):表中的一个记录
主键:一列(或几列),其值能够唯一标识表中的每一行
主键requirement
-
任意两行都不具有相同的主键值
-
每一行都必须具有一个主键值(主键不允许空值NULL)
-
主键列中的值不允许修改或更新
-
主键值不能重用(如果某行从表中删除,它的主键不能赋给以后的新行)
SQL的优点
-
SQL不是某个特定数据库厂商专有的语言。绝大多数重要的DBMS支持SQL,所以学习此语言使你几乎能与所有数据库打交道
-
SQL简单易学。它的语句全都是由有着很强描述性的英语单词组成,而且这些单词的数目不多
-
SQL虽然看上去很简单,但实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作
检索数据
SELECT语句
SELECT prod_name FROM Products;
结束SQL语句
多条SQL语句必须以;分号分隔。大多数DBMS不需要在单条SQL语句后加分号,但也有DBMS可能必须在单条SQL语句后加分号。即使不一定需要,加上分号也肯定没坏处
SQL语句和大小写
SQL语句不区分大小写,因此SELECT与select是相同的。同样,写成select也没有关系。许多SQL开发人员喜欢对SQL关键字使用大些,而对列名和表名使用小写,这样做更易于阅读和调试。
使用空格
在处理SQL语句时,其中所有空格都会忽略,SQL语句可以写成长长的一行,也可以分写在多行。
检索多个列
SELECT prod_id, prod_name, prod_price FROM Products;
检索所有列
SELECT * FROM Products;
attention:使用通配符
除非确实需要表中的每一列,否则最好别使用通配符。虽然使用通配符能让自己省事儿,不用明确列出所需列,但检索不需要的列通常会降低检索速度和应用程序的性能
检索不同的值
SELECT DISTINCT vend_id FROM Products;
如果使用DISTINCT关键字,它必须在列名的前面
不能部分使用DISTINCT
DISTINCT关键字作用于所有的列,不仅仅是跟在其后的那一列。例如,指定SELECT DISTINCT vend_id, prod_price, 则9行里的6行都会被检索出来,因为指定的两列组合起来有6个不同的结果。
限制结果
SELECT语句返回指定表中所有匹配的行,很可能是每一行,如果只想返回第一行或者一定数量的行,则需要限制结果
每一种数据库,返回行的语句是不同的
SQL Serve
SELECT Top 5 prod_name FROM Products;
DB2
SELECT prod_name FROM Products FETCH FIRST 5 ROWS ONLY;
Oracle
SELECT prod_name FROM Products WHERE ROWNUM <= 5
MySQL MariaDB PostgreSQL SQLite需要用LIMIT
SELECT prod_name FROM Products LIMIT 5;
指定从第5行起的5行数据
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
使用注释
SELECT prod_name FROM Products LIMIT 5; -- 这是一条注释
# 这是一条注释
SELECT prod_name FROM Products LIMIT 5;
/* SELECT prod_name FROM Products LIMIT 5; */
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
排序检索数据
子句
SQL语句由子句构成,有些子句是必须的,有些则是可选的。一个子句通常由一个关键字加上所提供的数据组成
为了明确地排序用SELECT语句检索出的数据,可使用ORDER BY子句
SELECT prod_name FROM Product ORDER BY prod_name
ORDER BY子句的位置
在指定一条ORDER BY子句时,应该保证它是SELECT语句中最后一条子句,如果它不是最后一条子句,将会出错
通过非选择列进行排序
通常,ORDER BY子句中使用的列将是为了显示而选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的
按多个列排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
首先按价格,然后按名称排序
指定排序方向
指定数据升序或降序排列
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;
多个列排序的情况
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
DESC
关键字之应用到直接位于其前面的列名,上面的例子中,只对prod_price
列指定DESC
,对prod_name
列不指定,因此prod_price
列以降序排序,而prod_name
列仍按照标准的升序排列
过滤数据
使用WHERE子句
SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49
注意:WHERE子句的位置
在同时使用ORDER BY和WHERE子句时,应该让ORDER BY 位于 WHERE之后,否则将会产生错误
检查单个值
小于
SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10;
小于等于
SELECT prod_name, prod_price
FROM Products
WHERE prod_price <= 10;
不等于
SELECT vend_id, prod_name
FROM Products
WHERE vend_id != 'DLL01';
范围值检查
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
空值检查
在创建表时,表设计人员可以指定其中的列能否不包含值。在一个列不包含值时,称其包含空值NULL
SELECT prod_name
FROM Products
WHERE prod_price is NULL;
高级数据过滤
组合WHERE子句
AND 操作符
要通过不止一个列进行过滤,可以使用AND操作符给WHERE子句附加条件
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4
OR 操作符
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
求值顺序
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL001' OR vend_id = 'BRS01' AND prod_price >= 10;
当SQL看到上述WHERE子句时,它理解为:由供应商BRS01制造的价格为10美元以上的所有产品,以及由供应商DLL01制造的所有产品,而不管其价格如何。由于AND在求值过程中优先级更高,操作符被错误地组合了。
解决办法
使用圆括号对操作符进行明确分组
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL001' OR vend_id = 'BRS01') AND prod_price >= 10;
IN操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name;
IN操作符完成了与OR相同的功能
为什么要使用IN操作符?其优点如下:
-
在有很多合法选项时,IN操作符的语法更清楚,更直观
-
在与其他AND和OR操作符组合使用时,求值顺序更容易管理
-
IN操作符一般比一组OR操作符执行得更快
-
IN的最大优点是可以包含其他SELECT语句,能够更动态地建立WHERE子句
NOT操作符
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
等同于
SELECT prod_name
FROM Products
WHERE vend_id != 'DLL01'
ORDER BY prod_name;
SELECT prod_name
FROM Products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;
为什么使用NOT
对于这里的这种简单的WHERE子句,使用NOT确实没有什么优势。但在更复杂的子句中,NOT是非常有用,NOT是非常有用的;例如,在与IN操作符联合使用时,NOT可以非常简单地找出与条件列表不匹配的行
用通配符进行过滤
LIKE 操作符
百分号(%)通配符
检索以任意Fish开头的字符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%'
%
告诉 DBMS 接受 Fish 之后的任意字符,不管它有多少字符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%'
搜索模式’%bean bag%'表示匹配任何位置上包含文本 bean bag 的值,
不论它之前或之后出现什么字符
下面的例子找出以 F 起头、以 y 结尾的所有产品
SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y'
根据部分信息搜索电子邮件地址
WHERE email LIKE 'b%@forta.com'
需要特别注意,除了能匹配一个或多个字符,%
还能匹配0个字符;%
代表搜索模式中给定位置的0个、1个或多个字符
%不能匹配NULL
通配符%
看起来像是可以匹配任何东西,但是无法匹配NULL
下划线__通配符
另一个有用的通配符是下划线(_)。下划线的用途与%一样,但它只匹配单个字符,而不是多个字符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
Result
prod_id prod_name
-------------------
BR02 12 inch teddy bear
BR03 18 inch teddy bear
这个 WHERE 子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行:第一行中下划线匹配 12,第二行中匹配 18。8 inch teddy bear 产品没有匹配,因为搜索模式要求匹配两个通配符而不是一个
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '% inch teddy bear';
Result
prod_id prod_name
--------------------
BR01 8 inch teddy bear
BR02 12 inch teddy bear
BNR3 18 inch teddy bear
方括号[]通配符
找出所有名字以 J 或 M 起头的联系人,可进行如下查询
SELECT cust_contact FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
cust_contact
-----------------
Jim Jones
John Smith Michelle Green
此语句的 WHERE 子句中的模式为’[JM]%'。这一搜索模式使用了两个不同的通配符。[JM]匹配方括号中任意一个字符,它也只能匹配单个字符。因此,任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符,返回所需结果。
此通配符可以用前缀字符^(脱字号)来否定。例如,下面的查询匹配以 J 和 M 之外的任意字符起头的任意联系人名(与前一个例子相反)
SELECT cust_contact FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;
使用通配符的技巧
通配符搜索一般比前面讨论的其他搜索要耗费更长的时间处理,这里有一些使用通配符时要记住的技巧
-
不要过度使用通配符,如果其他操作符能达到相同的目的,应该使用其他操作符
-
在确实需要使用通配符时,也尽量不要把它们用在搜索模式的开始处,把通配符置于开始处,搜索起来是最慢的
-
仔细注意通配符的位置。如果放错地方,可能不会反悔想要的数据
创建计算字段
计算字段
我们需要直接从数据库中检索出转换、计算或格式化过的数据,而不是检索出数据,然后再在客户端应用程序中重新格式化
拼接字段
拼接
将值联结到一起(将一个值附加到另一个值)构成单个值
在 SQL 中的 SELECT 语句中,可使用一个特殊的操作符来拼接两个列。根据你所使用的 DBMS,此操作符可用加号(+)或两个竖杠(||)表示。在 MySQL 和 MariaDB 中,必须使用特殊的函数
SELECT Concat(vend_name, '(' , vend_country, ')')
FROM crashcourse.vendors;
使用别名
别名用 AS 关键字赋予
SELECT Concat(vend_name, '(' , vend_country, ')')
AS vend_title
FROM crashcourse.vendors;
Best Practice
AS关键字是可选的,不过最好使用它,这被视为一条最佳实践
TIPS: 别名的其他用途
别名还有其他用途。常见的用途包括在实际的表列名包含不合法的字符(如空格)时重新命名它,在原来的名字含混或容易误解时扩充它
执行算术计算
计算字段的另一常见用途是对检索出的数据进行算术计算
SELECT prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
Result
prod_id | quantity | item_price | expanded_price |
---|---|---|---|
RGAN01 | 5 | 4.9900 | 24.9500 |
BR03 | 5 | 11.9900 | 59.9500 |
BNBG01 | 10 | 3.4900 | 34.9000 |
BNBG02 | 10 | 3.4900 | 34.9000 |
BNBG03 | 10 | 3.4900 | 34.9000 |
使用函数处理数据
使用函数
大多数SQL实现支持以下类型的函数
-
用于处理文本字符串(如删除或填充值,转换值为大写或小写)的文本函数
-
用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数
-
用于处理日期和时间值并从这些值中提取特定成分(如返回两个日期之差,检查日期有效性)的日期和时间函数
-
返回 DBMS 正使用的特殊信息(如返回用户登录信息)的系统函数
文本处理函数
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
Result
vend_name --------------------------- Bear Emporium
Bears R Us
Doll House Inc.
Fun and Games
vend_name_upcase ---------------------------- BEAR EMPORIUM
BEARS R US
DOLL HOUSE INC.
FUN AND GAMES
SOUNDEX
SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX 考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母比较。虽然 SOUNDEX 不是 SQL 概念,但多数 DBMS 都提供对 SOUNDEX 的支持
日期和时间处理函数
返回order_date
为2012年的
SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2012;
数值处理函数
常用数值处理函数
函数 | 说明 |
---|---|
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
EXP() | 返回一个数的指数值 |
PI() | 返回圆周率 |
SIN() | 返回一个角度的正弦 |
SQRT() | 返回一个数的平方根 |
TAN() | 返回一个角度的正切 |
汇总数据
聚集函数
函数 | 说明 |
---|---|
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
AVG() 函数
AVG()通过对表中行数计数并计算其列值之和,求得该列的平均值。AVG() 可用来返回所有列的平均值,也可以用来返回特定列或行的平均值
用来返回所有列的平均值
SELECT AVG(prod_price) AS avg_price
FROM Products;
用来返回特定列或行的平均值
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL001'
这条 SELECT 语句与前一条的不同之处在于,它包含了 WHERE 子句。此 WHERE 子句仅过滤出 vend_id 为 DLL01 的产品,因此 avg_price 中返回的值只是该供应商产品的平均值
COUNT() 函数
可利用 COUNT()确定表中行的数目或符合特定条件的行的数目
COUNT()函数有两种使用方式
-
使用
COUNT(*)
对表中行的数目进行计数,不管表列中包含的是空值还是非空值 -
使用
COUNT(column)
对特定列中具有值的行进行计数,忽略 NULL 值
COUNT(*)
会返回空值
SELECT COUNT(*) AS num_cust
FROM Customers;
COUNT(column)
不返回NULL
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
MAX() 函数
MAX()
返回指定列中的最大值
SELECT MAX(prod_price) AS max_price
FROM Products;
Tips
在用于文本数据时,MAX()返回按该列排序后的最后一行
MIN() 函数
MIN()的功能正好与 MAX()功能相反,它返回指定列的最小值
SELECT MIN(prod_price) AS min_price
FROM Products;
TIPS:
在用于文本数据时,MIN()返回该列排序后最前面的行
SUM() 函数
SUM()用来返回指定列值的和
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;
函数 SUM(quantity)返回订单中所有物品数量之和,WHERE 子句保证只统计某个物品订单中的物品
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;
聚集不同值
以上 5 个聚集函数都可以如下使用
-
对所有行执行计算,指定 ALL 参数或不指定参数(因为 ALL 是默认行为)
-
只包含不同的值,指定 DISTINCT 参数
下面的例子使用 AVG()函数返回特定供应商提供的产品的平均价格。它与上面的 SELECT 语句相同,但使用了 DISTINCT 参数,因此平均值只考虑各个不同的价格:
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
可以看到,在使用了 DISTINCT 后,此例子中的 avg_price 比较高,因为有多个物品具有相同的较低价格。排除它们提升了平均价格
TIPS:
DISINTCT
不能用于COUNT(*
如果指定列名,则DISTINCT
只能用于COUNT()
,DISTINCT
不能用于COUNT(*)
组合聚集函数
目前为止的所有聚集函数例子都只涉及单个函数。但实际上,SELECT 语句可根据需要包含多个聚集函数
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;
分组数据
数据分组
使用分组可以将数据分为多个逻辑组,对每个组进行聚集计算
创建分组
分组是使用 SELECT 语句的 GROUP BY 子句建立的
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
Result
vend_id num_prods
------- ---------
BRS01 3
DLL01 4
FNG01 2
上面的 SELECT 语句指定了两个列:vend_id 包含产品供应商的 ID, num_prods 为计算字段(用 COUNT(*)函数建立)。GROUP BY 子句指示 DBMS 按 vend_id 排序并分组数据。这就会对每个 vend_id 而不是整个表计算 num_prods 一次。从输出中可以看到,供应商 BRS01 有 3 个产品,供应商 DLL01 有 4 个产品,而供应商 FNG01 有 2 个产品
因为使用了 GROUP BY,就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY 子句指示 DBMS 分组数据,然后对每个组而不是整个结果集进行聚集
在使用GOURP BY子句前,需要知道一些重要的规定
-
GROUP BY子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组
-
如果在GROUP BY子句中嵌套了分组,数据将在最后指定的分组上进行汇总。换句话说,建立分组时,指定的所有列都一起计算(所以不能从个别列取回数据)
-
GROUP BY 子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式,不能使用别名
-
大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)
-
除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出
-
如果分组列中包含具有NULL行的值,则NULL将作为一个分组返回,如果列中具有多行NULL值,它们将分为一组
-
GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前
过滤分组
除了能用 GROUP BY 分组数据外,SQL 还允许过滤分组,规定包括哪些分组,排除哪些分组
目前为止所学过的所有类型的WHERE
子句都可以用HAVING
来代替,唯一的差别是,WHERE
过滤行,而HAVING
过滤分组
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
最后一行增加了 HAVING子句,它过滤COUNT(*) >= 2(两个以上订单)的那些分组
HAVING和WHERE的差别
WHERE
在数据分组前进行过滤,HAVING
在数据分组后进行过滤
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
Result
vend_id num_prods
------- -----------
BRS01 3
FNG01 2
分组和排序
GROUP BY 和 ORDER BY 经常完成相同的工作,但它们非常不同,理解这一点很重要
ORDER BY | GROUP BY |
---|---|
对产生的输出排序 | 对行分组,但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列也可以使用) | 只可能使用选择列或表达式列,而且必须使用每个选择列表达式 |
不一定需要 | 如果与聚集函数一起使用列(或表达式),则必须使用 |
它检索包含三个或更多物品的订单号和订购物品的数目
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3;
order_num items
--------- -----
20006 3
20007 5
20008 5
20009 3
在这个例子中,使用 GROUP BY 子句按订单号(order_num 列)分组数据,以便 COUNT(*)函数能够返回每个订单中的物品数目。HAVING 子句过滤数据,使得只返回包含三个或更多物品的订单。最后,用 ORDER BY 子句排序输出
SELECT子句顺序
子句 | 说明 | 是否必须使用 |
---|---|---|
SELECT | 要返回的列或表达式 | 是 |
FROM | 从中检索数据的表 | 仅在从表选择数据时使用 |
WHERE | 行级过滤 | 否 |
GROUP BY | 分组说明 | 仅在按组计算聚集时使用 |
HAVING | 组级过滤 | 否 |
ORDER BY | 输出排序顺序 | 否 |
使用子查询
子查询
SELECT 语句是 SQL 的查询。我们迄今为止所看到的所有 SELECT 语句都是简单查询,即从单个数据库表中检索数据的单条语句
利用子查询进行过滤
Orders ----> 订单编号,客户ID,订单日期
OrderItems -----> 各订单的物品存储在相关的OrderItems表中
Customers -----> 存储各位顾客的信息=
需求:列出订购物品 RGAN01 的所有顾客信息
分析:
-
检索包含物品RGAN01的所有订单的编号
-
检索具有前一步骤列出的订单编号的所有顾客的ID
-
检索前一步骤返回的所有顾客ID的顾客信息
# (1) 检索包含物品ANV01 的所有订单的编号
SELECT order_num FROM crashcourse.orderitems
WHERE prod_id = 'ANV01';
# (2) 检索具有前一步骤列出的订单编号的所有顾客的ID
SELECT cust_id FROM crashcourse.orders
WHERE order_num IN (
SELECT order_num FROM crashcourse.orderitems
WHERE prod_id = 'ANV01'
);
# (3) 检索前一步骤返回的所有顾客ID的顾客信息
SELECT * FROM crashcourse.customers
WHERE cust_id IN (
SELECT cust_id FROM crashcourse.orders
WHERE order_num IN (
SELECT order_num FROM crashcourse.orderitems
WHERE prod_id = 'ANV01'
)
);
作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。假如需要显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID 存储在 Orders 表中
customers -----> 顾客信息
orders -----> 订单信息
分析:
-
从customers表中检索顾客信息
-
对于检索出的每个顾客,统计其在Orders表中的订单数目
SELECT cust_name,
cust_state,
(
SELECT COUNT(*)
FROM crashcourse.orders
WHERE orders.cust_id = customers.cust_id
) AS orders
FROM crashcourse.customers
ORDER BY cust_name;
需求:需要一个顾客ID列表,其中包含他们已订购的总金额
orders ----> order_num, cust_id
orderitems ----> order_num, quantity, item_price
分析:
-
计算每一种
order_num
的quantity * item_price
的总数 -
通过
order_num
链接到cust_id
SELECT cust_id,
(
SELECT SUM(quantity * item_price)
FROM orderitems
WHERE orderitems.order_num = orders.order_num
) AS total_ordered
FROM orders
需求:从Products表中检索所有的产品名称(prod_name),以及名为quant_sold的计算列,其中包含所售产品的总数(在OrderItems表上使用子查询和SUM(quantity)检索)
products -----> prod_id
orderitems ----> prod_id, quantity
SELECT prod_id,
(
SELECT SUM(quantity)
FROM orderitems
WHERE orderitems.prod_id = products.prod_id
) AS quant_sold
FROM products;
联结表
联结
SQL 最强大的功能之一就是能在数据查询的执行中联结(join)表。联结是利用 SQL 的 SELECT 能执行的最重要的操作,很好地理解联结及其语法是学习 SQL 的极为重要的部分
在能够有效地使用联结前,必须了解关系表以及关系数据库设计的一些基础知识。下面的介绍并不能涵盖这一主题的所有内容,但作为入门已经够了
关系表
有一个包含产品目录的数据库表,其中每类物品占一行。对于每一种物品,要存储的信息包括产品描述、价格,以及生产该产品的供应商
现在有同一供应商生产的多种物品,那么在何处存储供应商名、地址、联系方法等供应商信息呢?
在这个例子中可建立两个表:一个存储供应商信息,另一个存储产品信息。Vendors 表包含所有供应商信息,每个供应商占一行,具有唯一的标识。此标识称为主键(primary key),可以是供应商 ID 或任何其他唯一值
Products 表只存储产品信息,除了存储供应商 ID(Vendors 表的主键) 外,它不存储其他有关供应商的信息。Vendors 表的主键将 Vendors 表与 Products 表关联,利用供应商 ID 能从 Vendors 表中找出相应供应商的详细信息
这样做的好处是:
-
供应商信息不重复,不会浪费时间和空间
-
如果供应商信息变动,可以只更新
Vendors
表中的单个记录,相关表中的数据不用改动 -
由于数据不重复,数据显然是一致的,使得处理数据和生成报表更简单
为什么使用联结
联结是一种机制,用来在一条 SELECT 语句中关联表,因此称为联结
创建联结
创建联结非常简单,指定要联结的所有表以及关联它们的方式即可
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
Result
vend_name prod_name prod_price
-------------------------------------------------------------
Doll House Inc. Fish bean bag toy. 3.4900
Doll House Inc. Bird bean bag toy. 3.4900
Doll House Inc. Rabbit bean bag toy. 3.4900
Bears R Us 8 inch teddy bear. 5.9900
Bears R Us 12 inch teddy bear. 8.9900
Bears R Us. 18 inch teddy bear. 11.9900
Doll House Inc. Raggedy Ann. 4.9900
Fun and Games King doll. 9.4900
Fun and Games. Queen doll 9.4900
SELECT 语句与前面所有语句一样指定要检索的列。这里最大的差别是所指定的两列(prod_name 和 prod_price)在一个表中,而第三列(vend_name)在另一个表中
现在来看 FROM 子句。与以前的 SELECT 语句不一样,这条语句的 FROM 子句列出了两个表:Vendors 和 Products 它们就是这条 SELECT 语句联结的两个表的名字。这两个表用 WHERE 子句正确地联结,WHERE 子句指示 DBMS 将 Vendors 表中的 vend_id 与 Products 表中的 vend_id 匹配起来
WHERE子句的重要性
若没有WHERE
后的联结条件,则返回的结果是笛卡尔积
笛卡尔积
由于没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
从上面的输出可以看到,相应的笛卡尔积不是我们想要的。这里返回的数据用每个供应商匹配了每个产品,包括了供应商不确定的产品(即使供应商根本就没有产品)
叉联结
有时,返回笛卡尔积的联结,也称叉联结(cross join)
内联结
目前为止使用的联结称为等值联结(equijoin),它基于两个表之间的相等测试。这种联结也称为内联结(inner join)。其实,可以对这种联结使用稍微不同的语法,明确指定联结的类型
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
这里,两个表之间的关系是以 INNER JOIN 指定的部分 FROM 子句。在使用这种语法时,联结条件用特定的 ON 子句而不是 WHERE 子句给出。传递给 ON 的实际条件与传递给 WHERE 的相同
联结多个表
SQL 不限制一条 SELECT 语句中可以联结的表的数目。创建联结的基本规则也相同。首先列出所有表,然后定义表之间的关系。
SELECT prod_name, vend_name, prod_price
FROM products
INNER JOIN orderitems ON products.prod_id = orderitems.prod_id
INNER JOIN vendors ON products.vend_id = vendors.vend_id;
TIPS:
虽然SQL本身不限制每个联结约束中表的数目,但实际上许多DBMS都有限制
例子
列出订购物品ANV01的所有顾客
SELECT *
FROM orders
INNER JOIN customers ON customers.cust_id = orders.cust_id
INNER JOIN orderitems ON orderitems.order_num = orders.order_num
WHERE prod_id = 'ANV01';
创建高级联结
使用表别名
第7课介绍了如何使用别名引用被索引的表列,给列起别名的语法如下:
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors
ORDER BY vend_name;
给表起别名的用法如下:
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';
使用不同类型的联结
迄今为止,我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结:自联结(self-join)、自然联结(natural join)和外联结(outer join)
自联结
使用表别名的一个主要原因是能在一条SELECT语句中不止一次引用相同的表
例子
假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones 工作的公司,然后找出在该公司工作的顾客。下面是解决此问题的一种方法:
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, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
WHERE c1.cust_id = c2.cust_id
AND c1.cust_contact = 'Jim Jones';
Customers 第一次出现用了别名 C1,第二次出现用了别名 C2。现在可以将这些别名用作表名。例如,SELECT 语句使用 C1 前缀明确给出所需列的全名。如果不这样,DBMS 将返回错误,因为名为 cust_id、cust_name、cust_contact 的列各有两个DBMS 不知道想要的是哪一列
用自联结而不用子查询
自联结通常作为外部语句,用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的,但需要DBMS处理联结远比处理子查询快得多
自然联结
无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结的列)。标准的联结(前一课中介绍的内联结)返回所有数据,相同的列甚至多次出现。自然联结排除多次出现,使每列只返回一次
自然联结要求你只能选择那些唯一的列,一般通过对一个表使用通配符 SELECT *
,而对其他表的列使用明确的子集来完成。
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';
外联结
许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行
可能需要使用联结完成以下工作:
-
对每个顾客下的订单进行计数,包括那些至今尚未下订单的顾客
-
列出所有产品及订购数量,包括没有人订购的产品
-
计算平均销售规模,包括那些至今尚未下订单的顾客
SELECT c.cust_id, o.order_num
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id;
还存在另一种外联结,就是全外联结(full outer join),它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行。全外联结的语法如下:
SELECT Customers.cust_id, Orders.order_num
FROM Orders
FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;
使用带聚集函数的联结
例子:要检索所有顾客及每个顾客所下的订单数
SELECT c.cust_id,
COUNT(o.order_num) AS total_order
FROM customers c
INNER JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.cust_id;
这条SELECT语句使用INNER JOIN将Customers和Orders表互相关联。 GROUP BY 子句按顾客分组数据,因此,函数调用 COUNT(Orders.order_num) 对每个顾客的订单计数,将它作为 num_ord 返回
cust_id num_ord
---------- --------
1000000001 2
1000000003 1
1000000004 1
1000000005 1
聚集函数也可以方便地与其他联结一起使用
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
cust_id num_ord
---------- -------
1000000001 2
1000000002 0
1000000003 1
1000000004 1
1000000005 1
使用联结和联结条件
汇总连结及其使用的要点
-
注意所使用的联结类型,一般我们使用内联结,但使用外联结也有效
-
关于确切的联结语法,应查看具体的文档,看相应的DBMS支持何种语法(大多数DBMS使用这两课中描述的某种语法)
-
保证使用正确的联结条件(不管采用哪种语法),否则会返回不正确的数据
-
应该总是提供联结条件,否则会得出笛卡尔积
-
在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的,一般也很有用,但应该在一起测试他们前分别测试每个联结。这会使故障排除更为简单
组合查询
多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句 但是,SQL 也允许执行多个查询(多条 SELECT 语句),并将结果作为一个查询结果集返回。这些组合查询通常称为并(union)或复合查询
主要有两种情况需要使用组合查询
-
在一个查询中从不同的表返回结构数据
-
对一个表执行多个查询,按一个查询返回数据
创建组合查询
可用 UNION 操作符来组合数条 SQL 查询。利用 UNION,可给出多条SELECT 语句,将它们的结果组合成一个结果集
使用UNION
举个例子,假如需要 Illinois、Indiana 和 Michigan 等美国几个州的所有顾客的报表,还想包括不管位于哪个州的所有的 Fun4All
第一条 SELECT 把 Illinois、Indiana、Michigan 等州的缩写传递给 IN 子句,
检索出这些州的所有行。第二条 SELECT 利用简单的相等测试找出所有 Fun4All。
组合这两条语句,可以如下进行
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';
为了便于参考,这里给出使用多条 WHERE 子句而不是 UNION 的相同查询
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
OR cust_name = 'Fun4All';
在这个简单的例子中,使用 UNION 可能比使用 WHERE 子句更为复杂。但对于较复杂的过滤条件,或者从多个表(而不是一个表)中检索数据的情形,使用 UNION 可能会使处理更简单
UNION规则
可以看到UNION非常容易使用,但在进行组合时需要注意几条规则
-
UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔(因此,如果组合四条SELECT语句,将要使用三个UNION关键字)
-
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不需要以相同的次序列出)
-
列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型(例如,不同的数值类型或不同的日期类型)
包含或取消重复的行
UNION 从查询结果集中自动去除了重复的行;换句话说,它的行为与一条 SELECT 语句中使用多个 WHERE 子句条件一样。因为 Indiana 州有一个 Fun4All 单位,所以两条 SELECT 语句都返回该行。使用 UNION 时,重复的行会被自动取消
这是 UNION 的默认行为,如果愿意也可以改变它。事实上,如果想返回所有的匹配行,可使用UNION ALL而不是UNION
对组合查询结果排序
SELECT 语句的输出用 ORDER BY 子句排序。在用 UNION 组合查询时,只能使用一条 ORDER BY 子句,它必须位于最后一条 SELECT 语句之后。对于结果集,不存在用一种方式排序一部分,而又用另一种方式排序另一部分的情况,因此不允许使用多条ORDER BY子句
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 = 'F un4All'
ORDER BY cust_name, cust_contact;
插入数据
数据插入
INSERT 用来将行插入(或添加)到数据库表,插入有几种方式
-
插入完整的行
-
插入行的一部分
-
插入某些查询的结果
插入完整的行
把数据插入表中的最简单方法是使用基本的INSERT
语法,它要求指定表名和插入到新行中的值,下面举一个例子:
INSERT INTO customers
VALUES (
'10000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL );
这个例子将一个新顾客插入到 Customers 表中。存储到表中每一列的数据在 VALUES 子句中给出,必须给每一列提供一个值。如果某列没有值,如上面的 cust_contact 和 cust_email 列,则应该使用 NULL 值(假定表允许对该列指定空值)。各列必须以它们在表定义中出现的次序填充
INTO关键字
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);
因为提供了列名,VALUES 必须以其指定的次序匹配指定的列名,不一定按各列出现在表中的实际次序。其优点是,即使表的结构改变,这条 INSERT 语句仍然能正确工作
TIPS
对于有自增的列,赋值时给一个NULL
即可
INSERT INTO customers VALUES (
NULL,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'CN',
NULL,
NULL
)
插入部分行
使用INSERT
的推荐方法是明确给出表的列名,使用这种语法,还可以省略列,这表示可以只给某些列提供值,给其他列不提供值
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA');
在本课前面的例子中,没有给 cust_contact 和 cust_email 这两列提供值。这表示没必要在 INSERT 语句中包含它们。因此,这里的 INSERT 语句省略了这两列及其对应的值
省略列
如果表的定义允许,则可以在INSERT
操作中省略某些列,省略的列必须满足以下条件:
-
该列定义为允许
NULL
值(无值或空值) -
在表定义中给出默认值,这表示如果不给出值,将使用默认值
插入检索出的数据
INSERT 还存在另一种形式,可以利用它将 SELECT 语句的结果插入表中,这就是所谓的 INSERT SELECT。顾名思义,它是由一条 INSERT 语句和一条 SELECT 语句组成的
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 语句从 CustNew 检索出要插入的值,而不是列出它们。SELECT 中列出的每一列对应于 Customers 表名后所跟的每一列。这条语句将插入多少行呢?这依赖于 CustNew 表有多少行。
从一个表复制到另一个表
有一种数据插入不使用 INSERT 语句。要将一个表的内容复制到一个全新的表(运行中创建的表),可以使用SELECT INTO语句
CREATE TABLE CustCopy AS
SELECT * FROM Customers;
更新和删除数据
更新数据
更新(修改)表中的数据,可以使用 UPDATE 语句。有两种使用 UPDATE的方式:
-
更新表中的特定行
-
更新表中的所有行
基本的 UPDATE 语句由三部分组成,分别是:
-
要更新的表
-
列名和它们的新值
-
确定要更新哪些行的过滤条件
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
更新多个列的语法稍有不同:
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';
其中 NULL 用来去除 cust_email 列中的值。这与保存空字符串很不同( 空字符串用’'表示,是一个值 ),而 NULL 表示没有值。
删除数据
从一个表中删除(去掉)数据,使用 DELETE 语句。有两种使用 DELETE的方式
-
从表中删除特定的行
-
从表中删除所有的行
TIPS:
不要省略WHERE
子句,在使用DELETE
时一定要细心,因为稍不注意,就会错误地删除表中所有行
DELETE FROM customers
WHERE cust_id = '1000000006'
更新和删除的指导原则
下面是许多 SQL 程序员使用 UPDATE 或 DELETE 时所遵循的重要原则
-
除非确实打算更新和删除每一行,否则绝对不要使用不带
WHERE
子句的UPDATE
和DELETE
语句 -
保证每个表都有主键
-
在 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进
行测试,保证它过滤的是正确的记录 -
使用强制实施引用完整性的数据库
-
防止执行不带 WHERE 子句的 UPDATE 或 DELETE 语句
创建和操纵表
创建表
SQL 不仅用于表数据操纵,还用来执行数据库和表的所有操作,包括表本身的创建和处理
一般两种创建表的方法:
-
多数DBMS都具有交互式创建和管理数据表的工具
-
表也可以直接用SQL语句操纵
表创建基础
利用CREATE TABLE
创建表,必须给出下列信息
-
新表的名字,在关键字
CREATE TABLE
之后给出 -
表列的名字和定义,用逗号分隔
-
有点DBMS还要求指定表的位置
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);
使用NULL值
每个表列要么是 NULL 列,要么是 NOT NULL 列
(
order_num INTEGER NOT NULL,
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL
);
指定默认值
SQL 允许指定默认值,在插入行时如果不给出值,DBMS 将自动采用默认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定
CREATE TABLE OrderItems
(
order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
item_price DECIMAL(8,2) NOT NULL
)
默认值经常用于日期或时间戳列, MySQL 用户指定 DEFAULT CURRENT_DATE()
CREATE TABLE `shop` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`code` varchar(64) NOT NULL,
`name` varchar(256) NOT NULL COMMENT '租户名称',
`status` tinyint(4) NOT NULL DEFAULT 1 COMMENT '状态,1:有效,0:无效',
`creation_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_id` (`code`)
)
更新表
以下是使用 ALTERTABLE 时需要考虑的事情
-
理想情况下,不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求,避免今后对表的结构做大改动
-
所有的 DBMS 都允许给现有的表增加列,不过对所增加列的数据类型(以及 NULL 和 DEFAULT 的使用)有所限制
-
许多 DBMS 不允许删除或更改表中的列
-
多数 DBMS 允许重新命名表中的列
-
许多 DBMS 限制对已经填有数据的列进行更改,对未填有数据的列几
乎没有限制
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
这条语句给 Vendors 表增加一个名为 vend_phone 的列,其数据类型为 CHAR
更改或删除列、增加约束或增加键,这些操作也使用类似的语法
ALTER TABLE Vendors
DROP COLUMN vend_phone;
复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:
-
用新的列布局创建一个新表;
-
使用 INSERT SELECT 语句, 从旧表复制数据到新表。有必要的话,可以使用转换函数和计算字段;
-
检验包含所需数据的新表;
-
重命名旧表(如果确定,可以删除它);
-
用旧表原来的名字重命名新表;
-
根据需要,重新创建触发器、存储过程、索引和外键。
删除表
使用 DROP TABLE 语句
DROP TABLE CustCopy;
重命名表
ALTER TABLE customersCopy RENAME TO copy_customers;
使用视图
### 视图
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Order.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
此查询用来检索订购了某种产品的顾客。任何需要这个数据的人都必须理解相关表的结构,知道如何创建查询和对表进行联结。检索其他产品(或多个产品)的相同数据,必须修改最后的WHERE
子句
现在,假如可以把整个查询包装成一个名为ProducCustomers
的虚拟表,则可以如下轻松地检索出相同的数据:
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';
这就是视图的作用。ProductCustomers是一个视图,作为视图,它不包含任何列或数据,包含的是一个查询(与上面用以正确联结表的相同查询)
#### 为什么使用视图
我们已经看到了视图应用的一个例子。下面是视图的一些常见应用。
-
重用SQL语句
-
简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道其基本查询细节
-
使用表的一部分而不是整个表
-
保护数据。可以授予用户访问表的特定部分的权限,而不是整个表的访问权限
-
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
创建视图之后,可以用与表基本相同的方式使用它们。可以对视图执行 SELECT 操作,过滤和排序数据,将视图联结到其他视图或表,甚至添加和更新数据
重要的是,要知道视图仅仅是用来查看存储在别处数据的一种设施。视
图本身不包含数据,因此返回的数据是从其他表中检索出来的。在添加
或更改这些表中的数据时,视图将返回改变过的数据
性能问题
因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时需要的所有检索。如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图,性能可能会下降得很厉害。因此,在部署使用了大量视图的应用前,应该进行测试
#### 视图的规则和限制
创建视图前,应该知道它的一些限制。不过,这些限制随不同的DBMS而不同,因此在创建视图时应该查看具体的DBMS文档
-
与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)
-
对于可以创建的视图数目没有限制
-
创建视图,必须具有足够的访问权限。这些权限通常由数据库管理人员授予
-
视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造视图。所允许的嵌套层数
-
许多DBMS禁止在视图查询中使用
ORDER BY
子句 -
有些DBMS要求对返回的所有列进行命名,如果列是计算字段,则需要使用别名
-
视图不能索引,也不能有关联的触发器或默认值
-
有些DBMS把视图作为只读的查询,这表示可以从视图检索数据,但不能将数据写回底层表
-
有些DBMS允许创建这样的视图,它不能进行导致行不再属于视图的插入或更新
### 创建视图
视图用CREATE VIEW
语句来创建
视图重命名
删除视图,可以使用DROP
语句,其语法为DROP VIEW viewname;
#### 利用视图简化复杂的联结
一个最常见的视图应用是隐藏复杂的SQL,这通常涉及联结
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM orders o
INNER JOIN orderitems oi ON oi.order_num = o.order_num
INNER JOIN customers c ON c.cust_id = o.cust_id;
使用视图进行查询
SELECT * FROM ProductCustomers;
#### 用视图重新格式化检索出的数据
视图的另一常见用途是重新格式化检索出的数据
CREATE VIEW VendorsTitle AS
SELECT CONCAT(vend_name, vend_country)
FROM vendors;
#### 用视图过滤不想要的数据
视图对于应用普通的WHERE
子句也很有用。例如,可以定义CustomerEMailList
视图,过滤没有电子邮件地址的顾客
CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
#### 使用视图与计算字段
在简化计算字段的使用上,视图也特别有用
CREATE VIEW OrderItemsExpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity * item_price AS expanded_price
FROM orderitems;