带你在SQL中创建表-- MySQL储存引擎、数据类型、规则

迪鲁宾
134 阅读5分钟

知道如何在SQL 中创建表是一个重要的基本概念。

CREATE TABLE 在本教程中，我将利用PostgreSQL和MySQL的代码实例，引导你了解SQL 语句的语法。

基本的CREATE TABLE 语法

下面是CREATE TABLE 语句的基本语法。

CREATE TABLE table_name(
	column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc
);

对于第一部分，你需要以CREATE TABLE 语句开始，然后是你要创建的表的名称。

如果我想创建一个教师信息表，那么我将这样写。

CREATE TABLE teachers();

在括号内，你将添加用于创建表的列的信息。如果你忘记了圆括号，那么你将得到一个错误信息。

CREATE TABLE teachers;

小括号末尾的分号告诉计算机它是SQL 语句的结束。你有时会听到这被称为语句终止符。

什么是MySQL 存储引擎？

根据MySQL 文档。

存储引擎是处理不同表类型的SQL操作的MySQL组件。

MySQL 使用这些存储引擎在数据库上执行CRUD（创建、读取、更新和删除）操作。

在MySQL ，你可以选择指定你想为表使用的存储引擎的类型。如果你省略了ENGINE 子句，那么默认将是InnoDB。

CREATE TABLE table_name(
	column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc
)ENGINE=storage_engine;

什么是IF NOT EXISTS 子句？

有一个可选的子句叫做IF NOT EXISTS ，它将检查你要创建的表是否已经存在于数据库中。 你可以把这个子句放在表名的前面。

CREATE TABLE IF NOT EXISTS teachers();

如果表已经存在，那么计算机将不会创建一个新的表。

如果你省略了IF NOT EXISTS 子句，并试图创建一个已经存在于数据库中的表，那么你将得到一个错误信息。

在这个例子中，我首先创建了一个名为教师的表。但是如果我试图在下一条命令中创建同样的表，我将会遇到一个错误。

CREATE TABLE IF NOT EXISTS teachers();
CREATE TABLE teachers();

如何在表中创建列

在CREATE TABLE 语句的括号内，你要列出你要创建的列的名称以及它们的数据类型和约束条件。

这是一个例子，我们可以在我们的教师表中添加四列：school_id,name,email 和age 。每一个列名都应该用逗号隔开。

CREATE TABLE teachers(
	school_id data_type column_constraint, 
	name data_type column_constraint,
    email data_type column_constraint, 
	age data_type column_constraint
);

根据MySQL 文档。

MySQL有一个每张表4096列的硬性限制，但对于一个给定的表，有效的最大限度可能更少。确切的列限制取决于几个因素。

如果你正在做较小的MySQL 个人项目，那么你可能不必担心超过你的表的列数的问题。

根据PostgreSQL的文档，每个表有1600列的限制。与MySQL ，确切的限制可以根据磁盘空间或性能限制而变化。

数据类型在SQL

当你在表中创建列时，你需要给它分配一个数据类型。数据类型描述了列内的值的类型。

下面是SQL 中六种流行的数据类型。

  1. 数字（int, float, serial, decimal, etc.
  2. 数据和时间（时间戳、数据、时间等
  3. 字符和字符串（char、varchar、text等
  4. Unicode（ntext, nvarchar等
  5. 二进制（二进制，等
  6. 杂项（xml、表等）。

这篇文章不会对每一种数据类型进行介绍，但会涵盖一些流行的数据类型。

下面是PostgreSQL 数据类型和MySQL 数据类型的完整列表。

什么是SERIAL 和AUTO_INCREMENT ？

在PostgreSQL ，SERIAL 数据类型是一个整数，每创建一个新行，它就会自动递增1。

我们可以在教师表中的school_id 列之后添加SERIAL 。

school_id SERIAL

在MySQL 中，你将使用AUTO_INCREMENT 而不是SERIAL 。在这个例子中，使用了INT 数据类型，代表一个整数。

school_id INT AUTO_INCREMENT

如果我们在教师表中增加五行，输出结果将显示school_id 列的数字1, 2, 3, 4, 5，因为每增加一行，整数会自动递增1。

什么是VARCHAR 数据类型？

VARCHAR 数据类型是一个可变的字符串长度，你可以设置一个最大的字符长度。

这是一个使用VARCHAR 数据类型的例子，用于我们教师表中的name 和email 列。数字30是最大的字符长度。

name VARCHAR(30) column_constraint,
email VARCHAR(30) column_constraint,

SQL中的列约束

这些是应用于表列内数据的规则。

下面是一些比较常见的列约束的列表。

  • PRIMARY KEY - 这个键作为表的唯一标识符。
  • FOREIGN KEY - 这个键确保一个列中的值也存在于另一个表中。这可以作为表之间的联系。
  • UNIQUE - 列中的所有值都必须是唯一的。
  • NOT NULL--数值不能是NULL。NULL是指没有一个值
  • CHECK - 根据一个布尔表达式测试一个值

PRIMARY 和FOREIGN 键的例子

在我们的教师表中，我们可以在school_id 列中添加一个PRIMARY KEY 。

这就是PostgreSQL中的代码。

 school_id SERIAL PRIMARY KEY

这是在MySQL中的代码。

school_id INT AUTO_INCREMENT PRIMARY KEY

如果你想有一个以上的PRIMARY KEY 列，那么你就在你的列创建之后添加它。

CREATE TABLE table_name(
	column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc
    PRIMARY KEY (column1, column2)
);

如果你想把一个表连接到另一个表，那么你可以使用FOREIGN KEY 。

假设我们有一个叫做 district_employees 的表，主键是district_id 。下面是PostgreSQL中的代码。

CREATE TABLE district_employees(
   district_id SERIAL PRIMARY KEY,
   employee_name VARCHAR(30) NOT NULL,
   PRIMARY KEY(district_id)
);

在我们的教师表中，我们可以使用一个外键并引用 district_employees 表。

district_id INT REFERENCES district_employees(district_id),

CREATE TABLE teachers(
    school_id SERIAL PRIMARY KEY,
    district_id INT REFERENCES district_employees(district_id),
    column1 data_type column_constraint,
    column2 data_type column_constraint,
    column3 data_type column_constraint,
    column4 data_type column_constraint,
    ... etc 
);

NOT NULL,CHECK 和的例子。UNIQUE

如果我们想确保没有任何值是空的，我们可以使用NOT NULL 约束。

name VARCHAR(30) NOT NULL

我们可以使用CHECK 约束来确保我们所有的教师都是18岁以上的。 CHECK 约束对一个值与一个布尔表达式进行测试。

age INT CHECK(age >= 18)

如果我们的一个值不符合该条件，那么我们将得到一个错误信息。

我们可以使用UNIQUE 约束来确保所有的电子邮件是唯一的。

email VARCHAR(30) UNIQUE

这就是教师表的最终结果。

这是代码在PostgreSQL中的样子。

CREATE TABLE teachers(
	school_id SERIAL PRIMARY KEY,
	name VARCHAR(30) NOT NULL,
    email VARCHAR(30) UNIQUE,
	age INT CHECK(age >= 18)      
);

这是在MySQL中的代码。

CREATE TABLE teachers(
	school_id INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(30) NOT NULL,
    email VARCHAR(30) UNIQUE,
	age INT CHECK(age >= 18)      
);
avatar
文章
阅读
粉丝