MySQL-DQL——连接查询(sql99标准)

sql99标准

语法：

select 查询列表

from 表1 as 别名

【连接类型】 join 表2 别名

on 连接条件

【where 筛选条件】【group by 分组条件】【having 筛选条件】【order by排序列表】

支持：

        内连接：inner

        外连接：左外 left【outer】，右外right【outer】，全外full【outer】

        交叉连接：cross

一、内连接

语法：

select 查询列表

from 表1 别名

inner join 表2 别名

on 连接条件

分类：等值连接、非等值连接、自连接

1、等值连接

特点：

可以添加排序、分组、部门名

inner可以省略

筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读

inner join连接和sql92语法中的等值连接效果一样，均是查询交集部分

eg1.查询员工名、部门名

select last_name,department_name
from employees as e
inner join departments as d
on e.`department_id`=d.`department_id`;

#添加筛选条件

eg2.查询名字中包含e的员工名和工种名

SELECT job_title,last_name
FROM employees AS e
INNER JOIN jobs AS j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%e%';

#添加筛选条件和分组

eg3.查询部门个数>3的城市名和部门个数

SELECT city,COUNT(*)
FROM departments AS d
INNER JOIN locations AS l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#添加筛选条件和分组和排序

eg4.查询部门的员工个数>3的部门名和员工个数，并按个数排列

SELECT department_name,COUNT(*)
FROM departments AS d
INNER JOIN employees AS e
ON d.`department_id`=e.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#三表连接

eg5.查询员工名、部门名、工种名，并按部门名降序

SELECT last_name,department_name,job_title
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`
INNER JOIN jobs AS j
ON e.`job_id`=j.`job_id`
ORDER BY department_name DESC;

2、非等值连接

eg1.查询员工的工资级别

SELECT salary,grade_level
FROM employees AS e
INNER JOIN job_grades AS g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
ORDER BY grade_level;

eg2.查询工资级别的个数大于20的级别，并按工资级别降序排序

SELECT salary,COUNT(*),grade_level
FROM employees AS e
INNER JOIN job_grades AS g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

3、自连接

eg1.查询员工的名字和其上级的名字

SELECT e.last_name AS 员工名 ,m.last_name AS 主管名
FROM employees AS e
INNER JOIN employees AS m
ON e.`manager_id`=m.`employee_id`
ORDER BY e.last_name;

二、外连接

应用场景：用于查询一个表中有，另一个表中没有的记录

特点：

        （1）外连接的查询结果为主表中所有的记录

                如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示NULL

                外连接查询结果=内连接结果+主表中有而从表中没有的记录

        （2）左外连接，left join左边的是主表

                右外连接，right join右边的是主表

        （3）左外和右外交换两个表的顺序，可以实现同样的效果

1、左（右）外连接

eg1.查询没有男朋友的女生名

左外连接：

SELECT b.name,bo.*
FROM beauty AS b  #主表
LEFT OUTER JOIN boys AS bo   #从表 
ON b.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;

右外连接：

SELECT b.name,bo.*
FROM boys AS bo  #从表 
RIGHT OUTER JOIN beauty AS b   #主表
ON b.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;

eg2.查询哪个部门没有员工

左外连接：

SELECT d.*,e.employee_id
FROM departments AS d
LEFT OUTER JOIN employees AS e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

右外连接：

SELECT d.*,e.employee_id
FROM employees AS e 
RIGHT OUTER JOIN departments AS d
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;
 

2、全外连接

全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的

返回左右表中所有的记录和左右表中连接字段相等的记录。

eg1.

USE girls;
SELECT b.*,bo.*
FROM beauty AS b
FULL OUTER JOIN boys AS bo
ON b.`boyfriend_id`=bo.id

三、交叉连接

实现笛卡尔乘积

eg：

SELECT b.*,bo.*
FROM beauty AS b
CROSS JOIN boys AS bo

#sql92和sql99对比：

功能：sql99支持的较多

可读性：sql99实现了连接条件和筛选条件的分离，可读性较高