ʵÑé8 Êý¾Ý¿âÍêÕûÐÔ ÏÂÔØ±¾ÎÄ

ʵÑé8 Êý¾Ý¿âÍêÕûÐÔ

¶þ¡¢±³¾°ÖªÊ¶

£¨1£© Êý¾Ý¿âÍêÕûÐÔ¸ÅÄ

Êý¾Ý¿âÍêÕûÐÔ¾ÍÊÇÒª·ÀÖ¹Êý¾Ý¿âÖдæÔÚ²»·ûºÏÓïÒåµÄÊý¾Ý£¬·ÀÖ¹Êý¾Ý¿âÖдæÔÚ²»ÕýÈ·µÄÊý¾Ý¡£ÎªÁ˱£Ö¤Êý¾Ý¿âµÄÍêÕûÐÔ£¬DBMSÌṩÁ˶¨Òå¡¢¼ì²éºÍ¿ØÖÆÊý¾ÝÍêÕûÐԵĻúÖÆ£¬²¢ÄܰÑÓû§¶¨ÒåµÄÊý¾Ý¿âÍêÕûÐÔÔ¼ÊøÌõ¼þ×÷ΪģʽµÄÒ»²¿·Ö´æÈëÊý¾Ý¿âÖС£ËüʹÓÃÔ¼Êø¡¢Ä¬ÈÏ¡¢¹æÔòºÍ´¥·¢Æ÷4ÖÖ·½·¨¶¨ÒåºÍʵʩÊý¾Ý¿âÍêÕûÐÔ¹¦ÄÜ¡£ £¨1£©

Êý¾ÝÍêÕûÐÔÔ¼ÊøµÄ·ÖÀࣺ Êý¾ÝÍêÕûÐÔÔ¼Êø¿ÉÒÔ·ÖΪ3Àࣺ

¡ñ±í¼¶Ô¼Êø£ºÈô¸ÉÔª×é¼äÒÔ¼°¹ØÏµÖ®¼äÁªÏµµÄÊý¾ÝÔ¼Êø¡£ÀýÈ磺ѡ¿Î±íÖУ¬Ã¿¸öÈË×î¶àÄÜÑ¡10ÃſΣ»Ñ§Éú±íÖУ¬Ñ§ÉúµÄѧºÅ±ØÐëΨһ£»Ñ¡¿Î±íÖеÄѧºÅºÍ¿Î³ÌºÅ±ØÐëÔÚѧÉú±íºÍ¿Î³Ì±íÖдæÔÚ¡£

¡ñÔª×é¼¶Ô¼Êø£ºÍ¬Ò»¸öÔª×é×Ö¶ÎÖ®¼ä±ØÐëÂú×ãµÄÔ¼ÊøÌõ¼þ¡£ÈçѧÉú±íÖÐÄêÁä×ֶεÄÖµÓ¦¸ÃµÈÓÚµ±Ç°ÈÕÆÚ¼õÈ¥³öÉúÈÕÆÚ¡£Ñ§ÉúѧºÅµÄµÚ5λ±íʾµÄÊÇѧԺ´úºÅ£¬Èç¹ûÆä´úºÅΪ1£¬ÔòѧԺ±àºÅÖ»ÄÜΪ0001£»Èç¹ûÆä´úºÅΪ2£¬ÔòѧԺ±àºÅÖ»ÄÜΪ0002µÈ¡£

¡ñ

ÊôÐÔ¼¶Ô¼Êø£ºÕë¶ÔÁеÄÀàÐÍ¡¢È¡Öµ·¶Î§¡¢¾«¶È¡¢ÅÅÐòµÈ¶øÖƶ¨µÄÔ¼ÊøÌõ¼þ¡£ÀýÈ磺ÐÔ±ðÖ»ÄÜÊÇ¡®ÄС¯»ò¡®Å®¡¯£»¿Î³Ì³É¼¨±ØÐëÔÚ0-100·ÖÖ®¼ä¡£

