数据库SELECT查询

主要内容:

一.单行函数

二.组函数

三.分组

四.rowid和rownum

单行函数

根据函数的返回结果，我们将函数分为单行函数和多行函数

1、单行函数:一条记录返回一个结果
2、多行函数 组函数 聚合函数 (重点) :多条记录 返回一个结果 (重点)

1.日期函数

日期函数: 注意区分 db数据库时间 ，java应用服务器的时间。以一方为准 oracle以内部数字格式存储日期:年,月,日,小时,分钟,秒
sysdate/current_date 以date类型返回当前的日期
add_months(d,x) 返回加上x月后的日期d的值
LAST_DAY(d) 返回的所在月份的最后一天
months_between(date1,date2) 返回date1和date2之间月的数目
next_day(sysdate,‘星期一’) 下周星期一

1)当前时间
select current_date from dual where 1 = 1;
select sysdate from dual where 1 = 1;

2)修改日期（天数±）
–两天后的时刻
select sysdate+2 from dual;

3)修改月份
–当前5个月后的时间
select add_months(sysdate,5) from dual;
–雇佣日期 2个月的时间
select ename,hiredate, add_months(hiredate,2) after from emp;
4)月份之差
–雇佣日期 距离现在的 月份数
select ename, months_between(sysdate , hiredate) from emp;

5)最后一天
–返回雇佣日期 当月最后一天的时间
select ename, last_day(hiredate) d from emp;

6)下一个星期的时间
–下一个星期二
select next_day(sysdate, ‘星期二’) from dual;

2.转换函数

to_date(c,m) 字符串以指定格式转换为日期
to_char(d,m) 日期以指定格式转换为字符串

select to_date(‘2017-3-21 18:12:12’,‘yyyy-mm-dd hh24:mi:ss’) time
from dual;
select to_char(sysdate,‘yyyy-mm-dd’) from dual;
select to_char(sysdate,‘yyyy/mm/dd’) from dual;
select to_char(sysdate,‘yyyy\mm\dd’) from dual;
–注意中文的问题
–select to_char(sysdate,‘yyyy年mm月dd日’) from dual;select
to_char(sysdate,‘yyyy"年"mm"月"dd"日"’) from dual;

3.其他函数 (保证类型兼容)
–1)、nvl nvl(string1,string2)  如果string1为null,则结果为string2的值
select ename, nvl(null,0) from emp;
select ename, nvl(to_char(comm),‘hello’) from emp;
–2)、decode decode(condition,case1,express1,case2 , express2,….casen , expressn, expressionm)
select ename,decode(deptno, 10,‘十’,20,‘二十’) from emp;
–3)、case when then else end

–给所有的员工 涨薪,10–>10% 20–>8% 30 -->15% 其他–>20%
–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;

组函数:

组函数|多行函数|聚合函数 即多条记录 返回一个结果。我们需要掌握如下几个组函数： avg 、sum、 min、 max、 count

1)、count :统计记录数 count() -->* 或一个列名
2)、max min: 最大值 最小值
3)、sum：求和
4)、avg:平均值

注意:
1、组函数仅在选择列表和Having子句中有效
2、出现组函数，select 只能有组函数或分组字段

分组:

分组: group by , 将符合条件的记录 进一步的分组
过滤组:having , 过滤组信息 ，表达式 同 where 一致
现在的结构如下
select distinct * | 字段 | 表达式 | 函数 as 别名
from 表 表别名
where 过滤行记录条件
group by 分组字段列表
having 过滤组
order by 字段列表 asc | desc

解析步骤

1)、from 2)、where 3)、group 4)、having 5)、select 6)、order by

group by :分组
1)、select 出现分组函数,就不能使用 非分组信息，可以使用 group by 字段
2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必
须出现在group by 中
过滤组 having :
where :过滤行记录，不能使用组函数， having:过滤组 可以使用组函数

–按 部门 查询 平均工资
select avg(sal) from emp group by deptno;
–按 部门岗位 查询 平均工资
select avg(sal) from emp group by deptno,job;

–按 部门 查询 平均工资，且平均工资大于2000的部门编号
–1、先分组 后过滤 (不推荐)
select *
from (select deptno, avg(sal) avsal from emp where 1 = 1 group by
deptno)
where avsal > 2000;
–2、过滤组 ,分组同时 过滤
select avg(sal), deptno from emp group by deptno having
–查询 最低平均工资的部门编号
–1)、按部门求出平均薪水
select avg(sal) from emp group by deptno;
–2)、找出最低的平均薪水
select min(avg(sal)) from emp group by deptno;
–3)、过滤组
select deptno
from emp
where 1 = 1
group by deptno
having avg(sal) = (select min(avg(sal)) from emp where 1 = 1 group
by deptno);

rowid 和 rownum:

ROWID 它是一个伪列，它并不实际存在于表中。它是 ORACLE 在读取表中数据行时， 根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的 ROWID 能 找到一行数据的物理地址信息。从而快速地定位到数据行。

ROWNUM 是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的 结果集的顺序号 ，每一个结果集 都有自己顺序号 ，不能直接查询大于 1 的数。利用 ROWNUM，我们可以生产一些原先难以实现的结果输出.

1.rowid

实现重复记录的删除
准备
drop table tb_student;
create table tb_student(
id number(4) ,
name varchar2(20),
course varchar2(20),
score number(5,2)
);
insert into tb_student values(1,‘张三’,‘语文’,81);
insert into tb_student values(2,‘张三’,‘数学’,75);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(3,‘李四’,‘语文’,81);
insert into tb_student values(4,‘李四’,‘数学’,90);
insert into tb_student values(5,‘王五’,‘语文’,81);
insert into tb_student values(6,‘王五’,‘数学’,100);
insert into tb_student values(7,‘王五’,‘英语’,90);
commit;

要求：删除重复记录，一条记录只保留一次
思路->将所有记录按照某种特定规律分组（相同的记录为一组），保留下每组中的一
条记录即可，其他记录删除

2.rownum

rownum :1、必须排序 2、不能直接取大于 1 的数
–最底层 rownum 数据库默认顺序号 -->没有用的
select emp., rownum from emp；
select emp., rownum from emp order by sal ;
–自己 排序后结果集的顺序号
select e., rownum from (select * from emp order by sal desc) e;
–取出工资前5名
select e., rownum
from (select * from emp order by sal desc) e
where rownum <= 5;
–取出 工资 3-5 名
select e.*, rownum
from (select * from emp order by sal desc) e
where rownum <= 5
and rownum >= 3;

–三层模板 (分页)
select e.*
from (select e., rownum rn
from (select * from emp order by sal desc) e
where rownum <= 5) e
where rn >= 3;
/
select 字段列表 from (select e.,rownum rn from (select from 表 order by 字段) e where rownum<=
最大值)
where rn>=最小值
/
select e.
from (select e., rownum rn
from (select * from emp order by sal desc) e
where rownum <= 10) e
where rn >= 6;