-- テーブルを作成するCREATE文（この行はコメント行）
CREATE TABLE テーブル名
(
    1つ目の列名 型 NOT NULL, -- カンマでつなげて書いていく
	2つ目の列名 型,          -- NULLを許可する場合は何も書かない(NOTNULLだけ書く）
    CONSTRAINT 主キーの名前 PRIMARY KEY (主キーを構成する列)  --主キーの書き方
);


--例：部署マスタ（department）を作成するCREATE文
CREATE TABLE department
(
    department_code character varying(10) NOT NULL,         -- 部署コードを10文字で用意
	department_name character varying(100),                 -- 部署名を文字100文字で用意
    CONSTRAINT pk_department PRIMARY KEY (department_code)  -- 部署コードで主キーとする
);


-- 主キーが複数の場合の例
CREATE TABLE テーブル名
(
   （略）
    CONSTRAINT 主キーの名前 PRIMARY KEY (主キーを構成する列1 , 列2 , 列3 , ・・・)
);

--ビューを作成する
CREATE OR REPLACE VIEW ビュー名 AS
SELECT xxx , xxx, … FROM テーブル名; 

--ビューを作成する例1
create view employee_vw as 
select employee_code , employee_name , department_code from employee;

--ビューを作成する例2(列の指定はアスタリスクでも可)
create view employee_vw as select * from employee;

--ビューを削除する
DROP VIEW ビュー名;

--ビューを削除する例
DROP VIEW employee_vw;

--ビューへのSELECT権限を付与する
GRANT SELECT ON ビュー名 TO ロール名;     --SELECT権限を付与する

--ビュー「employee_vw」へのSELECT権限を付与する例
GRANT SELECT ON employee_vw TO postgres;

--ビューへのSELECT権限を削除する
REVOKE ALL PRIVILEGES ON ビュー名 FROM ロール名;  --SELECT権限を削除する

--ビュー「employee_vw」へのSELECT権限を削除する例
REVOKE SELECT ON employee_vw FROM postgres;

列名 型 GENERATED ALWAYS AS 式 STORED

CREATE TABLE test
(   ・・・
    height numeric,   -- 身長
    weight numeric,   -- 体重
    --生成列 : BMI（ 体重　÷　身長（ｍ）の2乗）の作成
    bmi numeric(3,1) GENERATED ALWAYS AS (weight / ((height / 100) * (height / 100))) STORED,
    ・・・
)

CREATE TABLE test
(   ・・・ , 
    --生成列の例1：列「category」による場合分け
    hoge text GENERATED ALWAYS AS (    -- この列の定義はここから
    CASE 
    WHEN category '1' THEN '一般職'
    WHEN category '2' THEN '管理職'
    WHEN category '3' THEN '派遣'
    WHEN category '4' THEN '再雇用' 
    ELSE 'それ以外' END
    ) STORED                          -- ここまでです
    
    , ・・・
    , 

    --生成列の例2 : 誕生日によるクラス分け
    class_name text GENERATED ALWAYS AS
    (
     CASE 
     WHEN birthday between '2016/4/1' and '2017/3/31' THEN 'Aクラス' 
     WHEN birthday between '2017/4/1' and '2018/3/31' THEN 'Bクラス' 
     WHEN birthday between '2018/4/1' and '2019/3/31' THEN 'Cクラス' 
     WHEN birthday between '2019/4/1' and '2020/3/31' THEN 'Dクラス' 
     WHEN birthday between '2020/4/1' and '2021/3/31' THEN 'Eクラス' 
     ELSE '対象外' END) STORED

    , ・・・
)

--列を追加する構文
ALTER TABLE テーブル名 add 列名 型;

--例：「m_school」テーブルに列「address」を追加する
ALTER TABLE m_school add address character varying(100);


--列を削除する構文
ALTER TABLE テーブル名 DROP COLUMN 列名;

--例：「m_school」テーブルの列「address」を削除する
ALTER TABLE m_school DROP COLUMN address;

--桁数を変更する
ALTER TABLE テーブル名 ALTER COLUMN 列名 型;

--例：「student」テーブルの列「height」の定義をnumeric(3,1)に変更する
ALTER TABLE student ALTER COLUMN height TYPE numeric(3,1);

--データベースへコメントを追加する
COMMENT ON DATABASE データベース名 IS 'コメントです'; 

--テーブルへコメントを追加する
COMMENT ON TABLE テーブル名 IS 'コメントです';

--列へコメントを追加する
COMMENT ON COLUMN テーブル名.列名 IS 'コメントです';

--データベースのコメントを削除する
COMMENT ON DATABASE データベース名 IS NULL;

--テーブルのコメントを削除する
COMMENT ON TABLE テーブル名 IS NULL;

--列へコメントのコメントを削除する
COMMENT ON COLUMN テーブル名.列名 IS NULL;

--年月日時分の書式
select to_char(now(),'YYYY/MM/DD HH24:MI:SS'); --2020/04/01 22:34:56

--年月日の書式
select to_char(now(),'YYYYMMDD');       --20200401
select to_char(now(),'YYYY/MM/DD');     --2020/04/01
select to_char(now(),'YYYY.MM.DD');     --2020.04.01
select to_char(now(),'YYYY年MM月DD日');  --2020年04月01日

--月日の書式
select to_char(now(),'MMDD');           --0401
select to_char(now(),'MM/DD');          --04/01
select to_char(now(),'MM.DD');          --04.01
select to_char(now(),'MM月DD日');       --04月01日

--時刻の書式
select to_char(now(), 'HH:MI:SS');      --10:34:56（12時間表記）
select to_char(now(), 'HH24:MI:SS');    --22:34:56（24時間表記）
select to_char(now(), 'HH24時MI分SS秒'); --22時34分56秒

--今日の日付の取得
select current_date;      --"2020-10-01"

--現在時刻の取得（時刻のみ）
select current_time;      --"22:00:00.000000+09"

--現在日時の取得   ※now()とcurrent_timestampの違いはありません
select now();             --"2020-10-01 22:00:00.000000+09"
select current_timestamp; --"2020-10-01 22:00:00.000000+09"

--'YYYY/MM/DD'形式で表示する
select to_char(now(), 'YYYY/MM/DD');  --2020/10/01

--'YYYY/MM/DD HH24:MI:SS'形式で表示する
select to_char(now(), 'YYYY/MM/DD HH24:MI:SS'); --2020/10/01 12:34:56

--ランダム値を生成する（引数は必要ありません）
random() 

--範囲を指定したランダム値を生成する方法
--最小値と最大値のところに値を当てはめて実行して下さい
select round(( random() * (最小値 - 最大値) )::numeric, 0) + 最大値;

select * from random(); --結果例:0.13224（実行する度異なる値が生成）

--範囲指定：最小値=1 , 最大値=100
select round(( random() * (1 - 100) )::numeric, 0) + 100; --結果例:77

--範囲指定：最小値=1 , 最大値=10,000
select round(( random() * (1 - 10000) )::numeric, 0) + 10000; --結果例:7000

--余りを計算する構文
mod( 数値，割る数 ) 

--余りを求める
select mod(10,3);  -- 1（ 10 ÷ 3 の余り ）
select mod(5,3);   -- 2（ 5 ÷ 3 の余り ）
select mod(6,3);   -- 0（ 6 ÷ 3 の余り ）

--偶数の時、奇数の時と処理を分ける例
select case when mod(2,1) = 0 then '偶数の時の処理' 
else '奇数の時の処理' end;

--絶対値を取得する
abs( 数値 ) 

select * from abs(5);      -- 5
select * from abs(-5);     -- 5

--小数でも特に動作は変わりません
select * from abs(3.4);    -- 3.4
select * from abs(-3.4);   -- 3.4
select * from abs(3.45);   -- 3.45
select * from abs(-3.45);  -- 3.45