£¨3£©Ô¼ÊøµÄÖÖÀ༰Æäº¬Ò壺

Ô¼ÊøÀàÐÍ ÍêÕûÐÔ¹¦ÄÜÃèÊö Ö¸¶¨Ö÷Â룬ȷ±£Ö÷ÂëÖµ²»Öظ´£¬²¢²»ÔÊPRIMARY KEY ÐíÖ÷ÂëÄÚµÄÈκÎÁÐÖÐÊäÈëNULL£¬Ò»¸ö±íÖ»ÄÜÓÐÒ»¸öPRIMARY KEYÔ¼Êø Ö÷ÒªÓÃÓÚÔÚ²»Í¬µÄ±íÖ®¼ä½¨Á¢Ô¼Êø£¬ÒÔFOREIGN KEY ʵÏÖ²»Í¬±íÖ®¼äµÄ²ÎÕÕÍêÕûÐÔ¡£FOREIGN KEYÔ¼ÊøµÄÁлòÕßΪNULL£¬»òÕßΪ±»²ÎÕÕÁеÄÖµ¡£ UNIQUE ΨһÐÔÔ¼Êø£¬¶Ô±íÖеÄÁм¯½øÐÐÔ¼Êø£¬ÆäÖµ»òÕßΪ¿Õ£¬»òÕßÔÚÕû¸ö±íÖÐÊÇΨһµÄ Ö¸¶¨Ä³¸öÁлòÁÐ×é¿ÉÒÔ½ÓÊÜÖµµÄ·¶Î§£¬CHECK »òÖ¸¶¨Êý¾ÝÓ¦Âú×ãµÄÌõ¼þ£¬À´Ç¿ÖÆÊµÏÖÓû§ÒªÇóµÄÓòÍêÕûÐÔ¡£¿ÉÒÔΪÿÁÐÉèÖöà¸öCHECK KEYÔ¼Êø NOT NULL DEFAULT ·Ç¿ÕÔ¼Êø£¬Ö¸¶¨Ä³¸öÁв»ÄÜΪNULL ²åÈëÊý¾Ýʱ£¬Èç¹ûûÓÐÃ÷È·ÌṩÁÐÖµ£¬ÔòÓÃȱʡֵ×÷Ϊ¸ÃÁеÄÖµ £¨4£©SQLServerÌṩÁË2ÖÖ·½·¨£¬ÊµÏÖÊý¾Ý¿âÍêÕûÐÔ¿ØÖÆ¡£ ¡ñÉùÃ÷Êý¾ÝÍêÕûÐÔ£ºÍ¨¹ýÔÚ¶ÔÏó¶¨ÒåÖж¨Ò塢ϵͳ±¾Éí×Ô¶¯Ç¿ÖÆÊµÏÖ¡£ÉùÃ÷Êý¾ÝÍêÕûÐÔ

°üÀ¨¸÷ÖÖÔ¼Êø¡¢È±Ê¡ºÍ¹æÔò¡£ ¡ñ

¹ý³ÌÊý¾ÝÍêÕûÐÔ£º¹ý³ÌÊý¾ÝÍêÕûÐÔͨ¹ýʹÓÃǰ̨ÓïÑÔ»òTransact-SQL(´¥·¢Æ÷ºÍ´æ´¢¹ý³Ì)£¬Î¬»¤±í¼¶Êý¾ÝÔ¼ÊøºÍÔª×é¼¶Êý¾ÝÔ¼Êø£¬ÊµÏÖÐм¶Êý¾ÝµÄÍêÕûÐÔ£¬Ö÷ÒªÓÃÓÚʵÏÖÒµÎñÂß¼­Ô¼Êø)¡£

£¨5£©SQLServerµÄÊý¾ÝÍêÕûÐÔµÄÖÖÀࣺ

