【ワテ備忘録】C#でMicrosoft SQL ServerやMySQLを操作する【よく使うコマンド】

ワテがSQLの勉強を始めて約一年ほどになる。

基本的なコマンドが幾つあるのか知らないが、便利なコマンドを見付けた場合には備忘録としてメモしておく事にした。

以下はSQL Serverの場合である。MySQLでも同じ感じかな？

テストはしていないが。

また、あくまでSQL初心者のワテのメモなので、もっと良い方法がある可能性もあります。

では、本題に入ろう。

【ワテ備忘録】C#でMicrosoft SQL ServerやMySQLを操作する【よく使うコマンド】
SQLの数値型データについて
1. ４バイト有効数字７桁の実数型
2. ８バイト有効数字１５桁の実数型
データが何件有るのかカウントしたい場合
テーブルの作成日時、更新日時を取得したい
テーブルが存在するかどうか判定する
1. MSSQLの場合
  1. INFORMATION_SCHEMA.TABLESを使う方法
  2. OBJECT_ID() を使う方法
2. MySQLの場合
まとめ

【ワテ備忘録】C#でMicrosoft SQL ServerやMySQLを操作する【よく使うコマンド】

カラム Price はReal型（4バイト）で作成してあり、テーブルに何件かのデータを格納してあるとする。

カラムPriceのデータを取り出すごく普通のSQL文

SELECT [Price] 
FROM [SqlDataSource].[dbo].[Table];

実行例

まあ、これは基本中の基本だな。

角カッコ [ ] について

しかしながら、SQL初心者だった頃のワテは、この [ ] に戸惑った。

通常、こう言うサンプルコードにこんな [ ] があるとそれは説明の為に入れてあるカッコであって、実際に実行する場合には [ ] 無しで記述するのではないかと。

SELECT Price 
FROM SqlDataSource.dbo.Table;

こんな感じ。

結論としては、この例の場合にはこれでも良いのだが、一般には [ ] も付けたまま実行するほうが良い。

理由は例えばカラム名 Price がもし Price Book のように途中にスペースなど入ると二つの単語と見なされるのでSQL文法的に意味が変わって来る。

それが [Price Book] となっていると一つの単語として解釈されるので、正しく実行されるのだ。

数値ではなくて文字列として取得したい場合

その場合には、以下のようにすれば良い。

SELECT CONVERT(varchar(20), [Price]) 
FROM [SqlDataSource].[dbo].[Table];

ここでは varchar(20) の大きさにしてみたが、nvarchar(20) でも同じ結果になった。

"535"
"12800"
"5120"
"306"
"2300"
"23500"
"39429"
"17537"

今の場合は、varchar型なので文字列の長さは元の数値を保持するのに丁度良い長さになるようだ。

勿論、varchar(20)の文字数20は適当に調整する必要がある。あるいはvarchar(max)としておけば、どんなに長い数字が来ても文字列化出来る。

では、固定長の場合も実験した。

固定長の char(n) や nchar(n) を使って数値を文字列化する

SELECT CONVERT(char(10), [Price]) 
FROM [SqlDataSource].[dbo].[Table];

char(10)でもcchar(10)でも同じ結果になった。

"535       "
"12800     "
"5120      "
"306       "
"2300      "
"23500     "
"39429     "
"17537     "

このように左詰めで文字列化された。

文字列の長さを10桁に揃えたい場合

例えば ColNameが文字列型のカラムの場合には、RIGHT関数とSPACE関数を組み合わせて、

SELECT RIGHT(space(10) + ColName, 10) 
FROM [SqlDataSource].[dbo].[Table];

とすると、ColNameの文字列データにspace(10)で空白を10個付けて、その右側から10文字をRIHGT関数で取る事が出来る。

実行すると、例えば

"12345"   　　　  元になるColNameの文字列データ
"     12345" 　　 得られる10桁の文字列

となる。

まあ、このやり方はプログラミングの世界でも数値を右詰で固定長にする時によく使われる小細工だ。

これを使うと、

数値データを文字列化して10桁右詰にしたい場合

SELECT RIGHT(space(10) + CONVERT(varchar(10), [Price]), 10) 
FROM [SqlDataSource].[dbo].[Table];

とすれば、

"       535"
"     12800"
"      5120"
"       306"
"      2300"
"     23500"
"     39429"
"     17537"

このように10桁右詰の文字列が得られる。

数値に三桁ごとにカンマ区切りを入れて文字列として取り出す

SELECT Format([Price], '##,##0') 
FROM [SqlDataSource].[dbo].[Table];

これで行けた。

では、右詰固定長と三桁カンマを組み合わせると、

数値に三桁カンマを入れて、かつ右詰固定長の文字列として取り出す

SELECT RIGHT(space(10) + Format([Price], '##,##0'), 10) 
FROM [SqlDataSource].[dbo].[Table];

これを実行すると以下のようになる。

"       535"
"    12,800"
"     5,120"
"       306"
"     2,300"
"    23,500"
"    39,429"
"    17,537"

いい感じで書式変更が出来た。

注意事項としては、Format関数の引数には数値データが必要なのでPriceは数値データ型である必要がある。

さて、上でやったのはSQL文を実行した段階で数値データを三桁カンマ区切りの右詰固定長文字列化にした訳だが、よく考えてみたらSQLでやらなくても良いだろう。

