MySQL高阶语法
/*
【内容大纲】
1.字符串类常用函数
2.数字类常用函数
3.日期时间类常用函数
4.加密类常用函数
*/
/*==============================字符串常用函数===============================*/
/**
SubString()：用于截取指定字符串的方法。该方法有三个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要截取的字符串的起始位置，起始值为 1 。
参数3：用于指定要截取的长度。
*/
SELECT SUBSTRING('bcdefg', 2,3);
SELECT outerTradeNo, SUBSTRING(outerTradeNo, 1,8) as appId from jifenpay.ConsumeRecord order by id desc limit 1;
/**
Left()：用于返回指定字符串中指定长度的左侧部分。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要返回的子字符串的长度。
*/
SELECT LEFT('abcderf',4);
/**
Right()：用于返回指定字符串中指定长度的右侧部分。该方法有两个参数：
参数1：用于指定要操作的字符串。
参数2：用于指定要返回的子字符串的长度。
*/
SELECT Right('abcderf',3);
/**
Length()：用于返回指定文本的值的长度。该方法有一个参数：
参数1：用于指定要操作的文本或字符串。
*/
SELECT LENGTH(' 1234 ');
/**
Lower()：用于返回指定英文字符串的小写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为小写形式的字符串。
*/
SELECT LOWER('abcDEFg');
SELECT LOWER('中文字符123');
/*
Upper()：用于返回指定英文字符串的大写形式的字符串。如果不为英文字符串，则返回原值。该方法有一个参数：
参数1：用于指定要转换为大写形式的字符串
*/
SELECT UPPER('abcDEFg');
SELECT UPPER('中文字符123');
/**
Ltrim()：用于返回删除前导空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行删除前导空格操作的字符串
*/
SELECT Ltrim(' ABcd ');
/**
Rtrim()：用于返回截断尾随空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行截断尾随空格操作的字符串。
*/
SELECT Rtrim(' ABcd ');
/**
trim()：用于返回截断前导空格和尾随空格之后的字符串。该方法有一个参数：
参数1：用于指定要进行截断尾随空格操作的字符串。
*/
SELECT trim(' ABcd ');
/*lpad(str,len,padstr): 用字符串padstr填补str左端直到字串长度为len并返回
参数1： 基准字符串
参数2： 要返回的字符串长度
参数3： 用于填补到基准字符串左侧
*/
SELECT LPAD('abcd',12,'123')
/*rpad(str,len,padstr): 用字符串padstr填补str右端直到字串长度为len并返回
参数1： 基准字符串
参数2： 要返回的字符串长度
参数3： 用于填补到基准字符串左侧
*/
SELECT RPAD('abcd',12,'123')
/*
Space()：用于返回指定数值长度的空格字符串。该方法有一个参数：
参数1：指定返回空格字符串的长度。
*/
SELECT '1'+SPACE(5)+'2'
/*
CONCAT(str1,str2,...): 用于将多个字符串连接成一个字符串
该函数和 str1+str2有同样的作用
*/
SELECT CONCAT('abc','123');
SELECT 'abc'+'123';
/*
Reverse()：用于对指定字符串进行反转，按照单个字符进行反转。该方法有一个参数：
参数1：指定需要执行反转操作的字符串。
*/
SELECT REVERSE('abcd');
/*
Replace()：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。该方法有三个参数：
参数1：用于指定要操作的字符串，即被匹配的字符串。
参数2：用于指定要进行匹配的字符串。
参数3：用于指定用作替换存在的匹配项的字符串。
*/
SELECT REPLACE('welcome','co','12');
/*==============================数字常用函数===============================*/
/*
TRUNCATE(x,y)返回参数x,是有保留y位小数，直接截取，不进行四舍五入
*/
SELECT TRUNCATE(4.414,2);
SELECT TRUNCATE(4.415,2);
/*
ROUND(x,y)返回参数x的四舍五入的有y位小数的值
*/
SELECT ROUND(4.414,2);
SELECT ROUND(4.415,2);
/*
+号:直接进行相加操作
*/
SELECT t.balance, t.cashBalance, t.balance+t.cashBalance from jifenpay.Account t WHERE t.balance > 0 and cashBalance > 0 limit 1;
SELECT '123.01'+1.02;
SELECT '123.01' + 0;
SELECT '123.00' + 0;
SELECT CAST(12345.01 AS char)+0;
SELECT CAST(12345.00 AS char)+0;
/*
-号:直接进行减法操作
*/
SELECT t.balance, t.cashBalance, t.balance-t.cashBalance from jifenpay.Account t WHERE t.balance > 0 and cashBalance > 0 limit 1;
/*
sum()： 取累加和
*/
SELECT sum(balance) from jifenpay.Account;
SELECT enterpriseId, sum(balance) from jifenpay.Account GROUP BY enterpriseId;
/*
AVG(): 取平均值
*/
SELECT avg(balance) from jifenpay.Account;
SELECT enterpriseId,avg(balance) from jifenpay.Account GROUP BY enterpriseId;
/*
RAND()：取0-1之间的任一值
*/
SELECT RAND();
-- 取符合条件的任一条记录
SELECT * from jifenpay.Account where enterpriseId = 1350 ORDER BY RAND() limit 1;
/*==============================时间日期常用函数===============================*/
/*
CURDATE()和CURRENT_DATE()：获取当前日期,年月日
*/
SELECT CURDATE();
SELECT CURRENT_DATE();
/*
CURRENT_TIME(): 获取当前时间，时分秒
*/
SELECT CURRENT_TIME();
/*
NOW()： 获取当前时间，年月日时分秒
*/
SELECT NOW();
/*
Year()：获取指定日期表达式的年。该方法有一个参数：
参数1：指定要操作的日期表达式。
*/
SELECT Year(NOW());
SELECT Year(CURDATE());
SELECT Year('2018-01-02');
/*
Month()：获取指定日期表达式的月。该方法有一个参数：
参数1：指定要操作的日期表达式。
*/
SELECT Month(NOW());
SELECT Month(CURDATE());
SELECT Month('2018-01-02');
/*
Day()：获取指定日期表达式的日。该方法有一个参数：
参数1：指定要操作的日期表达式。
*/
SELECT Day(NOW());
SELECT Day(CURDATE());
SELECT Day('2018-01-02');
/*
DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果
*/
SELECT DATE_ADD(CURDATE(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 WEEK);
SELECT DATE_ADD(NOW(),INTERVAL 2 MONTH);
SELECT DATE_ADD(NOW(),INTERVAL 2 HOUR);
SELECT DATE_ADD(NOW(),INTERVAL 2 MINUTE);
SELECT DATE_ADD(NOW(),INTERVAL 2 SECOND);
SELECT DATE_ADD(NOW(),INTERVAL -2 HOUR);
/*
DATE_ADD(date,INTERVAL int keyword)返回日期date减去间隔时间int的结果
*/
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 WEEK);
SELECT DATE_SUB(NOW(),INTERVAL 2 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 2 HOUR);
SELECT DATE_SUB(NOW(),INTERVAL 2 MINUTE);
SELECT DATE_SUB(NOW(),INTERVAL 2 SECOND);
SELECT DATE_SUB(NOW(),INTERVAL -2 HOUR);
/*
DATEDIFF(expr1,expr2): 返回起始时间 expr和结束时间expr2之间的天数
*/
SELECT DATEDIFF('2018-01-01','2018-02-01');
SELECT DATEDIFF('2018-03-02 01:01:02','2018-03-01');
SELECT DATEDIFF('2018-03-02 01:01:02','2018-03-01 23:59:59');
/*
DATE_FORMAT(date,format): 返回指定格式的时间，该类型为字符串
FORMAT参数中可以有以下使用方法
年：
%Y 显示四位 ： 2015
%y 只显示后两位 ：15
月：
%M 月份的英文显示：October
%m 月份的阿拉伯显示：01-12
%b 月份的英文缩略显示：Oct
%c 月份的阿拉伯显示：1-12
日：
%d 阿拉伯显示：00-31
%D 带有英文后缀：1st-31th
%e 阿拉伯显示：1-31
%j 年的天：001-366
时：
%H 00-23
%h 01-12
%I 01-12
%k 0-23
%l 1-12
分：
%i：00-59
秒：
%S：00-59
%s:00-59
12小时制时间：%r: 02:02:44 PM
24小时制时间： %T： 14:02:44
*/
SELECT DATE_FORMAT('2018/01/31 13:01:02','%y-%M-%D %r');
SELECT DATE_FORMAT('2018/01/31 13:01:02','%Y-%c-%e %r');
SELECT DATE_FORMAT('2018/01/31 13:01:02','%Y-%m-%d %T');
SELECT DATE_FORMAT('2018/01/31 13:01:02','%Y-%m-%d');
/*
STR_TO_DATE()/DATE():把字符串转换成指定格式的时间格式
*/
SELECT STR_TO_DATE('1992-04-12','%Y-%m-%d');
SELECT DATE('1992-04-12');
SELECT DATE(CONCAT(entryYear,entryMonth,entryDay)), entryYear, entryMonth, entryDay from userdoor.Person WHERE id = 392;
/*==============================加密相关常用函数===============================*/
/*
MD5(str): md5加密
*/
SELECT MD5('123456');
/*
SHA(str): SHA加密
*/
SELECT SHA('123456');
SELECT initPassword,
SHA(CONCAT(UPPER(MD5(initPassword)), memberId))
FROM
userdoor.MemberPlus
WHERE
memberId = 945243;
/*
ENCODE/DECODE: 加密解密
*/
SELECT ENCODE('kongjuan123456','abcd');
SELECT DECODE(ENCODE('kongjuan123456', 'abcd'),'abcd');
