데이터베이스(3) : SQL의 기초(1)

SQL(Structed Query Language)이란?

SQL은 데이터베이스를 관리하기 조작하기 위한 언어로, 데이터를 정의, 수정, 검색하는데에 사용한다!

세부적으로 쓰임새에 따라 크게 네 가지로 분류할 수 있다.

• DDL(Data Definition Language)
  
  DDL은 데이터를 저장할 구조, 즉 schema나 structure를 정의하는 문법이라고 생각하면 된다.
  DDL을 통해 표를 만들거나, 인덱스를 부여하거나 유저를 만들 수 있다.
  예시로는 CREATE, ALTER, DROP, RENAME 등이 있다. 자세한 사용법은 계속해서 알아볼 것이다.
  
  
• DML(Data Manipulation Language)
  
  DML은 DDL을 통해 만들어진 테이블(표)에 데이터를 삽입, 삭제, 수정, 검색할 수 있게 해주는 문법이다.
  가장 자주, 많이 쓰게 될 것이고 앞선 포스트에서 본 SELECT, DELETE, UPDATE, INSERT가 해당한다.
  
  
• DCL(Data Control Language)
  
  DCL은 데이터 접근 권한을 관리하는 문법이다. 위의 DML을 사용할 권한을 부여하는 문법으로,
  GRANT, REVOKE 등의 명령어를 통해 특정 유저나 role에 권한을 부여하거나 회수할 수 있다.
  
  
• TCL(Transaction Control Language)
  
  TCL은 트랜잭션에 관련된 문법으로, 트랜잭션을 커밋하거나, 롤백, 세이브포인트로 되돌리는 기능이 있다.
  
  

이제 각 언어에 대해 하나씩 알아보도록 하겠다. TCL은 따로 다루지 않을 예정이다.

DDL(Data Definition Language)

DDL은 간단하게 위에서 설명했고, DDL을 이용해서 테이블을 생성할 때 어떤 것이 명시되어야 하냐면,

• 각 relation의 schema
• 테이블의 각 attribute의 domain
• 무결성 제약조건
• 각 테이블의 인덱스
• 각 테이블의 보안과 권한 정보

이 정도가 명시되어야하고, 나머지는 알고있지만 무결성 제약조건은 처음 보는 단어일 것이다.

DDL에서 굉장히 중요한 부분 중 하나이므로, 자세하게 설명하도록 하겠다.

무결성 제약조건(Integrity Constraints)

무결성 제약조건은 DDL을 통해 테이블을 생성할 때 지켜야하는 일종의 규칙이다.

이를 통해 정보의 퀄리티를 높게 유지할 수 있고, 데이터의 무결성을 유지하면서 데이터를 수정할 수 있음을 보장한다.

결론적으로, 무결성 제약조건은 데이터의 손상을 방지하는데 중요한 역할을 한다고 할 수 있다.

무결성 제약조건에는 크게 네 가지가 있다. 편의상 용어는 영어로 적어두겠다. 영어로 보는 편이 후에 도움이 될 것이다.

Domain constraints

도메인 제약조건은 테이블의 각 attribute의 도메인이 테이블을 정의할 때 지정해주어야 하고, 이것이 지켜져야 한다.

도메인은 앞에서 이야기 했듯이 일종의 자료형이다. string, char, int, time, date 등 우리가 아는 자료형으로 구성된다.

테이블을 정의할 때 도메인을 지정하면, 데이터가 추가되거나 했을 때 이 도메인에 맞추어서 추가되어야 한다.

이런 경우에서, Age 열에 A라는 char값이 들어가는 것은 허용되지 않는다. Age의 도메인은 정수이기 때문이다.

이 제약조건을 확인하려면, 조건을 추가해서 직접 찾아봐야 하지만,

데이터의 퀄리티를 위해서라면 이러한 제약조건은 반드시 지켜져야 한다. 

Entity integrity constraints

엔티티 무결성 제약조건은 고유키의 값이 null(비어있으면)이면 안되는 것을 의미한다.

이전 포스트에서 설명했는데, 모든 테이블은 반드시 하나의 고유키를 가지고 있어야하고, 그 값이 없으면 안된다고했다.

고유키 값이 없으면 해당 entity(row, object)를 다른 객체들과 구별할 수가 없기 때문이다.

