SQL Server

SQL Serverクライアントの起動
データベースの作成
データベースの確認
データベースの削除
データベースの権限
テーブルの作成
テーブルの確認
カラムの確認
テーブルに要素を登録
テーブルの要素を確認する
テーブルの要素を変更する
テーブルの要素を追加する
テーブルの要素を削除する
ストアドプロシージャの作成
ストアドプロシージャの実行
ストアドプロシージャの変更
ストアドプロシージャの削除
クエリー結果をテキストファイルへエクスポート
クエリー結果をCSVファイルへエクスポート
BCPによるCSVファイルへエクスポート
BCPによるCSVファイルからインポート
データベースのバックアップ
データベースの復元
SQLのバージョン表示
コマンドを連続で実行

SQL Serverクライアントの起動

DBを指定してSQLserverクライアントを起動

$ sqlcmd -E -S [サーバ名]

-S オプションでは SQLserverに接続するサーバ名を指定します。

例えば、下記のようにコマンドを実行します。

$ sqlcmd -E -S svr001

正しく接続されれば、sqlcmdのプロンプトが表示されます。終了したい場合は exit を実行するとプロンプトを終了することができます。

sqlcmd> exit

データベースの作成

sqlcmd>
create database [データベース名]
go

例えば、test というデータベースを作成する場合、下記のようにコマンドを実行します。

sqlcmd>
create database testdb
go

データベースの確認

mysql> show databases;

※MYSQLのみの機能です

データベースの削除

sqlcmd> drop [データーベース名]; go

例えば、testdb というデータベースを削除する場合、下記のようにコマンドを実行します。

sqlcmd> drop database testdb; go
Query OK, 0 rows affected (0.00 sec)

データベースの権限

SQLSERVER> grant all privileges on [dbname].* to [usr]@"%" identified by '[password]';

例えば、testdb というデータベースに GRANT OPTION 以外の全てのシンプルな権限を設定する場合、下記のようにコマンドを実行します。

SQLSERVER> grant all privileges on testdb.* to sanpei@"%" identified by 'xxxxxxxxx';

テーブルの作成

sqlcmd> create table [db_name]..[table_name](
   col_name1 data_type1, 
   col_name2 data_type2,
   col_name3 data_type3, 
   ...)
   go

例えば、testdb というデータベースに fruitと言うテーブルで各要素にcode char型、name vchar型、price int型の要素を設定する場合、下記のようにコマンドを実行します。

sqlcmd> create table testdb..fruit(
                   code char(4),
                   name varchar(20),
                   price int)
       go

※　use またはデーターベースを指定して起動した場合はデーターベースに接続されているので、テーブル名の前のデータベース名の指定は省略可能です。

sqlcmd> use testdb
sqlcmd> create table fruit(
                   code char(4),
                   name varchar(20),
                   price int)
       go

テーブルの確認

テーブルの確認

MYSQL> show tables from [db_name];

※MYSQLのみの機能です

カラムの確認

カラム（要素）の確認

MYSQL> show columns from [db_name].[tbl_name];

MYSQL> show columns from [tbl_name] from [db_name];

MYSQL> show columns from [tbl_name];

※MYSQLのみの機能です

テーブルに要素を登録

sqlcmd> insert into [db_name]..[tbl_name] values(
                 '[column1]', 
                 '[column2]'
                 ,[column3])
       go

例えば、testdb というデータベースに登録する場合下記のようにコマンドを実行します。

sqlcmd> insert into testdb..fruit values('1001', 'banana',420)
       go

テーブルの要素を確認する

sqlcmd> select [column1],[column2], ... from [db_name]..[tbl_name]
go

例えば、testdb というデータベースの fruitテーブルの全要素を確認する場合下記のようにコマンドを実行します。

sqlcmd> select * from testdb..fruit
sqlcmd> select code, name, price from testdb.fruit
sqlcmd> select * from fruit
go

code    name      price
--------------------------
1001    banana1        420
1002    apple2         300
1003    orange3        150

上記がsqlserverの正規の表示形式です

データベース接続されている場合、データベース指定は必要ありません。

