[ORACLE] 레코드 추가(INSERT), 수정(UPDATE), 삭제(DELETE), 조회(SELECT) 명령 (DML)

 

---

레코드 추가(INSERT), 수정(UPDATE), 삭제(DELETE), 조회(SELECT)

데이터 조작 언어 DML 명령 알아보기

---

 

 

1. DML?

---

DML(Data Management Language) 데이터 조작 언어란, 테이블에 레코드를 조작하기 위한 명령들을 일컫는다. 레코드는 각기 다른 자료형을 담을 수 있는 복합형 구조의 데이터 타입이다. 테이블의 한 행에 대응하며, C언어의 구조체와 성격이 비슷하다.

 

테이블에 레코드를 추가하는 INSERT, 수정하는 UPDATE, 삭제하는 DELETE, 조회 및 선택하는 SELECT 명령을 말한다.

 

 

 

 

2. 레코드 추가(INSERT)

---

 

레코드 추가 방법 #1

-- 레코드 추가 방법 #1
INSERT INTO 테이블이름(필드명1, 필드명2, 필드명3, ...) VALUES(값1, 값2, 값3, ...);

 

방법 #1 은 필드명과 입력되어야 하는 값들이 1:1 매칭되어 입력된다. 테이블의 필드명 순서를 반드시 지켜주어야 하는 것은 아니지만, 값들은 INSERT 명령 내부에 나열된 필드명대로 순서를 맞춰서 입력된다. 자료형이 일치하는 경우, 의도한 값이 아니어도 실행될 수 있기 때문에 순서를 집중해서 맞춰주자.

 

또한, null 값을 허용하는 필드나 default 값이 있는 필드는 생략하고 입력할 수 있다. 물론, 지정도 가능하다.

INSERT INTO exam(deptno, dname, loc) VALUES(50, NULL, '서울');

 

 

예시 1. exam01 테이블에 deptno 필드에는 10, dname 필드에는 '기획부', loc 필드에는 '서울' 값을 담으려면?

INSERT INTO exam01(deptno, dname, loc) VALUES(10, '기획부', '서울');

 

SQL문에서 문자를 입력할 때 '작은따옴표' 를 사용한다. (큰 따옴표는 사용처가 따로 있다.)

 

 

예시2. exam01 테이블에 deptno 필드에 20, dname 필드에 '영업부' 값을 담는 레코드를 작성하려면?

INSERT INTO exam01(deptno, dname) VALUES(20, '영업부');

 

예시3. booklist 테이블에 추가할 레코드 작성

INSERT INTO booklist(booknum, subject, makeyear, inprice, rentprice, grade)
VALUES(book_seq.nextVal, '좀비아이', 2020, 12000, 2500, 'all');

 

 

 

레코드 추가 방법 #2

-- 레코드 추가 방법 #2
INSERT INTO 테이블이름 VALUES(값1, 값2, 값3, ...);

 

방법 #2 는 모든 필드에 해당하는 값들을 최초에 테이블 생성시에 기술한 필드 순서에 맞춰 모두 입력하는 방법이다. NULL 을 허용하는 곳이라도 비우고 입력할 수 없으며, NULL을 직접 지정해야 한다.

 

 

예시1. exam01 테이블에 deptno NUMBER(2), dname VARCHAR(5), loc VARCHAR(10) 필드가 있다면?

INSERT INTO exam01 VALUES(30, '자재부', '부산');

 

예시2. 위와 같은 조건에 NULL 값이 들어가는 경우라면?

INSERT INTO exam01 VALUES(20, NULL, '대전');

 

예시3. booklist 테이블에 방법 #2로 값 넣어보기

INSERT INTO booklist
VALUES(book_seq.nextVal, '누가 내 머리에 똥쌌어', 2000, 11000, 1000, 'all');

 

 

 

3. 레코드 수정(UPDATE)

---

UPDATE 테이블명 SET 변경내용 WHERE 검색조건

 

데이터를 수정하는 UPDATE 명령이다. WHERE 구분은 생략이 가능하다. WHERE 절을 생략하면 모든 레코드를 대상으로 UPDATE 명령이 실행된다. (모든 레코드가 수정된다.)

 

검색 조건은 필드명(관계연산자)조건값으로 이루어진 조건 연산이다. 자바에서 if(괄호) 안에 사용했던 연산을 사용한다고 생각하면 된다. 나이가 29세 이상이라면 WHERE age >= 29 처럼 기술한다.

 

