数据库实验2010-To

实验一T-SQL应用
一、实验目的
1．熟悉T-SQL的语法结构。

2．掌握使用T-SQL
二、实验内容
1、SQL应用
针对示例数据库pubs，完成以下SQL语句：
图书titles(title_id--图书号,title--书名,type--类别,pub_id--出版社号,price--价格)
作者authors(au_id--作者号,au_lname-作者名称,phone--电话,city--城市,state--地区)
出版社publishers(pub_id--出版社号,pub_name--出版社名,city--城市,state--地区,country--国家)
1) 查询图书表中书名中含有“can”或者“it”的图书的名称和出版社的名称
2) 查询与“New Moon Books”出版社在同一个城市的作者。

3) 查询各类图书的平均价格和总价格；
4) 查询作者名称为“white”的作者出版的所有图书；
5) 查询图书价格榜中排在前5名的图书书名和价格。

6) 查询“Five Lakes Publishing”出版社出版的所有图书信息。

7) 创建一个视图，包含如下内容：图书号，书名，价格，出版社名称
8) 增加一个出版社的信息，出版社编号：9800，出版社的名字为：Beijing Publishering,所在城市Beijing
9) 把“business”类的图书价格增高一元；
10) 把“Five Lakes Publishing”出版社出版的所有图书的价格增加一元
11) 查询各个城市的作者数目情况。

12) 查询每个出版社出版的图书的数目情况。

13) 把“Five Lakes Publishing”出版社出版的所有图书信息删除。

--图书titles(title_id--图书号,title--书名,type--类别,pub_id--出版社
号,price--价格)
--作者authors(au_id--作者号,au_lname-作者名称,phone--电话,city--城
市,state--地区)
--出版社publishers(pub_id--出版社号,pub_name--出版社名,city--城市,state--地区,country--国家)
--1) 查询图书表中书名中含有“can”或者“it”的图书的名称和出版社的名称
select title,pub_name from titles t,publishers p where t.title like '%can%'or t.title like'%it%'and t.pub_id=p.pub_id
--2) 查询与“New Moon Books”出版社在同一个城市的作者。

select au_lname from authors a,publishers p where p.pub_name='New Moon Books'and a.city=p.city
--3) 查询各类图书的平均价格和总价格；
select type,sum(price)as总价格,avg(price)as平均价格from titles group by type
--4) 查询作者名称为“white”的作者出版的所有图书；
select title from titles t,authors a,titleauthor ta where
a.au_lname='white'and ta.title_id=t.title_id and ta.au_id =a.au_id
--5) 查询图书价格榜中排在前名的图书书名和价格。

select top 5 title,price from titles order by price desc
--6) 查询“Five Lakes Publishing”出版社出版的所有图书信息。

select t.title_id,t.title,t.type,t.pub_id,t.price from titles
t,publishers p where p.pub_name='Five Lakes Publishing'and
p.pub_id=t.pub_id
--7) 创建一个视图，包含如下内容：图书号，书名，价格，出版社名称
create view titles_view
as
select t.title_id,t.title,p.pub_name,t.price
from titles t,publishers p where t.pub_id=p.pub_id
--8) 增加一个出版社的信息，出版社编号：，出版社的名字为：Beijing Publishering,所在城市Beijing
insert into publishers(pub_id,pub_name,city)values('9800','Beijing Publishering','Beijing')
--9) 把“business”类的图书价格增高一元；
select price=price+1 from titles where type='business'
--10) 把“Five Lakes Publishing”出版社出版的所有图书的价格增加一元
select price=price+1 from titles t,publishers p where p.pub_name='Five Lakes Publishing'and t.pub_id=p.pub_id
--11) 查询各个城市的作者数目情况。

select city,count(au_lname)as作者数目from authors group by city
--12) 查询每个出版社出版的图书的数目情况。

select p.pub_name,count(t.title)as图书的数目from titles t,publishers p where t.pub_id=p.pub_id group by p.pub_name
--13) 把“Five Lakes Publishing”出版社出版的所有图书信息删除。

