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(*)