[8일차] DML(Data Manipulation Language), DDL(Data Definition Language)

오늘은 데이터베이스 명령어 DML, DDL에 대해 알아보자

 

DML(Data Manipulation Language)

DML은 데이터 조작 언어이며 대표적인 종류로는

• INSERT
• UPDATE
• DELETE

가 있다.

 

---

INSERT

테이블에 새로운 행을 추가하는 구문

[표현식] 1)INSERT INTO 테이블명 VALUES (값,값,값...)
테이블에 모든 컬럼에 대한 값을 직접 제시해서 한 행을 INSERT하고자 할 때
컬럼의 순번을 지켜서 VALUES에 값을 나열해야함

 

INSERT로 데이터를 추가하는 방법은 총 3가지가 있다.

 

1. 모든 컬럼의 값을 다 써주기

INSERT INTO EMPLOYEE 
 VALUES(900, '이소근','880914-1456789','SG8809@naver.com','01075966690',
        'D7','J5',4000000,0.2,200, SYSDATE, NULL, DEFAULT);

이렇게 우리가 저번에 배웠던 대로 모든 컬럼의 값을 다 써주는 방법이 있다.

 

 

 

   2. 내가 선택한 컬럼에만 INSERT하기

2) INSERT INTO 테이블명 (컬럼, 컬럼, 컬럼) VALUES (값,값,값)
테이블에 내가 선택한 컬럼에 대한 값만 INSERT할 때 사용
그래도 한 행 단위로 추가되기 때문에 선택안된 컬럼은 기본적으로 NULL이 들어감
=> NOT NULL제약 조건이 걸려있는 컬럼은 반드시 직접 값을 넣어줘야함 단, 기본 값 있는 애들은 안넣어도 됨.

 INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
 VALUES (901,'최르르', '383920-4949902', 'J7', SYSDATE);

 

   3. 값을 넣어주는 것 대신에 서브쿼리로 값을 주기

 

3)INSERT INTO 테이블명 (서브쿼리) VALUES로 값을 직접 명시하는 것 대신
서브쿼리로 조회된 결과 값을 통채로 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 DEPARTMENT ON (DEPT_CODE = DEPT_ID));

이런식으로 값이 들어갈 자리를 서브쿼리로 대신한다.

 

---

INSERT ALL

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

[표현식] INSERT ALL
INTO 테이블명1 VALUES(컬럼,컬럼,컬럼...)
INTO 테이블명2 VALUES(컬럼,컬럼,컬럼...)
서브쿼리 :

--테이트 테이블 (COPY한 테이블이고 말도안되는 조건을 넣어서 구조만 복사해옴)
CREATE TABLE EMP_DEPT
AS (SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
    FROM EMPLOYEE
    WHERE 1 = 0);

이렇게 테이블의 구조만 필요한 경우에 말도안되는 조건을 WHERE문으로 걸어서 값만 빼고 가져올 수 있다.

 

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');

위 처럼 조건을 걸어서 원하는 값을 서브쿼리로 가져온 후 두 개의 컬럼에 값을 넣어줄 수 있다.

 

---

UPDATE

 테이블에 기록되어있는 기존의 테이터를 수정하는 구문

[표현식] UPDATE 테이블명
SET 컬럼 = 값,
컬럼 = 값,
컬럼 = 값,
... -- AND로 연결하는 것이 아니라 ,로 연결한다
[WHERE 조건] --> 생략시 전체 모든 행의 데이터가 변경된다

*업데이트시에도 제약조건 잘 확인해야한다. ex) NOT NULL인데 NULL 불가

UPDATE DEPT_TABLE
SET DEPT_TITLE = '전략기획팀'
WHERE DEPT_ID = 'D9';

 

DEPT_ID = D9의 부서명을 전략기획팀으로 수정

 

 

UPDATE 시 서브쿼리를 사용 할 수 있다.

UPDATE 테이블명 SET 컬럼명 = (서브쿼리)
WHERE 조건

 

