Creating and Managing Schema Objects(创建管理方案对象)
1.About Data Definition Language (DDL) Statements(关于数据定义语言DDL语句)
DDL包括
,create
,change:alter
,drop.
DDL语句执行时会隐式的提交commit语句,所以不能对DDL语句进行rollback.
2.About Schema Object Names(关于架构对象名称'命名规范')
命名规范参考Oracle Database SQL Language Reference。
使用对象名称作为名称前缀,例如:t_ for tables, v_ for views, seq_ for sequences, and syn_ for
synonyms.
3.Creating and Managing Tables(units of data storage'数据存储单元')
(1)数据类型
(2)创建表
(3)数据完整性
Constraint Types(约束类型)
1,not null
2,unique,唯一,表中列或列的组合数据不能相同。但可以为空。
3,primary key 主键,not null,unique的组合。
4,foreign key 外键,表某列的值匹配其他表列的值。
5,check ,要求值满足指定的条件。CHECK (EMPLOYEE_AGE >= 18)。
6,ref,描述列和表的关系。
Adding Constraints to Existing Tables
alter table tablename modify(修饰) columnname not null; alter table tablename add constraint(约束) pk_name primary key (columnname); alter table tablename add constraint fk_name foregin key (columnname) references(参考) tablename1 (columnname)
Managing Indexes(索引)
当在表的某列定义了主键时,默认会为该主键创建一个唯一索引。
CREATE INDEX EVAL_JOB_IX
ON EVALUATIONS (JOB_ID ASC) NOPARALLEL;
DROP INDEX EVAL_JOB_ID;
CREATE INDEX EVAL_JOB_IX
ON EVALUATIONS (JOB_ID DESC) NOPARALLEL;
4.Creating and Managing Views
(1)creating views
语法:create view viewname as select
(2)changing queries(查询结果) in views
使用create or replace覆盖原有的结果。
(3)changing view names
(4)dropping views
DROP VIEW SALES_MARKETING;
5.Creating and Managing Sequences
(1)creating a sequence
create sequence name_seq
increment by 1
start with 1 order;
(2)dropping sequence
6.Creating and Managing Synonyms(同义词,替代名)
CREATE SYNONYM EMP FOR EMPLOYEES;