SQLを使うなら理解しておきたいアルゴリズム？(というか、select文の書き方) - where・order by・join・group by
SQLのselect文の書き方の覚書です。
なお、本文中の動作確認は「mysql Ver 5.0.67」で行いました。
目次
説明に使用するデータ構造(というかテーブル)
select文の使い方を説明するために、以下のようなテーブルを使います。
create table countries (
name nvarchar(30),
currency char(3),
population int
);
insert into countries values ( "日本", "JPY", 127156000 );
insert into countries values ( "フランス", "EUR", 65073482 );
insert into countries values ( "スペイン", "EUR", 44904000 );
insert into countries values ( "ロシア", "RUB", 141903979 );
insert into countries values ( "ベトナム", "VND", 84238000 );
insert into countries values ( "カンボジア", "KHR", 14805000 );
insert into countries values ( "コートジボワール", "XOF", 44904000 );
このテーブルは、以下のselect文で表示できます。
select * from countries;
このselect文の実行結果は以下の通りです。
+--------------------------+----------+------------+ | name | currency | population | +--------------------------+----------+------------+ | 日本 | JPY | 127156000 | | フランス | EUR | 65073482 | | スペイン | EUR | 44904000 | | ロシア | RUB | 141903979 | | ベトナム | VND | 84238000 | | カンボジア | KHR | 14805000 | | コートジボワール | XOF | 44904000 | +--------------------------+----------+------------+ 7 rows in set (0.00 sec)
■抽出(where句)
select文のwhere句を使うと、テーブルの中から特定の条件を満たすデータを抽出できます。
例として、商品テーブルproductsから特定の条件を満たすデータを抽出します。
create table products (
product_id char(4),
product_name char(30),
price int
);
insert into products values( "1010", "えんぴつ", 80 );
insert into products values( "1020", "ボールペン", 100 );
insert into products values( "1030", "消しゴム", 100 );
insert into products values( "2010", "定規", 140 );
insert into products values( "2020", "コンパス", 300 );
insert into products values( "3010", "のり", 200 );
商品テーブルproductsを表示すると、以下のようになります。
+------------+-----------------+-------+ | product_id | product_name | price | +------------+-----------------+-------+ | 1010 | えんぴつ | 80 | | 1020 | ボールペン | 100 | | 1030 | 消しゴム | 100 | | 2010 | 定規 | 140 | | 2020 | コンパス | 300 | | 3010 | のり | 200 | +------------+-----------------+-------+ 6 rows in set (0.00 sec)
以下のselect文は、商品テーブルproductsから「price > 100」を満たすデータを抽出します。
select * from products where price > 100;
抽出結果は以下のようになります。
+------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 2010 | 定規 | 140 | | 2020 | コンパス | 300 | | 3010 | のり | 200 | +------------+--------------+-------+ 3 rows in set (0.00 sec)
■ソート(order by句)
select文のorder by句を使うと、テーブルを特定の条件でソートできます。
ソートの例(昇順)
例として、売上テーブルsalesを特定の項目の「昇順」にソートします。
create table sales (
sales_date char(8),
sales_no char(5),
product_id char(4),
amount int
);
insert into sales values( "20090702", "00001", "1020", 10 );
insert into sales values( "20090702", "00002", "1010", 5 );
insert into sales values( "20090702", "00003", "3010", 20 );
insert into sales values( "20090703", "00001", "1030", 10 );
insert into sales values( "20090703", "00002", "1020", 15 );
insert into sales values( "20090704", "00001", "2020", 30 );
insert into sales values( "20090704", "00002", "3010", 20 );
売上テーブルsalesを表示すると、以下のようになります。
+------------+----------+------------+--------+ | sales_date | sales_no | product_id | amount | +------------+----------+------------+--------+ | 20090702 | 00001 | 1020 | 10 | | 20090702 | 00002 | 1010 | 5 | | 20090702 | 00003 | 3010 | 20 | | 20090703 | 00001 | 1030 | 10 | | 20090703 | 00002 | 1020 | 15 | | 20090704 | 00001 | 2020 | 30 | | 20090704 | 00002 | 3010 | 20 | +------------+----------+------------+--------+ 7 rows in set (0.00 sec)
以下のselect文は、売上テーブルsalesを「sales_noの昇順」にソートします。
select * from sales order by sales_no;
ソート結果は以下のようになります。
+------------+----------+------------+--------+ | sales_date | sales_no | product_id | amount | +------------+----------+------------+--------+ | 20090702 | 00001 | 1020 | 10 | | 20090703 | 00001 | 1030 | 10 | | 20090704 | 00001 | 2020 | 30 | | 20090702 | 00002 | 1010 | 5 | | 20090703 | 00002 | 1020 | 15 | | 20090704 | 00002 | 3010 | 20 | | 20090702 | 00003 | 3010 | 20 | +------------+----------+------------+--------+ 7 rows in set (0.00 sec)
ソートの例(降順)
例をもう一つ挙げます。商品テーブルproductsを特定の項目の「降順」にソートします。
create table products (
product_id char(4),
product_name char(30),
price int
);
insert into products values( "2010", "定規", 140 );
insert into products values( "2020", "コンパス", 300 );
insert into products values( "3010", "のり", 200 );
insert into products values( "1030", "消しゴム", 100 );
insert into products values( "1020", "ボールペン", 100 );
insert into products values( "1010", "えんぴつ", 80 );
商品テーブルproductsを表示すると、以下のようになります。
+------------+-----------------+-------+ | product_id | product_name | price | +------------+-----------------+-------+ | 2010 | 定規 | 140 | | 2020 | コンパス | 300 | | 3010 | のり | 200 | | 1030 | 消しゴム | 100 | | 1020 | ボールペン | 100 | | 1010 | えんぴつ | 80 | +------------+-----------------+-------+ 6 rows in set (0.00 sec)
以下のselect文は、商品データproductsを「product_idの降順」にソートします。
select * from products order by product_id desc;
ソート結果は以下のようになります。
+------------+-----------------+-------+ | product_id | product_name | price | +------------+-----------------+-------+ | 3010 | のり | 200 | | 2020 | コンパス | 300 | | 2010 | 定規 | 140 | | 1030 | 消しゴム | 100 | | 1020 | ボールペン | 100 | | 1010 | えんぴつ | 80 | +------------+-----------------+-------+ 6 rows in set (0.00 sec)
■結合(join句)
select文のjoin句を使うと、２つのテーブルを特定の条件で結合できます。
例として、商品テーブルproductsと売上テーブルsalesを結合します。
create table products (
product_id char(4),
product_name char(30),
price int
);
insert into products values( "1010", "えんぴつ", 80 );
insert into products values( "1020", "ボールペン", 100 );
insert into products values( "1030", "消しゴム", 100 );
insert into products values( "2010", "定規", 140 );
insert into products values( "2020", "コンパス", 300 );
insert into products values( "3010", "のり", 200 );
create table sales (
sales_date char(8),
sales_no char(5),
product_id char(4),
amount int
);
insert into sales values( "20090702", "00001", "1020", 10 );
insert into sales values( "20090702", "00002", "1010", 5 );
insert into sales values( "20090702", "00003", "3010", 20 );
insert into sales values( "20090703", "00001", "1030", 10 );
insert into sales values( "20090703", "00002", "1020", 15 );
insert into sales values( "20090704", "00001", "2020", 30 );
insert into sales values( "20090704", "00002", "3010", 20 );
商品テーブルproductsを表示すると以下のようになります。
+------------+-----------------+-------+ | product_id | product_name | price | +------------+-----------------+-------+ | 1010 | えんぴつ | 80 | | 1020 | ボールペン | 100 | | 1030 | 消しゴム | 100 | | 2010 | 定規 | 140 | | 2020 | コンパス | 300 | | 3010 | のり | 200 | +------------+-----------------+-------+ 6 rows in set (0.00 sec)
また、売上テーブルsalesを表示すると以下のようになります。
+------------+----------+------------+--------+ | sales_date | sales_no | product_id | amount | +------------+----------+------------+--------+ | 20090702 | 00001 | 1020 | 10 | | 20090702 | 00002 | 1010 | 5 | | 20090702 | 00003 | 3010 | 20 | | 20090703 | 00001 | 1030 | 10 | | 20090703 | 00002 | 1020 | 15 | | 20090704 | 00001 | 2020 | 30 | | 20090704 | 00002 | 3010 | 20 | +------------+----------+------------+--------+ 7 rows in set (0.00 sec)
以下のselect文は、商品テーブルと売上テーブルをproduct_idをキーとして結合します。
select s.sales_date, s.sales_no, p.product_id, p.product_name, s.amount, p.price from products p join sales s on p.product_id = s.product_id;
結合結果は以下のようになります。
+------------+----------+------------+-----------------+--------+-------+ | sales_date | sales_no | product_id | product_name | amount | price | +------------+----------+------------+-----------------+--------+-------+ | 20090702 | 00001 | 1020 | ボールペン | 10 | 100 | | 20090702 | 00002 | 1010 | えんぴつ | 5 | 80 | | 20090702 | 00003 | 3010 | のり | 20 | 200 | | 20090703 | 00001 | 1030 | 消しゴム | 10 | 100 | | 20090703 | 00002 | 1020 | ボールペン | 15 | 100 | | 20090704 | 00001 | 2020 | コンパス | 30 | 300 | | 20090704 | 00002 | 3010 | のり | 20 | 200 | +------------+----------+------------+-----------------+--------+-------+ 7 rows in set (0.00 sec)
■集計(group by句)
select文のgroup by句を使うと、テーブルを特定の条件で集計できます。
例として、売上データsalesを集計します。
create table sales (
sales_date char(8),
sales_no char(5),
product_name char(30),
price int,
amount int
);
insert into sales values( "20090702", "00001", "ボールペン", 100, 10 );
insert into sales values( "20090702", "00002", "えんぴつ", 80, 5 );
insert into sales values( "20090702", "00003", "のり", 200, 20 );
insert into sales values( "20090703", "00001", "消しゴム", 100, 10 );
insert into sales values( "20090703", "00002", "ボールペン", 100, 15 );
insert into sales values( "20090704", "00001", "コンパス", 300, 30 );
insert into sales values( "20090704", "00002", "のり", 200, 20 );
売上テーブルsalesを表示すると以下のようになります。
+------------+----------+-----------------+-------+--------+ | sales_date | sales_no | product_name | price | amount | +------------+----------+-----------------+-------+--------+ | 20090702 | 00001 | ボールペン | 100 | 10 | | 20090702 | 00002 | えんぴつ | 80 | 5 | | 20090702 | 00003 | のり | 200 | 20 | | 20090703 | 00001 | 消しゴム | 100 | 10 | | 20090703 | 00002 | ボールペン | 100 | 15 | | 20090704 | 00001 | コンパス | 300 | 30 | | 20090704 | 00002 | のり | 200 | 20 | +------------+----------+-----------------+-------+--------+ 7 rows in set (0.01 sec)
以下のselect文は、売上テーブルsalesを集計してsales_date毎の合計数量と合計売上高を求めます。
select sales_date, sum(amount), sum(price * amount) from sales group by sales_date;
集計結果は以下のようになります。
+------------+-------------+---------------------+ | sales_date | sum(amount) | sum(price * amount) | +------------+-------------+---------------------+ | 20090702 | 35 | 5400 | | 20090703 | 25 | 2500 | | 20090704 | 50 | 13000 | +------------+-------------+---------------------+ 3 rows in set (0.00 sec)
データ構造
アルゴリズム
- Javaを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計 (リスト＆マップ編)
- Javaを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計 (リスト＆ビーン編)
- PHPを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計
- VBAを使うなら理解しておきたいアルゴリズム - 抽出・結合・集計
- Javascriptを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計
- SQLを使うなら理解しておきたいアルゴリズム？(というか、select文の書き方) - where・order by・join・group by
- Bashを使うなら理解しておきたいアルゴリズム - 抽出・ソート・結合・集計
