MySQL入门（二），mysql入门

MySQL入门（二），mysql入门

本学习笔记参考《MySQL必知必会》和官方手册MySQL 5.6 Reference Manual

MySQL入门（一）
MySQL入门（三）
本文内容：
- MySQL联结表
- MySQL视图

四、MySQL联结表

    假设要存储货物的信息，建立一个 products 表，每种货物的信息占一行，包括产品名字，产地以及供应商的信息（供应商名字，电话等）。同一个供应商可能有多种产品，那么这时属于同一供应商的不同产品可能要重复存储供应商的信息，这会造成存储空间和时间的浪费，改动的不方便等。解决这个问题的方法是建立两个表，products 和 vendors ，vendors 表中以供应商ID（vend_id）作为主键（primary key），products 表中以 vend_id 作为外键(foreign key)，这样两个表之间就建立了联系。
    如果数据存储在多个表中可以使用联结来查询，联结是一种机制，用来在一条 SELECT 语句中关联表。

一个联结的例子（利用 WHERE 实现）：

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM vendors, products
    -> WHERE vendors.vend_id = products.vend_id
    -> ORDER BY vend_name, prod_name;

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

4.1 内部联结

    目前为止所用的联结称为等值联结(equijoin)，它基于两个表之间的相等测试，这种联结也成为内部联结。下面更为规范和首选的方法和上面的写法返回相同的结果。

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM vendors INNER JOIN products
    -> ON vendors.vend_id = products.vend_id;

4.2 联结多个表

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;

为简便起见也可以使用表别名来进行查询，表别名和列别名不一样，表别名不返回到客户机。下面的语句使用表别名，和上面的语句返回相同的结果。

mysql> SELECT prod_name, vend_name, prod_price, quantity
    -> FROM orderitems AS o, products AS p, vendors AS v
    -> WHERE p.vend_id = v.vend_id
    -> AND o.prod_id = p.prod_id
    -> AND o.order_num = 20005;

4.3 自联结

    假如你发现某物品（其ID为DTNTR）存在问题，因此你想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

用子查询的方式：

mysql> SELECT vend_id, prod_id, prod_name
    -> FROM products
    -> WHERE vend_id IN ( SELECT vend_id
                       -> FROM products
                       -> WHERE prod_id = 'DTNTR' );

用自联结的方式：

mysql> SELECT p1.vend_id, 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';

4.4 外部联结

    customers 表存储所有客户的信息，每个客户有唯一的ID（cust_id），cust_id 为 customers 的主键。orders 表存储客户订单（但不是订单细节），每个订单有唯一的订单号(order_num)，订单用 cust_id 列与 customers 表关联。

查询存在订单的客户及其订单号（可以用内联结）：

mysql> SELECT customers.cust_id, cust_name, orders.order_num
    -> FROM customers INNER JOIN orders
    -> ON customers.cust_id = orders.cust_id;

查询所有客户及其订单（包括没有订单的客户）：

mysql> SELECT customers.cust_id, cust_name, orders.order_num
    -> FROM customers LEFT OUTER JOIN orders
    -> ON customers.cust_id = orders.cust_id;

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

4.5 使用带聚集函数的联结

mysql> SELECT customers.cust_id, cust_name, orders.order_num,
    -> COUNT(orders.order_num) AS num_ord
    -> FROM customers INNER JOIN orders
    -> ON customers.cust_id = orders.cust_id
    -> GROUP BY customers.cust_id;

一篇介绍联结的文章

五、MySQL视图

5.1 什么是视图

    视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
例子，下面的语句完成检索订购了某个特定产品的客户的信息。

mysql> SELECT cust_name, cust_contact
    -> FROM customers, orders, orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orders.order_num = orderitems.order_num
    -> AND orderitems.prod_id = 'TNT2';

写出这样的语句必须知道表的结构和联结关系，现在如果把这个查询包装成一个名为 productcustomers 的虚拟表，则可以如下轻松的检索出相同的数据：

mysql> SELECT cust_name, cust_contact 
    -> FROM productcustomers 
    -> WHERE prod_id = 'TNT2';

5.2 为什么使用视图

    重用SQL语句；简化复杂的SQL操作；保护数据。但由于视图不包含数据，每次使用时都要完成一个检索，所以会影响性能。

5.3 使用视图

用 CREATE VIEW 语句来创建视图；
用 SHOW CREATE VIEW viewname; 语句来查看创建该视图的语句；
用 DROP VIEW viewname; 来删除视图；
更新视图时，可以先用 DROP 在用 CREATE。也可以直接用 CREATE OR REPLACE VIEW，如果视图不存在则创建，如果存在则替换原视图。

(1) 利用视图简化复杂的联结

mysql> CREATE VIEW productcustomers AS
    -> SELECT cust_name, cust_contact, prod_id
    -> FROM customers, orders, orderitems
    -> WHERE customer.cust_id = orders.cust_id
    -> AND orders.order_num = orderitems.order_num;

上面的语句创建了一个名为 productcustomers 的视图，它联结三个表，以返回订购了任意产品的所有客户列表。

mysql> SELECT * FROM productcustomers;

(2) 用视图重新格式化检索出来的数据

mysql> CREATE VIEW vendorlocations AS
    -> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
    -> AS vend_title
    -> FROM vendors ORDER BY vend_name;
mysql> SELECT * FROM vendorlocations;

(3) 用视图过滤不想要的数据

mysql> CREATE VIEW customeremaillist AS
    -> SELECT cust_id, cust_name, cust_email
    -> FROM customers
    -> WHERE cust_email IS NOT NULL;

新建视图 customeremaillist 过滤没有邮箱地址的用户。

mysql> SELECT * FROM customeremaillist;

(4) 使用视图与计算字段

mysql> CREATE VIEW orderitemsexpanded AS
    -> SELECT order_num, prod_id, quantity, item_price,
    -> quantity * item_price AS expanded_price
    -> FROM orderitems;

新建一个视图 orderitemsexpanded ，计算出所有订单里产品的总价格。

mysql> SELECT * FROM orderitemsexpanded
    -> WHERE order_num = 20005;

5.4 查看视图的信息

利用 DESCRIBE 语句查看视图的基本信息：

mysql> DESC orderitemsexpanded;

mysql> SHOW CREATE VIEW orderitemsexpanded;

查看所有已创建视图的信息：

mysql> USE information_schema;
mysql> SELECT * FROM views;            // 该语句输出信息比较多
mysql> SELECT TABLE_NAME, DEFINER FROM views;