数据库实际查询操作回顾（六)

回顾之前学习数据库的相关操作，复习时顺便记录下，以便以后自己可以再次查看！！！

/*

联结表

*/

(1)select vend_name,prod_name,prod_price
    from vendors,products
    where vendors.vend_id = products.vend_id
 order by vend_name,prod_name;//返回了当vendors的vend_id和表products的vend_id相等时，vend_name,prod_name,prod_price

(2)select vend_name, prod_name, prod_price
    from vendors, products
    order by vend_name, prod_name;//由于没有联结条件的表的关系返回的是笛卡儿积，所以这次查询返回的是笛卡儿积

/*
注意：
应该保证所有联结都有WHERE子句，否 则MySQL将返回比想要的数据多得多的数据。
同理，应该保 证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回 不正确的数据。
 
 */

(3) select vend_name,prod_name,prod_price
     from vendors inner join products
     on vendors.vend_id = products.vend_id
     order by vend_name,prod_name;//返回的结果和第一个查询一样,传递给ON的实际条件与传递给WHERE的相同。在使用这种语法时，联结条件用特定的ON子句而不是WHERE 子句给出
 

(4) 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;//返回了编号为20005的订单中的物品.订单物品存储在 orderitems表中。每个产品按其产品ID存储，它引用products 表中的产品。这些产品通过供应商ID联结到vendors表中相应的供应商， 供应商ID存储在每个产品的记录中。这里的FROM子句列出了3个表，而 WHERE子句定义了这两个联结条件，而第三个联结条件用来过滤出订单 20005中的物品。
 

(5)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';//返回订购物品TNT2的所有客户的ID的客户信息
 

/*

创建高级联结
    
*/

(6)select cust_name, cust_name
    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';//使用了别名

(7)select prod_id, prod_name
    from products
    where vend_id = (select vend_id
                                 from products
                                 where prod_id = 'DTNTR');//使用子查询，先返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生 产的所有物品
 

(8)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';//使用了表别名。products的第一次出现为别名p1， 第二次出现为别名p2。现在可以将这些别名用作表名。例如，SELECT语 句使用p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错 误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想 要的是哪一个列（即使它们事实上是同一个列）。WHERE（通过匹配p1中 的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的 prod_id过滤数据，返回所需的数据。
 

(9)select customers.cust_id,orders.order_num
    from customers left outer join orders
    on customers.cust_id= orders.cust_id;//，使用了左查询，检索所有客户，包括那些没有订单的客户

(10)select customers.cust_id,orders.order_num
    from customers inner join orders
    on customers.cust_id= orders.cust_id;//它检索所有客户及其订单,而没有订单的客户不会显示出来

/*
注意：
在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字 指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT 指出的是OUTER JOIN左边的表）。
上面的例子使用LEFT OUTER JOIN从FROM 子句的左边表（customers表）中选择所有行。为了从右边的表中选择所 有行，应该使用RIGHT OUTER JOIN。
*/

(11)select customers.cust_id,orders.order_num
    from customers right outer join orders
    on customers.cust_id= orders.cust_id;//使用了右联结，结果和上面查询一样。它检索所有客户及其订单,而没有订单的客户不会显示出来

(12)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;//返回了所有客户及每个客户所下的订单数，使用了count函数

(13)select customers.cust_name,
               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;//返回了所有客户及每个客户所下的订单数，使用了左联结，所有也返回了没有订单的客户

/*
总结下使用联结和联结条件 ：

 注意所使用的联结类型。一般我们使用内部联结，但使用外部联 结也是有效的。 
   保证使用正确的联结条件，否则将返回不正确的数据。 
   应该总是提供联结条件，否则会得出笛卡儿积。 
   在一个联结中可以包含多个表，甚至对于每个联结可以采用不同 的联结类型。虽然这样做是合法的，一般也很有用，但应该在一 起测试它们前，分别测试每个联结。这将使故障排除更为简单。


*/

/*

组合查询

*/

(14)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);//第一条SELECT检索价格不高于5的所有物品。第二条SELECT使 用IN找出供应商1001和1002生产的所有物品，而且UNION从查询结果集中自动去除了重复的行
 

/*
 UNION规则 ：
 1. UNION必须由两条或两条以上的SELECT语句组成，语句之间用关 键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个 UNION关键字） 。
 2. UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
 3.列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以 隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
 
 */

(15) select vend_id, prod_id, prod_price
         from products
         where prod_price <= 5
         union all
         select vend_id, prod_id, prod_price
         from products
 where vend_id in (1001,1002);//使用UNION ALL，MySQL不取消重复的行。因此这里的例子返 回9行，其中有一行出现两次。
 

 /*
 UNION与WHERE
 
 UNION几乎总是完成与多个 WHERE条件相同的工作。UNION ALL为UNION的一种形式，它完成 WHERE子句完成不了的工作。如果确实需要每个条件的匹配行全 部出现（包括重复行），则必须使用UNION ALL而不是WHERE。
 
 */

(16)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)
    order by vend_id, prod_price;//order by 在select子句后面，用来排序所有select语句返回的结果

数据库实际查询操作之相关数据库代码（MySQL必知必会数据库代码）

数据库实际查询操作回顾（八）

数据库实际查询操作回顾（七）

数据库实际查询操作回顾（五)

数据库实际查询操作回顾（四）

数据库实际查询操作回顾(三）

数据库实际查询操作回顾(二）

数据库实际查询操作回顾（一）