mssql-cli简介，一种用于SQL Server的新的交互式命令行工具

Microsoft recently released a new and interactive command-line query tool for SQL Server with modern features such as auto-completion and syntax highlighting. It’s called mssql-cli and this fully open source tool works on cross-platform under the BSD-3 license.

Microsoft最近发布了一种针对SQL Server的新的交互式命令行查询工具，该工具具有自动完成和语法突出显示等现代功能。 它称为mssql-cli，并且该完全开放源代码工具在BSD-3许可下可在跨平台上运行。

Mssql-cli was created and released with the goal to provide the following key enhancements over sqlcmd in the Terminal environment:

创建并发布Mssql-cli的目的是在终端环境中对sqlcmd提供以下主要增强功能：

• Auto-completion
  自动补全
• Syntax highlighting
  语法高亮
• Query history
  查询历史
• Configuration file support
  配置文件支持
• Multi-line queries
  多行查询

So, let’s get started and check out this tool.

因此，让我们开始并检查该工具。

安装 (Installation)

In order to install and run mssql-cli, you must have Python installed on your machine and it works with both Python 2.7 and 3.6 versions. For supported operating system specific installations including Python, see this full detailed installation guides. Since I’m a Windows user, let’s go through the steps of installation and set up mssql-cli on the Windows environment to see it in action.

为了安装和运行mssql-cli，您必须在计算机上安装Python，并且它可与Python 2.7和3.6版本一起使用。 有关受支持的特定于操作系统的安装（包括Python），请参阅此完整的详细安装指南。 由于我是Windows用户，因此，请完成安装步骤并在Windows环境中设置mssql-cli，以查看实际运行情况。

Python is not installed by default on Windows, so we need to obtain the latest installation package from here. Once the installation file is downloaded, double-click it to begin the installation process.

Windows默认情况下未安装Python，因此我们需要从此处获取最新的安装包。 下载安装文件后，双击它以开始安装过程。

In the first step, check the ‘Add Python 3.6 to PATH’ option because Python must be in the PATH environment variable and select the ‘Install Now’ option to install Python with default settings:

第一步，请检查“将Python 3.6添加到PATH”选项，因为Python必须在PATH环境变量中，然后选择“立即安装”选项以使用默认设置安装Python：

Depending on your User Account Control (UAC) settings you might be prompted to allow application to make changes to the computer. Just click the Yes button to continue with the installation:

根据您的用户帐户控制 （UAC）设置，可能会提示您允许应用程序对计算机进行更改。 只需单击“ 是”按钮即可继续安装：

Note: When a standard user attempts to perform this task, the credential prompt will be presented requiring administrative privileges

注意：当标准用户尝试执行此任务时，将显示凭据提示，要求具有管理权限

Once the installation is completed, open a Command Prompt, and install mssql-cli using the below command:

安装完成后，打开命令提示符 ，然后使用以下命令安装mssql-cli：

C:\> pip install mssql-cli

C：\> pip安装mssql-cli

Mssql-cli is installed via pip. It will start fetching the required packages, so an active internet connection is a requirement in this step:

Mssql-cli通过pip安装。 它将开始获取所需的软件包，因此在此步骤中需要有效的Internet连接：

Note: If Python was installed into the “Program Files” directory, you may need to open the command prompt as an administrator for the above command to succeed

注意：如果将Python安装到“ Program Files”目录中，则可能需要以管理员身份打开命令提示符，以上命令才能成功

Once we have mssql-cli installed, connect to your database with the following command:

一旦安装了mssql-cli，请使用以下命令连接到您的数据库：

$ mssql-cli -S <server name> -U <user name> -d <database name>

$ mssql-cli -S <服务器名称> -U <用户名称> -d <数据库名称>

Next, you’ll be prompted to enter the username password. There is no indication that a password is being entered and this is entirely intentional for entering passwords at the command line. Hit the return key when you’re ready to proceed:

接下来，系统将提示您输入用户名密码。 没有迹象表明正在输入密码，这完全是为了在命令行输入密码。 准备继续时，请按回车键：

Note: Connecting using Windows authentication is also possible using the “-E” option as it is for sqlcmd

注意：也可以使用“ -E”选项使用Windows身份验证进行连接，因为它适用于sqlcmd

If you encounter an error message that the connection failed like the one shown below in the screenshot, make sure the user account that you’re using is a member of the sysadmin fixed server role:

如果遇到错误消息，表明连接失败，如屏幕截图中所示，请确保您使用的用户帐户是sysadmin固定服务器角色的成员：

This can be easily done from Object Explorer in SQL Server Management Studio (SSMS). On the toolbar of Object Explorer, click the Connect icon and then in the Connect to Server dialog box connect to your instance of SQL Server. Expand the Security folder, do the same for Logins folder, right-click <user name>, and then click the Properties command:

这可以通过SQL Server Management Studio （SSMS）中的对象资源管理器轻松完成。 在对象资源管理器的工具栏上，单击“ 连接”图标，然后在“ 连接到服务器”对话框中，连接到您SQL Server实例。 展开“ 安全性”文件夹，对“ 登录名”文件夹执行相同的操作，右键单击“ <用户名>”，然后单击“ 属性”命令：

In the newly open Login Properties dialog select the Server Roles page in the top left, ensure that the sysadmin role is checked under the Server roles list, and click the OK button at the bottom right to save changes:

