数据库-SQL必知必会 笔记(补充...)

连接表和创建高级连接

可伸缩性(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

--提示: 保留点越多越好