MySQ数据表设计
数据表概念
数据表是数据库中的基本对象元素,以记录(行)和字段(列)组成的二维结构用于存储数据。数据表由表结构和表内容两部分组成,先建立表结构,然后才能输入数据。数据表结构设计主要包括字段名称、字段类型和字段属性的设置。
通常情况下,同一个数据库中可以有多个数据表,但表名必须是唯一的,表中每一条记录描述了一个相关信息的集合,每一个字段必须为唯一的,每个字段都需要指定数据类型。
数据列四大数据类型
数值类数据列类型
|数据类型|存储空间|说明|取值范围|
| ------------- |-------------| -----|
|TINYINT|1字节|非常小的整数|带符号值:-128127、无符号值:0255|
|SMALLINT|2字节|较小的整数|带符号值:-3276832767、无符号值:065535|
|MEDIUMINT|3字节|中等大小的整数|带符号值:-83886088388607、无符号值:016777215|
|INT |4字节 |标准整数| 带符号值:-21474836482147483647、无符号值:04294967295|
|BIGINT| 8字节 |大整数 |带符号值:-263263-1、无符号值:0264-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字段
添加索引:
我们使用add
关键字
ALTER TABLE `uses` ADD INDEX/UNIQUE/PRIMARY KEY 索引名称(字段名称)
这样会在该字段上建立索引(普通索引,唯一索引,主键索引)。
删除索引:
ALTER TABLE `users` DROP INDEX/UNIQUE/PRIMARY KEY 索引名称;
这样我们会删除这个索引,我们可以使用show indexes from 表名
查看当前表索引。
更改表名称:
我们使用rename
关键字
ALTER TABLE 旧表名 RENAME AS 新表名
将旧表名更改为新表名
更改AUTO_INCREMENT
初始值:
ALTER TABLE 表名称 AUTO_INCREMENT=1
将AUTO_INCREMENT的初始值设置为1
删除表:
DROP TABLE [IF EXISTS] 表名称;
- 如何启动和停止MySQL数据库服务(至少两种)?
- 在命令行模式下如何连接MySQL数据库服务。
- 在mysql命令提示符下:如何查看当前服务器下的数据库(浏览数据库);
- 在mysql命令提示符下:如何选择进入一个数据库;
- 在mysql命令提示符下:如何查看当前库下的所有表。
- 在mysql命令提示符下:如何如何查看一个表格的结构;
- 在mysql命令提示符下:如何取消当前sql命令;
- 在mysql命令提示符下:如何退出数据库;
- 在mysql命令提示符下:如何创建一个数据库;
- 在mysql命令提示符下:如何删除一个数据库;
- 在mysql命令提示符下:如何删除一个数据表;
-
参考下面图片,按照要求创建表格,(字段类型和约束只做参考)
Paste_Image.png - 为上面的每个表都添加10条测试数据。
- 修改其中的3条数据。
- 删除每个表的2条数据库。