Oracle数据库 表连接与表设计

一、99语法--表连接，rowid与rownum

（一）99语法--表连接

1、交叉连接cross join --->笛卡尔积

select * from emp cross join dept;

2、自然连接(主外键、同名列) natural join -->等值连接

select * from emp natural join dept;

--在指定列过程中同名列归共同所有(*除外)

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

3、连接(同名列) -->等值连接

--jion using(等值连接字段名) 当存在多个同名字段,可以指明使用哪一个做等值连接

select ename,sal,deptno from emp join dept using(deptno);

4、join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分

-- 数据来源1 join 数据来源2 on 连接条件 ; 即可以实现等值连接 可以实现非等值连接

select * from emp e join dept d on e.deptno = d.deptno;

--非等值连接

--查询员工信息以及每一个员工的薪资等级

select * from emp e join salgrade s on e.sal between s.losal and s.hisal;

-- 查询非20部门并且薪资>1500的员工信息以及薪资等级信息

select *

from emp e

join salgrade s

on e.sal between s.losal and s.hisal

where e.deptno != 20

and sal > 1500

order by sal desc;

5、join on|using -->外连接

--想要某张表中不满足连接条件的数据都显示,把这张表定义为主表

--左外 left join

--右外 right join

select * from emp e1 right join emp e2 on e1.mgr = e2.empno;

6、full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出现一次

select * from emp e1 full join emp e2 on e1.mgr = e2.empno; -- 两张表都作为主表

（二）rowid 与 rownum

ROWID 是 ORACLE 中的一个重要的概念。用于定位数据库中一条记录的一个 相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。 ROWID 它是一个伪列，它并不实际存在于表中。它是 ORACLE 在读取表中数据行时， 根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的 ROWID 能 找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是 通过 ROWID 来完成的，而且使用 ROWID 来进行单记录定位速度是最快的。我们可以将其用于删除重复数据。

ROWNUM 是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的 结果集的顺序号 ，每一个结果集 都有自己顺序号 ，不能直接查询大于 1 的数。利用 ROWNUM，我们可以生产一些原先难以实现的结果输出。 例如实现 分页 操作。 ps: oracle 中 索引从 1 开始，java 程序 从 0 开始。

1、rowid

实现重复记录的删除

要求：删除重复记录，一条记录只保留一次

思路->将所有记录按照某种特定规律分组（相同的记录为一组），保留下每组中的一

条记录即可，其他记录删除

1）找出重复数据 :哪个学生 哪门课重复了

select name,course,count(1) from tb_student group by name,course;

select name,course,count(1) from tb_student group by name,course having count(1)>1;

2）删除重复数据 :删除重复记录

-- 每条记录的唯一标识

select s.* , rowid from tb_student s;

--找出 保留的rowid

select min(rowid) from tb_student group by name,course;

--删除

delete from tb_student where rowid not in (select min(rowid) from tb_student group by name,course);

2、rownum

--规律: 把已确定的结果集中的数据从第一个开始 设置rownum,从1开始,依次+1

--优点: 有规律,规律可循,是数字,可以进行判断和分页操作

rownum :1）必须排序 2）不能直接取大于 1 的数

举个栗子：

--最底层 rownum 数据库默认顺序号 -->没有用的

select emp.*, rownum from emp；

select emp.*, rownum from emp order by sal ;

--自己 排序后结果集的顺序号

select e.*, rownum from (select * from emp order by sal desc) e;

--取出工资前5名

select e.*, rownum

from (select * from emp order by sal desc) e

where rownum <= 5;

--实现分页查询

select empno,ename,rownum from emp where rownum <=5;

--如果在使用rownum值判断之前就已经确定了一个结果集,这个结果集中的rownum就是已经确定的

--再嵌套一个select

select empno,ename,rownum n from emp; --数据来源 确定rownum

select *

from (select empno, ename, rownum n from emp)

where n >= 5

