MYSQL学习（四）

MYSQL学习（四）

联结表

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

创建联结

mysql> SELECT vend_name, prod_name,prod_price

   -> FROM vendors, products

   -> WHERE vendors.vend_id= products.vend_id

   -> ORDER BY vend_name,prod_name;

+-------------+----------------+------------+

| vend_name  | prod_name      | prod_price |

+-------------+----------------+------------+

| ACME       | Bird seed      |      10.00 |

| ACME       | Carrots        |       2.50 |

| ACME       | Detonator     |      13.00 |

| ACME       | Safe           |      50.00 |

| ACME       | Sling          |       4.49 |

| ACME       | TNT (1 stick)  |       2.50 |

| ACME       | TNT (5 sticks) |      10.00 |

| Anvils R Us | .5 ton anvil   |      5.99 |

| Anvils R Us | 1 ton anvil    |      9.99 |

| Anvils R Us | 2 ton anvil    |     14.99 |

| Jet Set    | JetPack 1000   |      35.00 |

| Jet Set    | JetPack 2000   |      55.00 |

| LT Supplies | Fuses          |       3.42 |

| LT Supplies | Oil can        |      8.99 |

+-------------+----------------+------------+

14 rows in set (0.00 sec)

目前为止所有的联结称为等值联结，它基于两个表之间的相等测试。这种联结称为内部联结。

笛卡尔积的联结类型又称叉联结。

mysql> SELECT vend_name, prod_name,prod_price

   -> FROM vendors INNERJOIN products

   -> ON vendors.vend_id = products.vend_id

   -> ORDER BY vend_name,prod_name;

+-------------+----------------+------------+

| vend_name  | prod_name      | prod_price |

+-------------+----------------+------------+

| ACME       | Bird seed      |      10.00 |

| ACME       | Carrots        |       2.50 |

| ACME       | Detonator      |      13.00 |

| ACME       | Safe           |      50.00 |

| ACME       | Sling          |       4.49 |

| ACME       | TNT (1 stick)  |       2.50 |

| ACME       | TNT (5 sticks) |      10.00 |

| Anvils R Us | .5 ton anvil   |      5.99 |

| Anvils R Us | 1 ton anvil    |      9.99 |

| Anvils R Us | 2 ton anvil    |     14.99 |

| Jet Set    | JetPack 1000   |     35.00 |

| Jet Set    | JetPack 2000   |      55.00 |

| LT Supplies | Fuses          |       3.42 |

| LT Supplies | Oil can        |      8.99 |

+-------------+----------------+------------+

14 rows in set (0.08 sec)

和使用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;

+----------------+-------------+------------+----------+

| prod_name      | vend_name   | prod_price | quantity |

+----------------+-------------+------------+----------+

| .5 ton anvil   | Anvils R Us |       5.99 |       10 |

| 1 ton anvil    | Anvils R Us |       9.99 |        3 |

| TNT (5 sticks) | ACME        |     10.00 |        5 |

| Bird seed      | ACME        |     10.00 |        1 |

+----------------+-------------+------------+----------+

4 rows in set (0.00 sec)

下面的SQL语句执行的结果和使用子查询得到的结果一样。

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

+----------------+--------------+

| cust_name      | cust_contact |

+----------------+--------------+

| Coyote Inc.    | Y Lee        |

| YosemitePlace | Y Sam        |

+----------------+--------------+

2 rows in set (0.01 sec)

创建高级联结

给列起别名的语法如下：

mysql> SELECT CONCAT(RTRIM(vend_name), '(',RTRIM(vend_country), ')') AS

    ->vend_title

   -> FROM vendors

   -> ORDER BY vend_name;

+-------------------------+

| vend_title              |

+-------------------------+

| ACME (USA)              |

| Anvils R Us (USA)       |

| Furball Inc. (USA)      |

| Jet Set (England)       |

| Jouets Et Ours (France) |

