sql ddl中key_SQL DDL：SQL Server中SQL DDL命令入门

sql ddl中key

This article explains SQL DDL commands in Microsoft SQL Server using a few simple examples.

本文使用一些简单示例说明Microsoft SQL Server中SQL DDL命令。

SQL commands broadly fit into four categories:

SQL命令大致分为四类：

• DDL (Data Definition Language)
  DDL（数据定义语言）
• DML (Data Manipulation Language)
  DML（数据处理语言）
• DCL (Data Control Language)
  DCL（数据控制语言）
• TCL (Transactional Control Language)
  TCL（交易控制语言）

This article only covers the SQL DDL commands.

本文仅介绍SQL DDL命令。

SQL DDL命令 (SQL DDL commands)

The DDL commands in SQL are used to create database schema and to define the type and structure of the data that will be stored in a database. SQL DDL commands are further divided into the following major categories:

SQL中的DDL命令用于创建数据库架构以及定义将存储在数据库中的数据的类型和结构。 SQL DDL命令进一步分为以下主要类别：

• CREATE
  创造
• ALTER
  改变
• DROP
  下降
• TRUNCATE
  截短

创造 (CREATE)

The CREATE query is used to create a database or objects such as tables, views, stored procedures, etc.

CREATE查询用于创建数据库或对象，例如表，视图，存储过程等。

建立资料库 (Creating a database)

The following example demonstrates how the CREATE query can be used to create a database in MS SQL Server:

下面的示例演示如何使用CREATE查询在MS SQL Server中创建数据库：

CREATE DATABASE LibraryDB

The script above creates a database named “LibraryDB” in MS SQL Server.

上面的脚本在MS SQL Server中创建一个名为“ LibraryDB”的数据库。

建立表格 (Creating a table)

The CREATE query is also used to add tables in an existing database as shown in the following script:

CREATE查询还用于在现有数据库中添加表，如以下脚本所示：

USE LibraryDB
CREATE TABLE Books
(
Id INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR (50) NOT NULL,
Price INT
)

The above script creates a table named “Books” in the “LibraryDB” database that we created earlier.

上面的脚本在我们之前创建的“ LibraryDB”数据库中创建了一个名为“ Books”的表。

The “Books” table contains three columns: Id, Name, and Price. The Id column is the primary key column and it cannot be NULL. A column with a PRIMARY KEY constraint must contain unique values. However, since we have set the IDENTITY property for the Id column, every time a new record is added in the Books table, the value of the Id column will be incremented by 1, starting from 1. You need to specify the values for the Name column as well as it cannot have NULL. Finally, the Price column can have NULL values.

“书籍”表包含三列：Id，名称和价格。 Id列是主键列，不能为NULL。 具有PRIMARY KEY约束的列必须包含唯一值。 但是，由于我们为Id列设置了IDENTITY属性，因此，每当在Books表中添加一条新记录时，Id列的值就会从1开始递增1。您需要为名称列及其不能为NULL。 最后，Price列可以具有NULL值。

To view all the tables in the LibraryDB, execute the following QL DDL script:

要查看LibraryDB中的所有表，请执行以下QL DDL脚本：

USE LibraryDB
GO
SELECT * FROM INFORMATION_SCHEMA.TABLES 
GO

You should see the following output:

您应该看到以下输出：

Similarly, to see all the columns in the Books table, run the following script:

同样，要查看Books表中的所有列，请运行以下脚本：

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Books'

Here is the output:

这是输出：

You can see how the CREATE query can be used to define the structure of a table and the type of data that will be stored in a table. Note, we have not added any record to the Books table yet as SQL DDL commands are only concerned with the structure of the database and not with the database records. The SQL DML commands are used for inserting, modifying and deleting database records.

您可以看到如何使用CREATE查询定义表的结构以及将存储在表中的数据类型。 注意，我们尚未将任何记录添加到Books表中，因为SQL DDL命令仅与数据库的结构有关，而与数据库记录无关。 SQL DML命令用于插入，修改和删除数据库记录。

改变 (ALTER)

The ALTER command in SQL DDL is used to modify the structure of an already existing table.

SQL DDL中的ALTER命令用于修改已经存在的表的结构。

添加新列 (Adding a new column)

For example, if we want to add a new column e.g. ISBN to the existing Books table in the LibraryDB database, the ALTER command can be used as follows:

例如，如果我们要向LibraryDB数据库中现有的Books表中添加一个新列（例如ISBN），则可以按以下方式使用ALTER命令：

USE LibraryDB
ALTER TABLE Books
ADD ISBN INT NOT NULL;

The syntax of the ALTER command is straightforward. The ALTER statement is used followed by the object type and the name of the object, which in this case are TABLE and Books, respectively.

ALTER命令的语法很简单。 使用ALTER语句，后跟对象类型和对象名称，在这种情况下分别为TABLE和Books。

Next, you need to specify the operation that you need to perform, which is ADD in our case. Let’s now again SELECT the columns from the Books table and see if the ISBN column has been added to the Books table:

接下来，您需要指定所需执行的操作，在本例中为ADD。 现在，让我们再次从Books表中选择列，并查看ISBN列是否已添加到Books表中：

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Books'

Here is the result set:

这是结果集：

In the output, you can see the newly added ISBN column.

在输出中，您可以看到新添加的ISBN列。

修改现有列 (Modifying an existing column)

Let’s see another case where ALTER command can be useful. Suppose, instead of adding a new column to a table, you want to modify an existing column. For example, you want to change the data type of the ISBN column from INT to VARCHAR (50). The ALTER query can be used as follows:

让我们看一下ALTER命令可能有用的另一种情况。 假设要修改现有列，而不是向表中添加新列。 例如，您要将ISBN列的数据类型从INT更改为VARCHAR（50）。 ALTER查询可以按如下方式使用：

USE LibraryDB
ALTER TABLE Books
ALTER COLUMN ISBN VARCHAR(50);

You can see that to modify an existing column within a table, we first have to use the ALTER command with the table name and then another ALTER command with the name of the column that is to be modified.

您可以看到，要修改表中的现有列，我们首先必须使用带有表名的ALTER命令，然后再使用带有要修改的列名的另一个ALTER命令。

If you again select the column names, you will see the updated data type (VARCHAR) for the ISBN column.

如果再次选择列名，则将看到ISBN列的更新的数据类型（VARCHAR）。

下降 (DROP )

The DROP command is a type of SQL DDL command, that is used to delete an existing database or an object within a database.

DROP命令是一种SQL DDL命令，用于删除现有数据库或数据库中的对象。

删除数据库 (Deleting a database)

The following DROP command deletes the LibraryDB database that we created earlier:

以下DROP命令删除我们之前创建的LibraryDB数据库：

DROP DATABASE LibraryDB

Note: If you execute the above command, the LibraryDB database will be deleted. To execute the rest of the queries in this article, you will again need to CREATE the LibraryDB database, along with the Books table.

注意：如果执行上述命令，则LibraryDB数据库将被删除。 要执行本文中其余的查询，您将再次需要创建LibraryDB数据库以及Books表。

删除表格 (Deleting a table)

The DROP command is a type of SQL DDL command that is used to delete an existing table. For instance, the following command will delete the Books table:

DROP命令是一种SQL DDL命令，用于删除现有表。 例如，以下命令将删除Books表：

DROP TABLE Books

删除列 (Deleting a column)

To delete a column within a database, the DROP query is used in combination with the ALTER query. The ALTER query specifies the table that you want to delete whereas the DROP query specifies the column to delete within the table specified by the ALTER query. Let’s drop the ISBN column from the Books:

要删除数据库中的列，将DROP查询与ALTER查询结合使用。 ALTER查询指定要删除的表，而DROP查询指定要在ALTER查询指定的表内删除的列。 让我们从书籍中删除ISBN列：

ALTER TABLE Books
DROP COLUMN ISBN

截短 (TRUNCATE )

The TRUNCATE command in SQL DDL is used to remove all the records from a table. Let’s insert a few records in the Books table:

SQL DDL中的TRUNCATE命令用于从表中删除所有记录。 让我们在Books表中插入一些记录：

INSERT INTO Books 
VALUES ('Book-A', 100),
('Book-B', 200),
('Book-C', 150)

Let’s see if the records have been actually inserted:

让我们看看是否实际插入了记录：

SELECT * FROM Books

Here is the result set:

这是结果集：

You can see the three records that we inserted in the Books table.

您可以看到我们在Books表中插入的三个记录。

The TRUNCATE command will remove all the records from the Books table as shown below:

TRUNCATE命令将从“书籍”表中删除所有记录，如下所示：

TRUNCATE TABLE Books

If you again select all the records from the Books table, you will see that the table is empty.

如果再次从“书籍”表中选择所有记录，则将看到该表为空。

结论 (Conclusion )

In this article, you saw how to use SQL DDL commands to create a database schema and to define and modify the structure of your database. You saw how to execute SQL DDL commands in MS SQL Server with the help of different examples. Have anything to say about the article? Please feel free to comment.

在本文中，您了解了如何使用SQL DDL命令创建数据库架构以及定义和修改数据库的结构。 通过不同的示例，您了解了如何在MS SQL Server中执行SQL DDL命令。 关于这篇文章有什么要说的吗？ 请随意发表评论。

翻译自: https://www.sqlshack.com/sql-ddl-getting-started-with-sql-ddl-commands-in-sql-server/

sql ddl中key