PostgreSQL Ch 03. 코드(쿼리) 문법#1

PostgreSQL Ch 03. Code(Query) Grammar

" PostgreSQL 코드(쿼리) 문법 공부하는 시간 "

---

▣ CREATE TABLE, INSERT INTO, VALUES, SELECT, FROM

-- 오전 : 기초문법 익히는 시간
-- 오후 : 서브쿼리 & 윈도우 함수 예제

CREATE TABLE develop_book(
	book_id		INTEGER,
	pub_date	INTEGER,
	book_name	VARCHAR(80),
	price		MONEY
);

-- 데이터 추가하기
INSERT INTO develop_book(book_id, pub_date, book_name, price)
VALUES(1, 20231013, 'book', 3000)
;

SELECT * FROM develop_book;

　1. CREATE INTO 문법을 활용해 develop_book 라는 데이터 테이블을 만들고

　2. INSERT INTO [테이블명][A컬럼명, B컬럼명,  C컬럼명] VALUES[A컬럼값, B컬럼값,  C컬럼값]
　3. 위 INSERT VALUES 문법으로 데이터를 추가하고
　4. 데이터 테이블을 선택해 데이터를 가져와 출력하는 모습이다.

develop_book 데이터 테이블의 데이터 출력 상태

 

-- 날짜 및 시간
CREATE TABLE datetime_study(
--	Columns		Data Type
	type_ts		TIMESTAMP,
	type_tstz	TIMESTAMPTZ,
	type_data	DATE,
	type_time	TIME
);

INSERT INTO datetime_study(type_ts, type_tstz, type_data, type_time)
VALUES(
	'2023-10-13 10:00:01+09', '2023-10-13 10:00:02+09', '2023-10-13', '10:00:01'
);

SELECT * FROM datetime_study;

datetime_study 데이터 테이블의 데이터 출력 상태

 

-- PostgreSQL은 배열형이다.
-- 배열형 테이블
CREATE TABLE contact_info(
--	Columns		Data Type
	cont_id		NUMERIC(3),
	name		VARCHAR(15),
	tel		INTEGER[],
	email		VARCHAR
)
;

-- 데이터 추가
INSERT INTO contact_info
VALUES(001, 'evan', ARRAY[01012345678, 01112345678], 'abc@gmail.com')
;

SELECT * FROM contact_info;

contact_info 데이터 테이블의 데이터 출력 상태

 

-- 데이터 추가
INSERT INTO contact_info
VALUES(002, 'kim', '{01212345678, 01312345678}', 'dcd@gmail.com')
;

SELECT * FROM contact_info;

　5. 데이터를 추가하면 어떻게 되는지 궁금한 필자는 위의 코드(쿼리)를 실행하여
　6. contact_info 데이터 테이블에 데이터를 추가해보았다.

contact_info 데이터 테이블의 데이터 출력 상태

 

▣ JSON 형태의 데이터 INSERT INTO VALUES

-- JSON형
CREATE TABLE develop_book_order(
--	Columns		Data Type
	id		NUMERIC(3),
	order_info	JSON NOT NULL
)
;

INSERT INTO develop_book_order
VALUES(001, '{"customer" : "evan", "books" : {"product" : "postgreSQL", "qty" : 2}}')
;

SELECT * FROM develop_book_order;

　7. Json 형태의 데이터를 INSERT INTO VALUES 문법을 통해 구현 해보았다.

develop_book_order  데이터 테이블의 데이터 출력 상태

 

▣ 형 변환: CAST 메서드 활용

-- 형 변환 : CAST 메서드 활용
SELECT CAST('3000' AS INTEGER);

SELECT * FROM develop_book;

SELECT book_id, CAST(pub_date AS VARCHAR) FROM develop_book;
SELECT book_id, pub_date::VARCHAR FROM develop_book;

DB ---> Python
DB type은 숫자 -- 파이썬에는 문자로

　8. CAST 문법을 활용하여 데이터 타입을 변환시킬 수 있다.

　9. pub_date = INTEGER >> VARCHAR

SELECT CAST('3000' AS INTEGER); // develop_book 데이터 테이블의 데이터 출력 상태

SELECT book_id, CAST(pub_date AS VARCHAR) FROM develop_book; // SELECT book_id, pub_date::VARCHAR FROM develop_book;

 

▣ 도메인 무결성 예시

-- 도메인 무결성 예시
-- 숫자가 0~9의 숫자만 입력되도록 설정
CREATE DOMAIN phoneint AS INTEGER CHECK (VALUE > 0 AND VALUE < 9);

-- 테이블 생성
CREATE TABLE domain_type_study(
	id phoneint
);

INSERT INTO domain_type_study VALUES(1); --성공
INSERT INTO domain_type_study VALUES(10); --실패

　10. 데이터 삽입 시 도메인 무결성을 위해 정수 값 제약 조건을

　11. 0보다 크게 9보단 작은 값만 사용될 수 있도록 조건을 주었다.

