MySQL学习之单行函数

最新推荐文章于 2023-03-24 21:51:03 发布

爱吃早饭的小王

最新推荐文章于 2023-03-24 21:51:03 发布

阅读量129

点赞数

文章标签： mysql 学习数据库

本文链接：https://blog.csdn.net/m0_55895641/article/details/124931906

版权

本文详细介绍了MySQL中的数值函数，如ABS、SIGN、PI等；字符串函数，如CONCAT、LOWER、LPAD等；日期和时间函数，如CURDATE、CURTIME、UNIX_TIMESTAMP等；流程控制函数，如IF、CASE WHEN...END等。通过实例展示了如何在SQL查询中进行计算、格式化和条件判断，帮助理解MySQL在数据处理中的强大功能。

摘要由CSDN通过智能技术生成

#第07章_单行函数

#1.数值函数
#基本的操作
SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;

#取随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

#四舍五入:ROUND(x,y)：x数保留y位小数 截断操作:TRUNCATE(x,y):x数保留y位小数
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1)
FROM DUAL;

#角度和弧度的转换
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;

#三角函数
SELECT
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1))
FROM DUAL;

#指数和对数
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
FROM DUAL;

#进制间的转换 CONV(x,f1,f2)：返回f1进制数变成f2进制数
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
FROM DUAL;

#2.字符串函数

#xxx worked for xxx
SELECT CONCAT(emp.last_name," worked for ",mgr.last_name) "details"
FROM employees emp JOIN employees mgr
ON emp.manager_id=mgr.employee_id;

#字符串的索引是从1开始的

SELECT last_name,salary
FROM employees
WHERE LOWER(last_name)="king";

#LPAD：左侧补星，实现右对齐效果
#RPAD：右侧补星，实现左对齐效果
SELECT employee_id,last_name,LPAD(salary,10,'*')
FROM employees;

SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name))
FROM employees;

#3.日期和时间函数

#3.1获取日期、时间

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE(),UTC_DATE(),UTC_TIME()
FROM DUAL;
#3.2日期与时间戳的转换
SELECT UNIX_TIMESTAMP(NOW())
FROM DUAL;

SELECT UNIX_TIMESTAMP(CURDATE())
FROM DUAL;

SELECT UNIX_TIMESTAMP('2021-10-1 12:12:30')
FROM DUAL;

#3.3获取月份、星期、星期数、天数等函数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

#3.4日期的操作函数
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(QUARTER FROM NOW())
FROM DUAL;

#3.5时间和秒钟转换的函数
SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(60456)
FROM DUAL;

#3.6计算日期和时间的函数
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;

SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

#3.7日期的格式化与解析
#格式化：日期--->字符串
#解析：字符串--->日期

#此时我们谈的是日期的显示格式化和解析
#之前我们接触过隐式的格式化和解析
SELECT *
FROM employees
WHERE hire_date='1993-01-13';

SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%H:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S %W %w %T %r')
FROM DUAL;

#解析：格式化的逆过程

SELECT STR_TO_DATE('2022-05-17 19:04:07 Tuesday 2 19:04:07 07:04:07 PM','%Y-%m-%d %H:%i:%S %W %w %T %r')
FROM DUAL;

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;

#4.流程控制函数
#4.1 IF(VALUE,VALUE1,VALUE2)
SELECT last_name,salary,IF(salary>=6000,'高工资','低工资') "details"
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details"
FROM employees;
#4.2 IFNULL(VALUE1,VALUE2):看作是IF(VALUE,VALUE1,VALUE2)的特殊情况
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;

#4.3 CASE  WHEN ... THEN... WHEN... THEN... ELSE... END
#类似于java中的 if...else if...else if...else...
SELECT last_name,salary,CASE WHEN salary>=15000 THEN '高薪'
			     WHEN salary>=10000 THEN '潜力股'
			     WHEN salary>=8000  THEN '偏低薪'
			     ELSE '低薪' END 'details'
FROM employees;
#4.4 CASE ... WHEN ... THEN... WHEN... THEN... ELSE... END
/*
练习1：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其
工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数,其他部门打印工资的1.4倍
*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary*1.1 
								     WHEN 20 THEN salary*1.2 
								     WHEN 30 THEN salary*1.3 
								     ELSE salary*1.4 END 'details'
								     
FROM employees;

/*
练习2：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其
工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。 
*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary*1.1 
								     WHEN 20 THEN salary*1.2 
								     WHEN 30 THEN salary*1.3 
								     END 'details'
								     
FROM employees
WHERE department_id IN(10,20,30);

#5.加密与解密
#PASSWORD()在mysql8.0中弃用
SELECT PASSWORD('musql')
FROM DUAL;

SELECT MD5('musql'),SHA('musql')
FROM DUAL;

#ENCODE()\DECODE()在mysql8.0中弃用
SELECT ENCODE('atguigu','musql'),DECODE(ENCODE('atguigu','musql'),'musql')
FROM DUAL;

#6.MySQL信息函数
SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷')
FROM DUAL;

#7.其他函数
# 如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2)
FROM DUAL;

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;


# 以“192.168.1.100”为例，计算方式为192乘以256的3次方，
# 加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;

#BENCHMARK()用于测试表达式的执行效率 
SELECT BENCHMARK(100000, MD5('mysql'))
FROM DUAL;
#CONVERT():可以实现字符集的转换
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu',USING 'gbk'))
FROM DUAL;

#课后练习
# 1.显示系统时间(注：日期+时间) 
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP()
FROM DUAL;
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary* 1.2 "new salary"
FROM employees;
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length） 
SELECT last_name,LENGTH(last_name) "name_length"
FROM employees
ORDER BY name_length ASC;
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT 
 SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
 FROM employees;
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序 
SELECT employee_id,
DATEDIFF(CURDATE(),hire_date)/365 "worked_years",
DATEDIFF(CURDATE(),hire_date) "worked_days",
TO_DAYS(CURDATE())-TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY worked_years DESC;

# 6.查询员工姓名，hire_date , department_id，满足以下条件：
#雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN(80,90,110)
AND commission_pct IS NOT NULL
#and hire_date >= '1997-01-01'; #存在着隐式转换
#and date_format(hire_date,'%Y-%m-%d') >='1997-01-01';#显示转换操作，格式化：日期-->字符串
#and DATE_FORMAT(hire_date,'%Y') >='1997';#显示转换操作，格式化
AND hire_date>=STR_TO_DATE('1997-01-01','%Y-%m-%d'); #显示转换操作,解析：字符串-->日期

# 7.查询公司中入职超过10000天的员工姓名、入职时间	
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date)>=10000;
# 8.做一个查询，产生下面的结果 
#<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0),' monthly but wants ',TRUNCATE(salary*3,0)) "Dream Salary"
FROM employees;
# 9.使用case-when，按照下面的条件： 
#job 		grade 
#AD_PRES 	A 
#ST_MAN 	B 
#IT_PROG	C 
#SA_REP 	D 
#ST_CLERK 	E

SELECT last_name "Last_name",job_id "Job_id",CASE Job_id WHEN 'AD_PRES'  THEN 'A'
							 WHEN 'ST_MAN'   THEN 'B'
							 WHEN 'IT_PROG'  THEN 'C'
							 WHEN 'SA_REP'   THEN 'D'
							 WHEN 'ST_CLERK' THEN 'E'
							 ELSE "undefinded" END  "Grade"
FROM employees;