MYSQL（八）

目录

一.多表查询分类讲解

1.等值连接vs非等值连接

1.1等值连接

1.2非等值连接

​编辑

2.自连接vs非自连接

2.1自连接

3.内连接vs外连接

3.1 SQL92语法实现多表查询

3.1.1  SQL92实现内连接

3.1.2  SQL92实现外连接（MYSQL不支持此语法，Oracle可以）

 3.2  SQL99语法实现多表查询

3.2.1SQL99语法实现内连接 （INNER JOIN）

3.2.2SQL99语法实现外连接 （OUTER JOIN）

左外连接（LEFT OUTER JOIN）

右外连接

语法

 满外连接（FULL OUTER JOIN） 

---

 拓展1：区分重复的列名

• 多个表中有相同列时，必须在列名之前加上表名前缀。
• 在不同表中具有相同列名的列可以用 表名 加以区分。

SELECT employees.last_name, departments.department_name,employees.department_id FROM employees, departments                                                                                            WHERE employees.department_id = departments.department_id;

拓展2：表的别名

• 使用别名可以简化查询。
• 列名前使用表名前缀可以提高查询效率。 

SELECT e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id         FROM    employees e , departments d  ##给表起别名                                                              WHERE e.department_id = d.department_id;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。  

一.多表查询分类讲解

1.等值连接vs非等值连接

1.1等值连接

利用两张表中具有相同的列值进行查询

类似这样就是等值连接

1.2非等值连接

现有EMPLOYEES和JOB_GRADES两张表，如下。

现在需要利用EMPLOYEES表里面储存的员工信息和JOB_GRADES里面所展示的薪资等级分类。来查询每个员工的薪资及其薪资所处的等级。

代码如下：

SELECT *

FROM job_grades;

SELECT last_name,salary,grade_level

FROM employees e,job_grades j

WHERE e.'salary' BETWEEN j.'lowest_sl' AND j.'highest_sal';#这样就是非等值连接

查询结果如下： 

2.自连接vs非自连接

2.1自连接

自连接：用同一张表中的数据进行查询处理

EMPLOYEES表如下

每行记录有employee_id和manager_id这两个字段

manager_id记录的是员工的上司的id

题目：查询employees表，返回“XXX works for XXX”

写法一：

SELECT CONCAT(worker.last_name ,' works for '

                , manager.last_name)

FROM employees worker, employees manager#起别名，本质是同一张表。

WHERE worker.manager_id = manager.employee_id ;#等值查询，利用WOKER表中的MANAGER_ID和MANAGER表中的EMPLOYEE_ID相等

worker和manager本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。  

 查询结果如下：

写法二：

#查询员工id和姓名及其管理者的id和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name

FROM employees emp,employees mgr

WHERE emp.'manager_id' = mgr.'employee_id';

3.内连接vs外连接

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表(右表)中不匹配的行，这种连接称为左（右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。如果是左外连接，则连接条件中左边的表也称为主表 ，右边的表称为从表 。
• 外连接分为左外连接，右外连接，满连接（SQL92中不存在满连接）

3.1 SQL92语法实现多表查询

下面两张表为下面使用SQL92和SQL99语法进行多表查询内连接和外连接例子所用到的表。

 已知employees表中有107行记录，departments表中有106条记录。

3.1.1  SQL92实现内连接

employees表中有107行记录，但是却只查询出了106条记录。

是因为在employees表中有一行记录（一行代表一个员工的信息）的deparment_id为null,意思是这个员工没有部门，则这种情况下，不满足查询条件，员工的数据就无法查询出来。

3.1.2  SQL92实现外连接（MYSQL不支持此语法，Oracle可以）

现在有一个员工没有部门，那么现在可以使用外连接，把这个没有部门的员工找出来，即把不符合匹配条件的数据查询出来。 

下面的两张运行结果可知MySQL不支持SQL92的外连接，而Oracle可以

不满足查询条件的员工的employee_id和deparment_id也展示了出来(employees e为主表，departments d为从表，所以这个为左外连接)

 3.2  SQL99语法实现多表查询

SQL99语法中使用JOIN...ON的方式实现多表查询。这种方式也能解决外连接的问题。MySQL是支持此种方式的

 使用JOIN...ON子句创建连接的语法结构

SELECT table1.column, table2.column, table3.column

FROM table1

JOIN table2 ON (table1和table2的连接条件)

JOINtable3 ON (table2和table3的连接条件)

语法说明：

• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
• 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

用到一个JOIN...ON语句的例子

SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id

FROM employee e

  JOIN departments d ON e.department_id=d.department_id;

 用到了两个JOIN...ON语句的例子  

SELECT  last_name, dapartment_name, city

FROM employees e

    JOIN departments d ON e.'dapartment_id' = d.'department_id'

       JOIN locations i ON d.'location_id' = i.'location_id'; 

3.2.1SQL99语法实现内连接 （INNER JOIN）

用INNER JOIN语法实现，这个INNER可以省略，直接用上方介绍到的JOIN...ON语法就能实现

SELECT 字段列表

FROM A表

INNER JOIN B表 ON 关联条件

WHERE 等其他子句;

3.2.2SQL99语法实现外连接 （OUTER JOIN）

左外连接（LEFT OUTER JOIN）

• 语法：（OUTER可加可不加）

#实现查询结果是A

SELECT 字段列表

FROM A表

LEFT OUTER JOIN B表 ON 关联条件

WHERE 等其他子句;

• 举例

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

右外连接

• 语法

SELECT 字段列表

FROM A表

RIGHT OUTER JOIN B表 ON 关联条件

WHERE 等其他子句; 

• 举例 

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;

需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在， 只能用 (+) 表示。 

 满外连接（FULL OUTER JOIN） 

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。