Mysql基础函数
标签（空格分隔）： 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 ;
日期函数要使用到的格式符
| 格式符 | 功能 |
|---|---|
| %Y | 四位的年份 |
| %y | 2位的年份 |
| %m | 月份（01，02....11，12） |
| %c | 月份（1，2.....11,12) |
| %d | 日（01，02....) |
| %H | 小时（二十四小时制） |
| %h | 小时（十二小时制） |
| %i | 分钟（00，01....59) |
| %s | 秒（00，01....59) |
其他函数
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` ;错误，不对应
