第3章 表的操作和数据的完整性约束

第3章表的操作和数据的完整性约束《MySQL数据库应用教程》刘瑞新主编配套资源目录第3章表的操作和数据的完整性约束3.1表的概述3.2数据类型3.3表的操作3.4数据的完整性约束3.1.1表的基本概念1）表名。2）列名。3）行或记录。4）列或属性。5）数据项。6）数据的完整性约束。3.1表的概述3.1表的概述3.1.2数据类型概述3.2数据类型2.浮点数类型和定点数类型及其取值范围3.2数据类型3.2.2字符串类型3.2数据类型3.2.3日期和时间类型3.2数据类型3.2.4二进制类型3.2.5复合类型1.SET（集合）类型列名SET('值1','值2',…,'值n')2.ENUM（枚举）类型列名ENUM('值1','值2',…,'值n')3.3表的操作3.3.1创建表1.使用SQL语句创建表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】在学生信息数据库studentinfo中，创建学生表student，student表的定义见表3-6。要求对表使用InnoDB存储引擎，设置该表的字符集为utf8，其对应校对规则是utf8_bin。3.3表的操作USEstudentinfo;CREATETABLEstudent(StudentIDCHAR(12)COMMENT'学号',StudentNameVARCHAR(20)COMMENT'姓名',SexENUM('男','女')DEFAULT'男',BirthdayDATECOMMENT'出生日期',AddressVARCHAR(30),ClassIDCHAR(10)COMMENT'班级编号')ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_bin;3.3表的操作3.3表的操作【例3-2】创建临时表temp_table。SQL语句如下：CREATETEMPORARYTABLEtemp_table(IDINTNOTNULL,NameVARCHAR(10),ValueINTNOTNULL);3.3表的操作2.使用NavicatforMySQL对话方式创建表【例3-3】在studentinfo数据库中，创建班级表class，该表的定义见表3-7。3.3表的操作3.3表的操作3.3表的操作3.3表的操作3.3.2查看表1.查看表的名称SHOWTABLES[{FROM|IN}db_name];【例3-4】在数据库studentinfo中，查看所有的表名。USEstudentinfo;SHOWTABLES;3.3表的操作2.查看表的基本结构SHOWCOLUMNS语句的语法格式为：SHOWCOLUMNS{FROM|IN}tb_name[{FROM|IN}db_name];DESCRIBE/DESC语句的语法格式为：{DESCRIBE|DESC}tb_name;【例3-5】在数据库studentinfo中，查看表student的结构。SHOWCOLUMNSFROMstudent;或DESCstudent;3.3表的操作3.3表的操作3.3表的操作3.查看表的详细结构SHOWCREATETABLEtb_name;【例3-6】在数据库studentinfo中，查看表class的详细信息。USEstudentinfo;SHOWCREATETABLEclass\G;3.3表的操作3.3表的操作3.3.3修改表1.使用SQL语句修改表结构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数据库中，向class表中添加班长一列ClassMonitor，数据类型是VARCHAR(20)，并将该列添加到原表Grade列之后。ALTERTABLEstudentinfo.classADDCOLUMNClassMonitorVARCHAR(20)AFTERGrade;DESCstudentinfo.class;3.3表的操作【例3-8】在studentinfo数据库中，向class表中添加一列入学日期EntryDate，添加到ClassName列后；把ClassMonitor的数据类型宽度改为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】分别使用上面两种语句，在数据库studentinfo中，修改student表名。先把student表名改为stu，然后再改回student。ALTERTABLEstudentRENAMETOstu;RENAMETABLEstuTOstudent;3.3表的操作4.使用NavicatforMySQL对话方式修改表结构【例3-11】使用NavicatforMySQL对话方式修改表结构，在数据库studentinfo中修改student表。3.3表的操作3.3.4删除表1.使用SQL语句删除表DROPTABLE[IFEXISTS]tb_name1[,tb_name2]...;【例3-12】在studentinfo数据库中，删除student表。DROPTABLEIFEXISTSstudent;2.使用NavicatforMySQL对话方式删除表3.4数据的完整性约束3.4.1数据完整性约束的概念和子句1.数据完整性约束的概念1）实体完整性约束。2）参照完整性约束。3）用户自定义完整性约束。3.4数据的完整性约束2.数据完整性约束条件子句在CREATETABLE语句中定义数据完整性约束条件子句的格式为：[NULL|NOTNULL][UNIQUE[KEY]]|[PRIMARY[KEY]][reference_definition]3.4数据的完整性约束3.4.2定义实体完整性1.主键约束（PRIMARYKEYConstraint）（1）主键列必须遵守的规则1）每一个表只能定义一个主键。2）主键的值，也称为键值，必须能够唯一标识表中的每一行记录，且不能为NULL。3）复合主键不能包含不必要的多余列。4）一个列名在复合主键的列表中只能出现一次。3.4数据的完整性约束（2）实现主键约束的方式1）列级完整性约束。列名数据类型[其他约束]PRIMARYKEY3.4数据的完整性约束【例3-13】在数据库studentinfo中，重新创建学生表student，要求以列级完整性约束方式定义StudentID列为主键。SQL语句如下：USEstudentinfo;DROPTABLEIFEXISTSstudent;CREATETABLEstudent(StudentIDCHAR(12)PRIMARYKEY,StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATE,AddressVARCHAR(30),ClassIDCHAR(10));3.4数据的完整性约束3.4数据的完整性约束同样，在数据库studentinfo中，创建院系表department，要求以列级完整性约束方式定义DepartmentID列为主键。CREATETABLEdepartment(DepartmentIDCHAR(2)PRIMARYKEY,DepartmentNameVARCHAR(20)NOTNULL);3.4数据的完整性约束2）表级完整性约束。PRIMARYKEY(列名,…)【例3-14】在studentinfo数据库中，创建选课表selectcourse，结构见表3-8，要求以表级完整性约束方式定义主键，将学号、课程编号（StudentID,CourseID）的列组合设置为selectcourse表的主键。3.4数据的完整性约束USEstudentinfo;DROPTABLEIFEXISTSselectcourse;CREATETABLEselectcourse(StudentIDCHAR(12),CourseIDCHAR(6),ScoreDECIMAL(4,1),SelectCourseDateDATE,PRIMARYKEY(StudentID,CourseID));3.4数据的完整性约束【例3-15】如果选课表selectcourse已经存在，可以删除原来的主键，把selectcourse表的主键修改为学号、课程号（StudentID,CourseID）的列组合。ALTERTABLEselectcourseDROPPRIMARYKEY,ADDPRIMARYKEY(StudentID,CourseID);3.4数据的完整性约束2.完整性约束的命名CONSTRAINT<约束名>{PRIMARYKEY(主键列的列表)|UNIQUEKEY(唯一键列的列表)|FOREIGNKEY(外键列的列表)REFERENCES被参照关系的表(主键列的列表)|CHECK(约束条件表达式)};3.4数据的完整性约束【例3-16】在数据库studentinfo中，定义课程表course，表结构见表3-9。3.4数据的完整性约束USEstudentinfo;DROPTABLEIFEXISTScourse;CREATETABLEcourse(CourseIDCHAR(6),CourseNameVARCHAR(30)NOTNULL,CreditSMALLINTNOTNULL,CourseHourSMALLINTNOTNULL,PreCourseIDCHAR(6),TermTINYINT,CONSTRAINTPK_coursePRIMARYKEY(CourseID));3.4数据的完整性约束【例3-17】在studentinfo数据库中，修改选课表selectcourse，要求以表级完整性约束方式定义主键（StudentID，CourseID），并指定主键约束名称为PK_selectcourse。在修改表结构语句中，要先删除原来的主键，然后添加新的主键。ALTERTABLEselectcourseDROPPRIMARYKEY,ADDCONSTRAINTPK_selectcoursePRIMARYKEY(StudentID,CourseID);3.4数据的完整性约束3.唯一约束（UNIQUEKEYConstraint)（1）列级完整性约束列名数据类型[其他约束]UNIQUEKEY（2）表级完整性约束UNIQUEKEY(列名,…)3.4数据的完整性约束【例3-18】在数据库studentinfo中，重新定义和创建班级表class。班级表class中的班级编号ClassID和班级名称ClassName这两列的值都是唯一的，在ClassID列上定义主键约束，在ClassName列上定义唯一键约束，都定义为列级的完整性约束。USEstudentinfo;DROPTABLEIFEXISTSclass;CREATETABLEclass(ClassIDCHAR(10)PRIMARYKEY,ClassNameVARCHAR(20)NOTNULLUNIQUEKEY,ClassNumTINYINT,GradeSMALLINT,DepartmentIDCHAR(2)NOTNULL);3.4数据的完整性约束ALTERTABLEclassMODIFYClassNameVARCHAR(20)NOTNULLUNIQUEKEY;DROPTABLEIFEXISTSclass;CREATETABLEclass(ClassIDCHAR(10)NOTNULL,ClassNameVARCHAR(20)NOTNULL,ClassNumTINYINT,GradeSMALLINT,DepartmentIDCHAR(2)NOTNULL,CONSTRAINTPK_classPRIMARYKEY(ClassID),CONSTRAINTUQ_classUNIQUEKEY(ClassName));3.4数据的完整性约束3.4数据的完整性约束SHOWINDEXFROMtb_name;例如，显示class表上定义的唯一键约束，SQL语句如下：SHOWINDEXFROMclass;4.PRIMARYKEY与UNIQUEKEY的区别3.4数据的完整性约束3.4.3定义参照完整性1.外键（FOREIGNKEY）的概念3.4数据的完整性约束2.外键约束（FOREIGNKEYConstraint）（1）在列级完整性上定义外键约束在从表中的列上定义外键约束的语法规则为。列名数据类型[其他约束]REFERENCES主表(主表中主键列的列表)[ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}][ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}]3.4数据的完整性约束（2）在表级完整性上定义外键约束的子句在从表中定义外键约束子句的语法规则为。FOREIGNKEY(从表中外键列的列表)REFERENCES主表(主表中主键列的列表)[ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}][ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}]3.4数据的完整性约束【例3-19】在数据库studentinfo中，重新定义学生表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数据的完整性约束【例3-20】在数据库studentinfo中，重新学生表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数据的完整性约束SHOWCREATETABLEstudent;3.4数据的完整性约束【例3-21】在数据库studentinfo中，重新创建选课表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数据的完整性约束3.4数据的完整性约束（3）通过ALTERTABLE语句添加外键约束ALTERTABLE外键所在的表名

