Oracle数据库（第二天）

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效率较高

单行函数

特征

1. 单行函数对单行操作
2. 每行返回一个结果
3. 有可能返回值与原参数数据类型不一致（转换函数）
4. 单行函数可以写在SELECT、WHERE、ORDER BY子句中
5. 有些函数没有参数，有些函数包括一个或多个参数
6. 函数可以嵌套

分类

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;