Chapter 7 多表查询
减少冗余，方便管理
主键(Primary Key)：它是关系数据库表中的一列或几列的组合；能够唯一表示一条记录。
实体完整性(Entity Integrity): 主键(Primary Key)不能为空(NULL)。
表的设计者负责定义主键，数据库管理系统负责维护实体完整性。
订单表(Order)
订单号|.......商品号|商品名|商品描述|单价|... 供应商号|供应商名
1NF(第一范式)
1.所有的键属性(列)都已定义
2.没有任何重复组(Repeating Groups),换句话说，每行和每列的交汇处可以而且只能包含一个值，而不是一组值
3.所有的属性(列)都依赖主键
实际是从一个实际的表格中找出字段。
********************************************************************************************
抽出Order表和Product表
Order:订单号|.......商品号
Product:商品号|商品名|商品描述|单价|... 供应商号|供应商名
我认为第一个范式就是：抽表，将关联的属性放置到一个表里面
2NF
1.该表为第一范式
2.该表不包含部分依赖
部分依赖：
商品所有其他部分的信息都依赖于商品号，商品号只是主键的一部分，所以这种依赖关系叫做部分依赖。
找主键，找外键
实际是找出一个可能的表，顺便找出主外键。
********************************************************************************************
3NF
1.该表符合第二范式
2.该表不包含传递依赖
传递依赖：一个或多个属性(列)依赖与非主键的属性(列)
实际是让这个表高度的内聚(成为一个真正独立的表)，不应该有其他表的字段，除了外键。
********************************************************************************************
7.10 相等连接
select e.
from emp e,dept d
where e.
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
where e.deptno = d.deptno
order by d.loc;
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
where e.deptno = d.deptno
order by d.loc
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.sal >= 1500
order by d.loc;
笛卡尔乘积(乘积连接)
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
order by d.loc;
select count(*)
from emp;
select count(*)
from dept;
7.13 自连接(Self join)
标题表：
一级标题，二级标题
select w.empno, w.ename, w.job, w.mgr, m.ename "M_Name", m.job "M_Job"
from emp w, emp m
where w.mgr = m.empno
and w.job like 'ANA%';
select e.ename, e.job, e.mgr
from emp e
where e.job like 'ANA%';
select e.empno, e.ename, e.job
from emp e
where e.empno = 7566;
create table manager
as
select *
from emp;
select * from manager;
select w.empno, w.ename, w.job, w.mgr, m.ename "M_Name", m.job "M_Job"
from emp w, manager m
where w.mgr = m.empno
and w.job like 'ANA%';
多表连接：
select w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary",
m.empno "M_Number", m.ename "M_Name", d.loc "Location"
from emp w, manager m, dept d
where w.mgr = m.empno
and m.deptno = d.deptno
and w.job in ('CLERK','ANALYST')
order by w.job, w.sal;
不等连接
SQL> desc salgrade;
Name Type Nullable Default Comments
----- ------ -------- ------- --------
GRADE NUMBER Y
LOSAL NUMBER Y
HISAL NUMBER Y
SQL> select * from salgrade;
GRADE LOSAL HISAL
----- ----- -----
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
and s.grade > 2;
7.16 外连接
select * from dept;
select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc
from emp e, dept d
where e.deptno(+) = d.deptno
e.deptno(+) = d.deptno 等价于
emp e right join dept d
on e.deptno = d.deptno
以right为基准，到left找数据，找不到显示空
select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc
from emp e, dept d
where d.deptno = e.deptno(+)
select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc
from emp e, dept d
where d.deptno(+) = e.deptno
自然连接(笛卡尔连接)
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e
cross join dept d
order by d.loc
select e.empno, e.ename, e.sal, d.deptno, d.loc
from emp e right join dept d
on e.deptno = d.deptno
order by d.deptno;
使用Using子句的连接
Using只能匹配一列
select e.empno, e.ename, e.sal, deptno, d.loc
from emp e
join dept d
using (deptno)
order by d.loc;
使用ON子句连接
select e.empno, e.ename, e.sal, d.deptno, d.loc
from emp e join dept d
on (e.deptno = d.deptno)
order by d.loc;
select w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary",
m.empno "M_Number", m.ename "M_Name", d.loc "Location"
from
emp w
join manager m
on w.mgr = m.empno
join dept d
on m.deptno = d.deptno
where w.job in ('CLERK','ANALYST')
order by w.job, w.sal;
select w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary",
m.empno "M_Number", m.ename "M_Name", d.loc "Location"
from
emp w
join manager m
on w.mgr = m.empno
join dept d
on m.deptno = d.deptno
and w.job in ('CLERK','ANALYST')
order by w.job, w.sal;
左外连接
select empno,ename,sal,e.deptno,d.deptno,d.loc
from dept d
left outer join emp e
on (e.deptno = d.deptno)
select empno,ename,sal,e.deptno,d.deptno,d.loc
from dept d
right outer join emp e
on (e.deptno = d.deptno)
update manager
set deptno = NULL
where deptno = 30;
commit;
select * from manager;
select manager.empno,manager.ename,manager.sal,manager.deptno,dept.deptno,dept.loc
from manager
left outer join dept
on (manager.deptno = dept.deptno)
select empno,ename,sal,manager.deptno,dept.deptno,loc
from manager, dept
where manager.deptno = dept.deptno(+)
order by manager.deptno
select manager.empno,manager.ename,manager.sal,manager.deptno,dept.deptno,dept.loc
from manager
right outer join dept
on (manager.deptno = dept.deptno)
select empno,ename,sal,manager.deptno,dept.deptno,loc
from manager, dept
where manager.deptno(+) = dept.deptno
order by manager.deptno
select manager.empno,manager.ename,manager.sal,manager.deptno,dept.deptno,dept.loc
from manager
full outer join dept
on (manager.deptno = dept.deptno)
减少冗余，方便管理
主键(Primary Key)：它是关系数据库表中的一列或几列的组合；能够唯一表示一条记录。
实体完整性(Entity Integrity): 主键(Primary Key)不能为空(NULL)。
表的设计者负责定义主键，数据库管理系统负责维护实体完整性。
订单表(Order)
订单号|.......商品号|商品名|商品描述|单价|... 供应商号|供应商名
1NF(第一范式)
1.所有的键属性(列)都已定义
2.没有任何重复组(Repeating Groups),换句话说，每行和每列的交汇处可以而且只能包含一个值，而不是一组值
3.所有的属性(列)都依赖主键
实际是从一个实际的表格中找出字段。
********************************************************************************************
抽出Order表和Product表
Order:订单号|.......商品号
Product:商品号|商品名|商品描述|单价|... 供应商号|供应商名
我认为第一个范式就是：抽表，将关联的属性放置到一个表里面
2NF
1.该表为第一范式
2.该表不包含部分依赖
部分依赖：
商品所有其他部分的信息都依赖于商品号，商品号只是主键的一部分，所以这种依赖关系叫做部分依赖。
找主键，找外键
实际是找出一个可能的表，顺便找出主外键。
********************************************************************************************
3NF
1.该表符合第二范式
2.该表不包含传递依赖
传递依赖：一个或多个属性(列)依赖与非主键的属性(列)
实际是让这个表高度的内聚(成为一个真正独立的表)，不应该有其他表的字段，除了外键。
********************************************************************************************
7.10 相等连接
select e.
from emp e,dept d
where e.
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
where e.deptno = d.deptno
order by d.loc;
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
where e.deptno = d.deptno
order by d.loc
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
where e.deptno = d.deptno
and e.sal >= 1500
order by d.loc;
笛卡尔乘积(乘积连接)
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e,dept d
order by d.loc;
select count(*)
from emp;
select count(*)
from dept;
7.13 自连接(Self join)
标题表：
一级标题，二级标题
select w.empno, w.ename, w.job, w.mgr, m.ename "M_Name", m.job "M_Job"
from emp w, emp m
where w.mgr = m.empno
and w.job like 'ANA%';
select e.ename, e.job, e.mgr
from emp e
where e.job like 'ANA%';
select e.empno, e.ename, e.job
from emp e
where e.empno = 7566;
create table manager
as
select *
from emp;
select * from manager;
select w.empno, w.ename, w.job, w.mgr, m.ename "M_Name", m.job "M_Job"
from emp w, manager m
where w.mgr = m.empno
and w.job like 'ANA%';
多表连接：
select w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary",
m.empno "M_Number", m.ename "M_Name", d.loc "Location"
from emp w, manager m, dept d
where w.mgr = m.empno
and m.deptno = d.deptno
and w.job in ('CLERK','ANALYST')
order by w.job, w.sal;
不等连接
SQL> desc salgrade;
Name Type Nullable Default Comments
----- ------ -------- ------- --------
GRADE NUMBER Y
LOSAL NUMBER Y
HISAL NUMBER Y
SQL> select * from salgrade;
GRADE LOSAL HISAL
----- ----- -----
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
select e.empno, e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
and s.grade > 2;
7.16 外连接
select * from dept;
select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc
from emp e, dept d
where e.deptno(+) = d.deptno
e.deptno(+) = d.deptno 等价于
emp e right join dept d
on e.deptno = d.deptno
以right为基准，到left找数据，找不到显示空
select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc
from emp e, dept d
where d.deptno = e.deptno(+)
select e.empno, e.ename, e.sal, e.deptno, d.deptno, d.loc
from emp e, dept d
where d.deptno(+) = e.deptno
自然连接(笛卡尔连接)
select e.empno, e.ename, e.sal, e.deptno, d.loc
from emp e
cross join dept d
order by d.loc
select e.empno, e.ename, e.sal, d.deptno, d.loc
from emp e right join dept d
on e.deptno = d.deptno
order by d.deptno;
使用Using子句的连接
Using只能匹配一列
select e.empno, e.ename, e.sal, deptno, d.loc
from emp e
join dept d
using (deptno)
order by d.loc;
使用ON子句连接
select e.empno, e.ename, e.sal, d.deptno, d.loc
from emp e join dept d
on (e.deptno = d.deptno)
order by d.loc;
select w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary",
m.empno "M_Number", m.ename "M_Name", d.loc "Location"
from
emp w
join manager m
on w.mgr = m.empno
join dept d
on m.deptno = d.deptno
where w.job in ('CLERK','ANALYST')
order by w.job, w.sal;
select w.empno "W_Number", w.ename "W_Name", w.job "W_Job", w.sal "W_Salary",
m.empno "M_Number", m.ename "M_Name", d.loc "Location"
from
emp w
join manager m
on w.mgr = m.empno
join dept d
on m.deptno = d.deptno
and w.job in ('CLERK','ANALYST')
order by w.job, w.sal;
左外连接
select empno,ename,sal,e.deptno,d.deptno,d.loc
from dept d
left outer join emp e
on (e.deptno = d.deptno)
select empno,ename,sal,e.deptno,d.deptno,d.loc
from dept d
right outer join emp e
on (e.deptno = d.deptno)
update manager
set deptno = NULL
where deptno = 30;
commit;
select * from manager;
select manager.empno,manager.ename,manager.sal,manager.deptno,dept.deptno,dept.loc
from manager
left outer join dept
on (manager.deptno = dept.deptno)
select empno,ename,sal,manager.deptno,dept.deptno,loc
from manager, dept
where manager.deptno = dept.deptno(+)
order by manager.deptno
select manager.empno,manager.ename,manager.sal,manager.deptno,dept.deptno,dept.loc
from manager
right outer join dept
on (manager.deptno = dept.deptno)
select empno,ename,sal,manager.deptno,dept.deptno,loc
from manager, dept
where manager.deptno(+) = dept.deptno
order by manager.deptno
select manager.empno,manager.ename,manager.sal,manager.deptno,dept.deptno,dept.loc
from manager
full outer join dept
on (manager.deptno = dept.deptno)
