데이터베이스(4) : SQL의 기초(2, 完)

SQL에서의 SET Operations

앞선 포스트의 기본적인 쿼리는 모두 set 연산이 아니어서 중복이 허용됐었다.

그렇다면 SQL에서 set 연산은 존재하지 않을까? 아니다. 여기도 set 연산이 존재한다.

큰 종류는 세 가지고, 중복 허용 여부에 따라 6개로 나뉜다.

• UNION
• UNION ALL
• INTERSECT
• INTERSECT ALL
• EXCEPT
• EXCEPT ALL

집합이므로 당연히 기본적으로는 중복되는 행을 제거하며, ALL을 붙일 경우 중복이 허용되는 구조이다.

합집합(Union operation)

합집합은 두 개 혹은 그 이상의 SELECT 연산들의 결합을 위해서 사용된다.

당연히 두 SELECT 연산을 합집합하려면, 해당 SELECT 연산의 결과가 같은 수의 열과 domain을 가져야한다.

SELECT * FROM First
UNION
SELECT * FROM Second;

중복이 제거되기 때문에 2와 3이 중복되지만 하나만 살아남은 모습이다.

여기서 만약 UNION을 UNION ALL로 바꿔준다면, 중복이 허용되어 (1, 2, 2, 3, 3, 4)가 결과 테이블에 저장될 것이다.

교집합(Intersect operation)

교집합도 기존의 알고있는 개념대로 두 테이블에 모두 존재하는 값만 결과 테이블에 저장되는 연산이다.

중복을 제거하므로 양 쪽에 모두 존재하면 값이 두 개겠지만 하나만 저장된다.

교집합 연산도 마찬가지로 같은 수의 열과 domain을 가져야한다.

SELECT * FROM First
INTERSECT
SELECT * FROM Second;

차집합(Except operation)

차집합 또한 알고있는 것처럼, 특정 테이블에는 존재하지만 다른 테이블에는 존재하지 않는 값을 반환하는 연산이다.

SELECT * FROM First
EXCEPT
SELECT * FROM Second;

위처럼 2와 3이 양 테이블에 모두 존재하지만, 1은 테이블1에만 존재하므로 결과 테이블에는 1만 저장되는 모습이다.

특히 차집합은 교환법칙이 성립하지 않음에 주의하자. 1 except 2와 2 except 1은 다른 결과를 내놓는다.

Null value와 Aggregate functions

데이터베이스에서 알 수 없는 값이나, 아예 비어있는 값일때 null을 이용하여 표시할 수 있다.

만약 수식에 null값이 포함되어 있다면, 그 결과는 항상 null이 나온다.

또, where 문에서 is null 키워드를 사용하면 null 값을 가진 부분의 다른 정보에 대해서 쿼리를 통해 받아올 수 있다.

SELECT name
FROM instructor
WHERE salary is null;

 

위와 같은 쿼리를 실행시키면, 월급 부분이 null로 되어있는 객체의 이름을 모은 테이블이 반환된다.

Aggregate Functions

집계 함수라고 직역할 수 있지만, 편의상 영어로 부르거나 섞어 쓰도록 하겠다.. 영어수업을 들어 한글용어가 익숙치않다.

이 함수들은 테이블의 값들에 대해 특정 연산을 수행하고, 그 결과값을 반환하는 함수들이다.

• avg : 평균값 계산해서 return
• min : 최솟값 return
• max : 최댓값 return
• sum : value 합 return
• count : 개수를 세서 return

일반적으로 이 함수가 실행되는 방식은 두 가지가 있다.

 

1. app에서 DB로부터 data를 넘겨받아서 직접 계산

2. app에서 DB에 요청하고, DB에서 계산 후 값을 app에 return

두 방법 모두 절대적인 계산의 양은 같다. 하지만 1번의 경우가 더 많은 cost를 사용하게 되어 2번이 자주 사용된다.

 

각설하고, 위의 함수들을 사용하는 예시를 알아보자.

특정 전공을 가진 사람들의 월급의 평균을 알아보는 쿼리를 작성한다고 치면,

SELECT avg(salary)
FROM Employees
WHERE dept_name = 'Comp.Sci';

 

