mysql 函数回顾

最近公司项目用到将一个表的查询出全表的父子集关系，并支持分页和模糊查询，复习一下相关技术，便于解决问题

 

语法：Create function function_name(参数列表)returns返回值类型

函数体 函数名，应合法的标识符，不应与系统关键字冲突。

一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。

参数部分，由参数名和参数类型组成。

返回值类类型.注意，一定要有return返回值语句。

创建函数：

#删除已存在的函数

mysql>DROP FUNCTION IF EXISTS SP_FUNC_DBDH_ONE

#定义分隔符，必须要有 ,本例为 && 可以为得数字符,例如: $$,%%,**

mysql>DELIMITER &&

mysql>

CREATE FUNCTION SP_FUNC_DBDH_ONE( PARA_VAR varchar(30))
    RETURNS VARCHAR(100)
     BEGIN
     DECLARE NAYIBU_FIR VARCHAR(30) DEFAULT 'THIS IS MY FIRST,';
    SET NAYIBU_FIR = concat(NAYIBU_FIR,PARA_VAR);     
    RETURN NAYIBU_FIR;
    END &&

#调用自定义函数,输出结果如下:

mysql> SELECT SP_FUNC_DBDH_ONE('PROGRAM');
+------------------------------+
| SP_FUNC_DBDH_ONE('PROGRAM') |
+------------------------------+
| 'THIS IS MY FIRST, PROGRAM        |
+------------------------------+
1 row in set (0.00 sec)

 2，无参的自定义函数

 delimiter &&

DROP FUNCTION IF EXISTS hello;

CREATE FUNCTION hello()  RETURNS VARCHAR(255)

BEGIN

          RETURN 'hello,this is my first function';

END  &&

delimiter;

调用函数：

select hello();

 

 

语法总结：

1 创建函数： create Function 函数名称 (输入参数变量，类型) RETURNS 类型 begin ... return 返回值 end (中间是具体实现)

2 变量定义关键字：declare 变量名+类型。

3流程控制: if(condition)then ...elseif(condition)  then ... else...  end if;

4 变量赋值: select ..into 变量名 或者是 set 变量名=value

5 date_format() 函数。

自定义函数中定义局部变量语法:

DECLARE var_name[,varname]...date_type [DEFAULT VALUE];

简单来说就是:

DECLARE 变量1[,变量2,... ]变量类型 [DEFAULT 默认值]

这些变量的作用范围是在BEGIN...END程序中,而且定义局部变量语句必须在BEGIN...END的第一行定义,这些局部变量的作用范围是在BEGIN...END程序中

ex:

DELIMITER //

CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)

RETURNS SMALLINT BEGIN DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;

SET a = x, b = y;

RETURN a+b;

END

//

 

 

为变量赋值语法:

SET parameter_name = value[,parameter_name = value...]

SELECT INTO parameter_name

ex:

...在某个UDF中...
DECLARE x int;
SELECT COUNT(id) FROM tdb_name INTO x;
RETURN x;
END//

用户变量定义语法:(可以理解成全局变量)

SET @param_name = value

SET @allParam = 100;
SELECT @allParam;

上述定义并显示@allParam用户变量,其作用域只为当前用户的客户端有效

自定义函数中流程控制语句语法:

存储过程和函数中可以使用流程控制来控制语句的执行。

MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

每个流程中可能包含一个单独语句，或者是使用BEGIN...END构造的复合语句，构造可以被嵌套

 

1．IF语句

IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：

IF search_condition THEN statement_list 
[ELSEIF search_condition THEN statement_list] ... 
[ELSE statement_list] 
END IF

其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。

注意：MYSQL还有一个IF()函数，他不同于这里描述的IF语句

下面是一个IF语句的示例。代码如下：

IF age>20 THEN SET @count1=@count1+1;  
ELSEIF age=20 THEN SET @count2=@count2+1;  
ELSE SET @count3=@count3+1;  
END IF;

该示例根据age与20的大小关系来执行不同的SET语句。

如果age值大于20，那么将count1的值加1；如果age值等于20，那么将count2的值加1；

其他情况将count3的值加1。IF语句都需要使用END IF来结束。

 

2．CASE语句

CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下：

CASE case_value 
WHEN when_value THEN statement_list 
[WHEN when_value THEN statement_list] ... 
[ELSE statement_list] 
END CASE

