Techcamp 転職 参加の記録

73期 2020年3月9日開始

3月25日(水) 13日目 「データベースの設計」

引き続きSQLのコードから

 

sequel pro に直接コードを打ってデータ検索をする

クエリタブを選択

select *

from users

データテーブルの検索 selectがカラム名 fromがテーブル名

select family_name

from users

e.g.family_nameカラムの取り出し。

※命令文は1行目から書き直す必要がある

 

ワイルドカード

文字の代わりとして使うことが出来る記号。SELECT句においては、アスタリスクが"全てのカラムを取得する"という意味のワイルドカードとして定義がされている

 

レコードの取得

WHERE id = 1

WHERE family_name = "阿部"

WHERE id <= 5

 

select *

from users

where family_name = "阿部"

e.g.阿部レコードのみを取得

 

where句

AND演算子が使える

 

SELECT *

FROM users

WHERE age <= 22 AND prefecture = "神奈川県"

 

or演算子

SELECT *

FROM users

WHERE age <= 20 OR prefecture = "東京都"

 

NOT演算子

SELECT *

FROM users

WHERE NOT prefecture = "東京都"

 

BETWEEN演算子(<= and =>) 上限下限を指定

ハイフン二つでコメント

SELECT *

FROM users

WHERE age BETWEEN 21 AND 24

-- ageが21以上かつ24以下

 

IN演算子(or)

1つのカラムに対しリストを指定して、カラムの値がそのリストに含まれるとき、その式は正になる

SELECT *

FROM users

WHERE prefecture IN ("東京都", "神奈川県")

prefectureが東京都か神奈川県を表示

 

CNCAT関数

SELECT CONCAT(family_name, first_name)

FROM users

複数の文字列をつなげる※繋がって一つのセルで表現される。別からむで出力は以下

SELECT family_name, first_name

FROM users

AS句

カラム名に別名をつける※↓”AS”は省略可能、書かなくて良い

SELECT CONCAT(family_name, first_name) AS "名前"

FROM users

 

DISTINCT 重複削除

SELECT DISTINCT user_id

FROM shifts

WHERE date = "2015-07-01"

 

GROUP BY 指定カラムの同じデータをグループとしてまとめる ※結果は上記と同じになるが、集計のされ方が異なる

SELECT user_id

FROM shifts

WHERE date = "2015-07-01"

GROUP BY user_id

 

COUNT関数 指定カラムを全て数える。以下のようにGroup by 併用の場合はグループ数を数える。以下ではASをコマ数としている。

SELECT user_id, COUNT(*) "コマ数"

FROM shifts

WHERE date = "2015-07-01"

GROUP BY user_id

 

JOIN 別のテーブル情報の結合 FROMの後に記述。

E.g.テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2

※JOINにはLEFT JOINやRIGHT JOINもある。

 

↓ASで短くできる

FROM shifts s

JOIN users u ON s.user_id = u.id

SELECT user_id, COUNT(*) "コマ数", u.* —u.*はuserテーブルの全ての情報を取得するという意味

FROM shifts s

JOIN users u ON s.user_id = u.id

WHERE date = "2015-07-01"

GROUP BY user_id

SELECT CONCAT(family_name, first_name) "名前",COUNT(*) "コマ数"

FROM shifts s

JOIN users u ON s.user_id = u.id

WHERE date = "2015-07-01"

GROUP BY user_id

最終的に、名前でシフトのコマ数が取得できた

 

サブクエリ ある検索結果を使用して別のSQL文を実行する仕組

NOT IN それ以外

 

以下では"2015-07-01"にシフトに入っていない人の一覧を取得

SELECT *

FROM users

WHERE id NOT IN (

    SELECT DISTINCT user_id

    FROM shifts

    WHERE date = "2015-07-01"

)

セレクト文を全て取得し、user_idの重複削除を取得

 

 

 

データベース設計

要素は以下の3つ。

  • サービスで扱う概念(エンティティ)
  • エンティティの属性
  • エンティティ同士の関係性(リレーション)

 