다음과 같이 avg 함수를 사용해주면, 테이블이 아닌 value가 반환된다.

혹은 전공이 컴퓨터공학인 사람들의 수를 중복없이 센다고 하면,

SELECT count(distinct salary)
FROM Employees
WHERE dept_name = 'Comp.Sci';

 

위처럼 쿼리를 작성해 실행해주면 된다. 전체 사원의 수를 센다고 가정하면,

SELECT count(*)
FROM Employees;

 

다음과 같이 작성하면 모든 사원의 수를 센 값이 반환된다.

이때, count(*)의 경우는 null값 까지 센다는 것을 꼭 기억하자. 해당 경우에만 특별하게 적용된다.

Aggregate Functions - GROUP BY

GROUP BY라는 키워드를 사용하여서 특정 열을 그룹화시킬 수 있다.

만약, 각 전공별 교수들의 평균 월급을 알고싶다고 가정해보자.

그렇다면 우선 원본 테이블에서 각 전공별로 묶어주는 작업이 필요하다. 이때 GROUP BY가 사용된다.

주로 특정 열에서 반복해서 등장하는 값이 있을 때 사용하는 키워드이며, GROUP BY 후 쿼리문에서 오류가 나는 일이

잦기 때문에 조심해서 사용해야한다.

SELECT dept_name, avg(salary) as avg_salary
FROM instructor
GROUP BY dept_name;

 

맨 밑의 GROUP BY 문이 해당 테이블을 dept_name을 기준으로 묶어주는 역할을 한다.

그러면 해당 열에서 같은 값을 가진 객체끼리 그룹핑되고, 이후 집계함수인 avg 함수에 의해 각 그룹의 평균값이 계산된다.

최종적으로 select 문에 의해서 각 전공별로 평균 월급이 얼마인지 정리된 테이블이 반환될 것이다.

이런 식으로 실행이 된단 이야기이다. 이때 위의 쿼리에서 as는 특정 값에 새로운 이름을 붙일 때 사용한다.

이때, GROUP BY에서 중요한 개념이 나오는데, 바로

집계 함수 바깥에 있는 select 문의 attribute는 반드시 GROUP BY list에서 나타낼 수 있어야 한다는 것이다.

이게 무슨 말이냐면, 위의 쿼리는 전공을 기준으로 그룹핑했고, 각 그룹의 평균 월급을 집계 함수로 계산했다.

전공 이름이 그룹핑 되어 각 전공이 한 번만 리스트에서 등장하고, 평균을 냈으니 그 값도 하나만 나온다.

따라서 정상적으로 결과 테이블에 표시할 수 있으니 오류가 나지 않는다.

 

하지만 결과 테이블에 ID를(select clause에 ID를) 추가하려고 해보면, 당연히 오류가 발생한다.

전공을 가진 사람마다 각각 다른 ID를 가지고있고, 이를 하나로 묶어서 표현할 방법이 없어 나타낼 수 없기 때문이다.

ID를 표현하려면 억지로 평균을 내거나, 최대 최솟값을 구해 하나만 나타내게끔 해야한다.

GROUP BY를 사용할 때는 select에 있는 값들이 그룹핑된 결과 테이블에 표시될 수 있어야 한다는 점을 꼭 기억하자.

Aggregate Functions - Having Clause

만약 쿼리에서 GROUP BY 키워드를 사용한다면, 조건문을 having 키워드를 사용해서 작성해야한다.

위의 쿼리에서 salary가 특정 값 이상인 경우만 보고싶다면, having 키워드를 사용해 쿼리를 작성해야한다.

SELECT dept_name, avg(salary) as avg_salary
FROM instructor
GROUP BY dept_name
having avg(salary) > 42000;

 

이때, 해당 쿼리에서도 where문을 사용할 순 있는데, 이때 where문은 반드시 group by 키워드 이전에 작성되어야 한다.

where은 그룹핑을 하기 전에 작동하는 조건문이고, having은 그룹핑을 한 후에 작동하는 조건문이라고 생각하자.

Nested Subqueries, Set Comparison

SQL은 더욱 동적이고 효율적인 작동을 위해서 서브쿼리의 중첩 기능을 제공해준다.