ADD[CONSTRAINT外键名]FOREIGNKEY(外键列名,…)REFERENCES关联外键的表名(主键列名,…)ONDELETE{CASCADE|RESTRICT|SETNULL|NOACTION}]ONUPDATE{CASCADE|RESTRICT|SETNULL|NOACTION}];3.4数据的完整性约束【例3-22】将选课表selectcourse的StudentID列设置为外键，该列的值参照学生表student的StudentID列的取值。由于selectcourse表已经在上例中创建，这里修改添加外键。ALTERTABLEselectcourseADDFOREIGNKEY(StudentID)REFERENCESstudent(StudentID)ONUPDATERESTRICTONDELETECASCADE;3.4数据的完整性约束【例3-23】在数据库studentinfo中，重新定义学生表student，要求在定义外键时，同时定义相应的参照动作。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数据的完整性约束3.4.4用户定义的完整性1.非空约束（NOTNULLConstraint）列名数据类型NOTNULL[其他约束]【例3-24】将student表的StudentName列修改为非空约束。SQL语句如下：ALTERTABLEstudentMODIFYStudentNameCHAR(20)NOTNULL;3.4数据的完整性约束2.默认值约束（DEFAULTConstraint）列名数据类型[其他约束]DEFAULT默认值【例3-25】将student表的Sex列的约束修改为非空、只能取值“男”或“女”、默认值为“男”。ALTERTABLEstudentMODIFYSexENUM('男','女')DEFAULT'男'NOTNULL;SHOWCREATETABLEstudent;3.4数据的完整性约束3.检查约束(CHECKConstraint)CONSTRAINT<检查约束名>CHECK(expr)3.4数据的完整性约束【例3-26】创建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数据的完整性约束如果要查看语句，在命令列界面窗格中输入下面SQL语句。SHOWCREATETABLEstudent1;3.4数据的完整性约束4.自增约束（AUTO_INCREMENTConstraint）列名数据类型[其他约束]AUTO_INCREMENT【例3-27】在studentinfo数据库中创建临时表temp1，设置ID列为INT类型，要求其为主键，自动增加；Name列为CHAR(10)。CREATETABLEtemp1(IDINTPRIMARYKEYAUTO_INCREMENT,NameCHAR(10));DESCtemp1;3.4数据的完整性约束3.4.5更新完整性约束1.删除约束（1）删除主键约束ALTERTABLEtb_nameDROPPRIMARYKEY;3.4数据的完整性约束【例3-28】在studentinfo数据库中创建临时表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数据的完整性约束在temp2表中，删除定义的主键约束。SQL语句如下：ALTERTABLEtemp2DROPPRIMARYKEY;3.4数据的完整性约束（2）删除唯一键约束ALTERTABLEtb_nameDROPINDEX{约束名|唯一键约束名};【例3-29】在临时表temp2中，删除Name列上定义的唯一键约束。如果没有给唯一键命名，则使用DROPINDEX子句删除的是定义唯一键的列名，SQL语句如下：ALTERTABLEtemp2DROPINDEXName;3.4数据的完整性约束（3）删除非空约束【例3-30】在临时表temp2中，删除Name列的非空约束。ALTERTABLEtemp2MODIFYNameCHAR(10)NULL;3.4数据的完整性约束（4）删除检查约束ALTERTABLEtb_nameDROPCHECK约束名;【例3-31】删除在temp2和course表中定义的检查约束。SQL语句如下：ALTERTABLEtemp2DROPCHECKCK_Age;ALTERTABLEcourseDROPCHECKCK_course_credit;3.4数据的完整性约束（5）删除自增约束ALTERTABLEtb_nameMODIFY列名INT;【例3-32】在临时表temp1中，删除ID列上定义的自增约束。SQL语句如下：ALTERTABLEtemp1MODIFYIDINT;3.4数据的完整性约束（6）删除默认值约束ALTERTABLEtb_nameALTER列名DROPDEFAULT;【例3-33】在临时表temp2中，删除Sex列的默认值。ALTERTABLEtemp2ALTERSexDROPDEFAULT;3.4数据的完整性约束（7）删除外键约束ALTERTABLEtb_nameDROPFOREIGNKEYforeign_key_name;【例3-34】在temp2表中，删除ID列上定义的外键约束FK_temp2。ALTERTABLEtemp2DROPFOREIGNKEYFK_temp2;3.4数据的完整性约束【例3-35】在表temp2中，在ID列上定义一个无命名的外键约束，然后删除它。定义一个无命名的外键约束，SQL语句如下：ALTERTABLEtemp2ADDFOREIGNKEY(ID)REFERENCEStemp1(ID);SHOWCREATETABLEtemp2;3.4数据的完整性约束3.4数据的完整性约束2.添加约束（1）添加主键约束ALTERTABLEtb_nameADD[CONSTRAINT<约束名>]PRIMARYKEY(主键列名);【例3-36】在temp2表中，为Name