DBMS-데이터를 검색,수정,삭제(feat.오라클 함수 2편)

직접 내용 정리하고 만든 예이니 퍼가실 때는 출처를 남겨주세요 :)

※ 들어가기 전 공지사항

DBMS - 개요 편을 참고해 계정을 생성해주시고, 

DBMS - 개요

DBMS-Table&레코드와 시퀀스 편을 봐주시고 와주셔야 합니다.

DBMS-Table&레코드와 시퀀스

---

▷실습하기 전 알아야 할 개념을 정리해보겠습니다.

데이터를 검색, 수정, 삭제

• 테이블의 데이터 전체 검색(모든 필드의 데이터를 검색) 할 때에는 select * from  테이블명; 을 이용합니다.
• 데이터를 검색할 때에는 select 필드명, 필드명,... from 테이블명 where 조건식; 을 이용합니다.
• 데이터를 수정할 때에는 update 테이블명 set 필드명 = 값, 필드명 = 값,... where 조건식; 을 이용합니다.
• 데이터를 삭제를 하고 싶을 때에는 delete 테이블명 where 조건식; 을 이용합니다.
• 테이블의 데이터 전체를 삭제하고 싶을 때에는 delete 테이블명; 을 이용합니다.

1. 오라클 함수

(1) NULL 함수

○ NVL(input1, input2)

input1 컬럼이 NULL이면 input2를 반환하고 NULL이 아니면 input1을 반환하는 함수입니다.

○ NVL2(input1, input2,input3)

input1 컬럼이 NULL이 아니면 input2를 반환하고 NULL이면 input3을 반환하는 함수입니다.

(2) 수치함수

sum(필드명)	특정필드의 합을 구하는 함수입니다.
avg(필드명)	특정필드의 평균을 구하는 함수입니다. *참고로, round를 이용해 보기 좋게 정리하기도 합니다.
max(필드명)	특정 필드의 최대값을 구하는 함수입니다.
min(필드명)	특정 필드의 최소값을 구하는 함수입니다.
count(필드명)	특정 필드의 개수를 구하는 함수입니다.

2. 연산자

(1) 논리연산자

where 필드명 논리 연산자 값; 으로 씁니다.

and	두 개의 조건을 모두 충족해야할 때 이용합니다.
or	둘 중 하나라도 만족할 때 이용합니다.
not	조건의 반대되는 상황일 때 이용합니다.

(2) 멤버연산자(in 연산자)

where 필드명 in(또는 not in) (값,값,값,...); 으로 씁니다.

in	값들 중 하나 이상 일치하면 조건에 맞는 것으로 생각해 그 내용을 보여줍니다.
not in	값들 중 하나 이상 일치하는 조건을 제외한 내용을 보여줍니다.

(3) 비교연산자

<	작다.
>	크다.
>=	크거나 같다.
<=	작거나 같다.
=	같다.
!=	같지 않다.
<>	같지 않다.
!<	작지 않다.(-크거나 같다.) 표준은 아니다.
!>	크지 않다.(=작거나 같다.)

(4) is null 연산자

데이터 값이 null 인 경우를 조회할 때 이용합니다.

참고로, is not null 이 되면 데이터 값이 null 이 아닌 경우를 조회할 때 씁니다.

(5) 연결연산자 (||)

각 열의 결과를 연결해 하나의 열로 결과를 표현할 수 있습니다.

또한, 문자열을 추가해서 새로운 데이터를 표현하는 열을 만들 수도 있습니다.

(6) any 연산자

select * from 테이블명 where 필드명 비교연산자 any(값,값,값,...); 으로 씁니다.

(7) 기타 연산자

between A and B	A와 B 사이의 내용을 검색할 때 이용합니다.
like	문자열 포함 검색할 때 이용합니다.**

**참고로, like 연산자는 where 필드명 like 값; 으로 씁니다.

○ 값% 의 의미는 값으로 시작하는가? 입니다.

○ %값 의 의미는 값으로 끝나는가? 입니다.

○ %값% 의 의미는 값이 포함이 되는가? 입니다.

○ _ 의 의미는 문자하나 어떤 문자든 상관 없지만 한 자리 문자를 뜻합니다.

3. 키워드

(1) 정렬

