SQL Server 数据库原理及应用实验报告（未完）

2022-12-18 13:25:49

实验内容

实验一数据定义
实验二数据基本查询
实验三数据高级查询
实验四数据更新和视图
实验五触发器和存储过程
实验六简单数据库应用系统设计与实现

实验一数据定义

实验目的：
1.熟练掌握SQL语言进行基本表结构的创建。
2.熟练应用SQL语言进行表结构的修改。
3.掌握SQL语言进行基本表的删除。
4.掌握SQL语言进行索引的建立和删除
5.选择具体的数据库管理系统进行实现

实验过程：
第一步打开 Microsoft SQL Service Management Studio 并连接数据库 schTest

use schTest

第二步用SQL语言CREATE TABLE语句创建实验一中学生表student、课程表course和选课表sc及其相应约束
表1 学生信息表student

字段（列名）	数据类型	长度	完整性约束
Sno	字符型	8	主键
Sname	字符型	4	不为空
Ssex	字符型	2	默认值为‘男’，取值范围‘男’，‘女’
Sage	整数型
Sdept	字符型

表2 课程信息表course

字段（列名）	数据类型	长度	完整性约束
Cno	字符型	2	主键
Cname	字符型	30
Credit	整数型
Cpno	字符型	3

表3 选课表SC

字段（列名）	数据类型	长度	完整性约束
Sno	字符型	8	主属性，外键
Cno	字符型	30	主属性、外键
Grade	整数型		取值范围0-100

CREATE TABLE Student
(
	Sno CHAR(9)  PRIMARY KEY,	--学号，主码
	Sname CHAR(6) NOT NULL,	--姓名，取唯一值
	Ssex CHAR(2) DEFAULT '男' CHECK  (Ssex= '男'or Ssex='女' ),	--性别
	Sage INT,	--年龄
	Sdept CHAR(10)	--系院
);
go

CREATE TABLE Course
(
	Cno CHAR(2) PRIMARY KEY,	--课程号
	Cname CHAR(30),	--课程名
	Cpno CHAR(3),	--先行课
	Ccredit SMALLINT,	--学分
);
go

CREATE TABLE SC
(
	Sno CHAR(8),	--学号
	Cno CHAR(2) ,	--课程号
	Grade INT CHECK(Grade >=0 AND Grade <=100)	--成绩
	PRIMARY KEY(Sno,Cno),
		/*主码由两个属性构成，必须作为表级完整性进行定义*/
	CONSTRAINT fk_Sno FOREIGN KEY (Sno) REFERENCES Student(Sno) on delete cascade on update cascade,
	CONSTRAINT fk_Cno FOREIGN KEY (Cno) REFERENCES Course(Cno) on delete cascade on update cascade
);
GO

第三步向创建的表中输入数据，测试所创建的完整性约束是否起作用

INSERT INTO student (sno,sname,ssex,sage,sdept)
VALUES('95001','刘晨','男','20','CS'),
('95002','月儿','女','19','IS'),
('95003','孙空空','女','18','MA'),
('95004','韩信','男','19','IS'),
('95005','刘云','女','17','CS'),
('95006','刘皇姨','女','21','IS'),
('95007','马部长','女','19','MA'),
('95008','李丽','女','20','CS'),
('95009','石头','女','19','IS'),
('95010','张半','女','18','CS');
go

INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES ('1', '数据库', '15', '4')
INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES ('2', '数学', '', '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', '数据处理', '', '2')
INSERT INTO Course (Cno, Cname, Cpno, Ccredit) VALUES ('7', 'PASCAL语言', '6', '4')
go

INSERT INTO sc(sno,cno,grade)
VALUES('95001','2','100'),
('95001','3','95'),
('95001','4','90'),
('95001','6','100'),
('95002','2','98'),
('95002','3',''),
('95003','1','99'),
('95003','2','80'),
('95003','3','98'),
('95004','1','90'),
('95005','7','97'),
('95005','2','37')
go

第四步用SQL语言ALTER语句修改表结构;
（1） STUDENT表中增加一个字段入学时间scome

ALTER TABLE  Student
ADD Scome date
go

（2）删除STUDENT表中sdept字段；

ALTER TABLE Student
DROP COLUMN Sdept
go

（3）删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束；

ALTER TABLE SC
DROP CONSTRAINT fk_Cno
go

（4）重建（3）中删除的约束

ALTER TABLE SC
ADD CONSTRAINT fk_Cno FOREIGN KEY (Cno) REFERENCES Course(Cno)

第五步重新定义一个简单表，然后用SQL语言DROP语句删除该表结构;

