数据库>>>服务器>>>Web
DB(database):存储数据的“容器”,有组织的一系列数据;
DBMS(Database Management System):数据库是通过DBMS创造和操作的。MySQL,Oracle…
SQL(structure Query Language):专门用来与数据库通信的语言。(官话 vs 方言)
————————————————
Z6J26_多表查询
如果有n个表实现多表查询,则需要至少n - 1 个连接条件
select e.emplyee_id, e.last_name, d.department_name, l.city
from employee e, departments d, loccation l
where e.'department_id' = d.'department_id' and d.'location_id' = l.'location_id';
Z6J27/28_等值连接 vs 非等值连接,自连接 vs 非自连接, 内连接 vs 外连接
#非等值连接例子
select *
from job_grades;
select e.last_name, e.salary, j.grade_level
from employees e, job_grades j
where e.'salary' between j.'lowest_sal' and j.'highest_sal';
#自连接例子
select *
from employees;
#查询员工id,员工姓名及其管理者的id和姓名
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';
#内连接 vs 外连接
外连接:合并具有同一列的两个以上的表的行,结果除了包含一个表与另一个表匹配的行之外,还查询到了左表 或 右表中不匹配的行。(分为左外连接,右外连接,满外连接)
#查询所有员工! 的last_name, department_name信息
#SQL92语法实现内连接:
select employee_id, department_name
from employees e, departments d
where e.'department_id' = d.'department_id';
#SQL92语法实现外连接:(使用 +)不过mysql不支持sql92外连接方式
select employee_id, department_name
from employees e, departments d
where e.'department_id' = d.'department_id'(+);
#SQL99语法中使用JOIN...ON的方式实现多表查询
#SQL99语法实现内连接:
select employee_id, department_name
from employees e JOIN departments d
ON e.'department_id' = d.'department_id';
select employee_id, department_name, city
from employees e JOIN departments d
ON e.'department_id' = d.'department_id';
JOIN location l
ON d.'location_id' = l.'location_id';
#SQL99语法实现外连接:
#left join ... on 左外连接
#right join ... on 右外连接
select last_name, department_name
from employees e LEFT JOIN departments
ON e.'department_name' = d.'department_name';
#满外连接: full ... join ... on 不过MySQL不支持full满外连接
Z6J29_使用SQL99实现7种JOINs操作(重点!)
UNION关键字的使用
(辅助完成满外连接)
考虑到效率问题,建议使用UNION ALL操作符;
#中图:内连接
select employee_id, department_name
from employees e JOIN departments d
ON e.'department_id' = d.'department_id';
#左上图:左外连接
select employee_id, department_name
from employees e LEFT JOIN departments d
ON e.'department_id' = d.'department_id';
#右上图:右外连接
select employee_id, department_name
from employees e RIGHT JOIN departments d
ON e.'department_id' = d.'department_id';
#左中图
select employee_id, department_name
from employees e LEFT JOIN department d
ON e.'department_id' = d.'department_id'
where d.'depaerment_id' IS NULL;
#右中图
select employee_id, department_name
from employees e RIGHT JOIN department d
ON e.'department_id' = d.'department_id'
where e.'depaerment_id' IS NULL;
#左下图:满外连接
#方式1:左上图 UNION ALL 右中图
select employee_id, department_name
from employees e LEFT JOIN departments d
ON e.'department_id' = d.'department_id'
UNION ALL
select employee_id, department_name
from employees e RIGHT JOIN department d
ON e.'department_id' = d.'department_id'
where e.'depaerment_id' IS NULL;
#方式2:左中图 UNION ALL 右上图
#右下图
#左中图 UNION ALL 右中图
select employee_id, department_name
from employees e LEFT JOIN department d
ON e.'department_id' = d.'department_id'
where d.'depaerment_id' IS NULL
UNION ALL
select employee_id, department_name
from employees e RIGHT JOIN department d
ON e.'department_id' = d.'department_id'
where e.'depaerment_id' IS NULL;
Z6J31_多表查询课后练习
1、’‘显示’'所有员工的姓名,部门号和部门名称
select e.last_name, e.department_id, d.department_name
from employees e left join departmentsgt d
on e.'department_id' = d.'department_id';
2、查询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 e.'department_id' = 90;
3、选择’‘所有’'有奖金的员工的last_name, department_name, location_id, city
select e.last_name, d.department_name, d.location_id, l.city
from employees e left join departments d
on e.'department_id' = d.'department_id'
left join location l
on d.'location_id' = l.'location_id'
where e.'commission_pct' is not null;
4、选择city在Toronto工作的员工的last_name,jon_id,department_id,department_name
select e.last_name, e.jon_id, d.department_id, d.department_name
from employees e join departments d
on e.'department_id' = d.'department_id'
join location l
on d.'location_id' = l.'location_id'
where l.'city' = 'Toronto';
5、查询员工所在的department_name, 部门地址 , last_name, 工作 , salary,其中员工所在部门的部门名称为"Executive"
select d.department_name, l.street_address, e.last_name, e.job_id, e.salary
from employees e join departments d
on e.'department_id' = d.'department_id'
join location l
on d.'location_id' = l.'location_id'
where d.'department_name' = "Executive";
6、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号
select e.last_name "员工", e.employee_id "员工id", m.last_name "管理者", m.employee_id "管理者id"
from employees e left join employees m
on e.'manager_id' = m.'employee_id'
7、查询哪些部门没有员工
select d.department_id
from departmets d left join employees e
on d.'department_id' = e.'department_id'
where e.'department_id' is null
8、查询哪个城市没有部门
select l.city
from location l left join departments d
on l.'location_id' = d.'location_id'
where d.'location_id' is null;
9、查询部门名为 Sales 或 IT 的员工信息
select e.employee_id, e.last_name, d.department_name
from employees e right join departments d
on e.'department_id' = d.'department_id'
where d.'department_name' in ('Sales','IT');