Oracle基础教程笔记（三）

[color=blue]表空间管理[/color]
1.create tablespace tabs datafile
'c:\oracle\product\10.1.0\oradata\test\tabs.dbf'
size 10m;
alter user test default tablespace tabs;
grant unlimited tablespace, dba to test;

[color=blue]表管理[/color]
1.语法方式启动listener：lsnrctl status
lsnrctl start listener
2.完整性：实体完整性，参照完整性，域完整性
3.实体完整性：记录的唯一性，通过主键来保证
4.参照完整性：关联表中相关记录的完整性，通过外键来保证
5.check约束：alter table emp add constraint ck_emp_sex check(sex='男' or sex=''女");
6.查看表的约束：desc all_constraints,desc user_constraints,desc dba_constraints
7.如果不建索引，数据是按照堆来存放
8.create index 索引名 on 表名（列名）；
select * from 表名 where 列名="abc";
9.位图索引，值很单一，例如只有男女两种
create bitmap index bit_m on emp(sex);

[color=blue]视图·同义词·序列[/color]
1.视图：是一条存储起来的select语句
2.CREATE OR REPLACE VIEW myview AS SELECT * FROM books;
3.对视图操作就是对表操作
INSERT INTO myview VALUES('0009','EBOOK');
4.为视图加上限制条件
CREATE OR REPLACE VIEW myview AS SELECT * FROM books
where price>30 with check option
5. desc dba_views
desc user_views
desc all_views
6.select user from dual; 查看当前用户
7.同义词：有两种同义词，一种是公共的，需要dba来创建，一种是私有的
8.create synonym dept for scott.dept;
create public synonym dept for scott.dept;
9.desc dba_synonyms
10.create sequence myseq
start with 1
increment by 1
order
nocycle（会影响效率）;
11.select myseq.nextval from dual;
select myseq.currval from dual 查询当前序列值
12.insert into auto values(myseq.nextval,
13.desc dba_sequences;
desc all_sequeneces;
desc user_sequeneces;