知识回顾

第一部分：存储过程

一、存储过程的创建

create procedure pro_name(parameter1, parameter2)
routine_body

• parameter1: 存储过程的参数由三个部分组成
  • 1、参数的类别 in、out、inout
  • 2、参数的名字
  • 3、参数的数据类型
• routine_body: 存储过程体
  • begin...end中，如果存储过程体只有一条SQL语句，begin...end 可以省略

二、存储过程体

• 局部变量的定义

  declare var_name type[default value];
  

  • var_name: 变量名
  • type: 变量的类型

• set语句

  set var_name = value;
  

• select...into语句

  select col_name into var_name table_expr;
  

  • table_expr: 指的是select语句中的from子句后面的语法部分。
  • select...into 语句返回的结构集只能有一条记录

• 流程控制语句

  • 条件判断语句

  if conditions
      then statement
  else statement
  end if;
  
  case 
      when conditions
          then statements
      else statements
  end;
  

  • 循环语句
    • while
    • loop
    • repeat
    • 在存储过程或者存储函数中可以定义多个循环
  • 局部变量定义的一些注意事项的注意事项:
    • 局部变量只能在存储过程体的begin...end语句块中声明；
    • 局部变量必须在存储过程体的开头处声明;
    • 局部变量的作用范围：仅限于声明它的begin...end语句块中
    • 局部变量不用于用户变量，局部变量前没有使用@符号，用户变量名前使用一个@符号，系统名前使用@@

三、存储过程的调用

call pro_name(param);

• pro_name: 指的是存储过程的名字
• param: 指的是存储过程的参数
• 如果存储过程中，没有参数，调用存储过程的时候小括号是否可以省略

call pro_cust_sel();
call pro_cust_sel;

四、存储过程的删除

drop procedure [if exists] pro_name

• 删除存储过程的时候，存储过程名字后没有括号。

五、游标的使用步骤

• 1、声明游标

  declare cursor_name cursor for select_statement
  

  • cursor_name: 游标的名字
  • select_statement: 定义游标的查询语句
  • select_statement与句子中是否可以有into？ 不能

• 2、打开游标

  open cursor_name
  

  • 打开游标的目的：将游标连接到select语句返回的结构集中；
  • 一个游标是可以被打开多次，并且每次打开游标的结构集可能会不同

• 3、读取游标

  fetch cursor_name into var_name[var_name...]
  

  • 注意：变量的数目必须等于声明游标时select子句中选择列的数目

• 4、关闭游标

  close cursor_name;
  

  • 注意：游标使用完之后需要关闭，关闭游标可以释放游标所使用的全部资源，如果游标没有手动关闭，在到达end语句时将自动关闭；游标被关闭之后，没有重新打开，不能使用，对于声明过得游标，不需要再次声明，直接打开使用。

六、游标使用的注意事项(简答题)

• 1、游标不能被单独使用，必须与存储过程和存储函数一起使用
• 2、在一个begin...end语句中可以定义多个游标，但是必须具有不同的名字；
• 3、游标不是一条SQL语句，是SQL语句检索出来的结果集。

第二部分：存储函数

一、存储函数的概念

• 存储函数与存储过程是一样的，都是有声明式SQL语句和过程式SQL语句组成的代码片段，可以通过应用程序或者其他SQL语句调用。

二、存储函数与存储过程的区别

• 存储函数没有输出参数，因为存储函数本身就是输出参数
• 可以直接对存储函数进行调用，不需要call语句
• 存储函数必须包含一条 return 语句，这条语句不允许在存储过程中出现。

三、创建存储函数基本语法

create function func_name(func_param,[func_param])
returns type
routine_body

• func_param：存储函数参数：包含 参数名 参数类型
• returns type：指定存储函数的返回值的数据类型
• routine_body: 与存储函数的区别是，在该部分需要包含一条 return 语句，用于指定存储函数的返回值

新知识讲解：

第一部分：存储函数

一、案例：在数据库mysql_test 中创建一个存储函数，要求该函数能根据给定的客户ID号返回客户的性别。如果数据库没有该指定客户的id号，则返回“没有客户”。

create function func_sex(id int)
returns varchar(20)
deterministic
begin
declare csex varchar(20);
select cust_sex into csex from customer where cust_id = id;
if csex is null then return (select '没有该用户');
else if csex = 'M' then set csex = '男';
else set csex = '女' 
return csex;
end if;
end if;
end $$

