웹개발 수업/SQL

[Day +41]DDL(CREATE), DML(INSERT, UPDATE, DELETE), DDL(ALTER, DROP)

Chole Woo 2021. 8. 20. 00:09
210819 목

 

DDL

1. FOREIGN KEY 삭제 옵션

-부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 어떻게 처리할 것인지 미리 설정할 수 있음

 

1) GRADE_CODE가 10인 행 삭제

DELETE FROM USER_GRADE WHERE GRADE_CODE = 10;
--integrity constraint (KH.FK_GRADE_CODE) violated - child record found
--오류 발생 / 자식 테이블의 행들이 찾아졌다는 뜻
--삭제 룰을 별도로 기재하지 않고 
--FK 제약 조건을 설정하면 ON DELETE RESTTICTED(삭제 제한)로
--기본 기정되어 있어 FK로 지정된 컬럼에서 사용되고 있는 값일 경우 해당 값 삭제 불가

2) 현재 시점 저장 

COMMIT;

3) GRADE_CODE가 20인 행 삭제 

DELETE FROM USER_GRADE WHERE GRADE_CODE = 20;
--GRADE_CODE 중 20은 외래키로

4) 시점 되돌리기

ROLLBACK;

5) 조회

SELECT * FROM USER_GRADE;

ROLLBACK 전

 

ROLLBACK 후

 

 

2. ON DELETE SET NULL : 부모키 삭제시 자식키를 NULL로 변경하는 옵션

1) USER_GRADE2 테이블 만들어서 조회

1-1) CREATE TABLE

CREATE TABLE USER_GRADE2(
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE2 VALUES(10, '일반회원');
INSERT INTO USER_GRADE2 VALUES(20, '우수회원');
INSERT INTO USER_GRADE2 VALUES(30, '특별회원');

1-2) 조회

SELECT * FROM USER_GRADE2;

 

2) 회원 관리 테이블 만들어서 조회

2-1) 테이블 생성

CREATE TABLE USER_FOREIGNKEY2(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT FK_GRADE_CODE2 FOREIGN KEY(GRADE_CODE)
REFERENCES USER_GRADE2(GRADE_CODE) ON DELETE SET NULL
);
INSERT INTO USER_FOREIGNKEY2
VALUES (1, 'user01', 'pass01', '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr', 10);

INSERT INTO USER_FOREIGNKEY2
VALUES (2, 'user02', 'pass02', '금성무', '남', '010-2234-3678'
, 'hong123@kh.or.kr', 10);

INSERT INTO USER_FOREIGNKEY2
VALUES (3, 'user03', 'pass03', '닝닝', '여', '010-1114-3378'
, 'hong123@kh.or.kr', 30);

INSERT INTO USER_FOREIGNKEY2
VALUES (4, 'user04', 'pass04', '전지현', '여', '010-1224-3338'
, 'hong123@kh.or.kr', NULL);
--GRADE에 NULL값도 가능

2-2) 조회

SELECT * FROM USER_FOREIGNKEY2;

 

3) USER_GRADE_2 행 삭제 

3-1) GRADE_CODE 10인 행 삭제 => 삭제 가능함
-삭제 후 USER_FOREIGNKEY2 테이블의 GRADE_CODE 컬럼 값이 10인 경우 NULL로 변경됨

DELETE FROM USER_GRADE2 WHERE GRADE_CODE = 10;

3-2) 

SELECT*FROM USER_GRADE2;



3. ON DELETE CASCADE : 부모키 삭제시 자식키도 함께 삭제
1) USER_GRADE3 테이블 만들기

1-1) 테이블 만들기

부모키 삭제 시 값을 사용하는 자식 테이블의 컬럼에 해당하는 행이 삭제됨 

CREATE TABLE USER_GRADE3(
GRADE_CODE NUMBER PRIMARY KEY,
GRADE_NAME VARCHAR2(30) NOT NULL
);

INSERT INTO USER_GRADE3 VALUES(10, '일반회원');
INSERT INTO USER_GRADE3 VALUES(20, '우수회원');
INSERT INTO USER_GRADE3 VALUES(30, '특별회원');

