实训一数据库和表的创建.docx

资源描述

实训一数据库和表的创建.docx

《实训一数据库和表的创建.docx》由会员分享，可在线阅读，更多相关《实训一数据库和表的创建.docx（23页珍藏版）》请在冰豆网上搜索。

实训一数据库和表的创建.docx

实训一数据库和表的创建

实训目的

（1）掌握数据库和表的基础知识。

（2）掌握使用企业管理器和Transact-SQL语句创建数据库和表的方法。

（3）掌握数据库和表的修改、查看、删除等基本操作方法。

实训内容和要求

1．数据库的创建、查看、修改和删除

（1）使用企业管理器创建数据库

创建成绩管理数据库Grademanager，要求见表10-1。

表10-1Grademanager数据库参数表

参数

参数值

参数

参数值

存储的数据文件路径

\db\grademanager_

data.mdf

存储的日志文件路径

\db\grademanager_

log.ldf

数据文件初始大小

5MB

日志文件初始大小

2MB

数据文件最大值

20MB

日志文件最大值

15MB

数据文件增长量

原来10%

日志文件增长量

2MB

（2）查看与修改数据库属性

①在企业管理器中查看创建后的grademanager数据库，查看grademanager_data.mdf、grademanager_log.ldf两个数据库文件所处的文件夹。

②使用企业管理器更改数据库。

更改的参数见表10-2。

表10-2要更改的参数表

参数

参数值

参数

参数值

增加的文件组名

grademanagergroup

增加的数据文件路径

\grademanager1_data.ndf

增加的日志文件路径

\grademanager1_log.ldf

增加的数据文件初始大小

7MB

增加的日志文件初始大小

3MB

增加的数据文件最大值

20MB

增加的日志文件最大值

30MB

增加的数据文件增长量

2MB

增加的日志文件增长量

2MB

（3）使用企业管理器删除该数据库

（4）使用Transact-SQL命令创建上述要求的数据库

（5）使用Transact-SQL命令查看和修改上述要求的数据库

（6）使用Transact-SQL命令删除该数据库

2．表的创建、查看、修改和删除

（1）在Grademanager数据库中创建如表10-3、表10-4和表10-5所示结构的表。

表10-3Student表的表结构

字段名称

数据类型

长度

精度

小数位数

是否允许NULL值

说明

Sno

char

否

主码

Sname

varchar

是

Ssex

char

是

取值：

男或女

Sbirthday

datetime

是

Sdept

char

是

Speciality

varchar

是

表10-4Course表（课程名称表）的表结构

字段名称

数据类型

长度

精度

小数位数

是否允许NULL值

说明

Cno

char

否

主码

Cname

varchar

否

表10-5SC表（成绩表）的表结构

字段名称

数据类型

长度

精度

小数位数

是否允许NULL值

说明

Sno

char

否

外码

Cno

char

否

外码

Degree

decimal

是

1～100之间

（2）向表10-3、表10-4和表10-5输入数据记录，见表10-6、表10-7和表10-8。

表10-6学生关系表Student

Sno

Sname

Ssex

Sbirthday

Sdept

Speciality

李勇

男

1987-01-12

计算机应用

刘晨

女

1988-06-04

电子商务

王敏

女

1989-12-23

数学

张立

男

1988-08-25

电子商务

表10-7课程关系表Course

Cno

Cname

Cno

Cname

C01

数据库

C03

信息系统

C02

数学

C04

操作系统

表10-8成绩表SC

Sno

Cno

Degree

C01

C02

C03

C02

C03

（3）修改表结构。

①向student表中增加“入学时间”列，其数据类型为日期时间型。

②将student表中的sdept字段长度改为20。

③将student表中的Speciality字段删除。

（4）删除student表。

思考题

（1）SQLServer的数据库文件有几种？

扩展名分别是什么？

（2）SQLServer2000中有哪几种整型数据类型？

它们占用的存储空间分别是多少？

取值范围分别是什么？

（3）在定义基本表语句时，NOTNULL参数的作用是什么？

（4）主码可以建立在“值可以为NULL”的列上吗？

实训二单表查询

实训目的

