陇东学院《数据库系统概论》上机代码展示

create database jwgl
create table Student(
Sno char(9) primary key,
Sname char(20),
Ssex char(2),
Sage smallint,
Sdept char(6)
);
create table Course(
Cno char(4) primary key,
Cname char(40),
Cpno char(4),
Ccredit smallint,
foreign key (Cpno) references Course(Cno)
);
create table SC(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)


insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男',20,'CS')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215122','刘晨','女',19,'CS')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215123','王敏','女',18,'MA')

insert into Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215125','张立','男',19,'IS')

alter table Student alter column Sdept char(20)

select * from Student



insert into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5',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','Pascal语言','6',4)

select * from Course



insert into SC(Sno,Cno,Grade)
values('201215121','1',92)

insert into SC(Sno,Cno,Grade)
values('201215121','2',85)

insert into SC(Sno,Cno,Grade)
values('201215121','3',88)

insert into SC(Sno,Cno,Grade)
values('201215122','2',90)

insert into SC(Sno,Cno,Grade)
values('201215122','3',80)

select * from SC

alter table Student alter column Sage int;
alter table Course add unique (Cname);


GRANT SELECT
ON TABLE Student
TO U1;

GRANT ALL PRIVILEGES
ON TABLE Student,Course
TO U2,U3;

GRANT SELECT
ON TABLE SC
TO PUBLIC;

GRANT UPDATE(Sno),SELECT
ON TABLE Student
TO U4;

GRANT INSERT
ON TABLE SC
TO U5
WITH GRANT OPTION;

GRANT INSERT
ON TABLE SC
TO U6
WITH GRANT OPTION;

GRANT INSERT
ON TABLE SC
TO U7;

REVOKE UPDATE(Sno)
ON TABLE Student
FROM U4;

REVOKE SELECT
ON TABLE SC
FROM PUBLIC;

REVOKE INSERT
ON TABLE SC
FROM U5 CASCADE;

CREATE ROLE R1;
GRANT SELECT,UPDATE,INSERT
ON TABLE SC
TO R1;
GRANT R1
TO 王平,张明;
REVOKE R1
FROM 王平;

GRANT DELETE
ON TABLE Student
TO R1;

REVOKE SELECT
ON TABLE Student
FROM R1;

CREATE VIEW CS_Student
AS
SELECT*
FROM Student
WHERE Sdept='CS';
GRANT SELECT
ON CS_Student
TO 王平;
GRANT ALL PRIVILEGES
ON CS_Studsent
TO 张明;

AUDIT ALTER,UPDATE
ON SC;

NOAUDIT ALTER UPDATE
ON SC;

CREATE TABLE Student(
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) NOT NULL,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

CREATE TABLE SC(
    Sno CHAR(9) NOT NULL,
    Cno CHAR(4) NOT NULL,
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno)
);

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

CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno),
    FOREIGN KEY(Sno) REFERENCES Student(Sno)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY(Cno) REFERENCES Course(Cno)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);

CREATE TABLE Student(
    Sno CHAR(9) PRIMARY KEY,
    Cno CHAR(4) NOT NULL,
    Ssex CHAR(2) CHECK(Ssex IN('男','女')),
    Sage SMALLINT,
    Sdept CHAR(20)
);

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

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

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<20),
    Ssex CHAR(2)
        CONSTRAINT C4 CHECK(Ssex IN('男','女')),
        CONSTRAINT StudentKey PRIMARY KEY(Sno)
);

ALTER TABLE Student
    DROP CONSTRAINT C4;

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 ASSERTION ASSE_SC_DB_NUM
    CHECK(60>=(
        SELECT COUNT(*)
        FROM Course,SC
        WHERE SC.Cno=Course.Cno AND Course.Cname='数据库'
);


ALTER TABLE SC ADD TERM DATE;
CREATE ASSERTION ASSE_SC_CNUM2
    CHECK(60>=ALL(SELECT COUNT(*) FROM SC GROUP BY Cno,TERM);


CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
    OLDROW AS oldTuple
    NEWROW AS newYuple
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);

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

CREATE TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
REFERENCING NEW row AS newTuple
FOR EACH ROW
BEGIN
    IF(newTuple.Job='教授')AND(newTuple.sal<4000)
        THEN newTuple.sal=4000;
    END IF;
END;