postgresql 실용예제 - arcturus9/useful-link GitHub Wiki

CREATE TABLE Address
(name       VARCHAR(32) NOT NULL,
 phone_nbr  VARCHAR(32) ,
 address    VARCHAR(32) NOT NULL,
 sex        CHAR(4) NOT NULL,
 age        INTEGER NOT NULL,
 PRIMARY KEY (name));
INSERT INTO Address VALUES('인성',   '080-3333-XXXX', '서울시',   '남', 30);
INSERT INTO Address VALUES('하진',   '090-0000-XXXX', '서울시',   '여', 21);
INSERT INTO Address VALUES('준',     '090-2984-XXXX', '서울시',   '남', 45);
INSERT INTO Address VALUES('민',     '080-3333-XXXX', '부산시',   '남', 32);
INSERT INTO Address VALUES('하린',   NULL,           '부산시',   '여', 55);
INSERT INTO Address VALUES('빛나래', '080-5848-XXXX', '인천시',   '여', 19);
INSERT INTO Address VALUES('인아',   NULL,           '인천시',   '여', 20);
INSERT INTO Address VALUES('아린',   '090-1922-XXXX', '속초시',   '여', 25);
INSERT INTO Address VALUES('기주',   '090-0001-XXXX', '서귀포시', '남', 32);
select * from address;


SELECT name, address  FROM Address WHERE address = '인천시';
select name, age from address where age >= 30;
select name, address, age from address where address = '서울시' and age >= 30;
select name, address, age from address where address = '서울시' OR age >= 30;
select name, address, age from address where address = '서울시' or address = '부산시' or address = '인천시';
select name, address from address where address in ('서울시', '부산시', '인천시');
select name, phone_nbr from address where phone_nbr is not null;
select sex, count(*) from address group by sex;
select address, count(*) from address group by address;
select count(*) from address group by ();
select count(*) from address;
select address, count(*) from address group by address having count(*) = 1;
select name, phone_nbr, address, sex, age from address order by age DESC;
create view CountAddress(V_address, cnt)
select address, count(*) from address group by address;
create view CountAddress(V_address, cnt) as select address, count(*) from address group by address;
select v_address, cnt from countAddress;

CREATE TABLE Address2
 (name       VARCHAR(32) NOT NULL,
 phone_nbr  VARCHAR(32) ,
 address    VARCHAR(32) NOT NULL,
 sex        CHAR(4) NOT NULL,
 age        INTEGER NOT NULL,
PRIMARY KEY (name));
INSERT INTO Address2 VALUES('인성', '080-3333-XXXX', '서울시', '남', 30);
INSERT INTO Address2 VALUES('민',   '080-3333-XXXX', '부산시', '남', 32);
INSERT INTO Address2 VALUES('준서', NULL,            '부산시', '남', 18);
INSERT INTO Address2 VALUES('지연', '080-2367-XXXX', '인천시', '여', 19);
INSERT INTO Address2 VALUES('서준', NULL,            '인천시', '여', 20);
INSERT INTO Address2 VALUES('중진', '090-0205-XXXX', '속초시', '남', 25);

select name from address where name in (select name from address2);
select * from address union select * from address2;
select * from address intersect select * from address2;

select * from address except select * from address2;
  • postgresql 구문 정리
# Selcet : 컬럼 선택
# FROM : 테이블명
# AS : 컬럼명 바뀌주기
# " " : AS의 컬럼명에 빈칸(space)이 들어가면, " " 붙이면 됨
# ORDER BY : sorting순서
# ORDER BY sort_expresssion [ASC | DESC] [NULLS FIRST | NULLS LAST]
# DISTINCT : column조합에 대하여 1개 item만 골라서 나타내기
# DISTINCT ON : 여러 column중에서 특정 column만 1개씩 고르고 싶을때
# WHERE : 조건문 (if같은 역할)
# WHERE ~ IN (A, B, C) : A, B, C가 포함된 경우
# WHERE ~ LIKE 'Abc%' : Abc 문자열 포함된 경우
# WHERE LENGTH (first_name) BETWEEN 3 AND 5 : 길이가 3~5 사이일 때
# WHERE last_name <> 'John' : not equal
# LIMIT : cut해서 제한되게 보여주는 경우 / ORDER BY로 sorting해서 활용
# FETCH : LIMIT하고 똑같은데, 다른 DB에서 활용하는 standard 문법임
# OR, NOT IN : operator로 쓰임
# BETWEEN ~ AND ~ : operator로 쓰임
# WHERE	first_name LIKE '_her%' : 확인
# INNER JOIN basket_b ON fruit_a = fruit_b;  : Join확인