# DBFinalWork

**Repository Path**: zrsharp/DBFinalWork

## Basic Information

- **Project Name**: DBFinalWork
- **Description**: No description available
- **Primary Language**: SQL
- **License**: GPL-3.0
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2020-05-22
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README


* [视图](#视图)
* [触发器](#触发器)


# 视图

```sql
--会员信息视图
--使用场景：查看会员卡的时候，直接能看到会员的会员卡信息会员卡类型以及和部分用户信息
--数据来源表：TM_User，VIPCard，VIP_Type
create view vip_info_view as
select u.userName,
    u.sex,
    u.headPortraitURL,
    u.phoneNumber
    v.cardId,
    t.typeName as VIPType,
    v.startTime,
    v.endTime,
    suitableConditions
from TM_User u, VIPCard v, VIP_Type t
where u.userName = v.userName
and v.VIP_TypeID = t.VIP_TypeID;


--第一分类视图
--使用场景：当进行分类筛选的时候，能直接从视图中获取所有没有父级分类的类别
--数据来源表：classification，classificationRelation
create view first_class_view as
select c.classificationID,
    c.name
from classification c
where c.classificationID 
not in
    (select SClassificationID 
    from classificationRelation);


--商品信息视图
--使用场景：查看商品的基本信息，当前价格，浏览次数，关注次数，平均星级，下单次数
--数据来源表：product，priceCalendar，productBrowsingRecord，productConcern， productEvaluation
create view product_info_view as
select p.productID,
    p.productName,
    p.productDescription,
    s.shopName,
    (select top 1 pr1.price
    from priceCalendar pr1
    where p.productID = pr1.productID
    order by pr1.price desc)
    as price,

    (select count(*)
    from productBrowsingRecord pbr1
    where pbr1.productID = p.productID)
    as visitTimes,

    (select count(*)
    from productConcern pc1
    where pc1.productID = p.productID)
    as concernTimes,

    (select avg(pe1.star)
    from productEvaluation pe1
    where pe1.productID = p.productID
    group by pe1.productID)
    as averageStars,

    (select count(*)
    from orderForm o1
    where o1.productID = p.productID)
    as boughtTimes
from product p, shop s, priceCalendar pr, productBrowsingRecord pbr,
    productConcern pc, productEvaluation pe
where p.shopID = s.shopID
and p.productID = pbr.productID
and p.productID = pc.productID
and p.productID = pe.productID;


--店铺信息视图
--使用场景：查看店铺基本信息，收藏次数，平均星级，订单数目
--数据来源表：shop，shopCollection，productEvaluation，orderForm，product
create view shop_info_view as
select s.shopID,
    s.shopName,
    s.shopDescription,
    s.userName as sellerName,
    
    (select count(*)
    from shopCollection sc1
    where sc1.shopID = s.shopID)
    as collectedTimes,
    
    (select avg(pe1.star)
    from productEvaluation pe1
    where pe1.productID
    in (select productID from product where product.shopID = s.shopID)
    	group by pe1.productID)
    as averageStars,
    
    (select count(*) from orderForm o1 where o1.productID  
    in (select productID from product where product.shopID = s.shopID)) 
    as finishedOrderNum
from shop s;

--员工收入视图
--使用场景：财务统计金额的时候，只能看到员工编号职位性别工资，但不能看到其他的信息
--数据来源表：shopEmpolyee
create view empolyee_income_view as
select se.empNo, se.sex, se.duty, se.salary, se.shopID
from shopEmpolyee se;


--已通过的店铺的申请信息视图
--使用场景：查看开店申请已经通过店铺的信息以及之前的申请表信息。
--数据来源表：shopApplying，shopApplyingPass
create view shop_apply_pass_view as
select sa.applyNo,
	sa.userName,
	sa.content,
	sa.commitTime,
	sa.applyState,
	sap.recordNo as apply_pass_num,
	sap.shopID,
	sap.passTime
from shopApplying sa
inner join shopApplyingPass sap
on sa.applyNo = sap.applyNo


--订单信息视图
--使用场景：查看订单信息，包括订单基本信息和订单的交易金额，买家姓名手机，物流序号，收货人姓名手机和详细地址以及邮编
--数据来源表：orderForm，priceCalendar，logisticsInfo，address，TM_User
create view order_info_view as
select oa.*,
    u.phoneNumber as buyerPhone,
    (oa.productQuantity * pc.price) as sumMoney,
    li.logisticsItemNo
from logisticsInfo li,
    priceCalendar pc,
    (select
        o.orderFormID,
        o.productQuantity,
        o.productID,
        o.userName as buyerName,
        o.recordTime,
        o.orderFormState,
        a.receiverName,
     	a.phoneNumber as receiverPhone,
     	province, city, county, township, detail, postcode
    from orderForm o
    left join address a
    on o.addressNo = a.addressNo) oa,
    TM_User u
where li.orderFormID = oa.orderFormID
and oa.productID = pc.productID
and oa.recordTime = pc.recordDate
and oa.buyerName = u.userName
```



# 触发器

```sql
--使用场景：在向TM_User表插入数据时，检查sex的值的合法性
--触发条件：插入的值中sex的值不是‘男’或‘女’其中一个
--触发类型：强化约束（Enforce restriction）
CREATE TRIGGER TS_SEX
ON TM_User
AFTER INSERT
AS
IF EXISTS(SELECT * FROM TM_User WHERE sex NOT IN ('男','女'))
	BEGIN 
		RAISERROR('性别只能是男和女两个选择之中的一个',16,1)
	ROLLBACK TRANSACTION
	END


--使用场景：当productSubdivide商品价格修改时，priceCalendar中的价格自动修改
--触发条件：更新productSubdivide表中商品的价格的时候
--触发类型：跟踪变化Auditing changes
CREATE TRIGGER Synchronization_PriceCalander
ON productSubdivide
FOR UPDATE
AS
IF UPDATE(price)
BEGIN
UPDATE priceCalendar
SET price = p2.price 
FROM productSubdivide p1,productSubdivide p2 
WHERE p1.productID = p2.productID
END


--使用条件：买家将商品加入购物车后，强制执行总金额=商品单价*数量
--触发条件：ShoopingCartItems条目增加
--触发类型：
CREATE TRIGGER Calculate_ShoppingCart_Sum
ON shoppingCartItems
FOR INSERT
AS
BEGIN TRANSACTION
UPDATE buyer
SET shoppingCartSum += p.price * s.quantity   
FROM shoppingCartItems s,productSubdivide p
WHERE s.productID = p.productID  
COMMIT TRANSACTION  


--使用条件：创建订单后立刻创建对应的物流
--触发条件：orderForm增加一条记录
--触发类型：
CREATE TRIGGER Create_Logistics
ON orderForm
AFTER INSERT
AS
BEGIN
	DECLARE @orderFormID char(10), @recordTime datetime
	SELECT @orderFormID = orderFormID FROM orderForm
	SELECT @recordTime = @recordTime FROM orderForm
	INSERT INTO logisticsInfo VALUES(@orderFormID,NULL,@recordTime,NULL)
END


--使用条件:删除父分类条目时删除他的子分类条目
--触发条件：删除classification中一条记录
--触发类型：
CREATE TRIGGER Delete_SClassificationID
ON classification
AFTER DELETE
AS
BEGIN
	DECLARE @ID char(10)
	SELECT @ID = classificationID  FROM deleted
	DELETE FROM classificationRelation
	WHERE classificationRelation.FClassificationID = @ID
END


--使用条件:禁止用户更改已经注册了的用户名
--触发条件：用户名被修改时
--触发类型：
CREATE TRIGGER Protect_UserName
ON TM_User
FOR UPDATE
AS
IF(UPDATE(userName))
	BEGIN
		PRINT'禁止更改用户名'
	ROLLBACK TRANSACTION
	END


--使用条件：删除一个商品时将他的图片也删除
--触发条件：删除一个商品
--触发类型：
CREATE TRIGGER Delete_Product
ON product
FOR DELETE
AS
BEGIN
	DECLARE @ID char(10)  --定义一个临时变量ID
	SELECT @ID = productID  FROM deleted  --这个临时表里存刚删掉的那个ID
	PRINT @ID
	DELETE FROM productPics WHERE productID = @ID  --然后根据这个ID删
END


--使用条件：聊天参与列表增加一条，就要创建一个对应的聊天窗口
--触发条件：向chatParticipationZ增加一条信息时
--触发类型
CREATE TRIGGER Add_ChatWindow
ON chatParticipation
FOR INSERT
AS 
BEGIN
	DECLARE @ID CHAR(10)
	SELECT @ID = chatWindowID FROM inserted
	INSERT INTO chatWindow VALUES(@ID,NULL)
END


--使用条件：关闭店铺时，检查买家的收藏夹，若有该店铺，则自动将买家收藏夹中该店铺的记录删除
--触发条件：向chatParticipationZ增加一条信息时
--触发类型
CREATE TRIGGER Check_Time
ON shop
FOR DELETE
AS
BEGIN
	DECLARE @shopID CHAR(10)
	SELECT @shopID = shopID FROM deleted
	DELETE FROM shopCollection WHERE shopID = @shopID
END


--使用条件：向购物车添加商品时，检查添加的商品是否不存在或者库存小于1，则提示错误
--触发条件：shoppingCartItems发生INSERT操作
--触发类型
CREATE TRIGGER Add_Product
ON shoppingCartItems
FOR INSERT
AS
BEGIN TRANSACTION 
IF NOT EXISTS(SELECT productID FROM product WHERE productID IN (SELECT productID FROM inserted))
BEGIN
	RAISERROR('该商品不存在',16,1)
	ROLLBACK
	RETURN
END
IF EXISTS(SELECT product.productID FROM product,productSubdivide WHERE product.productID IN (SELECT productID FROM inserted)
	AND product.productID = productSubdivide.productID AND productSubdivide.inventory < 0)
BEGIN
	RAISERROR('该商品没有库存',16,1)
	ROLLBACK
	RETURN
END
```