oracle数据库 左连接 右连接 自连接 。。

附件数据库 dept.dmp 用户名 mytestdb 密码 123

-- Create table

create table DEPT

(

  DEPTNO NUMBER not null,

  DNAME  VARCHAR2(30),

  LOC    VARCHAR2(30)

)

tablespace USERS

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64

    next 1

    minextents 1

    maxextents unlimited

  );

-- Add comments to the columns 

comment on column DEPT.DEPTNO

  is '部门编号';

comment on column DEPT.DNAME

  is '部门名称';

comment on column DEPT.LOC

  is '地址';

-- Create/Recreate primary, unique and foreign key constraints 

alter table DEPT

  add constraint DEPTNO_KEY primary key (DEPTNO)

  using index 

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited

  );

 

 

-- Create table

create table EMP

(

  EMPNO    NUMBER not null,

  ENAME    VARCHAR2(20),

  JOB      VARCHAR2(30),

  MGR      NUMBER,

  HIREDATE VARCHAR2(20),

  SAL      NUMBER,

  COMM     NUMBER,

  DEPTNO   NUMBER

)

tablespace USERS

  pctfree 10

  initrans 1

  maxtrans 255

  storage

  (

    initial 64

    next 1

    minextents 1

    maxextents unlimited

  );

-- Add comments to the columns 

comment on column EMP.EMPNO

  is '员工编号';

comment on column EMP.ENAME

  is '员工名称';

comment on column EMP.JOB

  is '工作';

comment on column EMP.MGR

  is '领导编号';

comment on column EMP.HIREDATE

  is '工作年限';

comment on column EMP.SAL

  is '薪水';

comment on column EMP.DEPTNO

  is '部门编号';

-- Create/Recreate primary, unique and foreign key constraints 

alter table EMP

  add constraint EMPNO_KEY primary key (EMPNO)

  using index 

  tablespace USERS

  pctfree 10

  initrans 2

  maxtrans 255

  storage

  (

    initial 64K

    next 1M

    minextents 1

    maxextents unlimited

  );

alter table EMP

  add constraint EMP_DOP_FK foreign key (DEPTNO)

  references DEPT (DEPTNO) on delete cascade;

 

 

 

--使用相等链接  查询 员工名称 薪水 和 部门

select ename 员工名称,sal 薪水,dept.deptno 部门 from emp,dept where emp.deptno=dept.deptno

 

select ename 员工名称,sal 薪水,dept.deptno 部门编号,dept.dname from emp,dept where emp.deptno=dept.deptno and dept.deptno=10

--加and条件

select e.ename,d.loc from emp e,dept d where e.deptno=d.deptno and e.deptno=30 

--自连接

select worker.ename||'''s manager is '|| manager.ename from emp worker,emp manager  where worker.mgr=manager.empno

--不等连接

select * from emp f  where f.sal between  1000 and 3000 

select * from emp f  where f.sal between  f.sal and f.sal

 

--外连接

select dept.dname,emp.ename from dept,emp where dept.deptno=emp.deptno(+) and emp.deptno(+)=10

 

--Cross join链接  交叉

select d.deptno,e.ename from dept d cross join emp e

--Natural join链接   自然

select e.ename,e.sal,d.dname from dept d natural join emp e

--Natural join链接 

--如果两张表具有多个同列名那么当建立natural join链接时。Oracle会基于多个同名列进行相等链接，如果只希望使用某个同名列执行相等连接那么需要使用using子句

select d.dname,e.ename from dept d join emp e using(deptno)

 

--On子句

select e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno and e.deptno=10

--左链接 left join

select a.dname,e.ename from dept a left join emp e on a.deptno=e.deptno and a.deptno=10

--右链接 right join

select d.dname,e.ename from dept d right join emp e on d.deptno=e.deptno and d.deptno=10

 

select d.dname,e.ename from dept d , emp e where d.deptno(+)=e.deptno and d.deptno(+)=10