이때, 서브쿼리는 select, from, where 모든 부분에 들어갈 수 있는데, 한 가지 중요한 점은

각 부분에 들어가기 위해서는 서브쿼리의 결과가 알맞는 형태여야 한다는 것이다.

예를 들어 select 문에는 attribute들이 들어가므로, 서브쿼리의 결과값 또한 attribute여야 중첩시킬 수 있다.

In syntax

서브쿼리에서 가장 기본적으로 쓰이는 문법이 In이다. In은 논리연산자 중 하나로,

특정 열에 있는 값들이 내가 가진 값과 매칭되는지 테스트하기 위해서 쓰는 문법이다.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT statement); // 서브쿼리가 들어가는 위치

 

이런 식으로 작동하는데, 서브쿼리가 가져온 value list와 column_name의 value와 매칭되는 것이 있는지 확인해준다.

예를 들어서 2021년 가을에도 열렸고 2022년 봄에도 열리는 과목의 코드를 가져오는 쿼리를 작성해보면,

select distinct course_id
from section
where semester = 'Fall' and year = 2017 and course_id in (select course_id
                                                          from section
                                                          where semester = 'Spring' and year = 2018);

 

다음과 같은 형태로 작성된다. 이걸 보면 그냥 having을 쓰면 되는거 아니냐? 라고 할 수 있는데, 맞는 말이다.

상황에 따라 서브쿼리 중첩과 조건문을 잘 섞어서 사용하면 된다. 서브쿼리는 단순히 쿼리의 효율을 위해 추가된 기능이다.

만약 매칭되지 않는 경우를 가져오고 싶다면, not in을 사용해주면 된다.

Some clause

in이 값을 매칭시켜주는 문법이었다면, some은 특정 기준을 만족하는 값들을 찾는 문법이다. 형태는 다음과 같다.

SELECT column_name(s)
FROM table_name
WHERE column_name [comparison_operator] some (SELECT statement); // 서브쿼리가 들어가는 위치

 

특히 some은 서브쿼리의 값들 중 단 하나라도 조건을 만족한다면 결과를 반환한다.

다음과 같이 70 > some { } 의 형태로, 서브쿼리의 값들 중 5라는 값이 있다고 하면, 70>5를 만족하므로 결과가 반환된다.

반면에 70 < some { }의 형태라면, 단 하나도 70보다 큰 값이 없으니 결과가 반환되지 않을 것이다.

 

만약 소비자가 거주 지역이 영국인 경우에 속하는 인원의 agent_code를 가져오는 쿼리를 작성한다면,

SELECT agent_code, agent_name
FROM agents
WHERE agent_code = SOME(SELECT agent_code
                        FROM customer
                        WHERE cust_country = 'UK');

 

다음과 같이 작성된다. customer 테이블에서 거주 지역이 영국인 객체의 agent_code를 value list로 받아서,

agents 테이블의 agent_code와 일치하는 값이 있다면 해당 객체의 code와 name을 가져오는 방식이다.

 

조금 더 직관적인 방식으로 이해해보자.

(5 < some {0, 5, 6}) = true
(5 < some {0, 5}) = false
(5 = some {0, 5}) = true
(5 != some {0, 5}) = true // 5 != 0 때문에 true반환

 

네 개의 예시를 들었다. 여기서 가장 중요한 점은, 단 하나만 만족해도 true를 반환한다는 것이다. 꼭 기억하도록 하자.

All clause

그렇다면 All은 모든 값과 비교해 특정 기준을 모두 만족해야만 결과값이 반환되는 것이라고 유추해볼 수 있다.

SELECT column_name(s)
FROM table_name
WHERE column_name [comparison_operator] all (SELECT statement); // 서브쿼리가 들어가는 위치

 

즉 All은 서브쿼리의 모든 값보다 크거나, 작거나 한 경우라고 보면 된다.

위의 그림처럼, 서브쿼리의 모든 값들이 70보다 작아야만 true로 결과값이 반환된다는 것이다.

하나라도 70보다 큰 값이 존재한다면, all의 조건을 위배하므로 false -> 결과값이 반환되지 않는다.

 

만약, 전공이 생물학인 교수들의 월급보다 항상 높은 월급을 받는 교수들의 이름을 받아온다고 하면,

