[DB] SQL

티라노·2025년 3월 18일
db

데이터베이스

목록 보기
4/11

SQL의 구성 요소

  • DML
    tuple을 insert, delete, modify하기 위한 언어
  • 무결성
  • View definition
  • Transaction
  • Embedded/Dynamic SQL
  • Data Definition

Data Definition Language(DDL)

DDL은 데이터를 특정하여 테이블을 정의한다. 정보의 무결성을 보장한다.

Domain types

SQL에서는 다양한 타입으로 테이블을 저장할 수 있다.

  • char, varchar : 불변/가변 길이 문자열
  • int, smallint : 32-bit/16-bit 정수
  • numeric : 고정 소수점, numeric(3.1) 처럼 나타낸다. 이 경우 3은 정수 자릿수, 0.1은 소수점 이하 자리수이다.
  • real, double procision, float : 부동 소수점

Create table

SQL로 테이블 생성

attribute1 type
attribute2 type
attribute3 type
.
.
.
primary key (ex)ID
foreign key (dept_name) reference department

select clause

relational algebra는 중복을 허용하지 않지만 SQL은 attribute를 선택할 때 중복을 허용한다. 이 때 select distinct 를 select 대신 사용하면 중복을 제거한 값을 얻을 수 있다.
기본적인 select에도 중복값이 있지만 특히 select all 을 사용하면 중복 값이 존재하는 채로 데이터를 얻을 수 있다.

SQL Operations

Ordering

order by name

order by는 기본값으로 오름차순 정렬을 가지고 있다. 하지만 asc 명령어를 붙여서 명시적으로 만들 수도 있다. 내림차순으로 정렬하고 싶다면 아래처럼 쓴다.

order by name desc

기준을 여러 개 둘 수도 있다. 이 경우 앞에 쓴 attribute부터 정렬한다.

order by department, name

Set Operations

select(합집합), intersect(교집합), except(차집합) 등을 사용할 수 있다.

// 합집합
select ~ UNION select ~

// 교집합
select ~ INTERSECT select ~

// 차집합
select ~ EXCEPT select ~

명령어 뒤에 ALL을 붙여 UNION ALL 처럼 쓰면 중복이 제거되지 않는다.

Null

where name is null
where name is not null

특정 attribute값이 null인 tuple에 조건을 줄 수 있다.
SQL은 이렇게 null값인 데이터를 unknown 으로 처리한다. (true도 false도 아님)

Aggregate Functions

데이터의 대표값을 집계하는 함수이다.
avg, min, max, sum, count 가 있다.

// avarage
select avg(salary) from instructor where dept_name='Comp. sci';

// distinct는 해당 attribute에서 중복값을 제외하는 명렁어이다
select count(DISTINCT ID) from teaches where semester='Spring';

// *는 모든 속성을 의미하므로 course table의 모든 tuple 개수를 세라는 의미이다
select count(*) from course;

group by

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

key값 별로 집계함수를 사용한 결과만을 table로 만들 수 있는데 이 때 group by를 쓴다. 위의 명령어를 수행하면 dept_name과 전공 별 소득으로 이루어진 테이블이 만들어진다.

groupping 조건에 포함되지 않는 속성을 포함하면 쉽게 에러가 난다.
예를 들어

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

에서 salary의 평균을 내더라도 ID 개수가 변하지 않으므로 테이블로 만들 수 없다.

Having Clause

집계 함수에도 having 조건을 넣을 수 있다.

select dept_name, ID, avg(salary) as avg_salary
from instructor
group by dept_salary
having avg(salary) > 10000;

subqueries

Set Membership

쿼리 안에 in 명령어를 통해 하위 쿼리문을 넣어서 join처럼 활용할 수 있다. 

select ~ from ~
where semester='spring' and year='2017' and course_id in (select ~ );

not in 을 쓰면 특정 값을 제외할 수 있다.

