数据库上机LAB5

主要考察了视图及相关操作，索引，批处理和变量，控制流。下面是题目及我的代码：
前提：
表结构为
customer(cid,gender,city)
food(cid,name,city,price)
neworder(oid,cid,fid,quantity)


TASK1:

–1. 建立购买过重庆或四川食物的顾客视图Shu-view（包含Customer中CID，City）

create view Shu_view
as
select distinct Customer.cid,Customer.City
from Customer,neworder,food
where 
Customer.CID=NewOrder.CID and
food.FID=NewOrder.FID and
(food.City='重庆' or food.City='四川')

–2. 运用视图Shu-view挑选出购买过重庆或四川食物的顾客中总消费最高的顾客CID

select shu_view.cid,sum(quantity*Price)
from Shu_view,NewOrder,Food
where 
shu_view.cid=Neworder.cid and
food.fid=neworder.fid
group by shu_view.cid
having sum(quantity*Price)>=all(
select sum(quantity*Price)
from Shu_view,NewOrder,Food
where 
shu_view.cid=Neworder.cid and
food.fid=neworder.fid
group by shu_view.cid
)

–3. 向视图Shu-view加入表项（16，湖南），能成功吗，为什么？
不能，因为视图只是一个虚表，不是一个物理表

–4. 建立男性顾客的视图Male-view（包含Customer中CID，City）,并要求对该视图进行的更新操作只涉及男性顾客。（WITH CHECK OPTION）

create view Male_view
as
select cid
from customer
where gender='男'
with check option

–5. 用对应的语句为以下的列创建恰当的索引，并写出相应的理由（也可以选择不创建）

--1.Food.Price	

/*建立非聚集索引，因为food表的主键fid就是其聚集索引*/
create nonclustered index price_index
on food  (price)

--2.Customer.Gender

/*建立非聚集索引，因为customer表的主键fid就是其聚集索引*/
create nonclustered index gender_index
on customer  (gender)

--3.Order.OID

/*没必要建立索引，因为order表本来就是按oid排序的*/

TASK2: --1. 写一个批处理，打印3遍消息“This is a test message”

print 'This is a test message'
go 3

–2. 建一个表Grade(Sno, value)，用循环结构插入100条记录，其中Sno是递增的奇自然数，value是0到100间的随机整数（rand函数）

create table Grade(
Sno int primary key not null,
value int
)

declare @i int
set @i=1
while @i<=100
	begin
		insert into Grade values (2*@i-1,cast(ceiling(rand() * 100) as int))
		set @i=@i+1
	end

–3. 写一个查询，展示学生的学号（Sno）和成绩状况。（成绩状况：对于Grade表中value大于80的行显示为’good’，60到80之间的显示’pass’，否则显示’not good’）

select grade.sno,passed=
case
when grade.value>80 then 'good'
when grade.value>60 and grade.value<=80  then 'pass'
else 'not good'
end
from grade

**）补一下知识点：case when 相当于构造一个新列，也就是一个新属性。它的多条件判断不是C里面的那样if … else if …if…，而是case when... then... when ... then... else... end这样。

–4. 用模拟的方法算PI的值，采样值取5000即可
–（提示：计算落入四分之一圆形区域的点数的占比）

declare @sum float,@i int,@x float,@y float
set @sum=0
set @i=0
while @i<=5000
	begin
		set @x=RAND()
		set @y=RAND()
		if @x*@x+@y*@y<=1
			set @sum=@sum+1
		set @i=@i+1
	end
print 'pi is '+convert(varchar,@sum/5000*4)

学到的新知识点：

1）按格式输出时，要先进行数据类型转换，比如把int、float型转成nvarchar。转换函数为convert（varchar,@带转换的数据）
2)生成1~N的随机整数数：cast(ceiling(rand()*N) as int)
3）生成0~N的随机整数：cast(ceiling(rand()*（N+1）) as int)