1 运算符和函数
1.1 字符函数
函数名称 | 描述 |
---|---|
CONCAT() | 字符连接 |
CONCAT_WS() | 使用指定的分隔符进行字符连接 |
FORMAT() | 数字格式化 |
LOWER() | 转换为小写字母 |
UPPER() | 转换为大写字母 |
LEFT() | 获取左侧字符 |
RIGHT() | 获取右侧字符 |
- CONCAT()和CONCAT_WS()都是将字符连接:
- CONCAT()将括号中给的多个参数连接到一块
如在数据表中我们需要讲几个字段连接到一起:
- CONCAT_WS()将多个字符按照括号中得的第一个参数连接在一起
将字符A,B,C用“|”连接到一起
- CONCAT()将括号中给的多个参数连接到一块
- FORMAT()将数字格式化,返回的是一个字符,将1234.821格式化到小数点后两位
- LEFT()或RIGHT()取字符的左或右侧的指定位数的字符
函数名称 | 描述 |
---|---|
LENGTH() | 获取字符串长度 |
LTRIM() | 删除前导(LEADING)空格 |
RTRIM() | 删除后续(TRAILING)空格 |
TRIM() | 删除前导和后续空格 |
SUBSTRAING() | 字符串截取 |
[NOT] LIKE | 模式匹配 |
REPLACE() | 字符串替换 |
- 删除前导空格后,长度变短
- 删除指定的前导字符
删除后续字符只需讲LEADING改为TRAILING,如果要前后都删除,只需改为BOTH
- 删除指定的前导字符
- 字符串中间的空格是无法使用TRIM方法删除的,可以使用REPLACE()方法,第一个参数为要处理的字符串,后两个参数分别为要被替换的字符和用来替换的字符
- SUBSTRING()方法的参数分别为要截取的字符串、截取的开始位置和截取的长度
- MySQL中索引是从1开始的
- 如果最后一个参数不写,则从起始位置一直截取到最后
- 也可以从后面向前,以负数计,-1表示最后一位
- [NOT] LIKE是模式匹配,%代表0个或多个字符
在名字中查找包含两个l的“ll”:
但如果插入一个记录,名字中包含%,然后查找包含“%”的名字:
在最后查找时,我们的想法是第一个%和最后一个%是通配符,中间的%是我们要查找的字符,但是结果却不正确,需要使用ESCAPE告知%不再作为通配符:
备注:“%”代表任意0个或多个字符,“_”代表任意一个字符
1.2 数值运算符和函数
函数名称 | 描述 |
---|---|
CEIL() | 进一取整 |
DIV | 整数除法 |
FLOOR() | 舍一取整 |
MOD | 取余数(取模) |
POWER() | 幂运算 |
ROUND() | 四舍五入 |
TRUNCATE() | 数字截取 |
进一取整 | 整数除法 | MOD可以对整数或浮点数取余 | 数字截取 |
---|---|---|---|
TRUNCATE()只做简单的截取,从小数点开始,小数点后从0开始向后记,小数点前从-1开始记
1.3 比较运算符和函数
函数名称 | 描述 |
---|---|
[NOT] BETWEEN…AND… | [不]在范围(闭区间)之内 |
[NOT] IN() | [不]在列出值范围内 |
IS [NOT] NULL | [不]为空 |
前后都是闭的 | 给的数轴上的具体的点 | 在数据表中判断记录的某个字段(不)是空 |
---|---|---|
1.4 日期时间函数
函数名称 | 描述 |
---|---|
NOW() | 当前的日期和时间 |
CURDATE() | 当前日期 |
CURTIME() | 当前时间 |
DATE_ADD() | 日期变化 |
DATEDIFF() | 日期差值 |
DATE_FORMAT() | 日期格式化 |
获取当前日期和时间 | DATE_ADD()参数:要变化的日期,如何变化(负数为减少正数增加)和单位 |
---|---|
计算两个日期的差值 | 将一个日期格式转换为另一种格式 |
1.5 信息函数
函数名称 | 描述 |
---|---|
CONNECTION_ID() | 连接ID |
DATABASE() | 当前数据库 |
LAST_INSERT_ID() | 最后插入记录的ID,在同时插入多条记录时,返回的是插入的第一条记录 |
USER() | 当前用户 |
VERSION() | 版本信息 |
1.6 聚合函数
聚合函数只有一个返回值
函数名称 | 描述 |
---|---|
AVG() | 平均值 |
COUNT() | 计数 |
MAX() | 最大值 |
MIN() | 最小值 |
SUM() | 求和 |
- AVG()求平均,求商品的平均价格:
- COUNT()计数,计算总的商品数:
1.7 加密函数
函数名称 | 描述 |
---|---|
MD5() | 信息摘要算法 |
PASSWORD() | 密码算法 |
- 对任意字符加密
- PASSWORD主要用来给MySQL修改密码
- 数据库中的信息是为了WEB页面准备的,尽量的使用MD5()
2 自定义函数
- 用户自定义函数(user-defined function,UDF)是一种对MySQL扩展的途径,其用法与内置函数相同
- 自定义函数的两个必要条件:
- 参数
- 返回值
- 函数可以返回任意类型的值,同样可以接收这些类型的参数
- MySQL中可以最多接收1024个参数
创建自定义函数
CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routin_body
- 函数体由合法的SQL语句构成
- 函数体可以是简单的SELECT或INSERT语句
- 函数体如果为复合结构则使用BEGIN..END语句
- 复合结构可以包含声明,循环,控制结构
2.1 创建不带参数的自定义函数
- 创建一个函数,获得固定格式的日期:
- 使用getDate()函数:
2.2 创建带有参数的自定义函数
- 创建一个函数,计算两个数的平均值
2.3 创建具有符合结构函数体的自定义函数
查看数据表users:
创建一个函数,用于向数据表中插入记录并返回id,此时将遇到如下问题:
- 插入语句之后的分号会被认为是函数的结束
需要将结束符“;”修改为其它符号,如“#”:
- 函数中有两条语句执行,所以前后需要使用BEGIN和END来构成聚合体
所以正确的写法如下:
使用函数添加一条记录:
2.4 删除函数
DROP FUNCTION [IF EXISTS] function_name