DML

데이터 조작어 DML Data Manipulation Language

테이블에 새 데이터를 삽입insert, 검색select ,수정update, 삭제delete

insert

데이터 직접 삽입

#데이터 직접 삽입 기본 양식
insert 
into 투플을 삽입할 테이블이름(속성이름나열)
values (삽입할 속성값들)
#into의 ()안  속성이름과 values()안 삽입할 속성값들은 개수도 같고 순서대로 일대일 대응되야함.

''' 7-48
판매 데이터베이스의 고객 테이블에 고객아이디가 tomato, 고객이름이 정은심, 나이가 36세,
등급이 gold, 적립금은 4,000원, 직업은 아직 모르는 새로운 고객의 정보를 삽입해보자.
그다음 고객 테이블에 있는 모든 내용을 검색해 삽입된 정은심 고객의 직업속성이 널값인지 확인해보자.
'''
insert 
into 고객(고객아이디, 고객이름, 나이, 등급, 적립금)
values ('tomato','정은심', 36, 'gold', 4000);

	#into절의 속성 순서가 고객 테이블의 속성 순서와 같으면 생략가능.결과값은 위와 같음
	insert 
	into 고객
	values ('tomato','정은심', 36, 'gold', null, 4000); #null은 직업

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

부속 질의문인 select문을 이용해 다른 테이블에서 검색한 데이터를 투플로 삽입하는 insert 문.

#부속 질의문을 이용한 데이터 삽입 기본양식
insert
into 테이블이름(속성들)
select 문;

#한빛제과에서 제조한 제품의 제품명,재고량,단가를 제품테이블에서 검색해 한빛제품 테이블에 삽입
insert 
into 한빛제품(제품명, 재고량, 단가)
select 제품명, 재고량, 단가
from 제품
where 제조업체 = '한빛제과';

select

기본검색

#기본검색 약식
select [all:distinct] 속성 #검색하고싶은 속성 
from 테이블; #검색하고싶은 속성이 있는 테이블
#[a:b]는 a또는b 라는 말이고 표시가 없으면 앞에오는 a가 기본값으로 설정됨

#고객테이블에서 고객아이디, 고객이름, 등급 속성 검색
select 고객아이디, 고객이름, 등급
from 고객;

#고객테이블의 모든 속성 검색
select *
from 고객;

#제품테이블에서 제조업체 검색,all키워드 사용(중복 허용)
select all 제조업체
from 제품;

#제품테이블에서 제조업체 속성을 중복없이 검색
select distinct 제조업체
from 제품;

#제품테이블에서 제품명과 단가를 검색하되, 단가를 가격이라는 새이름으로 출력
#실제로 바뀌는건 아니고 select문의 결과 테이블에서만 바껴서 출력됨
select 제품명, 단가 as 가격
from 제품;

산술식을 이용한 검색

#제품테이블에서 제품명과 단가 속성을 검색하되, 단가에 500원을 더해 '조정단가'라는 새 이름으로 출력
select 제품명, 단가 + 500 as "조정 단가" #공백 포함시 오라클에서는" mwsql서버에서는' 사용
from 제품;

조건검색

#조건검색 기본 약식
select [all:distinct] 속성
from 테이블
[where 조건];

#제품테이블에서 한빛제과가 제고한 제품의 제품명, 재고량, 단가를 검색
select 제품명, 재고량, 단가
from 제품
where 제조업체 = '한빛제과'; #"쓰면 에러남. '속성'

#주문테이블에서 apple 고객이 15개 이상 주문한 주문제품, 수량, 주문일자를 검색
select 주문제품, 수량, 주문일자
from 주문 
where 주문고객 = 'apple' and 수량 >=15; #and,or,not 사용가능

#제품테이블에서 단가가 2,000원이상이면서 3,000이하인 제품의 제품명, 단가, 제조업체 검색
select 제품명, 단가, 제조업체
from 제품
where 단가>=2000 and 단가<=3000;

like를 이용한 검색

like ’데이터%’ : 데이터로 시작하는 문자열

like ’%데이터’ : 데이터로 끝나는 문자열