and n <= 10; --where中rownum要使用别名,确定内部select语句的字段,如果直接写rownum,被认为是外部select语句的

--如果存在排序,rownum的序号可能出现问题(乱号),如果:根据主键进行order by,先排序再rownum,如果根据其他字段排序,一般会先rownum,再order by

select deptno,dname,rownum from dept order by deptno;

select empno,ename,deptno,rownum from emp order by deptno;

--如果rownum乱掉怎么办?

--解决方案: 外层嵌套一个select

select empno,ename,deptno,rownum num from emp order by deptno;

--以确定的有规律的rownum,如果判断,根据这个rownum判断,把当前select当做数据源使用

select empno, ename, deptno, rownum 外层的rownum, num 内层rownum

from (select empno, ename, deptno, rownum num from emp order by deptno);

--分页

select * from (select empno, ename, deptno, rownum n, num 内层rownum

from (select empno, ename, deptno, rownum num from emp order by deptno))

where n>5;

二、视图与索引

（一）视图

视图:建立在表|结果集|视图上的虚拟表，有以下作用

1、简化:select 查询语句

2、重用:封装select语句 命名

3、隐藏:内部细节

4、区分:相同数据不同查询

不是所有的用户都有创建视图的权限

1、前提： create view -->组 connect resource dba

2、授权: -->sqlplus /nolog

a)、sys登录 conn sys/123456@orcl as sysdba

b)、授权: grant dba to scott;

回收: revoke dba from scott;

c)、重新登录

create or replace view 视图名 as select语句 [with read only];

要求:所有列必须存在名称。

对视图的删除不会删除原有表的数据

drop view 视图名;

以下为操作过程：

-- 视图：

-- 建立在表和结果集之间的就是视图，其实也是结果集，但是这个结果集可以被存储，以后可以查询视图中的数据

-- 最大的优点：就是封装，简化sql

-- 合理使用视图，不要过于使用

select empno,ename,sal,deptno from emp where deptno in (20,30);

-- create or replace view 视图名 as 结果集 with read only;

create or replace view vw_emp as select empno,ename,sal,deptno from emp where deptno in (20,30) with read only;

select * from vw_emp;

-- 如果权限不够

-- 切换管理员sys用户

-- 进行授权：grant dba to scott;

-- 回收：revoke dba from scott;

grant dba to scott;

（二）索引

索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。

索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

索引一旦建立，在表上进行DML 操作时（例如在执行插入、修改或者删除相关操作时），oracle 会自动管理索引，索引删除，不会对表产生影响。

索引对用户是透明的，无论表上是否有索引，sql 语句的用法不变。

oracle 创建主键时会自动在该列上创建索引。

索引: 提高查询速度的一种手段 -->目录

1、唯一性较好字段适合建立索引

2、大数据量才有效果

3、主键|唯一: 唯一索引

create index 索引名 on表名 (字段列表...)

drop index 索引名

create index idx_emp on emp(sal,ename);

drop index idx_emp;

select * from emp order by sal,ename;

三、设计表

设计表首先应该按需遵循三范式

--表与表之间的关系: 一对一 一对多|多对一(主外键) 多对多{中间表}

--表 表名 字段 约束 表与表之间的关系

1）确定表名 2）确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)

主键：唯一标识一条记录（唯一并且非空）

唯一：唯一

非空：不能为空

默认：当没给值时使用给定一个默认值

外键：参考其他表（自己）的某个（某些）字段

检查：自定义的规则

--创建表与约束问题

--1)创建表的同时不创建约束, 结束后追加约束

--2)创建表的同时为字段添加约束

--3)创建表的结构结束之间添加约束

--约束的添加: 1)物理约束 :表中字段上添加 2)逻辑约束:java代码上使用逻辑判断

（一） 创建表（不加约束）

表名必须唯一，如果存在，必须删除

--1)创建表的同时不添加约束

