[30일 차] : Oracle - DML(INSERT, UPDATE DELETE) , DDL(ALTER, DROP)

서하루·2022년 11월 23일
0

(2) Oracle 공부기록

목록 보기
8/11

[학습목표]

  • DML < INSERT, UPDATE, DELETE >
  • DDL < ALTER, DROP >

< DML 데이터 조작언어 >

➡️ 테이블의 데이터를 삽입(INSERT)하거나, 수정(UPDATE)하거나, 삭제(DELETE)하는 구문

📒1. INSERT

- 테이블에 새로운 행을 추가시키는 구문

표현법1
> 특정 컬럼을 지정하지 않고 삽입하고자 할 때

--INSERT INTO 테이블명 VALUES(값, 값 값 ''');

--> 컬럼의 순번을 지켜서 VALUES의 값 나열해야함
--> 컬럼보다 부족하게 값을 입력했을 시 : not enough values 오류
--> 컬럼보다 값을 더 많이 입력했을 시 : too many values 오류
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - -

INSERT INTO EMPLOYEE
	VALUES(900, '홍길동', 
    '991212-123456', 'D1', SYSDATE, NULLM DEFAULT);


-- - - - - - - - - - - - - - -- - - - - - - - - - - - - - -

표현법2
> 특정컬럼을 선택해서 값을 제시하고자 할 때

INSERT INTO 테이블명(컬럼명, 컬럼명 ..) VALUES(값,값 ..);

--> 한 행으로 추가되기 때문에 선택 안된 컬럼은 기본적으로 NULL입력
--> 단, NOT NULL 제약조건이 걸려있는 컬럼은 반드시 선택해서 값을 제시해야함 !
> 단, 기본값(DEFAULT)이 지정됐다면 NULL 대신 기본값이 들어감

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
VALUES(901, 둘리, 120505-2055647, J7, SYSDATE);

- 서브쿼리를 수행한 결과값을 통째로 INSERT 가능

-- 새로운 테이블 셋팅
CREATE TABLE EMP_01 (
	EMP_ID NUMBER,
    EMP_NAME VARCHAR2(20),
    DEPT_TITLE VARCHAR2(20)
);

INSERT INTO EMP_01
(
	SELECT EMP_ID, EMP_NAME, DEPT_TITLE
      FROM EMPLOYEE
      LEFT JOIN DEPARTMANET ON (DEPT_CODE = DEPT_ID)

);

📒1-2. INSERT ALL

- 두 개 이상의 테이블에 각각 INSERT 할 때
- 사용되는 서브쿼리가 동일할 경우

✅ INSERT ALL 표현법 1

CREATE TABLE EMP_DEPT
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
	 FROM EMPLYEE
    WHERE 1=0; 
=> 컬럼명만 가져오기 위해서  FALUS가 나오는 조건식을 작성함 
    
CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, MANAGER_ID
	 FROM EMPLOYEE
	WHERE 1=0;
    

=> EMP_DEPT 테이블과 EMP_MANAGER 테이블 생성

-- 부서코드가 D1인 사원들의 사번,이름,부서코드, 입사일,사수사번 조회

SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1';

/*
-- INSER ALL을 쓰기위한 표현법 1
INSERT ALL
INTO 테이블명1 VALUES(컬럼명, 컬럼명, 컬럼명 ''')
INTO 테이블명2 VALUES(컬럼명, 컬럼명 ```)
	서브쿼리;
-------------------------------------------------
*/

INSERT ALL
INTO EMP_DEPT VALUES(EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE)
INTO EMP_MANAGER VALUES(EMP_ID, EMP_NAME, MANAGER_ID)

	SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
	  FROM EMPLOYEE
     WHERE DEPT_CODE = 'D1';

✅ INSERT ALL 표현법 2

CREATE TABLE EMP_OLD
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
     FROM EMPLOYEE
    WHERE 1=0;
    
CREATE TABLE EMP_NEW
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
     FROM EMPLOYEE
    WHERE 1=0;

/*
-- INSERT ALL 표현법 2
INSERT ALL
WHEN 조건1 THEN
	INTO 테이블명1 VALUES(컬럼명, 컬럼명 ''')
WHEN 조건2 THEN
	INTO 테이블명2 VALUES(컬럼명, 컬럼명''')
  서브쿼리;
-------------------------------------

*/
-- 입사일을 2000년 1월 1일 기준으로 이전 입사자들은 
-- EMP_OLD, 이후 입사자들은 EMP_NEW테이블에 들어가게됨 

INSERT ALL
WHEN HIRE_DATE < '2000/01/01' THEN
	INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE > '2000/01/01' THEN
	INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALATY)
    
    SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
     FROM EMPLOYEE;

📒 2. UPDATE

- 테이블에 기록돼있는 기존의 데이터를 수정하는 구문
⭐ UPDATE시 해당 컬럼에 대한 제약조건에 위배되서는 안됨 !

 /*
 	[표현법]
    UPDATE 테이블명
    SET 컬럼명 = 바꿀값,
    	컬럼명 = 바꿀값,
        ''''
     [WHERE 조건]; 
  > 여러개의 컬럼명 동시변경 가능 (단 ,컴마로 나열하기)
  > WHERE절 생략시 전체 모든 행의 데이터가 변경됨
 	
 
 */

-- D9 부서의 부서명을 '전략기획부'로 수정하기
UPDATE DEPT_COPY
SET DEPT_TITLE = '전략기획부'
WHERE DEPT_ID = 'D9';

-- 홍길동 사원의 급여를 200만원으로 수정하기
UPDATE EMP_SALARY
SET SALARY = 2000000
WHERE EMP_ID = '홍길동';

-- 김똥개 사원의 급여를 300만원, 보너스를 0.2로 변경
UPDATE EMP_SALARY
SET SALARY = 3000000, BONUS = 0.2
WHERE EMP_NAME = '김똥개';


-- < UPDATE문 안에 서브쿼리 가능 >
-- 방명수사원의 급여와 보너스를 유재식사원과 동일하게 수정하기
UPDATE EMP_SALARY
SET (SALARY, BONUS) = (SELECT SALARY, BONUS
						 FROM EMP_SALARY
                        WHERE EMP_NAME = '유재식')
                        
WHERE EMP_NAME = '방명수';

-- EMP_SALARY로부터 보너스값을 0.3으로 변경
-- 단, ASIA지역에서 근무하는 사원들만 !

UPDATE EMP_SALARY
SET BONUS = 0.3
WHERE EMP_ID IN (SELECT EMP_ID
				  FROM EMP_SALARY
                  JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID)
                  JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
                 WHERE LOCAL_NAME LIKE 'ASIA%');

📒 3. DELETE

- 테이블에 기록된 데이터를 삭제하는 구문 (한 행 단위)


/*
	[표현법]
    DELETE FROM 테이블명
    [WHERE 조건];
 
 > WHERE절 생략시 전체 행 모두 삭제됨 !
 
*/

--둘리, 마이클 사원 데이터 삭제하기
DELETE FROM DEPARTMENT
WHERE EMP_NAME IN ('둘리', '마이클');


-- DEPT_ID가 D1인 부서 삭제하기
DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';

-- > 위 데이터는 D1을 쓰고있는 자식데이터가 있기때문에 삭제가 불가능함

-- > 삭제 시 외래키 제약조건에 의해서 삭제가 불가능한 경우,
-- 잠시 제약조건을 비활성화시킬 수 있음 ! 

-- 비활성화 시키는 방법
ALTER TABLE EMPLOYEE DISABLE CONSTRAINT SYS_C007179 CASCADE;

-- 활성화 시키는 방법
ALTER TABLE EMPLOYEE ENABLE CONSTRAINT SYS_C007179;

< DDL 데이터 정의언어 >

➡️ 객체들을 생성(CREATE), 변경(ALTER), 삭제(DROP)하는 구문

📒 4. ALTER

- 객체를 변경하는 구문

🔴 [표현법] ALTER TABLE 테이블명 변경할내용

1.-1 컬럼 추가(ADD) : ADD 컬럼명 자료형 [DEFALUT 기본값]


-- DEPT_COPY에 CNAME 컬럼 추가하기
ALTER TABLE DEPT_COPY ADD CNAME VARCHAR2(20);
-- 새로운 컬럼이 만들어지고 기본적으로 NULL로 채워짐

-- DEPT_COPY에 LNAME 컬럼 추가(기본값을 한국으로)
ALTER TABLE DEPT_COPY ADD LNAME VARCHAR2(20) DEFAULT '한국';

1-2. 컬럼 수정(MODIFY)
🔴 데이터타입 수정 : MODIFY 컬럼명 바꾸고자하는데이터타입
🔴 DEFAULT값 수정 : MODIFY 컬럼명 DEFAULT 바꾸고자하는 기본값


ALTER TABLE DEPT_COPY MODIFY DEPT_ID CHAR(3);
-- 데이터타입을 CHAR(3)으로 수정완
--ALTER TABLE DEPT_COPY MODIFY DEPT_ID NUMBER;  <오류발생>
-- 현재 담겨있는 데이터타입이 CHAR타입인데 NUMBER로 바꾸려니 당연히 불가능한

1-3. 컬럼 삭제(DROP COLUMN)
🔴 [표현법] DROP COLUMN 삭제하고자하는컬럼명

ALTER TABLE EMP_NEW DROP COLUMN SALARY;
-- 테이블에서 최소 한개의 컬럼 하나는 있어야하기에 마지막에 삭제하려는 컬럼의 경우 오류 발생
--ROLLBACK > DDL구문을 취소시킬 수 없음

📒 4. 제약조건 추가/삭제

4-1. 제약조건 추가

➡️ PRIMARY KEY : ADD PRIMARY KEY(컬럼명)

➡️ FOREIGN KET : ADD FOREIGN KEY(컬럼명) REFERENCES 참조할테이블명[(컬럼명)][삭제옵션]

➡️ UNIQUE : ADD UNIQUE (컬럼명)

➡️ CHECK : ADD CHECK (컬럼에대한조건)

➡️ NOT NULL : MODIFY 컬럼명 NOT NULL | NULL

4-2. 제약조건 삭제
➡️DROP CONSTRAINT 제약조건명

➡️ NOT NULL의 경우 : MODIFY 컬럼명 NULL


--DEPT_COPY테이블로부터
--DEPT_ID에 PRIMARY KEY 제약조건 추가
--DEPT_TITLE에 UNIQUE 제약조건 추가
--LNAME에 NOT NULL 제약조건 추가

ALTER TABLE DEPT_COPY
    ADD CONSTRAINT DCOPY_PK PRIMARY KEY(DEPT_ID) -- 제약조건이름설정 가능 CONSTRAINT
    ADD CONSTRAINT DCOPY_UQ UNIQUE(DEPT_TITLE)
    MODIFY LNAME CONSTRAINT DCOPY_NN NOT NULL;

📒 5. 컬럼명/제약조건명/테이블명 변경(RENAME)

🔴 컬럼명 변경 : RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명
🔴 제약조건명 변경 : RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명
🔴 테이블명 변경 : RENAME [기존테이블명] RENAME TO 바꿀테이블명


-- 3-1. 컬럼명 변경 : RENAME COLUMN 기존컬럼명 TO 바꿀컬럼명
ALTER TABLE DEPT_COPY RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

-- 3-2. 제약조건명 변경 : RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명
ALTER TABLE DEPT_COPY RENAME CONSTRAINT SYS_C007186 TO LID_NN;

-- 3-3. 테이블명 변경 : RENAME [기존테이블명] TO 바꿀테이블명;
ALTER TABLE DEPT_COPY RENAME TO DEPT_TEST;

📒 6. 테이블 삭제

🔴 DROP TABLE 테이블명;

  • 단, 어딘가에 참조되고 있는 부모테이블의 경우 함부로 삭제 불가
  • 만약 부모테이블을 삭제하고자 한다면
    방법1. 자식테이블 삭제 후 부모테이블 삭제
    방법2. DROP TABLE 테이블명 CASCADE CONSRTAINT; 부모테이블만 삭제하는데, 제약조건도 삭제하는 방법 !
profile
서하루
이전 포스트

[29일 차] : Oracle - DDL(CREATE)

다음 포스트

[31일 차] : Oracle - DCL, TCL, VIEW

0개의 댓글