多表联查——内连接，自身连接，外连接，复合条件连接查询

USE test_db;

1内连接查询***

初始表1建立（已有fruits表的情况下）

CREATE table suppliers(
s_id INT NOT NULL AUTO_INCREMENT,
s_name char(50) NOT NULL,
s_city char(50) NULL,
s_zip CHAR(10) NULL,
s_call CHAR(50) NOT NULL,
PRIMARY KEY(s_id)
);

#数据填写

INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call)
VALUES(101,'fastfruit inc.','tianjin','300000','48075'),
(102,'tiantianguoyuan','chongqing','400000','44333'),
(103,'ACME','shanghai','200000','90046'),
(104,'fnk','zhongshan','527437','11111'),
(105,'good set','030000','22222'),
(106,'just eat ours','beijing','010','45678'),
(107,'DK.Lnc.','zhengzhou','450000','33332');

#查询表结构

DESC fruits;

DESC suppliers;

方法一： WHERE 子句 定义连接条件**（隐式内连接）**
（简单明了，但某些时候会影响查询的性能）

SELECT suppliers.s_id,s_name,f_name,f_price
FROM fruits,suppliers WHERE fruits.s_id = suppliers.s_id;

方法二：（显式内连接）使用 INNER JOIN 语法，连接条件使用 ON
（是ANSI SQL 的标准规范，使用INNER JOIN 语法能够确保不会忘记连接条件）

SELECT suppliers.s_id,s_name,f_name,f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;

（与方法一结果一致）

自连接查询

#自连接查询

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

f1,f2是物理上为同一张表，但逻辑上为两张表，为了防止产生二义性，所以使用了别名；
select返回以f1为前缀的列的全名，以 f2的 f_id = ‘a1’ 为对照进行数据过滤，返回所需数据。

外连接
（已有customers表情况下）
#创建表orders

CREATE TABLE orders(
o_num INT NOT NULL auto_increment,
o_date datetime NOT NULL,
c_id INT NOT NULL,
PRIMARY KEY(o_num)
);

#插入数据

INSERT INTO orders(o_num,o_date,c_id)
VALUES(3001,'2008-09-01',10001),
(3002,'2008-09-12',10003),
(3003,'200-09-30',10004),
(3004,'2008-10-02',10005),
(3005,'2008-10-08',10001);

• LEFT JOIN 左连接： 返回 包括 左表中所有记录 和 右表中连接字段相等 的记录。（左表数据全部显示，右表显示和左表有关联的数据）

#左连接，查询所有客户，包括没有订单的客户

SELECT customers.c_id,orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id =orders.c_id;

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

#右连接 查询所有订单中，包括没有客户的订单

SELECT orders.o_num,customers.c_id
FROM customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id; 

复合条件查询

添加过滤条件，限制查询结果

#指定查询客户id为10001的订单信息

SELECT customers.c_id,orders.o_num
FROM customers INNER JOIN orders
ON customers.c_id=orders.c_id AND customers.c_id = 10001;

#使用连接查询 ，对查询的结果排序

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;