数据库

SELECT篇-函数

主要内容

1.单行函数
2.组函数
3.分组
4.rowid和rownum

函数—单行函数

函数分为系统内置函数 自定义函数（后期学习的 plsql 中定义）；了解系统内 置函数（方法），重点掌握 to_date 、 to_char （字符和日期的转换）

根据函数的返回结果，将函数分为单行函数和多行函数
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,‘星期一’) 下周星期一

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 只能有组函数或分组字段

1.count

--1、count统计所有的员工数
--1)、*
--2)、主键
--3)、推荐
select ename,1 from emp;
select count(1) from emp where 1=1;
--2、null不参与运算
--存在佣金的员工数
--不推荐/不需要
select count(comm) from emp where comm is not null;
--推荐
select count(comm) from emp;

--统计 部门编号30的员工数 
select count(1) from emp where deptno=30;

--统计数量过程中 ，可能处理重复
--统计 存在员工的 部门数量
select count(distinct(deptno)) 有人的部门 from emp;

--统计10和20部门一共有多少人
select distinct(count(1)) from emp where deptno in(10,20);

2.max min: 最大值 最小值

--查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
select max(sal) maxSal , min(sal) minSal , count(1) from emp;

3.sum：求和

- 查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;

B:无论运算符两端简单还是复杂最终结果是boolean类型。
C:千万不要把==写成了=
D：结果为Boolean类型的值

4.avg:平均

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where sal<(select avg(sal)from
emp);

--查看 高于本部门平均薪水员工姓名
select * from emp e1 where sal>(select avg(sal) from emp e2 where
e1.deptno=e2.deptno );

分组

分组: 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:过滤组 可以使用组函数

子查询与行转列

一条sql语句实现需求

/*
id name course score
1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90
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(4,'李四','数学',90);
insert into tb_student values(5,'王五','语文',81);
insert into tb_student values(6,'王五','数学',100);
insert into tb_student values(7,'王五','英语',90);
commit;
drop table tb_student cascade constraints;
*/

--使用一条sql语句，查询每门课都大于80分的学生姓名
--1)、每门课 -->统计课程数 3
select count(distinct(course)) from tb_student ;
--2)、按学生查看每门课都大于80分 最低分大于80
select name from tb_student group by name having min(score)>80;
--综合
select name
 from tb_student
group by name
having min(score) > 80 and count(1) = (select
count(distinct(course)) from tb_student);

2.行转列

转成如下 方便: 解耦（与记录之间）+扩展(与结构之间)

行转列

--找出课程名(表头)
select distinct course from tb_student;
--数据(行记录) 分组（学生+行转列 decode）
select * from tb_student;
--1、行转列 decode
select name,decode(course,'语文',score) 语文,decode(course,'数学
',score) 数学,
decode(course,'英语',score) 英语 from tb_student;

--2、分组
select name,
 min(decode(course, '语文', score)) 语文,
 min(decode(course, '数学', score)) 数学,
 min(decode(course, '英语', score)) 英语
 from tb_student
group by name;

rowid 和 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;

要求：删除重复记录，一条记录只保留一次
思路->将所有记录按照某种特定规律分组（相同的记录为一组），保留下每组中的一
条记录即可，其他记录删除
1.找出重复数据 :哪个学生 哪门课重复了

select name,course,count(1) from tb_student group by name,course;
select name,course,count(1) from tb_student group by name,course
having count(1)>1;

2、删除重复数据 :删除重复记录

-- 每条记录的唯一标识
select s.* , rowid from tb_student s;
--找出 保留的rowid
select min(rowid) from tb_student group by name,course;
--删除
delete from tb_student where rowid not in (select min(rowid) from
tb_student group by name,course);

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.*,

--三层模板 (分页)
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;