Oracle——3.时间、类型转换、多表查询

目录

日期函数

隐式数据类型转换

显示数据类型转换

to_char函数对日期的转换

日期格式的函数

to_char函数对数字的转换

nvl函数

多表查询

练习

---

日期函数

系统时间：sysdate

函数	功能
months_between	两个日期相差多少月
add_months	向指定日期加上几个月
next_day	指定日期的下一个星期几所在的日期
round	日期四舍五入
last_day	指定日期的月份的最后一天
trunc	日期截断

--------------------------日期------------------------------------
-- 系统时间
-- sysdate
-- 时间可以进行 加减，是针对天数的处理
select sysdate+1 from dual
-- 两个日期相减，返回的是相隔天数
select (sysdate-hiredate)/7 from emp
-- months_between两个日期相差了多少个月
select months_between(sysdate,hiredate) from emp
-- round 四舍五入
select round(months_between(sysdate,'23-6月-2000')) from dual
-- add_months 如：存钱，6个月之后是哪天(-6是6个月之前)
select add_months(sysdate,6) from dual
-- next_day 表示下一个星期  
-- 比如：今天是星期四，求下一个星期五，那就是明天，因为周五还没过呢
-- 下个星期三（1是周日，2是周一……）
select next_day(sysdate,4) from dual
-- last_day 这个月的最后一天
select last_day(sysdate) from dual
-- round 针对日期 进行四舍五入，天数>15天会进位
-- 如果按照年进行四舍五入，那么，如果 月份大于6月就会进位
select round(to_date('2011-3-25','yyyy-mm-dd'),'month') from dual     --2011/4/1

---

隐式数据类型转换

如果不显式转换数据类型，那么Oracle自动完成下列转换

源数据类型	目标数据类型
varchar2 or char	number
varchar2 or char	date
number	varchar2
date	varchar2

-------------------隐式数据类型转换--------------------
-- 字符串自动转换数字
select * from emp where empno = '7900'
-- 字符串自动转换日期
select * from emp where hiredate = '22-2月-1981'

显示数据类型转换

to_char函数对日期的转换

to_char(date,'format_model')

格式参数：

• 必须包含在单引号中而且区分大小写
• 可以包含任意的有效的日期格式
• 可以使用fm去掉多余的空格或者前导零
• 与日期参数之间用逗号隔开

日期格式的函数

YYYY	以四位年份的格式显示：2022
YEAR	以英文形式来显示年
MM	以两位月份的格式显示：03
MONTH	以中文的形式显示月份
MON	以中文的形式显示月份
DY	以中文的形式显示星期
DAY	以中文的形式显示星期
DD	以两位日期的格式显示：03

时间格式：HH24-MI-SS AM

to_char函数对数字的转换

9	代表一个数字
0	强制显示0
$	显示美元符
L	显示本地货币符号
.	显示小数点
，	显示千位符

-- 字符串转日期格式
select to_date('2022-03-25 13:19:11','yyyy-mm-dd hh24-mi-ss') from dual
-- 将数字转成字符格式
select to_char(123456789,'L999,999,999.00') from dual
-- 将日期格式，转成字符串格式
select to_char(hiredate,'yyyy-mm-dd') from emp
-- 将字符串转成对应的数字
select to_number('38,474,685','99999999') from dual

nvl函数

将空值转换成一个已知的值：nvl（column name，值）

如果列值为空（ ‘’ 或 null ），那么返回后面的值，否则 直接返回前面的值

例：nvl（1,2） 返回 1

        nvl（‘’，2） 返回 2

        nvl（null，2） 返回 2

---

多表查询

----------------------多表查询-------------------------
-- 多表关联查询，也叫 多表查询
-- 笛卡尔积 就是乘积 emp表中的数据*dept表中的数据就是整个的条数
-- 条件 等值条件 条件数，一般情况下，最少是n表-1个条件
-- 在多表查询中，如果有重复的列名时，需要明确到底是哪个表中的数据，可以写成 表名.列名的形式
-- 因为多表关联查询时，多列可能会出现重复名的情况，列名太长的情况下，在使用时，不太方便
-- 所以可以给表起别名，方便正常使用
select emp.deptno from emp,dept where emp.deptno=dept.deptno and emp.ename='SCOTT'
-- 使用别名的方式，来进行多表查询
-- 但是要注意，一旦使用别名了，那么 表名就失效了
select ename,sal,e.deptno from emp e,dept d where e.deptno=d.deptno and e.ename='SCOTT'
-- 多表连接中，非等值连接
select * from emp e,salgrade g
where e.sal>=g.losal and e.sal<=g.hisal

-- 连接方式 有几种
-- 等值连接
-- 非等值连接
-- 99写法的 内连接
-- 外连接：左外连接，右外连接，全连接
-- 内连接和等值连接，查询结果是一样的，就是不同的写法
-- 等值连接与非等值连接都属于内连接
-- 等值连接
select * from emp e,dept d where e.deptno=d.deptno
-- 内连接
select * from emp e inner join dept d on e.deptno=d.deptno
-- 左外连接
-- 左外连接的意思是：join左边的表为主表，右边的表为从表，如果主表和从表有等值关系的数据全部显示出来
-- 如果主表中数据和从表中没有对应的数据，那么主表中的数据也依然显示出来
select * from dept d left join emp e on e.deptno=d.deptno
-- 右外连接
select * from dept d right join emp e on e.deptno=d.deptno
-- 全外连接
select * from dept d full join emp e on e.deptno=d.deptno
-- 自连接
-- 相当于这个表复制出来一份，用于不同的查询
select * from emp e,emp g
where e.mgr=g.empno

---

练习

1.写一个查询，显示所有员工姓名，部门编号，部门名称
2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点
4.查询每个员工编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序

-------------------练习---------------------
-- 1.写一个查询，显示所有员工姓名，部门编号，部门名称
select ename,e.deptno,dname from emp e,dept d where e.deptno=d.deptno
-- 2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
select ename,loc,sal from emp e,dept d where e.deptno=d.deptno and loc='CHICAGO' and comm is not null
-- 3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点
select ename,loc from emp e,dept d where e.deptno=d.deptno and ename like '%A%'
-- 4.查询每个员工编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序
select e.deptno,ename,sal,grade,loc from emp e,dept d,salgrade s 
where e.deptno=d.deptno
and e.sal between s.losal and s.hisal
order by grade
-- 5.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号
select e.ename,e.deptno,e.mgr,m.ename 经理姓名,d.loc 
from emp e,emp m,dept d 
where e.deptno=d.deptno 
and e.mgr=m.empno 
and d.loc in ('NEW YORK','CHICAGO')
-- 6.在上一题的基础上，添加没有经理的员工King，并按照员工编号排序
select e.ename,e.empno,m.ename,m.empno,d.loc 
from dept d inner join emp e on e.deptno=d.deptno
left join emp m on e.mgr=m.empno
where d.loc in('NEW YORK','CHICAGO')
-- 7.查询所有员工编号，姓名，部门名称，包括没有部门的员工也要显示出来
select empno,ename,dname
from emp e left join dept d on e.deptno=d.deptno

select empno,ename,dname from dept d
right join emp e on e.deptno=d.deptno