多表查询
分类:
* 合并结果集
* 连接查询
* 子查询
【1】合并结果集
* 要求被合并的表中,列数相同,列的类型要一一对应相同
* UNION,去除重复行
* UNION ALL,不去除重复行
例: select * from table1 union select * from table2;
select * from table1 union all select * from table2;
【2】连接查询
分类
* 内连接
* 外连接
> 左外连接
> 右外连接
> 全外连接(MySQL不支持)
* 自然连接(属于一种简化方式)
1. 内连接
* 方言:SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx(MySQL可以)
* 标准:SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
* 自然:SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
自动通过两张表的相同列进行匹配,相同列只显示一列
* 内连接查询出的所有记录都满足条件。不满足条件的丢掉
注意:where后面的条件叫做关联条件。
多表查询一定要去除笛卡尔积,用关联条件去除
2. 外连接
* 左外:SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx//坐标所有记录都显示,没有对应的右表则右表以null补充
* 左外自然:SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2;
* 右外:SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx
* 右外自然:SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2;
* 全链接:可以使用UNION来完成全链接:左外 union 右外
注意:外链接:不满足条件的也会查询出来,左外右用null补位,右外左用null补位
子查询
1. 出现的位置:
* where后作为条件存在
* from后作为表存在(一般为多行多列)
2. 条件
***** 单行单列:SELECT * FROM 表1 别名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 别名2 WHERE 条件)
*** 多行单列:SELECT * FROM 表1 别名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 别名2 WHERE 条件)
SELECT * FROM 表名 WHERE 列 > ANY (SELECT . . . .) WHERE 条件
** 单行多列:SELECT * FROM 表1 别名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 别名2 WHERE 条件)//where和子表中对应列数相同
**** 多行多列:SELECT * FROM 表1 别名1 , (SELECT ....) 别名2 WHERE 条件
注意:多表查询要找关联条件,on后面跟关联条件
多表查询小练习
- 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
- 列出薪金比关羽高的所有员工。
- 列出所有员工的姓名及其直接上级的姓名。
- 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
- 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
- 列出所有文员的姓名及其部门名称,部门的人数。
- 列出最低薪金大于15000的各种工作及从事此工作的员工人数。
- 列出在销售部工作的员工的姓名,假定不知道销售部的部门编号。
- 列出薪金高于公司平均薪金的所有员工信息,所在部门名称,上级领导,工资等级。
10.列出与庞统从事相同工作的所有员工及部门名称。
11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
12.列出每个部门的员工数量、平均工资。