20、MySQL的高级联结查询,非内联结

1、自联结语句

主要用途: 树形结构的应用, mid

传统模式

mysql> select * from products where prod_id in (select prod_id from products where vend_id = '1003');
+---------+---------+----------------+------------+-------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                       |
+---------+---------+----------------+------------+-------------------------------------------------+
| DTNTR   |    1003 | Detonator      | 13.00      | Detonator (plunger powered), fuses not included |
| FB      |    1003 | Bird seed      | 10.00      | Large bag (suitable for road runners)           |
| FC      |    1003 | Carrots        | 2.50       | Carrots (rabbit hunting season only)            |
| SAFE    |    1003 | Safe           | 50.00      | Safe with combination lock                      |
| SLING   |    1003 | Sling          | 4.49       | Sling, one size fits all                        |
| TNT1    |    1003 | TNT (1 stick)  | 2.50       | TNT, red, single stick                          |
| TNT2    |    1003 | TNT (5 sticks) | 10.00      | TNT, red, pack of 10 sticks                     |
+---------+---------+----------------+------------+-------------------------------------------------+

自连接模式

mysql> select p1.prod_id, p1.prod_name from products p1, products p2 where p1.prod_id = p2.prod_id and p2.vend_id = '1003';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

2、自然连接 数据压测、评估 100 X 1000

mysql> select p1.prod_id, p1.prod_name from products p1, products p2 where p2.vend_id = '1003';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| ANV01   | .5 ton anvil   |
| ANV01   | .5 ton anvil   |
| ANV01   | .5 ton anvil   |
| ANV01   | .5 ton anvil   |
| ANV01   | .5 ton anvil   |
| ANV01   | .5 ton anvil   |
| ANV01   | .5 ton anvil   |
| ANV02   | 1 ton anvil    |
| ANV02   | 1 ton anvil    |
| ANV02   | 1 ton anvil    |
| ANV02   | 1 ton anvil    |
| ANV02   | 1 ton anvil    |
| ANV02   | 1 ton anvil    |
| ANV02   | 1 ton anvil    |
| ANV03   | 2 ton anvil    |
| ANV03   | 2 ton anvil    |
| ANV03   | 2 ton anvil    |
| ANV03   | 2 ton anvil    |
| ANV03   | 2 ton anvil    |
| ANV03   | 2 ton anvil    |
| ANV03   | 2 ton anvil    |
| DTNTR   | Detonator      |
| DTNTR   | Detonator      |
| DTNTR   | Detonator      |
| DTNTR   | Detonator      |
| DTNTR   | Detonator      |
| DTNTR   | Detonator      |
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FB      | Bird seed      |
| FB      | Bird seed      |
| FB      | Bird seed      |
| FB      | Bird seed      |
| FB      | Bird seed      |
| FB      | Bird seed      |
| FC      | Carrots        |
| FC      | Carrots        |
| FC      | Carrots        |
| FC      | Carrots        |
| FC      | Carrots        |
| FC      | Carrots        |
| FC      | Carrots        |
| FU1     | Fuses          |
| FU1     | Fuses          |
| FU1     | Fuses          |
| FU1     | Fuses          |
| FU1     | Fuses          |
| FU1     | Fuses          |
| FU1     | Fuses          |
| JP1000  | JetPack 1000   |
| JP1000  | JetPack 1000   |
| JP1000  | JetPack 1000   |
| JP1000  | JetPack 1000   |
| JP1000  | JetPack 1000   |
| JP1000  | JetPack 1000   |
| JP1000  | JetPack 1000   |
| JP2000  | JetPack 2000   |
| JP2000  | JetPack 2000   |
| JP2000  | JetPack 2000   |
| JP2000  | JetPack 2000   |
| JP2000  | JetPack 2000   |
| JP2000  | JetPack 2000   |
| JP2000  | JetPack 2000   |
| OL1     | Oil can        |
| OL1     | Oil can        |
| OL1     | Oil can        |
| OL1     | Oil can        |
| OL1     | Oil can        |
| OL1     | Oil can        |
| OL1     | Oil can        |
| SAFE    | Safe           |
| SAFE    | Safe           |
| SAFE    | Safe           |
| SAFE    | Safe           |
| SAFE    | Safe           |
| SAFE    | Safe           |
| SAFE    | Safe           |
| SLING   | Sling          |
| SLING   | Sling          |
| SLING   | Sling          |
| SLING   | Sling          |
| SLING   | Sling          |
| SLING   | Sling          |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT1    | TNT (1 stick)  |
| TNT1    | TNT (1 stick)  |
| TNT1    | TNT (1 stick)  |
| TNT1    | TNT (1 stick)  |
| TNT1    | TNT (1 stick)  |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
| TNT2    | TNT (5 sticks) |
| TNT2    | TNT (5 sticks) |
| TNT2    | TNT (5 sticks) |
| TNT2    | TNT (5 sticks) |
| TNT2    | TNT (5 sticks) |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
98 rows in set (0.16 sec)

外部连接 left join 不用where 用on
下面是自连接,产生5条数据

mysql> select * from customers c, orders o where c.cust_id = o.cust_id;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+-----------+---------------------+---------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          | order_num | order_date          | cust_id |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+-----------+---------------------+---------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |     20005 | 2005-09-01 00:00:00 |   10001 |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |     20006 | 2005-09-12 00:00:00 |   10003 |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |     20007 | 2005-09-30 00:00:00 |   10004 |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | NULL                |     20008 | 2005-10-03 00:00:00 |   10005 |
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |     20009 | 2005-10-08 00:00:00 |   10001 |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+-----------+---------------------+---------+
5 rows in set (0.10 sec)

外部连接 where换成on left join 产生6条数据

还有right jion

mysql> select * from customers c left join orders o on c.cust_id = o.cust_id;
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+-----------+---------------------+---------+
| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          | order_num | order_date          | cust_id |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+-----------+---------------------+---------+
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |     20005 | 2005-09-01 00:00:00 |   10001 |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com |     20006 | 2005-09-12 00:00:00 |   10003 |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    |     20007 | 2005-09-30 00:00:00 |   10004 |
|   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       | NULL                |     20008 | 2005-10-03 00:00:00 |   10005 |
|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     |     20009 | 2005-10-08 00:00:00 |   10001 |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  | NULL                | NULL      | NULL                | NULL    |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+-----------+---------------------+---------+
6 rows in set (0.11 sec)

带有聚合函数的联结

自连接方式

mysql> select c.cust_name, c.cust_id, count(*) from customers c, orders o where c.cust_id = o.cust_id group by c.cust_id;
+----------------+---------+----------+
| cust_name      | cust_id | count(*) |
+----------------+---------+----------+
| Coyote Inc.    |   10001 |        2 |
| Wascals        |   10003 |        1 |
| Yosemite Place |   10004 |        1 |
| E Fudd         |   10005 |        1 |
+----------------+---------+----------+

left join模式

mysql> select c.cust_name, c.cust_id, count(*) from customers c left join orders o on c.cust_id = o.cust_id group by c.cust_id;
+----------------+---------+----------+
| cust_name      | cust_id | count(*) |
+----------------+---------+----------+
| Coyote Inc.    |   10001 |        2 |
| Mouse House    |   10002 |        1 |
| Wascals        |   10003 |        1 |
| Yosemite Place |   10004 |        1 |
| E Fudd         |   10005 |        1 |
+----------------+---------+----------+
5 rows in set (0.08 sec)
相关推荐
©️2020 CSDN 皮肤主题: 1024 设计师:白松林 返回首页