当前位置： 文江博客话题详情

我可以使用语音识别来编写 SQL 吗？

发布于 2024-07-09 15:16:17 字数 44 浏览 13 评论 0原文

我打字时手腕疼痛，我想开始使用语音识别编写 SQL 语句、存储过程和视图。

原文

I have wrist pain when I type and I would like to start writing SQL statements, stored procedure, and views using speech recognition.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

马蹄踏│碎落叶 2024-07-16 15:16:17

是的。 鉴于 SQL 的词汇量和类似句子的结构有限，它非常适合语音识别（也就是说，它就像编程语言一样适合）。 除了格式化 SQL 使其看起来更漂亮之外，我还可以比打字更快地听写它。 然而，听写代码并不适合所有人。 一开始可能会非常令人沮丧。 尝试这个并坚持下去的人可能是那些别无选择的人。

我使用 Dragon NaturallySpeaking 10 Professional。 专业版具有创建此类自定义词汇表所需的工具。 版本 9 也应该可以正常工作。 它很昂贵，所以如果可能的话，尽量让你工作的公司支付费用。 还要买一个像样的耳机麦克风。 NaturallySpeaking 附带的功能还不够好（但您可能想先尝试一下，看看它是否适合您）。 KnowBrainer 是提供麦克风推荐的好地方。

2009-01-05 更新：我在下面添加了专门针对 SQL Server Management Studio 中的听写的提示。

2012-01-04 更新：我已经跟踪 Microsoft 的 WSR 有一段时间了，希望能够添加工具来轻松地从头开始创建完全自定义的词汇表，就像我在本教程中所做的那样自然地讲。 不幸的是，这似乎只能通过 API (SAPI) 来完成。 我没有时间编写该代码，因此我将继续使用 NaturallySpeaking 编写代码，直到出现更好的代码。

准备工作

清理数据库名称和代码

口述“SELECT PT_17, PT_28, PT_29 FROM HIK.dbo.PATINFO”会很麻烦，但我想这是可能的。 您必须设置大量发音，因为 NaturallySpeaking 不知道“PT_17”的发音。 这对于听写来说更可取：

SELECT Patient.FirstName, Patient.MiddleName, Patient.LastName FROM Claim.dbo.Patient AS Patient WHERE Patient.LastName LIKE '%smith%'

我切换到 TSQL 词汇来听写上述语句。 LIKE 语句之前的所有内容都按其显示的方式说出。 '%smith%' 被口述为“开单引号百分号 sierra mike india tango hotel 百分号闭单引号 [PAUSE] 复合词”。 使用一致的表别名并始终将它们放在前面的字段有助于提高准确性，因为 NaturallySpeaking 会统计一个单词出现在另一个单词附近的频率。

创建 SQL 关键字的单词列表

每行放一个单词。 您可以选择在单词后面添加反斜杠 (\) 和发音。 NaturallySpeaking 使用一个小型单词备份字典来确定您添加到词汇表中的单词的发音，因此它可以毫无问题地弄清楚 SELECT、FROM 和 的方式WHERE 发音为。 它有时可以找出复合词，并对诸如 XACT_ABORT 之类的内容做出最佳猜测。 我会为此类情况提供发音。 您使用的数据库将确定列表包含哪些单词 - 检查您的文档以获取关键字列表。 您的列表看起来像这样，但要长得多。

SELECT
WHERE
FROM
XACT_ABORT\exact-abort
MAXDOP
NOLOCK\no-lock
LEN
RETURNS
CURSOR
MONEY

还要添加这些单词

\New-Line
\New-Paragraph
\All-Caps
\All-Caps-On
\All-Caps-Off
\Cap
\Caps-On
\Caps-Off
\No-Caps
\No-Caps-On
\No-Caps-Off
\No-Space
\No-Space-On
\No-Space-Off
\space-bar
\tab-key
a\alpha
b\bravo
c\charlie
d\delta
e\echo
f\foxtrot
g\golf
h\hotel
i\india
j\juliet
k\kilo
l\lima
m\mike
n\november
o\oscar
p\papa
q\quebec
r\romeo
s\sierra
t\tango
u\uniform
v\victor
w\whiskey
x\xray
y\yankee
z\zulu
PM
AM
one
two
three
four
five
six
seven
eight
nine
ten
eleven
twelve
thirteen
fourteen
fifteen
sixteen
seventeen
eighteen
nineteen
twenty
thirty
fourty
fifty
sixty
seventy
eighty
ninety
hundred
thousand
million
billion
trillion

