MySQL入门学习-数据类型及表操作

您是本文的第个读者

通过《MySQL入门学习-环境搭建》 和 《MySQL入门学习-数据库基本操作》 两篇文章，我们成功搭建了MySQL并且熟悉了常用的数据库操作命令。
接下来，就让我们一起来学习关于数据表的基本操作吧。

1 数据类型

在讲解数据表的基本操作之前，我们必须要先来学习下关于MySQL中的数据类型。

从下图中，我们可以很清晰的看得出MySQL总的来说有四种数据类型
MySQL数据类型

就像Java的整数类型总共有byte、short、int、long那样，在MySQL上述的四种数据类型中，具体细分下来，又会有很多种，所以下面我们一种一种来进行细分讲解。

1.1 整型

在MySQL中，整型总共分为5种，分别为 TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT 、 BIGINT ,他们的存储范围和字节分别是:

数据类型 存储范围 字节
TINYINT 有符号值： -128 到 127 (-27 到 27-1)
无符号值：0 到 255 (0 到 28-1)		 1
SMALLINT 有符号值： -32768 到 32767 (-215 到 215-1)
无符号值：0 到 65535 (0 到 216-1)		 2
MEDIUMINT 有符号值： -8388608 到 8388607 (-223 到 223-1)
无符号值：0 到 16777215 (0 到 224-1)		 3
INT 有符号值： -2147483648 到 2147483647 (-231 到 231-1)
无符号值：0 到 4294967295 (0 到 232-1)		 4
BIGINT 有符号值： -9223372036854775808 到 9223372036854775807 (-263 到 263-1)
无符号值：0 到 18446744073709551615 (0 到 264-1)		 8

1.2 浮点型

在MySQL中，浮点型总共分为2种，分别为 FLOAT 、 DOUBLE ，他们的存储范围分别是:

数据类型 存储范围
FLOAT[(M, D)] -3.402823466E+38 到 -1.175494351E-38
0 和 1.175494351E-38 到 3.402823466E+38
DOUBLE[(M, D)] -1.7976931348623157E+308 到 -2.225073858507204E-308
0 和 -2.225073858507204E-308 到 1.7976931348623157E+308

注意： 表格中，M表示数字总位数， D表示小数点后面的位数。 如果M 和 D 被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。

1.3 日期时间型

在MySQL中，日期时间型总共分为5种，分别是 YEAR 、 TIME 、 DATE 、 DATETIME 、 TIMESTAMP ,它们的详细说明如下：

数据类型 字节 详细说明
YEAR[(2｜4)] 1 默认是四位格式。在四位格式中，允许的值是1901到2155和0000。
在两位格式中，允许的值是70到69，表示从1970年到2069年。
MySQL以YYYY 格式显示YEAR值。
TIME 3 范围是’-838:59:59’到’838:59:59’。MySQL以’HH:MM:SS’格式显示TIME值，但允许使用字符串或数字为TIME列分配值
DATE 3 支持的范围为’1000-01-01’到’9999-12-31’。MySQL以’YYYY-MM-DD’格式显示DATE值，但允许使用字符串或数字为DATE列分配值。
DATETIME 8 日期和时间的组合。支持的范围是’1000-01-01 00:00:00’到’9999-12-31 23:59:59’。MySQL以’YYYY-MM-DD HH:MM:SS’格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。
TIMESTAMP 4 范围是’1970-01-01 00:00:00’到 2037年之间。 TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。

1.4 字符型

在MySQL中，字符型总共分为8种，它们分别是 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET ，它们的详细说明是：

数据类型 存储说明 其它说明
CHAR(M) M个字节， 0 <= M <= 255 定长类型，比如CHAR(5),如果我们只赋值abc，那么系统会自动在后面补两个空格
VARCHAR(M) L+1个字节，其中 L <= M 且 0 <= M <= 65535 变长类型
TINYTEXT L+1个字节，其中 L < 28
TEXT L+2个字节，其中 L < 216
MEDIUMTEXT L+3个字节，其中 L < 224
LONGTEXT L+4个字节，其中 L < 232
ENUM(‘value1’,’value2’…) 1或2个字节，取决于枚举值的个数，最多65535个
SET(‘value1’,’value2’…) 1，2，3，4或者8个字节，取决于SET成员的数目，最多64个成员

2 数据表创建

了解完数据类型后，让我们一起来学习下怎么去进行数据表的创建。

2.1 数据表基础创建语句

首先一起来看下，基本的数据表创建格式：

1
2
3
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型}
);

假如我们想要创建一个用户表，它有以下属性：

  • 姓名: 因为姓名的长度是变化的，所以它属于变长字符串类型，考虑到人名一般不会太长，因此定义长度为25
  • 年龄: 因为是年龄，所以是整形
  • 工资: 因为钱，可以精确到小数点后两位，但是一般数字不会太大，所以我们使用FLOAT类型即可

那么，我们的创建语句如下:

1
2
3
4
5
CREATE TABLE IF NOT EXISTS user (
    name VARCHAR(25),
    age  TINYINT,
    salary FLOAT(7,2)
);

2.2 约束

