SQL实验指导书（含部分答案）

SQL Server 2005实验指导书

实验三 用T-SQL编辑修改数据库数据

一、实验目的

熟练掌握在查询分析器中执行INSERT，UPDATE，DELETE语句来实现数据的添加、修改、删除等操作。

二、操作示例

1．使用INSERT语句向表中添加数据。

1）向mydatabase数据库的kcxxb表添加下列新记录（插入所有列）： use mydatabase go

insert kcxxb values (‘046110’，‘多媒体应用技术’，70，4) select * from kcxxb

--显示结果

2）向xsxxb表添加记录（插入部分列）：

use mydatabase go

insert xsxxb (xh,xm,csrq,address)

values('04651020','黄小烽','1983-7-6','40-203')

select * from xsxxb --查询显示结果

3）创建一个与xsxxb表结构一样的空表aa，并把xsxxb表中性别（xb）列为“女”的记录添加到aa表中。并查看aa表中的信息。

use mydatabase go

select * into aa from xsxxb where 1=2 --1=2是永远为假的条件。 insert aa select * from xsxxb where xb= '女' select * from aa

--查询显示结果

1

SQL Server 2005实验指导书

2．UPDATE语句修改数据

1)将xscjb表中的成绩（grade）减少3分。 USE mydatabase go

UPDATE xscjb SET grade=grade-3

2)将kcxxb表中课程名(kcm)为“哲学”的课时(ks)改为54。

Use mydatabase Go

Update kcxxb set ks=72 where kcm= '哲学'

3．DELETE语句删除记录

1）删除aa表中出生日期（csrq）在1985年以后的记录。 Use mydatabase Go

Select * from aa --查询删除前的结果 DELETE aa where csrq> '1985-12-31' Select * from aa --查询删除后的结果 2) 删除aa表中的全部记录。

Use mydatabase Go Delete aa

4．删除表aa

use mydatabase go

drop table aa

2

SQL Server 2005实验指导书

三、实验内容

1．按操作示例练习数据的编辑修改。 2．用select命令建立一个与kcxxb表相同结构的表qq，然后把记录(’ 123456’,’英语’,180，10)添加到qq表中。

Select * into qq from kcxxb where 1=2

Insert into qq values(’ 123456’,’英语’,180，10)

3．把记录数据(04651003,'李从良','男','1982-10-1','20-102',5555666)添加到表xsxxb中。

Insert into xsxxb values(04651003,'李从良','男','1982-10-1','20-102',5555666) 4．把记录(04651004,'张中东', '25-102',)添加到表xsxxb的xh(学号)、xm(姓名)和address(地址)中。

Insert into xsxxb(xh,xm,address) values(04651004,'张中东', '25-102') 5．把kcxxb表中课时（ks）大于70的添加到表qq中。

Insert qq select * from kcxxb where ks >70

6．把qq表中所有课时（ks）等于70的数据改为72。

Update qq set ks=72 where ks=70

7．把xsxxb表中电话(phone)为空（null）的记录的电话改为‘0000000’。 update xsxxb set phone=’0000000’ where phone is null 8．用公式“课时=学分*18）计算qq表中的课时。 Udate qq set ks=xuefen*18

8．把qq表中课程名(kcm)为‘数学’的记录删除。 delete qq where kcm=’数学’

9．删除qq表中的全部记录。 delete qq

10．用drop table命令删除qq表。 drop table qq

四、思考题

1．不指定字段插入记录数据时，能否颠倒数据的顺序？会有什么影响？ 2．插入数据到指定字段时可以颠倒数据的顺序吗？试试看。

3．如果要修改表的结构（如添加/删除列，修改列的名称/大小/数据类型等）时，应使用什么命令？请参考有关资料给xscjb表添加一列，修改其大小后再删除。

3

SQL Server 2005实验指导书

4．删除记录和删除表有何区别？

5．各条命令中各部分内容分别表示什么意思？能颠倒它们的顺序吗？自己试试看。

4

SQL Server 2005实验指导书

实验四 简单数据查询

一、实验目的：

通过本实验理解查询的概念和方法，掌握SELECT语句在单表查询中的应用。

二、实验示例

1．还原bookstore数据库