order by 정렬 ; 는 항상 SQL 문 가장 끝에 적혀 있어야 하며 where 절에 꼭 써야 하는 것은 아닙니다.

○ asc 는 오름차순 정렬할 때 씁니다.

○ desc 는 내림차순 정렬할 때 씁니다.

(2) 중복제거

select 필드명 distinct 필드명 from 테이블명; 을 이용합니다.

(3) 별칭

select 필드명 as 별칭 필드명 from 테이블명; 을 이용합니다.

참고로, 별칭을 붙일 때에는 한글이나 특수기호 또는 공백을 표현하고 싶을 시 "" 안에 적어야 합니다.

▷ 이제 실습해봅시다.

○ 실습하기 전, 아래에 있는 코드를 이용해 테이블 하나를 만들고 시퀀스를 추출해봅시다.

conn oracle/oracle;
create table member(
no number, 
name varchar2(30), 
id varchar2(20), 
pw varchar2(20),
money number,
da date
);

create sequence member_seq nocache;
insert into member values(
member_seq.nextval,'고길동','rhrlfehd','rhrlfehd',400,to_date('2021-06-14'));
insert into member values(
member_seq.nextval,'노길동','shrlfehd','shrlfehd',400,to_date('2021-06-14'));
insert into member values(
member_seq.nextval,'도민준','ehalswns','ehalswns',1400,to_date('2011-06-14'));
insert into member values(
member_seq.nextval,'도길동','ehrlfehd','ehrlfehd',300,to_date('2013-11-05'));
insert into member values(
member_seq.nextval,'천송이','cjsthddl','cjsthddl',500,to_date('2013-06-12'));
col name for a10;
col id for a10;
col pw for a11;
select * from member;
commit;

**참고로, 문자열의 간격조절을 하고 싶을 경우에는 col 항목명 for 크기;를 이용하면 됩니다.

이 때, 크기는 a10,a5와 같이 지정이 가능합니다. 

또한 commit 을 해도 문자열의 간격 조절은 저장되지 않고 테이블과 시퀀스만 저장됩니다.

 

○ 이제 실습해봅시다.

1. 계정을 연결해주세요.

2. no가 2번인 회원을 검색해보세요.

3. no가 2번이 아닌 회원을 검색해보세요.

4. name이 도민준인 회원을 검색해보세요.

5. name이 도씨인 회원을 검색해보세요.

6. name이 동으로 끝나는 회원을 검색해보세요.

7. pw 가 있는 회원 검색 후 마일리지를 내림차순으로 정렬해보세요.

8. da가 2013년도인 회원을 검색해보세요.

9. 11월에 가입한 회원을 찾아보세요.

10. no가 2번인 회원의 이름을 운영자로, id는 operator 로 수정해주세요.

11. da가 2011년인 회원들에게 money를 1000씩 제공해주세요.

12. id가  operator 인 사람을 탈퇴시켜주세요.

13. money필드의 합을 구하세요.

14. money필드의 최소값을 구하세요.

15. 연결연산자와 별칭을 이용하여 아래와 같이 출력하시오.

16. pw만 출력하시오.

17. in 연산자를 이용하여 no 중 1의 내용만 보여주세요.

18. any 연산자를 이용하여 아래와 같이 출력하시오.

19. member 테이블을 삭제하세요.

20. member_seq 을 삭제하세요.

21. 계정을 삭제해주세요.

답은 순서대로 입니다.

conn oracle/oracle;
select * from member where no=2;
select * from member where no!=2;
select * from member where name='도민준';
select * from member where name like '도%';
select * from member where name like '%동';
select * from member where pw is not null order by money desc;
select * from member where da>to_date('2012-12-31') and da<to_date('2014-01-01');
select * from member where da like '%/11/%';
update member name='운영자',id='operator' where no=2;
update member set money = money+1000 where da like '11/%/%';
delete member where id='operator';
select sum(money) from member;
select min(money) from member;
select name || '님이 가지고 있는 돈은'|| money as history from member;
select nvl(pw,'null') from member;
select * from member where no in(1,2);
select * from member where money>any(400,1000);
drop table member;
drop sequence member_seq;
/*21번의 답*/
conn/as sysdba;
drop user oracle;