【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

posted @ 2023-04-07 22:28 陈景中阅读(32) 评论(0) 编辑收藏举报

刷新页面返回顶部

陈景中的博客

客观办事主观生活

【MySQL】MySQL基础05 — SQL学习 — DQL — 常见函数 — 单行函数（转载请注明出处）

SQL学习 — DQL — 常见函数 — 单行函数

4. 常见函数（附加）

4.1 单行函数

4.2 单行函数练习

4.3 单行函数练习答案

公告

陈景中的博客

客观办事 主观生活

【MySQL】MySQL基础05 — SQL学习 — DQL — 常见函数 — 单行函数（转载请注明出处）

SQL学习 — DQL — 常见函数 — 单行函数

4. 常见函数（附加）

4.1 单行函数

4.2 单行函数练习

4.3 单行函数练习答案

公告

客观办事主观生活