SQLに関する基本

#そもそもデータベースについて
#####DMBSの役割
元々はデータはファイルに保存されていた。しかしそこにはデメリットがあった。
それはデータとプログラムを分けることができないこと(データの一元管理ができない)やデータへの同時アクセスに弱い事、データの冗長性(ダブり度)が高い。
↓
DMBSを使ってデータを一元管理して、データへの同時アクセスも可能にして、データの冗長性(ダブり度)も低くした。

DMBSの特徴
・トランザクション管理(データの操作に一貫性が生まれる)
・同時実行制御(複数のユーザーが同時に同じDBにアクセスできる)
・ログ先行書き込み(トランザクションのログを残して、DBがどう変わったかを記録する)
・セキュリティー面確保(ユーザー名とパスワードが正しい場合にデータベースにアクセスできる)

トランザクションについてはトランザクション処理をさらっとマスターしよう (1/3)

#####DBの種類
以前は階層型やネットワーク型が使われていたが、今は関係型（表の形）のデータ構造で保存するのが主流。

関係型のデータモデルでは以下の条件を守らなければいけない

・フラットな表構造(行と列で表を作成してね)

.表の間の関係づけは自分たちが持つ値の一致によって行う
他の表(user)の外部のキー(例えばUser_id)を得て、自分の表(taskやmicropost)からその値を持つ行を返す。

・正規化条件
こちらを参考にさせていただきます。正規化の要点を理解する

・リレーショナル代数
様々な命令を使って、表を加工してデータを得る。アプリ1つにつき1つデータベースがあり(複数作ることもできる)、データベースの上にはテーブルが複数設置する。

#DBへのアクセス方法の種類
DBのアクセスには3種類ある。DBクラス、クエリビルダ、ORM。

DBクラスではDBクラスのメソッドを使って、記述されたSQLクエリを実行する。SQLクエリを生で使うようなイメージに近い

クエリビルダではDBクラスのTableメソッドから、メソッドチェーンをつなぎ合わせていくことで、アクセスする。SQLの処理を直感的に使用できるイメージ。

ORMはDBのテーブルを意識せずに、データを物として意識してアクセスできるイメージ。

ORMが最も使いやすいが、それではできない処理などをDBクラス、クエリビルダで実装する

get()では指定した条件のもの全て、first()では指定した条件のものので最初のものということ。whereで一意に定まる条件にした後はfirst()で取得する

#SQLの基本

vigrant環境ではバックでMySQLdが動いている状態
↓
MySQLサーバーに接続(MySQL -u rootで入る)
↓
dbを作る
↓
現在のdbを指定(use データベース名)
↓
table(行列)を作る
↓
データを挿入する(ターミナルから直接orファイルに指示を書いて導入)

#####その他重要な事
アプリ開発でSQLのクエリを直接実行することは少ない。しかしORmapperというオブジェクトとテーブルを結びつけるための道具を使うことでテーブルに変更を加える。ORmapperは最終的にSQLのクエリとして実行される。

MySQLはデータベースのことで、MySQLサーバーが動くことで使用できるようになる。ローカルでは何もしなくても稼働しているが、サーバー側では動いていない時があるので起動させないといけない。

MySQLサーバーにアクセスする方法は２種類あり、ターミナルからの方法とMySQLワークベンチなどのクライアントソフトを利用した場合である。どちらもクライアント側である。クライアントソフトを利用できる場合は利用するが、ターミナルを使うこともある。

#####その他お作法

データベース毎に取り扱えるユーザーを限定するのが基本

select use();

これで今自分が使っているMySQLuserが何かわかる

またMySQLで以下のコードを打つとホストとユーザーを認識できる。

SELECT Host, User FROM mysql.user;

+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

mysql.userとはmysqlデータベースに存在するuserのこと。ホストとはネットワークコンピュータ名をネットワークに対応させた場合の名前。ユーザー名はホストに存在するMySQLを使用するユーザーのこと。補足だがMySQL5.7以降はpasswordを閲覧できず、authentication_stringで確認する。詳しくはMySQLでユーザー一覧を取得する方法 権限とパスワード一覧も取得！

実際にDBの作成や削除は以下のサイトが非常にわかりやすかったです。【MySQL超初心者向け】これを覚えれば初心者になれる基本コマンドとその意味

そしてSQLの命令を書いたファイル()をターミナルで指定してDB変更を実行するのが主流(migrationファイルもその一つ)。その命令にはdbを作るやtableを作るやデータ入力をする命令を書ける。またそこにはデータ入力時の条件も書ける。(ex.一意性、　デフォルト値、　空を許さないなど)

#SQLの処理の順番

FROM:データを取ってくる
↓
JOIN:データを統合する

