SQL必知必会学习笔记3
十三、创建高级联结
1.使用表别名
SELECT cust_name,cust_contact
FROM Customer AS C, Orders AS 0, OrderItems AS OI
WHERE C.cust_id = 0.cust_id
AND OI.order_num = 0.order_num
AND prod_id = 'RGAN01'
警告： Oracle中没有AS，直接指定Customer C。
2.使用不同类型的联结
迄今为止，我们使用的知识内联结或等值联结的简单联结。现在来看三种其他联结：自联结(self-join)、自然联结(natural join) 和 外联结(outer join)。
自联结
举例，加入要给JimJones同一公司的所有顾客发送一封信件。这个查询首要找出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 AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
尽量使用自联结，虽然最终结果是相同的，但许多DBMS处理联结远比处理子查询快的多。
自然联结(存疑)
使用标准的联结(内联结)返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次。怎么完成这个工作？答案是，系统不完成这项工作，有你自己完成它。自然联结要求你只能选择那些唯一的列，一般同步对一个表使用通配符(SELECT *), 而对其他表的列使用明确的子集来完成，下面举例：
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS 0, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
外联结
许多联结讲一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：
- 对于每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
- 列出所有产品以及订购数量，包括没有人订购的产品；
- 计算平均销售规模，包括那些至今尚未下订单的顾客。
上述例子中，联结包含了那些在相关表中没有关联的行。这种联结称为外联结。下面的SELECT语句给出了一个简单的内联结。它检索所有顾客及其订单：
SELECT Customers.cust_id，Order.order_num
FROM Customers INNER JOIN Orders
ON Customer.cust_id = Order.cust_id；
外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：
SELECT Customers.cust_id，Order.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customer.cust_id = Order.cust_id；
外联结还包含没有关联的行，使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包含其所有行的表(RIGHT支出的是OUTER JOIN右边的表，而LEFT支出的是OUTER JOIN左边的表)。还有一种全外联结，左右两边都会包含所有行。
SELECT Customers.cust_id，Order.order_num
FROM Orders FULL OUTER JOIN Customers
ON Customer.cust_id = Order.cust_id；
3.使用带聚集函数的联结
SELECT Customers.cust_id, COUNT(Order.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customer.cust_id = Order.cust_id；
GROUP BY Customer.cust_id;
4.使用联结和联结条件
联结及使用的要点:
- 注意所有的联结类型。一般我们使用内联结，但使用外联结也有效。
- 关于确切的联结语法，应该查看具体的文档,看相应的DBMS支持何种语法
- 保证使用正确的联结条件,否则会返回不正确的数据。
- 应该总是提供联结条件，否则 会得出笛卡尔积。
- 在一个联结中可以包含多个表，甚至可以对每个联结采用不同联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会故障排除更为简单。
十四、组合查询
1.组合查询
SQL也允许执行多个查询，并将结果作为一个查询结果集返回。这些组合查询通常称为并(union) 或复合查询(compound query)。主要有两种情况需要使用组合查询：
- 在一个查询中从不同的表返回结构数据；
- 对一个执行多个查询，按一个查询返回结果。
- 组合查询和多个WHERE条件具有相同的功能。
2.创建组合查询
使用UNION很简单，只需要给每条SELECT语句之间放上UNION。
进行组合时要注意几条规则
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。
- UNION中的每个查询必须包含相应的列、表达式或聚集函数。
- 列数据类型必须兼容：类型不必完全相同，必须是DBMS可以隐含切换的类型。
- UNION语句从查询结果中自动去除了重复的行。如果想返回所有的行，可以使用UNION ALL 而不是UNION。
对组合查询结果排序
在用UNION组合查询时，只能使用一条ORDER BY子句，必须位于一条SELECT语句之后。
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'
ORDER BY cust_name, cust_contact;
十五、插入数据
1.数据插入
INSERT用来进行将行插入到数据库。插入有集中方式：
- 插入完整的行；
- 插入行的一部分；
- 插入某些查询的结果
插入完整的行
使用基本的INSERT语法，他要求指定表明和插入到新行中的值。
INSERT INTO Customers
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
INTO关键字 跟在INSERT之后的INTO关键字是可选的。
这种方式很简单，但是并不安全，应该尽量避免使用过。编写INSERT语句的更安全的方法入下:
INSERT INTO Customers(cust_id, cust_name, cust_address,
cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000006',
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
可以只给部分列提供值，给其他列不提供值。其他列的值将被省略。省略的列必须满足以下。
- 该列定义为允许NULL值(无值或空值)。
- 在表定义中给出默认值。这表示如果不给出值，将使用默认值。
插入检索出的数据，即INSERT SELECT。由一条INSERT语句和一条SELECT语句组成的。
INSERT INTO Customer(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;
所有SELECT选出来的行都会被INSERT插入。
2.从一个表复制到另一个表
使用SELECT INTO将一个表的内容复制到一个全新的表。INSERT SELECT与SELECT INTO。他们之间的一个重要差别是前者导出数据，而后者导入数据。
SELECT *
INTO CustCopy
FROM Customers;
- 任何SELECT选项和字句都可以使用，包括WHERE和GROUP BY；
- 可以利用联结从多个表插入数据；
- 不管从多少个表中检索数据，数据都只能插入一个表中。
十六、更新和删除数据
介绍UPDATE和DELETE语句。
1.更新数据
使用UPDATE的方式：
- 更新表中的特定行
- 更新表中的所有行
- 不要省略WHERE语句，避免更新表中的所有行。
基本的UPDATE语句由三部分组成：
- 要更新的表
- 列名和他们的新值；
- 要确定更新哪些行的过滤条件。
UPDATE Customers
SET cust_email = ‘kim@thetoystore.com’
WHERE cust_id = '10000000005'
要删除某个列的值，可以设置它为NULL。
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '10000000005'；
2.删除数据
两种DELETE的使用方式：
- 从表中删除特定的行;
- 从表中删除所有的行。
DELETE FROM Customer
WHERE cust_id = '1000000006'
如果从表中国删除所有的行，不要使用DELETE，可使用TRUNCATE TABLE语句，它完成相同的工作，而且速度更快。
3.更新和删除的指导原则
- 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
- 保证每个表都有主键，尽可能像WHERE字句那样使用它。
- UPDATE或DELETE语句使用WHRER字句前，应该先用SELECT 进行测试，保证过滤是正确的。
- 使用强制实施引用完整性的数据，这样DBMS将不允许删除其数据与其他表相关联的行。
- 有的DBMS允许数据库管理员施加约束，防止不带WHERE的UPDATE个DELETE语句。
十七、创建和操纵表
1.创建表
创建表的两种方式：
- 多数DBMS都具有交互式创建和管理数据库的工具；
- 表可以直接用SQL语句操纵。
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值。
指定默认值
CREATE TABLE OrderItem
(order_num INTEGER NOT NULL,
order_item INTEGER NOT NULL,
prod_id CHAR(10) NOT NULL,
quantity INTEGER NOT NULL DEAFAULT 1,
item_price DECIMAL(8,2) NOT NULL);
2.更新表
使用ALTER TABLE时需要考虑的事
- 理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，便面今后对表的结构做大的改动
- 所有DBMS都允许给现在的表增加列，不过对所新增的列的数据类型有所限制。
- 许多DBMS不允许删除或更改表中的列。
- 多数DBMS允许重命名表中的列。
- 许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。
# 增加一列
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
# 删除一列
ALTER TABLE Vendors
DROP COLUMN vend_phone;
3.删除表
DROP TABLE CustCopy;
4.重命名表
不同的DBMS使用的语句不相同，SQL Server用户使用sp_rename存储过程。
