作业十三

最新推荐文章于 2021-07-29 15:21:07 发布

wjpei123

最新推荐文章于 2021-07-29 15:21:07 发布

阅读量293

点赞数

本文链接：https://blog.csdn.net/wjpei123/article/details/106859348

版权

[例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)    /*只能在表级定义主码*/
);

[例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] 显式说明参照完整性的违约处理示例

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表中相应的元组*/
);

[例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不允许取空值，则在  
     列级不允许取空值的定义 可以不写*/
);

[例5.6]建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

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

在这里插入图片描述

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)
);

在这里插入图片描述
[例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)
);

例5.9]当学生的性别是男时，其名字不能以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两个属性值之间的约束条件*/
);

性别是女性的元组都能通过该项检查，因为Ssex=‘女’成立;
当性别是男性时，要通过检查则名字一定不能以Ms.打头
在这里插入图片描述
[例5.10]建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

    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)
        );

[例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 Student 
    DROP CONSTRAINT C4;


```sql
在这里插入代码片

[例5.13] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40
可以先删除原来的约束条件，再增加新的约束条件

ALTER TABLE Student

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);

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

--先定义表SC_U
CREATE TABLE SC_U
	(Sno CHAR(9) PRIMARY KEY,  /*在列级定义主码*/
	 Cno CHAR(9),
	 Oldgrade SMALLINT,
	 Newgrade SMALLINT
	 )

CREATE TRIGGER SC_T		
AFTER UPDATE OF Grade ON SC   
REFERENCING
	 OLD 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)

在此处出现报错：“AFTER”附近有语法错误。”
改为T-sql语句：

CREATE TRIGGER SC_T
ON SC
FOR UPDATE
AS
	declare @OLD SMALLINT
	declare @NEW SMALLINT
	declare @SNO CHAR(9)
	declare @CNO CHAR(4)
IF(UPDATE(Grade))
	BEGIN
	select @OLD =Grade FROM DELETED
	select @NEW =Grade FROM INSERTED
	select @SNO =Sno FROM SC
	select @CNO =Cno FROM SC
	IF(@NEW>=1.1*@OLD)
	INSERT INTO SC_U(Sno,Cno,Oldgrade,Newgrade)
	VALUES (@SNO,@CNO,@OLD,@NEW)
END

[例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;

【例8.8】利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中，假设账户关系为Account（Accountnum，Total）

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 Accout
	            WHERE accountnum=outAccount;
	       IF totalDepositOut IS NULL THEN  /*如果转出账户不存在或账户中没有存款*/
	                ROLLBACK; 	   /*回滚事务*/
	                RETURN
	       END IF;
      IF totalDepositOut< amount THEN    	/*如果账户存款不足*/
	       ROLLBACK; 				/*回滚事务*/
	       RETURN
      END IF 
  SELECT Accountnum INTO inAccountnum FROM Account
      WHERE accountnum=inAccount;
  IF inAccount IS NULL THEN  		/*如果转入账户不存在*/                        
	ROLLBACK; 	         	 		/*回滚事务*/
	RETURN;
  END IF;
  UPDATE Account SET total=total-amount WHERE accountnum=outAccount; 
    /*修改转出账户余额，减去转出额 */
  UPDATE Account SET total=total + amount 
  WHERE accountnum=inAccount; /* 修改转入账户余额，增加转入额 */
  COMMIT;                       	/*提交转账事务 */
END;

[例8.9] 从账户01003815868转10000元到01003813828账户中。
T-sql：

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

建立存储过程

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               	   
		BEGIN
			PRINT '转出账户不存在或账户中没有存款'
			ROLLBACK TRANSACTION TRANS; 	   /*回滚事务*/
			RETURN;
		END;
	/*如果账户存款不足*/
	IF @totalDepositOut < @amount     	
		BEGIN
			PRINT '账户存款不足'
			ROLLBACK TRANSACTION TRANS; 				/*回滚事务*/
			RETURN;
		END
	/*检查转入账户的状态 */  
	SELECT @inAccountnum = accountnum  FROM Account	WHERE accountnum = @inAccount;
	/*如果转入账户不存在*/ 
	IF @inAccountnum IS NULL   		                       
		BEGIN
			PRINT '转入账户不存在'
			ROLLBACK TRANSACTION TRANS; 	         	 		/*回滚事务*/
			RETURN;
		END;
	/*如果条件都没有异常，开始转账。*/ 
	BEGIN
		UPDATE Account SET total = total - @amount	WHERE	accountnum = @outAccount; /* 修改转出账户余额，减去转出额 */
		UPDATE Account SET total = total + @amount	WHERE   accountnum = @inAccount; /* 修改转入账户余额，增加转入额 */
		PRINT '转账完成，请取走银行卡'
		COMMIT TRANSACTION TRANS;                       	/* 提交转账事务 */
		RETURN;
	END

执行存储过程

/*测试，正常情况*/
EXEC	Proc_TRANSFER
		@inAccount = 101,	--转入账户
		@outAccount = 102,	--转出账户
		@amount = 50		--转出金额

SELECT * FROM Account

wjpei123

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
作业十三

[例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)
复制链接

扫一扫