SQLServerµÄÊý¾ÝÍêÕûÐÔ°üÀ¨ÊµÌåÍêÕûÐÔ¡¢ÓòÍêÕûÐԺͲÎÕÕÍêÕûÐÔ3ÖÖ£¬Æä¶¨ÒåÈçÏ£º ¡ñʵÌåÍêÕûÐÔÊÇ±í¼¶ÍêÕûÐÔ£¬ÊµÌåÍêÕûÐÔͨ¹ýË÷Òý¡¢UNIQUEÔ¼Êø¡¢PRIMARY KEYÔ¼ÊøÊµÏÖ¡£

¡ñÓòÍêÕûÐÔ·ÖΪÁм¶ºÍÔª×é¼¶ÍêÕûÐÔ¡£ÓòÍêÕûÐÔͨ¹ýFOREIGN KEY¡¢CHECKÔ¼Êø¡¢DEFAULTÔ¼Êø¡¢NOT NULL¡¢¹æÔòºÍ´¥·¢Æ÷µÈʵÏÖ¡£

¡ñ²ÎÕÕÍêÕûÐÔÊÇ±í¼¶ÍêÕûÐÔ¡£²ÎÕÕÍêÕûÐÔͨ¹ýFOREIGN KEYºÍ´¥·¢Æ÷µÈʵÏÖ¡£ £¨6£©¹æÔòµÄ¸ÅÄî

¹æÔòÓëCHECK KEYÔ¼Êø¾ßÓÐÏàͬµÄ¹¦ÄÜ£¬¶Ô±íÖеÄÁнøÐÐÔ¼Êø¼ì²é¡£Ëü¿ÉÒÔʹÓöàÖÖ·½Ê½À´Íê³É¶ÔÊäÈëÊý¾ÝµÄ¼ìÑ飬¿ÉÒÔʹÓú¯Êý·µ»ØÖµ½øÐÐÑéÖ¤£¬Ò²¿ÉÒÔʹÓùؼü×ÖBETWEEN¡¢LIKEºÍINÍê³É¶ÔÊäÈëÊý¾ÝµÄ¼ì²é¡£

µ±½«¹æÔò°ó¶¨µ½ÁлòÕßÓû§¶¨ÒåµÄÊý¾ÝÀàÐÍʱ£¬¹æÔò½«Ö¸¶¨¿ÉÒÔ²åÈëµ½ÁÐÖеĿɽÓÊܵÄÖµ¡£¹æÔò×÷ΪÊý¾Ý¿âµÄÒ»¸ö¶ÀÁ¢¶ÔÏó´æÔÚ£¬±íÖÐÿÁлòÕßÿ¸öÓû§¶¨ÒåµÄÊý¾ÝÀàÐÍÖ»ÄܺÍÒ»¸ö¹æÔò°ó¶¨¡£°ó¶¨¹æÔò¼È¿ÉÒÔͨ¹ýÆóÒµ¹ÜÀíÆ÷ʵÏÖ£¬Ò²¿ÉÒÔͨ¹ýsp_bINdrule´æ´¢¹ý³ÌʵÏÖ¡£

´´½¨¹æÔòÓï·¨¸ñʽ£º

CREATE RULE rulename AS condition_expression ÆäÖи÷²ÎÊýº¬ÒåÈçÏ£º rulename£º¹æÔòµÄÃû³Æ

condition_expression£º¹æÔòµÄÌõ¼þ£¬¿ÉÒÔÊÇSQLÓï¾äÖÐWHERE×Ó¾äÖÐÈκÎÓÐЧµÄ±í´ïʽ¡£

£¨µ÷ÊÔÊÇ·ñ¿ÉÒÔ°üº¬º¯Êý£©

ËÄ¡¢ÊµÑé²½Öè

1£®ÔÚXSGLÊý¾Ý¿âÖУ¬´´½¨Ò»¸öÃûΪyanshi_studentµÄ±í£¬Ö¸¶¨SNOΪÖ÷Â룬ÇÒÉèÖÃÊôÐÔ¼¶Ô¼Êø¡¢Ôª×é¼¶Ô¼ÊøºÍ±í¼¶Ô¼Êø¡£

