【新人教育資料】SQLへの道〜DB編〜

あらすじ

新人がいっぱい入ってくる。新人のレベルもバラバラ。教育資料も古くなっているので、更新しましょう。
どうせなら、公開しちゃえばいいじゃん。という流れになり、新人教育用の資料を順次更新していくことにしました。

※後々、リクエストに応じて更新することが多いのでストックしておくことをおすすめします。

自分はTEMONA株式会社でCTOをしていますが、頭でっかちに理論ばっかり学習するよりは、イメージがなんとなく掴めるように学習し、実践の中で知識を深めていく方が効率的に学習出来ると考えています。

※他の登壇やインタビュー記事はWantedlyから見てください。

教育スタイルとしては正しい事をきっちりかっちり教えるのではなく、未経験レベルの人がなんとなく掴めるように、資料を構成していきます。

以下のようなシリーズネタで進めます。

No.	記事
1	【新人教育資料】第1章 SQLへの道〜DB編〜
2	【新人教育資料】第2章 SQLへの道〜3値論理編〜
3	【新人教育資料】第3章 SQLへの道〜基本数学編〜
4	【新人教育資料】第4章 SQLへの道〜SQL基本操作編〜
5	【新人教育資料】第5章 SQLへの道〜絞込編〜
6	【新人教育資料】第6章 SQLへの道〜ソート編〜
7	【新人教育資料】第7章 SQLへの道〜集合関数（SUM、MAX、MIN、AVG、COUNT）編〜
8	【新人教育資料】第8章 SQLへの道〜グループ編〜
9	【新人教育資料】第9章 SQLへの道〜結合編〜

では、今回もはじめていきましょう！

DB(データベース)とは

今回からはSQLへの道題して、DB周りを勉強しSQLを学びましょうという内容でお送りします。DB、DBって当たり前のように会話が出てくるようになるので、業務で会話についていけるように勉強していきましょう。

DB（DataBase:データベース）とは、データの集まりです。以下のようなアイコンで表現されたりします。

データファイルが整理整頓されて格納されていて、DBMS（データベース管理システム）によって管理されています。
DBとDBMSをまとめてデータベースシステムという。

DBMSで管理されるデータとしては、大きく分けて以下の3つで構成されています。

データ・ファイル
ログ・ファイル
コントロール・ファイル

難しい言葉がたくさん出てきますが、最終的にはデータの集まりだと認識してくれればいいと思います。

データベースは基本的に以下の特徴を持ちます。

特定のプログラムに依存しない。
構造や格納形式（Schema:スキーマ）が公開されている。

スキーマという言葉は頻出するのでちょっと調べておいてください。

データベースの種類

データベースの種類は色々あったりしますが、リレーショナル型データベースシステム(RMDBS)を勉強していきましょう。初めのうちは、それ以外のものは存在することだけ理解出来ていればOKです。

リレーショナル型データベース
PostgreSQL
MySQL
SQLite
Microsoft SQL server
oracle
db2
カラム指向型データベース（列指向型）
PureData System for Analytics（旧Netezza）
exadata
SAP HANA
Cassandra
DynamoDB
memcached
redis
bigtable
hbase
ドキュメント指向型データベース
mongodb
couchdb
グラフデータベース
neo4j
時系列データベース
Graphite
InfluxDB
opentsdb

リレーショナル型データベース

