MySQL函数介绍以及常用的一些使用方式

最新推荐文章于 2024-08-01 20:37:32 发布
最新推荐文章于 2024-08-01 20:37:32 发布
阅读量533 收藏 3
点赞数 1
分类专栏： 数据库 文章标签： mysql函数 mysql递归函数 mysql常用指令 mysql基础
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tangcv/article/details/80729022
版权

MySQL 有很多内置的函数，以下列出了这些函数的说明。

表来自：http://www.runoob.com/mysql/mysql-functions.html

delete from 表名 where 删除条件;

INSERT INTO 表名 ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

UPDATE 表名 SET title='学习' WHERE 条件;

模糊查询 SELECT * from 表名 WHERE author LIKE '%COM%';

拼接 CONCAT(name,'(',id,')') name_id,
截取 substring(code,1,3)

FIND_IN_SET()

时间戳转换 DATE_FORMAT(time,'%Y-%m-%d') time

CASE
  WHEN status = 1 THEN '提交'
  WHEN status = 2 THEN '发起' 
 ELSE
  '未知'
 end status 

除去重复的数据 distinct

排序 order by id asc是表示升序，desc表示降序。

 

批量替换表中某字段的的部分内容

update 表名 set  字段名 = replace(字段名, '被替换值', '替换成')

二个数值的百分比

SELECT 
concat(left (数值1/ 数值2 *100,5),'%') as "投诉率"
FROM DUAL

事物 

【BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认】

【SET AUTOCOMMIT=0 禁止自动提交 SET AUTOCOMMIT=1 开启自动提交】

begin;
INSERT INTO aa (name) VALUES (333);
INSERT INTO aa (name) VALUES (444);
INSERT INTO aa (name) VALUES (555);
commit;

group_concat()函数

group_concat() SELECT GROUP_CONCAT(name) FROM aa GROUP BY name简单通俗的理解就是：分组以后，把想要显示的列通过gruop_concat()函数拼接在一起。

 

查询数据库信息

//1
SELECT
a.table_name 表名,
a.table_comment 表说明,
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM
information_schema. TABLES a
LEFT JOIN information_schema. COLUMNS b ON a.table_name = b.TABLE_NAME
WHERE
a.table_schema = 'hzsf_donation' //库名称
AND a.table_name ='hd_donation_record' //库中得表名称
ORDER BY
a.table_name

//2
SELECT
	b.table_comment,
	a.column_name,
	a.is_nullable,
	a.data_type,
	a.column_comment,
	a.column_key,
	a.extra
FROM
	information_schema. COLUMNS a
LEFT JOIN information_schema. TABLES b ON b.table_name = a.TABLE_NAME
WHERE
	a.table_name = 'hd_donation_record'
AND a.table_schema = (SELECT DATABASE())
ORDER BY
	a.ordinal_position

 

MySQL 字符串函数

函数描述实例
ASCII(s)返回字符串 s 的第一个字符的 ASCII 码。

返回 CustomerName 字段第一个字母的 ASCII 码：

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;
CHAR_LENGTH(s)返回字符串 s 的字符数

返回字符串 RUNOOB 的字符数

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CHARACTER_LENGTH(s)返回字符串 s 的字符数

返回字符串 RUNOOB 的字符数

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2...sn)字符串 s1,s2 等多个字符串合并为一个字符串

合并多个字符串

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn)同 CONCAT(s1,s2,...) 函数，但是每个字符串直接要加上 x，x 可以是分隔符

合并多个字符串，并添加分隔符：

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2...)返回第一个字符串 s 在字符串列表(s1,s2...)中的位置

返回字符串 c 在列表值中的位置：

SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置

返回字符串 c 在指定字符串中的位置：

SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n)函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。

格式化数字 "#,###.##" 形式：

SELECT FORMAT(250500.5634, 2);     -- 输出 250,500.56
INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

从字符串第一个位置开始的 6 个字符替换为 runoob：

SELECT INSERT("google.com", 1, 6, "runnob");  -- 输出：runoob.com
LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置

获取 b 在字符串 abc 中的位置：

SELECT LOCATE('st','myteststring');  -- 5
LCASE(s)将字符串 s 的所有字母变成小写字母

字符串 RUNOOB 转换为小写：

