13.MySQ数据表设计

2017-03-26 本文已影响1171人 dptms

数据表概念

数据表是数据库中的基本对象元素，以记录（行）和字段（列）组成的二维结构用于存储数据。数据表由表结构和表内容两部分组成，先建立表结构，然后才能输入数据。数据表结构设计主要包括字段名称、字段类型和字段属性的设置。

通常情况下，同一个数据库中可以有多个数据表，但表名必须是唯一的，表中每一条记录描述了一个相关信息的集合，每一个字段必须为唯一的，每个字段都需要指定数据类型。

数据列四大数据类型

数值类数据列类型

|数据类型|存储空间|说明|取值范围|
| ------------- |-------------| -----|
|TINYINT|1字节|非常小的整数|带符号值：-128_{127、无符号值：0}255|
|SMALLINT|2字节|较小的整数|带符号值：-32768_{32767、无符号值：0}65535|
|MEDIUMINT|3字节|中等大小的整数|带符号值：-8388608_{8388607、无符号值：0}16777215|
|INT |4字节 |标准整数| 带符号值：-2147483648_{2147483647、无符号值：0}4294967295|
|BIGINT| 8字节 |大整数 |带符号值：-263_{263-1、无符号值：0}264-1|
|FLOAT| 4或8字节| 单精度浮点数| 最小非零值：+- 1.175494351E-38、最大非零值：+- 3.402823466E+38|
|DOUBLE |8字节| 双精度浮点数 |最小非零值：+- 2.225073E-308、最大非零值：+- 1.797693E+308|
|DECIMAL| 自定义| 以字符串形式表示的浮点数| 取决于存储单元字节数|

整型注意事项：

INT(3)、SMALLINT（3）等整型后面的数字不会影响数值的存储范围，只会影响显示
整型后面的数字只有配合零填充的时候才有实际意义。
整型后面的数字可以省略

浮点型注意事项：

浮点型后面的数字会将存入的数字四舍五入，例如：把一个1.234存入FLOAT(6,1)数据列中，结果是1.2,6代表显示长度，1代表小数位长度，会四舍五入。

字符串类数据列类型

|数据类型|存储空间|说明|取值范围|
| ------------- |-------------| -----|
|CHAR[(M)] |M字节| 定长字符串 |M字节
|VARCHAR[(M)] |L+1字节 |可变字符串| M字节
|TINYBLOB，TINYTEXT |L+1字节 |非常小的BLOB(二进制大对象)和文本串 |28-1字节|
|BLOB,TEXT |L+2字节 |小BLOB和文本串 |216-1字节|
|MEDIUMBLOB,MEDIUMTEXT |L+3字节| 中等的BLOB和文本串| 224-1字节|
|LONGBLOB,LONGTEXT |L+4字节 |大BLOB和文本串| 232-1字节|
|ENUM(‘value1’,’value2’…) |1或2字节| 枚举：可赋予某个枚举成员 |65535个成员|
|SET(‘value1’，‘value2’…) |1,2,3,4或8字节| 集合：可赋予多个集合成员 |64个成员|

字符串类型注意事项：

CHAR和VARCHAR类型的长度范围都在0~255之间
在使用CHAR和VARCHAR类型时，当我们传入的实际的值的长度大于指定的长度，字符串会被截取至指定长度
在使用CHAR类型时，如果我们传入的值的长度小于指定长度，实际长度会使用空格补至指定长度
在使用VARCHAR类型时，如果我们传入的值的长度小于指定长度，实际长度即为传入字符串的长度，不会使用空格填补
CHAR要比VARCHAR效率更高，当占用空间较大
BLOB和TEXT类型是可以存放任意大数据的数据类型
BLOB区分大小写，TEXT不区分大小写
ENUM和SET类型是特殊的的串类型，其列值必须从固定的串集中选择

ENUM只能选择其中一个值，SET可以选择多个值

日期和时间类数据列类型

|数据类型|存储空间|说明|取值范围|
| ------------- |-------------| -----|
|DATE |3 字节| “YYYY-MM-DD”格式表示的日期值 |1000-01-01~9999-12-31|
|TIME |3 字节| “hh:mm:ss”格式表示的时间值 |-838:59:59-838:59:59|
|DATETIME| 8 字节 |“YYYY-MM-DD hh:mm:ss”格式 |1000-01-01 00：00：00~9999-12-31|
|TIMESTAMP| 4 字节| “YYYYMMDDhhmmss”格式表示的时间戳 |19700101000000-2037年的某个时刻|
|YEAR |1 字节 |“YYYY”格式的年份值 |1901~2155|

日期类型注意事项：

存储日期时，我们可以使用整型来进行存储时间戳，这样做便于我们进行日期的计算

NULL值

NULL值注意事项：

NULL意味着“没有值”或“未知值”
可以测试某个值是否为NULL
不能对NULL值进行算术计算
对NULL值进行算术运算，其结果还是NULL
0或NULL都意味着假，其余值都意味着真

数据字段属性

UNSIGNED

只能用于设置数值类型，不允许出现负数
最大存储长度会增加一倍

ZEROFILL

只能用于设置数值类型，在数值之前会自动用0补齐不足的位数

AUTO_INCREMENT

用于设置字段的自动增长属性，每增加一条记录，该字段的值会自动加1

NULL和NOT NULL

默认为NULL，即插入值时没有在此字段插入值，默认为NULL值，如果指定了NOT NULL，则必须在插入值时在此字段填入值

DEFAULT

可以通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值

