本文共 3189 字，大约阅读时间需要 10 分钟。

# 连接查询/*	含义: 又称为多表查询,当查询的字段来自于多张表时,就会用到连接查询		分类:				按年代分类:				sql92标准: 仅仅支持内连接				sql99标准(推荐): 支持内连接,外连接(左外和右外),交叉连接								按功能分类:							内连接:											等值连接											非等值连接											自连接							外连接:											左外连接											右外连接											全外连接							交叉连接*/#--------------------sql92------------------# 1内连接# 1.1 等值连接/*		语法:				SELECT 查询列表				FROM 表名1,表名2,...				WHERE 等值连接条件						特点: 					1.为了解决多表中的字段名重名问题,往往为表起别名,提高语义性					2.表的顺序无要求					*/# 简单的两表连接# 案例: 查询员工名和部门名SELECT last_name, department_name FROM employees e, departments dWHERE e.department_id = d.department_id# 添加筛选条件# 1 查询部门编号 > 100 的部门名和所在的城市名SELECT department_id, cityFROM departments, locationsWHERE department_id > 100 AND departments.location_id = locations.location_id# 2 查询有奖金的员工名,部门名SELECT last_name, department_nameFROM employees e, departments dWHERE e.commission_pct is not nullAND e.department_id = d.department_id# 3 查询城市名中第二个字符为o的部门名和城市名SELECT department_name, cityFROM departments d, locations lWHERE d.location_id = l.location_idAND l.city LIKE "_o%";# 添加分组 + 筛选条件# 1 查询每个城市的部门个数SELECT count(*), cityFROM departments d, locations lWHERE d.location_id = l.location_idGROUP BY l.city;# 2 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资(感觉这题不好)SELECT department_name, d.manager_id, MIN(salary)FROM employees e, departments dWHERE e.department_id = d.department_id AND commission_pct IS NOT NULLGROUP BY department_name,d.manager_id# 3 查询每个工种的工种名和员工的个数,并按员工个数降序SELECT COUNT(*), job_titleFROM employees, jobsWHERE employees.job_id = jobs.job_idGROUP BY jobs.job_idORDER BY COUNT(*) desc# 4 查询员工名,部门名和所在的城市SELECT last_name, department_name, cityFROM employees e, departments d, locations lWHERE e.department_id = d.department_idAND d.location_id = l.location_id# 非等值连接# 1 查询员工的工资和工资级别SELECT salary,grade_levelFROM employees, job_gradesWHERE salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal# 自连接# 查询员工名和上级的名称SELECT e.employee_id,e.last_name,m.employee_id,m.last_nameFROM employees e, employees mWHERE e.manager_id = m.employee_id#------------------------sql99-----------------------/*	SELECT 查询列表	FROM 表1 	[INNER] JOIN 表2	ON 连接条件	WHERE 筛选条件	GROUP BY 分组列表	HAVING 分组后筛选	ORDER BY 排序列表		sql92 和 sql99 的区别:	sql99,使用join关键字替代了之前的逗号,并且将连接条件和筛选条件进行了分离*/#1.内连接#1.1等值连接#案例: 查询员工名和部门名SELECT last_name, department_nameFROM employeesINNER JOIN departmentsON employees.department_id = departments.department_id# 添加筛选条件# 查询部门编号 > 100 的部门名和所在的城市名SELECT department_name, cityFROM departments dINNER JOIN locations lon d.location_id = l.location_idWHERE department_id > 100# 分组加筛选# 查询每个城市的部门个数SELECT count(*), cityFROM departments djoin locations lON d.location_id = l.location_idGROUP BY l.city;# 分组加筛选加排序# 查询部门中员工个数>10的部门名,并按个数降序排序SELECT count(*), department_nameFROM employees eJOIN departments don e.department_id = d.department_idGROUP BY d.department_idHAVING count(*) > 10ORDER BY count(*) desc;#1.2非等值连接# 查询部门编号在10-90之间的员工的工资级别,并按级别进行分组SELECT count(*), j.grade_levelFROM employees eJOIN job_grades jon e.salary BETWEEN j.lowest_sal AND j.highest_salWHERE e.department_id BETWEEN 10 and 90GROUP BY j.grade_level#1.3自连接# 查询员工名和对应的领导名SELECT e.last_name, m.last_nameFROM employees eJOIN employees mON e.manager_id = m.employee_id#2.外连接