第五章 函数

5.1单行函数

• 只对一行进行变换，每行返回一个结果
• 可以嵌套
• 参数可以是一字段或一个表达式或一个值

5.1.1 字符串函数 函数用法CONCAT(S1,S2,…,Sn)连接S1,S2,…,Sn为一个字符串CONCAT_WS(s, S1,S2,…,Sn)同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上sCHAR_LENGTH(s)返回字符串s的字符数LENGTH(s)返回字符串s的字节数，和字符集有关INSERT(str, index , len, instr)将字符串str从第index位置开始，len个字符长的子串替换为字符串instrUPPER(s) 或 UCASE(s)将字符串s的所有字母转成大写字母LOWER(s) 或LCASE(s)将字符串s的所有字母转成小写字母LEFT(s,n)返回字符串s最左边的n个字符RIGHT(s,n)返回字符串s最右边的n个字符LPAD(str, len, pad)用字符串pad对str最左边进行填充，直到str的长度为len个字符RPAD(str ,len, pad)用字符串pad对str最右边进行填充，直到str的长度为len个字符LTRIM(s)去掉字符串s左侧的空格RTRIM(s)去掉字符串s右侧的空格TRIM(s)去掉字符串s开始与结尾的空格TRIM(【BOTH 】s1 FROM s)去掉字符串s开始与结尾的s1TRIM(【LEADING】s1 FROM s)去掉字符串s开始处的s1TRIM(【TRAILING】s1 FROM s)去掉字符串s结尾处的s1REPEAT(str, n)返回str重复n次的结果REPLACE（str, a, b）用字符串b替换字符串str中所有出现的字符串aSTRCMP(s1,s2)比较字符串s1,s2SUBSTRING(s,index,len)返回从字符串s的index位置其len个字符

• 举例1：大小写控制函数

函数结果LOWER(‘SQL Course’)sql courseUPPER(‘SQL Course’)SQL COURSE

这类函数改变字符的大小写。

• 举例2：字符控制函数

函数结果CONCAT(‘Hello’,‘World’)HelloWorldSUBSTR(‘HelloWorld’,1,5)HelloLENGTH(‘HelloWorld’)10INSTR(‘HelloWorld’,‘W’)6LPAD(salary,10,‘*’)*****24000RPAD(salary,10, ‘*’)24000*****TRIM(‘H’ FROM ‘HelloWorld’)elloWorldREPLACE(‘abcd’,‘b’,‘m’)amcd 5.1.2 数值函数/数学函数 函数用法ABS(x)返回x的绝对值CEIL(x)返回大于x的最小整数值FLOOR(x)返回小于x的最大整数值MOD(x,y)返回x/y的模RAND()返回0~1的随机值ROUND(x,y)返回参数x的四舍五入的有y位的小数的值TRUNCATE(x,y)返回数字x截断为y位小数的结果SQRT(x)返回x的平方根POW(x,y)返回x的y次方

• 举例1：ROUND:四舍五入

ROUND(45.926, 2)     -->  45.93

• 举例2：TRUNCATE:截断

TRUNCATE(45.926)      --> 45

• 举例3：MOD:求余

MOD(1600, 300)	 --> 100

5.1.3 日期函数 函数用法CURDATE() 或 CURRENT_DATE()返回当前日期CURTIME() 或 CURRENT_TIME()返回当前时间NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()返回当前系统日期时间YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)返回具体的时间值WEEK(date) / WEEKOFYEAR(date)返回一年中的第几周DAYOFWEEK()返回周几，注意：周日是1，周一是2，。。。周六是7WEEKDAY(date)返回周几，注意，周1是0，周2是1，。。。周日是6DAYNAME(date)返回星期：MONDAY,TUESDAY…SUNDAYMONTHNAME(date)返回月份：January，。。。。。DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔DATE_ADD(datetime, INTERVAL expr type)返回与给定日期时间相差INTERVAL时间段的日期时间DATE_FORMAT(datetime ,fmt)按照字符串fmt格式化日期datetime值STR_TO_DATE(str, fmt)按照字符串fmt对str进行解析，解析为一个日期

其中：

（1）DATE_ADD(datetime,INTERVAL expr type)

表达式类型：

参数类型参数类型YEARYEAR_MONTHMONTHDAY_HOURDAYDAY_MINUTEHOURDAY_SECONDMINUTEHOUR_MINUTESECONDHOUR_SECONDMINUTE_SECOND

举例：

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号

（2）DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

格式符说明格式符说明%Y4位数字表示年份%y表示两位数字表示年份%M月名表示月份（January,…）%m两位数字表示月份（01,02,03。。。）%b缩写的月名（Jan.，Feb.，…）%c数字表示月份（1,2,3,…）%D英文后缀表示月中的天数（1st,2nd,3rd,…）%d两位数字表示月中的天数(01,02…)%e数字形式表示月中的天数（1,2,3,4,5…）%H两位数字表示小数，24小时制（01,02…）%h和%I两位数字表示小时，12小时制（01,02…）%k数字形式的小时，24小时制(1,2,3)%l数字形式表示小时，12小时制（1,2,3,4…）%i两位数字表示分钟（00,01,02）%S和%s两位数字表示秒(00,01,02…)%W一周中的星期名称（Sunday…）%a一周中的星期缩写（Sun.，Mon.,Tues.，…）%w以数字表示周中的天数(0=Sunday,1=Monday…)%j以3位数字表示年中的天数(001,002…)%U以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天%u以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天%T24小时制%r12小时制%pAM或PM%%表示%

select date_format(now(),'%Y %m %D %H %i %s')
                         2020 09 8th 13 56 51

select str_to_date('2020-9-9', "%Y-%m-%D")
2020-09-09

5.1.4 流程函数 函数用法IF(value,t ,f)如果value是真，返回t，否则返回fIFNULL(value1, value2)如果value1不为空，返回value1，否则返回value2CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END相当于Java的if…else if…else…CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END相当于Java的switch…case…

• 举例1：

SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"
FROM employees;

• 举例2：

SELECT last_name, job_id, salary,
       CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
                   WHEN 'ST_CLERK' THEN  1.15*salary
                   WHEN 'SA_REP'   THEN  1.20*salary
       ELSE      salary END     "实发工资"
FROM   employees;

• 举例3：

SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' 
				  WHEN salary>=10000 THEN '潜力股'  
				  WHEN salary>=8000 THEN '屌丝' 
				  ELSE '草根' END  "描述"
FROM employees; 

5.1.5 其他函数 函数用法database()返回当前数据库名version()返回当前数据库版本user()返回当前登录用户名password(str)返回字符串str的加密版本，41位长的字符串md5(str)返回字符串str的md5值，也是一种加密方式 5.2多行（分组）函数

• 什么是分组函数

对一组数据进行运算，针对一组数据（多行记录）只返回一个结果，也称分组函数。

• 组函数类型
  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT()

可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM   employees
WHERE  job_id LIKE '%REP%';

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM	  employees;

COUNT(*)count(1返回表中记录总数,适用于任意数据类型。

SELECT COUNT(*)
FROM	  employees
WHERE  department_id = 50;

?COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM   employees
WHERE  department_id = 50;

• 问题：用count(*)，count(1)谁好呢?

  其实，对于myisam引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

  Innodb引擎的表用count(*)直接读行数，效率很低，因为innodb真的要去数一遍。