DQL---连接查询

DQL—连接查询

6. 连接查询

含义：涉及多个表字段的查询
笛卡尔乘积：表一有m行，表二有n行，结果为m*n行（缺乏连接条件）
分类：
按年代分类：sq192标准。sq199标准[推荐]
按功能分类：内连接（等值/非等值/自连接），外连接（左/右/全），交叉连接

----------------------------------------sq192标准-------------------------------------------
# 1.1 等值连接
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

#1.2 为表起别名
/* 如果为表起了别名，那么不能使用原始的表名*/
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id`=j.`job_id`;

# 1.3 两个表的顺序是否可以调换
SELECT last_name,e.job_id,job_title
FROM jobs AS j,employees AS e
WHERE e.`job_id`=j.`job_id`;

# 1.4 是否可以加筛选
SELECT last_name,department_name,commission_pct
FROM employees AS e ,departments AS d
WHERE commission_pct IS NOT NULL AND e.`department_id`=d.`department_id`;

SELECT department_name,city
FROM departments AS d,locations AS l
WHERE d.`location_id`=l.`location_id` AND l.`city` LIKE '_o%';

# 1.5 是否可以加分组
SELECT city,COUNT(*)
FROM locations AS l,departments AS d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;

SELECT department_name,d.manager_id,MIN(salary)
FROM employees AS e,departments AS d
WHERE e.`department_id`=d.`department_id` AND commission_pct IS NOT NULL
GROUP BY e.department_id;

# 1.6 是否可以加排序
SELECT job_title,COUNT(*)
FROM employees AS e, jobs AS j
WHERE e.`job_id`=j.`job_id`
GROUP BY e.`job_id`
ORDER BY COUNT(*) DESC;

# 1.7 多表连接
SELECT last_name,department_name,city
FROM employees AS e,departments AS d,locations AS l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;

# 1.8 非等值连接
## 查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees AS e,job_grades AS j
WHERE e.`salary` BETWEEN j.lowest_sal AND j.highest_sal;

# 1.9自连接

## 查询员工名和上级名称
SELECT e.employee_id 员工编号,e.last_name 员工名称,m.employee_id 领导编号,m.last_name 领导名称
FROM employees AS e,employees AS m
WHERE e.`manager_id`=m.`employee_id`;

---------------------------------------sql199标准(推荐使用)--------------------------------------
/*
语法： select 查询列表 
       from 表1 别名 [连接类型]
       join 表2 别名
       on 连接条件
       【where/group by/having/order by/】
分类： 内连接--> inner
       外连接--> 左外 left
                 右外 right
                 全外 full    
       交叉连接--> cross
*/

# 1. 内连接(inner join)
/*
分类：等值、非等值、自连接（inner可以省略）
*/
# 1.1 等值连接
## 查询员工名与部门名
SELECT last_name,department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`;
## 查询名字中包含e的员工名与工种名
SELECT last_name,job_title
FROM employees AS e
INNER JOIN jobs AS j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%e%';
## 查询部门个数大于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;
## 查询哪个部门的员工个数大于3的部门名和员工个数，并按照个数降序
SELECT department_name,COUNT(*)
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;
## 查询员工名、部门名、工种名，并按照部门名降序
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 d.department_name DESC;
# 1.2 非等值连接
## 查询员工工资级别大于20的个数并按照级别降序
SELECT grade_level,COUNT(*)
FROM employees AS e
JOIN job_grades AS jg 
ON e.`salary`>= jg.`lowest_sal` AND e.`salary`<=jg.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
# 1.3 自连接
## 查询姓名中包含K的员工的名字，上级的名字
SELECT e.last_name AS 员工名,m.last_name AS 上级名
FROM employees AS e
JOIN employees AS m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

# 2. 外连接
/*
场景：查询一个表中，而在另一个表中没有的字段
特点：
    1. 外连接中查询结果等于内连接结果加上主表有从表无的结果
    2. 左外连接，left左边为主表
    3. 右外连接，right右边为主表
    4. 左外右外交换两个表顺序，效果不变
    5. 全外连接结果=内连接结果+主表有从表无的结果+从表有主表无的结果
*/
## 查询那个部门没有员工
SELECT d.*,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;

# 3. 交叉连接
/*实现dikaer乘积*/
SELECT b.*,bo.*
FROM beauty AS b
CROSS JOIN boys AS bo;

本文地址：https://blog.csdn.net/G_eraint/article/details/107311899