1-2) 테이블 조회

SELECT * FROM USER_GRADE3;


2) 회원관리 테이블 만들기

2-1) 테이블 생성

CREATE TABLE USER_FOREIGNKEY3(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
GRADE_CODE NUMBER,
CONSTRAINT FK_GRADE_CODE3 FOREIGN KEY(GRADE_CODE)
REFERENCES USER_GRADE3(GRADE_CODE) ON DELETE CASCADE
--이부분이 바뀐 것!
);
INSERT INTO USER_FOREIGNKEY3
VALUES (1, 'user01', 'pass01', '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr', 10);

INSERT INTO USER_FOREIGNKEY3
VALUES (2, 'user02', 'pass02', '금성무', '남', '010-2234-3678'
, 'hong123@kh.or.kr', 10);

INSERT INTO USER_FOREIGNKEY3
VALUES (3, 'user03', 'pass03', '닝닝', '여', '010-1114-3378'
, 'hong123@kh.or.kr', 30);

INSERT INTO USER_FOREIGNKEY3
VALUES (4, 'user04', 'pass04', '전지현', '여', '010-1224-3338'
, 'hong123@kh.or.kr', NULL);
--GRADE에 NULL값도 가능

2-2) 테이블 조회

SELECT * FROM USER_FOREIGNKEY3;


3) USER_GRADE3 행 삭제

3-1) GRADE_CODE 10인 행 삭제 => 삭제 가능함
삭제 후 USER_FOREIGNKEY2 테이블의 GRADE_CODE 컬럼 값이 10인 경우 NULL로 변경됨 

DELETE FROM USER_GRADE3 WHERE GRADE_CODE = 10;

3-2) 조회

SELECT*FROM USER_GRADE3;


4. CHECK
: 컬럼에 기록되는 값에 조건 설정을 할 수 있음
형식) CHECK(컬럼명 비교 연산자 비교값)
-비교 값은 리터럴만 사용 가능하며 변하는 값이나 함수 값은 사용할 수 없음

1) 

CREATE TABLE USER_CHECK(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(20) NOT NULL,
USER_NAME VARCHAR2(30) NOT NULL,
GENDER VARCHAR2(10) CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);

INSERT INTO USER_CHECK
VALUES (1, 'user01', 'pass01', '부승관', '남', '010-1234-5678'
, 'hong123@kh.or.kr');

INSERT INTO USER_CHECK
VALUES (2, 'user02', 'pass02', '부승관', '남자', '010-1234-5678'
, 'hong123@kh.or.kr');
--check constraint (KH.SYS_C007171) violated 오류 발생
--'남'이 아니라 '남자'라고 입력하면 CHECK 옵션 위반 오류

INSERT INTO USER_CHECK
VALUES (3, 'user03', 'pass01', '전지현', '여', '010-1234-5678'
, 'hong123@kh.or.kr');

INSERT INTO USER_CHECK
VALUES (3, 'user03', 'pass01', '전지현', '여자', '010-1234-5678'
, 'hong123@kh.or.kr');
--check constraint (KH.SYS_C007171) violated 오류 발생
--'여'가 아니라 '여자'라고 입력하면 CHECK 옵션 위반 오류

2) 

CREATE TABLE TEST_CHECK(
TEST_NUMBER NUMBER,
CONSTRAINT CK_TEST_NUMBER CHECK(TEST_NUMBER > 0)
);

INSERT INTO TEST_CHECK VALUES (10);
INSERT INTO TEST_CHECK VALUES (-10);
--check constraint (KH.CK_TEST_NUMBER) violated 오류 발생
--0이상의 값만 입력 받는 CHECK 조건 위반

3) 

CREATE TABLE TBLCHECK(
C_NAME VARCHAR2(15),
C_PRICE NUMBER,
C_LEVEL CHAR(1),
C_DATE DATE,
CONSTRAINT TBCH_NAME_PK PRIMARY KEY(C_NAME),
CONSTRAINT TBCH_PRICE CHECK(C_PRICE >= 1 AND C_PRICE <= 99999),
CONSTRAINT TBCH_LEVEL CHECK(C_LEVEL IN ('A', 'B', 'C')),
CONSTRAINT TBCH_DATE CHECK(C_DATE >= TO_DATE('2016/01/01', 'YYYY/MM/DD'))
);

