**mysql多表查询**
#多表的查询如何实现
SELECT employee_id,department_name
FROM employees,departments
#查询出来了2889条记录
SELECT * FROM employees; #查询出107条记录
SELECT 2889/107
FROM DUAL; #结果是27
SELECT * FROM departments; #可以查询到有27个部门
#多表查询的正确方式:要有多表的连接条件
SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id
=departments.department_id
;
#查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.department_id
=d.department_id
AND d.location_id
=l.location_id
;
#非等值连接的例子
SELECT *
FROM job_grades;
#查询员工姓名以及其管理者的姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id
=mgr.employee_id
;
#练习:查询所有员工的last_name,department_name信息
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id
=departments.department_id
;
#SQL92语法实现内连接如上
#sql92实现外连接在mysql上不能跑,可以使用oracle来实现语句如下
#SQL92语法实现左外连接:右面要使用+ 如下
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id
=departments.department_id
(+);
#SQL92语法实现右外连接:左面要使用+ 如下
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id
(+)=departments.department_id
;
#sql99语法实现内连接
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id
=d.department_id
;
SELECT last_name,department_name,city
FROM employees e
JOIN departments d
ON e.department_id
=d.department_id
JOIN locations l
ON d.location_id
=l.location_id
;
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id
=d.department_id
JOIN locations l
ON d.location_id
=l.location_id
#sql99语法实现外连接+
#左外连接
SELECT last_name,department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id
=d.department_id
;
#右外连接
SELECT last_name,department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id
=d.department_id
;
#各种连接
#内连接
SELECT employee_id,last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id
=d.department_id
;
#左外连接
SELECT employee_id,last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id
=d.department_id
;
#右外连接
SELECT employee_id,last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id
=d.department_id
;
#只有左边:要用右边表的字段is null
SELECT employee_id,last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id
=d.department_id
WHERE d.department_id
IS NULL;
#只有右边:要用左边表的字段is null
SELECT employee_id,last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id
=d.department_id
WHERE e.department_id
IS NULL;
#满外连接:左外连union all上只有右边
SELECT employee_id,last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id
=d.department_id
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id
=d.department_id
WHERE e.department_id
IS NULL;
#满外连接:右外连 union all上只有左边
SELECT employee_id,last_name,department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id
=d.department_id
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id
=d.department_id
WHERE d.department_id
IS NULL;
#sqL 99新特性:自然连接(natural join) ,一般不用,其必须满足相同字段等值的内容才显示
SELECT last_name,department_name
FROM employees e
NATURAL JOIN departments d
#sqL 99新特性:using join
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id
=d.department_id
;