This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 3 years have passed since last update.

SQL 第2版ゼロからはじめるデータベース操作 6章

Last updated at 2020-06-29Posted at 2020-06-13

関数、述語、CASE式

算術関数

・　+(足し算)
・　-(引き算)
・　*(掛け算)
・　/(割り算)

まずはデータの用意

 create table samplemath
(m numeric(10,3),
 n integer,
 p integer);

begin transaction;

insert into samplemath(m,n,p) values (500,0,null);
INSERT 0 1

insert into samplemath values              
(-180,0,null),
(null,null,null),
(null,7,3),
(null,5,2),
(null,4,null),
(8,null,3),
(2.27,1,null),
(5.55,2,null),
(null,1,null),
(8.76,null,null);
INSERT 0 10

commit;

select * from samplemath;
    m     | n | p 
----------+---+---
  500.000 | 0 |  
 -180.000 | 0 |  
          |   |  
          | 7 | 3
          | 5 | 2
          | 4 |  
    8.000 |   | 3
    2.270 | 1 |  
    5.550 | 2 |  
          | 1 |  
    8.760 |   |  
(11 rows)

絶対値 ABS(absolute value)

select m, abs(m) as abs_col
from samplemath;
--NULLの結果はNULLになる
    m     | abs_col 
----------+---------
  500.000 | 500.000
 -180.000 | 180.000
          |        
          |        
          |        
          |        
    8.000 |   8.000
    2.270 |   2.270
    5.550 |   5.550
          |        
    8.760 |   8.760
(11 rows)

###余剰 MOD(module)

select n, p, mod(n, p) as mod_col
from samplemath;

n | p | mod_col 
---+---+---------
 0 |   |        
 0 |   |        
   |   |        
 7 | 3 |       1
 5 | 2 |       1
 4 |   |        
   | 3 |        
 1 |   |        
 2 |   |        
 1 |   |        
   |   |        
(11 rows)

###四捨五入 ROUND

--nに入る値で、小数点以下が決まる。
 select m,n,round(m,n)as roud_col
from samplemath;
    m     | n | roud_col 
----------+---+----------
  500.000 | 0 |      500 --小数点以下なし
 -180.000 | 0 |     -180 --小数点以下なし
          |   |         
          | 7 |         
          | 5 |         
          | 4 |         
    8.000 |   |         
    2.270 | 1 |      2.3 --小数点以下1
    5.550 | 2 |     5.55 --小数点以下2
          | 1 |         
    8.760 |   | 

 select m,n,round(m,10)as roud_col --小数点以下10
from samplemath;
    m     | n |    roud_col     
----------+---+-----------------
  500.000 | 0 |  500.0000000000
 -180.000 | 0 | -180.0000000000
          |   |                
          | 7 |                
          | 5 |                
          | 4 |                
    8.000 |   |    8.0000000000
    2.270 | 1 |    2.2700000000
    5.550 | 2 |    5.5500000000
          | 1 |                
    8.760 |   |    8.7600000000
(11 rows)

文字列関数

まずは準備

 create table samplestr
(str1 varchar(40),
 str2 varchar(40),
 str3 varchar(40));
CREATE TABLE

 begin transaction;
BEGIN

 insert into samplestr values ('あいう','えお',null),
 ('abc','def',null),
 ('山田','太郎','です'),
 ('aaa',null,null),
 (null,'あああ',null),
 ('@!#$%',null,null),
 ('ABC',null,null),
 ('aBC',null,null),
 ('abc太郎','abc','ABC'),
 ('abcdefabc','abc','ABC'),
 ('ミックマック','ッ','っ');
INSERT 0 11

commit;
COMMIT

select * from samplestr;
     str1     |  str2  | str3 
--------------+--------+------
 あいう       | えお   | 
 abc          | def    | 
 山田         | 太郎   | です
 aaa          |        | 
              | あああ | 
 @!#$%        |        | 
 ABC          |        | 
 aBC          |        | 
 abc太郎      | abc    | ABC
 abcdefabc    | abc    | ABC
 ミックマック | ッ     | っ
(11 rows)

###連結 ||

select str1,str2,
str1 || str2 as str_concat
from samplestr;
     str1     |  str2  |   str_concat   
