sql azure 语法_深入了解Azure Data Studio：扩展和Azure SQL DB开发

sql azure 语法

In the previous articles listed below, we went through the Azure Data Studio tool, starting from the installation procedure, showing the purpose of each component, how to organize your connections, and develop the different types of SQL Server database objects using the interactive query editor in that data studio.

在下面列出的以前的文章中，我们从安装过程开始介绍了Azure Data Studio工具，展示了每个组件的用途，如何组织连接以及使用交互式查询编辑器开发不同类型SQL Server数据库对象在那个数据工作室。

• Starting your Journey with Azure Data Studio 使用Azure Data Studio开始您的旅程
• Developing your SQL Server database using Azure Data Studio 使用Azure Data Studio开发SQL Server数据库
• Digging deeper into Azure Data Studio: More code and less GUI 深入了解Azure Data Studio：更多代码和更少GUI

In this article, we will extend the built-in features of the Azure Data Studio by using the Extensions and show how to connect to and develop an Azure SQL Database.

在本文中，我们将通过使用扩展来扩展Azure Data Studio的内置功能，并展示如何连接和开发Azure SQL数据库。

使用扩展 (Using Extensions)

As we discussed in the previous articles, Azure Data Studio (ADS) is not an administration tool, where most of the GUI wizards and features available in the SQL Server Management Studio (SSMS) tool are not available in ADS. For example, there is no wizard in ADS to create a new database, create a new table, add a server login, or map it to a database user.

正如我们在前几篇文章中讨论的那样，Azure Data Studio（ADS）不是管理工具，其中SQL Server Management Studio（SSMS）工具中可用的大多数GUI向导和功能在ADS中不可用。 例如，ADS中没有向导来创建新数据库，创建新表，添加服务器登录名或将其映射到数据库用户。

To support additional functionalities in ADS, the Extensions that are developed by the Microsoft development team, or by the development community can be easily installed and added to ADS and used as any other built-in features within that data studio.

为了支持ADS中的其他功能，可以轻松安装Microsoft开发团队或开发社区开发的扩展 ，并将其添加到ADS中，并用作该数据工作室中的任何其他内置功能。

In this article, we will show three useful extensions that you can take advantage of. But feel free to search for and install any other available extension that you may need.

在本文中，我们将展示三个有用的扩展，您可以利用它们。 但是请随时搜索并安装您可能需要的任何其他可用扩展。

SQL Server代理 (SQL Server Agent)

SQL Server Agent is not available as a built-in component within the Azure Data Studio. But it can be easily added to the data studio in case you need to schedule a specific task or create an alert or an operator to be notified when a specific event is detected.

SQL Server代理不能作为Azure Data Studio中的内置组件使用。 但是，如果您需要安排特定任务或创建警报或在检测到特定事件时通知操作员，则可以轻松地将其添加到数据工作室。

The SQL Server Agent extension for Azure Data Studio provides basic functionalities, such as creating a new scheduled job, controlling the run of the job, and checking the execution history of the agent jobs. In addition, you can use that extension to create an alert or an operator to be notified based on the alert or job status.

适用于Azure Data StudioSQL Server代理扩展提供了基本功能，例如创建新的计划作业，控制作业的运行以及检查代理作业的执行历史记录。 另外，您可以使用该扩展名创建警报，或者根据警报或作业状态来通知操作员。

To add the SQL Server Agent extension, browse the Extensions tab, search for the SQL Server Agent extension then click Install, and the extension will be installed in a few seconds, as shown below:

要添加SQL Server Agent扩展，请浏览Extensions选项卡，搜索SQL Server Agent扩展，然后单击Install ，该扩展将在几秒钟内安装，如下所示：

After installing the SQL Server Agent Extension, it can be accessed by right-clicking on the SQL Server instance then choose the Manage option, where a new tab will be displayed under the Administration section, showing the list of all available SQL Server Agent job under that SQL instance, as shown below:

安装SQL Server代理扩展后，可以通过右键单击SQL Server实例，然后选择“ 管理”选项来访问它，在“管理”部分下将显示一个新选项卡，其中显示了所有“可用SQL Server代理”作业的列表。该SQL实例，如下所示：

The SQL Server Agent extension of the Azure Data Studio, provides you with the ability to start, stop, edit or delete any SQL Server Agent job created under that SQL Server instance, by right-clicking on the job name and choose the required action, as below:

