vnsc5858威尼斯城官网Oracle学习笔记(四)

时间:2020-05-08 11:02来源:计算机教程
一、控制用户存取 1、创建修改用户Creating Users Create/alter usernew_user identified by password; 例:create user user_1 indentified bypwd_1 alter user user_1 identified by pwd_2 2、给用户授予权限 grantprivilege[,pri

一、控制用户存取 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; 二、创建角色并给角色授权vnsc5858威尼斯城官网, 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; 三、取消用户权限 revoke {privilege [,privilege...]|all} on object from {user[,user...]|role|public} [cascade constraints]; revoke select on 表1 from user_1; 四、Database Links create public database link hq.acme.com using 'sales'; select * from emp@hq.acme.com; 五、 oracle取并集、交集、差集 所取的列的数据类型必须兼容 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; 六、日期时间函数 求时差 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()

编辑:计算机教程 本文来源:vnsc5858威尼斯城官网Oracle学习笔记(四)

关键词: