十二、联结表
关系表
关系表的设计就是要把信息分解成多个表,一类数据一个表,各表通过某些共同的值互相关联。
这本书的数据库分成5个,5个表之间通过m某些id连接起来。
创建联结
SELECT vend_name , prod_name ,prod_price FROM Vendors , Products WHERE Vendors.vend_id = Products.vend_id;
SELECT vend_name , prod_name ,prod_price FROM Vendors , Products WHERE vend_id = vend_id;
-- 输入这个会返回笛卡尔积的联结,也称叉联结
等价于如下语法:
SELECT vend_name , prod_name ,prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
联结多个表
SELECT prod_name, vend_name, prod_name, prod_price, quantity FROM OrderItems, Vendors, Products WHERE Vendors.vend_id = Products.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;
十三、创建高级联结
使用表别名
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';
使用不同类型的联结
自联结
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, ci,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';
外联结
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id; --LEFT指定左边所有的行(右边为NULL也会列出)
RIGHT OUTER JOIN 、 FULL OUTER JOIN 分别表示右外联结、全联结
使用带聚类函数的联结
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;
十四、组合查询
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI');
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_name = 'Fun4A11';
组合后:
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 = 'Fun4A11';
等价于WHERE语句:
SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL','IN','MI') OR cust_name = 'Fun4A11';
对组合查询结果进行排序
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 = 'Fun4A11' ORDER BY cust_name, cust_contact;
UNION注意事项:
- UNION必须由两条或以上的SELECT语句组合而成
- UNION中的每个查询必须包含相同的列、表达式或聚类函数(次序没有要求)
- 列数据必须兼容