MySQL-DQL——连接查询(sql92标准)

连接查询：当查询的字段来自于多个表时，就需要用到连接查询

笛卡尔乘积现象：

        select count(*) from 表1;       #假设输出m行

        select count(*) from 表2;       #假设输出n行

        最终结果：输出m*n行 实现了全连接

发生原因：没有有效的连接条件

如何避免：加上有效的连接条件

连接查询分类：

        （1）按年代分类：

                sql92标准：仅仅支持内连接

                sql99标准【推荐】：支持内连接、外连接（左外+右外）、交叉连接

        （2）按功能分类：

                内连接：等值连接、非等值连接、自连接

                外连接：左外连接、右外连接、全外连接

                交叉连接

sql92标准

1、等值连接

特点：

多表等值连接的结果为多表的交集部分；

n表连接，至少需要n-1个连接条件；

多表的顺序没有要求；

一般需要为表起别名；

可以搭配所有查询子句使用，如分组、排序、筛选。

（1）基本等值连接

eg1.查询女生名和男生名

SELECT `name`,boyname FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;
 

eg2.查询员工名和对应的部门名

SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

（2）为表起别名

提高语句的简洁度，区分多个重名的字段

两个表的顺序可以调换，可以加筛选，可以加分组

<注>如果为表起了别名，则查询的字段就不能用原来的表名来限定

eg1.查询员工号、工种号、工种名

select last_name,e.job_id,job_title
from employees as e,jobs as j
where e.`job_id`=j.`job_id`;

#加筛选 使用AND子句

eg1.查询有奖金的员工号、部门名

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;

eg2.查询城市名中第二个字符为o的部门名和城市名

SELECT department_name,city
FROM locations AS l,departments AS d
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';

#加分组 使用group by子句

eg1.查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM locations AS l,departments AS d
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

eg2.查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name,d.manager_id,MIN(salary),commission_pct
FROM departments AS d,employees AS e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

#加排序 使用order by子句

eg1.查询每个工种的工种名和员工的个数，并且按员工个数降序排序

SELECT job_title,COUNT(*)
FROM employees AS e,jobs AS j
WHERE j.`job_id`=e.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

#实现三表连接 使用AND子句

eg1.查询员工名、部门名和所在的城市

SELECT last_name,department_name,city
FROM employees AS e,locations AS l,departments AS d
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`;

2、非等值连接

eg1.查询员工的工资和工资级别C以上的员工

SELECT last_name,salary,grade_level
FROM job_grades AS j, employees AS e
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`
AND j.grade_level <='C';

3、自连接

使用别名 

eg1.查询员工的名字以及他的上级的名字
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees AS e,employees AS m
WHERE e.`manager_id`=m.`employee_id`;