delete from titles where pub_id=(select pub_id from publishers where pub_name='Five Lakes Publishing')
2、T-SQL应用
1）输入并执行下面语句
USE pubs
SELECT title, price,
(SELECT AVG(price) FROM titles) AS average,
price-(SELECT A VG(price) FROM titles) AS difference
FROM titles
WHERE type='business'
本语句的含义是_查询business类的图书价格及价格与平均价格的差_____？
2）输入并执行下面语句
USE pubs
SELECT pub_name
FROM publishers
WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')
本语句的含义是___查询business类的图书的出版社名称___________________？
3）使用Group by和Having子句
●输入并执行下面语句
USE pubs
SELECT type, avg_price = avg(price)
FROM titles
GROUP BY type
语句的含义是___查询每类图书的平均价格______________________？
●输入并执行下面语句
USE pubs
SELECT type, avg_price = avg(price)
FROM titles
GROUP BY type
HA VING avg(price)>14
语句的含义是_____查询每类图书的平均价格且平均价格大于14元_______？与上一个语句有什么不同____对查询的平均价格做了限制___?
4）使用Compute和Compute by子句
●输入并执行下面语句
USE pubs
SELECT type, price
FROM titles
ORDER BY type
COMPUTE SUM(price)
含义是____查询每类图书的价格，并按类型升序排序，并计算所有类型的总价格_____？
输入并执行下面语句
USE pubs
SELECT type, price
FROM titles
ORDER BY type
COMPUTE SUM(price) BY type
含义是_____查询每类图书的价格，并按类型升序排序，并分别计算每个类型的总价格________________？与上一个语句有什么不同___计算总价格时按类型分组____?
5）输入并执行下面语句，了解变量的使用。

Declare @vFirstName varchar(20)
Declare @vLastName varchar(20)
set @vLastName ='Dodsworth'
Select @vFirstName=FirstName From Northwind..Employees
Where LastName=@vLastName
Select @vFirstName
问题：@vFirstName=FirstName是什么含义？
答：将查找的内容赋值给变量vFirstName
6）输入并执行下面语句，测试使用函数。

Select Convert(Char(10),Getdate(),120)
问题：Convert函数的含义是什么？Convert函数中后面120的含义是什么？
答：Convert函数的含义：将一种数据类型转换为另一种数据类型
120的含义：日期格式
整句含义：获得数据库当前日期，且格式为"yyyy-mm-dd"？
7）输入并执行下面语句，测试使用IF条件语句和WHILE循环语句。

Declare @Compute Int
Set @Compute=0
WHILE @Compute<10
Begin
if @Compute<5
Begin
Print '此时@Compute变量小于5，值为'+Cast(@Compute As Char(5))
End
Else
Begin
Print '此时@Compute变量不小于5，值为'+Cast(@Compute As Char(5))
End
Set @Compute=@Compute+1
End
上面程序的含义是什么（实现了一个什么功能）？
答：循环打印出0-9十个数，并判断是否大于5
8）输入并执行下面语句，测试使用Case语句。

USE pubs
GO
SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
当price小于10的时候，显示结果是什么？当price大于10的时候，显示结果是什么？上面程序的含义是什么（实现了一个什么功能）？
答：price小于10
price大于10
含义：查询图书价格分类和图书名的前20个字符，按价格降序排列
3、使用T-SQL编程实现
1）实现1+2+…n的和（n由用户输入）
2）实现n! （n由用户输入）
3）实现类似于系统函数ROUND的功能的程序。

（选作）
三、回答问题：
1. case语句与if语句的异同。

2.全局变量的应用有什么特点。

3. 完成实验指导书的内容，完成实验报告。

实验二自定义函数和完整性约束一、实验目的
1．掌握Transact-SQL编程知识；
2．学习、掌握用户自定义函数的建立和使用
二、自定义函数
练习1：创建自定义函数
创建一个用户自定义函数，并测试、查看函数返回值。

1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_TaxRate
(@ProdID INT)
RETURNS numeric(5,4)
AS
BEGIN
RETURN
(SELECT
CASE CategoryID
WHEN 1 THEN 1.10
WHEN 2 THEN 1
WHEN 3 THEN 1.10
WHEN 4 THEN 1.05
WHEN 5 THEN 1
WHEN 6 THEN 1.05
WHEN 7 THEN 1
WHEN 8 THEN 1.05
END
FROM Products
WHERE ProductID = @ProdID)
END
GO
2）该函数的含义或作用是什么？此函数中输入变量是什么？返回值类型？如何定义的返回值？
3）输入并执行语句测试函数
SELECT ProductName, UnitPrice,Northwind.dbo.fn_TaxRate(ProductID) AS TaxRate, UnitPrice * Northwind.dbo.fn_TaxRate(ProductID) AS PriceWithTax
FROM Products
结果是什么？
注意：函数可以在Select子句后面调用。

练习2：返回值为多值的自定义函数
创建函数返回多列多值。

1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_LargeFreight
(@FreightAmt money)
RETURNS TABLE
AS
RETURN
( SELECT S.ShipperID, panyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightAmt
)
2）该函数的含义或作用是什么？函数中输入变量是什么？返回值类型？如何定义的返回值？
3）输入并执行语句测试函数
SELECT * FROM fn_LargeFreight(600)
结果如何？
练习3：返回值为多值的自定义函数
本实验创建的函数也是返回多列多值，注意与上面实验的差别。