--------------+--------+----------------
 あいう       | えお   | あいうえお
 abc          | def    | abcdef
 山田         | 太郎   | 山田太郎
 aaa          |        | 
              | あああ | 
 @!#$%        |        | 
 ABC          |        | 
 aBC          |        | 
 abc太郎      | abc    | abc太郎abc
 abcdefabc    | abc    | abcdefabcabc
 ミックマック | ッ     | ミックマックッ
(11 rows)

3つの文字の連結

select str1, str2, str3,
 str1 || str2 || str3 as str_concat
 from samplestr
 where str1 = '山田';
 str1 | str2 | str3 |  str_concat  
------+------+------+--------------
 山田 | 太郎 | です | 山田太郎です
(1 row)

###文字列長LENGTH

select str1, length(str1) as len_str
 from samplestr;
     str1     | len_str 
--------------+---------
 あいう       |       3
 abc          |       3
 山田         |       2
 aaa          |       3
              |        
 @!#$%        |       5
 ABC          |       3
 aBC          |       3
 abc太郎      |       5
 abcdefabc    |       9
 ミックマック |       6
(11 rows)

###小文字化LOWER

select str1,
 lower(str1) as low_str
 from samplestr
 where str1 in ('ABC','aBC','abc','山田');
 str1 | low_str 
------+---------
 abc  | abc
 山田 | 山田
 ABC  | abc
 aBC  | abc
(4 rows)

###大文字化UPPER

select str1,
upper(str1) as low_str
from samplestr
;
     str1     |   low_str    
--------------+--------------
 あいう       | あいう
 abc          | ABC
 山田         | 山田
 aaa          | AAA
              | 
 @!#$%        | @!#$%
 ABC          | ABC
 aBC          | ABC
 abc太郎      | ABC太郎
 abcdefabc    | ABCDEFABC
 ミックマック | ミックマック
(11 rows)

###文字列の置換REPLACE
REPLACE(対象文字列,置換前の文字列,置換後の文字列)

select str1,str2,str3,
replace(str1,str2,str3) as rep_str
from samplestr;
     str1     |  str2  | str3 |   rep_str    
--------------+--------+------+--------------
 あいう       | えお   |      | 
 abc          | def    |      | 
 山田         | 太郎   | です | 山田
 aaa          |        |      | 
              | あああ |      | 
 @!#$%        |        |      | 
 ABC          |        |      | 
 aBC          |        |      | 
 abc太郎      | abc    | ABC  | ABC太郎
 abcdefabc    | abc    | ABC  | ABCdefABC
 ミックマック | ッ     | っ   | ミっクマっク
(11 rows)

###文字列の切り出しSUBSTRING
切り出し開始位置の数え方は左から何文字目
forに数字は文字数。指定がない場合はfrom以降全ての文字。

select str1,
substring(str1 from  3 for 2) as sub_str --３文字目から2文字
from samplestr;
     str1     | sub_str 
--------------+---------
 あいう       | う
 abc          | c
 山田         | 
 aaa          | a
              | 
 @!#$%        | #$
 ABC          | C
 aBC          | C
 abc太郎      | c太
 abcdefabc    | cd
 ミックマック | クマ
(11 rows)

select str1,
substring(str1 from  3 ) as sub_str　--3文字目から全て
from samplestr;
     str1     | sub_str  
--------------+----------
 あいう       | う
 abc          | c
 山田         | 
 aaa          | a
              | 
 @!#$%        | #$%
 ABC          | C
 aBC          | C
 abc太郎      | c太郎
 abcdefabc    | cdefabc
 ミックマック | クマック
(11 rows)

###現在日付 CURRENT_DATE

select current_date;
 current_date 
--------------
 2020-04-18
(1 row)

###現在時間CRENT_TIME

select current_time;
    current_time    
--------------------
 00:44:20.808903+09
(1 row)

###現在日時CURRENT_TIMESTAMP

select current_timestamp;
       current_timestamp       
-------------------------------
 2020-04-18 00:52:17.644223+09

###日付要素の切り出しEXRACT
EXRACT(日付要素 FROM 日付)

select current_timestamp,
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;
       current_timestamp       | year | month | day | hour | minute |  second   
-------------------------------+------+-------+-----+------+--------+-----------
 2020-04-18 00:59:45.901865+09 | 2020 |     4 |  18 |    0 |     59 | 45.901865
(1 row)

##変換変数

###型変換CAST
CAST(変換前の値 AS 変換するデータ型)

--string→integer
select cast('0001' as integer) as int_col;
 int_col 
---------
       1
(1 row)

