sqlcmd是什么?
sqlcmd真是开发者的福利,有了这玩意儿之后,数据库的部署,与数据库相关的一些自动化工作忽然简单了好多,也算是一个神器了。sqlcmd可以通过以下方式执行sql语句,sql脚本和系统过程!
- 通过命令提示符
- 在查询编辑器中的 SQLCMD 模式下
- 在 Windows 脚本文件中
- 在 SQL Server 代理作业的操作系统 (Cmd.exe) 作业步骤中
sqlcmd命令
我们可以在命令行下输入sqlcmd -?
来查看sqlcmd支持的命令,结果如下,非常清晰,如果很少使用sql server或者其他数据库,可能有一点点懵,没有关系,我们下边看几个使用例子就好了。
Microsoft (R) SQL Server Command Line Tool
Version 15.0.2000.5 NT
Copyright (C) 2019 Microsoft Corporation. All rights reserved.
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend] [-L[c] list servers[clean output]]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile] [-z new password]
[-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-K application intent]
[-M multisubnet failover]
[-b On error batch abort]
[-v var = "value"...] [-A dedicated admin connection]
[-X[1] disable commands, startup script, environment variables [and exit]]
[-x disable variable substitution]
[-j Print raw error messages]
[-g enable column encryption]
[-G use Azure Active Directory for authentication]
[-? show syntax summary]
使用实例
这里举几个例子,例子在windows的cmd下演示,但是实际应用中,完全可以放到windows脚本中进行自动化。
使用sqlcmd创建数据
在上边的help中我们看到有这么一个选项,[-i inputfile]
,使用这个选项可以执行sql脚本,我们首先写好数据库创建的脚本,然后使用这个选项执行以下,就可以完成数据库的自动创建了。
sqlcmd -i bankDB.sql
执行sql语句
以下是执行sql语句进行增删查的例子,
sqlcmd -d dbBank -Q "delete from t_user"
sqlcmd -d dbBank -Q "INSERT INTO t_user (code, name) VALUES (1,'xiaoming')"
sqlcmd -d dbBank -Q "select * from t_user"
上边的例子中,dbBank
是数据库实例名,-Q
表示执行完语句后退出。
下边我们执行一个查询,看看效果,
PS C:\db> sqlcmd -d dbBank -Q "select x,y,z from t_gps"
x y z
------------------------ ------------------------ ------------------------
-23841.519381999999 10031.667928000001 10465.281665
-23842.630016999999 10032.4208 10464.572515
-23840.958706000001 10035.797155 10471.961947
-23841.268124999999 10035.490234000001 10471.82294
-23842.15193 10034.437113 10473.411489
-23842.123846999999 10034.731308 10473.716693
-23844.085588000002 10034.371595000001 10478.963207999999
当然我们如果直接将上边的查询使用-o
选项进行导出,进行分析,那么这个分列可不太好,我们虽然可以使用excel工具的分列功能,可是如果我们进行下边的查询,那就不是那么容易分列了,
PS C:\db> sqlcmd -d dbBank -Q "select gpstime,x,y,z from t_gps"
gpstime x y z
----------------------- ------------------------ ------------------------ ------------------------
2020-05-29 18:15:00.000 -23841.519381999999 10031.667928000001 10465.281665
2020-05-29 18:30:00.000 -23842.630016999999 10032.4208 10464.572515
2020-05-29 18:45:00.000 -23840.958706000001 10035.797155 10471.961947
2020-05-29 19:00:00.000 -23841.268124999999 10035.490234000001 10471.82294
2020-05-29 19:30:00.000 -23842.15193 10034.437113 10473.411489
2020-05-29 19:45:00.000 -23842.123846999999 10034.731308 10473.716693
2020-05-29 20:00:00.000 -23844.085588000002 10034.371595000001 10478.963207999999
2020-05-29 20:15:00.000 -23843.295811 10034.750808999999 10477.039223
2020-05-29 20:30:00.000 -23842.700742000001 10034.844998 10476.080190000001
2020-05-29 20:45:00.000 -23842.613986 10034.876617 10475.102005999999
2020-05-29 21:00:00.000 -23842.190895 10034.803967 10475.072185999999
2020-05-29 21:15:00.000 -23841.973268000002 10034.931447999999 10475.0244
为什么呢?因为我们第一列是日期时间列,可是这中间有个空格,如果我们使用空格分列,那么这个日期时间列的数据也会被分割为两列,而且即便有办法分割成功,可是我们毕竟付出了一定的effort,何必如此呢。
PS C:\db> sqlcmd -d dbBank -s ',' -Q "select gpstime,x,y,z from t_gps"
gpstime ,x ,y ,z
-----------------------,------------------------,------------------------,------------------------
2020-05-29 18:15:00.000, -23841.519381999999, 10031.667928000001, 10465.281665
2020-05-29 18:30:00.000, -23842.630016999999, 10032.4208, 10464.572515
2020-05-29 18:45:00.000, -23840.958706000001, 10035.797155, 10471.961947
2020-05-29 19:00:00.000, -23841.268124999999, 10035.490234000001, 10471.82294
2020-05-29 19:30:00.000, -23842.15193, 10034.437113, 10473.411489
2020-05-29 19:45:00.000, -23842.123846999999, 10034.731308, 10473.716693
2020-05-29 20:00:00.000, -23844.085588000002, 10034.371595000001, 10478.963207999999
2020-05-29 20:15:00.000, -23843.295811, 10034.750808999999, 10477.039223
2020-05-29 20:30:00.000, -23842.700742000001, 10034.844998, 10476.080190000001
2020-05-29 20:45:00.000, -23842.613986, 10034.876617, 10475.102005999999
2020-05-29 21:00:00.000, -23842.190895, 10034.803967, 10475.072185999999
如上,我们只需要加入-s
来指定列的分隔符 (colseparator),我们指定输出文件的时候如果是csv格式,那么我们就无需分列了,被逗号隔开的地方自然分列。另外如果我们不希望看到多余的空格,那么只需再加一个选项-W
,如下,
C:\db>sqlcmd -d dbBank -W -s , -Q "select gpstime,x,y,z from t_gps"
gpstime,x,y,z
-------,-,-,-
2020-05-29 18:15:00.000,-23841.519381999999,10031.667928000001,10465.281665
2020-05-29 18:30:00.000,-23842.630016999999,10032.4208,10464.572515
2020-05-29 18:45:00.000,-23840.958706000001,10035.797155,10471.961947
2020-05-29 19:00:00.000,-23841.268124999999,10035.490234000001,10471.82294
2020-05-29 19:30:00.000,-23842.15193,10034.437113,10473.411489
2020-05-29 19:45:00.000,-23842.123846999999,10034.731308,10473.716693
2020-05-29 20:00:00.000,-23844.085588000002,10034.371595000001,10478.963207999999
2020-05-29 20:15:00.000,-23843.295811,10034.750808999999,10477.039223
2020-05-29 20:30:00.000,-23842.700742000001,10034.844998,10476.080190000001
2020-05-29 20:45:00.000,-23842.613986,10034.876617,10475.102005999999
2020-05-29 21:00:00.000,-23842.190895,10034.803967,10475.072185999999