[데이터베이스] SQL 총정리 1

DB 생성 및 테이블 생성

releation=테이블, 컬럼=필드=열=속성, 튜플=행=로우=레코드

• create database [DB명]
• create table [테이블명]( [필드명] [type(n)] [option], ... , [option]);
• type
  • char(n) : n 만큼 고정된 크기 할당
  • varchar(n) : 필요한 만큼만 크기 할당. 최대 n만큼 할당함
  • int, smallint : 보통 int는 8바이트 smallint는 4바이트
  • numeric(d,p): 소수 d는 총 길이, p는 소수 몇 째 자리 까지표시할 지
    • numeric(7,3) → 0000.000
• option
  • not null, primary key, foreign key 등이 있음
  • 여러 필드를 pk 또는 fk 지정할 경우 필드에 옵션을 바로 추가하지 못하고 따로 추가해야함
    • primary key (name, birth)
    • foreign key (lecture_name, instructor) references class(name, instructor)

조회

기본 구조

• select [필드] from [테이블]
• select [필드] from [테이블] where [그룹핑 전 조건] group by [필드] having [그룹핑 후 조건] order by [필드] [DESC/ASC]
  • where 부터는 optional
• as
  • 필드에 as [이름] 을 추가하여 [이름]으로 보이게할 수 있음
  • 테이블에 as [이름] 을 추가하여 [이름]으로 접근할 수 있음
• distinct
  • 필드 앞에 distinct를 추가하여 중복되는 값을 없애고 select할 수 있음

like, is null

• select [필드] from [테이블] where [필드] like "[문자열]"
  • ex) "_구" : 두 번째 문자가 '구'인 로우
  • ex) "%축구" : 앞의 문자는 상관 없이, 뒤에가 "축구"인 로우
• 필드 값이 null인 레코드를 찾을 경우 is null을 사용
  • where [필드] is null
  • null 연산
    • 5 + null -> null, 1 < null -> null

aggregate funcitons - avg, min, max, sum, count

• 전체 튜플 또는 group한 튜플을 대상으로 연산(평균, 최소, 최대, 합, 카운트)할 수 있음
• select 문에서 필드로 사용하거나 조건문에서 사용될 수 있다.

Set Operations - union, intersect, except

• 테이블간 연산(합집합, 차집합, 교집합)을 해서 새로운 테이블은 생성할 수 있다.

Subquery - in / not in, some / all, exist / not exist

• Subquery : 쿼리안에 쿼리를 넣을 수 있다.
  • select * from A where A.name in (select ~ 이하생략)
• in / not in : 특정 값이 해당 테이블 또는 배열(?)에 있는지 여부를 반환
  • where * from A where age in (20,21,22)
  • where * from A where (age, grade) in (select ~ )
• some / all : 특정 값과 subquery를 비교할 때 사용됨
  • ~ where salary > some (select salary ~ )
    • subquery의 튜플중에 salary가 높은게 하나라도 있으면 true
    • all은 모든 튜플보다 salary가 높아야 true
    • ~ where salary > (select min(salary) ~ ) 과 같음
• exist / not exist : subquery의 튜플이 하나 이상인지 확인
  • ~ where exist (select ~)

Correlated Subquery, Derived Relations, Scala Subquery

• Correlated Subquery : 바깥 쿼리의 테이블을 참조하여 생성한 subquery
  • SELECT b1.bookname FROM Book b1 WHERE b1.price > (SELECT avg(b2.price) FROM Book b2 WHERE b2.publisher=b1.publisher);
• Derived Relations : subquery로 생성한 테이블
  • SELECT b1.bookname FROM Book b1, (select publisher, avg(price) as avg_price from Book group by publisher) as b2 WHERE b1.publisher = b2.publisher AND b1.price > b2.avg_price;
• Scalar Subquery : 값이 하나인 subquery
  • ~ where price = (select max(price) from ~ )

수정

insert, update, delete

• insert into [테이블] value (값1, 값2, 값3)
• insert into [테이블](필드1, 필드2, 필드3) value (값1, 값2, 값3)
• insert into [테이블] select ~
• update [테이블] set [필드] = [값] where [조건]
  • 조건이 없으면 모든 레코드가 변경됨
• delete from [테이블] where [조건]
  • 조건이 없으면 모든 레코드가 삭제됨

alter, drop

• alter table [테이블] add [속성] [타입]
• alter table [테이블] drop [속성]
• drop table [테이블]

update 문에서 update하는 테이블을 subquery로 바로 참조할 수 없음 -> subquery를 두 번 생성

• update customer set address=( select * from (select address from customer where name='김연아') as temp ) where name='박세리'