多表查询
经典五张表
一对多,一个用户可能有多个角色
1.建表语句
t_user 表
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT, //主键自增 非空
`username` varchar(32) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
`remark` varchar(32) DEFAULT NULL,
`email` varchar(32) DEFAULT NULL,
`createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8
t_role表
CREATE TABLE `t_role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`keyword` varchar(64) DEFAULT NULL,
`description` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
一张中间表
t_user_role
CREATE TABLE `t_user_role` (
`user_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
KEY `FK_Reference_8` (`role_id`),
CONSTRAINT `FK_Reference_7` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`),
//外键关联t_user表中的主键id
CONSTRAINT `FK_Reference_8` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
列如我们要查询用户所对应的用户信息和角色信息
第一种 隐式内连接
SELECT
tu.id, tu.username, tu.remark,
tu.email, tu.createTime, tu.updateTime,
tr.id, tr.name, tr.description
FROM
t_user tu,
t_user_role tur,
t_role tr
WHERE
tu.id = tur.user_id
AND tr.id = tur.role_id;
查询的结果如下 我们查询三张表时,必须最少要有两个连接条件,否则会产生笛卡尔积
也就是说,如果你要同时查询n张表,所建立的查询条件就为n-1
第二种左外连接
左外连接是实际工作中用的比较多得查询方式,效率比较高
SELECT
tu.id, tu.username, tu.remark,
tu.email, tu.createTime, tu.updateTime,
tr.id, tr.name, tr.description
FROM
t_user tu
LEFT JOIN t_user_role tur ON tu.id = tur.user_id
LEFT JOIN t_role tr ON tur.role_id = tr.id
左外连接格式为
select 要查询的字段 from 表名 LEFT (outer)JOIN 要关联的表 on 连接条件 LEFT JOIN 要关联的表 on 连接条件
outer 可以省略不写