TIL 19일차

Moon-Tree·2023년 1월 25일

delete oracle select sql update 정렬

♣ Oracle

◆ 조회(Read) [SELECT]

SELECT * FROM 테이블명;
미리 테이블에 넣어둔 데이터를 원하는 목적에 맞게 탐색
조건, 그룹, 정렬
DATE
- 날짜와 시분초가 포함된다.
- 시분초를 작성하지 않으면 기본 값은 0 이다.
INSTR
- 오라클은 위치가 1 부터 시작 한다.
- 범위에 포함되지 않으면 0, 포함되어 있으면 1이다.
ex) SELECT * FROM PRODUCT WHERE INSTR(NAME, '초코') > 0;

-- 전체 데이터 조회
SELECT * FROM PRODUCT;

-- 특정 항목만 조회
SELECT NAME FROM PRODUCT;
SELECT NAME, PRICE FROM PRODUCT;

-- 없는 항목은 조회할 수 없다.
//SELECT NAME, POINT FROM PRODUCT;

-- 모든 항목을 조회
SELECT NO, NAME, PRICE, TYPE, MADE, EXPIRE FROM PRODUCT;
SELECT * FROM PRODUCT;

-- 계산한 항목을 추가로 조회
SELECT NAME, MADE, EXPIRE, EXPIRE-MADE FROM PRODUCT; // 날짜 계산은 일 수로 결과가 출력된다.

-- ERROR : 와일드카드는 단독으로만 사용 가능하다.
//SELECT *, EXPIRE-MADE FROM PRODUCT; : 불가능

SELECT PRODUCT.*, EXPIRE - MADE FROM PRODUCT; : 가능

-- 조건(필터) : 숫자, 문자열, 날짜
//SELECT * FROM PRODUCT WHERE 필터링조건;

-- 조건 1개
SELECT * FROM PRODUCT WHERE PRICE < 1000;

-- 조건 2개 이상이라면 자바에서는 &&, || 을 사용하여 처리
-- 오라클에서는 &&는 입력기호, ||는 문자열 덧셈기호라서 사용 불가
-- 대신 알파벳 형태의 연산을 지원한다. (AND, OR)
SELECT * FROM PRODUCT WHERE PRICE >= 1000 AND PRICE <= 2000;

-- (Q) 가격이 1000원 미만이거나 2000원 초과인 제품을 조회
SELECT * FROM PRODUCT WHERE 1000 > PRICE OR PRICE > 2000;

-- (주의) 오라클에서 같다는 = 1개로 처리한다.
-- 번호가 5번인 상품을 조회
SELECT * FROM PRODUCT WHERE NO = 5;

-- 번호가 5번이 아닌 상품을 조회
SELECT * FROM PRODUCT WHERE NO != 5;
SELECT * FROM PRODUCT WHERE NO <> 5;

-- 문자열 조건 : 일치, 유사 검색
-- 유사 검색 명령이 2가지 이므로 구분하여 사용할 수 있어야 한다.
-- LIKE는 %를 사용하여 패턴 검사를 하는 방식
-- INSTR은 특정 키워드가 몇 번째 위치에 존재하는지를 알아내는 방식
-- (!중요!) 전체적인 성능은 INSTR이 우세하지만 시작 검사 만큼은 LIKE가 빠르다.
-- LIKE는 문자열 앞글자만 달라도 FALSE이기 때문에 시작 검사는 빠르지만 %가 첫 글자일 경우 뒷 글자를 전부 검사해야 하기 때문에 검사 속도가 현저히 줄어든다.
-- INSTR은 첫 번째로 0과 1로 포함되어 있는지 확인을 우선 하고서 두 번째로 INSRT > 0; 검사를 한 번 더 하기 때문에 시작 검사는 속도가 느리고
-- 전체 검사는 검사 속도가 일정하기 때문에 전체 검사는 LIKE보다 우세하다.
SELECT * FROM PRODUCT WHERE NAME = '스크류바';

