太原理工大学软件学院数据库实验四(2021.4.26)

太原理工大学软件学院数据库实验四(2021.4.26)

-- （1）创建Student 表
CREATE TABLE Student
( Sno CHAR(8) PRIMARY KEY,
Sname CHAR(8) NOT NULL,
Ssex CHAR(2) CHECK( Ssex in ('男','女')),
Sage SMALLINT,
Sdept CHAR(20),
Sclass CHAR(4) NOT NULL,
Stotal smallint DEFAULT 0
);
-- （2）创建Course 表
CREATE TABLE Course
( Cno CHAR(4) CONSTRAINT FK_Course PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4) ,
Ccredit SMALLINT
);
-- （3）创建SC 表
CREATE TABLE SC
( Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno),
Cno CHAR(4),
Grade SMALLINT CONSTRAINT SC_CHECK CHECK(Grade >0 AND Grade<100),
PRIMARY KEY (Sno,Cno),
CONSTRAINT FK_SC FOREIGN KEY (Cno) REFERENCES Course(Cno)
);



select *from SC

-- （1）插入学生信息到Student表
INSERT INTO Student VALUES('20100001','李勇','男',20,'CS','1001',0)
INSERT INTO Student VALUES('20100002','刘晨','女',19,'CS','1001',0)

-- 请写出插入其余行的插入语句，并插入数据。
INSERT INTO Student VALUES('20100021','王敏','女',18,'MA','1001',0)
INSERT INTO Student VALUES('20100031','张立','男',19,'IS','1001',0)
INSERT INTO Student VALUES('20100003','刘洋','女',null,null,'1001',0)
INSERT INTO Student VALUES('20100010','赵斌','男',19,'IS','1001',0)
INSERT INTO Student VALUES('20100022','张明明','男',19,'CS','1001',0)

select * from Student

-- （2）插入到课程信息到Course表
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','数据库系统原理', '56',4)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('2','高等数学', null,2)

-- 请写出插入其余行的插入语句，并插入数据。
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('3','信息管理系统', '1',4)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('4','操作系统原理', '6',3)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('5','数据结构', '7',4)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('6','数据处理', null,2)
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('7','C语言', null,4)

select * from Course


-- （3）插入到SC表
INSERT INTO SC VALUES('20100001','1',92)
INSERT INTO SC VALUES('20100001','2',85)

-- 请写出插入其余行的插入语句，并运行。
INSERT INTO SC VALUES('20100001','3',88)
INSERT INTO SC VALUES('20100002','1',90)
INSERT INTO SC VALUES('20100002','2',80)
INSERT INTO SC VALUES('20100003','1',null)
INSERT INTO SC VALUES('20100010','3',null)

select * from SC





-- 3． 修改约束
-- 对数据库中已经存在的表，可对其增加约束或修改已存在的约束：
-- （1）添加约束
ALTER TABLE Course ADD UNIQUE(Cname)
ALTER TABLE Course ADD FOREIGN KEY (Cpno) REFERENCES Course(Cno)

-- 掌握如何声明唯一约束
-- （2）修改约束
ALTER TABLE SC DROP CONSTRAINT SC_CHECK
ALTER TABLE SC ADD CONSTRAINT SC_CHECK CHECK(Grade >=0 AND Grade<=100)

-- （1）执行下面的语句修改Student表，观察语句能否正确运行，解释为什么？
INSERT INTO Student VALUES('20100101','李斌','男',20,'CS','1001',0)
INSERT INTO Student VALUES('20100001','李斌','男',20,'CS','1001',0)--不能运行，原因能在表中插入重复主键
UPDATE Student SET Sno='20100021' WHERE Sname = '张立'--不能运行，原因能在表中插入重复主键



-- （2）执行下面的语句修改SC表，观察语句能否正确运行，解释为什么？
INSERT INTO SC VALUES('20100001','1',78)--不能运行，原因能在表中插入重复主键
INSERT INTO SC VALUES('20100001',null,78)--不能运行，原因能在表中插入重复主键，且不能有null值


