大型的系统中涉及的数据库表之间是存在关系的,如订单表的商品信息会与商品表产生关系,商品表中的供应商列会与供应商信息表产生关系,分开存储有助于数据的一致性和存储空间的节省。那么怎么保证关系的有效?怎么方便地联结多个表进行查询呢?就往下看吧~
1、外键
外键是表中的某一列,包含另一个表的主键值。vendors表的主键就可以作为products表的外键,将两张表相关联。
如果插入/更新products表中的vend_id不是另一个表的主键,非法数据插入/更新不成功。
2、where语句创建联结
例1:查找商品表中商品名称和相应供应商的名字等信息。
SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.`vend_id`=products.`vend_id`
例2:讲解子查询时有一个问题是列出订购物品TNT2的所有客户信息,这一问题也可以用多表联结来解决。
子查询:
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN(
SELECT cust_id
FROM orders
WHERE order_num IN(
SELECT order_num
FROM orderitems
WHERE prod_id='TNT2'
)
)
多表联结:
SELECT cust_name,cust_contact FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
AND prod_id='TNT2'
注意:实际使用时究竟子查询性能好还是联结好可以测试选择,与具体的数据量,索引设置等都有关系,不建议联结特别多的表。
3、笛卡儿积
如果例1中的where条件省略了将发生什么?
select vend_name,prod_name,prod_price from vendors,products
将返回第一个表的行数乘以第二个表的行数那么大的数据表,即两张表的结果两两组合的所有情况都会给出,是没有意义的。
4、内联结
内联结可以替代例1中的等值联结,而且更有助于展示联结关系,语法如下:
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON
5、其他类型的联结
(1)自联结
例3:查询生产id为DTNTR的供应商生成的其他商品,不同于子查询和前文讲的联结情况,这一问题只涉及一张表 products。
利用子查询的语法大家可以自行练习一下,这里重点说自联结的使用。
SELECT p1.prod_id,p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id='DTNTR'
可以看出为了对付一张表查询两次产生歧义,为表起了两个别名,明确返回需要的列。实际使用时自联结一般快于子查询,必要时要测试一下选择性能好的那个。
(2)外联结
有时我们不仅需要两个表的交集,还需要其中一个表中未被关联的行。这种类型的联结为外部联结。
例4:对每个客户下的订单进行检索,包括没有下单的客户。
SELECT customers.`cust_id`, orders.`order_num` FROM customers LEFT OUTER JOIN orders ON customers.`cust_id`=orders.`cust_id`
可以看出没有关联行的10002也被返回了,如果需要的是后一张表的全部内容,那么用right join,可以通过调整顺序互换。(INNER, OUTER是可以省略的,但是还是建议加上,防止出错)
(3)带聚集函数的联结
例5:检索所有客户及下的订单总数。
SELECT customers.`cust_id`, COUNT(orders.`order_num`) AS num_ord
FROM customers LEFT OUTER JOIN orders ON customers.`cust_id`=orders.`cust_id` GROUP BY customers.`cust_id`