CREATE TABLE dd(dd text);
DROP TABLE dd;

第六步用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引;

/*sql create index  定义表student的sname字段降序索引*/
CREATE INDEX ha ON Student (Sname desc);

第七步用SQL语言DROP语句删除索引;

/* drop删除索引*/
DROP INDEX ha ON Student;

实验二数据基本查询

实验目的：
1.掌握SQL查询语句的一般格式
2.掌握简单数据查询操作。
3.熟练掌握各种查询条件的表示。
4.掌握排序和分组操作在SQL语句中的实现。
5.掌握集函数的使用。

实验过程：
（1）查询全体学生的学号和姓名

 SELECT Sno,Sname FROM Student;

（2）查询全体学生的详细记录

SELECT * FROM Student;

（3）查询软件学院的学生姓名、年龄、系别

SELECT Sname,Sage,Sdept 
FROM Student 
WHERE Sdept='IS'

（4）查询所有选修过课程的学生学号（不重复）

SELECT Sno
FROM Student
WHERE Sno IN (select Sno from SC)

（5）查询考试不及格的学生学号（不重复）

SELECT Sno
FROM Student
WHERE Sno IN (
	SELECT  Sno
	FROM SC
	WHERE Grade < 60
)

（6）查询不是软件学院、计算机系的学生性别、年龄、系别

SELECT Ssex,Sage,Sdept
FROM Student
WHERE Sdept != 'CS' AND Sdept != 'IS'

（7）查询年龄18－20岁的学生学号、姓名、系别、年龄；

SELECT Sno,Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 18 AND 20

（8）查询姓刘的学生情况

SELECT *
FROM Student
WHERE Sname LIKE '刘%'

（9）查询姓刘或姓李的学生情况

SELECT *
FROM Student
WHERE Sname LIKE '刘%' OR Sname LIKE '李%'

（10）查询姓刘且名字为两个字的学生情况

SELECT *
FROM Student
WHERE Sname LIKE '刘__'

（11）查询1983年以后出生的学生姓名。

SELECT Sname
FROM Student
WHERE YEAR(GETDATE()) - Sage >= 1983

（12）创建表
studentgrad(sno,mathgrade,englishigrade,chinesegrade)计算学生各科总成绩并赋予别名

CREATE TABLE studentgrad(
	sno varchar(8),
	mathgrade int,
	englishgrade int,
	chinesegrade int
);

SELECT mathgrade + englishgrade + chinesegrade AS 总成绩
FROM studentgrad

（13）利用内部函数 year()查找软件学院学生的出生年份

SELECT YEAR(GETDATE()) - Sage AS 出生年份
FROM Student
WHERE Sdept = 'IS'

（14）利用字符转换函数实现字符联接。

Select sname + '年龄为'+ cast(sage as char(2)) + '岁'
From student

（15）查询全体学生情况，查询结果按所在系升序排列，对同一系中的学生按年龄降序排列。

SELECT *
FROM Student
ORDER BY Sage DESC

（16）查询学生总人数。

SELECT COUNT(*) AS 人数
FROM Student

（17）查询选修了课程的学生人数。

SELECT COUNT(Sno) AS 选课学生人数
FROM Student
WHERE Sno IN(
	SELECT DISTINCT Sno
	FROM SC
)

（18）查询选修了7号课程的学生总人数和平均成绩

SELECT COUNT(*) AS 选修7号课人数, AVG(Grade) AS 平均成绩
FROM SC
WHERE Cno = 7

（19）查询选修6号课程学生的最好成绩

SELECT MAX(Grade) AS 选修6号课最优成绩
FROM SC
WHERE Cno = 6

（20）查询每个系的系名及学生人数。

SELECT Sdept,COUNT(*)
FROM Student
GROUP BY Sdept

（21）查找每门课的选修人数及平均成绩

SELECT Cno,COUNT(*) AS 人数, AVG(Grade) AS 平均成绩
FROM SC
GROUP BY Cno

（22）查找没有先修课的课程情况

SELECT *
FROM Course
WHERE Cpno = NULL

实验三数据高级查询

实验目的：
掌握复杂数据查询操作。
掌握各种连接查询、嵌套查询的使用

实验过程：
（1）查询每个学生及其选课情况；

SELECT Student.Sname, SC.Cno
FROM Student,SC
WHERE Student.Sno = SC.Sno

（2）查询每门课的间接先修课

SELECT c1.cname, c2.cpno
FROM course c1, course c2
WHERE c1.cpno = c2.cno

（3）将STUDENT,SC进行右连接

