第二十节:MySQL中的函数
聚合函数
- 聚集函数:在行上进行运算并返回单个值的函数,主要用于汇集数据
- 注意点:聚合函数不能用于Where字句之后
- 创建表并且插入数据
create table Teacher( id int(11) primary key, name varchar(25), gender varchar(25), age int(11) ); insert into Teacher values(1,'Tom','man',21); insert into Teacher values(2,'Jack','man',22); insert into Teacher values(3,'Weiking','man',23); insert into Teacher values(4,'ZhangSan','man',24); insert into Teacher values(5,'TingTing','woman',null);
- avg(字段):返回指定字段的所有数据的平均值,会忽略列值为 NULL 的行
select avg(age) from Teacher; # 返回值为22.5
- count(字段):返回指定字段数据的行数
- count(*):统计整张表中的记录数,如果是null,也会进行统计
- count(1):统计整张表中的记录数,与count(*)功能相识,效率更高,如果是null,也会进行统计
- count(字段名称):对该字段有值的行数进行统计,如果是null,则不会进行统计
-
select count(*) from Teacher; # 返回值是5 select count(1) from Teacher; # 返回值是5 select count(age) from Teacher; # 返回值是4
- max(字段):返回指定字段的数据的最大值,如果指定字段的数据类型为字符串类型,先按字符串比较,然后返回最大值
select max(age) from Teacher; # 返回值是24 select max(name) from Teacher; # 返回值是ZhangSan
- min(字段):返回指定字段的数据的最小值,如果指定字段的数据类型为字符串类型,先按字符串比较,然后返回最小值
select min(age) from Teacher; # 返回值是21 select min(name) from Teacher; # 返回值是Jack
- sum(字段):返回指定字段的数据之和
select Sum(age) from Teacher; # 返回值是90
字符串函数
- concat(str1,...strn):用于将多个字符串合并成为一个字符串,如果传入的值中有null,那么最终结果是null
select concat('hello','workd'); # 返回值是helloworkd select concat('hello','null');# 返回值是hellonull select concat('hello',null); # 返回值是null
- concat_ws(分隔符,str1,str2,str3…):用于将多个字符串按照某个特定的分隔符合并成为一个字符串;如果分隔符为null,则最终结果返回null;如果字符串中有的值为null,则该字符串会被忽略
select concat_ws(' ','hello','world'); # 返回值是hello world select concat_ws(' ',null,'world'); # 返回值是world select concat_ws(null,'hello','world'); # 返回值是Null
- strcmp(str1,str2):用于比较两个字符串的大小。左大于右时返回1,左等于右时返回0,,左小于于右时返回-1(会从左到右逐个比较,直到比较出结果或者比较结尾)
select strcmp('ab','ac'); # 返回值是 -1 select strcmp('abcd','abc'); # 返回值是 1 select strcmp('abcd','abcd'); # 返回值是 0
- length(str):用于获取字符串的字节长度(要注意字符集)
select length('hello'); # 返回值是 5 select length(null); # 返回值是 NUll select length('你好'); # 返回值是 6 ,一个汉字三个字节
- upper(str):用于将str转换成为大写
select upper('Hello'); #返回值是HELLO
- lower(str):用于将str 转换成为小写
select lower('Hello'); #返回值是hello
- locate(str1,str2)和 position(str1 in str2): 用于返回字符串str1在str2中的位置,可以用来判断字符串str2中是否有字符串str1
select locate('wo','hello world'); # 返回值为7 select position('wo' in 'hello world'); # 返回值为7
- instr(str1,str2):用于返回字符串str2在str1中的位置,可以用来判断字符串str1中是否有字符串str2
select instr('hello world','wo');# 返回值为7
- left(str,n):截取str左边n的个字符
select left('hello',3); # 返回值为 hel
- right(str,n):截取str右边的n个字符
select right('hello',3); # 返回值为 llo
- substring(str,n,length):从字符串str下标为n的位置截取长度为length的字符(下标从1开始)
select substring('hello world',1,5); # 返回值为 hello
- ltrim(str):去除字符串str左边的空格
select ltrim(' hello world '); # 返回值为 'hello world '
- rtrim(str):去除字符串str右边的空格
select rtrim(' hello world '); # 返回值为 ' hello world'
- trim():去除字符串str两边的空格
select trim(' hello world '); # 返回值为 'hello world'
- replace(str,str1,str2): 将字符串str中所有的字符str1都替换为str2
select replace('hello world','workd','mysql'); # 返回值为 hello mysql
数值函数
- abs(x):返回x的绝对值
select abs(-1); # 返回值为1
- ceil(x):返回x的向上取整的整数
select ceil(-1.23); # 返回值为-1 select ceil(1.23); # 返回值为2
- floor:返回x向下取整的整数
select floor(-1.23); # 返回值为-2 select floor(1.23); # 返回值为1
- mod(x,y):返回x除y的余数
select mod(12,5); # 返回值为2
- rand():返回0-1之间的随机数,如果要使输出的随机值相同,可以使用rand(x),当x相同时返回同样的随机结果
select rand(); # 每次查询的结果不同 select rand(1); # 每次查询的结果都是相同随机数。
- round(x,y):对x进行四舍五入,保留y位小数
select round(3.1456,2); # 返回值为 3.15
- truncate(x,y):对数值x进行截取,保留y位小数(不四舍五入)
select truncate(3.1456,2); # 返回值为 3.14
日期函数
- 获取当前日期时间:now(),current_timestamp(),localtime(),sysdata()
select now(); # 返回值是 2020-01-21 07:56:27
- 获取当前日期:curdate(),current_date()
select curdate(); # 返回值是 2020-01-21
- 获取当前时间:curtime(),current_time()
select curtime(); # 返回值是 07:56:27
- 从某日期中截取年数、月份数、天数、小时数和分钟数:year(date)、month(date)、day(date)、hour(date)、minute(date)
select year('2019-12-31 08:21:49'); #返回值是2019 select month('2019-12-31 08:21:49'); #返回值是12 select day('2019-12-31 08:21:49'); #返回值是31 select hour('2019-12-31 08:21:49'); #返回值是8 select minute('2019-12-31 08:21:49'); #返回值是21
- 判断某日期是一年中的第几个季度、第几周和第几天:quarter(date)、week(date)、dayofyear(date)
select quarter('2019-12-31 08:21:49'); #返回值是4 select week('2019-12-31 08:21:49'); #返回值是52 select dayofyear('2019-12-31 08:21:49'); #返回值是365
- 判断某日期是星期几:dayname(date)
select dayname(curdate()); #返回值是 Tuesday
- 计算N天之前的日期:subdate(date,N)
select subdate('2019-12-31 08:21:49',3); #返回值是 2019-12-28 08:21:49
自定义函数
- 自定义函数:是一种过程式数据库对象,是由SQL语句和过程式语句组成的代码片段,可以被应用程序和其他的SQL语句调用
- 查看数据库中的自定义函数:show function status
- 查看创建自定义函数的语法:show create function 函数名
- 删除自定义函数:drop function if exists 函数名称
创建自定义函数
- 语法格式:create function 函数名称 (参数名称1 参数类型1,...,参数名称n 参数类型n) returns 类型 函数主体
- 语法说明:
- returns 类型:指定函数返回值的数据类型
- 函数主体:自定义函数的主体部分,包括局部变量、SET 语句、流程控制语句等。必须包含一个"Reture 值" 语句,其中值是函数的返回值
- 注意点:如果函数主体中的返回值是select语句,则select的返回值的结果只能是一行一列
- 实例:
- 创建函数;create function funAdd(a int,b int) returns int return a+b;
- 调用函数:select funAdd(1,6);