使用程序方式获取与处理MySQL表数据

8.1  执行多条语句获取 MySQL 表数据

8.1.1  MySQL 中的常量

8.1.2  MySQL 中的变量

1．用户变量

用户可以在表达式中使用自己定义的变量，这样的变量称为用户变量。

用户变量在使用前必须定义和初始化，如果使用没有初始化的变量，其值为 Null。

用户变量与当前连接有关，也就是说，一个客户端定义的变量不能被其他客户端使用。定义和初始化一个用户变量可以使用 Set 语句，其语法格式如下 ：

Set @< 变量名称 1>=< 表达式 1> [ , @< 变量名称 2>=< 表达式 2> , … ] ;

定义和初始化用户变量的规则如下。

2．系统变量

MySQL 有一些特定的设置，当 MySQL 数据库服务器启动的时候，这些设置被读取来决定下一步骤，这些设置就是系统变量，系统变量在 MySQL 服务器启动时就被引入并初始化为默认值。

系统变量一般都以“@@”为前缀，例如 @@Version 返回 MySQL 的版本。但某些特定的系统变量可以省略“@@”符号，例如 Current_Date（系统日期）、Current_Time（系统时间）、Current_Timestamp（系统日期和时间）和 Current_User（当前用户名）。

系统变量可以分为全局系统变量和会话系统变量两种类型。

3．局部变量

局部变量是可以保存单个特定类型数据值的变量，其有效作用范围为存储过程和自定义函数的 Begin…End 语句块之内，在 Begin…End 语句块运行结束之后，局部变量就消失了，在其他语句块中不可以使用该局部变量，但 Begin…End 语句块内所有语句都可以使用。

MySQL 中局部变量必须先定义后使用。使用 Declare 语句声明局部变量，定义局部变量的语法格式如下 ：

8.1.3  MySQL 中的运算符与表达式

1．运算符

2．表达式

3．运算符的优先级

当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的次序有时会影响所得到的运算结果。MySQL 运算符优先级如表 8-1 所示。

8.1.4  MySQL 中的控制语句

Begin…End 语句用于将多个 SQL 语句组合为一个语句块（语句块相当于一个单一语句），以达到一起执行的目的。

2．If…Then…Else 语句

If…Then…Else 语句用于进行条件判断，可用于实现程序的选择结构。根据是否满足条件，将执行不同的语句，其语法格式如下 ：

If 语句的执行过程为 ：如果条件表达式的值为 True，则执行对应的语句块 ；如果所有的条件表达式的值为 False，并且有 Else 子句，则执行 Else 子句对应的语句块。

3．Case 语句

Case 语句用于计算列表并返回多个可能结果表达式中的一个，可用于实现程序的多分支结构，虽然使用 If…Then…Else 语句也能够实现多分支结构，但是使用 Case 语句的程序可读性更强，一条 Case 语句经常可以充当一条 If…Then…Else 语句。

4．While 循环语句

While 循环语句用于实现循环结构，是有条件控制的循环语句，当满足某种条件时执行循环体内的语句。

While 循环语句的执行过程说明如下：首先判断逻辑表达式的值是否为 True，为 True 时则执行“语句块”中的语句，然后再次进行判断，为 True 则继续循环，为 False 则结束循环。

5．Repeat 循环语句

Repeat 循环语句是有条件控制的循环语句，当满足特定条件时，就会跳出循环语句。

Repeat 循环语句的执行过程说明如下：首先执行语句块中的语句，然后判断逻辑表达式的值是否为 True，为 True 则停止循环，为 False 则继续循环。Repeat 语句也可以被标注。

8.1.5  MySQL 中的注释符

MySQL 注释符有以下 3 种。

【任务 8-1】在命令行窗口中定义用户变量并执行多条 SQL 语句

【任务描述】

在命令行窗口中编辑与执行多条 SQL 语句，实现以下功能。

（1）为用户变量 name 赋值“人民邮电出版社”。

（2）从数据表“出版社”中查询“人民邮电出版社”的“出版社 ID”字段的值，并且将该值存储在用户变量 id 中。

（3）从数据表“图书信息”中查询“人民邮电出版社”的图书种类数量，并且将其存储在用户变量 num 中。

（4）显示用户变量 name、id 和 num 的值。

【任务实施】

在命令提示符后输入以下语句 ：

Use MallDB ;

Set @name=" 人民邮电出版社 " ; -- 给变量 name 赋值

