7、当行函数

#===== ===== 07_单行函数 ===== =====#
#接受参数返回一个结果 
#每行只返回一个结果

/**1、数值函数**/
/* 1.1 基本函数 */
SELECT SIGN(-21), SIGN(23), CEIL(2.35), LEAST(1, 3, -1), GREATEST(1, 20, 10)
FROM DUAL;

# 四舍五入
SELECT ROUND(12.45), ROUND(12.456, 1), ROUND(123.45, -1)
FROM DUAL;

# 截断操作
SELECT TRUNCATE(123.45, 1)
FROM DUAL;

/* 1.2 三角函数 */
# 角度与弧度互换函数
SELECT RADIANS(30), RADIANS(60), RADIANS(90), DEGREES(2*PI())
FROM DUAL;

# SIN
SELECT SIN(RADIANS(30)), DEGREES(ASIN(1)), SIN(RADIANS(45))
FROM DUAL;
# TAN
SELECT TAN(RADIANS(45)), DEGREES(ATAN(1))
FROM DUAL;

/*1.3 指数和对数 */
SELECT POW(2, 5), POWER(2, 4), EXP(2), LN(EXP(2)), LOG(EXP(2)), LOG2(4)
FROM DUAL;

/* 1.4 进制间的转换 */
SELECT BIN(10), HEX(11)
FROM DUAL;

/*2、字符串函数*/
/* 2.1 ASCII */
# utf-8 每个汉字占3个字节 LENGTH按字节存储数据
SELECT ASCII('abcd'), CHAR_LENGTH('hello'), CHAR_LENGTH('我们'),
LENGTH('hello'), LENGTH('我们')
FROM DUAL;

# CONCAT
SELECT CONCAT('hello', 'world'), CONCAT_WS('-', '2022', '01', '25')
FROM DUAL;

# INSERT： sql字符串索引从1开始 | REPLACE
SELECT INSERT('hello', 2, 3, 'aaaa'), REPLACE('hello', 'lo', 'am')
FROM DUAL;

# UPPER LOWER
SELECT UPPER('heLLo'), LOWER('HEllo')
FROM DUAL;

# LEFT RIGHT
SELECT LEFT('hello', 2), RIGHT('hello', 3), LEFT('hello', 12)
FROM DUAL;

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


# LTRIM RTRIM TRIM
SELECT LTRIM('  hello')
FROM DUAL;

# REPEAT 重复字符串
SELECT REPEAT('hello', 4), SPACE(5)
FROM DUAL;

# STRCMP比较字符串的ascii
SELECT STRCMP('abc', 'bd')
FROM DUAL;

# SUBSTR 返回子字符串 LOCATE 返回字符串首次出现的位置
SELECT SUBSTR('hello', 2, 2), LOCATE('ll', 'hello')
FROM DUAL;

# ELT 返回序列的第N个字符串
SELECT ELT(2, 'a', 'b', 'c', 'd')
FROM DUAL;

# FIELD 返回字符串在字符串列表的位置
SELECT FIELD('a', 'a', 'b'), FIND_IN_SET('a', 'm,a,m,a')
FROM DUAL;


/*3、日期与时间函数*/
/* 3.1 获取日期时间*/
SELECT CURDATE(), CURTIME(), NOW(),SYSDATE(), UTC_DATE(), UTC_TIME()
FROM DUAL;

/*3.2 日期与时间戳的转换*/
SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(1643180763), UNIX_TIMESTAMP('2022-10-01 23:12:12')
FROM DUAL;

/*3.3 获取月分、星期、星期数、天数等函数*/
SELECT YEAR(CURDATE()), MONTH(CURDATE()),DAY(CURDATE()), HOUR(NOW()), MINUTE(NOW())
FROM DUAL;

/* 3.4 日期操作函数 */
# extract 
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(HOUR FROM NOW()), EXTRACT(QUARTER FROM NOW())
FROM DUAL;

# 周六0 周日1 周一2
SELECT MONTHNAME(CURDATE()), WEEKDAY(CURDATE()), DAYOFMONTH(CURDATE()), DAYOFWEEK(CURDATE())
FROM DUAL;

