初涉MySQL操作（三）

1 运算符和函数

1.1 字符函数

函数名称	描述
CONCAT()	字符连接
CONCAT_WS()	使用指定的分隔符进行字符连接
FORMAT()	数字格式化
LOWER()	转换为小写字母
UPPER()	转换为大写字母
LEFT()	获取左侧字符
RIGHT()	获取右侧字符

• CONCAT()和CONCAT_WS()都是将字符连接：
  
  • CONCAT()将括号中给的多个参数连接到一块
    
    如在数据表中我们需要讲几个字段连接到一起：
    
  • CONCAT_WS()将多个字符按照括号中得的第一个参数连接在一起
    
    将字符A,B,C用“|”连接到一起
• 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