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 '讲师'(默认值)