MySQL（十）

自然连接（NATURAL JOIN）

它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。

在SQL92标准中：

SELECT employee_is,last_name,department_name
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id';
AND e.'manager_id'=d.'manager_id';

在SQL99中可写成

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING

USING制定了具体的相同的字段名称，你需要在USING的括号中填入要指定的同名字段。同时使用JOIN...USING可以简化JOIN ON的等值连接。

SELECT employee_is,last_name,department_name
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id';

可写成

SELECT employee_is,last_name,department_name
FROM employees e JOIN departments d
USING(department_id);

多表查询课后练习 

显示所有员工的姓名，部门号和部门名称

SELECT e.last_name,e.department_id,d.department_name
FROM employee e LEFT OUTER JOIN departments d
ON e.'department_id' =d.'department_id';

查询90号部门员工的job_id和90号部门的location_id

SELECT e.job_id,d.location_id
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id'
WHERE d.'department_id'=90;

查询所有有奖金的员工的last_name,department_name,location_id,city

SELECT last_name,department_name,location_id,city
FROM employees e LEFT JOIN departments d
ON e.'department_id'=d.'department_id'
LEFT JOIN  locations l
ON d.'location_is'=l.'location_id'
WHERE e.'commission_pct' IS NOT NULL;

选择city在Toronto工作的员工的last_name,job_id,department_id,department_name

 SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e,departments d,locations l
WHERE e.'department_id'=d.'department_id'
AND d.'location_id'=l.'location_id'
AND l.'city'='Toronto';

#sql92语法
SELECT e.last_name, e.job_id, e.department_id, d.department_name
FROM employees e,departments d, locations l
WHERE e.'department_id'=d.'department_id'
AND d.'location_id'=l.'location_id'
AND l.'city'='Toronto';

查询员工所在的部门名称，部门地址，姓名，工作，工资，其中员工所在部门的部门名称为“Execntive”

SELECT d.department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM departments d LEFT JOIN employees e
ON e.'department_id'=d.'department_id'
LEFT JOIN locations l
ON d.'location_id'=l.'location_id'
WHERE d.'department_name'='Executive';

选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式

SELECT emp.last_name "employees", emp.employee_id"Emp#", mgr.last_name"manager", mgr.employee_id"Mgr#"
FROM employees emp LEFT JOIN employees mgr
ON emp.manager_id=mgr.employee_id;

查询哪些部门没有员工

SELECT d.department_id
FROM departments d LEFT JOIN employees e
ON d.'department_id'=e.'department_id'
WHERE e.'department_id' IS NULL;
#本题也可以使用子查询

查询哪个城市没有部门

SELECT location_id, city
FROM locations JOIN departments d
ON l.'location_id'=d.'location_id'
WHERE d.'location_id' IS NULL;

查询部门名为Sales或IT的员工信息

SELECT e.employee_id, e.last_name, e.department_id
FROM employees e JOIN departments d
ON e.'department_id'=d.'department_id'
WHERE d.'department_name' IN ('Sales', 'IT');