第3章 表的操作和數(shù)據(jù)的完整性約束

第3章表的操作和數(shù)據(jù)的完整性約束《MySQL數(shù)據(jù)庫應(yīng)用教程》劉瑞新主編配套資源目錄第3章表的操作和數(shù)據(jù)的完整性約束3.1表的概述3.2數(shù)據(jù)類型3.3表的操作3.4數(shù)據(jù)的完整性約束3.1.1表的基本概念1）表名。2）列名。3）行或記錄。4）列或?qū)傩浴?）數(shù)據(jù)項。6）數(shù)據(jù)的完整性約束。3.1表的概述3.1表的概述3.1.2數(shù)據(jù)類型概述3.2數(shù)據(jù)類型2.浮點數(shù)類型和定點數(shù)類型及其取值范圍3.2數(shù)據(jù)類型3.2.2字符串類型3.2數(shù)據(jù)類型3.2.3日期和時間類型3.2數(shù)據(jù)類型3.2.4二進制類型3.2.5復(fù)合類型1.SET（集合）類型列名SET('值1','值2',…,'值n')2.ENUM（枚舉）類型列名ENUM('值1','值2',…,'值n')3.3表的操作3.3.1創(chuàng)建表1.使用SQL語句創(chuàng)建表CREATE[TEMPORARY]TABLE[db_name.]tb_name(column_definition1[列級完整性約束條件1,][column_definition2[列級完整性約束條件2],][…,][column_definitionN[列級完整性約束條件N],][表級完整性約束條件])[table_option];3.3表的操作列的定義格式如下：column_namedata_type[DEFAULTdefault_value][AUTO_INCREMENT][COMMENT'String']…table_option格式如下：[ENGINE=engine_name][DEFAULTCHARSET=characterset_name][COLLATE=collation_name]3.3表的操作【例3-1】在學(xué)生信息數(shù)據(jù)庫studentinfo中，創(chuàng)建學(xué)生表student，student表的定義見表3-6。要求對表使用InnoDB存儲引擎，設(shè)置該表的字符集為utf8，其對應(yīng)校對規(guī)則是utf8_bin。3.3表的操作USEstudentinfo;CREATETABLEstudent(StudentIDCHAR(12)COMMENT'學(xué)號',StudentNameVARCHAR(20)COMMENT'姓名',SexENUM('男','女')DEFAULT'男',BirthdayDATECOMMENT'出生日期',AddressVARCHAR(30),ClassIDCHAR(10)COMMENT'班級編號')ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;3.3表的操作3.3表的操作【例3-2】創(chuàng)建臨時表temp_table。SQL語句如下：CREATETEMPORARYTABLEtemp_table(IDINTNOTNULL,NameVARCHAR(10),ValueINTNOTNULL);3.3表的操作2.使用NavicatforMySQL對話方式創(chuàng)建表【例3-3】在studentinfo數(shù)據(jù)庫中，創(chuàng)建班級表class，該表的定義見表3-7。3.3表的操作3.3表的操作3.3表的操作3.3表的操作3.3.2查看表1.查看表的名稱SHOWTABLES[{FROM|IN}db_name];【例3-4】在數(shù)據(jù)庫studentinfo中，查看所有的表名。USEstudentinfo;SHOWTABLES;3.3表的操作2.查看表的基本結(jié)構(gòu)SHOWCOLUMNS語句的語法格式為：SHOWCOLUMNS{FROM|IN}tb_name[{FROM|IN}db_name];DESCRIBE/DESC語句的語法格式為：{DESCRIBE|DESC}tb_name;【例3-5】在數(shù)據(jù)庫studentinfo中，查看表student的結(jié)構(gòu)。SHOWCOLUMNSFROMstudent;或DESCstudent;3.3表的操作3.3表的操作3.3表的操作3.查看表的詳細結(jié)構(gòu)SHOWCREATETABLEtb_name;【例3-6】在數(shù)據(jù)庫studentinfo中，查看表class的詳細信息。USEstudentinfo;SHOWCREATETABLEclass\G;3.3表的操作3.3表的操作3.3.3修改表1.使用SQL語句修改表結(jié)構(gòu)ALTERTABLEtb_nameADD[COLUMN]new_col_nametype[constraint_condition][{FIRST|AFTER}existing_col_names]|MODIFY[COLUMN]col_nametype[constraint_condition][{FIRST|AFTER}existing_col_names]|CHANGE[COLUMN]col_namenew_col_nametype[constraint_condition]|ALTER[COLUMN]col_name{SET|DROP}DEFAULT|DROP[COLUMN]col_name|AUTO_INCREMENT[=n]|RENAME[{AS|TO}]new_tb_name;3.3表的操作【例3-7】在studentinfo數(shù)據(jù)庫中，向class表中添加班長一列ClassMonitor，數(shù)據(jù)類型是VARCHAR(20)，并將該列添加到原表Grade列之后。ALTERTABLEstudentinfo.classADDCOLUMNClassMonitorVARCHAR(20)AFTERGrade;DESCstudentinfo.class;3.3表的操作【例3-8】在studentinfo數(shù)據(jù)庫中，向class表中添加一列入學(xué)日期EntryDate，添加到ClassName列后；把ClassMonitor的數(shù)據(jù)類型寬度改為VARCHAR(10)。ALTERTABLEclassADDEntryDateDATEAFTERClassName;ALTERTABLEclassMODIFYClassMonitorVARCHAR(10);DESCclass;3.3表的操作2.使用SQL語句更改存儲引擎ALTERTABLEtb_nameENGINE=engine_name;【例3-9】將表student的存儲引擎修改為MyISAM。ALTERTABLEstudentENGINE=MyISAM;SHOWCREATETABLEstudent;ALTERTABLEstudentENGINE=InnoDB;3.3表的操作3.使用SQL語句重命名表名ALTERTABLEtb_nameRENAME[{AS|TO}]new_tb_name;RENAMETABLEtb_name1TOnew_tb_name1[,tb_name2TOnew_tb_name2]…;【例3-10】分別使用上面兩種語句，在數(shù)據(jù)庫studentinfo中，修改student表名。先把student表名改為stu，然后再改回student。ALTERTABLEstudentRENAMETOstu;RENAMETABLEstuTOstudent;3.3表的操作4.使用NavicatforMySQL對話方式修改表結(jié)構(gòu)【例3-11】使用NavicatforMySQL對話方式修改表結(jié)構(gòu)，在數(shù)據(jù)庫studentinfo中修改student表。3.3表的操作3.3.4刪除表1.使用SQL語句刪除表DROPTABLE[IFEXISTS]tb_name1[,tb_name2]...;【例3-12】在studentinfo數(shù)據(jù)庫中，刪除student表。DROPTABLEIFEXISTSstudent;2.使用NavicatforMySQL對話方式刪除表3.4數(shù)據(jù)的完整性約束3.4.1數(shù)據(jù)完整性約束的概念和子句1.數(shù)據(jù)完整性約束的概念1）實體完整性約束。2）參照完整性約束。3）用戶自定義完整性約束。3.4數(shù)據(jù)的完整性約束2.數(shù)據(jù)完整性約束條件子句在CREATETABLE語句中定義數(shù)據(jù)完整性約束條件子句的格式為：[NULL|NOTNULL][UNIQUE[KEY]]|[PRIMARY[KEY]][reference_definition]3.4數(shù)據(jù)的完整性約束3.4.2定義實體完整性1.主鍵約束（PRIMARYKEYConstraint）（1）主鍵列必須遵守的規(guī)則1）每一個表只能定義一個主鍵。2）主鍵的值，也稱為鍵值，必須能夠唯一標識表中的每一行記錄，且不能為NULL。3）復(fù)合主鍵不能包含不必要的多余列。4）一個列名在復(fù)合主鍵的列表中只能出現(xiàn)一次。3.4數(shù)據(jù)的完整性約束（2）實現(xiàn)主鍵約束的方式1）列級完整性約束。列名數(shù)據(jù)類型[其他約束]PRIMARYKEY3.4數(shù)據(jù)的完整性約束【例3-13】在數(shù)據(jù)庫studentinfo中，重新創(chuàng)建學(xué)生表student，要求以列級完整性約束方式定義StudentID列為主鍵。SQL語句如下：USEstudentinfo;DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(12)PRIMARYKEY,StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,AddressVARCHAR(30),ClassIDCHAR(10));3.4數(shù)據(jù)的完整性約束3.4數(shù)據(jù)的完整性約束同樣，在數(shù)據(jù)庫studentinfo中，創(chuàng)建院系表department，要求以列級完整性約束方式定義DepartmentID列為主鍵。CREATETABLEdepartment(DepartmentIDCHAR(2)PRIMARYKEY,DepartmentNameVARCHAR(20)NOTNULL);3.4數(shù)據(jù)的完整性約束2）表級完整性約束。PRIMARYKEY(列名,…)【例3-14】在studentinfo數(shù)據(jù)庫中，創(chuàng)建選課表selectcourse，結(jié)構(gòu)見表3-8，要求以表級完整性約束方式定義主鍵，將學(xué)號、課程編號（StudentID,CourseID）的列組合設(shè)置為selectcourse表的主鍵。3.4數(shù)據(jù)的完整性約束USEstudentinfo;DROPTABLEIFEXISTSselectcourse;CREATETABLEselectcourse(StudentIDCHAR(12),CourseIDCHAR(6),ScoreDECIMAL(4,1),SelectCourseDateDATE,PRIMARYKEY(StudentID,CourseID));3.4數(shù)據(jù)的完整性約束【例3-15】如果選課表selectcourse已經(jīng)存在，可以刪除原來的主鍵，把selectcourse表的主鍵修改為學(xué)號、課程號（StudentID,CourseID）的列組合。ALTERTABLEselectcourseDROPPRIMARYKEY,ADDPRIMARYKEY(StudentID,CourseID);3.4數(shù)據(jù)的完整性約束2.完整性約束的命名CONSTRAINT<約束名>{PRIMARYKEY(主鍵列的列表)|UNIQUEKEY(唯一鍵列的列表)|FOREIGNKEY(外鍵列的列表)REFERENCES被參照關(guān)系的表(主鍵列的列表)|CHECK(約束條件表達式)};3.4數(shù)據(jù)的完整性約束【例3-16】在數(shù)據(jù)庫studentinfo中，定義課程表course，表結(jié)構(gòu)見表3-9。3.4數(shù)據(jù)的完整性約束USEstudentinfo;DROPTABLEIFEXISTScourse;CREATETABLEcourse(CourseIDCHAR(6),CourseNameVARCHAR(30)NOTNULL,CreditSMALLINTNOTNULL,CourseHourSMALLINTNOTNULL,PreCourseIDCHAR(6),TermTINYINT,CONSTRAINTPK_coursePRIMARYKEY(CourseID));3.4數(shù)據(jù)的完整性約束【例3-17】在studentinfo數(shù)據(jù)庫中，修改選課表selectcourse，要求以表級完整性約束方式定義主鍵（StudentID，CourseID），并指定主鍵約束名稱為PK_selectcourse。在修改表結(jié)構(gòu)語句中，要先刪除原來的主鍵，然后添加新的主鍵。ALTERTABLEselectcourseDROPPRIMARYKEY,ADDCONSTRAINTPK_selectcoursePRIMARYKEY(StudentID,CourseID);3.4數(shù)據(jù)的完整性約束3.唯一約束（UNIQUEKEYConstraint)（1）列級完整性約束列名數(shù)據(jù)類型[其他約束]UNIQUEKEY（2）表級完整性約束UNIQUEKEY(列名,…)3.4數(shù)據(jù)的完整性約束【例3-18】在數(shù)據(jù)庫studentinfo中，重新定義和創(chuàng)建班級表class。班級表class中的班級編號ClassID和班級名稱ClassName這兩列的值都是唯一的，在ClassID列上定義主鍵約束，在ClassName列上定義唯一鍵約束，都定義為列級的完整性約束。USEstudentinfo;DROPTABLEIFEXISTSclass;CREATETABLEclass(ClassIDCHAR(10)PRIMARYKEY,ClassNameVARCHAR(20)NOTNULLUNIQUEKEY,ClassNumTINYINT,GradeSMALLINT,DepartmentIDCHAR(2)NOTNULL);3.4數(shù)據(jù)的完整性約束ALTERTABLEclassMODIFYClassNameVARCHAR(20)NOTNULLUNIQUEKEY;DROPTABLEIFEXISTSclass;CREATETABLEclass(ClassIDCHAR(10)NOTNULL,ClassNameVARCHAR(20)NOTNULL,ClassNumTINYINT,GradeSMALLINT,DepartmentIDCHAR(2)NOTNULL,CONSTRAINTPK_classPRIMARYKEY(ClassID),CONSTRAINTUQ_classUNIQUEKEY(ClassName));3.4數(shù)據(jù)的完整性約束3.4數(shù)據(jù)的完整性約束SHOWINDEXFROMtb_name;例如，顯示class表上定義的唯一鍵約束，SQL語句如下：SHOWINDEXFROMclass;4.PRIMARYKEY與UNIQUEKEY的區(qū)別3.4數(shù)據(jù)的完整性約束3.4.3定義參照完整性1.外鍵（FOREIGNKEY）的概念3.4數(shù)據(jù)的完整性約束2.外鍵約束（FOREIGNKEYConstraint）（1）在列級完整性上定義外鍵約束在從表中的列上定義外鍵約束的語法規(guī)則為。列名數(shù)據(jù)類型[其他約束]REFERENCES主表(主表中主鍵列的列表)[ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}][ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}]3.4數(shù)據(jù)的完整性約束（2）在表級完整性上定義外鍵約束的子句在從表中定義外鍵約束子句的語法規(guī)則為。FOREIGNKEY(從表中外鍵列的列表)REFERENCES主表(主表中主鍵列的列表)[ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}][ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}]3.4數(shù)據(jù)的完整性約束【例3-19】在數(shù)據(jù)庫studentinfo中，重新定義學(xué)生表student，要求以列級完整性約束方式定義與class表的外鍵ClassID，采用默認的RESTRICT參照動作。USEstudentinfo;DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(12)PRIMARYKEY,StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,AddressVARCHAR(30),ClassIDCHAR(10)REFERENCESclass(ClassID)ONUPDATERESTRICTONDELETERESTRICT);3.4數(shù)據(jù)的完整性約束【例3-20】在數(shù)據(jù)庫studentinfo中，重新學(xué)生表student，要求以表級完整性約束方式定義與class表的外鍵ClassID。DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(12),StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,AddressVARCHAR(30),ClassIDCHAR(10),CONSTRAINTPK_studentPRIMARYKEY(studentID),CONSTRAINTFK_studentFOREIGNKEY(ClassID)REFERENCESclass(ClassID));3.4數(shù)據(jù)的完整性約束SHOWCREATETABLEstudent;3.4數(shù)據(jù)的完整性約束【例3-21】在數(shù)據(jù)庫studentinfo中，重新創(chuàng)建選課表selectcourse，指定主鍵為StudentID和CourseID，指定外鍵分別為student表的StudentID，course表的CourseID。DROPTABLEIFEXISTSstudentinfo.selectcourse;CREATETABLEstudentinfo.selectcourse(StudentIDCHAR(12)NOTNULL,CourseIDCHAR(6)NOTNULL,ScoreDECIMAL(4,1),SelectCourseDateDATE,PRIMARYKEY(StudentID,CourseID),CONSTRAINTFK_selectcourse_studentFOREIGNKEY(StudentID)REFERENCESstudent(StudentID)ONUPDATERESTRICTONDELETERESTRICT,CONSTRAINTFK_selectcourse_courseFOREIGNKEY(CourseID)REFERENCEScourse(CourseID)ONUPDATERESTRICTONDELETERESTRICT);3.4數(shù)據(jù)的完整性約束3.4數(shù)據(jù)的完整性約束（3）通過ALTERTABLE語句添加外鍵約束ALTERTABLE外鍵所在的表名

