GDOU-B-11-112
广东海洋大学学生实验报告书（学生用表）
实验名称
实验2数据的查询、更新
课程名称
数据库系统原理
课程号
1620072
学院(系)
信息学院
专业
计算机科学与技术
班级
    1113
学生姓名
学号
实验地点
科技楼
实验日期
04-12
实验二  数据的查询、更新
一、实验目的
1、掌握用户自定义数据类型的方法
2、掌握用T-SQL语句进行数据的插入、修改、删除的方法
3、熟练掌握SELECT语句，能够运用该语句完成各种查询
二、实验要求
1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；
2、能认真独立完成实训内容；
3、实验后做好实验总结，根据实验情况完成总结报告。
三、实验内容
1、用T-SQL语句，创建一用户自定义数据类型：名称为“char20”，数据类型为varchar，长度为20，允许为空。
sp_addtype char20,'varchar(20)',null
2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修表，并向各表插入如下相应的数据。
(1) 建立“学生课程数据库”数剧库：
CREATE DATABASE 学生课程数据库
ON PRIMARY
(
    NAME=Student_dat,
    FILENAME='D:\学生课程数据库.mdf',
    SIZE=10MB,
    MAXSIZE=50MB,
    FILEGROWTH=10%
)
LOG ON
(
      NAME=Student_log,
      FILENAME='D:\学生课程数据库.ldf',
      SIZE=5MB,
      FILEGROWTH=10%
)
GO
(2)创建学生表
CREATE TABLE Student(
Sno INT PRIMARY KEY ,
Sname CHAR(10) ,
Ssex  CHAR(2) CHECK(Ssex='男' OR Ssex='女'),
Sage  SMALLINT CHECK(Sage BETWEEN 15 AND 30),
Sdept CHAR(20)
) ;
插入数据：
INSERT INTO Student(  Sno    ,Sname,Ssex,Sage,Sdept )
VALUES      ('95001','李敏勇',    '男',    20,    'CS')
INSERT INTO Student(  Sno    ,Sname,Ssex,Sage,Sdept )
VALUES ('95002',    '刘晨',    '女',    19,    'IS')
INSERT INTO Student(  Sno    ,Sname,Ssex,Sage,Sdept )
VALUES ('95003',    '王敏',    '女',    18,    'MA')
INSERT INTO Student(  Sno    ,Sname,Ssex,Sage,Sdept )
VALUES  ('95004',    '张立',    '男',  18 ,  'IS'  );
(2)创建课程表
CREATE TABLE Course(
Cno      CHAR(4) PRIMARY KEY ,
Cname CHAR(10) NOT NULL,
Cpno  CHAR(4),
Credit INT CHECK(Credit>=0 AND Credit<=100),
Teacher char20
);
插入数据：
INSERT INTO Course ( Cno,Cname,Cpno,Credit,Teacher)
VALUES      (1,'数据库',    5,    4,'王芳')
INSERT INTO Course ( Cno,Cname,Cpno,Credit,Teacher)
VALUES  (2    ,'数学',NULL,2,'刘新')
INSERT INTO Course ( Cno,Cname,Cpno,Credit,Teacher)
VALUES  (3    ,'信息系统',1,    4,'刘新')
INSERT INTO Course ( Cno,Cname,Cpno,Credit,Teacher)
VALUES (4,'    操作系统',    6,    3,'高升')
INSERT INTO Course ( Cno,Cname,Cpno,Credit,Teacher)
VALUES (5    ,'数据结构',    7,    4,'宋明')
INSERT INTO Course ( Cno,Cname,Cpno,Credit,Teacher)
VALUES (6    ,'数据处理',NULL,2,'张彬')
INSERT INTO Course ( Cno,Cname,Cpno,Credit,Teacher)
VALUES  (7,'Pascal语言',6,    4,'李磊');
(2)创建学生选修表
CREATE TABLE SC(
Sno      INT FOREIGN KEY REFERENCES Student(Sno),
Cno      CHAR(4) FOREIGN KEY REFERENCES Course(Cno),
Grade INT CHECK( Grade>=0 AND Grade<=100 ),
PRIMARY KEY(Sno,Cno)
);
插入数据：
INSERT INTO SC ( Sno,    Cno,    Grade)
VALUES  ('95001','1',92)
INSERT INTO SC ( Sno,    Cno,    Grade)
VALUES  ('95001','2',85)
INSERT INTO SC ( Sno,    Cno,    Grade)
VALUES  ('95001','3',88)
INSERT INTO SC ( Sno,    Cno,    Grade)
VALUES  ('95002','2',90)
INSERT INTO SC ( Sno,    Cno,    Grade)
VALUES  ('95003','2',55)
INSERT INTO SC ( Sno,    Cno,    Grade)
VALUES  ('95004','2',70)
3、用T-SQL语句，修改上面所建学生课程数据库中数据:
1) 向学生表:Student中加入一条记录：(95030,谢非,男,22,CS)并保存
INSERT INTO Student(  Sno    ,Sname,Ssex,Sage,Sdept )
VALUES      ('95030','谢非','男',22,'CS')
2) 将李敏勇的数据库的成绩改为98分
UPDATE SC   
SET Grade=98
WHERE SC.Sno IN
(SELECT SC.Sno FROM Student,SC
WHERE Student.Sno = SC.Sno AND Student.Sname ='李敏勇'
)  AND
SC.Cno IN (SELECT SC.Cno FROM  SC,Course
WHERE Course.Cno = SC.Cno AND Course.Cname='数据库')
3) 删除学生表Student中谢非的记录并保存
DELETE Student
WHERE Student.Sname ='谢非'
4) 能不能从Student表中删除李敏勇学生的记录,为什么?能不能删除王敏, 张立两个学生的记录?
DELETE Student
WHERE Student.Sname ='李敏勇'
不能删除李敏勇学生的记录：“DELETE 语句与REFERENCE 约束"FK__SC__Sno__07020F21"冲突。该冲突发生于数据库"学生课程数据库"，表"dbo.SC", column 'Sno'。”；
也不能删除王敏, 张立两个学生的记录。
4、用T-SQL语句，完成下面简单的查询
1）查询全体学生的学号、姓名及年龄.
SELECT Sno, Sname ,Sage
FROM Student
2）查询全体学生的姓名, 年龄及所在系（要用小写字母表示系名，并用“系名”来表示
列名）。
SELECT Sname, Sage,LOWER(Sdept) '系名'
FROM  Student
3）查询选修了课程的学生学号
SELECT  Sno
FROM    SC
4）查询信息系全体学生的姓名
SELECT Sname
FROM  Student
WHERE Sdept='IS'
5）查询所有年龄在20岁以下的学生姓名及其年龄
SELECT    Sname,  Sage
FROM  Student
WHERE  Sage<20
6）查询年龄在20到18间的学生的姓名,系别及年龄
SELECT    Sname, Sdept, Sage
FROM  Student
WHERE  Sage BETWEEN 18 AND 20
7）查询年龄不在23到19间的学生的姓名,系别及年龄
SELECT    Sname, Sdept, Sage
FROM  Student
WHERE  Sage<19 OR Sage>23
8）查询不是信息系(IS)和计算机系(CS)学生的姓名和性别
SELECT Sname, Ssex,Sdept
FROM Student
WHERE NOT Sdept='IS' AND NOT Sdept='CS'
9）查询所有姓刘的学生的姓名,学号和性别
SELECT Sname,Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%'
10）查询姓“张”且名为一个汉字的学生的姓名
SELECT Sname
FROM Student
WHERE Sname LIKE '张_'
11）查询名字中第2个字为”敏”字的学生姓名和学号
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE '_敏%'
12）查询所有不姓刘的学生姓名
SELECT Sname
FROM Student
WHERE Sname NOT LIKE '刘%'
13）查询全体学生情况,结果按所在系升序排列,同一系中的学生按年龄降序
SELECT    Sno, Sname, Ssex, Sage, Sdept
FROM        Student
ORDER BY Sdept, Sage DESC
14）查询学生表中所有学生信息，要求只显示前10％行数据
SELECT    TOP 10 PERCENT  *
FROM  Student   
15）按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前2
行数据,若第3行后的数据也等于70分也要显示。
SELECT  TOP 2 WITH TIES Sno,Grade
FROM  SC
WHERE Grade>=70
ORDER BY Grade DESC
16)查询每个学生的学号、课程号及分数，同时统计每个学生的总分
SELECT    Sno, Cno, Grade
FROM    SC
ORDER BY Sno
COMPUTE SUM (Grade)  BY Sno
17)查询每个学生的各科分数、最高分、最低分、总分、平均分
SELECT    Sno, Cno, Grade
FROM    SC
ORDER BY Sno
COMPUTE MAX (Grade) ,MIN (Grade)  ,SUM (Grade) ,AVG (Gradesql统计每个系的学生人数)  BY Sno
5、用T-SQL语句完成下面的查询
1)查询学生的总人数
SELECT    COUNT(*) '总人数'
FROM        Student
2)查询选修了课程的学生人数
SELECT COUNT(*)
FROM Student ,SC
WHERE Student.Sno = SC.Sno
3)计算选2号课程的学生平均成绩
SELECT    AVG(Grade)
FROM  SC
WHERE Cno=2
4)查询选修2号课程的学生最高分数
SELECT    MAX(Grade)
FROM  SC
WHERE Cno=2
5)求各个课程号及相应的选课人数
SELECT Cno,COUNT(Sno) '选课人数'
FROM  SC
GROUP BY Cno
6)查询选修了2门以上的课程的学生学号
SELECT Sno
FROM  SC
GROUP BY Sno
HAVING COUNT( *) > 2
7)查询每个学生及其选修课程的情况
SELECT Student.*,Course.*
FROM Student,SC,Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno
8)查询每一门课的间接先修课(即先修课的先修课)
SELECT Course.Cname,Course_2.Cname '间接先修课'
FROM  Course INNER JOIN
              Course AS Course_1 ON Course.Cno = Course_1.Cpno INNER JOIN
              Course AS Course_2 ON Course_1.Cno = Course_2.Cpno
