MySQL-4.建表语句和约束条件

本文地址：https://www.jianshu.com/p/4d09d98e87da

一、数据表简介

在MySQL中，存储数据的结构就是Table这样的二维数据表，也即是所谓的关系模型，每张表中可以存储一条或多条字段，如下所示：

mysql> select * from student;
+---------+------+------+
| name    | age  | sex  |
+---------+------+------+
| Tom     |   16 | M    |
| Charlie |   18 | M    |
| Emma    |   15 | F    |
+---------+------+------+
3 rows in set (0.00 sec)

每张表中的第一行是这张表的表头，表示这张表可以存储哪些数据。之下每一行就是一条数据，而每一列就代表一个字段。

使用DESCRIBE或者DESC命令可以查看表的结构和每个字段的数据类型。

mysql> DESCRIBE student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(20)  | YES  |     | NULL    |       |
| age   | tinyint(255) | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

---

二、约束条件

所谓约束条件，指的是对于字段，除了数据类型和数据长度以外其他的规定，例如非空、自增长等。

• NOT NULL(非空)

  如果没有指定NOT NULL这个约束条件的话，那么插入一条数据的时候，这个字段是可以不插入值的，这个时候，MySQL会自动将这个字段的值设置为NULL。

  而如果设置了NOT NULL约束条件，那么在插入数据的时候，这个字段必须有值，否则会报错。

  name VARCHAR(20) NOT NULL;
  

• AUTO_INCREMENT(自增长)

  整数类型或浮点数类型，可以设置这个约束条件，每张表中只能设置一个AUTO_INCREMENT字段，且不能设置默认值DEFAULT。AUTO_INCREMENT字段必须为一个KEY。

  当你在插入数据的时候，如果给这个字段插入NULL或者0，甚至不插入值时，这个字段会自动找出这个字段中最大的值，并将这个值+1并填充到当前这条数据中。

  id INT(11) AUTO_INCREMENT,
  

• DEFAULT(默认值)

  指定默认值，如果插入数据时，这个字段没有插入值，那么将会使用默认值来填充此字段。

  name VARCHAR(20) DEFAULT 'nobody',
  

• UNIQUE(唯一)

  当我们希望某一个字段的值都唯一的情况下，我们可以给这个字段指定UNIQUE约束条件。如果在插入一条新数据时，给一个UNIQUE字段插入一个已经存在的值，那么将会抛出一个异常。

  需要注意的是，UNIQUE不会把NULL视为重复的值，也就是说，UNIQUE字段中可以包含多个NULL。

  id INT(11) UNIQUE,
  

  如果需要在建表之后再设定某个字段为UNIQUE，可以用这种方式：

  ALTER TABLE tb_name ADD UNIQUE KEY(number);
  

• PRIMARY KEY(主键)

  主键通常是用来作为一条数据的唯一标识，所以主键字段的值必须是唯一的，且这个字段必须设置为NOT NULL，每一张表中也只能设置一个主键。

  当没有设置主键的时候，表中第一个出现的NOT NULL且UNIQUE字段，将会被视为主键。

  id INT(11) PRIMARY KEY,
  

  当建表之后需要设置主键时：

  ALTER TABLE tb_name ADD PRIMARY KEY (id);
  

  主键还可以被撤销：

  ALTER TABLE tb_name DROP PRIMARY KEY;
  

• FOREIGN KEY(外键)

  外键可以将表中的某一个字段与另一张表中的一个字段联系起来，从而为两张表建立联系。

  CONSTRAINT foreign_name FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
  

  详细使用请查看表关系章节。

---

三、建表语句(CREATE TABLE)

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tb_name
    (create_definition,...)
    [table_options]
    [partition_options]

关键字：

• TEMPORARY - 可以使用此关键字创建临时表，临时表只在当前会话中可见，并且会在会话关闭时自动删除。
• IF NOT EXISTS - 当表中不存在此表时，才执行CREATE TABLE语句，以免创建重名表引发异常。
• create_definition - 在tb_name字段后方括号中的，是表中定义字段的语句，定义多条字段字段时用逗号分隔。
• table_options - 用来优化表的选项。在大多数时候不需要指定。可以在这里指定表的引擎(engine)，如果没有指定的话则默认使用InnoDB引擎。
• partition_optins - 可以用于创建表的分区。

定义字段

col_name data_type[size] [NOT NULL|NULL] [DEFAULT value] [AUTO_INCREMENT],

除了需要在这里指定字段的名字、数据类型和约束条件之外，还有一些选项可以在此指定：

• COMMENT(注释)

  使用COMMENT可以为字段写入注释，最多1024个字符。如果要显示注释的话需要使用SHOW CREATE TABLE或者SHOW FULL COLUMNS语句。

• UNSIGNED(无符号)

  数值类型可以使用UNSIGNED来指定数据只表示正数，这样的话能使字段表示的最大范围翻倍。

• ZEROFILL(用0填充)

  当数值类型插入的数据长度比设定的最大长度小时，设定了ZEROFILL选项的字段将自动在数据的左端填充0，将数据补充到设定的最大长度。

---

四、修改表结构(ALTER TABLE)

可以使用ALTER TABLE语句来更改现有表的结构。 ALTER TABLE语句可用来添加列，删除列，更改列的数据类型，添加主键，重命名表等等。 以下是ALTER TABLE语句语法：

ALTER TABLE tb_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

需要注意的是多条修改语句间需要使用,分割。

以下是一些ALTER TABLE的使用例子：

• 重命名表

  ALTER TABLE tb_name RENAME new_name;
  

• 添加字段

  ALTER TABLE tb_name ADD col_name TIMESTAMP;
  

  如果需要指定添加字段的位置，那么可以在后面跟上一个AFTER子句，添加到某个字段下方。

  ALTER TABLE tb_name ADD col_name TIMESTAMP AFTER col_name2;
  

  使用AFTER能添加到除了第一行之外的所有位置，那么想添加到第一行的话，需要使用FIRST。

  ALTER TABLE tb_name ADD col_name TIMESTAMP FIRST;
  

• 删除字段

  ALTER TABLE tb_name DROP COLUMN col_name;
  

• 重命名并修改字段

  ALTER TABLE tb_name CHANGE old_col_name new_col_name data_type;
  

• 修改字段

  ALTER TABLE tb_name MODIFY col_name data_type;