like ‘%데이터%’ : 데이터가 들어가는 문자열

like ‘데이터_ _ _’ : 데이터로 시작하는 6자리 문자열

like ‘_ _ 한%’ : 세번째 글자가 ‘한’인 문자열

#고객 테이블에서 성이 김씨인 고객의 고객이름, 나이, 등급, 적립금 검색
select 고객이름, 나이, 등급, 적립금
from 고객
where 고객이름 like '김%';

null을 이용한 검색

검색 조건에서 널값은 다른 값과 크기를 비교하면 결과가 모두 거짓이 된다. 즉 null은 크기 비교 불가.

#고객테이블에서 나이가 아직 입력되지 않은 고객의 고객이름 검색
select 고객이름
from 고객
where 나이 is null;

#고객테이블에서 나이가 이미 입력된 고객의 고객이름 검색
select 고객이름
from 고객
where 나이 is not null;

정렬검색

#주문테이블에서 수량이 10개 이상인 주문의 주문고객, 주문제품, 수량, 주문일자를 검색해보자.
#단 주문제품을 기준으로 오름차순asc 정렬, 동일제품은 수량을 기준으로 내림차순desc 정렬
select 주문고객, 주문제품, 수량, 주문일자
from 주문
where 수량>=10
order by 주문제품 asc, 수량 desc; #오름차순 내림차순 정렬

집계 함수를 이용한 검색

개수 count, 최댓값 max, 최솟값 min, 합계 sum, 평균 avg

#제품테이블에서 모든 제품의 단가 평균을 검색
select avg(단가)
from 제품;

#한빛제과에서 제조한 제품의 재고량 합계를 제품 테이블에서 검색
select sum(재고량) as "재고량 합계"
from 제품
where 제조업체 = '한빛제과';

#제품테이블에서 제조업체의 수를 검색 (중복없이:distinct)
select count(distinct 제조업체)as "제조업체 수"
from 제품;

그룹별 검색

#그룹별 검색 기본 양식
select [all:distinct] 속성
from 테이블
[where 조건]
[group by 속성 [having 조건]] #group by 그룹으로 나누는 기준 속성 having 그룹에대한 조건
[order by 속성 [asc:desc]];

#주문테이블에서 주문제품별 수량의 합계 검색
select 주문제품, sum(수량) as 총주문수량
from 주문
group by 주문제품;

#제품테이블에서 제조업체별로 제조한 제품의 개수와 제품 중 가장 비싼 단가를 검색하되
#제품의 개수는 제품수라는 이름으로 출력하고 가장 비싼 단가는 최고가라는 이름으로 출력해보자.
select 제조업체, count(*) as 제품수, max(단가) as 최고가
from 제품
group by 제조업체;

#제품테이블에서 제품을 3개이상 제조한 제조업체별로 제품의 개수와, 제품중 가장 비싼 단가를 검색.
select 제조업체, count(*) as 제품수, max(단가) as 최고가
from 제품
group by 제조업체 having count(*)>=3;

#고객테이블에서 적립금 평균이 1,000원 이상인 등급에 대해 등급별 고객수와 적립금 평균 검색
select 등급, count(*) as 고객수, avg(적립금) as 평균적립금
from 고객
group by 등급 having avg(적립금)>=1000;

#주문테이블에서 각 주문고객이 주문한 제품의 총주문수량을 주문제품별로 검색
select 주문제품, 주문고객, sum(수량) as 총주문수량
from 주문
group by 주문제품, 주문고객;

조인 검색

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

#판매데이터베이스에서 banana고객이 주문한 제품의 이름을 검색
select 제품.제품명 #제품의 이름 검색
from 제품,주문 #두 테이블에서 
where 주문.주문고객 = 'banana' and 제품.제품번호 = 주문.주문제품; #조인

