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)