本次实验开始，我们要使用bookstore数据库进行数据查询，因此按实验二还原数据库的方法还原指定的bookstore数据库。Bookstore数据库是某书店仓库管理使用的数据库，其简单的E-R模型如图4-1所示。

图书供应商 进货 员工 销售 图书 客户 图4-1 bookstore数据库的E-R模型 书店仓库管理员收集供应商的信息进行修改,根据供应商提供的图书信息进行订货进货,每次进货都要记录进货信息，同时修改图书的库存信息。

在销售员销售图书时，减少图书库存量并记录每次的销售信息。 系统管理员负责员工等各种信息的更新处理。各表的结构如下所示：

表4-1 图书供应商(provider)表结构 字段名 字段类型 长度 允许空 描述 providerId int 4 N 供应商编号 主键,递增1的标识 providerName char 40 N 供应商名称 Address varchar 50 N 地址 Phone char 15 Y 电话 Postcode char 6 Y 邮编 Email varchar 20 Y 电子邮箱 Webaddress Varchar 20 Y 网址

5

SQL Server 2005实验指导书

表4-2 图书库存(book)表结构

字段名 bookId BookName ISBN Author Abstract number Price Publisher Pubdate 字段类型 int char (50) Char(20) Char(20) Char(500) int float char (25) smalldatetime

字段名 emId name sex birthday hireday address telephone fireday 表4-3 员工表（employeer）结构 字段类型 长度 允许空 描述 Int char Char smalldatetime smalldatetime Char Char smalldatetime 4 10 2 4 4 50 15 4 N N N Y N Y Y Y

表4-4 进货记录表（stock）结构 字段名 字段类型 长度 允许空 描述 stockID int 4 N 进货号 Bookid int 4 N 书号 providerid int 4 N 供应商号 stockerid int 4 N 进货员工号 number int 4 N 进货册数 price float 8 N 进价 stockdate datetime 8 N 进货日期

主键,递增1的标识 员工号 员工名 性别 出生日期 雇用日期 住址 电话号码 离职日期 主键,递增1的标识 默认为男 长度 4 50 20 20 500 4 8 25 4 允许空 N N N N Y N N N N 描述 图书编号 书名 书刊号 作者 摘要 库存数量 价格 出版社 出版日期 主键,递增1的标识 6

SQL Server 2005实验指导书

表4-5 销售记录表（sales）结构 字段名 字段类型 长度 允许空 描述 saleid int 4 N 销售号 Bookid int 4 N 书号 client nchar 8 Y 客户名 salerid int 4 N 销售员工号 number int 4 N 销售册数 price float 8 N 销售价 saledate datetime 8 N 销售日期

主键,递增1的标识 2．简单数据查询

1）查询所有记录的所有信息

单击开始->程序->Microsoft SQL Server->查询分析器，打开查询分析器，查看bookstore数据库book表中所有信息。

use bookstore go

select * from book

2）查询所有记录的部分列信息。查询book表中所有记录的书名(bookname)、作者(author)和出版社(publisher)信息。

use bookstore go

select bookname,author,publisher from book

3)查询时改变列标题的显示。查询检索book表所有记录的书名(bookname)，作者(author)和出版社(publisher)信息并分别加上书名、出版号、作者的标题信息。

use bookstore go

select bookname as '书名', author '作者' ,出版社=publisher from book

7

SQL Server 2005实验指导书

4)使用TOP关键字查询前面部分记录。 ①从book表中检索出前面20%的记录。

use bookstore go

select top 20 percent * from book

②从book表中查询出前面5条记录的bookname（书名）、author(作者)、publisher(出版社)和pubdate(出版日期)的信息，并分别使用书名、作者、出版社和出版日期作为结果显示标题。

use bookstore go

select top 5书名=bookname, author as 作者, publisher 出版社,

pubdate as 出版日期 from book

5)使用INTO子句创建新表。创建一个与book表结构完全相同的表newtab.

use bookstore go

select * into newtab from book select * from newtab

6) 使用distinct关键字消除重复的结果。如查询book表中的书出自哪些出版社（publisher）：

use bookstore go

select distinct publisher from book

三、实验内容

1．完成示例中的所有示例操作。

2.分别查询bookstore数据库中5张表的所有数据信息。

8

SQL Server 2005实验指导书

3．从图书供应商表（provider）中检索出所有的供应商的名称（providername）、地址（address）和电话(phone)。