--TBCH_PRICE
INSERT INTO TBLCHECK VALUES ('부승관', 0, 'A', '2017/01/01');
--check constraint (KH.TBCH_PRICE) violated 오류 발생
--0은 삽입 불가

--TBCH_LEVEL
INSERT INTO TBLCHECK VALUES ('부승관', 1, 'D', '2017/01/01');
--check constraint (KH.TBCH_LEVEL) violated 오류 발생
--LEVEL 때문

--TBCH_DATE
INSERT INTO TBLCHECK VALUES ('부승관', 1, 'D', '2015/01/01');
--check constraint (KH.TBCH_DATE) violated 오류 발생
--날짜 때문

 

 

5. SUBSQUERY를 이용한 테이블 생성
1) 컬럼명, 데이터 타입, 값이 복사되고 제약조건을 NOT NULL만 복사됨 

CREATE TABLE EMPLOYEE_COPY
AS SELECT * FROM EMPLOYEE;
SELECT * FROM EMPLOYEE_COPY;

CREATE TABLE EMPLOYEE_COPY2
AS SELECT EMP_ID, EMP_NAME, SALARY, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
LEFT JOIN JOB USING(JOB_CODE);
SELECT * FROM EMPLOYEE_COPY2;



*실습 문제

회원가입용 테이블 생성(USER_TEST)
-컬럼명 : USER_NO(회원번호) - 기본키(PK_USER_NO2), 
-       USER_ID(회원아이디) - 중복금지(UK_USER_ID),
-       USER_PWD(회원비밀번호) - NULL값 허용안함(NN_USER_PWD),
-       PNO(주민등록번호) - 중복금지(UK_PNO), NULL 허용안함(NN_PNO),
-       GENDER(성별) - '남' 혹은 '여'로 입력(CK_GENDER),
-       PHONE(연락처),
--      ADDRESS(주소),
--      STATUS(탈퇴여부) - 'Y' 혹은 'N'으로 입력(CK_STATUS)
--  탈퇴 여부는 입력 값이 없으면 'N'으로 입력
-- 각 컬럼의 제약조건에 이름 부여할 것
-- 각 컬럼에 코멘트 추가할 것
-- 5명 이상 INSERT할 것
-- 테이블 전체 조회, 코멘트 조회, 제약조건 조회 할 것

1) 테이블 만들기

CREATE TABLE USER_TEST(
USER_NO NUMBER CONSTRAINT PK_USER_NO2 PRIMARY KEY,
USER_ID VARCHAR2(20) CONSTRAINT UK_USER_ID UNIQUE,
USER_PWD VARCHAR2(20) CONSTRAINT NN_USER_PWD NOT NULL,
PNO VARCHAR2(20) CONSTRAINT UK_PNO UNIQUE CONSTRAINT NN_PNO NOT NULL,
GENDER VARCHAR2(3) CONSTRAINT CK_GENDER CHECK(GENDER IN ('남', '여')),
PHONE VARCHAR2(30),
ADDRESS VARCHAR2(100),
STATUS VARCHAR2(3) DEFAULT 'N' CONSTRAINT CK_STATUS CHECK(STATUS IN ('Y', 'N'))
);


COMMENT ON COLUMN USER_TEST.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_TEST.USER_ID IS '회원번호';
COMMENT ON COLUMN USER_TEST.USER_PWD IS '비밀번호';
COMMENT ON COLUMN USER_TEST.PNO IS '주민등록번호';
COMMENT ON COLUMN USER_TEST.GENDER IS '성별';
COMMENT ON COLUMN USER_TEST.PHONE IS '연락처';
COMMENT ON COLUMN USER_TEST.ADDRESS IS '주소';
COMMENT ON COLUMN USER_TEST.STATUS IS '탈퇴여부';