9)查询选修2号课程且成绩在90分以上（包括90分）的所有学生。
SELECT Sname
FROM  Student
WHERE Sno IN (
              SELECT Sno
                FROM  SC
                WHERE  Cno = 2 AND Grade>=90)
6. 用T-SQL语句完成下面的查询
1)查询与“刘晨”在同一个系学习的学生
SELECT Sno, Sname, Ssex, Sage, Sdept
FROM  Student
WHERE Sdept IN (
            SELECT Sdept
            FROM  Student
            WHERE Sname='刘晨') AND Sname !='刘晨'
2)查询选修了课程名为“数学”的学生学号和姓名
SELECT Student.Sno, Student.Sname
FROM  Student,SC,Course           
WHERE Course.Cname = '数学' AND Student.Sno = SC.Sno AND SC.Cno = Course.Cno
3)查询其它系中比信息系中某一学生年龄小的学生姓名和年龄
SELECT  Sname,Sage
FROM  Student
WHERE Sdept !='IS' AND Sage < ANY (SELECT  Sage
                                FROM  Student
                                WHERE Sdept ='IS' )
4)查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄
SELECT  Sname,Sage
FROM  Student
WHERE Sdept !='IS' AND Sage < ALL (SELECT  Sage
                                FROM  Student
                                WHERE Sdept ='IS' )
