oracle inner join | outer join | full join | add constraint | drop constraint | disable constraint | enable constraint

標籤：1對多   tab   級聯   入職   左串連   關聯   not   prim   column   

--建表語句
create table SCOTT.DEPT
(
deptno NUMBER(2) not null,
dname VARCHAR2(15),
loc VARCHAR2(13)
)
tablespace USERS
;
comment on table dept
  is ‘部門表‘;
comment on column deptno
  is ‘部門編號‘;
comment on column loc
  is ‘部門所在地‘；

--add primary key constraint
alter table dept
  add constraint pk_dept primary key (deptno);

create table SCOTT.EMP
(
empno NUMBER(4) not null,
ename VARCHAR2(10),
age NUMBER(3),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
)
tablespace USERS
;

create table SCOTT.EMP
(
empno NUMBER(4) primary key not null, --add primary key constraint, not null constraint
ename VARCHAR2(10) not null unique, --add unique constraint
age NUMBER(3) not null check(between 0 and 150), --add check constraint
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
constraint fk_emp_deptno foreign key (deptno) references dept (deptno) --add foreign key constraint
)
tablespace USERS
;
comment on table emp
  is ‘員工資訊表‘;
comment on column empno
  is ‘員工編號‘;
comment on column ename
  is ‘員工姓名‘;
comment on column job
  is ‘員工職位‘;
comment on column mgr
  is ‘直接上級編號‘;
comment on column hiredate
  is ‘入職日期‘;
comment on column sal
  is ‘員工薪水‘;
comment on column deptno
  is ‘部門編號‘;

--建表的時候添加外鍵約束, 加了串聯刪除
constraint fk_emp_deptno foreign key (deptno) references dept (deptno) on delete cascade

--add primary key constraint
alter table emp
  add constraint pk_emp primary key (empno);

--add foreign key constraint
alter table emp
  add constraint fk_emp foreign key (deptno)
  references dept (deptno);

--添加外鍵約束, 帶串聯刪除
alter table emp
  add constraint fk_emp_deptno foreign key (deptno)
  references dept (deptno)
  on delete cascade;

--add unique key constraint
alter table emp
  add constraint uk_emp_name unique (ename);

--add check constraint
alter table emp
  add constraint ck_emp_age check (age between 0 and 150);

--add not null constraint
alter table emp
  modify ename not null;

--drop not null contraint
alter table emp
  modify ename null;

--drop constraint
alter table emp
  drop constraint unique (name);

alter table emp
  drop constraint fk_emp_deptno;

--禁用約束
alter table emp
  disable constraint fk_emp_deptno;

--啟用約束
alter table emp
  enable constraint fk_emp_deptno;

--oracle 的串連分為內串連(inner join) 外串連(outer join) 全串連(full join)
--1.inner join, 交集
select * from A inner join B on A.field1 = B.field2;

select * from A, B where A.field1 = B.field2;

--內串連查詢能查詢出匹配的記錄, 匹配不上的記錄查詢不出來

select * from dept inner join emp on dept.deptno = emp.deptno;

select * from dept, emp where dept.deptno = emp.deptno;

--2.outer join ,外串連, 可以分為左外串連 left outer join 右外串連 right outer join
--要求: (1).2張表, 部門表和員工表, 一個部門有多個員工, 一個員工只能對應一個部門, 部門和員工是1對多的關係
--(2)部門表和員工表進行關聯查詢,並要查詢出部門的所有資訊
--採用左串連查詢, 左串連查詢以left join 前面的表為主表, 即使記錄關聯不上, 主表的資訊也能夠查詢出來
select * from A left join B on A.field1 = B.field2;

select * from dept left join emp dept.deptno = emp.deptno;

select * from A, B where A.field1 = B.field2(+);

select * from dept, emp where dept.deptno = emp.deptno(+);

--部門表和員工表進行關聯查詢,並要查詢出員工表的所有資訊
--右串連以 right join 後面的表為主表, 即使記錄關聯不上, 主表的資訊也能夠查詢出來

select * from A right join B on A.field1 = B.field2;

select * from dept right join emp on dept.deptno = emp.deptno;

--外串連就是在關聯不上的時候, 把其中的部分資訊查詢出來

--全串連 full join , full join ... on ...,全串連的查詢結果: 左外串連和右外串連查詢結果的並集,
--即使一些記錄關聯不上, 也能把部分資訊查詢出來
select * from A full join B on A.field1 = B.field2;

select * from dept full join emp on dept.deptno = emp.deptno;

select * from dept full join emp on 1 = 1;

oracle inner join | outer join | full join | add constraint | drop constraint | disable constraint | enable constraint