/*

create table 表名(

字段 字段类型,

字段 字段类型,

....

)

*/

--分析:尚学堂 : 教师表 班级表 学生表 ...

--学生表

create table sxt_student(

sid number(5), --5代表有效数字 (5,2)其中2为是小数位

sname varchar2(15), --可变长字符 默认字节个数 指明字符个数:(5 char)

sage number(3),

sgender char(1 char), --定长字符

hiredate date

)

--删除表

drop table sxt_student;

select * from sxt_student;

-- 添加测试数据

insert into sxt_student values(01,'迪丽热巴',25,'女',sysdate,020);

insert into sxt_student values(02,'胡歌',27,'男',sysdate,020);

insert into sxt_student values(03,'刘德华',40,'男',sysdate,022);

insert into sxt_student values(04,'铁臂阿童木',41,'男',sysdate,020);

insert into sxt_student values(05,'神奇女侠',1,'女',sysdate,022);

insert into sxt_student(sid) values(04);

（二）创建表（同时创建约束+默认名称）

-- 创建表的同时，添加约束 1)字段后直接添加约束，没有约束语 2)字段后直接添加约束，包括约束名

create table sxt_student(

-- 学生编号 主键约束

sid number(5) primary key,

-- 学生姓名 非空、唯一

sname varchar2(15) not null unique,

-- 年龄 检查约束0-150

sage number(3) check(sage between 0 and 45),

-- 性别 检查约束 '男'&'女'

sgender char(1 char) check(sgender in('男','女')),

-- 入学日期 默认值 sysdate

hiredate date default(sysdate),

cid number(5)

)

--加入注释

comment on table sxt_student is '尚学堂学生表';

comment on column sxt_student.sid is '学号,主键';

comment on column sxt_student.sname is '学生姓名';

comment on column sxt_student.sage is '年龄';

comment on column sxt_student.sgender is '性别';

comment on column sxt_student.hiredate is '入学日期';

comment on column sxt_student.cid is '班号';

（三）创建表（同时创建约束+指定名称）

创建表的同时创建约束并指定约束的名称，后期方便排错，推荐使用

-- 字段后添加约束并指定约束名

create table sxt_student(

-- 学生编号 主键约束

sid number(5) constraints pk_sxt_studnet_sid primary key,

-- 学生姓名 非空

sname varchar2(15) constraints sxt_student_sname_notnull not null,

-- 年龄 检查约束0-150

sage number(3) check(sage between 0 and 45),

-- 性别 检查约束 '男'&'女'

sgender char(1 char),

-- 入学日期 默认值 sysdate

hiredate date default(sysdate),

cid number(5),

-- 创建表结构结束前 添加约束

-- constraints pk_sgender check(sgender in('男','女')), -- 添加检查约束

constraints sxt_student_sname_unique unique(sname) -- 添加唯一约束

)

-- 后续追加约束

alter table sxt_student add constraints pk_sgender check(sgender in('男','女'));

-- 删除约束

alter table sxt_student drop constraints sxt_student_sname_notnull;

（四）约束的禁用与启用

ALTER TABLE tb_user disable constraint nn_user_name;

ALTER TABLE tb_user enable constraint nn_user_name;

（五）删除约束

alter table tb_user drop constraint uq_user_email cascade;

（六）修改约束

--非空

alter table tb_user modify (username varchar2(20));

--默认

alter table tb_user modify (age default null);

（七）序号

使用工具|程序管理流水号，序列在创建时 没有与表关联 ,在操作数据时 与表关联

1、创建

create sequence序列名 start with 起始值 increment by 步进;

2、使用

在操作数据 添加 更新-->主键

1)、currval :当前值

2)、nextval:下个值

create sequence seq_tb_user start with 2 increment by 2;

drop sequence seq_tb_user;

select seq_tb_user.nextval from dual;

select seq_tb_user.currval from dual

3、删除

drop sequence序列名