INSERT INTO USER_TEST VALUES (1, 'aa', 'aa', '930327-1222211', '여', 
'010-2221-1234', '경기도 광주시', 'Y');

INSERT INTO USER_TEST VALUES (2, 'bb', 'bb', '930327-1134111', '남', 
'010-2331-1234', '경기도 성남시', 'Y');

INSERT INTO USER_TEST VALUES (3, 'cc', 'cc', '930327-1155511', '여', 
'010-2551-1234', '경기도 고양시', 'Y');

INSERT INTO USER_TEST VALUES (4, 'dd', 'dd', '930327-2221111', '남', 
'010-2661-1234', '경기도 이천시', 'Y');

INSERT INTO USER_TEST VALUES (5, 'ee', 'ee', '930327-1331111', '여', 
'010-2771-1234', '경기도 수원시', 'Y');

2) 테이블 확인 

SELECT * FROM USER_TEST;


3) 테이블 주석 확인 

SELECT * FROM USER_COL_COMMENTS
WHERE TABLE_NAME = 'USER_TEST';


4) 테이블 제약 조건 확인 

SELECT * 
FROM USER_CONSTRAINTS C1
JOIN USER_CONS_COLUMNS C2 USING(CONSTRAINT_NAME)
WHERE C1.TABLE_NAME = 'USER_TEST';

DML(INSERT, UPDATE, DELETE) (SELECT가 포함되기도 하고 안되기도 함)

1) DML(INSERT, UPDATE, DELETE) (SELECT가 포함되기도 하고 안되기도 함)

2) DDL : 데이터 정의어, 데이터 베이스의 구조를 생성, 변경, 삭제
              CREATE, ALTER, DROP
--> 하나의 DDL 구문이 하나의 트랜잭션(AUTO COMMIT)

3) DML : 데이터 조작어, 데이터 베이스의 데이터를 조회, 삽입, 변경, 삭제
               (SELECT), INSERT, UPDATE, DELETE
--> *여러 DML 구문이 하나의 트랜잭션 *

4) DCL : 데이터 제어어, 권한을 주거나 회수할 수 있으며 트랜잭션 관리를 함
              GRANT, REVOKE, COMMIT, ROLLBACK
--> 하나의 DCL 구문이 하나의 트랜잭션(AUTO COMMIT)

트랜잭션의 시작은 첫 DML 구문 실행시

-COMMIT 또는 ROLLBACK 실행시 반영
-DDL 또는 DCL 구문 실행시, 정상 종료 시 AUTO COMMIT
-시스템 장애시 AUTO ROLLBACK

 

1. INSERT : 새로운 행을 추가하는 구문. 테이블의 행 개수 증가.

형식) INSERT INTO 테이블명 (컬럼명1, 컬럼명2, 컬럼명3, ...) VALUES (데이터1, 데이터2, 데이터3, ...);
-테이블에 내가 선택한 컬럼에 대한 값만 INSERT 할 때 사용
-선택안된 컬럼은 값이 NULL 처리

1) 테이블에 사람 추가하기

1-1) 장채현 정보 추가하기

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, 
DEPT_CODE, JOB_CODE, SAL_LEVEL, SALARY, BONUS, MANAGER_ID, HIRE_DATE,
ENT_DATE, ENT_YN)
VALUES (900, '장채현', '901123-2080503', 
'jang_ch@kh.ot.kr', '01055559512', 'D1', 'J7', 'S3', 4300000, 0.2, '200',
SYSDATE, NULL, DEFAULT);
SELECT * FROM EMPLOYEE;

1-2) 뒤로 돌아가기

ROLLBACK;

 

2) INSERT INTO 

INSERT INTO  테이블명 (데이터1, 데이터2, 데이터3, ...);
-테이블의 모든 컬럼에 대한 값을 INSERT할 때 사용

-단, 컬럼의 순서를 지켜서 VALUES에 값을 기입해야 함

INSERT INTO EMPLOYEE
VALUES (900, '장채현', '901123-2080503', 
'jang_ch@kh.ot.kr', '01055559512', 'D1', 'J7', 'S3', 4300000, 0.2, '200',
SYSDATE, NULL, DEFAULT);
COMMIT;
SELECT * FROM EMPLOYEE;


