MySQL学习笔记2：数据类型与操作数据表

最新推荐文章于 2022-10-25 16:19:45 发布

快递小可

最新推荐文章于 2022-10-25 16:19:45 发布

阅读量1k

点赞数 1

分类专栏： MySQL 文章标签： MySQL各种数据类型创建查看数据表空值与非空主键唯一约束默认约束

本文链接：https://blog.csdn.net/sxingming/article/details/52624144

版权

MySQL 专栏收录该内容

26 篇文章 1 订阅

订阅专栏

【1】数据类型是指列，存储过程参数，表达式和局部变量的数据特征，它决定了数据的存储格式，代表了不同的信息类型。

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

1.1》整型及其存储范围，如图：

MySQL支持在类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。
该可选显示宽度规定当显示宽度小于指定的列宽度的值时从左侧填满宽度。
当结合可选扩展属性ZEROFILL使用时，默认补充的空格用零代替。例如，对于声明为INT(5) ZEROFILL的列，值4检索为00004。
所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。

如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性。

1.2》浮点型及其存储范围，如图：

其中,FLOAT类型比较常用。

浮点和定点类型也可以为UNSIGNED。该属性防止负值保存到列中。然而，与整数类型不同的是，列值的上范围保持不变。
对于浮点列类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节。
MySQL允许使用非标准语法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里，“(M,D)”表示该值一共显示M位整数，
其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，
因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。

为了保证最大可能的可移植性，需要使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION，不规定精度或位数。
DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值，例如货币数据。当声明该类型的列时，可以(并且通常要)指定精度和标度；
例如：
salary DECIMAL(5,2) 在该例子中，5是精度，2是标度。精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。
在MySQL 5.1中以二进制格式保存DECIMAL和NUMERIC值。
标准SQL要求salary列能够用5位整数位和两位小数保存任何值。因此，在这种情况下可以保存在salary列的值的范围是从-999.99到999.99。
在标准SQL中，语法DECIMAL(M)等价于DECIMAL(M,0)。同样，语法NUMERIC(M)等价于NUMERIC(M,0)，可以通过计算确定M的值。
在MySQL 5.1中支持DECIMAL和NUMERIC数据类型的变量形式。M默认值是10。
DECIMAL或NUMERIC的最大位数是65，但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。
如果此类列分配的值小数点后面的位数超过指定的标度允许的范围，值被转换为该标度。(具体操作与操作系统有关，但一般结果均被截取到允许的位数）。

1.3》日期时间型，如图：

时间类型包含DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时，使用“零”值。

当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时，它将该值转换为该类的“零”值。一个例外是超出范围的TIME值被裁剪到TIME范围的相应端点。

下面的表显示了各类“零”值的格式。请注意如果启用NO_ZERO_DATE SQL模式，使用这些值会产生警告。

列类型	“零”值
DATETIME	'0000-00-00 00:00:00'
DATE	'0000-00-00'
TIMESTAMP	00000000000000
TIME	'00:00:00'
YEAR	0000

“零”值是特殊值，但你可以使用表内显示的值显式保存或引用它们。你也可以使用值'0'或0来保存或引用，写起来更容易。

DATETIME、DATE和TIMESTAMP类型是相关的。

当你需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。(“支持”表示尽管先前的值可能工作，但没有保证)。

当你只需要日期值而不需要时间部分时应使用DATE类型。MySQL用'YYYY-MM-DD'格式检索和显示DATE值。支持的范围是'1000-01-01'到 '9999-12-31'。

TIMESTAMP列的显示格式与DATETIME列相同。换句话说，显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。

TIMESTAMP类型：支持范围：1970年1月1号0点，到，2037年之间的一个值。

timestamp 类型的列有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。

timestamp比较特殊，如果定义一个字段的类型为timestamp，这个字段的时间会在其他字段修改的时候自动刷新。所以这个数据类型的字段可以存放这条记录最后被修改的时间，而不是真正的存放时间。

TIME类型：

MySQL以'HH:MM:SS'格式检索和显示TIME值(或对于大的小时值采用'HHH:MM:SS'格式)。TIME值的范围可以从'-838:59:59'到'838:59:59'。小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

YEAR类型：YEAR类型是一个单字节类型用于表示年。MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。

可以指定各种格式的YEAR值：

· 四位字符串，范围为'1901'到'2155'。四位数字，范围为1901到2155。

· 两位字符串，范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值。

· 两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。

· 函数返回的结果，其值适合YEAR上下文，例如 SELECT NOW(); 非法YEAR值被转换为0000。

任何包含两位年值的输入都会令人模糊，因为世纪不知道。这些值必须解释为四位形式，因为MySQL内部使用四位来保存年。

对于DATETIME、DATE、TIMESTAMP和YEAR类型，MySQL使用以下规则解释含模糊年值的日期：

· 00-69范围的年值转换为2000-2069。

· 70-99范围的年值转换为1970-1999。

1.4》字符型，如图：

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(10)可以占用10个字符。

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。

如果严格的SQL模式没有启用，你分配了一个char或者varchar列超出了列的最大长度，那么这个值就会被截短以填充列，这时候就会产生一个警告。

对于截短的非空格字符，你可以设置严格的SQL模式，来产生一个错误。
对于varchar列来说，SQL模式在启用的时候，大量的尾部空格在插入之前都会被截短，产生一个警告。
对于char列来说，不管SQL模式是否被启用，都会静默地去掉插入值的过量的尾部空格。
varchar值存储的时候是不会填充的。如何处理尾部空格，取决于版本。在MySQL5.0.3中，当值存储和检索的时候，保留尾部空格，和标准的SQL保持一致。

在MySQL5.0.3之前，值被保存到varchar列的时候，尾部空格会被剔除。这样也就意味着检索值的时候，也不存在这些空格。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	' '	4个字节	''	1个字节
'ab'	'ab '	4个字节	'ab'	3个字节
'abcd'	'abcd'	4个字节	'abcd'	5个字节
'abcdefgh'	'abcd'	4个字节	'abcd'	5个字节

请注意上表中最后一行的值只适用不使用严格模式时；如果MySQL运行在严格模式，超过列长度的值不保存，并且会出现错误。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));

Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;

