MySQL连接查询实例详解

本文实例讲述了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数据库计有所帮助。