- MySQL入门很轻松(微课超值版)
- 云尚科技
- 159字
- 2021-03-29 01:04:54
第4章 数据表的创建与操作
本章内容提要
数据实际存储在数据表中,可见在数据库中,数据表是数据库中最重要、最基本的操作对象,是数据存储的基本单位。本章就来介绍数据表的创建与操作,包括创建数据表、修改数据表、查看数据表结构与删除数据表等。
本章知识点
- 数据表中存放的数据类型。
- 创建数据表。
- 查看数据表的结构。
- 修改数据表。
- 删除数据表。
4.1 数据表中能存放的数据类型
MySQL支持多种数据类型,大致可以分为三类,分别是数值类型、日期和时间类型、字符串(字符)类型。
4.1.1 数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、TINYINT、MEDIUMINT和BIGINT),近似数值数据类型(FLOAT、REAL和DOUBLE),以及定点数类型(DECIMAL)。
注意:关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
1. 整数类型
MySQL提供多种整数类型,不同的数据类型提供的取值范围不同,可以存储的值的范围越大,其所需要的存储空间也就越大,因此要根据实际需求选择适合的数据类型。表4-1显示了每个整数类型的存储需求和取值范围。
表4-1 MySQL中的整数型数据类型
MSQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如INT(4))。INT(M)中的M指示最大显示宽度,最大有效显示宽度是4,需要注意的是,显示宽度与存储大小或类型包含的值的范围无关。
例如,假设声明一个INT类型的字段:
id INT(4)
该声明指出,在id字段中的数据一般只显示4位数字的宽度。假如向id字段中插入数值10000,当使用select语句查询该列值的时候,MySQL显示的是完整的带有5位数字的10000,而不是4位数字。
注意:其他整数数据类型也可以在定义表结构时指定所需要的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值。另外,不同的整数类型的取值范围不同,所需的存储空间也不同,因此,在定义数据表的时候,要根据实际需求选择最合适的类型,这样做有利于节约存储空间,还有利于提高查询效率。
2. 浮点数类型
现实生活中很多情况需要存储带有小数部分的数值,这就需要浮点数类型,如FLOAT和DOUBLE。其中,FLOAT为单精度浮点数类型;DOUBLE为双精度浮点数类型。浮点数类型可以用(M,D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。表4-2显示了每个浮点数类型的存储需求和取值范围。
表4-2 MySQL中的浮点数数据类型
注意:M和D在FLOAT和DOUBLE中是可选的,FLOAT和DOUBLE类型将被保存为硬件所支持的最大精度。
3. 定点数类型
MySQL中,除使用浮点数类型表示小数外,还可以使用定点数表示小数,定点数类型只有一种:DECIMAL。定点数类型也可以用(M,D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。DECIMAL的默认D值为0,M值为10。表4-3显示了定点数类型的存储需求和取值范围。
DECIMAL类型不同于FLOAT和DECIMAL,DECIMAL实际是以字符串存储的。DECIMAL的有效取值范围由M和D的值决定。如果改变M而固定D,则其取值范围将随M的变大而变大。如果固定M而改变D,则其取值范围将随D的变大而变小(但精度增加)。由此可见,DECIMAL的存储空间并不是固定的,而是由其精度值M决定,占用M+2字节。
表4-3 MySQL中的定点数数据类型
4.1.2 日期和时间类型
MySQL中,表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。例如,只需记录年份信息时,可以只用YEAR类型,而没有必要使用DATE。每一种类型都有合法的取值范围,当插入不合法的值时,系统会将“零”值插入到字段中。表4-4列出了MySQL中的日期和时间类型。
表4-4 日期和时间数据类型
4.1.3 字符串类型
字符串类型用于存储字符串数据,MySQL支持两类字符串数据:文本字符串和二进制字符串。文本字符串可以进行区分或不区分大小写的串比较,也可以进行模式匹配查找。MySQL中字符串类型指的是CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM和SET。表4-5列出了MySQL中的字符串数据类型。
表4-5 MySQL中字符串数据类型
注意:VARCHAR和TEXT类型是变长类型,它们的存储需求取决于值的实际长度(表格中用L表示),而不是取决于类型的最大可能长度。例如,一个VARCHAR(10)字段能保存最大长度为10个字符的一个字符串,实际的存储需求是字符串的长度L,加上1字节以记录字符串的长度。例如,字符串“teacher”,L是7,而存储需求是8字节。
另外,MySQL提供了大量的数据类型,为了优化存储,提高数据库性能,在不同情况下应使用最精确的类型。当需要选择数据类型时,在可以表示该字段值的所有类型中,应当使用占用存储空间最少的数据类型。因为这样不仅可以减少存储(内存、磁盘)空间,还可以在数据计算时减轻CPU的负载。
4.1.4 选择数据类型
MySQL提供了大量的数据类型,为了优化存储,提高数据库性能,在任何情况下均应使用最精确的类型,即在所有可以表示该列值的类型中,该类型使用的存储最少。
1. 整数和浮点数
如果不需要小数部分,则使用整数来保存数据;如果需要表示小数部分,则使用浮点数类型。对于浮点数据列,存入的数值会对该列定义的小数位进行四舍五入。例如,如果列的值的范围为1~99999,若使用整数,则MEDIUMINT UNSIGNED是最好的类型;若需要存储小数,则使用FLOAT类型。
浮点数类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高,因此,如要求存储精度较高时,应选择DOUBLE类型。
2. 浮点数和定点数
浮点数FLOAT和DOUBLE相对于定点数DECIMAL的优势是:在长度一定的情况下,浮点数能表示更大的数据范围。但是由于浮点数容易产生误差,因此对精确度要求比较高时,建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的,用于定义货币等对精确度要求较高的数据。在数据迁移中,FLOAT(M,D)是非标准SQL定义,数据库迁移可能会出现问题,最好不要这样使用。另外,两个浮点数进行减法和比较运算时也容易出问题,因此在进行计算的时候,一定要小心。如果进行数值比较,最好使用DECIMAL类型。
3. 日期和时间类型
MySQL对于不同种类的日期和时间有很多的数据类型,比如YEAR和TIME。如果只需要记录年份,则使用YEAR类型即可;如果只记录时间,只需使用TIME类型。
如果同时需要记录日期和时间,则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP列的取值范围小于DATETIME的取值范围,因此存储范围较大的日期最好使用DATETIME。
TIMESTAMP也有一个DATETIME不具备的属性。默认的情况下,当插入一条记录但并没有指定TIMESTAMP这个列值时,MySQL会把TIMESTAMP列设为当前的时间。因此当需要在插入记录的同时插入当前时间时,使用TIMESTAMP是方便的,另外TIMESTAMP在空间上比DATETIME更有效。
4. CHAR与VARCHAR之间的特点与选择
CHAR和VARCHAR的区别如下:
(1)CHAR是固定长度字符,VARCHAR是可变长度字符;
(2)CHAR会自动删除插入数据的尾部空格,VARCHAR不会删除尾部空格;
(3)CHAR是固定长度,所以它的处理速度比VARCHAR的速度要快,但是它的缺点就是浪费存储空间。所以对存储不大,但在速度上有要求的可以使用CHAR类型;反之,可以使用VARCHAR类型来实现。
存储引擎对于选择CHAR和VARCHAR的影响:
(1)对于MyISAM存储引擎:最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化,从而使数据检索更快,用空间换时间。
(2)对于InnoDB存储引擎:使用可变长度的数据列,因为InnoDB数据表的存储格式不分固定长度和可变长度,因此使用CHAR不一定比使用VARCHAR更好,但由于VARCHAR是按照实际的长度存储,比较节省空间,所以对磁盘I/O和数据存储总量比较好。