3) INSERT시 VALUES 대신 서브쿼리 사용 가능

CREATE TABLE EMP_01(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
DEPT_TITLE VARCHAR2(20)
);
SELECT * FROM EMP_01;
INSERT INTO EMP_01(
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
LETT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
);

ROLLBACK;




2. INSERT

1) INSERT시 서브쿼리가 사용하는 테이블이 같은 경우
-두 개 이상의 테이블에 INSERT ALL을 이용하여 한 번에 삽입 가능
-단, 각 서브쿼리의 조건절이 같아야 함

CREATE TABLE EMP_DEPT_D1
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE 1 = 0;
--1 = 0 이라는 조건은 모든 행에 대해서 FALSE이므로
--값은 삽입 되지 않고 테이블 컬럼만 생성됨
SELECT * FROM EMP_DEPT_D1;
--행은 아무것도 없고 테이블 컬럼만 생성됨

2) 

CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE
WHERE 1 = 0;
SELECT * FROM EMP_MANAGER;
--행은 아무것도 없고 테이블 컬럼만 생성됨

3) EMP_DEPT_D1 테이블에 "부서코드가 D1인 직원"을 조회해서 사번, 이름, 소속부서, 입사일을 삽입하고 EMP_MANAGER 테이블에 "부서코드가 D1인 직원"을 조회해서 사번, 이름, 관리자 사번을 삽입

INSERT ALL
INTO EMP_DEPT_D1 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';
SELECT * FROM EMP_MANAGER;

4) 입사일 기준으로 2000년 1월 1일 이전에 입사한 사원의 사번, 이름, 입사일, 급여를 조회해서 EMP_OLD 테이블로 삽입. 그 이후에 입사한 사원의 정보는 EMP_NEW 테이블로 삽입

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

CREATE TABLE EMP_NEW
AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE 1 = 0;
SELECT * FROM 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, SALARY)
    SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
    FROM EMPLOYEE;
    SELECT * FROM EMP_OLD;
    SELECT * FROM EMP_NEW;

OLD / NEW

  
3. UPDATE
-테이블에 기록된 컬럼의 값을 수정하는 구문
-테이블 전체 행 개수에는 변화가 없음

1) UPDATE 테이블명 SET 컬럼명 = 바꿀값, 컬럼명 = 바꿀값, ... [WHERE 컬럼명 비교연산자 비교값];

CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT_COPY;


2) DEPT_ID가 'D9'인 행의 DEPT_TITLE을 '전략기획팀'으로 수정

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

 

Q. 조건절을 설정하지 않고 UPDATE 구문을 실행하면?
 -해당 테이블의 모든 행의 컬럼 값이 변경됨

UPDATE DEPT_COPY
    SET DEPT_TITLE = '전략기획팀';
ROLLBACK;

 

3) UPDATE 시에도 서브쿼리 사용 가능
UPDATE 테이블명 SET 컬럼명 = (서브쿼리)
3-1) 평상시 유재식 사원을 부러워하던 방명수 사원의 급여와 보너스율을 유재식 사원과 동일하게 변경해주기로 함

CREATE TABLE EMP_SALARY 
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY, BONUS
FROM EMPLOYEE;
SELECT * FROM EMP_SALARY;
UPDATE EMP_SALARY
SET (SALARY, BONUS) = 
                    (SELECT SALARY, BONUS FROM EMP_SALARY
                     WHERE EMP_NAME = '유재식')
WHERE EMP_NAME = '방명수';



4) EMP_SALARY 테이블을 대상으로 ASIA 지역에서 근무하는 직원들의 보너스를 0.3으로 변경

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


5) UPDATE시 변경할 값은 해당 컬럼에 대한 제약 조건에 위배되지 않아야 함
5-1) FK제약조건이 걸려 있지 않으므로 FK제약조건 추가 후 테스트

