第二十节：MySQL中的函数

聚合函数

1. 聚集函数：在行上进行运算并返回单个值的函数，主要用于汇集数据
2. 注意点：聚合函数不能用于Where字句之后
3. 创建表并且插入数据

   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);

4. avg(字段)：返回指定字段的所有数据的平均值，会忽略列值为 NULL 的行

   select avg(age) from Teacher; # 返回值为22.5

5. count(字段)：返回指定字段数据的行数
   1. count(*)：统计整张表中的记录数，如果是null，也会进行统计
   2. count(1)：统计整张表中的记录数，与count(*)功能相识，效率更高，如果是null，也会进行统计
   3. count(字段名称)：对该字段有值的行数进行统计，如果是null，则不会进行统计

   4. select count(*) from Teacher; # 返回值是5
      select count(1) from Teacher; # 返回值是5
      select count(age) from Teacher; # 返回值是4

6.  max(字段)：返回指定字段的数据的最大值,如果指定字段的数据类型为字符串类型，先按字符串比较，然后返回最大值

   select max(age) from Teacher; # 返回值是24
   select max(name) from Teacher; # 返回值是ZhangSan

7. min(字段)：返回指定字段的数据的最小值,如果指定字段的数据类型为字符串类型，先按字符串比较，然后返回最小值

   select min(age) from Teacher; # 返回值是21
   select min(name) from Teacher; # 返回值是Jack

8. sum(字段)：返回指定字段的数据之和

   select Sum(age)  from Teacher; # 返回值是90

字符串函数

1. concat(str1,...strn):用于将多个字符串合并成为一个字符串，如果传入的值中有null，那么最终结果是null

   select concat('hello','workd');  # 返回值是helloworkd
   select concat('hello','null');#  返回值是hellonull
   select concat('hello',null); # 返回值是null

2. 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

3. strcmp(str1,str2):用于比较两个字符串的大小。左大于右时返回1，左等于右时返回0，，左小于于右时返回-1（会从左到右逐个比较，直到比较出结果或者比较结尾）

   select strcmp('ab','ac'); # 返回值是 -1
   select strcmp('abcd','abc'); # 返回值是 1
   select strcmp('abcd','abcd'); # 返回值是 0

4. length(str)：用于获取字符串的字节长度（要注意字符集）

   select length('hello'); # 返回值是 5
   select length(null); # 返回值是 NUll
   select length('你好'); # 返回值是 6 ，一个汉字三个字节

5. upper(str):用于将str转换成为大写

   select upper('Hello'); #返回值是HELLO

6. lower（str）:用于将str 转换成为小写

   select lower('Hello'); #返回值是hello

7. locate(str1,str2)和 position(str1 in str2): 用于返回字符串str1在str2中的位置,可以用来判断字符串str2中是否有字符串str1

   select locate('wo','hello world'); # 返回值为7
   select position('wo' in 'hello world'); # 返回值为7

8. instr(str1,str2):用于返回字符串str2在str1中的位置，可以用来判断字符串str1中是否有字符串str2

   select instr('hello world','wo')；# 返回值为7

9. left(str,n)：截取str左边n的个字符

   select left('hello',3); # 返回值为 hel

10. right(str,n)：截取str右边的n个字符

    select right('hello',3); # 返回值为 llo

11. substring(str,n,length)：从字符串str下标为n的位置截取长度为length的字符(下标从1开始)

    select substring('hello world',1,5); # 返回值为 hello

12. ltrim(str):去除字符串str左边的空格

    select ltrim(' hello world '); # 返回值为 'hello world '

13. rtrim(str):去除字符串str右边的空格

    select rtrim(' hello world '); # 返回值为 ' hello world'

14. trim():去除字符串str两边的空格

    select trim(' hello world '); # 返回值为 'hello world'

15. replace(str,str1,str2): 将字符串str中所有的字符str1都替换为str2

    select replace('hello world','workd','mysql'); # 返回值为  hello mysql

数值函数

1. abs(x)：返回x的绝对值

    select abs(-1); # 返回值为1

2. ceil(x)：返回x的向上取整的整数

   select ceil(-1.23); # 返回值为-1
   select ceil(1.23); # 返回值为2

3. floor：返回x向下取整的整数

   select floor(-1.23); # 返回值为-2
   select floor(1.23); # 返回值为1

4. mod(x,y):返回x除y的余数

   select mod(12,5);  # 返回值为2

5. rand():返回0-1之间的随机数，如果要使输出的随机值相同，可以使用rand(x),当x相同时返回同样的随机结果

   select rand(); # 每次查询的结果不同
   select rand(1); # 每次查询的结果都是相同随机数。

6. round(x,y)：对x进行四舍五入，保留y位小数

   select round(3.1456,2); # 返回值为 3.15

7. truncate(x,y)：对数值x进行截取，保留y位小数（不四舍五入）

   select truncate(3.1456,2); # 返回值为 3.14

日期函数

1. 获取当前日期时间：now(),current_timestamp(),localtime(),sysdata()

   select now(); # 返回值是 2020-01-21 07:56:27

2. 获取当前日期：curdate()，current_date()

   select curdate(); # 返回值是 2020-01-21

3. 获取当前时间：curtime()，current_time()

   select curtime(); # 返回值是 07:56:27

4. 从某日期中截取年数、月份数、天数、小时数和分钟数：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

5. 判断某日期是一年中的第几个季度、第几周和第几天：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

6. 判断某日期是星期几：dayname(date)

   select dayname(curdate()); #返回值是 Tuesday

7. 计算N天之前的日期:subdate(date,N)

   select subdate('2019-12-31 08:21:49',3); #返回值是  2019-12-28 08:21:49

自定义函数

1. 自定义函数：是一种过程式数据库对象，是由SQL语句和过程式语句组成的代码片段，可以被应用程序和其他的SQL语句调用
2. 查看数据库中的自定义函数：show function status
3. 查看创建自定义函数的语法：show create function 函数名
4. 删除自定义函数：drop function  if exists 函数名称

创建自定义函数

1. 语法格式：create function 函数名称 (参数名称1 参数类型1,...,参数名称n 参数类型n) returns 类型 函数主体
2. 语法说明：
   1. returns 类型：指定函数返回值的数据类型
   2. 函数主体：自定义函数的主体部分，包括局部变量、SET 语句、流程控制语句等。必须包含一个"Reture 值" 语句，其中值是函数的返回值
3. 注意点：如果函数主体中的返回值是select语句，则select的返回值的结果只能是一行一列
4. 实例：
   1. 创建函数;create function funAdd(a int,b int) returns int return a+b;
   2. 调用函数：select funAdd(1,6);