1.等值连接
特点：
①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表取别名
⑤可以搭配前面介绍的所有子句搭配使用
案例1：查询员工名和其相对应的部门名
SELECT
last_name,department_name
FROM
employees,departments
WHERE employees.department_id=departments.department_id;
1.1为表起别名（提高语句简洁度，区分重名字段）注意：若起了别名就不能再使用原名去限定
案例1:查询员工名、工种号、工种名
SELECT
last_name,e.job_id,job_title
FROM
employees AS e,jobs AS j
WHERE e.job_id=j.job_id;
1.2 两个表的顺序是可以调换的
SELECT
last_name,j.job_id,job_title
FROM
jobs AS j,employees AS e
WHERE j.job_id=e.job_id;
1.3 可以加筛选
案:1：查询有奖金的员工名、部门名
SELECT
last_name,department_name
FROM
departments AS d,employees AS e
WHERE d.department_id=e.department_id
AND e.commission_pctIS NOT NULL;
案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT
department_name,city
FROM
departments AS d,locations AS l
WHERE d.location_id=l.location_id AND l.city LIKE “_o%”;
1.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
employees e,departments d
WHERE e.department_id=d.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;
1.5 加排序
案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT
job_title,COUNT()
FROM
jobs j,employees e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY COUNT() DESC;
1.6 实现三表连接
案例：查询员工名、部门名和所在的城市
SELECT
last_name,department_name,city
FROM
locations l,departments d,employees e
WHERE l.location_id=d.location_id
AND d.department_id=e.department_id;
2.非等值连接（没有关键键相匹配）
案例1：查询员工的工资和工资级别
SELECT
salary,grade_level
FROM
job_grades jg,employees e
WHERE salary BETWEEN lowest_sal AND highest_sal;
3.自连接(自己连自己，一个表查询两次或多次)
案例：查询员工名和上级的名称
SELECT
e.employee_id,e.last_name,m.last_name
FROM
employees e,employees m
WHERE e.manager_id=m.employee_id;
练习题1
1.显示员工表的最大工资，工资平均值
SELECT
MAX(salary),AVG(salary)
FROM
employees;
2.查询员工表的employee_id,job_id,last_name,按department_id降序，salary升序
SELECT
employee_id,job_id,last_name,department_id,salary
FROM
employees
ORDER BY department_id DESC,salary ASC;
3.查询员工表的job_id中包含a和e的，并且a在e的前面
SELECT
*
FROM
employees
WHERE job_id LIKE “%a%e%”;
4.已知表 student，里面有id（学号），NAME，gradeId（年级编号）
已知表grade，里面有id（年级编号），NAME（年级名）
已知表result，里面有id，score，studentNo(学号)
要求查询姓名，年级名，成绩
SELECT
s.name,g.name,score
FROM
student s,grade g,result r
WHERE s.id=r.studentNo
AND s.gradeId=g.id;
5.显示当前日期，以及去前后空格，截取子字符串的函数
NOW()
TRIM() / TRIM(字符 FROM ‘’)
SUBSTR()
练习题2
1. 查询90号部门员工的job_id和90号部门的location_id
SELECT
job_id,location_id
FROM
departments d,employees e
WHERE d.department_id=e.department_id
AND e.department_id=90;
2. 选择所有有奖金的员工的last_name,department_name,location_id,city
SELECT
e.last_name,d.department_name,d.location_id,city
FROM
locations l,employees e,departments d
WHERE e.department_id=d.department_id
AND l.location_id=d.location_id
AND commission_pctIS NOT NULL;
3. 选择city在Toronto工作的员工的last_name , job_id , department_id , department_name
SELECT
last_name,job_id,e.department_id,department_name,city
FROM
employees e,departments d,locations l
WHERE d.department_id=e.department_id
AND l.location_id=d.location_id
AND l.city=‘Toronto’;
4.查询每个工种，每个部门的部门名，工种名和最低工资
SELECT
department_name,job_title,MIN(salary)
FROM
jobs j,departments d,employees e
WHERE j.job_id=e.job_id
AND d.department_id=e.department_id
GROUP BY e.job_id;
5.查询每个国家下的部门个数大于2的国家编号
SELECT
country_id,COUNT()
FROM
locations l,departments d
WHERE l.location_id=d.location_id
GROUP BY country_id
HAVING COUNT()>2;
6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
SELECT
e1.last_name employees,
e1.employee_id ‘Emp#’,
e2.last_name AS manager,
e2.employee_id AS ‘Mgr#’
FROM
employees e1,employees e2
WHERE e1.manager_id=e2.employee_id
AND e1.employee_id=101;