5)查询所有选修了2号课程的学生姓名
SELECT Sname
FROM Student , SC
WHERE Student .Sno = SC.Sno AND SC.Cno=2
6)查询没有选修3号课程的学生姓名
SELECT  Sname
FROM Student
WHERE NOT EXISTS (SELECT * FROM SC WHERE Student .Sno = SC.Sno AND Cno=1)
7、用T-SQL语句完成下面的复杂查询
1)至少选修刘老师所授课程中一门课程的女学生姓名
SELECT  Sname
FROM  Student,SC,Course           
WHERE Ssex = '女' AND Course.Teacher LIKE '刘%' AND Student.Sno = SC.Sno AND SC.Cno = Course.Cno
2)检索王同学不学的课程的课程号
SELECT  Cno
FROM Course
EXCEPT
SELECT Course.Cno
FROM  Student,SC,Course           
WHERE Student.Sname LIKE '王%' AND Student.Sno = SC.Sno AND SC.Cno = Course.Cno
3)检索全部学生都选修的课程的课程号与课程名。
SELECT  Cno,Cname
FROM Course
WHERE NOT EXISTS
            (SELECT *
            FROM  Student
            WHERE NOT EXISTS 
                          (SELECT *
                            FROM  SC                           
                            WHERE  Student.Sno = SC.Sno AND SC.Cno = Course.Cno))
