使用PL/SQL调取数据

从默认表EMP里面调取各种数据

SELECT * FROM EMPselect '['||trim(ename)||']' from emp;select * from  EMP where trim(ename) = 'SMITH';SELECT * FROM emp--Instr 第二个字符串出现位置的索引select Instr('Hello World','or') a from dualselect * from dual --Substr 字符串处理，1表示起始索引位置，2表示获取字符串的长度select substr('abc',1,2) from dual--Lpad 10表示获取的字符串长度，*表示左侧添加的字符（重复添加至需要长度）select Lpad('abc',10,'*') from dual--Rpadselect Rpad('abc',10,'*') from dual--replace 替换字符Select replace(ename,'A','a') from emp--Round 四舍五入select round(477,-2) from dualselect round(412.313,-2) from dual--Mod 取余select mod(11,8) from dual--TRUNC 截断 select TRUNC(49.66666,0) from dual----日期使用--sysdate 获取本机时间select sysdate from dual--MONTHS_BETWEENselect * from empselect * from emp order by hiredate descselect hiredate from emp where months_between ('1987-05-23','1980-12-17')select months_between ('01-5月 -81','23-1月 -82') from emp select months_between ('01-09-95','11-02-94') from dualselect months_between (sysdate,hiredate) from emp--ADD_MONTHS select add_months (hiredate,8),hiredate from emp--NEXT_DAYselect next_day (to_date('2017-6-25','yyyy-mm-dd'),1) from DUAL--LAST_DAYselect last_day (sysdate) from emp--To_char,To_date,To_numberselect to_char(hiredate) from empselect to_date(hiredate) from empselect to_number('12')+to_number('14') from dualselect to_char('sunday') from dual--分组函数select * from emp order by sal descselect avg(sal) from empselect sum(comm) from empselect count(*) from emp--练习select * from emp--找出每个月倒数第三天受雇的员工select * from emp where hiredate = last_day(hiredate)-2--找出 25 年前雇的员工select * from emp where ADD_MONTHS(sysdate,-25*12) <=hiredateselect * from emp where to_date(sysdate,'yyyy') = to_date(hiredate,'yyyy')+25--所有员工名字前加上 Dear ,并且名字首字母大写select 'Dear'||initcap(ename) from emp--找出姓名为 5 个字母的员工select ename from emp where length(ename) = 5select ename from emp where ename like '_____'--找出姓名中不带 R 这个字母的员工select ename from emp where ename not like '%R%'--显示所有员工的姓名的第一个字select substr(trim(ename),1,1) from emp  --显示所有员工，按名字降序排列，若相同，则按工资升序排序select * from (select * from emp order by sal asc) order by trim(ename) desc --假设一个月为 30 天，找出所有员工的日薪，不计小数select round(sal/30,0) from emp--找到 2 月份受雇的员工select * from empselect * from emp where to_char(hiredate,'fmmm') = '2'--列出员工加入公司的天数(四舍五入）select round(sysdate-hiredate,0) from emp