1.等值连接
/
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表其别名
⑤可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
/

案例1:查询女神名和对应的男神名
SELECT
name,boyName
FROM
boys,beauty
WHERE
boys.id = beauty.boyfriend_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 AS j
WHERE
e.job_id = j.job_id;

#3.可以加筛选？

案例1：查询有奖金的员工名、部门名
SELECT
e.last_name,d.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;

案例2：查询城市名中第二个字符为O的部门名和城市名
SELECT l.city,d.department_name
FROM
locations AS l,departments AS d
WHERE
l.location_id = d.location_id AND l.city LIKE ‘_O%’;

#4.可以加分组？

案例1：查询每个城市的部门个数
SELECT
COUNT(*),city
FROM
locations AS l,departments AS d
WHERE
l.location_id = d.location_id
GROUP BY
city;

#案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT
d.department_name,d.manager_id,MIN(salary)
FROM
departments AS d,employees AS e
WHERE
d.department_id = e.department_id AND e.commission_pct IS NOT NULL
GROUP BY
d.department_name,d.manager_id;

6.可以加排序

案例1：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT
COUNT(),job_title
FROM
employees AS e,jobs AS 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 AS e,departments AS d,locations AS l
WHERE
d.location_id = l.location_id AND e.department_id = d.department_id