实验二、数据库的建立和维护

 注：接实验一 

 

目录

一、实验名称

二、实验目的

三、实验内容

四、实验任务及结果

---

一、实验名称

       数据库的建立和维护

二、实验目的

      熟悉 SQL 的数据定义语言，熟练掌握使用 T-SQL 和 SQL Server Management Studio 向数 3 据库输入数据、修改数据和删除数据的操作。

三、实验内容

    1）向数据库中录入、修改、删除数据。

    2）熟悉 SQL Server Management Studio 管理工具和 Query Analyzer 工具的建立和维护数据         库的方法。

四、实验任务及结果

  1．使用 T－SQL 实现学生选课数据库的如下操作：

    1）向学生选课数据库中的每个基本表中录入若干条记录，要求记录满足基本表定义的约束条件。（学生、课程和选课表中各录入 10 条以上记录）。

use XSXK
go
insert into student(sno,sname,ssex,sage,sdept,stel)
values
('D001','张三','男',19,'数计','15877485631'),
('D002','李四','男',20,'化学','12345617904'),
('D003','王五','男',20,'管理','12345087904'),
('D004','王华','男',19,'数计','12345787904'),
('D005','李明','男',20,'马院','12345647904'),
('D006','钱欣','女',19,'材料','12345697904'),
('D007','王朝','男',22,'生工','12345347904'),
('D008','李茜','女',21,'管理','12345687904'),
('D009','马汉','男',20,'数计','12345687904'),
('D010','赵虎','男',22,'管理','12345687904'),
('D011','刘辰','女',20,'管理','12456759321'),
('D012','张恬','男',19,'数计','15774465631'),
('D013','严辉','男',19,'管理','15077455631')
go
insert into Course
values('JS001','高数1','4','','贾伟'),
('JS002','C语言','4','','李军'),
('JS003','英语','4','',''),
('JS004','管理学','3','','钟学友'),
('JS005','线性代数','3','','赵春婕'),
('JS006','高数2','4','JS001','贾伟'),
('JS007','体育','3','','黄荣'),
('JS008','马原','3','JS018','袁景衡'),
('JS009','离散数学','2','','鲁秋菊'),
('JS010','计算机网络原理','2','','周军'),
('JS011','JAVA基础','4','JS002','任民宏'),
('JS012','概率论','3','JS006',''),
('JS013','操作系统','2','','李征'),
('JS014','数据结构','3','JS002','李婧'),
('JS015','信息分析','2','','郭天印'),
('JS016','数据库原理及应用','3','','肖海蓉'),
('JS017','JAVA EE','3','JS011','任民宏'),
('JS018','思修','3','','')
go
insert into SC
values
('D001','JS002',70),
('D001','JS001',50),
('D012','JS011',40),
('D012','JS016',90),
('D002','JS001',80),
('D003','JS004',60),
('D002','JS003',70),
('D004','JS013',76),
('D006','JS006',40),
('D001','JS012',55),
('D001','JS013',85),
('D005','JS008',39),
('D006','JS001',90),
('D001','JS017',60),
('D008','JS004',40),
('D002','JS014',70),
('D009','JS014',60),
('D001','JS015',40),
('D007','JS001',78),
('D007','JS006',92),
('D011','JS004',88),
('D011','JS015',56),
('D006','JS005',75),
('D011','JS010',40),
('D013','JS004',78),
('D012','JS017',67),
('D009','JS007',49),
('D013','JS006',80),
('D011','JS001',70),
('D007','JS018',50),
('D002','JS008',66),
('D008','JS015',60),
('D009','JS005',70),
('D003','JS012',59),
('D009','JS009',34),
('D013','JS012',80)
go

    2）通过 Query Analyzer 实现对学生选课数据库的数据增加、数据删除和数据修改操作。

    （1）对于每个学生，求学生的选课门数和平均成绩，并把结果存入学生选课数据库中。

方法一：

create table Ssum(
  sno  char(10) primary key not null,
   cnumber smallint ,
   caverage smallint
  )

go
insert into Ssum
select student.sno,cnumber,caverage
from select sno,COUNT(*) as cnumber,AVG( grade ) as caverage
              from SC
              group by sno)  as X
where student.sno = x.sno
go

方法二：

create table Ssum(
  sno  char(10) primary key not null,
   cnumber smallint ,
   caverage smallint
  )

go
insert into Ssum
select sno,cnumber = COUNT(*), caverage = AVG( grade ) 
from SC
group by sno

go

    （2）将数据库原理与应用课程的学分修改为 4。
update Course
set ccredit = 4
where cname = '数据库原理及应用' 
go

    （3）将姓名为“王华”的学生选修数据库原理及应用课程的成绩增加 5 分。

update SC
set grade = grade + 5
where sno in (select sno
             from student
            where sname = '张恬')  and cno in (select cno
                                       from Course
                                       where cname = '数据库原理及应用' )
go

    （4）删除选课表中成绩低于 40 分的记录。

delete 
from SC
where grade < 40
go

    （5）删除学号为 180001 的学生记录，并讨论该删除操作所受到的约束。

delete 
from student
where sno = 'D001'

go
DELETE 语句与 REFERENCE 约束"FK__SC__sno"冲突。该冲突发生于数据库"XSXK"，表"dbo.SC", column 'sno'。
delete 
from SC
where sno = 'D001'
go
delete 
from student
where sno = 'D001'

go
我们不能直接对student中的学号进行删除操作，我们只有对SC表进行删除操作后，才能对student表进行删除，从这里可以看出数据库的参照完整性。

注：可以设置级联，执行一次delete语句可以对其进行删除

    （6）删除所有选修课程“JAVA”的选课记录。

delete 
from SC
where cno in (select cno
             from Course
             where cname = 'JAVA基础')
go
delete 
from Course
where cname = 'JAVA基础'

go

    （7）将管理学院全体学生的成绩置为空。

update SC
set grade = null
where sno in (select sno
             from student
             where sdept = '管理')

go

    （8）删除学生李萍的所有选课记录。

delete 
from SC
where sno in (select sno
             from student
            where sname = '王五')
go