-- 시작 검사일 경우 LIKE가 처리 속도가 빠르다.
SELECT * FROM PRODUCT WHERE NAME LIKE '스크류%'; // % 문자 생략되어도 가능, 패턴 검사
SELECT * FROM PRODUCT WHERE INSTR(NAME, '스크류') > 0; // 인덱스 위치 검사
-- SELECT * FROM PRODUCT WHERE INSTR(NAME, '스크류') = 1; // 시작 검사를 하고 싶을 때는 1을 설정한다.

-- 시작 검사를 제외한 전체 검사는 INSTR이 처리 속도가 빠르다.
SELECT * FROM PRODUCT WHERE NAME LIKE '%이%';
SELECT * FROM PRODUCT WHERE INSTR(NAME, '이') > 0;

-- (Q) 과자와 아이스크림을 조회
SELECT * FROM PRODUCT WHERE TYPE = '과자' OR TYPE = '아이스크림';
SELECT * FROM PRODUCT WHERE TYPE IN ('과자', '아이스크림');

-- (Q) 과자와 주류 중에서 가격이 1000원 이상 2000원 이하인 제품 조회
SELECT * FROM PRODUCT WHERE (TYPE = '과자' OR TYPE = '주류') AND (1000 <= PRICE AND PRICE <= 2000);
SELECT * FROM PRODUCT WHERE (TYPE = '과자' OR TYPE = '주류') AND PRICE BETWEEN 1000 AND 2000; // 1000원 이상 2000원 이하 사이

-- (Q) 이름에 '초코'가 들어있는 제품을 조회 ( 전체 검사는 INSTR의 성능이 우세하다. )
SELECT * FROM PRODUCT WHERE INSTR(NAME, '초코') > 0;
// SELECT * FROM PRODUCT WHERE NAME LIKE '%초코%';

-- (Q) 이름이 '바나나'로 시작하는 제품을 조회 ( 시작 검사는 LIKE의 성능이 우세하다. )
SELECT * FROM PRODUCT WHERE NAME LIKE '바나나%';
// SELECT * FROM PRODUCT WHERE INSTR(NAME, '바나나') = 1;

-- (Q) 이름에 '이'라는 글자가 들어있지 않은 제품을 조회
-- (참고) NOT은 부정연산 자리에 쓸 수 있는 영어 단어형 연산 기호
SELECT * FROM PRODUCT WHERE NAME NOT LIKE '%이%';
SELECT * FROM PRODUCT WHERE INSTR(NAME, '이') = 0;

◆ 날짜

기본 형식은 YYYY-MM-DD HH24:MI:SS (자바와 약간 다르다, 오라클은 대소문자를 구분하지 않는다.)
문자열 처럼도 사용할 수 있지만 국가마다 날짜 형식이 달라서 사용하기에 위험하다.
날짜용 명령들이 존재한다. (TO_CHAR, TO_DATE, EXTRACT)
DATE : 날짜와 시분초가 포함된다. (시분초를 작성하지 않으면 기본 값은 0이다.)

extract는 날짜에서 원하는 항목을 숫자로 추출하는 명령이다
select name, made, extract(year from made) from product;

- (ex) 2019년에 생산한 제품 조회
select * from product where extract(year from made) = 2019;

- (Q) 여름(6, 7, 8월)에 생산한 제품 조회
select * from product 
where extract(month from made) = 6 or extract(month from made) = 7 or extract(month from made) = 8;

select * from product 
where extract(month from made) in (6, 7, 8);

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

to_char 명령은 날짜를 원하는 서식의 문자열로 변경하는 명령

(자바의 SimpleDateFormat과 유사한 느낌이지만 대소문자는 구분하지 않음)
select name, made, to_char(made, 'yyyy-mm-dd hh24:mi:ss') from product;

- (ex) 2019년에 생산한 제품 조회
select * from product where to_char(made, 'yyyy') = '2019';

- (ex) 봄(3,4,5월)에 생산한 제품 조회
select * from product
where to_char(made, 'mm') = '03' or to_char(made, 'mm') = '04' or to_char(made, 'mm') = '05';

select * from product
where to_char(made, 'mm')  in ('03','04','05');

select * from product
where regexp_like(to_char(made, 'mm'), '^0[345]$');

