INNER JOIN region ON belong.addrid = region.id INNER JOIN majorinfo ON request.majorid = majorinfo.id
INNER JOIN company ON belong.comid = company.id AND request.comid = company.id;
4.4 Ô¼ÊøÉè¼Æ
alter table belong add constraint FK_Reference_4 foreign key (id) references region (id) on delete restrict on update restrict; alter table belong add constraint FK_Reference_5 foreign key (com_id) references company (id) on delete restrict on update restrict; alter table request add constraint request_ibfk_1 foreign key (comid) references company (id);
alter table request add constraint request_ibfk_2 foreign key (majorid) references major (id);
4.5 ´æ´¢¹ý³ÌÉè¼Æ
DELIMITER $$ CREATE
/*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE
`colstuvoccounsys`.`request_major`(IN
majorname
VARCHAR(20),OUT count_num INT) /*LANGUAGE SQL | [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ READS SQL DATA BEGIN
13
SELECT COUNT(*) FROM request WHERE major=majorname; END$$
DELIMITER ;
DELIMITER $$ CREATE
/*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE
`colstuvoccounsys`.`company_size`(IN
companysize
VARCHAR(10),OUT count_num INT) /*LANGUAGE SQL | [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ READS SQL DATA BEGIN
SELECT COUNT(*) FROM company WHERE size=companysize; END$$
DELIMITER ;
4.6 º¯ÊýÉè¼Æ
DELIMITER $$ CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `colstuvoccounsys`.`funk_company`(companysize VARCHAR(20))
14
RETURNS VARCHAR(40) /*LANGUAGE SQL | [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ BEGIN
RETURN (SELECT *FROM company GROUP BY size HAVING size=companysize); END$$
DELIMITER ;
DELIMITER $$ CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `colstuvoccounsys`.`funk_request`(companymajor VARCHAR(20)) RETURNS VARCHAR(40) /*LANGUAGE SQL | [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ BEGIN
RETURN (SELECT * FROM request GROUP BY major HAVING major=companymajor); END$$
DELIMITER ;
15
4.7 ´¥·¢Æ÷Éè¼Æ
/*company±í´¥·¢Æ÷*/ CREATE TRIGGER companyid AFTER UPDATE ON company FOR EACH ROW UPDATE request SET comid=new.id WHERE comid =old.id;
16
µÚÎåÕ ¸öÈË×ܽá
¿Î³ÌÉè¼ÆÊÇÅàÑøÑ§Éú×ÛºÏÔËÓÃËùѧ֪ʶ£¬·¢ÏÖ£¬Ìá³ö£¬·ÖÎöºÍ½â¾öʵ¼ÊÎÊÌ⣬¶ÍÁ¶Êµ¼ùÄÜÁ¦µÄÖØÒª»·½Ú£¬ÊǶÔѧÉúʵ¼Ê¹¤×÷ÄÜÁ¦µÄ¾ßÌåѵÁ·ºÍ¿¼²ì¹ý³Ì¡£Í¨¹ýÕâ´Î¿Î³ÌÉè¼ÆÔö¼ÓºÜ¶àÈÏʶ£¬ÔÚÄõ½¿Î³ÌÉè¼ÆÌâĿʱ£¬Ã»ÓÐÏñÒÔǰһÑùÂíÉÏ¿ªÊ¼¶¯ÊÖд´úÂ룬ÎÒÏÈ×öÁËÕûÌåµÄ·ÖÎö£¬°ÑÒªÉè¼ÆµÄ±í£¬±íµÄÊôÐÔ£¬±í¼äµÄ¹ØÏµÈ«²¿ÅªÇå³þ£¬²¢»ÁËÏàÓ¦µÄ¹ØÏµÍ¼ºÍE-Rͼ£¬´Ó¶øÒ²¾Í»ù±¾ÐγÉÁËÕâ´ÎÉè¼ÆµÄÕûÌå¹¹¼Ü¡£ÔÚд´úÂëµÄ¹ý³ÌÖÐÃ÷ÏԸоõµ½ºÜÇáËÉ£¬¾ÍÊÇÕÕ×ÅËùÉè¼ÆµÄ˼·һ²½Ò»²½µØ½øÐС£²¢µÃµ½ÒÔÏÂ×ܽ᣺Ê×ÏÈ£¬Á÷³ÌÊÇÒ»ÇеĸùÔ´£¬½ø¶È¿ØÖÆÊÇÏîĿ˳Àû½øÐеĻù´¡¡£Ã»Óдó¾Ö¹Û£¬Ãæ¶ÔÎÊÌâºÍ±ä¸ü¾Í»á²»ÖªËù´ëÁË¡£Æä´Î£¬¹¹¼ÜÊǺÜÖØÒªµÄ£¬ÕûÌå¿ò¼Ü´îºÃ£¬¾ßÌåϸ½ÚʵÏÖÆðÀ´¾ÍÊÇ˳Àí³ÉÕµÄÊÂÁË¡£×îºó£¬Êµ¼ù±ÈÒ»ÇпÕ̸ºÍÀíÂÛ¸üÄÜѧµ½¶«Î÷¡£Æ½Ê±Ñ§µÄÄÇЩÀíÂÛ¶¼²»¹ýÊÇÆ¤Ã«°ÕÁË£¬Ò»µ©²»Óúܿì¾ÍÍü¼Ç£¬Ê¼ÖÕÒªÔÚʵ¼ùÖвŻᷢÏÖÎÊÌâ²Å»áŬÁ¦È¥½â¾ö²ÅÄܳɳ¤¡£¿´µ½ÌâÄ¿ºó¾ÍÒ»Ö±ÔÚ˼¿¼ÔõôÉè¼ÆºÃÕâ¸öÊý¾Ý¿â£¬´ÓÀíÂÛµ½Êµ¼ù£¬ÔÚÕâЩÈÕ×ÓÀ¿ÉÒÔ˵µÃÊÇ¿à¶àÓÚÌ𣬵«ÊÇ¿ÉÒÔѧµ½ºÜ¶àºÜ¶àµÄ¶«Î÷£¬Í¬Ê±²»½ö¿ÉÒÔ¹®¹ÌÁËÒÔǰËùѧ¹ýµÄ֪ʶ£¬¶øÇÒѧµ½Á˺ܶàÔÚÊé±¾ÉÏËùûÓÐѧµ½¹ýµÄ֪ʶ¡£Í¨¹ýÕâ´Î¿Î³ÌÉè¼ÆÊ¹ÎÒ¶®µÃÁËÀíÂÛÓëʵ¼ÊÏà½áºÏÊǺÜÖØÒªµÄ£¬Ö»ÓÐÀíÂÛ֪ʶÊÇÔ¶Ô¶²»¹»µÄ£¬Ö»ÓаÑËùѧµÄÀíÂÛ֪ʶÓëʵ¼ùÏà½áºÏÆðÀ´£¬´ÓÀíÂÛÖеóö½áÂÛ£¬²ÅÄÜÕæÕýΪÉç»á·þÎñ£¬´Ó¶øÌá¸ß×Ô¼ºµÄʵ¼Ê¶¯ÊÖÄÜÁ¦ºÍ¶ÀÁ¢Ë¼¿¼µÄÄÜÁ¦¡£ÔÚÉè¼ÆµÄ¹ý³ÌÖÐÓöµ½ÎÊÌ⣬¿ÉÒÔ˵µÃÊÇÀ§ÄÑÖØÖØ£¬±Ï¾¹ÊǵÚÒ»´Î×öÕâÑùµÄÉè¼Æ£¬ÄÑÃâ»áÓöµ½¹ý¸÷ÖÖ¸÷ÑùµÄÎÊÌ⣬ͬʱÔÚÉè¼ÆµÄ¹ý³ÌÖз¢ÏÖÁË×Ô¼ºµÄ²»×ãÖ®´¦£¬¶ÔÒÔǰËùѧ¹ýµÄ֪ʶÀí½âµÃ²»¹»Éî¿Ì£¬ÕÆÎյò»¹»Àι̣¬ÀýÈçÔÚÉè¼ÆÕûÌå¼Ü¹¹Éè¼Æ¸÷±íÖ®¼äµÄ¹ØÏµÊ±£¬×ÜÊÇ¿¼ÂDz»¹»Öܵ½£¬Êý¾ÝµÄÍêÕûÐÔºÍÒ»ÖÂÐÔ°ÑÎÕ²»×¼¡£ÀíÂÛ²»¹»¹æ·¶»¯£¬¹ØÏµÄ£Ê½²»¹»ÓÅÔ½¡£ÎªÁËʵÏÖÕâЩ£¬ÎÒÒ²²ÎÔÄÁËºÜ¶à¹ØÓÚÊý¾Ý¿âÉè¼Æ·½ÃæµÄÊ飬´ÓÖÐÊÜÒæ·Ëdz¡£ÔÚÕâ´Î¿Î³ÌÉè¼Æ¹ý³ÌÖУ¬Ò²ÓкܶàûÓÐÏëµ½µÄµØ·½£¬Í¨¹ýÉÏÍø²éÕÒ×ÊÁÏ£¬ÍêÉÆÁ˲»ÉٵŦÄÜ¡£ÔÚÕâ´Î¿Î³ÌÉè¼Æ¹ý³ÌÖУ¬ÎÒÉî¿Ì¸Ð¾õµ½×Ô¼ºÖªÊ¶µÄȱ·¦£¬»¹ÐèÒª¿ÎÍâ¼ÌÐøÑ§Ï°£¬²»¶ÏÍêÉÆºÍÔö¼Ó×ÔÉíµÄ֪ʶºÍ¾Ñé¡£×îºó£¬¸ÐлÀÏʦµÄÖ¸µ¼£¬ÈÃÎÒ˳ÀûµÄÍê³ÉÁ˱¾´ÎµÄÊý¾Ý¿â¿Î³ÌÉè¼Æ¡£
17