其中，case_value参数表示条件判断的变量；

when_value参数表示变量的取值；

statement_list参数表示不同when_value值的执行语句。

CASE语句还有另一种形式。该形式的语法如下：

CASE 
WHEN search_condition THEN statement_list 
[WHEN search_condition THEN statement_list] ... 
[ELSE statement_list] 
END CASE

其中，search_condition参数表示条件判断语句；

statement_list参数表示不同条件的执行语句。

注意：这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句

并且用END CASE替代END来终止！！

3．LOOP语句

LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。

但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。

LOOP语句的语法的基本形式如下：

[begin_label:] LOOP 
statement_list 
END LOOP [end_label]

其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；

statement_list参数表示需要循环执行的语句。

下面是一个LOOP语句的示例。代码如下：

add_num: LOOP  
SET @count=@count+1;  
END LOOP add_num ;

该示例循环执行count加1的操作。因为没有跳出循环的语句，这个循环成了一个死循环。

LOOP循环都以END LOOP结束。

4．LEAVE语句

LEAVE语句主要用于跳出循环控制。其语法形式如下：

LEAVE label

其中，label参数表示循环的标志。

下面是一个LEAVE语句的示例。代码如下：

add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
END LOOP add_num ;

该示例循环执行count加1的操作。当count的值等于100时，则LEAVE语句跳出循环。

 

5．ITERATE语句

ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。

ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。

ITERATE语句的基本语法形式如下：

ITERATE label

其中，label参数表示循环的标志。

下面是一个ITERATE语句的示例。代码如下：

复制代码
add_num: LOOP 
SET @count=@count+1; 
IF @count=100 THEN 
LEAVE add_num ; 
ELSE IF MOD(@count,3)=0 THEN 
ITERATE add_num; 
SELECT * FROM employee ; 
END LOOP add_num ;
复制代码

该示例循环执行count加1的操作，count值为100时结束循环。如果count的值能够整除3，则跳出本次循环，不再执行下面的SELECT语句。

说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。

LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。

使用这两个语句时一定要区分清楚。

 

6．REPEAT语句

REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：

[begin_label:] REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT [end_label]

其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。

下面是一个ITERATE语句的示例。代码如下：

REPEAT 
SET @count=@count+1; 
UNTIL @count=100 
END REPEAT ;

该示例循环执行count加1的操作，count值为100时结束循环。

REPEAT循环都用END REPEAT结束。

 

 

7．WHILE语句

WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。

WHILE语句是当满足条件时，执行循环内的语句。

WHILE语句的基本语法形式如下：

[begin_label:] WHILE search_condition DO 
statement_list 
END WHILE [end_label]

其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；

statement_list参数表示循环的执行语句。

下面是一个ITERATE语句的示例。代码如下：

WHILE @count<100 DO 
SET @count=@count+1; 
END WHILE ;

该示例循环执行count加1的操作，count值小于100时执行循环。

如果count值等于100了，则跳出循环。WHILE循环需要使用END WHILE来结束。

 ######################################################################

mysql数据库自带特殊函数：

1、求余函数MOD(X,Y)

MOD(X,Y)返回x被y除后的余数，MOD()对于带有小数部分的数值也起作用，他返回除法运算后的精确余数

2、四舍五入函数TRUNCATE(X,Y)

TRUNCATE(X,Y)返回被舍去至小数点后y位的数字x。若y的值为0，则结果不带有小数点或不带有小数部分。

若y设为负数，则截去（归零）x小数点左边起第y位开始后面所有低位的值。

3、求余函数HEX(X)和UNHEX（X）函数

有以下的代码可以演示HEX和UNHEX的功能：

SELECT HEX('this is a test str')

4、LENGTH(STR)返回值为字符串的字符个数（sqlserver使用len（）函数）
LENGTH函数在读取char类型数据时会删除字符右边的空格，而varchar不会

5、CHAR_LENGTH(STR)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符

SELECT CHAR_LENGTH('DATE')

 

6、合并字符串函数CONCAT_WS（x,s1,s2,......）

CONCAT_WS（x,s1,s2,......）,CONCAT_WS代表CONCAT with Separator，是CONCAT（）函数的特殊形式。

第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是

其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

7、替换字符串的函数INSERT(S1,X,LEN,S2)

