데이터베이스3 - CRUD

조회

조회(Select)

-- 이름만 조회

select name from product;

 

-- 이름과 가격만 조회

select name, price from product;

 

--없는 항목은 조회할 수 없다

-- 항목을 계산해서 조회할 수 있다.

-- 날짜 계산 시 기본 처리 단위는 일이다.

select name, expire-made+1 from product;

 

-- 조회한 항목에 표시될 이름을 지정할 수 있다. (별칭 설정)

-- 별칭에 띄어쓰기가 있는 경우는 쌍다옴표로 감싸서 처리한다.

select name "이름", expire-made+1 "유통기한" from product;

조건 (where)

--가격이 2000원 이상인 상품 정보 조회

select * from product where price >= 2000; 

 

-- 1000원 이상 2000원 이하인 상품 정보 조회

select * from product where price >= 1000 and price <= 2000;

select * from product where price between 1000 and 2000;

 

-- 오라클에서는 같다가 = 로 표현된다.

select * from product where price = 1000;

 

-- 오라클에서는 같지 않다는 !=, <> 로 표현된다.

select * from product where price != 1000;

select * from product where price <> 1000;

 

문자열 조건

- 문자열도 같음을 = 로 처리한다.

- 유사 검색은 like와 instr()로 처리할 수 있다.

    - 시작 검색만큼은 반드시 like를 써야할 정도로 압도적인 차이가 발생한다.

- 나머지 성능은 전체적으로 instr이 우수하다.

- 똑같이 나오만 동작하는 원리가 다르다.

- 복잡한 검색은 like로 가능하다.

 

-- 스크로 시작되는 상품 정보 조회

select * from product where name like '스크%'; -- 앞 글자만 봐도 판정이 된다.

select * from product where instr(name, '스크') = 1; 

-- instr()은 위치를 찾는 명령이다. 있으면 1 없으면 0이 나온다.

-- 오라클은 인덱스가 1부터 시작한다.

 

select * from product where name like '%이%'; --이라는 글자가 나오는지 찾는다, 이런식으로 쓰면 스킵이 안 된다.

 

select * from product where name like '오%오'; --복잡한 검색

 

-- 퍼센트가 앞에 들어가면 instr()이랑 차이가 없다.

-- 전체를 다 봐야 하는 상황은 instr()이 빠르고 일부분을 봐야하는 like가 빠르다.

 

-- 시작키워드 검사만 like를 쓰고 나머지는 전부 instr()을 쓴다.

(Q) 문자열 조건 통합 예제

-- (Q) 과자와 아이스크림만 조회

select * from product where type = '과자' or type = '아이스크림';

select * from product where type in ('과자', '아이스크림'); -- O

-- (Q) 이름에 '초코'가 들어간 상품 정보 조회(instr이 좋음)

select * from product where name like '%초코%';

select * from product where instr(name, '초코') > 0; -- O

-- (Q) 이름이 '바나나'로 시작하는 상품 정보 조회(like가 좋음)

select * from product where name like '바나나%'; -- O

select * from product where instr(name, '바나나') = 1;

-- (Q) 이름에 '이'가 들어있지 않은 상품 정보 조회

select * from product where name not like '%이%';

select * from product where instr(name, '이') = 0; -- O

-- (Q) 가격이 1000원 이상인 과자 조회

select * from product where price >= 1000;

날짜 조건

- 기본 형식은 yyyy-mm-dd hh24:mi:ss 이다.
- 문자열로 간주하고 조회할 수 있다. (운영체제에 따라 작동여부 결정)
- 문자열로 변환하고 싶다면 to_char() 사용한다.
- 날짜 추출용 함수인 extract()를 사용하여 원하는 항목 추출할 수 있다.

 

-- 2020년에 제조된 상품 조회

-- select * from product where made like '2020%'; 안되는 코드

select * from product where to_char(made, 'yyyy') = '2020';

select * from product where extract(year from made) = 2020; --made라는 곳에서 연도를 뽑겠다.

select * from product where

made between

to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

and

to_date('2020-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss');

 

(Q) 날짜 조건 통합 예제

-- (Q) 여름(6, 7, 8)월에 생산한 상품 정보 조회

select * from product where to_char(made, 'mm') in ('06', '07'.'08'); --문자열을 사용해서 풀기

select * from product where extract(month from made) between 6 and 8;

select * from product where extract(month from made) = 06 or extract(month from made) = 07 or

extract(month from made) = 08;

 

-- (Q) 2019년 하반기에 생산한 상품 정보 조회

-- 하반기 (07/01~12/31)

select * from product where to_char(made, 'yyyy-mm')

in ('2019-07', '2019-08', '2019-09', '2019-10', '2019-11', '2019-12');

