4.单行函数

1.SQL函数介绍:

2.单行函数分类

3.字符函数

3.数学函数

4.日期函数

5.其他函数

6.流程控制函数

---

1.SQL函数介绍:

# 1.概念:SQL函数类似于Java中的方法, python中的函数, 实际就是封装的一小段代码, 对外只暴露方法名, 提供调用.

# 2.函数的优势:
　　1).对外提供调用,隐藏了具体的实现细节
　　2).提高了代码的重用性

# 3.调用语法: select 函数名(实参列表) [from 表名];

# 4.SQL函数的分类:
　　1).单行函数,如concat, length, ifnull等
　　2).分组函数:用作统计,又称统计函数,聚合函数,组函数

 

返回顶部

---

2.单行函数分类

# 1.字符函数
　　1).length:获取参数值的字节个数
　　2).concat:拼接字符串
　　3).upper, lower:转变字符的大小写
　　4).substr, substring:截取指定的字符
　　5).instr:返回子串第一次出现的起始索引,如果找不到返回0
　　6).trim:去除字符首尾的空格,或首尾的指定字符
　　7).lpad:用指定字符实现指定长度的字符串左填充
　　　 rpad:用指定字符实现指定长度的字符串右填充
　　8).replace:将字符串中的指定字符替换为指定的字符

# 2.数学函数
　　1).round:四舍五入
　　2).ceil:向上取整,返回一个大于等于该参数的最小整数
　　3).floor:向下取整,返回一个小于等于该参数的最小整数
　　4).truncate:截断函数
　　5).mod:取余

# 3.日期函数
　　1).now:返回当前系统日期+时间
　　2).curdata:返回当前系统日期,不包含时间
　　3).curtime:返回当前时间,不包含日期
　　4).year, month, day, hour, minute, second:获取指定部分,年月日时分秒

# 4.其他函数
　　1).version():查看当前MySQL版本
24　　 2).database():查看当前工作的数据库
　　3).user():查看当前用户

# 5.流程控制函数
　　1).if函数
　　2).case函数

 

返回顶部

---

3.字符函数

# 1.length: 获取参数值的字节数
　　select length("mysql");# 结果与mysql字符集相关, UTF-8下, 结果为:5
 
 3 # 2.concat: 拼接字符串
　　select concat(last_name,'_', first_name)as姓名from employees;
　　注:如果concat拼接的字段中有一个为null,拼接后的结果为null,可以配合ifnull函数进行逻辑处理.
 
 6 # 3.upper & lower: 将指定的字符转换为大小写, 如果单独处理字符串中的某个字符, 可以配合substr和concat使用
　　示例:将员工last_name变为大写, first_name变为小写,并拼接起来,命名为姓名
　　select concat(upper(last_name),lower(first_name))as姓名from employees;
 
 9 # 4.substr | substring:按要求截取字符串, 两种方式:
　　1).截取从指定索引出后面的所有字符
　　　　select substr('HelloMySQL',6);# 结果为:MySQL, MySQL中的索引位置从1开始
　　2).截取从指定索引位置的固定长度的字符
　　　　select substr('HelloMySQL',1,5);# 结果为:Hello, 其中的1是索引位置, 5代表要截取的字符长度为5
　　　　示例:实现将员工的last_name转换为首字母大写,其他字母小写的效果并以translation显示出来
　　　　select concat(upper(substr(last_name,1,1)), lower(substr(last_name,2)))as translation from employees;

# 5.instr: 返回子串第一次出现的索引, 如果找不到返回0
　　　　select instr('bcade','a')as times;# 结果为:3


# 6.trim: 取出字符首尾的空格或指定字符
　　1).去除空格
　　　　select trim(' ABC ')as strip;
　　2).去除指定字符
　　　　select trim('a'from'aaaHelloMySQLaaa')as strip;


# 7.lpad & rpad: 用指定字符实现指定长度字符串的左右填充
　　select lpad('hello',10,'*');# 结果为:*****hello
　　select rpad('hello',10,'*');# 结果为:hello*****
　　注:如果指定的字符长度小于第一个字符参数的长度, mysql会从左截取第二个参数相应的字符个数,例:
　　select lpad('hello',3,'*');# 结果为:hel
　　select rpad('hello',3,'*');# 结果为:hel


# 8.replace: 替换指定字符为目标字符
　　select replace('hello Oracle','Oracle','MySQL')as rep;# 结果为:hello MySQL
　　select replace('hello ABAC','A','D')as rep;# 结果为:hello DBDC, 全部替换

 

返回顶部

---

3.数学函数

