连接表和创建高级连接
可伸缩性(scale)
能够适应不断增加的工作量而不失败.设计良好的数据库或应用程序可称之为”可伸缩性好”.
笛卡尔积(cartesian product)
没有连接条件的表关系返回的结果.
比如: SELECT vend_name,pro_name,pro_name FROM Vendors,Products;
内部连接
INNER JOIN…ON
SELECT vend_name,pro_name,pro_price
FROM Venders AS V
INNER JOIN Products AS P ON V.vend_id = P.vend_id;
提示:联结的表越多,性能就下降得越厉害.SQL中对联结表的数目没有限制,但DBMS中却有.内部联结也称”等值联结”.
注意:Oracle中起别名不需要”AS”,直接写就行.
自连接
通常作为外部语句来替代从相同表中检索数据的子查询语句.
自然连接 (SELECT *)
一般连接可能会出现相同的列,尽管这些列分属于不同的表,但其实它们的名称和意义是一样的.
cust_id | order_num | cust_id | pro_id |
0128 | 100123 | 0128 | 0013 |
0129 | 112850 | 0129 | 0041 |
0330 | 145896 | 0330 | 0029 |
SELECT C.*,O.order_num,O.order_date,OI.pro_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 pro_id = "RGA001";
事实上,迄今为止,我们用到的”内部联结”都是”自然联结”.
外部连结
LEFT/RIGHT/FULL OUTER JOIN…ON
用于 许多联结将一个表中的行与另一个表中的行相关联,但有时候会需要包含没有关系行的那些行.
※ 计算平均销售规模,且包括那些至今未下订单的客户.
※ 对每个客户下了多少订单进行计数,且包括那些至今未下订单的客户.
左/右外联结
SELECT C.cust_id,O.order_num
FROM Customers AS C LEFT OUTER JOIN Orders AS O
ON C.cust_id = O.cust_id;
cust_id | order_num |
0111 | 2005 |
0112 | 2009 |
0113 | NULL |
0114 | 2006 |
0115 | 2008 |
在Order中表,根本就不存在未下订单的cust_id,所以普通的联结根本得不到这样的结果,这就体现了外联结的好处.
可以看到,”左联结”就是从写在FROM子句的左边的表中选择所有的行.
也可以用符号表示:
SQL Server : “ *= ” :左联结 “ =* “ :右联结
Oracel : “ (+)= ” :左联结 “ =(+) “ :右联结
-- 右联结 结果等同于上例
SELECT C.cust_id,O.order_num
FROM Customers AS C , Orders AS O
ON O.cust_id =* C.cust_id;
带聚集函数的外部联结
SELECT C.cust_id,COUNT(O.order_num) AS num_id
FROM Customers AS C INNER JOIN Orders AS O
ON C.cust_id = O.cust_id
GROUP BY C.cust_id;
cust_id | num_id |
0111 | 2 |
0113 | 1 |
0114 | 1 |
0115 | 1 |
SELECT C.cust_id,COUNT(O.order_num) AS num_id
FROM Customers AS C LEFT OUTER JOIN Orders AS O
ON C.cust_id = O.cust_id
GROUP BY C.cust_id;
cust_id | num_id |
0111 | 2 |
0112 | 0 |
0113 | 1 |
0114 | 1 |
0115 | 1 |
提示:一般用内部联结,但外部联结也是有效的;
应总是提供联结条件,否则会出现笛卡尔积.
管理事务处理
事件处理(transaction processing)可以用来维护数据库的完整性,它保证成批的SQL操作要么完全执行,要么完全不执行.
事务处理的4个专业术语
※ 事件(transation)指一组SQL语句;
※ 回退(rollback)指撤销指定SQL语句的过程;
※ 提交(commit)指将未存储的SQL语句结果写入数据库表;
※ 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),你可以对它发布回退指令.
提示:不能回退SELECT语句,也不能回退CREATE或DROP操作.事务中可以使用它们,但不能撤消它们.
不同的事务处理的语法是不同的
SQL Server
BEGIN TRANSACTION
...
COMMIT TRANSACTION
MySQL
START TRANSACTION
...
PostgreSQL使用ANSI SQL语法
BEGIN;
...
一般SQL语句都是直接针对数据库表执行和编写的,就是所谓的隐含提交(implicit commit),即提交(写或保存)操作是自动进行的.但是在事务处理中,不会隐含的提交.不过有的DBMS不这样.
SQL Server
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 123;
DELETE Orders WHERE order_num = 123;
COMMIT TRANSACTION
Oracle
DELETE OrderItems WHERE order_num = 123;
DELETE Orders WHERE order_num = 123;
COMMIT;
上例中,COMMIT语句仅在全部DELETE语句不出错时才发生.
为了支持回退部分事务处理,须在事务处理块中合适的位置放置占位符.
SQL Server
SAVE TRANSACTION delete1;
MySQL 和 Oracle
ROLLBACK TO delete1;
一个完整的例子:
BEGIN TRANSACTION
INSERT INTO Customes(cust_id,cust_name) VALUES('1001','Tom');
--设置保留点StartOrder,以便回退
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num,order_date,cust_id) VALUES(20100,' 2000/01/01 ','1001');
--SQL Server中,@@ERROR变量用来判断操作是否成功; 其它DBMS可能使用不同的变量
if @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num,order_item,quantity,price) VALUES(20100,1,'waterbox',5.49);
if @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION
--提示: 保留点越多越好