【4/1-13】速进！快速理解SQL 语句CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

2024-02-13 16:32
文章标签 sql 快速 理解 function 13 语句 database procedure check constraint trigger 速进

本文主要是介绍【4/1-13】速进！快速理解SQL 语句CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

文章目录

    • ==完整性==
    • ==CHECK==
    • ==CONSTRAINT==
    • ==ASSERTION==
    • ==TRIGGER==
    • ==PROCEDURE==
    • ==FUNCTION==

在介绍上述语句之前
先完成关于完整性的几个测试

完整性

  • 实体完整性
    [例5.1] 将Student表中的Sno属性定义为码
    （1）在列级定义主码
CREATE TABLE Student(Sno  CHAR(9)  PRIMARY KEY,Sname  CHAR(20) NOT NULL,     Ssex  CHAR(2),Sage  SMALLINT,Sdept  CHAR(20));

（2）在表级定义主码

CREATE TABLE Student(Sno  CHAR(9),  Sname  CHAR(20) NOT NULL,Ssex  CHAR(2),Sage  SMALLINT,Sdept  CHAR(20),PRIMARY KEY (Sno));

[例5.2] 将SC表中的Sno，Cno属性组定义为码

CREATE TABLE SC(Sno   CHAR(9)  NOT NULL, Cno   CHAR(4)  NOT NULL,  Grade SMALLINT,PRIMARY KEY (Sno,Cno)    /*只能在表级定义主码*/);
  • 参照完整性定义

关系SC中（Sno，Cno）是主码。Sno，Cno分别参照Student表的主码和Course表的主码
[例5.3]定义SC中的参照完整性

CREATE TABLE SC(Sno    CHAR(9)  NOT NULL, Cno    CHAR(4)  NOT NULL,  Grade  SMALLINT,PRIMARY KEY (Sno, Cno),   /*在表级定义实体完整性*/FOREIGN KEY (Sno) REFERENCES Student(Sno),  /*在表级定义参照完整性*/FOREIGN KEY (Cno) REFERENCES Course(Cno)    /*在表级定义参照完整性*/);

[例5.4] 显式说明参照完整性的违约处理示例
之前没有显式说明的时候，采用的都是隐式的，只不过没有写出来而已。现在我们把违约处理在FOREIGN KEY后面写出来

CREATE TABLE SC(Sno   CHAR(9)  NOT NULL,Cno   CHAR(4)  NOT NULL,Grade SMALLINT,PRIMARY KEY(Sno,Cno), 			FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE        /*级联删除SC表中相应的元组*/ON UPDATE CASCADE,      /*级联更新SC表中相应的元组*/FOREIGN KEY (Cno) REFERENCES Course(Cno)	                    ON DELETE NO ACTION 	/*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/ON UPDATE CASCADE   /*当更新course表中的cno时，级联更新SC表中相应的元组*/);

这里说明一下： 违约处理的显式执行后，当我去删除Course中某一个Cno而造成与SC表不一致时，拒绝删除，当我去修改Student的Sno或者Course的Cno时，SC表中也会同时更新相应元组。

  • 用户定义的完整性

（1）不允许取空值（NOT NULL）

[例5.5] 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值

CREATE TABLE SC(Sno CHAR(9)  NOT NULL,	Cno CHAR(4)  NOT NULL,	Grade  SMALLINT NOT NULL,	PRIMARY KEY (Sno, Cno),  … /* 如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义 可以不写 */);

（2）列值唯一

[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码
这里既可以设置表级完整性，也可设置为列级完整性

CREATE TABLE DEPT(Deptno  NUMERIC(2),Dname  CHAR(9)  UNIQUE NOT NULL,/*要求Dname列值唯一, 并且不能取空值*/Location  CHAR(10),PRIMARY KEY (Deptno));

好了 以上完整性内容介绍完毕，下面我们来看以下语句。
CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION

CHECK

CHECK<约束条件>

用CHECK短语指定列值应该满足的条件
[例5.7] Student表的Ssex只允许取“男”或“女”。
这个有点 CHECK+(条件)的意思
CHECK (Ssex IN(‘男’,‘女’))

CREATE TABLE Student(Sno   CHAR(9) PRIMARY KEY,Sname CHAR(8) NOT NULL,                     Ssex  CHAR(2)  CHECK (Ssex IN ('男','女')), /*性别属性Ssex只允许取'男'或'女' */Sage  SMALLINT,Sdept  CHAR(20));

CHECK语句指定条件之后，当Ssex取其他值的时候，会提示报错：

未更新任何行。
未提交行2中的数据。
错误源: .Net SqlClient Data Provider.
错误消息: INSERT语句与CHECK约束"CK_ _S_ Ssex_ _300424B4"冲
突。该冲突发生于数据库"SCHOOL" ,表"dbo.S", column 'Ssex'.
语句已终止。.
请更正错误并重试，或按Esc取消更改.

[例5.8] SC表的Grade的值应该在0和100之间。

CREATE TABLE  SC(Sno    CHAR(9) ,Cno    CHAR(4),Grade  SMALLINT CHECK (Grade>=0 AND Grade <=100),/*Grade取值范围是0到100*/PRIMARY KEY (Sno,Cno),FOREIGN KEY (Sno) REFERENCES Student(Sno),FOREIGN KEY (Cno) REFERENCES Course(Cno));

若性别取0-100以外其他值的时候，提示报错：

未更新任何行。
未提交行2中的数据。
错误源: .Net SqlClient Data Provider.
错误消息: INSERT语句与CHECK约束"CK_ _S_ Ssex_ _300424B4")冲
突。该冲突发生于数据库"SCHOOL" ,表"dbo.S", column 'Ssex'.
语句已终止。.
请更正错误并重试，或按Esc取消更改。