CREATE TABLE yanshi_student ( SNO char (8) ,

SNAME char (10) NOT NULL , AGE smallInt NULL,

SEX char (2) NULL DEFAULT 'ÄÐ' /*SEXȱʡֵΪÄÐ */ , DNO char (4) NOT NULL , BIRTHDAY datetime NULL ,

PRIMARY KEY (Sno) /*ÔÚ±í¼¶¶¨ÒåÖ÷Â룬ʵÏÖʵÌåÍêÕûÐÔ*/,

CHECK(SEX IN ('ÄÐ','Å®')) /*ÐÔ±ðÊôÐÔSEXÖ»ÔÊÐíÈ¡'ÄÐ'»ò'Å®' */ , CHECK(SNO LIKE '[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

/*SNOÖ»ÄÜΪ8λÊý×Ö£¬ÇÒ²»ÄÜÒÔ0¿ªÍ·*/ ,

CHECK((SUBSTRING(SNO,5,1)='3' AND DNO='0003')

OR(SUBSTRING(SNO,5,1) ='2' AND DNO='0002') OR (SUBSTRING(SNO,5,1) = '1' AND DNO='0001'))

/*¶¨ÒåÁËÔª×éÖÐSNOºÍDNOÁ½¸öÊôÐÔÖµÖ®¼äµÄÔ¼ÊøÌõ¼þ£¬SNOµÄµÚ5λֻÄÜΪ1£¬2£¬3£¬ÇÒµ±µÚ5λΪ1ʱ£¬DNOÖ»ÄÜΪ0001£¬µÚ5λΪ2ʱ£¬DNOÖ»ÄÜΪ0002£¬µÚ5λΪ3ʱ£¬DNOÖ»ÄÜΪ0003*/

)

ÇëͬѧÃÇÓÃÏÂÁÐSQLÓï¾äµ÷ÊÔ£¬²¢¹Û²ìÆäÏÖÏó£º

£¨1£© INSERT yanshi_student(SNO,SNAME,DNO) values('x0001','li','0001') £¨2£© INSERT yanshi_student(SNO,SNAME,DNO) values('00001','li','0001') £¨3£© INSERT yanshi_student(SNO,SNAME,DNO) values('10001','li','0001') £¨4£© INSERT yanshi_student(SNO,SNAME,DNO) values('20073001','li','0001') £¨5£© INSERT yanshi_student(SNO,SNAME,DNO) values('20073001','li','0003') 2£®ÔÚXSGLÊý¾Ý¿âÖУ¬´´½¨Ò»¸öÃûΪyanshi_course µÄ±í£¬Ö¸¶¨CNOΪÖ÷Âë¡£

CREATE TABLE yanshi_course

(

CNO char(6) ,

CNAME char(30) NOT NULL , TNAME char(10) NULL , CREDIT float NULL , ROOM char(30) NULL , PRIMARY KEY (CNO) )

ÇëͬѧÃÇÓÃÏÂÁÐSQLÓï¾äµ÷ÊÔ£¬²¢¹Û²ìÆäÏÖÏó£º

£¨1£© £¨2£© »§½çÃæ')

£¨3£©

INSERT yanshi_course(CNO,CNAME) values('133804','¹¤³ÌѵÁ·') INSERT yanshi_course(CNO,CNAME) values('222201','Èí¼þ¼¼Êõ»ù´¡') INSERT yanshi_course(CNO,CNAME) values('c43002','¿ÉÊÓ»¯³ÌÐòÉè¼ÆÓëÓÃ

3£®ÔÚXSGLÊý¾Ý¿âÖУ¬´´½¨Ò»¸öÃûΪyanshi_scµÄ±í£¬Ö¸¶¨SNO¡¢CNOΪÖ÷Â룬²¢ÇÒSNO²ÎÕÕyanshi_student±íÖеÄSNO£¬CNO²ÎÕÕyanshi_course±íÖеÄCNO¡£ CREATE TABLE yanshi_sc

(

SNO char(8) , CNO char(6) , GRADE float NULL, PRIMARY KEY(SNO,CNO),

FOREIGN KEY(SNO) references yanshi_student(SNO), FOREIGN KEY(CNO) references yanshi_course(CNO), CHECK (GRADE BETWEEN 0 AND 100),

)

ÇëͬѧÃÇÓÃÏÂÁÐSQLÓï¾äµ÷ÊÔ£¬²¢¹Û²ìÆäÏÖÏó£º

£¨1£©INSERT yanshi_sc(SNO,CNO,GRADE) VALUES('20073001','222201',81) £¨2£©INSERT yanshi_sc(SNO,CNO,GRADE) VALUES ('20073001','133804',67) £¨3£©INSERT yanshi_sc(SNO,CNO,GRADE) VALUES('20073001','222203',70) 4£®ÔÚXSGLÊý¾Ý¿âÖУ¬´´½¨Ò»¸ö¹æÔò£¬ÏÞ¶¨°ó¶¨ÁеÄÊäÈëÖµÔÚ0.5ºÍ5Ö®¼ä£¬²¢Ê¹Óô洢¹ý³Ìsp_bINdrule½«Æä°ó¶¨µ½yanshi_course±íÖеÄCREDITÁÐÖУ¬ÏÞ¶¨Ñ§·ÖÖ»ÄÜÔÚ0.5ºÍ5Ö®¼ä¡£

CREATE RULE rule4 AS @c1 BETWEEN 0.5 AND 5 EXEC sp_bindrule 'rule4','yanshi_course.CREDIT' ÇëͬѧÃÇÓÃÏÂÁÐSQLÓï¾äµ÷ÊÔ£¬²¢¹Û²ìÆäÏÖÏó£º

£¨1£©INSERT yanshi_course(CNO,CNAME,CREDIT) values('133805','¹¤³ÌѵÁ·',6) £¨2£©INSERT yanshi_course(CNO,CNAME,CREDIT) values('133805','¹¤³ÌѵÁ·',2) 5£®Ê¹ÓÃÆóÒµ¹ÜÀíÆ÷´´½¨¹æÔò£¬²¢½«Æä°ó¶¨µ½yanshi_course±íÖеÄCNOÁÐÉÏ¡£

´´½¨¹æÔò²Ù×÷²½ÖèÈçÏ£º £¨1£© £¨2£© Ôò¡±ÃüÁî¡£

£¨3£© £¨4£©

´Ëʱ»á´ò¿ªÐ½¨¹æÔò¶ÔÏó¶Ô»°¿ò£¬ÔÚÊäÈë¿òÖÐÊäÈëÊý¾Ý£¬Èçͼ8-1Ëùʾ¡£ µ¥»÷¡°È·ÈÏ¡±°´Å¥£¬¼´¿É´´½¨ÃûΪrule3µÄ¹æÔò¡£ ´ò¿ªÆóÒµ¹ÜÀíÆ÷£¬Õ¹¿ª·þÎñÆ÷×飬²¢Õ¹¿ªÏàÓ¦µÄ·þÎñÆ÷¡£

´ò¿ªXSGLÊý¾Ý¿â£¬Ñ¡Ôñ¡°¹æÔò¡±Îļþ¼Ð£¬È»ºóÓÒ»÷Êó±ê£¬Ö´ÐС°Ð½¨¹æ

ͼ8-1 ¹æÔòÊôÐÔ¶Ô»°¿ò ͼ 8-2 ¹æÔò°ó¶¨¶Ô»°¿ò

°ó¶¨¹æÔò²Ù×÷²½ÖèÈçÏ£º £¨1£© £¨2£© £¨3£©

´ò¿ªÆóÒµ¹ÜÀíÆ÷£¬Õ¹¿ª·þÎñÆ÷×飬²¢Õ¹¿ªÏàÓ¦µÄ·þÎñÆ÷¡£

´ò¿ªXSGLÊý¾Ý¿â£¬Ñ¡Ôñ¡°¹æÔò¡±Îļþ¼Ð£¬È»ºóÔÚÓÒ²àÏêϸÐÅÏ¢´°¸ñÖÐÑ¡ÔñÒªÓÒ»÷Êó±ê,Ö´ÐС°ÊôÐÔ¡±ÃüÁ´ò¿ª¡°¹æÔòÊôÐÔ¡±¶Ô»°¿ò£¬µ¥»÷¡°°ó¶¨ÁС±°´

°ó¶¨µÄ¹æÔò¶ÔÏórule3¡£

Å¥£¬´ò¿ª¡°½«¹æÔò°ó¶¨µ½ÁС±¶Ô»°¿ò£¬Èçͼ8-2Ëùʾ¡£ÔÚ¡°±í¡±ÏÂÀ­Áбí¿òÖÐÑ¡ÔñÁÐËùÔڵıíyanshi_course,È»ºóÔÚ¡°Î´°ó¶¨µÄÁС±Áбí¿òÖÐÑ¡ÔñÒª°ó¶¨µÄÁÐCNO£¬ÔÙµ¥»÷¡°Ìí¼Ó¡±°´Å¥£¬½«ÆäÌí¼Óµ½¡°°ó¶¨ÁС±Áбí¿òÖС£

£¨4£© ÁÐÉÏ¡£

µ¥»÷¡°È·ÈÏ¡±°´Å¥£¬¼´¿É½«rule3¹æÔò¶ÔÏó°ó¶¨µ½yanshi_course±íµÄCNO

ÇëͬѧÃÇÓÃÏÂÁÐSQLÓï¾äµ÷ÊÔ£¬²¢¹Û²ìÆäÏÖÏó£º

£¨1£©INSERT yanshi_course(CNO,CNAME) values('304516','Ó¢Óï¿ÚÓï') £¨2£©INSERT yanshi_course(CNO,CNAME) values('c04526','Ó¢Óï¿ÚÓï') 6£®½â³ýrule3¹æÔòµ½yanshi_course.CNOµÄ°ó¶¨£¬²¢É¾³ý¸Ã¹æÔò¡£

£¨1£©EXEC sp_unbindrule 'yanshi_course.CNO' £¨2£©DROP RULE rule3

˼¿¼Ìâ

£¨1£© ´´½¨Ò»¸ö±íyanshi_depart£¬°üº¬DNO¡¢DNAME¡¢DADDRESSºÍDEANËĸö×ֶΣ¬DNOÓÉ4¸öÊý×Ö×Ö·û¹¹³É£¬ÇÒΪ¸Ã±íµÄÖ÷Â룬DNAMEÓÉ30¸ö×Ö·û¹¹³É£¬DADDRESSÓÉ5¸ö×Ö·û¹¹³É£¬DEANÓÉ10¸ö×Ö·û¹¹³É¡£

£¨2£© ΪÀý1ÖеÄyanshi_student±í½¨Á¢Íâ¼ü¡°DNO¡±£¬²Î¿¼±íyanshi_departµÄ¡°DNO¡±ÁС£

£¨3£© Ϊ±íyanshi_departµÄDNAME½¨Á¢Ò»¸ö¹æÔò£º@dname IN ('»úµçѧԺ','ÐÅϢѧԺ','¹¤ÉÌѧԺ')¡£

£¨4£© Ϊ±íyanshi_departµÄDADDRESS½¨Á¢Ò»¸ö¹æÔò£º@daddress LIKE '[A-Z][1-9][1-9][1-9][1-9]'£¬ÏÞ¶¨DADDRESSµÄÖµÖ»ÄÜÓÉ×Öĸ¿ªÍ·£¬ºó¸ú4¸öÊý×Ö¡£ £¨5£© ɾ³ýµÚ3СÌâËù½¨Á¢µÄ¹æÔò¡£