SQL初心者のおぼえがき（２）

前回の続き。yuzatakujp1013.hatenablog.com

テーブルとレコードを更新しておきます。

mysql> create table users(
    -> id int not null primary key auto_increment,
    -> name varchar(255),
    -> email varchar(255),
    -> team enum('blue', 'red', 'yellow'),
    -> score double,
    -> created datetime
    -> );

mysql> insert into users (name, email, team, score, created) value
    -> ('taguchi', 'taguchi@email', 'blue', 5.5, '2015-05-11 10:00:00'),
    -> ('suzuki', 'suzuki@email', 'yellow', 8.2, '2015-06-21 20:00:00'),
    -> ('yukawa', 'yukawa@email', 'red', 2.3, '2015-06-21 13:00:00'),
    -> ('kimura', 'kimura@email', 'yellow', 7.4, '2015-06-28 15:00:00'),
    -> ('tanaka', 'tanaka@email', 'blue', 4.2, '2015-06-29 16:00:00');

レコードの抽出

レコードの抽出にはSELECT文を使います。

SELECT "フィールド名" FROM "テーブル名"

mysql> select * from users;
+----+---------+---------------+--------+-------+---------------------+
| id | name    | email         | team   | score | created             |
+----+---------+---------------+--------+-------+---------------------+
|  1 | taguchi | taguchi@email | blue   |   5.5 | 2015-05-11 10:00:00 |
|  2 | suzuki  | suzuki@email  | yellow |   8.2 | 2015-06-21 20:00:00 |
|  3 | yukawa  | yukawa@email  | red    |   2.3 | 2015-06-21 13:00:00 |
|  4 | kimura  | kimura@email  | yellow |   7.4 | 2015-06-28 15:00:00 |
|  5 | tanaka  | tanaka@email  | blue   |   4.2 | 2015-06-29 16:00:00 |
+----+---------+---------------+--------+-------+---------------------+
5 rows in set (0.00 sec)

これは全てのフィールド（*）を、usersテーブルから抽出する処理です。
ちなみに、フィールドが多すぎるとき、横幅が収まりきらないときは

mysql> select * from users \G;

とするといい感じに表示してくれます。

レコード内の重複を許さない場合は、DISTINCT文を使います。

mysql> select distinct team from users;
+--------+
| team   |
+--------+
| blue   |
| yellow |
| red    |
+--------+

また、レコード名に別名を付けたい場合は、

SELECT "テーブル別名"."フィールド1" "フィールド別名" FROM "テーブル名" "テーブル別名";

テーブルの規模が大きくなったり、複雑になってくると有効になってきます。（最初は特に意識する必要ないかも）

条件付きで抽出

データを選択的に抽出する場合はWHERE文を追加します。

SELECT "フィールド名" FROM "テーブル名" WHERE "条件"

mysql> select * from users where score = 5.5;
+----+---------+---------------+------+-------+---------------------+
| id | name    | email         | team | score | created             |
+----+---------+---------------+------+-------+---------------------+
|  1 | taguchi | taguchi@email | blue |   5.5 | 2015-05-11 10:00:00 |
+----+---------+---------------+------+-------+---------------------+
1 row in set (0.00 sec)

この時の条件式には、等号=、等号否定!=(<>)、不等号<, > などがあります。
また、○○以上△△以下と指定したい場合はBETWEENを使います。

mysql> select * from users where score between 5.0 and 8.0;
+----+---------+---------------+--------+-------+---------------------+
| id | name    | email         | team   | score | created             |
+----+---------+---------------+--------+-------+---------------------+
|  1 | taguchi | taguchi@email | blue   |   5.5 | 2015-05-11 10:00:00 |
|  4 | kimura  | kimura@email  | yellow |   7.4 | 2015-06-28 15:00:00 |
+----+---------+---------------+--------+-------+---------------------+

また、○○または△△と指定したい場合はINを使います。(ORを使ってもできます)

mysql> select * from users where team in ('red', 'blue');
+----+---------+---------------+------+-------+---------------------+
| id | name    | email         | team | score | created             |
+----+---------+---------------+------+-------+---------------------+
|  1 | taguchi | taguchi@email | blue |   5.5 | 2015-05-11 10:00:00 |
|  3 | yukawa  | yukawa@email  | red  |   2.3 | 2015-06-21 13:00:00 |
|  5 | tanaka  | tanaka@email  | blue |   4.2 | 2015-06-29 16:00:00 |
+----+---------+---------------+------+-------+---------------------+

曖昧な抽出をするときには、LIKE文を追加します。

mysql> select * from users where email like '%@email';

%は任意の文字列を意味しており、この場合、@emailがつくレコードを抽出します。
任意の1文字としたい場合は、_（アンダーバー）を使います。

並び替え・件数の制限

データの値で並び替えしたい場合は、ORDER BYを使います。（降順の場合は末尾にDESCを追加）

