上机实验指导

1、上机实验指导实验内容和思考参考答案数据库技术与应用实践教程SQL Server 2005上机实验指导（实验思考）一参考答案实验1:安装及管理工具使用 略。 实验2: SQL Server数据库的管理 二、实验内容及步骤4.单击 新建查询”打开查询设计器窗口， 在查询设计器窗口 中使用 Transact-SQL 语句 CREATE DATABASE 创建 studb数据库。然后通过系统存储过程sp_helpdb查看系统中的数据库信息。 CREATE DATABASE studb ONNAMEstudb_dat FILENAMEC:DataBasestudb.mdf EXEC sp_helpdb

2、5 . 在查询设计器中使用 Transact-SQL 语句ALTERDATABASE 修改studb数据库的设置，指定 数据文件大小为 5MB ,最大文件大小为 20MB ,自动递增大 小为 1MB。 ALTER DATABASE studb MODIFY FILENAMEstudb_data SIZE 5MBMAXSIZE 20MB FILEGROWTH1MB 6 .在查询设计器中为 studb数据库增加一个日志文 件，命名为 studb_Log2，大小为 5MB，最大文件大小为10MB。 ALTER DATABASE studb ADD LOG FILENAME studb_log2FIL

3、ENAMEstudb_log2.ldf SIZE 5MB MAXSIZE 10MB 8 . 使用 Transact -SQL 语句 DROP DATABASE 删 除 student_db 数据库。DROP DATABASE student_db 实 验3 SQL Server数据表的管理 二、实验内容及步骤5.使用 Transact-SQL 语句 CREATE TABLE 在 studentsdb数据 库中创建 grade 表。 CREATE TABLE grade 学号 char 4NULL 课程编号 char 4 NULL 分数 char 5 NULL ON PRIMARY 8 使用 T

4、ransact-SQL 语句 INSERT INTO.VALUES 向 studentsdb 数据库的 grade 表插入数 据： 学号为 0004，课程编号为 0001，分数为 80。 INSERT grade VALUES 00040001 80 9 使用 Transact-SQL 语句 ALTER TABLE 修改 curriculum 表的 “课程编号 ”列，使之为 非空。 ALTER TABLE curriculum ALTER COLUMN 课程编 号 NOT NULL 10 使用 Transact-SQL 语句 ALTER TABLE修改grade表的 分数”列，使其数据类型为r

5、eal。ALTER TABLE grade ALTER COLUMN 分数 real 11 使 用 Transact-SQL 语 句 ALTER TABLE 修 改 student_info 表的 “姓名 ”列，使其列名为 “学生姓名 ”，数据类型为 vacharlO,非空。 alter table student_info add 学生姓名 varchar10 NOTNULL update student_info set 学生姓名姓名 alter table student_info drop column 姓名 1 2分别使用 SQL Server 管理平台和 Transact-SQL

6、语句 DELETE 删除 studentsdb 数据库的 grade 表中学号为 OOO4 的成绩记录。 DELETE grade WHERE 学号 OOO4 13使用 Transact-SQL 语句 UPDATE 修改 studentsdb 数据库的 grade 表中学号 为0003、课程编号为 0005、分数为90的成绩记录。UPDATE grade SET 分数 9O WHERE 学号 OOO3 and 课程 编号 0005 14使用 Transact-SQL 语句 ALTER.ADD 为studentsdb数据库的grade表添加一个名为 备注”的数据 列，其数据类型为 VARCHAR

7、20 。 ALTER TABLE grade ADD 备注 VARCHAR20 NULL 15 分别使用 SQL Server 管理平台和 Transact-SQL 语句 DROP TABLE 删除 studentsdb 数据库中 grade 表。 DROP TABLE studentsdb.dbo.grade 三、实验思考 1使用 Transact-SQL 语 句删除在 studentsdb 数据库的 grade 表添加的 “备注 ”数据 列。 ALTER TABLE grade DROP COLUMN 备注实验 4 数 据查询 二、实验内容及步骤 在 2 studentsdb 数据库中使

8、用 SELECT 语句进行基本查询。 （1）在 student_info 表 中，查询每个学生的学号、姓名、出生日期信息。 SELECT 学号 姓名 出生日期 FROM student_info （2）查询学号为 0002 的学生的姓名和家庭住址。 SELECT 姓名 家庭住址 FROM student_info WHERE 学号 0002 （3）找出所有男同 学的学号和姓名。 SELECT 学号 姓名 FROM student_info WHERE 性别 男 3使用 SELECT 语句进行条件查询 （ 1） 在 grade 表中查找分数在 8090 范围内的学生的学号和分 数。 SELECT

