ドットインストール 旧版 MySQL入門 メモ

<1〜10>

画面をクリアするには Control + L コマンドの履歴は矢印キーの上下で呼び出すこともできる 末尾の；を忘れたときに、今のコマンドをキャンセルして次のコマンドを打ちたい場合には \c MySQL サーバーへの接続を終了　するには　quit; もしくは　\q としてあげれば OK

show database;
データベースの作成はcreate database データベース名; 削除はdrop database データベース名;
ちなみに今、操作対象になっているデータベースは select database(); で見ることができます.
操作対象を選ぶには、use データベース名;
MySQLのクエリでは大文字小文字は区別されない

データベースごとに作業用ユーザーを設定するのが一般的　　
create user ユーザー名@localhost identified by 'パスワード';

grant all mydb01.* to dbuser01@localhost;
# dbuser01 に対して mydb01 の全てのテーブルに関する全ての権限を与える

mysql -u dbuser01 -p 　# ユーザー切り替え。-pでパスワードを入力

# or -- でコメント　
/* */で複数行コメント

外部ファイルのコマンドを読み込むには source 、もしくは \.

description という意味で desc users; # テーブル構造の確認
テーブルの削除　# drop table テーブル名

MySQLで使えるデータ型で、数値は、整数は int 、浮動小数点は float 、そして float より精度の高いものには double

• の値しか扱わないので扱える領域を広くしてねという場合には、例えば int unsigned のように unsigned キーワードを付けてあげれば OK

文字列は、固定長のデータだったら char 、可変長のデータだったら varchar か text を使う。
商品コードは 4 桁固定といった場合には char(4) といった型
もしくは、メールアドレスのようにデータによって長さがまちまちな場合は varchar(255) としてあげると 255 バイトまでの可変長文字列を格納できます。
どのぐらいの長さになるかよくわからない場合は text

日付なら date 、時間なら time 、日時なら datetime

boolean 型は 1 桁の整数の型である tinyint(1) の別名で、内部的には true は数字の 1 、 false は数字の 0 で管理されています。

MySQL では 0 と null が false で、空文字を含むそれ以外の値は全て true になる

create tableの際、score に null が入らないようにするには、score float not null としてあげれば OK
値を設定しなかったときに使われる値はscore float default 0.0などのように設定する。
重複した値を許さない、という場合は unique

主キーはテーブルにつき 1 つしか付けることができないのですが、 primary key とすれば OK で、こうするだけで null ではない、重複しない値になることが保障されます
1:57
また、主キーには auto_increment という指定をつけて自動的に連番を振るように設定することが多い

テーブルの構造を変えるには、フィールドをあとから追加したい場合ですが alter table users としてあげて、 column を追加するには add column としてあげて、そのあとにフィールド名とフィールドの型（email varchar(255)など）を指定してあげれば OK
普通にこのままだと最後に追加されますが、例えば name のあとに追加したかった場合には、 after name; としてあげるとそのようになる

フィールドを削除したい場合ですが、例えば score を削除したかった場合には drop column としてあげて、フィールド名を書いてあげれば OK

フィールドのデータの型を変えたい場合は、 alter table users change
フィールド名を指定してあげて、新しいフィールド名とその型を user_name varchar(80) のように書いてあげれば OK

テーブル名を変えるには、alter table users rename で新しいテーブル名を書いてあげる

<11〜20>

データの抽出

taguchi さんか fkoji さんを抽出したかった場合ですが、 or を使えばいいので、 … where name = 'taguchi' or name = 'fkoji' と書いてあげれば OK で、もしくは in という演算子も使うことができて、 in としたあとに () の中の値のどれかだよ、というのは , 区切りで指定してあげればいいので、どちらもこの場合は同じ意味になります。

大文字の T から始まるレコードだけ抽出したい場合は、 where like の後ろにbinary 演算子を使えば厳密に比較をしてくれます
ワイルドカードにはもう1つあって、 _ を使えば任意の一文字を表現することができます。
name が 6 文字の場合は '______' としてあげれば OK
もしくは、名前の2文字目がaの人だけを抽出したかったら '_a%'

…limit 3 offset 3; とすると、最初の3名を除外して次の3件を表示

四捨五入する桁の位置を指定することもできて、例えば小数点 1 桁目で丸めたい場合は select round(5.358, 1); のようにしてあげると、こちらは 5.4 になる

rand() を使うと 0 以上 1 未満のランダムな数値を返してくれます。
例えば select * from users order by rand() limit 1; で、乱数で並び替えてあげて、そのうち 1 つだけ取り出すとしてあげましょう
そうすると、ランダムな値をもとに並び替えてくれて、一番上のレコードだけ抽出してくれるので、抽選で 1 名選ぶときなどの処理に使えます。

