SQL 프로그래밍

SQL 프로그래밍은 이전의 Relational Algrebra 와 Relational Calculus 을 기반으로 설계 되었다. 또한 RA 나 RC 의 요소가 아닌 부분도 포함이 되어 있다.

ex) in Sub Query 문법

Name Qualification

table-name.attribute-name 의 형태로 relation 의 attribute 에 접근 하는 것을 말함.

select * from s join p on s.city = p.city;

Order By

order by order-item-commalist 의 형태로 select 문 결과의 순서를 지정 할 수 있다.

• select 문 결과에 사용 가능
• column 이 2개 이상인 경우도 사용 가능

예제

• Relation Calculus or Relation Algebra to SQL

  <9.6.1 부품의 무게가 10 pound 이상이고 부품의 도시가 파리가 아닌 부품의 도시와
  색상을 찾아라.>
  
  select px.color 
  from p as px
  where px.city <> 'Paris'
  and px.weight > 10.0
  order by px.pno;

  <9.6.4 first city에 있는 공급자가 second city에 있는 부품을 공급하는 경우에 두 도시
  쌍을 찾아라.>
  
  select distinct s.city, p.city
  from s join sp join p
  where s.sno = sp.sno
  and p.pno = sp.pno;

  <9.6.8 공급된 부품에 대하여 부품번호와 공급된 부품 수량을 찾아라.>
  
  select sp.pno, sum(sp.qty) as totalQty
  from sp
  group by sp.pno;

  <9.6.10 부품 p2를 공급하는 공급자의 이름을 찾아라>
  
  select sname
  from s
  where sno in (
  	select sp.sno
  	from sp
  	where sp.pno = 'p2'
  )

  <9.6.11 red 부품을 적어도 한개 이상 공급하는 공급자의 이름을 찾아라.>
  
  select sname
  from s
  where in (
  	// red 부품을 공급하는 pno 를 가지는 sp 에서 sno 를 뽑아야함.
  	select sp.sno
  	from sp
  	where in (
  		// red 부품을 공급하는 pno 를 가지는 p 에서 pno 를 뽑아야함.
  		select p.pno
  		from p
  		where p.color = 'red'
  	)
  );

  <9.6.13 부품 p2를 공급하는 공급자 이름을 찾아라.>
  
  select distinct s.sname
  from s
  where in (
  	// pno='p2' 인 부품을 공급하는 pno 를 가진 tuple 을 sp 에서 뽑아내야함
  	select sp.pno
  	from sp
  	where sp.pno = 'p2'
  );
  
  select distinct s.sname
  from s
  where exists (
  	// sno 와 sp.sno 가 같고 sp.pno 가 p2 인 tuple 이 존재하는가?
  	select *
  	from sp
  	where sp.pno = 'p2'
  	and sp.sno = s.sno
  );

  <9.6.14 부품 p2를 공급하지 않는 공급자의 이름을 찾아라.>
  
  select distinct s.sname
  from s
  where s.sno not in (
  	select sp.sno
  	from sp
  	where sp.pno = 'p2'
  );
  
  select distinct s.sname
  from s
  where not exists (
  	// 부품 p2 를 공급하는 sp 의 sno 들을 전부 제거하고 남은 것들을 뽑아내야함.
  	select *
  	from sp
  	where sp.pno = 'p2'
  	and sp.sno = s.sno
  );

  <9.6.15 모든 부품을 공급하는 공급자의 이름을 찾아라.>
  
  select s.sname
  from s
  ...
  // for all 은 어떻게?
  // SQL 에서 for all 은 존재하지 않는다. 먼저 relation calculus 로 적어보자

  $sx.sname \ where \ forall \ px \ (exists \ spx \ (spx.pno = px.pno \ and \ spx.sno = sx.sno))$ 여기서 $\forall(\exists(...))$ 은 $\neg\exists(\neg\exists(...))$ 이므로 이와 같이 변형한다. $sx.sname \ where \ not \ exists \ px \ (not \ exists \ spx \ (spx.pno = px.pno \ and \ spx.sno = sx.sno))$ 이를 다시 SQL 문으로 변환하면,

  select sx.sname
  from s as sx
  where not exists (
  	select * 
  	from p as px
  	where not exists (
  		select *
  		from sp as spx
  		where spx.sno = sx.sno
  		and spx.pno = px.pno
  	)
  )

  사실 "모든 ~ 에 대하여" 는 DIVIDE BY 를 활용한 Relational Algebra 로 쉽게 표현 할 수 있다. $sp[sno, pno] \ divide \ by \ p[pno]$ 하지만 SQL 에는 마찬가지로 DIVIDE BY 구문이 없기에 $sp[sno] - ((sp[sno]\times p[pno])-sp)[sno]$ 로 변형되어야 한다. 이것을 SQL 문으로 변형하면,

  select distinct sub.sno
  from (
  	select * from (select sno from spj) as spj_sno 
  	cross join (select pno from p) as p_pno
  ) as sub left join spj 
  on spj.sno;