select providername,address,phone from provider

4．查询员工表（employeer表）中所有员工的姓名（name）、雇用日期(hireday)和电话(telephone)，并将标题分别显示为“姓名”、“雇用日期”和“电话”。 select name 姓名,hireday 雇用日期,telephone 电话 from employeer

5．查询销售记录表（sales表）中的销售号（saleid）、销售册数(number)和销售价格(price)，同时计算并显示它的销售总价。

select saleid,number,price,zj=number*price from sales

compute sum(number*price)

6．从销售记录表（sales表）中检索出前面20条记录的所有信息。 select top 20 * from sales

7．查询销售记录表（sales表）中的前面20%的记录信息。 select top percent 20 from sales

8．从进货记录表（stock表）中检索进货日期（stockdate）信息，要求消除重复记录。

select distinct stockdate feom stock

9．从员工表（employeer表）中检索所有记录的姓名(name)、性别(sex)、出生日期(birthday)和住址(address)信息，并保存到表embak中。

select name,sex,birthday,address into embak from employeer

10．查询员工表（employeer表）中所有记录的姓名(name)和出生日期(birthday)，并按出生日期从大到小的顺序排列查询结果。

select name,birthday from employeer order by birthday desc

9

SQL Server 2005实验指导书

四、思考题

1．select命令的基本格式是什么？能颠倒命令中的各部分内容吗？可以换行书写吗？

2．查询命令select的使用有什么规律吗？请找出来。

3．不指定查询结果的标题时，显示的标题是什么？指定查询结果的标题有几种方式？

4．select命令可以显示常量、变量或表达式的结果吗？

5．如果指定查询结果的列标题后再用它新建一个表，则新表中的字段名和原来的有何区别？

6．如果要排序查询结果，使用什么关键词？要从大到小排序和从小到大排序有何区别？

10

SQL Server 2005实验指导书

实验五 条件查询

一、实验目的：

进一步理解查询的概念和方法，掌握使用条件进行数据查询。

二、实验示例

1．使用算术表达式作为条件进行数据查询。如查询book表中库存量（number）小于100的书名(bookname)、作者(author)和出版社(publisher)信息。

use bookstore go

select bookname 书名, author 作者,publisher 出版社 from book

where number<100

2．使用逻辑表达式作为条件进行数据查询。从book表中查询出价格介于13~16之间的图书信息。

use bookstore go

select * from book where price>=13 and price <=16

3．使用BETWEEN子句作为条件的数据查询。从book表查询出价格介于13~16之间的记录。

use bookstore go

select * from book where price between 13 and 16

4．使用null作为条件的数据查询。如从在图书供应商表（provider表）中查询出所有电话(phone)不为空的记录信息。

use bookstore go

11

SQL Server 2005实验指导书

select * from provider where phone is not null

5．使用IN子句作为条件的数据查询。从book表中查询出作者为“李平”或“高平”的书名(bookname)、出版号(isbn)、作者(author)及数量(number)。

use bookstore go

select bookname,isbn,author,number from book

where author in('李平','高平')

6．使用LIKE子句进行模糊查询。

1) 从book表中检索出所有作者（author）姓“沈”的记录信息。

use bookstore go

select * from book where author like '沈%'

2) 从book表中查询出作者(author)姓名中包含有‘平’或‘萍’的记录信息

use bookstore go

select * from book where author like '%[平萍]%'

三、实验内容

1．按实验示例的步骤完成示例练习。

2．从图书库存表（book表）中查询出每本单价(price)大于20元的书名（bookname）、作者(author)和单价(price)，并按单价降序排列。

select bookname,author,price from book where price>20 order by price desc

3．在员工表（employeer表）中查询出所有出生在1980年以后的记录信息。 select * from employeer where year(birthday)>1980

4．在员工表（employeer表）中查询出在1980年以后出生，且2002年以前就被雇佣的记录信息（出生日期birthday在1980年以后,雇佣日期hireday在2002

12

SQL Server 2005实验指导书

年以前）。

select * from employeer where year(birthday)>1980 and year(hireday)<2002

5.查询员工表（employeer表）中所有电话(telephone)为空的员工姓名(name)、性别（sex）和雇用日期（hireday）。

select name,sex,hireday from employeer where telephone is null

