数据库作业13：SQL练习8

56 阅读 0 评论 37 点赞

我是靠谱客的博主知性唇膏，最近开发中收集的这篇文章主要介绍数据库作业13：SQL练习8，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

数据库的完整性

数据符合现实语义。
同一对象在不同关系表中数据是符合逻辑的。

- 实体完整性
5.1 将Student表中的Sno属性定义为码

CREATE TABLE Student
                (  Sno  CHAR(9)  PRIMARY KEY,
                   Sname  CHAR(20) NOT NULL,     
                   Ssex  CHAR(2),
                   Sage  SMALLINT,
                   Sdept  CHAR(20)
                );在列级定义
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表中相应的元组*/
           );

属性上约束条件定义：
列值非空（NOT NULL）
列值唯一（UNIQUE）
检查列值是否满足一个条件表达式（CHECK）
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),  
        );

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

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

5.7 Student表的Ssex只允许取“男”或“女”。

 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)，         
            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.%')
     
          );

完整性约束命名子句
CONSTRAINT <完整性约束条件名><完整性约束条件>
<完整性约束条件>包括NOT NULL、UNIQUE、PRIMARY KEY短语、FOREIGN KEY短语、CHECK短语等

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

修改表中的完整性限制
使用ALTER TABLE语句修改表中的完整性限制

5.12 去掉例5.10 Student表中对性别的限制

ALTER TABLE Student 
        DROP CONSTRAINT C4;

例5.13 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

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

触发器

语法格式
CREATE TRIGGER <触发器名>
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

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

CREATE TABLE SC_U
(Sno CHAR(10) NOT NULL,
 Cno CHAR(5) NOT NULL,
  Oldgrade SMALLINT CONSTRAINT X1 CHECK(Oldgrade BETWEEN 0 AND 100),
  Newgrade SMLLINT CONSTRAINT X2 CHECK (Newgrade BETWEEN 0 AND 100),
  CONSTRAINT SCUKEY PRIMARY KEY (Sno,Cno)
  );

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)

[例5.22] 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。
建立StudentInsertLog表

CREATE TABLE StudentInsertLog
(
Numbers INT
)

标准sql

CREATE TRIGGER Student_Count
 AFTER INSERT ON Student            
 REFERENCING
      NEW TABLE AS DELTA
 FOR EACH STATEMENT  
        INSERT INTO StudentInsertLog (Numbers)
     SELECT COUNT(*) FROM DELTA

t-sql

CREATE TRIGGER Student_Count
ON Student            
AFTER
INSERT
AS 
    INSERT INTO StudentInsertLog(Numbers)
   SELECT COUNT(*) FROM Student

当插入新的学生记录时，触发器启动，自动StudentInsertLogUser记录用户名和操作时间
建表

CREATE TABLE StudentInsertLogUser
(
  UserName nchar(10),
  DateAndTime datetime
)

创建触发器

CREATE TRIGGER Student_Time
ON Student            
AFTER
INSERT
AS 
 declare @UserName    nchar(10)
 declare @DateTime    datetime
 --相当于定义变量，变量名前要加@
 select @UserName = system_user
 select @DateTime = CONVERT(datetime,GETDATE(),120) --2018-04-11 16:33:10
 INSERT INTO StudentInsertLogUser(UserName,DateAndTime)
 VALUES (@UserName,@DateTime)

测试

INSERT
INTO  Student
VALUES ('131561202','纲','男',20,'CS');
SELECT * FROM Student;
SELECT * FROM StudentInsertLog;
SELECT * FROM StudentInsertLogUser;

结果：
在这里插入图片描述

【例5.23】定义一个BEFORE行级触发器，为教师表定义完整性规划“教授的工资不能低于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;

储存过程

创建储存过程
CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,…]) AS <过程化SQL块>；
[例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 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;

执行储存过程

CALL/PERFORM  PROCEDURE 过程名([参数1,参数2,...])；

例8.9] 从账户01003815868转10000元到01003813828账户中。

CALL PROCEDURE       TRANSFER(01003813828,01003815868,10000);

在SQL-sever中

新建用户

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

在这里插入图片描述

最后

以上就是知性唇膏为你收集整理的数据库作业13：SQL练习8的全部内容，希望文章能够帮你解决数据库作业13：SQL练习8所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：Other
浏览次数：56 次浏览
发布日期：2023-10-23 04:15:39
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_o_22_fz_13_jk5.html

数据库作业13：SQL练习8

概述

数据库的完整性

触发器

储存过程

最后

评论列表共有 0 条评论

发表评论取消回复

数据库作业13：SQL练习8

概述

数据库的完整性

触发器

储存过程

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复