Basic SQL

Domain types in SQL

char(n): 고정 길이 문자열 n으로 길이 지정
varchar(n): 가변 길이 문자열 n으로 길이 지정
int: 정수
smallint: 작은 정수
numeric(p,d): 고정 소숫점 p: 총 자릿수 d: 소숫점 이하 자릿수 예) 123.45 는 numeric(5,2) 에 해당
real: floating point number
float(n): floating point number 유저가 최소 n자리로 설정

Create table

table을 생성하는 방법:

create table 'table_name'(
	attribute1 domain,
    attribute2 domain,
    attribute3 domain
);

primary key(attribute1, attrubute2, ...)으로 primary key 지정
foreign key(attribute1, attrubute2, ...) references other_table로 foreign key 지정
domain뒤에 not null을 붙여 해당 attribute를 not null로 설정

위 세가지는 모두 create 문 안에서 사용

Update table

Insert

insert into table_name values(v1, v2, v3, ...);

으로 tuple 삽입
value는 해당 table의 attribute에 맞아야 함

Delete

delete from table_name;

해당 table의 tuple이 전부 지워짐 (table은 빈 상태로 남아있음)
Drop table

drop table table_name;

해당 table 자체가 지워짐
Alter

alter table table_name attribute_name domain;
alter table table_name drop attribute_name;

table에 attribute를 추가하거나, 제거

SQL query의 기본적인 형태

select attribute1, attribute2, ...
from table1, table2, ...
where 조건

기본적으로 이런 형태

Select clause

결과로 출력할 attribute들을 선택
예시)

select name
from instructor

instructor의 name만 가진 table return
참고로 대소문자 구분 안 함.
select뒤에 distinct를 붙이면 중복되는 값을 제거하고 return
select뒤에 all을 붙이면 중복되는 값 모두 return (기본적으로 중복되는 SQL은 값을 살리지만, 명시적으로 표기하는 것)
select * -> 모든 attribute return
select 'value' -> 해당 값 하나만을 가지는 table return (from이 없음)

Where clause

해당 query의 결과가 만족해야하는 조건을 붙임
예시)

select name
from instructor
where dept_name = 'Comp.Sci'

instructor table에서 dept_name이 Comp.Sci인 tuple들의 name만 가지는 table return
연산자 $<,<=,>,>=,=,and,<>$사용 가능 ($<>$는 $\not=$과 같음)
Tip) 문자열 비교시 정확히 일치 -> '=' 사용 / %나_를 사용한다 -> 'like' 사용

From clause

query에 포함될 table을 작성
from table1, table2, table3, ... 식으로 컴마로 연결하면 cartesian product한 table이 query로 들어옴

Rename operation

attribute나 table의 뒤에 as name 붙여서 rename 가능

select number as num
from table as T

심지어 as도 생략가능 table as T => table T

String operation

percent(%): %자리에 아무 substring이 와도 매칭됨
underscore( _ ): _자리에 아무 char가 와도 매칭됨
이런 operation 사용하면 = 이 아니라 like로 비교
예시)

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

instructor table에서 name이 %dar%형태인 tuple들만 return
Tip) string에 any substring이 아니라 %자체를 쓰고 싶다? \%로 사용 예) like '100\%'

string operation에서는 대소문자 구분 함

concat(string1, string2, ...) 혹은 string1 || string2 || ...으로 string concatenation
upper(string), lower(string)으로 대소문자 변환
length(string) => 길이구하기
substring(string, position, length) => substring 추출

Ordering

order by attribute

로 결과 table 정렬
기본적으로 오름차순 -> 내림차순으로 하고 싶으면 뒤에 desc 붙여서
정렬기준 여러개 사용 가능 -> 앞 기준으로 정렬된 안에서 뒷 기준으로 정렬

Where의 조건문

between으로 그 사이에 존재하는 값 조건 걸기 가능
예시) where salary between 90000 and 100000
Tuple끼리의 비교도 가능
예시)

select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, 'Biology');

Set operator

union, intersect, except로 각각 합, 교, 차집합 연산 가능
set 연산하면 자동적으로 중복 제거됨
중복 살리고 싶으면 연산자 뒤에 all붙여 연산하면 됨 예시) a union all b

