sql azure 语法_使用Azure Data Studio开发SQL Server数据库-CSDN博客

sql azure 语法

In the previous article, Starting your journey with Azure Data Studio, we put the first stone that helps you to be familiar with the Azure Data Studio and start using it to interact with your database objects.

在上一篇文章“ 开始使用Azure Data Studio的旅程”中，我们为帮助您熟悉Azure Data Studio并开始使用它与数据库对象进行交互提供了第一块石头。

In this article, we will dig deeper and show how to use Azure Data Studio to create the main database objects and interact directly with the data stored in your database using different DML operations.

在本文中，我们将进行更深入的研究，并展示如何使用Azure Data Studio创建主数据库对象，以及如何使用不同的DML操作直接与存储在数据库中的数据进行交互。

Azure Data Studio can be used easily to develop your database components and view/modify the data in the database tables. It allows you to run T-SQL queries, review, edit, and export the result to different useful formats, such as CSV, Excel, XML, and JSON, that can be processed by your applications or to be imported by another database engine.

Azure Data Studio可以轻松用于开发数据库组件以及查看/修改数据库表中的数据。它使您可以运行T-SQL查询，查看，编辑并将结果导出为其他有用的格式，例如CSV，Excel，XML和JSON，这些格式可以由您的应用程序处理或由另一个数据库引擎导入。

To write a new query, right-click on your database, from the databases list under the Connections tab, and choose New Query option, or simply select the New Query option under the Files menu, as shown below:

要编写新查询，请在“连接”选项卡下的数据库列表中右键单击数据库，然后选择“ 新建查询”选项，或在“文件”菜单下选择“新建查询”选项，如下所示：

From the opened New Query window, you can easily write your T-SQL script with the help of the built-in IntelliSense feature that provides you with all options to complete your code automatically, by providing few characters only. For example, writing “SEL” only will provide you with all commands that contain these characters, with the first valid one is the “SELECT” statement.

在打开的“新建查询”窗口中，您可以借助内置的IntelliSense功能轻松编写T-SQL脚本，该功能仅提供几个字符即可为您提供自动完成代码的所有选项。例如，仅编写“ SEL”将为您提供包含这些字符的所有命令，第一个有效的命令是“ SELECT”语句。

Besides, the SQL code snippets feature helps in providing the requested SQL syntax that you can use to create the different SQL Server objects, such as databases, tables, views, or stored procedures or modifying the existing objects. What is requested from your side is filling the variable information in the provided snippet and enjoy the simple developing process.

此外，SQL代码片段功能有助于提供所请求SQL语法，您可以使用该语法创建不同SQL Server对象，例如数据库，表，视图或存储过程，或修改现有对象。您的要求是将变量信息填充到提供的代码段中，并享受简单的开发过程。

In the example below, we only write the first three letters from the “SELECT” statement, and the IntelliSense feature will complete the SELECT statement automatically. Also, the code snippets feature provides us with the “select rows from a table” template, and you can drag it directly to your code by clicking on it, then fill the name of the table and provide the search condition directly without any complexity in the coding process, taking into consideration that the IntelliSense feature also helps in providing the list of database objects that we can use in our query, as shown below:

在下面的示例中，我们只写“ SELECT”语句的前三个字母，并且IntelliSense功能将自动完成SELECT语句。另外，代码片段功能还为我们提供了“从表中选择行”模板，您可以通过单击模板将其直接拖到代码中，然后填写表的名称并直接提供搜索条件，而无需进行任何复杂的设置。在编码过程中，考虑到IntelliSense功能还有助于提供我们可以在查询中使用的数据库对象的列表，如下所示：

From the same query editor, you can easily change the current connection, if you manage to execute the query in another SQL Server instance. This can be done by clicking on the Change Connections option and select the new connection from the recently connected servers or the saved connections, or provide the new connection information, includes the server name, credentials and authentication mode, in order to connect to the new instance, as shown below:

如果您设法在另一个SQL Server实例中执行查询，则可以从同一查询编辑器轻松更改当前连接。这可以通过单击“ 更改连接”选项并从最近连接的服务器中选择新连接或保存的连接来完成，或者提供新的连接信息（包括服务器名称，凭据和身份验证模式）以连接到新服务器。实例，如下所示：

