学习要点
数据类型
数据字段属性
数据表的类型及存储位置
索引
数据表对象管理
数据类型
数据库中的数据类型分为字段类型和值类型,定义如下:
在设计数据表字段的时候,字段类型定义为三大类:数值类、字符串类、日期时间类。
数据库中,插入数据表的实际值,大概分为四大类:数值型数据、字符串型数据、日期型数据、空值。
数值字段类型
类型
大小
范围(有符号)
范围(无符号)
用途
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的值
小数值
显示宽度
例如:假设声明一个INT类型的字段 YEAR INT(4)
该声明指明,在year字段中的数据一般只显示4位数字的宽度。
显示宽度只用于显示,并不能限制取值范围和占用空间,YEAR会占用4个字节的存储空间,并且允许的最大值也不会是9999,而是INT整型所允许的最大值。【在数字字段属性设置为:zerofill时候才有区别】。
数值类型带符号,如果设置成无符号UNSIGNED,则从0开始。
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个成员
集合
字符串中的转义序列能够被解析和识别。
CHAR和VARCHAR的选择问题:如果数值类型可确定,并且在255字节内,优先使用CHAR类型。
设计表的时候,从性能方面考虑,优先设计字段定长类型。定长表的性能优于变长表。
日期时间字段类型
类型
大小(字节)
范围
格式
用途
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
混合日期和时间值,时间戳
每个时间和日期字段类型都有一个零值,当插入非法数值时,就用零值添加。
可以使用整数字段类型存储时间戳。
MySQL时间戳(TIMESTAMP)和PHP时间戳格式是不一样的。
在PHP和MySQL交互中,PHP生成的时间戳一般以整数形式保存在MySQL中。
NULL可以插入数据库中,并参与检索数据
NULL不能参与算术运算
字符串和数值之间的自动转换:SELECT 1+’2’
字符串和日期类型根据上下文环境转换
NULL值类型
NULL可以插入数据库中,并参与检索数据
NULL不能参与算术运算
类型转换
字符串和数值之间的自动转换:SELECT 1+’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数据库的物理实现