上述都是CHECK列级约束，下面介绍一下元组级约束
在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制
[例5.9]当学生的性别是男时，其名字不能以Ms.打头。

CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')

这里条件的意思是：要么性别为女无约束条件，要么男生姓名不能以MS.开头，说白了就是男生的姓名不可以以MS.打头

CREATE TABLE Student(Sno    CHAR(9), Sname  CHAR(8) NOT NULL,Ssex    CHAR(2),Sage   SMALLINT,Sdept  CHAR(20),PRIMARY KEY (Sno),CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%')/*定义了元组中Sname和 Ssex两个属性值之间的约束条件*/);

CONSTRAINT

完整性约束命名子句
完整性约束条件包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

CONSTRAINT <完整性约束条件名><完整性约束条件>

[例5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。
学号：
C1 为完整性条件约束名
CHECK及后为完整性约束条件
其他类比

Sno  NUMERIC(6)
CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)

CREATE TABLE Student(Sno  NUMERIC(6)CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999),Sname  CHAR(20)  CONSTRAINT C2 NOT NULL,Sage  NUMERIC(3)CONSTRAINT C3 CHECK (Sage < 30),Ssex  CHAR(2)CONSTRAINT C4 CHECK (Ssex IN ( '男','女')),CONSTRAINT StudentKey PRIMARY KEY(Sno));

当没有标明约束名的时候，是系统默认的约束名，用CONSTRAINT为CHECK约束主动定义一个约束名，方便后续改动。这里注意名字不能重复
[例5.11]建立教师表TEACHER，要求每个教师的应发工资不低于3000元。
应发工资是工资列Sal与扣除项Deduct之和。

CREATE TABLE TEACHER(Eno    NUMERIC(4)  PRIMARY KEY,   /*在列级定义主码*/Ename  CHAR(10),Job    CHAR(8),Sal    NUMERIC(7,2),Deduct NUMERIC(7,2),Deptno NUMERIC(2),CONSTRAINT TEACHERFKey FOREIGN KEY (Deptno) 	REFERENCES DEPT(Deptno),CONSTRAINT C1 CHECK (Sal + Deduct >= 3000) );

[例5.12]去掉例5.10 Student表中对性别的限制。

ALTER + TABLE +表名
DROP + CONSTRAINT + 约束名

这里DROP一个CONSTRAINT，然后就突出了起名字的好处了，能够直接去修改和删除。

ALTER TABLE Student 
DROP CONSTRAINT C4;

[例5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40
先和【5.12】一样，DROP删除后，然后再ADD 添加。

ALTER TABLE Student
DROP CONSTRAINT C1;
ALTER TABLE Student
ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999),
ALTER TABLE Student
DROP CONSTRAINT C3;
ALTER TABLE Student
ADD CONSTRAINT C3 CHECK(Sage < 40);

ASSERTION

创建断言的语句格式

CREATE ASSERTION<断言名><CHECK 子句>

删除断言的语句格式为

DROP ASSERTION <断言名>;

每个断言都被赋予一个名字，<CHECK 子句>中的约束条件与WHERE子句的条件表达式类似。

这里注意：
T-SQL中没有ASSERTION
类似的有RULE，但使用方法不同：
上下文中不允许使用子查询，只允许使用标量表达式。
例如：

CREATE RULE sex_rule
AS @sex in (‘男’,‘女’)

使用Constraint基本能完成功能，不建议使用RULE。
[例5.18] 限制数据库课程最多60名学生选修

CREATE ASSERTION ASSE_SC_DB_NUM
CHECK (60 >= (SELECT count(*)FROM Course,SCWHERE SC.Cno=Course.Cno and Course.Cname ='数据库'));

