mysql数据库高级篇（一）

mysql数据库高级篇

1.表联结

---

1.1子查询

• 条件字段进行子查询

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'));   

以上语句中从最里层的子查询orderitem表中获得商品订单号作为外层orders表查询的条件，又从里层子查询orders表中获得客户id作为外层customers的表的查询条件，即在where当中嵌套子查询select语句进行数据过滤

但是，使⽤子查询并不总是执行这种类型的数据检索的最有效的方法。不建议嵌套太多层。

• 作为计算字段使用子查询

select cust_id,cust_name,
(select count(*)
from orders
where orders.cust_id = customers.cust_id) as orders
)
from customers
order by cust_name;

此语句中orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对检索出的每个客户执行一次。在此例子中，该子查询执行了了5次，因为检索出了5个客户。

注意:子查询中的WHERE子句与前面使用的WHERE子句稍有不同，因为它使用了完全限定列名,这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。因为有两个cust_id列，一个在customers中，另一个在orders中，需要比较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限定列名，MySQL将假定你是对orders表中的cust_id进行自身比较.

1.2表联结

关联表：保证把信息分成多个表，一类数据一个表，各表通过某些常用值相互关联（主键、外键）。
联结是一种机制，在单条select语句中，要查询多个关联表中的信息，可以联结多个表返回一组输出，联结在运行是关联表中正确的行。

• where创建表联结

select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;

此条语句中，where子句将供应商表和产品表中的供应商id联结起来实现查询目的。这里需要注意使用的是完全限定列名。
在联结两个表时，你实际上做的是将第一个表中的每⼀行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。因此，应该保证所有联结都有WHERE子句句，否则MySQL将返回比想要的数据多得多的数据。

# 联结多个表
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;

• 使用join或inner join实现表联结(推荐使用)

select vend_name,prod_name,prod_price from vendors inner join products on
vendors.vend_id = products.vend_id;

两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。
SQL规范首选INNER JOIN语法。

1.3 自联结

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';

自联结就是将表与自己本身进行联结，以查询表自身的其他信息，当查询条件与要查询的信息均在一张表上的时候可以考虑使用。

但是要注意因为连接的是同一张表，因此名字存在二义性，需要使用表别名（如：p1、p2）解决这个问题。

用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。因为联结的效率比子查询更高。

1.4 外部联结

外部联结内部联结用法类似，但是当需要显示没有关联的行时，内部联结可能造成信息丢失，这时就要使用外部联结。联结同时还可以搭配聚合函数使用。

关键字：
左联结（left join），保留关键字左边的表的所有信息进行联结。
右联结（right join），保留关键字右边的表的所有信息进行联结

select customers.cust_name,customers.cust_id,count(orders.order_num) as
num_ord from customers left join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;