MySQL多表联查

user表

CREATE TABLE user (
`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`username`  varchar(10) DEFAULT 'xxx' ,
`sex`  enum('w','m') DEFAULT 'w' ,
`age`  tinyint(4) NULL DEFAULT 18 ,
`grade`  varchar(10) DEFAULT 'python36' ,
PRIMARY KEY (`id`)
);

address表

CREATE TABLE address (
`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`uid`  int(10) UNSIGNED NOT NULL ,
`address`  varchar(255) DEFAULT NULL ,
PRIMARY KEY (`id`)
);

主体结构

select * from 表名1,表名2... where 表名1.字段=表名2.字段 and ...

查询id为1的用户的 用户信息以及地址信息

select * from user where id=1;

select * from address where uid=1;

实例

不起别名查询所有

SELECT
	*
FROM
	USER,
	address
WHERE
	`user`.id = address.uid
AND `user`.id = 1;

不起别名查询某个字段

SELECT
	`user`.id,
	`user`.username,
	address.address
FROM
	USER,
	address
WHERE
	`user`.id = address.uid
AND `user`.id = 1;

起别名查询某个字段

SELECT
	u.id,
	u.username,
	a.address
FROM
	USER u,
	address a
WHERE
	u.id = a.uid
AND u.id = 1;

主体结构

select * from 表1 inner join 表2 on 条件；

查询id为1的用户的 用户信息以及地址信息

不起别名

SELECT
	*
FROM
	USER
INNER JOIN address ON `user`.id = address.uid
AND `user`.id = 1;

起别名

select * from user u INNER JOIN address a on u.id=a.uid and u.id=1;

主体

select * from 表1 left join 表2 on 条件...

查询id为1的用户的 用户信息以及地址信息

不起别名

select * from user LEFT JOIN address on `user`.id=address.uid and `user`.id=1;

起别名

select * from user u LEFT JOIN address a on u.id=a.uid and u.id=1;

主体

select * from 表1 rightjoin 表2 on 条件...

查询id为1的用户的 用户信息以及地址信息

不起别名

select * from user RIGHT JOIN address on `user`.id=address.uid and `user`.id=1;

起别名

select * from user u RIGHT JOIN address a on u.id=a.uid and u.id=1;