DATABASE DDL, DML, DCL

Let's Just Go·2022년 5월 4일

Database oracle

DATABASE

목록 보기

5/11

DATABASE

DML(Data Manipulation Language)

INSERT

INSERT

특정 값 or 값들을 삽입해주는 함수
INSERT INTO 테이블이름 (컬럼명...) VALUES (값.. );
모든 컬럼 데이터를 한번에 지정해서 삽입할 수 있음

컬럼을 개별적으로 선택해서 삽입할 수 있음

DESC departments

-- 모든 컬럼 데이터를 한번에 저장 
INSERT INTO departments VALUES (290, '개발자', null, 1700);

-- 직접 컬럼을 지정하고 저장 
INSERT INTO departments (department_id, department_name, loacation_id) VALUES (290, '개발자',1700);

CREATE TABLE managers AS (SELECT employee_id, first_name, job_id, hire_date FROM employees);

CREATE TABLE managers
    AS
        ( SELECT
            employee_id,
            first_name,
            job_id,
            hire_date
        FROM
            employees
        WHERE
            1 = 2
        );
--테이블 생성
-- where 1= 2 논리값으로 FALSE이므로 테이블의 구조만 가져오고 데이터는 가져오지 않음

SELECT
    *
FROM
    managers;
--테이블의 구조만 가져온 것을 확인할 수 있음
DROP TABLE managers;
--생성한 테이블 삭제 

INSERT INTO managers
    ( SELECT
        employee_id,
        first_name,
        job_id,
        hire_date
    FROM
        employees
    );
--서브쿼리를 통해서 데이터를 삽입할 수 있음

UPDATE

UPDATE

특정 값 or 값들을 변경해주는 함수
UPDATE 테이블이름 SET 변경할 값 WHERE 변경 조건;

어떤 데이터를 수정해야할지 선택

--UPDATE 
CREATE TABLE emps
    AS
        ( SELECT
            *
        FROM
            employees
        );

SELECT
    *
FROM
    emps;
--CREATE TABLE AS 를 사용하면 제약 조건은 not null말고는 복사되지 않음
--제약조건 : 업무규칙을 지키는 데이터만 저장하고 그렇지 않은 것들이 DB에 저장되는 것을 방지하는 목적 

--UPDATE를 진행할 때는 누구를 수정할 지 잘 지목해야함 
--그렇지 않으면 수정 대상이 테이블 전체로 지목됨
UPDATE emps
SET
    salary = 30000;
--이렇게 되면 salary가 모두 30000으로 변경됨
SELECT
    *
FROM
    emps;

ROLLBACK;
--했던 행동을 다시 복구 

UPDATE emps
SET
    salary = 30000
WHERE
    employee_id = 100;
--특정값만 update된 것을 확인

UPDATE emps
SET
    salary = salary + salary * 0.1
WHERE
    employee_id = 100;
--사원번호가 100인 사람의 급여를 주겠다는 뜻 

UPDATE emps
SET
    phone_number = '515.123.4566',
    manager_id = 102
WHERE
    employee_id = 100;
--여러개의 컬럼에 있는 값을 업데이트 가능 

--UPDATE(서브쿼리)
UPDATE emps
SET
    ( job_id,
      salary,
      manager_id ) = (
        SELECT
            job_id,
            salary,
            manager_id
        FROM
            emps
        WHERE
            employee_id = 100
    )
WHERE
    employee_id = 101;
--사원번호가 101인 사람의 job_id와 salary,manager_id를 사원번호가 100번인 사람의 값으로 변경하는 것 

ROLLBACK;

DELETE

특정 값 or 값들을 삭제하는 함수

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

--DELECTE
DELETE FROM emps
WHERE
    employee_id = 103;
-- 사원번호 103번이 삭제된 것을 확인할 수 있음 

--사본테이블 생성 
CREATE TABLE depts
    AS
        ( SELECT
            *
        FROM
            departments
        );
        

--DELETE(서브쿼리)
DELETE FROM emps
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            depts
        WHERE
            department_id = 100
    );
    
SELECT
    *
FROM
    emps;
--사라진 것을 확인할 수 있음 

DELETE FROM emps
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            depts
        WHERE
            department_name = 'IT'
    );
--IT부서의 사람들이 모두 삭제된 것을 확인

MERGE

MERGE

UPDATE와 INSERT를 한번에 처리하고 싶을때