例えば、testdb というデータベースの fruitテーブルの要素name, priceを確認する場合下記のようにコマンドを実行します。

sqlcmd> select name, price from testdb..fruit
go

name      price
------------------
banana1        420
apple2         300
orange3        150

データベース接続されている場合、データベース指定は必要ありません。

テーブルの要素を変更する

テーブルの要素を変更（更新）する

sqlcmd> update [db_name]..[tbl_name] set [column1]=[data1]  [, [column2]=[data2] ...] 
     [where [columnx]=[datax]]
     go

例えば、testdb というデータベースの fruitテーブルの codeが1001要素のpraice要素とname要素を更新する場合下記のようにコマンドを実行します。

sqlcmd> update testdb..fruit set price=4200, name='banana9' where code=1001
sqlcmd> select code, name, price from testdb.fruit
go

+------+---------+-------+
| code | name    | price |
+------+---------+-------+
| 1001 | banana9 |  4200 |
| 1002 | apple2  |   300 |
| 1003 | orange3 |   150 |
+------+---------+-------+

データベース接続されている場合、データベース指定は必要ありません。

テーブルの要素を追加する

テーブルの要素を追加（挿入）する

sqlcmd> insert into [db_name]..[tbl_name] ([column1], [column1], ...)
   values ([data1], [data2], ...)
   go

例えば、testdb というデータベースの fruitテーブルに codeが1008でnameがbanana8でpraiceが198の要素を追加する場合、下記のようにコマンドを実行します。

sqlcmd> insert into testdb..fruit (code, name, price) values (1008, 'banana8', 198)
sqlcmd> select code, name, price from testdb.fruit
       go

+------+---------+-------+
| code | name    | price |
+------+---------+-------+
| 1001 | banana9 |  4200 |
| 1002 | apple2  |   300 |
| 1003 | orange3 |   150 |
| 1008 | banana8 |   198 |
+------+---------+-------+

データベース接続されている場合、データベース指定は必要ありません。

テーブルの要素を削除する

テーブルの要素を削除する

sqlcmd> delete from [db_name]..[tbl_name] [where [columnx]=[datax]];
go

例えば、testdb というデータベースの fruitテーブルの nameがbanana8の要素を削除する場合下記のようにコマンドを実行します。

sqlcmd> delete from testdb..fruit where name='banana8';
sqlcmd> select code, name, price from testdb.fruit;
go

+------+---------+-------+
| code | name    | price |
+------+---------+-------+
| 1001 | banana9 |  4200 |
| 1002 | apple2  |   300 |
| 1003 | orange3 |   150 |
+------+---------+-------+

データベース接続されている場合、データベース指定は必要ありません。

ストアドプロシージャの作成

ストアドプロシージャを作成する

create procedure [ストアド名] as
[ストアドの内容]
go

例ではデータベース「DBStest」のテーブル「TBLtest」より、要素「売上」の合計を集計するストアドを作成します。

create procedure STRtest as
select SUM(売上) as 売上合計 from DBStest..TBLtest
go

ストアドプロシージャの実行

ストアドプロシージャを実行します

use [データベース名]
exec [ストアド名]
go

「ストアドプロシージャの作成」にて作成したストアド「STRtest」を実行します。

use DBStest
exec STRtest
go

売上合計が集計されました。

売上合計
-----------
    1080000

ストアドプロシージャの変更

ストアドプロシージャを変更します

alter procedure [ストアド名] as
[ストアドの内容]
go

例では「ストアドプロシージャの作成」にて作成したストアプロシージャ「STRtest」に、新たに消費税合計の列を追加します。

alter procedure STRtest as
select SUM(売上) as 売上合計,(SUM(売上) * 0.8) as 消費税合計 from DBStest..TBLtest
go

変更したストアプロシージャ「STRtest」を実行します。

use DBStest
exec STRtest
go

消費税合計が新たに集計されました。

売上合計消費税合計

----------- --------------
   1080000       864000.0

ストアドプロシージャの削除

drop procedure [ストアド名] as
[ストアドの内容]
go

drop procedure STRtest
go

例では「ストアドプロシージャの作成」にて作成したストアド「STRtest」を削除します。

