mysql之数据类型、建表以及约束

袁不源呀

已于 2022-04-13 23:20:08 修改

阅读量1k

点赞数 2

分类专栏：学习文章标签： java

于 2022-04-13 20:57:37 首次发布

本文链接：https://blog.csdn.net/m0_68879220/article/details/124157684

版权

学习专栏收录该内容

19 篇文章 0 订阅

订阅专栏

数据类型

数据表由多个字段组成，每个字段在进行数据定义的时候都要确定不同的数据类型。向每个字段插入的数据内容决定了该字段的数据类型。MySQL 提供了丰富的数据类型，根据实际需求，用户可以选择不同的数据类型。不同的数据类型，存储方式是不同的。

INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT（整数类型）int-->int bigint-->long
FLOAT、DOUBLE、DECIMAL（小数类型）
DTAETIME、TIMESTAMP、DATE、TIME、YEAR（日期和时间类型）
CHAR、VARCHAR、TEXT、ENUM、SET（字符串类型）
BIT、BINARY、VARBINARY、BLOB（二进制类型）

TIME 类型

TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

DATE 类型 DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日

使用 CURRENT_DATE 或者 NOW()，插入当前系统日期

DATETIME 类型 DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH：MM：SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

TIMESTAMP时间戳

datetime 8 字节 yyyy-mm-dd hh:mm:ss
timestamp 4 字节 yyyy-mm-dd hh:mm:ss

服务器时区不一样的就建议选择 datetime。

如果是想要使用自动插入时间或者自动更新时间功能的，可以使用timestamp。

DECIMAL(P，D)定点类型，表示列可以存储D位小数的P位数

P是表示有效数字数的精度。 P范围为1〜65。

D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。

DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

类型名称说明存储需求
FLOAT 单精度浮点数 4 个字节
DOUBLE 双精度浮点数 8 个字节
DECIMAL (P, D)，DEC 压缩的“严格”定点数 p+2 个字节

UNSIGNED(无符号位):所有数值类型可用此修饰之后不能存放负数

datetime	8 字节	yyyy-mm-dd hh:mm:ss
timestamp	4 字节	yyyy-mm-dd hh:mm:ss

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL (P, D)，DEC	压缩的“严格”定点数	p+2 个字节

数据表

创建表

#语法
CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

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

<表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。

在创建数据表之前，应使用语句“USE<数据库>”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出 No database selected 的错误。

修改表

注：ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
1）新增语法：alter table 表名称 add column 列名 数据类型;
2）删除语法：alter table 表名称 drop column 列名;
3）修改语法：alter table 表名称 modify column 列名 数据类型;   
详细内容查看文档

删除表

#语法格式
DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
#例如
DROP TABLE  t1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t1,t2,t3;

查看表结构

DESC <表名>;

在SQLyog中可以将查看结果导出

约束

主键约束(PRIMARY KEY)

主键约束：唯一、不重复、不为空；每个表都应该有一个主键，并且每一个表只能有一个主键。

语法：CREATE TABLE 表名称(
        列名称 数据类型 Primary Key,
        ...
    );

主键生成策略
- sqlserver标识列IDENTITY
- oracle序列SEQUENCE
- mysql自动增长AUTO_INCREMENT
mysql自动增长 AUTO_INCREMENT
- 如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。
- 当插入记录时，没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。
- 当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况，情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。

非空值约束(NOT NULL)

非空值约束：用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

语法：CREATE TABLE 表名称(
    列名称 数据类型 NOT NULL,
    ...
);

唯一约束(UNIQUE)

唯一约束：指定TABLE的列或列组合不能重复，保证数据的唯一性。

语法：CREATE TABLE 表名称(
    列名称1 数据类型 NOT NULL,
    列名称2 数据类型,
    列名称3 数据类型,
    unique(列名称1[,列名称2,...,列名称N])
);

默认值约束(DEFAULT)

默认值约束：指定列的默认值。

语法：CREATE TABLE 表名称(
    列名称1 数据类型 DEFAULT '默认值',
    列名称2 数据类型,
    列名称3 数据类型,
    ...
);

例如： 1) 性别：sex varchar(2) default '男' 2) 日期：createdate timestamp default current_timestamp

外键约束(FOREIGN KEY)

外键约束：外键创建在从表（副表）中，从表中的FOREIGN KEY指向主表中的PRIMARY KEY。

主表：CREATE TABLE 表名称(
    列名称 数据类型 Primary Key,
    ...
);    

从表：CREATE TABLE 表名称(
    ...
    外键名称 数据类型,
    FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称),
    ...
);

1）添加表外键： ALTER TABLE 表名 ADD FOREIGN KEY(外键名称) REFERENCES 主表名称(主键名称); 2）删除表外键： ALTER TABLE 表名 DROP FOREIGN KEY 约束名; 3）新增主键： ALTER TABLE 表名 ADD PRIMARY KEY(主键名称); 4）删除主键：主键只有一个直接删除即可 ALTER TABLE 表名 DROP PRIMARY KEY; 5）新增唯一约束： ALTER TABLE 表名 ADD UNIQUE(列名称1[,列名称2,..]); 6）删除唯一约束：其实就是删除指定的唯一约束的索引名称 ALTER TABLE 表名 DROP INDEX 唯一约束缩影名称;