，保留此列表，因为您可能会多次修改它并重新创建词汇表以使其达到您喜欢的方式。

创建数据库对象名称的单词列表

这就是我在 SQL Server 中执行此操作的方法：

SELECT DISTINCT * FROM 
(
SELECT DISTINCT [name] FROM Database1.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ') 
UNION 
SELECT DISTINCT column_name AS [name] FROM Database1.information_schema.[columns]
UNION
SELECT DISTINCT [name] FROM Database2.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ') 
UNION 
SELECT DISTINCT column_name AS [name] FROM Database2.information_schema.[columns]
...
) AS UnionTable

将结果复制并粘贴到文本文件中。

为数据库对象名称创建发音

使用与上面列出的相同的发音格式。 创建这些的一个简单方法是使用正则表达式搜索和替换功能。 在 SQL Server Management Studio 或 Visual Studio 中，以下（非标准）正则表达式将为两个单词混合大小写名称创建发音。

Find: ^{[A-Z][a-z]+}{[A-Z][a-z]+}$
Replace: \0\\\1-\2

检查发音并清除任何看起来不正确的内容。 对于缩写词，ASP 变为“ASP”。 也保留这份清单。 如果您决定为其他编程语言创建词汇表，并且您是数据库开发人员，则可能会包含这些单词。

创建一个包含所有 SQL 代码（视图、过程等）的文本文档

SQL Server：

SELECT * FROM Database1.dbo.[View] UNION SELECT * FROM Database1.dbo.Routine UNION
SELECT * FROM Database2.dbo.[View] UNION SELECT * FROM Database2.dbo.Routine 
...
ORDER BY [Name]

删除注释和文字字符串。 正则表达式搜索和替换对此很有效。

建立您的词汇量

安装 NaturallySpeaking 并创建一个新用户（如果您还没有）。

创建新词汇

单击“NaturallySpeaking | 管理词汇...”。 单击新建。 将词汇表命名为适当的名称，例如“SQL”。 它基于“基础通用 - 空听写”。 当它询问您是否要扫描电子邮件或文档时，请单击“取消”。

导入单词

单击“单词 | 导入”。 添加您创建的两个单词列表并导入它们。

适应书写风格

单击“工具|准确性中心”。 单击“将文档中的单词添加到词汇表”。 使用默认设置，然后选择您创建的包含代码的文档。

尝试听写一些 SQL

您可能想要听写的第一件事是 select 语句。 请记住，SELECT 是您在 NaturallySpeaking 中用来开始选择文本的命令的命令。 因此，在听写之前您需要先说“Cap”，这样 NaturallySpeaking 就不会感到困惑。 就是这样。 好吧，至少足以让你开始。 根据需要修改单词列表、发音和单词属性。 您还可以采取其他措施来提高听写的准确性和速度。 当我想到它们时，我将编辑这篇文章并将它们添加到此处。

有关在 SQL Server Management Studio 中听写的提示

如果您在 SQL Server Management Studio 中听写，您可能会注意到性能非常慢。 请尝试以下方法来缓解此问题：

  • 关闭所有工具栏（创建宏
    访问常用的
    功能）
  • 保留尽可能少的窗格和
    尽可能打开文档
  • 一次仅打开一个数据库
  • 完成后隐藏搜索结果
    和他们一起 (Ctrl+R)
  • 如果还有其他一切
    失败，关闭并重新打开管理
    工作室
  • 在编辑窗口中显示制表位，以便更轻松地设置 SQL 格式。

SQL Server 2000 的查询分析器不存在这些问题。

Yes. SQL is well-suited to speech recognition (as well-suited as a programming language can be, that is), given it's limited vocabulary and sentence-like structure. Aside from formatting the SQL so that it looks nice, I can dictate it much faster than typing. Dictating code isn't for everyone, however. It can be quite frustrating in the beginning. The people who try this and stick with it will probably be those who have no other choice.