1）输入并执行下面语句
USE Northwind
GO
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
AS
BEGIN
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES WHERE ReportsTo = @InEmployeeID
RETURN
END
GO
2）该函数的含义或作用是什么？此函数中输入变量是什么？返回值类型是什么？如何定义的返回值？
3）输入并执行语句测试函数
SELECT EmployeeID, [Name], Title, MgrEmployeeID FROM dbo.fn_FindReports(5)
结果是什么？
练习4：设计一个函数，在pubs数据库中，输入类别，返回该类别图书的平均价格和最高价格和该类图书的数量。

并设计调用该函数的例子。

练习5：设计一个函数，在pubs数据库中，输入书号，返回该书的出版社编号和名称。

并
设计调用该函数的例子。

练习6：设计一个函数，在pubs数据库中，根据图书的价格，大于20元钱的认为是高价，在10-20元之间的是中等价位，小于10元的为低价书。

并设计调用该函数的例子。

练习7：设计一个函数，根据输入的数值，计算从1加到该数的和（如输入5，则计算1+2+3+4+5=15,输出为15）。

并设计调用该函数的例子。

三、完整性约束
练习1：查询系统数据库pubs中titles表上都有哪些完整性约束，按照约束名称、类别和内容把他们列出来。

练习2：用T-SQL语言完成一下操作：
1）创建表area
对这个表进行完整性设置：
2）设置该表的主码为areaid
3）在该表上建立一个check约束和默认值约束：邮编只能是6位数字，地区名称默认为‘beijing’
练习3：创建一个数据完整性，实现产品表和库存表的级联删除，表结构如下：create table product
( productID varchar(10) not null,
productName char(30) not null);
create table storge
(productID varchar(10) not null,
price money ,
amount int,
volumn money)
练习4：对系统提供的示例数据库pubs，进行如下操作，能否成功？如果不成功，问题在哪里？有什么修改方案？
1)增加一个出版社的信息：出版社名称为“new publishers”,所在城市为：“shanghai”,
所在地区为“华东”。

2)把所有从“Ramona Publishers”出版社出版的图书信息都删除。

四、完成实验报告
1．回答上述实验中的问题
2．完整性约束的作用是什么？
实验三存储过程和触发器
一、实验目的
1．掌握Transact-SQL编程知识；
2．学习、掌握存储过程和触发器的建立和使用
二、存储过程
练习1建立简单存储过程
创建一个简单的存储过程，了解实现存储过程的语法。

1）输入并执行下面语句
USE Northwind
GO
CREATE PROCEDURE FirstProc
AS
SELECT TOP 5 ProductName, UnitPrice FROM Products ORDER BY UnitPrice desc GO
2）输入并执行如下语句：
Use northwind
exec firstproc
这个存储过程的含义是什么？是否可以用视图实现同样的功能？
注意：区别视图和存储过程。

练习2进一步使用存储过程
当执行存储过程时，将执行时的信息返回给用户
1）输入并执行下面语句
create proc Error_proc
as
declare @MaxPrice money
declare @Char varchar(20)
select @Maxprice=max(unitprice) from products --找出价格最大值，并将值赋给变量set @char=cast(@Maxprice as varchar(20)) --转换数据类型为字符型
raiserror('The max price is %s',10,1,@char)
go
2）输入并执行语句调用存储过程
exec error_proc
显示结果是什么？变量值是否传递给显示信息？
练习3使用输出参数返回变量值
通过使用Output选项返回存储过程中的数值
1）输入并执行下面语句
create proc Return_proc
@ReturnMaxPrice money output
as
select @ReturnMaxPrice=max(unitprice) from products
go
2）执行下面语句，调用存储过程
declare @return money
exec Return_proc @return output
select @return
是否显示结果？显示的内容是什么
注意：在存储过程中的返回参数定义Output选项，在调用存储过程时也要定义Output选项，来接收返回值。

练习4按如下要求编写存储过程，并执行
1）在pubs数据库中创建一个存储过程，输入书的ID号（title_id），存储过程检索该书的书名、出版社名。

2）创建一个存储过程，入口参数为一个时间类型的值，返回如下格式的时间字符串：xxxx 年xx月xx日。

(提示：使用DATEPART函数，可在联机丛书中查询使用方法)
3）在pubs数据库中创建一个存储过程。

如果作者所在的State为‘CA’，则显示为“加州”；
如果是‘KS’，显示为“堪萨斯”；若是其它州，显示为“Others”。

二、触发器
练习1：创建触发器
要求：Products表中的UnitIsStock字段存放的是每个产品的库存量，[Order Details]表中存放的是订单信息。

当增加一个新订单时，库存量应该自动减去订单里面的订货数量。

