Oracle 11g 第四章知识点总结——多表查询
知识点预览
多表查询
多表查询
1. 从多个表中获取获取数据
2. 笛卡尔集
a) 笛卡尔集会在下面条件下产生:
省略连接条件
连接条件无效
所有表中的所有行互相连接
b) 为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件。
3. 连接的类型
a) Oracle 提供的连接 (8i 或更早):
i. Equijoin
ii. Non-equijoin
iii. Outer join
iv. Self join
b) 适用于SQL: 1999的连接
i. Cross joins
ii. Natural joins
iii. Using clause
iv. Full or two sided outer joins
v. Arbitrary join conditions forouter joins
4. Oracle 连接
a) 使用连接在多个表中查询数据。
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
b) 在 WHERE 字句中写入连接条件。
c) 在表中有相同列时，在列名之前加上表名前缀。
5. 等值连接
SELECT employees.employee_id,employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
6. 多个连接条件与 AND 操作符
7. 区分重复的列名
a) 使用表名前缀在多个表中区分相同的列。
b) 使用表名可以提高效率。
c) 在不同表中具有相同列名的列可以用别名加以区分。
8. 表的别名
a) 使用别名可以简化查询。
b) 使用表名前缀可以提高执行效率。
SELECTe.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;
9. 连接多个表
连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。
10. 非等值连接
SELECTe.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
11. 外连接
a) 外连接语法
使用外连接可以查询不满足连接条件的数据。
外连接的符号是 (+)。
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column(+)= table2.column;
SELECT table1.column,table2.column
FROM table1,table2
WHERE table1.column= table2.column(+);
SELECTe.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id ;
12. 自连接
SELECTworker.last_name || ' works for '
|| manager.last_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;
13. 使用SQL: 1999 语法连接
使用连接从多个表中查询数据：
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2]|
[NATURAL JOINtable2] |
[JOIN table2USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULLOUTER JOIN table2
ON (table1.column_name = table2.column_name)];
14. 叉集
a) 使用CROSS JOIN 子句使连接的表产生叉集。
b) 叉集和笛卡尔集是相同的。
SELECTlast_name, department_name
FROM employees
CROSS JOINdepartments ;
15. 自然连接
a) NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
b) 在表中查询满足等值条件的数据。
c) 如果只是列名相同而数据类型不同，则会产生错误。
SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;
16. 使用 USING 子句创建连接
a) 在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。
b) 使用 USING 可以在有多个列满足条件时进行选择。
c) 不要给选中的列中加上表名前缀或别名。
d) NATURAL JOIN 和 USING 子句经常同时使用。
SELECT e.employee_id, e.last_name, d.location_id
FROM employees e JOINdepartments d
USING (department_id) ;
17. 使用ON 子句创建连接
a) 自然连接中是以具有相同名字的列为连接条件的。
b) 可以使用 ON 子句指定额外的连接条件。
c) 这个连接条件是与其它条件分开的。
d) ON 子句使语句具有更高的易读性。
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id,d.location_id
FROM employees e JOINdepartments d
ON (e.department_id =d.department_id);
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id =e.department_id
JOIN locations l
ON d.location_id =l.location_id;
18. 内连接 与 外连接
a) 在SQL: 1999中，内连接只返回满足连接条件的数据。
b) 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外联接。
c) 两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为满 外联接。
19. 左外联接
SELECTe.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOINdepartments d
ON (e.department_id = d.department_id) ;
20. 右外联接
SELECTe.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOINdepartments d
ON (e.department_id = d.department_id) ;
21. 满外联接
SELECTe.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOINdepartments d
ON (e.department_id = d.department_id) ;
22. 增加连接条件
SELECTe.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;
23. 示例
a) 每个子查询当做临时表
多表查询 分步思想 化整为零思想
b) 查询SALES部门的所有雇员的经理名字
c) 查询雇员所在部门的部门名称
1).selectdeptno from emp;
2).selecte.deptno from emp e, dept d;
3).select e.ename,e.deptno,d.dname from emp e, dept d
Wheree.detno=d.deptno;
d) 笛卡尔积
中国队
美国队
巴西队
日本队
中国- 美国
中国- 巴西
中国- 日本
笛卡尔积：
select c1.name,c2.name
from t_c c1,t_c c2
消除笛卡尔积
select c1.name,c2.name
from t_c c1,t_c c2
where c1.name != c2.name;
等值连接
自连接
e) 查询每个雇员的经理名字
select e.empno,e.ename,e.mgr,m.empno,m.ename
from emp e,emp m
where e.mgr=m.empno;
f) 查询编号是10的所有雇员的经理名字
g) 查询SALES部门的所有雇员的经理名字
h) 查询部门名称是SALES的部门编号
Select deptno from dept Where dname=’SALES’
selecte.ename,m.ename,e.deptno,d.dname
fromemp e,emp m,(
Select deptno,dname
from dept
Where dname='SALES'
) d
where e.mgr=m.empno ande.deptno=d.deptno;
i) 找出没有经理的那个人所在的部门以及该部门所在的地区城市（显示该人的名字和所在部门名字和地区名字） hr用户
分析：
--找出没有经理的雇员
Select* from employees
Where manager_id is null;
--找到这个人所在的部门名称
Selecte.first_name|| '' || e.last_name,d.department_name
From departments d,(
Select* from employees
Wheremanager_id is null) e
wheree.department_id=d.department_id;
--显示该人所在的城市
--整合
j) 查询出在“Americas”这个国家里的所有员工以及其所在的部门
分析：
--查询出“Americas”所在的地区id
Select region_id from regions whereregion_name = ‘Americas’;
--查询出地区id是上述结果的这个国家的ID
selectc.country_id,c.country_name,r.region_name
from countries c,(
Selectregion_id,region_name
fromregions
whereregion_name = 'Americas') r
wherec.region_id=r.region_id;
--查询出有哪些地方在上一步的范围里
selectl.city,l.location_id,t1.country_name,t1.region_name
from locations l,
(selectc.country_id cid,c.country_name,r.region_name
fromcountries c,(
Selectregion_id,region_name
fromregions
whereregion_name = 'Americas') r
wherec.region_id=r.region_id) t1
wherel.country_id=t1.cid;
--查询落在上步结果里的部门
selectd.department_id,d.department_name,t2.city,t2.country_name,t2.region_name
from departments d ,(
selectl.city,l.location_id,t1.country_name,t1.region_name
fromlocations l,
(selectc.country_id cid,c.country_name,r.region_name
fromcountries c,(
Selectregion_id,region_name
fromregions
whereregion_name = 'Americas') r
wherec.region_id=r.region_id) t1
wherel.country_id=t1.cid
) t2
whered.location_id=t2.location_id;
--用等值连接实现：
selecte.first_name,d.department_name,l.city
from employees e, departmentsd,locations l,countries c,regions r
where e.department_id = d.department_id and d.location_id=l.location_idand l.country_id=c.country_id and c.region_id=r.region_id;
k) hr用户解锁，并设置密码123
解锁 以管理员身份登录sys system
Alter user hr account unlock;
设置密码
Alteruser hr identified by 123;
Conn hr/123;