7. 데이터베이스 언어 SQL / 03 SQL을 이용한 데이터 조작

1. SQL의 데이터 조작 기능

1. 데이터 검색 = select
2. 새로운 데이터 삽입 = insert
3. 데이터 수정 = update
4. 데이터삭제 = delete

 

2. 데이터의 검색

테이블에서 원하는 데이터를 검색하기 위해 select 문을 사용한다

다양한 검색 유형을 지원하며, 일반 사용자가 가장 많이 사용한다

 

기본 검색

기본 형식

select [all || distinct] 속성 리스트 from 테이블 리스트;

 

select 키워드와 함께 검색하고 싶은 속성의 이름을 콤마로 구분하여 나열한다

from 키워드로 함께 검색하고 싶은 속성이 있는 테이블의 이름을 콤마로 구분하여 차례로 나열한다

select 아이디, 이름, 등급 from 계정tbl;

 

테이블에 존재하는 모든 속성을 검색하기 위해 *를 사용할수도 있다

select * from 고객tbl;

 

 

select문의 결과 테이블은 관계 데이터 모델의 일반 릴레이션과 차이가 있다

관계 데이터 모델의 일반 릴레이션은 투플의 집합 개념으로 투플의 유일성을 만족해야 하기에 릴레이션 하나에 동일 투플이 중복되면 안된다

그러나 select문의 결과 테이블은 중복이 가능하다

 

중복을 허용할때는 ALL을 명시적으로 넣는것도 가능하다

select all 속성 이름 from 테이블 이름;

 

결과 테이블에서 투플의 중복을 제거하고 한 번씩만 출력되도록 하려면 distinct 키워드를 사용하면 된다

select distinct 속성 이름 from 테이블 이름;

 

결과 테이블에 출력되는 속성의 이름을 다른 이름으로 출력도 가능하다

as키워드를 변경할 이름과 함께 지정하면 된다

select 이름 as 다른 이름 from 테이블;

 

산술식을 이용한 검색

select 키워드와 함께 산술식을 제시할 수 있음

select 숫자 + 50 from 테이블;

실제 변경은 일어나지 않기에 as와 비슷하게 작동된다

 

조건 검색

조건을 만족하는 데이터만 검색한다

select 속성 from 테이블 where 조건;

where 키워드와 함께 비교 연산사, 논리 연산자를 이용해 조건을 넣을 수 있다

select 이름, 개수 from 테이블 where 고객='검색 고객 이름' and 개수 >= 10;

 

LIKE를 이용한 검색

검색 조건을 부분적으로 알고 있을시 LIKE 키워드를 사용해 검색할 수 있다

정확히 아는 경우는 =를 사용하면 된다

단, LIKE는 문자열 이용 조건만 사용가능하다

 

기호	설명
%	0개 이상의 문자
_	1개의 문자

 

 

예를들어 ABCDE중 BC만 아는경우

like '_BC%'로 검색을 하면 된다

 

select 속성 이름 from 테이블 likc '%이름중 일부분%';

%, _의 위치는 앞뒤 원하는 위치에 넣어서 찾을 수 있다

 

NULL을 이용한 검색

검색 조건에서 값이 널인지 비교하려면 is null키워드를 사용한다

반대의 경우 is not null을 사용한다

 

select 속성 from 테이블 where 속성 is null;

 

검색 조전에서 널 값은 크기 비교시 전부 거짓이 된다

즉, null일시 > < 연산 결과는 모두 false다

 

정렬 검색

결과 테이블의 내용을 사용자가 원하는 순서로 출력하려면 order by 키워드를 사용한다

 

오름차순 정렬이면 asc, 내림차순 정렬이면 desc로 표현한다

 

지정이 없을시 오름차순이 기본이다

select 속성 from 테이블 order by 속성 desc;

 

개수가 10개 이상인 투플을 검색해서 내림차순 정렬하기

select * from 테이블 where 개수 > 10 order by 개수 desc;

 

집계 함수를 이용한 검색

집계 함수

열 함수 column function이라고 불리가도 하며 개수, 합계, 평균, 최댓값, 최솟값을 계산 기능을 제공한다

 

특정 속성 값을 통계적으로 계산한 결과를 검색하기 위해 사용한다

 

sum, avg는 숫자 데이터 속성에만 적용 가능하며, 나머지는 대부분의 데이터 타입에 사용 가능하다

함수	의미	사용 가능한 속성 타입
count	속성 값의 개수	모든 데이터
max	속성 값의 최댓값
min	속성 값의 최솟값
sum	속성 값의 합계	숫자 데이터
avg	속성 값의 평균

 

집계 함수 사용시 2가지 주의 사항

• 집계 함수는 널인 속성 값은 제외하고 계산한다
• 집계 함수는  where 절에서는 사용할 수 없고 select, having 절에 사용 가능하다

select avg(숫자들) from 테이블

 

