MySQL数据表的设计

数据表（Table）

数据表是数据库的基本组成元素，以记录（行）和字段（列）组成的二位结构用于储存数据。数据库由表结构和表内容组成，先建立表结构，然后才能输入数据。数据表结构设计主要包括字段名称，字段类型和字段属性的设置。在关系数据库中，为了确保数据的一致性和完整性，在创建表时除了必须指定字段名称、字段类型和字段属性外，还需要使用约束（constraint）、索引（index）、主键（primary key）和外键（foreign key）等功能属性。

数据值和列类型

对MuSQL中数据值的分类，有数值型、字符型、日期型和控制等，这和一般的编程语言的分类差不多。另外，MySQL数据库的表是一个二维表，由一个或多个数据列构成。每个数据列都有它的特定类型，该类型决定了MySQL如何看待该列数据，我们可以把整形数值存放在字符型的列中，MySQL则会把它当成字符串处理。MySQL中的列类型有三种：数值型、字符串类和日期/时间类。从大类来看列类型和数值类型一样，都是只有三种，但每一种列类型都还可细分。下面对各种列类型进行详细介绍。

数值类的数据列类型

| 数据列类型 | 存储空间 | 说明 | 取值范围 |
|: ----------------- |:-------------|: --------------------|----------------------|
| TINTINT | 1字节 | 非常小的整数 | 带符号值： -128 ~ 127
无符号值：0 ~ 256|
|SMALLINT | 2字节 | 较小的整数 |带符号值：-32 768 ~ 32768
无符号值：0 ~ 65535 |
| MEDIUMINT| 3字节 | 中等大小的整数 | 带符号值：-8 388 608 ~ 8 388 607
无符号值：0 ~ 16 777 215|
| INT| 4字节 | 标准整数 | 带符号值：-2 147 483 648 ~ 2 147 483 647
无符号值：0 ~ 4 294 967 295|
| BIGINT| 8字节 | 大整数 | 带符号值：-9 223 372 036 854 775 808 ~ 9 223 372 036 854 775 807
无符号值：0 ~ 18 446 744 073 709 551 615|
| FLOAT| 4 或 8字节 | 单精度浮点数 | 最小非零值：±1.175494351E-38
最大非零值：±3.402823466E+38|
| DOUBLE| 8字节 | 双精度浮点数 | 最小非零值：±2.2250738585072014E-308
最大非零值：±1.7976931348623157E+308|
| DECIMAL| 自定义 | 以字符串形式表示的浮点数 | 取决于储存单元字节数|
为了节省存储空间和提高数据库处理效率，我们应根据应用数据的取值范围来选择一个最合适的数据列类型。如果把一个超出数据列取值范围的数存入该列，则MySQL就会截断该值，如：我们把99999存入 SMALLINT（3）数据列里，因为SAMLLINT(3)的取值范围是-32 768 ~ 32768，所以就会截断成32767存储。显示宽度3 不会影响数值的存储，只影响显示。

字符串类数据列类型

字符串可以用老表示任何一种值，所以它是最基本的类型之一。

| 数据列类型 | 存储空间 | 说明 | 最大长度 |
|: ----------------------- |:-----------------|: --------------------|----------------------|
| CHAR[(M)] | M字节 | 定长字符串 | M字节 |
| VARCHAR[(M)] | L + 1字节 | 可变字符串 | M字节 |
| TINYBLOD,TINYTEXT | L + 1字节 | 非常小的BLOB(二进制大对象) 和文本串 | 2^6 - 1字节 |
| BLOB,TEXT | L + 2字节 | 小BLOB和文本串 | 2^16 - 1字节 |
| MEDIUMBLOB,DEDIUMTEXT | L + 3字节 | 中等BLOB和文本串 | 2^24 - 1字节 |
| LONGBLOB,LONGTEXT | L + 4字节 | 大BLOB和文本串 | 2^32 - 1字节 |
| ENUM('value1','value2',....) | 1或2 字节 | 枚举：可赋予某个枚举成员 | 65535个成员 |
| SET('value1','value2',....) | 1,2,3,4,8, 字节 |集合：可赋予某个集合成员 | 64个成员 |

字符串类数据列类型

| 数据列类型 | 存储空间 | 说明 | 最大长度 |
|: ----------------------- |:-----------------|: --------------------|----------------------|
| DATE | 3字节 | "YYYY-MM-DD"格式表示的日期值 | 1000-01-01 ~ 9999-12-31 |
| TIME | 3字节 | "hh:mm:ss"格式表示的时间值 | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 |
| TIMESTAMP | 4字节 | "YYYYMMDDhhmmss"格式表示的时间戳 | 19700101000000 ~ 2037年的某个时刻 |
| YEAR | 1字节 | "YYYY"格式的年份值 | 1901 ~ 2155 |

NULL值

NULL 值可能使你感到奇怪，直到你习惯它。概念上，NULL意味着没有值或未知值，且它被看成与众不同的值。可以将NULL值插入到数据表中并从表中检索它们，也可以测试某个值是否为NULL，但不能对NULL值金像算术运算，如果对NULL值进行算数运算，其结果仍旧是NULL。在MySQL中，0或NULL都意味着假而其他意味着真。布尔运算的默认真值是1.

数据字段属性

1.UNSIGNED
该属性只能修饰数值类型，不允许数列出现负数。

2.ZEROFILL
该属性也只能用于设置数值类型，在数值类型前自动用0补足位数。

3.AUTO_INCREMENT
该属性用于设置字段的自动增量属性，当数值类型的字段设置为自增量属性时，每增加一条新的记录，该字段的值就自动加1，而且此字段的值不允许重复

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

5.DEFAULT
可以通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值。例如，在用户表user中可以将性别字段默认设置成“男”。

数据表对象管理

创建表

创建数据表的主要是定义表结构，包括数据表的名称，字段名，字段类型，约束和索引等。其基本语法如下

CREATE TABLE [IF NOT EXISTS] 表名称（
        字段名1  列类型  [属性]  [索引]  
        字段名2  列类型  [属性]  [索引]  
）[表类型] [表字符集]

修改表

修改表是指修改表的结构，在实际的应用中，当发现某个表的结构不满足要求时，可以用ALTER TABLE 语句来修改表的结构，包括添加新的字段、删除原有的字段、修改列的类型、属性、及索引，甚至可以修改表的目名称等。修改表的语法如下：

ALTER TABLE 表名 ACTION; ------------ #修改表的语法格式

ALTER TABLE 表名 ADD 字段名 <建表语句> [FIRST | AFTER 列名] #为指定的表添加新列

mysql> ALTER TABLE users ADD name VARCHAR(30) NOT NULL FIRST;
mysql> ALTER TABLE users ADD height DOUBLE NOT NULL  AFTER userpass;


为指定的数据表为了更改原有字段的类型，可使用CHANGE或MODIFY 子句。如果原列的名字和新列的名字相同，则change和Modify的作用相同。语法格式如下
>```
ALTER TABLE 表名CHANGE(MODIFY) 列表  <建表语句>      #为指定的表修改列类型

 mysql> ALTER TABLE users CHANGE telno phone INT UNSIGNED DEFAULT '0';

修改表名

ALTER TABLE 旧表名 RENAME AS 新表名       #为指定的数据表重新命名

删除表

DROP TABLE 旧表名 RENAME AS 新表名 #删除不再使用的数据表

作者：全栈coder
链接：https://www.jianshu.com/p/8b9fa7925e57
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。