SQL 입문

모든 예시는 sqlite3을 기준으로 작성하였습니다.

이 포스트에서는 아래의 내용을 다룹니다.

 

• 테이블 생성
• Domain Type
• 테이블 업데이트
  • insert
  • delete
  • drop
  • alter
  • update
• Query
  • select
  • from
  • where
• Relational Language
  • DML
  • DDL
• Aggregate Function
  • avg
  • min
  • max
  • sum
  • count
• Window Function
  • row_number
  • rank
• 기타 Query operation
  • like (%, _), substring, ||
  • Set operation (union, intersect, except)
  • Set membership (in) / Set comparison (exists, unique)
  • order by
  • limit
• Null value
• Subqueries

 

테이블 생성

create table student (
  ID          varchar(5),
  name        varchar(20) not null,
  dept_name   varchar(20),
  tot_cred    numeric(3, 0),
  primart key (ID),
  foreign key (dept_name) references department
);

위 문법은 아래와 같은 테이블을 생성할 수 있다.

table(relation) 이름: student

attributes: ID, name(반드시 값이 있어야 함), dept_name, tot_cred

primary key: ID

foreign key: dept_name (이 attribute의 값은 department table에 존재하는 값이여야 함)

 

 

Domain Type

varchar와 같은 여러 domain type에 대해 알아보자.

 

- char(n)

반드시 n 길이를 갖는 문자열만 저장할 수 있음.

 

- varchar(n)

최대 n 길이를 갖는 문자열을 저장할 수 있음.

 

- int, smallint

정수 타입만 저장할 수 있음

 

- numeric(p, d)

부동 소수점 타입을 저장할 수 있음.

총 p 자릿수를 저장할 수 있고, 소숫점 아래로는 d 자리까지 저장할 수 있음.

numeric(3, 1)
[OK] 44.5
[FAIL] 444.5, 1.23

 

- real, double precision

실수 타입을 저장할 수 있음

 

- float(n)

최소 n 자릿수의 실수 타입을 저장할 수 있음

 

 

테이블 업데이트

테이블에 변화를 이르키는 명령어에 대해 알아보자

 

- Insert

insert into instructor values ('10211', 'Smith', 'Biology', 66000);

instructor 테이블에 ('10211', 'Smith', 'Biology', 66000) tuple을 추가한다.

참고로 빈 값은 자동으로 null로 채워진다.

 

insert into instructor
  select ID, name, dept_name, 18000
  from student
  where dept_name = 'Music' and total_cred > 144;

음악학과에 144 학점 초과인 학생들을 18000 연봉으로 교수진 테이블에 추가한다.

(delete 와 마찬가지인 방식으로 sql은 tuple을 추가한다. (아래에서 설명))

 

- Delete

delete from student;

student 테이블의 모든 tuple을 제거한다.

 

delete from instructor
where dept_name in (
                   select dept_name
                   from department
                   where building = 'Watson'
                   );

instructor 테이블에서 Watson 건물에 있는 학과에 소속된 교수의 tuple을 제거한다.

 

단, sql은 제거하는 중 where 절에 조건이 달라져 의도하지 않은 값이 삭제되는 현상을 방지하기 위해

아래와 같은 방법을 사용하여 tuple을 제거한다.

1. 조건에 맞는, 삭제 대상인 tuple을 찾는다.

2. 삭제 대상인 tuple을 제거한다. (여기선 조건을 검사/계산하지 않는다)

 

- Drop Table

drop table student;

student tuple을 포함한 테이블 자체를 제거한다.

 

- Alter

alter table department add code numeric(4, 0);
alter table department drop code;

department 테이블에 code attribute를 추가, domain type은 numeric(4, 0). 초기값은 null.

department 테이블에 code attribute를 제거한다.

 

- Update

update instructor
  set salary = salary * 1.03
  where salary > 100000;
update instructor
  set salary = salary * 1.05
  where salary <= 100000;

100000만 이하의 연봉을 갖는 사람은 5%, 초과는 3%의 연봉을 올리는 쿼리문이다.