ALTER TABLE EMPLOYEE
ADD FOREIGN KEY (DEPT_CODE) REFERENCES DEPARTMENT (DEPT_ID);
UPDATE EMPLOYEE
SET DEPT_CODE = '65'
WHERE DEPT_CODE = 'D6';
--integrity constraint (KH.SYS_C007205) violated - parent key not found
--오류 발생
--FK 제약 조건 위배로 수정 불가
UPDATE EMPLOYEE
SET EMP_NAME = NULL
WHERE EMP_ID = 200;
--cannot update ("KH"."EMPLOYEE"."EMP_NAME") to NULL
--NOT NULL 제약 조건 위배로 수정 불가


5-2) EMP_NO에 UNIQUE 제약 조건 추가

ALTER TABLE EMPLOYEE
ADD UNIQUE(EMP_NO);
SELECT * FROM EMP_SALARY;
UPDATE EMPLOYEE
SET EMP_NO = '621231-1985634'
WHERE EMP_ID = 201;
--unique constraint (KH.SYS_C007206) violated 오류 발생

 

4. MERGE(병합)
-구조가 같은 테이블을 하나로 합치는 기능
-테이블에서 지정하는 조건의 값이 존재하면 UPDATE
-조건의 값이 없으면 INSERT됨

CREATE TABLE EMP_M01
AS SELECT * FROM EMPLOYEE;
CREATE TABLE EMP_M02
AS SELECT * FROM EMPLOYEE WHERE JOB_CODE = 'J4';
INSERT INTO EMP_M02
VALUES (999, '곽두원', '561016-1234567', 'kwack_dw@kh.or.kh', '01011112222',
'D9', 'J4', 'S1', 9000000, 0.5, NULL, SYSDATE, NULL, DEFAULT);

 

UPDATE EMP_M02
SET SALARY = 0;
SELECT * FROM EMP_M01; --EMPLOYEE 테이블과 동일
SELECT * FROM EMP_M02; --J4 사원 4명 + 곽두원 1명 + SALARY 0으로 변경

--테이블명 VER
--MERGE INTO EMP_M01 USING EMP_M02 ON(EMP_M01.EMP_ID = EMP_M02.EMP_ID)
--WHEN MATCHED THEN
--UPDATE SET
--EMP_M01.EMP_NAME = EMP_M02.EMP_NAME,
--EMP_M01.EMP_NO = EMP_M02.EMP_NO,
--EMP_M01.EMAIL = EMP_M02.EMAIL,
--EMP_M01.PHONE = EMP_M02.PHONE,
--EMP_M01.DEPT_CODE = EMP_M02.DEPT_CODE,
--EMP_M01.JOB_CODE = EMP_M02.JOB_CODE,
--EMP_M01.SAL_LEVEL = EMP_M02.SAL_LEVEL,
--EMP_M01.SALARY = EMP_M02.SALARY,
--EMP_M01.BONUS = EMP_M02.BONUS,
--EMP_M01.MANAGER_ID = EMP_M02.MANAGER_ID,
--EMP_M01.HIRE_DATE = EMP_M02.HIRE_DATE,
--EMP_M01.ENT_DATE = EMP_M02.ENT_DATE,
--EMP_M01.ENT_YN = EMP_M02.ENT_YN
--WHEN NOT MATCHED THEN
--INSERT VALUES(EMP_M02.EMP_ID, EMP_M02.EMP_NAME, EMP_M02.EMP_NO,
--              EMP_M02.EMAIL, EMP_M02.PHONE, EMP_M02.DEPT_CODE,
--              EMP_M02.JOB_CODE, EMP_M02.SAL_LEVEL, EMP_M02.SALARY,
--              EMP_M02.BONUS, EMP_M02.MANAGER_ID, EMP_M02.HIRE_DATE,
--              EMP_M02.ENT_DATE, EMP_M02.ENT_YN);

-- 별칭 VER

MERGE INTO EMP_M01 M1 USING EMP_M02 M2 ON(M1.EMP_ID = M2.EMP_ID)
WHEN MATCHED THEN
UPDATE SET
M1.EMP_NAME = M2.EMP_NAME,
M1.EMP_NO = M2.EMP_NO,
M1.EMAIL = M2.EMAIL,
M1.PHONE = M2.PHONE,
M1.DEPT_CODE = M2.DEPT_CODE,
M1.JOB_CODE = M2.JOB_CODE,
M1.SAL_LEVEL = M2.SAL_LEVEL,
M1.SALARY = M2.SALARY,
M1.BONUS = M2.BONUS,
M1.MANAGER_ID = M2.MANAGER_ID,
M1.HIRE_DATE = M2.HIRE_DATE,
M1.ENT_DATE = M2.ENT_DATE,
M1.ENT_YN = M2.ENT_YN
WHEN NOT MATCHED THEN
INSERT VALUES (M2.EMP_ID, M2.EMP_NAME, M2.EMP_NO,
               M2.EMAIL, M2.PHONE, M2.DEPT_CODE,
               M2.JOB_CODE, M2.SAL_LEVEL, M2.SALARY,
               M2.BONUS, M2.MANAGER_ID, M2.HIRE_DATE,
               M2.ENT_DATE, M2.ENT_YN);
SELECT * FROM EMP_M01; --EMPLOYEE 테이블과 동일




5. DELETE 
-테이블의 행을 삭제하는 구문
-테이블의 행의 개수가 줄어든다

 

형식) DELETE FROM 테이블명 WHERE 조건설정
-만약 WHERE 조건 설정이 없으면 모든 행이 삭제됨

COMMIT;
DELETE FROM EMPLOYEE
WHERE EMP_NAME = '장채현';
SELECT * FROM EMPLOYEE;

삭제됨

ROLLBACK;

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';
--FK제약 조건이 설정되어 있는 경우 참조되고 있는 값에 대해 삭제 불가
--integrity constraint (KH.SYS_C007205) violated - child record found
DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D3'
--FK제약조건이 설정되어 있더라도 참조하고 있는 값이 없으면 삭제 가능
 ROLLBACK;

 

1) 제약조건 비활성화
 EMPLOYEE 테이블의 FK 비활성화

 ALTER TABLE EMPLOYEE
 DISABLE CONSTRAINT SYS_C007205 CASCADE;
 --여기서는 CASCADE 안써줘도 됨

*CASCADE : 종속성을 의미
 

2) 제약 조건 비활성화로 D1 삭제 가능

 DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1'; --아까는 안됐는데 지금은 잘 삭제됨
ROLLBACK;

 

3) 비활성화 된 제약조건 다시 활성화

ALTER TABLE EMPLOYEE
ENABLE CONSTRAINT SYS_C007205;
DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';
--integrity constraint (KH.SYS_C007205) violated - child record found
--다시 오류 발생


4) TRUNCATE : 테이블의 전체 행을 삭제할 때 사용

-DELETE보다 수행 속도가 빠르나 ROLLBACK을 통해 복구 불가능(DDL)

TRUNCATE TABLE EMP_SALARY;
--Table EMP_SALARY이(가) 잘렸습니다. 라고 나옴
SELECT * FROM EMP_SALARY;
ROLLBACK;

DDL(ALTER, DROP)
: 객체(OBJECT)를 만들고(CREATE), 수정(ALTER)하고, 삭제(DROP)하는 구문

1. ALTER
ALTER TABLE 테이블명 수정할 내용;
-수정할 내용

-컬럼 추가/삭제, 제약조건 추가/삭제
-컬럼 자료형 변경, DEFAULT값 변경
-테이블명, 컬럼명, 제약조건명 변경

1) 컬럼 추가/수정/삭제 

SELECT * FROM DEPT_COPY;

2) 컬럼 추가(ADD) 

ALTER TABLE DEPT_COPY
ADD CNAME VARCHAR2(20);
--CNAME이라는 컬럼이 생김

3) 컬럼 추가 시 DEFAULT 값 설정 

ALTER TABLE DEPT_COPY
ADD LNAME VARCHAR2(40) DEFAULT '한국';
--LNAME이라는 컬럼이 생성됨

 

4) 컬럼 수정(MODIFY)
4-1) 컬럼 수정 전/후 조회문 

