实验二交互式SQL语句的使用.doc

本科实验报告课程名称： 数据库系统原理 实验项目： 交互式SQL语句的使用 实验地点： 4606 专业班级： 学号： 学生姓名： 指导教师： 2012 年 10 月 17 日一 目的与要求 （1）掌握数据库对象的操作过程，包括创建、修改、删除。 （2）熟悉表的各种操作，包括插入、修改、删除、查询。 （3）熟练掌握常用 SQL 语句的基本语法。二 实验设备与环境使用 SQL Server 数据库管理系统提供的 SSMS 和查询编辑器。三 实验内容、实验记录及实验结果与分析（1）实验内容 学生选课系统 要求如下： 建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。 要求认真进行实验，记录各实验用例及执行结果。 深入了解各个操作的功能。（2）实验的具体要求 数据定义 基本表的创建、修改及删除 索引的创建 视图的创建 数据操作 插入数据 修改数据 删除数据 数据查询 单表查询 分组统计 连接查询 嵌套查询 集合查询 视图操作 创建视图 视图查询（3）实验记录、结果 一、数据定义 创建学生选课数据库 ST，包括三个基本表，其中 Student 表保存学生基本信息，Course 表保存课程信息，SC 表保存学生选课信息，其结构如下表所示。 表 2-1 Student 表结构列名称用途类型长度约束备注Sno学号字符8主键Sname姓名字符8Ssex性别字符2Sage年龄整型Sdept所在系字符20Sclass班级字符4 表 2-2 Course 表结构列名称用途类型长度约束备注Cno课程号字符4主键Cname课程名字符40Cpno先修课程号字符4Ccredit学分整型 表 2-3 SC 表结构列名称用途类型长度约束备注Sno学号字符8外键Cno课程号字符4Sage年龄整型1创建、修改及删除基本表 （1）Student 表： CREATE TABLE Student ( Sno CHAR(8) PRIMARY KEY, Sname CHAR(8) , Ssex CHAR(2) NOT NULL, Sage INT, Sdept CHAR(20) );（2）Course 表： CREATE TABLE Course ( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL, Cpno CHAR(4) , Ccredit SMALLINT, ); （3）SC 表： CREATE TABLE SC ( Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno), Cno CHAR(4), Grade SMALLINT, );（4）检查表是否创建成功 SELECT * FROM Student;SELECT * FROM Course;SELECT * FROM SC;（5）修改表结构及约束 增加班级列 ALTER TABLE Student ADD Sclass char(4); 修改年龄列 ALTER TABLE Student ALTER COLUMN Sage smallint; 增加约束 ALTER TABLE Course ADD UNIQUE(Cname);2. 创建、删除索引 （1）为 Course 表按课程名称创建索引 CREATE INDEX iCname On Course(Cname);（2）为 Student 表按学生姓名创建唯一索引 CREATE UNIQUE INDEX iSname ON Student(Sname);（3）为 SC 表按学号和课程号创建聚集索引 CREATE CLUSTERED INDEX iSnoCno ON SC(Sno,Cno DESC);（4）为 Course 表按课程号创建唯一索引 CREATE UNIQUE INDEX iCno ON Course(Cno);3创建视图 建立信息系学生的视图： CREATE VIEW IS_Student AS SELECT Sno,Sname,Sage FROM Student; WHERE Sdept= IS;二、数据操作1插入数据(1)插入到Student表中：INSERT INTO Student VALUES(20100001,李勇,男,20,CS,1001) INSERT INTO Student VALUES(20100002,刘晨,女,19,CS,1001) INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100021,王敏,女,18,MA,1002) INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100031,张立,男,19,IS,1003) INSERT INTO Student(Sno,Sname,Ssex,Sclass)VALUES(20100003,刘洋,女,1001) INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass) VALUES(20100010,赵斌,男,19,IS,1005) INSERT INTO Student VALUES(20100022,张明明,男,19,CS,1002) （2）插入到Course表中：INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES(1,数据库系统原理,5,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES(2,高等数学,null,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,数据处理,null,2) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES(7,C语言,null,4) （3）插入到SC表中：INSERT INTO SC VALUES(20100001,1,92) INSERT INTO SC VALUES(20100001,2,85) INSERT INTO SC VALUES(20100001,3,88) INSERT INTO SC VALUES(20100002,1,90)INSERT INTO SC VALUES(20100002,2,80) INSERT INTO SC(Sno,Cno) VALUES(20100003,1) INSERT INTO SC(Sno,Cno,Grade) VALUES(20100010,3,null) （4）多行插入到表中 创建存一个表，保存学生的学号、姓名和年龄：CREATE TABLE cs_Student ( 学号 char(8), 姓名 char(8), 年龄 smallint ); 插入数据行： INSERT INTO cs_Student SELECT Sno,Sname,Sage FROM student Where Sdept=CS; （5）检查插入到表中的数据 SELECT * FROM Student SELECT * FROM Course SELECT * FROM SC2修改数据 （1）将学生 20100001 的年龄改为 22 岁。 UPDATE student SET Sage = 22 WHERE Sno=20100001; （2）将所有学生的年龄增加一岁： UPDATE Student SET Sage = Sage +1 （3）填写赵斌同学的管理信息系统课程的成绩： UPDATE SC SET Grade = 85 WHERE Sno=20100010 AND Cno=3 （4）将计算机科学系全体学生的成绩加5分： UPDATE sc SET Grade=Grade + 5 WHERE CS=(select Sdept from student where student.Sno=sc.Sno); （5） 将刘晨同学的 2 号课程成绩修改为 80： UPDATE SC SET Grade=80 WHERE Cno=2 AND Sno=(SELECT Sno FROM Student WHERE SC.Sno=Student.Sno AND Sname=刘晨); 将“20100021”同学的学号修改为“20100025”： UPDATE Student SET Sno=20100025 WHERE Sno=20100021;3. 删除数据 （1）删除学号为 201000022 的学生记录： DELETE FROM Student WHERE Sno=20100022 （2）删除学号 20100001 学生的 1 号课程选课记录 将选课信息复制到一个临时表 tmpSC 中： SELECT * INTO tmpSC FROM SC 在 tmpSC 中执行删除操作： DELETE FROM tmpSC WHERE Sno=20100001 and Cno=1 （3） 删除临时表中 20100002 学生的全部选课记录 SELECT * INTO tmpSC FROM SC DELETE FROM tmpSC WHERE Sno=20100002 （4）删除计算机科学系所有学生的选课记录 DELETE FROM tmpSC WHERE CS=(select Sdept from student where student.Sno=tmpSC.Sno );（5）删除全部选课记录： DELETE FROM tmpSC三、数据查询操作 1单表查询 （1）按指定目标列查询 查询学生的详细记录： SELECT * FROM Student; 查询学生的学号、姓名和年龄 SELECT Sno,Sname,Sage FROM Student; （2）目标列包含表达式的查询 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。 SELECT Sname,Year of Birth: ,2004-Sage,LOWER(Sdept) FROM Student; （3）查询结果集中修改列名称 查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名： SELECT Sname, Year of Birth: as BIRTH, 2000-Sage BIRTHDAY, DEPARTMENT = LOWER(Sdept) FROM Student;（4）取消重复行 查询选修了课程的学生学号：比较ALL和DISTINCT的区别 SELECT Sno FROM SC;SELECT DISTINCT Sno FROM SC; （5）简单条件查询 查询计算机科学系全体学生的名单 SELECT Sname FROM Student WHERE Sdept=CS;（6）按范围查询 查询年龄在2023岁之间的学生的姓名、系别和年龄 SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23（7）查询属性值属于指定集合的行 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别： SELECT Sname,Ssex FROM Student WHERE Sdept IN (IS,MA,CS);（8）模糊查询 查询所有姓刘学生的姓名、学号和性别 SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE 刘%（9）查询空值 查询缺少成绩的学生的学号和相应的课程号 SELECT Sno,Cno FROM sc WHERE Grade is null; （10）多重条件查询 查询计算机科学系年龄在 20 岁以下的学生姓名： SELECT Sname FROM student WHERE Sdept=CS and Sage23; 查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系： SELECT Sno,Sname,Sage,Sdept FROM student WHERE Sage=17 OR Sage=18 OR Sage=20 OR Sage=23; 查询年龄不在2124岁之间的学生的姓名、系别和年龄：SELECT Sname,Sdept,SageFROM studentWHERE Sage NOT BETWEEN 21 AND 24;2分组统计 （1）聚集函数的使用 查询学生总人数：SELECT COUNT(*) FROM Student; 查询选修了课程的学生人数：SELECT COUNT(DISTINCT Sno) FROM SC 查询最高分：SELECT MAX(Grade) FROM SC （2）聚集函数作用于部分行统计2号课程的总分、均分和最高分：SELECT SUM(grade) 总分,AVG(grade) 均分,MAX(grade) 最高分FROM sc WHERE Cno=2 （3）分组统计 统计各门课程的选课人数、均分和最高分：select cno 课程号,count(*) 人数,AVG(grade) 均分,MAX(grade) 最高分 from sc group by Cno统计均分大于90的课程 select cno 课程号,count(*) 人数,AVG(grade) 均分,MAX(grade) 最高分 from sc group by Cno having AVG(grade) 90 （4） 统计每个同学的学号、选课数、平均成绩和最高成绩SELECT Student.Sno 学号,COUNT(distinct Course.Cno) 选课数,AVG(Sc.Grade) 平均成绩,MAX(Sc.Grade) 最高成绩FROM SC JOIN Student ON (SC.Sno = Student.Sno)JOIN Course ON (SC.Cno = Course.Cno)GROUP BY Student.Sno; 统计每个班的每门课的选课人数、平均成绩和最高成绩SELECT Student.Sclass 班级,Course.Cname 课程名,COUNT(*) 选课人数,AVG(Sc.Grade) 平均成绩,MAX(Sc.Grade) 最高成绩 FROM SC JOIN Student ON (SC.Sno = Student.Sno)JOIN Course ON (SC.Cno = Course.Cno)GROUP BY Student.Sclass,Course.Cname3. 连接查询 （1）在 WHERE 中指定连接条件 查询每个参加选课的学生信息及其选修课程的情况：SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student,SCWHERE Student.Sno = SC.Sno查询每一门课的间接先修课：SELECT * FROM course first,course secondWHERE first.Cpno=second.Cno;SELECT first.Cno,second.Cpno FROM course first,course second WHERE first.Cpno=second.Cno; （2）在 FROM 中指定连接条件 查询每个参加选课的学生信息及其选修课程的情况：SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student JOIN SC ON (Student.Sno=SC.Sno) （3）使用外连接查询 查询每个学生信息及其选修课程的情况：SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno) （4）复合条件连接查询 查询选修2号课程且成绩在90分以上的所有学生：SELECT Student.Sno, Sname FROM Student join SC ON (Student.Sno = SC.Sno)WHERE SC.Cno= 2 AND SC.Grade 90; （5）多表查询 查询每个学生的学号、姓名、选修的课程名及成绩：SELECT Student.Sno,Sname,Cname,Grade FROM Student,SC,Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno; （6） 查询选修了 2 号课程的同学的学号和姓名 SELECT Sno,SnameFROM StudentWHERE EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=2); 查询各门课程的课程号、课程名称以及选课学生的学号 SELECT Course.Cno,Cname,Student.SnoFROM SC,Course,StudentWHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno; 查询选修了数据库系统原理课程的同学的学号和姓名和成绩SELECT Student.Sno,Sname,GradeFROM Student,Course,SCWHERE Student.Sno=SC.Sno AND Course.Cno=SC.Cno and Cname=数据库系统原理;4. 嵌套查询 （1）由 In 引出的子查询 查询与“刘晨”在同一个系学习的学生：SELECT Sno,Sname,Sdept FROM Student WHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname= 刘晨); （2）由比较运算符引出的子查询找出每个学生超过他选修课程平均成绩的课程号。SELECT Sno, Cno FROM SC x WHERE Grade = ( SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno); （3）带修饰符的比较运算符引出的子查询 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄：SELECT Sname,Sage FROM Student WHERE Sage ALL ( SELECT Sage FROM Student WHERE Sdept= CS) AND Sdept CS ; （4）由 EXISTS 引出的子查询：查询所有选修了1号课程的学生姓名 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1);5. 集合查询 （1）集合并查询计算机科学系的学生及年龄不大于19岁的学生：SELECT * FROM Student WHERE Sdept= CS UNION SELECT