mysql数据中的实体完整性

转载

桃太郎 2025-01-06 14:37:32

文章标签 mysql数据中的实体完整性数据库 sql sqlserver SQL 文章分类 MySQL 数据库

SQL要结束了

1、数据库完整性

1.1 实体完整性
1.2 参照完整性
1.3 用户定义的完整性
1.4 完整性约束命名子句
1.5 断言
1.6 触发器

2、存储过程

1、数据库完整性

数据库完整性是指数据库中存储数据的正确性和相容性，符合现实世界的语义、反映当前的现实状况；数据的相容性是指数据库同意对象在不同关系表中的数据是符合逻辑的

1.1 实体完整性

实体完整性指表的主码不能为 null，主码需要在创建表时定义

create table Student
(
	Sno varchar(15) primary key, --列级定义主码
	Sname varchar(10),
	Ssex char(2),
	Sage smallint,
	Sdept varchar(10)
); --Sno为主码，即一个学号唯一对应一名学生

create table SC
(
	Sno varchar(15),
	Cno varchar(10),
	Grade smallint,
	primary key(Sno,Cno) --表级定义主码
); --多个属性为主码时，只能表级定义

1.2 参照完整性

参照完整性指表中的外码属性值或者为 null，或者为其被参照关系表中的某个元组的主码值

create table SC
(
	Sno varchar(15),
	Cno varchar(10),
	Grade smallint,
	primary key(Sno,Cno), --表级定义主码
	foreign key(Sno) references Student(Sno),
	foreign key(Cno) references Course(Cno) --定义参照关系
); --SC表的Sno属性值必选在Student表中存在，SC表的Cno属性值必选在Cource表中存在

参照关系相当于将多个表以该种方式联系在了一起，当删除元组时，有可能会违反参照完整性，如：删除了Student中的某个元组，但该元组的Sno属性值与SC表的某个元组的Sno属性值形成了对照，会拒绝执行

可指定在违反参照完整性时，系统执行的策略

create table SC
(
	Sno varchar(15),
	Cno varchar(10),
	Grade smallint,
	primary key(Sno,Cno), --表级定义主码
	foreign key(Sno) references Student(Sno)
		on delete cascade	--删除Student中的元组时，级联删除SC表中对应的元组
		on update cascade,  --更新Student中的元组时，级联更新SC表中对应的元组
	foreign key(Cno) references Course(Cno)
		on delete no action --删除Course中的元组违反参照完整性时，拒绝执行
		on update cascade	--更新Course中的元组时，级联更新SC表中对应的元组
);

1.3 用户定义的完整性

用户定义的完整性是为满足现实数据规则自定义的属性值规律，如Student表中Sname属性不能取 null，在创建表时定义

create table SC
(
	Sno varchar(15) unique not null, --不能重复且非空
	Cno varchar(10) not null, --非空
	Grade smallint check(Grade>=0 and Grade<=100) --[0,100]开区间
);

create table Student
(
	Sno varchar(15) primary key,
	Sname varchar(10) not null,
	Ssex varchar(2) check(Ssex in('男','女')) --只能为男或女
);

除了为单个属性设置约束条件，也可为插入的数据设置约束条件

create table Student
(
	Sno varchar(15) primary key,
	Sname varchar(10) not null,
	Ssex varchar(2) check(Ssex in('男','女')), --只能为男或女
	check(Ssex='女' or Sname not like 'Ms.%')
); --男生不能称为...夫人

1.4 完整性约束命名子句

也可使用 constraint 子句在创建表时定义完整性约束，对约束进行命名

create table Student
(
	Sno numeric(6)
		constraint C1 check(Sno between 90000 and 99999),
	Sname varchar(10)
		constraint C2 not null,
	Sage numeric(3)
		constraint C3 check(Sage<30),
	Ssex varchar(2)
		constraint C4 check(Ssex in('男','女'))
); --约束命名为C1、C2、C3、C4

create table Teacher
(
	Eno numeric(4) primary key,
	Ename varchar(10),
	Job varchar(8),
	Salary numeric(7,2),
	Deduct numeric(7,2),
	Deptno numeric(2)
	constraint teacher_refer foreign key(Deptno)
	references Dept(Deptno), --为参照关系约束命名
	constraint sum_salary check(Salary+Deduct>=3000) --工资必须大于3000
);

为约束命名的目的是为了日后删除和更改表中的约束（修改表结构相关使用alter，修改元组使用update）

alter table Student
	drop constraint C1; --删除Student表的约束C1

alter table Student
	add constraint C1 check(Sno between 100 and 200); --添加新的C1约束

alter table Student
	drop constraint C3; --删除Student表的约束C3

alter table Student
	add constraint C3 check(Sage>=10 and Sage<=40); --添加新的C3约束

1.5 断言

声明断言来指定更具一般性的约束，可以涉及多个表或聚集操作的比较复杂的完整性约束，T-SQL中没有 assertion 关键字

create assertion <断言名> <check 语句>

/*create assertion asse_sc_db_num
	check
		(60 >=
			(select count(*)
			from Course,SC
			where SC.Cno=Course.Cno and Course.Cname='数据库')
		); --T-SQL不支持*/

T-SQL中可使用触发器实现类似的功能

1.6 触发器

触发器类似于窗体事件，在发送信号后执行一个函数，SQL中即在对表的增、删、改操作时激活相对应的触发器

create trigger <触发器名>
{before|after} <触发事件> on <表名> --指明所在表，激活的时刻
referencing new|old row as <变量> --指出引用的变量
for each{row|statement} --触发器类型，行级和语句级
[when<触发条件>] <触发动作>

T-SQL中实现上述断言的功能如下：

