文章目录
- 1、基础查询
- 2、where过滤
- 3、like模糊查询
- 4、order by排序(默认升序asc)
- 5、单行函数
- 6、数值函数
- 7、转换函数
- 7、时间日期函数
- 8、条件表达式
- 9、分组函数
- 10、where和having区别
- 11、练习
1、基础查询
-- 查询所有员工信息
select * from emp;
-- 查询员工号,姓名,月薪,奖金,年薪(表达式)6+
select empno,ename,sal,comm,sal*12 from emp;
-- 别名as可以省略,如果别名中间有空格,需要使用""引起来
select empno,ename,sal as 月薪,comm,sal*12 "年 薪" from emp;
-- 查看员工表不同的部门编号(distinct)去掉重复的
select distinct deptno from emp;
-- 查看不用部门的不同工种(distinct作用于后面出现的所有列)
select distinct deptno,job from emp;
-- 输出计算表达式3+20*5,显示当前日期 sysdate
select 3+20*5,sysdate from dual;
2、where过滤
-- where进行过滤
-- 查询10号部门的员工信息
select* from emp where deptno=10;
-- 查询员工名字为KING的员工信息(表中列值区分大小写)
select* from emp where ename='KING';
-- 查找薪水不等于1250员工的信息
select* from emp where sal!=1250;
select* from emp where sal<>1250;
-- 查询入职日期为1981年11月17日的员工信息(默认日期格式dd-mm-yy)
select* from emp where HIREDATE='17-11月-81'
--查找工资1000到2000的员工信息(between闭区间,在什么之间)
select* from emp where sal>=1000 and sal<=2000;
select* from emp where sal between 1000 and 2000;
-- 查询部门编号10或者20
select* from emp where deptno=10 or deptno=20;
-- 查询10号部门,工资为1300的信息
select* from emp where deptno=10 and sal=1300;
-- 查询81年2月到82年2月(不含2月)
select* from emp where hiredate>='1-2月-81' and hiredate<'1-2月-82';
--查询奖金为null的(不能使用等号)
select* from emp where comm is null;
-- where 条件中使用in--in后面是一个集合
-- 查询部门号是10或者20的员工信息
select* from emp where deptno in(10,20);
-- 查询不是10和20号部门的员工信息
select* from emp where deptno!=10 and deptno!=20;
select* from emp where deptno not in(10,20);
3、like模糊查询
-- like 模糊查找,其中:'%'匹配任意多个字符。'-'匹配一个字符
--查询员工首字母是S的员工
select* from emp where ename like 'S%';
-- 查询员工编号为79开头的员工信息
select* from emp where empno like '79%';
-- 查询名字为四个字母长度的员工信息
select* from emp where ename like '____';
-- 查询名字带_的信息(需要用\转义,并且要声明escape '\'是转义符)
select* from emp where ename like '%\_%' escape '\';
4、order by排序(默认升序asc)
-- 排序 order by (列名、别名、表达式,默认升序asc,降序desc)用在最后
--员工信息按照入职日期先后排序
select* from emp order by hiredate;
-- 员工薪水按从大到小进行排序
select* from emp order by sal desc;
-- 查询员工信息按照奖金逆序(nulls last 放到最后,默认他认为null无穷大)
select* from emp order by comm desc nulls last;
-- 员工信息按部门升序、薪资降序排列(asc和desc作用于最近的前面的一列,多列排序时,先安装第一列排序,相同时则安装第二列排序)
select* from emp order by deptno asc, sal desc;
-- 使用序号进行排序
-- 查询员工编号,姓名,工资,按照序号(工资)进行排序(序号从1开始)
select empno,ename,sal from emp order by 3;
-- 使用别名进行排序
-- 按员工的年收入进行排序
select empno,ename,sal,sal*12+nvl(comm,0) 年收入 from emp order by 年收入;
5、单行函数
-- 单行函数:只对针对一行进行,返回一行记录
-- 1、lower小写,upper大写,initcap单词的首字母大写
select lower('Hello') "小写",upper('hello') "大写",initcap('hello') "首字符大写" from dual;
-- 2、concat(连接符||)不支持多个参数,可以嵌套
select concat(concat('hello','world'),'111') from dual;
-- 可以连接两个以上字符串
select 'hello'||'world' from dual;
-- 3、字符串截取substr('helloworld',1,3) 第三个参数不写默认截取到最后,负数表示从倒数开始截取
select substr('helloworld',1,3) from dual;
-- 4、instr('hello','llo') 返回存在的位置
select instr('hello','llo') from dual;
-- 5、lpad和rpad('aaa',10,'#') 如果str长度不够len,l左边补#,r右边补#
select lpad('111',10,'#'),rpad('111','10','#') from dual;
-- 6、trim(' hello ') 去掉首部和尾部的空格,(trim('x' from 'xxxxhelloxxx') 去掉两端的x)
select 'helo'||trim(' hello ') from dual;
-- 7、replace(str,old,new) 将str字符串中的old 替换成new
select replace('hello','llo','ddddd') from dual;
-- 8、length 和 lengthb (对于字母没有区别,对中文有去吧,length中文占1个,lengthb中文占2个)
select length('hello'),lengthb('hello') from dual;
select length('中文'),lengthb('中文') from dual;
6、数值函数
-- 数值函数
-- 1、round 四舍五入(取两位小数)
select round(48.115,2) from dual;
-- 2、trunc(33.12,2) 截取,保留两位小数,直接舍弃后面的
select trunc(48.115,2) from dual;
-- 3、mod 取模(余数)
select mod(1600,300) from dual;
-- 4、ceil向上 floor向下取整
select ceil(48.115,2) from dual;
select floor(48.115,2) from dual;
7、转换函数
-- 转换函数:
-- 1、to_char 和to_number
-- 9表示数字、0零、$美元符、L本地货币符号、.小数点、,千位符
-- 把薪水转换 为 本地货币字符串
select empno, sal,to_char(sal,'L9,999') from emp;
-- 把上述某个结果转回数值型
select to_number('¥2,999','L9,999') from dual;
-- 2、to_char 与to_date
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
select to_date('2021-11-23 07:41:42 今天是 星期二','yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;
-- 查询1981-11-17日入职的员工信息
select* from emp where to_char(hiredate,'yyyy-mm-dd')='1981-11-17';
select* from emp where hiredate=to_date('1981-11-17','yyyy-mm-dd');
7、时间日期函数
-- 时间和日期函数
-- 显示当前系统日期
select sysdate from dual;
-- 显示当前的系统日期显示到秒
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
-- 显示星期几
select to_char(sysdate,'day') from dual;
-- 显示年
select to_char(sysdate,'yyyy') from dual;
-- 显示昨天今天明天
select sysdate-1 昨天, sysdate 今天, sysdate+1 明天 from dual;
-- 计算员工工龄 可以按照日、周、月、年、日期差减
select empno,ename,sysdate-hiredate 日,(sysdate-hiredate)/7 周,(sysdate-hiredate)/30 from emp;
-- 日期函数months_between 月差,add_months last_day next_day
select empno,ename,months_between(sysdate,hiredate) from emp;
-- add_months(在本月基础上加2个月)
select add_months(sysdate,2) from dual;
-- last_day(sysdate) 每个月最后一天
select last_day(sysdate) from dual;
-- next_day(sysdate,'星期一') 当前日期的下一个星期一是多少号
select next_day(sysdate,'星期一') from dual;
-- 对日期型四舍五入(过了一半就会入) round 截断trunc
select round(sysdate,'month') from dual;
select round(sysdate,'year') from dual;
select trunc(sysdate,'month') from dual;
8、条件表达式
-- 老板打算给员工涨工资,总裁1000,经理800,普通员工400,将涨前,涨后薪水列出
select empno,ename,job,
sal "涨前薪水",
case job
when 'president' then
sal+1000
when 'manager' then
sal+800
else
sal+400
end "涨后薪水"
from emp;
select empno,ename,job,sal "涨薪水前",decode(job,'president',sal+1000,'manager',sal+800,sal+400) "涨薪后" from emp;
9、分组函数
-- 分组函数:也称之为组函数或者聚合函数,oracle提供的常用的分组函数有sum,avg,count,max,min
-- 统计员工总人数
select count(empno) "员工人数" from emp;
-- 统计工种人数
select count(distinct job) from emp;
-- 求员工平均工资
select avg(sal) from emp;
-- 求员工评价奖金
select avg(nvl(comm,0)) from emp;
-- 求员工表中最高工资和最低工资
select max(sal),min(sal) from emp;
-- 分组数据
-- 说明按照group by 后给定的表达式,将from后面的table进行分组,针对每一组,使用组函数
-- 统计各个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
10、where和having区别
-- 统计各个部门不同工种的平均工资(前面查询的列要和后面的分组对应,没有出现在分组函数内,必须出现在group by后)
select deptno, job, avg(sal) from emp group by deptno,job;
-- 统计各个部门平均工资高于2000的(group by不能用where,必须用having加在group by 后)
select deptno, avg(sal) from emp group by deptno having avg(sal)>2000;
-- 求10号部门的平均薪水
select deptno,avg(sal) from emp where deptno=10 group by deptno;
select deptno,avg(sal) from emp group by deptno having deptno=10;
-- where和having区别
-- where用于对数据的第一次过滤,having只能用于分组数据之后过滤
-- 如果where和having同时出现了,则where应该出现在having前面
11、练习
-- 求十号部门的最低薪水和最高薪水
select max(sal),min(sal) from emp where deptno=10;
-- 求1980年12月17日入职的员工信息
select* from emp where to_char(hiredate,'yyyy-mm-dd')='1980-12-17';
select* from emp where hiredate=to_date('1980-12-17','yyyy-mm-dd');
-- 当前日期所在的月份的最后一天和第一天,显示格式yyyy-mm-dd
select to_char(last_day(sysdate),'yyyy-mm-dd') from dual;
select to_char(sysdate,'yyyy-mm') || '-01' from dual;
-- 查询名字中有字母A,并且是MANAGER的员工
select * from emp where job='MANAGER' and ename like '%A%';
-- 将员工信息按照部门倒序,薪水升序排列
select* from emp order by job desc,sal asc;
-- 将薪水在1200以上员工
select count(empno) from emp where sal>1200;
-- 求每个部门员工数
select deptno,count(empno) from emp group by deptno;