PostgreSQL 이거까지 알아야 하는가

PostgreSQL brew를 통한 설치

SQL과 장고를 연결할 psycopg2-binary 설치

 

• 생성
  • create database 데이터베이스 이름;

• 삭제
  • drop database 데이터베이스 이름;

자료형

• char(n) : 고정 길이 문자열
• varchar(n) : 가변 길이 문자열
• int2 : 2bytes 정수형
• int : 4bytes 정수형
• float4 : 4 bytes 실수형
• float8 : 8 bytes 실수형
• bool : 참/거짓 자료형
• date : 년-월-일
• time : 시-분-초
• timetz : 시-분-초 + 시간대
• timestamp : 년-월-일-시-분-초
• timestamptz : 년-월-일-시-분-초 + 시간대

자료형 타입 : https://www.postgresql.org/docs/13/datatype.html

 

테이블

 : 레코드(행)와 컬럼(열)으로 이루어진 표이다. 대소문자 구별을 하지 않지만, 큰 따옴표를 사용해서 구분을 명시할 수 있다

•  생성

SQL 테이블 생성
not null : 각각의 컬럼은 비어있을 수 없다
check : 각 컬럼은 0이상이어야 한다는 조건을 설정
primary key : 다른 레코드 값과 중복되지 않고 공백은 금지
generated always as identity : id를 1부터 자동으로 증가하게 설정
on delete set null : 외래키가 참조하는 레코드가 삭제되면 값은 null로 설정
on delete cascade : 외래키가 참조하는 레코드가 삭제되면 현재 테이블의 레코드도 삭제

• 삭제
  • drop table 테이블이름;

레코드

• 추가

insert into 테이블이름 values (값, ...);

insert into champion values (default, 'Tiber', 2, 5, 10, 88);
insert into champion values (default, 'Paran', 4, 2, 4, 13);
insert into champion values (default, 'Stroll', 5, 1, 2, 212);
insert into champion values (default, 'Jija', 2, 7, 1, 79);

insert into 테이블이름 (컬럼이름, ...) values (값, ...);
: 지정된 컬럼에만 값 넣고 나머지 컬럼에는 default 값 또는 null 값을 넣은 레코드를 테이블에 추가

• 수정

update 테이블이름 set 컬럼1=바꿀값1, 컬럼2=바꿀값2, ... where 조건문;

update champion set victory = True where k >= 2;
update champion set victory = False where k < 2;

• 삭제

delete from 테이블이름 where 조건문;

while 절 생략할 시 해당 테이블 삭제

 

컬럼

• 추가

alter table 테이블명 add 컬럼명 자료형 제약조건;

alter table champions add victory boolean;
alter table champions add new_column char(10);

champion 테이블에 자료형 boolen, char(10) null 값으로 초기화로 추가

• 수정

// 제약 조건 설정/삭제
alter table 테이블명 alter column 컬럼명 set 제약조건;
alter table 테이블명 alter column 컬럼명 drop 제약조건;

// 컬럼 이름 변경
alter table 테이블명 rename column 컬럼명 to 새로운이름;

// 컬럼 자료형 변경
alter table 테이블명 alter column 컬럼명 type 자료형;

// check 조건 추가/삭제
alter table 테이블명 add constraint 제약조건이름 check (조건);
alter table 테이블명 drop constraint 제약조건이름;

alter table champions alter column death set not null;
alter table champions alter column death set default 0;
alter table champions alter column death drop not null;
alter table champions alter column death drop default;

• 삭제

alter table 테이블명 drop column 컬럼명;

alter table champions drop column new_column;

 

레코드 검색

select 컬럼명 from 테이블명 where 조건;

// select... from..., where (레코드 중 하나의 값)
select id, champ_name, death from champion_info where death >= 4;

// * (모든 컬럼)
select * from champion_info where assist = 1;

// order by (vitory가 true인 레코드)
select * from champion_info where victory = True order by cs desc;

//group by (count, sum, avg, max, min 등과 함께 사용)
(vitory의 값이 t인, ㄹ 레코드끼리 모아서 평균과 합 출력)
select victory, avg(kill_), avg(death), avg(assist), sum(cs) 
from champion_info
group by victory
order by victory desc;

// group by ... having... (위의 검색 명령어 + 조건 한 개 추가)
select victory, avg(k), avg(death), avg(assist), sum(cs) 
from champion_info
group by victory
having sum(cs) > 100;

// limit (cs 내림차순으로 정렬 후, 3개 이하로 검색)
select * from champion_info order by cs desc limit 3;

 

인덱스 테이블 생성

create index 인덱스_테이블명 on 테이블명 (컬럼명, ...);

create index test2_info_index on test2 (info NULLS FIRST);
create index test3_desc_index on test3 (id DESC NULLS LAST);

---

실습 1 (INSERT)

INSERT INTO
	TABLE_NAME
(
	COLUMN1,
    	COLUMN2
)
VALUES
(
	VALUE1,
    	VALUE2
);
----------------------< 기본 문법 >---------

CREATE TABLE LINK(
    ID SERIAL PRIMARY KEY,
    URL VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(255),
    REL VARCHAR(50)
);


SELECT * FROM LINK // 테이블 조최 결과 X

----

>> 결과 1

INSERT
    INT
    LINK(URL, NAME)
VALUES ('HTTP://NAVER.COM', 'NAVER');

COMMIT;

SELECT * FROM LINK

----

>> 결과 2