| LT Supplies (USA)       |

+-------------------------+

6 rows in set (0.00 sec)

别名除了用于列名和计算字段以外，还允许给表起别名。主要是为了缩短SQL语句，允许在单条SELECT语句中多次使用相同的表。表的别名不返回客户机。

mysql> SELECT cust_name, cust_contact

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

+----------------+--------------+

| cust_name      | cust_contact |

+----------------+--------------+

| Coyote Inc.    | Y Lee       |

| YosemitePlace | Y Sam        |

+----------------+--------------+

2 rows in set (0.00 sec)

 

自联结：

使用子查询

mysql> SELECT prod_id, prod_name

   -> FROM products

   -> WHERE vend_id = (SELECT vend_id

   ->                  FROMproducts

   ->                  WHERE prod_id = 'DTNTR');

+---------+----------------+

| prod_id | prod_name      |

+---------+----------------+

| DTNTR  | Detonator      |

| FB     | Bird seed      |

| FC     | Carrots        |

| SAFE   | Safe           |

| SLING  | Sling          |

| TNT1   | TNT (1 stick)  |

| TNT2   | TNT (5 sticks) |

+---------+----------------+

7 rows in set (0.01 sec)

使用自联结的相同查询：由于此查询需要两张表实际上是相同的表，在FROM子句中出现了两次，为了解决对products的引用所具有的二义性，使用了别名，第一次出现为别名p1，第二次为别名p2。

mysql> SELECT p1.prod_id, p1.prod_name

   -> FROM products ASp1, products AS p2

   -> WHERE p1.vend_id = p2.vend_id

   -> AND p2.prod_id = 'DTNTR';

+---------+----------------+

| prod_id | prod_name      |

+---------+----------------+

| DTNTR  | Detonator      |

| FB     | Bird seed      |

| FC     | Carrots        |

| SAFE   | Safe           |

| SLING  | Sling          |

| TNT1   | TNT (1 stick)  |

| TNT2   | TNT (5 sticks) |

+---------+----------------+

7 rows in set (0.00 sec)

自然联结：无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（内部联结）返回所有的数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

mysql> SELECT c.*, o.order_num,o.order_date,

   ->        oi.prod_id,oi.quantity, oi.item_price

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

+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+

| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip |cust_country | cust_contact | cust_email     | order_num | order_date         |prod_id | quantity | item_price |

+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+

|  10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI        | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 |FB      |       1 |      10.00 |

|  10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI        | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 |FB      |       1 |      10.00 |

+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+

2 rows in set (0.00 sec)

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

使用内部联结。检索所有的客户及其订单。

mysql> SELECT customers.cust_id,orders.order_num

   -> FROM customers INNER JOIN orders

   -> ON customers.cust_id = orders.cust_id;

+---------+-----------+

| cust_id | order_num |

+---------+-----------+

|  10001 |     20005 |

|  10001 |     20009 |

|  10003 |     20006 |

|  10004 |     20007 |

|  10005 |     20008 |

+---------+-----------+

5 rows in set (0.00 sec)

检索所有客户，包括没有订单的客户。

mysql> SELECT customers.cust_id,orders.order_num

   -> FROM customers LEFTOUTER JOIN orders

   -> ON customers.cust_id= orders.cust_id;

+---------+-----------+

| cust_id | order_num |

+---------+-----------+

|  10001 |     20005 |

|  10001 |     20009 |

|  10002 |      NULL |

|  10003 |     20006 |

|  10004 |     20007 |

|  10005 |     20008 |

+---------+-----------+

6 rows in set (0.00 sec)

带聚集函数的联结：

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

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

+----------------+---------+---------+

| cust_name      | cust_id | num_ord |

+----------------+---------+---------+

| Coyote Inc.    |  10001 |       2 |

| Wascals        |  10003 |       1 |

| YosemitePlace |   10004 |      1 |

| E Fudd         |  10005 |       1 |

