一、数据类型
数据类型(data_type)是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。
数据库中的每个列都应该有适当的数据类型,用于限制或允许该列中存储的数据。例如,列中存储的为数字,则相应的数据类型应该为数值类型。
如果使用错误的数据类型可能会严重影响应用程序的功能和性能,所以在设计表时,应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事,这样做可能会导致数据丢失。因此,在创建表时必须为每个列设置正确的数据类型和长度。
MySQL 的数据类型有大概可以分为 5 种,分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。
注意:整数类型和浮点数类型可以统称为数值数据类型。
1) 数值类型
整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,浮点数类型包括 FLOAT 和 DOUBLE,定点数类型为 DECIMAL。
2) 日期/时间类型
包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
3) 字符串类型
包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。
4) 二进制类型
包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
定义字段的数据类型对数据库的优化是十分重要的。
1.1整数类型又称数值型数据,数值型数据类型主要用来存储数字。如:学生的成绩,年龄
MySQL 提供了多种数值型数据类型,不同的数据类型提供不同的取值范围,可以存储的值范围越大,所需的存储空间也会越大。MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。
从上表中可以看到,不同类型的整数存储所需的字节数不相同,占用字节数最小的是 TINYINT 类型,占用字节最大的是 BIGINT 类型,占用的字节越多的类型所能表示的数值范围越大。
根据占用字节数可以求出每一种数据类型的取值范围。例如,TINYINT 需要 1 个字节(8bit)来存储,那么 TINYINT 无符号数的最大值为 28-1,即 255;TINYINT 有符号数的最大值为 27-1,即 127。其他类型的整数的取值范围计算方法相同,如下表所示。
其他整型数据类型也可以在定义表结构时指定所需的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值。不同的整数类型有不同的取值范围,并且需要不同的存储空间,因此应根据实际需要选择最合适的类型,这样有利于提高查询的效率和节省存储空间。
1.2小数类型
mysql中使用浮点数和定点数来表示小数。
浮点类型有两种,分别是单精度浮点数(FLOAT)和双精度浮点数(DOUBLE);定点类型只有一种,就是 DECIMAL。
浮点类型和定点类型都可以用(M, D)来表示,其中M称为精度,表示总共的位数;D称为标度,表示小数的位数。
浮点数类型的取值范围为 M(1~255)和 D(1~30,且不能大于 M-2),分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的,FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。
下表中列出了 MySQL 中的小数类型和存储需求。
FLOAT 和 DOUBLE 在不指定精度时,默认会按照实际的精度(由计算机硬件和操作系统决定),DECIMAL 如果不指定精度,默认为(10,0)。
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的范围;缺点是会引起精度问题。
提示:不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。
最后再强调一下:在 MySQL 中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币、科学数据),使用 DECIMAL 的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点数时需要注意,并尽量避免做浮点数比较。
1.3日期时间型
mysql中有多处表示日期的数据类型:YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候,可以只使用 YEAR 类型。
每一个类型都有合法的取值范围,当指定确定不合法的值时,系统将“零”值插入数据库中。
下表中列出了 MySQL 中的日期与时间类型。
YEAR 类型是一个单字节类型,用于表示年,在存储时只需要 1 个字节。可以使用各种格式指定 YEAR,如:以 4 位字符串或者 4 位数字格式表示的YEAR: 2018 2019
TIME 类型用于只需要时间信息的值,在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时,MM 表示分钟,SS 表示秒。
DATE 类型用于仅需要日期值时,没有时间部分,在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD',其中 YYYY 表示年,MM 表示月,DD 表示日。
DATETIME 类型用于需要同时包含日期和时间信息的值,在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH:MM:SS',其中 YYYY 表示年,MM 表示月,DD 表示日,HH 表示小时,MM 表示分钟,SS 表示秒。
TIMESTAMP 的显示格式与 DATETIME 相同,显示宽度固定在 19 个字符,日期格式为 YYYY-MM-DD HH:MM:SS,在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围,为 '1970-01-01 00:00:01'UTC~'2038-01-19 03:14:07'UTC。在插入数据时,要保证在合法的取值范围内。
TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外,还有一个最大的区别是:
DATETIME 在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;
而 TIMESTAMP 值的存储是以 UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。
1.4 字符串类型
Mysql中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
下表中列出了 MySQL 中的字符串数据类型,括号中的M表示可以为其指定长度。
VARCHAR 和 TEXT 类型是变长类型,其存储需求取决于列值的实际长度(在前面的表格中用 L 表示),而不是取决于类型的最大可能尺寸。
例如,一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串,实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”,L 是 4,而存储要求 5 个字节。
CHAR 和 VARCHAR 类型
CHAR(M) 为固定长度字符串,在定义时指定字符串列长。当保存时,在右侧填充空格以达到指定的长度。M 表示列的长度,范围是 0~255 个字符。
例如,CHAR(4) 定义了一个固定长度的字符串列,包含的字符个数最大为 4。当检索到 CHAR 值时,尾部的空格将被删除。
VARCHAR(M) 是长度可变的字符串,M 表示最大列的长度,M 的范围是 0~65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定,而实际占用的空间为字符串的实际长度加 1。
例如,VARCHAR(50) 定义了一个最大长度为 50 的字符串,如果插入的字符串只有 10 个字符,则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。
【实例】下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列,说明 CHAR 和 VARCHAR 之间的差别,如下表所示。
对比结果可以看到,CHAR(4) 定义了固定长度为 4 的列,无论存入的数据长度为多少,所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度加 1。
1.5 二进制类型
二进制字符串类型有时候也直接被称为“二进制类型”。
MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。
二、创建表
表是一种重要的数据库对象,存储数据库中所有的数据,如果把数据库比喻成菜鸟驿站,那表就像驿站中放置快递的架子,一个表就是一个实体集,表实质上是行和列的集合,每一行代表一条记录(元组),每一列代表记录的一个字段(属性)。每个表由若干行组成,表的第一行为各列标题,其余行都是数据。 每一列必须具有相同的数据类型,表的行数和总大小仅受可用空间的限制。
特殊的NULL(空)值
null值不是“0”,也不是空格,它是一个比较特殊的值。一般表示值是否为空。在进行数据类型定义的时侯,系统一般默认字段值允许为空值,是不会显示的,或者是在图形化操作界面创建表字段操作窗口会有“是否允许为空”的勾选项。如果勾选上了,表示允许值为空。否则不允许为空值。如果是使用命令行窗口,进行数据类型定义,不允许为空时,就要在该数据类型定义行,添加“not null”,还有一种情况,如果在某列上设置了主键,该列也不允许重复值,也不允许出现空值。
表的创建有三个要素:字段名(属性),数据类型(字符串类型、日期时间型、整型),数据特征(是否为空,有没有主键)
1、创建表的方法有两种
第一种:图形化操作:连接服务器后,单击选定数据库展开目录,选择表,在弹出的快捷菜单中选择“新建表”命令,或者使用工具栏上的图形化按钮,均可以进行数据库的创建(见视频)
在弹出的表设计器中,输入字段名,数据类型,设置字段是否允许为空等。即完成表结构的设计。接下来可向表中添加数据。
第二种:使用SQL语句进行表的创建
表的创建:create table 表名(字段名1 数据类型(数据长度) 数据特征,字段名2 数据类型(数据长度) 数据特征,字段名3 数据类型(数据长度) 数据特征);
例如:创建一个班级表grade,表结构定义如下:
字段名 数据类型 长度 是否为空
BID(班级代码) char 6 否
BNAME(班级名称) varchar 20 否
PID系部代码 char 4 否
创建该表的SQL语句为
Create table grade(bid char(6) not null,
Bname varchar(20) not null,
Pid char(4) not null);
扩展:创建表时指定存储引擎和字符集