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.