创建索引

在MySQL中，主要有四类索引：

主键索引（PRIMARY KEY）

主键索引是关系数据库中最常见的索引类型，主要作用是确定数据表里一条特定的数据记录的位置。我们可以在字段后添加PRIMARY KEY来对字段设置为主键索引。
注意：
1.最好为每张表指定一个主键，但不是必须指定。
2.一个表只能指定一个主键，而且主键的值不能为空
3.主键可以有多个候选索引（例如NOT NULL，AUTO_INCREMENT）

唯一索引（UNIQUE）

唯一索引与主键索引一样，都可以防止创建重复的值。但是，不同之处在于，每个数据表中只能有一个主键索引，但可以有多个唯一索引。我们使用关键字UNIQUE对字段定义为唯一索引。

常规索引（INDEX）

常规索引技术是关系数据查询中最重要的技术，如果要提升数据库的性能，索引优化是首先应该考虑的，因为它能使我们的数据库得到最大性能方面的提升。常规索引也存在缺点：
1.多占用磁盘空间
2.会减慢插入，删除和修改操作
3.需要按照索引列上排序格式执行
创建索引我们可以使用INDEX和KEY关键字随表一同创建。

全文索引（FULLTEXT）

全文索引在MySQL中是一个FULLTEXT类型索引，但FULLTEXT索引只能用于MyISAM表，并且只可以在CHAR、VARCHAR或TEXT类型的列上创建，也允许创建在一个或多个数据列上。
但是FULLTEXT是不支持中文全文索引的，所以我们将来会使用效率更高的全文索引引擎Sphinx。

数据表的存储类型及存储位置

MySQL支持MyISAM、InnoDB、HEAP、BOB、ARCHIVE、CSV等多种数据表类型，在创建一个新MySQL数据表时，可以为它设置一个类型。

MyISAM和InnoDB两种表类型最为重要：

1.MyISAM数据表类型的特点是成熟、稳定和易于管理。
2.MyISAM表类型会产生碎片空间，要经常使用OPTIMIZE TABLE命令去清理表空间
3.MyISAM不支持事务处理，InnoDB支持
4.MyISAM不支持外键，InnoDB支持
5.MyISAM表类型的数据表效率更高
6.MyISAM表类型的数据表会产生三个文件，InnoDB表类型表默认只会产生一个文件。

字符集

创建表的时候，如果没有明确地指定任何字符集，则新创建数据表的字符集将由MySQL配置文件里charcter-set-server选项的设置决定。

在创建数据表时如果需要指定默认的字符集与之相同，但MySQL客户程序在与服务器通信时使用的字符集，我们需要使用default-character-set选项或通过SQL命令SET NAMES utf8来指定一个字符集为utf8.

创建表

创建数据表之前，我们应该注意：

1.创建数据库（如已存在则不需要创建）
2.选择数据库
3.在该数据库当中创建数据表

创建数据表需要注意：

1.指定数据表的名称（数据表不能重名）
2.指定该表的字段名称、字段数据类型、字段索引
3.指定表类型和表默认字符集（可省略）

建表实例

中文名	字段名	数据类型	属性	索引
用户编号	id	INT	UNSIGNED NOT NULL AUTO_INCREMENT	主键
用户名称	username	VARCHAR(50)	NOT NULL	普通
口令	userpass	VARCHAR(50)	NOT NULL	普通
联系电话	telno	VARCHAR(20)	NOT NULL	唯一
性别	sex	ENUM(‘男’,’女’)	NOT NULL DEFAULT ‘男’
出生日期	birthday	DATE	NOT NULL DEFAULT ‘0000-00-00’

CREATE TABLE IF NOT EXISTS `users`(
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `userpass` VARCHAR(50) NOT NULL,
    `telno` VARCHAR(20) NOT NULL UNIQUE,
    `sex` ENUM('nan','nv') NOT NULL DEFAULT 'nan',
    `birthday` DATE NOT NULL DEFAULT '0000-00-00',
    PRIMARY KEY(`id`),
    INDEX username_index(`username`),
    INDEX userpass_index(`userpass`)
)ENGINE=MyISAM DEFAULT CHARSET=utf8 collate utf8_general_ci ;

注意事项：

1.表的字段之间要使用逗号隔开。
2.建表的最后一句一定不能有逗号。
3.表名称和字段名称尽量不要使用MySQL系统的关键字
4.如果一定要使用关键字，我们可以使用反引号将表名称和字段名称包含起来来进行过滤屏蔽。
5.使用反引号会使建表效率增高。
6.数据表名称和字段名称不能重名
7.AUTO_INCREMENT属性必须依附于主键索引或唯一索引

修改数据表

修改表的语法：

ALTER TABLE 表名 ACTION;

我们可以对表进行修改字段，添加字段，删除字段，添加索引，删除索引，更改表名称，更改字段名称，更改auto_increment属性的初始值等。

修改字段：

我们使用change或者是modify关键字

ALTER TABLE `uses` CHANGE `username` `uname` VARCHAR(32) NOT NULL;

ALTER TABLE `users` MODIFY `username` VARCHAR(32) NOT NULL;

由上例可以发现：
change可以改变字段名称，而modify不可以

添加字段：

我们使用add关键字

ALTER TABLE `uses` ADD `tname` VARCHAR(32) NOT NULL;

这样我们就会新增一个tname字段。

删除字段：

我们使用drop关键字

ALTER TABLE `users` DROP `tname`;

这样我们会删除tname字段

添加索引：