MySQL入门学习笔记

第七章：函数

7.1 函数的种类

定义：所谓函数，就是输入某一值得到相应输出的功能，输入值称为参数，输出值称为返回值。

函数大致可以分为以下几种。

  • 算数函数：用来进行数值计算的函数
  • 字符串函数：用来进行字符串操作的函数
  • 日期函数：用来进行日期操作的函数
  • 转换函数：用来转换数据类型和值的函数
  • 聚合函数：用来进行数据聚合的函数

7.2 算数函数

创建表

-- DDL ：创建表
CREATE TABLE SampleMath
(m NUMERIC (10,3),
 n INTEGER,
 p INTEGER);

-- 插入数据
INSERT INTO SampleMath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO SampleMath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO SampleMath(m, n, p) VALUES (8.76, NULL, NULL);

-- 查看数据
select * from SampleMath;


7.2.1 ABS 绝对值

-- 语法结构
select ABS(字段名)
from 表名;

-- 注意：当ABS()函数内的参数为NULL时，结果也是NULL。

举例：查询输出m字段和绝对之后的m字段

select m,abs(m)
from SampleMath;

7.2.2 MOD 求余

-- 语法结构
select mod(字段名1(被除数),字段名2(除数))
from 表名;

-- 注意：mod函数只能对整数类型的列使用，因为小数计数中没有余数的概念。

举例：计算n列和p列的余数

select n,p,mod(n,p) as 余数
from SampleMath;

7.2.3 ROUND 四舍五入

-- 语法结构
select round(字段名(对象数值),数字(保留小数的位数))
from 表名；

举例：对m列的数值进行小数点后2位数的四舍五入处理

select m,round(m,2)
from SampleMath;

7.2.4 CEIL 向上取整

-- 功能：返回大于该参数的最小正整数
-- 语法结构
select ceil(字段名)
from 表名；

举例：对m列的数值进行向上取整

select m,ceil(m)
from SampleMath;

7.2.5 FLOOR 向下取整

-- 功能：返回小于该参数的最大正整数
-- 语法结构
select floor(字段名)
from 表名;

举例：对m列的数值进行向下取整

select m,floor(m)
from SampleMath;

7.2.6 TRUNCATE截断

-- 功能：截断小数点后几位
-- 语法结构
select truncate(字段名,数字(截取小数点后几位))
from 表名;

举例：截断m列小数点后2位小数

select m,truncate(m,2)
from SampleMath;

7.3 字符串函数

创建表

-- DDL ：创建表
CREATE TABLE SampleStr
(str1 VARCHAR(40),
str2 VARCHAR(40),
str3 VARCHAR(40);
 
-- 插入数据
INSERT INTO SampleStr (str1, str2, str3) VALUES ('opx' ,
'rt',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc' ,
'def' ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('山田' , '太郎' ,'是我');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aaa' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES (NULL ,
'xyz',NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('@!#$%' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('ABC' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('aBC' ,
NULL ,NULL);
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abc太郎' ,
'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('abcdefabc' ,
'abc' ,'ABC');
INSERT INTO SampleStr (str1, str2, str3) VALUES ('micmic' ,
'i' ,'I');
 
 -- 查询数据
select * from SampleStr;

7.3.1 length 字符串长度

-- 语法结构
select length(字段名/"字符串")
from 表名/虚表(dual)

举例：查询Str1字段内每个字符串的长度

select length(str1)
from SampleStr;

7.3.2 concat 拼接

-- 语法结构
select concat(字段名1,'连接符号',字段名2)
from 表名；

-- null与字符字符串拼接后为空

举例：将str1和str2连接起来。

select str1,str2,concat(str1,'',str2)
from SampleStr;

7.3.2 upper 转化成大写

-- 语法结构
select upper(字段名)
from 表名

举例：将 hello world 转换成大写

select upper('hello world')
from dual;

7.3.3 lower 转化成小写

-- 语法结构
select lower(字段名)
from 表名

举例：将 HELLO WORLD转化成小写

select lower("HELLO WORLD")
from dual;

7.3.4 substr 截取

-- 语法结构
select substr(字段名/字符串,开始的索引,截取的长度)
from 表名;

举例：截取出str1中第3位和第4位的字符

select substr(str1,3,2)
from SampleStr;

7.3.5 instr 获取索引

-- 功能：用来返回子串在字符串中出现的其实索引
-- 语法结构
select instr(字段名/字符串,子串)
from 表名;

举例：将“数据分析师必须掌握mysql”中的m其实索引取出来

select instr('数据分析师必须掌握mysql','m') as 起始索引 
from dual;

7.3.6 replace 替换

-- 语法结构
select replace(字段名/字符串,被替换的值,替换后的值)
from 表名;

举例:将 life is short,I love python 中的python改成mysql

select replace('life is short,I love python','python','mysql')
from dual;

7.3.7 trim 去除空格

-- 语法结构
select trim(字段名/字符串)
from 表名;

举例：去除 “ 你好 ”字符串中的空格

select trim("          你好            ")
from dual;

7.4 日期函数

7.4.1 now 返回当前的系统时间+日期

-- 语法结构
select now() as 时间日期 
from dual;

7.4.2 curdate 返回当前的系统日期

-- 语法结构
select curdate() as 日期 
from dual;

7.4.3 curtime 返回当前的时间

-- 语法结构
select curtime() as 时间 
from dual;

7.4.4 year 获取日期的年份

-- 语法结构
select year(日期字段/日期) 
from 表名;

举例：获取当前日期的年份

select year(now())
from dual;

7.4.5 month 获取月份

-- 语法结构
select month(日期字段/日期) 
from 表名;

举例：获取当前日期的月份

select month(now()) 
from dual;

7.4.6 day 获取日

-- 语法结构
select day(日期字段/日期) 
from 表名;

举例：获取当前日期的日

select day(now()) 
from dual;

7.4.7 获取时间（时、分、秒）的函数

-- hour:获取小时
-- minute:获取分钟
-- second:获取秒

-- 语法结构 以小时举例(分和秒与其类似)
select hour(时间字段/时间)
from 表名;

举例：获取现在时间为几时

select hour(now())
from dual;

7.4.8 date_format 函数

序号

格式符

功能

1

%Y

四位的年份

2

%y

2位的年份

3

%m

2位的月份

4

%c

1位的月份

5

%d

日

6

%H

小时,24小时制

7

%h

小时,12小时制

8

%i

分钟

9

%s

秒

-- 语法结构
select date_format(日期字段/日期,格式符)
from 表名;

举例:将系统时间格式化为:xxxx年xx月xx日的形式

select date_format(now(),'%Y年%m月%d日') as 日期 
from dual;