MySQL---连接查询--笛卡尔积

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

分类：
      按年份分类：
      sql92标准;仅仅支持内连接
      sql99标准；支持内连接+外连接(左外和右外)+交叉连接
      
      按功能分类：
             内连接
                   等值连接、非等值连接、自连接
                             
              外连接
                    左外连接、右外连接、全外连接
             交叉连接
      
*/
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 departments,employees
WHERE departments.department_id=employees.department_id;

#2.为表起别名
/*
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
好处：(1)提高语句的简介度
(2)区分多个重名的字段
*/
#2.查询工种号、工种名、员工名
SELECT  e.job_id ,job_title,last_name
FROM employees AS e,jobs AS j
WHERE e.job_id=j.job_id;

#3.两个表的顺序是否可以调换
#2.查询工种号、工种名、员工名
SELECT  e.job_id ,job_title,last_name
FROM jobs AS j,employees AS e
WHERE e.job_id=j.job_id;
#4、可以加筛选？
#案例：查询有奖金的员工名、部门名
SELECT  last_name,employees.department_id, 
department_name, commission_pct
FROM  employees,departments
WHERE  commission_pct IS NOT NULL
&&employees. department_id=departments.department_id;
#案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT  city,department_name
FROM  departments,locations
WHERE city LIKE '_o%' &&  
departments.location_id=locations.location_id;

#5:可以分组
#案例1：查询每个城市的部门个数
SELECT city,COUNT(department_id)
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` 
&&`commission_pct`IS NOT NULL
GROUP BY `department_name`,d.manager_id;

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