数据库实验内容.docx
《数据库实验内容.docx》由会员分享，可在线阅读，更多相关《数据库实验内容.docx（50页珍藏版）》请在冰豆网上搜索。
数据库实验内容
实验一数据库的创建和使用
一、实验目的和要求
1．掌握SQLserver2000的企业管理器的启动与使用；
2．掌握企业管理器创建数据库和建立表的方法；
3．掌握查询分析器的基本操作；
4．熟悉表格结构修改的方法。
二、实验内容和步骤
1．创建数据库
用向导创建数据库
使用企业管理器（EnterpriseManager）创建数据库
使用Transact-SQL语言创建数据库。
下面介绍利用企业管理器创建名为：
student.dbo的数据库；
（１）在企业管理器中，单击工具栏中的图标，或在数据库文件夹或其下属任一数据库图标上单击右键，选择新建数据库选项，就会出现“数据库属性”对话框。
（２）在常规（General）页框中，要求用户输入数据库名称以及排序规则名称。
（３）点击数据文件（DataFiles）页框，该页框用来输入数据库文件的逻辑名称、存储位置、初始容量大小和所属文件组名称。
（４）点击事务日志（TransactionLog）页框，该页框用来设置事务日志文件信息。
（５）单击“确定”按钮，则开始创建新的数据库。
2．建立数据表
在SQLServer2000中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。
SQLServer2000提供了两种方法创建数据库表，第一种方法是利用企业管理器（EnterpriseManager）创建表；另一种方法是利用Transact-SQL语句中的create命令创建表。
在EnterpriseManager中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择新建表选项，或者在工具栏中选择图标，就会出现新建表对话框，在该对话框中，可以定义列的以下属性：
列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。
然后根据提示进行设置。
在数据库中建立学生情况表、学生成绩表和课程表，
其结构如表1.1和表1.2、表1.3。
表1.1学生情况表
字段名
类型
是否可空
学号
char
否
姓名
char
是
出生年月
Date/Time
是
性别
char
是
表1.2学生成绩表
字段名
类型
是否可空
学号
Char
否
课程号
Char
是
成绩
float
是
表1.3课程表
字段名
类型
是否可空
课程名
Char
是
课程号
char
否
任课教师
char
是
3．添加索引
在学生情况表中以学号字段为主键，建立主索引和唯一索引；
在学生成绩表中以（学号+课程号）为主键，建立多重索引。
在课程表中以课程号为主键，建立主索引。
4．输入数据
向"学生情况表"输入数据。
表1.4学生情况表
学号
姓名
专业
出生年月
性别
20030001
欧阳立安
物理
82-1-21
男
20030002
庄严
物理
82-9-21
女
20030003
李华
物理
83-5-1
男
20030101
丁一
数学
82-4-4
男
20030102
马良
数学
81-12-2
女
20030103
朱墨
数学
82-10-1
男
向"学生成绩表"输入数据。
表1.5学生成绩表
学号
课程号
成绩
20030001
C001
90
20030001
L001
82
20030001
M002
85
20030001
E001
92
20030001
C002
65
……
……
……
20030002
C001
78
20030002
L001
86
20030002
M002
85
20030002
E001
70
20030002
C002
50
……
……
……
表1.6课程表
课程号
课程名
任课教师
C001
计算机文化基础
王强
L001
力学
张三
M002
数学
李四
E001
外语
王五
C002
数据结构
许志文
……
……
……
5．数据表操作
在企业管理器中打开student.dbo数据库对各表进行添加、删除、修改、查询等的各种操作及练习各种约束的建立。
实验二SELECT基本查询语句的使用
一、实验目的
1．学会使用查询分析器；
2．掌握SELECT的使用。
3．学会使用聚合函数。
二、实验内容及步骤
1、利用select进行简单的查询。
例1，查询指定的列，列出所有销售书籍的书号、购书数量、付帐方式信息
SELECTtitle_id,qty,paytermsFROMsales
例2．查询全部的列，列出所有购书信息:
SELECT*FROMsales
例3：
查询所有书籍的书号、购书数量、付账方式信息,并在每个书号前面显示字符串“付账方式:
”表明显示信息是付账方式信息。
SELECTtitle_id,qty,'付账方式:
',paytermsFROMsales
例4：
查询所有出版商的ID号和名称,并要求用ID和名称来进行区别（作列标题）。
SELECT'ID'=pub_id,'名称'=pub_nameFROMpublishers
或SELECTpub_id‘ID’,pub_name‘名称’FROMpublishers
例5：
查询所有的advance不大于$5500的business类型的书
SELECTtitle_id,type,advanceFROMtitlesWHERENOTadvance>$5500ANDtype='business‘
例6：
按照类型的升序和价格的降序（在类型相同时）显示书的信息
SELECTpub_id,type,price,title_idFROMtitlesORDERBYtype,pricedesc
例7：
求出各种商业图书的平均价格，价格总和
SELECTAVG（price）'AVG',SUM（price）'SUM',COUNT（PRICE）'COUNT'FROMtitlesWHEREtype='business'
例8：
利用COUNT（*）查询求出的是所有商业书籍的价格平均值和总和,要求出每类书的平均值和总和,就要利用GROUPBY对type进行分组,来求出每组的平均值和总和
SELECTtype,SUM（price）"SUM",COUNT（price）"COUNT",AVG（price）"AVG"FROMtitlesgroupbytype
三、练习
1、用SQL语句查询年龄>20的学生姓名、学号、年龄。
（select*from学生情况表wheredatediff（yy,出生年月,getdate（））>20）
2、用SQL语句查询成绩大于90分的学生的学号
3、用SQL语句查询出学生的学号、总成绩、平均成绩。
实验三连接与多表查询
一、实验目的
1．掌握各种不同的连接表的方法。
2．掌握通过表的连接完成从数据库的多张表中获得数据的方法。
二、实验内容和要求
1．连接
连接可以实现从两个或多个表中查询数据。
由于连接涉及多个表及其之间的引用，所以列的引用都必须明确，对于重复的列名必须用表名限定。
连接可以在FROM子句或WHRER子句中定义，但由于在FROM子句中指定连接条件有助于将连接条件与WHERE子句中指定的搜索条件区分开，建议使用这种方法。
连接条件可以通过指定每个表中要用于连接的列（如建立外键）或者指定比较各列值时使用的运算符（<,>,=等）定义。
在WHERE子句中定义连接的语法形式为：
WHEREtable.columnoperatortable.column
在FROM子句中定义连接的语法形式为：
FROMjoin_table1join_typejoin_table2[ON（join_condition）]
join_type指定所执行的连接类型：
内连接、外连接或交叉连接。
内连接使用比较运算符进行表间列值的匹配，并返回连接列匹配的行，分为等值连接、不等连接和自然连接；外连接不仅仅返回连接匹配的行，而且列出左表、右表和两个表，因此又分为左外连接、右外连接、全外连接；交叉连接返回左表中每一行与右表中每一行的组合，即两个表中所有行的笛卡儿积。
示例3-1：
在FROM子句中定义连接,返回一组产品（表Products中的记录）和供应商（表Suppliers中的记录）信息，在WHERE子句中限定单价高于10，且公司名以F开头。
USENorthwind
GO
SELECTProductID,Suppliers.SupplierID,CompanyName
FROMSuppliersJOINProducts
ON（Suppliers.SupplierID=Products.SupplierID）
WHEREUnitPrice>$10ANDCompanyNamelike‘F%’
示例3-2：
在WHERE子句中定义连接
SELECTP.ProductID,S.SupplierID,S.CompanyName
FROMSuppliersASS,ProductsASP
WHERES.SupplierID=P.SupplierID
ANDP.UnitPrice>$10
ANDS.CompanyNamelike‘F%’
该示例与上例相同。
它们的结果图3－1所示。
2．内连接（INNERJOIN）
内连接是用比较运算符比较表中的列值，返回符合连接条件的数据行，从而将两个表连接成一个新表。
内连接分为三种：
●等值连接：
在连接条件中使用等于运算符（＝）比较连接的列的列值，返回所有行（包括重复列）。
●不等值连接：
在连接条件中使用除等于运算符以外的其它运算符（>,<,<=,>=,!
>,!
<,<>）比较连接的列的列值
●自然连接：
在连接条件中使用等于运算符（＝）比较连接的列的列值，返回指定列，同时删除重复列。
示例3-3：
等值连接
USEpubs
GO
SELECT*
FROMauthorsINNERJOINpublishers
ONauthors.city=publishers.city
ORDERBYauthors.au_lname
该示例返回两个表中连接列的列值相等的所有列，结果如图3－2所示，其中返回列中包括两个city列，两个state列，均为重复列。
示例3-4：
不等值连接
USEpubs
GO
SELECTpublishers.pub_name,publishers.state,
authors.au_lname,authors.au_fname,authors.state
FROMpublishersINNERJOINauthors
ONauthors.state>publishers.state
WHEREpublishers.pub_nameLIKE‘a%’
ORDERBYauthors.au_lname
该示例为一个大于（>）连接，用于查找洲名按字母顺序排列在CA之后下面的以A开头的出版社。
结果如图3－3所示。
下面的示例为emp_pay表的employeeID列创建索引，并且强制唯一性。
因为指定了CLUSTERED子句，所以该索引将对磁盘上的数据进行物理排序。
SETNOCOUNTON
USEpubs
IFEXISTS（SELECT*FROMINFORMATION_SCHEMA.TABLES
WHERETABLE_NAME='emp_pay'）
示例3-5：
自然连接
USEpubs
GO
SELECTpublishers.pub_id,publishers.pub_name,authors.*
FROMauthorsINNERJOINpublishers
ONauthors.city=publishers.city
ORDERBYauthors.au_lname
该示例指定了需要返回的列，删除了重复列，为一个自然连接，结果如图3－4所示。
注意和等值连接的区别。
3．外连接（OUTERJOIN）
内连接只返回符合查询条件或连接条件的行作为结果集，即删除了所有不不符合限定条件的行，而外连接则返回的结果集除了包括符合连接条件的行，还会返回FROM子句中的至少一个表或视图的所有（当然这些行要满足搜索条件）。
外连接分为3种：
●左外连接（LEFTOUTERJOIN）：
通过左向外连接引用左表所有的行。
●右外连接（RIGHTOUTERJOIN）：
通过右向外连接引用右表所有的行。
●全外连接（FULLOUTERJOIN）：
返回两个表的所有的行。
注：
外连接可以省略关键词OUTER
示例3-6：
左外连接
USEpubs
GO
SELECTauthors.au_fname,authors.au_lname,publishers.pub_name
FROMauthorsLEFTOUTERJOINpublishers
ONauthors.city=publishers.city
ORDERBYauthors.au_fname,authors.au_lname,publishers.pub_name
该示例中，在authors和publishers中的city列上建立连接。
由于是左外连接，对于表authors中的所有列，不论是否满足连接条件，即与表publishers中的city列匹配，都将返回到结果集中，如图3－5所示。
由于结果集中的pub_name列基本上没有相匹配的数据，所以该列返回大量NULL。
示例3-7：
右外连接
USEpubs
GO
SELECTauthors.au_fname,authors.au_lname,publishers.pub_name
FROMauthorsRIGHTTOUTERJOINpublishers
ONauthors.city=publishers.city
ORDERBYauthors.au_fname,authors.au_lname,publishers.pub_name
该示例中，仍然在authors和publishers中的city列上建立连接。
由于是右外连接，对于表publishers中的所有列，不论是否满足连接条件，即与表authors中的city列匹配，都将返回到结果集中，如图3－6所示。
示例3-8：
全外连接
USEpubs
GO
SELECTauthors.au_fname,authors.au_lname,publishers.pub_name
FROMauthorsFULLOUTERJOINpublishers
ONauthors.city=publishers.city
ORDERBYauthors.au_fname,authors.au_lname,publishers.pub_name
该示例中，使用了全外连接，不论一个表的行是否匹配另一个表的数据，均将返回到结果集中，如图3－7所示。
图3-7全外连接
4．交叉连接（CROSSJOIN）
交叉连接不使用WHERE子句，它返回连接的两个表的笛卡儿积，结果集中包含两个表中的所有行的组合。
示例3-9：
交叉连接
USEpubs
GO
SELECTau_fname,au_lname,pub_name
FROMauthorsCROSSJOINpublishers
ORDERBYau_fname
该示例中，是交叉连接，返回到结果集中包含184行，如图3－8所示。
如在示例中加上语句WHEREauthors.city=publishers.city，则返回结果与内连接一样。
5．自身连接
自连接就是使用一个表中的列进行比较连接，从而将该表中的行同另一些行连起来。
示例3-10：
自连接
USEpubs
GO
SELECTfirst.au_fname,first.au_lname,second.au_fname,second.au_lname
FROMauthorsfirstINNERJOINauthorssecond
ONfirst.city=second.city
WHEREfirst.city=’Oakland’
ORDERBYfirst.au_fname,first.au_lname
该示例使用了自连接，查询了city列值为Oakland的具有相同邮政编码的作者的姓名，结果如图3－9所示。
★注意：
为了连接同一个表（自连接），一定要将该表指定两个别名，从而从逻辑上区分开来。
在上面的示例中，包含了重复数据。
如果要删除结果中作者自身匹配的行以及完全相同的行，语句应怎样修改？
留给学生自己考虑。
6．多表连接
前面的示例都是两个表的连接，多表连接就是将两个以上的表进行比较连接。
要连接多个表只要在FROM子句中包含多个连接条件，多个连接条件可以用逻辑运算符AND或OR连接。
示例3-11：
多表连接
USEpubs
GO
SELECTauthors.au_fname,authors.au_lname,titles.title
FROMauthorsJOINtitleauthor
ONauthors.au_id=titleauthor.au_idJOINtitles
ONtitleauthor.title_id=titles.title_id
ORDERBYau_fname
该示例中建立了两个连接，从而连接了三个表：
表authors、表titleauthors和表titles。
在结果中没有表titleauthors的任何列，但是如果没有titleauthors这样的中间表，此连接无法实现。
结果如图3－10所示。
练习：
上面的所有示例都是在FROM子句中定义连接连接的，在WHERE子句中定义连接的方法，同学可以自行完成。
实验四子查询
一、实验目的
1．掌握使用普通子查询方法获得表中数据。
2．掌握使用相关子查询方法获得表中数据。
二、实验内容和要求
1．子查询
子查询是嵌套在SELECT、INSERT、UPDATE、DELETE语句中或另外一个子查询中的SELECT语句，总是用圆括号括起来。
子查询包括：
a）由[NOT]IN引出的子查询。
b）由比较运算符引出的子查询。
c）由[NOT]EXISTS引出的子查询。
d）相关子查询。
示例4－1：
子查询作为一个表达式
USEpubs
GO
SELECTtitle,price,price-（SELECTavg（price）FROMtitles）asdifference
FROMtitles
WHEREtype=’popular_comp’
该示例中，子查询作为一个表达式中的一项返回单值，再被price减，作为difference列的数据。
结果如图4－1所示。
示例4-2：
使用子查询
SELECTProductName
FROMNorthwind.dbo.Products
WHEREUnitPrice=
（SELECTUnitPrice
FROMNorthwind.dbo.Products
WHEREProductName=’SirRodney’’sScones’）
该示例中，返回的结果集需要满足限定条件表Products中的UnitPrice列值等于子查询返回的单值。
结果图4－2所示。
图4－2子查询结果
思考：
可不可用连接完成同样的查询？
请同学自行完成。
⏹在使用子查询时，需要注意以下的限制条件：
⏹子查询不能包括COMPUTE或FORBROWSE子句。
⏹仅包含在子查询中的表中的列不能返回在结果集中。
⏹只有指定了TOP，才能指定ORDERBY子句。
⏹比较运算符后面的子查询的选择列表只能包含一个表达式或列名称（使用EXISTS和IN除外）。
⏹如果外部查询的WHERE子句包含某个列名，则该子句必须与子查询中的该列在连接上兼容。
⏹ntext、text、image数据类型不能用于子查询的选择列表中。
⏹由于必须返回单个值，GROUPBY和HAVING子句不能用于未经ANY或ALL关键字修饰的比较运算符引入的字查询中。
⏹DISTINCT关键字不能用于GROUPBY的子查询中。
⏹不能指定COMPUTE和INTO子句。
⏹由子查询创建的视图不能更新。
2．[NOT]IN子查询
由IN（或NOTIN）引出的子查询的结果是一列零值或更多值。
示例4-3：
IN子查询
USEpubs
GO
SELECTpub_name
FROMpublishers
WHEREpublishers.pub_idIN
（SELECTtitles.pub_id
FROMtitles
WHEREtype=’psychology’）
该示例中，首先子查询返回出版过心理学书籍的出版社的id（返回0877、0736、0736、0736、和0736），然后将这些者带回外部查询中，返回相对应的出版商名称：
USEpubs
GO
SELECTpub_name
FROMpublishers
WHEREpublishers.pub_idIN
（‘0877’、‘0736’、‘0736’、‘0736’，‘0736’）
结果如图4－3所示。
NOTIN查询与IN查询使用方法一样，请同学自己练习。
思考：
本例的查询可以用连接查询表示吗？
3．比较子查询
由比较运算符（＝、<>、>、>=、<、!
<、!
>、<＝）也可引出一个子查询。
在使用ANY或ALL修饰的比较运算符引出子查询时，必须返回单个值而不是值列表，所以在使用这种子查询时必须对返回结果有所预见，且经常使用集合函数。
示例4-4：
比较子查询
USEpubs
GO
SELECTau_lname,au_fname
FROMauthors
WHEREcity=
（SELECTcity
FROMpublishers
WHEREpub_name=’AlgodataInfosystems’）
该示例使用等于（＝）连接，返回居住在出版商AlgodataInfosystems所在城市中的作者姓名。
结果如图4－4所示。
思考：
如果出版商Algod
升级会员 