+----------------+---------+---------+

4 rows in set (0.00 sec)

 

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

+----------------+---------+---------+

| cust_name      | cust_id | num_ord |

+----------------+---------+---------+

| Coyote Inc.    |  10001 |       2 |

| Mouse House    |  10002 |       0 |

| Wascals        |  10003 |       1 |

| YosemitePlace |   10004 |       1 |

| E Fudd         |  10005 |       1 |

+----------------+---------+---------+

5 rows in set (0.00 sec)

组合查询

利用UNION操作符将多条SELECT语句组合成一个结果集。

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

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

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

mysql> SELECT vend_id, prod_id, prod_price

   -> FROM products

   -> WHERE prod_price <= 5;

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|   1003 | FC      |       2.50 |

|   1002 | FU1     |       3.42 |

|   1003 | SLING   |       4.49 |

|   1003 | TNT1    |       2.50 |

+---------+---------+------------+

4 rows in set (0.00 sec)

 

mysql> SELECT vend_id, prod_id, prod_price

   -> FROM products

   -> WHERE vend_id IN (1001, 1002);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|   1001 | ANV01   |       5.99 |

|   1001 | ANV02   |       9.99 |

|   1001 | ANV03   |      14.99 |

|   1002 | FU1     |       3.42 |

|   1002 | OL1     |       8.99 |

+---------+---------+------------+

5 rows in set (0.02 sec)

 

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

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|   1003 | FC      |       2.50 |

|   1002 | FU1     |       3.42 |

|   1003 | SLING   |       4.49 |

|   1003 | TNT1    |       2.50 |

|   1001 | ANV01   |       5.99 |

|   1001 | ANV02   |       9.99 |

|   1001 | ANV03   |      14.99 |

|   1002 | OL1     |       8.99 |

+---------+---------+------------+

8 rows in set (0.00 sec)

 

mysql> SELECT vend_id, prod_id, prod_price

   -> FROM products

   -> WHERE prod_price <= 5 OR vend_id IN (1001, 1002);

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|   1001 | ANV01   |       5.99 |

|   1001 | ANV02   |       9.99 |

|   1001 | ANV03   |      14.99 |

|   1003 | FC      |       2.50 |

|   1002 | FU1     |       3.42 |

|   1002 | OL1     |       8.99 |

|   1003 | SLING   |       4.49 |

|   1003 | TNT1    |       2.50 |

+---------+---------+------------+

8 rows in set (0.00 sec)

UNION的使用规则：

1）  UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔。

2）  UNION中的每个查询必须包含相同的列，表达式或聚集函数。

3）  列数据类型必须兼容。

 

UNION默认自动去除重复的行，如果不想去除重复的行，使用UNION ALL关键字。

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

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|   1003 | FC      |       2.50 |

|   1002 | FU1     |       3.42 |

|   1003 | SLING   |       4.49 |

|   1003 | TNT1    |       2.50 |

|   1001 | ANV01   |       5.99 |

|   1001 | ANV02   |       9.99 |

|   1001 | ANV03   |      14.99 |

|   1002 | FU1     |       3.42 |

|   1002 | OL1     |       8.99 |

+---------+---------+------------+

9 rows in set (0.00 sec)

 

UNION使用一条ORDER BY语句排序

mysql> 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)

   -> ORDER BY vend_id, prod_price;

+---------+---------+------------+

| vend_id | prod_id | prod_price |

+---------+---------+------------+

|   1001 | ANV01   |       5.99 |

|   1001 | ANV02   |       9.99 |

|   1001 | ANV03   |      14.99 |

|   1002 | FU1     |       3.42 |

|   1002 | FU1     |       3.42 |

|   1002 | OL1     |       8.99 |

|   1003 | FC      |       2.50 |

|   1003 | TNT1    |       2.50 |

|   1003 | SLING   |       4.49 |

+---------+---------+------------+

9 rows in set (0.00 sec)