The Sixwish project

SQL - Education

関数と演算子の一部

標準SQLとなっていないものも混ざるので

SQLを計算機として使う

テーブルを指定しなければ動かないというものではない。ということの説明と、計算機として使う方法を見ていきましょう。

lain=> Select 1 + 1;
 ?column?
----------
        2
(1 row)

lain=>

と言うように、From句を指定しなくても何とかなります。
今回はこの方法を使いつつ、関数や演算子の使い方を説明してきます。

文字列関数

比較的良く使う。プログラム側で行うより効率が良い場合もあるので、使い方を憶えておくとよい。

文字列結合、部分文字列の取り出し、文字の置換、文字数をそれぞれ。

lain=> Select '文字' || '列' as result;
 result
--------
 文字列
(1 row)

lain=> Select substring('123456789十123456789' from 1 for 11) as result;
    result
--------------
 123456789十1
(1 row)

lain=> Select overlay('123456789十123456789' placing '壱' from 1 for 1) as result;
        result
-----------------------
 壱23456789十123456789
(1 row)

lain=> Select character_length('123456789十123456789') as result;
--------
     19
(1 row)

lain=>

というわけで、標準SQLで使う場合の書き方が上記。
PostgeSQL方言では、overlayは、replaceを使います。なぜならそっちの方が高機能だから……

日付/時刻関数と演算子

日付、時間が絡む事は多いので、知っていると便利。良く使う。
日付計算は言語によってはサポートしていない場合があるので、使い方を憶えておくか、こういう手法があることを知っているとよい。

時間の計算方法から見ていきます。

lain=> Select date '2009-06-01' as result;
   result
------------
 2009-06-01
(1 row)

lain=> Select date '2009-06-01' + interval '1 month' as result;
       result
---------------------
 2009-07-01 00:00:00
(1 row)

lain=> Select date '2009-06-01' - interval '1 month' as result;
       result
---------------------
 2009-05-01 00:00:00
(1 row)

lain=> Select date '2009-06-01' - 12 * interval '1 month' as result;
       result
---------------------
 2008-06-01 00:00:00
(1 row)

lain=>

足したり引いたり、掛けてみたり。
intervalは、月指定だけではなく、year、days、hour、timeとそれぞれあるので、必要に応じて使い分ける事。

続いて関数。
憶えておく必要があるのは、現在時刻を取得する関数くらいで、あとは参考程度で十分。

lain=> Select current_date as result;
   result
------------
 2009-05-31
(1 row)

lain=> Select current_time as result;
       result
--------------------
 16:14:43.702939+09
(1 row)

lain=> Select current_timestamp as result;
            result
------------------------------
 2009-05-31 16:13:28.41296+09
(1 row)

lain=>

表示しているのはこれを書いている時間なので、実際に実行した場合の結果とは変わります。

データ型書式設定関数

日付から文字型、文字型から計算しやすいように日付型にしたりという場面で使用することが多いと思う関数。
型変換しないと計算できない場合などもあるので、その辺りを意識しながら使うことになります。

lain=> Select to_date('20090601', 'YYYYMMDD') as result;
   result
------------
 2009-06-01
(1 row)

lain=> Select to_date('20090601', 'YYYYDDMM') as result;
   result
------------
 2009-01-06
(1 row)

lain=> Select to_char(current_timestamp , 'YYYY-MM-DD') as result;
   result
------------
 2009-05-31
(1 row)

lain=>

集約関数

簡単な使い方だけ説明します。
とりあえず、count（件数）、max（最大）、min（最小）、avg（平均）を例を見ながら。
sum（合計）もあるのですが、ちょうど良いテーブルが無いのでお預けと。

lain=> Select count(materielcode) From materielmaster;
 count
-------
    15
(1 row)

lain=> Select max(employeecode) From employeemaster;
 max
-----
 120
(1 row)

lain=> Select min(employeecode) From employeemaster;
 min
-----
   1
(1 row)

lain=> Select avg(materieldivcode) From materieldivmaster;
        avg
--------------------
 3.0000000000000000
(1 row)

lain=>

一番最期の例はあまりにも意味が無いのですが、検算しやすいという理由だけです。

演習

問題１
employeemaster を下記のように表示してください。 
lain=> SQL QUERY
 employeecode |    name
--------------+-------------
            1 | 演習 一号
            2 | 演習 二号
            3 | 演習 三号
            4 | 演習 四号
           11 | 宮地 勤
           12 | 鈴木 ヒカル
           13 | 佐藤 真一
           14 | 岡部 幸平
（中略）
          119 | 佐藤 里奈
          120 | 鈴木 惇
(34 rows)

lain=>
問題２
materielmaster を下記のように表示してください。registdateの日付は、登録されているデータを表示してください。 
lain=> SQL QUERY
 materielcode | materielmaker | modelno  | productno | registdate
--------------+---------------+----------+-----------+------------
            1 | DoLL          | INSPIRON | D0001     | 2008-06-11
            2 | DoLL          | INSPIRON | D0002     | 2008-06-11
            3 | DoLL          | INSPIRON | D0003     | 2008-06-11
            4 | DoLL          | INSPIRON | D0004     | 2008-06-11
            5 | DoLL          | INSPIRON | D0005     | 2008-06-11
            6 | DoLL          | VOSTRO   | DD001     | 2008-06-11
            7 | DoLL          | VOSTRO   | DD002     | 2008-06-11
            8 | DoLL          | VOSTRO   | DD003     | 2008-06-11
            9 | DoLL          | VOSTRO   | DD004     | 2008-06-11
           10 | DoLL          | VOSTRO   | DD005     | 2008-06-11
           11 | oIZO          | S2100    | E0001     | 2008-06-11
           12 | oIZO          | S2100    | E0002     | 2008-06-11
           13 | oIZO          | S2100    | E0003     | 2008-06-11
           14 | oIZO          | S2100    | E0004     | 2008-06-11
           15 | oIZO          | S2100    | E0005     | 2008-06-11
(15 rows)

lain=>