通过使用Azure Data StudioSQL Server Agent扩展，可以右键单击作业名称并选择所需的操作，以启动，停止，编辑或删除在该SQL Server实例下创建的任何SQL Server Agent作业，如下：

From the same window, you can choose the New Job option to create a new SQL Agent job that is used to run a specific task, schedule it to be executed based on a predefined schedule or as a response for an event and create a notification to notify a SQL operator when the job completed, succeeded or failed.

在同一窗口中，可以选择“新建作业”选项来创建用于运行特定任务的新SQL Agent作业，根据预定义的计划将其计划为执行，或作为事件的响应，并创建通知以作业完成，成功或失败时通知SQL操作员。

The SQL Server Agent extension provides you with the ability to create an alert that will run an Agent job or notify an operator when a specific event occurs, create an operator to be notified when a job is completed, or an alert is received, and create a proxy account to impersonate the execution of a specific task that accesses an external resource as shown below:

SQL Server代理扩展使您能够创建警报，该警报将运行代理作业或在发生特定事件时通知操作员，创建将在作业完成或收到警报时得到通知的操作员，并创建代理帐户来模拟执行访问外部资源的特定任务，如下所示：

SQL事件探查器 (SQL Profiler)

The SQL Profiler tool is one of the SQL Server management tools that is installed when the SQL Server Management Studio is installed on your machine. It is a simple tracing tool that can be used to track the queries that are currently running in the connected SQL Server instance, showing the T-SQL text for the queries and other properties that help in identifying the applications and workload issues.

SQL Profiler工具是在计算机上安装SQL Server Management Studio时安装SQL Server管理工具之一。 它是一个简单的跟踪工具，可用于跟踪连接SQL Server实例中当前正在运行的查询，显示查询的T-SQL文本和有助于识别应用程序和工作负载问题的其他属性。

SQL Profiler can be added to the Azure Data Studio as an extension, which is still under preview. This extension helps in identifying the slowest queries that are causing problems and affecting other queries, studying the performance of these queries, and tune its performance.

可以将SQL Profiler作为扩展添加到Azure Data Studio中，该扩展仍处于预览状态。 此扩展有助于识别引起问题并影响其他查询的最慢查询，研究这些查询的性能并调整其性能。

The SQL Profiler extension can be installed by browsing the Extensions tab, search for the SQL Profiler extension then click Install, and the extension will be installed and ready to use in a few seconds, as shown below:

可以通过浏览“ 扩展”选项卡，搜索“ SQL Profiler”扩展，然后单击“安装”来安装SQL Profiler扩展，该扩展将被安装并可以在几秒钟内使用，如下所示：

The SQL Profiler extension is simpler than the SQL Profiler of the SSMS tool, where it contains a few templates that help in tracking the queries without advanced configurations and options.

SQL Profiler扩展比SSMS工具SQL Profiler更为简单，该扩展包含一些模板，这些模板可帮助跟踪查询而无需高级配置和选项。

To open the SQL Profiler extension, right-click on the SQL Server instance and choose the Launch Profiler option, then choose whether to use the standard or the T-SQL profiler template. When the SQL Profiler extension started, you can change the view of the displayed events in a way that serves a specific goal, such as concentrating on the duration, workload, tuning or the T-SQL queries, as shown below:

要打开SQL Profiler扩展，请右键单击SQL Server实例并选择Launch Profiler选项，然后选择使用标准模板还是T-SQL Profiler模板。 启动SQL事件探查器扩展时，可以按照特定目的更改显示的事件的视图，例如集中精力进行持续时间，工作量，调整或T-SQL查询，如下所示：

SQL Server导入 (SQL Server Import)

The SQL Server Management Studio Import and Export wizard, based on SSIS packages, is a useful tool that helps in exchanging the data between different types of parties in a proper and organized way.

基于SSIS包SQL Server Management Studio导入和导出向导是一个有用的工具，可帮助以正确且有组织的方式在不同类型的参与方之间交换数据。

Unfortunately, this tool is not available in the Azure Data Studio. But the Microsoft development team provided an extension, under preview, which can help in importing data from flat files, such as .txt and .csv files, to the databases hosted in the actively connected SQL Server instance.