두 쿼리문의 배치 순서가 중요한데, 위/아래 쿼리문의 위치가 바뀐다면 아래와 같은 문제점이 발생할 수 있다.

연봉 99000인 교수는 위 쿼리문으로 5% 상승을 받고, 100000 이 넘어가게 된다.
아래 쿼리문을 실행하면 3%의 추가 상승을 받게된다.

 

update instructor
  set salary = case
                 when salary <= 100000 then salary * 1.05
                 else salary * 1.03
               end;

위와 같이 case문을 사용하면 순서를 걱정할 필요가 없다.

 

update student S
  set tot_cred = (
                 select case
                          when sum(credits) is not null then sum(credits)
                          else 0
                        end
                 from takes, course
                 where takes.course_id = course.course_id and
                       S.ID = takes.ID and
                       takes.grade <> 'F' and
                       takes.grade is not null
                 );

student 테이블에 tot_cred attribute을 업데이트 하는데,

수강한 수업에서 학점이 F, null 이 아닌 수업의 학점을 더한 값을 업데이트를 한다.

단, 합이 null 이면 0으로 설정한다.

 

 

Query

퀴리문을 살펴보며 명령어에 대해 알아보자.

그 전에 기본적으로 SQL은 문자의 대소문자에 민감하지 않다. 즉, 대소문자 구분없이 명령어를 처리한다.

 

- Select 절

select distinct dept_name
from instructor;
--
select all dept_name
from instructor;

기본적으로 select는 중복을 허용하여 결과를 내준다.

만약, 중복을 제거하고 싶다면 distinct를 붙여주면 되고,

명확히 중복을 허용하고 싶다면 all을 붙여주면 된다.

 

select 'A' 
from instructor;
--
select ID, name, salary/12 as monthly_salary

그 뒤에는 가져올, 표시하고 싶은 attribute의 이름을 적으면 된다.

그냥 attribute의 이름을 콤마로 나누어 나열하면 해당 attribute만 가져오고, *을 사용하면 모든 attribute를 가져온다.

attribute가 아닌 'A'와 같은 문자열을 적으면 해당 문자열을 값으로 가져오고,

attribute에 사칙연산식을 적용하여 연산 결과를 가져올 수 있다.

해당 column의 이름을 바꾸고 싶다면 as <바꾸고 싶은 이름>으로 바꿀 수 있다.

 

- Where 절

select name
from instructor
where dept_name = 'Comp. Sci.' and salary > 70000;

where절은 특정 조건을 만족하는 데이터를 필터링해준다.

and, or, not 같은 논리적 연결자를 지원하며

<, <=, >, >=, =, <> 같은 논리 연산자를 지원한다. (<>는 not)

 

- From 절

select *
from instructor, teaches;

from절은 데이터를 가져올 테이블을 지정한다.

테이블을 여러개 가져올 수 있으며, 이 때 여러 테이블은 Cartesian Product 형태로 가져오기 때문에

연산량이 기하급수적으로 늘어날 수 있음에 유의해야 한다.

 

select distinct T.name
from instructor as T, instructor S
where T.salary > S.salary and S.dept_name = 'Comp. Sci.';

select절과 마찬가지로 as를 통해 이름을 바꿀 수 있다.

참고로 from절에서 as는 생략할 수 있다.

 

 

Relational Language

- DML (Data Manipulation Language)

insert, update, delete, select 가 있다.

 

- DDL (Data Definition Language)

create, alter, drop 이 있다.

 

- DCL (Data Control Language)

권한 설정 관련 부분

 

 

Aggregate Function

select count (distinct ID)
from teaches
where semester = 'Spring' and year = 2018;

집합 함수는 특정 column의 모든 데이터를 연산한 값을 출력한다.

avg(평균값), min(최솟값), max(최댓값), sum(합), count(갯수) 가 있다.

 

select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name;

group by 연산자를 통해 특정 attribute끼리 묶어서 연산할 수 있다.

위의 경우는 dept_name 별로 묶어 계산하기 때문에 dept_name 별로 평균 연봉을 알 수 있다.

단, select문의 attribute 중, 집합 함수에 포함되지 않은 attribute는 반드시 group by 문에 있어야 한다.

