数据库第五次实验数据库应用系统的数据库设计实验

CREATE TABLE College(
    cono INT PRIMARY KEY,
    coname CHAR(20) NOT NULL
)

INSERT INTO College
VALUES(001,'数计学院'),
      (002,'经管法学院'),
      (003,'体育学院')

CREATE TABLE Major(
    mno INT PRIMARY KEY,
    mname CHAR(20) NOT NULL,
    cono INT NOT NULL
)

INSERT INTO Major
VALUES(01,'计算机',001),
      (02,'数学',001),
      (03,'法学',002),
      (04,'篮球',003)

CREATE TABLE Class(
    cno INT PRIMARY KEY,
    cname CHAR(20) NOT NULL,
    mno INT NOT NULL,
    cono INT NOT NULL
)

INSERT INTO Class
VALUES(1,'计算机2202',01,001),
      (2,'数学2201',02,001),
      (3,'法学2201',03,002),
      (4,'篮球2005',04,003)

CREATE TABLE Student(
sno CHAR(20) PRIMARY KEY,
sname CHAR(20) NOT NULL,
ssex CHAR(10) NOT NULL CHECK(ssex = '男' OR ssex = '女'),
sage INT NOT NULL,
cno INT NOT NULL,
cname CHAR(20) NOT NULL,
mno INT NOT NULL,
mname CHAR(20),
cono INT NOT NULL,
coname CHAR(20)
)

INSERT INTO Student
VALUES('2201','唐三','男',19,1,'计算机2202',01,'计算机',001,'数计学院'),
      ('2202','小舞','男',18,2,'数学2201',02,'数学',001,'数计学院'),
      ('2203','比比东','男',18,3,'法学2201',03,'法学',002,'经管法学院'),
      ('2005','吴磊','女',22,4,'篮球2005',04,'篮球',003,'体育学院')

-- 课程
CREATE TABLE Course_gb(
id CHAR(20) PRIMARY KEY,
name CHAR(20) NOT NULL,
type CHAR(20) NOT NULL CHECK(type = '公共必修课'),
time INT NOT NULL,
credit INT NOT NULL,
term CHAR(10) NOT NULL CHECK(term = '春' OR term = '秋')
)

INSERT INTO Course_gb
VALUES('201','物理','公共必修课',20,5,'春'),
      ('202','英语','公共必修课',20,5,'秋')

CREATE TABLE Course_gx(
id CHAR(20) PRIMARY KEY,
name CHAR(20) NOT NULL,
type CHAR(20) NOT NULL CHECK(type = '公共选修课'),
time INT NOT NULL,
credit INT NOT NULL,
term CHAR(10) NOT NULL CHECK(term = '春' OR term = '秋')
)

INSERT INTO Course_gx
VALUES('204','唱','公共选修课',13,2,'春'),
      ('205','跳','公共选修课',13,2,'秋')

CREATE TABLE Course_zb(
id CHAR(20) PRIMARY KEY,
name CHAR(20) NOT NULL,
type CHAR(20) NOT NULL CHECK(type = '专业必修课'),
time INT NOT NULL,
credit INT NOT NULL,
term CHAR(10) NOT NULL CHECK(term = '春' OR term = '秋'),
mno INT NOT NULL
)

INSERT INTO Course_zb
VALUES('101','数据库','专业必修课',20,4,'春',01),
      ('102','Java','专业必修课',20,4,'秋',01),
      ('103','数学','专业必修课',20,5,'春',02),
      ('104','刑法','专业必修课',20,2,'春',03),
      ('105','篮球','专业必修课',20,3,'春',04)

CREATE TABLE Course_zx(
id CHAR(20) PRIMARY KEY,
name CHAR(20) NOT NULL,
type CHAR(20) NOT NULL CHECK(type = '专业选修课'),
time INT NOT NULL,
credit INT NOT NULL,
term CHAR(10) NOT NULL CHECK(term = '春' OR term = '秋'),
mno INT NOT NULL
)

INSERT INTO Course_zx 
VALUES('106','前端开发','专业选修课',17,3,'秋',01)

CREATE TABLE Score_gb(
sno CHAR(20),
gbno CHAR(20),
PRIMARY KEY(sno,gbno),
grade INT,
GPA INT
)

INSERT INTO Score_gb
VALUES('2201','201',88,3),
      ('2201','202',99,4),
      ('2202','201',77,2.5),
      ('2202','202',77,2.5),
      ('2203','201',81,3),
      ('2203','202',81,3),
      ('2005','201',91,3.5),
      ('2005','202',81,3)
                           
CREATE TABLE Score_gx(
sno CHAR(20),
gxno CHAR(20),
PRIMARY KEY(sno,gxno),
grade INT,
GPA INT
)

INSERT INTO Score_gx
VALUES('2005','204',100,4),
      ('2005','205',100,4),
      ('2201','204',80,3),
      ('2203','204',60,1)
                      
CREATE TABLE Score_zb(
sno CHAR(20),
zbno CHAR(20),
PRIMARY KEY(sno,zbno),
grade INT,
GPA INT
)

