数据库的常用函数

1、SQL聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

*AVG()函数

返回products表中所有产品的平均价格
输入：
select AVG(proid_price) as avg_price
from products
输出：
avg_price
16.133
分析：
此查询语句返回值是avg_price,他包含products所有产品的平均价格

它也可以确定单个列，只能用来确定数值列的平均值，而且列名必须作为函数参数给出。并且它忽略值为NULL的行。 

 *COUNT()函数

返回customers表中的客户的总数
输入：
select COUNT(*) as unm_cust
from customers
输出：
num_cust
5

COUNT（）的两种用法：

1、统计行数是包含NULL行

2、COUNT(column) 可以对特定的行进行计数，忽略NULL值

* MAX()函数

返回指定列的最大值
输入：
select MAX(prod_price) as max_price
from products
输出：
max_price
55.00
分析：返回的值即为最贵的物品价格

在MYsql允许将它用来返回任意列中的最大值，包括文本列中的最大值 。在用于文本数据时，如果数据按相应的列排序，则返回最后一行，并且也忽略列值为NULL的行。

*MIN(）函数

输入：
select MIN(prod_price) as min_price
from products
输出：
min_price
2.50
分析：其中返回products表中最便宜物品的价格

与MAX()函数相似，返回的值为最前面的一行 

 *SUM()函数

返回所有订购物品的总数
输入：
select SUN(quantity) as intems_ordered
from orderitems
where order_num=2005;
输出：
items_ordered
19
分析：函数返回了所有物品数量之和，where可以统计订单中某个物品

如果指定列名，则distinct只能用于COUNT()。distinct不能用于COUNT(*)，也不能使用COUNT(distinct) 

总结：以上五个函数，只有COUNT()不忽略值为NULL的行。

除此之外，常用的函数还有：

1、to_char函数:转换成字符串形式

查询订单客户和日期：
select xmdasite,to_char(xmdadocdt,'yyyy-mm-dd') as data 
from dsdata.xmda_t
where to_char(xmddocdt,'yyyy-mm-dd')='2019-08-01'
还可以设置为输入参数形式：

把where 修改为：
whre to_char(xmdadocdt,'yyyy-mm-dd')='${订单日期}'
如何想要查询一段时间内的数据：
whre to_char(xmdadocdt,'yyyy-mm-dd')='${起始日期}'
and to_char(xmdadocdt,'yyyy-mm-dd')='${结束日期}'

2、to_data函数：转换为日期类型

3、replace函数：代替

用法：
replace(xx公司-研发中心,'-','')

4、instr函数

用法：
instr(ooefl004,'-',1,1)
ooefl004是对象
‘-’位置
1是几个
1次数

5、substr

select 
ooefl004,
(case when instr(ooefl004,'-',1,1)>0 
then substr(ooefl004,1,instr(ooefl004,'-',1,1)-1)else ooefl004 end ) as 中心,
(case when length(ooefl004)-length(replace(ooefl004,'-',''))>1 
then 
substr(ooefl004,instr(ooefl004,'-',1,1)+1,instr(ooefl004,'-',1,2)-1-instr(ooefl004,'-',1,1)) 
else 
substr(ooefl004,instr(ooefl004,'-')+1,
length(ooefl004)-instr(ooefl004,'-')) end) 
as 部门
from dsdata.ooefl_t
where ooefl004 like '%中心-%部%'
and instr(ooefl004,'-')>=2
注释：ooefl004的形式为：xx中心-xx部门
通过第一case when 取出中心
通过第二case when 取出部门

6、upper(char):转化为大写

 eg:upper('sum')  得到：SUM

 7、lower(char):转化为小写

eg:lower('NBA')得到：nba

 8、NVL函数

SELECT ID,PERSONNAME,NVL(BOOKNUM,0) 
AS BOOKNUM,NVL(PERSONNUM,0) AS PERSONNUM 
FROM BOOKSTORE
相当于：
IF (BOOKNUM !=NULL) THEN
          RETURN BOOKNUM
ELSE
          RETURN 0

 9、NULLIF函数

SELECT E.NAME,E.JOB_ID,J.JOB_ID,NULLIF(E.JOB_ID,J.JOB_ID) AS SAME 
FROM EMPLOYEES E,JOB_HISTORY J 
WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID ORDER BY NAME;
理解分析：
IF(E.JOB_ID == J.JOB_ID) THEN
           RETURN NULL
ELSE
           RETURN E.JOB_ID

10、DECODE函数

SELECT ID,NAME,DECODE(LEVER,1,'教授',2,'副教授',3,'导师','讲师') POSITION,
(SELECT CLASSNUM 
FROM CLASS T 
WHERE T.NUM = S.NUM) NUM 
FROM TEACHER S 
WHERE T.LEVER IN (1,2,3);
分析理解：

IF(LEVER==1) THEN
          RETURN '教授'(翻译值1)
ELSE IF(LEVER==2) THEN
          RETURN '副教授'(翻译值2)
ELSE IF(LEVER==3) THEN
          RETURN '导师'
ELSE
          RETURN '讲师'(默认值)