1、1实验一 数据库及基本表的建立一、实验目的1、掌握 SQL SERVER 的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别 使用 SQL 语句、企业管理器（Enterprise Manager）创建数据库；2、使用 SQL 语句、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC 机，Windows 2000/xp；2数据库管理系统：SQL sever 2000/2005；四、实验方法、步骤及结果测试创建一个教学管理数据库 SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授
2、课信息、班级信息、系部信息、专业信息。创建：student 表( 学生信息表)、course 表（课程信息表) 、teacher 表（教师信息表)、student _course 表（学生选课成绩表)、teacher_course 表（教师上课课表）等。题目 1、创建数据库：实现代码及截图：查询分析器执行情况：SQL 语句及执行结果显示CREATE DATABASE SC ON PRIMARY ( NAME = NSC_DAT, FILENAME = NE:MSSQL.1Database experimentSC.mdf ,SIZE = 30MB , FILEGROWTH = 20%)LOG
3、ON ( NAME = NSC_LOG, FILENAME = NE:MSSQL.1Database experimentSC.log ,SIZE = 3MB , ,FILEGROWTH =1MB )实验结果截图显示2、创建基本表利用查询分析器，使用 SQL 语句方式创建方式将下面各表建立到教学管理数据库中。(1)创建student表2字段名 代码 类型 约束学号 s_sno char(8) 主键姓名 sname char(8) 非空性别 sex char(2) 出生日期 sbirthday Smalldatetime学生所在院系编号 dno char(6) 外键专业代码 spno char(
4、8) 外键班级编码 class_no char(4)(2)创建Course表字段名 代码 类型 约束课程编号 cno char(10) 主键课程名称 cname char(20) 非空专业代码 spno char(8) 外键课程类型编号 ctno tinyint理论学时 lecture tinyint实验学时 experiment tinyint开课学期 semester tinyint课程学分 credit tinyint（3）创建student _course表字段名 代码 类型 约束学号 s_sno char(8) 主键,与student表中s_sno 外键关联,级联删除上课编号 tci
5、d smallint 主键学生成绩 score tinyint(4)创建teacher表 字段名 代码 类型 约束教师编号 t_no char(8) 主键教师姓名 t_name char(8) 非空性别 t_sex char(2)出生日期 t_birthday smalldatetime教师所在院系编号 dno char(6) 外键职称 tech_title char(10)(5)创建系部表(department) 字段名 代码 类型 约束院系编号 dno char(6) 主键院系名称 dept_name char(20) 非空院系负责人 header char(8)(6)创建专业信息表(sp
6、eciality) 字段名 代码 类型 约束专业代码 spno char(8) 主键院系编号 dno char(6) 外键,非空专业名称 spname char(20) 非空(7)创建teacher_course表3字段名 代码 类型 约束上课编号 tcid smallint 主键教师编号 t_no char(8) 外键专业代码 spno char(8) 外键班级编码 class_no char(4)课程编号 cno char(10) 非空,外键学期 semester Char(6)学年 schoolyear Char(10)(8)创建班级表(class) 3、 查看各数据表之间的关系，生成数
7、据库关系图。生成数据库关系图截图显示字段名 代码 类型 约束专业代码 spno char(8) 主键，与 speciality 表中spno 外键关联,班级编码 class_no char(4) 主键,班负责人 header char(8)44、创建各表的实现代码及截图：SQL 语句student表USE SCGOcreate table student(s_sno char(8) primary key,sname char(8) not null,sex char(2),sbirthday smalldatetime,dno char(6),spno char(8),class_no ch
8、ar(4);Course表USE SCGOcreate table Course(cno char(10) primary key,cname char(20) not null,spno char(8),ctno tinyint,lecture tinyint,experiment tinyint,semester tinyint,credit tinyint);查询分析器执行情况：SQL 语句及执行结果截图显示6student_course表USE SCGOcreate table student_course(s_sno char(8)primary key,tcid smallint,
9、score tinyint);teacher表USE SC GOcreate table teacher(t_no char(8) primary key,t_name char(8) not null,t_sex char(2),t_birthday smalldatetime,dno char(6),tech_title char(10); department表USE SC GOcreate table department(dno char(6) primary key,dept_name char(20) not null,header char(8);7speciality表USE
10、 SCGOcreate table speciality(spno char(8) primary key,dno char(6) not null,spname char(20) not null);teacher_course表USE SC GOcreate table teacher_course(tcid smallint primary key,t_no char(8),spno char(8),class_no char(4),cno char(10) not null,semester char(6),schoolyear char(10);class表USE SC GOcrea
11、te table class(spno char(8), class_no char(4),header char(8),primary key(spno,class_no);c5、利用查询分析器修改上述各表。(1)、用 INSERT 语句向各个表 中插入数据录入 5 条记录。录入时注意体会外键约束。实现代码及截图：811查询分析器执行情况：SQL 语句及执行结果显示为department 表添加条记录insert into department(dno,dept_name,header)select 510000,计算机学院, 黄出翔 union allselect 510001,管理学院,
12、 陈鑫 union allselect 510002,机电学院, 陈冠希 union allselect 510003,艺术学院, 黑鬼 union all;-为 teacher表添加条记录insert into teacher(t_no,t_name,t_sex,t_birthday,dno,tech_title)select 4000,陈冠希,男 ,1980-12-7,6348,null union allselect 4001,张柏芝,男 ,1981-5-20,6349,null union allselect 4002,拉姆,男 ,1983-11-11,6350,null union
13、allselect 4003,郑智,男 ,1980-8-20,6351,null union allselect 4004,哈维 ,男,1980-8-25,6352,null;-为 speciality表添加条记录insert into speciality(spno,dno,spname)select 080605,510000,计算机科学与技术 union allselect 2000,510000,网络工程 union allselect 2001,510000,软件工程 union allselect 2002,510001,环境工程 union all;实验结果截图显示（插入数据的表
14、格）12-为 student表添加条记录insert into student(s_sno,sname,sex,sbirthday,dno,spno,class_no)select 10001,陈冠希, 女, 1993-09-08,510000,2000,01 union allselect 10002,谭吉乐, 男, 1966-02-05,510000,2001,2 union allselect 10003,陈明, 男, 1994-04-07,510002,2001,03 union allselect 10004,何为, 男, 1986-02-01,510003,2000,0201 un
15、ion allselect 10005,刘德华 ,男 ,1998-06-24,510000,0201;-为 Course表添加条记录insert into Course(cno,cname,spno,ctno,lecture,experiment,semester,credit)select 1,网络安全,080605,1,2,3,null,null union allselect 6000,电力电子,2000,null,null,null,null,null union allselect 6001,英语,313,null,null,null,null,null union allselec
16、t 6002,数据库原理,2003,null,null,null,null,null union allselect 60001,材料力学,2001,null,null,null,null,null;-为 student_course表添加条记录insert into student_course(s_sno,tcid,score)select 10001,1,99 union allselect 10001,2,89 union allselect 10002,2,66 union allselect 10003,2,53 union allselect 10004,3,78;13-为 cl
17、ass表添加条记录insert into class(spno,class_no,header)select 080605,0201,梁朝伟 union allselect 312,0201,陈冠希 union allselect 313,0202,张柏芝 union allselect 314,03,刘德华;-为 teacher_course表添加条记录insert into teacher_course(tcid,t_no,spno,class_no,cno,semester,schoolyear)select 1,4000,2000,1,6000,null,null union alls
18、elect 2,4001,2001,2,6000,null,null union allselect 3,4003,2002,1,1,null,null; 14共 8 组（共 8 个表）(2)、用 UPDATE 语句更改 student 表中数据；实现代码：update student set sex=男 where sname=陈冠希student 表更改前的内容截图显示查询分析 student 表更改后的内容截图显示(3)、用 DELETE 语句删除 student 表中数据；实现代码：delete from student where s_no=10002;15student 表更改前的
19、内容截图显示查询分析 student 表更改后的内容截图显示五、实验中出现的问题及解决方案要注意设置外键时候与主键的参照关系，外键的值不能是主键中没有的增加删除数据时出现的各种参照完整性约束，注意级联时数据的增删六、思考题1、 说明数据库中的表和数据文件的关系。表为数据库中数据库的基本单位，其数据库按行、列存储。每个表具有一表名和列的集合。每一列有一个列名、数据类型、宽度或精度、比例。一行是对应单个记录的列信息的集合。数据文件存放着在数据库总存储的数据，且一个数据库只有一个数据文件。2、 数据库中的日志文件能否单独修改?可以。把对数据的修改写到数据库中和把表示这个修改的日子记录写到日志文件中是
20、两个不同的操作。有可能在这两个操作之间发生某种故障，级即这两个操作只完成了其中一个。所以，可以单独修改数据库中的日志文件而不一定会修改数据。3、 附加练习题.(代码以及运行结果采用截图显示 )(1) 将计算机专业所有学生的数据库原理的成绩增加 10 分代码以及运行结果：update student_course set score=score+10where tcid in(select tcid from teacher_course where cno in(select cno from Course where cname= 数据库原理 );修改前选修数据库原理的学生为学号 10004
21、16修改后：(2) 删除计算机专业所有学生的数据库原理的选修信息代码以及运行结果：delete from student_coursewhere tcid in(select tcid from teacher_course where cno in(select cno from Course where cname= 数据库原理 );(3) 为 speciality 的 spname 添加唯一约束代码以及运行结果:alter table specialityadd constraint UQ_spname unique(spname);(4) 为 student_course 的 scor
22、e 设置检查约束17代码以及运行结果:alter table student_courseadd constraint CK_score check(score=0 and score1983-0-10截图：20（6）、查询全部学生的学号、姓名、性别和出身日期结果按照出生日期的升序排列。代码:select s_no,sname,sex,sbirthday from student order by sbirthday截图：连接查询：（1）、查询全部学生的学号、姓名、性别、所在院系名称和专业名称代码select s_no,sname,sex,dept_name,spnamefrom studen
23、t,department,speciality21where student.dno=department.dno and student.spno=speciality.spno截图：（2）、 查询选修了课程 1（上课编号）的学生的学号、姓名、专业名称和这门课的成绩代码：select student.s_no,sname,spname,scorefrom student,student_course,specialitywhere student.s_no=student_course.s_no and student.spno=speciality.spno and tcid=1截图：（3
24、）、查询学生不及格的情况列出不及格学生的学号、姓名和不及格的课程名称。代码：select student.s_no,sname,cnamefrom student ,student_course,teacher_course,Coursewhere student.s_no=student_course.s_no and teacher_course.tcid=student_course.tcid and Co=teacher_oand student_course.score80截图 ：实现代码及查询结果截图：2、选用 Northwind 数据库进行查询（1） 、对 NothWind. P
25、roducts 表进行简单查询；在查询分析器在窗口下用 SELECT 语句完成单表查询：查询所有 Products 的详细记录；Select * fromm Products实现代码及查询结果截图：查询单价（UnitPrice）小于 20 的 Products；代码: select * from Products where UnitePrice20截图27实现代码及查询结果截图：查询 Products 中单价（UnitPrice）最高的 Products 的资料；代码：select * from Products where UnitPrice in(select max(UnitPrice
26、) from Products)截图 ;五、实验中出现的问题及解决方案在进行查询操作时要看清楚题目的查询要求，灵活应用各种查询方式，可以尝试用子查询，多表连接查询，嵌套，相关子查询等不同的途径去解决问题，更重要的是具体情况具体分析六、思考题1、 连接查询分哪几类？各有什么特点？分为四类：1：等值与非等值连接查询；特点：只可以连接两个表，通过连接谓词来连接比较。2:自身连接；特点：不仅可以在两个表之间连接，也可以是一个表与其自身进行连接。283：外连接；特点：可以把要舍弃的元组保存在结果关系中，而在其他属性上填空值。4：复合条件连接。特点：WHERE 子句中可以有多个连接条件2、 进行连接查询时
27、应注意哪些问题？1: 使用连接查询时应在列名前加表名作为前缀但是如果不同表之间的列名不同可以不加表名作为前缀如果在不同表之间存在同名列在列名前必须加表名作为前缀否则会因为列的二义性而报错。2：使用连接查询时必须在 where 子句中指定有效的连接条件在不同表的列之间进行连接。如果不指定连接条件或者指定无效的连接条件那么会导致生成笛卡尔积实验题目 实验三 创建和使用视图、索引、存储过程 一、实验目的1、理解视图 、索引、存储过程的定义、索引、存储过程的优点与、索引、存储过程的工作原理；2、掌握在查询分析器和企业管理器中创建、修改及删除视图、索引、存储过程；3、掌握创建 视图、索引、存储过程的 S
28、QL 语句的用法；并能够熟练利用视图向表中插入、删除和修改数据。4、掌握使用视图来查询数据。二、实验主要仪器设备和材料1计算机及操作系统：PC 机，Windows ；2数据库管理系统：SQL Server 2005；三、实验方法、步骤及结果测试（一）、视图1、建立 “计算机学院 ”的学生基本情况视图 ies_student_view，该视图包括计算机学院所有学生的学号、姓名、性别、出身年月、专业名称。2、执行 ies_student_view 视图并观察结果。3、建立课程 1（上课编号）的学生名册的视图，该名册包括学生的学号、姓名、专业名称和这门课的成绩；并查询结果。4、建立统计不及格情况的视
29、图，列出不及格学生的学号、姓名和不及格的课程代码；5、执行视图并观察结果。6、修改视图 ies_student_view，使该视图包括所有学生的学号、姓名、性别、出身年月、学院名称、专业名称。7、执行 ies_student_view 视图并观察结果。8、删除视图 ies_student_view。并查询结果。记录上述实验过程并截图说明。29查询分析器执行情况：创建视图的 SQL 语句及执行结果显示1;CREATE VIEW ies_student_viewASSELECT s_no,sname,sex,sbirthday,spnameFROM student,specialityWHERE
30、student.spno=speciality.spno查询视图 SQL 语句及执行结果截图查询分析器执行情况：创建视图的 SQL 语句及执行结果显示2:SELECT * FROM ies_student_view查询视图 SQL 语句及执行结果截图查询分析器执行情况：修改视图的 SQL 语句及执行结果显示3:create view s_view asselect student.s_no,sname,dept_name,scorefrom student,student_course,departmentwhere student.s_no=student_course.s_no and s
31、tudent.dno=department.dnoand tcid=1查询语句：select * from s_view查询视图 SQL 语句及执行结果截图30查询分析器执行情况：删除视图的 SQL 语句及执行结果显示4:create view stu_failed_viewas select student.s_no,sname,cnofrom student,teacher_course,student_coursewhere student.s_no=student_course.s_noand student_course.tcid=teacher_course.tcid and sc
32、ore605:select * from stu_failed_view查询视图 SQL 语句及执行结果截图查询分析器执行情况：删除视图的 SQL 语句及执行结果显示6:alter view ies_student_viewasSELECT s_no,sname,sex,sbirthday,dept_name,spnameFROM student,speciality,department WHERE student.spno=speciality.spno and student.dno=department.dno查询视图 SQL 语句及执行结果截图7:查询分析器执行情况：删除视图的 SQ
33、L 语句及执行结果显示8:drop view ies_student_view查询视图 SQL 语句及执行结果截图说明视图已被删除（二）、索引（1）、在 student_course 表（学生选课表）的学生学号（sno）列上创建索引sc_sno_index(2)通过 SQL 语句查看运行结果31查询分析器执行情况：创建索引的 SQL 语句及执行结果显示CREATE INDEX sc_sno_indexon student_course(s_no desc)查询视图 SQL 语句及执行结果截图（三）、存储过程题目（1 ）创建存储过程 list_student_department,该存储过程接收
34、学院代码作为输入参数，列出数据库 sc 中某个院系学生的全部信息。查询分析器执行情况：SQL 语句CREATE Procedure list_student_departmentdno char(6)ASSELECT s_no,sname,sex,sbirthday,spno,class_no,student.dnoFROM student,department WHERE department.dno=dno AND student.dno=department.dno题目（2 ）使用 SQL 语句执行存储过程 list_student_department查询分析器执行情况：SQL 语句：
35、EXEC list_student_department dno=510000执行结果截图显示32题目（3 ）编写存储过程将某班选修某门课程的所有学生的学号和上课编号插入到student_course 表中查询分析器执行情况：SQL 语句create procedure add_stu_course(class_no char(4),cno char(10)asinsert into student_course(s_no,tcid)select s_no,teacher_course.tcidfrom student_course,teacher_coursewhere class_no=c
36、lass_no and cno=cno and student_course.tcid=teacher_course.tcid执行结果截图显示题目（4 ）使用 SQL 语句执行（3）创建的存储过程33查询分析器执行情况：SQL 语句exec add_stu_course 3,6001执行结果截图显示题目（5 ）使用 SQL 语句查看（3）创建的存储过程代码查询分析器执行情况：SQL 语句EXEC sp_helptext add_stu_course执行结果截图显示题目（6 ）使用 SQL 语句修改存储过程修改题目（1 ）创建的存储过程 list_student_department,使其返回值只包括 student表的部分列（学号，姓名，出生年月，学院名称和专业名称）查询分析器执行情况：SQL 语句ALTER Procedure list_student_departmentdno char(6)ASSELECT s_no,sname,sbirthday,dept_name,spnameFROM student,department,specialityWHERE department.dno=dno AND student.dno=department.dno