SELECT * 
FROM student RIGHT JOIN sc 
ON Student.Sno = sc.Sno

（4）查询既选修了2号课程又选修了3号课程的学生姓名、学号；

SELECT Sname,Sno
FROM Student
WHERE Student.Sno IN(
	SELECT Sno 
	FROM SC
	WHERE CNO = '2'
	AND SNO 
	IN(
		SELECT Sno
		FROM SC
		WHERE Cno = '3'
	)
)

（5）查询和刘晨同一年龄的学生

SELECT sname
FROM Student
WHERE Sage IN(
	SELECT Sage
	FROM Student
	WHERE Sname = '刘晨'
)

（6）选修了课程名为“数据库”的学生姓名和年龄

SELECT Sname,Sage
FROM Student
WHERE Sno IN(
	SELECT Sno
	FROM SC,Course 
	WHERE SC.Cno = Course.Cno
	AND Course.Cname = '数据库'
)

（7）查询其他系比IS系任一学生年龄小的学生名单

SELECT Sname
FROM Student
WHERE Student.Sage < (SELECT MIN(Sage) FROM Student WHERE Sdept = 'IS' )

（8）查询其他系中比IS系所有学生年龄都小的学生名单

SELECT Sname
FROM Student 
WHERE Student.Sdept != 'IS'
and Student.Sage < (select min(Sage) from Student where Sdept='IS')

（9）查询选修了全部课程的学生姓名

SELECT Sname
FROM Student
WHERE Sno IN(
	SELECT Sno
	FROM SC
	GROUP BY Sno
	HAVING COUNT(*) = (
		SELECT COUNT(Cno)
		FROM Course
	)
)

（10）查询计算机系学生及其性别是男的学生

SELECT SNAME
FROM Student
WHERE Sdept = 'CS' AND Ssex='男'

（11）查询选修课程1的学生集合和选修2号课程学生集合的差集

SELECT sname
FROM student s1,sc s2
WHERE s1.sno = s2.sno AND  s2.cno = '1'
EXCEPT
SELECT sname
FROM student s1, sc s2
WHERE s1.sno = s2.sno AND s2.Cno ='2'

（12）查询李丽同学不学的课程的课程号

SELECT cno
FROM Course
WHERE cno NOT IN (
	SELECT cno
	FROM sc
	WHERE sno = (select sno from student where sname = '李丽')
)

（13）查询选修了3号课程的学生平均年龄

SELECT AVG(sage) as 平均年龄
FROM student
WHERE sage IN (
	SELECT sage
	FROM student,sc
	WHERE sc.Cno='3' AND student.sno = sc.Sno

)

（14）求每门课程学生的平均成绩

SELECT cno,AVG(grade) as 平均成绩
FROM SC
GROUP BY cno

（15）统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列

SELECT Cno,count(sc.sno) as 人数
FROM  SC
GROUP BY Cno
HAVING count(sno) > 3
ORDER BY count(cno) DESC, cno

（16）查询学号比刘晨大，而年龄比他小的学生姓名。

SELECT s1.sname
FROM student s1, student s2
WHERE s2.Sname = '刘晨' AND s1.sage < s2.Sage AND s1.sno > s2.sno

（17）求年龄大于所有女同学年龄的男同学姓名和年龄

SELECT sname,sage
FROM student
WHERE Ssex='男' 
AND sage > (select max(sage) from student where ssex = '女')

实验四数据更新和视图

实验目的：
1.熟练掌握SQL的常用数据更新操作。
2.熟练应用INSERT,UPDATE,DELETE语句。
3.掌握更新操作的各种格式。
4.掌握视图的创建、更新、删除和查询。

实验过程：
１）插入如下学生记录（学号：95030，姓名：李莉，年龄：18）；

INSERT INTO Student(Sno,Sname,Sage) VALUES (95030, '李莉', 18)

２）插入如下选课记录（95030，1）；

INSERT INTO SC (sno,cno) VALUES (95030,1)

３）计算机系学生年龄改成20；

UPDATE Student SET Sage=20 WHERE Sdept='CS'

４）数学系所有学生成绩改成0；

UPDATE SC
SET Grade=0 
WHERE Sno IN (
	SELECT SNO
	FROM student
	WHERE Sdept = 'MA'
)

５）把低于总平均成绩的女同学成绩提高5分；

UPDATE SC
SET Grade = Grade + 5
WHERE grade < ( select avg(grade) from sc)
AND sno IN( select sno from student where ssex = '女')

６）修改2号课程的成绩，若成绩小于75分提高5％，成绩大于75时提高4％（两个语句实现，注意顺序）；