6．从销售记录表（sales表）中查询一次购买图书不小于20册（即number不小于20）的客户（client）和日期(saleday)。

select client ,saleday from sales where number>=20

7．从图书库存表（book表）中检索出机械工业出版社（publisher为机械工业出版社）的图书信息。

select * from book where publisher=’机械工业出版社’

8．查询图书库存表（book表）中的所有作者(author)姓王和姓孙的图书信息。 select * from book where author like’王%’ or author like ‘孙%’

9．从图书库存表（book表）中检索书名(bookname)中包括“语言”字符的图书信息。

select * from book where bookname like ‘%语言%’

10．从图书库存表（book表）中检索所有作者（author）是谭浩强、张基温、丁新民的图书信息。

select * from book where author in(‘谭浩强’,’张基温’,’丁新民’)

11．从book表中查找出作者为“吕一林”的书名。 select bookname ,author from book where author=’ 吕一林’

四、思考题

1．where子句应该放在select命令的什么位置？能放到其它位置吗？ 2．查询条件的表达方式有哪几种？

3．查询条件一定要在where后面吗？能不能随便放？

13

SQL Server 2005实验指导书

4．有两个以上的条件时应该如何书写？

5．用like关键字写查询条件时，条件的格式是怎样的？

14

SQL Server 2005实验指导书

实验六 数据分组与汇总查询

一、实验目的

1．熟练使用查询中的group by子句，将查询的数据记录按指定条件分成多个组。 2．在SELECT子句中使用sum、count、avg、max等汇总函数。

二、实验内容：

1．用统计函数进行统计计算

1）统计bookstore数据库中的book表中高等教育出版社(即publisher为高等教育出版社)的图书册数和平均价格。

use bookstore go

select sum(number) 册数 ,平均价格=avg(price) from book

where publisher='高等教育出版社'

2) 查询bookstore数据库中的book表中最高价格和最低价格。

use bookstore go

select max(price) 最高价 ,min(price) 最低价 from book

2．使用group by子句对查询结果进行分组统计

1）检索book表中各个出版社的图书数量和平均价格。

use bookstore go

select publisher 出版社,总数量=sum(number),平均价格=avg(price)

from book group by publisher

15

SQL Server 2005实验指导书

2） 检索book表中各出版社出版的图书最早时间和最晚时间(最小值和最大值)。

use bookstore go

select publisher, 最早时间=min(pubdate),max(pubdate) 最晚时间 from book group by publisher

3）查询供应商（provider表）中各地的供应商数量。

use bookstore go

select left(address,2), count(*) from provider group by left(address,2)

4）使用having过滤结果。查询供应商（provider表）中各地的供应商数量，并排除少于2个供应商的城市。

use bookstore go

select left(address,2), count(*) from provider group by left(address,2) having count(*)>1

三、实验内容

1．完成实验示例的操作练习。

2．根据销售记录表(sales)的数据计算2002年全年所销售书的总量。 select sum(number) from sales where year(saledate)=2002

3． 根据bookstore数据库中进货记录表（stock）的数据统计进货的的图书数量(即统计stock表中的进货数量)。 select sum(number) from stock

4．统计图书库存表（book表）中的图书平均价格、最低价格和最高价格。 select avg(price),min(price),max(price) from book

16

SQL Server 2005实验指导书

5．查询员工表（employeer表）中姓李的人数（即name中第一个字是李的数量）。 select left(name,1), count(*) from employeer where left(name,1)='李' group by left(name,1)

6.查询图书库存（book表）中高等教育出版社出版的价格在15到20元之间的图书种类。

select count(*) from book where price between 15 and 20

and publisher=’高等教育出版社’

7．查询图书库存（book表）中各出版社的图书种类、总数和平均价格。 select publisher,count(*),sum(number),avg(price) from book group by publisher

8．统计出book表各出版社出现的次数。排除小于3次的记录。 Select publisher,count(*) from book group by publisher having count(*)>=3 9．统计出员工表（employeer表）中各姓的人数（即根据name中第一个字进行分组统计）。

Select left(name,1) ,count(*) from employeer group by left(name,1) 10．统计员工表中男性和女性的人数。 select sex,count(*) from employeer group by sex

四、思考题

1．group by子句能和where子句一起使用吗？它们能颠倒顺序吗？

