なずブログ

インフラSE、Java開発、リモートワークエンジニアな人のメモ帳

VBAからSQL~その6.並び替え~

f:id:nazuna_0124:20200206232410p:plain

おつかれさまでーす。なずなです。


今回もSelectの内容です。いつもの準備はオッケーですかー?


f:id:nazuna_0124:20200217220557p:plain


f:id:nazuna_0124:20200217220630p:plain



並び替え:Order by

今回は並び替えです。動きだけ最初に見てみましょう。

年齢順に並び替えてみます。


f:id:nazuna_0124:20200217220740p:plain



Select * From Sample_Shain Order by Nenrei;


結果を見て注目です。

性別のとこはぐちゃぐちゃです。


データベースは並び順が保証されてません


指定しなければデータベースが適当に出力します。


Excelから入ると取っ付き悪い部分です。

そーゆうもんだと割り切って覚えちゃいましょう。



複数指定と逆順

男女を分けて、年齢を降順にしてみます。


f:id:nazuna_0124:20200218002137p:plain

f:id:nazuna_0124:20200218002116p:plain


複数列の設定はこんなかんじです。


-- 一列の降順
-- Select * From Sample_Shain Order by Nenrei desc;


Select * From Sample_Shain Order by Seibetu,Nenrei desc;


f:id:nazuna_0124:20200218002549p:plain



え? 順番を男を先にしたい???


考えてみて!



えー 未記入とかを追加して男、女、未記入で指定したい?


それは結構むずかしいので、先のおはなしで!!


ちまちまつくるよ、こんかいはここまで!

おつかれさまでした。

VBAからSQL~その5.Selectとフィルター~

f:id:nazuna_0124:20200203212829p:plain

おつかれさまです。なずなです。


準備が無事出来てあとは英語っぽい

構文を覚える回です。


JAVAと違って謎の呪文というほどではありません。


A5:SQLの使い方を覚えるのがメインといえます。

SQL文ははっておくので流すだけでもオッケー!


多少の修正を加えておけば、もれなく

「フリーツールのA5でSQL実行は問題なくできます」

と言い切れます!!



準備確認

ツールとサンプルデータのCSVExcelにしたものを準備です。


f:id:nazuna_0124:20200213222852p:plain


f:id:nazuna_0124:20200213222738p:plain



全表示、列指定表示

みたまんまですね!

アスタリスクワイルドカードなのはプログラムでは

よくあるお話です。

Select * From Sample_Shain

f:id:nazuna_0124:20200213222931p:plain


ついでにコメントアウトも覚えちゃいましょう。

--Select * From Sample_Shain

Select Namae,Furigana From Sample_Shain


f:id:nazuna_0124:20200213223219p:plain


こういう使い方はExcelはあまりしないかもですね。

データベースではちょいちょいでてきます。


条件を絞って抽出:Where

ここからですね。性別が女性のデータだけを選んでみます。


f:id:nazuna_0124:20200213223442p:plain


条件を後ろにくっつけます。

文字列を指定するときはシングルクォーテーションです。


--Select * From Sample_Shain

Select * From Sample_Shain Where Seibetu = ''

f:id:nazuna_0124:20200213223650p:plain


文字の曖昧検索

苗字が「あ」から始まる人だけで絞ってみます。


f:id:nazuna_0124:20200213223856p:plainf:id:nazuna_0124:20200213224122p:plain


以前のサンプルデータをダウンロードしていると

わたしとデータそのものは違うので注意!!



VBAではおなじみの Likeです。

ただし、こちらは「%」です。

--Select * From Sample_Shain

Select * From Sample_Shain Where Furigana Like 'あ%'

f:id:nazuna_0124:20200213224253p:plain


どれかと一致

例えば、年齢が「22」または「32」のデータ


f:id:nazuna_0124:20200213224527p:plain


型指定してないので数字でも文字列扱いです!!

Select * From Sample_Shain Where Nenrei = '22' or Nenrei = '32'

両方おぼえてね~

--Select * From Sample_Shain

--Select * From Sample_Shain Where Nenrei = '22' or Nenrei = '32'
Select * From Sample_Shain Where Nenrei in ('22','32')

結果はおんなじです。

f:id:nazuna_0124:20200213224647p:plain


f:id:nazuna_0124:20200213224833p:plain



今回はここまで!

次回は並べ替えからでーす。

VBAからSQL~作成予定メモ~

f:id:nazuna_0124:20200212190354p:plain

おつかれさまです。なずなです。

本日はだらっと仮予定を書いていく日です。


SQLの構文自体は覚えるだけなので、

説明がめんどくさいともいいます←


Selectとフィルター

前回はフィルターとの比較で終わっています。