use DBStest
exec STRtest
go

ストアドを実行

use DBStest
exec STRtest
go

ストアド「STRtest」が削除されたため結果が得られません

データベース コンテキストが 'DBStest' に変更されました。
メッセージ 2812、レベル 16、状態 62、サーバー MASAYA-PC、行 2
ストアド プロシージャ 'STRtest' が見つかりませんでした。

クエリー結果をテキストファイルへエクスポート

C:\>sqlcmd -E -S [サーバ名] -d [データベース名] -i [SQLファイル名] -o [出力ファイル名]

-E オプションでは データベースへの接続時にWindows認証による接続を指定します。
-S オプションでは SQLSERVER に接続するサーバ名を指定します。
-d オプションでは接続先のデータベース名を指定します。
-i オプションでは抽出時に使用するsqlファイル名を指定します。
-o オプションでは出力ファイルのファイル名を指定します。txtやcsv等の拡張子を指定可能です。

例えば、testtb というテーブルよりファイルを出力する場合には、下記のようにコマンドを実行します。

C:\Users\usr01>sqlcmd -E -S localhost -d testdb -i testin.sql -o testfile1.txt

※SQLファイル「testin.sql」はSQLCMDが格納されている場所、今回の例では「C:\Users\usr01」に予め格納してください。

今回はSQLファイル「testin.sql」に下記のSQLを記載します

SELECT * FROM testtb
go

※コマンドにてデータベース「testdb」が既に指定されているため、当DB下のテーブルより抽出する場合にはデータベースの指定は必要ありません

コマンド実行後にSQLCMDが格納されている場所、今回の例では「C:\Users\usr01」に出力ファイル「testfile1.txt」が出力されます。

データベース コンテキストが 'testdb' に変更されました。
管理番号       商品名                  単価          売上         
---------- -------------------- ----------- -----------
1          apple                        300      600000
2          orage                        500      300000
3          melon                        800       80000
4          banana                       250      100000

(4 行処理されました)

また複数のオプションを用いることで更に出力ファイルの様式を変更可能です

C:\>sqlcmd -E -S localhost -d testdb -W -i testin.sql -o testfile1.txt

-W オプションでは出力ファイルより空白を削除します。

データベース コンテキストが 'testdb' に変更されました。
管理番号 商品名 単価 売上
---- --- -- --
1          apple 300 600000
2          orage 500 300000
3          melon 800 80000
4          banana 250 100000

(4 行処理されました)

C:\>sqlcmd -E -S localhost -d testdb -h -1 -i testin.sql -o testfile1.txt

-h  オプションではヘッダーの表示をする行数を指定します。

値に -1 を指定した場合にはヘッダーが表示されません。

データベース コンテキストが 'testdb' に変更されました。
1          apple                        300      600000
2          orage                        500      300000
3          melon                        800       80000
4          banana                       250      100000

(4 行処理されました)

C:\>sqlcmd -E -S localhost -d testdb -s ',' -i testin.sql -o testfile1.txt

-s オプションでは出力ファイルより列間の区切り文字を指定します。

今回の例では区切り文字に , を指定します。

データベースコンテキストが ‘testdb’ に変更されました。管理番号 ,商品名 ,単価 ,売上

----------,--------------------,-----------,-----------
1         ,apple               ,        300,     600000
2         ,orage               ,        500,     300000
3         ,melon               ,        800,      80000
4         ,banana              ,        250,     100000

(4 行処理されました)

クエリー結果をCSVファイルへエクスポート

C:\>sqlcmd -E -S [サーバ名] -m 1 -d [データベース名] -W -s "," -i [SQLファイル名] -o [拡張子を .csv とした出力ファイル名]

例えば、TBLtest というテーブルよりexcelファイルを出力する場合には、まず下記のようにコマンドを実行します。

C:\>sqlcmd -E -S localhost -d DBStest -W -s "," -i testin.sql -o testfile1.csv

Excleはデフォルトでは , にて列を区切るため -s オプションを用いて区切り文字を , とします。