/*
한 테이블에 해당하는 데이터가 있다면 UPDATE를 없으면 INSERT로 처리

한 테이블에 해당하는 데이터가 있다면 UPDATE를 없으면 INSERT를처리하라 
만약 MERGER가 없으면 해당 데이터의 존재 유무를 일일히 확인하고 IF문을 사용해서 데이터가 있다면 UPDATE
없다면 else문을 사용하여 INSERT를 하라고 하나씩 이야기해야하는데 MERGE를 통해 쉽게 처리가능 
*/
CREATE TABLE emps_it
    AS
        ( SELECT
            *
        FROM
            employees
        WHERE
            1 = 2
        );

SELECT
    *
FROM
    emps_it;
--테이블의 구조만 가져옴

INSERT INTO emps_it (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id
) VALUES (
    105,
    '데이비드',
    '베컴',
    'DAVID@nekr.com',
    '22-APRIL-27',
    'IT_PROG'
);

SELECT
    *
FROM
    employees
WHERE
    job_id = 'IT_PROG';

MERGE INTO emps_it a -- (머지를 할 타겟 테이블)
USING -- 병합시킬 데이터
 (
          SELECT
              *
          FROM
              employees
          WHERE
              job_id = 'IT_PROG'
      )
b -- 조인 구문
 ON -- 병합시킬 데이터의 연결 조건
 ( a.employee_id = b.employee_id ) -- 조인 조건
         --B에는 사원번호가 103~107까지 조회되고 A의 사원번호가 105번이므로 충돌이 발생할 수 있음 
WHEN MATCHED THEN -- 조건에 일치할 경우 타겟 테이블에 이렇게 실행해라.
 --수정이 목적일 때 사용
 UPDATE
SET a.phone_number = b.phone_number,
    a.hire_date = b.hire_date,
    a.salary = b.salary,
    a.commission_pct = b.commission_pct,
    a.manager_id = b.manager_id,
    a.department_id = b.department_id
    
    /*
    DELETE만 단독으로 사용될 수 없음 
    UPDATE 이후 DELETE 작성이 가능 
    UPDATE 된 대상을 DELETE 하도록 설계되어 있기 때문에 삭제할 대상 컬럼들을 동일한 값으로 UPDATE하고 
    DELETE의 WHERE 절에 아까 지정한 동일한 값을 지정하여 삭제 
    */ DELETE
WHERE
                     a.employee_id = b.employee_id
        -- employee_id가 같으면 모두 지워라 라는 뜻
        -- update를 먼저 작성하고 DELETE 가능하며 데이비드 베컴이 삭제됨

WHEN NOT MATCHED THEN -- 조건에 일치하지 않는 경우 타겟 테이블에 실행.
INSERT /*속성(컬럼)*/
VALUES
    ( b.employee_id,
      b.first_name,
      b.last_name,
      b.email,
      b.phone_number,
      b.hire_date,
      b.job_id,
      b.salary,
      b.commission_pct,
      b.manager_id,
      b.department_id );
--------------------------------------------------------------------------------

INSERT INTO emps_it (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id
) VALUES (
    102,
    '렉스',
    '박',
    'LEXPARK',
    '21-APRIL-23',
    'AD_VP'
);

INSERT INTO emps_it (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id
) VALUES (
    101,
    '니나',
    '최',
    'NINA',
    '20-MAY-22',
    'AD_VP'
);

INSERT INTO emps_it (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date,
    job_id
) VALUES (
    103,
    '흥민',
    '손',
    'HMSON',
    '22-APRIL-27',
    'AD_VP'
);

--employees 테이블을 매번 수정되는 테이블이라고 가정 
--기존 데이터는 email, phone, salary, comm_pct, man_id, dept_id는 업데이트 하도록 처리
--새로 유입된 데이터는 그대로 추가 

MERGE INTO emps_it a
USING (
          SELECT
              *
          FROM
              employees
      )
b ON ( a.employee_id = b.employee_id )
--데이터가 충돌이 일어나면
WHEN MATCHED THEN UPDATE
SET a.first_name = b.first_name,
    a.email = b.email,
    a.phone_number = b.phone_number,
    a.salary = b.salary,
    a.commission_pct = b.commission_pct,
    a.manager_id = b.manager_id,
    a.department_id = b.department_id
-- 데이터의 충돌이 일어나면 a의 데이터에 b의 데이터 값을 update

WHEN NOT MATCHED THEN
INSERT
VALUES
    ( b.employee_id,
      b.first_name,
      b.last_name,
      b.email,
      b.phone_number,
      b.hire_date,
      b.job_id,
      b.salary,
      b.commission_pct,
      b.manager_id,
      b.department_id );
-- 데이터의 충돌이 일어나지 않으면 해당 값을 insert