そこでそのまま単体のテーブルに対してSelectで覚えます。

・列を指定して出力

・Where

・in

・Order by

・Group


リレーションとVlookup

社員番号とチームあたりのマスタテーブルを作成。

各チームの人数を求めるとかそのへんで。


データの削除

テーブル内の全削除とWhere使って削除できれば問題なし。

ここらへんで覚えたいのが、データベースに行の概念がないってことです。


VBAから入ってるとデータを座標で掴む癖がついてることが多いのです。


これは理解しにくくなるので補足説明をします。


データの追加

insertです。これ自体は簡単。


ここでの説明はさらっとながして、VBAで作成するときに

出番があるでしょう。


SQLインジェクションは念のため覚えてほしいところ。


データの更新

updateの構文としてはさらっと。

データベースの感覚掴んでからなので後回しの内容。


・削除追加で更新としたりする。

・更新履歴用のテーブルを作ったりもする。


VBAのシートではあんまりやらない処理が出てきます。



なにはともあれCRUDシステム!です。

補足

Select文の問題は作るかもしれません。

他ページ参照といきたいところですが。


DBとテーブルの設計に絡みそうなところは触れない予定。

目的はSQLの求人応募なのでスキル範囲外です。


ぎりぎり正規化までは基本情報処理準拠くらいで触る予定。

VBAからSQL~その4.できるようになりたいことの目安~

f:id:nazuna_0124:20200206232410p:plain

おつかれさまです。なずなです。


今回は環境を準備しながら、

どういうことができるようになるかを

説明する回です。


なぜ、それをするのかは入ってないので、

手順追いつつ(ぐぐりつつ)試してください。


サンプルデータをダウンロード

kazina.com

サイトをお借りします!


f:id:nazuna_0124:20200210190956p:plain


f:id:nazuna_0124:20200210191033p:plain



こんなかんじの設定でダウンロードしてください。


f:id:nazuna_0124:20200210191119p:plain



拡張子がcgiなので、csvに変更します。

Excelで開くとこう!


f:id:nazuna_0124:20200210191357p:plain


A5:SQLSQL実行の画面

接続まではできた!とのご連絡を頂いたので、

たぶん画面はこんなかんじ。


f:id:nazuna_0124:20200210193315p:plain


ファイル>新規とクリックします。

f:id:nazuna_0124:20200210193542p:plain


文字が入力できる画面が開いたらオッケーです!

f:id:nazuna_0124:20200210193723p:plain


コピペでテーブルの作成

それでは!単にコピペでテーブルを作成してみます。

create table shain(id, name);

f:id:nazuna_0124:20200210193908p:plain


▶ボタンで実行です。 下記みたいなエラーでるときあります。

f:id:nazuna_0124:20200210194030p:plain


そういう時は文字の左上にカーソル移動してから実行

f:id:nazuna_0124:20200210194111p:plain



問題なければ結果が出てきます。

f:id:nazuna_0124:20200210194154p:plain


更新して確認

右クリックして再読み込みします。

f:id:nazuna_0124:20200210194323p:plain


テーブルが1個できました!

ダブルクリックで開くと中身が表示されます。

(まだデータ入れてないのでなにもなし)

f:id:nazuna_0124:20200210194436p:plain



もう1つテーブルを作成

さーて、最初に落としたサンプル用のテーブルを作ります。

おんなじ手順で下記SQLを実行してみてください!


create table Sample_Shain(Namae, Furigana,Adoresu,Seibetu,Nenrei);


うまくできればこの画面まで進みます。

f:id:nazuna_0124:20200210195125p:plain


サンプルデータを張り付ける

この手順がGUIで可能なのがツールを使う理由の1つです。

ダウンロードしたサンプルデータをExcelで開いて

コピーします。


f:id:nazuna_0124:20200210195231p:plain


A5:SQLで右クリック、レコード貼り付け

f:id:nazuna_0124:20200210195328p:plain


f:id:nazuna_0124:20200210195411p:plain


これで準備は完了です!


Excelのフィルタを試す

一度Excelに戻ります。

Csvですがフィルタを付けて情報を抽出する。

例えば

・女性で年齢で昇順

f:id:nazuna_0124:20200210195611p:plain


難しくないはずです。


これをSQLでデータベースに指示できるようになる


SQLの学習とはそれを目指すことになります!!


次回は少しあくかもです。

上手くいかないときはDMくださーい。

VBAからSQL~その3.ツールの導入~

f:id:nazuna_0124:20190920212833p:plain

おつかれさまでっす。なずなです。


今回は学習準備コーナーです。

Windowsにインストールが必要です。


追加のインストールができない??


インターネットにつながらない環境???


このシリーズではパス!!

Accessでがんばれますので、DMでもください。



