Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nagahisa(Kenzo Nagahisa)

[Cloud102]#4 SQL事始め

Last updated at Posted at 2017-03-06

はじめに

　前回Azure Machine Learning(AML) Studioを少し触りましたが、元データはAML Studio内に用意されていたサンプルデータ(Automobile...)を使いました。
　実際にAML Studioを業務で使う場合には、元データは社内のDBやDWHからSQLでエクスポートしたものを取り込んで使うことも多いと思います。
　また最近はRDBMSだけでなく、多量のログなどの検索・分析にSQL方言を使うケースが増えてきました(Hadoop/Hive, GCP/BigQuery)
　実際に機械学習などを行う場合は、このデータ準備までが全体作業の80%といわれるくらい大変な作業です。
　一方、SQLは技術者でなくても使えますし、習熟すると複雑な検索条件を設定して必要なデータを取得でき、またその処理を再利用可能な形で保存しておくこともできます。

　で、言い訳が長くなりましたが、今回もPython勉強から大きく脱線してSQLの初歩を勉強します。（一応、PythonからのSQL実行にもトライします:sweat:）

　SQLが初めての人もいるので、今回は黒い画面(ssh)やWeb UI(Jupyter Notebook)ではなく、いつも使っているWindows PC上のAccess2013を使用してSQLにトライします。
　「この勉強会はLinuxだぁPythonだぁとか言ってるけど、やっぱり業務はWindowsだよね」というMS Loveな人にも楽しんでもらえればうれしいです:smile:

AGENDA

1.SQLってなによぉ
2.AccessをSQLで使う
3.SQL初めの1歩
4.DDLをちょろっと触る
5.JOINがつかえればかっこいい
6.一応Pythonでも(SQLite3)
7.MySQLを感じる
8.練習問題
9.MySQLをもっと感じる
10.特別講義

##1.SQLってなによぉ

ググるとたっくさん情報があります。こんな連載もありますのでざっと目を通すだけで勉強になります。

ゼロからのリレーショナルデータベース入門
http://www.atmarkit.co.jp/ait/series/2686/

で、この連載初回をちょっと見てみましょう。

ゼロからのリレーショナルデータベース入門（1）：
そもそも、リレーショナルデータベースとは何か？
http://www.atmarkit.co.jp/ait/articles/0807/16/news149_2.html

001.PNG

はい、なんのことはない"みんな大好きExcelくん"の高機能版です。
「ジョイン」はExcelだとVLOOKUPで２つのテーブルを結合するようなイメージです。

Excel2013 複数のテーブルを結合する方法
https://support.office.com/ja-jp/article/%E8%A4%87%E6%95%B0%E3%81%AE%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92%E7%B5%90%E5%90%88%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95-c80a9fce-c1ab-4425-bb96-497dd906d656

で、今回のテーマのSQLはこんな説明になっています。

002.PNG
（ちなみに、僕はSELECT=DML派です。）

ちょっと堅苦しい説明だとこんな感じ。

RDBMS
https://ja.wikipedia.org/wiki/%E9%96%A2%E4%BF%82%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E7%AE%A1%E7%90%86%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0
RDBMSの簡単な定義として、「データを表の形式で永続化し、かつデータ間の関連もまた表の形式で永続化できるDBMS」

関係データベースの、標準の問い合わせ言語として SQL がある。

SQL
https://ja.wikipedia.org/wiki/SQL

SQLは、Structured Query Languageと紹介されることもある。これは、IBM社がかつて提供していたSQL/DSやその他いくつかのRDBMSの実際の製品におけるSQLにおいては正しい。
しかし、標準SQLは、言語仕様そのものにはIBM社のRDBMSのDB2の影響が大きく見られるものの、「SQLは、何かの略語ではない」と定義している

まぁ難しいことは置いといて、SQLは使えばすぐに覚えられるので、さっそく触って明日から「SQLできます！」って言えるようになりましょう:smile:

##2.Microsoft AccessをSQLで使う

