（七）运算符与函数

2017-07-17 本文已影响65人黒猫

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位进行清零操作

3、比较运算符与函数

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

函数[NOT] BETWEEN……AND……

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

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

函数[NOT] IN()

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

“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位
　　如果该数据库中的信息是用于日后的WEB应用，建议都使用MD5进行加密。

函数PASSWORD()

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

结果共32位

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

验证原密码能否登陆：

提示密码错误

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

成功进入MySQL界面

8、运算符与函数汇总：

字符函数

数值运算符与函数