第07章_单行函数-优快云博客

本文链接：https://blog.youkuaiyun.com/2401_84526799/article/details/139566310

1.2 不同DBMS函数的差异

MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）

2. 数值函数

2.1 基本函数

#1.数值函数
# ABS()：取绝对值
# SIGN():取符号
# CEIL()：取上界
# CEILING()：取上界
# FLOOR(): 取下界
SELECT  ABS(-122),ABS(11),ABS(0),SIGN(-111),SIGN(23),SIGN(0),
        PI(),CEIL(-32.32),CEILING(43.32),FLOOR(343.43),FLOOR(-23.23)
FROM  DUAL;        

#RAND（）：返回一个随机数
#RAND(X): x,为种子值，相同的种子值会有相同的随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

#ROUND():四舍五入
SELECT ROUND(34.433),ROUND(2.35,1),
       ROUND(124.456,-1),ROUND(152.33,-2)
FROM DUAL;

#truncate():截断，没有四舍五入
SELECT TRUNCATE(123.333,0),TRUNCATE(123.44,1),TRUNCATE(123.344,-1)
FROM DUAL;

#SQRT():开方
SELECT SQRT(4),SQRT(-1)
FROM DUAL;

#单行函数可以嵌套
SELECT TRUNCATE(ROUND(113.23,1),0)
FROM DUAL;

2.2 角度与弧度

#角度与弧度的换算 radians  degees
#RADIANS():将角度换为弧度
#DEGREES():将弧度换成角度
SELECT RADIANS(30),RADIANS(60),RADIANS(90),
       DEGREES(2*PI()),DEGREES(PI()/2)
FROM DUAL;

2.3 三角函数

#2.三角函数

SELECT  SIN(RADIANS(30)),DEGREES(ASIN(-1)),#正玄
        COS(RADIANS(30)),DEGREES(ACOS(0)),#余弦，反余弦
        TAN(RADIANS(45)),DEGREES(ATAN(1)),#反正切值
        DEGREES( ATAN(1,-1)),#两个参数的反正切值
        COT(RADIANS(45))#余切值
FROM DUAL;

2.4 指数函数、对数函数

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

2.5 进制间的转换

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

3. 字符串函数

2.字符串函数
SELECT ASCII('abacsd'),#返回第一个字母的ASCII
       CHAR_LENGTH('hello'),CHAR_LENGTH('中国'),#返回的字符的个数
       LENGTH('avbb'),LENGTH('章的')#返回的是字节数
FROM DUAL;  
#连接函数
SELECT CONCAT(e.last_name,' word for ',man.`last_name`) 
FROM employees e JOIN  employees man
ON   e.`manager_id`=man.`employee_id`;

SELECT CONCAT_WS('-','hello','world','ford')
FROM DUAL;

#替换函数
#字符串的索引是从1开始的
SELECT INSERT('helloworld',2,4,'aaaaa'),REPLACE('faaaaabas','aa','ddd')
FROM DUAL;
     
#大小写函数
SELECT UPPER('aaagbabb'),LOWER('AFsFAS')
FROM DUAL;

SELECT last_name
FROM employees e
WHERE LOWER(e.`last_name`)='king';

#取左和取右函数
SELECT LEFT('hellow',2),RIGHT('hellowed',4)
FROM DUAL;

#LPAD:右对齐
#RPAD:左对齐
SELECT employee_id,last_name,LPAD(salary,10,' '),RPAD(salary,10,' ' )
FROM employees ;

/*
LTRIM  去除左端空格
RTRIM  去除右端空格
TRIM   去除左右两端空格
TRIM(s1 FROM s）：去掉字符串s开始与结尾的s1
 TRIM(LEADING s1 FROM s)：去掉字符串s开始处的s1
 TRIM(TRAILING s1 FROM s) :去掉字符串s结尾处的s
*/
SELECT CONCAT('-----',LTRIM('  fff  f fff fs  '),'ddddd'),#去除左端空格
       CONCAT('-----',RTRIM('  fff  f fff fs  '),'ddddd'),#去除右端空格
       CONCAT('-----',TRIM('  fff  f fff fs  '),'ddddd'),#去除左右两端空格
       TRIM('oo'FROM 'oo dd oo'), 
