oracle多表简单查询实例代码-木庄网络博客

本文整理自网络，侵删。

多表查询基本语法

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。

-- 笛卡尔积 
select * from emp, dept;

oracle多表查询

prompt Importing table dept...
set feedback off
set define off
insert into dept (DEPTNO, DNAME, LOC)
values (1, '01事业部', '324');
 
insert into dept (DEPTNO, DNAME, LOC)
values (2, '02事业部', '234');
 
insert into dept (DEPTNO, DNAME, LOC)
values (3, '03事业部', '234');
 
insert into dept (DEPTNO, DNAME, LOC)
values (4, '04事业部', '3244');
 
prompt Done.

prompt Importing table emp...
set feedback off
set define off
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小明', '运维', '9000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '1');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小刚', 'hr', '7000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '1');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小花', 'vue开发', '6000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '1');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小贝', '软件工程师', '9000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '2');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小涛', '数据中台', '11000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '2');
 
insert into emp (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values ('001', '小胜', '设计', '9000', to_date('26-09-2021', 'dd-mm-yyyy'), 900, 800, '2');
 
prompt Done.

prompt
 
prompt Creating table DEPT
prompt ===================
prompt
 
create table USERNAME.DEPT
(
  deptno NUMBER not null,
  dname  VARCHAR2(10) not null,
  loc    VARCHAR2(10) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
 
prompt
 
prompt Creating table EMP
prompt ==================
prompt
 
create table USERNAME.EMP
(
  empno    NVARCHAR2(20),
  ename    NVARCHAR2(20),
  job      NVARCHAR2(20),
  mgr      NVARCHAR2(20),
  hiredate DATE,
  sal      BINARY_DOUBLE,
  comm     BINARY_DOUBLE,
  deptno   NVARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
 
 
prompt Done
spool off
set define on

多表查询

阅读剩余部分