+----------------+----------------+

| CONCAT(v, '+') | CONCAT(c, '+') |

+----------------+----------------+

| ab  +          | ab+            |

+----------------+----------------+

1 row in set (0.00 sec)

CHAR(M)
固定长度字符串，当保存时在右侧填充空格以达到指定的长度。M表示列长度。M的范围是0到255个字符。
当检索CHAR值时尾部空格被删除。
VARCHAR(M)
变长字符串。M 表示最大列长度。M的范围是0到65,535。
VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定。最大有效长度是65,532字节。在 MySQL5.0以上的版本中，varchar数据类型的长度支持到了65535，也就是说可以存放65532个字节的数据，起始位和结束位占去了3个字节。

MySQL 5.1遵从标准SQL规范，并且不删除VARCHAR值的尾部空格。
VARCHAR保存时用一个字节或两个字节长的前缀+数据。如果VARCHAR列声明的长度大于255，长度前缀是两个字节。
BINARY(M)
BINARY类型类似于CHAR类型，但保存二进制字节字符串而不是非二进制字符串。
VARBINARY(M)
VARBINARY类型类似于VARCHAR类型，但保存二进制字节字符串而不是非二进制字符串。
TINYBLOB
最大长度为255(2^8–1)字节的BLOB列。
TINYTEXT
最大长度为255(2^8–1)字符的TEXT列。
BLOB[(M)]
最大长度为65,535(2^16–1)字节的BLOB列。
可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但足以容纳M字节长的值的BLOB类型。
TEXT[(M)]
最大长度为65,535(2^16–1)字符的TEXT列。
可以给出可选长度M。则MySQL将列创建为最小的但足以容纳M字符长的值的TEXT类型。
MEDIUMBLOB
最大长度为16,777,215(2^24–1)字节的BLOB列。
MEDIUMTEXT
最大长度为16,777,215(2^24–1)字符的TEXT列。
LONGBLOB
最大长度为4,294,967,295或4GB(2^32–1)字节的BLOB列。
LONGBLOB列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。
LONGTEXT
最大长度为4,294,967,295或4GB(2^32–1)字符的TEXT列。
LONGTEXT列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。
ENUM('value1','value2',...)
枚举类型。只能有一个值的字符串，从值列'value1'，'value2'，...，NULL中或特殊 ''错误值中选出。
ENUM列最多可以有65,535个截然不同的值。ENUM值在内部用整数表示。
SET('value1','value2',...)
一个设置。字符串对象可以有零个或多个值，每个值必须来自列值'value1'，'value2'，...
SET列最多可以有64个成员。SET值在内部用整数表示。

1.5》BIT数据类型

BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1到64。
要指定位值，可以使用b'value'符。value是一个用0和1编写的二进制值。
例如，b'111'和b'100000000'分别表示7和256。
如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。
例如，为BIT(6)列分配一个值b'101'，其效果与分配b'000101'相同。

【2】数据表（或称为表）是数据库中最重要的组成部分之一，是其他对象的基础。

数据表是数据库的重要组成部分。数据表是一个二维表格，行称为记录，列称为字段。

