SQL语言的应用 南京邮电大学软件工程与数据库实验报告一

实验报告一
实验名称：SQL语言的应用
指导教师：茅苏
实验类型：验证
实验学时：4*2
实验时间：2012年9月28日
一、实验目的和要求
练习使用SQL SERVER数据库产品，熟练使用查询分析器和企业管理器；
掌握SQL语言中常用的语句：用DDL创建基本表；用DML插入、修改、删除数据；用QL查询数据等。

二、实验环境(实验设备)
硬件：个人计算机；
软件：MS SQL SERVER环境。

三、实验原理及内容
1．用SQL SERVER的企业管理器创建数据库
数据库名称：10001927db
操作步骤：1、单击左侧的SQL Server组 右键 新建SQL Server注册 下一步 在左侧的“可用的服务器”栏选中或输入CC-PC，点击中间
的添加，将CC-PC添加到右侧的“添加的服务器”栏 下一步
选中“登录时使用Windows身份认证”，然后点击下一步 选中“在
现有SQL Server组中添加SQL Server”，然后点击下一步 完
成 关闭。

2、单击左侧的SQL Server组下面可用的已注册子组里面的数据库→
右键→新建数据库→输入数据库名称10001927db
2．用查询分析器在上一步创建的数据库中完成以下功能
（1）进入查询分析器并选择操作的数据库
操作步骤：1、开始→程序→MS SQL Server→查询分析器
确认SQL Server(s)为CC-PC
选择Windows身份认证
点击确定
2、在工具栏的下拉链表种选择名为10001927db的数据库。

（2）建立基本表：学生、课程和选课，写出DDL语句。

要求：需为每张表建立主键，其他完整性约束可自己添加。

建立学生表
create table student
( 学号 char(5)not null unique,
姓名 char(20),
年龄 int,
性别 char(1),
);
添加主键学号
alter table student add primary key(学号); 课程
建立课程表
create table class
( 课程号 char(5)not null unique,
课程名 char(20),
学时 int,
学分 int,
课程性质 CHAR(20),
);
添加主键课程号
alter table class add primary key(课程号);
建立选课表
create table sc
( 学号 char(5)not null unique,
课程号 char(5)not null unique,
成绩 int,
);
添加外键
alter table sc add constraint 课程号 foreign key (课程号) references class(课程号);
alter table sc add constraint 学号foreign key (学号) references student(学号);
（3）写出向数据库中插入和修改数据的DML语句
1）在上述3张表中插入相关数据，每张表至少插入2条记录。

DML语句：
向学生表中插入数据
insert into student values('S1','WANG',20,'M');
insert into student values('S2','LIU',19,'F');
insert into student values('S3','CHEN',22,'M');
向课程表中插入数据
insert into class values('C2','MATHS',64,4,'基础');
insert into class values('C4','PHYSICS',48,3,'基础');
insert into class values('C3','C',32,2,'专业基础');
insert into class values('C1','DB',48,3,'专业');
向选课表中插入数据
首先删除约束
alter table sc drop constraint UQ__sc__7C8480AE
insert into sc values('S1','C2',89);
insert into sc values('S2','C1',70);
insert into sc values('S3','C2',NULL);
insert into sc values('S1','C2',80);
insert into sc values('S2','C4',79);
未插入时：学生表
课程表
选课表
插入后：学生表
课程表
选课表
2）把课程性质为“基础”的学分提高1学分，学时提高8学时。

DML语句：
update class
set 学分 = 学分+1,学时 = 学时+8
where 课程性质 like '%基础';
修改后：
3）LIU同学因病退学，删除相应的数据。

DML语句：1、首先删除选课表中的相关信息，否则学生表中的信息不能删除，因为选课表中有学生表的外键
delete from sc
where 学号 in (select 学号
from student
where 姓名 = 'LIU');
2、删除学生表中的信息
delete from student
where 姓名 = 'LIU';
修改后：
选课表
学生表
4）把MATH课的非空成绩提高10%。

DML 语句：
update sc
set 成绩 = 成绩*1.1
where 成绩 is not NULL;
修改后：
（4）写出检索数据的QL语句
1）检索课程性质中包含“基础”的课程信息。

QL 语句：
select * from class
where 课程性质 like '%基础';
运行结果：
2）检索没有选修C2课程的学生学号与姓名。

QL语句：select from student
where 学号<>all(select 学号
from class
where 课程号 ='C2' );
检索结果：
检索得到的是空表，因为选修C2的人是LIU，而他的数据在之前的操作中被删除了。

3）查询平均成绩大于80分的学生学号、姓名和平均成绩，结果按平均成绩降序排列。

QL语句：
select student.学号,姓名, AVG(成绩)
from student,sc
group by student.学号,姓名
having AVG(成绩)>80
order by AVG(成绩) desc;
查询结果：
四、实验小结（包括问题和解决方法、心得体会、意见与建议等）
对于选课表，在建立之前不能设置主键和外键，应首先删除UNIQUE KEY约束然后插入数据，最后再建立主键和外键，否则由于表中有重复的数据将导致插入失败。