MySQL函数查询

目录

常见函数

一、字符函数

二、数学函数

三、日期和时间函数

四、流程控制函数

---

常见函数

功能：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1.隐藏了实现细节 2.提高代码的重用性
调用：select 函数名(实参列表)【from 表】；
特点：
    ①叫什么（函数名）
    ②干什么（函数功能）
分类：
    1.单行函数
    如concat、length、ifnull等
    2.分组函数
    
    功能：做统计使用，又称为统计函数、聚合函数、组函数

一、字符函数

1.length 获取数值的字节个数

SELECT LENGTH('john');
SELECT LENGTH('hhhh');

SHOW VARIABLES LIKE '%char%';

2.concat拼接字符串

SELECT CONCAT(last_name,'_',first_name) AS 姓名 FROM employees;
SELECT CONCAT_WS('-','hello','world');

3.upper、lower
实例：姓变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名
FROM employees;

4.substr、substring(索引从1开始)

截取从指定索引的全部字符

SELECT SUBSTR('遇见你的时候所有星星都落到我头上',7) AS out_put;

截取从指定索引处指定字符长度的字符

SELECT SUBSTR('哈哈哈哈哈哈哈哈哈',1,3) AS out_put;

案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) AS out_put
FROM employees;

5.instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('周生辰爱上了漼时宜','漼时宜') AS out_put;

6.trim

SELECT TRIM('             hhhh       xixi    ') AS out_put;
SELECT TRIM('aa'FROM'aaaabbbaacccaaaaaaaa') AS out_put;

7.lpad 用指定的字符实现左填充指定长度

SELECT LPAD('殷素素',2,'*') AS out_put;

8.rpad 用指定的字符实现右填充指定长度

SELECT RPAD('殷素素',5,'*') AS out_put;

9.replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

10.insert替换指定位置

SELECT INSERT('helloworld',2,3,'aaaa');

11.nullif(如果两个数相等，返回null，否则返回s1)
 

SELECT NULLIF(14,13);

二、数学函数

round 四舍五入

SELECT ROUND(1.99);
SELECT ROUND(2.569,2);

ceil 向上取整，返回>=该参数的最小整数

SELECT CEIL(-1.09);

floor 向下取整，返回<=该参数的最大整数

SELECT FLOOR(-2.06);

truncate截断

SELECT TRUNCATE(1.699999999999,2);

mod取余

MOD(a,b) -- a-a/b*b
SELECT MOD(10,-3);

取随机数

SELECT RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

角度与弧度的互换

radians:将角度转换为弧度
degrees:将弧度转换为角度

SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90),
DEGREES(2*PI()),DEGREES(RADIANS(60))

三角函数

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

指数和对数

SELECT POW(2,5),POWER(2,4),EXP(2)
FROM DUAL;

字符串函数

SELECT ASCII('abcdef'),LENGTH('hello'),CHAR_LENGTH('我们'),
LENGTH('我们')
FROM DUAL;

三、日期和时间函数

3.1 获取日期，时间

SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),
UTC_DATE(),UTC_TIME();

3.2日期与时间戳的转换

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2021-11-22 12:12'),
FROM_UNIXTIME(1637507201);

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

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) 
FROM DUAL;

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),
WEEKDAY('2021-10-26'), QUARTER(CURDATE()),WEEK(CURDATE()),
DAYOFYEAR(NOW()), DAYOFMONTH(NOW()),DAYOFWEEK(NOW()) 
FROM DUAL;

3.4日期的操作函数

SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2021-11-22')
FROM DUAL;

3.5 时间和秒钟转换的函数

SELECT TIME_TO_SEC(CURTIME()),
SEC_TO_TIME(60418)
FROM DUAL;

3.6 计算时间和日期的函数

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR),
DATE_ADD(NOW(),INTERVAL -1 YEAR)
FROM DUAL;

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY) AS col1,
DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2, 
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;

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),
DATEDIFF(NOW(),'2021-10-01'), TIMEDIFF(NOW(),'2021-10-25 22:10:10'),
FROM_DAYS(366),TO_DAYS('0000-12-25'), LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),32),
MAKETIME(10,21,23),PERIOD_ADD(20200101010101, 10) 
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;

解析：格式化的逆过程

四、流程控制函数

4.1 if(value,value1,value2)

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

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0),
salary * 12 *(1+IF(commission_pct IS NOT NULL,commission_pct,0))
FROM employees;

4.2 ifnull(value1,value2):看做是if(value,value1,value2)的特殊情况

SELECT last_name,commission_pct,IFNULL(commission_pct,0)
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('mysql')
FROM DUAL;

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

6.mysql信息函数

SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷')
FROM DUAL;

7.其他函数

SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;

benchmark()用于测试表达式的执行效率

SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;

convert():可以实现字符集的转换

SELECT CHARSET('hhhh'),CONVERT('hhhh'USING'utf8mb4')
FROM DUAL;