mysql> select * from users order by score;
+----+---------+---------------+--------+-------+---------------------+
| id | name    | email         | team   | score | created             |
+----+---------+---------------+--------+-------+---------------------+
|  3 | yukawa  | yukawa@email  | red    |   2.3 | 2015-06-21 13:00:00 |
|  5 | tanaka  | tanaka@email  | blue   |   4.2 | 2015-06-29 16:00:00 |
|  1 | taguchi | taguchi@email | blue   |   5.5 | 2015-05-11 10:00:00 |
|  4 | kimura  | kimura@email  | yellow |   7.4 | 2015-06-28 15:00:00 |
|  2 | suzuki  | suzuki@email  | yellow |   8.2 | 2015-06-21 20:00:00 |
+----+---------+---------------+--------+-------+---------------------+

抽出件数の制限をする場合には、LIMITを使います。

mysql> select * from users order by score desc limit 2;
+----+--------+--------------+--------+-------+---------------------+
| id | name   | email        | team   | score | created             |
+----+--------+--------------+--------+-------+---------------------+
|  2 | suzuki | suzuki@email | yellow |   8.2 | 2015-06-21 20:00:00 |
|  4 | kimura | kimura@email | yellow |   7.4 | 2015-06-28 15:00:00 |
+----+--------+--------------+--------+-------+---------------------+

これは、スコアの値を降順で並び替えた結果のうち上位2件のみ表示させる文です。

「抽選で1名選ぶ」という処理をしたい場合にもLIMITを使います。
ランダムで1名選ぶ必要があるので乱数を発生させるRAND()を使います。

mysql> select * from users order by rand() limit 1;
+----+--------+--------------+------+-------+---------------------+
| id | name   | email        | team | score | created             |
+----+--------+--------------+------+-------+---------------------+
|  5 | tanaka | tanaka@email | blue |   4.2 | 2015-06-29 16:00:00 |
+----+--------+--------------+------+-------+---------------------+

データの集計

レコードの総件数を調べたい場合、COUNT文を使います。

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        5 |
+----------+

また、最大値(MAX)、最小値(MIN)、平均値(AVG)、合計値(SUM)などといった計算も可能です。

mysql> select max(score) from users;
+------------+
| max(score) |
+------------+
|        8.2 |
+------------+

グループ集計したい場合は、GROUP BYを使います。
例えば、チームごとで平均点を算出したい場合

mysql> select team, avg(score) from users group by team;
+--------+------------+
| team   | avg(score) |
+--------+------------+
| blue   |       4.85 |
| red    |        2.3 |
| yellow |        7.8 |
+--------+------------+

SUMやAVGなどの関数の値に条件付けを行いたい場合、残念ながらWHERE文は使うことができません。
そこで、HAVING文を使います。

mysql> select team, avg(score) from users group by team having avg(score) > 5.0; 
+--------+------------+
| team   | avg(score) |
+--------+------------+
| yellow |        7.8 |
+--------+------------+

文字列

あるレコードの文字列の長さを取得したい場合はLENGTH()を使います。

mysql> select email, length(email) from users;
+---------------+---------------+
| email         | length(email) |
+---------------+---------------+
| taguchi@email |            13 |
| suzuki@email  |            12 |
| yukawa@email  |            12 |
| kimura@email  |            12 |
| tanaka@email  |            12 |
+---------------+---------------+

文字列を連結させたい場合は、CONCAT文を使います。

CONCAT ( 文字列 1, 文字列 2, 文字列 3, ...)

mysql> select concat(name, '(',team,')') from users;
+----------------------------+
| concat(name, '(',team,')') |
+----------------------------+
| taguchi(blue)              |
| suzuki(yellow)             |
| yukawa(red)                |
| kimura(yellow)             |
| tanaka(blue)               |
+----------------------------+

結果の名前が長ったらしくてわかりづらいので、AS文で名前を変えましょう。

mysql> select concat(name, '(',team,')') as LABEL from users;
+----------------+
| LABEL          |
+----------------+
| taguchi(blue)  |
| suzuki(yellow) |
| yukawa(red)    |
| kimura(yellow) |
| tanaka(blue)   |
+----------------+

また、文字列の先頭1文字だけ抽出するのにSUBSTRING文を使います。
strにおけるpos番目の位置から、len個の文字を読み込みます。

SUBSTRING (str, pos, len)

mysql> select name, substring(team, 1, 1) as TEAM_INITIAL from users;
+---------+--------------+
| name    | TEAM_INITIAL |
+---------+--------------+
| taguchi | b            |
| suzuki  | y            |
| yukawa  | r            |
| kimura  | y            |
| tanaka  | b            |
+---------+--------------+

とりあえずこんなところ。
レコードの更新、削除などについてまだ触れていなかったので次回に。（更新しました）yuzatakujp1013.hatenablog.com