그렇지 않다면 이상한 해당 column에 이상한 값이 출력되거나 에러가 발생한다.

 

select dept_name, avg (salary) as avg_salary
from instructor
-- where avg (salary) -- error
group by dept_name
having avg (salary) > 42000;

집합 함수 결과에 조건을 추가하고 싶다면 having 절을 사용하면 된다.

where 절을 사용하면 안되는 이유는, where 절은 group by 전에 먼저 실행되기 때문이다.

하지만 having 절은 group by 이후에 실행되기 때문에 논리적인 에러가 없다.

 

 

Window Function

select *, row_number() over () as row_num
from student;
--
select *, row_number() over (partition by dept_name) as row_num
from student;
--
select *, row_number() over (order by ID) as row_num
from student
order by ID;

aggregation function과 사용법은 비슷하지만, 결정적으로 다른 점은

window function은 묶여서 나오지 않고 tuple 개별적인 값을 제공한다는 점이다.

 

row_number() 는 행의 번호를 출력해주는데,

over절이 비어있으면 전체 테이블에서 행 번호를 출력해주고,

partition by 를 사용하면 group by와 비슷하게 같은 attribute로 묶인 데이터 안에서 행 번호를 출력해준다.

order by를 사용하면 정렬 후의 데이터를 기준으로 행 번호를 출력해준다.

 

select *, rank() over (partition by dept_name order by tot_cred) as rank
from student;

rank() 는 순위를 출력해준다.

위의 경우에는 dept_name으로 나누어 tot_cred가 높은 순서대로 1, 2, 3 등을 매긴다.

 

row_number와 rank의 차이점은 같은 데이터라도 row_number는 다르게 매긴다는 점이다.

 

 

기타 Query Operation

- String Operation

select name
from instructor
where name like '%dar\%' escape '\';

like 연산자를 통해 특정 문자열을 검사할 수 있다.

%는 어떠한 문자열이 들어와도 된다는 뜻이고,

_는 어떠한 문자 하나가 들어와도 된다는 뜻이다.

%, _을 문자 그자체로 쓰고 싶으면 escape 연산자를 통해 escape 문자를 지정하고 쓰면 된다.

 

select substring(name, 1, 2)
from student;

substring function을 통해 문자열 일부만 가져올 수 있다.

Woong -> Wo

 

select 'Mr. ' || name as mr_name
from student;

문자열을 이어 붙이는 연산도 한 번에 가능하다.

Woong -> Mr. Woong

 

- Set Operaton

select course_id from section where sem = 'Fall' and year = 2017
union
select course_id from section where sem = 'Spring' and year = 2018;

union, intersect, except 연산자를 통해 여러 결과를 or, and, substract 연산을 할 수 있다.

단, 위 연산결과는 중복이 제거되서 나온다.

중복을 허용하려면 union all, intersect all, except all 과 같이 all을 붙여주면 중복을 허용하여 연산한다.

 

- Set Membership(in, not in) / Set Comparison(exists, not exists, unique)

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
                   );
--
select distinct course_id
from section as S
where semester = 'Fall' and year = 2017 and
      exists (
             select *
             from section as T
             where semester = 'Spring' and year = 2018
                   and S.course_id = T.course_id
             );

위 두 쿼리문은 2017 가을학기와 2018 봄학기에 모두 열린 강의들을 출력하는 식이다.

사용법은 비슷하지만, 성능, 처리방식에서 명확한 차이를 보인다.

 

in 같은 경우에는 in 뒤에 있는 테이블에서 in 앞에 있는 데이터가 존재하는 지를 검사한다.

따라서 모든 subquery로 도출되는 테이블을 모두 탐색하는 수 밖에 없다.

그렇기에 테이블의 크기가 큰 경우에는 시간이 오래 걸릴 수 있다.

하지만, 테이블과 특정 attribute 들을 검사할 수 있기 때문에 여러 attribute를 tuple 형태로 묶어서

여러 조건을 한 번에 검사할 수 있다는 장점이 있다.

 

반면에 exists 같은 경우에는 exists 뒤에 있는 테이블에서 데이터가 하나라도 있는지를 검사한다.