INSERT INTO Score_zb
VALUES('2201','101',88,3),
      ('2201','102',78,2.5),
      ('2202','103',100,4),
      ('2203','104',61,1.5),
      ('2005','105',100,4)

CREATE TABLE Score_zx(
sno CHAR(20),
zxno CHAR(20),
PRIMARY KEY(sno,zxno),
grade INT,
GPA INT
)

INSERT INTO Score_zx
VALUES('2201','106',80,3)

-- 删除
-- 学生信息
go
CREATE TRIGGER delete_stu ON Student FOR DELETE
AS
DECLARE @sno CHAR(20)
SELECT @sno=sno FROM deleted
DELETE FROM Score_gb WHERE sno=@sno
DELETE FROM Score_gx WHERE sno=@sno
DELETE FROM Score_zb WHERE sno=@sno
DELETE FROM Score_zx WHERE sno=@sno

-- 课程信息
go
CREATE TRIGGER delete_gb ON Course_gb FOR DELETE
AS
DECLARE @gbno CHAR(20)
SELECT @gbno=id FROM deleted
DELETE FROM Score_gb WHERE gbno=@gbno
go
CREATE TRIGGER delete_gx ON Course_gx FOR DELETE
AS
DECLARE @gxno CHAR(20)
SELECT @gxno=id FROM deleted
DELETE FROM Score_gx WHERE gxno=@gxno

go
CREATE TRIGGER delete_zb ON Course_zb FOR DELETE
AS
DECLARE @zbno CHAR(20)
SELECT @zbno=id FROM deleted
DELETE FROM Score_zb WHERE zbno=@zbno

go
CREATE TRIGGER delete_zx ON Course_zx FOR DELETE
AS
DECLARE @zxno CHAR(20)
SELECT @zxno=id FROM deleted
DELETE FROM Score_zx WHERE zxno=@zxno

-- 创建视图
go
CREATE VIEW 学生成绩
AS
SELECT Student.sno 学号,sname 姓名,cname 班级,name 课程,grade 成绩,GPA 学分绩点
FROM Student ,Course_gb,Score_gb WHERE
Student.sno=Score_gb.sno AND Course_gb.id=Score_gb.gbno
UNION
SELECT Student.sno 学号,sname 姓名,cname 班级,name 课程,grade 成绩,GPA 学分绩点
FROM Student ,Course_gx,Score_gx WHERE
Student.sno=Score_gx.sno AND Course_gx.id=Score_gx.gxno
UNION
SELECT Student.sno 学号,sname 姓名,cname 班级,name 课程,grade 成绩,GPA 学分绩点
FROM Student ,Course_zb,Score_zb WHERE
Student.sno=Score_zb.sno AND Course_zb.id=Score_zb.zbno
UNION
SELECT Student.sno 学号,sname 姓名,cname 班级,name 课程,grade 成绩,GPA 学分绩点
FROM Student ,Course_zx,Score_zx WHERE
Student.sno=Score_zx.sno AND Course_zx.id=Score_zx.zxno
Go

-- 数据查询
-- （1）给定学号，按学号查询指定学生的基本信息
go
CREATE PROCEDURE stu_id @id CHAR(20)
AS
    SELECT*FROM Student
    WHERE sno=@id
    go
    EXECUTE stu_id '2201'

-- （2）给定姓名，按姓名查询指定学生的基本信息。
go
CREATE PROCEDURE stu_name @name CHAR(20)
AS
    SELECT*FROM Student
    WHERE sname=@name
    go
    EXECUTE stu_name '吴磊'

-- （3）给定课程号，按课程号查询指定课程的基本信息。
go
CREATE PROCEDURE c_id @id CHAR(20)
AS
    SELECT*FROM Course_gb
    WHERE id=@id
    UNION
    SELECT*FROM Course_gx
    WHERE id=@id
    UNION
    SELECT id,name,type,time,credit,term FROM Course_zb
    WHERE id=@id
    UNION
    SELECT id,name,type,time,credit,term FROM Course_zx
    WHERE id=@id
    go
    EXECUTE c_id '102'

-- （4）给定课程名，按课程名查询指定课程的基本信息。
go
CREATE PROCEDURE c_name @name VARCHAR(20)
AS
    SELECT*FROM Course_gb
    WHERE name=@name
    UNION
    SELECT*FROM Course_gx
    WHERE name=@name
    UNION
    SELECT id,name,type,time,credit,term FROM Course_zb
    WHERE name=@name
    UNION
    SELECT id,name,type,time,credit,term FROM Course_zx
    WHERE name=@name
    go
    EXECUTE c_name 'Java'
