Oracle培训（三十）——Oracle 11g 第十二章知识点总结——其他数据库对象

dkxqaakowb

Oracle培训（三十）——Oracle 11g 第十二章知识点总结——其他数据库对象

　　
　　知识点预览
　　
　　复习
　　其他数据库对象
　　
　　复习
　　
　　

--序列
Create sequence seq_name
--综合练习
--社区网站 人人网
--站内信系统
--会员
--member
--id主键自动增长
--namenot null
--password
--gender默认值 0, 1
--introduction
--emailunique
--logindate date
create table member
(
idnumber(6) primary key,
namevarchar2(20) not null,
passwordvarchar2(16) default('123456'),
genderchar(1) check(gender in ('0', '1')),
introductionvarchar2(1000) null,
emailvarchar2(20) not null unique,
logindatedate
);
--gender:0女 1男
insert into membervalues(seq_wgb_member.nextval, '温国兵', 'wgb', '1', 'I am unique!','wgbno27@163.com', sysdate);
insert into membervalues(seq_wgb_member.nextval, '姜国彦', 'jgy', '1', '嘿嘿', '123465@163.com', sysdate);
insert into member values(seq_wgb_member.nextval,'熊子睿', 'xzr', '1', '哈哈', '123447@163.com', sysdate);
insert into membervalues(seq_wgb_member.nextval, '李智鹏', 'lzp', '1', '呵呵', '124556@163.com', sysdate);
insert into membervalues(seq_wgb_member.nextval, '朱家敏', 'zjm', '1', '哇哇', '123232@163.com', sysdate);
--信
--message
--id主键自动增长
--titlenot null
--contentnot null
--to_member外键
--from_member外键
--face_id外键
--commons_id外键
--status记录状态 当前这封信有没有读到/有没有被回复 (1 2 3) case decode not null
--status0 inbox 1 outbox 2 当前这封信被读到 3 当前这封信没被读到 4 被回复 5 没有被回复
create table message
(
idnumber(6) primary key,
titlevarchar2(30) not null,
contentvarchar2(1000) not null,
to_membernumber(6),
from_membernumber(6),
face_idnumber(6),
commons_idnumber(6),
statusnumber(2),
constraintwgb_message_toMember_FK foreign key(to_member) references member(id),
constraintwgb_message_fromMember_FK foreign key(from_member) references member(id),
constraintwgb_message_faceId_FK foreign key(face_id) references faces(id),
constraintwgb_message_commonID_FK foreign key(commons_id) references commons(id)
);
insert into messagevalues(seq_wgb_message.nextval, '欢迎入学', '今天是开学的第一天 加油', 3, 2, 11, 5, 0);
insert into messagevalues(seq_wgb_message.nextval, '同学会', '今天是同学会 好开心', 5, 3, 4, 8, 1);
insert into messagevalues(seq_wgb_message.nextval, '饭卡掉了', '悲剧的一天', 4, 6, 10, 2, 0);
insert into messagevalues(seq_wgb_message.nextval, '打篮球', '强身健体', 6, 4, 11, 5, 1);
--表情
--faces
--id主键自动增长
--face文字 微笑 哭 --not null
create table faces
(
idnumber(6) primary key,
facevarchar2(10) not null
);
insert into facesvalues(seq_wgb_faces.nextval, '微笑');
insert into facesvalues(seq_wgb_faces.nextval, '哭');
insert into facesvalues(seq_wgb_faces.nextval, '大笑');
insert into facesvalues(seq_wgb_faces.nextval, '憨笑');
insert into facesvalues(seq_wgb_faces.nextval, '抓狂');
insert into facesvalues(seq_wgb_faces.nextval, '大兵');
insert into facesvalues(seq_wgb_faces.nextval, '酷');
insert into facesvalues(seq_wgb_faces.nextval, '悲伤');
insert into facesvalues(seq_wgb_faces.nextval, '难过');
insert into faces values(seq_wgb_faces.nextval,'开心');
--常用语
--commons
--id主键自动增长
--word文字 你好 我好 --not null
create table commons
(
idnumber(6) primary key,
wordvarchar2(40) not null
);
insert into commonsvalues(seq_wgb_commons.nextval, '你好');
insert into commons values(seq_wgb_commons.nextval,'我好');
insert into commonsvalues(seq_wgb_commons.nextval, '大家好');
insert into commonsvalues(seq_wgb_commons.nextval, '欢迎');
insert into commonsvalues(seq_wgb_commons.nextval, '再见');
insert into commonsvalues(seq_wgb_commons.nextval, '保重');
insert into commonsvalues(seq_wgb_commons.nextval, '注意身体');
--插入数据
--查询：
--某个用户登录后，查看自己的站内信 inbox outbox 内容 表情 常用语
--要求：1.inbox：显示发件人的名字、title、status、content、face、word
--status0 inbox 1 outbox 2 当前这封信被读到 3 当前这封信没被读到 4 被回复 5 没有被回复
selectt3.name as "发件人",mes.title as "信息标题",mes.content as "信息内容" ,t1.face as "表情",t2.word as "常用语",
casemes.status  when 0 then '收件箱'
when 1 then '发件箱'
when 2 then '当前这封信被读到'
when 3 then '当前这封信没被读到'
when 4 then '被回复'
when 5 then '没有被回复'
end"状态"
frommessage mes, member mem,
(
selectid,face
fromfaces
)t1,
(
selectid,word
fromcommons
)t2,
(
selectid, name
frommember
)t3
wheremes.from_member = mem.id
andt3.id = mes.from_member
andmes.face_id = t1.id
andmes.commons_id = t2.id
andmes.status = 0;

