데이터베이스 / SQL 구문 문법

SQL

Structured Query Language, 관계형 데이터베이스의 데이터를 다루는 언어

• SQL은 ANSI와 ISO에서 표준화된 언어로, 대부분의 RDBMS에서 사용할 수 있음
• 데이터베이스에서 데이터를 조작하는 명령어를 제공함

 

---

 

DDL(Data Definition Language)

데이터 구조를 정의하고 위한 언어

데이터 속성에 잘 맞는 데이터 타입을 지정하는 것이 중요

 

• create : 데이터베이스, 테이블 등 생성
• alter : 테이블 수정
• drop : 데이터베이스, 테이블 삭제
• truncate : 테이블 초기화
• rename : 테이블 이름 변경

 

DML(Data Manipulation Language)

데이터 조회, 조작을 위한 언어

• select : 데이터 조회
• insert : 데이터 삽입
• update : 데이터 수정
• delete : 데이터 삭제

 

DCL(Data Control Language)

데이터베이스 접근 권한, 회수를 위한 언어

• grant : 유저에게 특정 작업에 대한 수행 권한을 부여
• revoke : 유저에게 특정 작업에 대한 수행 권한을 박탈, 회수

 

TCL(Transaction Control Language)

트랜젝션 별 제어를 위한 언어

• commit : 트랜잭션의 작업을 저장
• rollback : 트랜잭션의 작업을 취소하고 이전의 상태로 되돌림
• savepoint : 저장점을 지정 후 rollback과 함께 사용해 특점 지점까지 rollback을 하게 함 

 

---

 

 

데이터 타입

PostgreSQL	MySQL	설명
Char(1)	Char(x)	글자 하나 저장 가능
VARCHAR(x)	VARCHAR(x)	길이 x만큼 글자 저장 가능
TEXT	TEXT	매우 긴 문자열 저장 가능
BOOLEAN	tinyint	참, 거짓 표현
ENUM('bad', 'good', 'great')	ENUM('bad', 'good', 'great')	지정한 값만 넣을 수 있는 데이터 타입

 

숫자 타입, autoincrement때문에 serial이 id값으로 많이 사용된다

 

DateTime, timestamp(3)이 자주 사용된다

 

 

---

 

 

CRUD

Create, Read, Update, Delete. 생성 조회 갱신 삭제를 의미

 

CREATE

CREATE : 테이블 생성

CREATE TABLE accommodation (
  id serial primary key,
  name varchar(255) not null,
  address text not null,
  city varchar(100) not null,
  country varchar(100) not null,
  rating decimal(3, 2),
  description text,
  created_at timestamp default current_timestamp
);

 

 

INSERT : 데이터 추가 

INSERT INTO accommodation (name, address, city, country, rating, description)
VALUES
    ('Nomad House', '123 Beach Rd', 'Canggu', 'Indonesia', 4.5, '멋진 바다'),
    ('Remote Work Haven', '456 Mountain St', 'Chiang Mai', 'Thailand', 4.7, '아름다운 산'),
    ('Digital Nomad Loft', '789 City Center', 'Medellin', 'Colombia', 4.8, '도시 중심');

 

이 때 created_at은 테이블 생성 시 default 값을 timestamp로 주었기 때문에, 따로 지정하지 않아도 레코드 삽입시 자동으로 기록됨

 

---

 

READ

SELECT : 테이블 로우 조회

--- 전체 컬럼 조회
SELECT * FROM accommodation;

-- 일부 컬럼 조회
SELECT name, description FROM accommodation;

--- 조건
--- where 키워드 사용
SELECT * FROM accommodation WHERE city = 'Bali';

-- WHERE + OR
SELECT * FROM accommodation WHERE city = 'Chiang Mai' OR city = 'Bali';

-- 평점 4.75점 이상
SELECT * FROM accommodation WHERE rating >= 4.75;

-- 평점 4.5점 이상 4.6 이하
SELECT * FROM accommodation WHERE rating BETWEEN 4.5 AND 4.6;

-- ID가 1, 3
SELECT * FROM accommodation WHERE id IN (1, 3);

-- ID가 1, 3이 아님
SELECT * FROM accommodation WHERE id NOT IN (1, 3);

-- order by : 정렬 키워드
-- asc(오름차순)은 기본값
-- 내림차순 (큰 것 부터)
SELECT * FROM accommodation ORDER BY rating DESC;

-- PostgreSQL은 id 순서롤 보장하지 않기 때문에, 순서가 중요한 경우 꼭 sort를 해준다.
SELECT * FROM accommodation;
SELECT * FROM accommodation ORDER BY id ASC;
SELECT * FROM accommodation ORDER BY created_at ASC;

-- 평점 상위 3개
SELECT * FROM accommodation ORDER BY rating DESC LIMIT 3;

-- 전체 로우 갯수
SELECT COUNT(*) FROM accommodation;

-- 전체 평점 평균, 최솟값, 최댓값
SELECT AVG(rating) as avg_rating FROM accommodation;
SELECT MIN(rating) as min_rating FROM accommodation;
SELECT MAX(rating) as max_rating FROM accommodation;

-- 전체 하루 숙박 가격 평균, 최솟값, 최댓값
SELECT AVG(price_per_night) as avg_price FROM accommodation;
SELECT MIN(price_per_night) as min_price FROM accommodation;
SELECT MAX(price_per_night) as max_price FROM accommodation;

-- 테이블이 가지고 있는 모든 국가
SELECT DISTINCT country FROM accommodation;
SELECT COUNT(DISTINCT country) FROM accommodation;

 

---

 

UPDATE

update : 데이터 갱신

-- 현재 데이터 확인
SELECT * FROM accommodation;

-- UPDATE 쿼리 실행 전 WHERE 조건 확인
SELECT * FROM accommodation
WHERE id = 1;

-- UPDATE 쿼리 실행
UPDATE accommodation
SET name = 'wow'
WHERE id = 1
RETURNING *;

-- 원래대로 변경
UPDATE accommodation
SET name = 'Nomad House'
WHERE id = 1
RETURNING *;

-- UPDATE 쿼리 실행 전 WHERE 조건 확인
SELECT * FROM accommodation
WHERE country = 'Thailand' AND rating < 5.0;

-- WHERE 조건에 해당하는 여러 로우의 값을 변경
-- 기존 rating 값을 활용할 수 있음
UPDATE accommodation
SET rating = rating + 0.1, price_per_night = price_per_night + 1
WHERE country = 'Thailand' AND rating < 5.0
RETURNING *;

-- 원래대로 변경
UPDATE accommodation
SET rating = rating - 0.1, price_per_night = price_per_night - 1
WHERE country = 'Thailand' AND rating < 5.0
RETURNING *;

-- WHERE를 뺴먹으면 로우 전체에 영향을 미칠 수 있다.
UPDATE accommodation
SET price_per_night = price_per_night - 1
RETURNING *;

-- 이전으로 변경
UPDATE accommodation
SET price_per_night = price_per_night + 1
RETURNING *;

 

---

 

DELETE

delete : 데이터 삭제

-- 현재 데이터 확인
SELECT * FROM accommodation;

-- id가 1인 로우 삭제
DELETE FROM accommodation
WHERE id = 1
RETURNING *;

DELETE FROM accommodation
WHERE id = 2 OR id = 3
RETURNING *;