• MySql数据库中的函数与Java中的函数类似，都是完成一定功能的代码的集合。根据函数操作数据行数可将SQL函数分为单行函数和多行函数：
  • 单行函数：单行函数仅对单条数据中的列进行操作并且返回一个结果；
  • 多行函数：多行函数可以操作成组的多条数据，每组返回一个结果，所以多行函数又称之为组函数；
  • 以下表为例：
常用聚合函数
  • COUNT()：统计指定列不为NULL的记录行数，例：
#统计月薪与绩效之和大于5000元的人数：
SELECT COUNT(id) FROM employee WHERE (salary+performance)>6000;
  • MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  • MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算，例：
#查询最高工资和最低工资：
SELECT MAX(salary),MIN(salary) FROM employee;
  • SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0，例：
#统计研发部职员总工资
SELECT SUM(salary) FROM employee WHERE department='研发部';
  • AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0，例：
#计算所有员工的平均工资
SELECT AVG(salary) FROM employee;
常用单行字符串函数
  • LENGTH(column_name|str)：返回字符串存储长度；
  • CHAR_LENGTH(column_name|str)：返回字符串中字符个数，例：
#计算字符串“String”的存储长度和字符个数
SELECT LENGTH('字符串'),CHAR_LENGTH('字符串');
  • 结果为：
  • CONCAT(column_name1|str1, column_name2|str2,…)：将多个字符串首尾相连后返回；
  • CONCAT_WS(separator,column_name1|str1, column_name2|str2,…)：将多个字符串按照执行separator进行首尾相连，例：
#用两种方式将研发部的员工的姓名、性别和月薪打印在同一个字段中，中间用“-”相接
SELECT CONCAT(name,'-',gender,'-',salary) planA,CONCAT_WS('-',name,gender,salary) planB FROM employee;
  • 结果为：
  • TRIM([{both | leading | trailing} [remstr] from] str)：返回去掉str源字符串两端、前缀或后缀字符串remstr；不指定both、leading、trailing ，则默认为 both，此时不能使用from关键字；不指定remstr，则去掉str两端的空格，例：
#去掉“  str  ”两端的空格、去掉“aaastr”左端的“a”、去掉“strabcabc”右边的“abc”
SELECT TRIM('  str  ') a,TRIM(LEADING 'a' FROM 'aaastr') b,TRIM(TRAILING 'abc' FROM 'strabcabc') c;
  • 结果为：
  • SUBSTR(str,pos[,len])：从源字符串str中的指定位置pos开始取一个长度为len的字符串并返回，与SUBSTRING相同，例：
#从“Hello World”中截取出“Hello”放在字段a,截取“World”放在字段b
SELECT SUBSTR('Hello World',1,5) a,SUBSTRING('Hello World',7) b;
  • 结果为：
  • REVERSE(str)：将字符串str反转，与Java中StringBuffer中的reverse功能类似，例：
#将字符串“abcdefg”反转
SELECT REVERSE('abcdefg');
  • 结果为：
  • STRCMP(expr1,expr2)：不区分大小写区分两个字符串，相同则返回0；第一个小于第二个返回-1，否则返回1，例：
#比较"abc"和"ABC"、比较"abc"和"abb"、比较"abc"和"acc"
SELECT STRCMP('abc','ABC') a,STRCMP('abc','abb') b,STRCMP('abc','acc') c;
  • 结果为：
  • REPLACE(str, from_str, to_str)：将源字符串str中所有子串form_str(区分大小写)替代成字符串to_str并返回替换后的字符串；
#将"www.jpc.JPC.com"中的"jpc"替换成"JING"
SELECT REPLACE('www.jpc.JPC.com','jpc','JING');
  • 结果为：
常用单行数值函数
  • MOD(x,y)：取x与y的余数，y为除数，x为被除数，例：
#求3除以10的余数
SELECT MOD(3,10);
  • 结果为：
  • ROUND(x[,y])：返回参数x的四舍五入值，保留y位小数；不指定第二个参数，则默认为0，例：
#对0.618四舍五入保留两位小数，对0.618四舍五入取整
SELECT ROUND(0.618,2),ROUND(0.618);
  • 结果为：
  • TRUNCATE(x,y)：返回数字x截断后的结果，保留y位小数，后面的小数不四舍五入而是直接省略，例：
#将1.618保留两位小数，对1.618保留一位小数
SELECT TRUNCATE(1.618,2),TRUNCATE(1.618,1);
  • 结果为：
常用单行日期函数
  • 获取当前的精确时间
SELECT now();
  • 获取指定格式的时间
SELECT DATE_FORMAT(now(),'%Y-%m-%d %h:%i:%s');
单行函数-转换函数

convert(value,type)：将value转换为type类型，type可以是char（字符型）、date（日期型）、time（时间型）、datetime（日期时间型）、 signed（整型） 和decimal（浮点型）类型中的一个;

单行函数-IF/IFNULL
  • IF(expr1,expr2,expr3)： expr1为TRUE，返回expr2，否则返回expr3；
  • IFNULL(expr1,expr2)：expr1不是NULL，返回expr1，否则返回expr2，例：
#查询所有员工的姓名、性别、月薪、管理工资(如果为空就打印0)、部门
#方式一：
SELECT name,gender,salary,IF(manage IS NULL,0,manage) manage,department FROM employee;
#方式二：
SELECT name,gender,salary,IFNULL(manage,0) manage,department FROM employee;
  • 结果为：