Êý¾Ý¿â¹¤×ʹÜÀíϵͳ±¨¸æºÍ´úÂë´óÈ« ÏÂÔØ±¾ÎÄ

create table ²¿ÃÅ( ²¿ÃÅÃû³Æ CHAR(50) NOT NULL primary key, ²¿ÃŸºÔðÈË CHAR(50) NOT NULL, ²¿ÃÅÈËÊý CHAR(50) );

create table Ô±¹¤(

¹¤ºÅ CHAR(50) NOT NULL primary key, ÐÕÃû CHAR(50) NOT NULL,

ÐÔ±ð CHAR(2) DEFAULT'ÄÐ'CHECK(ÐÔ±ð IN('ÄÐ','Å®')), ÄêÁä CHAR(10) NOT NULL, ¼®¹á VARCHAR(50) NOT NULL,

Éí·ÝÖ¤ºÅ CHAR(50) UNIQUE NOT NULL, »éÒöÇé¿ö CHAR(50), ѧÀú CHAR(50),

²¿ÃÅÃû³Æ CHAR(50) NOT NULL, ¹¤ÖÖ CHAR(50),

¹¤Áä CHAR(4) NOT NULL, Ö°ÎñµÈ¼¶ CHAR(50) );

CREATE TABLE ¹¤×Ê(

¹¤ºÅ char(50) not null primary key, ¹¤×ÊÄêÔ char(50) not null , ¸Úλ¹¤×Ê decimal not null, ¸Úλ²¹Ìù decimal,

³öÇÚÌìÊý decimal not null, ¼Ó°à¹¤Ê± decimal, ¼Ó°à¹¤×Ê decimal, ¼Ó°àÌìÊý decimal, ¼Ó°àÀà±ð char(50), ²¡¼Ù¿Û¿î decimal, ʼٿۿî decimal, ÆäËû¿Û¿î decimal, Ó¦·¢¹¤×Ê decimal,

ʵ·¢¹¤×Ê decimal not null,

foreign key (¹¤ºÅ) references Ô±¹¤(¹¤ºÅ)); alter table ¹¤×Ê

add constraint CK_gongho check(¹¤ºÅ>0);

create table ϵͳÓû§

(Óû§Ãû char(50) primary key, Óû§È¨ÏÞ char(50), Óû§ÃÜÂë char(10),)

create table ¹¤Á乤×Ê (

¹¤Áä char(4) primary key, ¹¤Á乤×Ê float(8)not null);

create table Ô±¹¤µÇ½ÃÜÂë (

¹¤ºÅ CHAR(50) primary key, ÃÜÂë char(6),

foreign key(¹¤ºÅ) references Ô±¹¤(¹¤ºÅ)); alter table Ô±¹¤µÇ½ÃÜÂë

add constraint CK_gongh check(¹¤ºÅ>0);

£¨Èý£©ÊÓͼ

(1)²éѯԱ¹¤¹¤×ÊÇé¿ö

½¨Á¢Ò»¸öÊÓͼ£¬·´Ó³Ô±¹¤ÐÕÃû¼°¹¤×ÊÇé¿ö¡£

CREATE VIEW Ô±¹¤_¹¤×Ê

AS

SELECT Ô±¹¤.ÐÕÃû, ¹¤×Ê.* FROM Ô±¹¤,¹¤×Ê

WHERE Ô±¹¤.¹¤ºÅ=¹¤×Ê.¹¤ºÅ

£¨ËÄ£©´æ´¢¹ý³Ì

(1)Ö¸¶¨Ô±¹¤¹¤×ÊÇé¿öµÄ²éѯ

¶¨ÒåÒ»¸öÃûΪstsffCostµÄ´æ´¢¹ý³Ì£¬¸Ã´æ´¢¹ý³ÌÒÔÒÔ¹¤×ÊÄêÔÂ(£Àstaffgongzi)¡¢³öÇÚÌìÊý(£Àstaffchuqin)¡¢¼Ó°à¹¤×Ê(£Àstaffjiaban)¡¢Ó¦·¢¹¤×Ê(£Àstaffying)¡¢Êµ·¢¹¤×Ê(£Àstaffshi)ΪÊäÈë²ÎÊý·µ»ØÖ¸¶¨Ô±¹¤¹¤×ÊÐÅÏ¢µÄ´æ´¢¹ý³Ì¡£ stsffCost´úÂëÈçÏ£º