文字列の演算

例えば select length('Hello'); としてあげると、今回は 5 文字が返ってくる もしくは文字列の一部だけを取り出したい場合は substr という命令が使えます。 select substr('Hello', 2); とすると 2 文字目以降を抽出してくれます もしくは … substr('Hello', 2, 3); とすると 2 文字目以降の 3 文字を抽出してくれるので、それぞれ ello 、 ell のようになるはずです。

文字列を全て大文字にするには upper 、全て小文字にするには lower を使ってあげればいい

文字列の連結については concat という命令を使ってあげてカンマ区切りで連結したい文字列をいくつでも並べてあげればOK

複数の文字列の中から 1 つだけを格納できる enum というデータ型
例えば rank というフィールドを作ってあげて enum('gold', 'silver', 'bronze') としてあげると、この中のどれかの値のみを格納できるようになります。
enum の設定にすることによって MySQL の設定にもよりますが、この中にある値以外をはじくことができます
enum を使うと有効なデータ以外は無効なデータにすることができる
内部的には数値が割り当てられていて、1, 2, 3, といった具合で値を保持しています

複数選びたい場合には set という型が使えます。
例えばtaguchi さんが gold と silver を持っているなら … ('taguchi', 5.8, 'gold,silver'); のようにカンマ区切りで複数書いてあげれば OK
なお、挿入時に順番を変えたとしても、こちらの create table で定義した順番に戻っている
したがって、抽出時にはこちらの順番を守りつつ条件を指定してあげる必要があります。
set に関しても、内部的には数値が割り当てられていて、最初の値には 2 の 0 乗の 1 、次が 2 の 1 乗の 2 、次が 2 の 2 乗の 4 、といった具合で値を保持しています
したがって、 1 番目と 2 番目が含まれるレコードを指定したい場合は、 1 + 2 で … coins = 3 としてあげると、 gold と silver を持つユーザーを抽出することができる

create table と select をうまく使うと、テーブルのコピーを簡単に行うことができたりします。
users のコピーで users_copy というテーブルを作りたかった場合は、 create table users_copy で、 as は省略できるので select * from users; としてあげれば、まるっとテーブルをコピーして新しいテーブルを作ってくれます。

テーブルの構造だけコピーしてデータはいらない、という場合は like が使えます。

<21〜30>

抽出条件に名前を付けてテーブルのように扱うことができる view について見ておきましょう なお、あくまで view は抽出条件であって値のコピーをして新しいテーブルを作るわけではないので、おおもとになる users テーブルの値が変われば結果も当然変わります。 view はテーブルと同じ扱いになるので、一覧を見るには show tables としてあげればテーブルと同列で view も表示してくれるはずです。 どうやってそのviewが作られたかを見るには、show create view top3; としてあげればOK 複雑な抽出条件を何度も使う場合に view は便利

トランザクション

例えば、fkoji さんから taguchi さんに 1.2 点 score を渡したかったと考えてみます
updateを使ってもよいが、2つの処理の間に何らかの処理が割り込んできたり、途中で止まったりしたら score の整合性がとれなくなってしまいます。
transaction を使って、まとめて行いたい処理を始める前に start transaction; 、そして、まとめて行いたい処理が終わった後に commit; としてあげれば、 start transaction; から commit; までの処理は必ずまとめて行われるという保証がされます。
なお transaction 中に、処理の影響で何らかの不具合が起きた場合、 transaction を終了して変更内容を破棄することもできます。
その場合は commit; ではなくて rollback; としてあげれば、今までの変更を破棄することができる

索引

索引の設定をしておくとデータの抽出が速くなる
ちなみに primary key には必ず索引が作られます。
索引の付け方にはいくつかの方法があるが、今回は alter table
索引を貼るには add index としてあげて、索引の名前を付けてあげます。
何でもいいのですが、今回は score に索引を設定しているので index_score
そのあと () の中に索引を設定したいカラムを書いてあげます。
そのテーブルにどういった索引が貼られているかは show index from users; で調べることができます
ちゃんとその索引が使われているかどうかは explain という命令で調べることができます

ただし、なんでもかんでも索引を付ければいいわけではなくて、索引はデータの追加や更新処理を行うたびに作り直されるので、今度はそちらの処理が遅くなってしまうというデメリットがあるので、よく考えて必要なだけ索引を付ける

索引の削除ですが、 drop index としてあげれば OK 索引の名前を付けてあげればいいので、 alter table users drop index index_score で OK

自分が作っているアプリケーションの抽出処理が遅いな、と思ったら索引を付けたり外したりして、パフォーマンスを最適化できるようになっておくといいでしょう

データの紐付け