CHECK里是一个连接查询。当第61个同学选择该课，将被拒绝

[例5.19]限制每一门课程最多60名学生选修

CREATE ASSERTION ASSE_SC_CNUM1
CHECK(60 >= ALL (SELECT count(*) FROM	 SC GROUP BY cno));

这里涉及聚集操作count 和分组函数group by的SQL语句

TRIGGER

触发器：上课的第一感觉就是特别像JAVA里面的动作监听器（Actionlistener），一旦监听到相应动作就会出发相应操作
CREATE TRIGGER语法格式

CREATE TRIGGER <触发器名>  
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH  {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

删除触发器的SQL语法：

DROP TRIGGER <触发器名> ON <表名>;

[例5.21]当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到下面表中：
SC_U（Sno,Cno,Oldgrade,Newgrade）
其中Oldgrade是修改前的分数，Newgrade是修改后的分数。

CREATE TRIGGER  SC_T		
AFTER UPDATE OF Grade ON SC
REFERENCINGOLD row  AS  OldTuple,NEW row AS  NewTuple
FOR EACH ROW 	
WHEN (NewTuple.Grade >= 1.1*OldTuple.Grade)INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)  
VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。

CREATE TRIGGER Student_Count
AFTER INSERT ON Student  	         
REFERENCINGNEW TABLE AS DELTA
FOR EACH STATEMENT  INSERT INTO StudentInsertLog (Numbers)SELECT COUNT(*) FROM DELTA

[例5.23] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

CREATE TRIGGER Insert_Or_Update_Sal 
BEFORE INSERT OR UPDATE ON Teacher  /*触发事件是插入或更新操作*/
FOR EACH ROW        /*行级触发器*/
BEGIN                             /*定义触发动作体，是PL/SQL过程块*/IF (new.Job='教授') AND (new.Sal < 4000) THEN  new.Sal :=4000;                END IF;
END;

BEGIN-END 就是一个大括号
IF 要和 END IF 相对应

在SQL serever中，基本语法：

CREATE TRIGGER <触发器名>
on {<表名>|<视图名>}
{FOR|AFTER|INSTEAD OF}
[INSERT,UPDATE,DELETE]
AS
sql_statement

触发器使用说明

例5.2
1.参考教材内容，新建Student表
2. 新建表StudentInsertLog
存储学生人数

CREATE TABLE StudentInsertLog
(
Numbers INT
)

3.新建表StudentInsertLogUser
存储用户名和操作时间

CREATE TABLE StudentInsertLogUser
(UserName nchar(10),DateAndTime datetime
)

4.新建触发器Student_Count
当插入新的学生记录时，触发器启动，自动在StudentInsertLog记录学生人数

CREATE TRIGGER Student_Count
ON Student  	         
AFTER
INSERT
AS 
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM Student

这里统计的是Student表中的数，与书上有所不同，所以最后测试Number为学生总数

5.新建触发器Student_Time
当插入新的学生记录时，触发器启动，自动在StudentInsertLogUser记录用户名和操作时间

CREATE TRIGGER Student_Time
ON Student  	         
AFTER
INSERT
AS declare @UserName    nchar(10)declare @DateTime    datetimeselect @UserName = system_userselect @DateTime = CONVERT(datetime,GETDATE(),120) --2018-04-11 16:33:10INSERT INTO StudentInsertLogUser(UserName,DateAndTime)VALUES (@UserName,@DateTime)

declare：声明，相当于定义一个变量名
变量名：@+名字
system_user：当前系统用户

6. 测试触发器效果

INSERT
INTO  Student
VALUES ('201215135','王五','男',18,'CS');
SELECT * FROM Student
SELECT * FROM StudentInsertLog
SELECT * FROM StudentInsertLogUser

PROCEDURE

创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

执行存储过程

  CALL/PERFORM  PROCEDURE 过程名([参数1,参数2,...])；

使用CALL或者PERFORM等方式激活存储过程的执行
数据库服务器支持在过程体中调用其他存储过程

修改存储过程

ALTER PROCEDURE 过程名1  RENAME TO 过程名2;

删除存储过程

DROP  PROCEDURE 过程名()；

[例8.8] 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。