--string→date
 select cast('2009-12-14' as date) as date_col;
  date_col  
------------
 2009-12-14
(1 row)

###NULLを値へ変換するCOALESCE

select coalesce(null, 1) as col_1,
coalesce(null,'test',null) as col_2,
coalesce(null,null,'2009-11-01') as col_3;
 col_1 | col_2 |   col_3    
-------+-------+------------
     1 | test  | 2009-11-01
(1 row)


select str2,coalesce(str2, 'nullです')
from samplestr;
 str2  | coalesce 
--------+----------
 えお   | えお
 def    | def
 太郎   | 太郎
        | nullです
 あああ | あああ
        | nullです
        | nullです
        | nullです
 abc    | abc
 abc    | abc
 ッ     | ッ
(11 rows)

##述語
述語の特徴は戻り値が文字列や日付ではなく全て真理値（true/false/unknown)

###LIKE述語
文字列の部分一致検索をする場合はLIKE述語を使う。
また部分一致には大きく分けて前方一致、中間一致、後方一致の3つある。

まずはデータの準備

 create table samplelike
(strcol varchar(6) not null,
primary key (strcol));

begin transaction;
BEGIN

insert into samplelike values ('abcddd'),
('dddabc'),
('abdddc'),
('abcdd'),
('ddabc'),
('abddc');
INSERT 0 6

commit;
COMMIT

％は「０文字以上の任意の文字列」という意味

--前方一致
 select * 
from samplelike
where strcol like 'ddd%';
 strcol 
--------
 dddabc
(1 row)

ーー後方一致
select * 
from samplelike
where strcol like '%ddd';
 strcol 
--------
 abcddd
(1 row)

--中間一致
select * 
from samplelike
where strcol like '%ddd%';
 strcol 
--------
 abcddd
 dddabc
 abdddc
(3 rows)

%の代わりに「_」を使うこともできる。
意味は任意の1文字

select * 
from samplelike
where strcol like 'abc__';
 strcol 
--------
 abcdd
(1 row)

postgres=# 
postgres=# select * 
from samplelike
where strcol like 'abc___';
 strcol 
--------
 abcddd
(1 row)

###BETWEEN
範囲検索が行える
引数を3つ使うのが特徴

 select shohin_mei, hanbai_tanka
from shohin
where hanbai_tanka between 100 and 1000;
  shohin_mei  | hanbai_tanka 
--------------+--------------
 Tシャツ      |         1000
 穴あけパンチ |          500
 フォーク     |          500
 おろしがね   |          880
 ボールペン   |          100
(5 rows)
--100と01000両端も含む

100と1000を含まないようにするのは以下

select shohin_mei, hanbai_tanka
from shohin
where hanbai_tanka > 100
and   hanbai_tanka < 1000;

###IS NULL、IS　NOT NULL
NULLかNULLじゃないか
NULLの行を選択するにはIS NULLを使う

select shohin_mei, shiire_tanka
from shohin
where shiire_tanka is null;
 shohin_mei | shiire_tanka 
------------+--------------
 フォーク   |             
 ボールペン |             
(2 rows)

逆にNULLじゃないものを選択したい場合はIS NOT NULLを使う

select shohin_mei, shiire_tanka
from shohin
where shiire_tanka is not null;
   shohin_mei   | shiire_tanka 
----------------+--------------
 Tシャツ          |          500
 穴あけパンチ      |          320
 カッターシャツ      |        2800
 包丁            |         2800
 圧力鍋          |         5000
 おろしがね        |         790
(6 rows)

###IN
ORの便利な省略系が使える
まずはORを見てみる

 select shohin_mei, shiire_tanka
 from shohin
 where shiire_tanka = 320
 or shiire_tanka = 500
 or shiire_tanka = 5000;
  shohin_mei  | shiire_tanka 
--------------+--------------
 Tシャツ      |          500
 穴あけパンチ |          320
 圧力鍋       |         5000
(3 rows)

上記をINを使うと以下になる

select shohin_mei, shiire_tanka
from shohin
where shiire_tanka in ( 320,500,5000);
  shohin_mei  | shiire_tanka 
--------------+--------------
 Tシャツ      |          500
 穴あけパンチ |          320
 圧力鍋       |         5000
(3 rows)

--逆はNOT IN　を使ってかける。ただしNULLのデータを選択できない。
 select shohin_mei, shiire_tanka
