SQL中的函数

查询语句集合（函数）


1.count
select count(customer) as count from `order` where customer = 'Bush';


select count(*) as numberOfOrders from `order` ;


select count(distinct customer) as typeOfOrders from `order` ;


2.first
select first(oprice) as maxPrice from `order`;


3.last
select last(oprice) as maxPrice from `order`;


4.max
select max(oprice) as maxPrice from `order`;


5.min
select min(oprice) as minPrice from `order`;


6.sum
select sum(oprice) as totalPrice from `order`;


7.GROUP BY 
select customer , SUM(oprice) from `order` group by customer ;


select customer , odate , SUM(oprice) from `order` group by customer , odate;


8.HAVING 在group by 中添加的条件
select customer , SUM(oprice) from `order` group by customer having SUM(oprice) > 0 ;


9.切换成大写ucase(字段名)
select ucase(lastName) from person;


select ucase(lastName) as Lastname , firstName  from person;


10.切换成小写 lcase(字段名)
select lcase(lastName) as Lastname , firstName  from person;


11.查询某个字段的部分字符 mid（字段名，开始下标，长度）
select mid(city,1,3) from person;


12.查询某个字段的长度 length（字段名）
select length(city) as cityLength from person;