MySQL入门(五)函数

本文数据集下载链接:https://pan.baidu.com/s/1sVcSXfVZimc6ruyMr4Gr3g ,提取码:7gfa

一、常见函数

调用方法SELECT 函数名(实参列表) [FROM 表]

优点:隐藏实现细节;提高代码的重用性

分类

  1. 单行函数,如concatlengthifnull
  2. 分组函数,传递一组值进去,传出一个值(又称统计函数)
1
2
3
SELECT DATABASE();		# 查看当前DATABASE
SELECT USER();
SELECT VERSION();

二、单行函数

1. 字符函数

函数1:LENGTH

1
2
SELECT LENGTH("MySQL");
SELECT LENGTH("MySQL真简单");

第一个显示为5,第二个显示为14(一个汉字占3个字节),由此我们知道LENGTH函数是用于获取参数值的字节个数的

函数2:CONCAT拼接函数

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

函数3:UPPER、LOWER

1
2
SELECT UPPER('MySQL');
SELECT LOWER('MySQL');

套娃1:将姓大写,名小写然后拼接

1
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) FROM employees;

由此可见函数可以嵌套调用

函数4:SUBSTR\SUBSTRING

1
SELECT SUBSTR('我喜欢MySQL', 4) output;

该函数返回MySQL,由此我们可见该函数的作用是返回索引及其之后的内容,同时我们可以发现MySQL中的索引是从0开始

1
SELECT SUBSTR('我喜欢你', 2, 4) output;

该函数返回喜欢你,这里是截取从指定索引处指定字符长度的字符

套娃2:姓名中首字符大写,其他字符小写,并用_拼接显示出来

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

函数5:instr返回起始索引

1
SELECT INSTR('我喜欢MySQL', 'MySQL') AS output;

返回结果为4

注意:如果找不到对应索引,返回0

函数6:trim去首尾空格

1
SELECT TRIM('    MySQL   ') AS output;

实际上也可以去除前后某个指定的元素,比如我们要去除下例中首尾的a

1
SELECT TRIM('a' FROM 'aaaaaaaMySQLaaaa') AS output;

函数7:lpad用指定字符实现左填充指定长度

1
SELECT LPAD('MySQL', 10, '*') AS output;

返回结果为*****MySQL,若我们将长度指定为小于字段长的数字,例如:

1
SELECT LPAD('MySQL', 3, '*') AS output;

返回值为MyS

函数8:rpad用指定字符右填充指定长度

用法与左填充一样

函数9:replace替换

1
SELECT REPLACE('我爱MySQL', '爱', '讨厌') AS output;

结果很容易猜到,嘿嘿这里就不说啦。而且注意哦,这里的替换是全部替换,可以自己验证一下

2. 数学函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/*
round: 四舍五入
*/
SELECT ROUND(-1.55); # 输出结果为-2
SELECT ROUND(1.467,2); # 输出结果为1.47

/*
ceil: 向上取整(返回大于等于该数的最小整数)
floor: 向下取整(返回小于等于该数的最大整数)
*/
SELECT CEIL(1.01); # 输出结果为2
SELECT FLOOR(-9.99); # 输出结果为-10

/*
truncate: 截断
*/
SELECT TRUNCATE(1.65, 1); # 输出结果为1.6

/*
mod: 取余
*/
SELECT MOD(10, 3); # 输出结果为1
# 被除数如果是正则为正,如果是负则为负,因为运算方式为:MOD(a,b)=a-a/b*b

3. 日期函数

3.1 获取日期的函数

1
2
3
4
5
6
7
8
9
10
11
12
13
# now: 返回当前系统日期+时间
SELECT NOW();

# curdate: 返回当前系统日期,不包含时间
SELECT CURDATE();

# curtime: 返回当前系统时间,不包含日期
SELECT CURTIME();

# 也可以自己定其他截取的时间特征:
SELECT YEAR(NOW()); # 返回今年
# 若希望返回英文:
SELECT MONTHNAME(NOW());

3.2 转换日期的函数

STR_TO_DATE

该函数的作用是按日期格式的字符转换成指定格式的日期,示例如下:

1
SELECT STR_TO_DATE('1999/2/18','%Y/%m/%d');

返回1999-02-18,小伙伴可以自己尝试大小写的区别,并自行查阅其他格式符的含义和功能

DATE_FORMAT

该函数的作用是将日期转换成字符,恰好与STR_TO_DATE反过来,示例如下:

1
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');

案例1:查询入职日期是1992年4月3号的员工信息

1
2
3
SELECT * 
FROM employees
WHERE hiredate = STR_TO_DATE('1992年4月3日','%Y年%m月%d日');

案例2:查询有奖金的员工名及入职日期,要求格式为:xx月/xx日/xx年

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

三、流程控制函数

1. IF函数

IF函数有三个参数,第一个表达式的结果若为 true,则返回表达式二的值,否则返回表达式三的值,如下将返回21

1
SELECT IF(10>5, 21, 10);

示例:若员工有奖金则提示有,否则提示没有

1
SELECT last_name, commission_pct, IF(commission_pct IS NULL, '没奖金', '有奖金') FROM employees;

2. case函数

使用语法一

1
2
3
4
5
6
CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1
WHEN 常量2 THEN 要显示的值2或语句2
...
ELSE 要显示的值n或语句n
END

案例1:查询员工的工资,要求部门编号=30,则显示工资为1.1倍,部门编号=20,则显示工资为1.2倍,部门编号=30,则显示工资为1.3倍,其他部门显示的工资为原工资

1
2
3
4
5
6
7
8
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;

使用语法二:类似于多重if

1
2
3
4
5
6
CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
...
THEN 要显示的值n或语句n
END

案例2:查询员工的工资情况:如果工资>20000,显示级别A,若工资>15000,显示级别B,若工资>10000,显示级别C,否则显示级别D

1
2
3
4
5
6
7
8
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS '工资级别'
FROM employees;
  • 版权声明: 本博客所有文章除特别声明外,著作权归作者所有。转载请注明出处!

扫一扫,分享到微信

微信分享二维码
  • Copyrights © 2020-2021 chenk
  • 由 帅气的CK本尊 强力驱动
  • 访问人数: | 浏览次数:

请我喝杯咖啡吧~

支付宝
微信