sql基礎

psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
quit

BEGIN;
ROLLBACK;

BEGIN;
SAVEPOINT my_savepoint;
ROLLBACK TO my_savepoint;
COMMIT;

CREATE TABLE STORE(
  ID INTEGER PRIMARY KEY,
  NAME VARCHAR(30) NOT NULL
);

CREATE TABLE IF NOT EXISTS PRODUCT (
  ID INTEGER ,
  STORE_ID INTEGER REFERENCES STORE(ID) ON DELETE CASCADE, 
  PRICE NUMERIC(10,4) CHECK (PRICE > 0) NOT NULL,
  NAME VARCHAR(30) UNIQUE,
  DESCRIPTION TEXT,
  SOLD_DATE TIMESTAMP,
  PRIMARY KEY(ID)
);

INSERT INTO STORE VALUES (1001,'EAST');

INSERT INTO PRODUCT 
  (ID, STORE_ID, PRICE, NAME, SOLD_DATE) 
  VALUES 
  (1001, 1001, 10000, 'wine', NOW()),
  (1002, 1001, 20000, 'coke', NOW());

UPDATE PRODUCT
SET PRICE = 11000,
    NAME = 'limited_wine'
WHERE ID = 1001;

DELETE FROM PRODUCT WHERE ID = 1002;

$ createuser -U postgres -P user1
$ dropuser -U postgres user1 
ALTER USER user1 WITH PASSWORD 'password';
ALTER USER user1 WITH SUPERUSER;
ALTER USER user1 WITH NOSUPERUSER;

CREATE DATABASE user1 WITH OWNER user1;
DROP DATABASE user1;

CREATE SCHEMA myschema AUTHORIZATION user1;
DROP SCHEMA myschema;

GRANT ALL ON PRODUCT TO USER1;
GRANT ALL ON ALL TABLES IN SCHEMA PUBLIC TO USER1;
REVOKE ALL ON PRODUCT FROM USER1;

-- 一時テーブルとしてコピーテーブルを作成。テーブル定義からレコードまで全部一緒
-- 一時テーブルはそのセッション中しか存在できない
SELECT * INTO TEMPORARY TABLE TMP_TABLE FROM PRODUCT;
-- ↓これだとテーブル定義（制約）が抜け落ちる。
--CREATE TABLE PRODUCT_COPY AS SELECT * FROM PRODUCT;

-- テーブル定義が同じなコピーテーブルを作成。
CREATE TABLE PRODUCT_COPY (LIKE PRODUCT INCLUDING ALL);

-- SELECT結果をINSERTする。
INSERT INTO PRODUCT_COPY
  (ID, STORE_ID, PRICE, NAME, SOLD_DATE) 
SELECT
  ID, STORE_ID, PRICE, NAME, SOLD_DATE
FROM
    PRODUCT;

-- 設定一覧
SELECT * FROM PG_CATALOG.PG_SETTINGS;
-- ユーザ一覧
SELECT * FROM PG_CATALOG.PG_ROLES;
-- DB一覧
SELECT * FROM PG_CATALOG.PG_DATABASE;
-- テーブル一覧
SELECT * FROM INFORMATION_SCHEMA.TABLES;
-- カラムからテーブルを検索する
SELECT * FROM INFORMATION_SCHEMA.COLUMNS;

-- バージョン、データベース名、ユーザ名
SELECT VERSION(), CURRENT_DATABASE(), CURRENT_USER;

-- INSERT INTO STORE VALUES (1001,'EAST');
INSERT INTO STORE VALUES (1002, 'WEST');

INSERT INTO PRODUCT 
  (ID, STORE_ID, PRICE, NAME, SOLD_DATE) 
  VALUES 
--   (1001, 1001, 10000, 'limited_wine', NOW()),
  (1002, 1001, 20000, 'white_wine', '2020-09-01',
  (1003, 1002, 30000, 'red_wine', '2020-10-01',
  (1004, 1002, 40000, 'red_rare_wine', '2020-10-01';
  (1005, 1002, 50000, NULL, '2020-10-01';

-- 集計結果を絞りたいときはHAVING (集計関数が使える)
SELECT STORE_ID, AVG(PRICE) AS AVG_PRICE 
FROM PRODUCT 
GROUP BY STORE_ID
HAVING AVG(PRICE) > 25000;

-- 集計前データを絞りたいときはWHERE (集計関数が使えない)
SELECT STORE_ID, AVG(PRICE) AS AVG_PRICE 
FROM PRODUCT 
WHERE PRICE > 30000
GROUP BY STORE_ID;

-- どれにも一致しないときは最後のWHEN句になる
SELECT 
  ID,
  CASE WHEN PRICE > 20000 THEN 20000
       WHEN PRICE > 10000 THEN 10000
  END
FROM PRODUCT;

-- postgres：COALESCE()は引数の一番目からNULLでない先頭の値を返す
SELECT 
  ID,
  COALESCE(DESCRIPTION, NAME || ':NULL置換', ' ')
FROM PRODUCT;

CREATE TABLE SCORE (
  ID INTEGER ,
  FIN_DATE TIMESTAMP, 
  MATH INTEGER,
  SCIENCE INTEGER,
  ENGLISH INTEGER
);

INSERT INTO SCORE VALUES
  (1, '2020-09-01', 50, 60, 70),
  (2, '2020-09-01', 51, 61, 71),
  (3, '2020-09-01', 52, 62, 72),
  (1, '2020-09-01', 53, 63, 73);

SELECT * 
FROM SCORE as A
WHERE NOT EXISTS(
  SELECT 0
  FROM SCORE as B
  WHERE 
      B.ID = A.ID
  AND B.FIN_DATE > A.FIN_DATE
)
ORDER BY ID;

SELECT * FROM
  (SELECT
    ID,
    FIN_DATE,
    MATH,
    SCIENCE,
    ENGLISH,
    ROW_NUMBER() OVER(PARTITION BY id ORDER BY FIN_DATE DESC) RN
  FROM SCORE) sub
WHERE RN = 1
ORDER BY ID;