インストールするもの

Sqlite3

www.sqlite.org


A5:SQL Mk-2

www.vector.co.jp


準備の手順メモ

  1. Sqlite3のダウンロード解凍

  2. A5:SQL のダウンロード解凍

  3. Sqlite3の実行、DB作成、テーブル作成

  4. A5:SQLを開く。作成したDBへ接続

  5. CSVからインポート


2のところまではぐぐりつつがんばって!!


3はできるかどうか調査してみて



眠いからじゃないです←

SQLも触るようになれば

よくあることなんです!シンジテ!!


手順と完成後は連休中になんとかできたらいいなー。

VBAからSQL~その2.AccessのSQL GUI~

f:id:nazuna_0124:20200206232410p:plain

おつかれさまでーす。なずなです。


本日はAccessを持ってない方向けに

簡単な画像だけです。

試さなくていいですよー。

別のツールでもっかいやります。


ふーん。わけわからん!

ぐらいで流してください。



その1.で作ったファイルをインポートする

Vlookupで作った表です。

Accessの初期画面

f:id:nazuna_0124:20200206232949p:plain


インポートの機能がついています。

使ってやると

f:id:nazuna_0124:20200206233242p:plain


左に注目なのです。

テーブルの項目にExcelの シート名が出てきています。


データのインポートが完了しました!



クエリオブジェクトで社員マスタを開いてみる

3つの画面を見てください。

f:id:nazuna_0124:20200206233458p:plain


f:id:nazuna_0124:20200206233521p:plain


f:id:nazuna_0124:20200206233615p:plain


3つ目だけ修正しつつ文字にします

SELECT 社員番号, 氏名 FROM 社員マスタ;


SQL学ぼう!って人は見たことあるかもしれません。

この文字列がSQLです


社員マスタのうち2列だけ表示したいときには

こんなんなってるって雰囲気だけ

感じてください←


Vlookupとリレーショナル

次に見てもらいたいのがこちら

f:id:nazuna_0124:20200206234208p:plain


f:id:nazuna_0124:20200206234247p:plain


はい!ちゃんと出勤データにたいして

マスタを参照して氏名が表示されています。


この動作がVlookupに似ているので

紹介だったのです!


ちなみにSQLの初期状態はこう

SELECT Sheet1.日付, Sheet1.出勤, 社員マスタ.氏名
FROM 社員マスタ INNER JOIN Sheet1 ON 社員マスタ.ID = Sheet1.ID;

ぜんぜんわからん!!! まったくです。

そのへんを今後ゆっくりやってきまーす。


次回からはAccessが無い人のための

ツール準備です。

インストールが入ってしまうのはゆるしてねっ

VBAからSQL~その1~

f:id:nazuna_0124:20200201132848p:plain

おつかれさまです。なずなです。


一向に進まないので、ちょっとずつ書いていく

大作戦で行きます!


下記は概要として流し読みお願いします。


shokuren.hateblo.jp



目安の補足

出勤表を作成します。

Accessにするか外側だけExcelかは応相談。(誰と?)


どんなものかイメージつきますよねー?だいじょぶですよねー??



社員のマスタデータを作る

ブックは載せません。そちらはnoteで展開します。

こういう表を作成してください。


f:id:nazuna_0124:20200205230723p:plain


注目は社員番号です。

重複がなく、番号からそのほかの情報が取得できる


ことが保証されている


このようなデータの集まりをマスタデータって呼ぶことにします。


出勤のサンプルデータを作る

手入力でオッケーです。

あとでサンプル作るプログラム作りますから!

f:id:nazuna_0124:20200205231005p:plain


さー ここです!!


F列に氏名のカラムを作って

Vlookupを使って表示してください。


ぐぐってだいじょうぶです。


Vlookupで表示する

答え書かないですよー。ちゃんと書きましょう!

絶対参照とかそのくらいやり直すのはぜんぜんおっけーです。


f:id:nazuna_0124:20200205231600p:plain


こんなかんじです。


サンプル用に増やす

単に増やします。


f:id:nazuna_0124:20200205231852p:plain



データの型

ここでSheet1の社員Noに書き加える人がいたらどうでしょう?


f:id:nazuna_0124:20200205232128p:plain


人力ならわかりますよ?

でも氏名がエラーになっちゃってます。


2つ問題があります。

・数字+文字が入力されている

・マスタに存在しないデータが入力されている


とても困るんです!



DBという仕組みとソフト

SQLはDBのための仕組みなので、DBの知識も必須です。


まずはこういう変なことされないようにするため

データの格納先としてDBってのがある。


このくらいで覚えちゃっておきましょう。


Vlookupで進めない方はここで終わっておっけーなかんじですっ