주관적인 실무 SQL

PostgreSQL이란??

-      객체관계형 데이터베이스 관리 시스템 ( ORDBMS ) 이다.

-      버클리 소스를 기반으로 확장된 오픈 소스이다.

-      표준 SQL 기능을 대부분 지원

-      복합 쿼리 , 참조키 , 트리거 , 뷰 , 트랜잭션 , 다중 버전 병행 제어

-      기능 확장 ( 자료형 , 함수 , 연산자 , 집계 함수 , 인덱스 방법 , 프로시져 언어 )

 

PostgreSQL 구조

-      PostgreSQL 물리적 구조는 매우 단순하다.

-      Shared Memory , 적은 수의 백그라운드 프로세스 , 데이터 파일로 구성

-      Shared Buffer 의 목적은 DISK I/O를 최소화 하는 것입니다. 그러기 위해서는 아래 항목을 만족해야 한다.

ㄱ.   매우 큰 버퍼를 빠르게 엑세스해야 한다 ( 수백 GB 단위 )

ㄴ.   많은 사용자가 동시에 접근할 때 경합을 최소화해야 한다.

ㄷ.   자주 사용되는 블록은 최대한 오랫동안 버퍼 내에 있어야 한다.

 

ETL

-      정의 : extract , transform , load 를 의미

 

 

UX

-      사용자 경험의 핵심은 “ 느낌 “ , “ 태도 “ , “ 행동 “

-      여기서 경험이란 소프트웨어를 사용하는 사용자의 “느낌”,”태도”,”행동”을 말한다.

-      경험을 설계한다는 말은 사용자의 “느낌,태도,행동”을 설계한다는 말이다.

 

기본키 (Primary key) 란??

 

-      DB에서 테이블을 생성할 때 하나 또는 그 이상의 항목을 기본키(primary key)로 설정할 수 있습니다. 기본키는 해당 테이블에서 가장 기본적인 값을 가집니다. 이를 PostgreSQL에서 몇 가지 측면에서 뜯어보면 다음과 같습니다.

 

1.     기술적인 의미

기본키는 다른 항목과 절대로 중복되어 나타날 수 없는 단일 값(unique)을 가집니다.

기본키는 절대 null ( 아무런 값이 없는 상태 ) 값을 가질 수 없습니다.

(Ex) 예를 들면 주민등록번호 같은 개념입니다. 동일한 이름을 가진 사람은 많을 수 있고 , 동일한 날에 동일한 이름을 가진 사람도 존재할 수 있지만 , 결국 그 사람들이 만나 서로의 민증을 대조해 보면 결국 다른 번호로 구분 됩니다.

기본키는 하나 이상의 컬럼이 그룹화 되어 기본키로도 쓰일 수도 있습니다.

 

2.     암묵적 성격

기본키를 추가할 때에는 기본키가 되는 컬럼 또는 컬럼의 그룹에 대하여 자동으로 단일의 B-트리 인덱스가 생성됩니다.

테이블은 기본키를 하나까지만 가질 수 있습니다.

 기술적 측면에서 기본키는 단일 값(unique)하고 not Null(Null 값 비허용)이면 기능적으로 동일하게 동작은 하지만 실제적으로 기본키처럼 구분되는건 오직 하나입니다. 즉 , 다 똑같은 unique하고 not Null 이라고 기본키가 되는게 아닙니다.

관계형 DB 이론상 모든 테이블은 반드시 하나의 기본 키를 가져야 합니다.

 

3.     정리

Primary key란 행을 고유하게 구분해 주는 최소의 정보입니다. 모든 테이블에는 primary ket가 있어야 하며 , 오직 하나의 primary key만 존재할 수 있습니다. 그리고 rm 하나의 primary key는 단일 컬럼으로 구성될 수도 있고 둘 이상의 다중의 컬럼들로 구성될 수도 있습니다. 만일 어떤 하나의 테이블에 primary key 역할을 할 수 있는 컬럼 또는 컬럼들의 그룹이 여러 개 있다면 그 컬럼 또는 컬럼들의 그룹을 candidate key 라고 합니다. 하나의 테이블에 여러 개의 candidate key들이 존재할 수 있습니다. 예를 들어서 회원 테이블에 각각의 회원에게 고유하게 부여되는 (회원번호)칼럼과 회원의(주민등록번호)컬럼 , 그리고 회원이 회원가입 시에 선택하면서 회원별로 고유한 회원의 ( 로그인ID ) 컬럼이 존재한다면 이 테이블에는 세 개의 candidate key가 존재하는 겁니다. 그렇지만 이 세 개의 candidate key 중에서 오직 하나의 candidate key 만이 primary key로 선택될 수 있습니다. 참고로 (회원번호)컬럼을 primary key로 선택했다면 (회원번호) 컬럼은 회원 테이블의 primary key가 되는 것이고 primary key로 뽑히지 못한 ( 주민등록번호 ) 컬럼과 (로그인) 컬럼은 alternate key가 됩니다.

