はじめに
前回までのまとめは以下に記載している。
MySQLについて改めて調べてみた
今回も引き続き、読んだ本についてまとめていく。
対象者
・データベースわからない！っていう初心者
・何と無くデータベース触ってきたけど、よくわかってない！って人
少しでも役に立てれば幸いである。
読んだ本
全体の章構成（3章編）
条件抽出
データ編集
複数テーブルを使う
ビューを使う
ストアドプロシージャを使う
トランザクションを使う
ファイルを使ったやり取り
条件抽出
以下に例として挙げられていた構文を記す
カラムの表示を工夫する
----- 構文 -----
> SELECT カラム1,カラム2 FROM テーブル名;
----- 例 -----
> SELECT test,date FROM tb;
----- 説明 -----
testという列とdateという列の順でテーブルを表示する
エイリアスを使う
----- 構文 -----
> SELECT カラム名 AS エイリアス FROM テーブル名;
----- 例 -----
> SELECT test AS 期末試験,date AS 日付 FROM tb;
----- 説明 -----
エイリアスとはあだ名のことで、列の名前を自分の好きな名前に変更することができる
例文では、testという行を期末試験という名前に変更し、dateという列を日付という名前に変更する
カラムの値を計算して表示する
----- 構文 -----
> SELECT カラム1*100 as カラム名 FROM テーブル名;
----- 例 -----
> SELECT price*100 as 売り上げ FROM tb;
----- 説明 -----
priceという列に書いてある値全てに100をかけて、「売り上げ」の列に計算結果を書く
関数を使って計算する
----- 構文 -----
> SELECT AVG(引数) FROM テーブル名;
----- 例 -----
> SELECT AVG(price) FROM tb;
----- 説明 -----
関数＝データを与えると決められた処理をしてその結果を返す
AVG＝平均を返す関数
（）に入れるのは引数が入る。
例文の意味は、priceという列の平均を出すということ
version確認
----- 構文 -----
> SELECT VERSION();
現在使っているデータベース確認
----- 構文 -----
> SELECT DATABASE();
現在使っているユーザ確認
----- 構文 -----
> SELECT USER();
文字列をつなげる
----- 構文 -----
> SELECT CONCAT(値1,値2,'文字列') FROM テーブル名;
----- 例 -----
> SELECT CONCAT(num,name,'さん') FROM tb1;
----- 説明 -----
num列とname列に記載されている２列と文字列の[さん]を繋げて表示させるという意味
今回の例で言うと、num列に01、name列に佐藤と入っていた場合、[01佐藤さん]と表示させることが出来る
※文字列は''で囲うことを忘れないようにする
表示するレコードを限定する（LIMIT編）
----- 構文 -----
> SELECT カラム名 FROM テーブル名 LIMIT 表示するレコード数;
----- 例 -----
> SELECT * FROM tb LIMIT 3;
----- 説明 -----
tbという名前のテーブルの全ての列の中の3行だけ表示される
LIMITで表示するのは早いので、数行だけ欲しい時に使うとよいらしい
表示するレコードを限定する（WHERE編）
----- 構文 -----
> SELECT カラム名 FROM テーブル名 WHERE 条件;
----- 例 -----
> SELECT * FROM tb WHERE price>=100;
----- 説明 -----
tbという名前のテーブルのprice列に記載されている数値の１００以上のものだけ表示するという意味
例えば、売り上げで１００以上のものは表示させるなどということが可能
条件を使う時、覚えておいた方がいいのは「比較演算子」と言う概念である。詳しくはググってみること
表示するレコードをあいまい検索する
----- 構文 -----
> SELECT カラム名 FROM テーブル名 WHERE カラム名 LIKE 条件;
----- 例 -----
> SELECT * FROM tb WHERE num LIKE '101';
----- 説明 -----
tbという名前のテーブルのnum列にある101がついているものを表示する。
例えば、名簿があったとして、その中から「山」がつく人だけ表示させたい場合は以下のようになる
> SELECT * FROM tb1 WHERE name LIKE '山'
NULLの行は表示しない
----- 構文 -----
> SELECT カラム名 FROM テーブル名 WHERE カラム名 IS NOT NULL;
----- 例 -----
> SELECT * FROM tb1 WHERE num IS NOT NULL;
----- 説明 -----
tbという名前のテーブルのnum列にある中で、何も入っていないものは表示させないで表示する。
複数条件を指定する場合（AND編）
----- 構文 -----
> SELECT カラム名 FROM テーブル名 WHERE 条件 AND 条件;
----- 例 -----
> SELECT * FROM tb1 WHERE price>=50 AND price>=100;
----- 説明 -----
tb1のテーブルにて、priceが５０以上で且つ１００以上のものを表示するという意味
複数条件を指定する場合（OR編）
----- 構文 -----
> SELECT カラム名 FROM テーブル名 WHERE 条件 OR 条件;
----- 例 -----
> SELECT * FROM tb1 WHERE price>=50 OR price>=100;
----- 説明 -----
tb1のテーブルにて、priceが５０以上または１００以上のものを表示するという意味
※ANDとORは一度に両方使うことも可能だが、ANDの方が優先して処理されるので、もしORを先に処理して欲しい場合は（）を使う
条件によって値を変えて表示させる場合
----- 構文 -----
> SELECT
CASE
WHEN 条件 THEN 表示させる値
WHEN 条件 THEN 表示させる値
END
FROM テーブル名;
----- 例 -----
> SELECT
CASE
WHEN price>=100 THEN '多い'
WHEN price>=50 THEN '中くらい'
END
FROM tb1;
----- 説明 -----
tb1のテーブルにて、priceが1００以上のものは「多い」と表示させ、50以上のものは「中くらい」と表示させるという意味
昇順に並び替える
----- 構文 -----
> SELECT カラム名 FROM テーブル名 ORDER BY キーとなるカラム;
----- 例 -----
> SELECT * FROM tb1 ORDER BY price;
----- 説明 -----
tb1のテーブルにて、price列を基準に昇順に並び替えるという意味
降順に並び替える
----- 構文 -----
> SELECT カラム名 FROM テーブル名 ORDER BY キーとなるカラム DESC LIMIT 値;
----- 例 -----
> SELECT * FROM tb1 ORDER BY price DESC LIMIT 5;
----- 説明 -----
tb1のテーブルにて、price列の値の多いものから（降順の為）5行表示させるという意味
降順を表す表記は「DESC」で、表示させたい場合は「ORDER BY」の後ろに付けるのが望ましい
表示するレコード範囲を決める場合
----- 構文 -----
> SELECT カラム名 FROM テーブル名 LIMIT 表示するレコード数 OFFSET 表示開始レコードのシフト数;
----- 例 -----
> SELECT * FROM tb1 ORDER BY price DESC LIMIT 2 OFFSET 3;
----- 説明 -----
tb1のテーブルにて、price列を降順に2つ表示する。ただし降順の３番目から2つ表示するという意味
グループごとに表示する
----- 構文 -----
> SELECT カラム名 FROM テーブル名 GROUP BY グループ化するカラム名;
----- 例 -----
> SELECT * FROM tb1 GROUP BY num;
----- 説明 -----
tb1のテーブルにて、num列でグループ化させたものを表示するという意味
グループごとに処理する
----- 構文 -----
> SELECT 集計したカラム FROM テーブル名 GROUP BY グループ化するカラム HAVING 条件;
----- 例 -----
> SELECT num,SUM(price) FROM tb1 GROUP BY num HAVING SUM(price)>=200;
----- 説明 -----
tb1のテーブルにて、priceの合計をnum（番号のこと）ごとに処理するが、表示するのは合計が２００以上のみ表示するという意味
グループ化で覚えておくことは、2つある。
1つは、グループ化の種類について。以下のような2種類が存在する。
①抽出してからグループに分ける方法
②グループごとに分けてから抽出する方法
もう1つは、抽出、並び替え、グループ化には順序があり、それは以下の順に実施すること。
①WHERE 条件
②GROUP BY xx
③ORDER BY xx (DESC)
以上が、3章の「条件抽出」にあたる部分だった。（これだけで結構おなかいっぱい。。）
データ編集
続いて、データを編集する際のコマンド例が記載されている。
カラムのデータを全て修正する
----- 構文 -----
> SELECT テーブル名 SET カラム名=設定する値;
----- 例 -----
> SELECT tb1 SET memo='特になし';
----- 説明 -----
tb1のテーブルにて、memo列を作り「特になし」と記述された１列を加えるという意味
条件に一致したレコードだけ修正
----- 構文 -----
> UPDATE テーブル名 SET カラム名=設定する値 WHERE 条件;
----- 例 -----
> UPDATE tb1 SET memo='優秀' WHERE price>=100;
----- 説明 -----
tb1のテーブルにて、price列の値が１００条の場合、memo列に「優秀」と入れるという意味
特定のレコードだけコピーする
----- 構文 -----
> CREATE TABLE 新規テーブル名 SERECT * FROM 元となるテーブル名 WHERE 条件;
----- 例 -----
> CREATE TABLE tb_test SERECT * FROM tb WHERE num LIKE A01;
----- 説明 -----
tbというテーブルを元に、tb_testという新規テーブルを作成し、num列のA01だけコピーするという意味
全レコードを削除する
----- 構文 -----
> DELETE FROM テーブル名 WHERE 条件;
----- 例 -----
> DELETE FROM tb1 WHERE old<30;
----- 説明 -----
tb1のテーブルにて、カラム「old」の値が３０未満のレコードが削除され、３０以上のものは残るという意味
順番に並べてから削除する
----- 構文 -----
> DELETE FROM テーブル名 ORDER BY カラム名 DESC LIMIT 設定値;
----- 例 -----
> DELETE FROM tb1 ORDER BY price DESC LIMIT 4;
----- 説明 -----
tb1のテーブルにて、price列の降順に並べ、上位4つだけ削除するという意味
以上が、データベースにおけるデータ編集の内容だった。
複数テーブルを使う
データ編集が出来るようになったら、次にテーブル単体の扱い方に慣れる必要がある。この節では、複数テーブルの扱い方について記載されていた。
2つのテーブルのレコードを集めて表示する場合
----- 構文 -----
> SERECT カラム名1 FROM テーブル名1 UNION SELECT カラム名2 FROM テーブル名2;
----- 例 -----
> SERECT * FROM tb1 UNION SELECT * FROM tb2;
----- 説明 -----
tb1とtb2のテーブルを1つにして表示するという意味
2つのテーブルを結合する場合
----- 構文 -----
> SELECT カラム名
FROM テーブル1
JOIN 結合するテーブル２
ON テーブル１のカラム＝テーブル２.カラム名;
----- 例 -----
> SELECT *
FROM tb
JOIN tb1
ON tb1＝tb1.num;
----- 説明 -----
tbとtb1のテーブルにてnum列が一致するレコードを結合して表示するという意味
テーブル名にエイリアスを使う場合
----- 構文 -----
> SELECT カラム名 FROM テーブル名 AS エイリアス
----- 例 -----
> SELECT * FROM tb AS x;
----- 説明 -----
tbのテーブルにて、xというエイリアスをつけたという意味
外部結合をしたい場合（左外部結合編）
----- 構文 -----
> SERECT カラム名
FROM テーブル1
LEFT JOIN 結合するテーブル2
ON テーブル1のカラム＝テーブル2のカラム;
----- 例 -----
> SERECT tb.num,tb1.num
FROM tb
LEFT JOIN tb2
USING(num);
----- 説明 -----
tbテーブルのnum列とtb2テーブルのnum列で一致したレコードを左外部結合する。
そしてnum列を表示するという意味
外部結合をしたい場合（右外部結合編）
----- 構文 -----
> SERECT カラム名
FROM テーブル1
RIGHT JOIN 結合するテーブル2
ON テーブル1のカラム＝テーブル2のカラム;
----- 例 -----
> SERECT tb.num,tb1.num
FROM tb
RIGHT JOIN tb1
USING(num);
----- 説明 -----
tbテーブルとtb2テーブルで一致したレコード、および右側に該当するテーブルである「tb1」の全てが表示されるという意味
自己結合する場合
----- 構文 -----
> SELECT カラム名 FROM テーブル名 AS エイリアス1 JOIN テーブル名2 AS エイリアス2;
----- 例 -----
> SELECT * FROM tb1 AS a JOIN tb2 AS b;
----- 説明 -----
自己結合とは、自分と同じ名前のテーブルを結合することが出来るということ。
例文では、tb1テーブルを結合するという意味
サブクエリを実行する場合
----- 構文 -----
> SELECT 表示するカラム FROM テーブル名 WHERE カラム名 IN (SELECTによるサブクエリでカラムを抽出);
----- 例 -----
> SELECT * FROM tb1 WHERE num IN (SELECT num FROM tb WHERE price>=200);
----- 説明 -----
サブクエリとは、「副問い合わせ」と言い、クエリを実行して取り出されたデータを使って、さらにクエリを発行することになるということ。
例として、年齢３０歳以上で且つ社員番号が２００以上の人を検索したい時、初めに年齢30歳以上の人をクエリで抽出し、２回目に社員番号２００以上の人を抽出します。
ここでいう年齢３０歳以上の人を抽出することを「サブクエリ」と呼ぶ。
例文では、IN以降の（）内に1段階目のクエリを入れて抽出を行い、（）内での処理を前提に２回目の抽出を行うようなサブクエリ文になっている。
訳すると、tbテーブルにおいてprice列が２００以上のnum列を抽出し、さらにtb1テーブルにて該当レコードを表示するという意味になる。
ここまで、DBにおけるデータの扱いやテーブルの扱いをみてきた。いよいよ、よく応用情報などでも耳にする言葉が出てくる節に移ってくる。
ビューを使う
ビューとは何か？
SELECTした結果をテーブルのように見せているのが「ビュー」と呼ばれるものの正体
一言で本文から抜粋すると、上記のようになる。
ビューはテーブルではなく、データそのものは存在しないとのこと。あくまでデータを取り出す為の情報となる。
また、ビューとは、「元になるテーブルの一部を表示している」為、仕組みとして元のテーブルが更新されると、ビューも更新される。
その逆に、ビューを更新すると元のテーブルも更新されてしまう、という事態が生じるので注意が必要。
ビューの定義の仕方
「ビューを作る」というよりは実体がないので「定義する」という言い方が望ましいらしい
ビューの定義
----- 構文 -----
> CREATE VIEW ビューの名前 AS SELECT カラム名 FROM テーブル名 WHERE 条件;
----- 例 -----
> CREATE VIEW v1 AS SELECT name,old FROM tb1;
----- 説明 -----
tb1テーブルにて、name,old列をv1という名前のビューとして定義するという意味
※例文ではWHEREによる条件指定なし
ビューの存在を確認する
----- 構文 -----
> SHOW TABLES;
ビューのカラム構造を確認する
----- 構文 -----
> DESC ビュー名;
----- 例 -----
> DESC v1;
ビューの詳細を確認する
----- 構文 -----
> SHOW CREATE VIEW ビュー名;
----- 例 -----
> SHOW CREATE VIEW v1;
----- 説明 -----
v1の詳細を確認するという意味
ビューの条件に合わない場合、エラーを返すようにする
----- 構文 -----
> CREATE VIEW ビュー名 AS SLECT カラム名 FROM テーブル名 WHERE 条件 WITH CHECK OPTION;
----- 例 -----
> CREATE VIEW v11 AS SLECT num,price FROM tb1 WHERE price>100 WITH CHECK OPTION;
----- 説明 -----
ビューから行をINSERTすることは出来るが、条件に合わない場合はデータが入らないようにしたい。
そういう時は「WITH CEHCK OPTION」を付けてビューを作成すると良い。
例文では、tb1テーブルにおいてprice列の１００以上のnum列とprice列をv11としてビューを定義する意味となり、このコマンドを実行後
INSERTによる行の追加をしても、エラーが返ってくるようになる。
ビューの上書き
----- 構文 -----
> SELECT OR REPLACE VIEW ビュー名 AS SELECT 新しいビュー名;
----- 例 -----
> SELECT OR REPLACE VIEW v1 AS SELECT NOW;
----- 説明 -----
v1というビューをNOWという新しい名前のビューに上書きするという意味
ビューのカラム構造を変更する場合
----- 構文 -----
> ALTER VIEW ビュー名 AS SELECT カラム名 FROM テーブル名;
----- 例 -----
> ALTER VIEW v1 AS SELECT name,old FROM tb1;
----- 説明 -----
ビュー定義を変更する場合、「ALTER VIEW」を使用する
例文では、v1（ビュー）の中にあるtb1というテーブル内のname,old列の構成を変えることができるという意味
ビューを削除する場合
----- 構文 -----
> DROP VIEW ビューの名前;
----- 例 -----
> DROP VIEW v1;
ストアドプロシージャを使う
ストアドプロシージャとは何か？
いくつものSQL文を1つにまとめ、それを「CALL xx」コマンドだけで実行できるようにしたもののこと。
プロシージャをつくる
----- 構文 -----
> CREATE PROCEDURE ストアドプロシージャ名()
BEGIN
SQL文①
SQL文②
END
----- 例 -----
> delimiter //
CREATE PROCEDURE ストアドプロシージャ名()
BEGIN
SELECT * FROM tb;
SELECT * FROM tb1;
END
//
delimiter ;
----- 説明 -----
SQL文の最後は必ず「;」で終わる決まりだが、プロシージャを作る時、いくつかのSQL文を選択してつくるとなると、エラーになってしまう。
そこで、「;」を「//」に一時的に変更させて、プロシージャを作る。
よって、例文では初めに「;」の変更を宣言する「delimiter //」という文が挿入されている。
ストアドプロシージャの実行
----- 構文 -----
> CALL ストアドプロシージャ名;
----- 例 -----
> CALL pr1;
ストアドプロシージャの表示
----- 構文 -----
> SHOW CREATE PROCEDURE ストアドプロシージャ名;
----- 例 -----
> SHOW CREATE PROCEDURE pr1;
ストアドプロシージャの削除
----- 構文 -----
> DROP PROCEDURE ストアドプロシージャ名;
----- 例 -----
> DROP PROCEDURE pr1;
ストアドファンクションとは何か？
ストアドファンクションも、ほとんどストアドプロシージャと同類のものと記述があり、唯一ストアドプロシージャと異なるのは
実行した時に値を返す点だと述べている。
ストアドファンクションを使えば、オリジナルの関数を作ることが出来る。
また、ストアドファンクションは別名「ユーザー定義関数」とも呼ばれるらしい。
ストアドファンクションをつくる
----- 構文 -----
> CREATE FUNCTION ストアドファンクション名(引数 データ型) RETURNS 返す値のデータ型
BEGIN
SQL①
RETURN 返す値・式
END
----- 例 -----
> delimiter //
CREATE FUNCTION fu1(sincho INT) RETURNS DOUBLE
BEGIN
RETURN sincho * sincho *22/10000;
END
//
delimiter ;
----- 説明 -----
例文の意味
sinchoという引数名で整数を指定し、fu1という名前のファンクションを作成します。
このファンクションは「RETURN DOUBLE」と記載がありますが、これは「DOUBLE型」と呼ばれる型を返す値として定義しており、小数点以下も扱うとしている。
BEGINから始まる文では、返す値を記載しており、sincho×sincho×22÷10000の値をRETURNで返しますという意味
ストアドファンクションの内容確認
----- 構文 -----
> SHOW CREATE FUNCTION ストアドファンクション名;
ストアドファンクションの削除
----- 構文 -----
> DROP FUNCTION ストアドファンクション名;
トランザクションを使う
トランザクションとは？
複数の処理を一つにまとめた手続きとして扱うことを「トランザクション」と言う。
ストレージエンジンについて知った上で、トラザクションについて考えるのが良いと本文にはある。
ストレージエンジンとは？
そもそも、MySQLは大きく分けて2つの機能があり、上と下に分けられる。
①接続したり、SQL文の内容を事前に調査するという機能＝「上」の部分
②実際の検索やファイル操作の作業を行う機能＝「下」の部分
この「下」の部分にあたるのが「ストレージエンジン」とのこと。
ストレージエンジンの種類
MySQLでは、このストレージエンジンをユーザーが自由に決められる。
さらに、テーブルごとにAテーブルは○○ストレージエンジン、Bテーブルは××ストレージエンジンといったように、選べる。（すごい）
MySQLのストレージエンジンの種類は以下に記載されていたので参照すると良い
デフォルトストレージエンジンに関する設定を記述する
ストレージエンジンを変更したい！
実際にストレージエンジンを変更する場合の手順は以下の通りである。
ストレージエンジンを変更する場合
----- 構文 -----
> ALTER TABLE テーブル名 ENGINE=エンジン名;
----- 例 -----
> ALTER TABLE tb ENGINE=MyISAM;
----- 説明 -----
tbテーブルのストレージエンジンをMyISAMに変更するという意味
トランザクションを開始したい！
開始する場合は、宣言する必要がある。
また、以下コマンドを実行した後、必ずQuery OKと表示されていることを確認するようにと記載があった。
トランザクションを開始する
----- 構文 -----
> START TRANSACTION;
Query OK, 10 rows affected (0.00 sec)
ファイルを使ったやり取り
SQL文を使ってカラムを全て形成するのは大変なので、ファイルをインポート・エクスポートしてデータベースにレコードを挿入していくことをよく実行する。
この節では、そういったファイルを使った操作方法についてまとめてあった。
インポート/エクスポート
ファイルをインポートする
----- 構文 -----
> LOAD DATA INFILE 'ファイル名' INTO TABLE テーブル名 オプションの記述;
----- 例 -----
> LOAD DATA INFILE 'c:/data/test.csv' INTO TABLE tb1 FIELDS TERMINATED BY ',';
----- 説明 -----
tb1テーブルに[,]で区切られたやり方でtest.csvファイルをインポートするという意味
ファイルをエクスポートする
----- 構文 -----
> SERECT カラム名 INTO OUTFILE 'ファイル名' オプションの記述 FROMO テーブル名;
----- 例 -----
> SERECT * INTO OUTFILE 'C:/data/test_out.csv' FIELDS TERMINATED BY ',' FROM tb1;
----- 説明 -----
tb1テーブルの内容がC:/data/test_out.csvにファイルとしてエクスポートされるという意味
SQLコマンドをファイルから読み込んで実行する場合
----- 構文 -----
> SOURCE テキストファイル名;
----- 例 -----
> SOURCE C:/data/sql_memo.txt;
----- 説明 -----
メモ帳内にSQL文を予め書いておき、SOURCEコマンドを用いて中身を実行するという意味
ファイルに書かれたSQLコマンドをコマンドプロンプトから実行する場合
----- 構文 -----
> mysql データベース名 -u ユーザー名 -p パスワード名 -e "MySQLモニタのコマンド";
----- 例 -----
> mysql (db1) -u root -p root -e "SOURCE C:/data/sql.txt";
----- 説明 -----
()内でくくったデータベース名は、実際に実行する際省略ができる。
例文の意味としては、sql.txt内に書かれているコマンドをコマンドプロンプトから実行するという意味
SQLの実行結果→ファイルに保存
teeコマンドを用いて結果をファイルに保存する
----- 構文 -----
> tee 出力するファイル名;
----- 例 -----
> tee log.txt;
----- 説明 -----
MySQLモニタ上で実行すると、log.txtが作成され、以降叩いたコマンドを全部記録してくれる。
記録を止める時は、以下コマンドを用いる。
> notee
バックアップ/リストア
データベースをダンプする
----- 構文 -----
> mysqldump -u ユーザー名 -p パスワード データベース名 > 出力ファイル名
----- 例 -----
> mysqldump -u root -p root db1>db1_out.txt
----- 説明 -----
db1の内容を元にdb1_out.txtという名前のファイルでダンプする（書き出す）
まとめ
mysqlの基礎から、実際に使う主なコマンド例文がこの章に記載されていた。
じっくり読みながらとなると、かなりのボリュームなので、知らない知見だけつまみ食いするのが良い気がした。