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;