エンティティ

例えばSNSでは「ユーザー」や「投稿内容」、「コメント」

 

必須となる手順は以下の4つ

  1. データベースで管理するデータ(エンティティ)を決める
  2. それぞれのデータの持つ属性を決める
  3. エンティティ同士の関係性を決める(リレーション)
  4. データを実際にデータベースのテーブルとして定義する

 

エンティティはテーブルに相当し、属性はテーブルが持つカラムに相当

 

キー

テーブルにおけるキーとはレコードを識別するための特別なカラム

主キー

多くの場合ID

外部キー

他のテーブルのレコードを識別する。例えばこのテーブルIDとは別のカラムで他のテーブルのIDと対応する数字(ID)など

 

rails g model テーブル名

モデルテーブルをターミナルから作成

 

制約 バリデーション

設定できる制約の中で主なものは以下の4つです。

  • NOT NULL制約

      マイグレーションファイルのdef change のcreate_tableへ記述後マイグレーション→ t.string :name, null: false

 

  • 一意性制約

重複データ禁止制約     

% rails g migration AddEmailToUsers email:string

add_indexメソッドの中でunique: trueという引数を指定することで、一意性制約をかけるためのマイグレーションファイルを作成できる

マイグレーションファイルのdef change へ追加 add_index :users, :email, unique: true

 

  • 主キー制約

主キーである属性値が必ず存在してかつ重複していない ※RailsではIDカラムが元々この制約を持っている

 

  • 外部キー制約

外部キーの対応するレコードが必ず存在しなくてはいけないという制約

作成したマイグレーションファイルのdef change のcreate_tableへ記述 t.references :user, foreign_key: true

 

 

インデックス

テーブル内のデータの検索を高速にするための仕組。カラムに設定するとそのカラムの検索が高速化する。

デメリット

  • データを保存・更新する速度が遅くなる
  • データベースの容量を使う

 

マイグレーションファイル

def change

    add_index :テーブル名, :カラム名

複数カラムへのインデックス

add_index :テーブル名, [:カラム名, :カラム名]

 

 

正規化

データベースのデータ構造をより効率的で重複や無駄のないシンプルな構造にするための手順

正規化を行ったあとのデータベースの構造を正規形という。反対は非正規形。

 

正規化は段階を踏む

非正規形

第一正規形

テーブル内に繰り返されるレコードをなくすこと

第二正規形

第三にほぼ同じ?

第三正規形

属性ごとにテーブルを分ける。e.g.一つのテーブルだったものを生徒テーブル、授業テーブルにきり分けるなど

(ボイスコッド正規形、第四正規形、第五正規形)

第三で終える事がほとんど。デメリットが大きい、小さいテーブルが増えるので遅くなる。

 

 

(課題

Id student class class_teacher lesson1 Lesson2 lesson1_score Lesson2_score

上記計8カラムのテーブルを切り分ける

以下の5テーブルになる

studentsテーブル 外部キーhomeroomeを持つ

Teacherテーブル 外部キーhomeroomeを持つ

homeroomsテーブル 主キー+ホームルームネイむ

subjectsテーブル 主キー+科目名

scoresテーブル 外部キーsubject_idを持つ

※ER図を書いた際

一対多の関係のときに外部キーを設定する。

 

 

中間テーブル

has_many throughオプション

中間テーブルをスルーするが外部キーはもらうという意味

  has_many :中間テーブル名

  has_many :多対多のテーブル名, through: :中間テーブル名

※しっかりと中間テーブルともアソシエーション を組む事

 

マークダウン 文書を記述するための軽量マークアップ言語のひとつ。記述されたものは、HTMLに変換される。

 

 

 

復習ドリル ~Explain the definition and make an example sentence.~

 

ワイルドカード

※JOINにはLEFT JOINやRIGHT JOINもある。

サブクエリ

データベース設計

エンティティ

キー、主キー外部キー

インデックス

正規化の段階

中間テーブル、thtoughオプション