ADD[CONSTRAINT外鍵名]FOREIGNKEY(外鍵列名,…)REFERENCES關(guān)聯(lián)外鍵的表名(主鍵列名,…)ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}]ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}];3.4數(shù)據(jù)的完整性約束【例3-22】將選課表selectcourse的StudentID列設(shè)置為外鍵，該列的值參照學(xué)生表student的StudentID列的取值。由于selectcourse表已經(jīng)在上例中創(chuàng)建，這里修改添加外鍵。ALTERTABLEselectcourseADDFOREIGNKEY(StudentID)REFERENCESstudent(StudentID)ONUPDATERESTRICTONDELETECASCADE;3.4數(shù)據(jù)的完整性約束【例3-23】在數(shù)據(jù)庫studentinfo中，重新定義學(xué)生表student，要求在定義外鍵時，同時定義相應(yīng)的參照動作。ALTERTABLEselectcourseDROPFOREIGNKEYFK_selectcourse_student;ALTERTABLEselectcourseDROPFOREIGNKEYselectcourse_ibfk_1;DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(12),StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,AddressVARCHAR(30),ClassIDCHAR(10),CONSTRAINTPK_studentPRIMARYKEY(StudentID),CONSTRAINTFK_studentFOREIGNKEY(ClassID)REFERENCESclass(ClassID)ONUPDATERESTRICTONDELETECASCADE);3.4數(shù)據(jù)的完整性約束3.4.4用戶定義的完整性1.非空約束（NOTNULLConstraint）列名數(shù)據(jù)類型NOTNULL[其他約束]【例3-24】將student表的StudentName列修改為非空約束。SQL語句如下：ALTERTABLEstudentMODIFYStudentNameCHAR(20)NOTNULL;3.4數(shù)據(jù)的完整性約束2.默認值約束（DEFAULTConstraint）列名數(shù)據(jù)類型[其他約束]DEFAULT默認值【例3-25】將student表的Sex列的約束修改為非空、只能取值“男”或“女”、默認值為“男”。ALTERTABLEstudentMODIFYSexENUM('男','女')DEFAULT'男'NOTNULL;SHOWCREATETABLEstudent;3.4數(shù)據(jù)的完整性約束3.檢查約束(CHECKConstraint)CONSTRAINT<檢查約束名>CHECK(expr)3.4數(shù)據(jù)的完整性約束【例3-26】創(chuàng)建student1表，定義Sex列為非空、只能取值“男”或“女”、默認值為“男”。CREATETABLEstudent1(StudentIDCHAR(12),StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)DEFAULT'男'NOTNULL,BirthdayDATE,AddressVARCHAR(30),ClassIDCHAR(10),CONSTRAINTCK_studentCHECK(Sex='男'ORSex='女'),CONSTRAINTPK_studentPRIMARYKEY(StudentID),CONSTRAINTFK_studentFOREIGNKEY(ClassID)REFERENCESclass(ClassID)ONUPDATERESTRICTONDELETECASCADE);3.4數(shù)據(jù)的完整性約束如果要查看語句，在命令列界面窗格中輸入下面SQL語句。SHOWCREATETABLEstudent1;3.4數(shù)據(jù)的完整性約束4.自增約束（AUTO_INCREMENTConstraint）列名數(shù)據(jù)類型[其他約束]AUTO_INCREMENT【例3-27】在studentinfo數(shù)據(jù)庫中創(chuàng)建臨時表temp1，設(shè)置ID列為INT類型，要求其為主鍵，自動增加；Name列為CHAR(10)。CREATETABLEtemp1(IDINTPRIMARYKEYAUTO_INCREMENT,NameCHAR(10));DESCtemp1;3.4數(shù)據(jù)的完整性約束3.4.5更新完整性約束1.刪除約束（1）刪除主鍵約束ALTERTABLEtb_nameDROPPRIMARYKEY;3.4數(shù)據(jù)的完整性約束【例3-28】在studentinfo數(shù)據(jù)庫中創(chuàng)建臨時表temp2，定義NameID列為INT類型，主鍵；Name列為CHAR(10)，非空，唯一鍵；Sex列為CHAR(2)，默認值為“男”；Age列為SMALLINT，要求Age>=16ANDAge<=30；ID列為INT，是temp1表的外鍵。CREATETABLEtemp2(NameIDINTPRIMARYKEY,NameCHAR(10)NOTNULLUNIQUEKEY,SexCHAR(2)DEFAULT'男',AgeSMALLINT,CONSTRAINTCK_AgeCHECK(Age>=16ANDAge<=30),IDINT,CONSTRAINTFK_temp2FOREIGNKEY(ID)REFERENCEStemp1(ID)ONUPDATERESTRICT);3.4數(shù)據(jù)的完整性約束在temp2表中，刪除定義的主鍵約束。SQL語句如下：ALTERTABLEtemp2DROPPRIMARYKEY;3.4數(shù)據(jù)的完整性約束（2）刪除唯一鍵約束ALTERTABLEtb_nameDROPINDEX{約束名|唯一鍵約束名};【例3-29】在臨時表temp2中，刪除Name列上定義的唯一鍵約束。如果沒有給唯一鍵命名，則使用DROPINDEX子句刪除的是定義唯一鍵的列名，SQL語句如下：ALTERTABLEtemp2DROPINDEXName;3.4數(shù)據(jù)的完整性約束（3）刪除非空約束【例3-30】在臨時表temp2中，刪除Name列的非空約束。ALTERTABLEtemp2MODIFYNameCHAR(10)NULL;3.4數(shù)據(jù)的完整性約束（4）刪除檢查約束ALTERTABLEtb_nameDROPCHECK約束名;【例3-31】刪除在temp2和course表中定義的檢查約束。SQL語句如下：ALTERTABLEtemp2DROPCHECKCK_Age;ALTERTABLEcourseDROPCHECKCK_course_credit;3.4數(shù)據(jù)的完整性約束（5）刪除自增約束ALTERTABLEtb_nameMODIFY列名INT;【例3-32】在臨時表temp1中，刪除ID列上定義的自增約束。SQL語句如下：ALTERTABLEtemp1MODIFYIDINT;3.4數(shù)據(jù)的完整性約束（6）刪除默認值約束ALTERTABLEtb_nameALTER列名DROPDEFAULT;【例3-33】在臨時表temp2中，刪除Sex列的默認值。ALTERTABLEtemp2ALTERSexDROPDEFAULT;3.4數(shù)據(jù)的完整性約束（7）刪除外鍵約束ALTERTABLEtb_nameDROPFOREIGNKEYforeign_key_name;【例3-34】在temp2表中，刪除ID列上定義的外鍵約束FK_temp2。ALTERTABLEtemp2DROPFOREIGNKEYFK_temp2;3.4數(shù)據(jù)的完整性約束【例3-35】在表temp2中，在ID列上定義一個無命名的外鍵約束，然后刪除它。定義一個無命名的外鍵約束，SQL語句如下：ALTERTABLEtemp2ADDFOREIGNKEY(ID)REFERENCEStemp1(ID);SHOWCREATETABLEtemp2;3.4數(shù)據(jù)的完整性約束3.4數(shù)據(jù)的完整性約束2.添加約束（1）添加主鍵約束ALTERTABLEtb_nameADD[CONSTRAINT<約束名>]PRIMARYKEY(主鍵列名);【例3-36】在temp2表中，為Name