データの検索 基本文法
データの準備
テーブルにあるデータの検索のために、まずはデータの入ったデータベースを用意する必要がある。
今回はこちらでデータの入ったプロジェクトを用意してあるので、以下の手順からデータの入ったデータベースを作成する。
現在mysqlに接続している場合はexitを実行し、mysqlを終了させてる。
1 |
mysql> exit
|
次に、ホームディレクトリ直下の"projects"ディレクトリへ移動。
1 |
cd ~/projects
|
そこで以下のコマンドから"sql-curriculum"というプロジェクトをクローンする。
1 |
クローンが完了したらそのディレクトリへ移動。
1 |
cd sql-curriculum
|
移動が出来たら以下のコマンドを実行し、データベースとカラムを作成。
1 2 3 |
bundle install
bundle exec rake db:create
bundle exec rake db:migrate
|
そして最後に以下のコマンドを実行し、データベースにデータを投入。
1 |
bundle exec rake db:seed
|
以上で作業は完了。
もし、アプリケーション作成段階でエラーが生じた場合は、以下のような赤字の文章が表示される（必ずしも同じ文面ではない）。
【例】ターミナル
1 2 3 4 5 6 7 8 9 10 11 12 |
Installing mysql2 0.5.3 with native extensions
Gem::Ext::BuildError: ERROR: Failed to build gem native extension.
current directory: /Users/user_name/Programs/web/foobar-repo/vendor/bundle/ruby/2.5.1/gems/mysql2-0.5.3/ext/mysql2
/Users/user_name/.rbenv/versions/2.5.1/bin/ruby -r
(中略)
An error occurred while installing mysql2 (0.5.3), and Bundler cannot continue.
Make sure that `gem install mysql2 -v '0.5.3'` succeeds before bundling.
|
エラーが表示されている場合は、以下のコマンドを実行。
ターミナル
1 2 3 4 5 |
念のため、SequelProからデータベースを確認。
SequelProにlocalhostで接続し、"sql_curriculum_development"のデータベースを選択。
そして"shifts"テーブルを押し、データがあることが確認できれば正常にデータの投入が完了している。
データの紹介
投入したデータについて紹介しておく。
今回のデータベースでは、ユーザーがアルバイトなどのシフトを提出するという想定の下、テーブルを作成している。
データベースには"users"と"shifts"の2つのテーブルがあり、1人のuserは多くのshiftを持つという構造になっている。
それぞれのテーブルは以下のようなカラムを持っている。
・usersテーブル
| カラム名 | 内容 |
|---|---|
| family_name | 苗字 |
| first_name | 名前 |
| age | 年齢 |
| prefecture | 出身の都道府県 |
・shiftsテーブル
| カラム名 | 内容 |
|---|---|
| date | シフトの日付 |
| start_time | シフトの開始時間 |
| user_id | このシフトが属するユーザーのid |
（※ シフトは1コマ2時間制であり、レコード1行が1コマを表しているという前提のため、finish_timeのようなカラムはない。）
SQL文を実行する場所について
最後に、SQL文を実行する場所を変更する。
これまではターミナルからmysqlに接続し、データベースやテーブルを作っていたが、SequelProの方からSQL文の実行をしていく。
実際にSequelProを開いて、SQL文を実行する場所を確認。
以下の画像のようにSequelProから、"sql_curriculum_development"のデータベースを選択した状態で、"クエリ"と書かれたタブを押す。
ここへSQL文を書き、実行していく。
試しに一度、SQL文を実行してみる。
SequelProで以下のSQL文を実行
1 2 |
SELECT *
FROM users
|
SQL文を実行するときは以下の画像のように、上画面へSQL文を記述し、"現在を実行"を押す。
以下の画像のように結果が下画面に表示されれば正常にSQL文が実行されている。
また、ターミナルからではなくSequelProからSQL文を実行する理由については以下のようなものが挙げられる。
・検索は長いSQL文を要するが、タイプミスをしたときにその部分だけを直せば再実行が出来る
・検索は場合によって非常に多くの出力を伴うが、それによって実行したSQL文が流れることがないため見やすい
・SQL文の文末に " ; "（セミコロン）を付ける必要がない
基本的な検索の構文
まずは基本的な検索の構文について。
先ほど、SequelProで以下のようなSQL文を実行した。
1 2 |
SELECT *
FROM users
|
このSELECT句とFROM句を用いたSQL文が、検索の基本的な形。
検索するテーブルを指定する
FROM句
検索するテーブルを指定する際は以下のように、FROM句でテーブル名を指定する。
1 |
FROM テーブル名
|
ここはただテーブル名を指定するだけ。
取得するカラムを指定する
SELECT句
取得するカラムを指定する際は以下のように、SELECT句でカラム名を指定する。
1 |
SELECT カラム名
|
実際にここでFROM句とSELECT句を使ったSQL文を実行してみる。
SequelProで以下のSQL文を実行
1 2 |
SELECT family_name
FROM users
|
以下のように結果が表示されていれば正常にSQL文が実行出来ている。
先ほどSELECT句を使った時は以下のように"*"（アスタリスク）を指定していた。
1 2 |
SELECT *
FROM users
|
そして結果としては全てのカラムが取得されていた。
これはSELECT句において、アスタリスクは"全てのカラムを取得する"というワイルドカードだからである。
ワイルドカード
ワイルドカードとは、文字の代わりとして使うことが出来る記号のこと。
SELECT句においては、アスタリスクが"全てのカラムを取得する"という意味のワイルドカードとして定義がされているため、このように指定することで全てのカラムの取得が出来た。
基本的な検索条件の指定
取得するカラムは指定が出来ていても、取得するレコードは指定されていない。
そこで、ここからは取得するレコードの制限をする。
取得するレコードの制限
WHERE句
取得するレコードを制限する際はWHERE句を使用。
WHERE句では取得するレコードの条件を指定し、その条件が正のレコードを取得する。
1 |
WHERE 条件
|
最も基本的な条件の指定方法を見ていく。
WHERE句の条件には、以下のように"="や"<="のような比較演算子を使用することが出来る。
また、内容が文字列や日付の場合は""（ダブルクォーテーション）で文字を囲う必要がある。
1 2 3 4 5 |
WHERE id = 1
WHERE family_name = "阿部"
WHERE id <= 5
|
このとき、WHERE句のみで検索は出来ないので、実際はSELECT句、FROM句と併せてSQL文を実行する必要がある。
実際にSQL文を実行して、どのようにレコードが取得されるかを確認してみる。
SequelProで以下のSQL文を実行
1 2 3 |
SELECT *
FROM users
WHERE family_name = "阿部"
|
以下のように結果が表示されていれば正常にSQL文が実行出来ている。
論理演算子を使ってみる
比較演算子に加えて、ANDやORのような論理演算子を使用してみる。
AND演算子
AND演算子は複数の条件を指定して式を作り、その条件が全て正であるとき、式が正となる。
以下のような式があったとき、条件aもbも正であれば、式は正になる。
1 |
WHERE a AND b
|
実際のSQL文を見てみる。
SequelProで以下のSQL文を実行
1 2 3 |
SELECT *
FROM users
WHERE age <= 22 AND prefecture = "神奈川県"
|
このSQL文では、ageが22以下かつprefectureが神奈川県のレコードを取得している。
以下のように結果が表示されていれば正常にSQL文が実行出来ている。内容を確認してみると、どのレコードもその2つの条件を満たしている。
OR演算子
OR演算子は複数の条件を指定して式を作り、その条件のうちどれか1つ以上が正となれば、式は正となる。
例えば以下のような式があったとき、条件aとbのどちらかが正であれば、式も正になる。
1 |
WHERE a OR b
|
こちらも具体的なSQL文を実行して、挙動を確認。
1 2 3 |
SELECT *
FROM users
WHERE age <= 20 OR prefecture = "東京都"
|
このSQL文では、ageが20以下もしくはprefectureが東京都であるレコードを取得している。
以下のように結果が表示されていれば正常にSQL文が実行出来ている。内容を確認してみると、どのレコードも条件のうち1つ以上を満たしている。
NOT演算子
NOT演算子は1つの条件を指定して式を作り、式の正誤はその条件と逆のものになる。
以下のような式があったとき、条件aが誤となる（当てはまらない）レコードが取得出来る。
1 |
WHERE NOT a
|
こちらも具体的なSQL文を実行して、挙動を確認。
1 2 3 |
SELECT *
FROM users
WHERE NOT prefecture = "東京都"
|
このSQL文では、prefectureが東京都でないレコードを取得している。
以下のように結果が表示されていれば正常にSQL文が実行出来ている。
便利な検索条件の指定
単一カラムの範囲検索
ageが21以上24以下のuserを探したいとする。
そうしたとき、以下のようなSQL文を書くことが出来る。
1 2 3 4 |
SELECT *
FROM users
WHERE 21 <= age AND age <= 24
-- ageが21以上かつ24以下
|
ハイフンを2つ使用して-- コメントと書くと、SQL文として実行されないコメントを残すことが出来る。
このとき"age"という言葉を2回書いてしまっているのは少し冗長である。
こういった範囲指定をする際に使える演算子がある。
BETWEEN演算子
BETWEEN演算子は1つのカラムに対し上限と下限を指定して式を作り、カラムの値がその範囲に含まれるとき、その式は正になるという演算子。
1 |
WHERE カラム名 BETWEEN 下限 AND 上限
|
ageが21以上24以下のuserという条件を、BETWEEN演算子を使って書き直し、実行。
1 2 3 |
SELECT *
FROM users
WHERE age BETWEEN 21 AND 24
|
以下のように結果が表示されていれば正常にSQL文が実行出来ている。
単一カラムのリスト検索
prefectureが"東京都"もしくは"神奈川県"であるuserを探したいとする。
それを実装すると、OR演算子を使って以下のようにSQL文を書くことが出来る。
1 2 3 |
SELECT *
FROM users
WHERE prefecture = "東京都" OR prefecture = "神奈川県"
|
これもまた、"prefecture"という言葉を2回書いてしまっているところが冗長と言える。
こういった場面ではリスト検索をしてあげると良い。
IN演算子
IN演算子は1つのカラムに対しリストを指定して、カラムの値がそのリストに含まれるとき、その式は正になるという演算子。
1 2 |
WHERE カラム名 IN (値1, 値2, ……)
-- (値1, 値2, ……)の部分がリストとなります
|
prefectureが"東京都"もしくは"神奈川県"であるuserという条件を書きなおす。
1 2 3 |
SELECT *
FROM users
WHERE prefecture IN ("東京都", "神奈川県")
|
以下のように結果が表示されていれば正常にSQL文が実行出来ている。
BETWEEN演算子とIN演算子はどちらもSQL文をより綺麗に書くためには必須の演算子である。
そのため、このような演算子を使うことを意識し、また他にもよりよい書き方がないかを考えながらSQL文を書くことを心がけるとよい。
