Oracle 10g SQL Fundamentals II(学习笔记二第1-2章)
第一章用户访问创建用户create user useridentified by passwd;授权grant privilege to user;grant create session,create table,create sequence,create view to scott;创建角色create role manager;grant create table,create view to manager;grant manager to user1,user2;alter user hr identified by hr;alter user test account lock/unlock;授查询权限grant select on employee to hr,scott;授修改部分列的权限grant update(department_name,location_id) on departmentsto scott,manager;授管理权限grant select,insert on departments to scott with grant option;授所有用户的权限grant select on scott.empto public;权限相关的视图role_sys_privs:role_tab_privs:user_role_privs:user_tab_privs_made:user_tab_privs_recd:user_col_privs_made:user_col_privs_recd:user_sys_privs:回收权限 revoke privilege on object from user ; 例如： revoke select,insert on departments from scott; 第二章管理Schema对象 添加列： alter table table_name add column datatype ...; 修改列： alter table table_name modify column datatype ...; 删除列： alter table table_name drop column_name; 设置列不可用 alter table table_name set unused column_name; 或者 alter table table_name set unused column column_name; 删除不可用列 alter table table_name drop unused columns; 添加约束语法 alter table table_name add constraint constraint_name ...; alter table emp modify employee_id primary key ; alter table emp2 add constraint emp_mgr_fk foreign key(manager_id) references emp2(employee_id); alter table emp2 add constraint emp_dt_fk foreign key(department_id) refences departments on delete cascade; 添加主键 alter table dept2 add constraint dept_id_pk primary key(department_id) deferrable initially deferred; 删除约束 alter table emp2 drop constraint emp_mgr_fk; 删除主键 alter table dept2 drop primary key cascade; 禁止用约束 alter table emp2 disable constraint emp_dt_fk; 启用用约束 alter table emp2 enable constraint emp_dt_fk;级联约束alter table emp2drop column employee_id cascade constraints;alter table test1drop (pk,fk,col1) cascade constraints;索引创建方式： 1自动创建： 主键创建 唯一主键创建 2手动创建 create index create table为主键指定特定索引 create table new_emp (employee_id number primary key using index (create index emp_id_idx on new_emp (employee_id)), first_name varchar(20), last_name varchar(25)); select index_name,table_name from user_indexes where table_name='new_emp'基于特定函数的索引create index upper_dept_name_idxon dept2(upper(department_name));select * from from dept2 where upper(department_name)='SALES';删除索引drop index index_name;删除表drop table emp3 purge;flashback table 语句 FLASHBACK TABLE[schema.]table[,[ schema.]table ]... TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ]; select original_name,operation,droptime from recyclebin;flashback table emp2 to before drop;创建外部表1.创建目录create or replace directory emp_diras '/.../emp_dir';2.授权grant read on directory emp_dir to hr;3.创建外部表的语法：CREATE TABLE <table_name> ( <col_name> <datatype>, … ) ORGANIZATION EXTERNAL (TYPE <access_driver_type> DEFAULT DIRECTORY <directory_name> ACCESS PARAMETERS (… ) ) LOCATION ('<location_specifier>') ) REJECT LIMIT [0 | <number> | UNLIMITED]; 4.外部表的创建 CREATE TABLE oldemp ( fname char(25), lname CHAR(25) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE NOBADFILE NOLOGFILE FIELDS TERMINATED BY ',' (fname POSITION ( 1:20) CHAR, lname POSITION (22:41) CHAR)) LOCATION ('emp.dat') ) PARALLEL 5 REJECT LIMIT 200;?