select * from product where extract(year from made) = 2019 and extract(month from made) between 7 and 12;

select * from product where made between to_date('2019-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and

to_date('2019-12-31 23:59:59' , 'yyyy-mm-dd hh24:mi:ss');

 

-- (Q) 2020년부터 현재까지 생산한 상품 정보 조회

select * from product where made between to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and sysdate;

 

-- (Q) 최근 1년간 생산한 상품 정보 조회

-- 오라클 날짜는 기본 계산 단위가 (일)이다.

-- 따라서 1년전은 sysdate - 365이다.

select * from product where made between sysdate-365 and sysdate;

정렬(Order)

- 모든 조회가 끝나고 나온 결과를 원하는 목적에 따라 재배열 (조건보다 앞에 나오면 안 됨, 데이터가 확정이 되고난 후)
- 정렬을 따로 지정하지 않겠다(비추천)
- ase(오름차순, ascending), desc(내림차순, ) 안 쓰면 오름차순

select * from product;

 

select * from product order by no;

select * from product order by no asc;

select * from product order by no desc;

 

-- 2차 정렬

select * from product order by price desc, no asc;

 

(Q) 정렬 통합 예제

-- (Q) 최근에 제조된 상품부터 출력

select * from product order by made desc;

 

-- 번호가 시퀀스라면 아래 코드도 가능

-- select * from product order by no desc;

-- (Q) 폐기일이 오래된 상품부터 출력

select * from product order by expire asc;

 

-- (Q) 이름순으로 출력

select * from product order by name asc;

select * from product order by name asc, no asc;

 

-- (Q) 상품을 종류별로 가격이 비싼 순으로 출력

select * from product order by type asc, price desc, no asc;

 

-- (Q) 유통기한이 가장 짧은 상품부터 출력(expire - made + 1) 오늘 만들어서 오늘 폐기해야하면 유통기한이 1이기 때문에 +1

select * from product order by expire-made+1 asc, no asc;

 

-- 부여한 별칭으로 정렬 가능

select

no, name, type, price, made, expire, expire-made+1 유통기한

from product

order by 유통기한 asc, no asc;

 

-- *(와일드카드)는 다른 항목과 같이 쓸 수 없고 테이블에 .*를 추가하여 사용

select

product.*, expire-made+1 유통기한

from product

order by 유통기한 asc, no asc;

 

-- 테이블에도 별칭 부여가 가능하다.

select

p.*, expire-made+1 유통기한

from product p

order by 유통기한 asc, no asc;

수정(Update)

 - 이미 존재하는 데이터의 값을 바꾸는 작업

- 개인정보변경, 조회수증가, 구독/해지, 좋아요, 게시글 수정, ...

-- update 테이블 이름 set 변경내용 [조건]

 

-- 모든 상품의 가격을 0으로 변경

update product set price = 0;

 

-- 과자만 0원으로 변경

update product set price = 0 where type ='과자';

update product set price = 0 where type ='생필품'; --db에 없는 조건이어도 구문은 수정이기 때문에 문제 없음

update product set price = 0, name= '증정품' where type = '과자';

 

-- 지금까지의 작업을 무효화시키겠다.

rollback;

 

-- 지금까지의 작업을 확정 저장하겠다.

commit;

-- 확인

select * from product;

(Q) 수정 통합 예제

--(Q) 1번 상품의 가격을 1500원으로 변경

update product set price = 1500 where no = 1;

 

--(Q) 스크류바의 가격을 2000원으로 변경

update product set price = 2000 where name = '스크류바';

 

--(Q) 멘토스의 가격을 1000원으로 변경하고 분류를 과자로 변경

update product set price = 1000, type = '과자' where name = '멘토스';

 

--(참고) 오라클에는 누적연산이 없습니다

--(Q) 과자의 가격을 500원 할인

update product set price = price-500 where type = '과자';

 

--(Q) 아이스크림의 가격을 10% 인상

update product set price = price * 1.1 where type = '아이스크림';

삭제(Delete)

- 데이터베이스에 저장된 값을 영구적으로 제거하는 것이다.
- commit / rollback 영향을 받는다.
- delete [from] 테이블이름 [조건];

 

--같은 구문

-- 전부다 삭제(비추천)

delete product;

delete from product;

 

-- (Q) 1번 상품 정보 삭제

delete product where no = 1;

-- (Q) 과자와 아이스크림 상품 정보 삭제

delete product where type = '과자' or type = '아이스크림';

delete product where type in ('과자', '아이스크림');

-- (Q) 2020년 상반기에 생산한 상품 정보 삭제

delete product

where made between to_date('2020-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

and

to_date('2020-06-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss');