MySQL CREATE TABLE 创建表

在本文中,我们介绍了在 MySQL 服务器上使用 CREATE TABLE 语句创建新表。

表是关系数据库中数据存储的基本单位。在 MySQL 中,CREATE TABLE 语句用来创建表。

CREATE TABLE 语法

我们使用 CREATE TABLE 语句在数据库中创建一个新表。 CREATE TABLE 语句的语法如下:

CREATE TABLE [IF NOT EXISTS] table_name (
   column_name data_type [NOT NULL | NULL] [DEFAULT expr],
   column_name data_type [NOT NULL | NULL] [DEFAULT expr],
   ...,
   [table_constraints]
) [ENGINE=storage_engine];

说明:

  • CREATE TABLE 语句创建一个给定名字 table_name 的表。

    • 表名可由字母、数字、下划线和美元符号组成,表名长度在 64 个字符以内。
    • 表名在一个数据库中是唯一的。
    • 新建的表会在当前默认的数据库中。如果还没有选择数据库,请使用 db_name.table_name 格式指定要新建的表所在的数据库。
  • IF NOT EXISTS 指示只有给定的表不存在的时候才进行创建。它是可选的。

    如果你给定一个已经存在的表名,又没有使用 IF NOT EXISTS 子句,服务器会返回一个错误。

  • column_name data_type [NOT NULL | NULL] [DEFAULT expr] [AUTO_INCREMENT] 定义了表中的一列。多个列使用逗号分隔。

    • column_name 是列的名字。列名可由字母、数字、下划线和美元符号组成,列名长度在 64 个字符以内。列名在一个表中是唯一的。
    • data_type 是数据类型,可以是 CHAR, VARCHAR, INT, DATE, DATETIME, BIT, TEXT, ENUM, JSON, BOLB 等。
    • [NOT NULL | NULL] 指示该列是否可以为 NULL。它是可选的。如果不指定该选项,则此列可以为 NULL。如果设置为 NOT NULL,则插入新行时该列必须有值。
    • [DEFAULT expr] 指示该列的默认值。它是可选的。如果不指定该选项,则此列的默认是 NULL
    • [AUTO_INCREMENT] 指示该列是否是一个自增列。如果使用了此选项,则该列的值可有服务器自动产生和填充。该列的值从 1 开始,每增加一个行就会加 1。一个表中只能有一个自增列。
  • [table_constraints] 位于列定义之后,它定义了表的约束。它是可选的。表的约束有主键外键CHECKUNIQUE 等。

  • ENGINE=storage_engine 子句指定了表使用的存储引擎。它是可选的。

    如果不指定此选项,则采用服务器默认的存储引擎。自 MySQL 5.5 版以来,服务器默认的引擎是由 MyISAM 变成了 InnoDB。

  • 当表名或者字段名中含有空格或者其他特殊字字符时,请使用 ` 包围起来。比如: `test 1`

CREATE TABLE 实例

以下实例将在 testdb 数据库中创建 useruser_hobby 两个表。请按照如下步骤执行。

  1. 使用 root 用户登录 MySQL 数据库:

    mysql -u root -p
    

    根据提示输入 root 用户的密码。

    注意:您也可以使用其他任何具有相应的数据库权限的用户登录。

  2. 使用以下语句选择 testdb 数据库

    USE testdb;
    

    如果还未创建数据库,请先运行如下语句:

    CREATE DATABASE testdb;
    
  3. 使用以下语句创建 user 表:

    CREATE TABLE `user` (
      `user_id` INT NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(45) NOT NULL,
      `age` INT NULL,
      `locked` TINYINT NOT NULL DEFAULT 0,
      `created_at` DATETIME NOT NULL,
      PRIMARY KEY (`user_id`));
    

    如果你没有操作第 2 部中的选择数据库,请将表名替换为 `testdb`.`user`

    这里创建的 user 表有 5 个字段:

    • user_id 列的数据类型是 INT,它不能为 NULL,并且它是一个自增列。
    • name 列的数据类型是 VARCHAR,它最多为 45 个字符。 它不能为 NULL
    • age 列的数据类型是 INT。它可以是 NULL
    • locked 列的数据类型是 TINYINT。它不能为 NULL,但是它有默认值 0
    • created_at 列的数据类型是 DATETIME。它不能为 NULL

    user 表的约束有:

    • PRIMARY KEY (`user_id`) 子句表明 user_id 列是主键
  4. 使用以下语句创建 user_hobby 表:

    CREATE TABLE `testdb`.`user_hobby` (
      `hobby_id` INT NOT NULL AUTO_INCREMENT,
      `user_id` INT NOT NULL,
      `hobby` VARCHAR(45) NOT NULL,
      `created_at` DATETIME NOT NULL,
      INDEX `fk_user_idx` (`user_id` ASC) VISIBLE,
      PRIMARY KEY (`hobby_id`),
      CONSTRAINT `fk_user`
        FOREIGN KEY (`user_id`)
        REFERENCES `testdb`.`user` (`user_id`)
        ON DELETE CASCADE
        ON UPDATE RESTRICT);
    

    这里创建的 user_hobby 表有 4 个字段:

    • hobby_id 列的数据类型是 INT,它不能为 NULL,并且它是一个自增列。
    • user_id 列的数据类型是 INT。它不能为 NULL。它通过外键指向了 user 表的 user_id 列。
    • hobby 列的数据类型是 VARCHAR,它最多为 45 个字符。 它不能为 NULL
    • created_at 列的数据类型是 DATETIME。它不能为 NULL

    user_hobby 表的约束有:

    • PRIMARY KEY (`hobby_id`) 子句表明 hobby_id 列是主键。
    • INDEX `fk_user_idx` 设定了在 user_id 列上建立索引。
    • CONSTRAINT `fk_user` 设定了一个外键。这个外键将 user_id 列引用了 user 表的 user_id
  5. 我们创建好 useruser_hobby 后,可以向表中插入数据了。请使用如下语句:

    INSERT INTO user (name, age, created_at) VALUES ('Jim', 18, NOW());
    
    INSERT INTO user_hobby (user_id, hobby, created_at) VALUES (1, 'Football', NOW());
    INSERT INTO user_hobby (user_id, hobby, created_at) VALUES (1, 'Swimming', NOW());
    

CREATE TABLE … LIKE

CREATE TABLE ... LIKE 语句可以用来克隆另一个表的定义。它以另一个表的定义为基础创建一个新的空表,包含了原表中定义的列属性和索引。CREATE TABLE ... LIKE 语句的语法如下:

CREATE TABLE new_table LIKE original_table;

CREATE TABLE ... LIKE 语句创建的是一个空表。

CREATE TABLE … SELECT

你可以使用 CREATE TABLE ... SELECT 语句从另一个表创建一个新表。该语句会一句 SELECT 子句中的列创建新表,并将 SELECT 的结果集插入到新表中。 CREATE TABLE ... SELECT 语句语法如下:

CREATE TABLE new_table [AS] SELECT * FROM original_table;

CREATE TABLE ... SELECT 语句可以用来复制一个表,包含列属性和数据。

结论

在本文中,我们讨论了在 MySQL 中使用 CREATE TABLE 语句创建一个新表。本文要点如下:

  • CREATE TABLE 关键词后跟要创建的表名。如果未选择数据库,则需要使用 db_name.table_name 指定表名。
  • 一个表总可以包含多个列,多个列使用逗号分隔。
  • NOT NULL | NULL 指示该列是否可以为 NULL
  • AUTO_INCREMENT 指示该列是否是一个自增列。
  • DEFAULT 子句为列设置一个默认值。
  • PRIMARY KEY 子句设置一个或多个列为主键。
  • FOREIGN KEY 子句设置一个或多个列为外键。

除了创建表,我们还可以修改已有的表删除表