INSERT(S1,X,LEN,S2)函数跟SQLSERVER里的STUFF()函数的功能是一样的，这里不作介绍了

8、填充字符串的函数LPAD(S1,LEN,S2)和RPAD(S1,LEN,S2)

LPAD(S1,LEN,S2)返回字符串s1，其左边由字符串s2填补到len字符串长度。假如s1的长度大于len，则返回值被缩短至len字符。

LPAD（）函数和RPAD（）函数功能跟SQLSERVER里的 REPLACE()相似，不过功能更加强大

9、RPAD(S1,LEN,S2)返回字符串s1，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到len字符长度

10、删除空格的函数TRIM(S)

TRIM(S)删除字符串s两侧的空格

MYSQL这里比SQLSERVER方便，SQLSERVER要删除两边的空格，需要使用： SELECT LTRIM(RTRIM(' hello '))

11、TRIM(S1 FROM S)删除字符串s中两端所有的子字符串s1。s1为可选项，在未指定情况下，删除空格

SELECT TRIM('xy' FROM 'xyxboxyokxxyxy')

12、重复生成字符串的函数REPEAT(S,N)

这个函数跟SQLSERVER里的REPLICATE()函数是一样的，参数个数都是一样的，这里不作介绍了

13、比较字符串大小的函数STRCMP(S1,S2)