SQLではあくまで単純に数値（あるいは文字列）として取り出しておいて、その後の処理でC#側で文字列をフォーマットすれば良いのだ。

上でやったのと同じ事をC#でするなら、こんな感じか。

var colStr = String.Format("{0, 10:##,##0}", colData);

ここでcolDataはデータベースから読み取った数値データ。

それを10桁右詰文字列かつ三桁カンマ区切りにしてcolStrに代入する。

String.Format メソッド (String, Object) MSDNから引用

public static string Format(
   string format,
   object arg0
)

C#の PadLeft() も便利

int num=123;
string str10keta = num.ToString().PadLeft(10, '0');

を実行すると、10桁右詰文字列が得られる。

str10keta = “0000000123”

となる。

PadLeft( 10, ‘0’ )

の10は、10個の文字 ‘0’ を追加するのではなてく、numを文字列化してそれに ‘0’ を左に追加して、全体の長さが丁度10文字になるように ‘0’ を追加してくれる。

気の利いた関数だ。

SQLの数値型データについて

データベースの世界では数値データの型が沢山あるので、ワテはほんの一部しか理解できていない。

よく使うのは整数型と実数型だが、

実数型に関してMSDNから引用すると、

float [ (n) ]
n は、科学的表記法で float 型の数の仮数を格納するために使用するビット数です。このため、有効桁数およびストレージのサイズを示します。 n を指定する場合、1 から 53 までの値にする必要があります。 n の既定値は 53 です。

n の値有効桁数ストレージのサイズ

1-24 7 桁 4 バイト

25-53 15 桁 8 バイト

double precision のシノニムは、float(53) です。

n の値	有効桁数	ストレージのサイズ
1-24	7 桁	4 バイト
25-53	15 桁	8 バイト

と言う事で、分りにくい説明だが、ワテの場合には、実数型に関しては以下のデータ型を使うようにしている。

４バイト有効数字７桁の実数型

Real
float(24)

どちらも同じ。

８バイト有効数字１５桁の実数型

double precision
float(53)

どちらも同じ。

データが何件有るのかカウントしたい場合

テーブルの中に何件のデータが有るのか知りたい場合も良くある。

その場合には、

SELECT count(*) FROM [SqlDataSource].[dbo].[Table];

実行するとテーブルの行数が出力される。

テーブルの作成日時、更新日時を取得したい

そのテーブルを作成したのはいつなのか知りたい場合はこれで行ける。

SELECT create_date,modify_date
FROM sys.tables
WHERE name='Tableの名前';

実行結果

create_date	modify_date
2016-11-29 17:00:28.300	2016-11-29 17:00:28.300

こんな感じで日時が得られる。

この例では新規に作成したテーブルにデータを書き込んだdのでcreateもmodifyも同じ日時になっている。

テーブルが存在するかどうか判定する

MSSQLの場合

二種類の方法を見付けた。

どちらでも出来るが、詳しい意味はよく知らない。

INFORMATION_SCHEMA.TABLESを使う方法

IF EXISTS(
  SELECT * FROM INFORMATION_SCHEMA.TABLES 
  WHERE
  -- TABLE_CATALOG = 'SqlDataSource' and 
  TABLE_SCHEMA = 'dbo' and
  TABLE_NAME = 'Table')

SELECT 1 ELSE SELECT 0;

実行して、テーブル名 ‘Table’ が存在すると 1 、無い場合には 0 が得られる。

なお、この方式ではデータベース名を

 TABLE_CATALOG = 'SqlDataSource'

の条件でより厳密に絞り込む事は出来ないようだ。実際、上記のコメントを解除すると存在するテーブルでも結果は 0（存在しない）になる。

OBJECT_ID() を使う方法

IF OBJECT_ID(N'SqlDataSource.dbo.Table', N'U') IS NOT NULL
 SELECT 1 ELSE SELECT 0

この方式だと、

データベース名（TABLE_CATALOG） = ‘SqlDataSource’

スキーマ名（Scheme） = ‘dbo’

テーブル名 = ‘Table’

の三つを指定出来るので、最近のワテはこちらを使っている。

MySQLの場合

SELECT count(*)
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename'

これで行ける。

なお、一つのデータベース内には同じ名前のテーブルは無いので、もしtablenameがある場合には、このSQLの実行結果は１、無い場合には 0 になる。

でも、一般に count(*) を使うとそれは条件にマッチした数が帰るので、1以上なら1、それ以外なら 0 を保証するなら、

SELECT CASE WHEN count(*) >= 1 then 1 else 0 end
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'tablename'

と書いてもいいかも。

まとめ

SQLは歴史が長いので奥が深いと言うかややこしいと言うか、兎に角、覚えなくてはいけない事が多い。

上記の例は、SQL初心者のワテがネット上のサンプルを適当に組み合わせて実験したものなので、正統的な手法かどうかは未確認。

でも、まあ動くのでそんなにはおかしくないと思うが。

みなさんは良い教科書を読むのが良い。

SQL Server 2016の教科書開発編

ソシム

この本はお勧めだ。

著者の松本美穂さんと松本崇博さんは夫婦のようで、夫婦でMicrosoft SQL Serverのコンサルティングやトレーニングなどを行う会社を経営されている。

まさにSQLの達人だ。業界でも有名人らしい。