データベース コンテキストが 'DBStest' に変更されました。
管理番号,商品名,単価,売上         
----------,--------------------,-----------,-----------
1,apple,300,600000
2,orage,500,300000
3,melon,800,80000
4,banana,250,100000

(4 行処理されました)

上記の例より

データベース コンテキストが 'DBStest' に変更されました。

の部分をファイルに表示したくない場合には、コマンドに -m -1 と入力します。

管理番号,商品名,単価,売上         
----------,--------------------,-----------,-----------

の部分をファイルに表示したくない場合には、コマンドに -h -1 と入力しヘッダーを非表示にします。

(4 行処理されました)

の部分ををファイルに表示したくない場合には、取り込み用のsqlファイルに set nocount on と記載します。今回の例ではsqlファイル「testin.sql」に下記のように記載します。

use DBStest
set nocount on
SELECT * FROM [dbo].[TBLtest]
go

 C:\>sqlcmd -E -S localhost -m 1 -d DBStest -W -h -s -s "," -i testin.sql -o testfile1.csv

出力結果が変更されました。

1,apple,300,600000
2,orage,500,300000
3,melon,800,80000
4,banana,250,100000

BCPによるCSVファイルへエクスポート

BCPコマンドで全てのテーブルをCSVファイルにエクスポートします

C:\> bcp [データベース名].[管理者名].[テーブル名] out [出力ファイル名] -t, -c -S [サーバ名]

-T オプションでは データベースへの接続時にWindows認証による接続を指定します。
-S オプションでは SQLSERVER に接続するサーバ名を指定します。
-t オプションでは 出力ファイルより列間の区切り文字を指定します。

※CSVファイルは , にて列を区切るため、区切り文字を , とします。

-c オプションでは 各テーブルの要素を char 形式にて出力します

例えば、TBLtest というテーブルよりファイルを出力する場合には、下記のようにコマンドを実行します。

テーブル
C:\>bcp DBStest.dbo.TBLtest out table.csv -t, -c -S localhost -T

1,apple,300,600000
2,orage,500,300000
3,melon,800,80000
4,banana,250,100000

BCPによるCSVファイルからインポート

BCPコマンドで指定DBのテーブルにCSVファイルよりインポートします

C:\>bcp [データベース名].[管理者名].[テーブル名] in [出力ファイル名] -t, -c -S [サーバ名]

例えば、TBLtest というテーブルにファイルを取り込む場合には、下記のようにコマンドを実行します。

C:\>bcp DBStest.dbo.TBLtest in table.csv -t, -c -S localhost -T

データベースのバックアップ

データベースのバックアップとログを取得します

sqlcmd -E -S [サーバ名] -Q“BACKUP DATABASE [データベース名] TO DISK='[バックアップファイル名]' WITH INIT"

-Q オプションでは コマンド実行後にSQLCMDを終了します。
-q オプションでは コマンド実行後にSQLCMDを終了しません。

例ではデータベース「DATtest」のバックアップファイル「BACKUPFILE1」を取得します。

sqlcmd -E -S localhost "BACKUP DATABASE DATtest TO DISK='BACKUPFILE1' WITH INIT"

コマンドの実行後にバックアップファイルはSQLserverの「Backup」フォルダに、ログファイルは「Log」フォルダに格納されます。

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\

※格納先はバージョン、環境により異なる場合があります。

データベースの復元

データベースのバックアップからの復元を行います

RESTORE DATABASE [データベース名] FROM DISK = N'[バックアップファイル]' WITH REPLACE

例ではデータベース「DATtest」をバックアップファイル「BACKUPFILE1」にて復元します。

sqlcmd> RESTORE DATABASE DATtest FROM DISK = N'BACKUPFILE1' WITH REPLACE
go

コマンドの実行後にログファイルは「Log」フォルダに格納されます。

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\

※格納先はバージョン、環境により異なる場合があります。

SQLのバージョン表示

現在のSQLのバージョンを表示します

sqlcmd> select @@version
go

コマンドを連続で実行

WAITFOR DELAY '[実行間隔]'
GO [実行回数]

例では同じコマンドを5秒ごとに実行する動作を、5回行います。

USE database
select * from table
WAITFOR DELAY '00:00:05'
GO 5