[DB] 4. SQL

Park Yeongseo·2024년 2월 8일
Databasedbsql데이터베이스
1

DB

목록 보기
5/9
post-thumbnail

서울대학교 이상구 교수님의 SNUON 강의 데이타베이스: 빅데이터 시대의 필수 정보관리 개론와 Database System Concepts 7th Edition의 내용을 바탕으로 정리한 내용입니다.

1. History

  • 시스템 R 프로젝트에서 IBM Sequel 언어를 채택하면서 사용되기 시작함
  • SQL(Structured Query Language)로 재명명됨
  • ANSI와 ISO에서 표준을 제공
    + SQL-86, SQL-89, SQL-92
    + SQL:1999, SQL:2003, SQL:2008
  • 대부분의 상용 DBMS는 SQL-92의 기능을 지원하며, 여기에 이후 표준이나 특수한 고유 기능들을 추가해 제공한다.

2. Data Definition

Create Table Construct

DDL은 데이터 정의, DML은 데이터 조작 언어다.

SQL 릴레이션은 create table 커맨드를 사용해 정의된다.

create table r (A1 D1, A2 D2, ..., An Dn,
				(integrity-constraint 1),
				...
				(integrity-constraint k))
  • rr은 릴레이션의 이름이다.
  • 각 AiA_i는 릴레이션 rr의 스키마의 애트리뷰트 이름이다.
  • 각 DiD_i는 애트리뷰트 AiA_i의 도메인의 값의 데이터 타입이다.

Example

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

이렇게 만들어진 테이블에 실제로 레코드를 추가하기 위해서는 insert문을 사용한다.

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

Domain Types in SQL

  • char(n) : 길이 nn의 고정된 길이를 가지는 문자열
  • varchar(n) : 최대 길이 nn의 가변 길이 문자열
  • int : 정수.
  • smallint : 정수
  • numeric(p,d): 전체 p자리, 소수점 이하 dd자리의 고정 소수점 수
  • real, double precision: 부동 소수점 수
  • float(n): 최소 n자리의 부동 소수점 수

모든 도메인 타입에 대해 null 값이 허용된다.

Integrity Constraints in Create Table

무결성 제약 조건이란 테이블에 들어오는 데이터들이 지켜야 하는 규칙들이다. 테이블 생성 시 사용할 수 있는 무결성 제약 조건들에는 다음의 것들이 있다.

  • not null
  • primary key(A1, ..., An)
    + 기본 키는 기본적으로 not null, unique를 함축한다.
  • foreign key(Am, ..., An) references r

단일 애트리뷰트가 기본 키가 될 때에는 애트리뷰트 선언과 함께 선언할 수도 있다

create table instructor (
		ID char(5) primary key,
		name varchar(20) not null,
		dept_name varchar(20),
		salary numeric(8, 2)
)

Drop and Alter Table Constructs

  • drop table: 데이터베이스에서 릴레이션을 삭제한다
  • alter table: 존재하는 릴레이션에 애트리뷰트를 추가하거나 삭제할 때 사용한다.
alter table r add A D

AA는 추가할 애트리뷰트의 이름, DD는 AA의 도메인이다. 기존 레코드에는 해당 애트리뷰트에 기본값으로 null이 들어간다. 

alter table r drop A

AA는 삭제한 애트리뷰트의 이름이다. 다만 애트리뷰트의 삭제는 많은 데이터베이스에서 지원되지 않는다.

3. Basic Structure - Select / From/ Where

Basic Structure of SQL Queries

SQL은 집합과 릴레이션 연산에 기반하고 있다. 전형적인 SQL 쿼리는 다음의 형태를 가진다.

select A1, A2, ..., An
from r1, r2, ..., rm
where P

AiA_i는 애트리뷰트, rir_i는 릴레이션, PP는 술부(조건)다. 쿼리의 결과는 또한 릴레이션이다.

The select Clause

select 문에는 뽑아올 애트리뷰트의 이름을 명시한다. 와일드카드로 애스터리스크(*)를 쓸 수 있으며, 이는 모든 애트리뷰트를 가리킨다.

  • SQL은 애트리뷰트 이름에 '-'가 포함되는 것을 허용하지 않는다
  • SQL는 대소문자 구분을 하지 않는다.

