字符串操作

--数据库中是从1开始 下标
--去掉null  0
select sal,comm,sal+nvl(comm,0) "实际工资" from emp;
--小数点 向上取 正数向后  负数向前
select round(6666.6666,2) from dual;
--直接去掉
select trunc(5555.55555,2) from dual;
--截取字符串 n从几位开始 m取几位 
select substr('holle word',n,m) from dual;
 --从左边对齐 n位数 m 代替字符
 select lpad(ename,n,m) from emp;
 --去掉字符 只能是首 尾
 select trim(' d s ') from dual;
 --指定去掉字符 前 
 select trim(leading 'a' from 'abcaa') from dual; 
 --后
 select trim(trailing'a' from 'abcaa') from dual; 
 
--时间 函数转换  距离几个月
select months_between(date'2014-5-5',sysdate) from dual;
--距离指定时间 相差天数
select date'2014-2-14'-sysdate from dual;
--求前面几个月的今天
select add_months(sysdate,-3) from dual;
--指定 年 月 日 查询
select next_day(sysdate, '星期一') from dual;
select round(sysdate, 'MONTH') from dual;
select trunc(sysdate, 'year') from dual;
--cast强制转换
select 5+cast('55' as number(2)) from dual;
--查询指定时间
select to_char(sysdate,'fm yyyy"年"mm"月"dd"日" day hh24 pm ') from dual;
--DDL、DCL自动提交；但DML不会自动提交（只是本会话可见）
--排序状态 相同的值 不同的排法
select sal,row_number() over(order by sal desc), 
      rank() over(order by sal desc),
      dense_rank() over(order by sal desc)
from emp;
--decode判断函数 给值 相当与 case when then 

select decode 

--创建用户及指定初始口令
create user zhangsan identified by abc;
--分别赋予用户创建回话,创建表,创建表空间和使用表空间的权限
grant create session to zhangsan;
grant create table to zhangsan;
grant create tablespace, unlimited tablespace to zhangsan;


create tablespace myts
datafile 'D:\myts.ora' size 5M;
create table t(i number);


create temporary tablespace tmpts
tempfile 'D:\tmp.ora' size 3M;


--创建新用户,使用了非标准标识符作为密码,
--并且明确指定了默认表空间(存放数据和日志,默认是USERS)和临时表空间(默认是TEMP)
create user lisi identified by "123"
default tablespace myts
temporary tablespace tmpts;


--一句授权语句可以同时授予多个权限,也可以同时授予多个用户
grant create session, create table, unlimited tablespace to lisi;
--对象所有者(创建者)具有对该对象的绝对控制权,
--还具有该对象权限的授出与没收权限
select * from t;
insert into t values(8);
drop table t;
select * from zhangsan.t;
grant select on t to zhangsan;


select * from lisi.t;
drop table lisi.t;
grant delete on t to lisi;


--多个用户可以共用同一个表空间(相互独立),还可以明确划分使用空间大小
create user wangwu identified by aaa
default tablespace myts
quota 4M on myts;


grant create session to wangwu;
--对象权限的授予,update可以精确到个别字段
--with grant option选项允许被授权者具有将此权限授权出去的权限
grant select, update(ename, sal) on scott.emp to wangwu with grant option;


select * from scott.emp;
update SCOTT.emp set ename='WANGWU', sal=80000, comm=10000 where ename='KING';
update SCOTT.emp set ename='WANGWU', sal=80000 where ename='KING';
rollback;
grant update(ename) on scott.emp to zhangsan with grant option;


--没收权限,cascade constriants还会没收此用户通过with grant option传播出去的该权限(递归)
revoke update on scott.emp from wangwu cascade constraints;
update SCOTT.emp set ename='WANGWU' where ename='KING';


--创建角色并授予权限,再将该角色授予用户
create role my;
grant connect, resource to my;
grant my to lisi,wangwu;


--对角色的授权和没收将直接影响属于该角色的所有用户
grant create user to my;
create user qianqi identified by xxx;


revoke connect from my;


create view myv as select * from t;
--删除角色将删除属于该角色的所有用户从该角色所获得的额外权限
drop role my;
--删除用户,cascade会删除该用户的模式(即该用户拥有的所有数据库对象)
drop user zhaoliu cascade;


--查询当前用户的对象权限,角色权限和系统权限
select * from role_tab_privs;
select * from role_role_privs;
select * from role_sys_privs;


--通过DBA用户查询用户的对象权限,角色权限和系统权限
select * from dba_tab_privs where grantee='ZHANGSAN';
select * from dba_role_privs WHERE grantee='ZHANGSAN';
select * from dba_sys_privs WHERE grantee='ZHANGSAN';


select * from dba_tab_privs where grantee='SYSTEM';
select * from dba_role_privs WHERE grantee='SYSTEM';
select * from dba_sys_privs WHERE grantee='SYSTEM';


create synonym e for scott.emp;
select * from e;
grant select on e to zhangsan;


select * from scott.emp;


create public synonym emp for scott.emp;
select * from emp;


--drop synonym emp;


create index nname on student(sname);
drop index nname;
create unique index uname on dept10(ename);
create index rsal on dept10(sal) reverse;
create bitmap index bcomm on dept10(comm);
create index fname on dept10(lower(ename));
create index salcomm on dept10(sal, comm);


select index_name, table_name from dba_indexes;
select index_name, table_name from all_indexes;
select index_name, table_name from user_indexes;