2、数据库操作语句：多表查询（表employ、department、location）

目录

1、多表查询连接条件

1）在employ、department两张表中查询

2）若查询语句中出现了多个表中都存在的字段，必须指明此字段所在的表。

3）如果表名比较长，SQL语句中多次使用表名，可以给表起别名，在select和where中使用表的别名

2、多表查询分类：等值连接VS非等值连接、自连接VS非自连接、内连接VS外连接

1）等值连接VS非等值连接

①非等值连接

2）自连接VS非自连接

①自连接：表本身自我引用

3）内连接VS外连接

①内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

②外连接：两个表在连接过程中除了返回满足连接条件的行外，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（null）

③外连接分为：左外连接、右外链接和满外连接

3、合并查询结果集：利用关键词UNION关键字，可以给出多条select语句，并将它们结果组成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并互相对应。各个select语句之间使用UNION或UBNION ALL关键字分割。

4、七种JOIN的实现：集合A、B

1)查询A、B的交集（属于内连接）

2)查询A、B的左外连接

3)查询A、B的右外连接

4)左中图实现

5)右中图实现

6)左下图实现（满外连接）

7)右下图：左中图union all右中图

5、SQL99语法的新特性

1)自然连接:它会自动查询两张连接表中所有相同字段，然后进行等值连接

2)using:指定具体的相同的字段名称，using的括号（）中填入要指定的同名字段

---

1、多表查询连接条件

1）在employ、department两张表中查询

Select employ_id,department_name from employ,department where employ.‘department_id’=department.department_id;(连接条件，多个连接条件使用and)

（department_id为null的无法查询）

//查询表employ、department，显示员工的id以及所在部门名称

2）若查询语句中出现了多个表中都存在的字段，必须指明此字段所在的表。

①条件：employ、department表中都有department_id字段

Select employ_id,department_name,employ.department_id from employ,department where employ.‘department_id’=department.department_id;

建议：从SQL优化角度（减少查询时间），建议多表查询时，每个字段前指明其所在的表。举例：

Select employ.employ_id,department.department_name,employ.department_id from employ,department where employ.‘department_id’=department.department_id;

3）如果表名比较长，SQL语句中多次使用表名，可以给表起别名，在select和where中使用表的别名

Select emp.employ_id,dept.department_name,emp.department_id from employ emp,department dept where emp.‘department_id’=dept.department_id;

注意：如果给表起了别名，一旦在select或where中使用表名的话，则必须使用表的别名，而不能在使用表的原名；因为MYSQL限制性From语句，表的别名已经把表的原名覆盖

结论：n个表至少有n-1个连接条件

2、多表查询分类：等值连接VS非等值连接、自连接VS非自连接、内连接VS外连接

1）等值连接VS非等值连接

①非等值连接

Select *from job_gardes

查询结果：

Select e.list_name,e.salary,j.gardes_level from employ e,job_gardes j Where  e.’salary’ between j.’lowest_sal’ and  j.’highest_sal’;

(where e.’salary’>=j.’lowest_sal’ and  e.’salary <=j.’highest_sal’;)

查询结果：

2）自连接VS非自连接

①自连接：表本身自我引用

练习：查询员工id、姓名及管理者的id、姓名

Select emp.employ_id，emp.last_name，mgr.employ_id, mgr.last_name from employ emp，employ mgr where emp.‘manager_id’=mgr.‘employ_id’；

3）内连接VS外连接

①内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行

例如：

Select employ_id,department_name from employ,department where employ.‘department_id’=department.department_id;（一共107条记录，只查出来106条记录，有一条空记录没有查出来）

②外连接：两个表在连接过程中除了返回满足连接条件的行外，还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时，结果表中相应的列为空（null）

③外连接分为：左外连接、右外链接和满外连接

        左外连接：连接条件中左边的表称为主表，右边的表称为从表

        右外连接：链接条件中右边的表称为主表，左边的表称为从表