SELECT LOWER('RUNOOB') -- runoob
LEFT(s,n)返回字符串 s 的前 n 个字符

返回字符串 runoob 中的前两个字符：

SELECT LEFT('runoob',2) -- ru
LEFT(s,n)返回字符串 s 的前 n 个字符

返回字符串 abcde 的前两个字符：

SELECT LEFT('abcde',2) -- ab
LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置

返回字符串 abc 中 b 的位置：

SELECT LOCATE('b', 'abc') -- 2
LOWER(s)将字符串 s 的所有字母变成小写字母

字符串 RUNOOB 转换为小写：

SELECT LOWER('RUNOOB') -- runoob
LPAD(s1,len,s2)在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len

将字符串 xx 填充到 abc 字符串的开始处：

SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s)去掉字符串 s 开始处的空格

去掉字符串 RUNOOB开始处的空格：

SELECT LTRIM("    RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len)从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,len)

从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：

SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置

返回字符串 abc 中 b 的位置：

SELECT POSITION('b' in 'abc') -- 2
REPEAT(s,n)将字符串 s 重复 n 次

将字符串 runoob 重复三次：

SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
REPLACE(s,s1,s2)将字符串 s2 替代字符串 s 中的字符串 s1

将字符串 abc 中的字符 a 替换为字符 x：

SELECT REPLACE('abc','a','x') --xbc
REVERSE(s)将字符串s的顺序反过来

将字符串 abc 的顺序反过来：

SELECT REVERSE('abc') -- cba
RIGHT(s,n)返回字符串 s 的后 n 个字符

返回字符串 runoob 的后两个字符：

SELECT RIGHT('runoob',2) -- ob
RPAD(s1,len,s2)在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len

将字符串 xx 填充到 abc 字符串的结尾处：

SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s)去掉字符串 s 结尾处的空格

去掉字符串 RUNOOB 的末尾空格：

SELECT RTRIM("RUNOOB     ") AS RightTrimmedString;   -- RUNOOB
SPACE(n)返回 n 个空格

返回 10 个空格：

SELECT SPACE(10);
STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

比较字符串：

SELECT STRCMP("runoob", "runoob");  -- 0
SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串

从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串

从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING_INDEX(s, delimiter, number)返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。		 
SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1)    -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c
TRIM(s)去掉字符串 s 开始和结尾处的空格

去掉字符串 RUNOOB 的首尾空格：

SELECT TRIM('    RUNOOB    ') AS TrimmedString;
UCASE(s)将字符串转换为大写

将字符串 runoob 转换为大写：

SELECT UCASE("runoob"); -- RUNOOB
UPPER(s)将字符串转换为大写

将字符串 runoob 转换为大写：

SELECT UPPER("runoob"); -- RUNOOB

MySQL 数字函数

函数名描述实例
ABS(x)返回 x 的绝对值　　

返回 -1 的绝对值：

SELECT ABS(-1) -- 返回1
ACOS(x)求 x 的反余弦值(参数是弧度) 
SELECT ACOS(0.25);
ASIN(x)求反正弦值(参数是弧度) 
SELECT ASIN(0.25);
ATAN(x)求反正切值(参数是弧度) 
SELECT ATAN(2.5);
ATAN2(n, m)求反正切值(参数是弧度) 
SELECT ATAN2(-0.8, 2);
AVG(expression)返回一个表达式的平均值，expression 是一个字段

返回 Products 表中Price 字段的平均值：

SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)返回大于或等于 x 的最小整数　 
SELECT CEIL(1.5) -- 返回2
CEILING(x)返回大于或等于 x 的最小整数　 
SELECT CEIL(1.5) -- 返回2
COS(x)求余弦值(参数是弧度) 
SELECT COS(2);
COT(x)求余切值(参数是弧度) 
SELECT COT(6);
COUNT(expression)返回查询的记录总数，expression 参数是一个字段或者 * 号

返回 Products 表中 products 字段总共有多少条记录：

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)将弧度转换为角度　　 
SELECT DEGREES(3.1415926535898) -- 180
n DIV m整除，n 为被除数，m 为除数

计算 10 除于 5：

SELECT 10 DIV 5;  -- 2
EXP(x)返回 e 的 x 次方　　

计算 e 的三次方：

