【新人教育 資料】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:データベース)とは、データの集まりです。以下のようなアイコンで表現されたりします。

image

データファイルが整理整頓されて格納されていて、DBMS(データベース管理システム)によって管理されています。

DBとDBMSをまとめてデータベースシステムという。

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

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

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

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

* 特定のプログラムに依存しない。

* 構造や格納形式(Schema:スキーマ)が公開されている。

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

データベースの種類

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

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

複数のテーブルの集まり。個々のテーブルは、その他のテーブルとリレーション(関連性)を持つ。

まずはリレーショナル型データベースを使って勉強していく形にしましょう。

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

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

テーブルとは

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

image

行指向と列指向(カラム型)

行指向データベース

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

image

列指向(カラム型)データベース

1つの列をひとかたまりのデータとして扱う。

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

image

カラムとデータ型

テーブルをつくる時、どんなデータを入れるか構造を指定します。カラム型には以下のようなものがあります。
標準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のデータを格納

インデックスとデータ

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

image

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

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

例)インデックスの種類

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

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

標準SQL

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

image

日本でも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までの道 〜オブジェクト指向編〜