SQL Server进阶应用实验

数据表以及复杂计算列的定义方法

已知以下info表和salelist表的表结构信息。其中，产品基本信息表info用于保存产品的基本信息，包括产品的编号、名称、单价（出厂价）以及产品的生产日期。出货单表salelist用于保存产品的出货记录，厂家每一次出货都会在salelist表中形成一条出货记录，且出货记录的属性包括：

1. 出货批次编号no：出货时给出的出货编号，是唯一的； 产品编号pno：对应于表info的产品编号pno；

2. 产品件数pnum：根据客户购买的数量来设定； 产品单价price：来自表info的产品单价；

3. 本批次产品总额to_price：计算列，根据列pnum和列price来计算，具体计算方法见下面说明

4. 下单日期pdate：顾客下单的日期；

计算列to_price的计算方法：一般情况下，本批次产品的总额to_price =产品单价price*产品件数pnum。如果购买数量等于或超过500，则按8折计算总额。

请给出创建这两个表的create语句。


复杂计算列的定义方法：

SQL语句：

create table info(
     pno int,
		pname varchar(50) constraint i2 not null,
		price float constraint i3 not null check(price>=0),
		pyear date constraint i4 not null
		primary key(pno)
);

create table salelist(
     no char(5) constraint s1 unique,
	   pno int constraint s2 not null,
	   pnum int constraint s3 not null default 0,
	   price float constraint s4 check(price>=0),
	   pdate date constraint s6 not null default'2018-4-1',
	   to_price as
	             case
				  when pnum>=0 and pnum<500 then price*pnum
				  when pnum>=500 then price*0.8*pnum
				  end
	   primary key(no)
);

说明：
pyear题目所给的数据类型为smalldatetime。smalldatetime包含日期与时间，且精确度比datetime更高。但pyear的取值范围又要求只包含年、月、日信息。一般情况下，若是在查询时有该要求，则可以使用

select convert(varchar(10),pyear,120) as [yyyy-mm-dd]

进行转化。在定义列的数据类型时，可直接将smalldatetime转换为date，直接只显示年月日信息。

插入数据

一、请基于以下insert语句，向表info中加入4条元组，然后用select语句查看已插入的所有元组的信息

INSERT info VALUES(1, ‘单车’, 510, ‘2009-5-1’);
INSERT info VALUES(2, ‘电瓶车’, 2300, ‘2011-5-1’);
INSERT info VALUES(3, ‘手表’, 7800, ‘2015-10-1’);
INSERT info VALUES(4, ‘水杯’, 150, ‘2016-7-1’);

SQL语句：

insert into info(pno,pname,price,pyear) values(1,'单车',510,'2009-5-1');
insert into info(pno,pname,price,pyear) values(2,'电瓶车',2300,'2011-5-1');
insert into info(pno,pname,price,pyear) values(3,'手表',7800,'2015-10-1');
insert into info(pno,pname,price,pyear) values(4,'水杯',150,'2016-7-1');
select * from info;

二、请基于以下insert语句，向表salelist中加入7条元组（注意：这7条元组只对salelist表中的no, pno, pnum, pdate这4个属性进行赋值），然后用select语句查看已插入的所有元组的信息

INSERT salelist(no,pno,pnum,pdate) VALUES(‘00001’,1,800,‘2017-1-10’);
INSERT salelist(no,pno,pnum,pdate) VALUES(‘00002’,1,200,‘2017-11-10’);
INSERT salelist(no,pno,pnum,pdate) VALUES(‘00003’,2,500,getdate()); --getdate()表示去取当前日期
INSERT salelist(no,pno,pnum,pdate) VALUES(‘00004’,2,750,‘2018-4-10’);
INSERT salelist(no,pno,pnum,pdate) VALUES(‘00005’,3,10,‘2018-4-10’);
INSERT salelist(no,pno,pnum,pdate) VALUES(‘00006’,4,1000,‘2017-12-10’);
INSERT salelist(no,pno,pnum,pdate) VALUES(‘00007’,4,1000,‘2017-12-10’);

SQL语句：

insert into salelist(no,pno,pnum,pdate) values('00001',1,800,'2017-1-10');
insert into salelist(no,pno,pnum,pdate) values('00002',1,200,'2017-11-10');
insert into salelist(no,pno,pnum,pdate) values('00003',2,500,getdate());
insert into salelist(no,pno,pnum,pdate) values('00004',2,750,'2018-4-10');
insert into salelist(no,pno,pnum,pdate) values('00005',3,10,'2018-4-10');
insert into salelist(no,pno,pnum,pdate) values('00006',4,1000,'2017-12-10');
insert into salelist(no,pno,pnum,pdate) values('00007',4,1000,'2017-12-10');
select * from salelist;      

用一个数据表去更新另外一个数据表的方法

用表info中单价price更新表salelist中的单价price。给出相应的update语句，同时用select语句查询表salelist中的所有信息（注意查看salelist表中的计算列to_price的值是不是基于更新的price属性值也被同步更新了）。

数据更新的语法格式：
update 表名
set 要修改的字段名称=字段修改后的新值
where 修改条件

用一个表更新另一个表：
update 待更新表的表名
set 要修改字段的名称=字段修改后的新值
from 待更新表表名
join 另一个表的表名
on 待更新表.两表间联系的字段=另一个表.两表间联系的字段

SQL语句：

update salelist
set salelist.price=info.price
from salelist 
join info
on salelist.pno=info.pno;
select * from salelist;     

用户自定义函数的定义和调用方法

函数的定义

根据以下SQL代码定义一个函数get_info(@dtime smalldatetime)，使之能够根据给定的日期时间值dtime返回salelist表中所有下单日期比这个日期时间值dtime更早（包括这个日期时间值，即@dtime > salelist.pdate）的所有元组信息（包括salelist表所有属性值）。

定义函数的语句格式：
create function 函数名 (参数) return table
as <过程化SQL块>

局部变量的声明方法：
@局部变量名

SQL语句：

go
create function get_info(@dtime smalldatetime) returns table
as return
( select * from salelist
  where @dtime>pdate
)
go    

注：create function语句为批处理中仅有的语句，故其前后都需要添加go。

函数的调用

利用定义的函数get_info( )，查询salelist表中2018年1月1日以前生产的产品的产品出货信息（包括salelist表所有属性值）。请给出相应的查询代码。

SQL语句：

select * from get_info(‘2018-1-1’);

数据表的删除方法和数据表中数据的删除方法

表中数据的删除

清空salelist表中的所有元组。

SQL语句：

delete from salelist;

数据表的删除

删除salelist表。

SQL语句：

drop table salelist;

参考文献

老师给的实验模板