SELECT COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_DEFAULT, COLUMN_ID, COMMENTS
FROM USER_TAB_COLUMNS
JOIN USER_COL_COMMENTS USING(TABLE_NAME, COLUMN_NAME)
WHERE TABLE_NAME = 'DEPT_COPY';

ALTER TABLE DEPT_COPY
MODIFY DEPT_ID VARCHAR2(3)
MODIFY LOCATION_ID VARCHAR(2)
MODIFY LNAME DEFAULT '미국';

5) 컬럼의 크기를 줄일 경우 기록된 값이 변경하려는 크기를 초과하는 값이 없어야 함 

ALTER TABLE DEPT_COPY
MODIFY DEPT_TITLE VARCHAR2(10);
--"cannot decrease column length because some value is too big"
--컬럼의 길이를 줄일 수 없다. 왜냐하면 어떤 값이 너무 크기 때문이다

6) 컬럼 삭제(DROP) 

--데이터가 기록되어 있어도 삭제됨
--삭제된 컬럼은 복구 안됨
--테이블에는 최소 한개의 컬럼이 존재해야하므로 모든 컬럼을 삭제할 수는 없음
CREATE TABLE DEPT_COPY2
AS SELECT * FROM DEPT_COPY;
ALTER TABLE DEPT_COPY2
DROP COLUMN DEPT_ID
--DEPT_ID 삭제됨
--DROP COLUMN DEPT_ID;
--DROP COLUMN DEPT_TITLE;
--DROP COLUMN LOCATION_ID;
--DROP COLUMN CNAME;
DROP COLUMN LNAME;
SELECT * FROM DEPT_COPY2;




7) 제약 조건이 설정되어 있는 컬럼 삭제 

CREATE TABLE TB1(
PK NUMBER PRIMARY KEY,
FK NUMBER REFERENCES TB1,
COL1 NUMBER,
CHECK(PK > 0 AND COL1 > 0)
);


8) 컬럼 삭제 시 참조하고 있는 컬럼이 있다면 삭제 불가능 

ALTER TABLE TB1
DROP COLUMN PK;
--"cannot drop parent key column"

 

9) 제약 조건과 함께 삭제 

ALTER TABLE TB1
DROP COLUMN PK CASCADE CONSTRAINTS;
SELECT * FROM TB1;



2. 제약조건 추가/ 삭제
제약조건 추가(ADD [CONSTRAINT 제약조건명] 제약조건(컬럼명))
단, NOT NULL 제약조건 추가는 ADD가 아닌 MODIFY로 추가
(MODIFY 컬럼명 [CONSTRAINT 제약조건명] NOT NULL)

1) 제약조건 추가 전 DEPT_COPY 테이블에 있는 제약 조건 확인
DEPARTMENT의 NOT NULL 제약 조건만 복사 되어 있음(서브쿼리로 생성한 테이블) 

SELECT * 
FROM USER_CONSTRAINTS C1
JOIN USER_CONS_COLUMNS C2 USING(CONSTRAINT_NAME)
WHERE C1.TABLE_NAME = 'DEPT_COPY';

2) DEPT_ID에 PK 추가 

ALTER TABLE DEPT_COPY
ADD CONSTRAINT DCOPY_DID_PK PRIMARY KEY(DEPT_ID);

3) LNAME에 NOT NULL 추가 (MODIFY) 

ALTER TABLE DEPT_COPY
MODIFY LNAME CONSTRAINT DCOPY_LNAME_NN NOT NULL;

4) DEPT_ID 의 PK 제약조건 삭제 

ALTER TABLE DEPT_COPY
DROP CONSTRAINT DCOPY_DID_PK;

5) LNAME 다시 NULL로 변경(MODIFY) 

ALTER TABLE DEPT_COPY
MODIFY LNAME NULL;

6) DEPT_ID 의 PK 제약조건 삭제 

ALTER TABLE DEPT_COPY
DROP CONSTRAINT DCOPY_DID_PK;

7) LNAME 다시 NULL로 변경(MODIFY) 

ALTER TABLE DEPT_COPY
MODIFY LNAME NULL;
저작자표시 비영리 변경금지 (새창열림)