C:\Windows\system32>mysql -uroot -p -P3306 -h127.0.0.1 #root用户登录
Enter password: ******
mysql> USE test #打开test数据库
Database changed
mysql> SELECT DATABASE();#显示当前用户所选择（打开）的数据库

+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)

2.3》在众多记录中，我们要保证记录的唯一性，不能存在重复的记录。为此，我们可以为某个字段添加AUTO_INCREMENT属性。
AUTO_INCREMENT属性：
自动编号，且必须与主键组合使用；即：AUTO_INCREMENT属性不能单独出现，必须与主键同时出现！
默认情况下，起始值为1，每次的增量为1；该字段必须为数值型（整型或浮点型，若为浮点型，则小数位数必须为0，如float(7,0)）。
mysql> CREATE TABLE tb3( #报错：自动编号的字段必须定义成主键。。。那么什么是主键呢？
-> id SMALLINT UNSIGNED AUTO_INCREMENT,
-> username VARCHAR(30) NOT NULL
-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
2.4》初涉主键约束 PRIMARY KEY
主键约束
每张数据表只能存在一个主键
主键保证记录的唯一性（主键不允许重复）
主键自动（默认）为NOT NULL
mysql> CREATE TABLE tb3(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, #AUTO_INCREMENT必须与主键组合使用。
-> username VARCHAR(30) NOT NULL
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> SHOW COLUMNS FROM tb3; #主键字段默认为NOT NULL（禁止为空）
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(30) | NO | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> INSERT tb3(username) VALUES('Tom');#插入记录，只为username字段赋值，id字段会自动取值（auto_increment）。
Query OK, 1 row affected (0.04 sec)

mysql> INSERT tb3(username) VALUES('John');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT tb3(username) VALUES('Rose');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT tb3(username) VALUES('Dilitar');
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM tb3; #可以看到id字段从1开始，自动取值。
+----+----------+
| id | username |
+----+----------+
| 1 | Tom |
| 2 | John |
| 3 | Rose |
| 4 | Dilitar |
+----+----------+
4 rows in set (0.00 sec)
mysql> CREATE TABLE tb4(
-> id SMALLINT UNSIGNED PRIMARY KEY, # PRIMARY KEY可以单独出现，可以不需要auto_increment
-> username VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> SHOW COLUMNS FROM tb4;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | NO | PRI | NULL | |
| username | varchar(20) | NO | | NULL | |
+----------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> INSERT tb4 VALUES(4,'Tom'); #插入记录（主键可以被赋值，但主键不可以重复，其它字段可以重复）
Query OK, 1 row affected (0.07 sec)
mysql> INSERT tb4 VALUES(33,'Tom');
Query OK, 1 row affected (0.19 sec)
mysql> SELECT * FROM tb4; #主键不可以重复，其他字段可以重复！
+----+----------+
| id | username |
+----+----------+
| 4 | Tom |
| 33 | Tom |
+----+----------+
2 rows in set (0.00 sec)
mysql> INSERT tb4 VALUES(4,'Rose'); #报错：主键不允许重复。
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

2.5》初涉唯一约束 UNIQUE KEY
唯一约束
唯一约束可以保证记录的唯一性（不允许重复）。（主键也可以保证记录的唯一性）
唯一约束的字段可以为空值（NULL）。（主键自动为NOT NULL）
每张数据表可以存在多个唯一约束。（每张数据表只能有一个主键）
mysql> CREATE TABLE tb5(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, #主键，自动编号
-> username VARCHAR(20) NOT NULL UNIQUE KEY, #唯一约束
-> age TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.18 sec)
mysql> SHOW COLUMNS FROM tb5;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> INSERT tb5(username,age) VALUES('Tom',22);#插入记录
Query OK, 1 row affected (0.05 sec)
mysql> INSERT tb5(username,age) VALUES('Tom',22);#报错：username字段被定义为唯一约束（UNIQUE KEY），不能重复。
ERROR 1062 (23000): Duplicate entry 'Tom' for key 'username'
2.6》初涉默认约束
DEFAULT默认值
当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。
mysql> CREATE TABLE tb6(
-> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, #主键，自动编号
-> username VARCHAR(20) NOT NULL UNIQUE KEY, #唯一约束，禁止为空
-> sex ENUM('1','2','3') DEFAULT '3' #默认值为'3'
-> );
Query OK, 0 rows affected (0.10 sec)
mysql> SHOW COLUMNS FROM tb6;
+----------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| sex | enum('1','2','3') | YES | | 3 | |
+----------+----------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> INSERT tb6(username) VALUES('Tom'); #sex字段取默认值'3'
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM tb6;
+----+----------+------+
| id | username | sex |
+----+----------+------+
| 1 | Tom | 3 |
+----+----------+------+
1 row in set (0.00 sec)