Prayer

*****************************************************************************************
視圖,存儲(chǔ)過程和觸發(fā)器
*****************************************************************************************

創(chuàng)建視圖:(查詢數(shù)據(jù),用于系統(tǒng)統(tǒng)計(jì)報(bào)表)
create view dept_sum_vu
(name,minsal,maxsal,avgsal)
as select d.department_name,min(e.salary),max(e.salary),avg(e.salary)
from employees e,departments d
where e.department_id = d.department_id
group by d.department_name;

存貯過程,觸發(fā)器和刪除外鍵

1).存貯過程:
存貯過程的路徑:打開Stored Procedures中new一個(gè)新的Stored Procedures,中間填寫代碼,代碼如下:
CREATE PROCEDURE sa.bbsTest AS

select Bbsid ,content , area_id , name FROM BBs a JOIN bbs_area b
on a.area_id = b.id


CREATE PROCEDURE bbsTest AS

select Bbsid ,content , area_id , name FROM BBs a JOIN bbs_area b
on a.area_id = b.id

2).觸發(fā)器

打開用戶表項(xiàng),點(diǎn)設(shè)計(jì)表,選中其中一個(gè)字段,右鍵task點(diǎn)manager triggers,在彈出的表單中寫代碼:
CREATE TRIGGER [tD_bbs_area] ON [dbo].[bbs_area]
FOR DELETE
AS
BEGIN
DELETE rebbs
FROM bbs, deleted
WHERE rebbs.bbs_id = bbs.bbsid
AND bbs.area_id = deleted.id

DELETE bbs
FROM deleted
WHERE bbs.area_id = deleted.id

END

3).刪除外鍵
打開用戶表項(xiàng),點(diǎn)設(shè)計(jì)表,選中其中一個(gè)字段,右鍵relationships,在彈出的主鍵和外鍵中選擇就可以了.
刪除外鍵,需要先刪除主鍵表里面的有關(guān)外鍵的字段,再來刪除外鍵的有關(guān)字段.