例如：查询所有的员工的employ_id,deprtment_name信息（使用左外连接）

Sql92语法实现内连接：

Select employ_id,department_name from employ,department where employ.‘department_id’=department.department_id;

Sql92语法实现外连接：使用+   ————————  mysql不支持SQL92语法中外连接的写法

Select employ_id,department_name from employ,department where employ.’department_id’=department.department_id（+）;

Sql99语法中使用（inner-内或outer-外）JOIN……ON的方式实现多表的查询，这种方式能够解决外连接的问题，同时mysql也支持

Sql99语法实现内连接：

例如两张表：Select employ_id,department_name from employ e （inner） join department d on e.‘department_id’=d.department_id;

例如三张表：Select employ_id,department_name，city from employ e  （inner） join department d on e.‘department_id’=d.department_id join locations l on d.‘locations_id’=l.‘locations_id’

Sql99语法实现外连接：

左外连接：

Select employ_id,department_name from employ e left （outer）join department d on e.'department_id'=d.department_id;

右外连接

Select employ_id,department_name from employ e right （outer）join department d on e.'department_id'=d.department_id;

满外连接：mysql不支持full outer join ……on

3、合并查询结果集：利用关键词UNION关键字，可以给出多条select语句，并将它们结果组成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并互相对应。各个select语句之间使用UNION或UBNION ALL关键字分割。

1）UNION关键字：返回两个查询结果集的并集，并去除重复记录

2）UNION ALL关键字：返回两个查询结果集的并集，并不去除重复记录

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果不存在重复数据，或者不需要去除重复数据，则尽量使用UNION ALL语句，以提高数据查询效率。 

4、七种JOIN的实现：集合A、B

1)查询A、B的交集（属于内连接）

Select employee_id,department_name from employees e join departments d on e.‘department_id’=d.‘department_id’;

2)查询A、B的左外连接

Select employee_id,department_name from employees e left join departments d on e.‘department_id’=d.‘department_id’;

3)查询A、B的右外连接

Select employee_id,department_name from employees e right join departments d on e.‘department_id’=d.‘department_id’;

4)左中图实现

Select employee_id,department_name from employees e left join departments d on e.‘department_id’=d.‘department_id’ where d.‘department_id’ is null;

5)右中图实现

Select employee_id,department_name from employees e right join departments d on e.‘department_id’=d.‘department_id’ where d.‘department_id’ is null;

6)左下图实现（满外连接）

方法一：左上图union all 右中图

Select employee_id,department_name from employees e left join departments d on e.‘department_id’=d.‘department_id’ union all select employee_id ,department_name from employees e right join departments d on e.‘department_id’=d.‘department_id’ where e.‘department_id’ is null;

方法二：左中图union all右上图

Select emplotee_id,department_name from employees e left join department d on e.‘department_id’=d.‘department_id’ where d.‘department_id’ is null union all select employee_id,department_name from employees e right join department d on e.‘department_id’=d.‘department_id’；

7)右下图：左中图union all右中图

Select employee_id,department_name from employees e left join departments d on e.‘department_id’=d.‘department_id’ where d.‘department_id’ is null union all Select employee_id,department_name from employees e right join departments d on e.‘department_id’=d.‘department_id’ where d.‘department_id’ is null;

5、SQL99语法的新特性

1)自然连接:它会自动查询两张连接表中所有相同字段，然后进行等值连接

传统写法：Select employee_id,department_id from employees e join departments d on e.‘department’=d.‘department_id’ and e.‘manager_id’=d.‘manager_id’;

自然连接写法：Select employee_id,department_id from employees e nature join departments d;

2)using:指定具体的相同的字段名称，using的括号（）中填入要指定的同名字段

传统写法：Select employee_id,department_id from employees e join departments d on e.‘department’=d.‘department_id’ and e.‘manager_id’=d.‘manager_id’;

using写法: Select employee_id,department_id from employees e join departments d on using(department_id);