Set @id=( Select 出版社 ID From 出版社信息

   Where 出版社名称 = " 人民邮电出版社 " ) ; -- 给变量 id 赋值

Set @num=( Select Count(*) From 图书信息 Where 出版社 =@id ) ;

Select @name , @id , @num ;

8.2  使用存储过程和游标获取与处理 MySQL 表数据

8.2.1  MySQL 的存储过程

在 MySQL 中使用存储过程主要有以下优点。

2．Delimiter 命令

Delimiter 命令用于更改 MySQL 语句的结束符，例如将默认结束符“;”更改为“$$”，避免与 SQL 语句的默认结束符冲突。其语法格式如下 ：

Delimiter < 自定义的结束符 >

3．创建存储过程

创建存储过程的语法格式如下 ：

Create Procedure < 存储过程名 >( [ < 参数列表 > ] )

        [ < 存储过程的特征设置 > ]

        < 存储过程体 >

4．查看存储过程

查看存储过程状态的语法格式如下 ：

Show Procedure Status [ Like < 存储过程名的模式字符 > ] ;

5．调用存储过程

存储过程创建完成后，可以在程序、触发器或者其他存储过程中被调用，其语法格式如下：

Call < 存储过程名 >( [ < 参数列表 > ] ) ;

6．修改存储过程

可以使用 Alter Procedure 语句修改存储过程的某些特征，其语法格式如下 ：

7．删除存储过程

在命令行窗口中删除存储过程的语法格式如下 ：

Drop Procedure [ if exists ] < 存储过程名 > ;

8.2.2  MySQL 的游标

【任务 8-2】在命令行中创建存储过程查看指定出版社出版的图书种类

【任务描述】

在命令行窗口中创建存储过程 proc0501，其功能是从“图书信息”数据表中查看人民邮电出版社出版的图书种类。

【任务实施】

在命令行窗口中创建存储过程 proc0501

成功登录 MySQL 服务器后，在命令行提示符后输入以下语句 ：

Delimiter $$

Use MallDB ;

Create Procedure proc0501()

Begin

        Declare name varchar(16) ;

        Declare id int ;

        Declare num int ;

        Set name=" 人民邮电出版社 " ; -- 给变量 name 赋值

        Set id=(Select 出版社 ID From 出版社信息 Where 出版社名称 = name) ;

        Select Count(*) Into num From 图书信息 Where 出版社 =id ;

        Select name , id , num ;

End $$

Delimiter ;

【任务 8-3】在 Navicat for MySQL 中创建有输入参数的存储过程

【任务描述】

 Navicat for MySQL 中创建包含输入参数的存储过程 proc0503，其功能是根据输入参数 strName 的值（存储“出版社名称”）从“图书信息”数据表中查看对应出版社出版的图书种类。

【任务实施】

查看数据库 MallDB 中已有的存储过程

启动 Navicat for MySQL，在窗口左侧双击打开连接 MallConn，再双击打开数据库MallDB，然后在工具栏中单击【函数】按钮，此时可以看到数据库 MallDB 中已有的存储过程，如图 8-8 所示。

在存储过程的定义窗口中输入如下所示的 SQL 语句 ：

Begin

       Declare id int ;

       Declare num int ;

       If (strName Is Not Null) Then

             Set id=(Select 出版社 ID From 出版社信息 Where 出版社名称=strName) ;

             Select Count(*) Into num From 图书信息 Where 出版社 =id ;

       End If ;

       Select strName , id , num ;

End

SQL 语句编辑完成后，单击工具栏中的【保存】按钮，对存储过程“proc0503”进行保存，存储过程保存完成后，完整的存储过程定义如图 8-13 所示。

图 8-13  完整的存储过程定义

8.3  使用函数获取与处理 MySQL 表数据

8.3.1  MySQL 的内置函数

MySQL 包含了 100 多个内置函数，从数学函数到比较函数等，系统定义的内置函数如表 8-2 所示。

8.3.2  MySQL 的自定义函数

1．自定义函数概述

MySQL 的自定义函数与存储过程相似，都是由 SQL 语句和过程式语句组成的代码片段，并且可以被应用程序调用。

2．自定义函数的定义

创建自定义函数的语法格式如下所示 ：

