【MySQL】MySQL基础05 — SQL学习 — DQL — 常见函数 — 单行函数（转载请注明出处）
SQL学习 — DQL — 常见函数 — 单行函数
4. 常见函数（附加）
/*
概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。
好处：1.隐藏了实现细节 2.提高代码的重用性
调用语法：select 函数名(实参列表) 【from 表】;
特点：
1.叫什么（函数名）
2.干什么（函数功能）
分类：
1.单行函数
功能：做处理
如：cancat \ length \ ifnull等
2.分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
*/
4.1 单行函数
-
字符函数
-
length：获取参数值的字节个数#1.length 获取参数值的字节个数 SELECT LENGTH('john'); SELECT LENGTH('张三丰'); # utf-8中一个汉字占3个字节 -
cancat：拼接字符串#2.cancat 拼接字符串 SELECT CONCAT(last_name,'_', first_name) FROM employees; -
upper \ lower：更换大小写#3.upper \ lower 更换大小写 SELECT UPPER('john'); SELECT LOWER('ROSE'); # 案例1：将姓变大写，将名变小写，然后拼接 SELECT CONCAT(UPPER(last_name),LOWER(first_name)) FROM employees; -
substr \ substring：截取字符串#4.substr \ substring 截取字符串 注意：索引从1开始 #截取从指定索引处后面的所有字符 SELECT SUBSTR('阿珍爱上阿强',5) AS out_put; # 截取从指定索引处指定字符长度的字符 SELECT SUBSTRING('阿珍爱上阿强',1,4) AS out_put; # 案例2：姓名中首字符大写，其他字符小写，然后用_拼接，显示出来 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)), '_' , LOWER(first_name)) AS out_put FROM employees; -
instr：返回子串第一次出现的索引，如果找不到返回0#5.instr 返回子串第一次出现的索引，如果找不到返回0 SELECT INSTR('阿珍爱上阿强','阿强') AS out_put; -
trim：去掉字符串前后中指定的字符#6.trim 去掉字符串前后中指定的字符 SELECT TRIM('a' FROM 'aaaaaaa张aaaaa三aa丰aaaaa') AS out_put; # 案例3：去掉前后a后，如何去掉张三丰中间的a呢？ SELECT REPLACE(TRIM('a' FROM 'aaaaaaa张aaaaa三aa丰aaaaa'),'a','') AS out_put; -
lpad：用指定的字符实现左填充指定长度#7.lpad 用指定的字符实现左填充指定长度 SELECT LPAD('张三丰',6,'*') AS out_put; -
rpad：用指定的字符实现右填充指定长度#8.rpad 用指定的字符实现右填充指定长度 SELECT RPAD('张三丰',6,'*') AS out_put; -
replace：替换#9.replace 替换 SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;
-
-
数学函数
-
round ：四舍五入
#1.round 四舍五入 SELECT ROUND(1.65); # 案例1：小数点后保留3位，四舍五入 SELECT ROUND(1.6585, 3); -
ceil ：向上取整, 返回>=该参数的最小整数
#2.ceil 向上取整, 返回>=该参数的最小整数 SELECT CEIL(1.52); -
floor ： 向下取整,返回<=该参数的最大整数
#3.floor 向下取整,返回<=该参数的最大整数 SELECT FLOOR(9.99); -
truncate ：截断,保留小数点后几位
#4.truncate 截断,保留小数点后几位 SELECT TRUNCATE(1.699999,3); -
mod ：取余
#5.mod 取余 /* 记住：mod(a,b) : a-a/b*b 例：mod(-10,-3) : -10 - (-10)/(-3) * (-3) = -1 */ SELECT MOD(10,3);
-
-
日期函数
-
常用日期格式符含义
格式符 功能 %Y 四位的年份 %y 两位的年份 %m 月份（01，02，03，....） %c 月份（1，2，3，...） %d 日（01，02，..） %H 小时（24小时制） %h 小时（12小时制） %i 分钟 %s 秒 -
now ：返回当前系统日期+时间
#1.now 返回当前系统日期+时间 SELECT NOW(); -
curdate ：返回当前系统日期，不包含时间
#2.curdate 返回当前系统日期，不包含时间 SELECT CURDATE(); -
curtime ：返回当前的时间，不包含日期
#3.curtime 返回当前的时间，不包含日期 SELECT CURTIME(); -
可以获取指定的部分，年、月、日、小时、分钟、秒
-
year ：获取指定年
#4.year 获取指定年 SELECT YEAR(NOW()); SELECT YEAR('2023-04-28'); SELECT YEAR(hiredate) AS '年' FROM employees; -
month ：获取指定月
#5.month 获取指定月 SELECT MONTH(NOW()); SELECT MONTHNAME(NOW()); # 月份数字转换成英文 -
date ：获取指定日
#6.day 获取指定日 SELECT DAY(NOW()); -
hour ：获取指定小时
#7.hour 获取指定小时 SELECT HOUR(NOW()); -
minute ：获取指定分钟
#8.minute 获取指定分钟 SELECT MINUTE(NOW()); -
second ：获取指定秒
#9.second 获取指定秒 SELECT SECOND(NOW()); -
str_to_date ：将字符通过str其指定的格式进行转换成日期,严格按照str格式来，不可自定义，否则结果为null
#10.str_to_date 将字符通过str其指定的格式进行转换成日期,严格按照str格式来，不可自定义，否则结果为null SELECT STR_TO_DATE('9-1-1999','%m-%d-%Y') AS out_put; -
date_format ：将日期转换成字符，可自定义
#11.date_format 将日期转换成字符，可自定义 SELECT DATE_FORMAT('2020-04-25','%y年-%m月-%d日') AS out_put; # 案例1：查询有奖金的员工名和入职日期(xx月/xx日 xx年) SELECT last_name AS 员工名, DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职日期 FROM employees WHERE commission_pct IS NOT NULL;
-
-
-
其他函数
#1.version 查看mysql版本号 SELECT VERSION(); #2.database 查看当前的库 SELECT DATABASE(); #3.user 查看当前用户 SELECT USER();
-
流程控制函数
-
if函数 ：实现if else的效果
#1.if函数 实现if else的效果 SELECT IF(10>5, '大', '小'); # 案例1：查询有奖金的员工名，奖金情况，如果没有奖金就打上继续努力，有奖金就打上你值得。 SELECT last_name, commission_pct, IF(commission_pct IS NULL, '继续努力' , '你值得') AS '备注' FROM employees; -
case函数使用方式一 ：switch case的效果，适用于等值判断
#2.case函数 使用一：switch case的效果，适用于等值判断 /* java中 switch(变量或表达式){ case 常量1：语句1; break; case 常量2：语句2; break; .... default: 语句; break; } mysql中 case 要判断的字段或表达式 when 常量1 then 要显示的值1或 语句1; #常量可以是数值也可以是字符串 when 常量2 then 要显示的值2或 语句2; .... else 默认情况要显示的值或 语句; end */ ------------------------------------ /* 案例2:查询员工的名字，部门号, 工资，要求： 部门号=30，显示的工资为1.3倍 部门号=40，显示的工资为1.4倍 部门号=50，显示的工资为1.5倍 其他部门，显示的工资为原工资 */ SELECT last_name AS '员工名', department_id AS '部门号', salary AS '工资', CASE department_id WHEN 30 THEN salary * 1.3 WHEN 40 THEN salary * 1.4 WHEN 50 THEN salary * 1.5 ELSE salary END AS '到手工资' FROM employees; -
case函数使用方式二 ：类似于多重if ，适用于区间判断
#3.case函数 使用二：类似于多重if ，适用于区间判断 /* java中 if(条件1){ 语句1; {else if(条件2){ 语句2; }else if(条件3){ ... }else{ 语句n; } mysql中 case when 条件1 then 要显示的值1 或语句1; when 条件2 then 要显示的值2 或语句2; ... else 要显示的值 或语句; end */ ---------------------------------- /* 案例3:查询员工的名字，工资，要求： 如果工资大于20000，显示A级别 如果工资大于15000，显示B级别 如果工资大于10000，显示C级别 否则，显示D级别 */ SELECT last_name AS '员工名', salary AS '工资', CASE WHEN salary>20000 THEN 'A' WHEN salary>=15000 THEN 'B' #区间判断值，但不能用between in关键字 WHEN salary>=10000 THEN 'C' ELSE 'D' END AS '工资级别' FROM employees;
-
-
单行函数总结
#总结 /* 1.字符函数 length concat substr instr trim upper / lower lpad / rpad replace 2.数学函数 round ceil floor truncate mod 3.日期函数 now curdate curtime year / month / day hour / minute / second str_to_date date_format 4.其他函数 version database user 5.控制函数 if case */
4.2 单行函数练习
# 将以下内容复制到sql图像化客户端界面进行思考
#1. 显示系统时间(注：日期+时间)
#2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
#3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
#4. 做一个查询，产生下面的结果
要求：<last_name> earns <salary> monthly but wants <salary*3> Dream Salary
案例：King earns 24000 monthly but wants 72000
#5. 使用 case-when，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
4.3 单行函数练习答案
文件名：04SQL_Single-RowFunctions.sql
链接：https://pan.baidu.com/s/11Xqwc9F7W3SZwaRH_8sFhA?pwd=ccjz
提取码：ccjz