在新打开的“登录属性”对话框中，选择左上角的“ 服务器角色”页面，确保在“服务器角色”列表下选中sysadmin角色，然后单击右下角的“ 确定”按钮以保存更改：

Go back to command prompt, hit the up-arrow key to bring the last executed command for connecting to your database. Once again, you’ll be prompted for the password, and this time the login should be successful:

返回命令提示符，按向上箭头键以使最后执行的命令连接到数据库。 再次提示您输入密码，这次登录应该成功：

特征 (Features)

Let’s check out the features that we mentioned at the very beginning of this article.

让我们检查一下本文开头提到的功能。

As soon as the user starts to type an SQL command, the auto-completion will appear that is context aware and it will narrow down suggestions as you type:

用户开始键入SQL命令后，将自动显示具有上下文意识的自动完成功能，并在您键入内容时缩小建议的范围：

Writing multi-line queries is easy to read with the included appropriate syntax highlighting and “smart” auto-completion:

通过附带的适当语法突出显示和“智能”自动完成功能，易于编写多行查询：

Query history is self-explanatory; you can reuse recently executed queries by browsing thru them using the up and down arrow keys. Furthermore, the hints are auto-suggested from history as you type.

查询历史是不言自明的； 您可以通过使用向上和向下箭头键浏览最近执行的查询来重用它们。 此外，在您键入时，提示会自动从历史记录中建议。

If you’re a Windows user like me, you can find the configuration file under the following path: C:\Users\<user name>\AppData\Local\dbcli\mssqlcli

如果您是像我这样的Windows用户，则可以在以下路径下找到配置文件： C：\ Users \ <用户名> \ AppData \ Local \ dbcli \ mssqlcli

If the default setting doesn’t work for you, you can edit the configuration file to your liking. Also, the config file is well written with examples for each option, so changing setting should be fairly easy. My advice, if you’re playing with the setting, make a backup of this file to e.g. Desktop, so you can restore it at any time:

如果默认设置不适合您，则可以根据自己的喜好编辑配置文件。 另外，配置文件中每个选项的示例都编写得很好，因此更改设置应该相当容易。 我的建议是，如果您正在使用此设置，请将该文件备份到例如Desktop，以便可以随时将其还原：

Note: To access AppData folder you’ll have to enable “Show hidden files, folders, and drives” option in Windows

注意：要访问AppData文件夹，您必须在Windows中启用“显示隐藏的文件，文件夹和驱动器”选项

Multi-line edit mode can be controlled by pressing the F3 key. You can see at bottom of the screen if this option is on or off. To execute your multi-line query, add a semi-colon at the end of the last line of your query, and then press the Enter key:

可以通过按F3键来控制多行编辑模式。 您可以在屏幕底部看到此选项是打开还是关闭。 要执行多行查询，请在查询的最后一行的末尾添加分号，然后按Enter键：

In my Windows 7 VM instance, I was experiencing the “Not enough memory” error after executing most of the queries:

在我的Windows 7 VM实例中，执行大多数查询后遇到“内存不足”错误：

This bug is already logged as issue #98 under the mssql-cli GitHub project. If you encounter any bugs with the tool, you can report them in the Issues section of GitHub repo. You can use the same thread to provide feedback as well. Microsoft is open to any questions, feedback, or any feature suggestions for future releases.

该错误已作为mssql-cli GitHub项目下的问题＃98记录。 如果您在使用该工具时遇到任何错误，可以在GitHub存储库的“ 问题”部分中报告它们。 您也可以使用同一线程来提供反馈。 Microsoft对将来的发行版有任何疑问，反馈或任何功能建议。

To quickly get the hang of this tool, check out this user guide for details on options and example usage.

要快速掌握此工具，请查看本用户指南，以获取有关选项和示例用法的详细信息。

结论 (Conclusion)

After SQL Server 2017 was released on Linux and macOS (Docker), the dbcli community raised the need to modernize sqlcmd. Not that we didn’t have a popular set of open source cross-platform CLI tools for databases, but this relatively new tool which supports “smart” auto-completion, syntax highlighting and easy installation looks very promising. The community grows each day and these guys are really passionate about creating better database CLI tools. The roadmap shows some very useful features and areas of focus in the future mssql-cli work. Overall, this is a great tool that supports all versions of SQL Server and has support for cross-platform which can be useful for users who can’t or don’t wish to deploy SSMS on client computers, etc. or they’re just into command line database clients.

在Linux和macOS（Docker）上发布SQL Server 2017之后，dbcli社区提出了现代化sqlcmd的需求。 并不是说我们没有一套流行的开放源代码的跨平台CLI工具用于数据库，但是这个相对较新的工具支持“智能”自动完成，语法突出显示和易于安装，看起来非常有前途。 社区每天都在增长，这些家伙真的非常热衷于创建更好的数据库CLI工具。 该路线图显示了将来的mssql-cli工作中一些非常有用的功能和重点领域。 总体而言，这是一个出色的工具，支持所有版本SQL Server，并且支持跨平台，这对于不愿或不希望在客户端计算机等上部署SSMS的用户很有用。进入命令行数据库客户端。

翻译自: https://www.sqlshack.com/introduction-mssql-cli-new-interactive-command-line-tool-sql-server/