CREATE PROCEDURE staffCost @staffgongzi decimal, @staffchuqin decimal, @staffjiaban decimal, @staffying decimal, @staffshi decimal AS

SELECT * FROM ¹¤×Ê

WHERE ¹¤×ÊÄêÔÂ=@staffgongzi AND ³öÇÚÌìÊý=@staffchuqin AND ¼Ó°à¹¤×Ê=@staffjiaban AND Ó¦·¢¹¤×Ê=@staffying AND ʵ·¢¹¤×Ê=@staffshi GO

(2£©Ö¸¶¨·¶Î§µÄÔ±¹¤µÄÐÅÏ¢²éѯ¡£ ÒÔÔ±¹¤¹¤ºÅ¡¢²¿ÃűàºÅΪÊäÈë²ÎÊý·µ»ØÖ¸¶¨Ô±¹¤»ù±¾ÐÅÏ¢µÄ´æ´¢¹ý³Ì£¬ÒªÇóÊäÈ벿·ÖÔ±¹¤ÐÅÏ¢²éѯԱ¹¤È«²¿ÐÅÏ¢¡£

¶¨ÒåÒ»¸öÃûΪstsffInfoµÄ´æ´¢¹ý³Ì£¬¸Ã´æ´¢¹ý³ÌÒÔÔ±¹¤¹¤ºÅ(£ÀstaffNum)¡¢²¿ÃűàºÅ(£ÀbmNum)×÷ΪÊäÈë²ÎÊý£¬´æ´¢¹ý³Ì·µ»Ø²éѯµ½µÄ½á¹û¼¯¡£

satffInfoµÄ¶¨Òå´úÂëÈçÏ£º

CREATE PROCEDURE staffInfo @staffNum VARCHAR(50)='%', @bmNum VARCHAR(50)='%' AS

SELECT *

FROM Ô±¹¤,²¿ÃÅ

WHERE Ô±¹¤.²¿ÃÅÃû³Æ=²¿ÃÅ.²¿ÃÅÃû³Æ AND

((Ô±¹¤.¹¤ºÅ LIKE @staffNum VARCHAR(50)+'%') AND (²¿ÃűàºÅ LIKE @bmNum VARCHAR(50)+'%')) GO

£¨3£©Í³¼ÆÖ¸¶¨Äê·Ý¸÷²¿ÃÅÿ¸öÔ·ݵŤ×Ê×ܶ

ÒÔÄê·ÝΪÊäÈë²ÎÊý£¬ÒÔ×ܹ¤×ʽð¶îΪÊä³ö²ÎÊý£¬²úÉú¸ÃÄê¶È12¸öÔ·ݵÄÿ²¿Ãʤ×Êͳ¼ÆµÄ´æ´¢¹ý³Ì¡£¶¨Òå¸Ã´æ´¢¹ý³ÌÃûΪbmMonhSum£¬ÊäÈë²ÎÊýΪ£ÀThisYear£¬ÊµÏÖ´úÂëÈçÏ£º

CREATE PROCEDURE bmMonthSum @ThisYear CHAR(10) AS

SELECT DATEPART(MM,¹¤×ÊÄêÔÂ) AS Ô±¹¤¹¤×ÊÔ·Ý,SUM(ʵ·¢¹¤×Ê) AS ²¿ÃÅÈËÊý FROM Ô±¹¤,¹¤×Ê,²¿ÃÅ

WHERE Ô±¹¤.¹¤ºÅ=¹¤×Ê.¹¤ºÅ AND Ô±¹¤.²¿ÃÅÃû³Æ=²¿ÃÅ.²¿ÃÅÃû³Æ AND DATEPART(YYYY,¹¤×ÊÄêÔÂ)=@ThisYear

GROUP BY DATEPART(MM,¹¤×ÊÄêÔÂ) GO

£¨Î壩´¥·¢Æ÷

£¨1£©¹Ø¼ü²Ù×÷£¬ÔÚ¹¤×ʱíÀïÌí¼ÓÒ»ÌõеļǼ£¬Êµ·¢¹¤×Ê»á×Ô¶¯¸üÐÂеÄÊý¾Ý¡£¶¨ÒåÕâ¸ö´¥·¢Æ÷Ãû³ÆÎªinsert_¹¤×Ê¡£ ´úÂëÈçÏ£º

create trigger insert_¹¤×Ê on ¹¤×Ê for insert as

declare @i char(20) declare @bb int set @i=case

when @bb=0001 then 'ʵ·¢¹¤×Ê' when @bb=0002 then 'ʵ·¢¹¤×Ê' end

print @i

(2)Ô±¹¤Ðµ÷Èë

½øÐÐÔ±¹¤Ðµ÷Èë²Ù×÷ÊÇÔÚÔ±¹¤ÐÅÏ¢±íÖÐÌí¼ÓÒ»ÌõеļǼ£¬¿ÉÒÔΪԱ¹¤ÐÅÏ¢±íÉè¼ÆÒ»¸öInsert´¥·¢Æ÷£¬µ±Ô±¹¤ÐÅÏ¢±íÖ´ÐÐInsert²Ù×÷ºó×Ô¶¯¸ü¸Ä³ö²¿ÃÅÐÅÏ¢±íÏàÓ¦¼Ç¼µÄÊý¾Ý¡£¶¨ÒåÕâ¸ö´¥·¢Æ÷Ãû³ÆÎªstaff_insert¡£ Æä´úÂëÈçÏ£º

CREATE TRIGGER staff_insert ON Ô±¹¤ FOR INSERT

AS IF (SELECT COUNT(*)

FROM ²¿ÃÅ,inserted,¹¤×Ê

WHERE ²¿ÃÅ.²¿ÃÅÈËÊý=inserted.²¿ÃÅÈËÊý AND ¹¤×Ê.¹¤ºÅ=inserted.¹¤ºÅ)=0 ROLLBACK TRANSACTION GO

£¨3£©Ô±¹¤µ÷³ö

½øÐÐÔ±¹¤µ÷³ö²Ù×÷ÊÇÔÚÔ±¹¤ÐÅÏ¢±íÖÐɾ³ý¸ÃÔ±¹¤µÄ¼Ç¼£¬¿ÉÒÔΪԱ¹¤ÐÅÏ¢±íÉè¼ÆÒ»¸öDelete´¥·¢Æ÷£¬µ±Ô±¹¤ÐÅÏ¢±íÖ´ÐÐDelete²Ù×÷ºó£¬¹¤×Ê±í¡¢²¿ÃűíÖеļǼҲ½«È«²¿É¾³ý¡£¶¨ÒåÕâ¸ö´¥·¢Æ÷Ãû³ÆÎªstaff_delete¡£ ÆäʵÏÖ´úÂëÈçÏ£º

CREATE TRIGGER staff_delete ON Ô±¹¤ FOR DELETE

AS SELECT COUNT(*)

FROM ²¿ÃÅ,¹¤×Ê,deleted

WHERE ²¿ÃÅ.²¿ÃÅÈËÊý=deleted.²¿ÃÅÈËÊý AND ¹¤×Ê.¹¤ºÅ=deleted.¹¤ºÅ GO

(4)Ô±¹¤ÄÚ²¿µ÷¶¯

ΪԱ¹¤±íÉè¼ÆÒ»¸öÃûΪstaff_updateµÄUPDATE´¥·¢Æ÷£¬µ±ÏµÍ³¶ÔÔ±¹¤±í½øÐÐUPDATE²Ù×÷ºó£¬½«»á×Ô¶¯´¥·¢Ëü¡£¸Ã´¥·¢Æ÷½«ÏàÓ¦¼Ç¼µÄ²¿ÃÅÈËÊý¡¢²¿ÃÅÃû³Æ¡¢¹¤×ʸüС£

ÏÂÃæÊÇclient_update´¥·¢Æ÷µÄ¶¨Òå´úÂ룻 CREATE TRIGGER staff_update ON Ô±¹¤ FOR UPDATE

AS SELECT COUNT(*)