07_MySQL DQL_多表查询_等值内连接
#6:连接查询
/*
含义:多表查询,当查询的字段来自多个表
笛卡尔积: 表1,m行;表2,n行; 表1,表2 = m*n行
发生原因:表1的每行和表2的n行拼接,形成n行,最终得到m*n行
如何避免:增加连接条件
连接查询的分类:
按年代分类:
sql92标准【mysql只支持该标准的内连接,一部分外连接】
sql99标准【推荐】mysql支持内连接 外连接(左右连接) 交叉连接
按功能分类:
内连接
等值连接
非等值连接
自连接
外连接
左连接
右连接
全连接
交叉连接
*/
USE girls;
# 笛卡尔积现象
SELECT * FROM beauty,boys;
# 增加连接条件
SELECT NAME,boyName
FROM beauty,boys
WHERE beauty.`boyfriend_id` = boys.`id`;
# 一、sql92标准
# 1、内连接-等值连接
/*
n表连接,需要至少n-1个条件
表的顺序没有要求
一般需要给表起别名
可以搭配所有子句使用,比如:筛选,分组,排序
*/
# 案例1: 查询女神名和对应的男神名
SELECT NAME,boyName
FROM beauty,boys
WHERE beauty.`boyfriend_id` = boys.`id`;
USE myemployees;
# 案例2: 查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;
# 2、给表起别名
/*
区分多个重名的字段
提高语句简洁
注意:如果给表起了别名,原始表在该SQL中就不再生效
*/
# 案例3:查询员工号,工种号,工种名
SELECT employee_id, e.job_id, job_title
FROM employees AS e,jobs AS j
WHERE e.`job_id` = j.`job_id`;
# 3、两个表的顺序是否可以调整
# 案例3:查询员工号,工种号,工种名
SELECT employee_id, e.job_id, job_title
FROM jobs AS j,employees AS e
WHERE e.`job_id` = j.`job_id`;
# 4、带筛选条件的等值内连接查询
# 案例:查询有奖金的员工名,部门名
SELECT last_name, department_name, commission_pct
FROM employees AS e, departments AS d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL ;
# 案例:查询所在城市名第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments AS d,locations AS l
WHERE d.`location_id` = l.`location_id`
AND l.`city` LIKE '_o%';
# 5、带分组的等值内连接查询
# 案例:查询每个城市的部门个数
SELECT COUNT(*), d.location_id, city
FROM departments AS d, locations AS l
WHERE d.`location_id` = l.`location_id`
GROUP BY d.`location_id`;
# 案例:查询有奖金的每个部门的部门名,领导编号,该部门的最低工资
SELECT department_name,e.manager_id,MIN(salary)
FROM departments AS d, employees AS e
WHERE d.`department_id` = e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY d.`department_id`;
# 6、带排序的等值内连接查询
# 案例:查询每个工种的工种名,员工个数,并按员工个数降序排序
SELECT job_title, COUNT(*) AS 员工个数
FROM jobs AS j, employees AS e
WHERE j.`job_id` = e.`job_id`
GROUP BY j.`job_id`
ORDER BY 员工个数 DESC;
# 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`;