MySQL数据库函数详解

我就是我 2023-10-05 16:28 11阅读 0赞

#### 文章目录 ####

*  一、数据库函数
 *   *  1.1 数学函数
     *  1.2 聚合函数
     *  1.3 字符串函数
     *  1.4 日期时间函数
     *  1.5 存储过程
     *   *  1.5.1 存储过程的优点
     *  1.5.2 创建存储过程
     *   *  1.5.3 修改、删除存储过程
 *  二、视图
 *   *  2.1 概念
 *  2.2 创建视图
 *   *  2.3 查询视图

## 一、数据库函数 ##

*  MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。
 *  MySQL 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。

### 1.1 数学函数 ###

数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。常用的数学函数如下表 所示。

<table> 
 <thead> 
  <tr> 
   <th>数学函数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>abs(x)</td> 
   <td>返回 x 的绝对值</td> 
  </tr> 
  <tr> 
   <td>rand()</td> 
   <td>返回 0 到 1 的随机浮点数</td> 
  </tr> 
  <tr> 
   <td>mod(x,y)</td> 
   <td>返回 x 除以 y 以后的余数</td> 
  </tr> 
  <tr> 
   <td>power(x,y)</td> 
   <td>返回 x 的 y 次方</td> 
  </tr> 
  <tr> 
   <td>round(x)</td> 
   <td>返回离 x 最近的整数</td> 
  </tr> 
  <tr> 
   <td>round(x,y)</td> 
   <td>保留x 的y 位小数四舍五入后的值</td> 
  </tr> 
  <tr> 
   <td>sqrt(x)</td> 
   <td>返回 x 的平方根</td> 
  </tr> 
  <tr> 
   <td>truncate(x,y)</td> 
   <td>返回数字 x 截断为 y 位小数的值</td> 
  </tr> 
  <tr> 
   <td>ceil(x)</td> 
   <td>返回大于或等于 x 的最小整数</td> 
  </tr> 
  <tr> 
   <td>floor(x)</td> 
   <td>返回小于或等于 x 的最大整数</td> 
  </tr> 
  <tr> 
   <td>greatest(x1,x2…)</td> 
   <td>返回集合中最大的值</td> 
  </tr> 
  <tr> 
   <td>least(x1,x2…)</td> 
   <td>返回集合中最小的值</td> 
  </tr> 
 </tbody> 
</table>

### 1.2 聚合函数 ###

*  MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。(aggregate function)

<table> 
 <thead> 
  <tr> 
   <th>聚合函数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>avg()</td> 
   <td>返回指定列的平均值(average)</td> 
  </tr> 
  <tr> 
   <td>count()</td> 
   <td>返回指定列中非 NULL 值的个数</td> 
  </tr> 
  <tr> 
   <td>min()</td> 
   <td>返回指定列的最小值</td> 
  </tr> 
  <tr> 
   <td>max()</td> 
   <td>返回指定列的最大值</td> 
  </tr> 
  <tr> 
   <td>sum(x)</td> 
   <td>返回指定列的所有值之和</td> 
  </tr> 
 </tbody> 
</table>

### 1.3 字符串函数 ###

<table> 
 <thead> 
  <tr> 
   <th>字符串函数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>length(x)</td> 
   <td>返回字符串 x 的长度</td> 
  </tr> 
  <tr> 
   <td>trim()</td> 
   <td>返回去除指定格式的值</td> 
  </tr> 
  <tr> 
   <td>concat(x,y)</td> 
   <td>将提供的参数 x 和 y 拼接成一个字符串</td> 
  </tr> 
  <tr> 
   <td>upper(x)</td> 
   <td>将字符串 x 的所有字母变成大写字母</td> 
  </tr> 
  <tr> 
   <td>lower(x)</td> 
   <td>将字符串 x 的所有字母变成小写字母</td> 
  </tr> 
  <tr> 
   <td>left(x,y)</td> 
   <td>返回字符串 x 的前 y 个字符</td> 
  </tr> 
  <tr> 
   <td>right(x,y)</td> 
   <td>返回字符串 x 的后 y 个字符</td> 
  </tr> 
  <tr> 
   <td>repeat(x,y)</td> 
   <td>将字符串 x 重复 y 次</td> 
  </tr> 
  <tr> 
   <td>space(x)</td> 
   <td>返回 x 个空格</td> 
  </tr> 
  <tr> 
   <td>replace(x,y,z)</td> 
   <td>将字符串 z 替代字符串 x 中的字符串 y</td> 
  </tr> 
  <tr> 
   <td>strcmp(x,y)</td> 
   <td>比较 x 和 y，返回的值可以为-1,0,1</td> 
  </tr> 
  <tr> 
   <td>substring(x,y,z)</td> 
   <td>获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串</td> 
  </tr> 
  <tr> 
   <td>reverse(x)</td> 
   <td>将字符串 x 反转</td> 
  </tr> 
 </tbody> 
