실용 PostgreSQL 스터디: chap.2~4

*책 실용SQL을 바탕으로 한 스터디 정리

 

Chapter 2. 데이터베이스와 테이블 생성

• 테이블: 데이터를 담고 있는 행과 열로 이루어진 표
• DBMS(Database Management System): 데이터베이스 관리 시스템. PostgreSQL도 여기에 해당
• 데이터베이스 생성 구문

CREATE DATABASE <database_name>;

테이블

• 각 열의 이름은 데이터 타입으,로 정의된 개별 데이터 요소를 대표한다.
• bigserial: 테이블에 행 추가시 자동으로 증가하는 특별한 정수 타입
• 테이블 생성 구문

-- 기본형
-- CREATE TABLE <table_name> (
-- 	id bigserial,
-- 	…
-- 	… )
-- example
CREATE table teachers (
    id bigserial,
    first_name varchar(35),
    last_name varchar(50),
    school varchar(50),
    hire_date date,
    salary numeric
);

• 테이블에 행 추가하는 구문: INSERT문
• 만약 여기서 컬럼명을 명시하지 않는다면? 테이블에 정의한 순서대로 값이 들어가게 되어있다.

-- INSERT INTO <table_name> (column1, column2, column3, ...)
-- VALUES (value1, value2, value3, ..)

INSERT INTO teachers (first_name, last_name, school, hire_date, salary)
VALUES ('Janet', 'Smith', 'F.D. Roosevelt HS', '2011-10-30', 36200),
       ('Lee', 'Reynolds', 'F.D. Roosevelt HS', '1993-05-22', 65000)

표준 SQL 요구사항

• 문자: 작은 따옴표 안에 들어가야함
• 날짜: 작은 따옴표 안에 들어가야하고, 국제기준상 YYYY-MM-DD 표기방식이 최선
• 정수와 소수가 포함된 숫자들: 들어갈 필요 없음
• 앞서 id를 bigserial로 정의했으므로 별도로 데이터를 넣을 필요 없이 자동으로 증가하는 정수로 채워진다.

 

Chapter 3. select로 시작하는 데이터 탐험

기초 구문

select * from <table_name>;

테이블 모든 행을 보는 표준 SQL 구문

TABLE <table_name>;

특정 열만 보기

SELECT <column1, column2, column3, ..> FROM <table_name>;

ORDER BY

• 데이터 정렬에 사용. 원본 테이블이 바뀌는 것이 아니라 쿼리의 결괏값만 변경
• 기본값은 오름차순이고, 내림차순으로 가져오고 싶다면 DESC를 명시
• 아래 커맨드에서 column에는 숫자도 입력이 가능하다. 숫자를 입력하면 해당 쿼리에서 숫자에 해당하는 위치의 컬럼을 기준으로 가져온다.

SELECT <column1, column2, column3, ..> FROM <table_name>
ORDER BY <column> <DESC>;

 

DISTINCT

• 고유값찾기
• 중복되는 값들이 종종 들어가있는데 이 때 중복을 제외한 값들을 알고 싶으면 사용한다

WHERE

• 특정 기준에 부합하는 열들에 담긴 행만 보여주기 위한 키워드
• 사용하는 연산자 일부

연산자	기능
=	같음
<> 또는 !=	같지않음
>	초과
>	미만
BETWEEN	범위 내
IN	값 중 하나 이상 일치
LIKE	패턴일치(대소구분)
ILIKE	패턴일치(대소구분안함)

LIKE / ILIKE

패턴을 검색하므로 데이터베이스가 클수록 검색 성능이 떨어질 수 있는데, 이는 인덱스를 활용해서 해결할 수 있음(8장 참조)

퍼센트 기호: 문자 한개 혹은 여러개에 매칭하는 와일드 카드

언더바: 문자 한개랑 매칭하는 와일드카드

LIKE 'b%';
LIKE 'b_';

연산자 조건 결합하기

