MySQL学习笔记——函数

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函数

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

IF()函数

# 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()函数

# 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()函数

# 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的最小整数值

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

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

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

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

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()函数生成随机数

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

# 返回当前
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

# 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

# 聚合函数，也叫组合函数，忽略空值
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 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----+