With the ability to move between the current connection databases, by selecting the new database name from the databases drop-down list, or easily include the USE database name clause at the top of your query, as shown below:

通过在数据库下拉列表中选择新的数据库名称，或在查询顶部轻松包含USE数据库名称子句，可以在当前连接数据库之间移动，如下所示：

Azure Data Studio provides you with the ability to organize your query files within folders and save the related script files in a specific folder. To achieve that, browse the Explorer tab and choose the Open Folder option to assign a specific folder for the current project and save the opened script files to that folder, with the ability to save the files to a Git repository as we discussed in the previous article, as shown below:

Azure Data Studio使您能够组织文件夹中的查询文件，并将相关的脚本文件保存在特定的文件夹中。为此，请浏览“资源管理器”选项卡，然后选择“ 打开文件夹”选项，为当前项目分配一个特定的文件夹，然后将打开的脚本文件保存到该文件夹，并具有将文件保存到Git存储库的能力，如我们先前讨论的那样。文章，如下所示：

The query editor of the Azure Data Studio is very rich querying console, where it provides you with the ability to analyze the query that you write before executing it, by generating an estimated execution plan for that query and provide full analysis about that query.

Azure Data Studio的查询编辑器是一个非常丰富的查询控制台，通过它可以为该查询生成一个估计的执行计划并提供对该查询的完整分析，从而使您能够在执行该查询之前分析所编写的查询。

Once you finish writing your query, click on the Explain option, and check the Results tab that contains the execution plan for that query in XML format. To review the XML plan for the query, click on the plan XML link and the XML plan will be opened in a separate window that allows you to review it easily, as shown below:

完成编写查询后，单击“ 解释”选项，然后检查“ 结果”选项卡，其中包含该查询的XML格式的执行计划。要查看查询的XML计划，请单击计划XML链接，然后将在单独的窗口中打开XML计划，使您可以轻松地对其进行查看，如下所示：

In addition to the XML plan, the Explain option provides you with the time that is required to generate that plan and if the query has any validation error, under the Messages tab, as below:

除XML计划外，Explain选项还为您提供了生成该计划所需的时间，以及查询是否存在任何验证错误（在“ 消息”选项卡下），如下所示：

The most useful result, for me, from the Explain option is the graphical execution plan of the query, that contains the expected execution map for that query with the components that are affecting the performance of the query, allowing you to tune the performance of that query before using it in the production environment, as below:

对我而言，“解释”选项中最有用的结果是查询的图形执行计划，其中包含该查询的预期执行映射以及影响查询性能的组件，从而使您可以调整该查询的性能在生产环境中使用它之前进行查询，如下所示：

To dig deep in the graphical execution plan statistics, the Top Operations tab provide statistical information about each component in the execution plan, that helps you to identify the bottlenecks in the query and enhance it, as follows:

为了深入了解图形执行计划的统计信息，“ 顶部操作”选项卡提供了有关执行计划中每个组件的统计信息，可帮助您识别查询中的瓶颈并加以增强，如下所示：

After checking the query and enhance it to retrieve the requested data in the best way, click on the Run option to execute the query and retrieve the needed records from the database table, and the records will be returned, by default, in a grid format, similar to the grid returned by the SSMS, as shown below:

检查查询并增强查询以最佳方式检索所需数据后，单击“ 运行”选项以执行查询并从数据库表中检索所需的记录，默认情况下，这些记录将以网格格式返回。，类似于SSMS返回的网格，如下所示：

Without enabling any type of statistics, the Azure Data Studio provides you with the number of rows returned by your query and the time it takes the SQL Server Database Engine to return the data, as shown below:

在不启用任何类型的统计信息的情况下，Azure Data Studio为您提供查询返回的行数以及SQL Server数据库引擎返回数据所花费的时间，如下所示：

Another special feature in the Azure Data Studio is the ability to export the data that is returned by any query to different formats that can be processed by your applications, used as input for another database engine, or stored in on-prem or Azure storage.

Azure Data Studio的另一个特殊功能是能够将任何查询返回的数据导出为可以由您的应用程序处理，用作另一个数据库引擎的输入或存储在本地或Azure存储中的不同格式。

These options include saving the query result to an Excel file, a CSV file, an XML file, a JSON file, or display it using a chart if possible. Below is the description for each icon that can be used as an export destination:

