连接查询知识点以及案例

#进阶6连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2 有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

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

SELECT * FROM beauty;

SELECT * FROM boys;


#一、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 e.job_id=j.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：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
and city LIKE '__a%';

#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 departments d,employees e
WHERE d.department_id=e.department_id
and commission_pct is not NULL
GROUP BY department_name;

#6.可以加排序

#案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY 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
and city like 's%';