sql 모음

1. CASE

SELECT

CASE NAME WHEN 'NICKJO' THEN '조재훈'
ELSE 'WHO ARE YOU?' END

FROM BOARD;

2. NULL 처리 함수

SELECT
SUM(coalesce(AGE,1)),
SUM(coalesce(AGE,0))

FROM BOARD;

3. 컬럼 추가

ALTER TABLE BOARD ADD COLUMN DEPTNO INTEGER;

ALTER TABLE BOARD ADD COLUMN TITLE VARCHAR(500) DEFAULT '' NOT NULL;

ALTER TABLE BOARD ADD COLUMN CONTENT TEXT DEFAULT '' NOT NULL;

# 컬럼 삭제

ALTER TABLE BOARD DROP COLUMN COLNAME;

# 컬럼 타입 변경

ALTER TABLE BOARD ALTER COLUMN COLNAME TYPE VARCHAR(100);

# 컬럼 제약조건추가
ALTER TABLE BOARD ALTER COLUMN COLNAME SET NOT NULL;
ALTER TABLE BOARD ALTER COLUMN COLNAME SET DEFAULT '';

4. 업데이트

UPDATE BOARD
SET DEPTNO = 30

WHERE NAME IN ('YUNGGCHEL','SANGKIL');

5. FK 추가

ALTER TABLE TB_PRODUCT ADD CONSTRAINT TB_PRODUCT_FKEY

FOREIGN KEY(PRODUCTCATEGORY) REFERENCES TB_PRODUCTCATEGORY(CATEGORYSEQ) MATCH FULL;

6. FK 삭제

ALTER TABLE TB_PRODUCT DROP CONSTRAINT productcategory;

7. 유저에게 role 추가

grant postgres to nickjo;

8. nickjo 에게 모든 권한 주기

ALTER USER nickjo WITH SUPERUSER;

alter user nickjo with createrole replication;

9. DB생성 및 다른 명령어들

create database [name] owner to [username];

alter database [dbname] rename to [new dbname];

alter database [dbname] owner to [username];

drop database [dbname];