Null value

값을 알 수 없거나(unknown value) 값이 존재하지 않으면 null이 됨
null이랑 arithmatic 연산하면 null됨
null인지 체크할때는 = null이 아니라 is null로 함

null과 비교연산하면 unknown return됨
and, or, not 연산에 unknown이 들어가면, unknown에 따라 값이 달라질 때만 unknown이 return됨
예) true and unknown = unknown, false or unknown = unknown
P is unknown으로 해당 조건문이 unknown을 return하는지 체크 가능

Aggregate function

avg(attribute) -> 평균값 반환
count(attribute) -> 갯수 세 줌
max, min, sum 등등도 있다

기본적으로 null은 무시하고 계산, 전부 null이면 null 반환
count(*)은 예외

Group by

통계함수를 사용 할 때, group by를 사용하여 그룹으로 묶어 통계 산출 가능
예시)

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

통계를 계산할 값이 없는 tuple은 무시됨

select문에 통계함수와 같이 사용된 attribute는 group by한 table에 있는 attribute여야 함. <- 그룹으로 묶을 때, 값이 다 달라 사라지는 attribute면 안된다는 말

Having clause

group by와 같이 사용
table을 그룹화 한 뒤 조건을 걸음
예시)

select dept_name, avg(salary)
from instructor
group by dept_name
having avg(salary) > 42000;

평균 salary가 42000을 넘는 dept_name과 avg(salary)가 return됨

Nested subquery

select-from-where안에 또 쿼리문이 들어갈 수 있음

Some clause

F <comp> some r 로 사용
<comp>에는 <, <=, >, >=, =, != 사용 가능
F와 r에 있는 모든 tuple과 <comp> 했을 때, 하나라도 true를 반환하면 true
주의) (= some) 은 in과 같지만 (!= some)은 not in과 같지 않음 (하나라도 같지 않음과 같지 않음은 다르니까)

All clause

F <comp> all r 로 사용
<comp>에는 <, <=, >, >=, =, != 사용 가능
F와 r에 있는 모든 tuple과 <comp> 했을 때, 전부 true를 반환해야 true

Exists

exsist r 로 사용
r에 tuple이 존재하는지 check
서브쿼리와 사용됨

이거 로직이 많이 헷갈리는데 예시를 통해 설명하자면

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

이렇게 되어 있으면, 우선 S에서 tuple을 하나 가져 온 뒤, 그 tuple로 subquery를 실행해 subquery가 empty가 아니면 해당 query를 결과로 반환한다.

Unique

unique r 로 사용
r에 중복 tuple이 있는지 check
서브쿼리와 사용됨
작동방식은 exists와 동일, 존재 check가 아니라 중복 check인 것만 다름

With clause

with clause는 해당 쿼리 내에서 사용할수 있는 temporary view를 생성함. <- (table형태의 변수 선언?)

with table명 (A1, A2,...) as
	(
    	~~~subquery~~~
    )

꼴로 사용하면 해당 서브쿼리를 table명으로 사용할 수 있음. table의 attribute도 A1, A2, ... 로 rename됨

Modification of the database

삭제
table의 모든 tuple 삭제
-> delete from table
뭔가 선택해서 삭제하고 싶다? where절로 조건 달기
주의) 조건이 뭔가 계산한 결과라면, tuple이 삭제되면서 변할 수 있음.

추가
insert into table values (value1, value2, ...);
주의) attribute 순서대로 들어감
명시적으로 표기하거나 직접 대입하고 싶으면
insert into table (A1, A2, ...) values (V1, V2, ...)
table을 넣는것도 가능
예시)

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

주의) attribute 형식이 일치해야 들어감

업데이트
update r
set ~
예시)

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

조건에 맞는 tuple에서 set의 수식을 수행

기타 많이 사용하는 구문들

CASE

case
	when 조건1 then 결과1
    when 조건2 then 결과2
    ...
    else default 결과
    end

조건문

COALESCE

coalesce(A1 or V1, A2 or V2, ...)

null이 아니고 가장 앞에 있는 값 반환

ROUND

round(input, 반올림 자릿수)

input을 반올림하여 return