MySQL学习笔记1（mysql数据类型）

Mysql学习笔记1

Mysql数据类型

1：整数类型，浮点数类型，定点数类型；
2：日期与时间类型；
3：字符串类型；
4：二进制类型；

浮点数类型和定点数类型
浮点数类型包括单精度浮点型（float型）和双精度浮点型（double型）。定点数类型就是（decimal型）

Float（6，2）表示数据为float型，数据长度为6位，保留2位小数。如果插入的值的精度高于实际定义的精度进行四舍五入。

DECIMAL型默认为整数。

日期与时间类型
YEAR类型
TIME类型
DATE类型
DATETIME类型
TIMESTAMP类型

YEAR类型
YEAR类型使用1个字节表示年份，MYSQL中使用YYYY形式显示YEAR类型的值。
按YEAR类型赋值方法如下：
1：使用4位字符串或数字表示，范围是1901到2155。输入格式是’YYYY’或者YYYY。例如，输入’2008’或者2008，可以直接保存为2008。超出范围就会插入0000。
INSERT INTO year VALUES(1997),(‘1998’),(‘1900’);

2：使用2位字符串表示。’00’到’69’转换为2000到2069。’70’到‘99’转换为1970到1999。插入’0’和’00’都表示2000。

3：使用2位数字表示。1到69转换为2001到2069。70到99转换为1970到1999。2位数字与2位字符串表示是不一样的。插入0，转换后的是0000不是2000。

TIME类型
TIME类型使用3个字节表示时间。MySQL中以HH:MM:SS形式显示TIME类型的值。TIME类型的范围可以从’-838:59:59’到’838:59:59’。
1：’D HH:MM:SS’格式的字符串表示。D表示天数，范围是’0-34’，保存时小时的值等于（D*24+HH）。
INSERT INTO time VALUES(‘2 23:50:50’),(’22:22:22’),(’11:11’),(‘2 20:20’),(‘2 20’),(‘30’)

2：’HHMMSS’格式的字符串或者数字表示。例如，输入’345454’,和345454。TIME类型会转换为34:54:54。如果输入0或者’0’，会转换为0000 00 00。
3：使用CURRENT_TIME或者NOW()输入当前系统时间。

DATE类型
DATE类型使用4个字节表示日期。MySQL中以YYYY-MM-DD的形式显示DATE类型的值。其范围从’1000-00-00’到’9999-12-31’。给DATE类型的字段复制如下：
1：’YYYY-MM-DD’和’YYYYMMDD’格式的字符串表示。例如输入’4008-2-8’DATE类型将转换为4008-02-08，输入’20220308’将转换为2022-03-08。
MySQL还支持一些不严格的语法格式，任何标点都可以用作间隔符。如’YYYY@MM/DD’。

2：’YY-MM-DD’或者’YYMMDD’的字符串表示。其中YY的取值，’00’到’69’转换为2000到2069。’70’到’99’转换为1970到1999。

3：YYYYMMDD或者YYMMDD格式的数字表示。

4：使用CURRENT_DATE或者NOW()来输入当前系统日期。

DATETIME类型
DATETIME类型使用8个字节表示日期时间。MySQl中以’YYYY-MM-DD HH:MM:SS’的形式显示DATETIME类型的值。
1：’YYYY-MM-DD HH:MM:SS’或者’YYYYMMDDHHMMSS’格式的字符串表示。这种方式可以表示的范围是‘1000-01-01 00:00:00’到’9999-12-31 23:59:59’。

2：’YY-MM-DD HH:MM:SS’或者’YYMMDDHHMMSS’格式的字符串表示。

3：YYYYMMDDHHMMSS或者YYMMDDHHMMSS的数字表示。

4：使用NOW()来输入当前系统日期时间。

DATESTAMP类型
DATESTAMP类型使用4个字节来表示日期和时间。范围是从1970-01-01 08:00:01到2038-01-19 11:14:07。

TIMESTAMP与DATETIME类型几种不同的形式，内容如下：
1：使用CURRENT_TIMESTAMP来输入系统当前日期和时间。
2：输入NULL时，系统会输入系统当前日期和时间。
3：无任何输入时，系统会输入系统当前日期和时间。

字符串类型
字符串类型是在数据库中存储字符串的数据类型。其类型包括：
CHAR类型VARCHAR类型
TEXT类型
ENUM类型
SET类型