这些选项包括将查询结果保存到Excel文件，CSV文件，XML文件，JSON文件，或在可能的情况下使用图表显示。以下是可用作导出目标位置的每个图标的说明：

For example, if you manage to export the records that are returned from the previous query to a JSON file in order to be processed by our local system. To achieve that, click on the Save as JSON icon, specify where to save that file, with the default folder is the current project folder we created previously, then the file will be saved and opened directly once created to allow you to review it, as shown below:

例如，如果您设法将先前查询返回的记录导出到JSON文件中，以便由我们的本地系统处理。为此，请单击“ 另存为JSON”图标，指定该文件的保存位置，默认文件夹是我们先前创建的当前项目文件夹，然后该文件将被保存并在创建后直接打开以供您查看，如下所示：

Azure Data Studio contains a rich query editor that allows you to write any DDL or DML commands with the help of the built-in IntelliSense and code snippet features.

Azure Data Studio包含一个丰富的查询编辑器，使您可以借助内置的IntelliSense和代码段功能编写任何DDL或DML命令。

Assume that we need to create a new database using that studio. As the Azure Data Studio is a development console, there is no GUI option to create a new database or database objects using a wizard. Instead, you need to open a new query window and create the database using CREATE DATABASE T-SQL statement directly, as shown below:

假设我们需要使用该工作室创建一个新数据库。由于Azure Data Studio是开发控制台，因此没有GUI选项可以使用向导创建新数据库或数据库对象。相反，您需要打开一个新的查询窗口并直接使用CREATE DATABASE T-SQL语句创建数据库，如下所示：

Once the query is executed, refresh the databases list within the Connections tab, and you will see that the database is created successfully, as below:

执行查询后，刷新“连接”选项卡中的数据库列表，您将看到数据库创建成功，如下所示：

To create a new table inside the created database, you need to use the CREATE TABLE T-SQL statement, providing the column name, data types, and any requested constraint or index, then execute the query and refresh the database tables, where the table will be created successfully, as below:

要在创建的数据库中创建新表，您需要使用CREATE TABLE T-SQL语句，提供列名，数据类型以及任何请求的约束或索引，然后执行查询并刷新数据库表，其中的表将成功创建，如下所示：

Now we are ready to insert new records to the created table. This can be achieved using the INSERT INTO T-SQL statement by providing values for all mandatory columns in the correct order. After executing the INSERT INTO query, we can confirm that the data is inserted correctly by writing a SELECT statement from that table or simply right-clicking on the table and choose SELECT TOP 1000, and the data will be displayed in a grid format, allowing you to review and analyze it easily, with the ability to export it to different formats as mentioned previously:

现在我们准备将新记录插入到创建的表中。这可以通过使用INSERT INTO T-SQL语句以正确顺序为所有必需列提供值来实现。执行INSERT INTO查询后，我们可以通过从该表中编写一条SELECT语句或简单地右键单击该表并选择SELECT TOP 1000来确认是否正确插入了数据，并且该数据将以网格格式显示，从而允许您可以轻松地对其进行查看和分析，并能够将其导出为前面提到的不同格式：

Azure Data Studio supports writing all types of T-SQL commands, including the data modification statements. In the example below, we write two statements; the first one is used to modify the last name for a specific user, and the second one is used to delete few records that are no longer needed. After executing the queries, the query editor will provide you with the number of affected rows from each statement and the time consumed to execute the whole batch, with the ability to calculate the execution time for each statement using the provided starting time:

Azure Data Studio支持编写所有类型的T-SQL命令，包括数据修改语句。在下面的示例中，我们编写了两个语句：第一个用于修改特定用户的姓氏，第二个用于删除一些不再需要的记录。执行查询后，查询编辑器将为您提供每个语句中受影响的行数以及执行整个批处理所花费的时间，并可以使用提供的开始时间来计算每个语句的执行时间：

结论 (Conclusion)

In this article, we went through different database development functionalities that are supported by the Azure Data Studio. In the next article, we will try to show more complex tasks that can be performed easily using the Azure Data Studio. Stay tuned!

在本文中，我们介绍了Azure Data Studio支持的不同数据库开发功能。在下一篇文章中，我们将尝试显示可以使用Azure Data Studio轻松执行的更复杂的任务。敬请关注！