Oracle学习笔记(四)

一、控制用户存取
1、创建修改用户CreatingUsers
Create/alterusernew_useridentifiedbypassword;
例：createuseruser_1indentifiedbypwd_1
alteruseruser_1identifiedbypwd_2
2、给用户授予权限
grantprivilege[,privilege]touser[,user|role,public...]
一些系统权限：
createsession/table/sequence/view/procedure

alter/delete/execute/index/insert/references/select/update
grantobject_priv[(columns)]
onobject
to{user|role|public}
[withgrantoption]
例如:
给用户user_1授予查询tt1表的权限
grantselectontt1表touser_1
给用户user_1授予修改“表1”中(列1，列2)的权限
grantupdate(列1，列2)on表1touser_1;
给用户user_1授予查询权限并可以把该权限授予其他用户的权限
grantselect
ontt1表
touser_1
withgrantoption
把权限授予所有用户
grantselect
on表1
topublic;

给用户user_1授权
grantcreatesessiontouser_1;

二、创建角色并给角色授权
1、创建角色
createrolemangager;
例如：createroletest_role1;
2、给角色授权
grantcreatetable,createviewtomanager;
例如：grantcreatetabletotest_role1;

3、把角色授予用户
grantmanagertouser_1,user_2...
granttest_user1touser_1;

三、取消用户权限
revoke{privilege[,privilege...]|all}
onobject
from{user[,user...]|role|public}
[cascadeconstraints];

revokeselecton表1fromuser_1;

四、DatabaseLinks
createpublicdatabaselinkhq.acme.comusing"sales";
select*fromemp@hq.acme.com;

五、oracle取并集、交集、差集
所取的列的数据类型必须兼容
1、取并集
union：会对数据排序，重复记录压缩，unionall不会
selectemployee_id,job_idfromemployess
union
selectemployee_id,job_idfromjob_history;
取所有并集不去除重复数据
selectemployee_id,job_idfromemployess
unionall
selectemployee_id,job_idfromjob_history;

2、取交集
selectemployee_id,job_idfromemployess
intersect
selectemployee_id,job_idfromjob_history;

3、差集
表employess去掉共同拥有的数据
selectemployee_id,job_idfromemployess
minus
selectemployee_id,job_idfromjob_history;

六、日期时间函数
求时差
selecttz_offset("US/Eastern")fromdual;

altersessionsettime_zone="-8:0";
selectsessiontimezone,current_datefromdual;
altersessionsettime_zone="-8:0";
selectsessiontimezone,current_timestampfromdual;

altersessionsettime_zone="-8:0";
selectcurrent_timestamp,localtimestampfromdual;

selectdbtimezone,sessiontimezonefromdual;
selectfrom_tz(timestamp"2000-03-2308:00:00","3:00")fromdual;

selectto_timestamp("2000-02-0111:00:00","YYYY-MM-DDHH:MI:SS")fromdual;

selectto_timestamp_tz("2000-02-0111:00:00","YYYY-MM-DDHH:MI:SSTZH:TZM")fromdual;
to_ymininterval()