MySQL必知必会 Day3

第14章使用子查询

14.1 子查询
14.2 利用子查询进行过滤
(1)检索包含物品TNT2的所有订单的编号
(2)检索具有前一步骤列出的订单编号的所有客户的ID。
(3)检索前一步骤返回的所有客户ID的客户信息。

//可以拆分为3个查询
//1
SELECT order_name
FROM orderitems
WHERE prod_id = 'TNT2';
//2
SELECT cust_id
FROM orders
WHERE order_num IN (20005，20007);

//两个查询合并成有子查询的查询
SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_name
                    FROM orderitems
                    HERE prod_id = 'TNT2);
//子查询总是从内向外处理。先执行括号内语句。

//3
SELECT cust_name,cust_contact
FROM customers
WHERE cust id IN (10001,10004);

//将123合并
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                    FROM orders
                    WHERE order_num IN(SELECT order_name
                                        FROM orderitems
                                        HERE prod_id = 'TNT2);

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

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*) 
        FROM  orders
        WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;
//一共有三列。最外层是按name排序。里面是计算两个id相等时，有多少种情况
//是orders表和customers表的id比较，需要标出表名

相关子查询（correlated subquery）涉及外部查询的子查询。任何时候只要列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）。

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*) 
        FROM  orders
        WHERE cust_id = cust_id) AS orders
FROM customers
ORDER BY cust_name;
//这种写法的id会自己和自己的id比，需要加表名就加

第15章联接表

15.1 联接
15.1.1 关系表
假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。
现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢
显然应该分成两个表，理由是：1.因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。2.如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。3.如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。
两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key）（在第1章中首次提到），可以是供应商ID或任何其他唯一值。
products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。vendors表的主键又叫作products的外键，它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。
15.1.2 为什么要使用联接
分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这些好处是有代价的。
15.2 创建联接

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

15.2.1 WHERE字句的重要性
在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。这种就称为笛卡尔积。
笛卡儿积（cartesian product）由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
15.2.2 内部联接
目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
//关系由INNER JOIN 指定，两个表联接。然后ON后面接条件

15.2.3 联结多个表

SELECT prod_name,vend_name,prod_price,quantiy
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存储在每个产品的记录中。

//这是14.2的一个例子
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                    FROM orders
                    WHERE order_num IN(SELECT order_name
                                        FROM orderitems
                                        HERE prod_id = 'TNT2);
//用联接来写，能高效一些
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE orders.cust_id = customers.cust_id
     AND orderitems.order_num = orders.order_num
     AND prod_id = 'TNT2';

第16章创建高级联结

16.1 使用表别名
之前有给列起别名，现在给表起别名。理由是可以缩短SQL语句，以及运行在单条SELECT语句中多次使用相同的表。

//上一章的例子
SELECT cust_name,cust_contact
FROM customers AS c,orders AS o,orderitems AS oi
WHERE orders.cust_id = customers.cust_id
     AND orderitems.order_num = orders.order_num
     AND prod_id = 'TNT2';
//表别名只在查询中使用，不返回客户机，列表面是会返回客户机的

16.2 使用不同类型的联结
迄今为止，我们使用的只是称为内部联结或等值联结（equijoin）的简单联结。现在来看3种其他联结，它们分别是自联结、自然联结和外部联结。
16.2.1 自联结

//如前所述，使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。
//假如你发现某物品（其ID为DTNTR）存在问题，
//因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。
//此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');
//上面的可以，接下来用自联接
SELECT prod_id, prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
    AND p2.prod_id = 'DTNTR';

16.2.2 自然联结
无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。每个内部联接都是自然联接。
自然连接就是把两个表中相同属性"衔接"，属性值相同的就保留下来，如果属性值不相同则去掉，注意，连接后的表的属性值个数为原来两个表的属性之和减去公共属性的个数，如果两个表没公共属性则自然连接就是笛卡尔乘积。
图片说明

16.2.3 外部联结
许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。

//只有INNER JOIN是表示
SELECT customers.cust_id,order.order_num
FROM customer INNER JOIN orders
    ON customers.cust_id = order.cust_id;
//左联结  两个表，有交集。左联结是整个左表
SELECT customers.cust_id,order.order_num
FROM customer LEFT INNER JOIN orders
    ON customers.cust_id = order.cust_id;
//右联结 右联结是整个右表   
SELECT customers.cust_id,order.order_num
FROM customer RIGHT INNER JOIN orders
    ON customers.cust_id = order.cust_id;

16.3 使用带聚集函数的联接

//聚集函数是运算汇总数据的
//要检索所有客户及每个客户所下的订单数
SELECT customers.cust_name,
       customers.cust_id,
       COUNT(order.order_num) AS num_ord
FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id
//下面左联结例子，相比上面，还包括了没有下订单的客户
SELECT customers.cust_name,
       customers.cust_id,
       COUNT(order.order_num) AS num_ord
FROM customers LEFT INNER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id

16.4 使用联结和联结条件
1.注意所使用的联结类型。一般我们使用内部联结，但使用外部联
结也是有效的。
2. 保证使用正确的联结条件，否则将返回不正确的数据。
3. 应该总是提供联结条件，否则会得出笛卡儿积。

第17章组合查询

17.1 组合查询
多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。
有两种基本情况，其中需要使用组合查询：
 在单个查询中从不同的表返回类似结构的数据；
 对单个表执行多个查询，按单个查询返回数据。
17.2 创建组合查询
17.2.1 使用UNION

//需要价格小于等于5的所有商品的列表，而且还想包括供应商1001和1002生产的所有物品
//第一条语句
SELECT vend_id, prod_id, prod_price
FROM products
WHERE pro_proce <= 5;
//第二句
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 pro_proce <= 5
    OR vend_id IN(1001,1002);
//使用UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE pro_proce <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN(1001,1002);
在这个简单的例子中，使用UNION可能比使用WHERE子句更为复杂。
但对于更复杂的过滤条件，或者从多个表（而不是单个表）中检索数据的情形，
使用UNION可能会使处理更简单。

17.2.2 UNION规则
 UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
 UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
17.2.3 包含或取消重复的行
UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。但是这是默认行为，使用UNION ALL，MySQL不取消重复的行，匹配的都返回出来。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE pro_proce <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN(1001,1002);

WHERE是完成不了这种重复行也返回的情况
17.2.4 对组合查询结果排序

SELECT vend_id, prod_id, prod_price
FROM products
WHERE pro_proce <= 5
UNION 
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN(1001,1002)
ORDER BY vend_id, prod_price;

使用UNION可极大地简化复杂的WHERE子句，简化从多个表中检索数据的工作。

MySQL必知必会 Day3

第14章 使用子查询

第15章 联接表

第16章 创建高级联结

第17章 组合查询

第14章使用子查询

第15章联接表

第16章创建高级联结

第17章组合查询