UPDATE SC
SET Grade = Grade + Grade*0.05
WHERE grade < 75 AND cno = 2

UPDATE SC
SET Grade = grade + grade*0.04
WHERE grade > 75 AND cno =2

７）删除95030学生信息；

DELETE FROM student
WHERE Sno=95030

８）删除SC表中无成绩的记录；

DELETE FROM sc
WHERE grade IS NULL

９）删除张娜的选课记录；

DELETE FROM SC
WHERE SNO = (select sno from student where sname = '张娜')

１０）删除数学系所有学生选课记录；

DELETE FROM sc
WHERE sno IN (
	SELECT sno
	FROM student
	WHERE sdept='MA'
)

１１）删除不及格的学生选课记录；

DELETE FROM sc
WHERE Grade < 60;

１２）查询每一门课程成绩都大于等于80分的学生学号、姓名和性别，把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中；

--创建表 stu
CREATE TABLE STU(
	sno varchar(8) PRIMARY KEY,
	sname varchar(4) NOT NULL,
	ssex VARCHAR(2) DEFAULT '男' CHECK(ssex='男' OR SSEX='女')
)
--插入数据
INSERT INTO STU (sno,sname,ssex)
SELECT sno,sname,ssex
FROM Student
WHERE sno IN (
	SELECT sno
	FROM SC
	WHERE grade >= 80
)

１３）把所有学生学号和课程号连接追加到新表中；

INSERT INTO STU (sno,cno)
SELECT student.sno,sc.cno
FROM student,sc
WHERE student.sno = sc.sno

１４）所有学生年龄增1；

UPDATE  student
SET Sage = sage +1

１５）统计3门以上课程不及格的学生把相应的学生姓名、系别追加到另外一个表中。

insert into stu(sname,sdept) 
select sname,sdept 
from student,sc 
where student.sno=(
	select sno 
	from sc 
	where grade<60 
	group by sno 
	having count(*)>3
);

１６）将学生学号、总成绩、平均成绩定义成一个视图，以便于查询。

go
CREATE VIEW stu_grade(sno,sum_grade,avg_grade) as
SELECT s1.Sno,sum(s2.Grade),avg(s2.grade)
FROM STUDENT s1,sc s2
WHERE s1.sno = s2.Sno
GROUP BY s1.Sno
go

１７）将Student表中所有女生记录定义为一个视图F_STU，并限制对视图的更新操作不能超过视图条件限制。

CREATE VIEW F_STU as
SELECT sno,sname,ssex,sage,sdept
FROM student
WHERE ssex='女'
WITH CHECK OPTION
go

１８）将上一题建立的F_STU视图，更改为给所有男生记录定义的视图，属性包括学号、性别、年龄、所选课程号。

go
alter VIEW F_STU 
as
SELECT s1.sno,s1.Sname,s1.Ssex,s1.Sage,s2.Cno
FROM STUDENT s1,SC s2
WHERE s1.sno = s2.Sno AND s1.Ssex='男'
go

１９）在上一题的视图中找出选修了3号课程的学生。

SELECT *
FROM F_STU
WHERE cno=3

２０）删除视图F_STU。

DROP VIEW F_STU

实验五触发器和存储过程

实验目的：
（1）掌握某主流DBMS支持的SQL编程语言和编程规范，规范设计存储过程；
（2）能够理解不同类型触发器的作用和执行原理，验证触发器的有效性;
（3）培养学生的系统思维，提升解决复杂工程问题所需的编程能力。

在前面创建的数据库基础上，定义BEFORE触发器和AFTER触发器；掌握数据库存储过程定义、存储过程运行，存储过程更名，存储过程删除，存储过程的参数传递。

实验过程：
1、创建一个不带参数的简单存储过程
（1）创建存储过程sp_avggrade，实现查询每门课程学生的平均成绩的功能；

CREATE PROCEDURE sp_avggrade AS
BEGIN
	SELECT Cno,avg(Grade) as 平均成绩
	FROM SC
	group by cno
END
GO

（2）调用该存储过程，实现查询。

EXECUTE sp_avggrade

2、创建一个带输入参数的存储过程
（1）创建存储过程sp_course_avggrade, 实现通过输入课程编号参数查询指定课程编号的课程平均成绩的功能；

GO
CREATE PROCEDURE sp_course_avggrade
@cno char(2)
AS
BEGIN
	SELECT Cno,AVG(grade)as 平均成绩
	FROM SC
	WHERE Cno=@cno
	GROUP BY Cno
END
go