SELECT
    *
FROM
    emps_it
ORDER BY
    employee_id ASC;
    
ROLLBACK;

DCL(Data Control Language)

TRANSACTION

Transaction
- 데이터베이스의 상태를 변화시키는 하나의 논리적 기능을 수행하기 위한 작업 단위 또는 한꺼번에 수행해야 할 일련의 연산
- COMMIT과 ROLLBACK을 통해 연산 수행 가능
- COMMIT을 해야 보류 중인 데이터를 데이터 베이스에 반영
```
 --Check Auto Commit 
 SHOW AUTOCOMMIT;
 
 -- Auto Commit On 
 SET AUTOCOMMIT ON;
 --연산을 수행할 때 마다 자동으로 commit되도록 하는 명령어
 SET AUTOCOMMIT OFF;
```

ROLLBACK

ROLLBACK

보류 중인 모든 데이터 변경 사항을 취소(폐기)

직전 커밋 단계로 회귀 및 트랜잭션 종료하는 기능

SELECT
    *
FROM
    emps;

INSERT INTO emps (
    employee_id,
    last_name,
    email,
    hire_date,
    job_id
) VALUES (
    300,
    'moon',
    'abs23@nfek.com',
    sysdate,
    1800
);

--보류중인 모든 데이터 변경사항을 취소, 직전 커밋 단계로 회귀 및 트랜잭션 종료 
ROLLBACK;

SAVEPOINT

SAVEPOINT

특정 지점으로 ROLLBACK하기 위해서 사용하는 명령어
ROOLBACK할 포인트를 직접 이름을 붙여서 지정

ORACLE에서만 가능하기 때문에 권장하지 않음

SAVEPOINT insert_moon;

INSERT INTO emps (
    employee_id,
    last_name,
    email,
    hire_date,
    job_id
) VALUES (
    300,
    'ahn',
    'wers23@nfek.com',
    sysdate,
    1500
);

SELECT
    *
FROM
    emps;
-- emps 테이블에 moon과 ahn이 모두 들어가있는 상태 

ROLLBACK TO SAVEPOINT insert_moon;
--moon을 insert하고 난 후의 상태로 ROLLBACK
SELECT
    *
FROM
    emps;

COMMIT

COMMIT
- 보류중인 모든 데이터 변경사항을 영구적으로 적용하면서 트랜잭션 종료
- COMMIT을 한 이후에는 어떤 방법을 사용하더라도 ROLLBACK안됨

DDL(Data Definition Language)

CREATE

CREATE

테이블을 생성하고 정의하는 명령어

CREATE TABLE 테이블명 (각 속성 정의);

CREATE TABLE dept1 (
    dept_no    NUMBER(2),
    dept_name  VARCHAR2(23),
    loca       VARCHAR2(15),
    dept_date  DATE,
    dept_bonus NUMBER(6)
);
--number(2) : 정수를 2자리까지 저장할 수 있음
--number(5,2) : 정수부, 실수부를 합친 총 자리수 5자리, 소수점 2자리 ex)123.45
--number : 괄호를 생략하면 (38,0) 자동 지정
--varchar2(byte) : 괄호 안에 들어올 문자열 최대 길이 
SELECT
    *
FROM
    dept1;
    
DESC dept1;
    
    
INSERT INTO dept1
VALUES (10, '영어부', '서울', sysdate, 20000);
--만약 테이블에서 정한 범위를 초과하면 ERROR발생 할 수 있음 
INSERT INTO dept1
VALUES (10, '영업부인것 같으면서도 총무도 하는 부서', '서울', sysdate, 20000);
--사전에 정한 테이블의 정의를 바꾸고 싶음!

ALTER

ALTER

테이블에 컬럼을 추가하거나 컬럼명 변경, 컬럼 정의 수정, 컬럼 삭제(ADD, RENAME COLUMN, MODIFY, DROP, RENAME ...)
테이블 이름도 수정 가능

ALTER TABLE 테이블명 ~~~;

--컬럼 추가 
ALTER TABLE dept1
ADD (dept_count NUMBER(3));

SELECT * FROM dept1;

--컬럼명 변경 
ALTER TABLE dept1
RENAME COLUMN dept_count TO emp_count;

--컬럼 속성 변경 
ALTER TABLE dept1
MODIFY (emp_count NUMBER(5));

DESC dept2;

--컬럼 삭제 
ALTER TABLE dept1
DROP COLUMN emp_count;

--테이블 이름 변경 
ALTER TABLE dept1
RENAME TO dept3;

SELECT * FROM dept3;

