select * from scott.emp; select ename "name"from scott.emp; --lower转化小写的用法 select lower(ename) ename ,sal from scott.emp; select * from scott.emp where lower(ename)='allen'; --upper 转化大写 select upper(ename) "ename" from scott.emp ; --INITCAP 首字母大写 select INITCAP(ename) as ename from scott.emp ; --CONCAT 或 ||连接 select concat(ename,sal )from scott.emp; select concat ('wang','feng') ename from scott.emp; select ename||' '||sal ename from scott.emp; select '@'||ename||'@'as ename from scott.emp; --SUBSTR select SUBSTR(ename ,1,3)from scott.emp; select substr(hiredate , 0)from scott.emp; --寻找月份 select* from scott.emp where hiredate = '22-2月-81'; select* from scott.emp where hiredate > '1-1月-81'; select * from scott.emp where hiredate between '1-1月-81'and'31-12月- 81'; --LENGTH 长度 select length(ename) as chang from scott.emp; --INSTR返回第一次出现某字符的位置 select INSTR(ename,'L')from scott.emp; --LPAd 变成一个长度为10的字符,不够用*代替 select lpad (ename ,10,'*')FROM scott.emp; --RPAD 变成一个长度为10的字符串,不够在其后面加* select rpad (ename ,10,'*')from scott.emp; select rpad (ename ,3)from scott.emp; --TRIM 去除字符两端的某个字符 select trim ('C'from ename)from scott.emp; select '['||' '||ename||' '||']'from scott.emp; select trim (ename)from (select ' ['||' '||ename||' '||'] ' ename from scott.emp); select trim ('a'from 'aaabbbbaa') from scott.emp; --"Contains 'a'?" --select from scott.emp; --ROUND: 四舍五入 select round(7850.3854512145,5)from scott.emp; select round(7850.38,5)from scott.emp; select round(7850.38,-2)from scott.emp; --TRUNC: 截断 select trunc (222222.2222,2)from scott.emp ; select trunc (222222,-2)from scott.emp ; --日期报错select trunc (hiredate)from scott.emp; select trunc (222222.222,0)from scott.emp ; --MOD: 求余 select mod (3,2)from scott.emp; --sysdate 返回时间毫秒表示 select sysdate-hiredate from scott.emp; --substr负数从后面算起 Select substr('abcde',-4,3) from scott.emp; --replace select replace (ename,'A','a')from scott.emp; --Months_between() select months_between(sysdate,add_months(sysdate,-2)) from scott.emp; select sysdate from scott.emp;--返回现在时间 --add_months select add_months(sysdate,1) from scott.emp; --select add_days(sysdate,2)from scott.emp; add-years (),add_day()无效 --next_day select next_day(sysdate,'星期一')from scott.emp; --order by select * from scott.emp order by sal,empno asc; select * from scott.emp order by sal,empno desc; --Last_day select last_day (sysdate)from scott.emp; select add_months (sysdate,-2) from scott.emp; --ROUND 日期 select round (sysdate,'year')from scott.emp; select round (sysdate ,'month')from scott.emp; select round (sysdate,'day')from scott.emp; --trunc截取日期 select trunc (sysdate,'year')from scott.emp; select trunc (sysdate,'month')from scott.emp; select trunc (sysdate,'day')from dual; --转化函数 -- to_char()对于日期的转化 select to_char (sysdate,'yyyy')from scott.emp; select to_char (hiredate,'yy')from scott.emp; select to_char (sysdate ,'yyyy-mm-dd')from scott.emp; select to_char (hiredate, 'mm')from scott.emp; --生日在5到8月之间 select *from scott.emp where to_number(to_char (hiredate, 'mm'))between 7 and 9; select *from scott.emp where to_number(to_char (hiredate, 'mmdd'))between to_number(to_char (sysdate, 'mmdd')) and to_number(to_char (add_months(sysdate,2), 'mmdd')); select to_number(to_char (add_months(sysdate,2), 'mm')) from dual; select to_char(sysdate,'fmyyyy-mm-dd') from scott.emp; select to_char (hiredate,'fmyyyy-mm') from scott.emp; select to_char (sysdate,'fmyyyy-mm-dd-hh24-mi-ss')from scott.emp; select to_char (sysdate,'fmyyyy/mm/dd/hh/mi/ss')from scott.emp; select to_char(sal,'l999,999') from scott.emp; select to_char(sal,'$999,999') from scott.emp; select to_char (sysdate,'d')from Scott.emp; --To_number把字符串变成数字 select to_number('13')+to_number('14') from dual; --to_date select to_date ('20090222','yyyymmdd')from dual; --NVL()函数 select nvl(comm,0) from scott.emp; select * from scott.emp; --nvl2(comm, sal+comm, sal) select nvl2(comm, sal+comm, sal) from scott.emp; --COALESCE()函数 select empno, ename, sal, comm, coalesce(sal+comm, sal, 0)as sal from scott.emp; --case select empno, ename, sal, case deptno when 10 then '财务部' when 20 then '研发部' when 30 then '销售部' else '未知部门' end from scott.emp; --DECODE()函数 select empno, ename, sal, decode(deptno, 10, '财务部', 20, '研发部', 30, '销售部', '未知 部门') 部门 from scott.emp; select * from scott.emp where job != 'CLERK'; select * from scott.emp order by sal desc ; --查询出每个部门的编号、名称、位置、部门人数、平均工资 select * from scott.emp where deptno = 10;