（1）掌握SELECT语句的基本用法。

（2）使用WHERE子句进行有条件的查询。

（3）掌握使用IN和NOTIN，BETWEEN…AND和NOTBETWEEN…AND来缩小查询范围的方法。

（4）掌握聚集函数的使用方法。

（5）利用LIKE子句实现字符串匹配查询。

（6）利用ORDERBY子句对查询结果排序。

（7）利用GROUPBY子句对查询结果分组。

实训内容和要求

1．表结构修改

（1）在实训一所创建的数据库中增加Teacher表，表结构见表10-9。

表10-9Teacher表（教师表）的表结构

字段名称

数据类型

长度

精度

小数位数

是否允许NULL值

说明

Tno

char

否

主码

Tname

varchar

是

Tsex

char

是

取值：

男或女

Tbirthday

datetime

是

Tdept

char

是

（2）在实训一所创建的数据库中增加Teaching表，表结构见表10-10。

表10-10Teaching表（授课表）的表结构

字段名称

数据类型

长度

精度

小数位数

是否允许NULL值

说明

Cno

char

否

外码

Tno

char

否

外码

Cterm

tinyint

是

1～5之间

（3）向表10-9和表10-10中输入数据记录，见表10-11和表10-12。

表10-11教师表Teacher

Tno

Tname

Tsex

Tbirthday

Tdept

101

李新

男

1977-01-12

102

钱军

女

1968-06-04

201

王小花

女

1979-12-23

202

张小青

男

1968-08-25

表10-12授课表Teaching

Cno

Tno

Cterm

C01

101

C02

102

C03

201

C04

202

2．完成下面查询

（1）查询年龄大于18岁的女生的学号和姓名。

（2）查询所有男生的信息。

（3）查询所有任课教师的Tname、Tdept。

（4）查询“电子商务”专业的学生姓名、性别和出生日期。

（5）查询成绩不及格的学生学号及课号，并按成绩降序排列。

（6）查询Student表中的所有系名。

（7）查询“C01”课程的开课学期。

（8）查询成绩在80～90分之间的学生学号及课号。

（9）统计有学生选修的课程的门数。

（10）查询在1970年1月1日之前出生的男教师信息。

（11）计算“C01”课程的平均成绩。

（12）输出有成绩的学生学号。

（13）查询所有姓“刘”的学生信息。

（14）统计输出各系学生的人数。

（15）查询成绩为79分、89分或99分的记录。

（16）查询选修了“C03”课程的学生的学号及其成绩，查询结果按分数降序排列。

（17）查询各个课程号及相应的选课人数。

（18）统计每门课程的选课人数和最高分。

（19）统计每个学生的选课门数和考试总成绩，并按选课门数降序排列。

（20）查询选修了3门以上课程的学生学号。

思考题

（1）聚集函数能否直接使用在SELECT子句、HAVING子句、WHERE子句、GROUPBY子句中？

（2）关键字ALL和DISTINCT有什么不同的含义？

（3）SELECT语句中的通配符有几种？

含义分别是什么？

（4）数据的范围除了可以利用BETWEEN…AND运算符表示外能否用其他方法表示？

怎样表示？

实训三多表连接查询

实训目的

（1）掌握SELECT语句在多表查询中的应用。

（2）掌握多表连接的几种连接方式及应用。

实训内容和要求

（1）查询计算机系（CS）女学生的学生学号、姓名及考试成绩。

（2）查询“李勇”同学所选课程的成绩。

（3）查询“李新”老师所授课程的课程名称。

（4）查询女教师所授课程的课程号及课程名称。

（5）查询至少选修一门课程的女学生姓名。

（6）查询姓“王”的学生所学的课程名称。

（7）查询选修“数据库”课程且成绩在80～90分之间的学生学号及成绩。

（8）查询课程成绩及格的男同学的学生信息及课程号与成绩。

（9）查询选修“C04”课程的学生的平均年龄。

（10）查询学习课程名为“数学”的学生学号和姓名。

（11）查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩。

（12）查询在第3学期所开课程的课程名称及成绩。

思考题

（1）指定一个较短的别名有什么好处？