Create Function < 函数名称 >( [< 输入参数名 > < 参数类型 > [ , … ] )

           Returns < 函数返回值类型 >

           [ < 函数的特征设置 > ]

           < 函数体 >

3．查看自定义函数

Show Function Status [ Like < 函数名的模式字符 > ] ;

4．修改自定义函数

修改函数是指修改已定义好的自定义函数，其语法格式如下 ：

Alter Function < 自定义函数名称 > [ < 函数的特征设置 > ] ;

5．删除自定义函数

删除自定义函数的语法格式如下 ：

Drop Function [ if exists ] < 自定义函数名称 > ;

【任务 8-4】在命令行窗口中创建自定义函数 getTypeName()

【任务描述】

在命令行窗口中创建一个自定义函数 getTypeName()，该函数的功能是从“商品类型”数据表中根据指定的“类型编号”获取“类型名称”。

【任务实施】

在命令行窗口中创建自定义函数 getTypeName()

在命令提示符后输入以下语句 ：

Delimiter $$

Create Function getTypeName( strTypeNumber varchar(9) )

          Returns Varchar(10)

Deterministic Begin

      Declare strTypeName varchar(10) ;

      If ( strTypeNumber Is Not Null) Then

         Select 类型名称 Into strTypeName From 商品类型

                    Where 类型编号 = strTypeNumber ;

      End If ;

      Return strTypeName ;

End $$

Delimiter ;

8.4  使用触发器获取与处理 MySQL 表数据

1．触发器概述

触发器是一种特殊的存储过程，它与数据表紧密相连，可以看作数据表定义的一部分，用于对数据表实施完整性约束。

存储过程可以使用 Call 命令调用，触发器的调用和存储过程不一样，触发器只能由数据库的特定事件来触发，并且不能接收参数。当满足触发器的触发条件时，数据库系统就会执行触发器中定义的程序语句。

2．创建触发器

MySQL 中创建触发器的语法格式如下：

Create Trigger < 触发器名称 > Before | After < 触发事件 >

          On < 数据表名称 >

          For Each Row

      < 执行语句 > ;

3．查看触发器

查看触发器是指查看数据库中已存在的触发器的定义、状态和语法信息等，可以使用SQL 语句来查看已经创建的触发器。

Select * From Information_Schema.Triggers Where Trigger_Name=< 触发器名 > ;

4．删除触发器

删除触发器的语法格式如下 ：

Drop Trigger [ < 数据库名 >.]< 触发器名 >

【任务 8-5】创建 Insert 触发器

【任务描述】

创建一个名为“order_insert”的触发器，当向“订单信息”数据表插入一条订单记录时，将用户变量 strInfo 的值设置为“在订单信息表中成功插入一条记录”。

【任务实施】

在命令行窗口中创建触发器 order_insert

在命令提示符后输入以下语句 ：

Delimiter $$

Create Trigger order_insert After Insert On 订单信息 For Each Row

Begin

       Set @strInfo= " 在订单信息表中成功插入一条记录 " ;

End $$

Delimiter ;

在 Triggers 数据表中查看触发器信息

在命令提示符后输入以下 Select 语句查看触发器信息 ：

Select Trigger_Name,Event_Manipulation,Event_Object_Schema , Event_Object_Table

         From Information_Schema.Triggers Where Trigger_Name="order_insert" ;

使用 Select 语句查看触发器信息的结果如图 8-19 所示。

应用触发器 order_insert

在命令提示符后直接输入以下语句查看用户变量 strInfo 的值，此时该变量的初始值为“0x”：

Select @strInfo ;

接下来，向“订单信息”数据表中插入一条记录，测试触发器 order_insert 是否会被触发。对应的语句如下 ：

Insert Into 订单信息 ( 订单编号 , 提交订单时间 , 订单完成时间 , 送货方式 , 客户 ,

                  收货人 , 付款方式 , 商品总额 , 运费 , 优惠金额 , 应付总额 , 订单状态 )

 Values("132577616584", "2020-10-25 11:13:08", "2020-10-28 15:31:12", " 京东快递 ", 2, " 陈芳 ", " 货到付款 ", 268.80, 0.00, 10.00, 258.80, " 已完成 ") ;

【任务 8-6】创建 Delete 触发器

【任务描述】

创建一个名为“commodityType_delete”的触发器，该触发器用于实现以下功能 ：限制用户删除“商品类型”数据表中的记录，当用户删除记录时抛出禁止删除记录的错误提示信息。

【任务实施】

在命令行窗口中创建触发器 commodityType_delete

在命令提示符后输入以下语句 ：

Delimiter $$

Create Trigger commodityType_delete Before Delete

             On 商品类型 For Each Row

Begin

    Set @strDeleteInfo=" 商品类型数据表中的记录不允许删除 " ;

    Delete From 商品类型 ;

End $$

Delimiter ;