MySQL数据库原理与应用(微课版)ch05数据表操作

第5章

数据表操作本章概述在数据库中，针对数据表对象的操作有，创建数据表、查看表结构、修改表结构、重命名数据表、复制数据表和删除数据表。在创建数据表时，需要为表字段设置所存储数据的类型、约束，还可以为数据表对象设置索引，以提高数据表检索效率。本章学习目标掌握数据表操作，包括创建数据表、查看表结构、修改表结构、重命名数据表、复制数据表和删除数据表。熟悉数据类型并能够为字段选择恰当的数据类型。重点掌握数字类型、字符串类型、日期时间类型。掌握表约束操作，能够恰当地为数据表设置表约束。掌握索引操作，能够对数据表设置合适的索引，以提高数据检索效率。目录01020304表约束操作数据类型索引操作数据表基本操作05本章小结目录01020304表约束操作数据类型索引操作数据表基本操作05本章小结关系数据库基础5.1.1创建数据表创建数据表，使用CREATETABLE语句，语法如下。CREATE[TEMPORARY]TABLE[IFNOTEXISTS]数据表名[(create_definition,…)][table_options][select_statement]关系数据库基础5.1.2查看数据表使用SHOWCOLUMNS语句查看数据表结构：SHOW[FULL]COLUMNSFROM数据表名[FROM数据库名];或SHOW[FULL]COLUMNSFROM数据表名.数据库名;关系数据库基础5.1.3复制数据表在一个已经存在的数据表的基础上创建该表的备份：CREATETABLE[IFNOTEXISTS]数据表名{LIKE源数据表名|(LIKE源数据表名)}关系数据库基础5.1.4修改表结构可以使用ALTERTABLE语句来实现，语法格式如下。ALTER[IGNORE]TABLE数据表名alter_spec[,alter_spec]…|table_options关系数据库基础5.1.5重命名数据表重命名数据表可以使用RENAMETABLE语句来实现：RENAMETABLE数据表名1To数据表名2例如：RENAMETABLEtb_admin_oldTOtb_admin_old1;关系数据库基础5.1.6删除数据表使用DROPTABLE语句即可删除数据表：DROPTABLE[IFEXISTS]数据表名;目录01020304表约束操作数据类型索引操作数据表基本操作05本章小结数据类型5.2.1数字类型：数字类型总体可以分成整数和浮点两种数据类型。整数类型：数据类型取值范围说明单位TINYINT符号值：-128~127，无符号值：0~255最小的整数1字节BIT符号值：-128~127，无符号值：0~255最小的整数1字节BOOL符号值：-128~127，无符号值：0~255最小的整数1字节SMALLINT符号值：-3276832767无符号值：0~65535小型整数2字节MEDIUMINT符号值：-8388608~8388607无符号值：0~16777215中型整数3字节INT符号值：-2147683648~2147683647无符号值：0~4294967295标准整数4字节BIGINT符号值：-9223372036854775808~9223372036854775807无符号值：0~18446744073709551615大型整数8字节数据类型5.2.1数字类型：数字类型总体可以分成整数和浮点两种数据类型。浮点类型：数据类型取值范围说明单位FLOAT+(-)3.402823466E+38单精度浮点数8或4字节DOUBLE+(-)1.7976931348623157E+308双精度浮点数8字节DECIMAL+(-)2.2250738585072014E-308一般整数自定义长度数据类型5.2.2字符串类型字符串类型包括3类：普通文本字符串类型（CHAR和VARCHAR）、可变类型（TEXT和BLOB）和特殊类型（SET和ENUM）。数据类型5.2.2字符串类型普通文本字符串类型（CHAR和VARCHAR）数据类型取值范围说明NationalCHAR(M)[binary|ASCII|unicode]0~255个字符固定长度为M的字符串，其中M的取值范围为0~255。national关键字指定了应该使用的默认字符集。binary关键字指定了数据是否区分大小写(默认是区分大小写的)。ASCII关键字指定了在列中使用latin1字符。unicode关键字指定了使用UCS字符集CHAR1~255个字符与CHAR(M)类似[national]VARCHAR(M)[binary]0~255个字符长度可变，其他与CHAR(M)类似数据类型5.2.2字符串类型可变类型数据类型最大长度（字节数）说明TINYBLOB2^8-1(255)小BLOB字段TINYTEXT2^8-1(255)小TEXT字段BLOB2^16-1(65535)常规BLOB字段TEXT2^16-1(65535)常规TEXT字段MEDIUMBLOB2^24-1(16777215)中型BLOB字段MEDIUMTEXT2^24-1(16777215)中型TEXT字段LONGBLOB2^32-1(4294967295)长BLOB字段LONGTEXT2^32-1(4294967295)长TEXT字段数据类型5.2.2字符串类型特殊类型类型最大值说明ENUM("valuel","value2",...)65535该类型的列只可以容纳所列值之一或为NULLSET("valuel","value2",...)64该类型的列可以容纳一组值或为NULL数据类型5.2.3日期和时间类型日期和时间类型包括DATE、DATETIME、TIME、TIMESTAMP和YEAR。其中每种类型都有其取值范围，如赋予它一个不合法的值，将会被“0”代替。日期和时间类型如表所示。类型取值范围说明DATE1000-01-01~9999-12-31日期，格式为YYYY-MM-DDTIME-838:58:59~835:59:59时间，格式为HH:MM:SSDATETIME1000-01-0100:00:00~9999-12-3123:59:59日期和时间，格式为YYYY-MMDDHH:MM:SSTIMESTAMP1970-01-0100:00:00~2037年的某个时间时间标签，在处理报告时使用显示格式取决于M的值YEAR1901~2155年份可指定两位数字和四位数字的格式数据类型5.2.4如何选择类型整数和浮点数浮点数和定点数日期与时间类型CHAR与VARCHAR之间的特点与选择ENUM和SETBLOB和TEXT目录01020304表约束操作数据类型索引操作数据表基本操作05本章小结表约束操作完整性约束条件是对字段进行限制的，要求用户对该属性进行的操作符合特定的要求。如果不满足完整性约束条件，数据库系统就不再执行用户的操作。MySQL中基本的完整性约束条件如表所示。类型取值范围PRIMARYKEY主键，可以唯一地标识对应的元组FOREIGNKEY外键，是与之联系的某表的主键NOTNULL字段值不能为空UNIQUE字段值唯一AUTOINCREMENT字段取值自动增加，这是MySOL特色DEFAULT设置默认值表约束操作当数据库表中的某个字段上的内容不希望设置为NULL时，则可以使用非空（NOTNULL）约束进行设置。CREATETABLEtablename(PropNamepropTypeNOTNULL,……);设置表字段的非空约束表约束操作当向数据表中插入一条新记录时，如果没有为某个字段赋值，那么数据库系统会自动为这个字段插入默认值。CREATETABLEtablename(propNamepropTypeDEFAULTdefaultValue,……);设置表字段的默认值表约束操作当数据库表中某个字段上的内容不允许重复时，可以使用唯一（UNIQVE，UK）约束进行设置。CREATETABLEtablename(propNamepropTypeUNIQUE,……);设置表字段唯一约束（UNIQUE，UK）表约束操作当数据库表中某个字段上的内容不允许重复时，可以使用唯一（UNIQVE，UK）约束进行设置。CREATETABELtablename(propNamepropTypePRIMARYKEY,……);设置表字段的主键约束表约束操作AUTO_INCREMENT是MySQL唯一扩展的完整性约束，当为数据库表中插入新记录时，字段上的值会自动生成唯一的ID。CREATETABLEtablename(propNamepropTypeAUTO_INCREMENT,……);设置表字段值自动增加表约束操作外键（FOREIGNKEY，FK）是表的一个特殊字段，外键约束用于保证多个表（通常为两个表）之间的参照完整性，即构建与两个表的字段之间的参照关系。CREATETABLEtablename_1(propName1_1propType1_1,propName1_2propType1_2,……CONSTRAINTFK_propFOREIGNKEY(propName1_1)REFERENCEStablename_2(propName2_1));设置表字段的外键约束目录01020304表约束操作数据类型索引操作数据表基本操作05本章小结索引操作普通索引唯一性索引单列索引多列索引空间索引索引概述索引操作在建立数据表时可以直接创建索引，这种方式比较直接，且方便、易用。在建立数据表时创建索引的基本语法结构如下：CREATETABLEtable_name(属性名数据类型[约束条件],属性名数据类型[约束条件]…属性名数据类型[UNIQUE|FULLTEXT|SPATIAL]INDEX|KEY[别名](属性名1[(长度)][ASC|DESC]));创建索引索引操作在MySQL中，不但可以在创建数据表时创建索引，也可以直接在已经创建的表中，在已经存在的一个或几个字段中创建索引。语法结构如下：CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(属性[(length)][ASC|DESC]);创建索引索引操作删除索引可以通过DROP语句来实现，语法结构如下：DROPINDEXindex_nameONtable_name;删除索引目录01020304表约束操作数据类型索引操作数据表基本操作05本章小结本章小结本章主要介绍了MySQL数