--방명수 사원의 급여와 보너스 값을 유재식 사원의 급여와 보너스 값으로 변경
UPDATE EMP_SALARY
SET SALARY = (SELECT SALARY
              FROM EMP_SALARY
              WHERE EMP_NAME = '유재식'),
    BONUS = (SELECT BONUS
              FROM EMP_SALARY
              WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';

SELECT * FROM EMP_SALARY
WHERE EMP_NAME = '방명수' OR EMP_NAME = '유재식';

이렇게 두 가지를 한꺼번에 할 수도 있지만 위 쿼리의 경우 어디서 본 것 같은 기분이 든다.

바로 다중열이다.

그럼 다중열 쿼리로 바꿔보자

--다중열로 변경
UPDATE EMP_SALARY
SET (SALARY, BONUS) = (SELECT SALARY, BONUS
                       FROM EMP_SALARY
                       WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';

쿼리가 훨씬 짧아지고 간결해졌다!

 

--ASIA지역에서 근무하는 사원들의 보너스 값을 0.3으로 변경
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%');

서브쿼리를 사용하여 이렇게 원하는 값을 잘 뽑아올 수 있다.

서브쿼리의 활용도가 무궁무진하여 헷갈릴 수 있을 것 같다..

---

DELETE

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

[표현식] DELETE FROM 테이블명
[WHERE 조건] --> WHERE 안쓰면 모든 행 삭제됨 (큰일남)

 

솔직히 DELETE는 잘 쓰지 않는다고 한다.

그래서 기초적인 것만 빠르게 알아보자.

 

DELETE FROM EMPLOYEE;

SELECT * FROM EMPLOYEE;
ROLLBACK;

DELETE FROM EMPLOYEE
WHERE EMP_NAME = '이소근';

DELETE FROM EMPLOYEE
WHERE EMP_ID = '901';

이렇게 원하는 것을 WHERE 로 특정할 수 있다.

WHERE  은 거의 필수로 기입해줘야하고

 

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';

위 처럼 D1의 값을 가져다쓰는 자식 데이터가 존재하면 삭제가 불가능하다

-> 기본적으로 제약조건이 걸려있다는 뜻

 

여기까지가 DML이다 우리가 공부할 때 중간중간 사용해서 금방 끝난 느낌이다.

필수로 배워야하는 것만 배워서 그런건가..?

 

---

DDL(Data Definition Language)

데이터 정의 언어(명령어)

DDL의 명령어는 크게 3가지가 있다.

 

• CREATE(생성)
• ALTER(변경)
• DELETE(삭제)

CREATE는 저번 글에서 다뤄보았기 때문에 이번에는 ALTER부터 시작해보겠다 !

 

ALTER

객체를 변경하는 구문

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

*변경할 내용
1) 컬럼 추가 / 수정 / 삭제
2) 제약조건 추가 / 삭제 --> 수정은 불가(삭제 후 다시 추가)
3) 컬럼명/제약조건명/테이블명 변경

 

 

ADD ( 값 추가하기 )

 --1) 컬럼 추가/수정/삭제
 --DEPT_TABLE에 CNAME컬럼 추가
 ALTER TABLE DEPT_TABLE ADD CNAME VARCHAR2(20);
 
  --LNAME 컬럼 추가 (기본값 -> 한국)
 ALTER TABLE DEPT_TABLE ADD LNAME VARCHAR2(20) DEFAULT '한국';

 

 

MODIFY ( 기본 값 (DAFAULT 값) 변경하기)

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

 ALTER TABLE DEPT_TABLE MODIFY DEPT_ID CHAR(5);
 ALTER TABLE DEPT_TABLE MODIFY DEPT_ID NUMBER; --여기서 오류!
 ALTER TABLE DEPT_TABLE MODIFY DEPT_TITLE VARCHAR2(10); -->크기가 작아져서 크기오류 발생

두 번째 줄에 오류가 나는 이유는 DEPT_ID는 현재 VARCHAR2 타입인데 NUMBER로 형변환 할 시 안에 있는 데이터들이 숫자만 있는게 아니기 때문에 형변환 오류가나며 실패하게 된다.

 

마지막 줄에 오류나는 이유도 비슷한 이유인데 현재 길이가 20에서 10으로 줄어들 때 안에 있는 데이터의 길이가 10을 초과하기 때문에 오류가 발생한다.

 

한 번에 변경하기

ALTER TABLE DEPT_TABLE
    MODIFY DEPT_TITLE VARCHAR2(40)
    MODIFY LNAME DEFAULT '미국';