STRCMP(S1,S2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1

14、匹配子串开始位置的函数

LOCATE(STR1,STR)、POSITION(STR1 IN STR)、INSTR(STR,STR1)3个函数作用相同，返回子字符串str1在字符串str中的开始位置

这三个函数跟SQLSERVER里的CHARINDEX()函数功能类似

15、返回指定位置的字符串的函数

ELT(N,字符串1,字符串2,字符串3,...,)，若N=1,则返回值为字符串1，若N=2，则返回值为字符串2，以此类推。

若N小于1或大于参数的数目，则返回值为NULL

16、返回指定字符串位置的函数FIELD(S,S1,S2,...)

FIELD(S,S1,S2,...)返回字符串s在列表s1，s2，......中第一次出现的位置，在找不到s的情况下，返回值为0。

如果s为NULL，则返回值为0，原因是NULL不能同任何值进行同等比较。

17、返回子串位置的函数FIND_IN_SET(S1,S2)

FIND_IN_SET(S1,S2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号

‘,’分开的字符串组成的列表。如果s1不在s2或s2为空字符串，则返回值为0。如果任意一个参数为NULL，则返回值为NULL。

这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。

18、选取字符串的函数MAKE_SET(X,S1,S2,...)

MAKE_SET(X,S1,S2,...)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1，s2对应比特01以此类推。

s1，s2...中的NULL值不会被添加到结果中。

日期和时间函数

1、获取当前日期的函数和获取当前时间的函数

CURDATE()、CURRENT_DATE()、CURRENT_TIMESTAMP()、LOCALTIME()、NOW()、SYSDATE()

以上函数都是返回当前日期和时间值，MYSQL的函数数量的确比SQLSERVER多很多，SQLSERVER获取当前时间和日期用的

函数是： SELECT GETDATE()

 

2、计算日期和时间的函数

MYSQL里计算日期和时间的函数比较多

增加日期：DATE_ADD(date,interval  expr type)，ADDDATE(date,interval  expr type)

减去日期：DATE_SUB(date,interval  expr type)，SUBDATE(date,interval  expr type)

增加时间：ADD_TIME(date,expr)

减去时间：SUBTIME(date,expr)

时间差：DATEDIFF()

最后一天：LAST_DAY(datetime)

日期和时间格式化：

DATE_FORMAT(date,format)

TIME_FORMAT(time,format)

返回日期时间字符串的显示格式：GET_FORMAT(val_type,format_type)

 系统信息函数

1、获取MYSQL版本号、连接数和数据库名的函数

VERSION()返回指示MYSQL服务器版本的字符串。这个字符串使用utf8字符集

SELECT VERSION()

2、CONNECTION_ID()返回MYSQL服务器当前连接的次数，每个连接都有各自唯一的ID

查看当前用户的连接数

SELECT CONNECTION_ID()

 

3、SHOW PROCESSLIST;

4、SHOW FULL PROCESSLIST;

processlist命令的输出结果显示了有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态

帮助识别出有问题的查询语句等。

 

如果是root帐号，能看到所有用户的当前连接。如果是其他普通帐号，则只能看到自己占用的连接。showprocesslist只能列出当前100条

如果想全部列出，可以使用SHOW FULL PROCESSLIST命令

各个列的含义

（1）id列，用户登录mysql时，系统分配的“connection_id”

（2）user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句

（3）host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户

（4）db列，显示这个进程目前连接的是哪个数据库

（5）command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）

（6）time列，显示这个状态持续的时间，单位是秒

（7）state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有状态的描述，state只是语句执行中的某一个状态。一个sql语句，

以查询为例

可能需要经过

copying to tmp table，

sorting result，

sending data

等状态才可以完成

（8）info列，显示这个sql语句，是判断问题语句的一个重要依据。

5、DATABASE()和SCHEMA()函数返回使用utf8字符集的默认（当前）数据库名

6、获取用户名的函数

USER()、CURRENT_USER()、CURRENT_USER、SYSTEM_USER()、SESSION_USER()

这几个函数返回当前被MYSQL服务器验证的用户名和主机名组合。这个值符合确定当前登录用户

存取权限的MYSQL帐户。一般情况下，这几个函数的返回值是相同的。

7、获取字符串的字符集和排序方式的函数

CHARSET(STR)返回字符串str自变量的字符集

8、COLLATION(str)返回字符串str的字符排列方式

9、获取最后一个自动生成的ID值的函数

LAST_INSERT_ID()自动返回最后一个INSERT或UPDATE为AUTO_INCREMENT列设置的第一个发生的值

在向数据表插入一条记录时，LAST_INSERT_ID()返回带有AUTO_INCREMENT约束的字段最新生成的值2；继续向表

中同时添加3条记录，这时候因为当使用一条INSERT语句插入多个行时，LAST_INSERT_ID只返回插入的第一行数据

时产生的值，在这里为第3条记录。之所以这样，是因为这使依靠其他服务器复制同样的INSERT语句变得简单

加密函数

1、加密函数PASSWORD(STR)

PASSWORD(STR)从原文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL

2、加密函数MD5(str)

MD5(str)为字符串算出一个MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL

3、加密函数ENCODE(str,pswd_str)

ENCODE(str,pswd_str)使用pswd_str作为密码，加密str。使用DECODE()解密结果，结果是一个和str长度相同的二进制字符串

 4、解密函数DECODE(crypt_str,pswd_str)

 DECODE(crypt_str,pswd_str)使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE()返回的字符串

1、格式化函数FORMAT(x,n)

FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。

若n为0，则返回结果函数不含小数部分

2、不同进制的数字转换的函数

CONV(N,from_base,to_base)函数进行不同进制数间的转换。

3、IP地址与数字相互转换的函数

INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。

地址可以是4或8比特地址

INET_NTOA(expr)给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示。

4、加锁函数和解锁函数

GET_LOCK(str,timeout)设法使用字符串str给定的名字得到一个锁，超时为timeout秒。

RELEASE_LOCK(str)解开被GET_LOCK()获取的，用字符串str所命名的锁。

IS_FREE_LOCK(str)检查名为str的锁是否可以使用

IS_USED_LOCK(str)检查名为str的锁是否正在被使用

5、重复执行指定操作的函数

BENCHMARK(count,expr)函数重复count次执行表达式expr。他可以用于计算MYSQL处理表达式的速度。

结果值通常为0（0只是表示处理过程很快，并不是没有花费时间）

另一个作用是他可以在MYSQL客户端内部报告语句执行的时间。

6、改变字符集的函数

CONVERT(...using...)带有USING的CONVERT()函数被用来在不同的字符集之间转化数据。

7、改变数据类型的函数

CAST：属于SQL2003标准，并且在mysql，sqlserver，oracle都已经被实现

CAST(x,AS type)和CONVERT(x,type)函数将一个类型的值转换为另一个类型的值，可转换的type有：

BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED

在SQLSERVER里也是使用这两个函数进行数据类型转换的~

mysql字符串函数集锦

存储过程与自定义函数的区别

posted on 2018-01-26 16:11  让代码飞  阅读(166)  评论(0)    收藏  举报

导航

一款免费在线思维导图工具推荐：https://www.processon.com/i/593e9a29e4b0898669edaf7f?full_name=python