FROM DUAL;

 #     重复几遍            返回几个空格    比较两个字符ASCII的大小
SELECT REPEAT('aa',2),LENGTH(SPACE(5)),STRCMP('abv','avc')   
FROM DUAL; 
 #         截取字符串       返回'11'在‘locate’的位置
SELECT SUBSTR('abvc',2,3),LOCATE('11','locate')
 FROM DUAL;
 
#     返回指定位置的字符串          返回‘mm’首次出现的位置
SELECT ELT(3,'a','g','h','r','ere'),FIELD('mm','a','hh','mm','afd'),
       FIND_IN_SET('mm','ff,mm,ffff,mm,ee'), #返回‘mm’首次出现的位置
       REVERSE('sfges') # 反转字符串
FROM DUAL;

 #NULLIF(s1,s2):相同返回NULL，不同返回s1
SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name))
FROM   employees;

4. 日期和时间函数

4.1 获取日期、时间

#4.时间和日期函数
SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),
       NOW(),SYSDATE(),
       UTC_DATE(),UTC_TIME()
FROM DUAL;

4.2日期与时间戳的转换

#4.2时间与时间撮的转换
# UNIX_TIMESTAMP('2024-06-08 22:25:19'):将时间转换成时间撮
# FROM_UNIXTIME(1717856719)：将时间撮转换为时间 
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2024-06-08 22:25:19'),
       FROM_UNIXTIME(1717856719)        
 FROM DUAL;

4.3 获取月份、星期、星期数、天数等函数

#4.3获取月份，星期，星期数，天数等函数
SELECT  YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
        HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME())
FROM DUAL;
       
#                                         周几
SELECT MONTHNAME(NOW()),DAYNAME(NOW()),WEEKDAY(NOW()),
       QUARTER(NOW()),WEEK(NOW()),DAYOFYEAR(NOW()),
       DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

4.4 日期的操作函数

#4.4 日期的操作函数
#EXTRACT(type FROM date):返回指定日期中特定的部分，type指定返回的值
SELECT  EXTRACT(SECOND FROM NOW()),EXTRACT(MINUTE FROM NOW()),
        EXTRACT(DAY FROM NOW()),EXTRACT(HOUR FROM NOW()),
        EXTRACT(DAY_MICROSECOND FROM NOW())
FROM   DUAL;

4.5 时间和秒钟转换的函数

#4.5 时间和秒数的转化
SELECT TIME_TO_SEC(CURTIME()),
       SEC_TO_TIME(CURTIME())
FROM DUAL;

4.6 计算日期和时间的函数

第1组：

4.6计算时间和日期的函数
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR) ,
       DATE_SUB(NOW(),INTERVAL 1 YEAR)
FROM DUAL;
#时间增加和减小函数
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
 DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
 DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

第2组：

#计算时间差函数
 SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),
 DATEDIFF(NOW(),'2021-1001'),
 TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
 LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,
 10)
 FROM DUAL;

4.7 日期的格式化与解析

#4.7 日期的格式化与解析

SELECT *
FROM employees
WHERE hire_date='2023-01-23';#日期隐式的转换

#进行格式化
SELECT DATE_FORMAT(CURDATE(),'%Y:%M:%D'),DATE_FORMAT(CURDATE(),'%Y:%m:%d'),
       DATE_FORMAT(CURTIME(),'%H:%i:%s'),
       DATE_FORMAT(NOW(),'%Y:%M:%D %h:%i:%s %W %w %T %r')
FROM DUAL;       
 
#进行解析
SELECT STR_TO_DATE('2024:June:9th 12:00:13 Sunday 0','%Y:%M:%D %h:%i:%s %W %w')
 FROM DUAL;
 
#得到格式
SELECT GET_FORMAT(DATE,'USA') 
FROM DUAL;

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