SELECT EXP(3) -- 20.085536923188
FLOOR(x)返回小于或等于 x 的最大整数　　

小于或等于 1.5 的整数：

SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)返回列表中的最大值

返回以下数字列表中的最大值：

SELECT GREATEST(3, 12, 34, 8, 25); -- 34

返回以下字符串列表中的最大值：

SELECT GREATEST("Google", "Runoob", "Apple");   -- Runoob
LEAST(expr1, expr2, expr3, ...)返回列表中的最小值

返回以下数字列表中的最小值：

SELECT LEAST(3, 12, 34, 8, 25); -- 3

返回以下字符串列表中的最小值：

SELECT LEAST("Google", "Runoob", "Apple");   -- Apple
LN返回数字的自然对数

返回 2 的自然对数：

SELECT LN(2);  -- 0.6931471805599453
LOG(x)返回自然对数(以 e 为底的对数)　　 
SELECT LOG(20.085536923188) -- 3
LOG10(x)返回以 10 为底的对数　　 
SELECT LOG10(100) -- 2
LOG2(x)返回以 2 为底的对数

返回以 2 为底 6 的对数：

SELECT LOG2(6);  -- 2.584962500721156
MAX(expression)返回字段 expression 中的最大值

返回数据表 Products 中字段 Price 的最大值：

SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)返回字段 expression 中的最小值

返回数据表 Products 中字段 Price 的最小值：

SELECT MIN(Price) AS LargestPrice FROM Products;
MOD(x,y)返回 x 除以 y 以后的余数　

5 除于 2 的余数：

SELECT MOD(5,2) -- 1
PI()返回圆周率(3.141593）　　 
SELECT PI() --3.141593
POW(x,y)返回 x 的 y 次方　

2 的 3 次方：

SELECT POW(2,3) -- 8
POWER(x,y)返回 x 的 y 次方　

2 的 3 次方：

SELECT POWER(2,3) -- 8
RADIANS(x)将角度转换为弧度　　

180 度转换为弧度：

SELECT RADIANS(180) -- 3.1415926535898
RAND()返回 0 到 1 的随机数　　 
SELECT RAND() --0.93099315644334
ROUND(x)返回离 x 最近的整数 
SELECT ROUND(1.23456) --1
SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1　 
SELECT SIGN(-10) -- (-1)
SIN(x)求正弦值(参数是弧度)　　 
SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x)返回x的平方根　　

25 的平方根：

SELECT SQRT(25) -- 5
SUM(expression)返回指定字段的总和

计算 OrderDetails 表中字段 Quantity 的总和：

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)求正切值(参数是弧度) 
SELECT TAN(1.75);  -- -5.52037992250933
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） 
SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL 日期函数

函数名描述实例
ADDDATE(d,n)计算起始日期 d 加上 n 天的日期 
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
ADDTIME(t,n)时间 t 加上 n 秒的时间 
SELECT ADDTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:16 (秒)
CURDATE()返回当前日期 
SELECT CURDATE();
-> 2018-09-19
CURRENT_DATE()返回当前日期 
SELECT CURRENT_DATE();
-> 2018-09-19
CURRENT_TIME返回当前时间 
SELECT CURRENT_TIME();
-> 19:59:02
CURRENT_TIMESTAMP()返回当前日期和时间 
SELECT CURRENT_TIMESTAMP()
-> 2018-09-19 20:57:43
CURTIME()返回当前时间 
SELECT CURTIME();
-> 19:59:02
DATE()从日期或日期时间表达式中提取日期值 
SELECT DATE("2017-06-15");    
-> 2017-06-15
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数 
SELECT DATEDIFF('2001-01-01','2001-02-02')
-> -32
DATE_ADD(d，INTERVAL expr type)计算起始日期 d 加上一个时间段后的日期 
SELECT ADDDATE('2011-11-11 11:11:11',1)
-> 2011-11-12 11:11:11    (默认是天)

SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE)
-> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)
DATE_FORMAT(d,f)按表达式 f的要求显示日期 d 
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type)函数从日期减去指定的时间间隔。