#판매데이터베이스에서 나이가 30세 이상인 고객이 주문한 제품의 번호와 주문일자를 검색
select 주문.주문제품, 주문.주문일자
from 고객, 주문
where 고객.나이>=30 and 고객.고객아이디 = 주문.주문고객;
	#별명부여한 코드
	select o.주문제품, o.주문일자
	from 고객 c, 주문 o
	where c.나이>=30 and c.고객아이디 = o.주문고객
	#inner join과 on 을 이용한 코드
	select 주문.주문제품, 주문.주문일자
	from 고객 inner join 주문 on 고객.고객아이디 = 주문.주문고객
	where 고객.나이 >=30;

#판매데이터베이스에서 고명석 고객이 주문한 제품의 제품명 검색
select 제품.제품명
from 고객, 제품, 주문
where 고객.고객이름 = '고명석' and 고객.고객아이디 = 주문.주문고객 and 제품.제품번호 = 주문.주문제품;
#고객과 제품은 연관성이 없어 중간에 연결고리로 주문을 합쳐줌(제품-주문-고객)

***# inner join 검색 양식
select 속성
from 테이블1 inner join 테이블2 on 조인조건
[where 검색조건]***

#inner join 예제
#나이가 30세 이상인 고객이 주문한 제품의 주문제품과 주문일자 검색
select 주문.주문제품, 주문.주문일자
from 고객 inner join 주문 on 고객.고객아이디=주문.주문고객
where 고객.나이 >=30;

***# outer join 검색 양식
#검색 대상을 어느쪽 테이블로 할것이냐에 따라 lift,right,full로 정함
select 속성
from 테이블1 left:right:full outer join 테이블2 on 제약조건 
[where 검색조건]***

#outer join 예제
#주문하지 않은 고객도 포함해서 고객이름과 주문제품, 주문일자를 검색
select 고객.고객이름, 주문,주문제품, 주문.주문일자
from 고객 left outer join 주문 on 고객.고객아이디=주문.주문고객;
#left즉 왼쪽에 있는 고객 테이블 중 주문 안한 고객도 출력

	select 고객.고객이름, 주문,주문제품, 주문.주문일자
	from 주문 right outer join 고객 on 주문.주문고객=고객.고객아이디;
	#right즉 오른쪽에 있는 고객 테이블 중 주문 안한 고객도 출력. 즉 위의 코드결과와 같음
	

부속 질의문을 이용한 검색

select문 안에 select문 포함. 부속질의문을 먼저 수행하고 그 결과로 상위질의문을 수행.

안의 select문을 nested select문, 부속 질의문, 서브질의문 sub query 라고함.

괄호()로 묶어 작성, order by 절을 사용할 수 없음, 상위 질의문보다 먼저 수행됨.

부속질의문의 결과 값에 따라 단일 행 부속 질의문, 다중 행 부속 질의문 이 있음.

밖의 select문을 상위 질의문, 주 질의문 main query 라고함.

• [ 다중 행 부속 질의문 연산자 ]
  • in : 부속 질의문의 결과 값 중 일치하는게 있으면 참
  • not in :부속 질의문의 결과 값 중 일치하는게 없으면 참
  • exists : 부속 질의문의 결과 값이 하나라도 존재하면 참
  • not exists :부속 질의문의 결과 값이 하나라도 존재하지 않으면 참
  • all: 부속질의문의 결과값 모두와 비교한 결과가 참이면 검색조건을 만족(비교연산자사용)
  • any 또는 some :부속질의문의 결과값 중 하나라도 비교한 결과가 참이면 검색조건을 만족(비교연산자사용)

#판매데이터베이스에서 달콤비스킷을 생산한 제조업체가 만든 제품들의 제품명과 단가를 검색.7-40
#1.달콤비스킷의 제조업체 검색. 괄호 안 부속질의문에서 검색.
#2.제조업체에서 만든 제품의 제품명과 단가 검색
select 제품명, 단가
from 판매
where 제조업체=(select 제조업채
							from 제품
							where 제품명='달콤비스킷');
	#위의 코드와 같음
	select 제품명, 단가
	from 제품
	where 제조업체='한빛제과';

#판매데이터베이스에서 적립금이 가장 많은 고객의 고객이름과 적립금을 검색.7-41
select 고객이름, 적립금
from 고객
where 적립금=(select max(적립금) from 고객);
	#위의 결과값과 같음
	select 고객이름, 적립금
	from 고객
	where 적립금 = 5000; #즉 위의 식의 괄호안 부속질의문 값이 5000
