《数据库系统实验》
实验报告
题目 | 实验11——数据库完整性 |
一、实验环境:
1、操作系统:Windows 11 22H2;
2、DBMS :mysql 8.0 CE;
二、实验内容与完成情况
11.5 创建一个教工表teacher(tno,tname,tadd,telphone,tsex,id),将教工号tno设为主键,性别默认值为“男”
SQL语言:
create table teacher
(
tno CHAR(7) primary key, #设置主键
tname VARCHAR(10),
tage INT,
telphone CHAR(12),
tsex char(2) default '男', #性别默认值为"男"
tid VARCHAR(20)
)engine=InnoDB;
desc teacher;
结果:
11.6 根据教工表teacher完成以下任务
(1) 设置telphone默认值为00000000
SQL语言:
ALTER TABLE teacher modify telphone CHAR(12) default '0000000';
desc teacher;
结果:
(2) 设置tsex的check检查约束为:输入值只能为“男”或“女”
SQL语言:
ALTER TABLE teacher CHANGE COLUMN tsex tsex enum('男','女') default '男';
desc teacher;
结果:
尝试插入tsex不为‘男’或‘女’的数据:插入失败。
(3) 设置id的位数为15位或18位,每位都是数字
SQL语言:
delimiter //
create trigger tr_id after insert on teacher #创建触发器
for each row
begin
declare iresult int default 0;
if (length(new.tid)!=15 and length(new.tid)!=18) #如果id长度不为15或18则报错
then
SIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = 'The length is not allowed';
end if;
select new.tid regexp '^[0-9]*$' into iresult; #如果id内容存在非数字则报错
if (iresult=0)
then
SIGNAL SQLSTATE'45000' SET MESSAGE_TEXT ='id must be consisted of nums';
end if;
END//
尝试插入长度不为15或18的数据:插入失败。
插入长度为15和18的元素但包含非数字:插入失败。
插入长度为15且均为数字的数据:插入成功。
11.7 设有订报管理子系统数据库DingBao中的表PAPER,表内容如下
请在掌握数据库完整性知识的基础上,根据表的内容设定尽可能多的完整性规则用于该表,用于保障该表的正确性和完整性。
举例:
①设置报纸编号为主码,并且pno的长度必须为6,同时是由组数字组成;
②约束ppr大小必须大于0;
③设置报纸的名称不可以为空且不可以相同。
SQL语言:
create table paper
(
pno char(7) primary key ,
pna char(20) unique not null,
ppr float not null
)engine=InnoDB;
delimiter //
create trigger trig after insert on paper #创建触发器
for each row
begin
declare iresult int default 0;
if (length(new.pno)!=6 or ppr<0)
then
SIGNAL SQLSTATE'45000';
end if;
select new.pno regexp '^[0-9]*$' into iresult; #如果id内容存在非数字则报错
if (iresult=0)
then
SIGNAL SQLSTATE'45000';
end if;
END//
验证方式与11.6基本一致,不再赘述。