MySQL-02 数据表管理

学习要点

• 数据类型
• 数据字段属性
• 数据表的类型及存储位置
• 索引
• 数据表对象管理

 

数据类型

数据库中的数据类型分为字段类型和值类型，定义如下：

• 在设计数据表字段的时候，字段类型定义为三大类：数值类、字符串类、日期时间类。
• 数据库中，插入数据表的实际值，大概分为四大类：数值型数据、字符串型数据、日期型数据、空值。

数值字段类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128，127)	(0，255)	小整数值
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

1. 显示宽度

例如：假设声明一个INT类型的字段 YEAR INT(4)

该声明指明，在year字段中的数据一般只显示4位数字的宽度。

显示宽度只用于显示，并不能限制取值范围和占用空间，YEAR会占用4个字节的存储空间，并且允许的最大值也不会是9999，而是INT整型所允许的最大值。【在数字字段属性设置为：zerofill时候才有区别】。

1. 数值类型带符号，如果设置成无符号UNSIGNED，则从0开始。
2. DECIMAL存储类型为字符串，不参与四舍五入，精度高。不能直接运算。涉及财务会计计算使用DECIMAL类型。

 

字符串字段类型

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
ENUM(‘value1’,’value2’,…)	65535个成员	枚举
SET(‘value1’,’value2’,…)	64个成员	集合

1. 字符串中的转义序列能够被解析和识别。
2. CHAR和VARCHAR的选择问题：如果数值类型可确定，并且在255字节内，优先使用CHAR类型。
3. 设计表的时候，从性能方面考虑，优先设计字段定长类型。定长表的性能优于变长表。

 

日期时间字段类型

类型	大小 (字节)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	8	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

1. 每个时间和日期字段类型都有一个零值，当插入非法数值时，就用零值添加。
2. 可以使用整数字段类型存储时间戳。
3. MySQL时间戳（TIMESTAMP）和PHP时间戳格式是不一样的。
4. 在PHP和MySQL交互中，PHP生成的时间戳一般以整数形式保存在MySQL中。
5. NULL可以插入数据库中，并参与检索数据
6. NULL不能参与算术运算
7. 字符串和数值之间的自动转换：SELECT  1+’2’
8. 字符串和日期类型根据上下文环境转换

NULL值类型

1. NULL可以插入数据库中，并参与检索数据
2. NULL不能参与算术运算

类型转换

1. 字符串和数值之间的自动转换：SELECT  1+’2’
2. 字符串和日期类型根据上下文环境转换

 

数据字段属性

 

属性名	作用
UNSIGNED	设置无符号数值类型。从0开始。
ZEROFILL	设置了显示宽度：int(6)，当不足6位数的时候高位0填充。
AUTO_INCREMENT	自增字段。插入方式：null、0，或者留空。也可以指定值。
NULL	空值，默认。
NOT NULL	非空。
DEFALUT	默认值。

综合示例：

 

 

 

自增字段插入值方式示例：

 

 

数据表的类型(存储引擎)及存储位置

查询MySQL数据库支持的表类型：mysql>SHOW  ENGINES  \G

MyISAM数据表类型：采用表格锁定机制来优化多个并发的读/写操作。更新机制浪费存储空间。读取数据速度快。

InnoDB数据表类型：在MySQL5.6.17版本中，默认创建InnoDB数据表类型。

MyISAM和InnoDB对比

表类型功能对比	MyISAM表	InnoDB表
事务处理	不支持	支持
数据行锁定	不支持，只支持表锁定	支持
外键约束	不支持	支持
表空间大小	相对小	相对大，最大是ISAM2倍
全文索引	支持	不支持
COUNT问题	无	执行COUNT(*)查询速度慢

查询数据表引擎类型

 

或者

 

创建InnoDB类型表示例：

 

 

创建MyISAM类型表示例：

 

 

 

 

数据表的存储位置

WAMP数据表的存储位置：

D:\wamp\bin\mysql\mysql5.6.17\data\数据库名

mysql中数据表存储位置

 D:\mysql\data\数据库名

.frm文件：表结构定义文件。

.idb文件：INNODB类型表数据文件。

.MYD文件：MYISAM类型表数据文件

.MYI文件：索引文件。

 

索引

主键索引

每张表只能有一个主键索引。创建方式：

 

或者创建表结构时候子句的形式：

 

唯一索引

唯一索引和主键索引的区别：唯一索引允许出现一次NULL

常规索引

创建：

CREATE INDEX 索引名 ON 表（字段1[，字段2，字段3...]）

　　

删除：

DROP INDEX 索引名 ON 表名

　　

全文索引

• InnoDB类型表不支持。
• 建表结构时候以子句的形式创建：FULLTEXT(字段名)
• 查询全文索引

SELECT  结果  FROM  表  WHERE  MATCH(字段)  AGAINST(待检索字符)

　　

如何使用索引

WHERE 条件的字段中，如果有索引，则会去按照索引去查询数据。

 

索引示例

 

 

数据表对象管理

创建表

CREATE  TABLE  [IF NOT EXISTS]  表名 (

　　字段1  字段类型  [属性]  [索引],

　　字段2  字段类型  [属性]  [索引],

　　字段3  字段类型  [属性]  [索引],

　　…

　　字段n  字段类型  [属性]  [索引],

) [表类型] [表字符集];

 

　　

修改表

• 添加新列

ALTER  TABLE 表名  ADD  字段名  <建表语句>  [FIRST|AFTER 列名]

　　

• 修改字段类型

ALTER  TABLE 表名 MODIFY 字段  <建表语句>

ALTER  TABLE 表名 CHANGE 原字段名 新字段名  <建表语句>

　　

• 修改表名

ALTER TABLE 旧表名  RENAME AS 新表名

　　

完成MySchool数据库和FootBallManager数据库的物理实现