数据库SELECT

一、 函数—单行函数

函数分为系统内置函数 自定义函数（后期学习的 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,'星期一') 下周星期一

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;

--查询 82的员工信息

select * from emp where hiredate like '%82%' ;
或
select * from emp where to_char(hiredate,'yyyy')='1982';
或
select *
from emp
where hiredate between to_date('1982-01-01', 'yyyy-mm-dd') and
to_date('1982-12-31', 'yyyy-mm-dd');

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;

4、 分析函数 (了解)

select ename, sum(1) over(order by sal desc) aa from emp;
select deptno,
ename,
sal,
sum(sal) over(order by ename) 连续求和,
sum(sal) over() 总和, -- 此处sum(sal) over () 等同于sum(sal)
100 * round(sal / sum(sal) over(), 4) "份额(%)"
from emp;
select deptno,
ename,
sal,
sum(sal) over(partition by deptno order by ename) 部门连续求和, --各部门的薪水"连
续"求和
sum(sal) over(partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
100 * round(sal / sum(sal) over(partition by deptno), 4) "部门份额(%)",
sum(sal) over(order by deptno, ename) 连续求和, --所有部门的薪水"连续"求和
sum(sal) over() 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
100 * round(sal / sum(sal) over(), 4) "总份额(%)"
from emp;

二、 组函数

组函数|多行函数|聚合函数 即多条记录 返回一个结果。我们需要掌握如下几个组函数：
avg 、 sum、 min、 max、 count
1)、 count :统计记录数 count() -->* 或一个列名
2)、 max min: 最大值 最小值
3)、 sum：求和
4)、 avg:平均值
注意:
1、组函数仅在选择列表和Having子句中有效
2、出现组函数，select 只能有组函数或分组字段

说明：

• 组信息 与单条记录不能同时查询
• 组函数 不能用在 where中，能使用的地方 select having
• null 不参与运算

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;

--查询 最高薪水的员工名称 及薪水
--组信息 与单条记录不能同时查询
select max(sal), ename, sal from emp; 错误
select ename, sal from emp where sal=(select max(sal) from emp );

3、 sum：求和

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

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

1.group by :分组

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

2、过滤组 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
avg(sal)>2000;

--查询 最低平均工资的部门编号
--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);

--查看 高于本部门平均薪水员工姓名
--1、按部门求出平均薪水
--2、关联子查询
select *
from emp e
where exists
(select deptno
from (select deptno, avg(sal) avgsal from emp group by
deptno) e2
where e.deptno = e2.deptno
and e.sal > avgsal);
--另外一种 (推荐)
select *
from emp e1
where sal > (select avg(sal) from emp e2 where e2.deptno =
e1.deptno);

3.练习：

/*
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);

四、 行转列

id name course score
1 张三 语文 81
2 张三 数学 75
3 李四 语文 81
4 李四 数学 90
5 王五 语文 81
6 王五 数学 100
7 王五 英语 90

转成如下
方便: 解耦（与记录之间）+扩展(与结构之间)
姓名 语文 数学 英语
张三 81 75
李四 81 90
王五 81 100 90

1、行转列

--找出课程名(表头)
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;