tonglin0325的个人主页

MySQL学习笔记——函数

tonglin0325

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
8
# 条件判断语句
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
8
# 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
10
# 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
6
# 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
5
mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1

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

1
2
3
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
15
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
9
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
15
# 返回当前
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
40
# 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
40
# 聚合函数，也叫组合函数，忽略空值
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 |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----+

 

tonglin0325

标签

标签云

AWS Airbnb Android CDH Doris ELK Flink Git Grafana HAProxy HBase Hadoop Hive Hudi InfluxDB JDBC JMeter JVM Java JavaWeb LaTex Linkedin Linux ML ML Infra MPP Maven MySQL Nexus OpenTSDB Paper Play Prometheus Python React Redis RocksDB Scala Solr Spark SpringBoot SpringMVC Thrift YARN antlr arthas avro cassandra clickhouse confluent docker filebeat flume golang google gradle hexo impala jenkins k8s kafka kerberos kudu ldap mac mongo mybatis nginx nlp open-falcon openwrt parquet presto ranger scyllaDB twitter zookeeper 刷题 前端 图存储及计算 多线程 广告系统 开发工具 数据结构 杂谈 正则表达式 算法 系统设计 计算机网络 设计模式

文章归档

最近文章