SQL必知必会(三) 联结表、创建高级联结、组合查询

十二、联结表

关系表

关系表的设计就是要把信息分解成多个表，一类数据一个表，各表通过某些共同的值互相关联。

这本书的数据库分成5个，5个表之间通过m某些id连接起来。

创建联结

SELECT vend_name , prod_name ,prod_priceFROM Vendors , ProductsWHERE Vendors.vend_id = Products.vend_id;SELECT vend_name , prod_name ,prod_priceFROM Vendors , ProductsWHERE vend_id = vend_id;-- 输入这个会返回笛卡尔积的联结，也称叉联结

等价于如下语法：

SELECT vend_name , prod_name ,prod_priceFROM Vendors INNER JOIN ProductsON Vendors.vend_id = Products.vend_id;

联结多个表

SELECT prod_name, vend_name, prod_name, prod_price, quantityFROM OrderItems, Vendors, ProductsWHERE Vendors.vend_id = Products.vend_idAND OrderItems.prod_id = Products.prod_idAND order_num = 20007;

十三、创建高级联结

使用表别名

SELECT cust_name, cust_contactFROM Customers AS C, Orders AS O, OrderItems AS OIWHERE C.cust_id = O.cust_idAND OI.order_num = O.order_numAND prod_id = 'RGAN01';

使用不同类型的联结

自联结

SELECT cust_id, cust_name. cust_contactFROM CustomersWHERE cust_name = (SELECT cust_name                    FROM Customers                    WHERE cust_contact = 'JIM JONES');

等价于：

SELECT c1.cust_id, ci,cust_name, c1.cust_contactFROM Customers AS c1, Customers AS c2WHERE c1.cust_name = c2.cust_nameAND c2.cust_contact = 'JIM JONES';

外联结

SELECT Customers.cust_id, Orders.order_numFROM Customers LEFT OUTER JOIN OrdersON Customers.cust_id = Orders.cust_id;  --LEFT指定左边所有的行（右边为NULL也会列出） 

RIGHT OUTER JOIN 、 FULL OUTER JOIN 分别表示右外联结、全联结

使用带聚类函数的联结

SELECT Customers.cust_id,        COUNT(Orders.order_num) AS num_ordFROM Customers INNER JOIN OrdersON Customers.cust_id = Orders.cust_idGROUP BY Customers.cust_id;

十四、组合查询

SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI');SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4A11';

组合后：

SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI');UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4A11';

等价于WHERE语句：

SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI')OR cust_name = 'Fun4A11';

对组合查询结果进行排序

SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN ('IL','IN','MI');UNIONSELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_name = 'Fun4A11'ORDER BY cust_name, cust_contact;

UNION注意事项：

1. UNION必须由两条或以上的SELECT语句组合而成
2. UNION中的每个查询必须包含相同的列、表达式或聚类函数（次序没有要求）
3. 列数据必须兼容