고유키에 해당하는 열만 null값이 있으면 안되고, 나머지 열에는 null값이 있어도 괜찮다. 구별하는데 크게 상관이 없다.

해당 테이블의 경우, 고유키 값에 해당하는 ID 값이 비워져있다. 이런 경우가 엔티티 무결성 제약조건에 위배되는 경우다.

Referential integrity constraints

참조 무결성 제약조건은 두 개 이상의 테이블이 있을 때 지켜져야 하는 규칙이다.

여기서는 외래키(foreign key)라는 개념이 사용되는데, 이는 테이블1과 테이블2가 있다고 가정했을 때,

두 테이블이 정보를 나눠서 저장해둔 각각의 테이블이라면, 그 둘을 이어주기 위해 테이블2의 고유키를 

테이블1에서 고유키가 아닌 다른 attribute로 사용하는 경우 이를 외래키라고 이야기한다.

 

이때 외래키를 사용하는 테이블에서는, 해당 열에서 특정 값을 골랐을 때 테이블2와 연결되는 관계가 있어야한다.

만약 테이블1에서 외래키 열의 값이 202인데, 테이블2에는 202라는 값이 존재하지 않는다면 둘 사이의 관계가 없는 것.

이런 경우 외래키를 사용하는 것에 모순이 생긴다.

따라서 외래키 제약조건은, 외래키로 사용하는 테이블의 각 value는 null이거나 테이블2에 존재해야 한다는 것이다.

위의 상황을 보면, 위의 테이블1에서 테이블2의 고유키인 면허번호를 외래키로 사용하고 있다.

즉, Tom의 면허증 번호가 201이고, 이 값을 타고 테이블2로 가면 위치가 서울임을 알 수 있다. 이런 관계가 존재한다.

그러나 202라는 값은 테이블2에 존재하지 않는다. 이런 경우가 참조 무결성 제약조건에 위배되는 경우이다.

이를 해결하려면 테이블2에 202 값을 추가하거나, 202가 null이 되면 해결된다!

가장 이해하기 까다로운 부분이 해당 제약조건이니 여러 번 읽어보고 이해하길 바란다.

Key constraints

키 제약조건은 각 엔티티를 고유하게 식별하기 위해 존재하는 규칙이다.

하나의 테이블은 여러 개의 키를 가질 수 있지만, 그 중 딱 하나만 고유키가 된다.

고유키가 된 열은 각 객체가 고유한 값을 가져야 하며, null이 되면 안됨을 여태까지 배워왔다.

위와 같이 고유키 값이 고유하지 않은 경우 위배

키 제약조건에는 두 가지 유형이 있다. 하나는 위의 고유키에 대한 경우고, 두 번째는 외래키에 대한 경우이다.

 

위에서 외래키를 조금 어렵게 설명했는데, 쉽게 이야기하자면

관계형 데이터베이스에서 두 테이블 간의 연결고리를 제공해주는 키라고 설명할 수 있다.

특히 하나의 테이블에서는 고유키로 작동해야 하고, cross-reference 라고 생각하면 된다.

이때, 외래키로 사용하는 테이블에서는 해당 값들이 중복되도 괜찮다. 원본은 고유키지만, 참조에는 관련이 없다.

외래키 사용 예시

해당 사진을 보면 더 이해가 쉬울 것이다. Parent에서 CustID가 고유키로 사용되고 있고,

Child에서 해당 열을 외래키로 참조하여 사용하고 있다. 자세히 보면 값이 중복되는 것을 확인할 수 있다.

왜냐면 개인이 연락처를 여러개 가지고 있을 수 있기 때문이다.

따라서 Parent에서 한 사람을 선택하면, ID값을 참조해 해당 인물의 연락처들을 수집할 수 있다.

이런 식으로 외래키가 사용된다고 생각하면 된다!

Domain Types in SQL

가장 대표적인 SQL에서의 도메인 타입들에 대해 살펴보고 넘어가자.

• char(n) : 고정 길이의 문자열이다. 길이 n을 지정해줄 수 있다.
• varchar(n) : 가변 길이의 문자열이다 최대 n글자의 문자열을 저장할 수 있다.
• int : 정수 자료형
• smallint : int보다는 더 작은 자료형
• numeric(p, d) : 고정된 길이의 실수를 표현할 수 있다. p개의 숫자를 적을 수 있고, 그중 d글자가 소수부분 길이이다.
  만약 numeric(3, 1)이라면, 44.5 와 같은 값이 저장될 수 있다.