9、 学 号 分 数 FROM grade WHERE 分 数 BETWEEN 80 AND 90（ 2）在 grade 表中查询课程编号为0003 的学生的平均分。 SELECT avg 分数 AS 平均分 FROM grade WHERE 课程编号 0003 （ 3）在 grade 表中查 询学习各门课程的人数。 SELECT 课程编号 COUNT AS 人数 FROM gradeGROUP BY 课程编号 （ 4）将学生按出生 日期由大到小排序。 SELECT FROM student_info ORDER BY 出生日期 DESC （ 5）查询所有姓 “张 ”的学生的学号和 姓名。 SEL

10、ECT FROM student_info WHERE 姓名 LIKE 张 4对 student_info 表，按性别顺序列出学生的学号、姓名、 性别、出生日期及家庭住址，性别相同的按学号由小到大排 序。 SELECT 学号 姓名 性别 出生日期 家庭住址 FROM student_info ORDER BY 性别 学号 5使用 GROUP BY 查 询子句列出各个学生的平均成绩。 SELECT 学号 SUM 分 数 / COUNT AS 平均成绩 FROM grade GROUP BY 学号 6使用 UNION 运算符将 student_info 表中姓 “张 ”的学生 的学号、 姓名与 c

11、urriculum 表的课程编号、 课程名称返回在 一个表中，且列名为 u_编号、u_名称，如图1-8所示。图 1-8联合查询结果集 SELECT学号AS u_编号 姓名AS u_ 名称 FROM student_infoWHERE 姓名 LIKE 张 UNIONSELECT 课程编号 AS u_编号 课程名称 AS u_名称 FROM curriculum 7 嵌套查询 （1）在 student_info 表中查 找与“刘卫平 ”性别相同的所有学生的姓名、出生日期。SELECT 姓名，出生日期 FROM student_info WHERE 性别 SELECT 性别 FROM student

12、_info WHERE 姓名刘卫平 （2）使用 IN 子查询查找所修课程编号为 0002、 0005 的 学生学号、 姓名、 性别。 SELECT 学号，姓名， 性别 FROMstudent_info WHERE student_info. 学号 IN SELECT 学号 FROM grade WHERE 课 程 编 号 IN0002 ，0005 （3 ）列 出学号为 0001 的学生的分数比 0002 号的学生的最低分 数高的课程编号和分数。 SELECT 课程编号，分数 FROM grade WHERE 学号 0001 AND 分数 ANY SELECT 分数 FROM grade WHE

13、RE 学号 0002 列出学号为 0001 的学生 的分数比 （ 4）学号为 0002 的学生的最高成绩还要高的课 程编号和分数。 SELECT 课程编号，分数 FROM grade WHERE 学号 0001 AND 分数 SELECT MAX 分数 FROMgrade WHERE 学号 0002 8连接查询 （ 1）查询分数 在 8090 范围内的学生的学号、姓名、分数。 SELECT student_info.学号，姓名，分数 FROM student_info , grade WHERE student_info. 学 号 grade. 学 号 AND 分 数 BETWEEN 80 A

14、ND 90（2）查询学习“ C语言程序设计”课程的学生的学号、姓名、分数。 SELECT student_info. 学号， 姓名，分数 FROM student_infoINNER JOIN grade ON student_info.学号 grade.学号 INNER JOIN curriculum ON 课 程名称 C 语言程序设计（ 3）查询所有男同学的选课情况，要求列出学号、姓名、课程名称、分数。 SELECT a.学号a.姓名 c.课程名称 b.分数 FROM student_info a INNERJOIN grade b ON a.学号 b.学号 INNER JOIN curr

15、iculum c ON b.课 程编号c.课程编号 WHERE a.性别 男 （4）查询每个学生的所选课程的最高成绩，要求列出学号、姓名、课程编号、分数。SELECT a.学号c.姓名a.课程编号a.分数FROM grade a INNER JOIN student_info c ON a.学号 c.学号 WHERE a. 分数 SELECT MAXb. 分数 FROM grade b WHERE b.学号a学号（5）查询所有学生的总成绩，要求 列出学号、姓名、总成绩，没有选修课程的学生的总成绩为 空。 提示：使用左外连接。SELECT a.学号a.姓名b.总成绩 FROM student_i