複数のテーブルの集まり。個々のテーブルは、その他のテーブルとリレーション(関連性）を持つ。
まずはリレーショナル型データベースを使って勉強していく形にしましょう。

以下はチューニンニングなんかによってもだいぶ変わるのでなんともいえませんが、defaultで使った場合って感じで特徴書いておきます。

名称	概要	無償/有償
PostgreSQL	複雑クエリは得意。スケールアウトに弱い。基本はスケールアップで対応。	無償
MySQL	WEBサーバなどの参照用には向いている。スケールアウト構成が柔軟に組める。	無償
Microsoft SQL server	Microsoftの出しているDB、linuxでは動作しない。監査やセキリティ対応などは比較的容易に対応出来る	有償
oracle	linux上で動かす方が断然パフォーマンスがいい(昔の話かも!?)大規模向き。実はMySQLから移行出来るツールなんかもあったりする	有償
db2	IBM製のDB。昔はくせがあったが、最近のバージョンは使いやすくなった印象。	有償

テーブルとは

行（column:カラム）と列（record:レコード）で構成される二次元の表。Excel等のスプレッドシートのようなものです。
リレーショナル型データベースの基本単位。

行指向と列指向（カラム型）

行指向データベース

1つの行をひとかたまりのデータとして扱う。1レコードずつ取り出し処理を行う。
例えば、「特定の条件をみたす行を検索して取り出す」等はよくある操作。

列指向（カラム型）データベース

1つの列をひとかたまりのデータとして扱う。
例えば、「テーブル内の全レコードに対し、あるカラムの値を一斉に更新する」等の操作に便利。特定の列に関する集計など、列に対してまとめて操作をするような処理が得意。

カラムとデータ型

テーブルをつくる時、どんなデータを入れるか構造を指定します。カラム型には以下のようなものがあります。
標準SQLでは定義されていますが、各RMDBにより異なるので、必ず事前に調べるようにデータ型の違いを調べるようにしましょう。

名称	例	概要
SMALLINT	真数型	-32,768～32,767の整数
INTEGER	真数型	-2,147,483,648～2,147,483,647の整数
FLOAT [(p)]	概数型	仮数部が-2p～2pの概数
DOUBLE PRECISION	概数型	8バイトの浮動小数点数
REAL	概数型	4バイトの浮動小数点数
DECIMAL [(p [,q])]	真数型	桁数p、小数点以下q桁のパック形式10進数
NUMERIC [(p [,q])]	真数型	桁数p、小数点以下q桁のゾーン形式10進数
CHARACTER [(n)]	文字列型	固定長でnn文字の文字列
NATIONAL CHARACTER [(n)]	各国語文字列型	固定長でnn文字の日本語文字列
CHARACTER VARYING (n)	文字列型	可変長でn最大n文字の文字列
NATIONAL CHARACTER VARYING (n)	各国語文字列型	可変長でn最大n文字の日本語文字列
CHARACTER LARGE OBJECT *1	CLOB型	指定された文字コードのデータで格納
DATE	日時型	年から日までの10文字の日付を格納
TIMESTAMP [(p)] [WITH TIME ZONE]	日時型	時から秒までの8文字の時刻を格納
TIME [(p)] [WITH TIME ZONE]	日時型	年から秒までの19文字の時刻印を格納
INTERVAL	時間隔修飾子	時間隔型
BINARY LARGE OBJECT *1	BLOB型	バイナリ属性のデータを格納
BIT [(n)]	BIT型	0,1,nullのデータを格納
BIT VARYING (n)	BIT型	最大n文字までのビット列データを格納
BOOLEAN *1	BOOLEAN型	true,false,unknownのデータを格納

インデックスとデータ

インデックス（索引）とは、テーブルに格納されているデータに素早くたどり着くための仕組みです。
本の索引のようなものだと理解しましょう。テーブルを作成する際に、カラムに対して必要に応じて定義します。
カラムに対して、インデックスを作成することを「インデックスを張る」とも言ったりします。

カラムに対して、やらためったらインデックスを張っても意味がありません。むしろ、テーブルに対して参照以外の操作をする際に合わせてインデックスを更新したりしなければならないため、遅くなったりします。またインデックスを保存するインデックスファイルの分だけ、ファイル容量も大きくなります。

開発の際には、実際に本番に近い想定データを用意し、利用したいSQLコマンドを試しながら、コマンドでRDBMSの統計情報からキャッシュヒット率がどうなっているが、カーディナリ値がどうなっているかなど、調べながらindexの張る事になります。

例)インデックスの種類

