MySQLにおける初歩的なDB操作

◆データベースを作成
（データベースの名前はphplesson）

mysql> CREATE DATABASE phplesson;
Query OK, 1 row affected (0.00 sec)

◆テーブルを作成
（操作を実行するデータベースをUSEで指定してから行う）

mysql> USE phplesson;
Database changed
mysql> CREATE TABLE address_t (
    -> number INT,
    -> name VARCHAR(50),
    -> birthday DATE,
    -> address VARCHAR(255),
    -> freespace TEXT);
Query OK, 0 rows affected (0.00 sec)

◆作成したテーブル情報を確認

mysql> DESC address_t;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| number    | int(11)      | YES  |     | NULL    |       |
| name      | varchar(50)  | YES  |     | NULL    |       |
| birthday  | date         | YES  |     | NULL    |       |
| address   | varchar(255) | YES  |     | NULL    |       |
| freespace | text         | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

◆主キー（プライマリキー）を設定

主キーは、テーブル内のフィールドを一意（ユニーク）にするためのもの。値がNULLではいけない。また、重複した値は登録できなくなる。

カラム「number」に主キーを設定

mysql> ALTER TABLE address_t ADD PRIMARY KEY(number);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

どうなったか確認してみる

mysql> DESC address_t;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| number    | int(11)      | NO   | PRI | 0       |       |
| name      | varchar(50)  | YES  |     | NULL    |       |
| birthday  | date         | YES  |     | NULL    |       |
| address   | varchar(255) | YES  |     | NULL    |       |
| freespace | text         | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

numberのKeyの属性がPRIになっている。（主キーに設定された）

◆新しいカラムを追加

address_tテーブルに新しく「mail」という名前のカラムを追加

mysql> ALTER TABLE address_t ADD mail VARCHAR(128);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

確認してみる

mysql> DESC address_t;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| number    | int(11)      | NO   | PRI | 0       |       |
| name      | varchar(50)  | YES  |     | NULL    |       |
| birthday  | date         | YES  |     | NULL    |       |
| address   | varchar(255) | YES  |     | NULL    |       |
| freespace | text         | YES  |     | NULL    |       |
| mail      | varchar(128) | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

◆作成済みのカラムのカラム名とデータ型を変更

カラム「freespace」の名前と、このカラムのデータ型を変更してみる
（BLOBは可変長で長いバイナリテキスト。文字数でなくバイト数でカウント）

mysql> ALTER TABLE address_t CHANGE freespace explanation BLOB;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

確認してみる

mysql> DESC address_t;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| number      | int(11)      | NO   | PRI | 0       |       |
| name        | varchar(50)  | YES  |     | NULL    |       |
| birthday    | date         | YES  |     | NULL    |       |
| address     | varchar(255) | YES  |     | NULL    |       |
| explanation | blob         | YES  |     | NULL    |       |
| mail        | varchar(128) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

◆データの挿入

文字列の値は引用符で囲うことが必要。数値型の値はそのままで大丈夫。でも、つけ忘れを防ぐためにも、データの値を追加するときは文字でも数値でも引用符「’data’」で囲うようにしておいたほうがいい。

mysql> insert into address_t values ('1','suzuki','1980-01-01','TokyoNakanoku','setsumei','suzuki@hogehoge.hoge');
Query OK, 1 row affected (0.02 sec)

確認してみる

mysql> select * from address_t;
+--------+--------+------------+---------------+-------------+----------------------+
| number | name   | birthday   | address       | explanation | mail                 |
+--------+--------+------------+---------------+-------------+----------------------+
|      1 | suzuki | 1980-01-01 | TokyoNakanoku | setsumei    | suzuki@hogehoge.hoge |
+--------+--------+------------+---------------+-------------+----------------------+
1 row in set (0.00 sec)

◆データの更新

numberが1のsuzukiさんのaddressをTokyoNakanokuからTokyoSuginamikuに変更してみる。

mysql> update address_t set address='TokyoSuginamiku' where number='1';

このクエリの意味は、address_tテーブル内のnumberカラムの値が1のフィールドにおいて、addressの値をTokyoSuginamikuに変更するということ。
（whereで変更する対象を指定しないと、全てのフィールドが変更されてしまうので注意）

データが更新できたか確認してみる。

mysql> select * from address_t;
+--------+--------+------------+-----------------+-------------+----------------------+
| number | name   | birthday   | address         | explanation | mail                 |
+--------+--------+------------+-----------------+-------------+----------------------+
|      1 | suzuki | 1980-01-01 | TokyoSuginamiku | setsumei    | suzuki@hogehoge.hoge |
+--------+--------+------------+-----------------+-------------+----------------------+
3 rows in set (0.00 sec)

◆テーブル内のデータを削除

mysql> delete from address_t where number='1';

このクエリの意味は、address_tテーブル内のnumberカラムの値が1のフィールドの全データを削除するということ。
これも、where句を使って範囲指定をしないと全てのフィールドが削除対象になるので注意。

確認すると
（2と3は後から追加した）

