lyt经典版MySQL基础——进阶6：连接查询-sql92语法-内连接

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

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

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
    按年代分类：
    sql92标准：仅仅支持内连接
    sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接
        交叉连接
*/

SELECT * FROM beauty;
SELECT * FROM boys;

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

#一、sql92标准
#1.等值连接
/*
(1)多表等值连接的结果为多表的交集部分
(2)n表连接，至少需要n-1个链接条件
(3)多表的顺序没有要求
(4)一般需要为表起别名
(5)可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/

#案例1：查询女神名和对应的男神名
SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;
#案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE departments.department_id=employees.department_id;

#2.为表起别名
/*
(1)提高语句的简洁度
(2)区分多个重名的字段

注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
因为执行顺序是 from --> where --> select
*/
#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e,jobs
WHERE e.job_id=jobs.job_id;

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

#4.可以加筛选
#案例：查询有奖金的员工名、部门名
SELECT last_name,department_name
FROM departments,employees AS e
WHERE departments.department_id=e.department_id
AND e.commission_pct IS NOT NULL;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments,locations l
WHERE departments.location_id=l.location_id
AND city LIKE '_o%';

#5.可以加分组
#案例1：查询每个城市的部门个数
SELECT city,COUNT(department_name) 个数
FROM locations l,departments d
WHERE l.location_id=d.location_id
GROUP BY city;
#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments d, employees e
WHERE d.department_id=e.department_id
AND e.commission_pct IS NOT NULL
GROUP BY d.department_id;

#6.可以加排序
#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT j.job_title,COUNT(*) FROM employees e,jobs j
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 d.department_id=e.department_id 
AND d.location_id=l.location_id;


#2.非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN lowest_sal AND highest_sal;

#3.自连接
#案例：查询员工名和上级的名称
SELECT e.employee_id '员工id',e.last_name '员工名',
m.employee_id '上级id',m.last_name '上级名'
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;