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

本文主要是介绍【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

相关文章

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点: 1.确保子表中的某个字段(外键)只能引用父表中的有效记录2.主表中的列被删除时,子表中的关联列也会被删除3.主表中的列更新时,子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数,谈性能都是耍流氓。MySQL没有限制单表最大记录数,它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB,才推荐分库分表。性能由综合因素决定,抛开业务复杂度,影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考,并非铁律。 博主曾经操作过超过4亿行数据

Java进阶13讲__第12讲_1/2

多线程、线程池 1.  线程概念 1.1  什么是线程 1.2  线程的好处 2.   创建线程的三种方式 注意事项 2.1  继承Thread类 2.1.1 认识  2.1.2  编码实现  package cn.hdc.oop10.Thread;import org.slf4j.Logger;import org.slf4j.LoggerFactory

认识、理解、分类——acm之搜索

普通搜索方法有两种:1、广度优先搜索;2、深度优先搜索; 更多搜索方法: 3、双向广度优先搜索; 4、启发式搜索(包括A*算法等); 搜索通常会用到的知识点:状态压缩(位压缩,利用hash思想压缩)。

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置

MySQL数据库宕机,启动不起来,教你一招搞定!

作者介绍:老苏,10余年DBA工作运维经验,擅长Oracle、MySQL、PG、Mongodb数据库运维(如安装迁移,性能优化、故障应急处理等)公众号:老苏畅谈运维欢迎关注本人公众号,更多精彩与您分享。 MySQL数据库宕机,数据页损坏问题,启动不起来,该如何排查和解决,本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志,排查哪个表(表空间

电脑桌面文件删除了怎么找回来?别急,快速恢复攻略在此

在日常使用电脑的过程中,我们经常会遇到这样的情况:一不小心,桌面上的某个重要文件被删除了。这时,大多数人可能会感到惊慌失措,不知所措。 其实,不必过于担心,因为有很多方法可以帮助我们找回被删除的桌面文件。下面,就让我们一起来了解一下这些恢复桌面文件的方法吧。 一、使用撤销操作 如果我们刚刚删除了桌面上的文件,并且还没有进行其他操作,那么可以尝试使用撤销操作来恢复文件。在键盘上同时按下“C

MySQL高性能优化规范

前言:      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后,总结出了这篇! 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份

【生成模型系列(初级)】嵌入(Embedding)方程——自然语言处理的数学灵魂【通俗理解】

【通俗理解】嵌入(Embedding)方程——自然语言处理的数学灵魂 关键词提炼 #嵌入方程 #自然语言处理 #词向量 #机器学习 #神经网络 #向量空间模型 #Siri #Google翻译 #AlexNet 第一节:嵌入方程的类比与核心概念【尽可能通俗】 嵌入方程可以被看作是自然语言处理中的“翻译机”,它将文本中的单词或短语转换成计算机能够理解的数学形式,即向量。 正如翻译机将一种语言