2．函数sum、avg、min、max和count能做什么？它们可以出现在什么位置？ 3．在一个命令中能同时使用几个统计函数吗？ 4．group by子句中的having子句和where子句相同吗？

17

SQL Server 2005实验指导书

实验七 高级数据查询

一、实验目的

1．学习在多个表之间进行数据联接查询 2．学习使用子查询进行数据查询

二、实验示例操作

1．多表查询

1）从sales 、book两个表中检索所有销售图书的书名、作者和销售价格。

use bookstore go

select book.bookname,book.author,sales.price from book,sales

where book.bookid=sales.bookid

2) 从stock和book表中检索所有进货图书的书名、进货价格和出版社。

use bookstore go

select b.bookname 书名,b.publisher 出版社, s.price 进货价

from book as b join stock s on b.bookid=s.bookid

3) 从sales 、book和employeer三个表中检索所有销售图书的员工名、书名、销售价格和销售数量。

use bookstore go

select e.name 员工姓名,b.bookname 书名, s.price 销售价,s.number 销售量

18

SQL Server 2005实验指导书

from employeer as e join sales s on e.emid=s.salerid join book as bon b.bookid=s.bookid

2．子查询

1）使用子查询进行比较测试：从book表中检索数据，列出高于平均价格的图书的书名、序列号、作者、价格及其出版社。

use bookstore go

select bookname,isbn,author,price,publisher from book

where price >(select avg(price) from book)

2)使用子查询进行集成员测试：从employeer和sales表中检索卖出了图书的员工的信息。

use bookstore go

select * from employeer where emid in (select salerid from sales)

3)使用子查询进行存在性测试：从sales表中检索卖出了图书的员工，然后在employeer表中查询的该员工的信息。

use bookstore go

select * from employeer e

where exists (select * from sales s where s.salerid=e.emid)

三、实验内容

1．完成示例操作的练习

2．查询员工表（employeer表）年龄最小（birthday最大）的员工姓名(name)、性别（sex）和雇用日期（hireday）。

19

SQL Server 2005实验指导书

select name,sex,hireday from employeer wher birthday=(select max(birthday) from employeer)

2．从库存表(book表)和销售表(sales表)中查询各类书籍的书名（bookname）、销售数量(number)。

select bookname,sum(sales.number) from book,sales where book.bookid=sales.bookid group by bookname

3．从进货表（stock）、供应商provider表和库存表（book）中查询所有进货的书名(bookname)、进货数量（number）和供应商名称(providername)。 select

bookname,stock.number,providername

from

stock,provider,book

where

provider.providerid=stock.providerid and book.bookid=stock.bookid

4．从库存表(book表)和销售表(sales表)中查询所有书籍的销售数量(number)（显示全部书名和销售数量）。

select bookname,sale.number from book,sales where book.bookid =sales.bookid

5．从进货表stock、库存表book和销售表sales中查询各种书籍的销售与进货差价。 select

bookname,cj=sales.price-book.price

from

book,stock,sales

where

stock.bookid=book.bookid and book.bookid=sales.bookid

6．从供应商provider表库存表book和进货表stock中查询库存量最多的图书提供商信息。 select

providerid,providername

from

provider,book,stock

where

book.number=(select max(number) from book) and provider.providerid=stock.providerid and stock.bookid=book.bookid

7．从员工表employeer和销售表sales中查询性别为男的员工的销售记录。 select * from sales where salerid in(select salerid from employeer where sex=’男’)

8．从进货表stock和库存表book中查询进货数量小于500册的书名（bookname），

20

SQL Server 2005实验指导书

作者(author)和库存数量(number)。

select bookname,author,book.number from book,stock where book.bookid=stock.bookid and stock.number<500

9．从sales表和book表中检索出库存表中各种图书的销售数量（显示书名和销售量，包括没有销售记录的图书）。

select

bookid,bookname,sum(number)

from

(select

book.bookid,

bookname,sales.number from book left join sales on book.bookid=sales.bookid) as temp

group by bookid,bookname

四、思考题

1．比较示例中的操作，分析多表联接查询可以使用哪些方式？ 2．什么时候使用子查询进行比较测试？ 3．什么情况下使用子查询进行集成员测试？ 4．总结存在性测试的使用方法。

21

本文来源：https://www.bwwdw.com/article/4fs7.html