上一篇 MySQL基礎(chǔ)(二)—操作表記錄
這一篇是對函數(shù)的筆記，其中操作的數(shù)據(jù)庫在上一篇文章中有代碼匈子，可以去看一下。
1.函數(shù)
1.1：函數(shù)的分類
- 字符函數(shù)
- 數(shù)值運(yùn)算符與函數(shù)
- 比較運(yùn)算符與函數(shù)
- 日期時(shí)間函數(shù)
- 信息函數(shù)
- 聚合函數(shù)
- 加密函數(shù)
1.2：具體使用
1.2.1：字符函數(shù)
CONCAT() 字符的連接
SELECT CONCAT(player.`playerName`," 號碼：",player.`playerNumber`," 總冠軍數(shù)量：",player.`championNumber`) AS playerData FROM player
CONCAT_WS() 使用指定的分隔符進(jìn)行字符拼接(就是用哪個(gè)符號拼接) 這里最少需要三個(gè)參數(shù) 第一個(gè)參數(shù)是用那個(gè)符號拼接 后邊的是連接數(shù)據(jù)
SELECT CONCAT_WS("+","李","小米"); #結(jié)果：李+小米
#把球員的名稱和背號和總冠軍數(shù)量拼接在一起 并且用|分割
SELECT CONCAT_WS("|",player.`playerName`,player.`playerNumber`,player.`championNumber`) AS playerData FROM player
FORMAT() 數(shù)字格式化 返回結(jié)果是一個(gè)字符(四舍五入)
第一個(gè)參數(shù) 是要格式化的數(shù)字
第二個(gè)是保留幾位小數(shù)
SELECT FORMAT(1256.7462,2) #結(jié)果：1256.75
LOWER() 轉(zhuǎn)換成小寫字母
SELECT LOWER("MySQL") #結(jié)果：mysql
UPPER() 轉(zhuǎn)化成大寫字符
SELECT UPPER("MySQL") #結(jié)果：MYSQL
LEFT() 獲取左側(cè)字符,就是前幾位
第一個(gè)參數(shù)：從那個(gè)字符串獲取
第二個(gè)參數(shù)：獲取幾位
SELECT LEFT("MySQL",2) #結(jié)果：My
#然后把獲取出來的轉(zhuǎn)換成小寫
SELECT LOWER(LEFT("MySQL",2)) #結(jié)果：my
RIGHT() 獲取右側(cè)字符,就是獲取后幾位
SELECT RIGHT("MySQL",10) #結(jié)果：MySQL
LENGTH() 獲取字符串長度
SELECT LENGTH("MySQL") #結(jié)果：5
LTRIM() 刪除前導(dǎo)空格
SELECT LENGTH(" MySQL ") #結(jié)果：9
SELECT LTRIM(" MySQL ") #結(jié)果：MySQL
SELECT LENGTH(LTRIM(" MySQL "))#結(jié)果：7
RTRIM() 刪除后導(dǎo)空格
SELECT LENGTH(" MySQL ") #結(jié)果：9
SELECT RTRIM(" MySQL ") #結(jié)果： MySQL
SELECT LENGTH(RTRIM(" MySQL ")) #結(jié)果：7
TRIM() 刪除前導(dǎo)和后導(dǎo)空客
SELECT TRIM(" MySQL ") #結(jié)果：MySQL
SELECT LENGTH(TRIM(" MySQL ")) #結(jié)果：5
刪除前邊或者后邊的指定字符
SELECT TRIM(LEADING '#' FROM "##MySQL###") #結(jié)果：MySQL###
SELECT TRIM(TRAILING '#' FROM "##MySQL###") #結(jié)果：##MySQL
SELECT TRIM(BOTH '#' FROM "##MySQL###") #結(jié)果：MySQL
SUBSTRING() 字符串截取
第一個(gè)參數(shù)：從第幾位開始截取 下標(biāo)從1開始(這里可以寫負(fù)數(shù)闯袒，負(fù)數(shù)是從后往前截取)
第二個(gè)參數(shù)：截取多少位(如果不寫這個(gè)虎敦，就截取到結(jié)尾)
SELECT SUBSTRING("MySQL",1,3) #結(jié)果：MyS
SELECT SUBSTRING("MySQL",2) #結(jié)果：ySQL
SELECT SUBSTRING("MySQL",-2,1) #結(jié)果：Q
SELECT SUBSTRING("MySQL",-2) #結(jié)果：QL
[NOT] LIKE 匹配模式
SELECT "MySql" LIKE "%M%" #結(jié)果：1
UPDATE player SET player.playerName="%李%小米" WHERE player.playerName="李小米"
SELECT * FROM player WHERE player.`playerName` LIKE "%1%%" ESCAPE 1; #1后邊的第一個(gè)字符就不用看成通配符了
REPLACE() 字符串替換
第一個(gè)參數(shù)：準(zhǔn)備操作的串
第二個(gè)參數(shù)：把誰替換
第三個(gè)參數(shù)：替換成啥
SELECT REPLACE("#My#SQ#L#","#","") #結(jié)果：MySQL
1.2.2：數(shù)值運(yùn)算符與函數(shù)
CEIL() 進(jìn)一取整 向上取整
SELECT CEIL(1.32) #結(jié)果：2
FLOOR() 舍一取整 向下取整
SELECT FLOOR(1.32) #結(jié)果：1
DIV 整數(shù)除法
SELECT 3 DIV 5 #結(jié)果：0
MOD 取余數(shù)
SELECT 5 MOD 3 #結(jié)果：2
SELECT 5.2 MOD 3 #結(jié)果：2.2
POWER() 冪運(yùn)算
2的3次方
SELECT POWER(2,3) #結(jié)果：8
ROUND() 四舍五入
第一個(gè)參數(shù)：準(zhǔn)備操作的數(shù)
第二個(gè)參數(shù)：保留幾位 不寫默認(rèn)保留整數(shù)
SELECT ROUND(1.32) #結(jié)果：1
SELECT ROUND(1.51) #結(jié)果：2
SELECT ROUND(1.5151,2) #結(jié)果：1.52
TRUNCATE() #數(shù)字截取 把結(jié)果直接去掉 不四舍五入
SELECT TRUNCATE(125.872,2) #結(jié)果125.87
SELECT TRUNCATE(125.872,1) #結(jié)果 125.8
SELECT TRUNCATE(125.872,0) #結(jié)果125
SELECT TRUNCATE(125.872,-1) #結(jié)果120
SELECT TRUNCATE(125.872,-2) #結(jié)果 100
1.2.3：比較運(yùn)算符與函數(shù)
[NOT] BETWEEN ... AND ... [不]在范圍之內(nèi)
SELECT 2 BETWEEN 1 AND 3 #結(jié)果：1
SELECT 2 BETWEEN 3 AND 5 #結(jié)果：0
[NOT]IN() [不]在列出值范圍內(nèi)
SELECT 2 IN (1,3,5,7) #結(jié)果：0
SELECT 2 NOT IN (1,3,5,7) #結(jié)果：1
IS [NOT] NULL [不]為空
那個(gè)球隊(duì)的隊(duì)名不是null
SELECT *FROM team WHERE team.`teamName` IS NOT NULL
1.2.4：日期時(shí)間函數(shù)
NOW() 當(dāng)前的日期和時(shí)間
SELECT NOW() #結(jié)果：2018-05-23 14:34:49
CURDATE() 當(dāng)前日期
SELECT CURDATE() #結(jié)果：2018-05-23
CURTIME() 當(dāng)前時(shí)間
SELECT CURTIME() #結(jié)果：14:34:49
DATE_ADD() 日期變化
加一年
SELECT DATE_ADD("2018-05-23" ,INTERVAL 365 DAY) #結(jié)果：2019-05-23
減一年
SELECT DATE_ADD("2018-05-23" ,INTERVAL -365 DAY) #結(jié)果：2017-05-23
加兩個(gè)月
SELECT DATE_ADD("2018-05-23" ,INTERVAL 2 MONTH) #結(jié)果：2018-07-23
加兩個(gè)星期
SELECT DATE_ADD("2018-05-23" ,INTERVAL 2 WEEK) #結(jié)果：2018-06-06
DATEDIFF() 日期差值
SELECT DATEDIFF("2018-05-23" ,"2018-06-06") #結(jié)果：-14
SELECT DATEDIFF("2018-06-06","2018-05-23" ) #結(jié)果：14
DATE_FORMAT() 日期格式化 %表示小于10的時(shí)候 用09來表示
SELECT DATE_FORMAT("2018-06-06","%m/%d/%Y" ) #結(jié)果：06/06/2018
1.2.5：信息函數(shù)
CONNECTION_ID() 連接ID(也就是線程id)
SELECT CONNECTION_ID()
DATABASE() 當(dāng)前數(shù)據(jù)庫
SELECT DATABASE()
LAST_INSERT_ID() 最后插入記錄
INSERT INTO player VALUES(NULL,"李小米","后衛(wèi)",6,998,5);
SELECT LAST_INSERT_ID()
USER()當(dāng)前用戶
SELECT USER()
VERSION() 當(dāng)前數(shù)據(jù)庫 版本信息
SELECT VERSION()
1.2.6：聚合函數(shù)
AVG() 平均值
SELECT AVG(team.`championNumber`) FROM team
COUNT() 計(jì)數(shù)
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：加密函數(shù)
MD5() 這個(gè)很常用
SELECT MD5("123456") #結(jié)果：e10adc3949ba59abbe56e057f20f883e
PASSWORD() 這個(gè)是在mysql中改變user的密碼，會使用到搁吓，一般不用
SELECT PASSWORD("123456") #結(jié)果：*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
2：自定義函數(shù)
用戶自定義函數(shù)是一種對MySQL擴(kuò)展的途徑原茅，用法和內(nèi)置函數(shù)相同吭历。如果小伙伴們會寫代碼就知道函數(shù)的結(jié)構(gòu)了堕仔。一個(gè)函數(shù)可能沒參數(shù)和返回值，到那時(shí)一定有函數(shù)體晌区。那么問題來了摩骨，在sql中的函數(shù)體有啥要求呢？
2.1：函數(shù)體的要求
- 1朗若、函數(shù)體可以由合法的SQL語句構(gòu)成
- 2恼五、函數(shù)體可以是簡單的SELECT或者INSERT語句；
- 3哭懈、函數(shù)體如果為復(fù)合結(jié)構(gòu)則使用BGGIN...END語句灾馒；
- 4、復(fù)合結(jié)構(gòu)可以包含聲明遣总、循環(huán)睬罗、控制結(jié)構(gòu)轨功。
2.2：創(chuàng)建自定義函數(shù)
2.2.1：創(chuàng)建無參函數(shù)
#創(chuàng)建無參函數(shù)
SELECT DATE_FORMAT(NOW(),"%Y年%m月%d日 %H點(diǎn):%i分:%s秒");
# 方法名 返回類型 返回值
CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H點(diǎn):%i分:%s秒');
#執(zhí)行自定義函數(shù)
SELECT f1();
2.2.2：創(chuàng)建有參函數(shù)
#創(chuàng)建有參函數(shù)
CREATE FUNCTION f2(num1 INT ,num2 INT) RETURNS FLOAT(10,2) RETURN (num1+num2)/2;
#執(zhí)行自定義函數(shù)
SELECT f2(5,4);
2.2.3：創(chuàng)建復(fù)雜函數(shù)
#創(chuàng)建具有復(fù)合結(jié)構(gòu)函數(shù)體的自定義函數(shù)
#往球員表中插入一條記錄，返回插入記錄的id
#參數(shù) 球員名稱容达，球員位置古涧，號碼，球隊(duì)id,總冠軍數(shù)量
#這里在sql中默認(rèn)以分號結(jié)束當(dāng)前語句花盐，但是在字段定義函數(shù)中要使用到分號羡滑，所以把分號改成;;或者其他的來結(jié)束當(dāng)前語句
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("我是小米","后衛(wèi)",55,999,5);
SELECT addPlayer("我是小米1","后衛(wèi)",55,999,5);
SELECT addPlayer("我是小米2","后衛(wèi)",55,999,5);
2.2.4：刪除自定義函數(shù)
DROP FUNCTION f1;
DROP FUNCTION f2;
DROP FUNCTION addPlayer;
