(七)运算符与函数


1、字符函数

函数名称 | 描述
----|------|----
CONCAT() | 字符连接
CONCAT_WS() | 使用指定的分隔符进行字符连接
FORMAT() | 数字格式化
LOWER() | 转换成小写字母
UPPER() | 转换成大写字母
LEFT() | 获取左侧字符
RIGHT() | 获取右侧字符
LENGTH() | 获取字符串长度
LTRIM() | 删除前导空格
RTRIM() | 删除后续空格
TRIM() | 删除前导和后续空格
SUBSTRING() | 字符串截取
[NOT] LIKE() | 模式匹配
REPLACE() | 字符串替换

函数CONCAT()

创建数据表“name”并插入记录:


查看该表数据:


要求查询的结果同时输出“first_name”与“last_name”:


使用“concat()”连接,并赋予别名:


函数CONCAT_WS()

函数CONCAT_WS()要求至少有3个参数,第一个是参数是指定的分隔符,例如“.”,后续的参数是要连接的数据:

函数FORMAT()

函数FORMAT()可以将数字格式化为字符型,“,”之后的数字为保留到几位小数:

函数LOWER()、UPPER()

函数LOWER()可以将数据转化为纯小写字母:

函数UPPER()可以将数据转化为纯大写字母:


函数LEFT()、RIGHT()

函数LEFT()参数中“,”的数字为从左侧获取几位字符:

函数RIGHT()参数中“,”的数字为从右侧获取几位字符,还可以进行函数的嵌套,将获取的字符进行小写转化:


函数LENGTH()

函数LENGTH()可以获取字符串的长度,注意空格也算在长度之内:

函数LTRIM()、RTRIM()、TRIM()

函数LTRIM()可以将第一个字符前的空格删除:

函数RTRIM()可以将最后一个字符后的空格删除:


函数TRIM()可以将字符前后的空格都删除:


除此之外,函数TRIM()还可以删除特定字符,例如使用“LEADING”删除前导特定字符:

使用“TRAILING”删除后续特定字符:


使用“BOTH”删除前导及后续特定字符:


函数SUBSTRING()

函数SUBSTRING()可以截取字符串,参数分别是要截取的内容,从第几位开始截取,截取几位;要注意的是MySQL是从1开始,与Java从0开始不同:

如果只注明从第几位开始截取,那么SUBSTRING()会一直截取到字符串的结尾:

SUBSTRING()的起始位置参数还可以是负值,代表从末尾开始倒数的第几位;但要注意,截取的长度不能是负值,否则结果为空:

函数[NOT] LIKE()

函数LIKE()可以模糊匹配,使用通配符“_”或“%”,“_”指任意一个字母;“%”指任意大于0个字母:

查看数据表“user”中的详细记录:

例如查找姓名第4个字母为“e”的用户记录:


每一条“_”代表一位字母
每一条“_”代表一位字母

例如查找姓名中含有字母“o”的用户记录:


“%”代表不限制字母数量
“%”代表不限制字母数量

查看数据表“name”中的详细记录:


查询带有“%”符号的记录:


结果出现了未带有“%”的记录,原因是系统将3个“%”全部认为是通配符,因此,可以在中间的“%”前加转义字符“\”,使系统认为其只是普通的“%”符号,没有特殊意义:


函数REPLACE()

之前的LTRIM()、RTRIM()、TRIM()只能对字符之前或之后的空格进行处理,而对于字符中间的空格则无能为力,对此可以使用REPLACE()进行替换,其参数分别是待处理的字符串,要替换的字符,替换后的字符:


而且要替换的字符及替换后的字符数量并没有限制:



2、数值运算符与函数

数值运算符即常见的:+、-、x、/等等,这里不做详细的演示,而函数如下表所示:

函数名称 | 描述
----|------|----
CEIL() | 进一取整
DIV | 整数除法
FLOOR() | 舍一取整
MOD | 取余数
POWER() | 幂运算
ROUND() | 四舍五入
TRUNCATE() | 数字截取

函数CEIL()、FLOOR()

只要小数点后有位数,函数CEIL()就向上取整;而函数FLOOR()则刚好相反,会向下取整:


函数DIV

函数DIV进行整数除法,结果只保留到整数:


函数MOD

函数MOD进行取余数与取模相同,对于整数或浮点数都适用:


函数POWER()

函数POWER()即幂运算,例如求2的12次方:


函数ROUND()

函数ROUND()就是常见的四舍五入,参数分别是待处理的数据以及要保留几位小数,例如对3.652分别保留2位、1位以及取整:


函数TRUNCATE()

函数TRUNCATE()即数字截断,参数分别是待处理的数据以及要保留几位小数,与ROUND()不同,TRUNCATE()会直接去掉被截断的数字,例如对3.652分别保留2位、1位以及取整:


最后的“-1”会在将小数点后所有位数都截掉的基础上,再将小数点前1位进行清零操作
最后的“-1”会在将小数点后所有位数都截掉的基础上,再将小数点前1位进行清零操作

3、比较运算符与函数

函数名称 | 描述
----|------|----
[NOT] BETWEEN……AND…… | [不]在……范围之内
[NOT] IN() | [不]在列出值范围内
IS [NOT] NULL | [不]为空

函数[NOT] BETWEEN……AND……

查看数据表“user”中的详细记录:

查找年龄在14岁至28岁之间的用户记录:


函数[NOT] IN()

函数[NOT] IN()括号中的内容就是列出值的范围:


“1”代表TRUE,“0”代表FALSE
“1”代表TRUE,“0”代表FALSE

函数IS [NOT] NULL

查看数据表“name”:


查找姓名为空的记录:



4、日期时间函数

函数名称 | 描述
----|------|----
NOW() | 当前时间和日期
CURDATE() | 当前日期
CURTIME() | 当前时间
DATE_ADD() | 日期变化
DATEDIFF() | 日期差值
DATE_FORMAT() | 日期格式化

函数NOW()、CURDATE()、CURTIME()

函数NOW()返回当前查询时的日期及时间,而函数CURDATE()、CURTIME()则是分别显示日期或时间:


函数DATE_ADD()

函数DATE_ADD()可以对日期进行增减,参数为代修改的日期以及要增减的量,常用的关键词有YEAR、MONTH、WEEK、DAY等:



可用关键词
可用关键词

函数DATEDIFF()

函数DATEDIFF()可以计算日期之间差值,例如2016年6月28日距离现在相差多少:


函数DATE_FORMAT()

函数DATE_FORMAT()可以转换日期的格式:


可用说明符
可用说明符

5、信息函数

函数名称 | 描述
----|------|----
CONNECTION_ID() | 连接ID
DATABASE() | 当前数据库
LAST_INSERT_ID() | 最后插入记录的ID
USER() | 当前用户
VERSION() | 版本信息

函数CONNECTION_ID()

函数CONNECTION_ID()可以返回MySQL服务器的连接数,也就是到目前为止MySQL服务的连接次数,每个连接都有各自唯一的ID:


函数DATABASE()

函数DATABASE()显示当前数据库:


函数LAST_INSERT_ID()

函数LAST_INSERT_ID()需要有类似“ID”的自动编号字段:


因此对数据表“name”加以改造:


添加一条新记录,此时“id”编号自增应为5,查询最后插入记录的ID,结果确实是5:


如果一次插入多条记录,那么只会查询到多条记录中第一条插入记录的ID:


函数USER()、VERSION()

函数USER()、VERSION()分别显示登录的用户名及MySQL版本信息:



6、聚合函数

函数名称 | 描述
----|------|----
AVG() | 平均值
COUNT() | 计数
MAX() | 最大值
MIN() | 最小值
SUM() | 求和

聚合函数都只有一个返回值,下列演示都使用数据表“tdb_goods”:


函数AVG()

求所有商品的平均价格:


还可以使用函数ROUND()对其进行四舍五入,并保留2为小数:


函数COUNT()

求商品的总数量:


函数MAX()

求最高的商品价格:


函数MIN()

求最低的商品价格:


函数SUM()

求所有商品的总价格:



7、加密函数

函数名称 | 描述
----|------|----
MD5() | 信息摘要算法
PASSWORD() | 密码算法

函数MD5()

函数MD5()为计算机安全领域广泛使用的一种散列函数,用以提供消息的完整性保护:

结果共32位
结果共32位

  如果该数据库中的信息是用于日后的WEB应用,建议都使用MD5进行加密。

函数PASSWORD()

函数PASSWORD()更多的用于密码的计算:


结果共32位
结果共32位

例如修改MySQL用户的登录密码:


验证原密码能否登陆:


提示密码错误
提示密码错误

使用修改后的密码“root”进行登录:


成功进入MySQL界面
成功进入MySQL界面

8、运算符与函数汇总:

  • 字符函数

函数名称 | 描述
----|------|----
CONCAT() | 字符连接
CONCAT_WS() | 使用指定的分隔符进行字符连接
FORMAT() | 数字格式化
LOWER() | 转换成小写字母
UPPER() | 转换成大写字母
LEFT() | 获取左侧字符
RIGHT() | 获取右侧字符
LENGTH() | 获取字符串长度
LTRIM() | 删除前导空格
RTRIM() | 删除后续空格
TRIM() | 删除前导和后续空格
SUBSTRING() | 字符串截取
[NOT] LIKE() | 模式匹配
REPLACE() | 字符串替换

  • 数值运算符与函数

函数名称 | 描述
----|------|----
CEIL() | 进一取整
DIV | 整数除法
FLOOR() | 舍一取整
MOD | 取余数
POWER() | 幂运算
ROUND() | 四舍五入
TRUNCATE() | 数字截取

  • 比较运算符与函数

函数名称 | 描述
----|------|----
[NOT] BETWEEN……AND…… | [不]在……范围之内
[NOT] IN() | [不]在列出值范围内
IS [NOT] NULL | [不]为空

  • 日期时间函数

函数名称 | 描述
----|------|----
NOW() | 当前时间和日期
CURDATE() | 当前日期
CURTIME() | 当前时间
DATE_ADD() | 日期变化
DATEDIFF() | 日期差值
DATE_FORMAT() | 日期格式化

  • 信息函数

函数名称 | 描述
----|------|----
CONNECTION_ID() | 连接ID
DATABASE() | 当前数据库
LAST_INSERT_ID() | 最后插入记录的ID
USER() | 当前用户
VERSION() | 版本信息

  • 聚合函数

函数名称 | 描述
----|------|----
AVG() | 平均值
COUNT() | 计数
MAX() | 最大值
MIN() | 最小值
SUM() | 求和

  • 加密函数

函数名称 | 描述
----|------|----
MD5() | 信息摘要算法
PASSWORD() | 密码算法


版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓

推荐阅读更多精彩内容