04.数据库基础—数据库常见函数

1.avg():返回某列的平均值

例：求平均值

select avg(price) as avgPrice from orders

例：筛选价格高于平均价格的客户：

select customer from orders
where price >(select avg(price) from orders)

2.count():返回某列的行数

例：select count(customer) as customerNum 
from orders where customer = 'pony',
//输出结果为2，证明有两行的customer名称为pony
例：select count(*) as numberOrders from orders;   //统计一共有多少行
例：统计不同客户的数目
select count(distinct customer) as numberOrders from orders,

3.统计函数

frist():返回第一个记录的数据
select first(order) as firstOrder from orders,

last():返回最后一个记录的数据
select first(order) as lastOrder from orders,

max():返回某列的最高值
select max(order) as maxOrder from orders,

min():返回某列的最低值
select min(order) as minOrder from orders,

sum():返回某列的和
select sum(order) as totalOrder from orders

4.group by :对结果集进行分组

例：针对一个表格，我们需要查询每个客户和对应的总金额，将其创建一个新表

select customer,sum(price) from orders
group by customer  //按照customer合并，计算每个用户的和，汇总到一起

由于where不能与合计函数一起使用，要用having替代

select customer ，sum(price) from orders
group by orders
having sum(price) < 2000 //添加筛选条件

如果我们需要查询'bush'或'adams'拥有超过1500的订单总金额

select customer ,sum(price) from orders
where customer = 'bush' or customer = 'adams'
group by customer
having sum(price) >1500

5.ucase():转换大写

例：将firstname和lastname的内容提取出来，并将lastname里面的内容转化为大写

select fristname ,ucase(lastname) as lastname  from perosons,

ps.lcase是将字母转化为小写，用法与ucase相同

6.其他函数

mid():提取字符

例：从city列提取前三个字符
select mid(city,1,3) as shortCity from persons

len():返回文本的长度

例 ：获取city 列中值的长度
select len(city) as lenghOfCity from persons

round():把字段舍入为指定的小数位数

select round(price,0) from orders,  //把price列取出来，留0位小数

format():对字段进行格式化

例：select id, price ,format(getdate(),'YYYY-MM-DD') as perdate
from products

limit:选取表中数据：

select * from emp limit  12,3 //选取第13到15条记录