第十一章 使用数据处理函数
文本处理函数[常用的文本处理函数说明表]
select vend_name,Upper(vend_name) as VEND_NAME from vendors order by vend_name;
select vend_name,Lower(vend_name) as VEND_NAME from vendors order by vend_name;
select vend_name,length(vend_name) as vend_name_changdu from vendors order by vend_name;
select vend_name,left(vend_name,3) as vend_name_jianxie from vendors order by vend_name;
select cust_name,cust_contact from customers where cust_contact='Y.Lie'; #源数据中存在的是'Y.Lee'而不是'Y.Lie',左侧方式无结果返回#
select cust_name,cust_contact from customers where soundex(cust_contact)=soundex('Y.Lie'); #使用soundex()函数[能对串进行发音比较]即可将结果查找成功#
常用的文本处理函数中locate（）和substring()函数暂时没弄明白如何使用
常用文本函数说明表1
常用文本函数说明表2
日期和时间处理函数[常用的日期和时间处理函数说明表]
select order_num,order_date,cust_id from orders where date(order_date)='2005-09-30';
select order_num,order_date,cust_id from orders where date(order_date) between '2005-09-01' and '2005-09-30';
select order_num,order_date,cust_id from orders where year(order_date)=2005 and month(order_date)=9;
select order_num,order_date,cust_id,adddate(order_date,1) from orders;
select order_num,order_date,cust_id,addtime(order_date,'1 1:0:0') from orders;
select curdate();
select curtime();
select order_num,order_date,cust_id,date_format(order_date,'%Y-%m-%d') from orders;
select order_num,order_date,cust_id,dayofweek(order_date) from orders; #返回的是1-7代表星期几#
select order_num,order_date,cust_id,dayname(order_date) from orders; #返回的是英文版的星期#
常用日期函数说明表
数值处理函数[常用数值处理函数说明表]#一般用的较少
常用数值函数说明表
第十二章 汇总数据
聚集函数[SQL聚集函数说明表]
select avg(prod_price) as avg_price from products; #对整列求平均#
select avg(prod_price) as avg_price from products where vend_id=1003; #对特定列和行求平均#
select count(*) as num_cust from customers;
select count(cust_email) as num_custemail from customers;
#如果指定列明，则指定列的值为空的行被count()忽略，但如果count()函数中使用的是*则不忽略空值行#
select max(prod_price) as max_price,min(prod_price) as min_price from products;
select order_num,sum(quantity)as sum_quantity from orderitems where order_num=20005;
select order_num,sum(quantity)as sum_quantity from orderitems group by order_num order by sum_quantity;
select order_num,sum(quantity*item_price)as total_price from orderitems where order_num=20005;
select order_num,sum(quantity*item_price)as total_price from orderitems group by order_num order by total_price;
SQL聚集函数说明表
聚合不同值[使用distinct]
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
SELECT vend_id, AVG(DISTINCT prod_price) AS avg_price FROM products GROUP BY vend_id;
组合聚集函数
SELECT
COUNT(*) AS num_prod,
AVG(prod_price) AS price_avg,
MAX(prod_price) AS price_max,
MIN(prod_price) AS price_min
FROM products;