第5章 数据类型、表的操作和存储引擎

第5章数据类型、表的操作和存储引擎学习目标：了解信息、数据和数据管理的基本概念，了解数据管理技术的发展历史，理解数据库系统的基本概念，理解概念模型、逻辑模型的相关概念，理解数据库的体系结构。5.1数据类型5.1数据类型5.1.1数值类型1．整数类型及其取值范围5.1数据类型2．浮点数类型和定点数类型及其取值范围5.1数据类型3．数值类型的选择在选择数值类型时，应遵循如下原则。①选择最小的可用类型。②对完全都是数字的，即无小数点，可以选择整数类型，如整数年龄。③浮点数类型用于有小数部分的数，如带小数的成绩。④在MySQL中，定点数类型以字符串形式存储，因此，其精度比浮点数类型要高。如果对数据的精度要求比较高，就要选择定点数类型DECIMAL。⑤不论是定点数类型还是浮点数类型，如果用户指定的精度值超过精度范围，则会进行四舍五入的处理。5.1数据类型5.1.2字符串类型1．字符串类型及其取值范围5.1数据类型2．字符串类型的选择①从速度方面考虑，要选择固定的列，可以使用CHAR[(size)]型；②要节省空间，使用可变长度的列，可以使用VARCHAR[(size)]型；③要搜索的内容不区分大小写，可以使用TEXT[(size)]型；④要搜索的内容区分大小写，可以使用二进制类型。5.1数据类型5.1.3日期和时间类型5.1数据类型5.1.4二进制类型MySQL支持两类字符串：文本字符串和二进制字符串。二进制字符串类型也称为二进制类型，MySQL中的二进制类型有BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB类型。由于在实际中很少使用二进制类型，故不进行详细介绍。5.1.5复合类型MySQL还支持两种复合（数据）类型：SET和ENUM。它们是基于字符串的集合类型，但是可以被视为不同的数据类型。1．SET（集合）型set_nameSET('value1','value2',…,'valuen')例如，将一个人的兴趣爱好hobby用SET型表示，SQL语句如下：hobbySET('看电影','听音乐','旅游','购物','打游戏')5.1数据类型2．ENUM（枚举）型set_nameENUM('value1','value2',…,'valuen')例如，sex列用ENUM型表示如下：sexENUM('男','女','未知')5.1数据类型5.2表的操作5.2.1创建表CREATE[TEMPORARY]TABLE[db_name.]tb_name(column_definition1[列级完整性约束条件1,][column_definition2[列级完整性约束条件2],][…,][column_definitionN[列级完整性约束条件N],][表级完整性约束条件])[table_option];5.2表的操作列的定义包括列名、数据类型、指定默认值、注释列名等属性组成，各项之间用空格分隔。格式如下：column_namedata_type[DEFAULTdefault_value][AUTO_INCREMENT][COMMENT'String']…able_option：对表的操作，包括存储引擎、默认字符集、校对规则等，各项之间用空格分隔。格式如下：[ENGINE=engine_name][DEFAULTCHARSET=characterset_name][COLLATE=collation_name]5.2表的操作【例5-1】在数据库student_db中，创建班级表class，该表的定义说明见表5-6。要求对表使用InnoDB存储引擎，设置该表的字符集为utf8mb4。5.2表的操作USEstudent_db;CREATETABLEclass(ClassIDCHAR(8)PRIMARYKEYCOMMENT'班级编号',ClassNameVARCHAR(20)NOTNULLCOMMENT'班级名称',DepartmentNameVARCHAR(20)NOTNULL,GradeCHAR(4)NOTNULL,ClassNumSMALLINT)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;5.2表的操作【例5-2】创建临时表temp_table1。SQL语句如下：CREATETEMPORARYTABLEtemp_table1(IDINTNOTNULL,NameVARCHAR(10),AgeINT);5.2表的操作5.2表的操作5.2表的操作5.2.2查看表1．查看表的名称列表SHOWTABLES[{FROM|IN}db_name];【例5-3】查看全部表的名称。USEstudent_db;SHOWTABLES;5.2表的操作2．查看表的基本结构SHOWCOLUMNS{FROM|IN}tb_name[{FROM|IN}db_name];{DESCRIBE|DESC}tb_name;5.2表的操作【例5-4】查看class表的结构。USEstudent_db;SHOWCOLUMNSFROMclass;或DESCclass;5.2表的操作5.2表的操作3．查看表的详细结构SHOWCREATETABLEtb_name;5.2表的操作【例5-5】查看class表的详细结构。SHOWCREATETABLEclass;5.2表的操作5.2.3修改数据表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;5.2表的操作1．添加新列【例5-6】向class表中添加班主任HeadTeacher列，数据类型是VARCHAR(20)，将该列添加到DepartmentName列后；添加入学日期EntryDate列，将该列添加到最后。ALTERTABLEclassADDCOLUMNHeadTeacherVARCHAR(20)AFTERDepartmentName;ALTERTABLEclassADDEntryDateDATE;5.2表的操作2．修改列的数据类型、约束【例5-7】把class表中ClassName列的数据类型宽度改为VARCHAR(10)，约束为NOTNULL。ALTERTABLEclassMODIFYClassNameVARCHAR(10)NOTNULL;DESCclass;5.2表的操作3．改变列名【例5-8】把class表中的班主任HeadTeacher列名改为班长Monitor列，数据类型和宽度不变；ClassNum改为Number，数据类型改为TINYINT。ALTERTABLEclassCHANGEHeadTeacherMonitorVARCHAR(20),CHANGEClassNumNumberTINYINT;5.2表的操作4．删除列【例5-9】把class表中的Monitor列和EntryDate列删除。ALTERTABLEclassDROPMonitor,DROPEntryDate;5.2表的操作5．重命名表名ALTERTABLEtb_nameRENAME[{AS|TO}]new_tb_name;RENAMETABLEtb_name1TOnew_tb_name1[,tb_name2TOnew_tb_name2]…;5.2表的操作【例5-10】分别使用上面两种语句，修改class表名，先把class表名改为cla，然后再改回class。ALTERTABLEclassRENAMETOcla;SHOWTABLES;RENAMETABLEclaTOclass;SHOWTABLES;5.2表的操作5.2.4复制表结构CREATETABLE[IFNOTEXISTS]new_table_nameLIKEold_table_name;例如，复制class表的结构，新表名为class1，SQL语句如下：CREATETABLEIFNOTEXISTSclass1LIKEclass;5.2表的操作5.2.5删除表DROPTABLE[IFEXISTS]tb_name1[,tb_name2]…;【例5-11】删除class表和class1表。SQL语句如下：DROPTABLEIFEXISTSclass;DROPTABLEIFEXISTSclass1;SHOWTABLES;5.2表的操作5.2.6使用NavicatforMySQL操作表1．创建表【例5-12】创建学生表student，student表的定义说明见表5-7。5.2表的操作5.2表的操作5.2表的操作5.2表的操作2．查看和修改表结构【例5-13】查看和修改student表的结构。5.2表的操作3．使用NavicatforMySQL删除表【例5-14】删除student表。5.3MySQL的存储引擎5.3.1常用的存储引擎MySQL支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDBCluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED、solidDB等。5.3.2查看存储引擎1．查看默认的存储引擎SHOWVARIABLESLIKE'default_storage_engine';2．查看支持的存储引擎SHOWENGINES;5.3MySQL的存储引擎【例5-15】查看所安装MySQL版本默认的存储引擎和支持的存储引擎。SHOWVARIABLESLIKE'default_storage_engine';SHOWENGINES;5.3MySQL的存储引擎5.3.3设置存储引擎1．设置默认存储引擎例如，设置默认存储引擎为InnoDB，服务器参数如下：[mysqld]default-storage-engine=INNODB2．将已存在表的存储引擎修改成