二、调用存储函数

基本语法：

select func_name(func_param);

例如：

select func_sex(1);

三、删除存储函数

基本语法:

drop function [if exists] func_name

举例：

drop function func_sex;

第二部分: 数据库的完整性

一、 数据库完整性定义

数据库完整性是指数据的正确性和相容性、一致性

二、数据库完整性约束的定义

加在数据库数据之上的语义约束条件;

三、数据库完整性约束的作用

为了防止数据库追踪出现不符合语义的数据

四、完整性约束作为数据库关系模式定义的一部分，可以通过create table / alter table 去定义

五、完整性约束条件的作用对象：

• 列级约束:（包含数据类型约束、数据格式约束、取值范围约束、空值约束）
  • 类型约束
  • 数据格式约束
  • 取值范围约束
  • 空值约束
• 元组约束: 各个字段之间的相互约束
  • 如入职日期不能在出事日期之前
• 表级约束（包含）
  • 如：学生成绩表中的学号受学生基本信息表中的学号的约束

六、定义和实现完整性约束

• 1、实体完整性

  • 通过主键约束和候选键约束来实现

  • 主键约束：

    • 主键可以由一列组成，也可以由多列组成，多列组成的主键叫复合组件；
    • 定义主键列必须遵循以下规则:
      • 1、每个表只能定义一个组件
      • 2、组件的值必须能够唯一标识每一条记录，且不能为null
      • 3、复合主键不能包含不必要的列
      • 4、一个列名在主键中不能重复出现

  • 实现主键约束:

    • 通过primary key去实现
    • 作为列的完整性约束，此时只需要将表中某个列的属性定义后加关键字primary key即可；
      • 示例：定义列级别的实体完整性约束，创建一个Student表，其属性为sno，sname，sage，sdept，sno为该表主键。

      create table student(
          sno int not null primary key, 
          sname varchar(20) not null,
          sage int,
          sdept int
          )
      

    • 作为表的完整性约束，此时只需要在表中所有列定义后加primary key(column)来实现
      • 示例：

      create dept(
          dno int not null,
          dname varchar(50) not null,
          daddr varchar(100),
          primary key(dno)
          )
      

    注意: 当主键只有一个属性的时候，两种方法都可以使用，但是多个列作为主键时，只能使用表级约束。

    • 删除主键：alter table tb_name drop primary key
    • 通过alter table 的方式定义主键：

    alter tb_name add primary key(column)
    

  • 候选键约束：

    • 候选键约束与主键约束一样，可以由多列组成，成为复合候选键
    • 候选键的值也必须是唯一的，且不能为null
    • 候选键可以在create table 和 alter table语句中使用，使用unique来定义，使用方式与主键类似，可以定义为表级约束和列级约束
    • 例子: 创建一个test表，表中有id name type 将id 和type定义为候选键约束

    create table test(
        id int not null,
        name char(20) not null,
        type int not null,
        unique(id,type)
    )
    

  • 主键和候选键的区别：

    • 1、一个表只能有一个主键，但是可以有多个候选键；
    • 2、定义主键约束时，系统会自动产生一个primary key，定义候选键的时候，系统会自动产生一个unique索引。

• 2、参照完整性: 在MySQL中，参照完整性是通过定义一个外键声明来实现的

  student(dno)
  dept(dno)
  外键的值可以为空，但是主键的值不能为空
  

  • a) 外键的定义：可以定义为表级约束和列级约束
  • b) 表级约束：

  foreign key(column, column) references tb_name(column, column) [on delete reference_option] [on update reference_option]
  

  • c) 列级约束：

  references tb_name(column) [on delete reference_option] [on update reference_option]
  

  • d) 参照完整性的实现策略：
    • 1、restrict: 限制策略，也是默认值，当要删除、更新被参照表上的被参照列上的，并且在外键中出现的值时，系统拒绝在这个操作。
    • 2、cascade: 级联策略 ，从被参照表中删除或者更新记录行时，自动删除或者更新操作参照表中的匹配记录行；
    • 3、set null: 置空策略，当从被参照表中删除或更新记录行时，设置参照表中与之对应的外键列的值为null
    • 4、no action: 不采取措施，同 restrice

• 3、用户定义完整性