SQL Server 数据库原理 实验四 数据更新和视图

时间：2025-05-11 08:46:57

如果你对其他数据库原理的实验感兴趣，请考虑阅读我的专栏

SQL Server实验系列

实验四 数据更新和视图

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

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

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

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

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

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

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

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

UPDATE SC
SET Grade=0 
WHERE Sno IN (
	SELECT SNO
	FROM student
	WHERE Sdept = 'MA'
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

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

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

６）修改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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

７）删除95030学生信息；

DELETE FROM student
WHERE Sno=95030
  • 1
  • 2

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

DELETE FROM sc
WHERE grade IS NULL
  • 1
  • 2

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

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

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

DELETE FROM sc
WHERE sno IN (
	SELECT sno
	FROM student
	WHERE sdept='MA'
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

DELETE FROM sc
WHERE Grade < 60;
  • 1
  • 2

１２）查询每一门课程成绩都大于等于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
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

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

INSERT INTO STU (sno,cno)
SELECT student.sno,sc.cno
FROM student,sc
WHERE student.sno = sc.sno
  • 1
  • 2
  • 3
  • 4

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

UPDATE  student
SET Sage = sage +1
  • 1
  • 2

１５）统计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
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

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

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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

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

CREATE VIEW F_STU as
SELECT sno,sname,ssex,sage,sdept
FROM student
WHERE ssex='女'
WITH CHECK OPTION
go
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

１８）将上一题建立的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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

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

SELECT *
FROM F_STU
WHERE cno=3
  • 1
  • 2
  • 3

２０）删除视图F_STU。

DROP VIEW F_STU
  • 1

相关文章