oracle基本函数

 INSERT into hr.JOBS(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) values ('IT_TEST','测试员',3000.00,8000.00);
 decs hr.JOBS
 INSERT INTO hr.JOBS values ('IT_DBA','数据库管理员',5000.00,15000.00);
 
 INSERT INTO hr.JOBS(JOB_ID,JOB_TITLE,MIN_SALARY) values ('PP_MAN','产品经理',5000.00);
 
 UPDATE hr.EMPLOYEES set SALARY=SALARY*1.15 WHERE JOB_ID='IT_PROG';
 UPDATE hr.EMPLOYEES set SALARY=(SELECT avg(SALARY) FROM hr.EMPLOYEES WHERE JOB_ID='IT_PROG')
 WHERE EMPLOYEE_ID=104;
 
 delete 删除数据产生数据回滚信息/truncate 删除表中所有记录不能撤销
 
 truncate语句中还可以使用关键字reuse storage，表示删除记录后仍然保存记录占用的空间；与此相反，也可使用
 drop storage关键字。使用关键字reuse storage 保留删除记录后的空间的truncate语句如下
 TRUNCATE table it_employees resuse storage;
 
 若使用DELETE FROM TABLE_NAME语句，则整个表中的记录都将被删除，只剩一个表格的定义，在这一点上，语句作用的效果
 和truncate table table_name的效果相同。但是delete的语句可以使用rollback来恢复数据，而truncate则不能。
 
 
 -- 赋予权限
 GRANT SELECT,UPDATE,DELETE,INSERT ON TABLE it_employees TO USER1;
 GRANT ALL PRIVILEGES ON TABLE it_employees TO USER1;
 
 --收回权限
 revoke UPDATE(EMPLOYEE_ID)
 
 --字符类函数
 --字符对应ascii码
 SELECT ascii('A') BIG_A,ASCII('a') SMALL_A FROM dual;
 --ascii码对应字符
 SELECT CHR(65),CHR(97) FROM DUAL;
 
 -- 字符串拼接 与 ||
 SELECT concat('oracle','11g') name from dual;
 
 -- 首字母大写
 SELECT initcap('oracle universal installer') name from dual;
 
 --返回字符低j次出现的位置
 SELECT instr('Moisossoppo','o',-2,3) from dual;
 SELECT instr('Moisossoppo','o',3,3) from dual;
 
 --返回字符长度
 SELECT LENGTH('oracle 11g') name from dual;
 
 --字符转小写
 SELECT LOWER(JOB_ID) from hr.JOBS WHERE lower(JOB_ID) like 'it%';
 
 --去掉c1左边的字符 使其不在c2中
 SELECT LTRIM ('Moisossoppo','Mois') from dual;
 
 --字符替换 c3中的字符替换c1中的c2
 SELECT replace ('fellblue','blue','yellow') from dual; 
 
 --字符串截取
 SELECT substr('message',1,2) from dual;
 
 --数字类函数
 
 SELECT abs(-2) from dual;
 SELECT ceil(10) from dual;
 SELECT cos(10) from dual;
 SELECT cosh(10) from dual;
 SELECT exp(2) from dual;
 SELECT floor(10) from dual;
 SELECT ln(2) from dual;
 SELECT log(2,2) from dual;
 SELECT mod(5,2) from dual;
 SELECT power(2,3) from dual;
 SELECT round(3.14159,3) from dual;
 SELECT sign(-1) from dual;
 SELECT trunc(3.3312323,3) from dual;

 
 --日期类函数
 SELECT add_months(sysdate,3) from dual;
 SELECT last_day(sysdate) from dual;
 SELECT sysdate from dual;
 
 --转换类函数
 select to_char(345345234.2222,'9999999999.999$') from dual;
 SELECT to_char(0.7,'990.99') from dual;
 SELECT to_multi_byte('高') from dual;