（2）调用该存储过程，获取指定课程的平均成绩

EXECUTE sp_course_avggrade 2

3、创建一个带输入输出参数的存储过程
（1）创建存储过程sp_sdept _student，实现根据用户输入的院系编号参数，查找该学院的学生人数，并以变量形式输出的功能；

GO
CREATE PROC sp_sdept_student
@sdept CHAR(4),
@name char(4) output,
@sumstudent int output
AS
BEGIN
	select @name=sdept  from student where @sdept=Sdept group by Sdept

	SELECT @sumstudent=COUNT(Sdept)
	FROM Student
	WHERE @sdept = Sdept
	GROUP BY Sdept
END
GO

（2）调用该存储过程，以返回变量的形式获取相应院系的学生人数。

DECLARE @sum char(4),@name char(4)	--定义变量
EXECUTE sp_sdept_student CS, @name output, @sum output --执行存储过程，输入两个变量
SELECT @name as 系别, @sum as 系总人数	--查询输出结果
go

4、触发器的创建与使用
（1）在学生表上创建触发器，实现学生表中删除学生记录时，成绩表中该学生成绩记录的级联删除；

--若sc外键不是 on delete cascade，执行下两行代码删除、重建外键
	/*
	ALTER TABLE SC drop CONSTRAINT fk_no;
	ALTER TABLE SC ADD CONSTRAINT fk_sno FOREIGN key (sno) REFERENCES Student(Sno) on delete cascade on update cascade;
	*/
GO
CREATE TRIGGER delete_stu_sc
ON student	--在x表中创建
FOR DELETE	--受x事件触发
AS
BEGIN
	DELETE SC
	FROM SC,deleted d
	WHERE sc.Sno=d.Sno
END
GO

（2）在学生表上创建触发器，若录入的成绩大于100分，小于0分，则拒绝插入记录并显示；

--若创建含记录成绩列的表时，使用了CHECK来约束成绩范围，要删除checx重新创建表

GO
CREATE TRIGGER insert_grade
on sc
FOR INSERT
AS
IF(select grade from inserted) > 100 or (select grade from inserted) <0
BEGIN
	Print '录入成绩不符合规定，拒绝插入记录'
	rollback transaction
END
GO

（3）验证(1)、(2)中触发器。

DELETE  from student where sno='95002'
INSERT INTO  SC VALUES (95006, 7, 156)

实验六简单数据库应用系统设计与实现

实验目的：
（1）理解并能运用数据库设计的常见步骤来设计满足给定需求的概念模型（ER模型）和关系数据模型；
（2）能结合有关的指南对模型进行优化处理。
（3）能够在某主流DBMS上创建数据库模式、所建数据库模式满足用户的基本业务需求;

实验内容:
给出如下选题，每组同学任选一个选题进行相应的设计与实现。具体选题如下：
（1）某汽车维修部门的维修工单如下表所示：

（2）某科技管理部门欲开发一个科研项目申报与评审系统，涉及的部分信息如下：
项目：项目编号，项目名称，申请者，期限，金额，项目描述。
项目类别：类别编号，类别名称。
专家：专家编号，姓名，职称，单位。
其中：根据项目类别，每个申报项目需由对应领域的多位专家进行评审；每位专家只参与一类项目的评审，评审时要记录评审日期和评审意见。
（3）某学生宿舍管理系统，涉及的部分信息如下：
学生：学号，姓名，性别，专业，班级。
寝室：寝室号，房间电话。
管理员：员工号，姓名，联系电话。
其中：每个寝室可同时住宿多名学生，每名学生只分配一个寝室；每个寝室指定其中一名学生担当寝室长；每个管理员同时管理多个寝室，但每个寝室只有一名管理员。

实验过程：
每组同学从以上选题中任选一个进行相应的设计与实现。要求如下：
（1）从简单的选题语义出发，按照数据库设计步骤，分别完成如下内容：概念设计（ER模型），给出满足需求的最终全局ER图，要求模型尽量精简，消除不必要的冗余，并给出理由或说明。

（2）逻辑设计（关系模型），把ER模型转换成适当的关系模式，并进行适当地规范化（可选），设计相关完整性约束。

（3）物理设计，在某一主流DBMS中创建数据库，根据业务需求规划适当索引（包括聚簇索引和非聚簇索引），并设计必要的用户视图（即外模式），完成相关查询或更新要求。

码农公寓

实验内容

实验一 数据定义

实验二 数据基本查询

实验三 数据高级查询

实验四 数据更新和视图

实验五 触发器和存储过程

实验六 简单数据库应用系统设计与实现