　12. 아래 출력 결과를 참고하면 1은 정상 삽입
　　　10은 제약 조건 위반 되었다는 출력문을 볼 수 있다.

INSERT INTO domain_type_study VALUES(1); --성공 // INSERT INTO domain_type_study VALUES(10); --실패

 

▣ 5가지 제약 조건

　13. 5가지 제약 조건은 아래와 같다.

제약 조건	설명
NOT NULL	빈값을 허용하지 않는 조건
UNIQUE	유일한 값을 갖는 조건
PRIMARY KEY	NOT NULL + UNIQUE 제약조건
FOREIGIN KEY	4가지 규칙
CHECK	Boolen 타입의 True 만족

-- 5가지 제약 조건
-- 1. NOT NULL
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
	cont_id		NUMERIC(3)		NOT NULL,
	name		VARCHAR(15)		NOT NULL,
	tel		INTEGER[]		NOT NULL,
	email		VARCHAR
);

-- 2-1. NOT NULL & UNIQUE
CREATE TABLE contact_info(
	cont_id		NUMERIC(3)		UNIQUE NOT NULL,
	name		VARCHAR(15)		NOT NULL,
	tel		INTEGER[]		NOT NULL,
	email		VARCHAR
);

-- 2-2. 여러 컬럼에 UNIQUE 적용
CREATE TABLE contact_info(
	cont_id		NUMERIC(3)		NOT NULL,
	name		VARCHAR(15)		NOT NULL,
	tel		INTEGER[]		NOT NULL,
	email		VARCHAR,
	UNIQUE(cont_id, tel, email)
);

　14. 첫번째 테이블 생성: NOT NULL >>> 데이터 삽입 시 값이 없으면 오류가 뜬다.

　15. 두번째 테이블 생성: UNIQUE NOT NULL
　　　>>> 데이터 삽입 시 값이 중복되거나 없으면 오류가 뜬다.

　16. 세번째 테이블 생성: UNIQUE(A, B, C) >>> (A, B, C) 삽입 값이 전부 중복되면 오류가 뜬다.

 

▣ NOT NULL 제약 조건 위반 체크하기

-- 기본키 지정
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
	cont_id		SERIAL			NOT NULL PRIMARY KEY,
	name		VARCHAR(15)		NOT NULL,
	tel		INTEGER[]		NOT NULL,
	email		VARCHAR,
	UNIQUE(tel, email)
);

　17. NOT NULL 제약 조건 위반: cont_id, name, tel 컬럼에 삽입 값이 NULL값 이기에 오류 발생

　18. NOT NULL 제약 조건 위반: cont_id, name 컬럼에 삽입 값이 NULL값 이기에 오류 발생

-- NOT NULL 제약 조건 위반
INSERT INTO contact_info(email) VALUES('abc@gmail.com');

17. cont_id, name, tel 컬럼에 삽입 값이 NULL값 이기에 오류 발생

INSERT INTO contact_info(tel)
VALUES ('{01012346789, 01098765432}');

18. NOT NULL 제약 조건 위반: cont_id, name 컬럼에 삽입 값이 NULL값 이기에 오류 발생

 

▣ UNIQUE 제약 조건 위반 체크하기

　19. 고유 값이 될 데이터를 삽입한다.

-- UNIQUE 제약 조건 위반
INSERT INTO contact_info(name, tel)
VALUES ('홍길동', '{01012346789, 01098765432}');

-- UNIQUE(A, B, C) == 컬럼 전부 일치해야 오류 뜸
INSERT INTO contact_info(name, tel)
VALUES ('동길홍', '{01012346789, 01098765432}');

INSERT INTO contact_info(name, tel)
VALUES ('동길홍', '{01012346789, 01098765432}');

-- UNIQUE(A, B, C) 다 일치해도 NULL 값은 유일하지 않다 간주함
INSERT INTO contact_info(name, tel, email)
VALUES ('동길홍', '{01012346789, 01098765432}', 'sample@gmail.com');

19. 고유 값이 될 데이터를 삽입한다.

　20. 중복된 키 값 존재

INSERT INTO contact_info(name, tel, email)
VALUES ('동길홍', '{01012346789, 01098765432}', 'sample@gmail.com');

SELECT * FROM contact_info;

중복된 키 값 존재

contact_info  데이터 테이블의 데이터 출력 상태

▣ 외래키 지정하기

　21. book 테이블 생성

CREATE TABLE book(
	book_id		SERIAL	NOT NULL,
	name		VARCHAR	NOT NULL,
	admin_no	SERIAL	NOT NULL REFERENCES contact_info(cont_id),
	email		VARCHAR,
	PRIMARY KEY (book_id, admin_no)
);

　22. subject 테이블 생성

-- 학교 / 수업, 선생님
CREATE TABLE subject(
	subj_id		NUMERIC(5) 		NOT NULL PRIMARY KEY,
	subj_name	VARCHAR(10)		NOT NULL
);

