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

本文介绍了SQL中联结表的基本概念与操作方法,包括如何通过ID将不同的表连接起来,实现多表联结查询,以及如何使用不同类型的联结如自联结和外联结。此外还讲解了如何进行组合查询及对结果进行排序。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

十二、联结表

关系表

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

这本书的数据库分成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注意事项:
  1. UNION必须由两条或以上的SELECT语句组合而成
  2. UNION中的每个查询必须包含相同的列、表达式或聚类函数(次序没有要求)
  3. 列数据必须兼容
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值