Orders 表中 OrderDate 字段减去 2 天：

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
DAY(d)返回日期值 d 的日期部分 
SELECT DAY("2017-06-15");  
-> 15
DAYNAME(d)返回日期 d 是星期几，如 Monday,Tuesday 
SELECT DAYNAME('2011-11-11 11:11:11')
->Friday
DAYOFMONTH(d)计算日期 d 是本月的第几天 
SELECT DAYOFMONTH('2011-11-11 11:11:11')
->11
DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推 
SELECT DAYOFWEEK('2011-11-11 11:11:11')
->6
DAYOFYEAR(d)计算日期 d 是本年的第几天 
SELECT DAYOFYEAR('2011-11-11 11:11:11')
->315
EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值。 
type可取值为： 
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
 
SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') 
-> 11
FROM_DAYS(n)计算从 0000 年 1 月 1 日开始 n 天后的日期 
SELECT FROM_DAYS(1111)
-> 0003-01-16
HOUR(t)返回 t 中的小时值 
SELECT HOUR('1:2:3')
-> 1
LAST_DAY(d)返回给给定日期的那一月份的最后一天 
SELECT LAST_DAY("2017-06-20");
-> 2017-06-30
LOCALTIME()返回当前日期和时间 
SELECT LOCALTIME()
-> 2018-09-19 20:57:43
LOCALTIMESTAMP()返回当前日期和时间 
SELECT LOCALTIMESTAMP()
-> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year)基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 
SELECT MAKEDATE(2017, 3);
-> 2017-01-03
MAKETIME(hour, minute, second)组合时间，参数分别为小时、分钟、秒 
SELECT MAKETIME(11, 35, 4);
-> 11:35:04
MICROSECOND(date)返回日期参数所对应的毫秒数 
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23
MINUTE(t)返回 t 中的分钟值 
SELECT MINUTE('1:2:3')
-> 2
MONTHNAME(d)返回日期当中的月份名称，如 Janyary 
SELECT MONTHNAME('2011-11-11 11:11:11')
-> November
MONTH(d)返回日期d中的月份值，1 到 12 
SELECT MONTH('2011-11-11 11:11:11')
->11
NOW()返回当前日期和时间 
SELECT NOW()
-> 2018-09-19 20:57:43
PERIOD_ADD(period, number)为 年-月 组合日期添加一个时段 
SELECT PERIOD_ADD(201703, 5);   
-> 201708
PERIOD_DIFF(period1, period2)返回两个时段之间的月份差值 
SELECT PERIOD_DIFF(201710, 201703);
-> 7
QUARTER(d)返回日期d是第几季节，返回 1 到 4 
SELECT QUARTER('2011-11-11 11:11:11')
-> 4
SECOND(t)返回 t 中的秒钟值 
SELECT SECOND('1:2:3')
-> 3
SEC_TO_TIME(s)将以秒为单位的时间 s 转换为时分秒的格式 
SELECT SEC_TO_TIME(4320)
-> 01:12:00
STR_TO_DATE(string, format_mask)将字符串转变为日期 
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10
SUBDATE(d,n)日期 d 减去 n 天后的日期 
SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n)时间 t 减去 n 秒的时间 
SELECT SUBTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)
SYSDATE()返回当前日期和时间 
SELECT SYSDATE()
-> 2018-09-19 20:57:43
TIME(expression)提取传入表达式的时间部分 
SELECT TIME("19:30:10");
-> 19:30:10
TIME_FORMAT(t,f)按表达式 f 的要求显示时间 t 
SELECT TIME_FORMAT('11:11:11','%r')
11:11:11 AM
TIME_TO_SEC(t)将时间 t 转换为秒 
SELECT TIME_TO_SEC('1:12:00')
-> 4320
TIMEDIFF(time1, time2)计算时间差值 
SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
TIMESTAMP(expression, interval)单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 
SELECT TIMESTAMP("2017-07-23",  "13:10:11");
-> 2017-07-23 13:10:11
TO_DAYS(d)计算日期 d 距离 0000 年 1 月 1 日的天数 
SELECT TO_DAYS('0001-01-01 01:01:01')
-> 366
WEEK(d)计算日期 d 是本年的第几个星期，范围是 0 到 53 
SELECT WEEK('2011-11-11 11:11:11')
-> 45
WEEKDAY(d)日期 d 是星期几，0 表示星期一，1 表示星期二 
SELECT WEEKDAY("2017-06-15");
-> 3
WEEKOFYEAR(d)计算日期 d 是本年的第几个星期，范围是 0 到 53 
SELECT WEEKOFYEAR('2011-11-11 11:11:11')
-> 45
YEAR(d)返回年份 
SELECT YEAR("2017-06-15");
-> 2017
YEARWEEK(date, mode)返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 
SELECT YEARWEEK("2017-06-15");
-> 201724

