達人に学ぶSQL

 

自分の書くSQLがなんだか効率が悪い気がしてきて勉強したくなり、初めてのSQL達人に学ぶSQL徹底指南書を入手して学習してます。

せっかくなので重要そうなところをここにまとめておきます。使ってるSQLはMySqlでCloud9でテスト環境を作成してテストしてます。

■サブクエリ

・情報を取得するための、一時テーブルを生成する。
・一時テーブルは処理が終わったら破棄される。

Cloud9のMySQLにログインします。

masasikatano:~/workspace $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.5.54-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

それからテストテーブルの作成。

mysql> use c9
Database changed
mysql> CREATE TABLE IF NOT EXISTS users (
    ->   ID int(11) unsigned AUTO_INCREMENT NOT NULL,
    ->   score int(11) NOT NULL default '0',
    ->   name text NOT NULL,
    ->   sex enum('male', 'female') NOT NULL default 'male',
    ->   updated_at TIMESTAMP NOT NULL,
    ->   created_at TIMESTAMP NOT NULL,
    ->   deleted_at TIMESTAMP NULL,
    ->   PRIMARY KEY (ID)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc users;
+------------+-----------------------+------+-----+---------------------+-----------------------------+
| Field      | Type                  | Null | Key | Default             | Extra                       |
+------------+-----------------------+------+-----+---------------------+-----------------------------+
| ID         | int(11) unsigned      | NO   | PRI | NULL                | auto_increment              |
| score      | int(11)               | NO   |     | 0                   |                             |
| name       | text                  | NO   |     | NULL                |                             |
| sex        | enum('male','female') | NO   |     | male                |                             |
| updated_at | timestamp             | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| created_at | timestamp             | NO   |     | 0000-00-00 00:00:00 |                             |
| deleted_at | timestamp             | YES  |     | NULL                |                             |
+------------+-----------------------+------+-----+---------------------+-----------------------------+
7 rows in set (0.00 sec)

テストデータを挿入します。

mysql> INSERT INTO users (score,name,sex,updated_at,created_at,deleted_at) VALUES
    -> (10,'masasikatano','male','2017-1-11 00:00:00','2017-6-11 00:00:00',null),
    -> (100,'concernedfennel','female','2017-1-12 00:00:00','2017-7-12 00:00:00',null),
    -> (100,'juicysubstr','male','2017-1-13 00:00:00','2017-3-13 00:00:00',null),
    -> (110,'buttheadbaron','female','2017-1-14 00:00:00','2017-4-14 00:00:00',null),
    -> (110,'hauntcyclase','female','2017-1-15 00:00:00','2017-1-15 00:00:00',null),
    -> (130,'profileasked','male','2017-1-16 00:00:00','2017-1-16 00:00:00',null),
    -> (145,'jaguarracer','female','2017-1-17 00:00:00','2017-5-21 00:00:00',null),
    -> (160,'boorishdisplay','male','2017-1-18 00:00:00','2017-3-21 00:00:00',null),
    -> (200,'baseballsecure','female','2017-1-19 00:00:00','2017-2-19 00:00:00','2017-3-20 00:00:00'),
    -> (10,'woundmille','male','2017-2-21 00:00:00','2017-2-21 00:00:00','2017-2-23 00:00:00'),
    -> (-10,'hollandaiseoncology','male','2017-3-11 00:00:00','2017-3-11 00:00:00',null),
    -> (-20,'clotpolestringed','male','2017-4-10 00:00:00','2017-4-11 00:00:00',null),
    -> (-30,'listcampus','female','2017-4-11 00:00:00','2017-4-11 00:00:00','2017-7-10 00:00:00'),
    -> (-100,'teammatemillwright','female','2017-5-11 00:00:00','2017-5-11 00:00:00',null),
    -> (0,'mongoosehalting','male','2017-6-11 00:00:00','2017-6-11 00:00:00',null),
    -> (0,'gashbam','female','2017-7-11 00:00:00','2017-7-11 00:00:00',null)
    -> ;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from users;
+----+-------+---------------------+--------+---------------------+---------------------+---------------------+
| ID | score | name                | sex    | updated_at          | created_at          | deleted_at          |
+----+-------+---------------------+--------+---------------------+---------------------+---------------------+
|  1 |    10 | masasikatano        | male   | 2017-01-11 00:00:00 | 2017-06-11 00:00:00 | NULL                |
|  2 |   100 | concernedfennel     | female | 2017-01-12 00:00:00 | 2017-07-12 00:00:00 | NULL                |
|  3 |   100 | juicysubstr         | male   | 2017-01-13 00:00:00 | 2017-03-13 00:00:00 | NULL                |
|  4 |   110 | buttheadbaron       | female | 2017-01-14 00:00:00 | 2017-04-14 00:00:00 | NULL                |
|  5 |   110 | hauntcyclase        | female | 2017-01-15 00:00:00 | 2017-01-15 00:00:00 | NULL                |
|  6 |   130 | profileasked        | male   | 2017-01-16 00:00:00 | 2017-01-16 00:00:00 | NULL                |
|  7 |   145 | jaguarracer         | female | 2017-01-17 00:00:00 | 2017-05-21 00:00:00 | NULL                |
|  8 |   160 | boorishdisplay      | male   | 2017-01-18 00:00:00 | 2017-03-21 00:00:00 | NULL                |
|  9 |   200 | baseballsecure      | female | 2017-01-19 00:00:00 | 2017-02-19 00:00:00 | 2017-03-20 00:00:00 |
| 10 |    10 | woundmille          | male   | 2017-02-21 00:00:00 | 2017-02-21 00:00:00 | 2017-02-23 00:00:00 |
| 11 |   -10 | hollandaiseoncology | male   | 2017-03-11 00:00:00 | 2017-03-11 00:00:00 | NULL                |
| 12 |   -20 | clotpolestringed    | male   | 2017-04-10 00:00:00 | 2017-04-11 00:00:00 | NULL                |
| 13 |   -30 | listcampus          | female | 2017-04-11 00:00:00 | 2017-04-11 00:00:00 | 2017-07-10 00:00:00 |
| 14 |  -100 | teammatemillwright  | female | 2017-05-11 00:00:00 | 2017-05-11 00:00:00 | NULL                |
| 15 |     0 | mongoosehalting     | male   | 2017-06-11 00:00:00 | 2017-06-11 00:00:00 | NULL                |
| 16 |     0 | gashbam             | female | 2017-07-11 00:00:00 | 2017-07-11 00:00:00 | NULL                |
+----+-------+---------------------+--------+---------------------+---------------------+---------------------+
16 rows in set (0.00 sec)

サブクエリでscoreが100以上のmaleのnameを抽出

mysql> SELECT U.name 
    ->   FROM
    ->        ( 
    ->          SELECT * 
    ->          FROM users 
    ->          WHERE score >= 100
    ->        ) 
    ->        AS U
    ->  WHERE sex='male';
+----------------+
| name           |
+----------------+
| juicysubstr    |
| profileasked   |
| boorishdisplay |
+----------------+
3 rows in set (0.00 sec)

合ってそうです。

LaravelのEloquentでのサブクエリの使い方は割愛

■Case式

・CASE式を使用することで、SQL中で条件制御を行うことが出来ます。

mysql> SELECT id,name,sex
    ->       ,CASE sex
    ->          WHEN 'male' THEN '男'
    ->          WHEN 'female' THEN '女'
    ->          ELSE 'another'
    ->        END 性別
    -> FROM   users;
+----+---------------------+--------+--------+
| id | name                | sex    | 性別   |
+----+---------------------+--------+--------+
|  1 | masasikatano        | male   | 男     |
|  2 | concernedfennel     | female | 女     |
|  3 | juicysubstr         | male   | 男     |
|  4 | buttheadbaron       | female | 女     |
|  5 | hauntcyclase        | female | 女     |
|  6 | profileasked        | male   | 男     |
|  7 | jaguarracer         | female | 女     |
|  8 | boorishdisplay      | male   | 男     |
|  9 | baseballsecure      | female | 女     |
| 10 | woundmille          | male   | 男     |
| 11 | hollandaiseoncology | male   | 男     |
| 12 | clotpolestringed    | male   | 男     |
| 13 | listcampus          | female | 女     |
| 14 | teammatemillwright  | female | 女     |
| 15 | mongoosehalting     | male   | 男     |
| 16 | gashbam             | female | 女     |
+----+---------------------+--------+--------+
16 rows in set (0.00 sec)

■変数

・あるステートメント内のユーザー定義変数に値を格納し、あとから別のステートメントでこれを参照できます。

mysql> SET @t1=1, @t2=2, @t3:=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 |
+------+------+------+--------------------+
1 row in set (0.00 sec)

■OLAP

・OLAP関数というのは,OLAP(OnLine Analytical Processing)という名前のとおり,データベースを使ってリアルタイムに分析を行う処理に対応した関数のことです。DWH(Data Ware House)とかBI(Business Intelligence)の分野でよく利用されます。

・MySQLには未実装なものが多い(MariaDBにもない)

ほげえ・・

↑の変数を利用する方法があるみたいなのでやってみます。

mysql> SET @rank = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT (@rank := @rank + 1) AS rank, 
    -> id ,score,name 
    -> FROM users
    -> ORDER BY score DESC;
+------+----+-------+---------------------+
| rank | id | score | name                |
+------+----+-------+---------------------+
|    1 |  9 |   200 | baseballsecure      |
|    2 |  8 |   160 | boorishdisplay      |
|    3 |  7 |   145 | jaguarracer         |
|    4 |  6 |   130 | profileasked        |
|    5 |  4 |   110 | buttheadbaron       |
|    6 |  5 |   110 | hauntcyclase        |
|    7 |  2 |   100 | concernedfennel     |
|    8 |  3 |   100 | juicysubstr         |
|    9 |  1 |    10 | masasikatano        |
|   10 | 10 |    10 | woundmille          |
|   11 | 15 |     0 | mongoosehalting     |
|   12 | 16 |     0 | gashbam             |
|   13 | 11 |   -10 | hollandaiseoncology |
|   14 | 12 |   -20 | clotpolestringed    |
|   15 | 13 |   -30 | listcampus          |
|   16 | 14 |  -100 | teammatemillwright  |
+------+----+-------+---------------------+
16 rows in set (0.00 sec)

うおお超便利

ついでに、Laravel EloquentでMySQLのユーザ定義変数を使う方法もあるみたいです。

■Having句

・HAVING句はWHERE句と異なりGROUP BY句によってグループ化が行われたデータに対して条件を指定してデータを絞り込む場合に使用します。

↓Havingを使用しない例

mysql> SELECT sex, AVG(score)
    -> FROM users
    -> GROUP BY sex ;
+--------+------------+
| sex    | AVG(score) |
+--------+------------+
| male   |    47.5000 |
| female |    66.8750 |
+--------+------------+
2 rows in set (0.00 sec)

↓Havingを使用して絞込を加えた例

mysql> SELECT sex, AVG(score)
    -> FROM users
    -> GROUP BY sex 
    -> HAVING AVG(score) > 50;
+--------+------------+
| sex    | AVG(score) |
+--------+------------+
| female |    66.8750 |
+--------+------------+
1 row in set (0.00 sec)

便利だ。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です