SELECT name
FROM instructor
WHERE salary > ALL (SELECT salary
                    FROM instructor
                    WHERE dept_name = "Biology");

 

다음과 같이 쓸 수 있다. ALL 을 사용했으므로, 서브쿼리에 있는 모든 value보다 커야 result table에 추가될 수 있다.

여기서도 직관적인 예시를 들어서 보면 다음과 같다.

(5 < all {0, 5, 6}) = false
(5 < all {6, 10}) = true
(5 = all {4, 5}) = false
(5 != all {4, 6}) = true

 

all은 항상 모든 값과 비교해서 만족해야만 true를 반환해 result table에 포함시킬 수 있음을 기억하자.

따라서 일반적으로 all보다는 some이 효율 자체는 좋은 편이다. some은 하나만 만족하면 나머지 값은 비교하지 않지만,

all의 경우는 다 만족할 때까지 모든 값에 대해서 비교해야하기 때문이다.

Exists clause

마지막으로 알아볼 것은 exists 이다. exists는 단어에서도 알 수 있듯이 존재여부를 판별하는 문법이다.

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT statement); // 서브쿼리가 들어가는 위치

 

사용하는 방법은 다음과 같고, 서브쿼리의 결과에 있는 값이 바깥의 main 쿼리에도 '존재'하면 값을 result에 추가한다.

가장 간단하고 딱히 설명할 것이 없어서, 쿼리만 보여주고 넘어가겠다.

 

만약 Customer라는 테이블과 Order 테이블에서 같은 id가 존재하면 해당 행을 result에 추가하는 쿼리를 작성하면,

SELECT name
FROM Customer
WHERE EXISTS (SELECT *
              FROM Orders
              WHERE Customers.cutromer_id = Orders.c_id);

 

이와 같이 작성하면 된다. 여기까지가 select를 사용하는 쿼리에서의 여러 바리에이션이었고,

이제부터는 데이터베이스를 직접적으로 수정하는 쿼리들에 대해서 알아볼 것이다.

삭제(Deletion)

말 그대로 데이터베이스에 존재하는 데이터를 삭제하는 문법이다. 하나만 삭제할 수도 있고, 여러개 삭제할 수도 있다.

기본적인 쿼리의 형태는 다음과 같다.

DELETE (*)		// *은 있으나 없으나 all
FROM table_name
WHERE some_condition;

 

DELETE 뒤에 아무것도 붙이지 않거나 *을 적으면 모든 데이터가 삭제된다.

이때 데이터는 삭제되지만 데이터베이스의 schema, 즉 relation 자체가 사라지는 것은 아니다.

몇 개의 객체를 지우냐는 where문의 조건에 따라 달라지게 된다.

 

이런 데이터가 존재한다고 생각해보자. 여기서 특정 이름을 가진 학생 데이터만 삭제하려면,

DELETE
FROM Student
WHERE NAME = 'Ram';

 

이와 같이 작성하면 해당 객체만 삭제된다. 혹은 나이가 20인 객체를 지우는 쿼리를 작성하면,

DELETE
FROM Student
WHERE Age = 20;

 

이러면 나이가 20인 두 개의 객체가 삭제될 것이다.

삽입(Insertion)

삽입은 테이블에 새로운 행(객체, 데이터)을 추가하는 것으로, INSERT INTO 키워드를 사용한다.

행을 추가하는 방법에는 크게 값만 집어넣는 방식과, 어떤 열에 어떤 값을 넣을지 세부적으로 지정하는 방식이 있다.

두 쿼리 예시를 모두 적어서 차이점을 보여주면,

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

 

이 방식이 값만 집어넣는 방식으로, 앞에서부터 1열, 2열, 3열에 저장된다. 열의 개수보다 적으면 null로 채워진다.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

 

이 방식은 특정 열에 특정 값을 넣는 방식이다. 이때도 안채워지는 열이 있다면 null로 채워진다.

위의 Student 테이블을 이용해서, 19살인 HARSH 라는 학생을 추가한다고 해보면,

INSERT INTO Student
VALUES ('5', 'HARSH', 'WEST BENGAL', 'XXXXXXXXXX', '19');

 

이런 식으로 쿼리를 작성하면 테이블에 행이 하나 추가될 것이다.

