MySQL学习笔记之连接查询（本文只详细介绍 SQL 92标准的连接查询）使用where进行连接查询

连接查询（本文只详细介绍 SQL 92标准的连接查询）

一、连接查询的含义与分类

• 含义：
  • 又称为多表查询，当查询的字段来自于多个表时，就会用到连接查询
• 笛卡尔乘积现象：
  • 假设表1 有m行 表2有n行，结果有m*n行
• 发生原因：
  • 没有有效的连接条件
• 解决方法：
  • 添加有效的连接条件
• 连接条件的分类：
  • 按年代分类：
    • SQL 92标准：仅支持内连接
    • SQL 99标准：【推荐使用】，支持内连接+外连接(不包括全外连接)+交叉连接
  • 按功能分类：
    • 内连接：等值连接、非等值连接、自连接
    • 外连接：左外连接、右外连接、全外连接
    • 交叉连接

① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要n-1个连接条件
③ 多表的顺序没有要求，且一般要取别名
④ 可以搭配前面学习的所有子句使用 ORDER BY、GROUP BY、WHERE 筛选

二、等值连接（可以理解为两个表的交集部分）92标准下同

案例1 查询女生对应的男生

SELECT NAME,boyName
FROM beauty,boys
WHERE beauty.`boyfriend_id` = boys.`id`;

案例2 查询员工名和对应的部门名

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

案例3.查询员工名、工种号、工种名

SELECT last_name,employees.job_id,job_title
FROM employees,jobs	#多张表的顺序可随意变更
WHERE employees.`job_id`=jobs.`job_id`;

注意：可以为表起别名,但是起了别名之后就不再认识原名了，需要用别名指代表

SELECT e.last_name,e.job_id,job_title
FROM employees AS e,jobs j 
WHERE e.`job_id`=j.`job_id`;

1.连接查询并加筛选功能：WHERE 连接条件 AND 筛选条件

案例4 查询有奖金的员工名、部门名

SELECT last_name,d.department_name,commission_pct
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL;

案例5 查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM locations loca,departments depa
WHERE depa.`location_id` = loca.`location_id`
AND loca.`city` LIKE '_o%';

2.连接查询搭配分组GROUP BY

案例6 查询每个城市的部门个数

SELECT COUNT(*),city
FROM departments,locations
WHERE departments.`location_id`=locations.`location_id`
GROUP BY city;

案例7 查询有奖金的每个部门的部门名和部门的领导编号以及该部门的最低工资

SELECT department_name,e.`department_id`,d.manager_id, MIN(salary) min_salary
FROM departments d, employees e
WHERE e.`department_id` = d.`department_id`  #连接条件：employees表中有department_id	对应到departments表中的department_id
AND e.`commission_pct` IS NOT NULL
GROUP BY e.`department_id`;

3.连接多表+分组+排序

案例8 查询每个工种的工种名和员工的个数，并且按员工的个数排序

SELECT job_title,COUNT(*) AS cnt
FROM jobs, employees e
WHERE e.`job_id` = jobs.`job_id`
GROUP BY job_title
ORDER BY cnt DESC;

案例9 连接3表：查询员工名 部门名 所在城市(并要求城市以s开头)

SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city LIKE 's%';

三、非等值连接

可以简单理解为：判断一个表中的某字段是否位于另一个表的n个字段之间

案例10 查询员工的工资和工资等级

SELECT last_name,salary,grade_level
FROM employees e, job_grades jg
WHERE e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;

四、自连接

所谓自连接，就是自己连接自己，是同一个表的两边操作
注意：通常给一个表起不同的别名，以示区分

案例11 查询员工名 员工id 及其上司的员工名和员工id

SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e,employees m
WHERE e.`manager_id` = m.`employee_id`;