CHAR类型VARCHAR类型
CHAR类型VARCHAR类型都是在创建表时指定了最大长度。基本形式为：字符串类型（M）
例如，CHAR(4)就是指数据类型为CHAR类型，其长度为4。
CHAR类型的长度是固定的。在创建表时就指定了，长度可以是0-255之间的任何数。
VARCHAR类型的长度是可变的。在创建表时指定了最大长度，最大长度值可以取0-65535之间的任何值。VARCHAR类型实际占用空间为字符串的实际长度加1。不是每条记录都占用最大空间长度。
如果插入字符串’123 ’，CAHR会将123后的空格自动去掉，而VARCHAR会保留空格。

TEXT类型
TEXT类型是一种特殊的字符串类型。TEXT类型只能保存字符数据，如文章。TEXT类型包括TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。
各种TEXT类型的对比
类型 允许的长度 存储空间
TINYTEXT 0-255字节 值的长度+2字节
TEXT 0-65535 +2字节
MEDIUMTEXT 0-167772650 +3
LONGTEXT 0-4294967295 +4

CREATE TABLE string(a CAHR(10),b VARCHAR(20),c TEXT);

ENUM类型
ENUM类型是枚举类型，其基本形式为
属性名 ENUM（’值1’,’值2’,…, ‘值n’）这些值末尾的空格会被系统直接删除。

ENUM类型中的值只能取列表中的一个元素。其取值列表中最多能有65535个值。列表中每个值都有一个顺序排列的编号，MySQL中存入的是这个编号而不是这个值。

如果ENUM类型加上NOT NULL属性，其默认值为取值列表的第一个元素。如果不加NOT NULL属性，ENUM类型允许插入NULL，而且NULL为默认值。

SET类型
SET类型其基本形式如下
属性名 SET（’值1’,’值2’,…, ‘值n’） 这些值末尾的空格会被系统直接删除。
SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能有64个元素构成的组合。列表中每个值都有一个顺序的编号，MySQL中存入的是这个编号而不是这个值。

