[b]一、控制用户存取 [/b]1、创建修改用户Creating Users Create/alter user new_user identified by password; 例：create user user_1 indentified by pwd_1 alter user user_1 identified by pwd_2 2、给用户授予权限 grant privilege[,privilege] to user [,user|role,public...] 一些系统权限： create session/table/sequence/view/procedure alter/delete/execute/index/insert/references/select/update grant object_priv [(columns)] on object to {user|role|public} [with grant option] 例如: 给用户user_1授予查询tt1表的权限 grant select on tt1表 to user_1 给用户user_1授予修改“表1”中(列1，列2)的权限 grant update(列1，列2) on 表1 to user_1; 给用户user_1 授予查询权限并可以把该权限授予其他用户的权限 grant select on tt1表 to user_1 with grant option 把权限授予所有用户 grant select on 表1 to public; 给用户user_1授权 grant create session to user_1; [b]二、创建角色并给角色授权 [/b]1、创建角色 create role mangager; 例如：create role test_role1; 2、给角色授权 grant create table,create view to manager; 例如：grant create table to test_role1; 3、把角色授予用户 grant manager to user_1,user_2... grant test_user1 to user_1; [b]三、取消用户权限[/b] revoke {privilege [,privilege...]|all} on object from {user[,user...]|role|public} [cascade constraints]; revoke select on 表1 from user_1; [b]四、Database Links [/b]create public database link hq.acme.com using 'sales'; select * from emp@hq.acme.com; [b]五、 oracle取并集、交集、差集 [/b]所取的列的数据类型必须兼容 1、取并集 union ：会对数据排序，重复记录压缩，union all不会 select employee_id,job_id from employess union select employee_id,job_id from job_history; 取所有并集不去除重复数据 select employee_id,job_id from employess union all select employee_id,job_id from job_history; 2、取交集 select employee_id,job_id from employess intersect select employee_id,job_id from job_history; 3、差集 表employess去掉共同拥有的数据 select employee_id,job_id from employess minus select employee_id,job_id from job_history; [b]六、日期时间函数[/b] 求时差 select tz_offset('US/Eastern') from dual; alter session set time_zone='-8:0'; select sessiontimezone,current_date from dual; alter session set time_zone='-8:0'; select sessiontimezone,current_timestamp from dual; alter session set time_zone='-8:0'; select current_timestamp,localtimestamp from dual; select dbtimezone,sessiontimezone from dual; select from_tz(timestamp'2000-03-23 08:00:00','3:00') from dual; select to_timestamp('2000-02-01 11:00:00','YYYY-MM-DD HH:MI:SS')from dual; select to_timestamp_tz('2000-02-01 11:00:00','YYYY-MM-DD HH:MI:SS TZH:TZM')from dual; to_ymininterval()