MySQL - SQL 쿼리

김법우·2021년 12월 10일
0

Database

목록 보기
3/10
post-thumbnail

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))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))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]sp[sno, pno] \ divide \ by \ p[pno] 하지만 SQL 에는 마찬가지로 DIVIDE BY 구문이 없기에 sp[sno]((sp[sno]×p[pno])sp)[sno]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;
profile
개발을 사랑하는 개발자. 끝없이 꼬리를 물며 답하고 찾는 과정에서 공부하는 개발자 입니다. 잘못된 내용 혹은 더해주시고 싶은 이야기가 있다면 부디 가르침을 주세요!

0개의 댓글