[데이터베이스]SQL

SQL(Structured Query Language - 구조화된 쿼리 언어)은 데이터베이스용 프로그램 언어이다.

 

에스큐엘 혹은 시퀄이라고 읽으며, 데이터베이스 시스템에서 자료를 처리하는 용도로 사용된다.

 

이름에서 미루어볼 수 있듯이 SQL은 구조화된(Structured) 테이블을 사용하는

 

관계형 데이터베이스(RDB -Relational Database)에서 주로 쓰인다.

 

SQL문법을 보기 전에, 자주 쓰이는 단어에 먼저 익숙해지자.

 

• 반드시 알고 있어야 할 키워드

RDB를 사용할 때 각 부분을 부르는 명칭들이다. 소통을 위해 반드시 알고 있어야 한다.

 

이름	내용
데이터(Data)	각 항목에 저장되는 값
테이블(Table, Relation)	자료의 구조를 2차원 표로 나타낸 것
컬럼(Column, Field)	테이블의 각 열, 데이터 값
레코드(Record, Row, Tuple)	테이블의 각 행, 단일 구조 데이터 항목. 모든 레코드는 동일한 구조
키(Key)	레코드마다 가지는 고유한 값. 기본키(Primary Key)와 외래키(Foreign Key) 등이 있다.

 

• 쿼리(Query)?

쿼리는 질의문이라는 뜻을 가지고 있으며, 검색을 할 때 사용하는 검색어도 일종의 쿼리이다.

 

데이터를 필터링하기 위한 질의문이라고 봐도 된다.

 

SQL 쿼리는 데이터베이스를 참조하거나 수정하는데 쓰인다.

 

• 트랜잭션(Transaction)?

트랜잭션이란 더 이상 쪼갤 수 없는 일처리의 최소 단위를 뜻하며,

 

여기선 데이터베이스의 쿼리를 가장 작은 업무로 쪼갠 것을 말한다.

 

트랜잭션은 안전성을 보장하기 위한 4가지 속성을 가지는데, 이들의 앞 글자를 따 ACID라고 부른다.

 

• Atomicity(원자성) - 모든 트랜잭션은 작업이 전부 성공하거나 전부 실패해야 한다. 부분적인 실패는 전체의 실패를 뜻한다. A 계좌에서 B 계좌로 돈을 보낼 때, A 계좌에선 출금이 이루어졌으나 B 계좌에선 입금이 일어나지 않는 사태를 방지한다.
• Consistency(일관성) - 데이터베이스의 상태(제약, 규칙)는 트랜잭션 전후로 일관되어야 한다.
• Isolation(고립성) - 모든 트랜잭션은 다른 트랜잭션의 영향을 받지 않는다. 만 원이 들어있는 계좌에서 A, B 두 계좌로 동시에 6000원씩 이체 시도를 할 경우, A에 먼저 송금한 뒤 B로 보내는 것과 동일한 결과를 얻어야 한다.
• Durability(지속성) - 성공한 트랜잭션의 결과는 안정적으로 보존되어야 한다. 시스템 오류가 발생해도 기록은 영구적이어야 한다.

 

SQL 문법

 

많이 쓰이는 SQL 문법에 대해 정리한다. 아래와 같은 데이터 테이블이 있다고 하자.

 

Table - Customers

• CREATE, USE, INSERT, DELETE, DESCRIBE

데이터베이스 생성, 사용, 테이블 생성 및 데이터 입력 시 사용한다. 먼저 데이터베이스를 생성해 보자.

CREATE DATABASE Shop_Management;

생성한 데이터베이스를 사용해 테이블을 만들거나 수정하려면 먼저 선언을 해야 한다.

USE Shop_Management;

데이터베이스 안에서 테이블을 만들려면 다시 CREATE를 사용해야 한다.

CREATE TABLE Customers (
  CustomerID int PRIMARY KEY AUTO_INCREMENT,
  CustomerName varchar(255),
  ContactName varchar(255)
  ...
);

생성한 테이블에 데이터를 입력할 때는 INSERT를 사용한다.

INSERT INTO Customers (CustomerName, ContactName, ...)
VALUES ('Alfreds Futterkiste', 'Maria Anders', ...);

삭제는 DELETE를 사용한다.

