连接查询是数据库查询中很重要的一个组成部分。而多表连接查询是在日常开发中经常需要用到的一个操作。在此对多表连接查询做一个总结,为了便于理解本文以一个实际开发中的案例作为例子来讲解。
表结构
首先我们创建三张表:用户表(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);