前端学数据库之Mysql函数

字符函数

function1

function2

CONCAT()

  CONCAT()函数用于字符连接

mysql> select concat ('a','b');

CONCAT()函数也可用于将一个数据表中的两个字段的所有记录进行字符连接

mysql> create table test(

-> username varchar(20),

-> age tinyint unsigned

-> );

Query OK, 0 rows affected (0.12 sec)

insert test values ('a',1),('b',2),('c',3),('d',4);

CONCAT_WS()

  CONCAT_WS()函数使用指定的分隔符进行字符连接，该函数的第一个参数是指定分隔符

select concat_ws ('','1','2','3');

FORMAT()

  FORMAT()函数主要用于数字格式化，最终返回一个字符型数字。第一个参数为待格式化的数字，第二个参数为要保留的小数位数　

mysql> select format (123456,45677,2);

LOWER()

  LOWER()函数将字符转换成小写字母

UPPER()

  UPPER()函数将字符转换成大写字母

LEFT()

  LEFT()函数用于获取左侧字符

RIGHT()

  RIGHT()函数用于获取右侧字符

LENGTH()

  LENGTH()函数用于获取字符串长度

LTRIM()

  LTRIM()函数用于删除前导空格

RTRIM()

  RTRIM()函数用于删除后续空格

TRIM()

  TRIM()函数用于删除前导和后续空格

REPLACE()

  REPLACE()函数用于字符串替换

mysql> select replace('Mysql','l','-');

SUBSTRING()

  SUBSTRING()函数用于字符串截取，第一个参数是开始截取的起始位置，第二个参数是截取的字符数目

  注意：这里是从1开始计数的，而不是从0开始的

mysql> select substring('Mysql',1,3);

如果省略第二个参数，则一直截取到字符串的结尾

mysql> select substring('Mysql',2);

[NOT] LIKE

  [NOT] LIKE用于模式匹配，其中%代表0个或多个字符，_代表任意1个字符。返回1表示匹配，0表示不匹配

数值运算符

function15

CEIL()

  CEIL()函数主要用于进一取整(向上取整)

mysql> select ceil(3.14);

FLOOR()

mysql> select floor(3.1);

  FLOOR()函数主要用于舍一取整(向下取整)

ROUND()

  ROUND()函数主要用于四舍五入，两个参数分别为浮点数和保留的小数位数

DIV

  DIV主要用于整数除法

mysql> select 7/2;

MOD

  MOD主要用于取余数(取模)，等同于% ，可以是整数也可以是小数

POWER()

  POWER()函数主要用于幂运算

mysql> select power(3,2);

TRUNCATE()

  TRUNCATE()函数主要用于数字截取，两个参数分别为数值和截断到小数点后n位

mysql> select truncate(33.3,2);

mysql> select truncate(33.33,0);

mysql> select truncate(33.33,-1);

mysql> select truncate(33.33,-2);

比较运算符

function23

[NOT] BETWEEN...AND...

  [NOT] BETWEEN...AND...表示[不]在范围之内

mysql> select 10 not between 5 and 20;

mysql> select 10 between 5 and 20;

[NOT] IN()

  [NOT] IN()表示[不]在列出值范围内

mysql> select 10 in (5,10,15,20);

mysql> select 12 in (5,10,15,20);

IS [NOT] NULL

  IS [NOT] NULL表示不为空

mysql> select null is null;

mysql> select 0 is null;

日期时间

function27

NOW()

  NOW()函数返回当前日期和时间

mysql> select now();

CURDATE()

  CURDATE()函数返回当前日期

mysql> select curdate();

CURTIME()

  CURTIME()函数返回当前时间

DATE_ADD()

  DATE_ADD()函数日期变化，可以增加，也可以减少

mysql> select date_add('2020-09-24',interval 365 day);

mysql> select date_add('2020-09-24',interval -365 day);

mysql> select date_add('2020-09-24',interval 1 year);

DATEDIFF()

  DATEDIFF()函数日期差值，两个日期的差值

mysql> select datediff('2020-09-24','2018-09-24');

DATE_FORMAT()

  DATE_FORMAT()函数进行日期格式化

mysql> select date_format('2020-09-24','%m/%d/%y');

信息函数

function34

CONNECTION_ID()

  CONNECTION_ID()返回连接ID(线程ID)

DATEBASE()

  DATEBASE()返回当前数据库名称

LAST_INSERT_ID()

  LAST_INSERT_ID()返回最后插入记录的ID，当一次性写入多条记录时，函数返回的ID为第一条记录的ID

mysql> SHOW COLUMNS FROM test;

mysql> INSERT test VALUES(NULL,'Mary'), (NULL, 'H');

mysql> SELECT LAST_INSERT_ID();  

USER()

  USER()返回当前用户

VERSION()

  VERSION()返回版本信息

mysql> select user();

mysql> select version();

聚合函数

function40

聚合函数只能用于数据表，不能用于单个数值的计算

下面建立一个test数据表用来测试数据

mysql> create table test1( id tinyint, num tinyint );

Query OK, 0 rows affected (0.03 sec)

mysql> insert test1 values (1,1),(2,2),(3,3),(4,4),(5,5);

AVG()：平均值COUNT()：计数 MAX()：最大值 MIN()：最小值SUM()：求和

加密函数

function44

MD5()

  MD5()：信息摘要算法，为以后的Web页面做准备，尽量使用MD5()

PASSWORD()

  PASSWORD()：密码算法，通过PASSWORD()修改当前用户和其他用户的密码，修改客户端自己的密码

自定义函数

  函数可以返回任意类型的值，同样可以接收这些类型的参数。函数参数和返回值之前没有必然的联系

  注意：一个函数最多可以有1024个参数

  用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径，其用法与内置函数相同

  自定义函数的两个必要条件：1、参数；2、返回值

创建自定义函数

CREATE FUNCTION function_nameRETURNS 

{STRING|INTEGER|REAL|DECIMAL}routine_body

关于自定义函数的函数体

  1.函数体可以由合法的SQL语句构成；

  2.函数体可以是简单的SELECT或INSERT语句；

  3.函数体如果为复合结构则使用BEGIN...END语句；

  4.复合结构可以包含声明、循环、控制结构。

创建不带参数的自定义函数

CREATE FUNCTION f1() RETURNS VARCHAR(30) 

RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');

创建带参数的自定义函数

注意：如果自定义函数存在符合结构的多个语句，函数体要包含在BEGIN...END内，同时，需要通过DELIMITER将默认的结束符 ; 修改成其他符号，如：//   $$  ，以免函数由于语句结尾的 ; 号导致中断

删除函数

DROP FUNCTION [IF EXISTS] function_name