201509-实验二：交互式SQL

实验二：交互式SQL一、实验目的 1熟悉数据库的交互式SQL工具。 2熟悉通过SQL对数据库进行操作。二、实验平台 SQL SERVER 2008三、实验内容和要求 1在SQL Server 2008中建立一个数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。 2根据以下要求认真完成实验并填写实验报告。1） 数据定义 1熟悉数据库的创建、修改及删除 2熟悉基本表的创建、修改及删除。 3熟悉索引的创建和删除。 4熟悉视图的创建和删除。2）数据操作 1完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）。 2完成各类更新操作（插入数据，修改数据，删除数据）。3）视图的操作 视图的定义（创建和删除），查询，更新（注意更新的条件）。四、实验步骤（一）数据定义一、数据库操作1.创建数据库创建一个数据库TEST，数据文件的逻辑名称为test_data，数据文件存放在D盘根目录下，文件名为testdada.mdf，数据文件的初始存储空间大小为10MB，最大存储空间为50MB，存储空间自动增长量为5MB；日志文件的逻辑名称为test_log，日志文件物理的存放在D盘根目录下，文件名为tesdata.ldf，初始存储空间大小为5MB，最大存储空间为25MB，存储空间自动增长量为5MB。 CREATE DATABASE test ON (NAME=test_data, FILENAME=d:testdata.mdf, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=5) LOG ON (NAME=test_log, FILENAME=d:testdata.ldf, SIZE=5MB, MAXSIZE=25MB, FILEGROWTH=5)2. 修改数据库将TEST数据库中test_data文件的最大存储空间修改为UNLIMITED。 ALTER DATABASE test MODIFY FILE (NAME=test_data, MAXSIZE=unlimited)3. 删除数据库删除数据库TEST。 DROP DATABASE TEST二、基本表操作1.建立基本表在数据库TEST中建立3张基本表：Student、Course和SC。1）创建学生表Student，由以下属性组成：学号 SNO（INT 型，主码），姓名 SNAME（CHAR型，长度为 8，非空唯一），性别 SEX（CHAR 型，长度为 2），所在系 DEPTNO （INT 型）。CREATE TABLE Student(SNO INT PRIMARY KEY, SNAME CHAR(8) NOT NULL UNIQUE, SEX CHAR(2),DEPTNO INT);2）创建课程表 Course，由以下属性组成：课程号 CNO（INT 型），课程名 CNAME（CHAR型，长度为 20，非空），授课教师编号 TNO（INT 型），学分 CREDIT（INT 型）。其中(CNO，TNO)为主码。CREATE TABLE Course( CNO INT,CNAME CHAR(20) NOT NULL,TNO INT,CREDIT INT, PRIMARY KEY (CNO,TNO); 3）创建学生选课表 SC，由以下属性组成：学号 SNO，课程 CNO，成绩 GRADE。所有属性均为 INT 型，其中(SNO，CNO)为主码。CREATE TABLE SC( SNO INT,CNO INT,GRADE INT, PRIMARY KEY(SNO,CNO);4）创建教师表 Teacher，由以下属性组成：教师编号 TNO（INT 型，主码），教师姓名TNAME（CHAR 型，长度为 8，非空），所在系 DEPTNO（INT 型）。CREATE TABLE Teacher(TNO INT PRIMARY KEY, TNAME CHAR(8) NOT NULL, DEPTNO INT);5）创建系表 Dept，由以下属性：系号 DEPTNO（INT 型，主码），系名 DNAME（CHAR 型，长度为 20，非空）。CREATE TABLE Dept( DEPTNO INT PRIMARY KEY, DNAME CHAR(20) NOT NULL); 2.修改基本表 1）在 Student 表中加入属性 SAGE(INT 型)。ALTER TABLE Student ADD SAGE INT;2）将 Student 表中的属性 SAGE 类型改为 SMALLINT 型。ALTER TABLE Student ALTER COLUMN SAGE SMALLINT; 3.删除基本表 1）在所有操作结束后删除 STUDENT 表。 DROP TABLE Student； 2）在所有操作结束后删除 COURSE 表。 DROP TABLE Course； 3）在所有操作结束后删除 SC 表。 DROP TABLE SC； 4）在所有操作结束后删除 TEACHER 表。 DROP TABLE Teacher； 5）在所有操作结束后删除 DEPT 表。 DROP TABLE Dept； 三、索引操作1.建立索引 1）在 Student 表上建立关于 SNO 的唯一索引。 CREATE UNIQUE INDEX stusno ON Student(SNO); 2)在 Course 表上建立关于 CNO 升序的唯一索引。 CREATE UNIQUE INDEX coucno ON Course(CNO); 2.删除索引 1）删除 Student 表上的索引 stusno。 DROP INDEX stusno； 2）删除 Course 表上的索引 coucno。 DROP INDEX coucno；四、视图操作1.建立视图 在插入数据的 Student 基本表上为计算机科学与技术系的学生记录建立一个视图CS_STUDENT。CREATE VIEW CS_STUDENT AS SELECT * FROM Student WHERE DEPTNO= (SELECT DEPTNO FROM Dept WHERE DNAME=计算机科学与技术)WITH CHECK OPTION; 2.删除视图 在操作结束后删除视图 CS_STUDENT。 DROP VIEW CS_STUDENT; （二）数据操作一、更新操作1.插入数据 1）向 STUDENT 表插入下列数据： 1001,张天,男,10,201002,李兰,女,10,211003,陈铭,男,10,211004,刘茜,女,20,211005,马朝阳,男,20,22 INSERT INTO Student VALUES(1001,张天,男,10,20); INSERT INTO Student VALUES(1002,李兰,女,10,21); INSERT INTO Student VALUES(1003,陈铭,男,10,21); INSERT INTO Student VALUES(1004,刘茜,女,20,21); INSERT INTO Student VALUES(1005,马朝阳,男,20,22);2）向 COURSE 表插入下列数据：1,数据结构,101,42,数据库,102,43,离散数学,103,44,C 语言程序设计,101,2INSERT INTO Course VALUES(1,数据结构,101,4);INSERT INTO Course VALUES(2,数据库,102,4);INSERT INTO Course VALUES(3,离散数学,103,4);INSERT INTO Course VALUES(4,C 语言程序设计,101,2);3）向 SC 表插入下列数据：1001,1,801001,2,851001,3,781002,1,781002,2,821002,3,861003,1,921003,3,901004,1,871004,4,901005,1,851005,4,92INSERT INTO SC VALUES(1001,1,80);INSERT INTO SC VALUES(1001,2,85);INSERT INTO SC VALUES(1001,3,78);INSERT INTO SC VALUES(1002,1,78);INSERT INTO SC VALUES(1002,2,82);INSERT INTO SC VALUES(1002,3,86);INSERT INTO SC VALUES(1003,1,92);INSERT INTO SC VALUES(1003,3,90);INSERT INTO SC VALUES(1004,1,87);INSERT INTO SC VALUES(1004,4,90);INSERT INTO SC VALUES(1005,1,85);INSERT INTO SC VALUES(1005,4,92);4）向 TEACHER 表插入下列数据：101,张星,10102,李珊,10103,赵天应,10104,刘田, 20INSERT INTO Teacher VALUES(101,张星,10);INSERT INTO Teacher VALUES(102,李珊,10);INSERT INTO Teacher VALUES(103,赵天应,10);INSERT INTO Teacher VALUES(104,刘田, 20);5）向 DEPT 表插入下列数据：10,计算机科学与技术20,信息INSERT INTO Dept VALUES(10,计算机科学与技术);INSERT INTO Dept VALUES(20,信息);2.修改数据将张星老师数据结构课的学生成绩全部加 2 分UPDATE SCSET GRADE = GRADE + 2WHERE CNO IN(SELECT CNOFROM Course,TeacherWHERE Course.TNO = Teacher.TNO AND Teacher.TNAME=张星);3.删除数据删除马朝阳同学的所有选课记录DELETE FROM SCWHERE SNO IN(SELECT SNOFROM StudentWHERE SNAME = 马朝阳);二、查询操作1.单表查询1）查询所有学生的信息。SELECT *FROM Student；结果：1001张天男10 201002 李兰女10 211003 陈铭男10 211004 刘茜女20 211005 马朝阳男20 222）查询所有女生的姓名。SELECT SNAMEFROM StudentWHERE SEX = 女;结果：李兰刘茜3）查询成绩在 80 到 89 之间的所有学生选课记录，查询结果按成绩的降序排列。SELECT *FROM SCWHERE GRADE = 80AND GRADE = 89ORDER BY GRADE DESC;结果：1004 1 871002 3 861001 2 851005 1 851002 2 821001 1 804）查询各个系的学生人数。SELECT DEPTNO,count(SNO)FROM StudentGROUP BY DEPTNO;结果：10 320 22.连接查询查询信息系年龄在 21 岁以下（含 21 岁）的女生姓名及其年龄。SELECT SNAME,AGEFROM Student,DeptWHERE Student.DEPTNO = Dept.DEPTNOAND Dept.DNAME = 信息AND AGE = 21AND SEX = 女;结果：刘茜 213.嵌套查询1）查询修课总学分在 10 学分以下的学生姓名。SELECT SNAMEFROM StudentWHERE SNO IN(SELECT SNOFROM SC,CourseWHERE SC.CNO = Course.CNOGROUP BY SNOHAVING SUM(CREDIT)25） 查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。select sname,cno,gradefrom student,scwhere grade80 and student.sno=sc.snoorder by grade asc6） 列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。select top 3 s.sno,sname,sdept,grade fromstudent s join sc on sc.sno=ojoin course c on o =owhere ame =数据库基础order by grade desc7） 查询有考试成绩的所有学生的姓名、修课名称及考试成绩，要求将查询结果保存到一张新的永久表中，假设新表名为new_sc。select sname,cname,grade into new_sc from student s join sc on s.sno=sc.snojoin course c on o =owhere grade is not null8） 查询选修VB的学生学号、姓名、所在系和成绩，并对所在系进行如下处理： 当所在系为“计算机系”时，显示“CS”； 当所在系为“信息管理系”时，显示“IS”； 当所在系为“通信工程系”时，显示“CO”； 对其他系，均显示“OTHER”；select sc.sno,s.sname,case sdept when 计算机系thenCSwhen 信息系thenISwhen 数学系thenCOelse OTHERend as所在系 ,grade 成绩from student s join sc on s.sno=sc.snojoin course c on o =owhere cname=vb9） 查询“C001”课程的考试成绩高于“C001”课程的平均成绩的学生的学号和“C001”课程成绩。select sc.sno,sc.grade from sc where o=C001and grade (select AVG(grade)from sc where cno=C001)5、 将计算机系所有选修“计算机文化学”课程的学生成绩加10分，分别用子查询和多表连接形式实现。子查询：update sc set grade=grade +10where sno in(select sno from student where sdept=计算机系)and cno in(select cno from course where cname=计算机文化学)多表查询：update sc set grade=grade +10 from sc join student s on sc.sno=s.sno join course c on o=o where sdept=计算机系and ame=计算机文化学6、 删除VB考试成绩最低的学生的VB修课记录。delete from sc where grade =(select MIN(grade)from sc join course c on o=owhere ame=vb)and cno in(select cno from course where cname =VB)7、写出创建满足下述要求视图的SQL语句，并执行这些语句。1） 查询学生的学号、姓名、所在系、课程号、课程名、课程学分；Create view v1 As Select s.sno,sname,sdept,o,cname,credit From student s join sc on s.sno = sc.snoJoin course c