mysql多表查询

 

mysql多表查询

前提如下：

1. person表：
2. ----------------------------------
3. id name      dept_id   salary
4. 0001 wangda  101       1500
5. 0002 wanger  103       3000
6. 0003 wangsan 108       4000
7. 0004 wangsi  109       2000
8. 0005 wangwu  101       6000
9. 0006 wangliu 102       7000
10. 0007 wangqi  [NULL]    4000
11. ----------------------------------
12. dept表：
13. ----------------------------------
14. dept_id dept_name
15. 101 caiwu
16. 102 zhenggong
17. 103 wuzi
18. 108 shangwu
19. 109 renshi
20. 110[NULL]
21. ----------------------------------
22. salary_grade表：
23. ----------------------------------
24. salary_grade lowest_sal highest_sal
25. A            0          999
26. B            1000       1999
27. C            2000       2999
28. D            3000       4999
29. E            5000       6999
30. F            7000       9999
31. ----------------------------------

内连接，查询两张表中的指定数据（等值）

功能：查询person表中有部门的人员的人员姓名、部门编号和部门名称

思路：人员姓名和部门编号在person表中都有。但是部门名称是在dept表中和部门编号进行对应的。

1. SELECT
2. p.name,
3. p.dept_id,
4. d.dept_name
5. FROM
6. person p,
7. dept d
8. WHERE
9. p.dept_id = d.dept_id;

结果：

1. name dept_id dept_name
2. wangda 101 caiwu
3. wangwu 101 caiwu
4. wangliu 102 zhenggong
5. wanger 103 wuzi
6. wangsan 108 shangwu
7. wangsi 109 renshi

这里注意，在person表中没有部门的wangqi在这次查询中并没有出现。

内连接，查询三张表中的指定数据（不等值）

功能：查询person表中有部门的人员的人员姓名、部门编号、部门名称、薪资和薪资等级

思路：部门名称在dept表中，而薪资等级是在salary_grade表中和薪资范围相关。

1. SELECT
2. p.`name`,
3. p.dept_id,
4. d.dept_name,
5. p.salary,
6. s.salary_grade
7. FROM
8. person p,
9. dept d,
10. salary_grade s
11. WHERE
12. p.dept_id = d.dept_id
13. AND p.salary BETWEEN s.lowest_sal
14. AND s.highest_sal;

结果：

1. name dept_id dept_name salary salary_grade
2. wangda 101 caiwu 1500 B
3. wangsi 109 renshi 2000 C
4. wanger 103 wuzi 3000 D
5. wangsan 108 shangwu 4000 D
6. wangwu 101 caiwu 6000 E
7. wangliu 102 zhenggong 7000 F

 

外连接，查询两张表中的指定数据（join）

功能（LEFT JOIN ON）：查询person表中所有人（包括有部门和无部门的人员）的人员名称和部门名称

1. SELECT
2. p. NAME,
3. d.dept_name
4. FROM
5. person p
6. LEFT JOIN dept d ON p.dept_id = d.dept_id;

结果：

1. NAME dept_name
2. wanger wuzi
3. wangsan shangwu
4. wangsi renshi
5. wangwu caiwu
6. wangliu zhenggong
7. wangqi [NULL]

功能（RIGHT JOIN ON）：查询dept表中所有部门中的人员名称、所在部门名称

1. SELECT
2. p. NAME,
3. d.dept_name
4. FROM
5. person p
6. RIGHT JOIN dept d ON p.dept_id = d.dept_id;

结果：

1. NAME dept_id dept_name
2. wangda 101 caiwu
3. wangwu 101 caiwu
4. wangliu 102 zhenggong
5. wanger 103 wuzi
6. wangsan 108 shangwu
7. wangsi 109 renshi
8. [NULL]110[NULL]

如何同时实现左右连接（两张表的只需要一次UNION，三张表则需要三次UNION，四张表需要）

1. SELECT
2. *
3. FROM
4. person p
5. LEFT JOIN dept d ON p.dept_id = d.dept_id
6. UNION
7. SELECT
8. *
9. FROM
10. person p
11. RIGHT JOIN dept d ON p.dept_id = d.dept_id;

结果：

1. id name dept_id salary dept_id1 dept_name
2. 0001 wangda 101 1500   101 caiwu
3. 0002 wanger 103 3000   103 wuzi
4. 0003 wangsan 108 4000  108 shangwu
5. 0004 wangsi 109 2000   109 renshi
6. 0005 wangwu 101 6000   101 caiwu
7. 0006 wangliu 102 7000  102 zhenggong
8. 0007 wangqi [NULL]4000[NULL][NULL]
9. [NULL][NULL][NULL][NULL]110[NULL]

 

SQL:1999

natural join    自然连接：将两表中所有相同字段全部关联（且不会产生笛卡尔集）。

1. SELECT
2. *
3. FROM
4. person
5. NATURAL JOIN dept;

结果：

1. dept_id id name salary dept_name
2. 1010001 wangda 1500 caiwu
3. 1010005 wangwu 6000 caiwu
4. 1020006 wangliu 7000 zhenggong
5. 1030002 wanger 3000 wuzi
6. 1080003 wangsan 4000 shangwu
7. 1090004 wangsi 2000 renshi

join using(str)    连接并指定连接用字段。和natural join相比，更加具有针对性。

1. SELECT
2. *
3. FROM
4. person
5. JOIN dept USING (dept_id);

结果：

1. dept_id id name salary dept_name
2. 1010001 wangda 1500 caiwu
3. 1010005 wangwu 6000 caiwu
4. 1020006 wangliu 7000 zhenggong
5. 1030002 wanger 3000 wuzi
6. 1080003 wangsan 4000 shangwu
7. 1090004 wangsi 2000 renshi

自连接：同表多别名查询

调整person表结构：

1. id name dept_id salary manager_id
2. 0001 wangda 101 1500   0005
3. 0002 wanger 103 3000   0006
4. 0003 wangsan 108 4000  0005
5. 0004 wangsi 109 2000   0006
6. 0005 wangwu 101 6000   0007
7. 0006 wangliu 102 7000  0007
8. 0007 wangqi [NULL]9000[NULL]

功能：查询wangsi的上司的姓名

1. SELECT
2. e.id employee_id,
3. e. NAME employee,
4. m.id manager_id,
5. m.`name` manager
6. FROM
7. person e,
8. person m
9. WHERE
10. e.manager_id = m.id
11. AND lower(e. NAME)='wangsi';

结果：

1. employee_id employee manager_id manager
2. 0004 wangsi 0006 wangliu