select * from emp;
--选择部门30中员工的所有信息
select * from emp where deptno=30;
--列出职位为（manager)的员工的编号，姓名
select empno,ename from emp
where job='Manager';
--找出奖金高于工资的员工
select * from emp where comm>sal;
--找出每个员工奖金和工资的总和
select sal+comm,ename from emp;
--找出部门10中的经理（manager）和部门20中的普通员工（clerk）
select * from emp
where(deptno=10 and job='manager')
or(deptno=20 and job='clerk')
--找出部门10中既不是经理也不是员工，而且工资大于等于2000的员工
select * from emp
where deptno=10 and job not in('manager','clerk') and sal>=2000;
--找出有奖金的员工的不同工作
select distinct job from emp where comm is not null and comm>0;
--找出没有奖金或者奖金低于500的员工
select*from emp where comm<500 or comm is null;
--显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select ename from emp order by hiredate;
字符函数
--upper将指定字符改为大写
select upper('abcde')from dual;
select*from emp where ename=upper('smith');
--lower将指定内容改为小写
select lower('ABCDE')from dual;
--initcap将指定内容改为小写首字母大写
select ename,initcap(ename)from emp;
--concat连接两个字符串
select concat('a','b')from dual;
select 'a'||'b'from dual;
--substr(索引目标，索引值，显示位数)
select substr(ename,0,1)from emp;
--length显示字符长度
select ename,length(ename)from emp;
--replace将目标中的所有A替换成白
--replace（目标，要替换内容，替换后内容）
select replace(ename,'A','白')from emp;
--instr
select instr('hellow world','or')from dual;
--lpad在smith目标左边填充增加10个“*”号
select lpad('smith',10,'*')from emp;
--rpad在目标右侧填充
select rpad('smith',8,'&')from emp;
--trim过滤目标两边的空格
select trim(ename)from emp;
数值函数
--round取整数四舍五入
select round(sal/30)from emp;
--trunc取整数去除小数点后所有
select trunc(sal/30)from emp;
日期函数
--months_between()求目标时间到当前时间的月数
select hiredate,months_between(sysdate,hiredate)from emp;
--add_months()在当前时间月份上增加三个月
select add_months(sysdate,3)from dual;
--next_months()
select next_day(sysdate,'星期一')from emp;
--last_day()当前时间最后一天
select last_day(sysdate)from dual;
转换函数
--to_char
select to_char(sysdate,'yyyy')from dual;
select to_char(sysdate,'fmyyyy-mm-dd')from dual;
select to_char(sal,'l999,999,999')from emp;
select to_char(sysdate,'mm')from dual;
--to_number将两个目标相加
select to_number('13')+to_number('14')from dual;
--to_date设置日期
select to_date('20090210','yyyymmdd')from dual;
练习
--找出每个月倒数第三天受雇的员工
select * from emp where last_day(hiredate)-2=hiredate;
--找出25年前雇的员工
select * from emp where hiredate<=add_months(sysdate,-25*12);
--所有员工名字前加上dear，并且名字首字母大写
select 'dear'||initcap(ename)from emp;
--找出姓名为5个字母的员工
select * from emp where length(ename)=5;
--找出姓名中不带R这个字母的员工
select * from emp where ename not like'%R%';
--显示所有员工的姓名的第一个字
select substr(ename,0,1)from emp;
--显示所有员工，按名字降序排列，若相同，则按工资升序排序
select * from emp order by ename desc;
--假设一个月30天，找出所有员工的日薪，不计小数
select sal/30 from emp;
--找出2月份受雇的员工
select * from emp where to_char(hiredate,'fmmm')='2';
--列出员工加入公司的天数（四舍五入）
select hiredate,round(months_between(sysdate,hiredate)*30)as comeday from emp;
--分别用case和decode函数列出员工所在的部门，deptno=10显示部门10；
分组函数
--count计算总数
select count(ename)from emp;
--avg
select avg(s#)from student;--平均值
--max min sum
select max(sal)from emp;--最大值
select min(sal)from emp;--最小值
select sum(s#)from student;--总和
--使用where字句限定查询条件
--使用order by字句指定排序方式
--不允许在where字句中使用分组函数
--group by
--分组函数嵌套
select max(avg(sal))from emp group by deptno;
练习
--分组统计各部门下工资>500的员工的平均工资
select * from emp;
select avg(sal)from emp where sal>500 group by deptno;
--统计各部门下平均工资>500的部门
select deptno,avg(sal)from emp where sal>500 group by deptno;
select deptno,avg(sal)from emp group by deptno having avg(sal)>500;
--算出部门30中得到奖金最多的员工奖金
select max(comm)from emp where deptno=30;
--算出每个职位的员工数和最低工资
select * from emp;
select job,min(sal),count(*)from emp group by job;
--算出每个部门，每个职位的平均工资和平均奖金，如果平均奖金大于300，显示‘奖金不错’，
--如果平均奖金100到300，显示‘奖金一般’，如果平均奖金小于100，显示‘基本没有奖金’，
--按部门编号降序，平均工资降序排列
select job,avg(sal),avg(comm)from emp group by job having avg(comm)>300
select avg(sal) as 平均工资,avg(comm) as 平均奖金,
case
when avg(comm)>300 then '奖金不错'
when avg(comm)<100 and avg(comm)>300 then '奖金不错'
end from emp
group by job order by avg(comm),avg(sal)desc;
通用函数
--nvl()
select nvl(comm,0)from emp;
--nvl2()comm存在total显示0不存在显示1
select empno,ename,sal,comm,nvl2(comm,0,1)total from emp;
--coalesce()
select empno,ename,sal,comm,coalesce(sal+comm,sal,0)from emp;
--case表达式
select empno,ename,sal,
case deptno
when 10 then '财务部'
when 20 then '研发部'
when 30 then '销售部'
else '未知部门'
end 部门 from emp;
--decode()
select empno,ename,sal,decode(deptno,10,'财务部'，20,'研发部',30,'销售部','未知部门')部门 from emp;
--单行函数嵌套
select empno,lpad(initcap(trim(ename)),10,'')name,job,sal from emp;
练习
--找出每个月倒数第三天受雇的员工
select ename from emp where last_day(hiredate)-2=hiredate;