• real, double precision : 부동 소수점으로 표현하는 실수, double precision 사용
• float(n) : n개의 글자를 소수 부분으로 쓸 수 있는 실수.

테이블 생성(Create Table Construct)

그럼 이제 앞선 정보들을 바탕으로 테이블을 생성하는 쿼리를 작성해보자.

우선 기본적으로 create table 이라는 명령어를 통해 테이블을 생성한다. 이때 테이블의 기본적인 정보에 대해 적어준다.

create table r(A1 D1, A2 D2, ,,, An Dn,
		(integrity-constraint_1),
        	...
        	(integrity-constraint_k));

 

이와 같은 형태로 테이블을 생성하게 된다. 이때 $A_i$는 attribute의 이름을 의미하고, $D_n$은 그 성질의 domain이다.

더 직관적인 예를 들어서 설명하자면,

create table instructor(
	    ID	char(5),
            name	varchar(20),
            dept_name	varchar(20),
            salary	numeric(8, 2));

 

이와 같이 테이블을 정의할 수 있다. attribute의 이름을 지정하고, 해당 attribute의 domain을 지정하는 식이다.

이외에도 무결성 제약조건을 위한 것들을 지정할 수도 있다.

위의 상태에서 ID를 고유키로 지정하고, dept_name을 dapartment 테이블로부터 참조하는 외래키로 지정하면,

create table instructor(
	    ID	char(5),
            name	varchar(20),
            dept_name	varchar(20),
            salary	numeric(8, 2)),
            primary key (ID),
            foreign key (dept_name) references department);

 

이렇게 적어서 정의하면 된다.

기본적인 쿼리의 구조

전형적인 SQL 쿼리의 구조는 다음과 같다.

select A1, A2, ,,, An
from r1, r2, ,,,  r_m
where P

 

여기서도 마찬가지로 $A_n$은 읽어들일 attribute, $r_m$은 정보를 읽을 테이블(relation), P는 조건이다.

즉, 특정 테이블로부터, 조건을 만족하는 attribute에 있는 값들을 가져오는 쿼리가 되는 것이다.

당연하게도 SQL 쿼리의 실행 결과도 테이블로 반환된다. 특히 select 쿼리는 정보를 읽어들이는 쿼리이다.

 

만약 where 절을 추가하지 않고 실행시키면, 조건없이 모든 내용이 반환된다.

더불어 all을 *(asterisk)로 표현하여 쿼리에 적어줄 수도 있다.

SELECT *
FROM Customer;

 

이런 쿼리를 작성했다면, 테이블에 있는 모든 정보가 리턴된다. 즉 복사본이 하나 생기는 것이다.

정보를 가져와야하는 데이터베이스에 대한 정보가 없을 때 주로 사용하는 쿼리이다.

이미 데이터베이스 schema의 구조를 안다면 필요한 attribute만 가져와서 읽어들이면 된다.

 

위의 쿼리의 결과가 사진과 같다고 해보자. 여기서 이름과 주소, 도시만 알고 싶다면 다음과 같이 쿼리를 수정하면 된다.

SELECT CustomerName, Address, City
FROM Customer;

원하는 attribute만 골라오는 모습

The select clause

여기까지 봤다면 한 가지 비슷한 것이 생각날 것이다. 바로 이전 포스트의 관계 대수에서 project 연산과 매우 흡사하다.

헷갈리지 말자. 관계대수에서의 project가 SQL 쿼리의 select와 비슷하다. 관계대수의 select와 혼동하지 말자!

추가로, SQL 쿼리는 대소문자를 구분하지 않는다. 테이블 이름이 Name이라면, NAME, name, NaME 모두 인식한다.

 

게다가 SQL은 set 연산이 아니어서 중복을 허용한다. 쿼리의 결과에서 중복을 제거하고 싶다면,

select 다음에 distinct를 적어주면 된다. 반대로 다 표시하고 싶다면 all을 적어주면 된다.

사실 아무것도 안적는 것이 all과 동일한 결과지만, 이 쿼리를 보는 사람들이 직관적으로 인식하게끔 써주는 것이 좋다.

The from clause

