1.表和列的命名规则

（1）必须由字母开始，字符长度为1-30个字符，只能包含a-z A-Z 0-9  _ $ #（2）同一个用户所拥有的对象之间不能重名，不能用oracle服务器的保留字（3）建议使用描述性的名字为表和其他数据库对象命名（4）表和列名对大小写不敏感2.create table语法create table tbl_name(coll datatype,......) tablespace tablespace_name;创建表create table emp(emp_no number not null,emp_name varchar(50),constraint pk_emp primary key(emp_no));select dbms_metadata.get_ddl('TABLE','EMP') from dual;查看表结构3.增加备注comment on table emp is 'employees table';comment on column emp.emp_no is 'employees comment';查看备注set linesize 400;col comments for a30;select * from user_tab_comments where table_name='EMP';TABLE_NAME               TABLE_TYPE  COMMENTS------------------------------ ----------- ------------------------------EMP                   TABLE       employees tableSQL> select * from user_col_comments where table_name='EMP';TABLE_NAME               COLUMN_NAME              COMMENTS------------------------------ ------------------------------ ------------------EMP                   EMP_NO                  employees commentEMP                   EMP_NAME4.用子查询创建表create table a as select owner,object_name,subobject_name from all_objects;create table tbl_01(owner,object_name,subobject_name) as select owner,object_name,subobject_name from all_objects;select count(*) from tbl_01;  COUNT(*)----------     718595.制定default值create table tbl_02(emp_no number,emp_name varchar(50) default 'shiwei',create_date date);insert into tbl_02(emp_no,create_date) values(1,sysdate);select * from tbl_02;    EMP_NO EMP_NAME                          CREATE_D---------- -------------------------------------------------- --------     1 shiwei                          10:33:146.删除表drop table a;drop table a purge;7.rename对象rename old_name to new_name;old_name:存在的table view sequence private synonym的名字8.截断表truncat table a;truncat 与delete的区别truntcat释放表空间delete只删除表数据9.sequence序列号oracle删除序列化： DROP SEQUENCE seq_a;oracle创建序列化：CREATE SEQUENCE seq_a            INCREMENT BY 1  -- 每次加几个             START WITH 1       -- 从1开始计数             NOMAXVALUE        -- 不设置最大值             NOCYCLE               -- 一直累加，不循环             CACHE 10;            -- 缓存存储10个sequence，oracle默认存20个oracle修改序列化：Alter Sequence         .如果想要改变 start值，必须 drop  sequence 再create .想改变序列化的minvalue必须删除序列化后再重新建立序列化。  Alter sequence 的例子         ALTER SEQUENCE seq_a                                      INCREMENT BY 10                                       MAXVALUE 10000                                       CYCLE    -- 到10000后从头开始                                       NOCACHE ;  sequence的使用：SELECT seq_a.nextval FROM dual; --当前值的下一个值，第一次执行为开始值10SELECT seq_a.currval FROM dual; -- 当前值注：每一个登录用户都必须先执行seq_a.nextval后,才能执行seq_a.currval,否则会报ORA-08002: sequence seq_a.CURRVAL is not yet defined in this session错误//查询sequenceselect sequence_name from  user_sequences;SELECT  * FROM user_sequences ; -- 查询当前用户所有的sequencesSELECT * FROM all_sequences;  --查询所有用户的sequencescreate table tbl_03(id number,name varchar(50),primary key(id));create sequence seq_tbl_03  minvalue 1 maxvalue 99999 start with 1 increment by 2 cache 20 nocycle;初始化select seq_tbl_03.nextval from dual;insert into tbl_03 values(seq_tbl_03.currval,'guoyf');insert into tbl_03 values(seq_tbl_03.nextval,'guo');10.创建同义词synonym同义词其实在使用一种替换操作，oracle会自动将同义词替换为它所代表的对象名，再对对象进行操作。如果表和同义词同名，oracle优先操作表。语法：create or replace [public] for objects_name;SQL> create synonym offices for hr.locations;SQL> select count(*) from offices;  COUNT(*)----------    25drop synonym offices;11.约束（constrains）oracle服务器用约束来防止无效数据输入到表中。约束做的事情：在插入行数据或者从表中删除行数据时强制遵循规则。not nulluniqueprimary keyforeign key：在列和引用表的一个列之间建立，并且强制一个外键关系check：指定一个必须为真的条件drop table teacher purge;create table teacher(teacher_id number not null primary key,teacher_name varchar2(50),ages number(3) check (ages < 200));drop table stu purge;drop table student purge;create table student(stu_id number not null primary key,teacher_id number not null,stu_name varchar2(50),address varchar2(50)，#constraint uk_name unique(stu_name),#constraint pk_student primary key(stu_id));alter table student add constraint fk_student_teacher foreign key(teacher_id) references teacher(teacher_id);删除teacher表中数据时，先删除student表中的数据依赖添加唯一索引alter table student add constraint uk_name unique(stu_name);添加check约束alter table teacher add constraint check_ages check(ages<200);查看约束 select owner,constraint_name,constraint_type,table_name,status from user_constraints;CREATE TABLE promotions_var1    ( promo_id         NUMBER(6)                       CONSTRAINT promo_id_u  UNIQUE --自定义约束名    , promo_name       VARCHAR2(20)    , promo_category   VARCHAR2(15)    , promo_cost       NUMBER(10,2)    , promo_begin_date DATE    , promo_end_date   DATE    ) ;11.用户管理创建角色create role dba_group1;赋予角色权限grant create session,create table,create view to dba_group1;查看角色权限select * from role_sys_privs where role='DBA_GROUP1';创建用户create user test identified by test default tablespace tbs_01;给用户赋予dba_group1的权限grant dba_group1 to test;用户连接conn test/test查看用户权限select * from user_sys_privs ;--为空 因为直接赋予的是角色，不是具体权限//角色视图---授予角色的系统权限SQL> select * from role_sys_privs;---可由用户访问的角色SQL> select * from user_role_privs;---授予用户的系统权限SQL> select * from user_sys_privs;12.revoke回收权限revoke create view from dba_group1;revoke dba_group1 from test;create user test identified by test;grant connect, create view to test;revoke create view from test;