Mysql学习(二)
先回顾一下之前的内容:
- 检索数据
- 排序检索
- 过滤数据(WHERE)
- 通配符(LIKE)
- 正则表达式(REGEXP)
- 创建计算字段
- 数据处理函数
- 子查询
联结表
内部联结
场景:
假如有一个包含产品目录的数据库表,其中每种类别的物品占一行,这一行里有对物品的描述信息,如价格,类别,生产日期等等,以即 供应商信息
现在假如有同一供应商生产的多种物品,那么在何处存储供应商信息呢(供应商姓名,地址,联系方式等)?将这些供应商的信息与商品分开存储有以下几个理由:
- 减少存储空间
- 减少耦合性,修改商家信息时不用在每个商品后修改
在这个例子里可以创建两个表,一个存供应商信息,一个存商品信息。vendors表包含所有供应商信息,每个供应商占一行,有一个主键,products表则只存商品信息,它除了存储供应商的ID(vendors的主键)外不存储供应商的其他信息,通过主键可将两个表关联起来。
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
或者
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
结果如下
*注意:
如果漏掉了WHERE,则得到的结果为两个表做笛卡尔积*
同样的,可以联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
使用列表别名
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 = 'TNT2';
自联结
如果你发现某种商品(ID为DTNTR)存在问题,想要知道该供应商的其他物品是否也存在这样的问题,该查询要求先找ID为DTNTR的物品的供应商,再找出它的其他物品。如下
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
FROM products
WHERE prod_id = 'DTNTR');
也可以使用自联结
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';
外部联结
许多联结可以将一个表中的行与另一个表中的行联结,但有时要包含没有关联行的那些行,如
- 对每个客户下了多少订单进行计数,包括至今未下订单的客户
- 列出所有产品以及订购数量,包括没有人订购的商品
- 计算平均销售规模,包括至今未下单的客户
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;