MySQL笔记--MySQL常见函数(7)

我们已经学习完排序查询，接下来我们涉足常见函数，跟上我的步伐！

进阶4：常见函数

基础知识

概念

类似于 java 中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名

优点

1.隐藏了实现的细节
2.提高代码的重用性

调用（现成函数）

select 函数名（实参列表）【from 表】；

分类

1.单行函数 如：length，ifnull,concat等
2.分组函数又称为统计函数，聚合函数，组函数
功能：做统计使用

单行函数

字符函数

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

代码

SELECT LENGTH('张三nbnbnb');

结果

2.concat: 拼接字符串

代码

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

结果

3.upper,lower：转换成大写字母；转换成小写字母

案例1：将姓变大写，名变小写，然后拼接

代码

SELECT 
			CONCAT(UPPER(last_name),'-',LOWER(first_name))AS 姓名 
FROM 
			employees

结果

4.substr,substring

注意：SQL语句索引从1开始

截取从指定索引处后面所有的字符

代码

SELECT SUBSTR('小龙女爱上杨过',6) AS 大侠;

结果

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

代码

SELECT SUBSTR('小龙女爱上杨过',1,3) AS 仙女;

结果

案例1;姓名首字符大写，其他字符小写然后用’-‘拼接，显示出来

代码

SELECT
			CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2)),'_',
			UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2))) AS 姓名
			
FROM
			employees;

结果

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

代码

SELECT INSTR('郭靖喜欢黄蓉','郭靖') AS 大侠;

6.trim：去掉前后空格；可以去掉字符如下所示

代码

SELECT	LENGTH(TRIM('    --     ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaa-aa-aaaa') AS out_put;

结果

7.lpad: 用指定的字符实现左填充指定长度,如果长度小于的话如下例子

代码

SELECT LPAD('唐代诗人李白',10,'X');
SELECT LPAD('唐代诗人李白',3,'X');

结果

8.rpad: 用指定的字符实现右填充指定长度，如果长度小于的话如上例子

代码

SELECT RPAD('唐代诗人李白',10,'X');

结果

9.replace: 替换全部字符串

代码

SELECT REPLACE('张无忌爱上周芷若','周芷若','周敏')AS 倚天屠龙记;

结果

数字函数

1.round: 四舍五入

代码

SELECT ROUND(2.54) AS 四舍五入;

结果

2.ceil: 向上取整,返回>=该参数的最小整数

代码

SELECT CEIL(1.01) AS 向上取整;

结果

3.floor: 向下取整，返回<=该参数的最大整数

代码

SELECT FLOOR(1.01) AS 向下取整;

结果

4.truncate：截断，后面取几位小数

代码

SELECT TRUNCATE(1.3333,2) AS 截断;

结果

5.mod: 取余 <=> %

# MOD(a,b) <=> a-a/b*b
#注意这里的a/b是取整

日期函数

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

代码

SELECT NOW() AS 日期;

结果

2.curdate: 返回当前系统日期，不包括时间

代码

SELECT CURDATE() AS 日期;

结果

3.curtime: 返回当前时间

代码

SELECT CURTIME() AS 时间;

结果

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

#年
SELECT YEAR(NOW()) AS 年;    #2021
SELECT YEAR('2001-3-18') AS 年;  #2001
SELECT YEAR(hiredate) AS 年 FROM  employees; #2014

#月
SELECT MOUTH(NOW()) AS 月; #6
SELECT MOUTHNAME(NOW()) AS 月; #June

#str_to_data:将日期格式的字符转换成指定格式的日期
SELECT STR_TO_DATE('6-7-2021','%m-%d-%Y') AS 日期;  #2021-06-07
#date_format:将日期转换成字符
SELECT DATE_FORMAT('2021/6/7','%Y年%m月%d日') AS 日期; #2021年06月07日

指定格式符号

案例1：查询入职日期为1992-4-3的员工信息

代码

SELECT
			*
FROM
			employees
WHERE
			hiredate = STR_TO_DATE('1992-4-3','%Y-%c-%d')

结果

案例2：查询有奖金的员工名和入职日期（XX年/XX月/XX日）

代码

SELECT
			last_name,DATE_FORMAT(hiredate,'%Y年/%m月/%d日') AS 入职日期
FROM
			employees
WHERE
			commission_pct IS NOT NULL;

结果

其他函数（补充）

代码

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

流程控制函数（补充）

if函数：if-else 的效果

代码

#类似三目运算
SELECT IF(10>5,'big','small') AS compare; #big

case函数

case 要判断的字段或表达式
when 常量1(条件1) then 要显示的值或语句1
when 常量2(条件2) then 要显示的值或语句2
…
else 要显示的值n或语句n
end;

案例1：查询员工的工资，要求：部门号=30，显示的工资为1.1倍；部门号=40，显示的工资为1.2倍；部门号文为50，显示的工资为1.3倍；其他部门不变

代码

SELECT department_id,salary AS 原来的工资,
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;

结果