sql简介_SQL表简介

sql简介

In this article, we will learn the concept of SQL tables and then work on how we can create tables with different techniques in SQL Server.

在本文中，我们将学习SQL表的概念，然后研究如何在SQL Server中使用不同的技术创建表。

A relational database model is one of the most used data models to store and process the data. Tables are the essential elements of a database. In this context, tables are the database objects that hold the data in the relational databases. A database contains one or more tables and these tables can be modeled as relational. The tables come into existence from the columns and every column must have a name and a data type. These columns store data according to the defined data types and these data records are called rows. The following illustration shows an example of a table structure.

关系数据库模型是用于存储和处理数据的最常用的数据模型之一。表是数据库的基本元素。在这种情况下，表是在关系数据库中保存数据的数据库对象。数据库包含一个或多个表，并且这些表可以建模为关系表。这些表是由列组成的，每列都必须具有名称和数据类型。这些列根据定义的数据类型存储数据，这些数据记录称为行。下图显示了表结构的示例。

As we can see, the Persons table consists of Id, Name, Surname, and Age columns. These columns hold either numeric or text data according to defined data types. Now, we will learn how to create tables with different techniques.

如我们所见， Persons表由Id ， Name ， Surname和Age列组成。这些列根据定义的数据类型保存数字或文本数据。现在，我们将学习如何使用不同的技术创建表。

使用SQL Server Management Studio（SSMS）创建表 (Create a table using SQL Server Management Studio (SSMS))

SQL Server Management Studio is an IDE (integrated development environment) that helps to manage SQL Server and building T-SQL queries. So, it is very easy to create a table through the SSMS. After connecting to the database, we right-click on the Tables folder and select the New on the pop-up menu and click the Table option.

SQL Server Management Studio是一个IDE（集成开发环境），可帮助管理SQL Server和构建T-SQL查询。因此，通过SSMS创建表非常容易。连接到数据库后，我们右键单击Tables文件夹，然后在弹出菜单上选择New ，然后单击Table选项。

A New Table customized window will be shown to create a table quickly. At the same time, we can find the properties of the selected column at the bottom of the same screen.

将显示一个新表自定义窗口快速创建表。同时，我们可以在同一屏幕的底部找到所选列的属性。

On the New Table window, we can define the columns with names and data types. Each column must have a data type and a unique name.

在“新建表”窗口中，我们可以定义具有名称和数据类型的列。每列必须具有数据类型和唯一名称。

Tip: When we checked the Allow Nulls option enables us to store null values to the associated column.

提示：选中“允许空值”选项后，我们可以将空值存储到关联的列中。

When we right-click on any column, we can set some options for this column. The right-arrow indicates which column we are working on.

当我们右键单击任何列时，我们可以为此列设置一些选项。右箭头指示我们正在处理的列。

Set Primary Key: The primary key is a value or combination of values that help to uniquely identify each row on the table. For this table, we will set the Id column as a primary key. After setting a column as a primary key, a key sign will be appeared on the column.

设置主键： 主键是一个值或值的组合，有助于唯一地标识表中的每一行。对于此表，我们将Id列设置为主键。将列设置为主键后，键符号将出现在列上。

When we try to check the Allow Nulls option for a column that has been set as a primary key, SSMS returns an error.

当我们尝试检查已设置为主键的列的“ 允许空值”选项时，SSMS返回错误。

As we stated, SQL tables allow us to specify composite primary keys. Only we need to select more than one column with the help of the shift key and click the Set Primary Key option.

如前所述，SQL表允许我们指定复合主键。只有我们需要在shift键的帮助下选择多个列，然后单击Set Primary Key选项。

The key sign will be shown for these multiple columns.

这些多列将显示关键标志。

Relationships: This option provides to define a foreign key relationship with the other tables.

关系：此选项用于定义与其他表的外键关系。

Indexes/Keys: Through this option, we can create indexes or set unique constraints for the columns.

索引/键：通过此选项，我们可以创建索引或为列设置唯一约束。

Check Constraints: Check constraints are used to control according to specified rule the data that will be stored by the columns. This option provides to create this type of rules (constraints).

检查约束： 检查约束用于根据指定规则控制将由列存储的数据。此选项提供创建这种类型的规则（约束）的功能。

Properties: When we select this option for any column, we can reach table property windows.

属性：当我们为任何列选择此选项时，我们可以访问表属性窗口。

On this screen, we can change the table name and other properties of the table. As a final step, we will click the save button or the CTRL+S key combination. The created table will be shown under the Tables folder.

在此屏幕上，我们可以更改表名称和表的其他属性。最后一步，我们将单击“保存”按钮或CTRL + S组合键。创建的表将显示在“ 表”文件夹下。

使用T-SQL创建表 (Create a table using T-SQL)

The CREATE TABLE statement is used to create a new table in SQL Server. The query below creates the Persons table. After the CREATE TABLE statement, we define the table name. In the brackets, we specify the column names and data types. Additionally, we set the Id column as a primary key.

CREATE TABLE语句用于在SQL Server中创建新表。下面的查询创建人员表。在CREATE TABLE语句之后，我们定义表名称。在方括号中，我们指定列名称和数据类型。此外，我们将Id列设置为主键。

CREATE TABLE  [Persons]
([Id]      [INT]
 PRIMARY KEY, 
 [Name]    [VARCHAR](50) NOT NULL, 
 [SurName] [VARCHAR](50) NOT NULL, 
 [Age]     [SMALLINT] NOT NULL
)

We get an error after executing the query because a table with the same name exists under the same schema. For this reason, before creating a table, we need to check the existence of the table thus we can avoid this type of errors. The DROP TABLE condition will be executed if the Persons table already exists on the database.

由于执行相同查询的表存在于同一模式下，因此执行查询后会出现错误。因此，在创建表之前，我们需要检查表的存在，从而可以避免此类错误。如果数据库中已经存在Persons表，则将执行DROP TABLE条件。

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = N'Persons')
BEGIN
DROP TABLE Persons
END
GO
CREATE TABLE  [Persons]
([Id]      [INT]
PRIMARY KEY, 
[Name]    [VARCHAR](50) NOT NULL, 
[SurName] [VARCHAR](50) NOT NULL, 
[Age]     [SMALLINT] NOT NULL
)
 
GO
SELECT * FROM Persons

从现有表创建表 (Create a table from an existing table)

We can create a new table from the existing table. SELECT INTO statement creates a new table and inserts the result set of the SELECT query to the new table. However, if we want to create an empty copy of a table we can use the following method. This method uses a WHERE condition that causes an empty result set to be returned from the query.

我们可以从现有表创建一个新表。 SELECT INTO语句创建一个新表，并将SELECT查询的结果集插入到新表中。但是，如果要创建表的空副本，可以使用以下方法。此方法使用WHERE条件，该条件导致从查询返回空结果集。

SELECT * INTO CopyPersons FROM Persons 
WHERE 1=0
GO
SELECT * FROM CopyPersons

The disadvantage of this method is that it does not copy the indexes and constraints from source table to destination (new) table. Such as, we know that the Id column is the primary key for the Person table but this attribute does not transfer to the CopyPerson table.

此方法的缺点是它不会将索引和约束从源表复制到目标（新）表。例如，我们知道Id列是Person表的主键，但是此属性不会转移到CopyPerson表。

使用Azure Data Studio创建表 (Create a table using Azure Data Studio)

Azure Data Studio is a new lightweight tool that enables us to execute queries on-premises or cloud databases and it also helps to manage databases. Code Snippets is one of the main advantages of the Azure Data Studio, these templates assist us in typing queries to generate proper syntax easily. After opening a new query window, we type “CREATE TABLE” to create a SQL table and select the sqlCreateTable snippet.

Azure Data Studio是一种新的轻量级工具，使我们能够在本地或云数据库中执行查询，还有助于管理数据库。代码段是Azure Data Studio的主要优点之一，这些模板可帮助我们键入查询以轻松生成正确的语法。打开新的查询窗口后，我们键入“ CREATE TABLE ”以创建一个SQL表并选择sqlCreateTable代码段。

After selecting the template, the query table will be automatically typed into the query window.

选择模板后，查询表将自动输入到查询窗口中。

After modifying the required fields the template, the CREATE TABLE statement will be completed.

修改模板的必填字段后，将完成CREATE TABLE语句。

结论 (Conclusion)

In this article, we learned the fundamentals of the SQL tables in the relational databases and then learned different techniques used to create tables. We can use the method that is easiest and convenient for us.

在本文中，我们学习了关系数据库中SQL表的基础知识，然后学习了用于创建表的不同技术。我们可以使用最简单方便的方法。