if(object_id('trg_sc_insert') is not null)
	drop trigger trg_sc_insert;
go

create trigger trg_sc_insert
on SC for insert --不支持before，for相当于after
as
	declare @sum_p int,
			@Sno varchar(15),
			@Cno varchar(10),
			@Grade smallint; --定义自变量
	select @sum_p=count(*) from Course,SC where SC.Cno=Course.Cno and Course.Cname='数据库';
	select @Sno=Sno,@Cno=Cno,@Grade=Grade from inserted; --获取插入的元组的属性值
	if(@sum_p>60)
		begin
			delete SC where Sno=@Sno and Cno=@Cno and Grade=@Grade;
		end
go

T-SQL设置触发器与标准SQL的区别还是很大的，删除了newrow和oldrow，使插入操作和修改操作的触发器在编写时很相似，并使用 begin–end 语句块取代end if等

/*修改成绩大于百分10时在其他表中记录*/
/* T-SQL不支持
create trigger SC_T
after update of Grade on SC
referencing
	oldrow as oldTuple
	newrow 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);
*/

if(object_id('SC_T') is not null)
	drop trigger trg_sc_insert;
go

create trigger SC_T
on SC after update --相当于for
as
	declare @Sno varchar(15),
			@Cno varchar(10),
			@old_Grade smallint, 
			@new_Grade smallint; --定义自变量
	select @Sno=Sno,@Cno=Cno,@new_Grade=Grade from inserted; --获取修改后的属性值
	select @old_Grade=Grade from deleted; --获取修改前的属性值
	if(@new_Grade>=1.1*@old_Grade)
		begin
			insert into SC_U(Sno,Cno,old_Grade,new_Grade)
			values(@Sno,@Cno,@old_Grade,@new_Grade);
		end
go

执行一条修改语句尝试一下

update SC
set Grade=100
where Sno='20191102041' and Cno='5'; --以前76分

select * from SC_U;

mysql数据中的实体完整性_mysql数据中的实体完整性

在做一个练习

/*为教师表Teacher教授的工资不能低于4000元*/
/*
create trigger insert_or_update_salary
before insert or update on Teacher
referencing newrow as newTuple
for each row
begin
	if(newTuple.Job='教授' and newTuple.Salary<4000)
		then newTuple.Salary:=4000; --赋值使用 :=
	end if;
end;
不支持*/

create trigger insert_or_update_salary
on Teacher for insert,update
as
	declare @Eno numeric(4,0),
			@Ename varchar(10),
			@Job varchar(8),
			@Salary numeric(7,2),
			@Deduct numeric(7,2),
			@Deptno numeric(2,0);
	select @Eno=Eno,@Ename=Ename,@Job=Job,@Salary=Salary,@Deduct=Deduct,@Deptno=Deptno
	from inserted;
	if(@Salary+@Deduct<4000)
		begin
			update Teacher set Salary=4000 where Eno=@Eno and Ename=@Ename;
		end
go

insert into Teacher
values(15,'王伯成','厕所所长',500,100,15);

insert into Teacher
values(16,'王小成','厕所扫地',5000,100,15);
update Teacher
set Salary=500
where Eno=16;

select * from Teacher;

mysql数据中的实体完整性_mysql数据中的实体完整性_02

删除触发器如下

/*drop trigger insert_or_update_salary on Teacher; --不支持，不能指定表*/
drop trigger insert_or_update_salary; --删除触发器

insert into Teacher
values(20,'王大成','厕所拖地',500,100,15);

select * from Teacher;

mysql数据中的实体完整性_mysql数据中的实体完整性_03

2、存储过程

类似于函数，传入参数，执行相应SQL语句

以下为标准SQL语法，T-SQL不完全支持

create procedure transfer(inAccount int,outAccount int,amount int)
as
	declare totalDepositOut int,
			totalDepositIn int,
			inAccountnum int;
begin
	select total_salary into totalDepositOut from Account where Account_id=outAccount;
	if totalDepositOut is null then
		rollback;
		return;
	end if;
	if totalDepositOut<amount then
		rollback;
		return;
	end if;
	select total_salary into totalDepositIn from Account where Account_id=inAccount;
	if totalDepositIn is null then
		rollback;
		return;
	end if;
	update Account set total_salary=total_salary-amount where Account_id=outAccount;
	update Account set total_salary=total_salary+amount where Account_id=inAccount;
end;

call procedure transfer(1,2,100); --执行

标准SQL和T-SQL区别很大，如下为T-SQL语法

create procedure transfer_account
	@in_id int,
	@out_id int,
	@salary int --定义参数
as
	declare @in_salary int,
			@out_salary int;
	select @in_salary=total_salary from Account where Account_id=@in_id;
	select @out_salary=total_salary from Account where Account_id=@out_id;
	if(@in_salary is null)
		begin
			print '收款账户不存在';
			return;
		end
	if(@out_salary is null)
		begin
			print '汇款账户不存在';
			return;
		end
	if(@out_salary<@salary)
		begin
			print '钱不够';
			return;
		end
	update Account set total_salary=total_salary+@salary where Account_id=@in_id;
	update Account set total_salary=total_salary-@salary where Account_id=@out_id;
go

--1号存款500，2号300
exec transfer_account 1,2,500; --2号向1号汇款500
exec transfer_account 5,1,100; --不存在的5号收款
exec transfer_account 1,6,100; --不存在的6号汇款

exec transfer_account 1,2,100; --2号向1号汇款100，成功

select * from Account;

/*alter procedure transfer_account rename to transfer_salary; --不支持*/
exec sp_rename 'transfer_account','transfer_salary'; --更改存储过程名

drop procedure transfer_salary; --删除存储过程

mysql数据中的实体完整性_数据库_04