hemiao3000

# 操作 Table

# 1. 创建表

创建表的过程是规定 数据列 的属性的过程，同时也是定义数据完整性约束的过程。

表（TABLE）是出于数据库（DATABASE）“之下”的，在创建数据表之前，应该使用语句 UESE 数据库名; 来指定当前（及未来）的操作都是在哪个数据库（DATABASE）中。

语法：

CREATE TABLE <表名> (
  <字段名1> <数据类型> [列级别约束条件] [默认值],
  <字段名2> <数据类型> [列级别约束条件] [默认值],
  <字段名3> <数据类型> [列级别约束条件] [默认值],
  ...
  [表级别约束条件]
) [ENGINE=引擎] [DEFAULT CHARSET=字符编码];

注意：上面出现的 <> 表示此处内容由用户自定义。<> 本身并非 SQL 的内容的一部分。

被 [] 括起来的部分都不是必要部分，缺失的话，MySQL 会使用默认设置。

  • 例如：

    CREATE TABLE `dept` (
  `deptno` INT,
  `dname` VARCHAR(15),
  `loc` VARCHAR(13)
);

需要注意的是：

  • SQL 语法是不区分大小写的，但是按惯例，关键字大写。
  • 严格语法中，数据库名 和 字段名 需要用 反单引号（`）括起来。
  • 如果 数据库名 和 字段名 的命名涉及多个单词，惯例是 下划线命名法 。

# 2. 约束

创建表时要确保数据的完整性，『约束』是保证数据完整性的重要技术手段。

通过约束，MySQL 数据库对于数据库操作行为进行限定，某些违反约束的操作行为会被 MySQL 数据库拒绝。

需要注意的是：

  • 『列级别约束』位于列声明的末尾，『表级别约束』位于表声明的尾部，是标准写法，但不是唯一的写法。
  • 按惯例，列级别约束一般都是写在列声明的尾部，而表级别约束可酌情考虑是否也出现在列声明的尾部。
  • 一个约束条件是列级别约束，还是表级别约束，取决于它的功能本身（是限定列，还是限定表），与它在声明中所处位置无关。

# 非空（Not Null）约束

非空（Not Null）是一个列级别 约束，它要求（未来）它所限定的列中不允许插入（或修改为）空值。

未指定该约束时，允许列中数据为 NULL 。

CREATE TABLE <表名> (
  字段名 数据类型 NOT NULL,
  ...
);

# 唯一性（Unique）约束

唯一性（UNIQUE）约束是一个列级别 约束，它要求（未来）它所限定的列中不允许插入（或修改）重复的数据。

未指定该约束时，允许列中数据有重复值。

CREATE TABLE <表名> (
  字段名 数据类型 UNIQUE,
  ...
);

# 默认值（Default）约束

默认值（DEFAULT）约束是一个列级别 约束，它要求（未来）它所限定的列中插入数据时，如果未指定具体值，那么就以此处声明的值作为插入值。

未指定该约束时，列中插入未指定数据时，默认值为 NULL 。当然，是否真的可以插入 NULL，还要看有没有 非空约束）。

CREATE TABLE <表名> (
  字段名 数据类型 DEFAULT <具体值>,
  ...
);

# 主键（Primary Key）约束

主键（PRIMARY KEY）约束是一个表级别 约束，它等价于 非空约束 + 唯一约束，即要求它所限定的列中值 非空且唯一 。

一张表中，主键约束最多只能有一个。

主键约束分两种：

  • 单主键，通常直接写在 列声明 的尾部。
  • 联合主键。只能出现在 表声明 的尾部。
CREATE TABLE <表名> (
  字段名 数据类型 PRIMARY KEY,
  ...
);

或者

CREATE TABLE <表名> (
  ...
  PRIMARY KEY(<字段1>, <字段2>, ...)
);

被主键约束修饰/修饰的列（额外地）被称为 主键列 。

主键列常常会结合使用 AUTO_INCREMENT 关键字，启用数据库的 自增长 功能。这并非一个标准功能，并非所有的数据库都支持 自增长 功能。MySQL 和 SQL Server 支持该功能。

自增长功能，只能用于整型列，且一张表只能使用一次。

# 外键（Foreign Key）约束

外键（FOREIGN KEY）约束是一个 表级别 约束，它用于表示，被它所 修饰/限定 的列中的数据，需要参照另一张表的某个列中出现的值。

外键列约束也是有两种“出现”方式，但是通常也是出现在标准位置：表声明的尾部。

CREATE TABLE <A表> (
  ...
  字段名 类型 REFERENCES B表(X字段)
  ...
);

CREATE TABLE <A表> (
  ...
  CONSTRAINT <约束名> FOREIGN KEY (A表的X字段名) REFERENCES B表(Y字段)
);

按照惯例，外键约束的名字通常以 fk_ 开始。

我们可以使用下述 SQL 语句『禁用』外键约束的约束校验功能：

SET FOREIGN_KEY_CHECKS = off;

另外，还可以使用下述 SQL 语句『启用』外键约束的约束校验功能：

SET FOREIGN_KEY_CHECKS = on;

注意，如果存在外键约束，建表时需要先建主表，后建从表；删除时，则相反。

# 3. 查看表的结构

# 查看表的字段信息

DESC <表名>;
DESCRIBE <表名>;

# 查看建表语句

SHOW CREATE TABLE <表名>;

# 4. 删除 Table

DROP TABLE [IF EXISTS] <表名>;

如果删除是一张被其他表所关联的表，那么必须先移除关联关系。

# 5. 修改表的元数据

# 添加主键约束

ALTER TABLE <表名> 
  ADD CONSTRAINT <主键约束名> 
  PRIMARY KEY <表名>(<主键字段>);

主键约束的名字习惯性形如：PK_表名

# 添加外键约束

ALTER TABLE <从表名> 
  ADD CONSTRAINT <外键约束名> 
  FOREIGN KEY <从表>(<外键字段>) 
  REFERENCES <主表>(<主键字段>);

外键约束的名字习惯性形如：FK_主表_主键字段

# 删除主键约束

ALTER TABLE <表名> DROP PRIMARY KEY;

# 删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键> ;

# 其他

略

# 6. MySql 中的数据类型总结

MySQL 数据库中支持很多数据类型，它们可以分成下面个主要类别：

  • 数值类型
  • 字符串类型
  • 日期和时间类型

# 数值类型：整数和浮点数

MySQL 的数值类型大致分为两类：一个是 整数 ，另一个是 浮点数 。

整数类型 存储大小 类比 Java 类型
TINYINT 1 字节 byte、Byte
SMALLINT 2 字节 short、Short
INT、INTEGER 4 字节 int、Integer
BIGINT 8 字节 long、Long

例如：

year INT(4)   # 最小显示宽度，不够补空格。与存储空间/取值范围无关
浮点和定点型 存储大小 类比 Java 类型
FLOAT 4 字节 float、Float
DOUBLE 8 字节 double、Double
DECIMAL(M, C) (M+2) 字节 BigDecimal

和编程中一样，FLOAT 和 DOUBLE 的运算都是 非精确 的，而 DECIMAL 类型是 精确 的。

DECIMAL 底层是以字符串的形式存储数值，因此不存在浮点型的精度问题。不指定精度默认为 (10, 0) ：有效数字 共 10 个，其中 小数点后 数字 0 个。

# 字符串类型

细分起来，MySQL 中的字符串类型有 8 种，一般而言分为：字符串 和 长文本 两个大类。

类型 存储 参考 Java 类型
CHAR(N) 定长，N 个字节，但最多 255 个字符 String
VARCHAR(N) 变长，实际长度+1个字节，但最多 65535 个字符 String
  • CHAR 类型的字符串的长度总是固定的。
  • VARCHAR 类型的字符串的长度总是字符串的真正长度。
类型 存储 参考 Java 类型
TINYTEXT 变长，实际长度+1个字节，但最多（2^8-1） 个字节 String
TEXT 变长，实际长度+2个字节，但最多（2^16-1）个字节 String
MEDIUMTEXT 变长，实际长度+3个字节，但最多（2^24-1）个字节 String
LONGTEXT 变长，实际长度+4个字节，但最多（2^32-1）个字节 String

# 日期/时间类型

类型 字符串形式格式 范围 存储大小 参考 Java 类型
DATE YYYY-MM-DD 1000-01-01
至
9999-12-31		 3 字节 java.util.Date
TIME HH:MM:SS -838:59:59
至
838:59:59		 3 字节 java.util.Date
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00
至
9999-12-31 23:59:59		 8 字节 java.util.Date

注意

DATETIME是 新特性，是从 MySQL 5.6.5 版本开始引入的新类型用以替代老的 TIMESTAMP 类型。TIMESTAMP 类型的表示范围只有 1970-01-01 00:00:00 ~ 2038-01-19 03:14:07

MySQL 有个很好的特性，对于 日期/时间 类型，可以直接输入 符合其形式的字符串 即可，MySQL 内部会自动将字符串转换成日期和时间类型格式的数据。

MySQL 有三个内置变量，通过它们可以查询返回（字符串形式的）『当前日期』，『当前时间』 和 『当前日期时间』。

SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP FROM dual;

# 枚举类型

绝大多数编程领域和数据库领域都在 尽量避免 使用枚举类型。

← 查询语句 →