2-2 《某电子商务网站》产品管理模块

Create Database  ProductDB
on Primary
(name=' ProductDB',
filename='D:\boss\ ProductDB.mdf')
log on
(name='ProductDB_log',
filename='D:\boss\ ProductDB_log.ldf')

use  ProductDB
go

Create table T_category
(
category_id int identity(1,1),
category_name varchar(30),
register_date datetime,
primary key(category_id )
)

Create table T_product
(
product_id int identity(1,1),
category_id  int,
product_name varchar(50),
price money,
remark varchar(2000),
register_date datetime,
primary key(product_id )
)

Create table T_product_review
(
produc_id int identity(1,1) ,
product_id int ,
review varchar(2000),
register_date datetime,
primary key(produc_id )
)


alter table T_product
add constraint FK_T_product_category_id foreign key(category_id ) references T_category(category_id )

alter table T_product_review
add constraint FK_T_product_review_product_id foreign key(product_id ) references T_product(product_id )
--产品价格列（Price）只能输入 1-1000 之间的数；--
alter table T_product
add constraint CK_price  check(price>0 and price<1001)
--录入时间列(Register_date)默认值为当前录入时间（三个表均设置）。--
alter table T_category
add constraint GE_T_category_register_date default getdate() for register_date

alter table T_product
add constraint GE_T_product_register_date default getdate() for register_date

alter table T_product_review
add constraint GE_T_product_review_register_date default getdate() for register_date

--在三个表分别中录入 3 条测试数据（样本数据包含下面题目中使用的数据）；--
insert into T_category values('家电','2011-3-6')
insert into T_category values('民用','2011-4-6')
insert into T_category values('生活','2011-2-6')

insert into T_product values('1','冰箱','481','挺好的','2011-3-6')
insert into T_product values('2','杀虫剂','32','还好','2011-5-2')
insert into T_product values('3','菜刀','10','挺好的','2011-3-6')


insert into T_product_review values('1','还不错','2011-4-3')
insert into T_product_review values('2','还不错','2011-6-4')
insert into T_product_review values('3','还不错','2011-5-3')

select category_id,product_name from [dbo].[T_product] where category_id=1
--查询某类别下所有产品；--
select *from [dbo].[T_product] where [price] between 300 and 500
--查询录入日期在 2011 年 3 月到 6 月之间的产品数据；--
select *from [dbo].[T_product] where [register_date] between '2011-3-1' and '2011-6-30'
--查询产品价格在 90-200 元之间的所有评论；--
select review from [dbo].[T_product_review],[dbo].[T_product] where [dbo].[T_product].product_id=[dbo].[T_product_review].product_id and price between 90 and 200
--查询评论数在 1-3 条的所有产品。--
select [product_name],[price] from 
[dbo].[T_product],[dbo].[T_product_review] where[dbo].[T_product].product_id=[dbo].[T_product_review].product_id and [produc_id] between 1 and 3
group by [product_name],[price]