INSERT
    INT
    LINK (URL, NAME)
VALUES
('http://www.google.com','Google'),
('http://www.daum.com','Daum'),
('http://instagram.com','Instagram');

COMMIT;

----

>> 결과 3

CREATE TABLE LINK_TMP AS
SELECT * FROM LINK WHERE 0=1;

INSERT INTO LINK_TMP
SELECT * FROM LINK;

COMMIT;

SELECT * FROM LINK_TMP;

▼ ▼ 결 과 1 ▼ ▼

id	url	name	description	rel
1	HTTP://NAVER.COM	NAVER	null	null

 

▼ ▼ 결 과 2 ▼ ▼

id	url	name	description	rel
1	HTTP://NAVER.COM	NAVER	null	null
2	http://www.google.com	Google	null	null
3	http://www.daum.net	Daum	null	null
4	http://instagram.com	Instagram	null	null

▼ ▼ 결 과 3 ▼ ▼

id	url	name	description	rel
1	HTTP://NAVER.COM	NAVER	null	null
2	http://www.google.com	Google	null	null
3	http://www.daum.net	Daum	null	null
4	http://instagram.com	Instagram	null	null

---

실습 2 (UPDATE)

UPDATE
    TAGET_TABLE A
SET
    A.COLUMN_1 = 표현식
FROM
    REF_TABLE B
WHERE
    A.COLUMN_1 = B.COLUMN_1;

-----------------------< 기본 문법 >-----------------

>>> 실습 1

CREATE TABLE PRODUCT_SEGMENT
(
    ID SERIAL PRIMARY KEY,
    SEGMENT VARCHAR NOT NULL,
    DISCOUNT NUMERIC (4, 2)
);

INSERT INTO PRODUCT_SEGMENT(SEGMENT, DISCOUNT) VALUES
('GRAND LUXURY', 0.05),
('LUXURY', 0.06),
('MASS', 0.1);

COMMIT;

----

>>> 실습 2

CREATE TABLE PRODUCT2
(
	ID SERIAL PRIMARY KEY,
	NAME VARCHAR NOT NULL,
	PRICE NUMERIC(10,2),
	NET_PRICE NUMERIC(10,2),
	SEGMENT_ID INT NOT NULL,
	FOREIGN KEY (SEGMENT_ID)
	REFERENCES PRODUCT_SEGMENT(ID)
);

INSERT INTO PRODUCT2(NAME,PRICE,SEGMENT_ID)
VALUES
('K5',804.89,1),
('K7',228.55,3),
('K9',567.98,2),
('SONATA',666.55,3),
('SPARK',567.55,2),
('AVANTE',678.76,3),
('LOZTE',441.23,2),
('SANTAFE',445.66,1),
('TUSON',887.77,3),
('TRAX',789.55,2),
('ORANDO',163.66,1),
('RAY',456.66,1),
('MORNING',982.55,3),
('VERNA',207.88,1),
('K8',985.45,1),
('TICO',896.38,1),
('MATIZ',575.74,2),
('SPORTAGE',530.64,2),
('ACCENT',892.43,1),
('TOSCA',161.71,3);

COMMIT;

----

>>> 실습 3

UPDATE
	PRODUCT2 A
SET
	NET_PRICE = A.PRICE - (A.PRICE * B.DISCOUNT)
FROM
	PRODUCT_SEGMENT B
WHERE
	A.SEGMENT_ID = B.ID;

COMMIT;

▼ ▼ 결 과 1 ▼ ▼

id	segment	discount
1	GRAND LUXURY	0.05
2	LUXURY	0.06
3	MASS	0.1

▼ ▼ 결 과 2 ▼ ▼

ID	NAME	PRICE	NET_PRICE	SEGMENT_ID
1	K5	804.89	NULL	1
2	K7	228.55	NULL	3
3	K9	567.98	NULL	2
4	SONATA	666.55	NULL	3
5	SPARK	567.55	NULL	2
6	AVANTE	678.76	NULL	3
7	LOZTE	441.23	NULL	2
8	SANTAFE	445.66	NULL	1
...	...	...	...	...

▼ ▼ 결 과 3 ▼ ▼

ID	NAME	PRICE	NET_PRICE	SEGMENT_ID
1	K5	804.89	764.65	1
2	K7	228.55	205.7	3
3	K9	567.98	533.5	2
4	SONATA	666.55	599.9	3
5	SPARK	567.55	533.5	2
6	AVANTE	678.76	610.88	3
7	LOZTE	441.23	414.76	2
8	SANTAFE	445.66	423.38	1
...	...	...	...	...

---

실습 3 (DELETE)

DELETE
FROM
	TARGET_TABLE A
WHERE 조건식;

------------------------< 기본 문법 >--------------

>>> 결과 1번 (문제 실습1번 기준)

DELETE
FROM
	LINK
WHERE
	ID = 5;
	
COMMIT;

>>> id 5인 행을 삭제

----

>>> 결과 2번

DELETE
FROM
	LINK_TMP A
	USING LINK B
WHERE
	A.ID = B.ID;

COMMIT;

>>> id 5인 행만 출력

----

>>> 결과 3번

DELETE FROM LINK;

COMMIT;

>>> 모든데이터 삭제

 

 

https://dog-developers.tistory.com/175?category=896375

PostgreSQL UPSERT 문법

https://postgresql.kr/docs/9.6/index.html

PostgreSQL 9.6.2 문서