두개 이상의 필드를 수정하고자 한다면 , (콤마)로 구분해서 기술한다. 복합 조건을 사용할 때는 AND, OR을 사용한다.

(자바에서 사용한 관계연산자로 표현하면 && → AND, || → OR, ! → NOT )

 

 

예시1. memberlist 테이블에 membernum이 3인 회원의 전화번호를 010-0000-1111로 수정한다면?

UPDATE memberlist SET phone='010-0000-1111' WHERE membernum=3;

 

WHERE 절 안에 있는 ' = ' 은 ' 같다 ' 의 의미이고, SET 에 있는 ' = ' 는 대입 연산자이다.

 

 

예시2. booklist 테이블에 도서 제목(subject) '봉제인형 살인사건' 도서의 grade를 'all' 로 수정한다면?

UPDATE booklist SET grade='all' WHERE subject='봉제인형 살인사건';

UPDATE booklist SET grade='all' WHERE subject LIKE '봉제인형%'; -- 봉제인형 으로 시작
UPDATE booklist SET grade='all' WHERE subject LIKE '%살인사건'; -- 살인사건 으로 끝
UPDATE booklist SET grade='all' WHERE subject LIKE '%인형 살인%' -- ~인형 살인~ 이 중간에 들어감

 

%(와일드카드 문자) 는 위치에 따라 이곳에 문자가 올 수 있음을 나타낸다.

 

 

예시3. booklist의 모든 도서의 대여가격(rentprice)를 10% 이상 인상한 값으로 수정한다면?

UPDATE booklist SET rentprice = rentprice * 1.1;

 

예시4. memberlist 에서 생일이 NULL 인 경우, '2000-01-01' 로 설정한다면?

UPDATE memberlist SET birth='00/01/01' WHERE birth IS NULL;

 

NULL 값은 ' = ' 로 비교 할 수 없다. IS 를 사용해야 한다.

 

 

예시5. 외래키로 참조되는 필드를 수정한다면?

ALTER TABLE rentlist ADD CONSTRAINT fk2 FOREIGN KEY(mnum) REFERENCES memberlist(membernum);

 

memberlist 테이블의 membernum 필드가 rentlist 테이블의 mnum 에 참조되는 값이라고 했을 때

UPDATE memberlist SET membernum=10 WHERE membernum=1;

 

rentlist 테이블의 bnum 필드 값에 1이 없다면 변경이 가능하고, 있다면 변경이 불가능하다.

 

 

 

4. 레코드 검색(SELECT)

---

SELECT 필드명들 또는 * FROM 테이블명 WHERE 검색조건

 

 

예제1. memberlist의 모든 필드를 검색하려면?

SELECT * FROM memberlist;

 

모든 필드를 한 번에 지목하기 위해서 * 를 사용한다.

 

 

예제2. booklist에서 출판년도(makeyear)가 2020년 이후인 도서의 제목(subject)와 출판년도(makeyear)를 조회하려면?

SELECT subject, makeyear FROM booklist WHERE makeyear>=2020;

 

예제3. booklist에서 대여가격(rentprice) * 10 가격에서 구매가격(inprice)를 빼기 연산한 데이터를 "마진"이란 표시를 해서 조회하려면?

SELECT rentprice * 10 - inprice AS "마진" FROM rentprice;

 

AS 로 화면에 표시될 데이터 값을 지정할 수 있다. 지정할 데이터 값은 "큰 따옴표"를 붙인다. (생략해도 되나 구분을 위해 붙여주자)

 

 

예제4. booklist에서 booknum과 subject 값을 - 로 붙이고, book info 란 데이터로 출력한다면?

SELECT booknum || '-' || subject AS "book info" FROM booklist;

 

 

예제5. rentlist에서 bnum의 데이터 중 중복을 제외하고, "대여된 도서번호들" 이란 데이터로 출력한다면?

SELECT DISTINCT bnum AS "대여된 도서번호들" FROM rentlist;

 

DISTINCT 는 중복을 제거한다.

 

 

예제6. booklist에서 도서 제목(subject)에 두번째 글자가 '것'인 도서 정보를 모두 출력한다면?

SELECT * FROM booklist WHERE subject LIKE '__것%';

 

예제7. emp 테이블에서 sal이 1000 이상인 데이터를 ename의 내림차순으로 정렬하여 조회한다면?

SELECT * FROM emp WHERE sal>=1000 ORDER BY ename DESC;

 

ORDER BY 는 SELECT 명령의 결과를 특정 필드값의 오름차순이나 내림차순으로 정렬하라는 명령이다.

ASC은 오름차순 정렬, DESC은 내림차순 정렬이다. 정렬 기준을 쓰지 않으면 기본적으로 오름차순 정렬로 실행된다. 내림차순 정렬을 위해서는 반드시 정렬 기준이 되는 필드명 뒤에 DESC를 써주어야 한다.

WHERE 구문 뛰에 또는 구문 맨 끝에 ORDER BY 필드명 [DESC]

 

위와 같은 형태로 사용한다.

 

 

예제8. emp 테이블에서 급여(sal)이 1000 달러 이상, 3000달러 이하인 사원 정보를 모두 출력한다면?

SELECT * FROM emp WHERE sal>=1000 AND <= 3000;
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;

 

예제9. emp 테이블에서 부서번호(deptno)가 10이 아닌 사원 정보를 모두 출력한다면?

SELECT * FROM emp WHERE NOT(deptno=10);
SELECT * FROM emp WHERE deptno<>10;

 

 

 

5. 함수를 사용하는 레코드 검색(SELECT)

---

SUM(), AVG()

예제1. booklist의 입고가격(inprice) 합계를 구하면?

SELECT SUM(inprice) AS "입고가격합계" FROM booklist;

 

예제2. booklist의 입고가격(inpirce) 평균을 구하면?

SELECT AVG(inprice) AS "입고가격평균" FROM booklist;

 

NUMBER 자료형을 갖는 필드에 한해 더하는 SUM(필드명) , 평균내는 AVG(필드명) 함수를 사용할 수 있다.

 

 

ANY(), SOME()

ANY() 은 괄호안에 나열된 내용 중 어느 하나라도 해당하는 것이 있다면 검색 대상으로 한다. IN과 유사한 기능이나 ANY 는 ' = ' 을 사용한다.

 

예제1. emp 테이블에 deptno 값이 10, 20, 40 중 하나라도 해당되는 레코드를 출력하려면?

SELECT * FROM emp WHERE deptno = ANY(10, 20, 40);

 

예제2.

SELECT * FROM emp WHERE deptno = SOME(10, 20, 40);

 

ANY() 와 SOME() 은 같은 기능을 한다.

 

 

ALL()

ALL() 은 괄호안에 나열된 내용을 모두 만족해야 검색 대상으로 한다.

 

예제1. emp 테이블에 deptno 값이 10, 20, 40 을 모두 만족하는 레코드를 출력하려면?

SELECT * FROM emp WHERE deptno = ALL(10, 20, 40);

 

ALL() 의 경우 괄호안의 값을 동시에 만족해야 하는 조건이므로 해당하는 레코드가 없을 경우가 대부분이다. 위와 같은 방식보다는 아래 예제처럼 ' 구성내용과 모두 같지 않을 때 ' 를 필터링할 때 자주 사용된다.

 

 

예제2. emp 테이블에 deptno 값이 10, 20, 40 을 만족하지 않는 레코드를 출력하려면?

SELECT * FROM emp WHERE dptno <> ALL(10, 20, 40);

 

예제3.

SELECT * FROM emp WHERE deptno NOT IN(10, 20, 40);

 

위와 같이 IN으로 표현할 수 있다.

 

 

 

6. 레코드 외의 검색(SELECT)

---

6-1. 사용자가 관리하는 테이블 목록

SELECT * FROM TAB; -- 오래된 명령
SELECT * FROM TABS;

 

현재 사용자(scott) 이 관리하고 있는 테이블 목록을 보여준다.

 

 

6-2. 특정 테이블의 구조 조회

DESC booklist;

 

커맨드창(cmd)에서 테이블의 필드 리스트와 데이터 형식을 확인할 수 있다.

 

 

 

 

6-3. 테이블의 제약사항 조회

SELECT * FROM USER_CONSTRAINTS;

 

모든 제약 사항을 확인할 수 있다.

 

SELECT * FROM USER_CONSTRAINT WHERE TABLE_NAME = 'rentlist';

 

WHERE 절을 붙일 경우 특정 테이블의 제약 사항만을 확인한다.

 

 

 

7. 레코드 삭제(DELETE)

---

DELETE FROM 테이블명 WHERE 삭제조건;

 

WHERE 절을 붙이지 않을 경우 해당 테이블의 모든 데이터를 삭제한다.

 

 

예제1. emp 테이블의 모든 레코드를 삭제하려면?

DELETE FROM emp;

 

예제2. booklist 테이블에서 bnum 값이 5보다 큰 레코드를 삭제하려면?

DELETE FROM emp WHERE bnum>=5;