MySQL数据查询之连接查询(2)-木庄网络博客

【例3】查询供应f_id='a1'的水果供应商提供的水果种类，SQL语句如下：

1	`mysql> select f1.f_id,f1.f_name -> from fruits` `as` `f1,fruits` `as` `f2 -> where f1.s_id =f2.s_id` `and` `f2.f_id ='a1';+------+------------+\| f_id \| f_name \|+------+------------+\| a1 \| apple \|\| b1 \| blackberry \|\| c0 \| cherry \|+------+------------+3 rows in set (0.00 sec)`

(2)、外连接查询

外连接查询将查询多个表中相关联的行。
内连接时，返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据，即返回查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接：

①左连接：返回包括左表中的所有记录和右表中连接字段相等的记录。
②右连接：返回包括右表中的记录和左表中连接字段相等的记录。

1.左连接left join

首先创建表orders，SQL语句如下;

mysql> create table orders    -> (
    -> o_num int not null auto_increment,
    -> o_date datetime not null,
    -> c_id int not null,
    -> primary key (o_num)
    -> );Query OK, 0 rows affected (0.11 sec)mysql> insert into orders(o_num,o_date,c_id)
    -> values(30001,'2008-09-01',10001),
    -> (30002,'2008-09-12',10003),
    -> (30003,'2008-09-30',10004),
    -> (30004,'2008-10-03',10005),
    -> (30005,'2008-10-08',10001);Query OK, 5 rows affected (0.06 sec)Records: 5  Duplicates: 0  Warnings: 0

【例】在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语句如下：

mysql> select * from customers;+-------+----------+---------------------+---------+--------+-----------+-------------------+| c_id | c_name | c_address | c_city | c_zip | c_contact | c_email |+-------+----------+---------------------+---------+--------+-----------+-------------------+| 10001 | redhool | 200 Street | Tianjin | 300000 | LiMing | LMing@163.com || 10002 | Stars | 333 Fromage Lane | Dalian | 116000 | Zhangbo | Jerry@hotmail.com || 10003 | Netbhood | 1 Sunny Place | Qingdao | 266000 | LuoCong | NULL || 10004 | JOTO | 829 Riverside Drive | Haikou | 570000 | YangShan | sam@hotmail.com |+-------+----------+---------------------+---------+--------+-----------+-------------------+4 rows in set (0.00 sec)mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date | c_id |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)mysql> select customers.c_id , orders.o_num -> from customers left outer join orders -> on customers.c_id = orders.c_id;+-------+-------+| c_id | o_num |+-------+-------+| 10001 | 30001 || 10003 | 30002 || 10004 | 30003 || 10001 | 30005 || 10002 | NULL |+-------+-------+5 rows in set (0.00 sec)

2.右连接right join

右连接是左连接的反向连接，将返回右表的所有行。如果右表的某行在左表中没有匹配行，左表将返回空值。

【例】在customers表和orders表中，查询所有订单，包括没有客户的订单，SQL语句如下：

1	`mysql> select customers.c_id, orders.o_num -> from customers right outer join orders -> on customers.c_id = orders.c_id;+-------+-------+\| c_id \| o_num \|+-------+-------+\| 10001 \| 30001 \|\| 10003 \| 30002 \|\| 10004 \| 30003 \|\| NULL \| 30004 \|\| 10001 \| 30005 \|+-------+-------+5 rows in set (0.00 sec)`

(3)、复合条件连接查询

复合条件连接查询是在连接查询的过程中，通过添加过滤条件，限制查询结果，使查询的结果更加准确。

【例1】在customers表和orders表中，使用inner join语法查询customers表汇总ID为10001的客户的订单信息，SQL语句如下：

1	`mysql> select customers.c_id,orders.o_num -> from customers inner join orders -> on customers.c_id = orders.c_id` `and` `customers.c_id = 10001;+-------+-------+\| c_id \| o_num \|+-------+-------+\| 10001 \| 30001 \|\| 10001 \| 30005 \|+-------+-------+2 rows in set (0.00 sec)`

【例2】在fruits表和suppliers表之间，使用inner join语法进行内连接查询，并对查询结果排序，SQL语句如下：

mysql> select suppliers.s_id,s_name,f_name,f_price -> from fruits inner join suppliers -> on fruits.s_id = suppliers.s_id -> order by fruits.s_id;+------+----------------+------------+---------+| s_id | s_name | f_name | f_price |+------+----------------+------------+---------+| 101 | FastFruit Inc. | apple | 5.20 || 101 | FastFruit Inc. | blackberry | 10.20 || 101 | FastFruit Inc. | cherry | 3.20 || 102 | LT Supplies | orange | 11.20 || 102 | LT Supplies | banana | 10.30 || 102 | LT Supplies | grape | 5.30 || 103 | ACME | apricot | 2.20 || 103 | ACME | coconut | 9.20 || 104 | FNK Inc. | lemon | 6.40 || 104 | FNK Inc. | berry | 7.60 || 105 | Good Set | melon | 8.20 || 105 | Good Set | xbabay | 2.60 || 105 | Good Set | xxtt | 11.60 || 106 | Just Eat Ours | mango | 15.70 || 107 | DK Inc | xxxx | 3.60 || 107 | DK Inc | xbabay | 3.60 |+------+----------------+------------+---------+16 rows in set (0.00 sec)