#이처럼 부속질의문의 값이 단일값이면 '단일 행 부속 질의문'이라고함

#판매데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체를 검색.7-42
select 제품명, 제조업체
from 제품
where 제품번호 in (select 제품명
								from 주문
								where 주문고객 = 'banana');
	#위의 결과값과 같음
	select 제품명, 제조업체
	from 제품
	where 제품번호 in ('p01','p04','p06')
#이처럼 부속질의문의 값이 여러개 반환을 하는것을 '다중 행 부속 질의문'이라함.

#판매데이터베이스에서 banana고객이 주문하지 않은(not in) 제품의 제품명과 제조업체 검색.7-43
select 제품명, 제조업체
from 제품
where 제품번호 not in (select 주문제품
										from 주문
										where 주문고객 = 'banana');
	
#판매데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명,단가,제조업체 검색.7-44
select 제품명, 단가, 제조업체
from 제품
where 단가 > all (select 단가
								from 제품
								where 제조업체 ='대한식품');

#판매데이터베이스에서 2022년 3월 15일에 제품을 주문한 고객의 고객이름 검색.7-45
select 고객이름
from 고객
where exists(select *
						from 주문
						where 주문일자 = '2022-03-15'
														and 주문.주문고객=고객.고객아이디);
#판매데이터베이스에서 2022년3월15일에 제품을 주문하지 않은 고객의 고객이름을 검색.7-46
select 고객이름
from 고객
where not exists(select *
								from 주문
								where 주문일자 ='2022-03-15'
								and 주문.주문고객 = 고객.고객아이디);
	#조의 질의를 이용한 select문.(결과같음)
	select 제품.제품명, 제품.제조업체
	from 제품, 주문
	where 제품.제품번호 = 주문.주문제품 and 주문.주문고객='banana');
	#exists연산자를 이용한 select문. (결과같음)
	select 제품명, 제조업체
	from 제품
	where exists(select *
							from 주문
							where 제품.제품번호 = 주문.주문제품
							and 주문.주문고객 = 'banana');

update

set 다음 값을 어떻게 수정할 것인지. where 절에 제시된 조건을 만족하는 투플만 속성값을 수정하는데 where절을 생략하면 테이블의 모든 투플을 set절에서 지정한 대로 속성 값을 수정.

#데이터 수정 기본 양식
update 테이블이름
set 속성1=값1, 속성2=값2...
[where 조건];

#제품 테이블에서 제품번호가 p03인 제품의 제품명을 통큰파이로 수정.7-49
update 제품
set 제품명 = '통큰파이'
where 제품번호 = 'p03';

select *from 제품;

#제품테이블에 있는 모든 제품의 단가를 10%인상. 그다음 제품 테이블의 모든 내용을 검색해 인상확인.7-50
update 제품
set 단가=단가*1.1;

select * from 제품;

#판매 데이터베이스에서 정소화 고객이 주문한 제품의 주문수량을 5개로 수정해보자.
#그런다음 주문테이블의 모든 내용을 검색하여 수정 내용을 확인.
update 주문
set 수량 =5
where 주문고객 in (select 고객아이디
								from 고객
								where 고객이름 = '정소화';

select * from 주문;

delete

#데이터 삭제 양식
delete
from 테이블이름
[where 조건];

#주문테이블에서 주문일자가 2022년5월22일인 주문내역을 삭제. 그다음 주문테이블의 모든 내용 검색.7-52
delete
from 주문
where 주문일자 = '2022-5-22';

select * from 주문;

#판매데이터베이스에서 정소화 고객이 주문한 내역을 주문 테이블에서 삭제후 주문테이블의 모든내용 검색.7-53
delete 
from 주문
where 주문고객 in (select 고객아이디
								from 고객
								where 고객이름 = '정소화';

select * from 주문;

#판매데이터베이스에서 주문 테이블에 존재하는 모든 투플 삭제한후 검색해 확인.
delete 
from 주문

select * from 주문;