不幸的是，该工具在Azure Data Studio中不可用。 但是Microsoft开发团队在预览下提供了一个扩展，该扩展可以帮助将数据从平面文件（例如.txt和.csv文件）导入到活动连接SQL Server实例中托管的数据库中。

The SQL Server Import extension can be installed by browsing the Extensions tab, search for the SQL Server Import extension then click Install, and the extension will be installed and ready to use in a few seconds, as shown below:

可以通过浏览“ 扩展”选项卡，搜索“ SQL Server导入”扩展，然后单击“安装”来安装SQL Server导入扩展，该扩展将被安装并可以在几秒钟内使用，如下所示：

To start the SQL Server Import wizard, right-click on the database name from the list of databases under the Connections tab, and choose the Import Wizard option, as shown below:

要启动SQL Server导入向导，请在“连接”选项卡下的数据库列表中右键单击数据库名称，然后选择“ 导入向导”选项，如下所示：

From the opened Import Flat File wizard, specify the location of the .txt or.csv file that contains the data to be imported, the connection information of the destination database table, preview the data before importing it, check the columns data types and properties then click Done to start the import process, and the data will be successfully imported if there are no compatibility issues, as shown below:

从打开的“导入平面文件”向导中，指定包含要导入的数据的.txt或.csv文件的位置，目标数据库表的连接信息，在导入之前预览数据，检查列的数据类型和属性然后单击“完成”开始导入过程，如果没有兼容性问题，数据将成功导入，如下所示：

Don’t forget that you can extend the functionality of the Azure Data Studio by adding other extensions based on your requirements. Just search for what you want and install it to help you with your tasks, as shown below:

不要忘记，您可以根据需要添加其他扩展来扩展Azure Data Studio的功能。 只需搜索所需内容并安装它即可帮助您完成任务，如下所示：

Azure SQL数据库开发 (Azure SQL DB Development)

Azure Data Studio, as the name indicates, can be used to connect to the Azure data-related services and perform different development tasks on these services, such as the Azure SQL Database, Azure SQL Managed Instance and Azure Database for PostgreSQL.

顾名思义，Azure Data Studio可用于连接到与Azure数据相关的服务，并在这些服务上执行不同的开发任务，例如Azure SQL数据库，Azure SQL托管实例和PostgreSQLAzure数据库。

入门 (Getting Started)

In order to connect to your Azure database services, browse the Azure section under the Connections tab and click on the Sign in to Azure. From the opened Linked accounts window, click Add an account option, which allows you to log in to your Azure account, as shown below:

为了连接到您的Azure数据库服务，请浏览“连接”选项卡下的“ Azure”部分，然后单击“ 登录到Azure” 。 在打开的“链接帐户”窗口中，单击“添加帐户”选项，该选项使您可以登录到Azure帐户，如下所示：

Once the Azure account is added, you can choose the Azure subscription that contains your database services, and the Azure Data Studio will display all available database services under that subscription, as shown below:

添加Azure帐户后，可以选择包含数据库服务的Azure订阅，Azure Data Studio将显示该订阅下的所有可用数据库服务，如下所示：

To connect to Azure SQL Database under your subscription, click on the plug icon and provide the required SQL authentication credentials, then click on the Connect option, as shown below:

要连接到订阅下的Azure SQL数据库，请单击插件图标并提供所需SQL身份验证凭据，然后单击“ 连接”选项，如下所示：

When the connection established successfully, the Home page will be displayed, with a brief description of the Azure SQL Database version and the list of all available databases under that Azure SQL Server, as shown below:

成功建立连接后，将显示主页，其中简要介绍了Azure SQL数据库版本以及该Azure SQL Server下所有可用数据库的列表，如下所示：

开发Azure SQL数据库 (Develop Azure SQL Database)

Most of the database development tasks that we performed on the on-prem SQL Server databases can be performed on the Azure SQL Database.

我们在本地SQL Server数据库上执行的大多数数据库开发任务都可以在Azure SQL数据库上执行。

To open the query editor, right-click on the database name and choose New Query option, as shown in the screenshot below:

要打开查询编辑器，请右键单击数据库名称，然后选择“ 新建查询”选项，如下面的屏幕快照所示：

In the opened query editor, create a new database table, using the Create Table T-SQL statement, providing the list of the required columns, the data type of each column, and the indexes and constraints that are needed to connect the tables together. When you complete drawing your table, click on the Run option, and the table will be created directly, as shown below:

在打开的查询编辑器中，使用Create Table T-SQL语句创建一个新的数据库表，提供所需列的列表，每列的数据类型以及将这些表连接在一起所需的索引和约束。 完成表绘制后，单击“ 运行”选项，将直接创建表，如下所示：

Once the table is ready, we will fill that table with data, using the INSERT INTO T-SQL statement below:

一旦表准备好，我们将使用下面的INSERT INTO T-SQL语句用数据填充该表：

To review the data that is inserted to the table, write a SELECT statement from that table, or simply right-click on the table name and choose SELECT TOP 1000 option, and the data will be displayed in a grid view, as shown below:

要查看插入到表中的数据，请从该表中编写一条SELECT语句，或者简单地右键单击表名并选择SELECT TOP 1000选项，数据将显示在网格视图中，如下所示：

The query editor allows you to tune your queries by studying how the query execution will be performed. This can be achieved by using the Explain option, which displays the estimated execution plan of that query. If you move the mouse pointer over any node of the execution plan, a tooltip will be displayed, showing all estimated execution statistics for that specific operation, as shown below:

查询编辑器使您可以通过研究如何执行查询来调整查询。 这可以通过使用Explain选项来实现，该选项显示该查询的估计执行计划。 如果将鼠标指针移到执行计划的任何节点上，将显示一个工具提示，显示该特定操作的所有估计执行统计信息，如下所示：

To modify the column value for a specific record, or delete a set of rows that are no longer needed from your Azure SQL Database, you can easily write an UPDATE or DELETE statement in the Azure Data Studio query editor, as shown below:

若要修改特定记录的列值，或从Azure SQL数据库中删除不再需要的行集，可以在Azure Data Studio查询编辑器中轻松编写UPDATE或DELETE语句，如下所示：

Or simply right-click on the table name and choose the Edit Data option, that opened the table’s data in edit view, allowing you to change any columns value directly. It also provides you with the ability to display the T-SQL statement that is used to view the data, using Show SQL Pane option, edit the query with the condition that filters the required data then run the query to display only the records you are interested in, and the records will be ready for the modification, as shown below:

或者，只需右键单击表名称，然后选择“ 编辑数据”选项，该选项将在编辑视图中打开表的数据，使您可以直接更改任何列的值。 它还使您能够显示用于查看数据的T-SQL语句，使用“ 显示SQL窗格”选项，以过滤所需数据的条件编辑查询，然后运行查询以仅显示您要查看的记录感兴趣，记录将准备好进行修改，如下所示：

And finally, if you plan to perform any administrative task on that Azure SQL Database or modify any configurable option, Azure Data Studio provides you with an option, Open in Azure Portal, to open that Azure service in the Azure Portal, and perform the required action directly in the Azure Portal, as shown below:

最后，如果您打算在该Azure SQL数据库上执行任何管理任务或修改任何可配置的选项，Azure Data Studio将为您提供一个选项“ 在Azure Portal中打开”，以在Azure Portal中打开该Azure服务，并执行所需的操作直接在Azure门户中执行操作，如下所示：

结论 (Conclusion)

In this article, we showed how to extend the Azure Data Studio functionality using Microsoft or third-party extensions. In addition, we discussed how to connect to the Azure SQL Database service from ADS and develop database objects in it as we do for on-premises databases.

在本文中，我们展示了如何使用Microsoft或第三方扩展来扩展Azure Data Studio功能。 此外，我们讨论了如何从ADS连接到Azure SQL数据库服务并像在本地数据库中一样在其中开发数据库对象。

This is the last (but not least) article about Azure Data Studio for now. But I will continue with that series once I find another interesting and useful functionality in ADS for database developers or administrators. I hope you enjoyed that subject and found it useful. Stay tuned for the next subject!

这是有关Azure Data Studio的最后（但并非最不重要）文章。 但是，一旦我在ADS中为数据库开发人员或管理员找到了另一个有趣且有用的功能，我将继续该系列文章。 我希望您喜欢这个主题并发现它有用。 请继续关注下一个主题！

翻译自: https://www.sqlshack.com/digging-deeper-into-azure-data-studio-extensions-and-azure-sql-db-development/

sql azure 语法