TRUNCATE

TRUNCATE

테이블의 구조만 남기고 데이터를 모두 없애고 싶을때 사용하는 명령어

-- 테이블의 구조만 남겨놓고 데이터만 모두 삭제 
TRUNCATE TABLE dept3;

SELECT * FROM dept3;
--모두 삭제된 것을 확인

DROP

DROP
- TRUNCATE와 다르게 테이블도 삭제하는 명령어
```
DROP TABLE dept3;
```

PRACTICE

Practice

INSERT, UPDATE, DELETE를 이해하기 위한 연습

CREATE TABLE depts
    AS
        ( SELECT
            *
        FROM
            departments
        );
-- 1번 
INSERT INTO depts VALUES (
    280,
    '개발',
    NULL,
    1800
);

INSERT INTO depts VALUES (
    290,
    '회계부',
    NULL,
    1800
);

INSERT INTO depts VALUES (
    300,
    '재정',
    301,
    1800
);

INSERT INTO depts VALUES (
    310,
    '인사',
    302,
    1800
);

INSERT INTO depts VALUES (
    320,
    '영업',
    303,
    1700
);

--2번 
SELECT
    *
FROM
    depts;

UPDATE depts
SET
    department_name = 'IT_bank'
WHERE
    department_name = 'IT Support';

UPDATE depts
SET
    manager_id = 301
WHERE
    department_id = 290;

UPDATE depts
SET
    department_name = 'IT_Help',
    manager_id = 303,
    location_id = 1800
WHERE
    department_name = 'IT Helpdesk';
--1개 이상의 값 변경 

UPDATE depts
SET
    manager_id = 301
WHERE
    department_id IN ( 290, 300, 310, 320 );

SELECT
    *
FROM
    depts;

-- 3번 
DELETE FROM depts
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            depts
        WHERE
            department_name = '영업'
    );

DELETE FROM depts
WHERE
    department_id = (
        SELECT
            department_id
        FROM
            depts
        WHERE
            department_name = 'NOC'
    );

MERGE의 이해를 이해하기 위한 연습

MERGE

-- 4번 
SELECT
    *
FROM
    depts;

DELETE FROM depts
WHERE
    department_id > 200;

UPDATE depts
SET
    manager_id = 100
WHERE
    manager_id IS NOT NULL;

MERGE INTO depts a
USING (
          SELECT
              *
          FROM
              departments
      )
b ON ( a.department_id = b.department_id )
WHEN MATCHED THEN --조건이 맞는다면 
 UPDATE
SET a.department_name = b.department_name,
    a.manager_id = b.manager_id,
    a.location_id = b.location_id
WHEN NOT MATCHED THEN -- 조건이 맞지 않는다면 
INSERT
VALUES
    ( b.department_id,
      b.department_name,
      b.manager_id,
      b.location_id );

SELECT
    *
FROM
    depts;

--5번
CREATE TABLE job_it
    AS
        ( SELECT
            *
        FROM
            jobs
        WHERE
            min_salary >= 6000
        );

SELECT
    *
FROM
    job_it;

INSERT INTO job_it VALUES (
    'IT_DEV',
    '아이티개발팀',
    6000,
    20000
);

INSERT INTO job_it VALUES (
    'NET_DEV',
    '네트워크개발팀',
    5000,
    20000
);

INSERT INTO job_it VALUES (
    'SEC_DEV',
    '보안개발팀',
    6000,
    19000
);

MERGE INTO job_it a
USING (
          SELECT
              *
          FROM
              jobs
          WHERE
              min_salary >= 0
      )
b ON ( a.job_id = b.job_id )
WHEN MATCHED THEN UPDATE
SET a.min_salary = b.min_salary,
    a.max_salary = b.max_salary
WHEN NOT MATCHED THEN
INSERT
VALUES
    ( b.job_id,
      b.job_title,
      b.min_salary,
      b.max_salary );

SELECT
    *
FROM
    job_it;

Let's Just Go

안녕하세요! 공부한 내용을 기록하는 공간입니다.

이전 포스트

DATABASE SUBQUERY

다음 포스트

DATABASE DDL, DML, DCL

DATABASE

DATABASE

DML(Data Manipulation Language)

INSERT

UPDATE

DELETE

MERGE

DCL(Data Control Language)

TRANSACTION

ROLLBACK

SAVEPOINT

COMMIT

DDL(Data Definition Language)

CREATE

ALTER

TRUNCATE

DROP

PRACTICE

DATABASE SUBQUERY

DATABASE Constraint, View, Sequence

0개의 댓글