mysql> select * from address_t;
+--------+-------+------------+----------------+-------------+---------------------+
| number | name  | birthday   | address        | explanation | mail                |
+--------+-------+------------+----------------+-------------+---------------------+
|      2 | saito | 1978-01-01 | TokyoShibuyaku | setsumei    | saito@hogehoge.hoge |
|      3 | ito   | 1979-01-01 | TokyoToshimaku | setsumei    | ito@hogehoge.hoge   |
+--------+-------+------------+----------------+-------------+---------------------+
2 rows in set (0.00 sec)

deleteしたフィールドのデータが削除されている。

◆テーブル内のデータを検索する

address_tテーブル内のnumberカラムの値が1のフィールドデータ全てを取得してみる。

mysql> select * from address_t where number='1';
+--------+--------+------------+----------------+-------------+----------------------+
| number | name   | birthday   | address        | explanation | mail                 |
+--------+--------+------------+----------------+-------------+----------------------+
|      1 | suzuki | 1989-09-01 | TokyoShibuyaku | setsumei    | suzuki@hogehoge.hoge |
+--------+--------+------------+----------------+-------------+----------------------+
1 row in set (0.00 sec)

今度は、同じフィールドで、nameカラムの値だけを取得。

mysql> select name from address_t where number='1';
+--------+
| name   |
+--------+
| suzuki |
+--------+
1 row in set (0.01 sec)

◆SELECT文のWHERE句で使用できる演算子の例
（以下の例は全てaddress_tテーブルが対象）

birthdayの値が1980未満のデータを取得したい場合（演算子<を使用）

mysql> select * from address_t where birthday < '1980';

numberの値が1と2と5と7のデータを取得したい場合（演算子inを使用）

mysql> select * from address_t where number in ('1','2','5','7');

numberの値が1と2と5と7ではないデータを取得したい場合（演算子not inを使用）

mysql> select * from address_t where number not in ('1','2','5','7');

and、or、notを使用することで条件を複数つなげて指定することもできる。

birthdayの値が1970以上、1980未満のデータだけを取得したい場合（演算子>=とandを使用）

mysql> select * from address_t where birthday >= '1970' and birthday < '1980';

addressの値にTokyoToshimakuを含むもの、またはTokyoShibuyakuを含むものを取得したい場合（演算子=とorを使用）
＊ようするに、値にTokyoToshimakuを含む、もしくはTokyoShibuyakuを含むもの（どちらも存在すれば両方ともということ）

mysql> select * from address_t where address = 'TokyoToshimaku' or address = 'TokyoShibuyaku';

◆WHERE句で使用できる条件式

likeを使って曖昧検索（〜〜を含むデータを取得）をする。
＊任意の一文字を表す_や、0以上の任意の文字を表す%等のワイルドカードを使って。

like 'P_P'（PHPやPAPが一致する：前方一致）
like 'A%'（Aから始まるデータが一致：前方一致）
like '%A'（Aで終わるデータが一致：後方一致）
like '%A%'（Aを含むデータが一致）

birthdayの値が19?0を含むデータを取得する場合

mysql> select * from address_t where birthday like '19_0%';

addressの値にShiを含むデータを取得する場合

mysql> select * from address_t where address like '%Shi%';

◆MySQLで使用出来る関数

任意の文字列をフィールドから切り出して取得する関数substringとright

address_tテーブルにおいて、nameカラムの値（文字列）の1文字目から4文字目までを取得する

mysql> select substring(name, 1, 4) from address_t;

address_tテーブルにおいて、nameカラムの値（文字列）を右端から4文字取得する

mysql> select right(name, 4) from address_t;

日付の関数（年のみを返す）

address_tテーブルにおいて、birthdayカラムの値から年のみを取得する

mysql> select year(birthday) from address_t;

right関数とyear関数を併用することで、以下のようにして生年月日から現時点での年齢を知ることも出来る。

mysql> select name, birthday, curdate(), (year(curdate())-year(birthday))-(right(curdate(),5)<right(birthday,5)) as age from address_t;

＊curdate()は現在の日付を返す関数。as名前とすることで、得られた結果のフィールド名をつけることができる。

◆order byでデータを並び替える

select文で取得したデータを並び替えるのにorder byを使う。
ascは昇順（小さい順）、descは降順（大きい順）に並び替える。
＊省略すると昇順。

birthdayカラムの値を昇順で並び替えて取得

mysql> select * from address_t order by birthday asc;

birthdayカラムの値を降順で並び替えて取得

mysql> select * from address_t order by birthday desc;

件数を指定してデータを取得したい場合はlimitを使う。

address_tテーブルにおいて、先頭から3件目までのデータのみを取得する場合

mysql> select * from address_t limit 0,3;

◆group byでデータをグループ化する

値をグループ化することで、そのグループにおけるデータの合計や平均値などを求めることが出来る。

max：グループ化したデータの最大値を取得
min：グループ化したデータの最小値を取得
sum：グループ化したデータの合計を取得
avg：グループ化したデータの平均値を取得
count：グループ化したデータのデータ数をカウント

birthdayカラムの値でグループ化し、その値（年）がそれぞれ何人いるかを取得する

mysql> select year(birthday),count(*) as num from address_t group by year(birthday);

＊birthdayカラムの値をyear関数を使って年だけ取り出し、その年にあたるデータ数をcountでカウント。得た値をas numで「num」として表示している。