SQL은 릴레이션 레코드에 중복을 허용하며, 쿼리 결과도 마찬가지다 중복을 없애기 위해서는 select 뒤에 distinct 키워드를 명시적으로 추가해야 한다. all 키워드는 중복을 제거하지 않을 때 사용하며, 디폴트이기에 꼭 명시하지는 않아도 된다.

select문에는 +,−,∗,/+, -, *, /의 산술식이 포함될 수 있다.

The where Clause

릴레이션 대수에서의 셀렉션 술부에 해당한다. 여기에는 from 절에 나타나는 릴레이션의 애트리뷰트가 포함되어야 한다.

비교 연산자(>, <, =, <=, >=, !=), 논리 연결사(and, or, not)을 사용할 수 있다. 비교는 산술식 결과에도 적용될 수 있다. SQL은 between 비교 연산자도 지원한다. 

where salary between 90000 and 100000

The from Clause

식을 계산할 때 스캔되어야 할 릴레이션들을 명시하는 것으로, 릴레이션 대수의 카테시안 곱에 상응한다.

정리하자면 다음의 SQL은 

select A1, A2, ..., An
from r1, r2, ..., rm
where P

릴레이션 대수식

∏A1,...,Anσp(r1×r2×...×rm)\prod_{A_1, ..., A_n} \sigma_p(r_1 \times r_2 \times ...\times r_m)

과 같다.

4. Additional Basic Operations

The Rename Operation

SQL에서는 as절을 이용해 릴레이션이나 애트리뷰트의 이름을 재명명할 수 있다.

(애트리뷰트의 재명명)

select ID, name, salary/12 as monthly_salary
from instructor

(릴레이션 재명명)

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

여기에서 T,ST, S를 튜플 변수(tuple variables)라 부른다.

as 키워드는 선택적으로, 생략할 수도 있다.

String Operations

like는 문자열의 비교를 위해 쓰인다. 특수 문자를 이용해 패턴을 나타낼 수도 있다.

  • %는 문자열, _는 정확히 한 문자에 대해 쓰인다
  • 문자로서의 %, _을 쓰고 싶을 때에는 이스케이프 문자를 이용해 \%, \_로 나타낸다.
select *
from course
where title like '%data%'

like외에도 문자열을 붙이거나, 대소문자를 바꾸거나, 문자열 길이를 계산하거나, 부분문자열을 얻거나 하는 등을 위한 연산들도 있다.

Ordering the Display of Tuples

쿼리 결과를 원하는 순서로 정렬해서 나타낼 수도 있다.

select *
from instructor
order by name desc
  • des는 내림차순, asc는 오름차순으로 정렬하며, 기본은 오름차순이다.
  • 여러 애트리뷰트를 기준으로도 정렬할 수 있다.
order by dept_name desc, name asc

Set Operations

집합 연산 union, intersect, except는 릴레이션 대수의 연산과 같다. 기본적으로 SQL은 중복을 제거하지 않지만, 집합 연산의 경우에는 기본적으로 중복을 제거한다. 중복을 제거하지 않으려면 all 키워드를 이용한다.

r union all s
r intersect all s
r except all s

Null Values

애트리뷰트는 널 값을 가질 수 있으며, 이는 null로 표기한다. null은 값을 알 수 없거나, 값이 없음을 나타내는데 쓰인다.

  • null이 포함된 산술식의 결과는 null이다.
  • is null은 널 값을 확인하는 데 쓰일 수 있다.
    + = null의 결과는 항상 unknown이고, 최종적으로는 false로 처리된다.

Three Valued Logic

  • null과의 비교는 항상 unknown이다.
  • Three-valued logic은 진리값 unknown을 사용한다.
    + unknown or true = true
    + false and unknown = false
    + 이외의 논리 연산 결과는 모두 unknown이다.
  • "PP is unknown"이 참는 경우는 조건문 PP의 결과가 unknown인 경우 밖에 없다.
    + = null이 아니라 is null을 쓰는 이유.
  • unknown를 결과로 가지는 조건문은 마지막에 false로 처리된다.

5. Aggregate Functions