select name from instructor where name not in ('Mozart', 'Einstein');

집계 함수와 함께 쓸 수도 있다.

select count(ID) from takes
where tuple
in (select tuple from teaches where ID='10103');

다음은 예시이다

// 학생 수(id)를 집계
select count(distinct id)
from takes
where course_id
in
// subquery 시작
// id가 10101인 교수가 가르치는 course_id를 자신의 course_id로 가지는 학생
(
    select course_id
    from teaches
    where teaches.ID=10101
)

some Clause

some 조건(적어도 하나 이상)을 만족하는 쿼리문을 구성할 수 있다.
예를 들어 컴퓨터학과의 여러 교수 중에서 적어도 1명이라도 월급으로 추월한 교수를 전체 학과에서 찾을 수 있다. 이럴 때 some 명령어를 사용한다.

select name
from instructor
where salary > some (
  select salary
  from instructor
  where dept_name='Comp.Sci'
);

// some을 쓰지 않으려면?
// 최소치를 구한 다음 해당 값보다 크면 통과
select name
from instructor
where salary > (
  select min(salary) 
  from instructor
  where dept_name='Comp.Sci'
);

위 예시처럼 집계 함수 등을 써서 비교할 수도 있지만, 비교할 값 개수가 많거나 숫자로 비교할 수 없는 값이거나 할 때 some을 쓰지 않으면 오류가 난다.

＝some 은 in 과 같은 작용을 한다.
하지만 ≠some 이 not in 이라고 할 수 없다.

all Clause

all 조건(모든 대상에 대해 만족)을 만족해야 한다.

select name from instructor
where salary > all (select salary from instructor where dept_name='Comp.Sci');

≠all 은 not in 과 같은 작용을 한다.
하지만 ＝all 이 in 이라고 할 수 없다.

empty relations

원하는 tuple의 존재 여부는 exists와 not exists로 나타낼 수 있다.
쿼리문이 현재 테이블 + 바깥의 테이블에 연관된 경우 corelation이라고 한다.

select distinct S.ID, S.name
from student S
where not exists (
  (select course_id from course
  where dept_name = 'Biology')
minus
  (select T.course_id from takes T
  where S.ID = T.ID)
)

from절의 subquery

from절의 조건으로 subquery를 쓸 수 있다.

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

마찬가지로 위 코드에서도 where 대신 subquery 안에 having을 넣어서 구현할 수도 있다.

with Clause

임시 테이블을 만든다. select문 앞에 붙여서 사용한다.

with avg_budget (value) as
(select avg(budget) from department)
select department.dept_name
from department, avg_budget
where department.budget > avg_budget.value

예시처럼 구현하면 department의 budget 평균을 구한 값을 가지는 테이블이 생성된다.
이 테이블은 코드를 수행하는 동안만 유효하다.
with에서는 쉼표로 구분해서 여러 개의 테이블을 생성할 수도 있다.

scala subquery
with와 비슷하지만 with로 생성한 테이블이 여러 개의 attribute를 가질 수 있는 반면에 스칼라로 생성한 객체는 한 개의 값만 가진다.

예를 들어 numeric 평균값 하나만 필요하다면 scala subquery를 이용하는 것이 나을 수도 있다.

명령어는 따로 없고, subquery를 만들어서 as로 이름 붙인 뒤 사용하면 된다.

DB modification

삽입, 삭제, 수정 등 relation을 편집하는 것을 db modification이라고 한다.

insertion, deletion, update

DB modification에서는 case문을 활용할 수 있다.

update ~ set ~ case ~;

case문에서는 if문처럼 분기를 나뉘어 when과 else로 조건을 줄 수 있다.

case
  when(조건문) ~ then '(반환값)'
  else ~ '(반환값)'
end
profile
티라노
이전 포스트

[DB] 관계형 데이터베이스

다음 포스트

[DB] Intermediate SQL

0개의 댓글