--2.outbox：显示收件人的名字、title、status、content、face、word
selectt3.name as "收件人",mes.title as "信息标题",mes.content as "信息内容",t1.face as "表情",t2.word as "常用语",
casemes.status  when 0 then '收件箱'
when 1 then '发件箱'
when 2 then '当前这封信被读到'
when 3 then '当前这封信没被读到'
when 4 then '被回复'
when 5 then '没有被回复'
end"状态"
frommessage mes, member mem,
(
selectid,face
fromfaces
)t1,
(
selectid,word
fromcommons
)t2,
(
selectid, name
frommember
)t3
wheremes.to_member = mem.id
andt3.id = mes.to_member
andmes.face_id = t1.id
andmes.commons_id = t2.id
andmes.status = 1;
create sequence seq_wgb_member;
create sequence seq_wgb_message;
create sequence seq_wgb_faces;
create sequenceseq_wgb_commons;


　　其他数据库对象
　　

　　1.常见数据库对象
　　

　　2.什么是序列?
　　
　　序列:
　　自动提供唯一的数值
　　共享对象
　　主要用于提供主键值
　　代替应用代码
　　将序列值装入内存可以提高访问效率
　　3.CREATE SEQUENCE 语句
　　
　　定义序列:
　　CREATESEQUENCE sequence
　　 [INCREMENT BY n]
　　 [START WITH n]
　　 [{MAXVALUE n | NOMAXVALUE}]
　　 [{MINVALUE n | NOMINVALUE}]
　　 [{CYCLE | NOCYCLE}]
　　 [{CACHE n | NOCACHE}];
　　
　　4.创建序列
　　a)创建序列 DEPT_DEPTID_SEQ为表 DEPARTMENTS 提供主键
　　b)不使用 CYCLE 选项
　　
　　

CREATE SEQUENCEdept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;


　　Sequence created.
　　

　　5.查询序列
　　a)查询数据字典视图 USER_SEQUENCES获取序列定义信息
　　
　　

SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM    user_sequences;


　　b)如果指定NOCACHE 选项，则列LAST_NUMBER 显示序列中下一个有效的值
　　

　　6.NEXTVAL 和 CURRVAL 伪列
　　a)NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
　　b)CURRVAL 中存放序列的当前值
　　c)NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效
　　

　　7.序列应用举例
　　
　　

