MySQL基础(三)—函数、自定义函数

上一篇 MySQL基础(二)—操作表记录
这一篇是对函数的笔记，其中操作的数据库在上一篇文章中有代码，可以去看一下。

1.函数

1.1：函数的分类

• 字符函数
• 数值运算符与函数
• 比较运算符与函数
• 日期时间函数
• 信息函数
• 聚合函数
• 加密函数

1.2：具体使用

1.2.1：字符函数

CONCAT() 字符的连接

SELECT CONCAT(player.`playerName`,"    号码：",player.`playerNumber`,"    总冠军数量：",player.`championNumber`) AS playerData FROM player

CONCAT_WS() 使用指定的分隔符进行字符拼接(就是用哪个符号拼接) 这里最少需要三个参数 第一个参数是用那个符号拼接 后边的是连接数据

SELECT CONCAT_WS("+","李","小米"); #结果：李+小米
#把球员的名称和背号和总冠军数量拼接在一起  并且用|分割
SELECT CONCAT_WS("|",player.`playerName`,player.`playerNumber`,player.`championNumber`) AS playerData FROM player

FORMAT() 数字格式化 返回结果是一个字符(四舍五入)
第一个参数 是要格式化的数字
第二个是保留几位小数

SELECT FORMAT(1256.7462,2)  #结果：1256.75

LOWER() 转换成小写字母

SELECT LOWER("MySQL")       #结果：mysql

UPPER() 转化成大写字符

SELECT UPPER("MySQL")       #结果：MYSQL

LEFT() 获取左侧字符,就是前几位
第一个参数：从那个字符串获取
第二个参数：获取几位

SELECT LEFT("MySQL",2)      #结果：My
#然后把获取出来的转换成小写
SELECT LOWER(LEFT("MySQL",2))   #结果：my

RIGHT() 获取右侧字符,就是获取后几位

SELECT RIGHT("MySQL",10)    #结果：MySQL

LENGTH() 获取字符串长度

SELECT LENGTH("MySQL")      #结果：5

LTRIM() 删除前导空格

SELECT LENGTH("  MySQL  ")  #结果：9
SELECT LTRIM("  MySQL  ")   #结果：MySQL  
SELECT LENGTH(LTRIM("  MySQL  "))#结果：7

RTRIM() 删除后导空格

SELECT LENGTH("  MySQL  ")      #结果：9
SELECT RTRIM("  MySQL  ")       #结果：  MySQL
SELECT LENGTH(RTRIM("  MySQL  "))   #结果：7

TRIM() 删除前导和后导空客

SELECT TRIM("   MySQL   ")      #结果：MySQL   
SELECT LENGTH(TRIM("  MySQL  "))    #结果：5

删除前边或者后边的指定字符

SELECT TRIM(LEADING '#' FROM "##MySQL###")  #结果：MySQL###   
SELECT TRIM(TRAILING '#' FROM "##MySQL###") #结果：##MySQL   
SELECT TRIM(BOTH '#' FROM "##MySQL###")     #结果：MySQL  

SUBSTRING() 字符串截取
第一个参数：从第几位开始截取 下标从1开始(这里可以写负数，负数是从后往前截取)
第二个参数：截取多少位(如果不写这个，就截取到结尾)

SELECT SUBSTRING("MySQL",1,3)       #结果：MyS
SELECT SUBSTRING("MySQL",2)     #结果：ySQL   
SELECT SUBSTRING("MySQL",-2,1)      #结果：Q 
SELECT SUBSTRING("MySQL",-2)        #结果：QL   

[NOT] LIKE 匹配模式

SELECT "MySql" LIKE "%M%"       #结果：1 
UPDATE player SET player.playerName="%李%小米" WHERE player.playerName="李小米"
SELECT * FROM player WHERE player.`playerName` LIKE "%1%%" ESCAPE 1;  #1后边的第一个字符就不用看成通配符了

REPLACE() 字符串替换
第一个参数：准备操作的串
第二个参数：把谁替换
第三个参数：替换成啥

SELECT REPLACE("#My#SQ#L#","#","")      #结果：MySQL

1.2.2：数值运算符与函数

CEIL() 进一取整 向上取整

SELECT CEIL(1.32)       #结果：2

FLOOR() 舍一取整 向下取整

SELECT FLOOR(1.32)      #结果：1 

DIV 整数除法

SELECT 3 DIV 5          #结果：0 

MOD 取余数

SELECT 5 MOD 3          #结果：2 
SELECT 5.2 MOD 3        #结果：2.2 

POWER() 幂运算

2的3次方
SELECT POWER(2,3)       #结果：8

ROUND() 四舍五入
第一个参数：准备操作的数
第二个参数：保留几位 不写默认保留整数

SELECT ROUND(1.32)      #结果：1 
SELECT ROUND(1.51)      #结果：2
SELECT ROUND(1.5151,2)      #结果：1.52

TRUNCATE() #数字截取 把结果直接去掉 不四舍五入

SELECT TRUNCATE(125.872,2)      #结果125.87
SELECT TRUNCATE(125.872,1)      #结果 125.8
SELECT TRUNCATE(125.872,0)  #结果125
SELECT TRUNCATE(125.872,-1) #结果120
SELECT TRUNCATE(125.872,-2) #结果  100

1.2.3：比较运算符与函数