1）先使用sp_helptrigger [Order Details]命令查看[Order Details]表中关于触发器信息。

2）在[Order Details]表上创建触发器，自动计算库存量。

输入并执行下面语句
USE Northwind
GO
IF EXISTS ( SELECT name FROM sysobjects
WHERE type = 'TR' AND name = 'OrdDet_Insert' )
DROP TRIGGER OrdDet_Insert
GO
CREATE TRIGGER OrdDet_Insert
ON [Order Details]
FOR INSERT
AS
UPDATE P SET
UnitsInStock = (P.UnitsInStock - I.Quantity)
FROM Products AS P INNER JOIN Inserted AS I
ON P.ProductID = I.ProductID
GO
3）用sp_helptrigger [Order Details]命令查看[Order Details]表中关于触发器信息。

4）使用下面的语句测试触发器。

输入编号为11077的订单，并且订货数量为50，并用两个Select语句查看结果。

（下面语句一起执行结果会很明显）
select productid,UnitsInStock from products where productid=22
insert into [Order Details] (orderid,productid,unitprice,quantity,discount)
values (11077,22,21.00,50,0.0)
select productid ,UnitsInStock from products where productid=22
5）两次库存数量的差值是什么？
注意：临时表Inserted的使用。

练习2：创建删除触发器
设有两张表NewCategories和NewProducts。

当删除NewCategories表中一条记录时，NewProducts表中的相关数据同时删除。

1）创建两张新表NewCategories和NewProducts。

USE Northwind
GO
SELECT * INTO NewCategories FROM Categories
SELECT * INTO NewProducts FROM Products
GO
2）输入并执行下面语句，用以在NewCategories表上创建删除触发器
CREATE TRIGGER Category_Delete ON NewCategories
FOR DELETE
AS
DELETE NewProducts
FROM NewProducts AS P INNER JOIN Deleted AS d
ON P.CategoryID = D.CategoryID
3）使用下面的语句测试触发器。

在NewCategories表中删除分类号为6的记录，并用两个Select语句查看NewProducts表结果。

（下面语句一起执行结果会很明显）
SELECT ProductID, CategoryID, Discontinued
FROM NewProducts WHERE CategoryID = 6
DELETE NewCategories WHERE CategoryID = 6
SELECT ProductID, CategoryID, Discontinued
FROM NewProducts WHERE CategoryID = 6
4）NewProducts表中分类号为6的记录是否自动删除?
练习3：使用触发器验证业务规则
newProducts表中存放每个产品的基本信息，[Order Details]表中存放的是订单信息。

如果一个产品存在着订单，那么这个产品不能从newProducts表中被删除。

1）在上面实验创建的newProducts表上创建触发器。

USE Northwind
GO
CREATE TRIGGER Product_Delete
ON NewProducts FOR DELETE
AS
IF (Select Count (*)
FROM [Order Details] INNER JOIN deleted
ON [Order Details].ProductID = Deleted.ProductID
) > 0
BEGIN
RAISERROR('Transaction cannot be processed. This Product still has a history of orders.', 16, 1)
ROLLBACK TRANSACTION
END
2）使用下面语句测试触发器，将产品编号为1的产品信息从NewProducts中删除。

DELETE NewProducts WHERE ProductID = 6
3）是否能删除？为什么？
练习4：约束与触发器的激活顺序
Products表中存放每个产品的基本信息，[Order Details]表中存放的是订单信息。

如果一个产品存在着订单，那么这个产品不能从Products表中被删除。

1）在Products表上创建触发器。

USE Northwind
GO
CREATE TRIGGER Product_Delete2
ON Products FOR DELETE
AS
IF (Select Count (*)
FROM [Order Details] INNER JOIN deleted
ON [Order Details].ProductID = Deleted.ProductID
) > 0
BEGIN
RAISERROR('Transaction cannot be processed. This Product still has a history of orders.', 16, 1)
ROLLBACK TRANSACTION
END
2）使用下面语句测试触发器，将产品编号为1的产品信息从Products中删除。

DELETE Products WHERE ProductID =1
4）是否能删除？为什么？
三、触发器设计（选做）
1．设计三种触发器：INSERT 、UPDATE和DELETE触发器。

（可以在pubs数据库上，也可以自己设计数据库。

如是自己设计数据库，要写明数据库的表结构）
2．设计验证触发器起作用的例子。

实验四使用ADO访问数据库
一、实验目的
⏹了解、体会Client/Server 体系结构应用程序的特点。

⏹掌握使用ADO对象编写数据库访问程序的概念、方法、一般步骤。

二、实验内容
1.建立学生数据库。

2.使用VB设计界面，访问后台数据。

3.实现数据的查询。

4.实现插入、删除、修改student表中的数据功能。