SQL必知必会

一.了解SQL

database 数据库

数据库软件称为数据库管理系统(DBMS Data Base Management System )

table 表

column 列

row 行

primary key 主键:唯一标识表中每行的这个列(或这几个列)称为主键。

表中的任何列都可以作为主键,只要它满足以下条件:

  • 任意两行都不具有相同的主键值
  • 每一行都必须具有一个主键值(主键列不允许有NULL)
  • 主键列中的值不允许修改或者更新
  • 主键值不能重用(如果某行从表中删除,它的主键不能赋给以后的新行)

几乎所有重要的DBMS都支持SQL(Structured Query Language)

二.检索数据

检索单个列

SELECT prod_name
FROM Products;
prod_name
-------------------
Fish bean bag toy
Bird bean bag toy
Rabbit bean bag toy
8 inch teddy bear
12 inch teddy bear
18 inch teddy bear
Raggedy Ann
King doll
Queen doll

检索多个列

SELECT prod_id, prod_name, prod_price
FROM Products;
prod_id prod_name prod_price

BNBG01 Fish bean bag toy 3.4900
BNBG02 Bird bean bag toy 3.4900
BNBG03 Rabbit bean bag toy 3.4900
BR01 8 inch teddy bear 5.9900
BR02 12 inch teddy bear 8.9900
BR03 18 inch teddy bear 11.9900
RGAN01 Raggedy Ann 4.9900
RYL01 King doll 9.4900
RYL02 Queen dool 9.4900

检索所有列

SELECT *
FROM Products;

检索不同的值

SELECT vend_id
FROM Products;
vend_id
----------
BRS01
BRS01
BRS01
DLL01
DLL01
DLL01
DLL01
FNG01
FNG01

使用DISTINCT关键字,指示数据库只返回不同的值