-- 2．检查唯一约束
-- 执行下面的语句修改Course表，观察语句能否正确运行，解释为什么？
INSERT INTO Course VALUES('8','JAVA',7, 3)
INSERT INTO Course VALUES('9','数据结构',7, 3)--不能运行，原因能在表中插入重复键

-- 3．检查默认值、允许空值列
-- 运行如下的语句：
INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100102','张盛','男','1008')
-- 观察插入数据行的数值
SELECT * FROM Student WHere Sno='20100102'


-- 4．检查非空约束
-- 下面的语句包含空值，检查运行结果，解释为什么？
INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100103','张盛','男',NULL)--不允许有空值
INSERT INTO Student(Sno,Sname,Ssex) VALUES('20100104','张盛','男')--不允许有空值


-- 5．检查CHECK 约束
-- 执行下面的语句，解释其运行结果。
INSERT INTO SC VALUES('20100001','4',95)
INSERT INTO SC VALUES('20100001','4',102)--主键与上一个插入冲突
INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100103','张盛','男','1008')
INSERT INTO Student(Sno,Sname,Ssex,Sclass) VALUES('20100104','张盛','','1008')--与上一个名字冲突

-- 6．检查外键约束
-- （1）执行下面的语句检查外键约束的作用
INSERT INTO SC VALUES('20100301','1',95)--Student中没有这个学号
INSERT INTO SC VALUES('20100001','10',95)--外键冲突，course中没有这个号
UPDATE SC SET Cno = '10' Where Cno='1'--外键冲突，course中没有这个课程号
UPDATE Course SET Cno = '10' Where Cno='3'--sc中修改有牵连，不能轻易修改




-- （2）执行下面的语句检查对被引用表的约束
DELETE Student WHERE Sno='20100021'
DELETE Student WHERE Sno='20100001'--外键冲突，有学生信息，不能随意修改
UPDATE Course SET Cno = '10' WHERE Cname = '数据库系统原理'--外键有冲突，需要号做先行课，不能随意修改


-- 三、触发器的定义及使用
-- 1．定义触发器
-- （1）定义一个触发器，实现有关学分的完整性约束：当向SC表插入一行选课记录时，自动
-- 将该课程的学分累加到该学生的总学分中。
CREATE TRIGGER tr_INSERT ON SC
FOR INSERT
AS
--声明变量
DECLARE @sno char(8)
DECLARE @cridit int
DECLARE @cno char(4)
--提取插入的数据
SELECT @sno=Sno,@cno=Cno FROM inserted
--提取学生的总学分
SELECT @cridit = Ccredit
FROM SC join Course ON (SC.Cno = Course.Cno)
WHERE SC.Cno = @cno
--更新总学分
UPDATE Student SET Stotal = Stotal + @cridit
WHERE Sno = @sno
GO


-- （2）定义一个触发器，实现对SC表的操作登记：当用户向SC表插入或修改时，记录该操作
-- 到数据库中。
--  创建日志登记表:
CREATE TABLE LOG_TABLE
(
username char(10), --操作人员
date datetime, --修改时间
Sno char(8) , --学生学号
Cno char(4) --课程号
)
--  创建日志登记触发器:
CREATE TRIGGER tr_UPDATE ON SC
FOR INSERT,UPDATE
AS
DECLARE @sno char(8)
DECLARE @cno char(4)
DECLARE @new smallint
SELECT @sno=Sno,@cno=Cno FROM inserted
INSERT INTO LOG_TABLE VALUES(CURRENT_USER,getdate(),@Sno,@Cno);
GO


-- （3）执行插入操作，触发触发器：
INSERT INTO SC VALUES('20100001','6',95)

-- （4）验证触发器是否触发
select * from student
select * from LOG_TABLE