MySQL数据表的设计

数据表：数据表由表结构和表内容两部分组成，先建立表结构，然后才能输入数据。

数据表结构设计主要包括字段名称、字段类型、和字段属性的设置。

在关系数据库中，为了确保数据的完整性和一致性，在创建表时除了必须指定字段名称、字段类型、字段属性外，还需要使用约束（constraint）、索引（index）、主键（primary key）和外键（foreign key）等功能属性。

 

数据值和列类型（字段的类型）：

对MySQL中数据值的分类：有数值型、字符型、日期型和空值等。

MySQL中的列类型有三种：数值类、字符串类和日期/时间类。

 

数值类的数据列类型

​数值分整型和浮点型两种。

整型中又分为5种整型数据列类型：

数据列类型	存储空间	说明	取值范围
TINYINT 男或女	1字节	非常小的整数	带符号值：-127~127 无符号值：0~255
INT 最常用的	4字节	标准整数
BIGINT	8字节	大整数
SMALLINT 学校人数
MEDIUMINT

 

整型注意事项：

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

浮点型数据列类型：

 

数据列类型	存储空间	说明	取值范围
FLOAT	4或8字节	单精度浮点数
DOUBLE （商城）	8字节	双精度浮点数
DECIMAL （金融、银行、科学研究）	自定义	以字符串形式表示的浮点数	取决于存储单元字节数

 

字符串类数据列类型

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

我们可以用字符串类型来存储图像或声音之类的二进制数据，也可以存储用gzip压缩的数据。但是现在不会把图片存储到数据库中。

MySQL支持以单或双引号包围的字符序列。如：“MySQL”,'PHP'。

同PHP程序一样，MySQL能识别字符串中的转义序列，转义序列用反斜杠（\）表示。

 

字符串列类型:

 

枚举：存男或女这样的汉字或字符串。用的时候直接调用过来，用的很少，一般用tinyint代替，因为数字更快！

如果成员过多，可以考虑用集合，但是当能用到枚举和集合的时候就可以考虑换内存级别的数据库了，如：redius。

 

字符串类型注意事项：

• CHAR和VARCHAR类型的长度范围都在0~255之间
• 在使用CHAR和VARCHAR类型时，当我们传入的实际的值的长度大于指定的长度，字符串会被截取至指定长度
• 在使用CHAR类型时，如果我们传入的值的长度小于指定长度，实际长度会使用空格补至指定长度
• 在使用VARCHAR类型时，如果我们传入的值的长度小于指定长度，实际长度即为传入字符串的长度，不会使用空格填补
• CHAR要比VARCHAR效率更高，但是占用空间较大

 

日期和时间型数据列类型

 

日期类型注意事项：

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

 

时间戳：1970年1月1日0时0分0秒开始

调用PHP中的time()函数获取当前的时间戳

 

<?php
//设置时区
date_default_timezone_set("PRC");

//获取当前时间
echo time();

//输出当前时间
echo date('Y-m-d H:i:s',time());

 

 

NULL值

NULL值注意事项：

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

 

数据字段属性：

UNSIGNED

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

ZEROFILL

• 只能用于设置数值类型，在数值之前会自动用0补齐不足的位数
• 当给一个字段使用ZEROFILL修饰时，该字段自动应用UNSIGNED属性。

AUTO_INCREMENT

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

NULL和NOT NULL

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

DEFAULT

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

字符集

完整创建表的语句：

create table 表名(

字段名 字段类型（长度） [unsigned] [not null][auto_increment],

字段名 字段类型（长度） [unsigned] [not null default 'xxx'],

....

primary key(id)

)engine=myisam default charset=utf8 collate utf8_general_ci;

 

 

表字段操作：

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

 

添加字段    alter    table    表名    add    字段名称    类型    属性    索引类型。。。[FIRST   |   AFTER 列名]

删除字段    alter    table    表名    drop    字段名称；

修改字段    alter    table    表名    modify    字段名称    类型    属性；

                  alter    table    表名    change    原字段名称    新字段名称    类型    属性；

修改表名    alter    table    旧表名    rename    as    新表名

                  alter    table    旧表名    rename    to    新表名

                  alter    table    旧表名    rename    新表名

修改字段中，change除了更改类型外，还能更改列名，而modify不能实现这个功能。change更改；modify修改；

如果没有指定可选的FIRST或AFTER,则在列表尾添加一列，否则在指定列添加新列。

 

创建索引：

在MySQL中主要有四类索引：主键索引、唯一索引、常规索引、全文索引。

 

主键索引：是关系数据库中最常见的索引类型，主要作用是确定数据表里一条特定的数据记录的位置。我们可以在字段后添加PRIMARY KEY来对字段设置为主键索引。

注意：

1.最好为每张表指定一个主键，但不是必须指定。

2.一个表只能指定一个主键，而且主键的值不能为空

3.主键可以有多个候选索引（例如NOT NULL，AUTO_INCREMENT）

 

唯一索引与主键索引一样，都可以防止创建重复的值。

但是，不同之处在于，每个数据表中只能有一个主键索引，但可以有多个唯一索引。

我们使用关键字UNIQUE对字段定义为唯一索引。

 

常规索引技术是关系数据查询中最重要的技术，如果要提升数据库的性能，索引优化是首先应该考虑的，因为它能使我们的数据库得到最大性能方面的提升。

常规索引也存在缺点：

1.多占用磁盘空间

2.会减慢插入，删除和修改操作

3.需要按照索引列上排序格式执行

创建索引我们可以使用INDEX和KEY关键字随表一同创建。

 

创建索引   

create    index    索引名称    on    数据表名（字段）

create    unique    index    索引名    on    数据表名（字段）

添加一个主键索引：     alter    table    数据表名    add    primary    key（列）；

添加一个唯一索引：     alter    table    数据表名    add    unique    索引名（字段名）；

添加一个常规索引：     alter    table    数据表名    add    index    索引名（字段名）；

 

删除索引

删除常规索引：        drop    index    索引名称    on    数据表名；

删除常规索引：        alter    table    表名    drop    index    索引名；

删除唯一索引：        alter    table    表名    drop    index    索引名；

 

查看索引：show index(es) from 数据表名；

 

 

 

 bbs_user | CREATE TABLE bbs_users1 (

 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

 `username` varchar(20) NOT NULL,

 `sex` tinyint(4) DEFAULT '1',

 `email` varchar(50) DEFAULT NULL,

 `qq` varchar(15) NOT NULL,

 `xingqu` text,

 PRIMARY KEY (`id`),

 UNIQUE KEY `cde` (`username`),

 UNIQUE KEY `aaaa` (`email`),

 KEY `qqin` (`qq`)

 ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 |

 

来自为知笔记(Wiz)