SELECT DISTINCT vend_id
FROM Products;
vend_id
----------
BRS01
DLL01
FNG01
注意:不能部分使用 DISTINCT
DISTINCT 字作用于所有的列,不 仅仅 是跟在其后的那一列。例
如,你指定 SELECT DISTINCT vend_id, prod_price ,除非指定的
两列完全相同,否 所有的行都会被 索出来。
对于MYSQL可以使用LIMIT OFFSET限制结果
SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;
LIMIT 5 OFFSET 5 指示 MySQL DBMS 返回从第 5 行起的 5 行数据。
第一个数字是指从哪儿开始,第二个数字是 索的行数。 句的
出是:
prod_name
-------------------
Rabbit bean bag toy
Raggedy Ann
King doll
Queen doll
所以, LIMIT 指定返回的行数。 LIMIT OFFSET 指定从哪儿开始。
在我 的例子中, Products 表中只有 9 品,所以 LIMIT 5 OFFSET
5 只返回了 4 行数据(因 没有第 5 行)。
使用注释
行内注释 
--之后的文本就是注释
#这一整行都将作为注释
/*和8/之间的任何内容都是注释

三.排序检索数据

如果不排序,数据一般将以它在 底层 表中出 示, 有可能是数据最初添加到表中的 序。但是, 如果数据随后进 更新或 除,那么 序将会受到 DBMS 重用回 收存储 的方式的影响。
了明确地排序用 SELECT 索出的数据,可使用 ORDER BY 子句。 ORDERBY子句取一个或多个列的名字,据此 对输 行排序。 看下面的例子:
SELECT prod_name
FROM Products
ORDER BY prod_name;
prod_name
--------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
Bird bean bag toy
Fish bean bag toy
King doll
Queen doll
Rabbit bean bag toy
Raggedy Ann

按多个列排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
prod_id prod_price prod_name
------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
RGAN01 4.9900 Raggedy Ann
BR01 5.9900 8 inch teddy bear
BR02 8.9900 12 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR03 11.9900 18 inch teddy bear
于上述例子中的 出, 在多个行具有相同的 prod_price 值时 才对产 品按 prod_name 行排序。如果 prod_price 列中所有的 都是 唯一的,则 不会按 prod_name 排序。
按列位置排序
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;
prod_id prod_price prod_name
------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
RGAN01 4.9900 Raggedy Ann
BR01 5.9900 8 inch teddy bear
BR02 8.9900 12 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR03 11.9900 18 inch teddy bear

指定排序方向

行降序排序,
指定 DESC 字。
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC;
prod_id prod_price prod_name
------- ---------- --------------------
BR03 11.9900 18 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR02 8.9900 12 inch teddy bear
BR01 5.9900 8 inch teddy bear
RGAN01 4.9900 Raggedy Ann
BNBG01 3.4900 Fish bean bag toy
BNBG02 3.4900 Bird bean bag toy
BNBG03 3.4900 Rabbit bean bag toy

用多个列排序

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;
prod_id prod_price prod_name
------- ---------- --------------------
BR03 11.9900 18 inch teddy bear
RYL01 9.4900 King doll
RYL02 9.4900 Queen doll
BR02 8.9900 12 inch teddy bear
BR01 5.9900 8 inch teddy bear
RGAN01 4.9900 Raggedy Ann
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy
DESC 字只 应用到直接位于其前面的列名。
DESC DESCENDING 写, 两个关 字都可以使用。与 DESC 相对 的是 ASC ASCENDING ),在升序排序 可以指定它。但 实际 上, ASC没有多大用 ,因 升序是默 的(如果既不指定 ASC 也不指定 DESC, 假定 ASC )。

四.过滤数据

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;
prod_name prod_price
------------------- ----------
Fish bean bag toy 3.49
Bird bean bag toy 3.49
Rabbit bean bag toy 3.49

where子句操作符

确定 是否 NULL ,不能 简单 检查 是否 = NULL SELECT 句有一个 特殊的WHERE 子句,可用来 检查 具有 NULL 的列。 WHERE 子句就 是IS NULL 子句。其 法如下:
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

五.高级数据过滤

and操作符(用在WHERE子句中的关字,用来指示足所有定条件的行

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
prod_id prod_price prod_name
------- ---------- --------------------
BNBG02 3.4900 Bird bean bag toy
BNBG01 3.4900 Fish bean bag toy
BNBG03 3.4900 Rabbit bean bag toy

or操作符(WHERE子句中使用的关字,用来表示索匹配任一定条件的行

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’;
prod_name prod_price
------------------- ----------
Fish bean bag toy 3.4900
Bird bean bag toy 3.4900
Rabbit bean bag toy 3.4900
8 inch teddy bear 5.9900
12 inch teddy bear 8.9900
18 inch teddy bear 11.9900
Raggedy Ann 4.9900

提示:and操作符比or操作符优先级高,可以使用括号操作符行明确分

in操作符(WHERE子句中用来指定要匹配的清的关字,功能与OR相当)

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;
prod_name prod_price
------------------- ----------
12 inch teddy bear 8.9900
18 inch teddy bear 11.9900
8 inch teddy bear 5.9900
Bird bean bag toy 3.4900
Fish bean bag toy 3.4900
Rabbit bean bag toy 3.4900
Raggedy Ann 4.9900

not操作符(WHERE子句中用来否定其后条件的关

SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;
prod_name
------------------
12 inch teddy bear
18 inch teddy bear
8 inch teddy bear
King doll
Queen doll

六.用通配符进行过滤

like操作符

百分号(%)通配符(%表示任何字符出任意次数

SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE 'Fish%';
prod_id prod_name 
------- ------------------ 
BNBG01 Fish bean bag toy
条子句 ,将 检索任意以Fish起头的词。%告诉 DBMS接受Fish之后的任意字符,不管它有多少字符。
SELECT prod_id, prod_name 
FROM Products 
WHERE prod_name LIKE '%bean bag%';
prod_id prod_name
-------- -------------------- 
BNBG01 Fish bean bag toy 
BNBG02 Bird bean bag toy 
BNBG03 Rabbit bean bag toy
'%bean bag%' 表示匹配任何位置上包含文本 bean bag 值, 不论 它之前或之后出 什么字符。
下划线(_)通配符
下划 线 的用途与 % ,但它只匹配 单个字符,而不是多个字符。
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';
prod_id prod_name
-------- --------------------
BR02 12 inch teddy bear
BR03 18 inch teddy bear

方括号([])通配符

方括号([])通配符用来指定一个字符集,它必 匹配指定位置(通配符的位置)的一个字符。
例如,找出所有名字以 J M 系人,可 行如下 查询
SELECT custcontact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
cust_contact
-----------------
Jim Jones
John Smith
Michelle Green

七.创建计算字段

在MYSQL中,拼接两个字段

SELECT Concat(vend_name, ' (', vend_country, ')')
FROM Vendors
ORDER BY vend_name;
-----------------------------------------------------------
Bear Emporium (USA )
Bears R Us (USA )
Doll House Inc. (USA )
Fun and Games (England )
Furball Inc. (USA )
Jouets et ours (France )
上面两个 SELECT 句拼接以下元素:
vend_name 列中的名字;
包含一个空格和一个左 括号的字符串;
vend_country 列中的国家;
包含一个右 括号的字符串。
可以使用RTRIM()函数去掉空格
SELECT Concat(RTRIM(vend_name), ' (',RTRIM(vend_country), ')')
FROM Vendors
ORDER BY vend_name;

使用别名

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

执行算术计算

SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num = 20008;
prod_id quantity item_price
---------- ----------- ---------------------
RGAN01 5 4.9900
BR03 5 11.9900
BNBG01 10 3.4900
BNBG02 10 3.4900
BNBG03 10 3.4900
如下 汇总 物品的价格(单价乘以 订购 数量):
SELECT prod_id,
 quantity,
 item_price,
 quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;
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算术操作符

八.使用函数处理数据

文本处理函数

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
vend_name vend_name_upcase
--------------------------- ----------------------------
Bear Emporium BEAR EMPORIUM
Bears R Us BEARS R US
Doll House Inc. DOLL HOUSE INC.
Fun and Games FUN AND GAMES
Furball Inc. FURBALL INC.
Jouets et ours JOUETS ET OURS
UPPER() 将文本 转换为 大写
常用的文本处理函数
SOUNDEX 是一个将任何文本串转换为 描述其 音表示的字母数字模式的算法
日期和时间处理函数
MYSQL可以使用YEAR()函数从日期中提取年份,还可以使用MONTH(),DAY()等函数
数值处理函数

九.汇总数据

SQL聚集函数

AVG()函数(过对表中行数数并算其列之和,求得列的平均

SELECT AVG(prod_price) AS avg_price
FROM Products;
avg_price
-------------
6.823333

说明:AVG()函数忽略列值为NULL的行

COUNT()函数(COUNT()函数

COUNT() 函数有两种使用方式:
使用 COUNT(*) 表中行的数目 数,不管表列中包含的是空值
NULL 是非空
使用 COUNT(column) 特定列中具有 的行 数,忽略 NULL
SELECT COUNT(*) AS num_cust
FROM Customers;
num_cust
--------
5
SELECT COUNT(cust_email) AS num_cust
FROM Customers;
num_cust
--------
3

MAX()函数(返回指定列中的最大

SELECT MAX(prod_price) AS max_price
FROM Products;
max_price
----------
11.9900

提示:对非数数据使用 MAX() 虽然 MAX()一般用来找出最大的数或日期,但多(并非所有)DBMS允将它用来返回任意列中的最大,包括返回文本列中的最 大值。在用于文本数据MAX()返回按列排序后的最后一行。  MAX()函数忽略列值为NULL的行。

MIN()函数(返回指定列的最小值)

SELECT MIN(prod_price) AS min_price
FROM Products;
min_price
----------
3.4900

提示:对非数数据使用 MAX() 虽然 MAX()一般用来找出最小的数或日期,但多(并非所有)DBMS允将它用来返回任意列中的最小,包括返回文本列中的最小值。在用于文本数据MAX()返回按列排序后的最前面的行。  MAX()函数忽略列值为NULL的行。

SUM()函数(用来返回指定列的和

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;
items_ordered
----------
200
明: NULL
SUM() 函数忽略列 值为 NULL 的行。
聚集不同值
以上5个聚集函数都可以如下使用
所有行 算,指定 ALL 参数或不指定参数(因 ALL 是默 行为)。
只包含不同的 ,指定 DISTINCT 参数。
下面的例子使用 AVG() 函数返回特定供 商提供的 品的平均价格。它
与上面的 SELECT 句相同,但使用了 DISTINCT 参数,因此平均 只考
各个不同的价格:
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
avg_price
-----------
4.2400
可以看到,在使用了 DISTINCT 后,此例子中的 avg_price 高,因 为有多个物品具有相同的 低价格。排除它 提升了平均价格。
注意: DISTINCT 不能用于 COUNT(*) 如果指定列名,则 DISTINCT 只能用于 COUNT() DISTINCT 不能用 于COUNT(*) 似地, DISTINCT 使用列名,不能用于 算或表
达式。
组合聚集函数
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;
num_items price_min price_max price_avg
---------- --------------- --------------- ---------
9 3.4900 11.9900 6.823333

十.分组数据

创建分组

是使用 SELECT 句的 GROUPBY 子句建立的。理解分 的最好 法 是看一个例子:
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
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 子句指示 DBMS 数据,然后 每个 而不是整个结 果集 行聚集。
除聚集 句外, SELECT 句中的每一列都必 GROUPBY 子句
出。
如果分 列中包含具有 NULL 的行, NULL 将作 一个分 返回。
如果列中有多行 NULL ,它 将分
GROUP BY 子句必 WHERE 子句之后, ORDER BY 子句之前。
过滤分组
SQL 此提供了HAVING子句。 HAVING 非常 似于 WHERE 。事 上,目前 止所学 的所有类 型的 WHERE 子句都可以用 HAVING 来替代。唯一的差 是, WHERE 过滤行,而 HAVING 过滤
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
cust_id orders
---------- -----------
1000000001 2
明: HAVING WHERE 的差 别 这里有另一种理解方法, WHERE 在数据分 过滤 HAVING 在数 据分组 过滤 是一个重要的区 WHERE 排除的行不包括在
中。 可能会改 变计 ,从而影响 HAVING 子句中基于 值过滤掉的分
分组和排序
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
要按 订购 物品的数目排序 出,需要添加 ORDER BY 子句,如下所示:
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
order_num items
--------- -----
20006 3
20009 3
20007 5
20008 5

SELECT子句顺序

十一.使用子查询

在,假如需要列出 订购 物品 RGAN01 的所有 客, 应该 样检 索?下
面列出具体的步
(1) 索包含物品 RGAN01 的所有 订单 号。
(2) 索具有前一步 列出的 订单编 号的所有 客的 ID
(3) 索前一步 返回的所有 ID 客信息。
上述每个步 都可以 独作 一个 查询 行。可以把一条 SELECT
句返回的 果用于另一条 SELECT 句的 WHERE 子句。
也可以使用子 查询 来把 3 查询组 合成一条 句。
第一条 SELECT 句的含 很明确,它 prod_id RGAN01 的所有
物品, 索其 order_num 列。 出列出了两个包含此物品的 订单
SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';
order_num
-----------
20007
20008
在,我 知道了哪个 订单 包含要 索的物品,下一步 查询 订单 20007
20008 相关的 ID 写如下的SELECT 句:
SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);
在, 两个 查询 ,把第一个 查询 (返回 订单 号的那一个) 变为
查询 看下面的 SELECT 句:
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
 FROM OrderItems
 WHERE prod_id = 'RGAN01');
输出结果:
cust_id
----------
1000000004
1000000005

在得到了 订购 物品 RGAN01 的所有 客的 ID 。下一步是
ID 客信息。 索两列的 SQL
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN ('1000000004','1000000005');
可以把其中的 WHERE 子句 转换为 查询 ,而不是硬 编码这 ID
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'));

输出:

cust_name cust_contact
----------------------------- --------------------
Fun4All Denise L. Stephens
The Toy Store Kim Howard

十二.联结表

创建联结

联结 非常 简单 ,指定要 联结 的所有表以及关 的方式即可
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
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匹配起来。

笛卡儿积(cartesian product)
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目
将是第一个表中的行数乘以第二个表中的行数。

内联结

上面使用的联结称为等值连接,它基于两个表之间的相等测试。联结也称联结inner join)。其实,可以对这联结使用稍微不同的语法,明确指定联结型。下面的 SELECT句返回与前面例子完全相同的数据:

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

 

句中的 SELECT 与前面的 SELECT 句相同,但 FROM 子句不同。
里,两个表之 的关系是以 INNERJOIN 指定的部分 FROM 子句。在使用
联结 条件用特定的 ON 子句而不是 WHERE 子句 出。 传递
ON 实际 条件与 传递给 WHERE 的相同。
联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
prod_name vend_name prod_price quantity
--------------- ------------- ---------- --------
18 inch teddy bear Bears R Us 11.9900 50
Fish bean bag toy Doll House Inc. 3.4900 100
Bird bean bag toy Doll House Inc. 3.4900 100
Rabbit bean bag toy Doll House Inc. 3.4900 100
Raggedy Ann Doll House Inc. 4.9900 50

十三.创建高级联结

使用表别名

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 cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
 FROM Customers
 WHERE cust_contact = 'Jim Jones');
是第一种解决方案,使用了子 查询 。内部的 SELECT 句做了一个
单检 索,返回 Jim Jones 工作公司的 cust_name 名字用于外部 查询
WHERE 子句中,以 索出 为该 公司工作的所有雇
在来看使用 联结 的相同 查询
SELECT c1.cust_id, c1.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';

cust_id cust_name cust_contact
-------- -------------- --------------
1000000003 Fun4All Jim Jones
1000000004 Fun4All Denise L. Stephens
提示:用自 联结 而不用子 查询
联结 通常作 外部 句,用来替代从相同表中 索数据的使用子
询语 句。 然最 果是相同的,但 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 Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
cust_id order_num
---------- ---------
1000000001 20005
1000000001 20009
1000000002 NULL
1000000003 20006
1000000004 20007
1000000005 20008
似上一 提到的内 联结 SELECT 句使用了关 OUTERJOIN 来指定联结类 型(而不是在 WHERE 子句中指定)。但是,与内 联结 联 两个表中的行不同的是,外联结还 包括没有关 行的行。在使用 OUTER JOIN语 ,必 使用 RIGHT LEFT 字指定包括其所有行的表
RIGHT 指出的是 OUTERJOIN 的表,而 LEFT 指出的是 OUTERJOIN 左边 的表)。上面的例子使用 LEFT OUTER JOIN FROM 子句左 的表 (Customers 表)中 选择 所有行。

存在另一种外 联结 ,就是全外 联结 full outer join ),它 索两个表中
的所有行并关 那些可以关 的行。与左外 联结 或右外 联结 包含一个表
的不关 的行不同,全外 联结 包含两个表的不关 的行。全外 联结
法如下
SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers 
ON Orders.cust_id = Customers.cust_id;

十四.组合查询

组合查询

SQL 也允 许执 行多个 查询 (多条 SELECT 句),并将 果作
查询结 果集返回。 查询 通常称 并( union )或复合 查询
compound query )。
主要有两种情况需要使用 查询
在一个 查询 中从不同的表返回 构数据;
一个表 行多个 查询 ,按一个 查询 返回数据。
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');
cust_name cust_contact cust_email
----------- ------------- ------------
Village Toys John Smith sales@villagetoys.com
Fun4All Jim Jones jjones@fun4all.com
The Toy Store Kim Howard NULL

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
cust_name cust_contact cust_email
----------- ------------- ------------
Fun4All Jim Jones jjones@fun4all.com
Fun4All Denise L. Stephens dstephens@fun4all.com
两条 句,可以如下 行:
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';
cust_name cust_contact cust_email
----------- ----------- ----------------
Fun4All Denise L. Stephens dstephens@fun4all.com
Fun4All Jim Jones jjones@fun4all.com
Village Toys John Smith sales@villagetoys.com
The Toy Store Kim Howard NULL
UNION 查询结 果集中自 去除了重复的行; 话说 ,它的行 与一
SELECT 句中使用多个 WHERE 子句条件一
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';
cust_name cust_contact cust_email
----------- ------------- ------------
Village Toys John Smith sales@villagetoys.com
Fun4All Jim Jones jjones@fun4all.com
The Toy Store Kim Howard NULL
Fun4All Jim Jones jjones@fun4all.com
Fun4All Denise L. Stephens dstephens@fun4all.com

十五.数据插入

数据插入

INSERT 用来将行插入(或添加)到数据 表。插入有几种方式:
插入完整的行;
插入行的一部分;
插入某些 查询 果。
插入完整的行
INSERT INTO Customers
VALUES('1000000006',
 'Toy Land',
 '123 Any Street',
 'New York',
 'NY',
 '11111',
 'USA',
 NULL,
 NULL);
到表中每一列的数
据在 VALUES 子句中 出,必 须给 每一列提供一个 。如果某列没有
如上面的 cust_contact cust_email 列, 则应该 使用 NULL (假定
表允 许对该 列指定空 )。各列必 以它 在表定 中出 的次序填充。
法很 简单 ,但并不安全, 应该 尽量避免使用。上面的 SQL 语 句高度依赖 于表中列的定 次序, 于其容易 得的次序信息。即 使可以得到这 种次序信息,也不能保 各列在下一次表 变动 后保持 完全相同的次序。因此,编写依 于特定列次序的 SQL 句是很不安全
的, 这样 早会出 问题
INSERT 句的更安全(不 烦琐 )的方法如下:
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);

插入部分行

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')

插入检索出的数据

假如想把另一表中的 客列合并到 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;

从一个表复制到另一个表

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

十六.更新和删除数据

更新数据

基本的 UPDATE 句由三部分组 成,分 是:
要更新的表;
列名和它 的新
确定要更新哪些行的 过滤 条件。
UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';
UPDATE 是以要更新的表名开始。在 个例子中,要更新的表名
Customers SET 命令用来将新 值赋给 被更新的列。在 里, SET 子句
cust_email 指定的
SET cust_email = 'kim@thetoystore.com
更新多个列的 法稍有不同:
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';

删除数据

DELETE FROM Customers
WHERE cust_id = '1000000006';
个例子中,只 1000000006 。 如果省略WHERE 子句,它将 除表中每个 客。
明: 除表的内容而不是表 DELETE语 句从表中 除行,甚至是 除表中所有行。但是, DELETE 不删 除表本身。

十七.创建和操作表

创建表

一般有两种 建表的方法:
多数 DBMS 都具有交互式 建和管理数据 表的工具;
表也可以直接用 SQL 句操
用程序 建表,可以使用 SQL CREATETABLE 句。需要注意的是,使
用交互式工具 时实际 上就是使用 SQL 句。 句不是用 户编 写的,界
面工具会自 生成并 行相 SQL 句(更改已有的表 也是 这样 )。

利用 CREATE TABLE 建表,必 须给 出下列信息:
新表的名字,在关 CREATE TABLE 之后 出;
表列的名字和定 ,用逗号分隔;
有的 DBMS 要求指定表的位置。
下面的 SQL 建本 中所用的 Products 表:
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 的列也允 在插
入行 列的 。不允 NULL 的列不接受没有列 的行,
话说 ,在插入或更新行 列必
指定默认值
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()
更新表
以下是使用 ALTER TABLE时 需要考 的事情。
理想情况下,不要在表中包含数据 时对 行更新。 应该 在表的 设计过程中充分考 未来可能的需求,避免今后 表的 构做大改动
所有的 DBMS 都允 许给现 有的表增加列,不 过对 所增加列的数据 型 (以及NULL DEFAULT 的使用)有所限制。
DBMS 不允 许删 除或更改表中的列。
多数 DBMS 重新命名表中的列。
DBMS 限制 填有数据的列 行更改, 未填有数据的列几 乎没有限制。
使用 ALTER TABLE 更改表 构,必 须给 出下面的信息:
ALTER TABLE 之后 出要更改的表名( 表必 存在,否 将 出错 );
列出要做哪些更改。
Vendors 表增加一个名 vend_phone 的列,其数据
CHAR
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
更改或 除列、增加 束或增加 些操作也使用 似的 法(注意,
下面的例子并非 所有 DBMS 都有效):
ALTER TABLE Vendors
DROP COLUMN vend_phone;

删除表

除表( 除整个表而不是其内容)非常 简单 ,使用 DROP TABLE 句即可:
DROP TABLE CustCopy;

重命名表

RENAME oldname newname

十八.使用视图

视图

视图 是虚 的表。与包含数据的表不一 视图 只包含使用 时动态检 索 数据的查询
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';
在,假如可以把整个 查询 包装成一个名 ProductCustomers 的虚
表, 可以如下 松地 索出相同的数据:
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

为什么使用SQL

重用 SQL 句。
化复 SQL 操作。在 查询 后,可以方便地重用它而不必知道 其基本查询细节
使用表的一部分而不是整个表。
数据。可以授予用 户访问 表的特定部分的 限,而不是整个表的访问权限。
更改数据格式和表示。 视图 可返回与底 表的表示和格式不同的数据。
视图 之后,可以用与表基本相同的方式使用它 。可以 对视图执
SELECT 操作, 过滤 和排序数据,将 视图联结 到其他 视图 或表,甚至添加
和更新数据
视图的规则和限制
与表一 视图 唯一命名(不能 给视图 取与 视图 或表相同的名字)。
于可以 建的 视图 数目没有限制。
视图 ,必 具有足 访问权 限。 限通常由数据 管理人 员授予。
视图 可以嵌套,即可以利用从其他 视图 索数据的 查询 来构造 视图。所允 的嵌套 数在不同的 DBMS 中有所不同(嵌套 视图 可能会严重降低 查询 的性能,因此在 境中使用之前, 应该对 行全面测试 )。
DBMS 禁止在 视图查询 中使用 ORDER BY 子句。
有些 DBMS 要求 返回的所有列 行命名,如果列是 算字段, 需要使用别 名(关于列 名的更多信息, 7 )。
视图 不能索引,也不能有关 的触 器或默 认值
有些 DBMS 视图 查询 表示可以从 视图检 索数据,但不能将数据写回底层 表。 具体的 DBMS 文档。
有些 DBMS 许创 这样 视图 ,它不能 致行不再属于 视图 的插入或更新。例如有一个视图 ,只 件地址的 客。如果更新某个顾 客, 除他的 件地址,将使 该顾 客不再属于 视图 这是默认 ,而且是允 的,但有的 DBMS 可能会防止 种情况 生。
创建视图
创建视图用CREATE VIEW,删除视图用 DROP VIEW

十九.管理事务处理

在使用事 务处 ,有几个反复出 的关 键词 。下面是关于事 务处 理需要知道的几个术语
transaction )指一 SQL 句;
回退( rollback )指撤 指定 SQL 句的 程;
提交( commit )指将未存 SQL 果写入数据 表;
保留点( savepoint )指事 务处 理中 置的 临时 占位符( placeholder ),可以对 布回退(与回退整个事 务处 理不同)。
有的 DBMS 要求明确 标识 务处 的开始和 束。如在 SQL Server 中,标识如下:
BEGIN TRANSACTION
...
COMMIT TRANSACTION
MariaDB MySQL 中等同的代 码为
START TRANSACTION
...
Oracle 使用的 法:
SET TRANSACTION
...

使用ROLLBACK

SQL ROLLBACK 命令用来回退(撤 SQL 句, 看下面的 句:
DELETE FROM Orders;
ROLLBACK;

使用COMMIT

一般的 SQL 句都是 针对 数据 表直接 行和 写的。 就是所 隐 式提交(implicit commit ),即提交(写或保存)操作是自 动进 行的。 在事务处 中,提交不会 行。不 ,不同 DBMS 的做法有所不同。有的 DBMS 式提交 理事 端,有的 这样
行明确的提交,使用 COMMIT 句。下面是一个 SQL Server 的例子:
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

使用保留点

使用 简单 ROLLBACK COMMIT 句,就可以写入或撤 整个事 。但是,只对简单 的事 才能 这样 做,复 的事 可能需要部分提交或回退。
要支持回退部分事 ,必 在事 务处 中的合适位置放置占位符。 这样,如果需要回退,可以回退到某个占位符。
SQL 中, 些占位符称 保留点。在 MariaDB MySQL Oracle 中创建占位符,可使用SAVEPOINT 句。
SAVEPOINT delete1;
每个保留点都要取能 够标识 它的唯一名字,以便在回退 DBMS 知道
回退到何 。要回退到本例 给出的保留点,在 MariaDB MySQL Oracle 中,如下 行:
ROLLBACK TO delete1;

文章用到的表

Vendors表

Products表

Customers表

Orders表

OrderItems表

  • 31
    点赞
  • 22
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值