插入记录时，SET字段里的元素顺序无关紧要，存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。
如定义SET类型取值类表为(’A’,’B’,’C’,’D’,’E’,’F’）,插入值为（’C,B,D’）。显示为BCD

如何选择数据类型

在MySQL中创建表时，需要考虑为字段选择哪种数据类型是最合适。选择了合适的数据类型，会提高数据库的效率。

1．整数类型和浮点数类型
整数类型和浮点数类型最大的区别是能否表达小数。最常用的整数类型是INT型。浮点数类型包括FLOAT类型和DOUBLE类型，如果需要精确到小数后10位以上，就应该选择DOUBLE类型。

2．浮点数类型和定点数类型
对于浮点数和定点数，当插入值的精度高于实际定义的精度，系统会自动四舍五入处理。其目的是为了使该值的精度达到要求。浮点数进行四舍五入不会报警，定点数会报警。
在MySQL中定点数精度比浮点数更高。而且浮点数会出现误差，如果对数据精度要求比较高，应该选择定点数。

3．CHAR类型和VARCHAR类型
CHAR类型长度是固定的，VARCHAR类型长度是在范围内可变的。因此，VARCHAR类型比CHAR类型占用的空间少，且更加灵活。对于长度变化比较大的字符串类型，建议选择VARCHAR类型。
CHAR类型的处理速度比VARCHAR类型要快。因此，对于长度变化不大和查询速度要求较高的字符串类型，最好选择CHAR类型。

4．日期和时间类型
如果需要记录日期和时间，可以选择DATETIME类型和TIMESTAMP类型。DATETIME类型表示的范围比TIMESTAMP类型大。TIMESTAMP类型时根据时区来显示的，如果需要显示时区和时间的对应应选TIMESTAMP类型。

5．ENUM类型和SET类型
ENUM类型最多可以有65535个成员。而SET类型最多包括64个成员。ENUM类型只能从成员中选取一个，SET类型可以选择多个。

6．TEXT类型和BLOB类型
TEXT类型和BLOB类型很相似。TEXT类型只能存储字符数据，而BLOB类型可以存储二进制数据。如果存储文章等纯文本数据，选择TEXT类型；如果存储图片等二进制文件，选择BLOB类型。TEXT类型包括TINYTEXT、TEXT、MEDIUMTEX、 LONGTEXT。BLOB类型也是。

常见问题与解答

1． MySQL中什么数据类型能够存储路径?
MySQL中字符串类型都能存储路径，但是如果路径中使用”\”符号时，这个符号会被过滤。解决办法是用“/”或者“\\”代替”\”。
2． MySQL中如何使用布尔类型?
在SQL标准中，存在BOOL和BOOLEAN类型。MySQL也可以定义BOOL和BOOLEAN类型。但是BOOL和BOOLEAN类型最后转换的是TINYINT(1)类型。因此，定义表时将一个字段成BOOL和BOOLEAN类型。数据库中真实定义的是TINYINT(1)。
3． MySQL中存储GPJ图片和MP3音乐?
一般情况下，数据库中不直接存储图片和音频文件。而是存储图片和音频文件的路径。如果实在需要在MySQL数据库中存储图片和音频文件。选择BOOL类型。

常用属性

上面大概总结了MySQL中的数据类型和如何选择数据类型，当然了，上面的总结肯定是不全面的，如果要非常全面的总结这些内容，好几篇文章都不够的。下面就再来总结一些常用的属性。
1.auto_increment
auto_increment能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插入的行赋值为上一次插入的ID+1。
MySQL要求将auto_increment属性用于作为主键的列。此外，每个表只允许有一个auto_increment列。例如：
代码如下:

id smallint not null auto_increment primary key

2.binary
binary属性只用于char和varchar值。当为列指定了该属性时，将以区分大小写的方式排序。与之相反，忽略binary属性时，将使用不区分大小写的方式排序。例如：
代码如下:

hostname char(25) binary not null

3.default
default属性确保在没有任何值可用的情况下，赋予某个常量值，这个值必须是常量，因为MySQL不允许插入函数或表达式值。此外，此属性无法用于BLOB或TEXT列。如果已经为此列指定了NULL属性，没有指定默认值时默认值将为NULL，否则默认值将依赖于字段的数据类型。例如：
代码如下:

subscribed enum('0', '1') not null default '0'

4.index
如果所有其他因素都相同，要加速数据库查询，使用索引通常是最重要的一个步骤。索引一个列会为该列创建一个有序的键数组，每个键指向其相应的表行。以后针对输入条件可以搜索这个有序的键数组，与搜索整个未索引的表相比，这将在性能方面得到极大的提升。
代码如下:

create table employees(id varchar(9) not null,firstname varchar(15) not null,lastname varchar(25) not null,email varchar(45) not null,phone varchar(10) not null,index lastname(lastname),primary key(id));

我们也可以利用MySQL的create index命令在创建表之后增加索引：
复制代码 代码如下:

create index lastname on employees (lastname(7));

这一次只索引了名字的前7个字符，因为可能不需要其它字母来区分不同的名字。因为使用较小的索引时性能更好，所以应当在实践中尽量使用小的索引。
5.not null
如果将一个列定义为not null，将不允许向该列插入null值。建议在重要情况下始终使用not null属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值。
6.null
为列指定null属性时，该列可以保持为空，而不论行中其它列是否已经被填充。记住，null精确的说法是“无”，而不是空字符串或0。
7.primary key
primary key属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能为空。为指定为主键的列赋予auto_increment属性是很常见的，因为此列不必与行数据有任何关系，而只是作为一个唯一标识符。主键又分为以下两种：
(1)单字段主键
如果输入到数据库中的每行都已经有不可修改的唯一标识符，一般会使用单字段主键。注意，此主键一旦设置就不能再修改。
(2)多字段主键
如果记录中任何一个字段都不可能保证唯一性，就可以使用多字段主键。这时，多个字段联合起来确保唯一性。如果出现这种情况，指定一个auto_increment整数作为主键是更好的办法。
8.unique
被赋予unique属性的列将确保所有值都有不同的值，只是null值可以重复。一般会指定一个列为unique，以确保该列的所有值都不同。例如：
复制代码 代码如下:

email varchar(45) unique

9.zerofill
zerofill属性可用于任何数值类型，用0填充所有剩余字段空间。例如，无符号int的默认宽度是10；因此，当“零填充”的int值为4时，将表示它为0000000004。例如：
复制代码 代码如下:

orderid int unsigned zerofill not null