01-4数据库函数

• 分组：group by ；分组后的过滤条件：having（where 字段无法与函数一起使用）

O_Id	OrderDate	OrderPrice	Customer
1	2008/12/29	1000	Bush
2	2008/11/23	1600	Carter
3	2008/10/5	700	Bush
4	2008/9/28	300	Bush
5	2008/8/6	2000	Adams
6	2008/7/21	100	Carter

希望查找每个客户的订单总金额：

select Customer,SUM(OrderPrice) group by Customer

结果：

Customer	SUM(OrderPrice)
Bush	2000
Carter	1700
Adams	2000

若省略 group by字段：select Customer,SUM(OrderPrice)

Customer	SUM(OrderPrice)
Bush	5700
Carter	5700
Bush	5700
Bush	5700
Adams	5700
Carter	5700

原因：上述select 语句指定两列，customer、sum(orderprice)，sum返回单独一个值，customer返回6个值

希望查找订单金额少于2000的客户：

select Customer,SUM(OrderPrice) group by Customer having SUM(OrderPrice)<2000

Customer	SUM(OrderPrice)
Carter	1700

• 常用的函数

1、单行函数：一条记录返回一个值 LENGTH('ABC') 返回字符串长度

2、多行函数（组函数、聚集函数）多条记录返回一个值

SUM() 总和；AVG() 求平均数；COUNT() 统计记录条数；MAX() 求最大值；MIN() 求最小值

组函数：having 分组后的过滤（经常与group by 连用，若不使用 则默认所有数据为一组）

求员工总收入，平均收入 select SUM(salary) 总收入,AVG(salary) 平均收入 from emp;

求每个部门的员工总收入、平均收入

select dep_id ,SUM(salary) , AVG(salary) from emp group by dep_id ;

空值转换函数 nvl ：大部分组函数会忽略空值

解决方案：使用空值转换函数nvl，函数可嵌套使用 比如提出null 替换为0

select avg(commission_pct) as avgcomm

-----13

select avg(nvl(commission_pct,0)) as avgcomm

-----2.6

结论：avg以及大部分组函数不会将空值考虑在内，组函数处理的是所有的非空值

count（*）除外，会统计空值的行 但如果为null 会忽略

去重函数：distinct 字段名

统计该表有多少部门：select count(distinct dep_id)) from t_dep

每个部门有多少员工 select count(id) from t_emp group by dep_id

常见执行顺序：from 找表 表连接 ->  再where 过滤

  -> 分组 group by -> 计算组函数 count()   select

  -> having 分组后过滤  -> order by 排序

一、数据库的函数 （Oracle）

优点：简化我们的SQL脚本

<1>单行函数

1、字符函数   char   varchar2  varchar

 length  字符串的长度

 lower   大写变小写

 upper   小写变大写

 substr   求子串

 

2、数值函数   number

 round    四舍五入

 trunc     截取

 

3、日期函数   date

 sysdate   当前系统时间

 to_char   date -> char  常用于查询显示日期  提取

   to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')

   to_char(sysdate, 'yyyy')

   to_char(sysdate, 'mm')

 to_date   char -> date  常用于生成日期数据

   to_date('2014-02-07', 'yyyy-mm-dd')

   to_date('2014 02 07 09:30:08',

             'yyyy mm dd hh24:mi:ss')

   添加日期数据时，建议使用yyyy 四位年 避免歧义

    13 ?   RR  2013    YY 1913

   2013   yyyy

 months_between(d1, d2)  d1和d2相差几个月

 add_months(d1, n)  求出n个月以后的d1时间

 last_day()    求出本月最后一天

 next_day()    下一个星期几

 trunc(sysdate, 'MONTH')  截取  本月的第一天

 trunc(sysdate, 'YEAR')    本年的第一天

 trunc(sysdate)     今天的0点

 

其它：nvl   空值处理函数

 

<2>多行函数 （组函数、聚集函数）

 sum   avg  count  max  min