2 Oracle基础二（约束-视图-序列-远程登录-链接-同义词-伪列-索引）

一、约束

约束是一个数据库对象，对表中数据起到一个限制作用，它是依附于表中的某个字段上的。

分为以下几种：

1、主键约束 Primary Key(PK)   特性：唯一，非空

创建表的同时创建约束：

（1）在字段后面直接加约束类型（这样是系统自定义约束名）

（2）在所有字段写完后，加上 CONSTRAINT 约束名 PRIMARY KEY(字段名)

建表后，修改表，增加主键约束：ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY(字段名)；

2、唯一约束 Unique Key(UK)  特性：唯一，可为空

语法同上；

3、检查约束 Check Key（CK）特性：和where条件写法类似

检查约束所在列的数据是否符合约定规则，是否在规定范围内

4、非空约束 Null Key

5、外键约束 Foreign Key(FK)

定义：外键约束所在列的数据，需要依赖于主表对应字段（必须是主键）的数据，类似于父子继承关系。

外键数据，必须是主表主键字段中的数据，否则插入时会报错。

注意：主表中被依赖的字段无法删除，需要删除子表数据，才能删掉对应的父表数据。

可以指定级联删除 on delete  cascade,删除主表被依赖数据时同时删除子表依赖数据行；

CREATE TABLE person(
pid number(8) ,
pname varchar2(200) NOT NULL,
gender varchar2(10),
birthdate date,
age number(8) ，

CONSTRAINT pk_person_pid PRIMARY KEY(pid),

CONSTRAINT uk_person_pid unique(pid),

CONSTRAINT ck_person_age check(age between 1 and 50)

);

create table book(

bid number(8),

bname varchar2(30),

pid number(8),

CONSTRAINT fk_book_pid foreign key(pid) references person(pid) on delete cascade

);

 二、视图

视图是一种数据库对象，可以看做表的镜像,不占用存储空间。

CREATE [OR REPLACE] VIEW 视图名

AS

SELECT 查询语句;

例：

create view emp_stat

as

select e.empno,e.ename,e.deptno,e.job,d.loc

from emp e,dept d

where e.deptno=d.deptno;

--一般情况下，是不会修改视图的。简单视图可以被修改，实际修改的是查询相关的表，复杂视图很难被更新，需要用到后面所讲触发器。

删除视图：DROP VIEW 视图名称；

三、序列 sequence 

假想序列是一个数据池，那么需要确定从几开始，按几递增。

一个数据库对象，可以让我们按照预先设定的规则，从序列中取数。

语法：

CREATE SEQUENCE 序列名

[ START WITH xx ] [ INCREMENT BY XX ]

[ MINVALUE XX | NOMINVALUE ]

[ MAXVALUE XX | NOMAXVALUE ]

[ CACHE XX | NOCACHE ]

[ CYCLE XX | NOCYCLE ]

选项都是可选的，可以直接创建： CREATE SEQUENCE myseq;

用法：

myseq.CURRVAL 当前值

myseq.NEXTVAL 下一个值

select myseq.nextval from dual;

select myseq.currtval from dual;

作用：一般用来构造主键数据；

四、登录远程数据库

1、客户端

2、修改tnsnames.ora文件：服务器IP、服务器的数据库名、服务器的数据库端口号

ORCL_109 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.0.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

五、数据库链接Dblink

是一个数据库对象，我们可以通过它访问其他数据库中的资源，可以理解为各个数据库之间的桥梁。

语法：CREATE [PUBLIC] DATABASE LINK 数据库链接名

CONNECT TO 用户名 IDENTIFIED BY 密码 USING '服务名|字符串连接符'；

create database link link_109 connect to scott identified by tiger using 'orcl_109';--如果如四中已在tnsnames.ora中配置好名称‘ORCL_109 ’，此处直接写orcl_109即可；

使用：

select e.empno,e.ename,e.deptno,d.dname

from emp e,dept@link_109 d

where e.deptno=d.deptno;

六、同义词 Synonym

