标签（空格分隔）： MySQL

一、MySQL中常见的函数

一、字符函数

1.length获取参数值的字节个数

查看字符长度语句：
SHOW VARIABLES LIKE '%char%'

2.concat 拼接字符串

SELECT CONCAT(last_name,'_',first_name)  姓名
FROM `employees` ;

3. upper(大写转换）、lower（小写转换）

语法: upper()

示例：将姓名大写，名变小写，然后拼接
SELECT CONCAT(UPPER(`last_name`),'_',LOWER(`first_name`))  姓名
FROM `employees` ;

4. substr、substring,索引

注意：索引从1开始
语法：substr()
截取从指定索引处后面所有字符
示例：

SELECT SUBSTR('李莫愁爱上了陆展源',7)  out_put ;

截取从指定索引处指定字符串长度

SELECT SUBSTR('李莫愁爱上了陆展源',1，3)  out_put ;

案例：姓名中首字母大写，其余字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER (SUBSTR(`last_name`,1,1)) , '_' , LOWER(SUBSTR(`last_name` , 2)))
FROM `employees` ;

5. instr(返回子串第一次出现的索引，如果找不到返回0）

SELECT INSTR('杨不殷六侠爱上了殷六侠','殷六侠')  out_put ;

6. trim(去前后空格）

 去空格（前后）
SELECT TRIM('       张翠上     ')AS  out_put ;
 去指定字符（前后）
SELECT TRIM('a'FROM 'aaaaaaaa张aaaaa翠山aaaaaaa') AS out_put ;

7. lpad（指定的支付串实现左填充指定长度）

SELECT LPAD('殷素素',10,'*') AS out_put ;

8. rpad(指定的支付串实现右填充指定长度）

SELECT RPAD('小星星',10,'*') AS out_put ;

9. replace 替换（全体替换）

SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put ;

二、数学函数

1、round 四舍五入

SELECT ROUND(-1.23) ;          == -2
 SELECT ROUND(1.2345,2) ;      == 1.23(小数点等候保留几位小数）

2、ceil 向上取整，返回>=该参数的最小整数

SELECT CEIL(1.09) ;               ==2
 SELECT CEIL(-1.09) ;             ==-1

3、floor 向下取整，返回<=该参数的最小整数

 SELECT FLOOR(1.09) ;           == 1
  SELECT  FLOOR(-1.09) ;          ==-2

4、truncate 截断

SELECT TRUNCATE(1.23456789,1) ;    == 1.2

5、mod取余 a-a/b*b

 SELECT MOD(10,3) ;               == 1
 SELECT MOD(-10,3) ;               == -1
 SELECT MOD(-10,-3) ;              == -1

三、日期函数

1、now 返回当前系统日期+时间

SELECT NOW() ;

2、curdate 返回当前系统日期，不包含时间

SELECT CURDATE() ;

3、curtime 返回当前时间，不包含日期

SELECT CURTIME() ;

4、获取指定的部分，年、月、日、小时、分钟、秒

 SELECT YEAR(NOW()) ;
 SELECT YEAR('1992-1-13') ;       ==1992
   SELECT MONTH(NOW()) ;
   SELECT MONTHNAME(NOW()) ;//英文输出

str_to_date 将字符通过指定的格式转换成日期

查询入职日期为1992-4-3的员工信息
方法一：
SELECT * FROM `employees`
WHERE `hiredate` = '1992-4-3' ;
方法二：
SELECT * FROM `employees`
WHERE `hiredate` = STR_TO_DATE('4-3-1992','%c-%d-%Y') ;

dete_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') out_put ;

案例：查询有奖金的员工名和入职日期，并按奖金比降序排列
写法一：
SELECT `last_name`,DATE_FORMAT(`hiredate`,'%Y-%c-%d') AS 入职日期,`commission_pct`
FROM `employees` WHERE NOT ISNULL(`commission_pct`)
ORDER BY `commission_pct` DESC ;
 
写法二：
SELECT `last_name`,DATE_FORMAT(`hiredate`,'%m/%d %y') AS 入职日期,`commission_pct`
FROM `employees` WHERE `commission_pct` IS NOT NULL
ORDER BY `commission_pct` IS NOT NULL DESC ;

日期函数要使用到的格式符

其他函数

1、select version() ;版本号
2、select database() ;查看当前前数据库
3、select user() ;当前的用户

流程控制函数

1、if函数： if else 的效果

写法：if(比较或要判断的 ， 值1， 值2）

SELECT IF(10>5,'大','小') ;

案例：有无奖金
 SELECT `last_name`,`commission_pct`, IF(`commission_pct` IS NULL,'没奖金,哈哈哈','有奖金，嘻嘻') AS 有无奖金 
 FROM `employees` ;

2、case函数的使用一：switch case 的效果

mysql中case语句的概念及其语法：
case 要判断的字段或表达式
when 常量1 then 要显示的值或语句1 ；
when常量2 then 要显示的值或语句2 ；
....
else 要显示的值或语句n;
end

案例：查询员工的工资
要求：
 部门号 = 30， 显示的工资为1.1倍
 部门号 = 40， 显示的工资为1.2倍
 部门号 = 50， 显示的工资为1.3倍
 其他部门，显示原工资
 SELECT `salary`,`department_id`,
 CASE `department_id`
 WHEN 30 THEN `salary` * 1.1
 WHEN 40 THEN `salary` * 1.2
 WHEN 50 THEN `salary` * 1.3
 ELSE `salary`
 END AS 新工资 
 FROM `employees` ;

3、case 函数的使用二：类似于 多重if

mysql中
case
when 常量1 then 要显示的值或语句1 ；
when常量2 then 要显示的值或语句2 ；
....
else 要显示的值或语句n;
end

案例：查询员工的工资
    要求：
    如果工资>20000,显示A级别
    如果工资>20000,显示A级别
    如果工资>20000,显示A级别
    否则，显示D级别
 SELECT `salary`,`department_id`,
 CASE
 WHEN `salary`>20000 THEN 'A'
 WHEN `salary`>15000 THEN 'B'
 WHEN `salary`>10000 THEN 'C'
 ELSE 'D'
 END AS 工资级别

二、分组函数

功能：用于统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 求和、avg 平均值、 max 最大值、min 最小值、 count 计算个数

特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数均忽略null值
3、可以和distinct搭配
4、count函数的单独介绍：一般使用COUNT（*）用作统计行数
5、和分组函数一同查询的字段要求是group by后的字段

1、简单 的使用

 SELECT SUM(`salary`) FROM `employees` ;
  SELECT AVG(`salary`) FROM `employees` ; 
  SELECT SUM(`salary`) AS 和,AVG(`salary`) AS 平均 FROM `employees` ;

2、和distinct搭配

SELECT SUM(DISTINCT `salary`), SUM(`salary`) FROM `employees` ;

3、count函数的详细介绍

 SELECT COUNT(`salary`) FROM `employees` ;
 SELECT COUNT(*) FROM `employees` ;用的最多
 SELECT COUNT(1) FROM `employees` ;
三者结果相同

效率：
MYISAM存储引擎下，COUNT（）的效率高
INNODB存储引擎下，CIOUNT（）和count（1）的效率差不多，比COUNT（字段）要高一些

4、和分组函数一同查询的字段有限制

 SELECT AVG(`salary`),`email` FROM `employees` ;错误，不对应