MySql学习（六）

使用子查询

子查询：即嵌套在其他查询中的查询

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

子查询一般与IN操作符结合使用，但也可以用于测试等于（=）、不等于（<>）等。

相关自查询：涉及外部查询的子查询

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

当列名有多义性时，应使用完全限定列名

联结

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

1.联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在

运行时关联表中正确的行。

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

vendors和products两个表用WHERE子句正确联结，WHERE子句指示MySQL匹配vendors表中的vend_id和products表中vend_id。

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

select vend_name,prod_name,prod_price
from vendors,products
order by vend_name,prod_name;

应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。

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的相同。

2.联结多个表（内部联结）

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,orders,orderitems
where customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

创建高级联结

创建表别名：缩短SQL语句，允许在单条select语句中多次使用相同的表

from customers AS c, orders AS o

除了内部联结外还有三种：自联结，自然联结，外部联结

1.自联结

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.自然联结

标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

3.外部联结

联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结

select customers.cust_id,orders.order_num
from customers left outer join orders
ON customers.cust_id = orders.cust_id;

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。

4.使用带聚集函数的联结

select customers.cust_name,
       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;

检索所有客户及每个客户所下的订单数

组合查询

有两种基本情况，其中需要使用组合查询：

  在单个查询中从不同的表返回类似结构的数据

  对单个表执行多个查询，按单个查询返回数据

使用UNION：在各条语句之间放上关键字UNION

select vend_id,prod_id,prod_price
from products
where prod_price <= 5
union
select vend_id,prod_id,prod_price
from products
where vend_id IN (1001,1002);

也可以使用Where子句完成相同工作

select vend_id,prod_id,prod_price
from products
where prod_price <= 5 
or vend_id IN (1001,1002);

UNION从查询结果集中自动去除了重复的行，可使用UNION ALL返回所有匹配行（可以完成Where不能完成的工作）

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后，排序所有的结果集