定义：Synonym是一个数据库对象，通过它可以对表、视图、序列等数据库对象起别名。

语法：CREATE [PUBLIC] SYNONYM 同义词名 FOR [Schema.]数据库对象名[@Dblink]

用处：

1、隐藏对象所归属的用户名（模式）

例：Tab其实是一个归属于sys用户的视图，全名是sys.tab，但是可以直接使用select * from tab;来查询，其实tab就是sys.tab的同义词。 create public synonym tab for sys.tab;

2、给数据库对象取一个好用的名字

create synonym salg for salgrade;

3、隐藏对象所归属的位置（数据库链接）

create synonym emp7 for emp@link_107;

作用：安全、方便。

查询当前用户下同义词：select * from user_synonyms;

删除同义词：drop synonym xx;

七、伪列

（一）Rownum

表示行号，通常用其分页查询到列。

例1：查询员工编号，员工姓名，工资，只显示前5条数据

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

例2：查询员工编号，员工姓名，工资，显示第5到10行数据

select empno,ename,sal from emp where rownum>5 and rownum <=10;[!错误！]

例3：查询员工编号，员工姓名，工资，显示第11行数据

select empno,ename,sal from emp where rownum =11;[！错误！]

【需要注意：】rownum使用时，需要保证rownum从第1行开始排序

例2正确写法：【思路：需要把rownum写在子查询中，当作一个表中的固定的列使用】

select rownum,rn,empno,ename,sal 

from (select rownum rn,empno,ename,sal from emp) e

where rn>5 and rn<=10;

例3正确写法：

select *

from (select rownum rn,empno,ename,sal from emp) e 

where rn=11;

例4：查询工资排名前5位的员工编号，员工姓名，工资

select empno,ename,sal

from emp

where rownum <=5

order by sal desc;【！错误！这是先取出num1-5的再排序了】

[需要注意：]order by 是最后执行的，是对查询结果进行排序的。

例4正确写法：

select *  

from (select empno,ename,sal from emp where sal is not null order by sal desc) e

where rownum<=5;

例5：查询工资排名在第5到10的员工编号，姓名，工资

select *

from  (select rownum rn, empno,ename,sal 

                    from (select empno,ename,sal from emp where sal is not null order by sal desc) e)

where rn between 5 and 10;

（二）Rowid

是一个伪列，标明表中唯一的行，可以理解为表中每行数据的物理地址，相当于每行数据的门牌号，通过rowid是查询定位表中数据最快的方式。

Rowid是在我们向表内插入数据时，自动生成的，它和表数据一起存放到表的每一行中。

--经典例题：（前提条件：给dept表插入一些重复数据）使用rowid删除这些重复数据。

---另一种说法：请给出一种方法，快速删除重复数据。

1、查询出按照各字段分组，最小的rowid

select deptno,dname,loc,min(rowid)

from dept_bak

group by deptno,dname,loc

2、删除表中rowid不在刚才找到的rowid中的数据

delete from dept_bak

 where rowid not in (select min(rowid)

from

dept_bak

group by deptno,dname,loc

);

八、索引 Index

是一种数据库对象，它基于表中的一列或多列（跟约束类似，都是建立在列上的）。

作用：1提高查询效率 2唯一索引可以保证数据的唯一性

缺点：1降低了数据维护（增删改）的效率；2创建索引需要额外时间空间；

分类：可以分为唯一索引和普通索引，也可以分为普通索引和聚合索引；

需要注意：主键约束和唯一约束默认给表创建了一个唯一索引。

语法：

CREATE [UNIQUE] INDEX 索引名

ON 表名（column [ ASC | DESC ]  [ , column [ ASC|DESC] ]）

示例：给dept_bak表的dept_no字段加上索引；

给某个字段建索引，其实是相当于系统中单独创建一个rowid和该列的表。

删除索引：DROP INDEX 索引名；

索引的重建：ALTER INDEX 索引名 REBUILD; 何时重建索引：1表上频繁发生update,delete操作；2表上发生了alter table ..move操作（move操作导致了rowid变化）。