mysql自定义函数的分号_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;