（2）内连接与外连接有什么区别？

实训四嵌套查询

实训目的

（1）掌握嵌套查询的使用方法。

（2）掌握相关子查询与嵌套子查询的区别。

（3）掌握带IN谓词的子查询的使用方法。

（4）掌握带比较运算符的子查询的使用方法。

（5）掌握带ANY或ALL谓词的子查询的使用方法。

（6）掌握带EXISTS谓词的子查询的使用方法。

实训内容和要求

（1）查询与“李勇”同一个系的同学姓名。

（2）查询学号比“刘晨”同学大，而出生日期比他小的学生姓名。

（3）查询出生日期大于所有女同学出生日期的男同学的姓名及系别。

（4）查询成绩比该课程平均成绩高的学生的学号及成绩。

（5）查询不讲授“C01”课的教师姓名。

（6）查询没有选修“C02”课程的学生学号及姓名。

（7）查询选修了“数据库”课程的学生学号、姓名及系别。

（8）查询选修了全部课程的学生姓名。

（9）查询没有学生选修的课程号及课程名称。

（10）查询所有与李勇选修课程相同的学生信息。

（11）分别用子查询和连接查询，求“C02”号课程不及格的学生信息。

思考题

（1）“=”与IN在什么情况下作用相同？

（2）使用存在量词[NOT]EXISTS的嵌套查询时，何时外层查询的WHERE条件为真，何时为假？

（3）当既能用连接查询又能用嵌套查询时，应该选择哪种查询较好？

为什么？

实训五数据更新

实训目的

（1）掌握利用INSERT命令实现对表数据的插入操作。

（2）掌握利用UPDATE命令实现对表数据的修改操作。

（3）掌握利用DELETE命令实现对表数据的删除操作。

实训内容和要求

利用SELECTINTO…命令备份Student、SC、Course这3个表，备份表名自定。

（1）向Student表中插入记录（""，"张静"，"1981-3-21"，"女"，"CS"，"电子商务"）。

（2）插入学号为“”、姓名为“李四”的学生信息。

（3）把计算机系的学生记录保存到表TS中（TS表已存在，表结构与Student表相同）。

（4）将学号为“”的学生姓名改为“张华”，系别改为“CS”，专业改为“多媒体技术”。

（5）将“李勇”同学的专业改为“计算机信息管理”。

（6）将“”学生选修“C03”号课程的成绩改为该课的平均成绩。

（7）把成绩低于总平均成绩的女同学的成绩提高5%。

（8）把选修了“数据库”课程而成绩不及格的学生的成绩全改为空值（NULL）。

（9）删除学号为“”的学生记录。

（10）删除“计算机系”所有学生的选课记录。

（11）删除SC表中尚无成绩的选课记录。

（12）把“张晨”同学的成绩全部删除。

思考题

（1）如何从备份表中恢复3个表？

（2）DROP命令和DELETE命令的本质区别是什么？

（3）利用INSERT、UPDATE和DELETE命令可以同时对多个表进行操作吗？

实训六索引与视图

实训目的

（1）掌握索引的创建和使用。

（2）掌握视图的创建、修改和删除。

（3）掌握使用视图来访问数据。

实训内容和要求

1．索引的创建与使用

（1）用索引管理器创建SC表的索引IX_XS_KC

①选择要创建索引的数据库文件夹，并在右边的对象窗口中选择并打开其中的【表】对象。

②选择所要创建索引的SC表，并从【操作】菜单中选择【所有任务】子菜单下的【管理索引】命令，打开SQLServer的索引管理器窗口。

③单击其中的【新建】按钮，创建新的索引，并为其设置相应的属性。

为SC表创建一个基于【课程号】列和【成绩】列的索引IX_XS_KC，其中课程号列按升序排列，成绩列按降序排列。

首先，单击【新建】按钮，此时系统打开【新建索引】对话框，在其中的【列名】列表框中选择【课程号】选项。

再选择【成绩】选项，并选中其后的【排列次序（DESC）】选项，使成绩列按降序排列。

接着，选择【填充因子】选项，其值保留系统默认的80，并选中【填充索引】选项，使索引中间页具有与叶级页相同的填充程度。