複数のテーブルの紐付け
Aテーブルの外部キーとBテーブルの主キーが一致していることが必要になる。

joinにもinnerとouterという種類が存在。inner joinでは二つのテーブルのキーによって紐づいている行だけでテーブルを作る(こちらがデフォルト)。outer joinでは二つのテーブルのキーによって紐づいていない行も含めてテーブルを作る。

また取得したいカラムを指定する時は
テーブル名.カラム名
とすることで指定できる。
↓
WHERE:データの所得条件を絞る
↓
GROUP:カラム内で同じ値の物を1つのグループに分けて、その中で関数処理をして出力をする。そのためGROUPと関数はセット

select sum(score), team from users_with_team group by team;

参考文献は【SQL】GROUP BYで自在に集計!集計関数やHAVINGと合わせて使おう
↓
関数:データにある値をいじって出力、GROUP無しでも使える。例えばsumやavg、countなど
↓
HAVING:もしグループ化した後にデータ所得の条件があるならばhaving使用して絞る

select sum(score), team from users_with_team group by team having sum(score) > 10.0;

注意：whereと似ているが順番はwhereで絞った後にgroup化して、havingでさらに条件を加えるって感じ

↓
SELECT:読み出したいデータを決める
↓
ORDER:好きな順番に並べる(デフォルトでは下に行くにつれて数が増えるascになっている)
↓
LIMIT:見れる範囲を狭める

読み出す時の注意
どういう条件で絞るのか、どういうグループ分をするのかを大切にする。またそれらの処理がどういう順番なのかも注意する

###その他

#####between in like

x between a and b --aとbは数字

この意味はaからbの間のxってこと

x in (a, b)

この意味はx = aかx = bのって意味

またクエリのwhereの条件に特定の文字列を含むカラムを探したい場合は
where カラム名 like パターン文字列
で検索できる。
↓
パターン文字列は%を使って表し、％とは0文字以上の文字なので%a%にするとaを含むカラムってことになる。またSQLでは大文字小文字は区別しない。文字数指定は_で行う。

#####order by カラム1 カラム2
order by カラム1 カラム2とすると カラム1で優先的に並び替えた後にカラム2で並び替える。

#####delete
deleteで行を削除する場合、その行に外部キーとして使用されているのフィールドがあれば、削除できない。

#####enumとset(データの型)

enumというデータの型は、テーブル作成時にenumの()内で指定した物しかカラムに代入しないようにできる。

setというデータの型は、テーブル作成時にenumの()内で指定した物しかカラムに代入しないようにできる。しかし()内で指定した物ならば、カラムに複数代入できる。

#####distinct

distinct カラム名でカラムの中の種類を知ることができる。そのためカラムの中にどんな種類の内容があるかみたい時に使う。

#具体例（よく使う順番に説明）

###①まずやること

#####MySQLサーバー起動
(元々動いていて起動しなくて良いケースもある)

systemctl start mysqld;

#####MySQLへの接続
選択したしたユーザーでログイン

mysql -u mysqlユーザー名 -p

###②ユーザー設定

#####ユーザー作成
(この後でgrantで権限追加させる)

create user ユーザー名@ホスト名　identified by 'パスワード';

#####全ての権限を持つユーザーを調べる

select * from mysql.user¥G; --　mysql.userとは全ての操作権限を持つユーザーの情報を管理しているテーブル

権限: Y　になっていれば権限を持つ
権限: N になっていれば権限を持たない

#####ユーザー情報の変更

alter user 'ユーザー名@ホスト名' identified by '新パスワード';

#####作成したユーザーへの権限追加
(作成するだけでは権限は持てない)

grant 権限 on レベル to ユーザー名;

レベル(範囲)を指定した後に権限を与える。レベルは、全て、データベース、テーブル、カラムという順番で範囲が狭まっていく。

例：カラムを触るというレベルに関しては、selectとupdateの権限は与えてあげよう。

参考文献は[MySQL]権限の確認と付与

#####ユーザーの権限削除
rootユーザーが行う。

revoke all privileges on grant option from  ユーザー名@ホスト名

###③データベース設定

#####データベースの作成

create database データベース名 default character set 設定したい文字コード;

#####データベースの選択

use データベース名;

###④テーブル設定

#####テーブル作成(自動連番はauto_incrementで行う)

create table テーブル名 (
列の名前　データ型　条件(ヌルダメ、一意性、主キー、自動連番),
列の名前　データ型　条件(ヌルダメ、一意性とか)
・
・

)エンジン名 自動連番の時の最初の値; 

#####テーブルの中のカラムを確認

show columns from テーブル名;

#####テーブルの中身変更

alter table テーブル名 変更命令;

変更命令の種類は列の追加削除、バリデーションの変更、行,列の名前変更、外部制約の追加削除など