I use Dragon NaturallySpeaking 10 Professional. The Professional version has the tools that are needed to create a custom vocabulary like this. Version 9 should work fine, also. It's expensive, so try to get the company you work for to pay for it if possible. Get a decent headset microphone also. The one that comes with NaturallySpeaking isn't good enough (but you may want to try it first to see if it works for you). KnowBrainer is a good place for microphone recommendations.

2009-01-05 Update: I have added tips below specific to dictating in SQL Server Management Studio.

2012-01-04 Update: I have been keeping track of Microsoft's WSR for quite a while now, hoping tools would be added to easily create a completely custom vocabulary from scratch like I am doing in this tutorial with NaturallySpeaking. Unfortunately, it appears that this can only be done through the API (SAPI). I don't have the time to write that code, so I will continue to use NaturallySpeaking to write code until something better comes along.

Preparation

Clean up your database names and code

Dictating "SELECT PT_17, PT_28, PT_29 FROM HIK.dbo.PATINFO" would be a pain in the butt, but I guess it would be possible. You would have to set a lot of pronunciations, since NaturallySpeaking would have no idea how "PT_17" would sound. This would be preferable for dictation:

SELECT Patient.FirstName, Patient.MiddleName, Patient.LastName FROM Claim.dbo.Patient AS Patient WHERE Patient.LastName LIKE '%smith%'

I switched to my TSQL vocabulary to dictate the above statement. Everything up to the LIKE statement is spoken just as it appears. '%smith%' was dictated as "open-single-quote percent-sign sierra mike india tango hotel percent-sign close-single-quote [PAUSE] compound-that". Using consistent table aliases and always preceding fields with them helps improve accuracy, since NaturallySpeaking keeps statistics of how often one word appears near another.

Create a word list of SQL keywords

Put one word on each line. You can optionally follow a word with a backslash (\) and a pronunciation. NaturallySpeaking uses a small backup dictionary of words to determine the pronunciation of words you add to a vocabulary, so it has no problem figuring out how SELECT, FROM, and WHERE are pronounced. It can sometimes figure out a compound word, and it makes its best guess for something like XACT_ABORT. I would provide pronunciations for cases like these. The database you use will determine what words the list contains - check your documentation for a list of keywords. Your list will look something like this, but be much longer.

SELECT
WHERE
FROM
XACT_ABORT\exact-abort
MAXDOP
NOLOCK\no-lock
LEN
RETURNS
CURSOR
MONEY

Also add these words

\New-Line
\New-Paragraph
\All-Caps
\All-Caps-On
\All-Caps-Off
\Cap
\Caps-On
\Caps-Off
\No-Caps
\No-Caps-On
\No-Caps-Off
\No-Space
\No-Space-On
\No-Space-Off
\space-bar
\tab-key
a\alpha
b\bravo
c\charlie
d\delta
e\echo
f\foxtrot
g\golf
h\hotel
i\india
j\juliet
k\kilo
l\lima
m\mike
n\november
o\oscar
p\papa
q\quebec
r\romeo
s\sierra
t\tango
u\uniform
v\victor
w\whiskey
x\xray
y\yankee
z\zulu
PM
AM
one
two
three
four
five
six
seven
eight
nine
ten
eleven
twelve
thirteen
fourteen
fifteen
sixteen
seventeen
eighteen
nineteen
twenty
thirty
fourty
fifty
sixty
seventy
eighty
ninety
hundred
thousand
million
billion
trillion

Keep this list around, since you'll probably modify it several times and re-create your vocabulary to get it the way you like it.

Create a word list of your database object names

This is how I do it in SQL Server:

SELECT DISTINCT * FROM 
(
SELECT DISTINCT [name] FROM Database1.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ') 
UNION 
SELECT DISTINCT column_name AS [name] FROM Database1.information_schema.[columns]
UNION
SELECT DISTINCT [name] FROM Database2.[dbo].[sysobjects] WHERE xtype not IN ('F', 'S', 'PK', 'D', 'UQ') 
UNION 
SELECT DISTINCT column_name AS [name] FROM Database2.information_schema.[columns]
...
) AS UnionTable

Copy and paste the results into a text file.

Create pronunciations for your database object names

Use the same format for pronunciations as listed above. An easy way to create these is to use a regex search and replace function. In SQL Server Management Studio or Visual Studio the following (non-standard) regex will create pronunciations for two word mixed case names.

