MySQL入门 DQL语言之七：sql92语法值连接查询（多表查询）

MySQL入门 DQL语言之六：sql92语法值连接查询（多表查询）

#6：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔集乘积现象：表1 有n行，表2有m行，结果等于n*m行

发生原因：
    ①没有有效的连接条件
    ②省略连接条件
    ③所有表中的所有行相互连接
如何避免：添加有效的连接条件

分类：
    按年代分类：
    sql92标准：仅仅支持内连接
    sql99标准【推荐】：支持内连接+外链接（左外和右外）+交叉链接
    
    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外链接：
            左外连接
            右外链接
            全连接
        交叉链接
*/
SELECT * FROM beauty;
SELECT * FROM boys;
#这样会产生笛卡尔集，即是两个表的总行数相乘
SELECT NAME,boyName FROM boys,beauty;
#添加有效连接条件
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` =  boys.`id`;

#一：sql92标准
#1、等值连接
/*
①多表等值连接的结果为多表的交集部分
②N表连接，至少需要n-1个连接条件
③多表连接的顺序没要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句的使用（排序、分组等）
*/
#案1：查询女神名对应的男神名
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.`boyfriend_id` =  boys.`id`;

#案例2：查询部门名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#2、为表起别名
/*
①提高语句的简洁度
②区分多个重名的字段

注意：如果为表起了别名，则查询字段就不能用原来的表名去限定
*/
#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs j
WHERE e.`job_id` = j.`job_id`;

#3、两个表顺序是否可以调换
#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM jobs j,employees AS e
WHERE j.`job_id` = e.`job_id` ; 

#4、可以加筛选
#案例：查询有奖金的员工名、和部门名
SELECT last_name ,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id` 
AND e.`commission_pct` IS NOT NULL;

#案例2：查询城市民中第二个字符为0的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE l.`location_id` = d.`location_id`
AND city LIKE '_o%'

#5、可以加分组
#案例1:查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
GROUP BY city;

#案例2：查询出有奖金的每个部门的部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND commission_pct IS NOT NULL 
GROUP BY department_name,d.`manager_id`;
#6、可以加排序
#案例：每个工种的工种名和员工个数并且按员工个数降序
SELECT j.`job_title`,COUNT(*)
FROM jobs j,employees e
WHERE j.`job_id` = e.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接
#案例:查询员工名、部门名和所在的城市
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`;

#二、非等值连接
#案例1：查询出员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`
AND grade_level = 'A';

#二、自连接
#案例1：查询员工名和上级的名称
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`;