시작하며
오늘은 데이터베이스를 사용하기 위해 필요한 SQL에 대해서 학습하였다. 프레임워크나 라이브러리를 학습했던 것과 달리 완전히 새로운 언어를 배우기 때문에 많은 내용을 이해하기 위해 시간을 들였다. 다행히 SQL은 실제 언어와 비슷하기 때문에 어렵지 않게 명령어를 익힐 수 있었다.
SQL
정의
SQL이란 Structured Query Language의 약자로 데이터베이스 용 프로그래밍 언어이다. 데이터베이스에 쿼리를 보내 원하는 데이터를 가져오거나 삽입할 수 있고 (relation 이라고도 불리는) 데이터가 구조화된(structured) 테이블을 사용하는 데이터베이스에서 활용할 수 있다.
쿼리
쿼리(query)란 '질의문' 이라는 뜻을 가지고 있는데, 예를 들면 검색을 할 때 입력하는 검색어가 일종의 쿼리라고 할 수 있다. 검색을 할 때, 기존에 존재하는 데이터를 검색어로 필터링하는 것처럼 쿼리는 저장되어 있는 데이터를 필터하기 위한 질의문으로도 볼 수 있다.
데이터베이스 명령어
- 데이터 베이스 생성 :
CREATE DATABASE 데이터베이스_이름;
- 데이터 베이스 사용 :
USE 데이터베이스_이름;
- 테이블 생성 :
CREATE TABLE user ( id int PRIMARY KEY AUTO_INCREMENT, name varchar(255), email varchar(255) );
- 테이블 정보 확인 :
DESCRIBE user;
SQL 명령어
SELECT
데이터셋에 포함될 특성을 특정한다.
SELECT 'hello world'SELECT 2SELECT 15 + 3
FROM
FROM 뒤에는 결과를 도출해낼 데이터베이스 테이블을 명시한다.
SELECT 특성_1 FROM 테이블_이름SELECT 특성_1, 특성_2 FROM 테이블_이름SELECT * FROM 테이블_이름
WHERE
필터 역할을 하는 쿼리문으로 선택적으로 사용할 수 있다.
특정 값과 동일한 데이터 찾기
SELECT 특성_1, 특성_2 FROM 테이블_이름 WHERE 특성_1 = "특정 값"특정 값을 제외한 값을 찾기
SELECT 특성_1, 특성_2 FROM 테이블_이름 WHERE 특성_2 <> "특정 값"특정 값보다 크거나 작은 데이터를 필터할 때에는 '<', '>', 비교하는 값을 포함하는 '이상', '이하' 값은 '<=', '>=' 을 사용
SELECT 특성_1, 특성_2 FROM 테이블_이름 WHERE 특성_1 > "특정 값" // SELECT 특성_1, 특성_2 FROM 테이블_이름 WHERE 특성_1 <= "특정 값"문자열에서 특정 값과 비슷한 값들을 필터할 때에는 'LIKE'와 '\%' 혹은 '*' 를 사용
SELECT 특성_1, 특성_2 FROM 테이블_이름 WHERE 특성_2 LIKE "%특정 문자열%"리스트의 값들과 일치하는 데이터를 필터할 때에는 'IN' 을 사용
SELECT 특성_1, 특성_2 FROM 테이블_이름 WHERE 특성_2 IN ("특정값_1", "특정값_2")값이 없는 경우 'NULL' 을 찾을 때에는 'IS' 와 같이 사용
SELECT * FROM 테이블_이름 WHERE 특성_1 IS NULL값이 없는 경우를 제외할 때에는 'NOT' 을 추가해 이용
SELECT * FROM 테이블_이름 WHERE 특성_1 IS NOT NULL
ORDER BY
오름차순 정렬
SELECT * FROM 테이블_이름 ORDER BY 특성_1내림차순 정렬
SELECT * FROM 테이블_이름 ORDER BY 특성_1 DESC
LIMIT
결과로 출력할 데이터의 갯수를 정할 수 있다.
SELECT * FROM 테이블_이름 LIMIT 200
DISTINCT
유니크한 값을 받고 싶을 때에는 SELECT DISTINCT 를 사용할 수 있다.
특성_1을 기준으로 유니크한 값들만 선택
SELECT DISTINCT 특성_1 FROM 테이블_이름특성_1, 특성_2, 특성_3의 유니크한 '조합' 값들을 선택
SELECT DISTINCT 특성_1 ,특성_2 ,특성_3 FROM 테이블_이름
INNER JOIN
둘 이상의 테이블을 서로 공통된 부분을 기준으로 연결
SELECT * FROM 테이블_1 JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B
OUTER JOIN
'LEFT OUTER JOIN'으로 LEFT INCLUSIVE을 실행
SELECT * FROM 테이블_1 LEFT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B'RIGHT OUTER JOIN'으로 RIGHT INCLUSIVE을 실행
SELECT * FROM 테이블_1 RIGHT OUTER JOIN 테이블_2 ON 테이블_1.특성_A = 테이블_2.특성_B
GROUP BY
데이터를 조회할 때 그룹으로 묶어서 조회한다.
customers 테이블의 모든 레코드를 State에 따라 그룹화
SELECT * FROM customers GROUP BY State;
HAVING
HAVING은 GROUP BY 로 조회된 결과를 필터링할 수 있다.
invoices 테이블을 CustomerId로 그룹화하고 그 평균이 6을 초과한 결과를 조회
SELECT CustomerId, AVG(Total) FROM invoices GROUP BY CustomerId HAVING AVG(Total) > 6.00
COUNT()
COUNT 함수는 레코드의 갯수를 헤아릴 때 사용한다.
모든 레코드에 대한 COUNT 함수 사용 예시
SELECT *, COUNT(*) FROM customers GROUP BY State;각 State에 해당하는 레코드의 갯수를 확인하는 COUNT 함수 예시
SELECT *, COUNT(*) FROM customers GROUP BY State;
SUM()
레코드의 합을 리턴하는 함수
invoice_items라는 테이블에서 InvoiceId 필드를 기준으로 그룹하고, UnitPrice 필드 값의 합을 리턴
SELECT InvoiceId, SUM(UnitPrice) FROM invoice_items GROUP BY InvoiceId;
AVG()
레코드의 평균값을 계산하는 함수
SELECT InvoiceId, SUM(UnitPrice) FROM invoice_items GROUP BY InvoiceId;
MAX(), MIN()
MAX 함수와 MIN 함수는 각각 레코드의 최대값과 최소값을 리턴하는 함수이다.
SELECT CustomerId, MIN(Total) FROM invoices GROUP BY CustomerId
SELECT 실행 순서
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
SELECT CustomerId, AVG(Total) FROM invoices WHERE CustomerId >= 10 GROUP BY CustomerId HAVING SUM(Total) >= 30 ORDER BY 2 // 1. FROM invoices: invoices 테이블에 접근을 한다. 2. WHERE CustomerId >= 10: CustomerId 필드가 10 이상인 레코드들을 조회한다. 3. GROUP BY CustomerId: CustomerId를 기준으로 그룹화한다. 4. HAVING SUM(Total) >= 30: Total 필드의 총합이 30 이상인 결과들만 필터링한다. 5. SELECT CustomerId, AVG(Total): 조회된 결과에서 CustomerId 필드와 Total 필드의 평균값을 구한다. 6. ORDER BY 2: AVG(Total) 필드를 기준으로 오름차순 정렬한 결과를 리턴한다.
ACID
ACID는 데이터베이스 내에서 일어나는 하나의 트랜잭션(transaction)의 안전성을 보장하기 위해 필요한 성질이다.
- Atomicity
- Consistency
- Isolation
- Durability
Atomicity(원자성)
원자성은 하나의 트랜잭션에 속해있는 모든 작업이 전부 성공하거나 전부 실패해서 결과를 예측할 수 있어야 한다. 하나의 단위로 묶여있는 여러 작업이 부분적으로 실행된다면, 업데이트가 일어났지만 누가 업데이트했는지 모르거나, 업데이트 날짜가 누락되는 등 데이터가 오염될 수 있다. 그렇기 때문에 특정 쿼리를 실행했는데 부분적으로 실패하는 부분이 있다면, 전부 실패하도록 구현되어 있다.
Consistency(일관성)
하나의 트랜잭션 이전과 이후, 데이터베이스의 상태는 이전과 같이 유효해야 한다. 다시 말해, 트랜잭션이 일어난 이후의 데이터베이스는 데이터베이스의 제약이나 규칙을 만족해야 한다는 뜻이다. 예를 들어 '모든 고객은 반드시 이름을 가지고 있어야 한다'는 데이터베이스의 제약이 있다고 가정하면 이름 없는 새로운 고객을 추가하는 쿼리, 기존 고객의 이름을 삭제하는 쿼리는 일관성을 위반한다.
Isolation(격립성, 고립성)
모든 트랜잭션은 다른 트랜잭션으로부터 독립되어야 한다는 뜻이다. 실제로 동시에 여러 개의 트랜잭션들이 수행될 때, 각 트랜젝션은 고립(격리)되어 있어 연속으로 실행된 것과 동일한 결과를 나타낸다.
Durability(지속성)
하나의 트랜잭션이 성공적으로 수행되었다면, 해당 트랜잭션에 대한 로그가 남아야 한다. 만약 런타임 오류나 시스템 오류가 발생하더라도, 해당 기록은 영구적이어야 한다는 뜻이다.
SQL vs NoSQL
데이터베이스는 크게 관계형 데이터베이스와 비관계형 데이터베이스로 구분하며, 관계형 데이터베이스는 SQL을 기반으로 하고, 비관계형 데이터베이스는 NoSQL로 데이터를 다룬다. SQL과 NoSQL은 만들어진 방식, 저장하는 정보의 종류, 그리고 저장하는 방법 등에 차이가 있다.
관계형 데이터베이스에서는 테이블의 구조와 데이터 타입 등을 사전에 정의하고, 테이블에 정의된 내용에 알맞은 형태의 데이터만 삽입할 수 있습니다. 관계형 데이터베이스는 행(row)과 열(column)로 구성된 테이블에 데이터를 저장합니다. 각 열은 하나의 속성에 대한 정보를 저장하고, 행에는 각 열의 데이터 형식에 맞는 데이터가 저장된다. 특정한 형식을 지키기 때문에, 데이터를 정확히 입력했다면 데이터를 사용할 때에는 매우 수월하다. 관계형 데이터베이스에서는 SQL을 활용해 원하는 정보를 쿼리할 수 있는데, 이 말은 관계형 데이터베이스에서는 스키마가 뚜렷하게 보인다는 말과 같다. 다시 말해, 관계형 데이터베이스에서는 테이블 간의 관계를 직관적으로 파악할 수 있다.
대표적인 관계형 데이터베이스는 MySQL, Oracle, SQLite, PostgresSQL, MariaDB 등이 있다.
NoSQL은 SQL 앞에 붙은 'No'에서 알 수 있듯이, 주로 데이터가 고정되어 있지 않은 데이터베이스를 가리킨다. NoSQL이 SQL과 반대되는 개념처럼 사용된다고 해서, NoSQL에 스키마가 반드시 없는 것은 아니며, 관계형 데이터베이스에서는 데이터를 입력할 때 스키마에 맞게 입력해야 하는 반면, NoSQL에서는 데이터를 읽어올 때 스키마에 따라 데이터를 읽어 온다. 이런 방식을 'schema on read'라고도 한다. 읽어올 때에만 데이터 스키마가 사용된다고 하여, 데이터를 쓸 때 정해진 방식이 없다는 의미는 아니고, 데이터를 입력하는 방식에 따라, 데이터를 읽어올 때 영향을 미친다.
대표적인 NoSQL은 몽고DB, Casandra 등이 있다.
관계형 데이터베이스
1:1 관계
하나의 레코드가 다른 테이블의 레코드 한 개와 연결된 경우
1:N 관계
하나의 레코드가 서로 다른 여러 개의 레코드와 연결된 경우
N:N 관계
여러 개의 레코드가 다른 테이블의 여러 개의 레코드와 관계가 있는 경우로, N:N(다대다) 관계를 위해 스키마를 디자인할 때에는, Join 테이블을 만들어 관리한다. 1:N(일대다) 관계와 비슷하지만, 양방향에서 다수의 레코드를 가질 수 있다.
자기참조 관계(Self Referencing Relationship)
하나의 엔티티타입내에서 엔티티와 엔티티가 관계를 맺고 있는 형태의 관계이다. 이 관계는 1:N(일대다) 관계와 유사하다고 생각할 수 있으나, 일반적으로 일대다 관계는 서로 다른 테이블의 관계를 나타낼 때 표현하는 방법이다.
마치며
SQL을 배우면서 데이터베이스를 어떤 식으로 사용하고 얼마나 중요한지에 대해서 알 수 있었다. 클라이언트와 서버간 리소스 교환이 필요한 사이트라면 거의 무조건 데이터베이스가 사용될 것이다. 내일은 데이터베이스를 설계하는 법을 학습하면서 어떻게 하면 효율적으로 스키마를 디자인 할 수 있는지 배울 예정이다. 오늘은 새로운 언어에 대해 익숙해 질 수 있도록 복습을 하고 실제 명령문을 MySQL을 통해 학습해 볼 예정이다.
