Oracle数据库(The Second Day)
使用exists的查询
描述:exists条件为true,存在记录则返回结果,后续不再继续 比较查询,与查询的字段无关,与记录有关
难点:外层结果集 内层结果集 关系列(没有关系列 true)
——无关系列时
select *
from emp
where exists
(select deptno,dname from dept where dname in ('SALES', 'ACCOUNTING'));
用java循环理解方式:
while(emp 的12条记录){
while(dept的2条记录){
if(true){
System.out.println(...);
break;
}
}
}
输出12条语句,exists只是判断子查询中的记录是否存在,为true还是false。true则显示查询表的所有记录,false则不显示
——有关系列时
select *
from emp e
where exists (select deptno, dname
from dept d
where dname in ('SALES', 'ACCOUNTING')
and e.deptno = d.deptno);
java理解
while(emp 的14条记录){
while(dept的2条记录){
if(e.deptno=d.deptno){
System.out.println(...);
break;
}
}
}
先判断记录是否存在,再查询e.deptno = d.deptno的记录
效率:in是全盘扫描,效率较低,exists效率较高
单行函数
特征
- 单行函数对单行操作
- 每行返回一个结果
- 有可能返回值与原参数数据类型不一致(转换函数)
- 单行函数可以写在SELECT、WHERE、ORDER BY子句中
- 有些函数没有参数,有些函数包括一个或多个参数
- 函数可以嵌套
分类
1.日期函数
—to_date(c,m) -> 将字符串c以指定格式转换为日期m
select to_date('2021-06-06 08:00:00','yyyy-mm-dd hh24:mi:ss') from dual;
y:年 m:月 d:日 hh24:24小时制 mi:分钟 s:秒
—sysdate/current_date 以date类型返回当前的日期
-- 当前日期
select sysdate from dual;
select current_date from dual;
-- 今天后的第二天(后天)
select sysdate+2 from dual;
sysdate+d(d为整数):d>0表示未来的第d天;d<0表示过去的第d天
—add_months(d,x) 返回加上x月后的日期d的值
--雇员入职了两个月
select ename,hiredate, add_months(hiredate,2) after from emp;
—LAST_DAY(d) 返回的所在月份的最后一天
--雇员入职当月的最后一天
select ename,hiredate, last_day(hiredate) d from emp;
—months_between(date1,date2) 返回date1和date2之间月的数目
--雇员入职后到当前时间的月数
select ename, months_between(sysdate , hiredate) from emp;
—next_day(sysdate,星期一) 下一个星期一
--下个星期二
select next_day(sysdate, '星期二') from dual;
2.转换函数
to_char(d,m) -> 将日期d以指定格式转换为字符串m
--将当前时间转换为指定字符串
select to_char(sysdate,'yyyy-mm-dd') time from dual;
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') time from dual;
3.nvl函数(处理null)
--nvl nvl(字段,自定义值) 如果字段为null,则结果为自定义值
select ename, nvl(null,0) from emp;
select ename, nvl(to_char(comm),'hello') from emp;
4.decode函数与case when then else end
decode:decode(condition,case1,express1,case2 , express2,….casen , expressn,
expressionm)如果expression=value1,则输出result1,expression=value2,输出reslut2,expression=value3,输出result3
举个栗子:给所有的员工 涨薪,10–>10% 20–>8% 30 -->15% 其他–>20% --decode
--decode
select ename,
sal,
deptno,
decode(deptno,
10,sal * 1.1,
20,sal * 1.08,
30,sal * 1.15,
sal * 1.2) raisesal
from emp;
--case when then else end
select ename,
sal,
deptno,
(case deptno
when 10 then
sal * 1.1
when 20 then
sal * 1.08
when 30 then
sal * 1.15
else
sal * 1.2
end) raisesal
from emp;
聚合函数
求行数:count
select count(1) from emp
最大值:max 最小值:min 平均值:avg 求和sum
select max(sal) , min(sal),avg(sal), sum(sal) from emp
分组group by
group by 语句用于结合聚合函数,根据一个或多个列对结果集进行分组
having :分组后的条件筛选,效果与where相同,但是where语句不能合聚合函数一起使用
select avg(sal),deptno from emp group by deptno having avg(sal)>2000
行转列
行转列:decode
注意:通过group by进行name分组时,前面要加入聚合函数,例如:min,max
select name,
min(decode(course,'数学',score))数学,
min(decode(course,'语文',score))语文,
min(decode(course,'英语',score))英语
from tb_student
group by name
rowid与rownum
rownum对行号进行排序
select e.*,rownum from emp e
常用于分页操作
-- 三层实现数据分页查询
/*
pageNum=1 pageSize=10
*/
select * from (
select e.*, rownum rn
from
(select * from emp order by sal desc) e
where rownum <= pageNum*pageSize
) temp where temp.rn>= (pageNum-1)*pageSize+1
rowid实现重复记录的删除
--两种方法:
--一种通过对name,course进行分组
delete from(
select tb.*,tb.rowid from tb_student tb where not tb.rowid in(
select min(rowid) from tb_student group by name,course
))
--一种通过对id进行去重,
delete from(
select tb.*,tb.rowid from tb_student tb where not tb.rowid in(
select min(rowid) from(select distinct(id),name,score from tb_student)
group by id
))
表连接
笛卡尔积
select * from emp,dept;
select ename,dname from emp,dept;
等值连接
select ename,dname,e.deptno from emp e,dept d where e.deptno=d.deptno
外连接
当主表在左边是称为做外连接,主表在右边时叫右外连接
主表的数据会全部显示,副标的数据根据与主表的连接条件进行保留
--所有部门的 部门名称,员工数
--左外
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
on d.deptno = i.deptno;
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
using (deptno);
--右外
select dname, n
from (select deptno, count(1) n from emp group by deptno) i
right outer join dept d
on d.deptno = i.deptno;