【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 陈景中阅读(34) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 【MySQL】MySQL基础06 — SQL学习 — DQL — 常见函数 — 分组函数（转载请注明出处）

· 【MySQL】MySQL基础02 — SQL学习 — DQL — 基础查询（转载请注明出处）

· Mysql基础函数

· MySQL基础函数使用

· MySQL函数

陈景中的博客

客观办事主观生活

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

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

4. 常见函数（附加）

4.1 单行函数

4.2 单行函数练习

4.3 单行函数练习答案

公告

搜索

我的标签

随笔分类

随笔档案

阅读排行榜

推荐排行榜

	/*
	概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名。
	好处：1.隐藏了实现细节 2.提高代码的重用性
	调用语法：select 函数名(实参列表) 【from 表】;
	特点：
	1.叫什么（函数名）
	2.干什么（函数功能）

	分类：
	1.单行函数
	功能：做处理
	如：cancat \ length \ ifnull等
	2.分组函数
	功能：做统计使用，又称为统计函数、聚合函数、组函数


	*/

	#1.length 获取参数值的字节个数
	SELECT LENGTH('john');
	SELECT LENGTH('张三丰'); # utf-8中一个汉字占3个字节

	#2.cancat 拼接字符串
	SELECT CONCAT(last_name,'_', first_name)
	FROM employees;

	#3.upper \ lower 更换大小写
	SELECT UPPER('john');
	SELECT LOWER('ROSE');

	# 案例1：将姓变大写，将名变小写，然后拼接
	SELECT CONCAT(UPPER(last_name),LOWER(first_name))
	FROM employees;

	#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;

	#5.instr 返回子串第一次出现的索引，如果找不到返回0
	SELECT INSTR('阿珍爱上阿强','阿强') AS out_put;

	#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;

	#7.lpad 用指定的字符实现左填充指定长度
	SELECT LPAD('张三丰',6,'*') AS out_put;

	#8.rpad 用指定的字符实现右填充指定长度
	SELECT RPAD('张三丰',6,'*') AS out_put;

	#9.replace 替换
	SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

	#1.round 四舍五入
	SELECT ROUND(1.65);

	# 案例1：小数点后保留3位，四舍五入
	SELECT ROUND(1.6585, 3);

	#2.ceil 向上取整, 返回>=该参数的最小整数
	SELECT CEIL(1.52);

	#3.floor 向下取整,返回<=该参数的最大整数
	SELECT FLOOR(9.99);

	#4.truncate 截断,保留小数点后几位
	SELECT TRUNCATE(1.699999,3);

	#5.mod 取余
	/*

	记住：mod(a,b) : a-a/b*b

	例：mod(-10,-3) : -10 - (-10)/(-3) * (-3) = -1

	*/
	SELECT MOD(10,3);

	#2.curdate 返回当前系统日期，不包含时间
	SELECT CURDATE();

	#3.curtime 返回当前的时间，不包含日期
	SELECT CURTIME();

	#4.year 获取指定年
	SELECT YEAR(NOW());
	SELECT YEAR('2023-04-28');
	SELECT YEAR(hiredate) AS '年' FROM employees;

	#5.month 获取指定月
	SELECT MONTH(NOW());
	SELECT MONTHNAME(NOW()); # 月份数字转换成英文

	#10.str_to_date 将字符通过str其指定的格式进行转换成日期,严格按照str格式来，不可自定义，否则结果为null
	SELECT STR_TO_DATE('9-1-1999','%m-%d-%Y') AS out_put;

陈景中的博客

客观办事 主观生活

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

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

4. 常见函数（附加）

4.1 单行函数

4.2 单行函数练习

4.3 单行函数练习答案

公告

搜索

我的标签

随笔分类

随笔档案

阅读排行榜

推荐排行榜

客观办事主观生活

	#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();

	#1.if函数实现if else的效果
	SELECT IF(10>5, '大', '小');

	# 案例1：查询有奖金的员工名，奖金情况，如果没有奖金就打上继续努力，有奖金就打上你值得。
	SELECT last_name, commission_pct, IF(commission_pct IS NULL, '继续努力' , '你值得') AS '备注'
	FROM employees;

	#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;

	#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
	*/

	# 将以下内容复制到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