집계 함수(aggregate function)는 릴레이션 컬럼의 여러 값들을 이용해 하나의 값을 반환하는 함수다.

  • avg : 평균
select avg(salary)
from instructor
where dept_name = 'Comp. Sci.';
  • min : 최솟값
  • max : 최댓값
  • sum : 합
  • count : 수

group by 문을 통해 특정 애트리뷰트에서 같은 값을 가지고 있는 레코드들을 하나로 묶을 수도 있다. 단 집계 함수 밖에 있는 select 절의 애트리뷰트는 반드시 group by 절에 포함되어야 한다.

# OKAY
select dept_name, avg(salary)
from instructor
group by dept_name;

# ERROR
select dept_name, ID, avg(salary)
from instructor
group by dept_name;

집계 함수의 경우에는 널을 무시하고, 오직 모든 값이 널일 때에만 널을 결과로 낸다. 단, count(*)의 경우에는 널 값을 무시하지 않는다.

6. Nested Subqueries

서브쿼리는 다른 쿼리에 들어가 있는 select-from-where 표현이다. 

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

아래의 쿼리

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

는 서브쿼리와 some문을 이용해 다음과 같이 나타낼 수도 있다.

select name
from instructor
where salary > some (select salary
					 from instructor
					 where dept_name = Biology);

서브쿼리 전체와 비교하고 싶을 때는 some 대신 all 키워드를 쓰면 된다.

Test for Empty Relations

exists는 인자 서브쿼리의 결과가 공집합이 아닐 때 참이다.

  • exists r ⇔\Leftrightarrow r≠∅r \neq \emptyset
  • not exists r ⇔\Leftrightarrow r=∅r = \emptyset

7. Modification of the Database

Deletion

delete from은 릴레이션에서 조건을 만족하는 레코드를 삭제한다

delete from course
where dept_name = 'Appl. Math'

SQL은 위 명령이 입력됐을 때, 권한만 있으면 아무런 경고없이 해당 레코드를 삭제하니 반드시 주의해아 한다.

아래와 같은 쿼리를 생각해보자

delete from instructor
where salary < (selected avg(salary) from instructor);

삭제가 되면서 salary의 평균이 변하고 원하지 않던 레코드가 삭제되는 결과가 나타날 수도 있다. 이를 위해 SQL은 다음과 같은 해결법을 사용한다.

  • 먼저 avg(salary)를 계산하고 삭제할 튜플들을 모두 미리 찾는다.
  • 이후 avg를 재계산하거나 삭제에 따라 튜플들을 재계산하지 않고, 위에서 찾은 튜플들을 삭제한다.

Insertion

튜플 삽입은 insert into를 이용한다.

insert into r values(v1, v2, ..., vn);

위와 같이 애트리뷰트의 이름을 명시하지 않는 경우, 테이블에 명시된 순서대로 애트리뷰트에 값을 넣는다. 이와 달리 아래와 같이 애트리뷰트의 이름을 직접 명시해 사용할 수도 있다.

insert into r(An, ..., A2, A1) values (vn, ..., v2, v1);

삽입의 경우에도 서브쿼리를 이용할 수 있다.

insert into student
	select ID, name, dept_name, 0
	from instructor

이때 삭제할 때와 마찬가지로 서브쿼리가 먼저 다 계산되고 나서 그 결과를 릴레이션에 추가한다

Updates

이미 있는 릴레이션의 애트리뷰트의 값을 바꾼다.

update r
set attribute = ...
where ...

다음과 같은 두 SQL문을 생각해보자. 연봉이 100,000을 초과하면 3%, 그렇지 않으면 5% 인상해야한다.

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

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

이때 만약 두 SQL문의 순서가 바뀌면, 어떤 강사는 두 번의 연봉 인상을 받게 될 수도 있다. 순서가 중요하므로 반드시 주의해야한다.

profile
Park Yeongseo
꾸준함, 기본기, 성찰, 공유
이전 포스트

[DB] 3. Relational Algebra

다음 포스트

[DB] 5. Intermediate SQL & Integrity Constraints, Authorization

2개의 댓글

comment-user-thumbnail
Harim Kim
2024년 2월 11일

헉 두 번의 연봉 인상 개이득

1개의 답글