有些时候，我们还说希望对我们的每一列的内容进行一定的限制的，在MySQL中，我们称之为约束。

下面，我们将来讲解下当前MySQL中支持的约束方式。

2.2.1 非空约束

有时候，我们会希望某个字段禁止为空，这个时候，就对某一列有一个具体的约束了，此时，MySQL提供了一个非空约束，我们只要在某个字段后面添加 NOT NULL 即可。

具体在创建表的时候，格式如下:

1
2
3
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型} NOT NULL
);

比如，我想前面创建的用户表中的用户名不能为空，创建表时的语句则需要更改为:

1
2
3
4
5
CREATE TABLE IF NOT EXISTS user (
    name VARCHAR(25) NOT NULL,
    age  TINYINT,
    salary FLOAT(7,2)
);
2.2.2 主键约束

1> 创建主键约束

也许我们会有这么一个需求，我们希望某一列能够保证记录的唯一性并且值不能为空，此时，MySQL提供了一个主键约束，我们只要在字段后面添加 PRIMARY KEY 或者在最后面使用 PRIMARY KEY({字段名称}, {字段名称}) 。

具体的SQL语句格式如下：

1
2
3
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型} PRIMARY KEY
);

或者

1
2
3
4
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型} NOT NULL,
    PRIMARY KEY({字段名称}）
);

2> 删除主键约束

1
ALTER  TALBLE {数据表名称} DROP PRIMARY KEY;

3> 主键约束的特点

为了方便和后面讲到的唯一约束进行对比，下面我们来总结一下 主键约束 的特点:

  • 设置为主键的列自动设置为非空 NOT NULL
  • 主键用语保证记录的唯一性
  • 每张表只能存在一个主键
  • 自动为主键列创建索引
2.2.3 唯一约束

1> 创建唯一约束

当然，我们有时候想要有多个字段都是唯一的，并且这个字段的值可能是空的，此时，MySQL提供了一个唯一约束，我们只要在字断后面添加 UNIQUE KEY 或者在CREATE中使用 UNIQUE({字段名称}) 即可。

具体的SQL语句格式如下：

1
2
3
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型} UNIQUE KEY
);

或者

1
2
3
4
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型} NOT NULL,
    UNIQUE({字段名称}）
);

2> 删除唯一约束

1
ALTER  TALBLE {数据表名称} DROP {INDEX | KEY} {唯一约束名称};

3> 唯一约束的特点
下面总结下唯一约束的特征并且和主键约束进行对比，差别将会用红色进行标出:

  • 保证记录的唯一性
  • 字段可以为空值
  • 每张表可以存在多个唯一约束
  • 自动为唯一约束的列创建索引
2.2.4 默认约束

默认约束，它可以给这一列设置一个默认值，当我没有为某一列设置其它值时，那么这一列的值将会是我们设置的默认值。

具体的SQL语句格式如下:

1
2
3
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型} DEFAULT {默认值}
);
2.2.5 外键约束

1> 创建外键约束

外键约束用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。

具体的SQL语句格式如下：

1
2
3
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型} FOREIGN KEY REFERENCES {关联数据表名称}(关联字断名称)
);

或者

1
2
3
4
CREATE TABLE [IF NOT EXISTS] {数据表名称} (
    {列名} {数据类型},
    FOREIGN KEY({字段名称}) REFERENCES {关联数据表名称}(关联字断名称)
);

2> 删除外键约束

1
ALTER  TALBLE {数据表名称} DROP FOREIGN KEY {外键约束名称};
2.2.6 约束分类

通过前面的介绍，我们可以了解到，约束总共有5种，但是大家知道吗？约束从另外一个角度来说，可以分成两类：

  • 表级约束: 与列定义相互独立，不包含在列定义中；通常用于对多个列一起进行约束，与定义用’,’ 分隔；必须指出要约束的列的名称
  • 列级约束: 包含在列定义中；直接跟在该列的其它定义之后，用空格分隔；不必指定列名

通过前面的定义以及各种约束的讲解，我们可以了解到：

  • 非空约束、默认约束只能用于列级约束的创建
  • 主键约束、外键约束、唯一约束皆可用于表级约束和列级约束的创建

2.3 表的基本操作

2.3.1 字段操作

1> 向表中添加新的字段 

1
ALTER TABLE {数据表名称} ADD {列名称} {列定义};

2> 修改列名称 

1
ALTER TABLE {数据表名称} CHANGE [COLUMN] {列名称} {新的列名称} {列定义} [FIRST | AFTER {列名称}];

3> 修改列定义 

1
ALTER TABLE {数据表名称} MODIFY [COLUMN] {列名称} {列定义} [FIRST | AFTER {列名称}];

4> 删除列定义 

1
ALTER TABLE {数据表名称} DROP [COLUMN] {列名称};
2.3.2 数据表重命名

给现有的数据表重新命名

1
ALTER TABLE {数据表名称} RENAME [TO | AS] {新数据表名称};

或者

1
RENAME TABLE {数据表名称} RENAME TO {新数据表名称} [,  {数据表名称1} TO  {新数据表名称1}];



1、本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可。

2、转载请在评论处说明并且注明出处:http://xiaoxuetu.github.io , 谢谢合作 ！