なんと、今回は社内OA標準アプリに入っているMicrosoft Access2013(以下Access)を使います。
社内OA環境には(SSHクライアントは入ってないのに)Accessが入ってるってみなさん知ってました？(まぁAccessだけじゃなくて、Office2013が全部はいっているんですが・・・）

Accessは、Access DBMSとそれをベースにしたアプリケーションの開発・実行基盤です。
(DBとアプリは、*.mdbとか *.accdbって１つのファイルに格納されます）

Accessは外部RDBMSのフロントエンドに使えるなど非常に強力なツールですが、今回はその中のあまり活用されていないSQL機能を使ってみます。
(Accessは、SQL使わなくてもGUIで同じことが出来るので、わざわざSQL使わないんです:sweat:)

(1)参考サイト

Access SQL初心者入門講座
http://access-sql.seesaa.net/

会社環境はAccess2013なのでこのサイトのキャプチャとちょっとUIが違います。
本記事の前半では、この「Access SQL初心者入門講座」の抜粋をAccess2013での実行する方法を説明します。
超抜粋なので「なんかよー判らん」って方は元記事を参照してください。

注意
AccessデータベースはRDBMSとしてみるとちょっと特殊です。
ハンズオン環境として便利なので使いますが、他のRDBMSではデータ型やワイルドカード演算子が違うことがあるので、頭の隅に「Accessはちょっと特殊」って覚えておいてください。
なお、最後に一般的なMySQLもちらっと触ってAccessとの差を感じる予定です。

###(2)サンプルAccessデータベースのダウンロード

横着して最終段階(第四段階)のAccessデータベース(SampleDB4.mdb)をダウンロードして使用します。

第四段階で使うAccessデータベースの準備をする。
http://access-sql.seesaa.net/category/6440281-1.html

このファイルを自分のWindows PCにダウンロードして展開してください。
003.PNG

###(3)サンプル内のテーブルを確認する

展開してできたファイル(SampleDB4.mdb)をダブルクリックしてAccessを起動してください。
こんな感じで起動したら「コンテンツの有効化」をクリックしてセキュリティ警告を消しておきます。

004.PNG

なんか、都道府県一覧のテーブルみたいですね。
005.PNG

次にこれを「デザインビュー」で見てみます。なお、今まで見ていたのは「データシートビュー」です。
左上メニューの「表示」の🔽 をクリックして「デザインビュー」を選びます。
006.PNG

テーブル名を右クリックしたメニューから「デザインビュー」に移ることも出来ます。
009.PNG

デザインビューの下には選択中のフィールドに対応したプロパティが表示されています。
T08Detailsテーブルではこんな感じです。
007.PNG

008.PNG

８ケのテーブルはこんな内容です。

TableName テーブル名
T01Prefecture 都道府県マスター
T02City 市町村マスター
T03Town 郵便番号マスター
T04Customer 顧客マスター
T05Sex 性別マスター
T06Goods 商品マスター
T07Slip 伝票
T08Details 明細

全体構造はこちらに説明があります。
http://access-sql.seesaa.net/category/3070541-1.html

access-sql166.gif

access-sql167.gif

###(4)サンプル内のクエリを確認する

メニューの下の「テーブル」横にある🔽 をクリックして「クエリ」に変更します。
010.PNG

各々のクエリを「デザインビュー」で確認してみます。（SQL練習クエリは空です）
Q01Slip
011.PNG
Q02Details
012.PNG

２つのクエリの「データシートビュー」も確認して、元デーブル内容と合わせて「ふむふむ」となって下さい。（「ふむふむ」とならない人は申告して下さい）

3.SQL初めの1歩

SampleDB4.mdbにある「SQL練習」クエリを使ってSQL文にトライします。

(1)参考サイト

詳しい内容は以下の参考サイトを読んでもらうとして、どんどんSQL実行しちゃいましょう！
SQL講座
http://rfs.jp/sb/sql/

(2)SQL実行の仕方

「SQL練習」クエリの「デザインビュー」から「SQLビュー」に移動します。
013.PNG

SQLビューに以下を入力します（コピペできます。最後のセミコロンを忘れないように）。

015.PNG

左から２番目のメニューの「SQL実行」から実行します。
016.PNG

SQL文の結果が「データシートビュー」で表示されました。
017.PNG

Accessでは、「データシートビュー」「デザインビュー」「SQLビュー」を行き来することが出来ます。

注意
次からいろんなSQL文を「SQL練習」クエリから実行しますが、一回実行する都度、デザインビュー内の「SQL練習」画面を右上の❎ ボタンで消しておいて下さい。（これは直前に実行した内容が保存されているためです）

(3)SELECT文

以下のSELECT文を一行づつ、「SQL練習」クエリの「SQLビュー」にコピペして実行してみましょう（行頭からセミコロンまでが１行のSQL文です）。

SELECT * FROM T01Prefecture WHERE PREF_CD >= 40;
SELECT PREF_CD,PREF_NAME FROM T01Prefecture WHERE PREF_CD >=36 and PREF_CD <=39;
SELECT PREF_CD,PREF_NAME FROM T01Prefecture WHERE PREF_CD BETWEEN 36 AND 39;

SELECT * FROM T01Prefecture WHERE PREF_NAME LIKE '*川県';
SELECT * FROM T01Prefecture WHERE PREF_NAME LIKE '*川県' ORDER BY  PREF_CD DESC;

SELECT COUNT(*)  FROM T01Prefecture WHERE PREF_NAME LIKE '*川県';

SELECT * FROM T01Prefecture WHERE PREF_NAME LIKE '*山県' ;
SELECT * FROM T01Prefecture WHERE PREF_NAME LIKE '*山*県' ;

SELECT PREF_CD, COUNT(*) FROM T02City GROUP BY PREF_CD;
SELECT T01.PREF_NAME, COUNT(T02.CITY_CD) FROM T01Prefecture T01,T02City T02 WHERE T01.PREF_CD = T02.PREF_CD GROUP BY PREF_NAME;

SELECT T01.PREF_NAME, T02.CITY_CD FROM  T01Prefecture T01  INNER JOIN T02City T02  ON T01.PREF_CD = T02.PREF_CD;

SELECT T01.PREF_CD, T01.PREF_NAME, Count(T02.CITY_CD)
FROM T01Prefecture AS T01 INNER JOIN T02City AS T02 ON T01.PREF_CD = T02.PREF_CD
GROUP BY T01.PREF_CD, T01.PREF_NAME;


SELECT PREF_CD FROM T02City;
SELECT DISTINCT (PREF_CD) FROM T02City;

こんなのが出てきましたので頭の片隅に置いておいて下さい。

  • ORDER BY ASC/DSC
  • GROUP BY
  • DISTINCT

(4)INSERT・UPDATE・DELETE文

SELECT文はテーブルやクエリの情報を条件を指定して検索するだけでしたが、SQLでテーブルにデータを追加したり、削除したりすることが出来ます（っていうか、テーブルに対する操作はなんでもできます:smile:)。
適当な顧客データをSQL入れてみましょう。

INSERT INTO T04Customer VALUES  (11, '永久 健三', 11217, '3650064', '080-1234-5678', '1961-01-19', 0);

おっと、他レコードをみると電話番号は入れなくて良いみたいですので、電話番号を更新しておきましょう。

UPDATE T04Customer SET TEL='000-000-000' WHERE CST_CD = 11;

で、最後に追加したデータを削除してみます。

DELETE FROM T04Customer WHERE CST_CD = 11;

##4.DDLをちょろっと触る

　いままで見てきたSQL文(SELECT,INSERT,UPDATE,DELETE)はDML(Data Manipulation Language)といわれます。
　DMLに対して、テーブル自体を作成(CREATE TABLE),削除(DROP TABLE),変更(ALTER TABLE)するSQL文をDDL(Data Definition Language)と言います。
　開発プロジェクトでは、データベース管理者(DBA)がDDLを処理し、アプリケーション開発者がDMLを使ってコーディングするという役割分担をすることが多いです。

以下を参考に、新しいテーブルをCREATE文で作成してみましょう。

###(1) 参考
クエリからSQL（CREATE TABLE文）を実行してAccessのテーブルを作成する
https://www.nishi2002.com/3963.html

###(2) やってみる

「SQL練習」クエリの「SQLビュー」で以下を実行してみます。

CREATE TABLE 会員(
ID COUNTER PRIMARY KEY,
会員名  CHAR(10),
ポイント INT,
入会   YESNO,
メモ    MEMO,
登録日  DATE
);

018.PNG

実行ボタンを押したら、「テーブル」表示にして「会員」テーブルが出来ていることを確認します。
019.PNG

追加した「会員」テーブルと「顧客マスター」にリレーションを作成するなど、新しい機能がどんどん追加できます。

「会員」テーブルにMISC列を追加してみます。

ALTER TABLE 会員 ADD COLUMN MISC TEXT;

020.PNG

列が追加されました。
021.PNG

「会員」テーブルを削除します。

DROP TABLE 会員;

022.PNG

会員テーブルがなくなりました。
023.PNG

##5.JOINが使えればかっこいい

　まずはデータ分析のためにSELECT文がいろいろ使えるようになるとよいと思います。
（元データのINSERT/UPDATE/DELETEは危険なので誰かにお願いしましょう:sweat:）

　ただし、1つのテーブルのデータだけで分析することはほぼ無い(注)ので、複数テーブルを結合して検索するSELECT文をを勉強しましょう。
(注)あらかじめ必要な項目をまとめて１つのクエリ(View)にしておき、それをSELECT文のFROM句で指定することもあります。

で、手抜きですが、参考リンクを貼っておきますので、INNER JOIN, OUTER JOINを勉強してください。

SQL素人でも分かるテーブル結合(inner joinとouter join)
http://qiita.com/naoki_mochizuki/items/3fda1ad6594c11d7b43c

INNER JOINを使った表の結合
http://www.pursue.ne.jp/jouhousyo/SQLDoc/select21.html
LEFT JOINとRIGHTを指定して使って表を結合する
http://www.pursue.ne.jp/jouhousyo/SQLDoc/select22.html
同じ表を結合する(自己結合)
http://www.pursue.ne.jp/jouhousyo/SQLDoc/table_betumei.html

こんなJOIN(結合)があるんだなぁってのが感じられれば、なんとかなります:smile:。

  • 内部結合 INNER JOIN
  • 外部結合 RIGHT JOIN, LEFT JOIN

##6.一応Pythonでも(SQLite3)

一応この勉強会シリーズは「Python勉強会」なので、PythonからSQL文を操作してみます。
ただ、MySQLなどのようなちゃんとしたRDBMSは環境構築の問題があるので、簡単に使えるSQLiteを扱います。

###(1)参考
Pythonで色々なデータベースを操作する
http://qiita.com/mima_ita/items/9a5ab3b45c7575776b06

###(2)SQLiteをPythonで触ってみる

上記参考のSQLite部分をJupyter Notebookでやってみましょう。
http://qiita.com/mima_ita/items/9a5ab3b45c7575776b06#sqlite

AML Studioに空のPython3 Notebookを作って以下Pythonコードを順番に実行します。

・テーブル作ってみる(CREATE TABLE)

# -*- coding: cp932 -*-
# sqlite3はPython2.5から以降から標準であるはず.
import sqlite3
conn = sqlite3.connect('test.sqlite3')
sql = '''CREATE TABLE  IF NOT EXISTS  t01prefecture(
                         pref_cd INTEGER,
                         pref_name TEXT);'''
conn.execute(sql)

・データ入れてみる(INSERT)

#コミットの試験
pref_cd = 100
pref_name = u"モテモテ国"
conn.execute(u"""INSERT INTO t01prefecture(PREF_CD, PREF_NAME)
            VALUES (?, ?)""" , (pref_cd, pref_name))

pref_cd = 101
pref_name = u"野望の国"
conn.execute(u"""INSERT INTO t01prefecture(PREF_CD,PREF_NAME)
            VALUES (?, ?)""" , (pref_cd, pref_name,))
conn.commit()

・検索してみる(SELECT)

rows = conn.execute(u'SELECT * FROM t01prefecture WHERE pref_cd > ?', (0,))
for row in rows:
    print(u"%d %s" % (row[0], row[1]))

・おまけ

# ユーザ定義
# 文字を連結するのみ
class UserDef:
    def __init__(self):
        self.values = []
    def step(self, value):
        self.values.append(value)
    def finalize(self):
        return "/".join(map(str, self.values)) 

conn.create_aggregate("userdef", 1, UserDef)
rows = conn.execute(u'SELECT userdef(PREF_NAME) FROM t01prefecture')
for row in rows:
    print(u"%s" % (row[0]))
conn.close()

こんな感じになります。
030.JPG
おまけ部分
031.JPG

##7.MySQLを感じる

先ほど「MySQLは環境構築が・・・」と書きましたが、探したら無料でWebから使えるこんなのがありました。
https://paiza.io/

「コードを作成してみる（無料）」をクリックするとこんな画面に遷移します。
040.JPG

「Swift」になっているところをクリックして「MySQL」に変更します。
041.JPG

この画面にSQL文を入れて実行することができます。以下はサンプルをそのまま実行した結果です。
042.JPG

043.JPG

注意

  • 無料で使えるけど、1セッションで終了する(データ保持しない）ので全ソース(DDL + DML)を記述する必要があります。
  • 型がAccessデータベースと異なることに注意( Accessでは、VARCHARじゃなくTEXTだったよね）

##8.練習問題

Paizaラーニングさんはこんな練習問題も用意してくれてます。この練習問題を各自上記の paiza.ioで実行してみてください。

成人の人数を数える SQL問題
https://paiza.jp/learning/count-age

部署毎の人数を数える SQL問題
https://paiza.jp/learning/divisions-number

##9.MySQLをもっと感じる

黒い画面に抵抗感の少ない方は、ちゃんとしたMySQL Serverを試してみることをお勧めします。
と言っても、無償枠が切れると知らない間に課金されちゃうような環境だと嫌なので、完全無料でできる方法をご紹介します。

はい、僕の大好きな、GCP Cloud Shellを使います。
Cloud Shell使ったことないかたは、以下など参考にしてください。

[Cloud102]#2-1 GCPを始めよう(前半:GCE編)
http://qiita.com/nagahisa/items/d491da7099c1229d42d2

Cloud Shell起動します。
001.JPG

MySQL Serverをインストールします。いろいろ文句言われますが無視して'Y'で進めます。

sudo apt-get install mysql-server

002.JPG

途中でrootユーザのパスワードを聞かれますので２回入力します（入力文字は表示されていません）。
003.JPG

インストールが終わったらサービスを起動します。

sudo service mysql start

004.JPG

mysqlクライアントを使ってMySQLサーバに接続します(rootユーザ）。rootユーザのパスワードを聞かれるので、インストール中に入力したパスワードを入力します（入力文字は表示されていません）。
mysqlクライアントが立ち上がるとプロンプトが'>mysql'に変更されます。

mysql -u root -p

005.JPG

MySQLには"DATABASE"という概念があるので、適当なDATABASE(以下キャプチャではkenzo)を作成して、それを使います。このkenzoにはまだテーブルは作成されていません。

CREATE DATABASE kenzo;
USE  kenzo;

006.JPG

CREAT TABLE文でテープル testを作成して、確認します。

CREATE TABLE test(
    id int(11) NOT NULL,
    first varchar(255) NOT NULL,
    last varchar(255) NOT NULL,
    PRIMARY KEY (id) );

show tables;
desc  test;

007.JPG

この残念ながら環境はCloud Shellとの接続が終了すると消えてしまいます。
しかし、フルのMySQL機能が使えますので、作成したテーブルやデータをファイルにexportしておき、再度MySQL Serverをインスールし、そのファイルからimportすれば継続作業は可能です。（CREATE TABLEなどのDDL文をScript fileで作成しておき、テーブル作成などはそのscript fileで行って、importは作成済みテーブルへ行うことをお勧めします）。

せっかくなので、GCP Cloud SQL(MySQL互換）を使ってみたい！という方は以下など参考にトライしてみてください。
mysqlクライアントの接続先サーバがCloud SQLになるだけで、上記の確認手順はそのまま使えます:smile:

https://cloud.google.com/sql/docs/getting-started?hl=ja
https://cloud.google.com/shell/docs/examples#manage_cloud_sql_data

##10.特別講義(QIITAでは非公開です）

如何でしたでしょうか？ SQLってとっても簡単でしょ！　
今は使う機会が少ないかも知れませんが、絶対に覚えておいて損はないです。

Enjoy!!:muscle_tone3:

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?