두 가지를 한 번에 변경할 수 있다!

 

DROP( 컬럼 삭제)

컬럼 삭제(DROP COLUMN) : DROP COLUMN 삭제하고자 하는 컬럼

 

ALTER TABLE DEPT_COPY DROP COLUMN DEPT_ID;
ALTER TABLE DEPT_COPY DROP COLUMN CNAME;
ALTER TABLE DEPT_COPY DROP COLUMN LNAME;
ALTER TABLE DEPT_COPY DROP COLUMN DEPT_TITLE;
ALTER TABLE DEPT_COPY DROP COLUMN DEPT_COPY; --여기서 오류!

마지막 줄에 오류가 나게되는데 오류가 나는 이유는 테이블에는 최소 한 개 이상의 값이 존재해야 하기  때문에

마지막 남은 컬럼은 삭제가 불가능하다!

 

---

제약조건 추가

제약조건을 추가할 때 사용하던 것 그대로 사용하면 된다. (수정 = 삭제하고 다시 추가)

-PRIMARY KEY : ALTER TABLE 테이블명 ADD PRIMARY KEY(컬럼명);
-FOREIGN KEY : ALTER TABLE 테이블명 ADD FOREIGN KEY(컬럼명) REFERENCES 참조할 테이블명[(참조할 컬럼)];
-UNIQUE : ALTER TABLE 테이블명 ADD UNIQUE(컬럼명);
-CHECK : ALTER TABLE 테이블명 ADD CHECK(컬럼에 대한 조건식);
-NOT NULL : ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

제약조건명 지정하고자 한다면 [CONSTRAINT 제약조건명] 제약조건

--DEPT_ID에 PRIMARY KEY 제약조건 추가
ALTER TABLE DEPT_TABLE ADD  PRIMARY KEY(DEPT_ID);

--DEPT_TITLE에 UNIQUE 제약조건  추가
ALTER TABLE DEPT_TABLE ADD UNIQUE(DEPT_TITLE);

--LNAME에 NOT NULL제약조건 추가
ALTER TABLE DEPT_TABLE MODIFY LNAME NOT NULL;

 

제약조건 삭제

ALTER TABLE DEPT_TABLE DROP PRIMARY KEY;
ALTER TABLE DEPT_TABLE DROP UNIQUE(DEPT_TITLE);
ALTER TABLE DEPT_TALBE MODIFY LNAME NULL;

ADD가 아닌 DROP으로 바꿔주면 된다.

NOT NULL은 ADD 가 아닌 MODIFY를 그대로 사용하고 NOT NULL에서 NOT을 뺀 NULL만 기입해주면 된다.

 

참조되고 있는 부모테이블 삭제하는 법

DROP TABLE DEPT_TABLE;
어딘가에 참조되고있는 부모테이블은 함부로 삭제가 되지 않는다.
만약 지우고자 한다면
-- 1. 자식테이블 먼저 삭제 후 삭제
-- 2. 그냥 부모테이블만 삭제하는데 제약조건까지 삭제하는 방법

-- DROP TABLE 테이블명 CASCADE CONSTRAINT;

 

 

RENAME

컬럼명/제약조건명/테이블명 변경(RENAME)
컬럼명 변경 : RENAME COLUMN 기존컬럼명 TO 바꿀 컬럼명
DEPT_TITLE -> DEPT_NAME변경

 

RENAME 사용

--DEPT_TITLE -> DEPT_NAME변경
ALTER TABLE DEPT_TABLE RENAME COLUMN DEPT_TITLE TO DEPT_NAME;

 

 

제약조건명 사용

--3_2) 제약조건명 변경 : RENAME CONSTRAINT 기존제약조건명 TO 바꿀제약조건명
ALTER TABLE DEPT_TABLE RENAME CONSTRAINT SYS_C007119 TO DTABLE_LID_NN;

 

 

테이블명 변경

--3_3) 테이블명 변경 : RENAME TO 바꿀테이블명
ALTER TABLE DEPT_TABLE RENAME TO DEPT_TEST;

 

 

TRUNCATE  (테이블 초기화)

TRUNCATE TABLE DEPT_TEST;

 

 

이렇게 간단한 SQL 명령어 정리가 끝났다.

복습 잊지말고 자바도 복습하러가자!