DELETE FROM Customers 
WHERE CustomerName = 'Alfreds Futterkiste';

마지막으로 테이블의 정보를 확인하고 싶을 땐 DESCRIBE를 사용한다.

DESCRIBE Customers;

 

• SELECT, FROM, LIMIT

SELECT는 테이블에서 원하는 자료(컬럼 - Column)만 뽑아올 때 사용한다.

SELECT CustomerName FROM Customers
LIMIT 5;

SELECT는 CustomerName에 대한 컬럼만 가져오겠다는 뜻이고

 

FROM은 Customers 테이블에서 정보를 가져오겠다는 뜻이며,

 

LIMIT는 정보를 5줄만 뽑겠다는 뜻이다. 결과는 아래와 같다.

 

두 개 이상의 정보를 한 번에 요청할 수도 있다.

SELECT CustomerID, CustomerName, City FROM Customers
LIMIT 5;

모든 컬럼의 정보를 보려면 *를 입력하면 된다.

SELECT * FROM Customers
LIMIT 5;

 

• WHERE

필터 역할을 하는 쿼리이다. 예를 들면 Customers 테이블에서 프랑스 사람만 골라낼 때 사용한다.

SELECT * FROM Customers
WHERE Country='France';

CustomerID를 특정해 로우를 뽑아낼 수도 있다.

SELECT * FROM Customers
WHERE CustomerID between 4 and 6;

두 개 이상의 값을 뽑아낼 땐 IN을 사용한다.

SELECT * FROM Customers
WHERE Country IN ('Germany', 'France');

혹은 OR를 사용할 수도 있다.

SELECT * FROM Customers
WHERE Country='Germany' OR Country='France';

AND의 경우도 물론 존재한다.

SELECT * FROM Customers
WHERE Country='Germany' AND City='Berlin';

특정 값보다 크거나 작은 값을 요청할 땐 '>', '<', '>=', '<='를 사용한다.

SELECT * FROM Customers
WHERE CustomerID <= 3;

특정 값을 제외하고 싶을 땐 <>를 사용한다.

SELECT * FROM Customers
WHERE CustomerID <> 3
Limit 3;

문자열에서 특정 값과 비슷한 값들을 찾으려면 LIKE와 %를 사용한다(값이 일치해야 하면 IN을 사용).

SELECT * FROM Customers
WHERE CustomerName LIKE 'A%';

A로 시작하는 CustomerName을 찾아보았다. A로 끝나는 문자열을 찾고 싶으면 '%A'를 입력하면 된다.

 

중간에 A가 한 번이라도 들어간 문자열을 찾으려면 '%A%'와 같이 작성하면 된다.

 

물론 'A%A'와 같이 사용할 수도 있다.

 

위의 예에서 사용된 %와 꾸준히 사용하고 있는 *를 와일드카드(Wildcards)라고 묶어서 부르는데,

 

주로 WHERE 안에서 LIKE와 같이 사용된다. 와일드카드에 대해 짧게 정리하고 가자.

 

먼저 MS Access 와일드카드의 종류는 아래와 같다.

 

기호	설명	예제
*	0개 이상의 임의의 문자(열)를 매치	bl* finds bl, black, blue, and blob
?	알파벳 문자 한 글자와 매치	h?t finds hot, hat, and hit
[]	괄호 안의 문자중 하나와 매치	h[oa]t finds hot and hat, but not hit
!	괄호 안의 문자에 포함되지 않은 문자 하나와 매치	h[!oa]t finds hit, but not hot and hat
-	(오름차순)범위에 해당하는 문자 매치	c[a-b]t finds cat and cbt
#	숫자 하나와 매치	2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, 295

 

계속해서 SQL SERVER에서 사용하는 와일드카드의 종류는 아래와 같다.

 

기호	설명	예제
%	0개 이상의 임의의 문자(열)를 매치	bl% finds bl, black, blue, and blob
_	알파벳 문자 한 글자와 매치	h_t finds hot, hat, and hit
[]	괄호 안의 문자중 하나와 매치	h[oa]t finds hot and hat, but not hit
^	괄호 안의 문자에 포함되지 않은 문자 하나와 매치	h[^oa]t finds hit, but not hot and hat
-	(오름차순)범위에 해당하는 문자 매치	c[a-b]t finds cat and cbt

