《第5章MySQL表结构管理电子课件 MySQL数据库管理与应用.pptx》由会员分享，可在线阅读，更多相关《第5章MySQL表结构管理电子课件 MySQL数据库管理与应用.pptx（112页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第5章MySQL表结构管理电子课件 MySQL数据库管理与应用第5章 MySQL表结构管理主要内容5.1 表的概念5.2 MySQL的数据类型5.3 创建表5.4 定义约束5.5 使用Workbench创建表5.6 查看表5.7 修改表5.8 删除表5.9 本章小结5.1 表的概念表（Table）是关系数据库中最重要的数据库对象，用来存储数据库中的数据。一个数据库包含一个或多个表，表由行（Row）和列（Column）组成。表中的一行称为一个记录（Record），每个表包含若干行数据。列又称为字段（Field），由数据类型、长度、是否允许空值、默认值等组成，每个列表示记录的一个属性。一个完整的表
2、包含表结构和表数据两部分内容，表的结构主要包括表的列名、数据类型、长度、是否允许空值、默认值以及约束等，表数据就是表中的记录。例如，“教务管理系统”数据库中的学生表的结构和部分数据如表5.1和表5.2所示。5.1 表的概念表5.1 学生（student）表结构列名数据类型长度允许空值默认值约束说明snochar11否主键学生编号snamevarchar10否学生姓名ssexchar1否男只能取男或女性别sbirthdaydate否出生日期snationvarchar10否民族mnochar4是外键，参照major表的mno列专业编号5.1 表的概念表5.2 学生（student）表数据snos
3、namessexsbirthdaysnationmno20190101001刘丽女2001-3-2汉族010120190101002张林男2000-9-12汉族010120190102001李宏男2001-8-29回族010220200102001孙明男2001-10-18汉族010220200102002赵均男2000-12-19汉族010220200101001张莉女2001-6-20汉族010120210201001牛伟男2003-9-18回族02015.2 MySQL的数据类型定义表时需要对表中的列进行属性设置，包括列的名称、数据类型、长度、默认值等，其中最重要的属性就是数据类型。数据
4、类型是指存储在数据库中的数据的类型，决定了数据的存储格式和取值范围。MySQL中的数据类型主要包括数值类型、字符串类型、日期和时间类型、空间类型和JSON类型。本书主要介绍数值类型、字符串类型、日期和时间类型。5.2 MySQL的数据类型5.2.1 数值类型1.整数类型l整数类型包括TINYINT、INTEGER（或INT）、SMALLINT、MEDIUMINT和BIGINT。数据类型存储字节数无符号数取值范围有符号数取值范围TINYINT10255-128127SMALLINT2065535-3276832767MEDIUMINT3016777215-83886088388607INTEGE
5、R（或INT）404294967295-21474836482147483647BIGINT80264-1-263263-15.2 MySQL的数据类型5.2.1 数值类型2.定点数类型l定点数类型包括DECIMAL和NUMERIC，用于存储精确的数值数据。在MySQL中，NUMERIC被实现为DECIMAL。l定点数类型的定义语法为DECIMAL(M,D)或NUMERIC(M,D)，其中M是最大位数（精度），范围是1到65，D是小数点后的位数（小数位数），它的范围是0到30，并且不能大于M。如果D省略，则默认为0，如果M省略，则默认为10。l例如，定义salary列的数据类型为DECIMAL
6、(5,2)，则该列能够存储具有五位数字和两位小数的任何值，因此salary列的取值范围从-999.99到999.99。5.2 MySQL的数据类型5.2.1 数值类型3.浮点数类型l浮点数类型包括单精度FLOAT类型和双精度DOUBLE类型，代表近似的数据值。MySQL对单精度值使用四个字节存储，对双精度值使用八个字节存储。l对于FLOAT，MySQL支持在关键字FLOAT之后指定可选的精度，但是FLOAT（p）中的精度值仅用于确定存储大小，0到23之间的精度将产生一个4字节的单精度浮点列。从24到53的精度将产生一个8字节的双精度浮点列。l由于浮点值是近似值，而不是存储为精确值，因此在比较中
7、尝试将其视为精确值可能会导致问题。5.2 MySQL的数据类型5.2.1 数值类型4.位值类型l位数据类型用于存储位值，BIT（M）允许存储一个长度为M的位值，M的范围从1到64。要指定位值，可以使用b值表示法。值是使用0和1组成的二进制值。例如，b111和b10000000分别表示7和128。l如果给一个BIT（M）列赋值一个长度小于M的位值，则该值在左侧用零填充。例如，将b101赋值给类型为BIT（6）的列实际上与b000101相同。5.2 MySQL的数据类型5.2.2 字符串类型字符串数据类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和
8、SET类型数据类型存储字节CHAR(M)Mw字节，0=M=255，其中w是字符集中最大长度字符所需的字节数。BINARY(M)M字节，0=M=255VARCHAR(M),VARBINARY(M)如果列值需要0255个字节，则L+1个字节，如果值可能需要超过255个字节，则L+2个字节TINYBLOB,TINYTEXTL+1个字节，其中L28BLOB,TEXTL+2个字节，其中L216MEDIUMBLOB,MEDIUMTEXTL+3个字节，其中L224LONGBLOB,LONGTEXTL+4个字节，其中L=1andcterm=8);5.4 定义约束5.4.5 外键约束外键约束在两个表的列之间建立
9、参照关系，用来实现参照完整性。一个表可以有一个或多个外键，外键可以是一列也可以是多列，外键的值可以为空值，如果不为空则必须是它所参照的另一个表的主键的一个值。外键约束通常涉及到两个表，根据表之间的参照关系分为：l主表（父表）：被参照表，在两个表的参照关系中主键所在的表l从表（子表）：参照表，在两个表的参照关系中外键所在的表5.4 定义约束5.4.5 外键约束1.创建表时定义外键约束在CREATETABLE语句中定义外键约束，其语法格式为：CONSTRAINTconstraint_nameFOREIGNKEY(col_name,.)REFERENCEStbl_name(col_name,.)ON
10、 DELETE RESTRICT|CASCADE|SET NULL|NOACTIONON UPDATE RESTRICT|CASCADE|SET NULL|NOACTION5.4 定义约束5.4.5 外键约束【例5.15】创建score表，将sno列设置为外键，参照student表的sno列。CREATETABLEscore(snochar(11)notnull,cnochar(3)notnull,gradetinyint,CONSTRAINTpk_sno_cnoPRIMARYKEY(sno,cno),CONSTRAINTfk_snoFOREIGNKEY(sno)REFERENCESstude
11、nt(sno);5.4 定义约束5.4.5 外键约束2.修改表时定义外键约束如果表已经存在并且需要创建外键约束，可以用ALTERTABLE语句对表结构进行修改增加外键约束，语法格式如下：ALTERTABLEtbl_nameADDCONSTRAINTconstraint_nameFOREIGNKEY(col_name,.)REFERENCEStbl_name(col_name,.)ONDELETERESTRICT|CASCADE|SETNULL|NOACTIONONUPDATERESTRICT|CASCADE|SETNULL|NOACTION5.4 定义约束5.4.5 外键约束【例5.16】修改
12、score表，将cno列设置为外键参照course表的cno列。在MySQL命令行客户端输入命令：ALTERTABLEscoreADDCONSTRAINTfk_cnoFOREIGNKEY(cno)REFERENCEScourse(cno);5.5 使用Workbench创建表列名数据类型长度允许空约束说明mnochar4否主键专业编号mnamevarchar20否唯一专业名称dnochar2是外键，参照dept表的dno列所属学院编号专业表major5.5 使用Workbench创建表打开WorkBench工具，连接到MySQL服务器。在左侧的“SCHEMAS”导航栏中找到jwgl数据库，单击
13、左边的箭头，在下面的“Tables”节点上单击鼠标右键，在出现的菜单中选择“Create Table”，右侧将打开“new_table-Table”对话框的“Columns”选项卡。5.5 使用Workbench创建表5.5 使用Workbench创建表在Table Name文本框中输入要创建的表名major，在Charset/Collation、Engine下拉列表中选择表的默认字符集、字符集的排序规则、存储引擎，这里采用默认值。依次输入每个列的名称、数据类型、长度；mno列为主键，不允许空值，勾选该列后的PK和NN复选框；mname列不允许空值，要求唯一，勾选该列后的NN和UQ复选框。5.
14、5 使用Workbench创建表5.5 使用Workbench创建表选择“Foreign Keys”选项卡，在左侧列表框的“Foreign Key Name”处输入外键约束的名称“fk_dno”，“Referenced Table”下拉列表中选择dept表。右侧列表框中的“Column”处出现major表中的列，勾选dno列，在对应的“Referenced Column”处选择dept表中的dno列。单击“Apply”按钮，完成表的创建。5.5 使用Workbench创建表5.6 查看表5.6.1查看当前数据库中的表可以使用SHOWTABLES命令查看当前数据库中已有的表。【例5.17】查看j
15、wgl数据库中已有的表。在MySQL命令行客户端输入命令：USEjwglSHOWTABLES;5.6 查看表5.6.2 查看表结构在 MySQL中 可 以 使 用 DESCRIBE语 句 或 SHOWCOLUMNS|FIELDS语句查看某个表的基本结构，包括列名、数据类型、键、是否允许空值、默认值等信息。5.6 查看表5.6.2 查看表结构1.DESCRIBE语句语法格式：DESCRIBEdb_name.tbl_name【例5.18】使用DESCRIBE语句查看jwgl数据库中student表的结构。在MySQL命令行客户端输入命令：DESCstudent;5.6 查看表5.6.2 查看表结构
16、2.SHOWCOLUMNS|FIELDS语句语法格式：SHOWCOLUMNS|FIELDSFROM|INtbl_nameFROM|INdb_name【例5.19】使用SHOWCOLUMNS|FIELDS语句查看jwgl数据库中course表的结构。在MySQL命令行客户端输入命令：SHOWCOLUMNSFROMcourse;5.6 查看表5.6.3 查看表的定义语句SHOW CREATE TABLE语 句 可 以 用 来 查 看 创 建 表 的CREATETABLE语句，其语法格式为：SHOWCREATETABLEtbl_name【例5.20】使用SHOWCREATETABLE语句查看stud
17、ent表的定义。在MySQL命令行客户端输入命令：SHOWCREATETABLEstudentG5.6 查看表5.6.3 查看表的定义语句5.7 修改表MySQL中可以使用ALTER TABLE语句修改表，包括：列的修改约束的修改修改表名和表的选项。5.7 修改表5.7.1 列的添加、修改与删除1添加列MySQL中添加列的语句格式如下：ALTERTABLEtbl_nameADDCOLUMNcol_namecolumn_definitionFIRST|AFTERcol_name5.7 修改表5.7.1 列的添加、修改与删除【例5.21】在student表中添加学生电话stelephone字段，数
18、据类型为VARCHAR，长度8，允许空值。在MySQL命令行客户端输入命令：ALTERTABLEstudentADDstelephoneVARCHAR(8);5.7 修改表5.7.1 列的添加、修改与删除2修改列的定义MySQL中修改列名的语句格式如下：ALTERTABLEtbl_nameMODIFYCOLUMNcol_namecolumn_definitionFIRST|AFTERcol_name5.7 修改表5.7.1 列的添加、修改与删除【例5.22】将student表中的stelephone字段的数据类型改为CHAR，长度8。在MySQL命令行客户端输入命令：ALTERTABLEstu
19、dentMODIFYstelephoneCHAR(8);5.7 修改表5.7.1 列的添加、修改与删除3修改列名MySQL中修改列名的语句格式如下：ALTERTABLEtbl_nameRENAMECOLUMNold_col_nameTOnew_col_name5.7 修改表5.7.1 列的添加、修改与删除【例5.23】将student表中的stelephone字段的名称改为sphone在MySQL命令行客户端输入命令：ALTERTABLEstudentRENAMECOLUMNstelephoneTOsphone;5.7 修改表5.7.1 列的添加、修改与删除4修改列名和列定义MySQL中可以使
20、用CHANGECOLUMN修改列名和列的定义，语句格式如下：ALTERTABLEtbl_nameCHANGECOLUMNold_col_namenew_col_namecolumn_definitionFIRST|AFTERcol_name5.7 修改表5.7.1 列的添加、修改与删除【例5.24】将student表中的sphone字段的名称改为smobilephone，数据类型为VARCHAR，长度11，允许空值。在MySQL命令行客户端输入命令：ALTERTABLEstudentCHANGEsphonesmobilephoneVARCHAR(11);5.7 修改表5.7.1 列的添加、修改
21、与删除5修改列的默认值MySQL中可以使用ALTERCOLUMN修改列的默认值、是否可见，语句格式如下：ALTERTABLEtbl_nameALTERCOLUMNcol_nameSETDEFAULTliteral|(expr)|SETVISIBLE|INVISIBLE|DROPDEFAULT5.7 修改表5.7.1 列的添加、修改与删除【例5.25】修改student表，为snation列设置默认值汉族。在MySQL命令行客户端输入命令：ALTERTABLEstudentALTERCOLUMNsnationSETDEFAULT汉族;5.7 修改表5.7.1 列的添加、修改与删除CHANGE、M
22、ODIFY、RENAMECOLUMN和ALTER子句允许对表中现有列进行更改，它们的区别为：lMODIFYCOLUMN子句只能改列的定义，不能改列的名称；lRENAMECOLUMN子句只能改列的名称，不能改列的定义；lMODIFYCOLUMN子句可以重命名列，也可以更改列定义，一般用于同时更改列名和列定义。如果只改列名，使用RENAMECOLUMN子句更简单。如果只改列的定义，使用MODIFYCOLUMN子句更方便。lALTERCOLUMN子句仅用于更改列的默认值和可见性。5.7 修改表5.7.1 列的添加、修改与删除6删除列MySQL中删除列的语句格式如下：ALTERTABLEtbl_nam
23、eDROPCOLUMNcol_name【例5.26】修改student表，删除smobilephone列。在MySQL命令行客户端输入命令：ALTERTABLEstudentDROPCOLUMNsmobilephone;5.7 修改表5.7.2 约束的添加与删除1删除主键约束MySQL中可以通过下面的语句删除主键：ALTERTABLEtbl_nameDROPPRIMARYKEYl需要说明的是，如果要删除的主键被其他表中的外键引用，会删除失败，如下例所示。【例5.27】使用DROPPRIMARYKEY删除student表的主键。在MySQL命令行客户端输入命令：ALTERTABLEstudent
24、DROPPRIMARYKEY;5.7 修改表5.7.2 约束的添加与删除2删除唯一性约束lMySQL中唯一性约束实际上是通过唯一性索引实现的，创建唯一性约束时会自动创建一个唯一性索引。要删除唯一性约束，只需删除对应的唯一性索引即可。l删除唯一性约束的语句格式为：ALTERTABLEtbl_nameDROPINDEX|KEYindex_namel如果不清楚唯一性索引名，可以使用SHOWCREATETABLE语句查看表的定义，从中获取名称。5.7 修改表5.7.2 约束的添加与删除2删除唯一性约束【例5.29】在major表中，删除mname列设置的唯一性约束。输入命令：ALTERTABLEmaj
25、orDROPINDEXmname_UNIQUE;5.7 修改表5.7.2 约束的添加与删除3删除CHECK约束MySQL中删除CHECK约束的语句格式为：ALTERTABLEtbl_nameDROPCHECK|CONSTRAINTconstraint_name【例5.30】在course表中，删除cterm列设置的CHECK约束。在MySQL命令行客户端输入命令：ALTERTABLEcourseDROPCONSTRAINTch_cterm;5.7 修改表5.7.2 约束的添加与删除4删除外键约束MySQL中删除外键约束的语句格式为：ALTERTABLEtbl_nameDROPFOREIGNKE
26、Yconstraint_name【例5.31】在score表中，删除sno列设置的外键约束。在MySQL命令行客户端输入命令：ALTERTABLEscoreDROPFOREIGNKEYfk_sno;5.7 修改表5.7.3 修改表名MySQL中可以通过两种方法修改表名：(1)RENAMETABLEold_tbl_nameTOnew_tbl_name(2)ALTERTABLEtbl_nameRENAMETO|ASnew_tbl_name【例5.32】将student_bak重命名为student_backup。在MySQL命令行客户端输入命令：ALTERTABLEstudent_bakRENAM
27、ETOstudent_backup;5.7 修改表5.7.4 修改表的选项(1)修改表的存储引擎ALTERTABLEtbl_nameENGINE=engine_name(2)修改表的默认字符集ALTERTABLEtbl_nameDEFAULTCHARACTERSET=charset_name(3)修改表的修改表的加密选项ALTERTABLEtbl_nameENCRYPTION=Y|N5.7 修改表5.7.5 使用Workbench修改表打开WorkBench工具，连接到MySQL服务器。在左侧的“SCHEMAS”导航栏中找到jwgl数据库，单击左边的箭头，在下面的“Tables”节点下找到ma
28、jor表，单击鼠标右键，在出现的菜单中选择“AlterTable”，右侧将打开“major-Table”对话框的“Columns”选项卡，如图所示。这里可以添加列、删除列、修改现有列的名称、定义、添加、删除主键和唯一性约束。5.7 修改表5.7 修改表5.7.5 使用Workbench修改表选择“Indexes”选项卡，如图所示，可以对索引和键进行添加、修改和删除。在左侧列表框的“IndexName”处输入唯一性约束的名称“un_manme”，“Type”下拉列表中选择UNIQUE。右侧列表框中的“Column”处勾选mname列。5.7 修改表5.7 修改表5.7.5 使用Workbench
29、修改表选择“ForeignKeys”选项卡，如图所示，可以添加和删除外键约束。单击“Apply”按钮，完成修改。5.7 修改表5.8 删除表5.8.1使用命令删除表MySQL中删除表的语句格式为：DROPTEMPORARYTABLEIFEXISTStbl_name,tbl_name【例5.33】删除student_backup表。在MySQL命令行客户端输入命令：DROPTABLEstudent_backup;5.8 删除表5.8.2 使用使用Workbench删除表打开WorkBench工具，连接到MySQL服务器。在左侧的“SCHEMAS”导航栏中找到要删除的表，单击鼠标右键，在弹出的菜单中选择“DropTable”，如图所示。5.8 删除表5.8.2 使用使用Workbench删除表在弹出的窗口中，选择“DropNow”直接删除表，如图所示。5.9 本章小结表是关系数据库中最基本的对象，本章主要介绍了表的基础知识以及MySQL中对表结构进行管理的相关内容，主要包括创建表、修改表、删除表以及查看表。通过本章的学习，读者可以了解表的概念、表的结构、约束和数据完整性的概念，并且能够使用命令或图形化工具实现表结构的管理操作，为以后的学习打下良好的基础。