from shohin
where shiire_tanka  not in ( 320,500,5000);
   shohin_mei   | shiire_tanka 
----------------+--------------
 カッターシャツ |         2800
 包丁           |         2800
 おろしがね     |          790
(3 rows)

###INとサブクエリ
まずはデータの準備

create table tenposhohin
(tenpo_id char(4) not null,
tenpo_mei varchar(200) not null,
shohin_id char(4) not null,
suryo integer not null,
primary key (tenpo_id, shohin_id));
CREATE TABLE

begin transaction;
BEGIN

insert into tenposhohin (tenpo_id,tenpo_mei, shohin_id, suryo)
values 
('000A','東京','0001',30),
('000A','東京','0002',50),
('000A','東京','0003',15),
('000B','名古屋','0002',30),
('000B','名古屋','0003',120),
('000B','名古屋','0004',20),
('000B','名古屋','0006',10),
('000B','名古屋','0007',40),
('000C','大阪','0003',20),
('000C','大阪','0004',50),
('000C','大阪','0006',90),
('000C','大阪','0007',70),
('000D','福岡','0001',100);
INSERT 0 13

commit;
COMMIT

たとえば大阪の店舗が販売している商品の販売単価を表示しようとおもったら、

--まずは大阪の店舗が扱う商品IDを取得
 select shohin_id
 from tenposhohin
 where tenpo_id = '000C';
 shohin_id 
-----------
 0003
 0004
 0006
 0007
(4 rows)

--上記の商品IDの販売単価を探すという手順になる
select shohin_mei, hanbai_tanka
from shohin
where shohin_id = '0003' 
or   shohin_id = '0004' 
or   shohin_id = '0006' 
or   shohin_id = '0007';
  shohin_mei   | hanbai_tanka 
----------------+--------------
 カッターシャツ |         4000
 包丁           |         3000
 フォーク       |          500
 おろしがね     |          880
(4 rows)

それをサブクエリとINを使えば以下のように表現できる

select shohin_mei,hanbai_tanka
from shohin
where shohin_id in (
                     select  shohin_id
                     from tenposhohin
                     where tenpo_id = '000C'
);
   shohin_mei   | hanbai_tanka 
----------------+--------------
 カッターシャツ |         4000
 包丁           |         3000
 フォーク       |          500
 おろしがね     |          880
(4 rows)

###NOT INを使ったサブクエリ
以下は東京の店舗以外で販売している商品以外の商品名と販売単価を表示するという意味。
東京で販売している商品はTシャツ'0001',穴あけパンチ'0002',カッターシャツ'0003'なので、
それ以外の商品名と販売単価を表示する

select shohin_mei,hanbai_tanka
from shohin
where shohin_id not in (select  shohin_id
                        from tenposhohin
                        where tenpo_id = '000A'
);
 shohin_mei | hanbai_tanka 
------------+--------------
 包丁       |         3000
 圧力鍋     |         6800
 フォーク   |          500
 おろしがね |          880
 ボールペン |          100
(5 rows)

###EXISTS
ある条件に合致するレコードの存在有無を調べる
たとえば、INでも求めた大阪に置いてある商品名と販売単価を再度EXISTSを使ってもとめる
特徴は引数が１つなので、相関サブクエリを常に指定する。

--s
select shohin_id, shohin_mei, hanbai_tanka
from shohin;
 shohin_id | shohin_mei | hanbai_tanka 
-----------+------------+--------------
 0001      | Tシャツ      |         1000
 0002      | 穴あけパンチ  |          500
 0003      | カッターシャツ  |         4000
 0004      | 包丁        |         3000
 0005      | 圧力鍋       |         6800
 0006      | フォーク      |          500
 0007      | おろしがね    |          880
 0008      | ボールペン    |          100
(8 rows)
--ts
 select * 
 from tenposhohin ;
 tenpo_id | tenpo_mei | shohin_id | suryo 
----------+-----------+-----------+-------
 000A     | 東京      | 0001      |    30
 000A     | 東京      | 0002      |    50
 000A     | 東京      | 0003      |    15
 000B     | 名古屋    | 0002      |    30
 000B     | 名古屋    | 0003      |   120
 000B     | 名古屋    | 0004      |    20
 000B     | 名古屋    | 0006      |    10
 000B     | 名古屋    | 0007      |    40
 000C     | 大阪      | 0003      |    20
 000C     | 大阪      | 0004      |    50
 000C     | 大阪      | 0006      |    90
 000C     | 大阪      | 0007      |    70
 000D     | 福岡      | 0001      |   100
