数据库函数的详解

数据库函数

1.函数的定义

函数分为系统内置函数 自定义函数；其中了解系统内置函数（方法），重点掌握to_date，to_char （字符和日期的转换）根据函数的返回结果，我们将函数分为单行函数和多行函数。

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

1.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,星期一) 下周星期一。
当前时间

 select current_date from dual where 1=1;

修改日期（天数±）

select sysdate from dual where 1=1;

两天后的时刻

select sysdate+2 from dual;

修改月份
当前5个月后的时间

select add_months(sysdate,5) from dual; 

雇佣日期 2个月的时间

select ename,hiredate, add_months(hiredate,2)
after from emp;

月份之差
雇佣日期 距离现在的月份数

select ename, months_between(sysdate ,
hiredate) from emp;

最后一天
返回雇佣日期 当月最后一天的时间

select ename, last_day(hiredate) d from emp;

下一个星期的时间
下一个星期二

select next_day(sysdate, '星期二') from dual;

1.2 转换函数（重点）

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

select to_date('2017-3-21 18:12:12', 'yyyy-mmdd 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;

1.3. 多行|聚合|组函数

组函数|多行函数|聚合函数即多条记录返回一个结果。
我们需要掌握如下几个组函数：
avg 、sum、 min、max、count
count :统计记录数 count() -->* 或一个列名
max min: 最大值 最小值
sum：求和
avg:平均值

注意:

1. 组函数仅在选择列表和Having子句中有效
2. 出现组函数，select 只能有组函数或分组字段
   说明：
   组信息 与单条记录不能同时查询
   组函数 不能用在 where中，能使用的地方 select
   having
   null 不参与运算

1.4 分组

分组: 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 : 过滤组

1. where : 过滤行记录，不能使用组函数
2. having : 过滤组 可以使用组函数

1.5 行转列

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

行转列

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

2. rowid 与 rownum

ROWID 是 ORACLE 中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID 它是一个伪列，它并不实际存在于表中。它是ORACLE 在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID 能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的多数操作都是通过ROWID 来完成的，而且使用ROWID 来进行单记录定位速度是最快的。我们可以将其用于删除重复数据。
ROWNUM 是一种伪列，它会根据返回记录生成一个序列
化的数字。排序后的结果集的顺序号，每一个结果集 都
有自己顺序号 ，不能直接查询大于 1 的数。利用
ROWNUM，我们可以生产一些原先难以实现的结果输
出。 例如实现分页操作。
ps: oracle 中 索引从 1 开始，java 程序 从 0 开始

3.表连接

当我们获取的数据不是来自于同一张表而是来自于多张
表时就需要使用到表连接

3.1表连接

select * from 表1;
select * from 表2;

注意:同名列 非* 必须区分
数据源 、关系列、 过滤条件、字段

1. 笛卡尔积
   非* 必须区分 使用表名 或别名.区分
2. 等值连接(在笛卡尔积基础上 取条件列
   相同的值)
3. 非等值连接 > < != <>between and
4. 自连接: 特殊的等值连接 (来自于同一张
   表)
5. 外连接
6. 表连接
7. 交叉连接

select * from emp cross join dept;

6. 自然连接
   自动做等值连接
   等等等