• AND: 조건1이면서 조건2에 해당
• OR: 조건1이거나 조건2이거나

실습

SELECT * FROM teachers
ORDER BY school, last_name;

SELECT * FROM teachers
WHERE first_name LIKE 'S%' and salary >= 40000;

SELECT * FROM teachers
WHERE hire_date > '2010-01-01'
ORDER BY salary DESC;

 

Chapter 4. 데이터타입

데이터 타입

• 테이블의 각 열에 하나만 지정가능
• CREATE TABLE 문에서 열 이름과 함께 입력

문자: 글자와 기호

char(n)

• 입력한 n에 따라 길이가 고정된 열이 정의
• 입력 글자가 많으면 20자까지만 저장하고, 적게 입력하면 나머지 공간을 공백으로 채움
• 최근엔 잘 사용되지 않음

varchar(n)

• 최대 길이가 n으로 정의되는 가변길이 데이터타입을 담은 열
• char과 다르게 데이터를 적게 입력한다고 공백을 추가하지 않는다

text

• 길이 제한이 없는 가변길이 데이터타입을 담은 열로, PostgreSQL 문서에 따르면 가장 길게 담을 수 있는 문자열 크기는 1GB
• 표준SQL에 포함되진 않으나 다수의 데이터베이스에서 사용

숫자

• 정수와 소수를 포함한 모든 수
• 정수: 양수와 음수를 포함한 정수
• 고정 소수점과 부동 소수점: 실수를 표현하는 두 가지 형태

integer

• smallint: 입력되는 데이터값이 한정되어 있다면 적합하다.
• bigint: 공간을 가장 많이 차지하나 숫자열을 다룰 때 엔간한 조건은 충족이 가능하다. 다루는 수의 크기가 21억을 넘어갈 경우엔 반드시 선택해야한다.

자동증가 정수

• 시리얼 데이터타입: ANSI SQL 표준인 자동증가 정수를 PostgreSQL에서 고유 구현한 데이터 타입
• IDENTITY 키워드: ANSI SQL 표준

CREATE TABLE people (
    id1 serial,
    id2 integer GENERATED ALWAYS AS IDENTITY
);

10진수

고정소수점: numeric

• argument
  • precision: 정밀도. 입력될 숫자의 전체 자릿수
  • scale: 척도. 소수점 아래 자릿수

부동소수점

• real: 소수점 이하 6자리까지 정밀도를 허용
• double precision: 소수점 이하 15자리까지 정밀도를 허용하며, 가변 정밀도 타입이라고도 한다.

Tip 필요한 숫자 타입 지정하는 방법

• 가능하면 정수를 쓴다.
• 소수점 데이터 다룰때 계산의 정확도가 요구되는 일이라면 numeric 혹은 decimal을 사용하자
• 충분히 큰 숫자타입을 지정하는게 좋다.

날짜와 시간: 시간 정보

timestamp

• 날짜와 시간. 추적할 수 있는 다양한 상황에서 유용한 날짜와 시간을 기록

date

날짜만 기록하며 SQL 표준의 일부

time

시간만 기록하며 SQL 표준의 일부

interval

수량 단위 형식으로 표현된 시간단위를 나타내는 값

JSON과 JSONB 이해하기

JSON

• 자바스크립트 개체 표기법의 약자
• 데이터를 저장하고 교환하는데 사용되는 구조화된 데이터형식
• 키/값 쌍과 값 목록의 컬렉션으로 정보 구성
• PostgreSQL이 지원하는 JSON 타입
  • json: JSON 텍스트를 그대로 저장
  • jsonb: JSON 텍스트를 바이너리 형식으로 변환해 저장. 인덱싱을 지원해서 처리 속도가 빠름

CAST()

• 데이터 타입 변환하는 함수

SELECT hire_date, CAST(hire_date as varchar(20)) 
FROM teachers;

-- 단축 표기법
SELECT hire_date, hire_date::varchar(20) 
FROM teachers;