最后，将索引名设置为IX_XS_KC。

④单击【确定】按钮，完成新索引的创建。

回到索引管理器窗口。

（2）强制使用刚才创建的索引查询数据

①启动SQLServer查询分析器，打开【SQL查询分析器】窗口，并在工具栏的数据库下拉列表框中选择要操作的【Grademanager】数据库。

②强制使用IX_XS_KC索引查询所有课程的及格成绩记录。

在查询命令窗口中输入以下SQL查询命令并执行：

SELECTSNO,CNO,DEGREE

FROMSC

WITH（INDEX（IX_XS_KC））

WHEREDEGREE>=60

观察一下显示出来的数据是否有序。

（3）学生练习（分别利用企业管理器和Transact-SQL命令创建）

①为Student表创建一个名为S_dept的索引，以系别排序。

②为Teacher表创建一个名为T_name的聚集索引，以Tname降序排序。

③查看Student表的索引。

④删除S_dept索引。

2．视图的创建与使用

（1）利用企业管理器创建视图

①启动SQLServer企业管理器，打开【SQLServerEnterpriseManager】窗口。

②选择要创建视图的Grademanager数据库，并在右边的对象窗口中选择其中的【视图】对象。

③选择【操作】菜单中的【新建视图】命令，打开SQLServer的视图设计窗口。

④在【数据源关系图】窗口中右击，打开【添加表】窗口，添加Student表和SC表。

⑤选择Student表的Sno和Sname列，选择SC表的Cno和Degree列，作为视图的显示列。

⑥设置Sno列的排序类型为升序。

⑦设置查询条件：

先在Cno行的【准则】列设置条件为“='C01'”，然后在Degree行的【准则】列设置条件为“<60”。

⑧单击快捷工具栏上的快捷按钮，在弹出的【另存为】对话框中输入视图名，如“v_C01不及格”，然后单击【确定】按钮，关闭视图设计窗口，完成视图的创建。

⑨在【v_C01不及格】视图上右击，在弹出的快捷菜单中选择【设计视图】命令，修改视图定义。

⑩添加数据源Course表，以显示C03号课程的课程名称。

打开【添加表】窗口，选择【Course】表，系统自动为Course表和SC表建立基于课程号的内连接。

选择Course表中的Cname（课程名称）列。

添加查询条件：

修改课程号行中【准则】列的条件为“='C03'”；修改成绩行中【准则】列的条件为“>=70”，并复制该行，取消【输出】列中的复选，并修改【准则】列的条件为“<=90”。

单击快捷工具栏上的快捷按钮，关闭视图设计窗口，保存对视图的修改。

（2）学生练习（分别利用企业管理器和Transact-SQL命令创建）

①创建一个简单视图，查询“计算机系”学生的信息。

②创建一个简单视图，统计每门课程的选课人数和最高分。

③创建一个复杂视图，查询与“张立”同学同一系别的学生信息。

④创建一个复杂视图，查询选修了课程的同学的姓名、课程名及成绩。

思考题

（1）使用索引为什么能提高查询速度？

（2）索引经常被创建在哪些字段上？

（3）聚集索引与其他索引有什么不同？

实训七存储过程与触发器

实训目的

（1）理解存储过程和触发器的功能及特点。

（2）学会使用Transact-SQL编写存储过程和触发器的方法。

（3）学会如何使用企业管理器创建存储过程和触发器。

（4）掌握存储过程的创建、执行与删除操作。

（5）掌握触发器的创建、修改和删除操作。

（6）理解可以使用存储过程和触发器来维护数据的完整性。

实训内容和要求

1．存储过程的创建、执行与删除

（1）创建带输入参数的存储过程

①利用企业管理器创建一个带输入参数的存储过程proc_Stud1，其中的输入参数用于接收课程号，默认值为“C01”，然后在SC表中查询该课成绩不及格的学生学号，接着在Student表中查找这些学生的基本信息，包括学号、姓名、性别、系别和专业信息，最后输出。

②单击存储过程属性对话框中的【语法检查】按钮，对输入的CREATEPROCEDURE语句进行语法分析。