(13 rows)

select shohin_mei, hanbai_tanka
from shohin as s
where exists (select * 
             from tenposhohin as ts
             where ts.tenpo_id = '000C'
             and ts.shohin_id = s.shohin_id );
   shohin_mei   | hanbai_tanka 
----------------+--------------
 カッターシャツ |         4000
 包丁           |         3000
 フォーク       |          500
 おろしがね     |          880
(4 rows)

NOTを使って書くこともできる。

select shohin_mei, hanbai_tanka
from shohin as s
where not exists (select * 
                  from tenposhohin as ts
                  where ts.tenpo_id = '000C'
                  and ts.shohin_id = s.shohin_id );
  shohin_mei  | hanbai_tanka 
--------------+--------------
 Tシャツ      |         1000
 穴あけパンチ |          500
 圧力鍋       |         6800
 ボールペン   |          100
(4 rows)

CASE

CASE式は場合分けをするときに使う。
つまり条件分岐である。
CASE式は２種類あり、単純CASE式と検索CASE式がある。
CASE WHEN 評価式　THEN 式　
　　 WHEN 評価式　THEN 式　
WHEN 評価式　THEN 式　
:
ELSE　式
END

WHEN句の評価式は戻り値が真理値
もし評価が真になれば、THEN句で指定された式が戻されて、CASE式全体が終了する。
評価が偽なら次のWHEN句の評価に移る。
最後のWHEN句まで繰り返しても真にならなかっった場合は、
ELSEで指定された式がもどされて終了する。
　

--検索case式 検索case式の場合、様々な条件式をかける。
select shohin_mei,
      case when shohin_bunrui = '衣服' then 'A:' || shohin_bunrui
           when shohin_bunrui = '事務用品' then 'B:' || shohin_bunrui
           when shohin_bunrui = 'キッチン用品' then 'C:' || shohin_bunrui
           else null
           end as abc_shohin_bunrui
from shohin;
--else null は最後にnullを返すという意味
--else nullは省略可能で自動的にelse nullになる
   shohin_mei   | abc_shohin_bunrui 
----------------+-------------------
 Tシャツ        | A:衣服
 穴あけパンチ   | B:事務用品
 カッターシャツ | A:衣服
 包丁           | C:キッチン用品
 圧力鍋         | C:キッチン用品
 フォーク       | C:キッチン用品
 おろしがね     | C:キッチン用品
 ボールペン     | B:事務用品
(8 rows)


 --単純case式
select shohin_mei,
 case shohin_bunrui
 when '衣服' then 'A:' || shohin_bunrui
 when '事務用品' then 'B:' || shohin_bunrui
 when 'キッチン用品' then 'C:' || shohin_bunrui
 else null
 end as abc_shohin_bunrui
 from shohin;
   shohin_mei   | abc_shohin_bunrui 
----------------+-------------------
 Tシャツ        | A:衣服
 穴あけパンチ   | B:事務用品
 カッターシャツ | A:衣服
 包丁           | C:キッチン用品
 圧力鍋         | C:キッチン用品
 フォーク       | C:キッチン用品
 おろしがね     | C:キッチン用品
 ボールペン     | B:事務用品
(8 rows)

CASE式は式なので、たとえば以下のようなにGROUP BY句のように行でしか表せないものを、列で表すことができる。

select shohin_bunrui, sum(hanbai_tanka) as sum_tanka
 from shohin
 group by shohin_bunrui;
 shohin_bunrui | sum_tanka 
---------------+-----------
 キッチン用品  |     11180
 衣服          |      5000
 事務用品      |       600
(3 rows)

select 
sum( case when shohin_bunrui = '衣服' 
     then hanbai_tanka else 0 end) as sum_tanka_ihuku,
sum(case when shohin_bunrui = 'キッチン用品'
     then hanbai_tanka else 0 end) as sum_tanka_kitchen,
sum(case when shohin_bunrui = '事務用品'
    then hanbai_tanka else 0 end ) as sum_tanka_jimu
from shohin;
 sum_tanka_ihuku | sum_tanka_kitchen | sum_tanka_jimu 
-----------------+-------------------+----------------
            5000 |             11180 |            600
(1 row)

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

SQL 第2版 ゼロからはじめるデータベース操作 6章