5.流程控制函数

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

#4.1IF(value,value1,value2)

SELECT last_name,salary,IF(salary>6000,'高工资','低工资')
FROM employees;

SELECT  last_name,salary,IF(commission_pct IS NOT NULL,commission_pct,0)
FROM employees;

#4.2IFNULL(VALUE1,VALUE2)
SELECT  last_name,salary,IFNULL(commission_pct,0)
FROM employees;

#4.3 CASE WHEN ... THEN ...WHEN ....THEN..ELSE...END
#类似java中的if..else 
SELECT last_name,salary,CASE WHEN salary>=13000 THEN '有钱'
                         WHEN salary>=10000 THEN '一般'
                         WHEN salary>=3000 THEN '拉'
                         ELSE '早跟' END      "水平"
FROM employees;

#4.4 CASE.... WHEN ... THEN ...WHEN ....THEN..ELSE...END
#类似于switch ...case ...case....dufault 
#练习：三个部门10，20，30 的工资成倍打印一下
SELECT last_name,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 "detail"
FROM employees
WHERE  department_id IN (10,20,30);

6. 加密与解密函数


#5.加密与解密函数
#PASSWORD():mysql80中已经弃用
selcet PASSWORD('12345')
FROM DUAL;

#不可逆的加密
SELECT MD5('122'),SHA('23r')
FROM DUAL;
#这个加密和解密也不能在mysql80使用
SELECT ENCODE('ssss','fsd'),DECODE(ENCODE('ssss','fsd'),'fsd')
FROM DUAL;

7. MySQL信息函数

#6。信息函数
#         版本     服务器连接数    数据库     数据库
SELECT  VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
        USER(),CURRENT_USER(),#当前用户
        CHARSET('发放'),#返回字符串的字符集
        COLLATION('fa哈')#返回字符串的比较规则
FROM DUAL;

8. 其他函数

#7。其他函数
#如果n的值小于或等于0，只会保留整数部分
SELECT FORMAT(132.234,3),FORMAT(223.32,0),FORMAT(223.23,-2)
FROM DUAL;
#不同进制的之间转换
SELECT CONV(19,10,2),CONV(23,10,16),CONV(NULL,10,2)
FROM DUAL;
#将iP地址转化为数字           将数字还原成IP地址
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;
#BENCHMARK():测试表达式执行的时间
SELECT BENCHMARK(10000,'afsdf') 
FROM DUAL;
#CONVERT():实现字符集的转化
SELECT CHARSET('abc'),CHARSET(CONVERT('abc' USING 'utf8mb4')),
        CHARSET(CONVERT('abc' USING 'GBK'))
FROM DUAL;

课后练习

【题目】
SELECT *FROM employees;
# 1.显示系统时间(注：日期+时间)
   SELECT  NOW(),CURDATE(),CURTIME()
   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)
     FROM employees   
     ORDER BY last_name ;
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
    SELECT  CONCAT(employee_id,' ',last_name,' ',salary)  OUT_PUT
    FROM employees;
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
     SELECT YEAR(NOW())-YEAR(hire_date),DATEDIFF(NOW(),hire_date)
     FROM employees;
# 6.查询员工姓名，hire_date , department_id，
#满足以下条件：雇用时间在1997年之后，department_id 
#为80 或 90 或110, commission_pct不为空
  SELECT last_name,hire_date,department_id
  FROM employees
  WHERE  DATEDIFF(hire_date,'1997-01-01')>0 && department_id IN(80,90,110)&& 
          commission_pct IS NOT NULL;
          
# 7.查询公司中入职超过10000天的员工姓名、入职时间
    SELECT last_name,hire_date
    FROM employees
    WHERE DATEDIFF(NOW(),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))
 FROM employees; 

# 9.使用case-when，按照下面的条件：
job                  gradede
AD_PRES              A
ST_MAN               B
IT_PROG              C
SA_REP               D
ST_CLERK               E


    SELECT last_name,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  'F'  END "grade"
     FROM employees;