MySQL 高级函数

函数名描述实例
BIN(x)返回 x 的二进制编码

15 的 2 进制编码:

SELECT BIN(15); -- 1111
BINARY(s)将字符串 s 转换为二进制字符串 
SELECT BINARY "RUNOOB";
-> RUNOOB
CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END
CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 
SELECT CASE 
　　WHEN 1 > 0
　　THEN '1 > 0'
　　WHEN 2 > 0
　　THEN '2 > 0'
　　ELSE '3 > 0'
　　END
->1 > 0
CAST(x AS type)转换数据类型

字符串日期转换为日期：

SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29
COALESCE(expr1, expr2, ...., expr_n)返回参数中的第一个非空表达式（从左向右） 
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');
-> runoob.com
CONNECTION_ID()返回服务器的连接数 
SELECT CONNECTION_ID();
-> 4292835
CONV(x,f1,f2)返回 f1 进制数变成 f2 进制数 
SELECT CONV(15, 10, 2);
-> 1111
CONVERT(s USING cs)函数将字符串 s 的字符集变成 cs 
SELECT CHARSET('ABC')
->utf-8    

SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbk
CURRENT_USER()返回当前用户 
SELECT CURRENT_USER();
-> guest@%
DATABASE()返回当前数据库名 
SELECT DATABASE();   
-> runoob
IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 
SELECT IF(1 > 0,'正确','错误')    
->正确
IFNULL(v1,v2)如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 
SELECT IFNULL(null,'Hello Word')
->Hello Word
ISNULL(expression)判断表达式是否为 NULL 
SELECT ISNULL(NULL);
->1
LAST_INSERT_ID()返回最近生成的 AUTO_INCREMENT 值 
SELECT LAST_INSERT_ID();
->6
NULLIF(expr1, expr2)比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 
SELECT NULLIF(25, 25);
->
SESSION_USER()返回当前用户 
SELECT SESSION_USER();
-> guest@%
SYSTEM_USER()返回当前用户 
SELECT SYSTEM_USER();
-> guest@%
USER()返回当前用户 
SELECT USER();
-> guest@%
VERSION()返回数据库的版本号 
SELECT VERSION()
-> 5.6.34

自定义函数

对于任意一个函数，其都包含如下要素：

  • 函数名；
  • 参数类别（可以为空）；
  • 返回值；
  • 函数体（作用域）。

根据上面这些函数要素，我们就来尝试创建自定义函数。

创建函数

-- 基本语法
create function 函数名([参数列表]) returns 数据类型
begin
    -- 函数体
    -- 返回值，类型为 returns 指定的数据类型
end
  •  

如果我们定义的函数的函数体内仅含有返回值，则可以省略begin和end。此外，自定义函数和系统函数的调用方式相同。执行如下语句，进行测试：

-- 自定义函数
create function showLove() returns int
return 521;
-- 调用自定义函数
select showLove();
  •  

8

查看函数

查看函数，基本语法为：

  • show function status + [like 'pattern'];

9

如上图所示，我们可以看到showLove函数是属于test数据库的，这引出了函数的一个性质，即函数是属于具体数据库的，在一个数据库定义的函数不能在其定义的数据库外使用，但是可以查看。

查看函数的创建语句，基本语法为：

  • show create function + 函数名;

10

修改函数 & 删除函数

函数只能先删除后新增，不能修改。删除函数的基本语法为：

  • drop function + 函数名;

执行如下语句，进行测试：

-- 删除函数
drop function showLove;
-- 查看函数
show function status like 'showLove'\G;
  • 11

函数参数

对于函数的参数，一共有两种，分别为形参和实参，其中，形参可以理解为定义函数时使用的参数，且形参必须指定数据类型；实参可以理解为在调用函数时传入的值或变量。因此，函数定义的具体形式应该为：

  • function 函数名(形参名字 形参类型) returns 返回数据类型

下面，我们定义一个函数，完成一个简单的需求，即求1到指定数值的和。代码如下：