如果有语法错误，则进行修改，直到没有语法错误为止。

③在查询分析器中使用EXECUTE命令执行上述存储过程。

（2）创建带嵌套调用的存储过程

①在查询分析器窗口中输入创建存储过程的CREATEPROCEDURE语句。

创建一个带嵌套调用的存储过程proc_Stud2。

该存储过程也有一个输入参数，它用于接收授课教师的姓名，默认值为“李新”，然后嵌套调用存储过程proc_Cno，输出其所授课程的课程号，接着用此课程号来完成上一部分实训中所创建的存储过程proc_Stud1的功能。

相应的CREATEPROCEDURE语句如下：

（@授课课老师char（8）=’李新’）

DECLARE@课程号char（3）

--嵌套调用存储过程proc_Cno

EXECUTEproc_Cno

@授课老师，@课程号OUTPUT

--查询指定课程成绩不及格的学生的基本信息

SELECTa.Sno，a.Sname，a.Ssex，a.Sdept

FROMStudenta，SCb

WHEREb.Cno=@课程号

ANDb.Degree<60

ANDa.Sno=b.Sno

proc_Cno的存储过程如下：

CREATEPROCEDUREproc_Cno

@教师char（10）='李新'

@课程号char（3）OUTPUT

SELECT@课程号=CnoFROMTeachinga,Teacherb

WHEREa.Tno=b.Tnoandb.Tname=@教师

②单击工具栏上的快捷键，对输入的CREATEPROCEDURE语句进行语法分析。

如果有语法错误，则进行修改，直到没有语法错误为止。

③单击工具栏上的快捷按钮，执行CREATEPROCEDURE语句。

（3）执行所创建的两个存储过程

①在查询分析窗口中输入以下EXECUTE语句，执行存储过程proc_Stud1。

EXECUTEproc_Stud1'C03'

②单击工具栏上的快捷键，执行存储过程。

③在查询分析器窗口中输入以下EXECUTE语句，执行存储过程proc_Stud2。

EXECUTEproc_Stud2DEFAULT

④单击工具栏上的快捷键，执行存储过程。

（4）删除新建的存储过程。

①在查询分析器窗口中输入DROPPROCEDURE语句和所有新创建的存储过程名。

DROPPROCEDURE

Proc_Stud1，proc_Stud2

②单击工具栏上的快捷键，删除存储过程。

2．触发器的创建、执行和删除

（1）创建触发器。

①在企业管理器中为SC表创建一个触发器tri_UPDATE_DELETE_SC，该触发器是基于UPDATE操作和DELETE操作的复合型触发器，当修改了该表中的成绩信息或者删除了成绩记录时，触发器激活生效，显示相关的操作信息。

--创建触发器

CREATETRIGGERtri_UPDATE_DELETE_SC

ONSC

FORUPDATE，DELETE

--检测成绩列表是否被更新

IFUPDATE（Degree）

BEGIN

--显示学号、课程号、原成绩和新成绩信息

SELECTINSERTED.Cno，DELETED.DegreeAS原成绩，

INSERTED.DegreeAS原成绩

FROMDELETED,INSERTED

WHEREDELETED.Sno=INSERTED.Sno

END

--检查是更新还是删除操作

ELSEIFCOLUMNS_UPDATED（）=0

BEGIN

--显示被删除的学号、课程号和成绩信号

SELECT‘被删除的学号’=DELETED.Sno，DELETED.Cno

DELETED.DegreeAS原成绩

FROMDELETED

END

ELSE

--返回提示信息

PRINT'更新了非成绩列！

②单击【确定】按钮，完成触发器的创建。

（2）激活触发器。

①在查询分析器窗口中输入以下UPDATESC语句，修改成绩列，激发触发器。

UPDATESCSETDegree=Degree+5

WHERECno='C01'

②在查询分析器窗口中输入以下UPDATESC语句修改非成绩列，激发触发器。

UPDATESCSETCno='C13'

WHERECno='C03'

③在查询命令窗口中输入以下DELETESC语句，删除成绩记录，激发

展开阅读全文