Java基础27–mysql-单行函数
单行函数
- 只对一行进行变换,每行返回一个结果*
- 可以嵌套
- 参数可以是一字段或一个表达式或一个值
单行函数-字符串函数
字符串函数
函数 | 用法 |
---|---|
CONCAT(S1,S2,…,Sn) | 连接S1,S2,…,Sn为一个字符串 |
CONCAT_WS(s, S1,S2,…,Sn) | 同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上s |
CHAR_LENGTH(s) | 返回字符串s的字符数 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
INSERT(str, index , len, instr) | 将字符串str从第index位置开始,len个字符长的子串替换为字符串instr |
UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(s,n) | 返回字符串s最左边的n个字符 |
RIGHT(s,n) | 返回字符串s最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(【BOTH 】s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(【LEADING】s1 FROM s) | 去掉字符串s开始处的s1 |
TRIM(【TRAILING】s1 FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
STRCMP(s1,s2) | 比较字符串s1,s2 |
SUBSTRING(s,index,len) | 返回从字符串s的index位置其len个字符 |
举例1:大小写控制函数
*
函数 | 结果 |
---|---|
LOWER(‘SQL Course’) | sql course |
UPPER(‘SQL Course’) | SQL COURSE |
这类函数改变字符的大小写。
- 举例2:字符控制函数
函数 | 结果 |
---|---|
CONCAT(‘Hello’,‘World’) | HelloWorld |
SUBSTR(‘HelloWorld’,1,5) | Hello |
LENGTH(‘HelloWorld’) | 10 |
INSTR(‘HelloWorld’,‘W’) | 6 |
LPAD(salary,10,’*’) | *****24000 |
RPAD(salary,10, ‘*’) | 24000***** |
TRIM(‘H’ FROM ‘HelloWorld’) | elloWorld |
REPLACE(‘abcd’,‘b’,‘m’) | amcd |
(1)length(xx):求字节数,和字符集有关,比如汉字uft-8三个字节,GBK两个字节
(2)char_length(xx):求字符数
(3)CONCAT(str1,str2,…)
(4)CONCAT_WS(分隔符,str1,str2,…)
(5)UPPER(xx);转大写
(6)LOWER(xx):转小写
(7)LEFT(s,n):返回最左边的3个字符
RIGHT(s,n):返回最右边的3个字符
(8)LTRIM(s):去掉左边的空格
RTRIM(s):去掉右边的空格
TRIM(s):去掉前后空白符
TRIM(【BOTH 】s1 FROM s) :从s中去掉前后的s1
TRIM(【LEADING】s1 FROM s):从s中去掉开头的s1
TRIM(【TRAILING】s1 FROM s):从s中去掉结尾的s1
(9)SUBSTRING(s,index,len)
#求字符串的长度
#例如:查询员工的姓名的字符的个数
SELECT ename AS "姓名", CHAR_LENGTH(ename) AS "姓名的字数" FROM t_employee;
#求字符串的拼接
SELECT 'hello' + 'world'; //#在mysql中,+只表示求和,不表示拼接,Java中+可以表示拼接
SELECT CONCAT('hello','world','java');
SELECT CONCAT_WS('-','hello','world','java');
CONCAT_WS每个字符串之间要加上- |
#转大小写
SELECT UPPER('hello'),LOWER('HELLO');
SELECT LEFT('hello',3);
SELECT CONCAT('[',LTRIM(' hello world '),']');
SELECT CONCAT('[',TRIM(' hello world '),']');
SELECT TRIM(BOTH '#' FROM '###hello world###');//将左右两边#去掉
SELECT TRIM(LEADING '#' FROM '###hello world###');//将开头#去掉
SELECT TRIM(TRAILING '#' FROM '###hello world###');//将结尾#去掉
SELECT SUBSTRING('hello',2);#从第二个字符,不是下标的概念
单行函数-数值(数学)函数
函数 | 用法 |
---|---|
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回大于x的最小整数值 |
FLOOR(x) | 返回小于x的最大整数值 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1的随机值 |
ROUND(x,y) | 返回参数x的四舍五入的有y位的小数的值 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根 |
POW(x,y) | 返回x的y次方 |
- 举例1:ROUND:四舍五入
ROUND(45.926, 2) --> 45.93
- 举例2:TRUNCATE:截断
TRUNCATE(45.926) --> 45
- 举例3:MOD:求余
MOD(1600, 300) --> 100
SELECT CEIL(2.3),FLOOR(2.3),ROUND(2.3)
SELECT CEIL(2.6),FLOOR(2.6),ROUND(2.6)
SELECT RAND() #[0,1)的随机值
#ROUND(x,y),保留小数点后y位,看第y+1位,决定四舍五入
#TRUNCATE(x,y),保留小数点后y位,y位后直接截掉
SELECT ROUND(2.12345,4),TRUNCATE(2.12345,4)
SELECT ename,salary FROM t_employee;
SELECT ename, ROUND(salary,0) FROM t_employee;
单行函数-日期时间函数
函数 | 用法 |
---|---|
CURDATE() 或 CURRENT_DATE() | 返回当前日期 |
CURTIME() 或 CURRENT_TIME() | 返回当前时间 |
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() | 返回当前系统日期时间 |
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time) | 返回具体的时间值 |
WEEK(date) / WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFWEEK() | 返回周几,注意:周日是1,周一是2,。。。周六是7 |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
DAYNAME(date) | 返回星期:MONDAY,TUESDAY…SUNDAY |
MONTHNAME(date) | 返回月份:January,。。。。。 |
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2) | 返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔 |
DATE_ADD(datetime, INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
DATE_FORMAT(datetime ,fmt) | 按照字符串fmt格式化日期datetime值 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
其中:
(1)DATE_ADD(datetime,INTERVAL expr type)
表达式类型:
参数类型 | 参数类型 |
---|---|
YEAR | YEAR_MONTH |
MONTH | DAY_HOUR |
DAY | DAY_MINUTE |
HOUR | DAY_SECOND |
MINUTE | HOUR_MINUTE |
SECOND | HOUR_SECOND |
MINUTE_SECOND |
举例:
举例:
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #可以是负数
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH); #需要单引号
(2)DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)
格式符 | 说明 | 格式符 | 说明 |
---|---|---|---|
%Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
%M | 月名表示月份(January,…) | %m | 两位数字表示月份(01,02,03。。。) |
%b | 缩写的月名(Jan.,Feb.,…) | %c | 数字表示月份(1,2,3,…) |
%D | 英文后缀表示月中的天数(1st,2nd,3rd,…) | %d | 两位数字表示月中的天数(01,02…) |
%e | 数字形式表示月中的天数(1,2,3,4,5…) | ||
%H | 两位数字表示小数,24小时制(01,02…) | %h和%I | 两位数字表示小时,12小时制(01,02…) |
%k | 数字形式的小时,24小时制(1,2,3) | %l | 数字形式表示小时,12小时制(1,2,3,4…) |
%i | 两位数字表示分钟(00,01,02) | %S和%s | 两位数字表示秒(00,01,02…) |
%W | 一周中的星期名称(Sunday…) | %a | 一周中的星期缩写(Sun.,Mon.,Tues.,…) |
%w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
%j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周,(1,2,3。。)其中Sunday为周中第一天 |
%u | 以数字表示年中的第几周,(1,2,3。。)其中Monday为周中第一天 | ||
%T | 24小时制 | %r | 12小时制 |
%p | AM或PM | %% | 表示% |
(1)系统时间
NOW()和SYSDATE()
CURRENT_DATE():只有日期
CURRENT_TIME():只有时间
CURRENT_TIMESTAMP():日期时间
(2)求出年等
year(x)
month(x)
day(x)
(3)DATEDIFF(x,y):间隔天数
(4)格式化
DATE_FORMAT:把日期转字符串
STR_TO_DATE:把字符串转日期
#系统时间
SELECT NOW()
SELECT SYSDATE()
SELECT CURRENT_DATE()
SELECT CURRENT_TIME()
SELECT CURRENT_TIMESTAMP()
SELECT ename,birthday,YEAR(birthday),MONTH(birthday),DAY(birthday) FROM t_employee
SELECT ename,YEAR(NOW()) - YEAR(birthday) AS "年龄" FROM t_employee;
SELECT ename, YEAR(NOW()) - YEAR(hiredate) + 1 AS "入职年数" FROM t_employee
SELECT ename,birthday, DAYOFWEEK(birthday) FROM t_employee;
SELECT DAYOFWEEK(NOW())
SELECT birthday,DATEDIFF(NOW(), birthday) FROM t_employee;//间隔天数
SELECT DATEDIFF(NOW(),'2019-05-13');//间隔天数
SELECT DATE_FORMAT(birthday,'%Y年%m月%d日') FROM t_employee;
单行函数-流程函数
函数 | 用法 |
---|---|
IF(value,t ,f) | 如果value是真,返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
- 举例1:
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0)) as "年薪"
FROM employees;
- 举例2:
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "实发工资"
FROM employees;
- 举例3:
xxxxxxxxxx SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪' WHEN salary>=10000 THEN '潜力股' WHEN salary>=8000 THEN '屌丝' ELSE '草根' END "描述"FROM employees;
/*
流程控制函数
(1)if(value,x,y):如果value为true,返回x,否则y
(2)ifnull(value,x):如果value是null值,就用x代替,否则用value自己的值
在mysql中null值是非常特殊
①判断null值用:<=>或is null ,is not null,不能用=和!=
②计算null值,所有运算符,遇到null,运算结果都是null
(3)case 字段/表达式
when 常量值 then 值/表达式
when 常量值 then 值/表达式
when 常量值 then 值/表达式
else 值/表达式
end
类似于Java的switch...case
(4)case
when 条件1 then 值/表达式
when 条件2 then 值/表达式
when 条件3 then 值/表达式
else 值/表达式
end
类似于Java的if..else if...
*/
SELECT IF(FALSE,1,2)
#求员工的姓名和实发工资
#实发工资 = 薪资 + 奖金 = 薪资 + 薪资 * 奖金比例
SELECT ename,salary + salary * commission_pct FROM t_employee;
SELECT ename,salary + salary * IFNULL(commission_pct,0) FROM t_employee;
SELECT ename,job_id, CASE job_id
WHEN 1 THEN '教学总监'
WHEN 2 THEN '讲师'
WHEN 3 THEN '助教'
ELSE '其他'
END AS "职位说明"
FROM t_employee;
SELECT ename, salary, CASE
WHEN salary >= 20000 THEN '高富帅'
WHEN salary >= 15000 THEN '潜力股'
WHEN salary >= 8000 THEN '屌丝'
ELSE '土鳖'
END AS "情况"
FROM t_employee;
单行函数-其他函数
函数 | 用法 |
---|---|
database() | 返回当前数据库名 |
version() | 返回当前数据库版本 |
user() | 返回当前登录用户名 |
password(str) | 返回字符串str的加密版本,41位长的字符串 |
md5(str) | 返回字符串str的md5值,也是一种加密方式 |
加密函数
INSERT INTO t_user VALUES('chailinyan','123456');
INSERT INTO t_user VALUES('admin',PASSWORD('123456'));//PASSWORD加密
INSERT INTO t_user VALUES('yanghongqiang',MD5('123456'));//MD5加密
password加密后,MD5还没运行加密
MD5加密
password和MD5加密规则不一样
SELECT * FROM t_user WHERE username = 'chailinyan' AND `password` = '123456';
//SELECT * FROM t_user WHERE username = 'admin' AND `password` = '123456';
没加密的可以直接读出来
password加密的不可以直接查出来,应该写为
SELECT * FROM t_user WHERE username = 'admin' AND `password` = PASSWORD('123456');
//或者写为
MD5加密直接查查不出来
MD5加密再查询应写为:
SELECT * FROM t_user WHERE username = 'yanghongqiang' AND `password` = MD5('123456');
password加密返回字符串str的加密版本,41位长的字符串,MD5加密返回字符串str的md5值,字符串长度短一点,也是一种加密方式(之前说MD5无法逆转,相当于自带安全的加密算法,破解难度大,比较安全)