sql创建表_SQL创建表

最新推荐文章于 2024-08-07 22:07:28 发布

cunchi4221

最新推荐文章于 2024-08-07 22:07:28 发布

阅读量1.8w

点赞数 7

文章标签：数据库 mysql sql java postgresql

原文链接：https://www.journaldev.com/24795/sql-create-table

版权

sql创建表

When we have to store data in relational databases, the first part is to create the database. Next step is to create a table in the database that will store our data. In this tutorial, we will discuss how to create a table using SQL queries in MySQL and PostgreSQL databases.

当我们必须将数据存储在关系数据库中时，第一部分是创建数据库。下一步是在数据库中创建一个表来存储我们的数据。在本教程中，我们将讨论如何在MySQL和PostgreSQL数据库中使用SQL查询创建表。

I am not covering SQL Server create table examples, because they are similar to PostgreSQL queries.

我没有介绍SQL Server创建表的示例，因为它们类似于PostgreSQL查询。

SQL创建表 (SQL Create Table)

In order to store data in a table, it is very important to understand the type of data that needs to be stored. Let us try to understand the syntax for creating a table.

为了将数据存储在表中，了解需要存储的数据类型非常重要。让我们尝试了解创建表的语法。

SQL创建表语法 (SQL Create Table Syntax)

CREATE TABLE table_name( column1 datatype, column2 datatype,... column-N datatype, PRIMARY KEY(one or more column) );

CREATE TABLE is the keyword to tell the database to create a table.
CREATE TABLE是告诉数据库创建表的关键字。
table_name is the unique name that is used for the table.
table_name是用于表的唯一名称。
The brackets that are next to the table name contains the list of columns.
表名称旁边的方括号包含列列表。
The list contains the column name and the data type that can be stored in the respective columns.
该列表包含列名称和可以存储在相应列中的数据类型。
PRIMARY KEY is used to specify the columns to be used for primary key.
PRIMARY KEY用于指定要用于主键的列。

具有一列主键SQL创建表 (SQL Create Table with one column Primary Key)

When we create a table, we have to provide the primary key information along with the column structure. Let’s look at some example to create a table with a single column as the primary key.

创建表时，我们必须提供主键信息以及列结构。让我们看一些示例，创建一个以单列作为主键的表。

MySQL (MySQL)

CREATE TABLE `test`.`student` (
`studentId` INT NOT NULL,
`studentName` VARCHAR(45) NULL,
`State` VARCHAR(45) NULL,
`Country` VARCHAR(45) NULL,
PRIMARY KEY (`studentId`),
UNIQUE INDEX `studentId_UNIQUE` (`studentId` ASC) VISIBLE);

Above query will create a new table “Student” with the primary key column as “studentId”. Notice that every column name has a data type defined. For example, we can store only INT data in the studentId column whereas we can store VARCHAR data in the studentName column. VARCHAR(45) means that the maximum size of the string data allowed is 45 characters. Since the primary key can’t be null, we specify it in the studentId column definition.

上面的查询将创建一个新表“ Student”，主键列为“ studentId”。请注意，每个列名都有一个定义的数据类型。例如，我们只能在studentId列中存储INT数据，而可以在studentName列中存储VARCHAR数据。 VARCHAR（45）表示允许的字符串数据的最大大小为45个字符。由于主键不能为null，因此我们在studentId列定义中指定它。

SQL Create Table – MySQL

SQL创建表– MySQL

PostgreSQL (PostgreSQL)

We can create a table in PostgreSQL database using the following query.

我们可以使用以下查询在PostgreSQL数据库中创建表。

CREATE TABLE "test.student"(
"StudentId" integer NOT NULL,
"StudentName" character varying(45),
"State" character varying(45),
"Country" character varying(45),
PRIMARY KEY ("StudentId")
);

SQL Create Table – PostgreSQL

SQL创建表– PostgreSQL

具有多个主键SQL创建表 (SQL Create Table with Multiple Primary Keys)

Let’s look at another example where we will use multiple columns in the primary key.

让我们看另一个例子，我们将在主键中使用多个列。

MySQL (MySQL)

CREATE TABLE `test`.`customer` (
  `CustomerId` INT NOT NULL,
  `CustomerName` VARCHAR(45) NULL,
  `ProductId` VARCHAR(45) NOT NULL,
  `State` VARCHAR(45) NULL,
PRIMARY KEY (`CustomerId`, `ProductId`),
UNIQUE INDEX `CustomrId_UNIQUE` (`CustomerId` ASC) VISIBLE);

Above query will create “customer” table in the “test” database schema. The primary key of this table is the combination of CustomerId and ProductId.

上面的查询将在“测试”数据库模式中创建“客户”表。该表的主键是CustomerId和ProductId的组合。

SQL Create Table – Multiple Column Primary Key – MySQL

SQL创建表–多列主键– MySQL

PostgreSQL (PostgreSQL)

CREATE TABLE "test.customer"(
"CustomerId" integer NOT NULL,
"CustomerName" character varying(45),
"ProductId" character varying(45),
"Country" character varying(45),
PRIMARY KEY ("CustomerId","ProductId")
);