mysql多表连接查询举例_MySQL多表连接查询

连接查询是数据库查询中很重要的一个组成部分。而多表连接查询是在日常开发中经常需要用到的一个操作。在此对多表连接查询做一个总结，为了便于理解本文以一个实际开发中的案例作为例子来讲解。

表结构

首先我们创建三张表：用户表(t_user)、账户表(t_account)和账户流水表(t_account_info)，表中的字段非常的简单，分别如下：

t_user

user_id

user_name

1

ricky

2

jack

3

lily

4

mike

5

kobe

6

candy

t_account

user_id

balance

1

5000

2

18000

3

2000

4

25000

5

150000

t_account_info

user_id

type

amount

create_time

1

网购消费

8000

1463132262

1

餐饮消费

2500

1462132262

1

交通消费

500

1461532262

2

网购消费

5000

1463132262

2

餐饮消费

2500

1462132262

2

交通消费

1500

1462232262

2

旅游消费

25000

1463132262

3

网购消费

2000

1463232262

连接查询

内连接

需求：

现在需要分别检索用户表的user_id、user_name和账户表中的balance。 显然，两个表的连接条件是：用户表的user_id=账户表的user_id。

我们可以有两种方式，这两种是等效的

SELECT u.user_id,u.user_name, a.balance FROM t_user AS u, t_account AS a WHERE u.user_id=a.user_id;

另外一个是：

SELECT u.user_id,u.user_name, a.balance FROM t_user AS u INNER JOIN t_account AS a WHERE u.user_id=a.user_id;

外连接

在通常的连接操作中，只有满足连接条件的元组才能作为结果输出。例如上例中t_user和t_account做连接查询的结果表中没有id为6的用户信息，原因在于他没有账户信息，在t_account中没有元组，造成t_user中这些元组在连接时被舍弃了。

有时想以t_user为主体列出每个每个用户的基本信息及账户信息，如果某个用户没有账户，仍把舍弃的t_user元组保存到结果表中，而在t_account表的属性上填空值(NULL)，这时候就需要外连接。

1、左外联结

左外连接列出所有左边关系(t_user)中的所有元组。

SELECT u.user_id,u.user_name, a.balance FROM t_user AS u LEFT OUT JOIN t_account AS a WHERE u.user_id=a.user_id;

2、右外连接

左外连接列出所有右边关系(t_account)中的所有元组。

SELECT u.user_id,u.user_name, a.balance FROM t_user AS u RIGHT OUT JOIN t_account AS a WHERE u.user_id=a.user_id;

最后，为了方便测试，附上完整的表结构SQL：

//建库

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

use test;

/**建表*/

CREATE TABLE `t_user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(20) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `balance` double(9,2) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_account_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `type` varchar(20) NOT NULL, `amount` double(8,2) NOT NULL, `create_time` bigint(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/**初始化数据*/

INSERT INTO t_user(user_name) VALUES('ricky');

INSERT INTO t_user(user_name) VALUES('jack');

INSERT INTO t_user(user_name) VALUES('lily');

INSERT INTO t_user(user_name) VALUES('mike');

INSERT INTO t_user(user_name) VALUES('kobe');

INSERT INTO t_user(user_name) VALUES('candy');

INSERT INTO t_account(user_id,balance) VALUES(1,5000);

INSERT INTO t_account(user_id,balance) VALUES(2,18000);

INSERT INTO t_account(user_id,balance) VALUES(3,2000);

INSERT INTO t_account(user_id,balance) VALUES(4,25000);

INSERT INTO t_account(user_id,balance) VALUES(5,150000);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(1,'网购消费',8000,1463132262);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(1,'餐饮消费',2500,1462132262);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(1,'交通消费',500,1461132262);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(2,'网购消费',5000,1463132262);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(2,'餐饮消费',2500,1462132262);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(2,'交通消费',1500,1461132262);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(2,'旅游消费',25000,1461132262);

INSERT INTO t_account_info(user_id,`type`,amount,create_time) VALUES(3,'网购消费',2000,1463232262);