# 1.round: 四舍五入
　　select round(1.672);# 结果:2
　　select round(1.672,2);# 结果:1.67
 
 4 # 2.ceil & floor: 向上取整(返回大于等于该阐述的最小整数) & 向下取整(返回小于等于该参数的最大整数)
　　select ceil(1.002);# 结果为:2
　　select ceil(-1.002);# 结果为:-1
　　select floor(1.002);# 结果为:1
　　select floor(-1.002);# 结果为:-2
 
 9 # 3.truncate: 截断, 不进行四舍五入
　　select truncate(1.56428,2);# 结果为:1.56

# 4.mod: 取余
　　select mod(10,-3);# 结果为: 1
　　select mod(-10,3);# 结果为: -1
　　注:运算机制为:mod(a, b)-->模= a -(a/b)*b (其中a/b是除法后的取整运算)
　　也可简单记忆为:模的符号与被操作数相同,即被除数

 

返回顶部

---

4.日期函数

# 1.now: 返回当前系统日期 + 时间
　　select now();

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


# 3.curtime: 返回当前时间, 不包含日期
　　select curtime();


# 4.获取指定的部分:年月日时分秒 --> (year, month, day, hour, minute, second)
　　select year(now());# 结果为:2019
　　select year('2019-4-22')# 结果为:2019
　　select year(hiredate)as年from employees;# 查询员工的入职年份
　　(其他以此类推)


# 5.str_to_date: 将日期格式的字符转换成指定格式的日期
　　select str_to_date('4-22-2019','m%-%d-%Y');


# 6.date_format: 将日期转换成字符
　　示例:查询奖金率不为空的所有员工的姓名和入职日期,入职日期以'xx月/xx日 xx年'的格式显示
　　select last_name, date_format(hiredate,'%m月/%d日 %y年')
　　from employees
　　where commission_pct isnotnull;

 

返回顶部

# 关于日期格式的格式化符号如下表:

序号	格式符	功能
1	%Y	四位的年份
2	%y	两位的年份
3	%m	月份(01, 02, …)
4	%c	月份(1, 2, ……..)
5	%d	日(01, 02, 03, …)
6	%H	小时(24小时制)
7	%h	小时(12小时制)
8	%i	分钟(00, 01, 02, …)
9	%s	秒(00, 01, 02, …)

返回顶部

---

5.其他函数

# 1.select version(); 查看当前mysql的版本信息
# 2.select database(); 查看当前工作的数据库
# 3.select user(); 查看当前用户

 

返回顶部

---

6.流程控制函数

# if函数: if(表达式, 值1, 值2) --> 如果表达式为True, 显示值1, 反之, 显示值2
示例:查询员工名字,奖金率,判断奖金率是否为空,为空的显示备注字段为'没奖金, 呵呵',不为空的显示备注字段为'有奖金, 嘻嘻'
select last_name, commission_pct,if(commission_pct isnull,'没奖金, 呵呵','有奖金, 嘻嘻')as备注
from employees;

 

返回顶部

# case函数的使用:两种方式
1.相当于Java中switch...case效果,适合与等值判断
　　1).语法:
　　　　case要判断的字段或表达式
　　　　when常量1then要显示的值1(或语句1;)
　　　　when常量2then要显示的值2(或语句2;)
　　　　...
　　　　else要显示的值n(或语句n;)
　　　　end
　　2).示例:查询员工的工资,部门号,要求部门号=30的,显示新工资为原工资的1.1倍,部门号=40的,显示新工资为原工资的1.2倍,部门号=50的,显示新工资为原工资的1.3倍,其他部门号的显示新工资为原工资数并显示为新工资列.
　　　　select salary as原始工资, department_id,
　　　　case department_id
13　　　　 when30then salary*1.1
　　　　when40then salary*1.2
　　　　when50then salary*1.3
　　　　else salary
　　　　endas新工资
　　　　from employees;


2.相当于多重if判断,适合不等值的情况下使用
　　1).语法:
　　　　case
　　　　when条件1then要显示的值1(或语句1;)
　　　　when条件2then要显示的值2(或语句2;)
　　　　...
　　　　else要显示的值n(或语句n)
　　　　end
　　2).示例:查询员工的工资情况,要求显示工资列和工资级别列,如果工资>20000,显示A,如果工资>1500,显示B,如果工资>10000,显示C,其他的显示D
　　　　select salary,
　　　　case
　　　　when salary>20000then'A'
　　　　when salary>15000then'B'
　　　　when salary>10000then'C'
　　　　else'D'
　　　　endas工资级别
　　　　from employees;

 

返回顶部