本文实例讲述了MySQL连接查询。分享给大家供大家参考，具体如下：
创建表suppliers：
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,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');
内连接
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits ,suppliers
WHERE fruits.s_id = suppliers.s_id;
使用 inner join 语法进行内连接查询
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits
INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;
创建表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(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);
左连接
SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;
右连接
SELECT customers.c_id, orders.o_num
from customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;
复合条件连接查询
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;
【例.46】在fruits表和suppliers表之间使用内连接查询，查询之前，查看两个表的结构
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits ,suppliers
WHERE fruits.s_id = suppliers.s_id;
【例.47】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;
【例.48】查询供应f_id='a1'的水果供应商提供的其他水果种类
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';
【例.49】在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语法如下
SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;
【例.50】在customers表和orders表中，查询所有订单，包括没有客户的订单
SELECT customers.c_id, orders.o_num
from customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;
【例.51】在customers表和orders表中，使用INNER JOIN语法查询customers表中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;
【例.52】在fruits表和suppliers表之间使用INNER JOIN语法进行内连接查询，并对查询结果排序
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;
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》
希望本文所述对大家MySQL数据库计有所帮助。
您可能感兴趣的文章:- MySQL中基本的多表连接查询教程
- 详解MySQL中的分组查询与连接查询语句
- mysql连接查询（左连接，右连接，内连接）
- MySQL查询优化:连接查询排序浅谈
- MySQL笔记之连接查询详解
- MySQL优化之使用连接(join)代替子查询
- MySQL查询优化:用子查询代替非主键连接查询实例介绍
- 经典mysql连接查询例题
- MySQL中对表连接查询的简单优化教程
- mysql多表连接查询实例讲解
- mysql多个left join连接查询用法分析
- Mysql自连接查询实例详解
