Oracle-day2：随机函数、innot in、大小写转换、范围查询、日期类型、空值与非空值、排序、条件判断

前提：

1、system账户

2、oracle数据库

3、操控的是scott的emp表

一、随机函数

/* 一、随机函数
*/
-- 随机函数：dbms_random.value()
select dbms_random.value() from dual;-- 传递参数范围(大于1，小于10）
select dbms_random.value(1,10) from dual;-- 如果想要到参数b的值，a可从0开始，后把随机数向上取整
select ceil(dbms_random.value(0,47)) from dual;-- 找出emp表中，工资>800 喝 工资<900 的销售员
select * from scott.emp where 
(sal > 800 and job = 'CLERK' )
or
(sal > 900 and job = 'SALESMAN');

二、in() 和 not in() 子查询

/*二、、多列子查询分别有：not in() -- 不包含、 in() -- 包含这两个运算符用于where表达式中，以列表项的形式支持同一个项（列）的多个值选择例如：--1、 in()的举例select * from 表 where 查询项 in (val1,val2,......)--2、not in() 的举例 -- 不存在select * from 表 where 查询项 not in(val1,val2,......)
*/--2.1例题：查询出全部职员CLERK、分析员ANALYST或推销员SALESMAN的姓名、工种、工资和所在部门号
select ename,job,sal,deptno from scott.emp
where job in ('CLERK','ANALYST','SALESMAN'); -- 查询出job项中包含的三个字段数据

三、大小写转换

/*三、大小写转换函数3.1、upper('字符串') -- 字母转大写3.2、lower('字符串') -- 字母转小写
*/
-- 1、upper传入小写后转为大写
select ename,job,sal,deptno from scott.emp
where job in(upper('clerk'),'analyst');-- 2、在emp表中找出20号部门中，不是经理和分析员ANALYST的记录
select * from scott.emp where deptno = 20 and job <> 'MANAGER'
and job !='ANALYST';
-- 2.1 用 not in()
select * from scott.emp where deptno = 20 and job not in('MANAGER','ANALYST');

四、between A and B 范围内查询

/*四、某个范围内的判断：between A and B在A 和 B 之间 -- 相当于 >= A and <= B同时：也可以判断不在A到B的范围，加上 notnot between A and B 即：不在A到B之间，相当于 < A or > B
*/
-- 4.1 例题：查询所有薪水在2000-4000范围内的员工信息
select * from scott.emp where sal between 2000 and 4000;-- 4.2 查询所有薪水不在2000-4000范围内的员工信息
select * from scott.emp where sal not between 2000 and 4000;-- 数字、字符串可以用范围，日期也可以用到范围
-- 4.3 查询1981年之内入职的员工信息，雇佣日期是date类型
-- 1981年之间 --> 1981-01-01 到 1981-12-31

五、日期类型

5.1 日期格式字符串转为日期类型

/*五、日期类型5.1、date关键字 将日期格式的字符串转成日期 date'4位年份-2位月份-2位天数'
*/
--写法一、查询1981-01-01 到 1981-12-31的员工
select * from scott.emp 
where hiredate >= date'1981-01-01' and hiredate <= date'1981-12-31';
--写法二
select * from scott.emp
where hiredate between date'1981-01-01' and date '1981-12-31';

5.2 中文日期格式

/*5.2 中文格式日期：'2位天数-数字+月-4位年份'
*/
-- 写法一：and
select * from scott.emp where
hiredate >= '01-1月-1981' and hiredate <= '31-12月-1981';-- 写法二、between
select * from scott.emp where
hiredate between '01-1月-1981' and '31-12月-1981';

5.3 to_date('日期str','格式') 转日期

/*5.3 to_date：将日期格式的字符串转成日期语法：to_date('日期格式字符串','日期格式')
*/
-- 写法1、and
select * from scott.emp where 
hiredate >= to_date('1981-01-01','yyyy-mm-dd')
and
hiredate <= to_date('1981-12-31','yyyy-mm-dd');-- 写法2、between
select * from scott.emp where 
hiredate between to_date('1981-01-01','yyyy-mm-dd')
and
to_date('1981-12-31','yyyy-mm-dd');

5.4 to_char 截取日期转字符串

/*5.4 to_char 转字符串返回值：字符串可用于将日期格式的列中的年份截取格式：to_char(日期字符串,'日期格式')
*/
-- 截取到hiredate中的年份
-- 1、查询截取到日期字符串的年份
select ename,hiredate,to_char(hiredate,'yyyy') from scott.emp;-- 2、查询年份为1981的
select * from scott.emp where to_char(hiredate,'yyyy') = '1981';-- 3、查询年份为1981，但是判断的值为数字类型(跟2同样有效）
select * from scott.emp where to_char(hiredate,'yyyy') = 1981;-- 4、能否截取其他格式？
-- 4.1 截取到年-月
select ename,hiredate,to_char(hiredate,'yyyy-mm') from scott.emp;
-- 4.2 截取到日-月
select ename,hiredate,to_char(hiredate,'ywmm-dd') from scott.emp;

5.5 练习

-- 5、统计1月份入职的员工信息(返回来的月是2位数)
-- 写法一、
select * from scott.emp where
to_char(hiredate,'mm') = '01';
-- 写法二、
select * from scott.emp where
to_char(hiredate,'mm') = 01;-- 6、统计1-6月份入职的员工信息
-- between
-- 写法一、in()找出满足1-6月的
select * from scott.emp where 
to_char(hiredate,'mm') in('01','02','03','04','05','06');-- 写法二、not in() 找出不满足1-6月的
select * from scott.emp where 
to_char(hiredate,'mm') not in('07','08','09','10','11','12');-- 写法三、通过逻辑运算符
select * from scott.emp where 
to_char(hiredate,'mm') = '01' or
to_char(hiredate,'mm') = '02' or
to_char(hiredate,'mm') = '03' or
to_char(hiredate,'mm') = '04' or
to_char(hiredate,'mm') = '05' or
to_char(hiredate,'mm') = '06';-- 写法四、通过between A and B
select * from scott.emp where 
to_char(hiredate,'mm') between 01 and 06;select * from scott.emp where 
to_char(hiredate,'mm') between '01' and '06';-- 写法五、大于等于
select * from scott.emp where 
to_char(hiredate,'mm') >= 01
and
to_char(hiredate,'mm') <= 06;

六、空与非空值

/*六、空值和非空空值：null非空：not null与这两者有关的查询：is null 和 is not null不能直接做 = null 或者 != nullnvl(a,b) 空值替换函数，如果a为空，则返回b，如果a不为空，则返回a
*/
-- 6.1 查找出emp表中所属经理（MGR）为空的职工的有关信息
select * from scott.emp where mgr is null; -- 正确写法
-- 需要注意到错误的写法
-- select * from scott.emp where mgr = null;
-- select * from scott.emp where mgr != not nul;-- 6.2 查找emp表中奖金为空的记录
select * from scott.emp where comm is null;-- 6.3 查找出emp表中奖金不为空的记录
select * from scott.emp where comm is not null;-- 6.4 查找emp表中奖金不为空（有奖金的情况下）出现为0的情况
select * from scott.emp where comm > 0;
-- 同时键入大于0且不为空
select * from scott.emp where comm is not null and comm > 0;-- nvl(a,b) 空值替换函数，如果a为空，则返回b，如果a不为空，则返回a-- 6.5 查询奖金，奖金为空返回0，奖金为空返回1,不为空的返回本体comm
select comm,nvl(comm,0),nvl(comm,1) from scott.emp;-- 6.6 查询emp表中，奖金comm不为0的
-- 这里如果comm是null，那么就返回0，但是0又<>0,所以获取到的是非0的值
select * from scott.emp where nvl(comm,0) <> 0;-- 6.7 查找emp表中没有奖金的记录（注意奖金为0的情况）
-- 写法1、
select * from scott.emp where comm is null or comm = 0;
-- 写法2、
select * from scott.emp where nvl(comm,0) = 0;-- 6.8 在emp表中查询工资+奖金之和大于1000的记录
-- 写法1、
select emp.*,sal+nvl(comm,0) from scott.emp where (sal + nvl(comm,0)) > 1000;-- 6.9 一个有效值 + 空值，返回的数据类型：null
select sal,comm,sal+comm from scott.emp;
-- 一个有效值+空值返回的类型是空值，这个时候需要对null值进行nvl处理
select sal,comm,sal+comm,sal+nvl(comm,0) from scott.emp;

七、排序

/*七、排序 order by语法： oorder by 列名(表达式)asc -- 默认，表示升序，从小到大，可省略desc -- 表示降序，从大到小   随机排序，校验数据时使用  -- 也使用到random.value（排序时，若一列的值相同，则以下一列做判断）（排序时，可用表达式、别名）（排序时，要考虑空值的情况，如果不处理空值，则空值的记录会影响排序）升序排列时空值会排在最后，降序时排在最前如果在排序中有多个排序，在order by 列1 排序,列2 排序... 即可
*/
-- 7.1 找出工资大于800的职员并按照工资高低排序
select * from scott.emp where 
sal > 800 and job = 'CLERK'
-- 升序排序
order by sal;
;-- 7.2 找出工资大于800的职员 和 工资大于900的职员，并按工资升序，雇员编号降序
select * from scott.emp where 
(sal > 900 and job = 'CLERK')
or
(sal > 900 and job = 'SALESMAN')
-- 工资升序
order by sal,empno desc;
;-- 7.3 找出工资增加500以后大于1500的信息并按增加后的工资降序雇员编号升序排列
select emp.*,nvl(sal,0)+500 as sal1 from scott.emp where
nvl(sal,0)+500 > 1500
-- 排序
order by sal1 desc,empno;
;-- 7.4 找出工资增加500后大于1500的信息 并按 奖金降序，雇员编号升序排列
select emp.*,nvl(sal,0)+500 as sal1 from scott.emp where
nvl(sal,0)+500 > 1500
-- 排序
order by nvl(comm,0) desc,empno;
;-- 7.5 随机排序 -- 
select * from scott.emp order by dbms_random.value();

八、以上相关练习

/*八、课时相关练习
*/
--1、查询emp表中所有记录按照部门号升序，员工编号降序排列
select * from scott.emp
--排序
order by deptno,empno desc;
;--2、计算每个员工的年度总报酬，并按总报酬由高到低顺序显示  (nvl空值处理函数)
select emp.*,nvl(sal,0) * 12 as sumSal from scott.emp
-- 排序
order by sumSal desc;
;--3、查找出工资高于1000元的职工的姓名、工种、工资和部门号，并按部门号由小到大排序显示
select ename,job,sal,deptno from scott.emp
where 
nvl(sal,0) > 1000
order by deptno;
;--4、查找出奖金超过本人基本工资3%的职工的姓名，工资，奖金，奖金与工资的比例，并按其比例由高到低显示
select 
ename,sal,nvl(comm,0),nvl(comm,0)/sal as commSal
from scott.emp
where 
nvl(comm,0) > sal * 0.03
order by commSal desc;
;--5、按工种升序，而同工种按工资降序排列显示全部职工的姓名，工种，工资。
select ename,job,sal from scott.emp
order by job,nvl(sal,0) desc;
;--6、查询所有薪水在2000-4000范围内并且部门号是10或(和)30的员工信息 
select * from scott.emp
where 
nvl(sal,0) between 2000 and 4000;
and
deptno in(10,30)
;--7、查询所有没有奖金的部门不是30员工信息
select * from scott.emp
where 
nvl(comm,0) = 0
and
deptno <> 30
;--8、查询所有部门编号是10或30，姓名里面包含A的记录
select * from scott.emp
where
deptno in(10,30)
and
ename like '%A%'
;--9、查询所有20部门并且薪水超过2000的姓名里面包含ALL的员工的记录
select * from scott.emp
where 
deptno = 20
and
nvl(sal,0) > 2000
and
ename like '%ALL%'
;--10、查询年薪超过10000的员工的姓名，编号，薪水，年收入，按照年薪降序排列 
select 
ename,empno,nvl(sal,0),nvl(sal,0)*12 as sumSal 
from scott.emp
where
nvl(sal,0)*12 > 10000;
order by sumSal desc;
;--11、查询入职日期在1981-5-1到1981-12-31至间的所有员工信息
select * from scott.emp
where
hiredate between to_date('1981-05-01','yyyy-mm-dd')
and
to_date('1981-12-31','yyyy-mm-dd')
;--12、找出1981年下半年入职的员工
select * from scott.emp
where
hiredate between to_date('1981-06-01','yyyy-mm-dd')
and
to_date('1981-12-31','yyyy-mm-dd')
;--13、要求查询基本工资不大于1500，同时不可以领取奖金的雇员信息
select * from scott.emp
where
nvl(sal,0) < 1500
and
nvl(comm,0) = 0
;

九、条件判断

*九、条件判断：case when 语句casewhen 条件判断1 then 条件1为true的时候执行when 条件判断2 then 条件2为true的时候执行when 条件判断n then 条件n为true的时候执行else所有条件都不成立的时候执行end [表示结束，可取别名,可中文]--------------------补充一个 decodedecode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
*/
-- 9.1 emp表中，工资<2000的为C，2000-3000的为B，3000以上为A，其他情况为D
select emp.*,casewhen sal < 2000 then 'C'when sal between 2000 and 3000 then 'B'when sal > 3000 then 'A'else 'D'end "工资等级"
from scott.emp;-- 9.2 emp表中，工资<2000的为C，2000-3000的为B，3000以上为A，其他情况为D -- 一级为一列
select emp.*,case when sal < 2000 then 'C' end "等级C",case when sal between 2000 and 3000 then 'B' end "等级B",case when sal > 3000 then 'A' end "等级A",case when sal is null then 'D' end "等级D"
from scott.emp;-- 9.3 1981年及以前入职的，显示为”老员工“，1982以后入职的，为新员工
select emp.*,case when to_char(hiredate,'yyyy') <= 1981  then '老员工' else '新员工'end "级别"
from scott.emp;-- 9.4 在emp表根据岗位分类，显示如果job是PRESIDENT显示为老板，job是MANAGER为经理，其他是员工
select emp.*,case when job = 'PRESIDENT' then '老板'when job = 'MANAGER' then '经理'else '员工'end 职位from scott.emp;