--“%”表示零或多个字符“-”对于特殊字符可以用escape(逃避)标识符来查找

select * from emp where ename like '%*_%' escape '*';

select * from emp where ename like '%S%';

--逻辑运算符

--选择在部门30中所有的员工

select * from emp where deptno=30;

--列出职位为MANAGER的员工的信息

select * from emp where job='MANAGER';

--找出奖金高于工资的员工

select * from emp where comm>sal;

--列出每个员工的每个月的工资和奖金的总和

select ename, sal+nvl(comm,0) income from emp;

--找出部门10中的经理(MANAGER)和部门20中的办事员(CLERK)

select ename,job,deptno 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 ename,job,comm from emp where comm is not null and comm>0;

--找出没有奖金或者奖金低于500的员工

select * from emp where comm is null or comm<500;

--显示雇员姓名和雇佣日期,根据雇佣年限,将最老的员工放在最前面

select ename,hiredate from emp order by hiredate;

--单行函数

--字符函数

select upper('abcd')from dual;

select * from emp where ename=upper('smith');

select lower('ABCD')from dual;

select initcap(ename)from emp;

select concat('a','b')from dual;

select 'a'||'b'from dual;

select substr('abcdefg',length('abcdefg')-2)from dual;

select substr('abcde',-4,3)from dual;

select ename,length(ename)from emp;

select replace(ename,'A','a')from emp;

--"instr()"函数两个参数代表在在第一个字符串中查找第二个字符串,若查不到,返回0,若查到,返回索引值(空格也占一个字符)

select instr('hello world','or')from dual;

--"lpad(rpad)"代表ename字段占据10个字符,如果不够则左(右)边由&填充

select lpad('ename',10,'&')from emp;

select rpad('ename',10,'&')from emp;

--trim函数的使用(过滤首尾空格)

select trim(' smith jonny ')name from dual;

--数值函数

select round(412,-2)from dual;

select round(412.235,2)from dual;

select mod(10,4)from dual;

select trunc(431.2596,-2)from dual;

select trunc(431.2596,2)from dual;

--日期函数

select months_between(sysdate,hiredate)dates from emp;

select months_between(hiredate,sysdate)dates from emp;

select add_months(sysdate,1)from dual;

select next_day(sysdate,'星期一')from dual;

select last_day(sysdate)from dual;

--转换函数

select ename,to_char(hiredate,'yyyy')year from emp;

select to_char(sysdate,'fmyyyy-mm-dd')from dual;

select ename,to_char(sal,'L999,999.99') from emp;

select to_char(sysdate,'D')from dual;

select to_number('1')+to_number('2')sum from dual;

select to_date('20200915','yyyy-mm-dd')mydate from dual;

select to_date('2020-01-01','yyyy-mm-dd')from dual;

--通用函数

select ename,nvl(comm,0)from emp;

select ename,empno,sal,comm,nvl2(comm,comm+sal,sal)total from emp;

--"nullif(exp1,exp2)"如果表达式exp1的值与exp2相等则返回null否则返回exp1

select nullif(1,2)from dual;

--"coalesce()"函数,依次考察各个表达式的值,遇到非null值即停止并返回该值

select empno,ename,sal,sal,coalesce(sal+comm,sal,0)income from emp;

--"case"表达式

select ename,empno,deptno,sal,

case deptno

when 10 then '财务部'

when 20 then '研发部'

when 30 then '销售部'

else '未知部门'

end 部门

from emp;

--"decode"函数

select ename,empno,deptno,sal,

decode(deptno,10,'财务部',

20,'研发部',

30,'销售部',

'未知部门')部门

from emp;

--单行函数嵌套

select empno,lpad(initcap(ename),10,'&')name from emp;

--找出每个月倒数第三天入职的员工

select * from emp where hiredate=last_day(hiredate)-2;

--找出30年前受雇的员工

select * from emp where sysdate>=(add_months(hiredate,30*12));

--所有员工的名字前面加上‘Dear’,并且首字母大写

select 'Dear '||initcap(ename)name from emp;

--找出姓名为5个字母的员工

select ename from emp where length(trim(ename))=5;

--找出姓名中不带R的员工

select * from emp where ename not like '%R%';

--显示所有员工姓名的第一个字母

select substr(ename,0,1)from emp;

select substr(ename,1,1)from emp;

--显示所有员工按名字降序排列,若相同,按照工资升序排列

select * from emp order by ename desc,sal asc;

--假设一个月30天,找出所有员工的日薪,不计小数

select ename, trunc(sal/30)from emp;

--找出2月份受雇的员工

select * from emp where to_char(hiredate,'mm')=2;

--列出员工加入公司的天数

select ename, round(sysdate-hiredate)days from emp;

--分组函数

--count()函数,如果数据库表里没有数据count(*)返回的不是null而是0

select count(comm)from emp;

--分组函数与空值(分组函数省略列中的空值)

select avg(comm)from emp;

select avg(nvl(comm,0))from emp;

--出现在SELECT 列表中的字段或者出现在order by 后面的字段，如果不是包含在分组函数

--中，那么该字段必须同时在GROUP BY 子句中出现,包含在 GROUP BY 子句中的字段则不必须出现在SELECT 列表中。

--可使用where 字句限定查询条件,可使用Order by 子句指定排序方式

--如果没有GROUP BY 子句，SELECT 列表中不允许出现字段(单行函数)与分组函数混用的情况。

select ename from emp;--合法

select avg(sal)from emp;--合法

select ename,avg(sal)from emp;--非法

--不允许在where子句中使用分组函数

select avg(sal) from emp where avg(sal)>2000 group by deptno;--非法

--"having"子句

select deptno,avg(sal)from emp where hiredate>=to_date('19810501','yyyy-mm-dd')

group by deptno,job having avg(sal)>2000 order by deptno,job;

--分组函数嵌套

select max(avg(sal))from emp group by deptno;

ITPub博客

菜鸟级Oracle查询语句<二>