第5章Orcal-数据库代码

¡¾Àý5.1¡¿Ê¹ÓÃDBCAÃüÁîÒÔsilent·½Ê½´´½¨Êý¾Ý¿âtem11g£ºdbca-silent-createDatabase-templateNameGeneral_Purpose.dbc-gdbnametem11g-sidtem11g-responseFileNO_VALUE¡¾Àý5.2¡¿ÔÚµ±Ç°µÄBookManage·½°¸Öд´½¨Ò»¸ö³ö°æÉç±í.CREATETABLEPress(PublishIDChar(8)PrimaryKey,PublishNameVarchar2(50),PublishAddressVarchar2(50),PublishPhoneNoVarchar2(15),PublishEmailVarchar2(30));¡¾Àý5.3¡¿»ùÓÚscott·½°¸ÖеÄemp±íµÄempno¡¢enameºÍjob×ֶδ´½¨Ò»ÐµĹÍÔ±±í£¬°üÀ¨±àºÅ¡¢ÐÕÃûºÍ¹¤×÷Èý¸ö×ֶΡ£¹ÍÔ±±íÖеıàºÅ¡¢ÐÕÃûºÍ¹¤×÷×ֶηֱðÀ´×ÔÓÚscott·½°¸ÖÐemp±íµÄempno¡¢enameºÍjob×ֶΡ£CREATETABLE¹ÍÔ±(±àºÅ,ÐÕÃû,¹¤×÷)ASSELECTempno,ename,jobFROMscott.emp;¡¾Àý5.4¡¿´´½¨Ðµijö°æÉç±íNewPress,±ÈÀý5.2Öд´½¨µÄPress¶àÒ»ÁÐPublishCredit£¬ÆäĬÈÏֵΪgood¡£CREATETABLENewPress(PublishIDChar(8)PrimaryKey,PublishNameVarchar2(50),PublishAddressVarchar2(50),PublishPhoneNoVarchar2(15),PublishEmailVarchar2(30),PublishCreditChar(20)default'good');¡¾Àý5.5¡¿Ö´ÐÐÏÂÁÐÓï¾ä²é¿´book±íµÄ½á¹¹¡£SQLDESCbook;¡¾Àý5.6¡¿ÏòPress±íÖÐÌí¼ÓÒ»ÁУºPublishCredit£¬Êý¾ÝÀàÐÍchar(20)£¬Ä¬ÈÏÖµgood¡£ALTERTABLEPressADDPublishCreditChar(20)default'good';¡¾Àý5.7¡¿É¾³ýPress±íÖеÄPublishCreditÁС£ALTERTABLEPressDROPCOLUMNPublishCredit¡¾Àý5.8¡¿É¾³ýnewpress±í¡£DROPTABLEnewpress;¡¾Àý5.9¡¿ÏòÒÑ´´½¨µÄ³ö°æÉçPress±íÖвåÈë±í5-10ËùʾµÄÊý¾Ý¡£SQLINSERTINTOPress(PublishID,PublishName,PublishAddress,PublishPhoneNo,PublishEmail)VALUES('2010001','Ç廪´óѧ³ö°æÉç','±±¾©','010-12345673','[e-mail]');SQLINSERTINTOPress(PublishID,PublishName,PublishAddress,PublishPhoneNo,PublishEmail)VALUES('2010002','ÖÐÔ­¹¤Ñ§Ôº³ö°æÉç','ºÓÄÏÖ£ÖÝ','0371-6666666','[e-mail]');SQLCOMMIT;SQLSELECT*FROMPress;¡¾Àý5.10¡¿½«³ö°æÉçPress±íÖС°ÖÐÔ­¹¤Ñ§Ôº³ö°æÉ硱µÄµç»°ºÅÂë¸ÄΪ¡°0371-99999999¡±£¬Ö´ÐÐÏÂÁÐÓï¾ä¡£UPDATEpressSETPublishPhoneNo='0371-99999999'WHEREPublishName='ÖÐÔ­¹¤Ñ§Ôº³ö°æÉç';COMMITWORK;¡¾Àý5.11¡¿É¾³ý³ö°æÉçPress±íÖгö°æÉçÃû³ÆΪ¡°Ç廪³ö°æÉ硱µÄ¼Ç¼£¬Ö´ÐÐÏÂÁÐÓï¾ä¡£DELETEPressWHEREPublishName='Ç廪³ö°æÉç';COMMITWORK;¡¾Àý5.12¡¿ÏÈɾ³ýÔ­À´´´½¨µÄ±íPress£¬ÖØж¨ÒåPress±í£¬Ê¹PublishNameÁв»×¼Îª¿Õ¡£DROPTABLEPress;CREATETABLEPress(PublishIDChar(8)PrimaryKey,PublishNameVarchar2(50)NOTNULL,PublishAddressVarchar2(50),PublishPhoneNoVarchar2(15),PublishEmailVarchar2(30));¡¾Àý5.13¡¿ÖØж¨ÒåPress±í£¬Ê¹PublishNameÁÐȡֵΨһ¡£CREATETABLEPress(PublishIDChar(8)PrimaryKey,PublishNameVarchar2(50)UNIQUE,PublishAddressVarchar2(50),PublishPhoneNoVarchar2(15),PublishEmailVarchar2(30));¡¾Àý5.14¡¿ÖØж¨ÒåPress±í£¬´øÓмì²éÔ¼ÊøÒÔ±£Ö¤ÓÊÏä¸ñʽÕýÈ·¡£CREATETABLEPress(PublishIDChar(8)PrimaryKey,PublishNameVarchar2(50)UNIQUE,PublishAddressVarchar2(50),PublishPhoneNoVarchar2(15),PublishEmailVarchar2(30)check£¨PublishEmaillike'%@%'£©)¡¾Àý5.15¡¿°´ÕÕ±í5-8µÄÉè¼Æ£¬´´½¨Book±í£¬ÆäÖеÄBookPublish²ÎÕÕ³ö°æÉç±íÖеÄPublishName¡£CREATETABLEBook(BookIDChar(10)PrimaryKey,BookNameVarchar2(30),BookWriterVarchar2(20),BookPublishVarchar2(50),BookPublishDateDate,BookPriceFloat,BookSortVarchar2(20),BookAmountInt,BookRemainInt,ForeignKey(BookPublish)referencesPress(PublishName));¡¾Àý5.16¡¿´´½¨ÊÓͼBookview£¬¿ÉÒԲ鿴ËùÓгö°æÉçÃû×ֺ͸óö°æÉç³ö°æµÄͼÊé×ÜÊý¡£CREATEVIEWBookview(BookPublish,BookNum)ASSELECTBookPublish,count(*)FROMBookGROUPBYBookPublish;¡¾Àý5.17¡¿Ê¹ÓÃÊÓͼBookview£¬²é¿´ËùÓгö°æÉçÃû×ֺ͸óö°æÉç³ö°æµÄͼÊé×ÜÊý¡£SQLSELECT*FROMBookview;¡¾Àý5.18¡¿ÐÞ¸ÄÊÓͼBookview£¬Ê¹Æä¿ÉÒԲ鿴ËùÓÐÊéµÄÊéÃû£¬×÷Õߺͳö°æÉçÐÅÏ¢¡£SQLCREATEORREPLACEVIEWBookview(BookName,BookWriter,BookPublish)ASSELECTBookName,BookWriter,BookPublishFROMBook;¡¾Àý5.19¡¿É¾³ýÊÓͼBookview¡£SQLDROPVIEWBookview;¡¾Àý5.20¡¿ÔÚBook±íBookNameÁÐÉÏ´´½¨Ë÷ÒýBookindex1¡£SQLCREATEINDEXBookindex1ONBook(BookName);¡¾Àý5.21¡¿ÔÚBook±íBookWriterÁÐÉÏ´´½¨Î¨Ò»Ë÷ÒýBookindex2¡£SQLCREATEUNIQUEINDEXBookindex2ONBook(BookWriter);¡¾Àý5.22¡¿ÐÞ¸ÄÔÚBook±íBookNameÁÐÉϵÄË÷ÒýÃûBookindex1¸ÄΪBookindex3¡£SQLALTERINDEXBookindex1renametoBookindex3;¡¾Àý5.23¡¿É¾³ýÔÚBook±íÉϵÄË÷ÒýBookindex2¡£DROPINDEXBookindex2;