RDBMSによって異なりますが、インデックスにも種類があります。取りうる値の分布や特性に合わせてインデックスを選択しましょう。

ビットマップインデックス
Btreeインデックス
ハッシュインデックス

標準SQL

SQL（エスキューエル）とは、リレーショナルデータベース管理システム（RDBMS)において、データに問い合わせるための言語です。
当初は、各RDBMSごとに言語の仕様が異なっていました。そこで、国際標準として規格化されています。その標準化された規格に合わせたものを標準SQLといいます。SQL92規格など呼ばれますが、SQL92規格、SQL99規格を押さえておけば大抵のSQLが使えます。SQL92規格などは一般公開されているものではないので、すぐにネットで取得するような事が出来ないのですが、書籍などでは解説しているものが多いため、興味がある人は購入をおすすめします。

日本でもJIS規格としてSQLが定義されているので興味がある人は見てください。

JISX3005-2
※Macだと見れないかもしれないです。

各種RDBMSも基本的には標準SQLに沿うように設計させています。実際に開発する際も特定のRDBMSに依存したSQLでなく、標準SQLをベースに使用することで、規模が大きくなった際にDBを他のものに移行させなければならなくなった時も少ない工数で対応することが出来ます。

自分の書いたSQLが標準SQLに準拠しているのかチェックしてくれるサイトなどもあるので、試してみると面白いです。
標準SQL92規格チェッカー

MySQLインストール

手元の学習環境として、リレーショナル型データベースのMySQLを使って勉強していきましょう。
本格的には「【新人教育資料】SQLの道〜SQL基本操作編〜」から使う予定ですが、先走ってMySQLをインストールしちゃいましょう。

HomeBrewインストール(入っている人はSKIP)

$ ruby -e "$(curl -fsSL https://raw.github.com/Homebrew/homebrew/go/install)"

【Mac】HomeBrewでバージョンを指定してMySQLインストール

brewのアップデート

$ brew update

MySQLのバージョン確認

% brew search mysql
automysqlbackup                mysql                          mysql-connector-c++
homebrew/versions/mysql51      mysql++                        mysql-sandbox
homebrew/versions/mysql55      mysql-cluster                  mysql-search-replace
homebrew/versions/mysql56      mysql-connector-c              mysqltuner
homebrew/php/php53-mysqlnd_ms                  Caskroom/cask/mysql-connector-python
homebrew/php/php54-mysqlnd_ms                  Caskroom/cask/mysql-utilities
homebrew/php/php55-mysqlnd_ms                  Caskroom/cask/mysqlworkbench
homebrew/php/php56-mysqlnd_ms                  Caskroom/cask/navicat-for-mysql

brew

$ brew install homebrew/versions/mysql56

MySQL確認

$ brew info mysql

MySQL起動

$ mysql.server start

Starting MySQL
. SUCCESS!

MySQL接続

$ mysql -uroot

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25 Homebrew

MySQLセキリティ設定(必要に応じて)

セキリティ設定をする癖をつけましょう。

$ mysql_secure_installation

root ユーザのパスワード設定
anonymous ユーザの削除
root ユーザがリモートサーバからログインできないようにする
初期サンプルDBの削除

接続確認

$ mysql -uroot -p
Enter password:[設定したパスワード]

あとがき

最近便利なフレームワークばっか出てきているので、SQLって殆ど触った事ありませんって人が多いように感じます。
実際便利なものはドンドン利用すべきだとは思いますが、あわせて理論部分もしっかり理解出来るように頑張っていきましょう。

開発時にはSQLだけでなく、システムをどのように定義づけていくかというモデリングも必要になるので、以下のUMLまでの道シリーズも暇があれば読んでみてください。

【新人教育資料】第1章 UMLまでの道〜オブジェクト指向編〜

【新人教育 資料】SQLへの道 〜DB編〜