/* 3.5 时间与秒钟转换函数 */
SELECT TIME_TO_SEC(NOW())
FROM DUAL;

/* 3.6 计算日期与时间的函数 */
# DATE_ADD(NOW(), INTERVAL 1 YEAR) date_sub()
SELECT NOW(), DATE_ADD(NOW(), INTERVAL 1 YEAR), DATE_ADD(NOW(), INTERVAL -1 MONTH),DATE_SUB(NOW(), INTERVAL 20 MINUTE),
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH)
FROM DUAL;

# DATEDIFF :计算相差的天数 | TIMEDIFF :相差的时间戳
SELECT DATEDIFF(NOW(), '2022-1-20'), TIMEDIFF(NOW(), '2022-1-26 10:22:22')
FROM DUAL;

/* 3.7 日期的格式化与解析*/
# 显示的格式化与解析 格式化 : 日期 --> 字符串 | 解析： 字符串 --> 日期

# 格式化 GET_FORMAT()
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-%d'), TIME_FORMAT(NOW(), '%H:%i:%S'),
DATE_FORMAT(CURDATE(), GET_FORMAT(DATE, 'USA'))
FROM DUAL;

# 解析：格式化的逆过程
SELECT STR_TO_DATE('2022-1-26', '%Y-%m-%d')
FROM DUAL;


/*4、流程控制函数*/

/*4.1 IF(VALUE, VALUE1, VALUE2) VALUE 为true VALUE1 else VALUE2*/
SELECT last_name, salary, IF(salary >= 6000, '高', '低')
FROM employees;

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

# IFNULL(V1, V2) if V1 != NULL val = V1 else val = V2
SELECT last_name, commission_pct, IFNULL(commission_pct, 0) 'details'
FROM employees;


/* 4.2 CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END  相当于 if ... else if ... else ...*/
SELECT last_name, salary , CASE WHEN salary >= 15000 THEN 'T1'
                WHEN salary >= 10000 THEN 'T2'
                WHEN salary >= 8000 THEN 'T3'
                ELSE 'T4' END 'detail'
FROM employees;


/* 4.3 CASE exp WHEN ... THEN ... WHEN ... THEN ... ELSE ... END 相当于 switch ... case1.. case2 .. */


/*5、加密与解密函数*/
/* 5.1 PASSWORD() 8.0中已经被弃用*/


/* 5.2 MD5() SHA()不可逆*/
SELECT MD5('mysql'), SHA('msyql')
FROM DUAL;

/*6、MySQL信息函数*/

SELECT VERSION(), CONNECTION_ID(),CHARSET('尚硅谷')
FROM DUAL;


/*7、其他函数*/
/* 7.1 FORMAT 四舍五入 */
SELECT FORMAT(12.34, 1)
FROM DUAL;

/* 7.2 BENCHMARK() 测试表达式的执行效率 */
SELECT BENCHMARK(1000000000,  MD5('mysql'))
FROM DUAL;


/*    ****      课后练习      ****    */
# 1.显示系统时间(注：日期+时间)
SELECT NOW(), SYSDATE()
FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id, last_name, salary, salary * (1 + 0.2) 'new salary'
FROM employees;

# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name, LENGTH(last_name)
FROM employees
ORDER BY last_name ASC;

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id, ',', last_name, ',', salary) 'OUT_PUT'
FROM employees;

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT last_name, DATEDIFF(NOW(), hire_date) / 365 work_years, DATEDIFF(SYSDATE(), hire_date)
FROM employees
ORDER BY work_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 YEAR(hire_date) >= 1997 AND department_id IN (80, 90, 110) AND
commission_pct IS NOT NULL;
WHERE DATE_FORMAT(hire_date, '%Y') >= '1997' AND department_id IN (80, 90, 110) AND
commission_pct IS NOT NULL;



# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name, hire_date
FROM employees
WHERE DATEDIFF(NOW(), hire_date) >= 10000;

# 8.做一个查询，产生下面的结果
SELECT job_id 'job', 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;