INSERT INTO departments(department_id,
department_name,location_id)
VALUES      (dept_deptid_seq.NEXTVAL,
'Support', 2500);


　　1 row created.
　　
　　序列 DEPT_DEPTID_SEQ 的当前值
　　
　　

SELECT dept_deptid_seq.CURRVAL
FROM    dual;


　　8.使用序列
　　a)将序列值装入内存可提高访问效率
　　b)序列在下列情况下出现裂缝:
　　i.回滚
　　ii.系统异常
　　iii.多个表同时使用同一序列
　　c)如果不讲序列的值装入内存(NOCACHE), 可使用表 USER_SEQUENCES 查看序列当前的有效值
　　

　　9.修改序列
　　
　　修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存
　　

　　

ALTER SEQUENCEdept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;


　　Sequence altered.
　　

　　10.修改序列的注意事项
　　a)必须是序列的拥有者或对序列有 ALTER 权限
　　b)只有将来的序列值会被改变
　　c)改变序列的初始值只能通过删除序列之后重建序列的方法实现
　　d)其它的一些限制
　　

　　11.删除序列
　　a)使用DROP SEQUENCE 语句删除序列
　　b)删除之后，序列不能再次被引用
　　
　　

DROP SEQUENCEdept_deptid_seq;


　　Sequence dropped.
　　

　　12.索引
　　
　　索引:
　　一种数据库对象
　　通过指针加速 Oracle 服务器的查询速度
　　通过快速定位数据的方法，减少磁盘 I/O
　　索引与表相互独立
　　Oracle 服务器自动使用和维护索引
　　

　　13.创建索引
　　a)自动创建: 在定义 PRIMARYKEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
　　b)手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
　　c)在一个或多个列上创建索引
　　
　　CREATE INDEX index
　　ON table(column[, column]...);
　　
　　d)在表 EMPLOYEES的列 LAST_NAME 上创建索引
　　
　　

CREATE INDEX emp_last_name_idx
ON               employees(last_name);


　　Index created.
　　

　　14.什么时候创建索引
　　
　　以下情况可以创建索引:
　　列中数据值分布范围很广
　　列中包含大量空值
　　列经常在 WHERE 子句或连接条件中出现
　　表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
　　

　　15.什么时候不要创建索引
　　
　　下列情况不要创建索引:
　　表很小
　　列不经常作为连接条件或出现在WHERE子句中
　　查询的数据大于2%到4%
　　表经常更新
　　加索引的列包含在表达式中
　　

　　16.查询索引
　　
　　可以使用数据字典视图USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
　　
　　

SELECT  ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM     user_indexes ix, user_ind_columns ic
WHERE   ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';


　　17.基于函数的索引
　　a)基于函数的索引是一个基于表达式的索引
　　b)索引表达式由列, 常量, SQL 函数和用户自定义的函数
　　
　　

CREATE INDEXupper_dept_name_idx
ONdepartments(UPPER(department_name));


　　
　　Index created.
　　
　　

SELECT *
FROM   departments
WHERE  UPPER(department_name) = 'SALES';


　　
　　18.删除索引
　　a)使用DROP INDEX 命令删除索引
　　
　　DROP INDEX index;
　　
　　b)删除索引UPPER_LAST_NAME_IDX
　　
　　

DROP INDEXupper_last_name_idx;


　　Index dropped.
　　c)只有索引的拥有者或拥有DROP ANY INDEX权限的用户才可以删除索引
　　
　　19.同义词
　　
　　使用同义词访问相同的对象:
　　方便访问其它用户的对象
　　缩短对象名字的长度
　　
　　CREATE [PUBLIC] SYNONYM synonym
　　FOR object;
　　
　　20.创建和删除同义词
　　a)为视图DEPT_SUM_VU 创建同义词
　　
　　

CREATESYNONYM  d_sum
FOR  dept_sum_vu;


　　Synonym Created.
　　b)删除同义词
　　
　　

DROP SYNONYMd_sum;


　　Synonym dropped.