4)检索选修课程包含刘老师所授课的学生学号。
SELECT DISTINCT Student.Sno
FROM  Student,SC,Course           
WHERE  Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Teacher LIKE '刘%'
5)求选修课程号为2的学生的平均年龄。
SELECT AVG (Sage) '平均年龄'
FROM  Student,SC
WHERE  Student.Sno = SC.Sno AND SC.Cno=2
6)求刘老师所授课程的每门课程的学生平均成绩。
SELECT Cname,Teacher,AVG (Grade) '平均成绩'
FROM  Student,SC,Course           
WHERE  Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Teacher LIKE '刘%'
GROUP BY Cname,Teacher
7)检索学号比刘同学大，而年龄比他小的学生姓名。
SELECT  Sname
FROM  Student
WHERE Sno>(SELECT Sno FROM  Student WHERE Sname='刘%')
AND Sage<(SELECT Sage FROM  Student WHERE Sname='刘%')
8)求年龄大于女同学平均年龄的男同学姓名和年龄。
SELECT  Sname,  Sage
FROM  Student
WHERE Sage>(SELECT AVG(Sage) FROM Student WHERE Ssex='女') AND Ssex='男'
9)求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECT  Sname,  Sage
FROM  Student
WHERE Sage>(SELECT MAX(Sage) FROM Student WHERE Ssex='女') AND Ssex='男'
10)检索每一门课程成绩都大于等于80分的学生学号、姓名和性别，并把检索到的值送往另一个已存在的基本表S（SNO,SNAME,SEX）。
CREATE TABLE S(
SNO INT PRIMARY KEY ,
SNAME CHAR(10) ,
SEX  CHAR(2) CHECK(SEX='男' OR SEX='女'))
INSERT INTO S(SNO,SNAME,SEX)
SELECT DISTINCT Student.Sno, Sname, Ssex
FROM  Student,SC           
WHERE  Student.Sno = SC.Sno AND  Grade>80
11)把选课数学课不及格的成绩全改为空值。
UPDATE SC
SET Grade=NULL
WHERE '数学' IN (
                SELECT  Cname  FROM  Course,SC
                WHERE SC.Cno = Course.Cno  AND Grade <=60)
12)把王同学的选课信息全部删去。
DELETE SC
WHERE Sno IN (SELECT SC.Sno FROM Student,SC
WHERE  Sname LIKE '王%' AND Student.Sno = SC.Sno)
13)把低于总平均成绩的男同学成绩提高5％。 
UPDATE SC
SET Grade=Grade*1.05
WHERE Grade<(SELECT AVG(Grade) FROM  SC ) AND
Sno IN ( SELECT Sno FROM Student WHERE Ssex='男')
14)检索没有选修‘1’课程的学生学号和姓名
SELECT Sno, Sname
FROM  Student
WHERE NOT EXISTS
(SELECT * FROM SC WHERE Sno=Student .Sno AND Cno=1)
15)检索至少有一门课程超过学生‘95001’一门成绩的学生学号
SELECT DISTINCT Student.Sno
FROM  Student,SC
WHERE Grade > (SELECT MIN(Grade) FROM SC WHERE Sno='95001')
AND  Student.Sno = SC.Sno
16)向学生选修课程表中插入元组“学生95003选修课程1”。
INSERT INTO SC(Sno,Cno)
VALUES( '95003',1)
17)求出女同学的每一年龄组（超过10人）有多少人？要求查询结果按人数升序排列，人数相同的按年龄降序排列。
SELECT  COUNT(Sage)'人数' , Sage
FROM  Student
WHERE Ssex = '女'
GROUP BY  Sage
ORDER BY '人数' ASC,Sage DESC
18)定义视图S_AVG，其中包括学生学号以及每个学生选修课程的门数（要求成绩非空）和平均成绩。
CREATE VIEW S_AVG (学号,选课门数,平均成绩)
AS
SELECT Sno, COUNT(Sno), AVG(Grade)
FROM  SC
GROUP BY Sno
四、思考题
1、在创建基本表时是否可以缺省主码？
答:可以
2、简述GROUP  BY 、COMPUTE 、COMPUTE BY的区别？
答:
a)GROUP BY 生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数;
b)
COMPUTE 生成多个结果集。一类结果集包含每个组的明细行，其中包含选择列表中的表达式。另一类结果集包含组的子聚合，或 SELECT 语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其它表达式。聚合函数在 COMPUTE 子句中指定，而不是在选择列表中;
c)GROUP BY 和聚合函数用同一 SELECT 语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。