MySQL学习笔记——函数

目录

• 1.内置函数和运算符
  • 1.常用函数
  • 5.流程控制函数
  • 6.数值函数和操作符
  • 7.日期和时间函数
  • 8.字符串函数和操作符
  • 19.聚合函数

---

MySQL函数官方文档可以参考：https://dev.mysql.com/doc/refman/5.7/en/functions.html

回到顶部

1.内置函数和运算符

参考：https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html

1.常用函数

5.流程控制函数

参考：https://dev.mysql.com/doc/refman/5.7/en/flow-control-functions.html

CASE函数

?

1 2 3 4 5 6 7

# 条件判断语句 SELECT NAME,sex,age '原来年龄'     CASE     WHEN age IS NULL THEN 100     ELSE age     END AS '年龄' FROM tb_emp;

IF()函数

?

1 2 3 4 5 6 7

# IF(expr1,expr2,expr3)，如果expr1是True，返回expr2，否则返回expr3 mysql> SELECT IF(1>2,2,3);         -> 3 mysql> SELECT IF(1<2,'yes','no');         -> 'yes' mysql> SELECT IF(STRCMP('test','test1'),'no','yes');         -> 'no'

IFNULL()函数

?

1 2 3 4 5 6 7 8 9

# IFNULL(expr1,expr2)，如果字expr1不为NULL，则返回expr1，如果为NULL，则返回expr2 mysql> SELECT IFNULL(1,0);         -> 1 mysql> SELECT IFNULL(NULL,10);         -> 10 mysql> SELECT IFNULL(1/0,10);         -> 10 mysql> SELECT IFNULL(1/0,'yes');         -> 'yes'

NULLIF()函数

?

1 2 3 4 5

# NULLIF(expr1,expr2)，如果expr1=expr2，则返回NULL，否则返回expr1 mysql> SELECT NULLIF(1,1);         -> NULL mysql> SELECT NULLIF(1,2);         -> 1

6.数值函数和操作符

参考：https://dev.mysql.com/doc/refman/5.7/en/numeric-functions.html

使用CEIL(x)函数/CEILING(x)函数返回不小于x的最小整数值

?

1 2 3 4

mysql> SELECT CEILING(1.23);         -> 2 mysql> SELECT CEILING(-1.23);         -> -1

使用FLOOR(x)函数返回不大于x的最大整数值

?

1 2	mysql> SELECT FLOOR(1.23), FLOOR(-1.23);         -> 1, -2

使用ROUND()函数保留N位小数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> SELECT ROUND(-1.23);         -> -1 mysql> SELECT ROUND(-1.58);         -> -2 mysql> SELECT ROUND(1.58);         -> 2 mysql> SELECT ROUND(1.298, 1);         -> 1.3 mysql> SELECT ROUND(1.298, 0);         -> 1 mysql> SELECT ROUND(23.298, -1);         -> 20 mysql> SELECT ROUND(.12345678901234567890123456789012345, 35);         -> 0.123456789012345678901234567890

使用RAND()函数生成随机数

?

1 2 3 4 5 6 7 8

mysql> select RAND() from user limit 3; +---------------------+ | RAND()              | +---------------------+ |  0.1310934062405428 | |  0.8610111650647699 | |  0.9117756373358663 | +---------------------+

7.日期和时间函数

参考：https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

# 返回当前 SELECT NOW();   # 查询时间是1981年 SELECT * FROM tb_emp WHERE YEAR(diredate) = 1981 AND MONTH(diredate) = 1982;   #插入时间 INSERT INTO tb_emp(NAME,sex,age,address,email,dept_id,diredate) VALUES('ZHOU','男',33,'香港','ZHOU@163.com',2,'1988-09-09');   INSERT INTO tb_emp(NAME,sex,age,address,email,dept_id,diredate) VALUES('CAI','女',30,'香港','CAI@163.com',2,NOW());

8.字符串函数和操作符

参考：https://dev.mysql.com/doc/refman/5.7/en/string-functions.html

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

# concat 连接字符串 SELECT CONCAT(NAME,sex) FROM tb_emp;   # UPPER 转换大写/LOWER 转换小写 SELECT UPPER(NAME) FROM tb_emp WHERE dept_id=1; SELECT LOWER(NAME) FROM tb_emp WHERE dept_id=1;     # 返回字符串长度 SELECT LENGTH(NAME) FROM tb_emp WHERE dept_id=1;   # 返回部分字符 SELECT SUBSTR(NAME,2,2) FROM tb_emp WHERE dept_id=1;   # 比较字符串，按顺序比较，相等返回0，如果第一个的字符小于第二个，返回-1，否则返回1 mysql> SELECT STRCMP('text', 'text2'); +-------------------------+ | STRCMP('text', 'text2') | +-------------------------+ |                      -1 | +-------------------------+ mysql> SELECT STRCMP('text', 'texu'); +------------------------+ | STRCMP('text', 'texu') | +------------------------+ |                     -1 | +------------------------+ mysql> SELECT STRCMP('text', 'texa'); +------------------------+ | STRCMP('text', 'texa') | +------------------------+ |                      1 | +------------------------+ mysql> SELECT STRCMP('text', 'texa1'); +-------------------------+ | STRCMP('text', 'texa1') | +-------------------------+ |                       1 | +-------------------------+

19.聚合函数

参考：https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions-and-modifiers.html

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

# 聚合函数，也叫组合函数，忽略空值 SELECT AVG(age) FROM tb_emp; SELECT SUM(age) FROM tb_emp; SELECT MAX(age) FROM tb_emp; SELECT MIN(age) FROM tb_emp; SELECT AVG(age) AS '平均年龄',SUM(age) AS '总年龄',MAX(age) AS '最高年龄',MIN(age) AS '最低年龄' FROM tb_emp WHERE dept_id=1;   # COUNT不统计null，统计的是行数/记录数 SELECT COUNT(*) FROM tb_emp SELECT COUNT(email) FROM tb_emp   # 不统计重复记录 SELECT COUNT(DISTINCT diredate) FROM tb_emp   # 分组统计 GROUP BY # 每个部门的平均年龄 SELECT dept_id,AVG(age) FROM tb_emp GROUP BY dept_id   SELECT dept_id,AVG(age),address FROM tb_emp GROUP BY dept_id,address   # 限定查询结果 HAVING 不能使用where,where子句中不可以使用函数 SELECT dept_id,AVG(age) FROM tb_emp GROUP BY dept_id HAVING AVG(age)>23 ORDER BY AVG(age) DESC;   # LIMIT 常用来分页 SELECT * FROM tb_emp LIMIT 5; #查询前5个记录 SELECT * FROM tb_emp LIMIT 5,10; #查询前6-10个记录   # group_concat，先聚合，再返回concat后的字符串 select username, group_concat(email),count(1) as cnt from user group by username having cnt > 1; # 输出 +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----+ | username     | group_concat(email)                                                                                                                                    | cnt | +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----+ | jshellshear0 | jshellshear0@prlog.org,jshellshear0@taobao.com                                                                                                         |   2 | | test         | ,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test,test@test |  16 | +--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----+