</table>

trim函数可以过滤指定的字符串：

完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
    简化格式：TRIM([remstr FROM] str)

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

代码如下：

mysql> SELECT TRIM(' bar '); //默认删除前后空格 
    -> 'bar' 
    mysql> SELECT TRIM(LEADING '，' FROM '，，barxxx'); //删除指定首字符 如'，‘ 
    -> 'barxxx' 
    mysql> SELECT TRIM(BOTH '，' FROM '，，bar，，，'); //删除指定首尾字符 
    -> 'bar' 
    mysql> SELECT TRIM(TRAILING '，' FROM 'barxxyz，，'); 
    -> 'barxxyz'

### 1.4 日期时间函数 ###

*  MySQL 也支持日期时间处理，提供了很多处理日期和时间的函数。

<table> 
 <thead> 
  <tr> 
   <th>字符串函数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>curdate()</td> 
   <td>返回当前时间的年月日</td> 
  </tr> 
  <tr> 
   <td>curtime()</td> 
   <td>返回当前时间的时分秒</td> 
  </tr> 
  <tr> 
   <td>now()</td> 
   <td>返回当前时间的日期和时间</td> 
  </tr> 
  <tr> 
   <td>month(x)</td> 
   <td>返回日期 x 中的月份值</td> 
  </tr> 
  <tr> 
   <td>week(x)</td> 
   <td>返回日期 x 是年度第几个星期</td> 
  </tr> 
  <tr> 
   <td>hour(x)</td> 
   <td>返回 x 中的小时值</td> 
  </tr> 
  <tr> 
   <td>minute(x)</td> 
   <td>返回 x 中的分钟值</td> 
  </tr> 
  <tr> 
   <td>second(x)</td> 
   <td>返回 x 中的秒钟值</td> 
  </tr> 
  <tr> 
   <td>dayofweek(x)</td> 
   <td>返回 x 是星期几，1 星期日，2 星期一</td> 
  </tr> 
  <tr> 
   <td>dayofmonth(x)</td> 
   <td>计算日期 x 是本月的第几天</td> 
  </tr> 
  <tr> 
   <td>dayofyear(x)</td> 
   <td>计算日期 x 是本年的第几天</td> 
  </tr> 
 </tbody> 
</table>

**示例1：**

mysql> select month(curdate());
    +------------------+
    | month(curdate()) |
    +------------------+
    |               12 |
    +------------------+
    1 row in set (0.00 sec)

**示例2：**

mysql> select week(now());
    +-------------+
    | week(now()) |
    +-------------+
    |          52 |
    +-------------+
    1 row in set (0.00 sec)

**示例3：**

mysql> select dayofmonth(now());
    +-------------------+
    | dayofmonth(now()) |
    +-------------------+
    |                28 |
    +-------------------+
    1 row in set (0.01 sec)

### 1.5 存储过程 ###

*  在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求。
 *  MySQL 数据库存储过程是一组为了完成特定功能的 SQL 语句的集合。存储过程这个功能是从 5.0 版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。
 *  存储过程在使用过程中是将常用或者复杂的工作预先使用 SQL 语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。
 *  当需要使用该存 储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高。
 *  存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。
 *  存储过程的应用范围很广，例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。如果了解一些编程语言，可能会发现存储过程更像是面向对象方法的模拟。

#### 1.5.1 存储过程的优点 ####

*  存储过程执行一次后，生成的二进制代码就驻留在缓冲区，之后如果再次调用的话，将 直接调用二进制代码，使得存储过程的执行效率和性能得到大幅提升。
 *  存储过程是 SQL 语句加上控制语句的集合，有很强的灵活性，可以完成复杂的运算。
 *  存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调 用语句，从而可以降低网络负载。
 *  存储过程被创建后，可以多次重复调用，它将多条 SQL 封装到了一起，可随时针对 SQL语句进行修改，不影响调用它的客户端.
 *  存储过程可以完成所有的数据库操作，也可以通过编程的方式控制数据库的信息访问权 限。

### 1.5.2 创建存储过程 ###

使用 **CREATE PROCEDURE** 语句创建存储过程，其语法格式如下所示。

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
    [过程参数[,…] ] 格式
    [ IN | OUT | INOUT ] <参数名><类型>

*  **存储过程名称**

存储过程的名称应该尽量避免选取与 MySQL 内置的函数或者字段相同的名称，否则会发生错误。

存储过程可以添加参数，具有自己的参数列表。参数包括参数名和其对应的类型。 存在多个参数时，参数列表之间用逗号进行分隔。创建存储过程的时候可以不使用参数，但是括号要存在，也可以有一个或多个参数。

*  **MySQL参数:**

MySQL 的参数分为：输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字表示。其中，输入参数可以传递给一个存储过程；输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。