와일드카드는 명령문에서 '_A%'와 같이 섞어서 사용하는 것도 가능하다.

 

• IS, IS NOT

값이 없는 경우를 찾을 경우 IS NULL을, 비어있지 않은 경우는 IS NOT NULL을 사용한다.

SELECT * FROM Customers
WHERE Country IS NULL;

SELECT * FROM Customers
WHERE Country IS NOT NULL;

 

• DISTINCT

특정 컬럼에 어떤 종류의 값이 들어있는지 확인할 땐 DISTINCT를 사용한다.

SELECT DISTINCT Country FROM Customers;

 

• ORDER BY, DESC

데이터를 기준에 따라 정렬시킬 경우 ORDER BY를, 내림차순일 땐 DESC를 추가해 사용한다.

SELECT * FROM Customers
ORDER BY Country;

SELECT * FROM Customers
ORDER BY City DESC;

 

• (INNER) JOIN

INNER JOIN은 두 개 이상의 테이블의 교집합을 뽑아내는 쿼리이다.

 

기준을 정해 두 테이블을 합친다고 보면 된다.

 

예를 들어, 지금까지 사용하던 Customers 테이블에 더해 아래와 같은 Orders 테이블이 있다고 하자.

 

Table - Orders

Customers와 Orders는 공통적으로 CustomerID라는 컬럼을 가지고 있다.

 

이를 이용해 두 테이블을 INNER JOIN 하면 아래와 같이 된다.

SELECT Orders.OrderID, Customers.CustomerID, Orders.EmployeeID, Customers.CustomerName 
FROM Customers
INNER JOIN Orders ON Orders.CustomerID = Customers.CustomerID;

먼저 기존의 테이블을 선택하고(FROM Customers) 합칠 테이블을 INNER JOIN Orders로 이어준다.

 

ON 뒤에는 두 테이블을 이을때 사용할 조건을 입력하면 된다. 결과는 아래와 같다.

INNER JOIN 대신 JOIN을 입력해도 결과는 같다.

 

두 테이블의 겹치는 부분만 뽑아온 것을 확인할 수 있는데,

 

CustomerID - 1과 6의 경우엔 Orders 테이블에 존재하지 않아 제외된 것도 볼 수 있다.

 

• OUTER JOIN

OUTER JOIN은 세 종류가 존재한다.

 

그중 가장 중요한 LEFT JOIN만 살펴보고 넘어가자.

 

LEFT JOIN은 합칠 테이블에서 기존 테이블과 겹치는 부분만 가져오는 방식이다.

 

문법은 INNER JOIN과 같다.

SELECT Orders.OrderID, Customers.CustomerID, Orders.EmployeeID, Customers.CustomerName 
FROM Customers
LEFT JOIN Orders ON Orders.CustomerID = Customers.CustomerID
ORDER BY Customers.CustomerID;

Customers 테이블에 없는 정보는 null로 표시되는 것을 확인할 수 있다.

 

• GROUP BY, COUNT

GROUP BY는 관련 있는 레코드를 하나로 묶을 때 사용한다.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;

COUNT()는 괄호 안의 기준에 맞춰 레코드를 헤아릴 때 사용한다.

 

• HAVING

HAVING은 WHERE과 비슷하지만 그룹화된 이후 필터를 할 때 사용한다.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) >= 2;

 

• SUM, AVG, MAX, MIN

이름 그대로 합, 평균, 최대, 최솟값을 찾는 쿼리이다.

SELECT SUM(PostalCode) FROM Customers;

PostalCode 중 숫자만 더한 값을 보여주는 걸 볼 수 있다.

 

AVG, MAX, MIN도 동일하게 사용한다.

 

• SELECT 쿼리의 실행 순서

여태까지 알아본 SELECT 쿼리의 필터는 다음 순서로 동작한다.

 

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. ORDER BY

예를 들면 다음과 같다.

SELECT CustomerId, CustomerName, Country
FROM Customers
WHERE CustomerId <= 7
GROUP BY CustomerId
HAVING CustomerName LIKE 'B%'
ORDER BY 3;

마지막 줄의 ORDER BY 3은 3번째 컬럼을 기준으로 정렬하라는 의미이다.