MySQL关联查询

两个表：

name表

id	name
1	ru
2	ruge
3	ruye
4	rushen

 

 score表

 

id	score
1	120
2	130
4	150
5	110

 

1.内联

 

（1）不做限制

SELECT * FROM NAME AS NAME INNER JOIN score AS s ;

结果：
    id  name        id   score  
------  ------  ------  --------
     1  ru           1       120
     2  ruge         1       120
     3  ruye         1       120
     4  rushen       1       120
     1  ru           2       130
     2  ruge         2       130
     3  ruye         2       130
     4  rushen       2       130
     1  ru           4       150
     2  ruge         4       150
     3  ruye         4       150
     4  rushen       4       150
     1  ru           5       110
     2  ruge         5       110
     3  ruye         5       110
     4  rushen       5       110

 （2）id相等时

SELECT * FROM NAME AS n INNER JOIN score AS s ON n.`id`=s.`id`;

结果：
id	name	id	score
1	ru	1	120
2	ruge	2	130
4	ruye	4	150

 2、左外连接（left join 或者left outer join）

 

SELECT * FROM NAME AS n LEFT JOIN score AS s ON n.`id`=s.`id`;

结果：

    id  name        id   score  
------  ------  ------  --------
     1  ru           1       120
     2  ruge         2       130
     4  rushen       4       150
     3  ruye    (NULL)    (NULL)

 

 3、右外连接（right join或者right outer join）

SELECT * FROM NAME AS n RIGHT JOIN score AS s ON n.`id`=s.`id`;

结果：

id	name	id	score
1	ru	1	120
2	ruge	2	130
4	ruye	4	150
\N	\N	5	110

 注：外联必须有查询条件

 

 二、3表关联查询

 

没有限制条件时：

SELECT * FROM `stuname` nm INNER JOIN `stuaddress` address INNER JOIN `stuscore` score;

 

id	name	id	address	id	score
1	ru	1	hebei	1	120
1	ru	2	beijing	1	120
2	ruge	1	hebei	1	120
2	ruge	2	beijing	1	120
3	ruye	1	hebei	1	120
3	ruye	2	beijing	1	120
4	rushen	1	hebei	1	120
4	rushen	2	beijing	1	120
1	ru	1	hebei	2	130
1	ru	2	beijing	2	130
2	ruge	1	hebei	2	130
2	ruge	2	beijing	2	130
3	ruye	1	hebei	2	130
3	ruye	2	beijing	2	130
4	rushen	1	hebei	2	130
4	rushen	2	beijing	2	130
1	ru	1	hebei	4	150
1	ru	2	beijing	4	150
2	ruge	1	hebei	4	150
2	ruge	2	beijing	4	150
3	ruye	1	hebei	4	150
3	ruye	2	beijing	4	150
4	rushen	1	hebei	4	150
4	rushen	2	beijing	4	150
1	ru	1	hebei	5	160
1	ru	2	beijing	5	160
2	ruge	1	hebei	5	160
2	ruge	2	beijing	5	160
3	ruye	1	hebei	5	160
3	ruye	2	beijing	5	160
4	rushen	1	hebei	5	160
4	rushen	2	beijing	5	160

 

 

 

有限制条件时：

SELECT * FROM `stuname` nm INNER JOIN `stuaddress` address ON nm.`id`=address.`id` INNER JOIN `stuscore` score ON nm.`id`=score.`id`;

 

id	name	id	address	id	score
1	ru	1	hebei	1	120
2	ruge	2	beijing	2	130

 

 

 

三，另一个实例

SELECT permission_id,permission_name,permission_url,parent_id FROM tbpermissions WHERE permission_id>999999 AND permission_id<10000000 AND permission_id IN(
		SELECT permission_id FROM tbrole_permissions WHERE role_id=(
		SELECT role_id FROM tbuserinfo WHERE user_id='0'));

等价于下面的sql：

SELECT tm.* FROM tbpermissions tm INNER JOIN 
	(SELECT tpm.permission_id FROM tbrole_permissions tpm INNER JOIN  tbuserinfo tu ON tpm.`role_id`=tu.`role_id` AND tu.`user_id`='0') tp
	 ON tm.permission_id>999999 AND tm.permission_id<10000000 AND tm.permission_id IN(tp.`permission_id`);