--创建表空间create tablespace TESTTABLESPACE datafile 'E:\oracle\oradata\TESTTABLESPACE1.dbf'size 10M autoextend on next 5m maxsize 20m, 'E:\oracle\oradata\TESTTABLESPACE2.dbf'size 10M autoextend on next 5m maxsize 20m--创建用户create user test1 identified by test1 default tablespace TESTTABLESPACEtemporary tablespace temp;--创建用户create user test2 identified by test2 default tablespace TESTTABLESPACEtemporary tablespace temp;--删除角色 cascade表示删除该用户下所有对象（表，视图，索引，存储过程），然后再删除该用户的定义--如果该用户下没有任何其他对象（表，视图，索引等等）就可以不用添加cascadedrop user test1 cascade;drop user test2 cascade;--修改用户的默认表空间alter user test1 default tablespace TESTTABLESPACE--修改密码 by字符后面字符表示要修改的密码alter user test1 identified by 111111--为用户分配空间大小 user后代表用户名，on后代表表空间alter user test1 quota 10m on TESTTABLESPACE--删除开发者角色drop role developer;--创建开发者角色create role developer;--为用户赋予权限grant create session, create sequence,create trigger,create cluster,create procedure, create type,create operator,create table,create indextype to developergrant developer to test1;--查询某个角色的权限select * from role_sys_privs where role='DEVELOPER'select * from role_sys_privs where role='CONNECT'select * from role_sys_privs where role='RESOURCE'--收回用户、角色、public的某个权限revoke create session from developer;--插入时间格式数据insert into Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values( 1055,'步兵','clerk',9001,to_date('1992-10-10', 'yyyy-mm-dd'),1000.0,null,20 ); --创建Sequencescreate sequence TESTSEQminvalue 1maxvalue 999999999999999999999999999start with 1increment by 1cache 20;--日期字符串转日期格式select to_date('2008-01-13','yyyy-mm-dd') from dual;--日期格式转字符串select to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;--取日期所在月的最后一天select last_day(sysdate) from dual;--monthsbetween函数select months_between(to_date('2010-03-14','yyyy-mm-dd'),to_date('2024-05-04','yyyy-mm-dd')) from dual;--时区转换select new_time(sysdate,'pdt','gmt') from dual;--查找指定的下个星期几select next_day(sysdate,'星期五') from dual;--数字的话1是指星期天，照西方的习惯来算select next_day(sysdate,6)from dual;--获取系统日期select to_char(sysdate ,'yyyy-mm-dd hh24:mi:ss day') from dual;--十六进制转二进制select hextoraw('12abc') from dual;--二进制转换成十六进制select rawtohex('10101010') from dual;--将给定字符串转换成数字select to_number('123') from dual;--日期的截取select trunc(hiredate,'day') days from emp;--对日期之中天的操作（+，-）select sysdate ,sysdate+1/60/60/24 opratedate from dual;select sysdate,sysdate-1/60/60/24 opratedate from dual;--对于日期之中月的操作(+,-)select sysdate,add_months(sysdate,1) from dual;select sysdate,add_months(sysdate,-1) from dual;--对于日期之中年的操作（+，-），其实就是对于月的操作，一年就是12个月select sysdate,add_months(sysdate,-12*3) from dual;select sysdate,add_months(sysdate,-12*3) from dual;--日期函数numtodsintercal与numtoysinterval的操作--numtodsinterval 常用操作符 day,hour,minute,second--numtoymintercal常用操作符 year,monthselect sysdate,sysdate+numtodsinterval(1,'day') from dual;select sysdate,sysdate+numtoyminterval(-5,'year') from dual;--timestamp类型操作select to_char(to_timestamp('2008-06-01 18:05:00','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh:mi:ss am') from dual;--返回指定字符串的ascii码select ascii('A') from dual;--返回制定整数对应的字符select chr(53370) from dual;--字符串的连接select concat('aa','bb') word from dual;select 'aa'||'bb' word from dual;--首字母大写，只对英文字符有效select initcap('aaaaa')from dual;--返回指定字符在目标字符的位置select instr('oracle traning r','r',1,3) from dual;--返回指定字符串的长度select length('hello oralce') from dual;--返回小写字符串select lower('AAaA') from dual;--返回大写字符串select upper('aAaAa') from dual;--粘贴字符（lpad 左边粘贴，rpad右边粘贴）select || lpad( rpad('gao',10,'*'),17,'#')from dual;--删除边界指定字符select ltrim(rtrim(' gao qian l ',' '),' ') from dual;--截取子串select substr('13800138000',3,5) from dual;--替换字符串select replace('henno world','nn','ll') from dual;--去除字符串两边空格select trim(' ssssssssssssdf ')from dual;--返回指定值的绝对值select abs(-100),abs(100) from dual;--精度直接舍弃select trunc(10.5555,2) from dual;select trunc(100.444,-2) from dual;--精度四舍五入select round(10.5555,3) from dual;select round(167.567,-2)from dual;--空列相加select sal+nvl(comm,0) from emp;--获取该列的最大值select max(sal) from emp;--获取rownumber 和rowid，rownumber用于排序等，rowid是指在物理中存取的位置select e.*, rownum,rowid from emp e;--求出当前时间与目标时间相差几年几个月select trunc(months_between(sysdate,to_date('2011-01-01','yyyy-mm-dd'))/12)||'年'||trunc(mod(months_between(sysdate,to_date('2011-01-01','yyyy-mm-dd')),12))||'月' 时间 from dual;