　23. subject 테이블에 값 삽입하기

INSERT INTO subject VALUES(00001, '수학'), (00002, '과학'), (00003, '사회');

SELECT * FROM subject;

 

subject 데이터 테이블의 데이터 출력 상태

　24. teacher 테이블 생성

CREATE TABLE teacher(
	teac_id			NUMERIC(5)	NOT NULL PRIMARY KEY,
	teac_name		VARCHAR(20)	NOT NULL,
	subj_id			NUMERIC(5)	REFERENCES subject,
	teach_certifi_date	DATE
);

　25. teacher 테이블에 각각 데이터 삽입하기

INSERT INTO teacher
VALUES(00011, '정선생', 00001, '2023-10-12');

INSERT INTO teacher
VALUES(00021, '김선생', 00002, '2023-10-12');

INSERT INTO teacher
VALUES(00031, '박선생', 00003, '2023-10-12');

　26. 위 데이터 테이블은 subj_id 컬럼이
　　　subject 테이블의 subj_id와 일치해야되는 제약 조건이 있음

INSERT INTO teacher
VALUES(00041, '이선생', 00004, '2023-10-12');

외래키 제약 조건 위반 사항 출력 상태

-- 00005 넣으려고 할 시 참조 테이블 참조 키 값 없음으로 오류 발생

 

▣ CASE WHEN 을 활용하여 시험 점수 등급 분류

　27. 학생들의 점수를 보고 CASE WHEN을 이용하여 등급을 분류 해보자

-- CASE WHEN
SELECT * FROM student_score;

　28. 90점 이상은 A 분류 후
　　  80점 이상은 B로 분류 후

　　  70점 이상은 C로 분류 

　　  50점 이상은 D로 나머지는 F로 분류 하였다.

SELECT
	id
	, name
	, CASE
		WHEN score >= 90 THEN 'A'
		WHEN score >= 80 THEN 'B'
		WHEN score >= 70 THEN 'C'
		WHEN score >= 50 THEN 'D'
		ELSE 'F'
	END AS grade
FROM student_score;

90점 이상은 A 분류 후 80점 이상은 B로 분류 후 70점 이상은 C로 분류  50점 이상은 D로 나머지는 F로 분류 하였다.

 

▣ EXISTS를 이용한 데이터 관계 파악하기

　29. EXISTS를 사용하면 두 데이터 테이블간의 관계를 확인할 때 유용합니다. 예를 들어,
　　　어떤 조건을 만족하는 데이터가 서브쿼리의 테이블에 존재하는지만 확인하고 싶다면?

-- EXISTS 두개다 같은 과일 데이터 셋
SELECT * FROM real_amount;
SELECT * FROM assumption_amount;

real_amount 데이터 테이블 불러오기 // assumption_amount 데이터 테이블 불러오기

　30. 아래와 같이 WHERE 문법에 EXISTS를 사용하여 real_amount 테이블과
　　　assumption_amount 테이블과의 관계를 확인하게 되며

　　　 한개 이상의 동일 행값이 존재할 시 real_amount 테이블을 반환(출력)하게 됩니다.

-- EXISTS 연산자는 주로 조인 없이 두 테이블 간의 관계를 확인할 때 유용합니다.
-- 예를 들어, 어떤 조건을 만족하는 데이터가 서브쿼리의 테이블에 존재하는지만 확인하고 싶을 때 사용됩니다.
-- 존재하면 외부 쿼리의 SELECT 데이터가 불러와지고
-- 존재하지 않으면 비어있는 데이터만 보이게 됩니다.

SELECT * FROM real_amount
WHERE EXISTS (
	SELECT * FROM assumption_amount
);

WHERE 문법에 EXISTS를 사용하여 두 테이블과의 관계를 확인하게 되며 한개 이상의 행이 동일할 시 데이터를 전부 불러오게 됩니다.

　31. 반대로 어떠한 행도 일치하지 않을 시 아래와 같이 텅빈 데이터가 보이게 됩니다.

SELECT * FROM real_amount
WHERE EXISTS (
	SELECT * FROM factory
);

SELECT * FROM real_amount
WHERE EXISTS (
	SELECT * FROM exception
);

 

▣ SUBSTR, LEFT 문법

　32. 글자 추출에 유용한 SUBSTR, LEFT 문법을 소개합니다.

　　　SUBSTR(추출할 데이터, 추출할 위치, 텍스트 길이) 특징: 추출할 위치를 지정함

　　　LEFT(추출할 데이터, 텍스트 길이) 특징: 맨 좌측 기준으로 추출함

-- MySQL SUBSTR
-- PostgreSQL
SELECT SUBSTR('evan_123456789', 1, 4);

SELECT LEFT('evan_123456789', 6);

SUBSTR // LEFT 함수 비교

---

끝. 수고하셨습니다.

간단한 실습문제 풀러가기 링크