테이블에 primary key를 생성하면 성능이 현저하게 향상되는 것은 primary key를 정의하면 물리적으로 uniqueness를 보장하기 위하여 unique index가 만들어지기 때문입니다. 일반적으로 primary key를 기준으로 데이터를 select 한다거나 primary key를 기준으로 다른 컬럼(들)의 값을 update 또는 delete 하는 작업이 흔히 수행되기 때문에 테이블에 primary key를 정의해 주면 where 조건절에 primary key가 Search Arguments로 사용된 쿼리들의 성능은 현저하게 향상됩니다.

 

불행히도 , 테이블에 candidate key들이 2,3개나 있음에도 불구하고 primary key를 정의하지 않은 경우들을 간혹 볼 수 있습니다. 그런 경우 primary key를 생성하지 않아서 primary key를 사용하는 쿼리들의 성능이 나쁜 것은 물론이며 ,테이블에 잘못된 중복 데이터들이 저장됨으로 인하여 데이터 무결성까지 손상되어 있는 비극적인 상황까지 발전한 경우도 볼 수 있습니다. 테이블에서 각각의 행들을 고유하게 구분해 주는 컬럼 또는 칼럼들의 그룹을 찾아서 primary key를 만들어 주는 것은 반드시 빠뜨려서는 안되는 매우 기본적인 작업입니다.

 

간혹 , primary key 칼럼이 반드시 테이블의 첫 번째 컬럼이어야 하는 것은 아닙니다. 테이블을 만들고 나서 보니 테이블의 두 번째 칼럼이 primary key라고 해서 슬퍼하지 않으셔도 됩니다 !! 그렇지만 관례상 primary key 컬럼을 테이블의 첫 번째 컬럼으로 배치하는 것이 일반적입니다.

 

CREATE TABLE 명령어를 사용하여 테이블을 만들어 줄 때 잊지 마세요. Candidate key 중 하나를 primary key 로 정의해야 한다는 것을요!! 다음과 같이 CONSTRAINT 절을 사용하여 primary key를 정의하면 됩니다. 이 때 이름은 여러분의 시스템에서 표준화한 명명 규칙이 있다면 그 명명 규칙을 따라서 여러분이 정하시면 되고 , primary key 컬럼을 clustered index로 정할지 아니면 nonclustered index로 정할지는 인덱스 튜닝 기준에 따라 정하면 됩니다.

 

CREATE TABLE TABLE_NAME (

       Emp_number smallint NOT NULL ,

       Emp_name varchar(25) NOT NULL

       CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (Emp_number) )

 

만일 테이블을 만들 때 깜빡하고 primary key 제약 조건을 만들어 주는 것을 잊어버렸다 해도 괜찮습니다.

 

ALTER TABLE TABLE_NAME

ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED ( Emp_number )

 

가끔 primary key가 안 만들어진다고 하시는 분들이 계시는데요. 이미 primary key 컬럼에 중복 값이 저장되어 있거나 아니면 primary key 로 정의하고자 하는 컬럼 또는 컬럼들이 NULL 허용으로 정의되어 있기 때문입니다. Primary key 컬럼은 반드시 NOT NULL로 정의하셔야 합니다. Primary key 컬럼은 NOT NULL 이어야만 합니다.

 

출처 : https://linuxism.ustd.ip.or.kr/503

 

 

Oracle Inner Join Example

 

CREATE TABLE palette_a (

id INT PRIMARY KEY,

color VARCHAR2 ( 100 ) NOT NULL

);

CREATE TABLE palette_b (

           id INT PRIMARY KEY,

           color VARCHAR2 ( 100 ) NOT NULL

);

 

INSERT INTO palette_a (id, color) VALUES (1,’RED’);

INSERT INTO palette_a (id, color) VALUES (2,’GREEN’);

INSERT INTO palette_a (id, color) VALUES (3,’BLUE’);

INSERT INTO palette_a (id, color) VALUES (4,’PURPLE’);

 

INSERT INTO palette_b (id, color) VALUES (1,’GREEN’);

INSERT INTO palette_b (id, color) VALUES (2,’RED’);