그냥 날짜 자체로 비교도 가능한가?
(ex) 2019년에 생산한 제품 조회 = 2019-01-01부터 2019-12-31까지
문자열과 날짜를 혼용하면 국가나 지역에 따라 작동하지 않을 수 있다
// select * from product where made >= '2019-01-01' and made <= '2019-12-31'; : 불가능

to_date를 사용하면 문자열을 날짜로 바꿀 수 있다(형식을 알려줘야 한다)

	select * from product where made >= to_date('2019-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss');
	select * from product where made <= to_date('2019-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss');

	select * from product where made between to_date('2019-01-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');

날짜에서 sysdate라는 키워드는 현재시각에 대한 날짜 객체를 말한다

	- 최근 1년간 생산된 제품 조회
	select * from product where made between sysdate-365 and sysdate;

	- 최근 5분간 내역 조회
	select * from product where made between sysdate-5/24/60 and sysdate;

◆ 정렬

조회가 끝난 데이터를 원하는 순서대로 나열하는 작업
오름차순(ascending, asc)과 내림차순(descending, desc)으로 구분

(주의) 정렬은 데이터가 확정된 이후에 해야한다

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

정렬은 마지막에!

	select * from product order by price desc where price between 1000 and 2000;
	select * from product where price between 1000 and 2000 order by price desc;

만약 정렬 시 동일한 항목이 존재한다면, 하위 정렬 기준을 추가해야 한다
```
	select * from product order by price desc, no asc;
```

◆ 수정(UPDATE)

이미 존재하는 데이터를 고치는 것이다.
COMMIT / ROLLBACK의 영향을 받는다.
UPDATE 테이블이름 SET 바꿀 내용 [조건];
전체를 수정하는 경우는 거의 없고, 조건을 통해 원하는 항목을 수정한다.

특히 PK(Primary Key)를 이용하여 하나의 항목만 고치는 경우가 많다.

	-- 모든 상품 가격을 0원으로 변경
	UPDATE PRODUCT SET PRICE = 0;

	-- 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 -= 500  WHERE TYPE = '과자'; -- ERROR
	-- UPDATE PRODUCT SET PRICE = -500  WHERE TYPE = '과자'; -- ERROR
	UPDATE PRODUCT SET PRICE = PRICE - 500  WHERE TYPE = '과자';

	-- (Q) 아이스크림 가격 10% 인상
	UPDATE PRODUCT SET PRICE = PRICE * 1.1 WHERE TYPE = '아이스크림';

	SELECT * FROM PRODUCT;
	ROLLBACK;  -- `COMMIT;` 만 하지 않으면 이전의 데이터 상태로 돌아갈 수 있다.

◆ 삭제(DELETE)

존재하는 데이터를 지우는 작업이다.
데이터의 변경이 생기므로 COMMIT / ROLLVACK 필요하다.
조건을 대부분 사용한다.
DELETE 테이블이름 [조건];

실제로는 PK(Primary Key)를 이용하여 하나의 항목만 삭제하는 경우가 많다.

	-- 전체 삭제
	DELETE PRODUCT;
	-- DELETE * FROM PRODUCT; -- CREATE의 코드와 비슷하여 헷갈리므로 권장하지 않는다.

	-- (Q) 1번 상품 삭제
	DELETE PRODUCT WHERE NO = '1';

	-- (Q) 2020년 상반기에 제조된 상품 정보를 삭제
	DELETE PRODUCT WHERE EXTRACT (YEAR FROM MADE) = 2020 
	AND EXTRACT (MONTH FROM MADE) BETWEEN 1 AND 6;

	DELETE PRODUCT WHERE REGEXP_LIKE(TO_CHAR(MADE, 'YYYY-MM'), '^2020-0[1-6]$');

	DELETE PRODUCT WHERE MADE BETWEEN TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') 
	AND TO_DATE('2020-6-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

	SELECT * FROM PRODUCT;
	ROLLBACK; -- 현재까지의 작업 내역을 무효화 시킨다.
	COMMIT; -- 현재까지의 작업 내역을 최종 저장한다.

Moon-Tree

Backend Developer

이전 포스트

TIL 18일_5차

다음 포스트