delimiter $$
create function addAll(num int) returns int
begin
	-- 定义条件变量
	set @i = 1;
	set @res = 0;    -- 保存求和结果
	-- 循环求和
	while @i <= num do
		-- 任何变量想要修改值都必须使用 set 关键字，且 MySQL 中没有 += 或者 ++ 运算符
		set @res = @res + @i;
		-- 修改循环变量
		set @i = @i + 1;
	end while;
	-- 返回求和结果
	return @res;
end
$$
delimiter ;
  •  

如上图所示，函数已经定义成功。接下来，执行如下语句，进行测试：

-- 调用函数求和
select addAll(100);

-- 查询自定义变量 @res 和 @i
select @res, @i;
  • 13

如上图所示，求和函数addAll已经正确执行。此外，我们发现在函数内部使用@符号定义的变量@res和@i在函数外部也是可以查看使用的，这说明：使用@符号定义的变量为全局变量。

变量作用域

在 MySQL 中，变量的作用域有两种，分别为全局和局部，其中，全局变量可以在任何地方使用；局部变量只能在函数内部使用。

  • 全局变量：使用set关键字定义，用@符号标识；
  • 局部变量：使用declare关键字声明，且所用的局部变量必须在函数体开始之前进行声明。

接下来，我们利用局部变量定义一个函数，完成一个简单的需求，即求1到指定数值的和，要求10的倍数不加。代码如下：

delimiter $$
create function addAll2(num int) returns int
begin
	-- 声明变量，包含循环变量和结果变量
	declare i int default 1;    -- 定义局部变量可以含有属性
	declare res int default 0;
	-- 循环求和
	mywhile:while i <= num do
		-- 条件判断
		if i % 10 = 0 then
			-- 修改循环变量
			set i = i + 1;
			-- 重新循环
			iterate mywhile;
		end if;
		-- 修改结果变量
		set res = res + i;
		-- 修改循环变量
		set i = i + 1;
	end while;
	-- 返回求和结果
	return res;
end
$$
delimiter ;
  • 14

如上图所示，函数已经定义成功。接下来，执行如下语句，进行测试：

-- 调用函数求和
select addAll(100), addAll2(100);
  • 15

如上图所示，函数已经正确执行。

温馨提示：符号[]括起来的内容，表示可选项；符号+，则表示连接的意思。

delimiter：

delimiter就是告诉mysql解释器，该段命令是否已经结束了，是否可以执行，默认情况下，delimiter是分号，遇到分号就执行，后面的双美元符号 就是告诉mysql，遇到双美元符号再执行。

 

递归查询

 

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for t_areainfo
-- ----------------------------
DROP TABLE IF EXISTS `t_areainfo`;
CREATE TABLE `t_areainfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `level` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parentId` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of t_areainfo
-- ----------------------------
INSERT INTO `t_areainfo` VALUES ('1', '0', '中国', '0', '0');
INSERT INTO `t_areainfo` VALUES ('2', '0', '华北区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('3', '0', '华南区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');
INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('6', '0', '丰台区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('7', '0', '朝阳区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('46', '0', 'xx省1', '1', '0');
INSERT INTO `t_areainfo` VALUES ('47', '0', 'xx省2', '1', '0');
INSERT INTO `t_areainfo` VALUES ('48', '0', 'xx省3', '1', '0');
INSERT INTO `t_areainfo` VALUES ('49', '0', 'xx省4', '1', '0');
INSERT INTO `t_areainfo` VALUES ('50', '0', 'xx省5', '1', '0');
INSERT INTO `t_areainfo` VALUES ('51', '0', 'xx省6', '1', '0');
INSERT INTO `t_areainfo` VALUES ('52', '0', 'xx省7', '1', '0');
INSERT INTO `t_areainfo` VALUES ('53', '0', 'xx省8', '1', '0');
INSERT INTO `t_areainfo` VALUES ('54', '0', 'xx省9', '1', '0');
INSERT INTO `t_areainfo` VALUES ('55', '0', 'xx省10', '1', '0');
INSERT INTO `t_areainfo` VALUES ('56', '0', 'xx省11', '1', '0');
INSERT INTO `t_areainfo` VALUES ('57', '0', 'xx省12', '1', '0');
INSERT INTO `t_areainfo` VALUES ('58', '0', 'xx省13', '1', '0');
INSERT INTO `t_areainfo` VALUES ('59', '0', 'xx省14', '1', '0');
INSERT INTO `t_areainfo` VALUES ('60', '0', 'xx省15', '1', '0');
INSERT INTO `t_areainfo` VALUES ('61', '0', 'xx省16', '1', '0');
INSERT INTO `t_areainfo` VALUES ('62', '0', 'xx省17', '1', '0');
INSERT INTO `t_areainfo` VALUES ('63', '0', 'xx省18', '1', '0');
INSERT INTO `t_areainfo` VALUES ('64', '0', 'xx省19', '1', '0');

3、向下递归：

利用find_in_set()函数和group_concat()函数实现递归查询：

 

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);

WHILE sTempChd IS NOT NULL DO
SET sTemp= CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(id) INTO sTempChd FROM t_areainfo WHERE FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
RETURN sTemp;
END;

 

4、调用方式：

SELECT queryChildrenAreaInfo(1);

查询id为"4"下面的所有节点

SELECT * FROM t_areainfo WHERE FIND_IN_SET(id,queryChildrenAreaInfo(4));

5、向上递归：

 

DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;
CREATE FUNCTION queryChildrenAreaInfo1(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);

SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
WHILE sTempChd <> 0 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END;

6、调用方式：

查询id为"7"的节点的所有上级节点：

SELECT * from t_areainfo where FIND_IN_SET(id,queryChildrenAreaInfo1(7));

 

 

 

网寻星
关注
专栏目录
MySQL常用数据库函数
游戈 的博客
09-13 972
一. 常用的函数分类 • 数学函数 • 聚合函数 • 字符串函数 • 日期时间函数 二. 常用的数学函数 1.rand() 返回0到1的随机数 mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.2819034413266028 | +--------------------+ 1 row in set (0.00 sec) mod(x,y) 返回x除以y以后的
MySQL函数运用
weixin_66202611的博客
07-04 1177
目录一，函数的定义 二，常用函数 三，字符串函数四， 数学函数五，判断函数六、合并(union) （1）日期函数案例一： 《获取当前时间效果图》 《日期转换效果图》 案例二： 效果图参考 案例1： 效果图参考 案例2： 效果图 效果图 (1).IF(expr,v1,v2) 如果expr是TRUE则返回v1，否则返回v2 -----(行列转换) 需求：每一个学生的语数外分数 判断：如果是当前分数就去当前分数，不是则用0代替
mysql 函数
mengzuchao的专栏
06-13 388
MySQL聚合函数MySQL聚合函数 - 提供最常用的MySQL聚合函数的简要概述。avg()函数 - 计算一组值或表达式的平均值。count()函数 - 计算表中的行数。instr()函数 - 返回子字符串在字符串中第一次出现的位置。sum()函数 - 计算一组值或表达式的总和。min()函数 - 在一组值中找到最小值。max()函数 - 在一组值中找到最大值。group_concat()函数 ...
MySQL常用函数大全（sql基础语法、示例）
最新发布
weixin_56023591的博客
08-01 787
基本语法示例：获取日期所在月份的最后一天：sql结果。
mysql里的函数用法
weixin_56863206的博客
08-11 318
关于mysql里常见函数的用法
如何使用MySQL——函数
u010243595的博客
10-03 776
信息化函数 ——提供关于数据库的信息及表和修改的数据之间的交互信息 1、DATABASE() ——提供关于当前连接数据库的名称       2、CURRENT_USER() ——查看当前连接的主机名和用户名       3、LAST_INSERT_ID() ——返回上次自动生成的值，并且赋给一个AUTO_INCREMENT特性定义的列 聚合函数 ——输出数据的日
MySQL 常用函数的使用
12-14
MySQL 是一个广泛使用的开源关系型数据库管理系统，它提供了丰富的函数来简化数据处理。在本文中，我们将探讨几个关键的函数类别，包括字符串函数、日期函数、数字函数以及高级函数，如CASE表达式和IF表达式。 1. *...
mysql截取函数常用方法使用说明
12-15
这里我们将详细介绍四个常见的MySQL截取函数：`LEFT()`、`RIGHT()`、`SUBSTRING()` 和 `SUBSTRING_INDEX()`。 1. **`LEFT()` 函数**： `LEFT(str, length)` 用于从字符串 `str` 的左侧开始截取 `length` 个字符。...
MySQL中一些常用函数列举
12-14
MySQL常用函数 1.字符函数： 名称 描述 CONCAT() 字符连接 CONCAT_WS() 使用指定的分隔符进行字符连接 FORMAT() 数字格式化 LOWER() 转换成小写字母 UPPER() 转换成大写字母 LEFT() 获取左侧字符 ...
Mysql常用函数大全(分类汇总讲解)
09-09
以上只是MySQL中部分常用函数的简要介绍，实际操作中还有许多其他函数，如日期时间的加减、日期范围判断等。熟练掌握这些函数能够极大地提升你的SQL查询效率和数据处理能力。在实际工作中，根据具体需求灵活运用这些...
mysql常用函数汇总.chm
08-14
Mysql的常用函数整体, 从网上收集的一些常用函数, 进行汇总整体成文档 MySQL控制流函数： CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真，则返回resultN，否则返回default CASE [test] WHEN...
mysql中一些函数的用法
ziggyPLAYguitar的博客
12-28 306
首先在mysql中创有这样一堆数据： AVG 用来找出平均值； group by 子句来计算多种记录集的平均值 between 子句： 之前要获得某一范围的数据要用>= , CONCAT 函数用于将字符串合并起来 ： 有两个name为NULL的合并时结果依旧为NULL，需要注意若有某一处
Mysql一些函数的用法
WangXiaoYuuuuu
10-29 113
记录工作中用到的一些mysql函数，待补充。。。 IF()函数 IF函数，相当于java中的三目表达式。 IF(expr1,expr2,expr3) 参数含义：如果expr1的值为true，则返回expr2的值，如果expr1的值为false， 则返回expr3的值。 这里举一个工作中用到的例子： SELECT DISTINCT u.* FROM zt_client u WHERE ...
mysql函数使用
myloveinnocence的博客
08-17 483
1.分组时，拼接一组字符串，相当于给字符串sum()  (group_concat()函数) 1.1. 实例演示： https://blog.csdn.net/qqahanson/article/details/26820851 CREATE TABLE `jxc_orderdetail` ( `detail_id` int(11) NOT NULL AUTO_INCREMENT, ...
mysql function
经营IT
07-13 657
写示例的意义在于熟悉，下次需要的使用的可以快速查询，已经包含大部分语法，个人觉得可以解决80%的类似问题 DELIMITER $$ DROP FUNCTION IF EXISTS qStoreIdByUser CREATE `qStoreIdByUser`(userId INTEGER) RETURNS VARCHAR(4000) CHARSET utf8 COLLATE utf8_
MySql Functions
czh4869623的专栏
09-02 285
1）创建函数 CREATE DEFINER=`root`@`localhost` FUNCTION `fun_studentunum`(classname nvarchar(200)) RETURNS int(32) BEGIN declare num int default 0; select count(1) into num from mystudent s inner...
mysql FUNCTION
atares0223的博客
12-02 511
DROP FUNCTION IF EXISTS fn_classes_teacher_num; CREATE FUNCTION fn_classes_teacher_num(id_in varchar(20)) RETURNS INTEGER READS SQL DATA  BEGIN RETURN   (SELECT num_of_teacher from classes where
MySQL 基础学习笔记2
weixin_42167594的博客
06-08 165
InnoDB和MyISAM的使用场合MyISAM储存引擎：由于不支持事物处理，也不支持外键，访问速度比较快，以访问为主优先选择。InnoDB储存引擎：在事务处理上有优势，有事物控制能力，可以保证事物的完整性，还可实现并发控制。这样的需求优选。默认引擎的的更改：在配置文件my.ini文件中可以修改。修改defaultstorage-engine=InnoDB也可以在创建表时修改:engine=MyI...
MySQL常用函数速查手册
"MySQL常用函数列表" MySQL是一个广泛使用的开源关系型数据库管理系统，它提供了丰富的内置函数，方便用户进行数据处理和操作。以下是一些在MySQL中常用的函数及其功能： 1. CHARSET(str): 这个函数用于获取字符串...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

网寻星

打赏老板，留下需求，为你而码

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值