nickjoIT
sql 모음 본문
1. CASE
SELECT
CASE NAME WHEN 'NICKJO' THEN '조재훈'
ELSE 'WHO ARE YOU?' END
ELSE 'WHO ARE YOU?' END
FROM BOARD;
2. NULL 처리 함수
SELECT
SUM(coalesce(AGE,1)),
SUM(coalesce(AGE,0))
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 '';
# 컬럼 제약조건추가
ALTER TABLE BOARD ALTER COLUMN COLNAME SET NOT NULL;
ALTER TABLE BOARD ALTER COLUMN COLNAME SET DEFAULT '';
4. 업데이트
UPDATE BOARD
SET DEPTNO = 30
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];
'DB > postgresql' 카테고리의 다른 글
pgadmin 실행 (0) | 2017.03.09 |
---|---|
[ubuntu]pgadmin4 설치 (0) | 2017.03.05 |
각종 조회 (0) | 2016.09.27 |
테이블 컬럼 정보 확인 (0) | 2016.09.25 |
Postgresql 문법 (0) | 2016.09.03 |
Comments