이렇게 직접 하나 하나 행을 추가할 수도 있지만, 만약 Student 테이블이 여러개라면,

하나의 테이블의 내용을 다른 테이블에 복사할 수도 있다. select를 어떻게 쓰느냐 따라 세 가지 방법으로 나뉘는데,

 

1. 테이블2 전체를 테이블1에 복사한다.

2. 테이블2의 특정 열의 데이터만 테이블1에 복사한다.

3. 테이블2의 특정 조건을 만족하는 객체를 테이블1에 복사한다.

 

하나씩 어떻게 쿼리를 작성하고 사용되는지 살펴보도록 하겠다.

이런 형태의 StudentA 와 StudentB 테이블이 있다고 해보자.

StudentB의 모든 객체를 StudentA에 복사하려면 다음과 같이 쿼리를 작성하면 된다.

INSERT INTO StudentA
SELECT * FROM StudentB;

 

이러면 두 테이블이 합쳐진 것처럼 업데이트된다. 이제 2번의 경우를 알아보자.

위의 쿼리에서 ROLL_NO, NAME, AGE만 가져오고 싶다면, 세부적으로 쿼리를 작성하면 된다.

INSERT INTO StudentA(ROLL_NO, NAME, Age)
SELECT ROLL_NO, NAME, Age
FROM StudentB;

 

이러면 세 개의 열의 정보만 모두 가져오게 되고, 정보가 없는 ADDRESS와 PHONE은 null 값으로 채워질 것이다.

마지막으로 3번의 경우는, 정보를 가져올 테이블의 select 문에서 where을 사용하면 된다.

만약 나이가 18세인 객체만 StudentA에 포함한다고 하면,

INSERT INTO StudentA
SELECT *
FROM StudentB
WHERE Age = 18;

 

다음과 같이 where문을 사용하면 특정 조건을 만족하는 객체만 복사해올 수 있다.

수정(Update)

마지막으로 알아볼 가장 기본적인 SQL의 문법은 데이터를 수정하는 update이다.

update문도 이전의 문법 연산들과 마찬가지로 하나의 열만 수정할 수도, 여러 개의 열을 수정할 수도 있다.

기본적인 update 쿼리의 형태는 다음과 같다.

UPDATE table_name
SET column1=value1, column2=value2,,,
WHERE condition;

 

보면 알듯이, SET 구문은 특정 열의 값을 다른 값으로 바꿔주는 실제 수정하는 부분이고,

WHERE문은 이 수정이 적용될 특정 행(객체)을 지정하는 부분이다. WHERE문이 없으면 모든 객체에 대해 수정된다.

크게 어려운 부분이 없으니 바로 예시를 보도록 하자.

 

여기서 나이가 20세인 학생의 이름을 Kstone으로 바꾸는 쿼리를 작성하면 다음과 같다.

UPDATE Student
SET NAME = 'Kstone'
WHERE Age = 20;

 

위의 경우는 하나의 열의 값만 바꾸는 것이고, 당연히 여러 개의 열의 값도 수정해줄 수 있다.

ROLL_NO가 1인 학생의 이름을 Kstone으로 바꾸고, Age를 24로 바꾸는 쿼리를 작성해보겠다.

UPDATE Student
SET NAME = 'Kstone', Age = 24
WHERE ROLL_NO = 1;

 

이렇게 작성해주면 된다. 여기서 중요한 점은, WHERE문의 사용 유무이다.

사용할 경우는 조건에 맞는 특정 행만 수정되지만, 사용하지 않을 경우 모든 행에 대해 값이 수정됨을 주의하자.

 

 

이상으로 길고 긴 SQL의 기본에 대해 다루어보았다. 이번 포스트는 특히 예시와 내용이 많아 잘 읽어보길 바란다.

이전 포스트에 쿼리를 연습할 수 있는 사이트를 올렸었는데, 아쉽게도 SELECT를 제외한 구문은 사용하지 못한다.

서버에서 데이터베이스의 형태를 고정해둔 것이기 때문에, 우리가 이 데이터를 직접 수정하지는 못한다.

따라서 해당 사이트는 SELECT 구문을 연습할 때 유용하게 사용하길 바란다.