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';