Find: ^{[A-Z][a-z]+}{[A-Z][a-z]+}$
Replace: \0\\\1-\2

Review the pronunciations and clean up anything that doesn't look right. For acronyms, ASP becomes `A.S.P.'. Keep this list around, as well. If you decide to make vocabularies for other programming languages, you will probably include these words if you're a database developer.

Create a text document that contains all of your SQL code (views, procedures, etc.)

SQL Server:

SELECT * FROM Database1.dbo.[View] UNION SELECT * FROM Database1.dbo.Routine UNION
SELECT * FROM Database2.dbo.[View] UNION SELECT * FROM Database2.dbo.Routine 
...
ORDER BY [Name]

Remove comments and literal strings. Regex search and replace works well for this.

Build your vocabulary

Install NaturallySpeaking and create a new user if you have not already.

Create a new vocabulary

Click on "NaturallySpeaking | Manage Vocabularies...". Click New. Name the vocabulary something appropriate, such as "SQL". Base it on "Base General - Empty Dictation". When it asks you if you want to scan your email or documents, click cancel.

Import words

Click "Words | Import". Add the two word lists you created and import them.

Adapt to writing style

Click "Tools | Accuracy Center". Click "Add words from your documents to the vocabulary". Use the default settings, and select the document you created which contains your code.

Try dictating some SQL

The first thing you'll probably want to dictate is a select statement. Keep in mind that SELECT is what you use to begin a command in NaturallySpeaking that selects text. Because of this, you'll want to say "Cap" before dictating it so NaturallySpeaking doesn't get confused. That's it. Well, at least enough to get you started. Modify your word lists, pronunciations, and word properties as needed. There are other things you can do to increase accuracy and the speed at which you can dictate. As I think of them, I will edit this post and add them here.

Tips for dictating into SQL Server Management Studio

If you dictate into SQL Server Management Studio, you may notice very slow performance. Try the following to alleviate this:

  • Turn off all toolbars (create macros
    to access commonly used
    functionality)
  • Keep as few panes and
    documents open as possible
  • Keep only one database open at a time
  • Hide search results after you're done
    with them (Ctrl+R)
  • If all else
    fails, close and reopen management
    studio
  • Display the tab stops in the edit window to make it easier to format your SQL.

Query Analyzer from SQL Server 2000 does not have these issues.

回复 原文
烙印 2024-07-16 15:16:17

http://voicecode.io

我最近发布了 VoiceCode，这是我为解决自己的 RSI 问题而创建的语音编码解决方案。

我用它在 Sublime Text 和 Xcode 中进行编码，以及一般计算机使用。 它适用于用任何语言（包括 SQL）编写代码。 该解决方案的优点在于，所有命令都可以链接到“命令短语”中，因此您不必像使用其他语音命令解决方案那样在每个单独的命令之间暂停。

它内置支持所有标准变量名称格式（蛇形命名法、驼峰式命名法等），并为键盘快捷键的每种排列提供内置命令（即 command-shift-5、command-option-shift-T 等） ，具有光标移动命令、应用程序切换命令、窗口切换命令、符号组合命令，如“=>”、“||”、“>=”等，等等。 另外，添加您自己的自定义命令也非常容易。

http://voicecode.io

I recently released VoiceCode, a coding-by-voice solution I created to solve my own RSI issues.

I use it for coding in Sublime Text and Xcode, as well as general computer usage. It works for writing code in any language including SQL. The great thing about this solution is that all the commands can be chained into "command phrases" so you don't have to pause between every individual command like you do with other voice command solutions.

It has builtin support for all standard variable-name formats (snake case, camel case, etc), has builtin commands for every permutation of keyboard shortcuts (ie command-shift-5, command-option-shift-T, and so on), has cursor movement commands, app switching commands, window switching commands, commands for symbol combos like "=>", "||", ">=", etc, and tons more. Plus it is very easy to add your own custom commands as well.

回复 原文
~没有更多了~

关于作者

栖竹

暂无简介

文章
评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

梦途

文章 0 评论 0

唐睦州

文章 0 评论 0

且行且努力

文章 0 评论 0

Yiu Peng

文章 0 评论 0

albertliao

文章 0 评论 0

逆夏时光

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文