[NOT] BETWEEN … AND … [不]在范围之内

SELECT 2 BETWEEN 1 AND 3        #结果：1 
SELECT 2 BETWEEN 3 AND 5        #结果：0 

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

SELECT 2 IN (1,3,5,7)           #结果：0
SELECT 2 NOT IN (1,3,5,7)       #结果：1

IS [NOT] NULL [不]为空

那个球队的队名不是null
SELECT *FROM team WHERE team.`teamName` IS NOT NULL

1.2.4：日期时间函数

NOW() 当前的日期和时间

SELECT NOW()            #结果：2018-05-23 14:34:49

CURDATE() 当前日期

SELECT CURDATE()        #结果：2018-05-23

CURTIME() 当前时间

SELECT CURTIME()        #结果：14:34:49

DATE_ADD() 日期变化

加一年
SELECT DATE_ADD("2018-05-23" ,INTERVAL 365 DAY)  #结果：2019-05-23
减一年
SELECT DATE_ADD("2018-05-23" ,INTERVAL -365 DAY)  #结果：2017-05-23
加两个月
SELECT DATE_ADD("2018-05-23" ,INTERVAL 2 MONTH)  #结果：2018-07-23
加两个星期
SELECT DATE_ADD("2018-05-23" ,INTERVAL 2 WEEK)  #结果：2018-06-06

DATEDIFF() 日期差值

SELECT DATEDIFF("2018-05-23" ,"2018-06-06")  #结果：-14
SELECT DATEDIFF("2018-06-06","2018-05-23" )  #结果：14

DATE_FORMAT() 日期格式化 %表示小于10的时候 用09来表示

SELECT DATE_FORMAT("2018-06-06","%m/%d/%Y" )  #结果：06/06/2018

1.2.5：信息函数

CONNECTION_ID() 连接ID(也就是线程id)

SELECT CONNECTION_ID()

DATABASE() 当前数据库

SELECT DATABASE()

LAST_INSERT_ID() 最后插入记录

INSERT INTO player VALUES(NULL,"李小米","后卫",6,998,5); 
SELECT LAST_INSERT_ID()

USER()当前用户

SELECT USER()

VERSION() 当前数据库 版本信息

SELECT VERSION()

1.2.6：聚合函数

AVG() 平均值

SELECT AVG(team.`championNumber`) FROM team

COUNT() 计数

SELECT COUNT(team.`championNumber`) FROM team

MAX() 最大值

SELECT MAX(team.`championNumber`) FROM team

MIN() 最小值

SELECT MIN(team.`championNumber`) FROM team

SUM() 求和

SELECT SUM(team.`championNumber`) FROM team

1.2.7：加密函数

MD5() 这个很常用

SELECT MD5("123456")        #结果：e10adc3949ba59abbe56e057f20f883e

PASSWORD() 这个是在mysql中改变user的密码，会使用到，一般不用

SELECT PASSWORD("123456")   #结果：*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

2：自定义函数

用户自定义函数是一种对MySQL扩展的途径，用法和内置函数相同。如果小伙伴们会写代码就知道函数的结构了。一个函数可能没参数和返回值，到那时一定有函数体。那么问题来了，在sql中的函数体有啥要求呢？

2.1：函数体的要求

• 1、函数体可以由合法的SQL语句构成
• 2、函数体可以是简单的SELECT或者INSERT语句；
• 3、函数体如果为复合结构则使用BGGIN…END语句；
• 4、复合结构可以包含声明、循环、控制结构。

2.2：创建自定义函数

2.2.1：创建无参函数

#创建无参函数
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日 %H点:%i分:%s秒");
#       方法名          返回类型      返回值  
CREATE FUNCTION   f1()  RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H点:%i分:%s秒');
#执行自定义函数
SELECT f1();

2.2.2：创建有参函数

#创建有参函数
CREATE FUNCTION   f2(num1 INT ,num2 INT)  RETURNS FLOAT(10,2) RETURN (num1+num2)/2;
#执行自定义函数
SELECT f2(5,4);

2.2.3：创建复杂函数

#创建具有复合结构函数体的自定义函数
#往球员表中插入一条记录，返回插入记录的id
#参数   球员名称，球员位置，号码，球队id,总冠军数量

#这里在sql中默认以分号结束当前语句，但是在字段定义函数中要使用到分号，所以把分号改成;;或者其他的来结束当前语句
DELIMITER ;;  

CREATE FUNCTION   addPlayer(playerName VARCHAR(20) ,playerPosition VARCHAR(20) ,playerNumber INT UNSIGNED ,teamId INT UNSIGNED ,championNumber  INT) 
 RETURNS INT   UNSIGNED
BEGIN
    INSERT INTO player VALUES(NULL,playerName,playerPosition,playerNumber,teamId,championNumber);
    RETURN LAST_INSERT_ID();
END  ;; 

DELIMITER ;

SELECT addPlayer("我是小米","后卫",55,999,5);
SELECT addPlayer("我是小米1","后卫",55,999,5);
SELECT addPlayer("我是小米2","后卫",55,999,5);

2.2.4：删除自定义函数

DROP FUNCTION f1;
DROP FUNCTION f2;
DROP FUNCTION addPlayer;

点击查看本节sql文件
下一篇 MySQL基础(四)—存储过程和存储引擎