CREATE OR REPLACE PROCEDURE TRANSFER(inAccount INT,outAccount  INT,amount FLOAT) /*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/AS DECLARE		/*定义变量*/totalDepositOut Float;totalDepositIn Float;inAccountnum INT;
BEGIN                         	    /*检查转出账户的余额 */SELECT Total INTO totalDepositOut FROM AccoutWHERE accountnum=outAccount;IF totalDepositOut IS NULL THEN  /*如果转出账户不存在或账户中没有存款*/ROLLBACK; 	   /*回滚事务*/RETURNEND IF;IF totalDepositOut< amount THEN    	/*如果账户存款不足*/ROLLBACK; 				/*回滚事务*/RETURNEND IFSELECT Accountnum INTO inAccountnum FROM AccountWHERE accountnum=inAccount;IF inAccount IS NULL THEN  		/*如果转入账户不存在*/                        ROLLBACK; 	         	 		/*回滚事务*/RETURN;END IF;UPDATE Account SET total=total-amountWHERE accountnum=outAccount; /* 修改转出账户余额，减去转出额 */UPDATE Account SET total=total + amount WHERE   accountnum=inAccount; /* 修改转入账户余额，增加转入额 */COMMIT;                       	/* 提交转账事务 */
END;

[例8.9] 从账户01003815868转10000元到01003813828账户中。

CALL PROCEDURE  TRANSFER(01003813828,01003815868,10000);

该标准SQL语句无法在T-SQL中运行

那么在SQL serever中，步骤：
1.建立新表Account，并写入两个用户

DROP TABLE IF EXISTS Account;CREATE TABLE Account
(
accountnum CHAR(3),	-- 账户编号
total FLOAT		-- 账户余额
);INSERT INTO Account VALUES(101,50);
INSERT INTO Account VALUES(102,100);SELECT * FROM Account

2. 建立存储过程

IF (exists (select * from sys.objects where name = 'Proc_TRANSFER'))DROP PROCEDURE Proc_TRANSFER
GO
CREATE PROCEDURE Proc_TRANSFER 
@inAccount INT,@outAccount  INT,@amount FLOAT/*定义存储过程TRANSFER，参数为转入账户、转出账户、转账额度*/
AS
BEGIN TRANSACTION TRANS   DECLARE		/*定义变量*/@totalDepositOut Float,@totalDepositIn Float,@inAccountnum INT;/*检查转出账户的余额 */     SELECT @totalDepositOut = total FROM Account	WHERE accountnum = @outAccount;/*如果转出账户不存在或账户中没有存款*/IF @totalDepositOut IS NULL               	   BEGINPRINT '转出账户不存在或账户中没有存款'ROLLBACK TRANSACTION TRANS; 	   /*回滚事务*/RETURN;END;/*如果账户存款不足*/IF @totalDepositOut < @amount     	BEGINPRINT '账户存款不足'ROLLBACK TRANSACTION TRANS; 				/*回滚事务*/RETURN;END/*检查转入账户的状态 */  SELECT @inAccountnum = accountnum  FROM Account	WHERE accountnum = @inAccount;/*如果转入账户不存在*/ IF @inAccountnum IS NULL   		                       BEGINPRINT '转入账户不存在'ROLLBACK TRANSACTION TRANS; 	         	 		/*回滚事务*/RETURN;END;/*如果条件都没有异常，开始转账。*/ BEGINUPDATE Account SET total = total - @amount	WHERE	accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */UPDATE Account SET total = total + @amount	WHERE   accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */PRINT '转账完成，请取走银行卡'COMMIT TRANSACTION TRANS;                       	/* 提交转账事务 */RETURN;END

3.执行存储过程
账户不存在情况（输入、输出账户都测试一下）
正常情况:

SELECT * FROM Account
EXEC	Proc_TRANSFER@inAccount = 101,	--转入账户@outAccount = 102,	--转出账户@amount = 50		--转出金额SELECT * FROM Account

FUNCTION

下期更新

16:48-14:10=两个半小时，其中休息了一会，做前面的时候还好，基本都会没什么问题，后面到了触发器，还有长篇代码的存储过程，有点头疼. 慢慢理解吧在这里插入图片描述

这篇关于【4/1-13】速进！快速理解SQL 语句CHECK / CONSTRAINT / TRIGGER / PROCEDURE/ FUNCTION的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/706032。

相关文章

MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
使用EasyPoi快速导出Word文档功能的实现步骤

使用EasyPoi快速导出Word文档功能的实现步骤

《使用EasyPoi快速导出Word文档功能的实现步骤》EasyPoi是一个基于ApachePOI的开源Java工具库,旨在简化Excel和Word文档的操作,本文将详细介绍如何使用EasyPoi快速... 目录一、准备工作1、引入依赖二、准备好一个word模版文件三、编写导出方法的工具类四、在Export
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
一文详解MySQL索引(六张图彻底搞懂)

一文详解MySQL索引(六张图彻底搞懂)

《一文详解MySQL索引(六张图彻底搞懂)》MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度,：本文主要介绍MySQL索引的相关资料,文中通过代码介绍的... 目录一、什么是索引？为什么需要索引？二、索引该用哪种数据结构？1. 哈希表2. 跳表3. 二叉排序树4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2