MySQLコマンド

[PR]

×
[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。
MySQLのCASE構文～プログラム超初心者のためのMySQLコマンド～
今回はMySQLのCASE構文について整理して書き留めておきたいと思います。

まず、MySQLのCASE構文は主に次の2つの構文に分類できます。

1.値のリストとの比較
```
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

『MySQL 5.6 リファレンスマニュアル :: 13.6.5.1 CASE 構文』
```
2.条件分岐
```
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

『MySQL 5.6 リファレンスマニュアル :: 13.6.5.1 CASE 構文』
```
実際に試してみたいと思いますが、その前に、準備を行います。

$ mysql -u root

mysql> create database students;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+------------------------+
| Database               |
+------------------------+
| students               |
+------------------------+

mysql> Ctrl-C -- exit!
Aborted

$ mysql -u root -D students

mysql> show tables;
Empty set (0.00 sec)

mysql> create table test(name text, age integer);

mysql> show tables;
+--------------------+
| Tables_in_students |
+--------------------+
| test               |
+--------------------+
1 row in set (0.00 sec)

mysql> insert into test (name, age) values ('taro', 14), ('jiro', 16), ('sabro', 18);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from test;
+-------+------+
| name | age |
+-------+------+
| taro |   14 |
| jiro |   16 |
| sabro |   18 |
+-------+------+
3 rows in set (0.00 sec)

準備ができましたので、それぞれ試してみたいと思います。
なお、下記の例では、CASE構文で取得したデータのカラムにタイトル名を設定するためにas句を使用していますが、この際には「end case」の「case」は取り外す必要があります。

1.値のリストとの比較

mysql> select
    ->     name,
    ->     case age
    ->         when 13 then '中学1年生'
    ->         when 14 then '中学2年生'
    ->         when 15 then '中学3年生'
    ->         when 16 then '高校1年生'
    ->         when 17 then '高校2年生'
    ->         when 18 then '高校3年生'
    ->         else 'その他'
    ->     end as class
    -> from test;
+-------+---------------+
| name | class         |
+-------+---------------+
| taro | 中学2年生     |
| jiro | 高校1年生     |
| sabro | 高校3年生     |
+-------+---------------+
3 rows in set (0.00 sec)

2.条件分岐

mysql> select
    ->     name,
    ->     case
    ->         when age between 13 and 15 then '中学生'
    ->         when age between 16 and 18 then '高校生'
    ->         else 'その他'
    ->     end as junior_senior
    -> from test;
+-------+---------------+
| name | junior_senior |
+-------+---------------+
| taro | 中学生        |
| jiro | 高校生        |
| sabro | 高校生        |
+-------+---------------+
3 rows in set (0.00 sec)

以上です！

【参考サイト】
『MySQL 5.6 リファレンスマニュアル :: 13.6.5.1 CASE 構文』
『MySQL 5.6 リファレンスマニュアル :: 12.4 制御フロー関数』
『カラムの値に応じて異なる結果を返す条件式の記述』
PR
重複しているレコードを抽出する～プログラム超初心者のためのMySQLコマンド～

今回は重複したレコードを抽出するコマンドを書き留めておきます。

SELECT * FROM テーブル名
GROUP BY カラム名
HAVING COUNT(*) >= 2

この構文の注意点は集計関数(GROUP BY)とそれに集計条件を付加するHAVING句の組み合わせです。
それぞれを下記で説明します。

GROUP BY カラム名
このカラムはグループ化の基準列名を書きます。

HAVING
この句の後に、集計結果に対する集計条件を書きます。
HAVING句と同じように条件を記述する句にWHERE句がありますが、それとの違いは、WHERE句は検索時に絞り込みを行う『検索条件』であるのに対して、HAVING句は集計後に絞り込みを行う『集計条件』であることです。

以上です！

上記でまとめましたが、後ほど、上司より指摘がありました。
こちらが、正しいですね。

SELECT 重複検索対象カラム名 FROM テーブル名
GROUP BY 重複検索対象カラム名
HAVING COUNT(重複検索対象カラム名) >= 2

こういうことですね、、

idとcompany_idだけからなるappleテーブルを作成
mysql> create table applel(id int, company_id int);

データを流し込み
mysql> insert into apple (id, company_id) values
    -> (1,1),
    -> (2,1),
    -> (3,2),
    -> (4,2),
    -> (5,3),
    -> (6,3),
    -> (7,4);

上記のqueryを順に流し込む
mysql> select * from apple;
+------+------------+
| id   | company_id |
+------+------------+
|    1 |          1 |
|    2 |          1 |
|    3 |          2 |
|    4 |          2 |
|    5 |          3 |
|    6 |          3 |
|    7 |          4 |
+------+------------+
7 rows in set (0.00 sec)

mysql> select * from apple group by company_id;
+------+------------+
| id   | company_id |
+------+------------+
|    1 |          1 |
|    3 |          2 |
|    5 |          3 |
|    7 |          4 |
+------+------------+
4 rows in set (0.00 sec)

mysql> select * from apple group by company_id having count(*) >= 2;
+------+------------+
| id   | company_id |
+------+------------+
|    1 |          1 |
|    3 |          2 |
|    5 |          3 |
+------+------------+
3 rows in set (0.00 sec)

company_idは正しいですが、それと一緒にとれるそれ以外のもの(今回のは場合は単純にid)、company_id=1のデータは、id=1とid=2があるが、id=1だけが表示される。
実際のテーブルだとこれの他に名前とか住所などがあるが、それは単にcompany_id=1の中の1レコードに過ぎない。

つまり、実際と異なることがわかります。

つまり、基本的に、group by等で集計を行うときには、group byで指定したカラムかsum,avg,count,min,maxなどの集計用の関数をselectの後ろでは指定するのが基本となります。

mysql> select company_id from apple group by company_id having count(company_id) >= 2;
+------------+
| company_id |
+------------+
|          1 |
|          2 |
|          3 |
+------------+
3 rows in set (0.00 sec)

以上です！

【参考サイト】
『MySQL:重複しているレコードを抽出する』
『SQLで複数行あるデータのみを抽出する方法』
『スッキリわかる SQL 入門ドリル215問付き! (スッキリシリーズ) 』
テーブルに定義されているフィールドの一覧を表示する～プログラム超初心者のためのMySQLコマンド～

今回は、テーブルに定義されているフィールドの一覧を表示するコマンドを整理します。

方法は、２つあります。

desc(describe)

mysql>DESCRIBE テーブル名;
mysql>DESC テーブル名;

show

mysql>SHOW COLUMNS FROM テーブル名;
mysql>SHOW FIELDS FROM テーブル名;

なお、SHOWを使用する場合、条件式LIKEを指定することができます。

SHOW FIELDS FROM テーブル名 LIKE '%hogehoge%';

どちらの方法でも、下記の様に表示されます。

+--------------------------------------+------------------+------+-----+---------+----------------+
| Field                                | Type             | Null | Key | Default | Extra          |
+--------------------------------------+------------------+------+-----+---------+----------------+
| id                                   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| hoge                           | int(11)          | NO   |     | NULL    |                |
+--------------------------------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

今まで、show columnsしか使っていなかったのでdescは入力文字数が少ないから便利ですね。

以上です。

【参考サイト】
『Databaseの一覧を表示する』
MySQLでDB内の全てのインデックスの一覧をCSV出力する
今回は、MySQLでDB内の全てのインデックスの一覧をCSV出力する方法を整理します。

通常、インデックスを参照する場合、下記の方法があります。

SHOW INDEX FROM [データベース名.]テーブル名;
SHOW INDEX FROM テーブル名 FROM データベース名;

しかし、この方法だと、テーブル毎にしかインデックスが参照できません。

そこで、下記のMySQLコマンドを実行するとDB内の全てのインデックスの一覧をCSV出力できます。

select
TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,COLUMN_NAME,SEQ_IN_INDEX
from
information_schema.STATISTICS
where
TABLE_SCHEMA = 'データベース名'
order by TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,SEQ_IN_INDEX
INTO OUTFILE "/tmp/インデックス一覧名.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

なお、「information_schema」とは、

INFORMATION_SCHEMAはSQL標準の規格※となっている、インスタンス内のメタデータを格納するデータベースです。このようなメタデータを格納する機能は、データディクショナリやシステムカタログと呼ばれることもあります。MySQLの INFORMATION_SCHEMAは標準規格ANSI/ISO SQL:2003のPart 11のSchemataの定義に従っており、SQL:2003のコアであるF021 Basic information schemaに準拠するように実装されています。
『MySQLのメタデータ管理(2) INFORMATION_SCHEMA』

INFORMATION_SCHEMAにはメモリに格納された参照専用の特別なテーブルがあり、SELECT文を使ってメタデータの確認ができます。ただし実態はテーブルではなくビューで、データを格納するファイルはありません。またトリガーの設定もできません。それぞれのテーブルに複数の文字列型の列が存在しますが、文字コードは基本的にutf8で、デフォルトのCOLLATION (utf8_general_c)を使用しています。
『MySQLのメタデータ管理(2) INFORMATION_SCHEMA』

まとめると、
1. INFORMATION_SCHEMAは、インスタンス内のメタデータを格納するデータベース。
2. INFORMATION_SCHEMAにはメモリに格納された参照専用の特別なテーブルがあり、SELECT文を使ってメタデータの確認ができる。
3. この実態はテーブルではなくビューで、データを格納するファイルはない。
4. MySQL サーバーに関する情報が保存されているデータベース。
5. このデータベースを参照することは可能だが、更新及び削除することはできない。
以上です。

【参照サイト】
『mysql: 登録されているインデクスを確認する方法』
『MySQLでDB内のインデックスをすべて表示する方法についてのメモ』
『インデックスの作成・削除・情報表示・追加』
『MySQLのメタデータ管理(2) INFORMATION_SCHEMA』
『INFORMATION_SCHEMA というデータベースは何ですか？』
重複行を除外してデータを取得する～プログラム超初心者のためのMySQLコマンド～

今回は、重複行を除外してデータを取得するコマンドを書き留めえておきます。

selectの後に付けて使用します。
使用例としては、下記の通りです。

mysql>SELECT DISTINCT カラム名 FROM テーブル名;

なお、sumやcountなどと併用する場合は、下記のように記述します。

mysql>SELECT SUM( DISTINCT カラム名 ) FROM テーブル名;

なお、distinctの説明としては下記の通りとなります。

DISTINCT
読み方：ディスティンクト

DISTINCTとは、SELECT文で生成されるテーブル（表）から冗長な重複行を取り除くものである。

DISTINCTをSELECT文に指定することによって、列の各行の値が全て等しい行は1行のみ残して、他は全て破棄される。同じ列の値がNULL値である場合は、値が等しいものとして処理される。

DISTINCTの指定は、テーブル（表）の行の出現順序を規定するものではない。行の出現順序を指定したい場合はORDER BY句を指定する。

『IT用語辞典バイナリ』

また、distinctの英語の意味としては、「別個の」「種類が異なる」「明白な」などとなります。

以上です！

【参考サイト】
『重複行を除外』
『最近、MySQLばっか。今日は“DISTINCT”について。』

プロフィール

ＨＮ：

プラムザ　太郎

性別：

非公開

MySQLコマンド

プロフィール

カテゴリー

最新記事

RSS

リンク

P R