Mysql中的连接查询

Mysql中的关联查询

1.背景

当一张表中字段不能满足查询条件,需要查询另一张表的数据来补全结果时.可以使用关联查询/子查询.

2.实例

2.1 数据库中存在着两张表

• 姓名表:user_name

  # 记录着4个用户的名称信息
  SELECT * from user_name;
  

  uid	name	class
  1	yang	A
  2	su	A
  3	zhang	A
  4	li	B

• 年龄表:user_age

  # 记录着2个用户的年龄信息.
  SELECT * from user_age;
  

  uid	age	class
  1	18	A
  2	19	B

放在一起是不是好看些

2.2 当需要查询uid=1的用户名称以及年龄时,单表不满足查询结果.

SELECT
	* 
FROM
	user_name n
	INNER JOIN user_age a ON n.uid = a.uid 
	AND n.uid = '1';

2.3 连接查询

2.3.1 左连接

先执行左表的select得到左表的全部信息,再根据连接条件拼接上右边,确保左边的结果全部显示.

2.3.1.1 name 左连接 age(大表name,小表age)

SELECT
	* 
FROM
	user_name n
	LEFT JOIN user_age a ON n.uid = a.uid 
	AND n.uid = '1'

分析:

1.左连接,先在左表name执行select语句

SELECT
	* 
FROM
	user_name n;

2.根据连接条件name.uid = age.uid拼接上右表age,不满足的置空.

SELECT
	* 
FROM
	user_name n
	LEFT JOIN user_age a ON n.uid = a.uid 

3.最后根据剩下的AND条件,继续过滤不满足的结果.

SELECT
	* 
FROM
	user_name n
	LEFT JOIN user_age a ON n.uid = a.uid 
	AND n.uid = '1';

2.3.2 小表age 左连接 大表name

SELECT
	* 
FROM
	user_age a
	LEFT JOIN user_name n ON n.uid = a.uid 
	AND a.uid = '1';

2.3.2 右连接

先执行右表的select得到右表的全部信息,再根据连接条件拼接上左边,确保右边的结果全部显示.

2.3.2.2 name 右连接 age

SELECT
	* 
FROM
	user_name n
	RIGHT JOIN user_age a ON n.uid = a.uid 
	AND n.uid = '1'

2.3.2.2 age 右连接 age

SELECT
	* 
FROM
	user_age a
	RIGHT JOIN user_name n ON n.uid = a.uid 
	AND a.uid = '1';

2.3.3 内连接

只显示满足关联条件的结果.

2.3.3.1 name 内连接 age

SELECT
	* 
FROM
	user_name n
	INNER JOIN user_age a ON n.uid = a.uid 
	AND n.uid = '1';

2.3.3.2 age 内连接 name

SELECT
	* 
FROM
	user_age a
	INNER JOIN user_name n ON n.uid = a.uid 
	AND a.uid = '1';