Select 구문과 SubQuery

select 구문을 살펴보면 하단과 같다. 여기서 [...] 는 생략가능한 부분이다.

select

[from table-reference]

[where where-condition]

[order by {col_name | expr} [asc | desc]]

[having where-condition]

[group by {col_name | expr} [with rollup]]

Regular Expression

where condition 중 특이한 것이 정규식 부분인데,

1) select city from p where name like 'b%';
2) select city from p where name like '_u%';

1번의 경우 b 로 시작하는 경우를 찾는 것 (문자열 내에서) 이고

2번의 경우 첫 번째 글자는 상관 없이 두 번째 글자가 u 이면서 그 뒤는 상관 없는 경우를 찾는 것이다.

SubQuery

서브 쿼리는 select 문 내에 다시 select 문이 나타나는 것을 말한다.

in 을 사용한 서브 쿼리가 가장 대표적이다.

select pname
from p
where city in (
	select city
	from p
	where weight > 15
)

여기서 in 은 서브 쿼리의 결과에 or 을 사용한 것과 같다. 이외에도 any, all 등이 존재한다.

any, all 등은 비교 연산자와 함께 사용 하는 것이 올바르다.

• ANY
  • >, ≥ : 서브 쿼리 결과값의 최소값 보다 크거나 같으면
  • <, ≤ : 서브 쿼리 결과값의 최대값 보다 작거나 같으면
  • = : in 과 동일
  • ≠ : not in 과 동일
• ALL
  • >, ≥ : 서브 쿼리 결과값의 최대값 보다 크거나 같으면
  • <, ≤ : 서브 쿼리 결과값의 최소값 보다 작거나 같으면
  • = : 서브 쿼리 결과값이 2개 이상이면 오류 발생
  • ≠ : 서브 쿼리 결과값이 2개 이상이면 오류 발생

Group By, Having

group by 구문을 통해 특정 column 을 묶어 group 을 만들고 Aggregate Function (sum, count, avg ... )를 적용 할 수 있다.

• Group By 는 Where 절을 적용하기 전에 적용한다.
• Having 은 Where 절을 적용한 후 Group 의 결과에 대해 적용한다.

<공급 수량(qty)이 100 이상인 공급 부품들의 부품별 수량 합계가 500인 부품번호와 부품
수량을 찾아라.>

select pno, sum(qty) as totQty
from sp
where qty >= 100 // group by 를 적용하기 전에 미리 where 절을 적용
group by pno // qty 가 100 보다 큰 경우에 대해 pno 를 기준으로 group 화 
having totQty > 500; // group 화 된 후 계산된 sum 을 기준으로 restriction

Instert 문

기본 구문은 다음과 같다.

insert into 
s(sno, sname, status, city)
values('s9', 'home', 10, 'Busan');

테이블에서 Primary Key 를 instert 시 자동으로 증가하도록 하는 조건을 적용 할 수 있는데 이를 Auto-increment 라고 한다.

alter table student auto-increment=10

위의 코드를 통해 auto-increment 를 수행할 기준점을 재설정 할 수 있다.

select last_insert_id();

위 구문은 마지막으로 자동 삽입된 auto-increment 의 값이다.

Update 문

주의!

• where 절 없이 update 문 실행 시 전체 Record 의 값이 변경됨.

• 2개 이상의 column 을 변경 하도록 지정 할 수 있음.

  update s 
  set name='ce', city='busan'
  where sname like 'A%';

Delete, Truncate 문

주의!

• Delete

  • where 절 없이 실행 시 전체 레코드를 삭제한다.
  • where 절과 order by 를 사용해 조건에 만족하는 Record 삭제가 가능하다.
  • DML(Data Manipulation language)이므로 where 절과 after delete trigger 사용 가능
  • foreign Key constraint 가 있더라도 삭제가 가능
  • 테이블 초기화 하지 않는 경우 삭제된 레코드에 대한 로그 추적이 이루어져 truncate 보다 속도가 느림

• Truncate

  • DDL(Data Definition language)이므로 where 절을 사용 할 수 없고 레코드들을 전부 삭제 한 뒤 테이블을 자동 초기화 한다.
  • delete 이후 trigger 구현이 없고 삭제된 레코드들에 대한 로그 추적이 되지 않으므로 속도는 빠르다.
  • 로그 추적이 없으므로 RollBack 할 수 없다.

  delete from s
  where s.sname like 'Bu%'
  order by s.name desc
  limit 3;
  
  truncate table s;