from 문은 쿼리를 통해서 정보를 불러올 테이블을 적어주는 부분이다.

하나의 테이블에서만 다룰 것이라면 하나만 적으면 되지만, 여러개를 다룰 경우 쉼표로 구분해준다.

이때, 여러 개의 테이블을 적게 되면 해당 테이블들의 카테시안 곱을 한 테이블에서 정보를 읽게 된다.

select *
from instructor, teaches;

 

이와 같은 쿼리가 있다면, instructor X teaches 를 한 테이블이 리턴된다는 것이다.

마찬가지로 카테시안 곱을 하므로, 두 테이블에 같은 이름의 attribute가 있다면 instructor.ID 와 같은 식으로 구분된다.

카테시안 곱 복습사진

만약 두 테이블의 ID가 동일한 객체의 이름을 알고싶다면, 다음과 같이 쿼리를 작성하면 된다.

select name
from instructor, teaches
where instructor.ID = teaches.ID;

The where clause

위의 예시를 설명하기위해 where 문을 먼저 작성해버렸는데, where 문은 조건을 적어두는 부분이다.

위의 경우는 두 테이블에서 ID가 같은 객체를 찾기 위한 조건으로 해당 where 문이 사용되었다.

where 문 또한 직접 쿼리의 결과를 보며 알아보자.

 

이런 데이터를 가진 Product라는 데이터베이스가 있다고 하자. 우리는 여기서 가격이 20보다 저렴한 물건을 찾고,

그 물건을 제품 이름의 알파벳 내림차순으로 정렬하고 싶다. 이를 쿼리로 작성하면,

SELECT *
FROM Products
WHERE Price < 20 ORDER BY ProductName DESC;

실행 결과

 

다음과 같이 결과 테이블에는 가격이 20보다 저렴한 물건이 이름 역순으로 정렬되어서 저장된다.

이때 where 문에 적을 조건은 관계대수에서 배웠듯이 논리연산자를 결합해서도 사용이 가능하다는 점을 기억하자.

그러나 대소비교를 하는 비교연산자는 domain이 numeric한 경우에만 적용이 가능하다.

사실 데이터베이스의 구현이 어떻게 되어있느냐에 따라 다르지만, 일반적으로는 그렇다.

 

where 문에서 사용할 수 있는 약간의 스킬들을 조금 설명하고 포스트를 마치겠다.

만약 특정 범위에 있는 값을 가져오고 싶다면, 비교연산자를 사용시 Price > 20 and Price < 30 과 같이 표현해야 한다.

그러나 between 키워드를 이용하면, 이를 Price between 20 and 30으로 표현 가능하다.

SELECT ProductName
FROM Product
WHERE Price between 20 and 30;

 

추가로, python에서 tuple이라는 자료형을 배웠다면 여기서도 요긴하게 써먹을 수 있다.

(a1, a2) = (b1, b2)와 같이 적으면 a1 = b1, a2 = b2 인 정보만 읽어올 수 있다.

SELECT name
FROM instructor, teaches
WHERE (instructor.ID, dept_name) = (teaches.ID, 'Biology');

 

이런 식으로 사용할 수 있다.

마지막으로 위에서 ORDER BY 라는 키워드를 사용했는데, 이는 결과를 오름차순 혹은 내림차순으로 정렬할 때 사용한다.

ORDER BY + (atrribute name)의 형태로 사용되며, 해당 attribute를 정렬한다는 의미이다.

아무것도 적지 않으면 오름차순으로 정렬, DESC를 적으면 내림차순(descending order)으로 정렬된다.

 

이상으로 SQL의 첫 번째 포스트의 내용은 끝이다.

기본적인 내용들이니 매우 쉽게 이해가 될 것이다. 특히 이런 명령어 류는 여러번 실행해보며 익숙해지는 것을 추천한다.

내가 공부할 때 사용했던 SQL 연습 사이트를 소개하고 끝내겠다.

 

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

SQL Tryit Editor v1.6

 

해당 사이트에서는 여러 데이터베이스 예시를 통해 쿼리를 작성 시 어떤 결과가 반환되는지 테스트해볼 수 있다!

여기서 다양한 조합의 쿼리를 실행해보며 어떻게 작동하는지 연습하면 매우 좋을 것 같다.

이상으로 다음 포스트때 뵙도록 하겠다.