Sqlite 简明教程

SQLite - CREATE Table

SQLite CREATE TABLE 语句用于在给定的任何数据库中创建新表。创建基本表包括对表命名以及为其列和每个列的数据类型定义。

SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column’s data type.

Syntax

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

Following is the basic syntax of CREATE TABLE statement.

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

CREATE TABLE 是一个关键字，用于指示数据库系统创建新表。CREATE TABLE 语句后面跟表唯一的名称或标识符。可以选择指定 database_name 和 table_name。

CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally, you can specify database_name along with table_name.

Example

以下是一个示例，它使用 ID 创建一个 COMPANY 表，其中 ID 为主键，并且 NOT NULL 是约束，它表明在表中创建记录时这些字段不能为 NULL。

Following is an example which creates a COMPANY table with ID as the primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table.

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

让我们在后续章节中创建另一个表，我们将在我们的练习中使用该表。

Let us create one more table, which we will use in our exercises in subsequent chapters.

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

您可以使用 SQLite 命令 .tables 来验证是否成功创建了该表，该命令将用于列出附加数据库中的所有表。

You can verify if your table has been created successfully using SQLite command .tables command, which will be used to list down all the tables in an attached database.

sqlite>.tables
COMPANY     DEPARTMENT

在这里，您可以看到 COMPANY 表两次，因为它显示了 main 数据库的 COMPANY 表以及为 testDB.db 创建的“test”别名的 test.COMPANY 表。您可以使用以下 SQLite .schema 命令获取有关表的完整信息。

Here, you can see the COMPANY table twice because its showing COMPANY table for main database and test.COMPANY table for 'test' alias created for your testDB.db. You can get complete information about a table using the following SQLite .schema command.

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);