内部結合の inner join で、もう一つが外部結合の outer join になります これらの違いですが、 inner join は 2 つのテーブルに共通のデータだけを取得する方法、 outer join は 2 つのテーブルで一致しないデータも含めてデータを取得する方法になります innerは省略できる

外部結合はどちらのテーブルを軸にするかで構文が異なってきます
outerも省略できるので、left join か　right join

外部キーの制約

今回 comments テーブルに対して外部キー制約を設定してあげます。 いろいろな方法があるのですが、 alter table を使ってあげましょう まずは add constraint fk_comments として制約をつけてあげて制約の名前を設定してあげます。 その後に foreign key (post_id) references posts(id) としてあげます。 ただし、紐付けるカラムの型が一致していないといけないので、 今回は int unsigned で統一してあげましょう。 外部キー制約を設定してしまうと、関連するデータがある場合にはデータの削除や更新が簡単にはできなくなります たとえばここで delete from posts where id = 1; としてあげると、 post の 1 に関しては関連するコメントがあるので、このように外部キー制約のエラーになっているのがわかるかと思います。 このように、データの整合性を取りたい場合には便利な仕組み 外部キー制約を削除するには alter table を使ってあげて、 alter table comments drop foreign key fk_comments; として外部キー制約の名前をつけてあげれば OK です。

<31〜36>

MySQL では直前に挿入されたレコードの id を調べる命令が用意されています
last_insert_id() という命令なので、 insert into comments (post_id, body) values (last_insert_id(), 'comment for latest post'); と書いてあげると、直前に挿入されたレコードの id を引っ張ってきて入れてくれます。
関連した複数のテーブルにデータを挿入する場合は last_insert_id() をよく使う

Trigger

あるテーブルでなんらかの変更が起きたときに、それをトリガーにして何らかの処理をすることができる Trigger という仕組みについてみていきましょう 今回、 posts テーブルにデータが挿入されたら他のテーブルにログが残るようにしてあげましょう。 logs テーブルを作ってあげて、 msg varchar(255) というフィールドを設定してあげます Trigger の作り方なのですが、 create trigger posts_insert_trigger .... としてあげて Trigger の名前をつけてあげれば OK です その後にafter insert on posts for each row insert into logs (msg) values ('post added!'); のように書いていきます Trigger なのですが、 insert だけに使えるというわけではなくて、 delete や update にも使えますし、 after ではなく before にすることで、処理前に Trigger の処理を実行することができます。 フィールドを縦並びにして表示する方法は\G

\Gは他のコマンドにも使えるので覚えておくと良い

複雑なTrigger

複雑なTriggerの場合は begin end でまずは囲ってあげてください。 ここで注意が必要で、 SQL の区切り文字は ; なので、 create trigger が values ('post updated!'); で終わってしまいます そこで、一時的に区切り文字を別のものにして create trigger 内で複数の文が書けるようにしましょう なんでもいいのですが、区切り文字を変えるには delimiter としてあげて…、今回は // にしてあげましょう。 では create trigger の最後はこちらなので、 // としてあげます。 続く文に関しては delimiter は元に戻しておきたいので、//の次の行でdelimiter ; としてあげましょう。

日時

作成日時と更新日時を管理したかったら、created datetime 、 updated datetime で管理すれば、日時に関するデータを入れることができます。 デフォルトでレコード挿入時にそのときの日時を入れるには、default current_timestamp updated に関しては、レコードの更新をしたときにそのときの日時が入ってくれるとさらに便利。 current_timestampの後に、on update current_timestamp とすれば、更新時にそのときの日時でこのカラムを更新してくれます。

例えば created と created に 14 日間足した日時が知りたいときは select created, date_add(created, interval 14 day) from posts; と書いてあげれば OK

他にも date_format という命令を使えば、好きな書式で表示することもできます
例えば、 select created, date_format(created, '%W %M %Y') from posts; としてあげましょう。
%W などの記号については公式マニュアルを参照してもらいたいのですが,このように曜日や月日が表現できている.

データベースの内容をファイルに書き出す

いくつかの方法がありますが、一番手軽なのは mysqldump を使う方法 いったん、 mysql から抜けてあげて、 mysqldump コマンドを使ってあげましょう mysqldump -u dbuser -p myapp > 20170722_myapp.backup.sql としてユーザー名とデータベース名を指定してあげて、リダイレクションを使ってあげて…、例えばこのようにファイル名をつけてあげましょう

こちらのファイルですが、バックアップとして取っておいて、もし何かあったときにこのデータを使って復元してあげればいいでしょう
バックアップの SQL を読み込むには、今まで通り . コマンドを使ってあげればいいので、バックアップしたファイルを . ./20170722_myapp.backup.sql のように読み込んであげましょう。