SQL数据处理函数

SQL支持 一下类型的函数
1：用于处理文本串
2：用于在数值数据上进行算数操作
3：用于处理日期和时间值并从这些值中提取特定成分
4：返回DBMS正使用的特殊信息

文本处理函数

select vend_name, Upper(vend_name) AS vend_name_upcase
from venders
order by vend_name;

常用的文本处理函数
Left（） 返回串左边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转化为小写
LTrim() 去掉串左边的空格
Riight() 返回串右边的字符
RTrim() 去掉串右边的空格
Soundex() 返回串的SOUNDEX值
SubString() 返回子串的字符
Upper() 将串转化为大写

匹配发音类似于Y.Lie的联系名

select cust_name,cust_contact
from customers
where Soundex(cust_contact) = Soundex('Y lie');

日期和时间处理函数

select cust_id ， order_num
from orders
where order_date = '2015-09-01';

仅提取列的日期部分
select cust_id ， order_num
from orders
where Date(order_date) = '2015-09-01';

匹配2005年9月下的所有订单
select cust_id ， order_num
from orders
where Date(order_date) BETWEEN   '2015-09-01'   AND  '2005-09-30';

匹配2005年9月份的订单
select cust_id ， order_num
from orders
where Year(order_date) = 2005 AND Month(order_date) = 9 ;

数值处理函数

AVG()函数

select  AVG(prod_price)  AS  avg_price
from products;

select  AVG(prod_price)  AS  avg_price
from products
where vend_id = 1003;

select  AVG(DISTINCT  prod_price)  AS  avg_price
from products
where vend_id = 1003;

分析
AVG()用来确定特定列或行的平均值

COUNT()函数

select  COUNT(*)  AS  num_cust
from cusomers;

select  COUNT(cust_email)  AS  num_cust
from cusomers;

分析
1：对表中行的数目进行计数
2：对特定列中具有值的行进行计数

MAX()函数

select  MAX(prod_price)  AS  max_price
from products;

MIN()函数

select  MIN(prod_price)  AS  min_price
from products;

注意
min（）忽略列值为NULL的行

SUM()函数

select SUM(quantity) AS items_ordered
from orderitems
where order_num = 20005;

select SUM(items_price*quantity) AS total_price
from orderitems
where order_num = 20005;    

组合聚集函数

select      COUNT(*) AS num_items,
			MIN(prod_price) AS price_min,
			MAX(prod_price) AS price_max,
			AVG(prod_price) AS price_avg,
from  products;