###⑤データ操作

#####データ挿入

insert into テーブル名(カラムA,カラムB,カラムC) values(カラムAに入れたい値,カラムBに入れたい値,カラムCに入れたい値);

#####データ取得

select カラム名　from　テーブル名 where 条件; 

#####データ更新

update テーブル名 set カラム名 = 値 where 条件(updateを適用するカラムの条件、例えばid = 1とか)

#####データ削除

delete from テーブル名 where　条件

#アプリケーションからのDBへのアクセス
アプリケーションからのDBアクセスはORmapperによって行われるので、あまり意識することはないが、これを使わないやり方は以下のような感じ

まずどのホストのどのユーザーのどのDBにアクセスしたいかを明記して、アクセスする。
それがうまくいった時と、いかなかった時の条件分岐書く
↓
うまくいった時の処理の中でクエリをセットして、実行する。
それがうまくいった時と、いかなかった時の条件分岐書く

phpではこの条件分岐の作成がtry catch文で行われたりする

#よく使う概念

#####トランザクション
→まとめて行いたい処理をまとめて行えるようにする
ex. 2つのデータ更新をまとめて行いたい時。例えば銀行の振り込みとか

start transaction --トランザクションの開始
bigin --トランザクションの開始

commit --トランザクションの終了

rollback --一連のトランザクションの取り消し

#####クエリとサブクエリ
クエリとはデータを読み出す命令のこと。
サブクエリとは、クエリで使うためのテーブルを用意するためのクエリ。()のなかにクエリを書いて、それをメインのクエリに使用する。

参考文献は7. サブクエリ

わざわざデータから新しく抽出した値をカラムとして保存しない場合に、一時的に新しい値を得る時に使う。

#####インデックス
add-indexでカラムにインデックスを付けること、そうすると検索が速くなる(文字列のカラムにインデックスをつけるとアルファベット順に並び替えて、検索を速くする)。つけすぎるとパフォーマンスが落ちてしまう、つけたり外したりでパフォーマンスを変更する。ちなみに主キーにはnullでなく、一意性、インデックスを持つという制約が勝手についている。

またインデックスをつける指針はデータが頻繁に検索される場合、そのカラムがwhereの条件によく設定される場合、nullを多く持つカラムの場合など

ALTER TABLE テーブル名 add index インデックス名 (カラム名);

これでどのカラムにインデックスを設定して、その設定の名前をインデックス名とする

また以下のコマンドでどこにどんなインデックスがついているか知れる

show index from テーブル名;

またadd-indexマイグレーションファイルのオプションとしてunique:trueにする事で、カラムの内容は一意性でないといけなくなる。(同一のメルアドは登録できない)。つまりインデックスと一意性の制約を同時に設定してくれるってこと。
↓
主キーとユニークインデックスの違いは何？となりそうだが、主キーはテーブルに一つ、ユニークインデックスはいくつでもという違いがある。

参考文献はデータベースにindexを張る方法と主キーとユニークインデックスキーの違いは？

#####主キー、外部キー、制約
主キーとはテーブルの一つのカラムにだけ設定できる物。主キーにはnullでなく、一意性、インデックスを持つという制約が勝手についている。

別のテーブルBの主キーをテーブルAのカラムに設定する場合、別のテーブルBの主キーを外部キーと呼ぶ。テーブルAとテーブルBの主従関係に対して、Aが削除されたらBはどうするみたいな設定をすることを外部キー制約という。

MySQLで書く場合は

ALTER TABLE `子テーブル` ADD FOREIGN KEY (`子テーブルの外部キーのカラム`) REFERENCES `親テーブル` (`親テーブルの主キー
`) ON イベント(update or delete) 設定;

この意味は
親テーブルの主キーが、イベント(update or delete)されたら、設定のように行う。

例: 親テーブルの行がdeleteされたら、その行の主キーの値を外部キーに持つ子テーブルの行達も一緒に削除する

詳しい設定などは参考文献MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？

#####バックアップ

mysqldump -u root -p データベース名 > バックアップファイル(拡張子は.sql）

リダイレクトを使用して、バックアップを作成する

復元する時は

mysql -u root -p データベース名 < バックアップファイル(拡張子は.sql）

#####view
view(目線)とはtableにいくつも持てる物で、特定の条件を満たしたtableという感じ。tableが条件付きtableを何個も持てるので、見返す時に便利。

create view ビュー名 as　select ビューに落とし込みたいカラム from テーブル名

#その他、補足

トリガーとは
あるtableで入力や削除や変更がされたら、トリガーでという別の場所でその記録をを保存できる

高速でデータベースでは高速でデータのやり取りをするために、画像や写真はデータストレージに置いて、そのリンク先をstringとしてカラムに持つことで、管理する