따라서 subquery로 도출되는 테이블을 모두 검색하지 않을 수 있다.

그렇기에 보통의 경우 in을 사용하는 방법보단 시간이 적게 걸린다.

하지만, 여러 조건을 동시에 검사해야 하는 경우, subquery가 복잡해 질 수 있다.

또한, 반환되는 값이 true / false 기 때문에 null값과의 비교가 불가능하다.

 

select T.course_id
from course as T
where unique (
             select R.course_id
             from section as R
             where T.course_id = R.course_id and R.year = 2017
             );

위 쿼리문은 2017년에 최대 1번만 열린 강의의 목록을 출력하는 식이다.

unique는 뒤의 subquery의 데이터가 중복이 없는 경우에만 true를 반환한다.

 

- Order by

select distinct name
from instructor
order by dept_name desc, name asc;

order by를 통해 tuple의 순서를 정렬할 수 있다.

asc는 오름차순으로 정렬하는 연산자이고 이는 생략할 수 있다.

desc는 내림차순 정렬을 하는 연산자이다.

여러 정렬 기준을 순서에 따라 콤마로 구분지어 적용할 수 있다.

 

order by 뒤에 attribute 이름이 아닌 숫자가 오는 경우

해당 숫자 번호에 해당하는 attribute를 기준으로 정렬을 수행하게 된다.

예로 들어 Student(ID, name,  tot_credits, dept_name) 스키마로 정의된 테이블에 대해

order by 2 로 하면 name attribute 에 대해 오름차순으로 정렬하게 된다.

 

- limit

select ID, name
from student
limit 10 offset 20;

출력되는 데이터의 갯수에 제한을 걸 수 있다.

위의 경우 처음 20개의 데이터는 표시하지 않고 다음 데이터(21번) 부터 10개의 데이터(30까지)만 출력하도록 할 수 있다.

 

 

Null value

스키마에서 not null을 설정하지 않은 이상 null값을 사용할 수 있다.

데이터 무결성 검사를 위해 where 절에서 is null, is not null 로 null값을 검사할 수 있다.

 

null, unknown 값은 최종적으로 false 취급되긴 하나,

연산 도중에는 논리적 에러 방지를 위해 별도의 처리가 필요할 수 있다.

 

 

Subqueries

위의 Set Membership, Set Comparison 에서 where절의 subquery에 대해 살펴보았다.

물론 from, select 절에서의 subquery 역시 가능하다.

 

- from clause subquery

select dept_name, avg_salary
from (
     select dept_name, avg (salary) as avg_salary
     from instructor
     group by dept_name
     )
where avg_salary > 42000;

위에 Aggregate Function 설명하면서 having 절에 대한 설명을 했을 때 짰던 쿼리문과 동일한 결과를 출력한다.

다만, from 절 내부에서 avg aggregate function에 의한 column이 추가된 상태로

테이블이 반환되기 때문에 이 쿼리문에선 having 절이 필요가 없다.

 

- select clause subquery

select dept_name, 
       (
       select count(*)
       from instructor
       where department.dept_name = instructor.dept_name
       ) as num_instructors
from department;

위 쿼리문은 각 학과에 존재하는 교수들의 수를 이름과 함께 출력하는 식이다.

중요한 점은 select 절에서 사용하는 subquery의 반환 값은

반드시 1개의 attribute로만 이루어진 tuple이어야 한다는 점이다. (2개 이상시 런타임 에러 발생)

그렇기에 scalar subquery 라고도 불린다.

 

- with clause subquery

with dept_total (dept_name, value) as (
  select dept_name, sum(salary)
  from instructor
  group by dept_name
),
dept_total_avg (value) as (
  select avg(value)
  from dept_total
)
select dept_name
from dept_total, dept_total_avg
where dept_total.value > dept_total_avg.value;

with절을 사용하면 복잡한 쿼리식도 간단하게 작성할 수 있다.

연산된 테이블을 변수에 저장하여 사용할 수 있도록 도와준다고 생각하면 편하다.

위 쿼리문은 학과별 연봉의 합이 그 평균보다 큰 학과의 이름을 출력하는 식이다.