이때 집계 함수 사용시 속성의 의미가 달라지기에 as를 사용하여 새 이름을 부여하는 것이 좋다

select sum(속성) as "속성 합계" from 테이블;

 

distinct 키워드를 엮어 중복을 제거한 항목 갯수를 얻을 수도 있다

select count(distinct 속성) from 테이블;

 

그룹별 검색

group by

테이블에서 특정 속성의 값이 같은 투플을 모아 그룹으로 만들고, 그룹별로 검색을 하기 위해서 사용한다

 

그룹에 대한 조건을 추가하려면 having키워드를 같이 사용한다

 

group by가 없는 select는 테이블을 하나의 그룹으로 보고 검색하는것이다

 

select 이름 from 테이블 group by 이름;

이렇게 할시 이름 속성의 중복을 하나로 겹쳐서 결과 테이블을 반환한다

 

select 이름, max(소모량) from 테이블 group by 이름 having max(소모량) > 3;

having을 사용해 특정 그룹만 찾아서 그룹을 지을 수 있다

 

그룹별 검색시 집계 함수 혹은 group by에 있는 속성 외 속성은 select에서 사용할 수 없다

 

여러 테이블에 대한 조인 검색

조인 검색

여러 개의 테이블을 연결하여 데이터를 검색하는 것

 

조인 검색을 하려면 테이블을 연결해주는 속성이 필요하고 이 속성을 조인 속성이라고 한다

테이블을 연결하려면 조인 속성의 이름을 달라도 되지만, 도메인은 반드시 같아야 한다

 

일반적으로 외래키를 조인 속성으로 사용한다

 

조인 검색을 위한 sql문은 from에 검색에 필요한 모든 데이터를 나열하고 where에서 조인 조건을 제시한다

 

select 사람.이름 from 사람 테이블, 등급자 테이블 where 사람.이름 = '사람 이름' and 등급자.이름 = 사람.이름;

 

부속 질의문을 이용한 검색

selcet 안에 다른 select를 포함시킬수 있다

이때 두 번째 select를 부속 질의문 sub query라고 한다. 상위는 주 질의문 main query가 된다

 

부속 질의문은 괄호로 묶어 작성하면 order by를 사용할 수 없다

또한 상위 질의문보다 선 수행 된다

 

select 물건 이름 from 물건 테이블 where 물건 제작사 = (select 물건 제작사 from 물건 테이블 where 물건 이름 = '물건 이름');

 

부속 질의문은 하나의 행을 결과로 반환하는 단일 행 부속 질의문, 하나 이상의 행을 결과로 반환하는 다중 행 부속 질의문으로 분류한다

 

부속 질의문과 상위 질의문을 연결하는 연산자가 필요하고, 이때 부속 질의문의 종류에 따라 사용 가능 연산자가 다르다

 

단일 행 부속 질의문의 경우 평범한 < > = 와 같은 연산사를 사용할 수 있다

 

다중 행 부속 질의문 사용 연산자

연산자	설명
IN	서브쿼리 결과 집합에 속하는 값이 있는지 비교
NOT IN	서브쿼리 결과 집합에 속하지 않는 값인지 비교
ANY 또는 SOME	서브쿼리 결과 집합 중 하나라도 조건을 만족하면 참
ALL	서브쿼리 결과 집합의 모든 값과 비교해서 조건을 만족해야 참
EXISTS	서브쿼리 결과 집합에서 하나로 존재시 참
NOT EXISTS	서브쿼리 결과 집합에서 하나라도 존재하지 않으면 참

 

판매 데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체를 검색

select 제품명, 단가, 제조업체 from 판매
where 단가 > all (
					select 단가 from 제품 where 제조업체 = '대한식품'
                );

 

3. 데이터의 삽입

insert문

테이블에 새로운 투플을 삽입하기 위해 필요한 sql문

 

2가지 방법이 존재한다

1. 테이블에 투플을 직접 삽입
2. 부속 질의문을 이용해 투플을 삽입

데이터 직접 삽입

insert into 테이블[속성 리스트] values (속성값 리스트);

into 키워드와 함께 테이블 이름을 제시 후, values 키워드 다음의 속성 값들이 차례로 삽입된다

 

into의 속성과 values의 속성은 1:1 대응 되어야 하며 개수도 같아야 한다

 

insert into
	투플(속성1, 속성2, 속성3)
    value
    ('new 속성1', 'new 속성2', 'new 속성3');

 

부속 질의문을 이용한 데이터 삽입

다른 테이블에서 검색한 데이터를 투플로 삽입하는 insert 문

insert
into 테이블(속성)
select ~~;

 

4. 데이터의 수정

update 문을 사용한다

update 테이블
set 속성1 = 값1, 속성2 = 값2;
where 속성3 = '이름';

where없이 사용시 모든 투플이 지정된다

 

5. 데이터의 삭제

delete 문을 사용한다

delete from 테이블 [where 조건];

where없이 선언시 모든 투플을 지워 빈 테이블로 만들어 버린다