16、nfo a LEFT OUTER JOIN SELECT 学号 SUM 分数 AS 总成绩 FROM grade GROUP BY 学号 b ON a学号b.学号 为（6） grade表添加数据行：学号0004、课程编号为 0006、分数为 76。查询所有课程的选修情况， 要求列出课程编号、课程名称、选修人数， curriculum 表中 没有的课程列值为空。 提示： 使用右外连接。 INSERT INTO grade 学号 课程编号 分数 VALUES0004 0006 76 SELECT a.课程编号a.课程名称b.选修人数FROM curriculum a RIGHT OUTER JOI

17、N SELECT 课程编号 COUNT AS 选修人数 FROM grade GROUP BY 课程编号 b ON a.课程编号b.课程编号 三、实验思考 1.查询所有没 有选修课程的学生信息，返回结果包括学号、姓名、性别。SELECT 学号 姓名 性别 FROM student_info WHERE 学 号 NOT IN SELECT 学号 FROM grade 2在 student_info 表和grade表之间实现交叉连接。 SELECT a.学号a姓名 a.性别 b.课程编号 b.分数 FROM student_info a CROSSJOIN grade b 3查询每个学生的所选课程

18、的成绩，并列出学 号生成分组汇总行(总成绩)和明细行(各课成绩)。提示： 使用 SELECT 语句的 COMPUTE 选项。 SELECT 学号 分 数 FROM grade ORDER BY 学号 COMPUTE SUM 分数 BY 学号 4在查询语句中 SELECT 、FROM 和 WHERE 选 项分别实现什么运算？ 投影、选择、自然连接 5在查询 的 FROM 子句中实现表与表之间的连接有哪几种方式？对 应的关键字分别是什么？有三种： 内连接 (INNER JOIN )、外连接( LEFT OUTERJOIN 、 RIGHT OUTER JOIN 、FULL OUTER JOIN )和

19、交叉连接( CROSS JOIN )。实验 5：索引 与视图 二、实验内容及操作步骤1分别使用 SQL Server管理平台和 Transact-SQL 语句为 studentsdb 数据库的 student_info 表和 curriculum 表创建主键索引。 CREATE UNIQUE CLUSTERED INDEX PK_curriculum ON curriculum 课程编号 ON PRIMARY 9 在 studentsdb 数据 库中，以 student_info 表为基础，使用 SQL Server 管理平 台建立名为 v_stu_i 的视图，使视图显示学生姓名、性别、 家庭

20、住址。 10在 studentsdb 数据库中， 使用 Transact-SQL 语句 CREATE VIEW 建立一个名为 v_stu_c 的视图，显示 学生的学号、姓名、所学课程的课程编号，并利用视图查询 学号为 0003 的学生情况。 CREATE VIEW v_stu_c ASSELECT a.学号 a姓名 b.课程编号 FROM student_info a INNER JOIN grade b ON a.学号 b.学号 SELECT FROM v_stu_c WHERE 学号 0003 11基于 student_info 表、 curriculum 表和 grade 表，建立一个名

21、为 v_stu_g 的视图， 视图中具有所有学生的学号、姓名、课程名称、分数。使用 视图 v_stu_g 查询学号为 0001 的学生的所有课程与成绩， 如图 1-9 所示。 图 1-9 学号为 0001 的学生的视图信息 CREATE VIEW v_stu_gASSELECT a.学号 a姓名 c.课程名 称 b.分数 FROM student_info a INNER JOIN grade b ON a.学 号b.学号INNER JOIN curriculum c ON b.课程编号 c课程 编号 SELECT FROM v_stu_g WHERE 学号?0001? 12分别 使用 SQL

22、 Server 管理平台和 Transact-SQL 语句修改视图 v_stu_c，使之显示学号、姓名、每个学生所学课程数目。ALTER VIEW v_stu_c AS SELECT a. 学号 a.姓名 COUNT AS 课程数目 FROM student_info a INNER JOIN grade b ON a学号 b.学号 GROUP BY a.学号a姓名13.使用 Transact-SQL语句 ALTER VIEW 修改视图v_stu，使其具 有列名学号、姓名、性别。 ALTER VIEW v_stu_i 学号，姓 名，性别 AS SELECT 学号，姓名，性别 FROM student_info 14使用系统存储过程sp_rename 将视图 v_stu_i 更名为v_stu_info 。 sp_rename v_stu_i， v_stu_info 21 利用视图 v_stu_i 为 student_info 表添加一行数据： 学号为 0015、姓 名为陈婷、性别为女。 INSERT INTO v_stu_iVALUES,0015?陈婷？女? 22.利用视图 v_stu删除学号为 0015 的学生记录。 DELETE FROM v_stu_i WHERE 学号 0015 23.利用视图 v_stu_g 修改