-- （5）给定学号和课程名，按学号和课程号查询指定学生所修指定课程的成绩和学分绩点。
go
CREATE PROCEDURE s_0 @sno CHAR(20),@cno CHAR(20)
AS
    SELECT grade,GPA FROM Score_gb
    WHERE sno=@sno AND gbno=@cno
    UNION
    SELECT grade,GPA FROM Score_gx
    WHERE sno=@sno AND gxno=@cno
    UNION
    SELECT grade,GPA FROM Score_zb
    WHERE sno=@sno AND zbno=@cno
    UNION
    SELECT grade,GPA FROM Score_zx
    WHERE sno=@sno AND zxno=@cno
    go 
    EXECUTE s_0 '2005','204'
-- （6）给定学号，按学号查询指定学生所修全部课程的课程名、成绩和学分绩点。要求使用所建立的“学生成绩” 视图。
go
CREATE PROCEDURE s_1 @id CHAR(20)
AS
    SELECT 学号,姓名,班级,课程,成绩,学分绩点
    FROM 学生成绩 WHERE 学号=@id
    go
    s_1 '2005'
-- （7）给定班级和课程名，按班级和课程号查询指定班级所有学生选修指定课程的成绩，查询结果以学号、姓名、成绩、学分绩点的形式显示。要求使用所建立的“学生成绩” 视图。
go
CREATE PROCEDURE s_2 @class VARCHAR(20),@name VARCHAR(20)
AS
    SELECT 学号,姓名,成绩,学分绩点
    FROM 学生成绩 
     WHERE 班级=@class AND 课程=@name
    go 
    s_2 '篮球2005','唱'

-- （8）查询每个学生的学分绩点的总和及平均学分绩点。
SELECT 姓名,SUM(学分绩点)学分绩点总和,AVG(学分绩点)平均学分绩点
FROM 学生成绩 GROUP BY 姓名

-- 数据更新
-- （1）插入一个学生的基本信息。
INSERT INTO Student
VALUES('2204','丁晨曦','男',23,4,'篮球2005',04,'篮球',003,'体育学院')

-- （2）插入一门课程的基本信息。
INSERT INTO Course_gx
VALUES(206,'rap','公共选修课',20,2,'秋')

-- （3）插入一个学生某一门课的成绩。
INSERT INTO Score_gx
VALUES('2204','206',73,6)

-- （4）给定学号，按学号修改指定学生的基本信息。
go
CREATE PROCEDURE stu_id1 @id CHAR(20)
AS
    UPDATE Student SET ssex='女'
    WHERE sno=@id
    go
    stu_id1 '2204'

-- （5）给定课程号，按课程号修改指定课程的基本信息。
go
CREATE PROCEDURE c_id1 @id CHAR(20)
AS
    UPDATE Course_gb SET Time =Time+1
    WHERE id=@id
    
    UPDATE Course_gx SET Time =Time+1
    WHERE id=@id
    
    UPDATE Course_zb SET Time =Time+1
    WHERE id=@id
    
    UPDATE Course_zx SET Time =Time+1
    WHERE id=@id
go
c_id1 '105'

-- （6）给定学号和课程名，按学号和课程名修改指定学生所修指定课程的成绩。
go
CREATE PROCEDURE s_01 @sno CHAR(20),@cname VARCHAR(20)
AS
    UPDATE Score_gb SET  grade=grade+1
    FROM Course_gb
    WHERE sno=@sno AND name=@cname
    
    UPDATE Score_gx SET  grade=grade+1
    FROM Course_gx
    WHERE sno=@sno AND name=@cname

    UPDATE Score_zb SET  grade=grade+1
    FROM Course_zb
    WHERE sno=@sno AND name=@cname

    UPDATE Score_zx SET  grade=grade+1
    FROM Course_zx
    WHERE sno=@sno AND name=@cname
    go
    s_01 '2201','数据库'

-- （7）给定学号，按学号删除指定学生的基本信息及修课信息。
go
CREATE PROCEDURE s_11 @id CHAR(20)
AS 
    DELETE FROM Student WHERE sno=@id
go
s_11 '2202'

-- （8）给定学号和课程名，按学号和课程名删除指定学生所修指定课程及成绩信息。
go
CREATE PROCEDURE s_12 @sno CHAR(20),@name VARCHAR(20)
AS
    DELETE FROM Score_gb WHERE sno=@sno AND gbno=(SELECT id FROM Course_gb WHERE name=@name)
    DELETE FROM Score_gx WHERE sno=@sno AND gxno=(SELECT id FROM Course_gx WHERE name=@name)
    DELETE FROM Score_zb WHERE sno=@sno AND zbno=(SELECT id FROM Course_zb WHERE name=@name)
    DELETE FROM Score_zx WHERE sno=@sno AND zxno=(SELECT id FROM Course_zx WHERE name=@name)
go
s_12 '2201','前端开发'

-- 数据控制
-- 先新建几个不同的数据库用户，给他们赋予不同的权限，然后自己以这些不同的用户登
-- 录 SQL Server，比较操作权限的不同。

go
exec sp_addlogin'cxk','1234'
go
GRANT SELECT ON Student TO cxk WITH GRANT OPTION

go
exec sp_addlogin'lilies','1234'
go
GRANT UPDATE ON Student TO lilies WITH GRANT OPTION