INSERT INTO palette_b (id, color) VALUES (3,’CYAN’);

INSERT INTO palette_b (id, color) VALUES (4,’BROWN’);

 

---

Inner join

 

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b

FROM palette_a a

INNER JOIN palette_b b ON a.color = b.color;

 

Inner join Result

 

ID_A COLOR_A ID_B COLOR_B

2 GREEN 1 GREEN

1 RED 2 RED

 

---

 

Oracle left Join Example

 

Left outer join

 

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b

FROM palette_a a

LEFT JOIN palette_b b ON a.color = b.color;

 

Left outer join Result

 

ID_A COLOR_A ID_B COLOR_B

2 GREEN 1 GREEN

1 RED 2 RED

3 BLUE NULL NULL

4 PURPLE NULL NULL

 

 

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b

FROM palette_a a

LEFT JOIN palette_b b ON a.color = b.color

WHERE b.id IS NULL;

 

ID_A COLOR_A ID_B COLOR_B

3 BLUE NULL NULL

4 PURPLE NULL NULL

 

---

 

Oracle right Join Example

 

 

Right outer join

 

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b

FROM palette_a a

RIGHT JOIN palette_b b ON a.color = b.color;

 

Right outer join Result

 

ID_A COLOR_A ID_B COLOR_B

1 RED 2 RED

2 GREEN 1 GREEN

NULL NULL 4 BROWN

NULL NULL 3 CYAN

 

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b

FROM palette_a a

RIGHT JOIN palette_b b ON a.color = b.color

WHERE a.id IS NULL;

 

ID_A COLOR_A ID_B COLOR_B

NULL NULL 4 BROWN

NULL NULL 3 CYAN

 

---

 

Oracle full outer join

 

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b

FROM palette_a a

FULL OUTER JOIN palette_b b ON a.color = b.color;

 

ID_A COLOR_A ID_B COLOR_B

1 RED 2 RED

2 GREEN 1 GREEN

3 BLUE NULL NULL

4 PURPLE NULL NULL

NULL NULL 4 BROWN

NULL NULL 3 CYAN

 

---

 

Oracle full outer join – only rows unique to both tables

 

 

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b

FROM palette_a a

FULL OUTER JOIN palette_b b ON a.color = b.color

WHERE a.id IS NULL OR b.id IS NULL;

 

ID_A COLOR_A ID_B COLOR_B

NULL NULL 3 CYAN

NULL NULL 4 BROWN

3 BLUE NULL NULL

4 PURPLE NULL NULL

 

 

 

Union이란?

 

Union은 여러 개의 SQL문을 합쳐 하나의 SQL문으로 만들어주는 방법입니다.

두개의 쿼리의 합집합을 만들어준다고 생각하면 될듯합니다.

 

Union과 UnionALL의 차이점

 

Union과 UnionALL은 두 쿼리문을 하나로 합쳐준다는 것에 공통점이 있습니다.

하지만 Union은 두 쿼리의 결과의 중복값을 제거해서 보여주고

UnionALL은 중복된값도 전부 다 보여준다는 차이점이 있습니다.

속도는 당연히 중복값제거를 위해 연산을 한번 더 해야하기 때문에

UnionALL이 Union보다 더 빠릅니다.

 

Union 사용시 주의점 : 칼럼명이 같아야 한다. ( 같지않을경우 AS를 사용하여 길게 만들어주면 됩니다 ) , 칼럼별 데이터타입이 같아야합니다.

 

UNION / UNION ALL 사용법

 

1.     두개의 테이블조회 쿼리문 합집합 구하기

SELECT * FROM EX_TABLE1

Union/UnionALL

SELECT * FROM EX_TABLE2

 

2.     물품별 총 합계금액 구하기

SELECT

COMPANY,

ITEM,

MAX(AM) AS AM,

MAX(QT_IO) AS QT_IO,

MAX(AM_RETURN) AS AM_RETURN,

MAX(QT_RETURN) AS QT_RETURN,

MAX(AM) * MAX(QT_IO)-MAX(AM_RETURN) * MAX(QT_RETURN ) AS AM_TOT

FROM

(

            SELECT

            COMPANY,

            ITEM,

            AM,

            QT_IO,

            ‘0’AM_RETURN,

            ‘0’QT_RETURN

            FROM EX_TABLE01

UNION ALL

            SELECT

            COMPANY,

            ITEM,

            ‘0’AM,

            ‘0’QT_IO,

            AM AS AM_RETURN,

            QT_RETURN

            FROM EX_TABLE02

) UN

Group by COMPANY,ITEM