mysql 函数处理数据

soundex() ：处理相同音节的数据，如Michael和Michelle

select cust_name,cust_contact
from customers
where soundex(cust_contact) = soundex('Michael Green');

处理日期和时间函数

按照年

select order_num
from orders
where year(order_date) = 2020;

按照月

select order_num
from orders
where month(order_date) = 1;

按照日

select order_num
from orders
where date(order_date) = 12;

其他函数

# column列名
select left(column，n) -- 返回字符串左边n个
select right(column,n) -- 返回字符串右边n个
select ltrim(column) -- 删除字符串左侧空格
select rtrim(column) -- 删除字符串右侧空格
select upper(column) -- 将字符串转为大写
select lower(column) -- 将字符串转为小写

计算平均数avg

select avg(prod_price) as avg_price
from products

count()计算非null值的行数

select count(cust_email) as num_cust
from customers;

select count(*) as num_cust
from customers;

max(column) 返回该列最大值

select max(prod_price) as max_price
from products;

min(column) 返回该列最小值 – max,min忽略null值

select min(prod_price) as min_price
from products;

sum(column)

select sum(quantity) as items_ordered
from orderitems
where order_num = 20005;

select sum(item_price*quantity) as total_price
from orderitems
where order_num= 20005;

计算不同价格的的平均数

select avg(distinct prod_price) as avg_price
from products
where vend_id = 'DLL01';