此外，存储过程的主体部分，被称为过程体，包含了在调用时必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始，以关键字 END 结束。若过程体中只有一条 SQL 语句，则可以省略 BEGIN-END 标志。

**in、out、inout参数**

mysql> use school;
    mysql> set @num1=1,@num2=2,@num3=3;
    mysql> delimiter $$
    mysql> create procedure test(in num1 int,out num2 int,inout num3 int)
        -> begin
        -> select num1,num2,num3;
        -> set num1=10,num2=20,num3=30;
        -> select num1,num2,num3;
        -> end $$
    mysql> delimiter ;
    mysql> call test(@num1,@num2,@num3);
    +------+------+------+
    | num1 | num2 | num3 |
    +------+------+------+
    |    1 | NULL |    3 |
    +------+------+------+
    1 row in set (0.00 sec)
    
    +------+------+------+
    | num1 | num2 | num3 |
    +------+------+------+
    |   10 |   20 |   30 |
    +------+------+------+
    1 row in set (0.00 sec)

**总计1：** in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中。在存储过程使用过程中，参数值in、out、inout都会发生改变。

mysql> select @num1,@num2,@num3;
    +-------+-------+-------+
    | @num1 | @num2 | @num3 |
    +-------+-------+-------+
    |     1 |    20 |    30 |
    +-------+-------+-------+
    1 row in set (0.00 sec)

**总结2：** 调用完存储过程后，发现in参数不会使全局变量值发生改变，而out和inout参数调用完存储过程后，会使全局变量的值改变。

*  **DELIMITER**

在存储过程的创建过程中，会用到 DELIMITER 命令。因为在 MySQL 中，服务器处理SQL 语句默认是以分号作为语句结束标志的，过程体中由多条 SQL 语句构成，每条 SQL 后面都是分号结尾，那么 MySQL 服务器在处理时遇到第一条 SQL 语句就会结束整个过程， 不再去处理后面的 SQL 语句。为了解决这个问题，在创建存储过程时，使用 DELIMITER 命令。

1.  定义存储过程

delimiter $$
    create procedure 存储过程名(in 参数名 参数类型)
    begin
    #定义变量
    declare变量名变量类型
    #变量赋值
    set 变量名 = 值
    sql语句1;
    sql语句2;
    ..
    sql语句n;
    end $$
    delimiter ;

1.  调用存储过程

call存储过程名(实际参数);

1.  查询存储过程

show procedure status where db='数据库";

1.  删除存储过程

drop procedure存储过程名;

#### 1.5.3 修改、删除存储过程 ####

**修改：**

*  存储过程的修改分为特征的修改和业务 内容的修改。特征的修改可以使用 ALTER PROCEDURE 来实现

ALTER PROCEDURE <过程名> [ <特征> … ]

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

**删除：**

*  使用 DROP PROCEDURE 语句即可删除存储过程

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

在删除时存储过程的名字是放到最后的，前面可以添加 **IF EXISTS** 这个关键字，其主要作用是防止因删除不存在的存储过程而引发的错误。删除存储过程的具体操作如下所示。

mysql> drop procedure myupdate;
    Query OK, 0 rows affected (0.00 sec)

mysql> call myupdate;
    ERROR 1305 (42000): PROCEDURE school.myupdate does not exist

需要注意的是：存储过程名称后面没有参数列表，也没有括号。在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致与之关联的存储过程无法运行。

## 二、视图 ##

**特点**

*  安全性高
 *  简化sql操作
 *  可以针对不同用户创建不同的视图（不同权限的用户浏览不同的信息）

### 2.1 概念 ###

*  视图是一张虚拟的表，数据不存在视图中，视图是真实表的映射数据。例如：水中捞月是典型的视图
 *  利用条件筛选，分组，排序等产生出一个结果集（结果集保存在内存中），并且做成持久化保存（保存映射）
 *  视图占用资源小，真实表中数据产生变化，会影响到视图。

## 2.2 创建视图 ##

*  创建视图

create view <视图名称>
    create view info_view as select id,name,age from info 条件
    多表相连的条件是on
    单表相连的条件是where

*  创建多表相连查询视图

create view info_hob_view as select info.id,info.name,info.age,info,score,info.addr,hob.hobname from info inner join hob on info.hobby=hob.id;	'//将刚刚的多表相连查询创建名为info_hob_view的视图'
    select * from info_hob_view;	'//查看视图'

### 2.3 查询视图 ###

*  查询视图

select * from info_view;
    select name,age from info_view;

*  查询多表相连

select info.id,info.name,info.age,info,score,info.addr,hob.hobname from info inner join hob on info.hobby=hob.id; '//从info表和hob表中选择info.id,info.name,info.age,info,score,info.addr,hob.hobname且info表中的hobby=hob表中的id'
    '//inner join：内部相连'