sqldeveloper-9

디가우징

 

 

SQL | Strucured Query Language

(0. DQL : SELECT)

1. DML : INSERT, UPDATE, DELETE

// 테이블에 데이터 삽입, 수정, 삭제 하는 명령

* 메모리에서 처리 (속도 차이)

* 개발자가 주로 사용하는 명령어

 

2. DDL : CREATE, ALTER, DROP

* 데이터베이스 관리자(DBA) 고유 업무영역

* 데이터베이스 객체를  생성, 수정, 삭제하는 명령

 

3. DCL : COMMIT, ROLLBACK

* DML을 이용한 데이터 조작 후 확정, 취소

 

-- DML
-- 데이터 조작어 (Data Manipulate Language)
-- 1. SELECT : 조회
-- 2. INSERT : 삽입
-- 3. UPDATE : 수정
-- 4. DELETE : 삭제
-- * DML은 보통 TCL(Transaction Control Language: 트랜잭션 제어어)와 함께 사용한다.
-- COMMIT : 데이터 조작 확정하는 명령
-- ROLLBACK : 데이터 조작 취소하는 명령
-- * 동시에 접속한 다른 데이터베이스 사용자(=세션)은 이전 사용자가 변경을 확정/취소 하지 않으면~ 작업이 진행되지 않음.

-- =============================================================================
-- 새로운 데이터 삽입 : INSERT
-- =============================================================================

--1. 컬럼을 지정해서 데이터 삽입 --> 순서 변경 가능
INSERT INTO 테이블 (컬럼명1, 컬럼명2, ...컬럼명)
VALUES (값1, 값2,...값n)

-- 1. 테이블의 구조대로(컬럼대로) 데이터 삽입
INSERT INTO 테이블명
VALUES (값1, 값2,...값n);

-- Q. 사원테이블에 신규 사원을 등록하고자 한다. 이때 INSERT 구문을 작성한다면?
-- 테이블 구조 파악하는 명령 : DESC 또는 DESCRIBE (SQL 대소문자를 구분함)
-- * 다만, 데이터베이스 객체(예: 테이블, 뷰, 시퀀스, 인덱스, 트리거, 함수,...)

INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (207, 'Smith', 'SMITH2024', TO_DATE('2009-08-19'), 'SA_REP'); -- 신규사원은 수습기간 3개월 후 업무배치

SELECT MAX(employee_id) AS MAX_EMPID
FROM employees; -- 마지막 사번 : 206

SELECT *
FROM employees
ORDER BY employee_id DESC;

COMMIT; -- 데이터 저장이 확정 ==> 다음 번 DEVELOPER로 DB연결, 데이터 조회할때 남아있음
ROLLBACK; -- 삽입을 취소! ==> 데이터 삽입 이전으로 회귀

-- =============================================================================
-- 새로운 데이터 변경/수정 : UPDATE
-- =============================================================================
-- * WHERE 절 생략시, 모든 행(ROW, 레코드)에 영향을 미침.

UPDATE 테이블명
SET 컬럼명1=값,
    컬럼명2=값2,
    ...
    컬럼명n=값n
WHERE 조건절;

-- Q. 방금 입력한 207번 사원 Smith의 부서를 80번으로 업데이트 하시오
UPDATE employees
SET department_id = 80
WHERE employee_id = 207;

-- Q. 방금 입력한 207번 사원 Smith의 담당 매니저를 149번 사원으로 업데이트 하시오
UPDATE employees
SET manager_id = 149,
    department_id = 80
WHERE employee_id = 207;
-- WHERE 절을 깜빡하고 생략하고, 업데이트를 실행했다면?

ROLLBACK;

-- =============================================================================
-- 데이터 삭제 : DELETE
-- =============================================================================

DELETE FROM 테이블명
WHERE 조건절;

-- Q. 207번 Smith 사원이 건강상의 이유로 퇴사를 결정했으므로, 사원테이블에서 해당 사원의 정보를 
-- 삭제하시오

DELETE FROM employees
WHERE employee_id = 207;

SELECT constraint_name, constraint_type, table_name 
FROM user_constraints
WHERE table_name IN ('EMPLOYEES', 'JOB_HISTORY'); -- 제약조건 테이블

SELECT *
FROM job_history;

DELETE FROM job_history
WHERE employee_id = 207;

COMMIT;

SELECT * 
FROM employees;

 

-- =========================
-- DML 명령어 실습 (연습문제)
-- 1. EMPLOYEES 테이블에 다음 신입 사원들의 정보를 입력하시오

-- employee_id     last_name     email       hire_date    job_id : Not Null 칼럼
--     401          Hopkins      HOPKINS     2023/12/31    SA_REP
--     402          Abraham      LINCOLN     2024/06/12    SA_REP
--     403          Tomas        EDISION99   2024/08/19    SA_REP

INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (401, 'Hopkins', 'HOPKINS', '2023/12/31', 'SA_REP');
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (402, 'Abraham', 'LINCOLN', '2024/06/12', 'SA_REP');
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
VALUES (403, 'TOMAS', 'EDISON', '2024/08/19', 'SA_REP');

-- 2. EMPLOYEES 테이블에 방금 입력한 사원들의 정보중, 아래와 같이 각각 변경하시오
-- employee_id     last_name     salary    department_id
--     401          Hopkins      6000        80
--     402          Abraham      7000        80
--     403          Tomas        8000        80

UPDATE employees
SET salary = 6000,
    department_id = 80
WHERE employee_id = 401;
UPDATE employees
SET salary = 7000,
    department_id = 80
WHERE employee_id = 402;
UPDATE employees
SET salary = 8000,
    department_id = 80
WHERE employee_id = 403;

-- 3. EMPLOYEES 테이블에 등록된 사원중 입사년도가 2023,2024년도에 해당하는 사원을 
-- 삭제하시오

DELETE FROM employees
WHERE employee_id = 401;
DELETE FROM employees
WHERE employee_id = 402;
DELETE FROM employees
WHERE employee_id = 403;

-- ORA-02292: 무결성 제약조건(HR.JHIST_EMP_FK)이 위배되었습니다. - 자식 레코드가 발견되었습니다.
-- EMPLOYEES 테이블에 데이터가 삽입되면, 트리거가 실행되면서 JOB_HISTORY 테이블에 해당 사원이 등록되어서 생기는 문제
-- 해결방법 : 1) 테이블 생성시 ON DELETE CASCADE를 추가 하거나 2) 아니면 직접 각 테이블의 자식 레코드를 찾아서 삭제

SELECT *
FROM job_history;

DESC job_history;

-- ======
-- ITAS : 이타쓰 ??  INSERT INTO 테이블명 AS SELECT 이하~ : 데이터를 조회 후 다른 테이블에 삽입
-- CITAS : 씨타쓰 ?? CREATE TABLE 테이블명 [AS] SELECT 이하~ : 데이터를 조회 후 다른 테이블을 생성하여 삽입
-- =========

CREATE TABLE emp AS
SELECT * 
FROM employees
WHERE 1 > 2; --거짓 조건 : 테이블의 구조만 복사
-- WEHERE 조건절; -- 테이블 구조와 데이터를 복사 (조건에 맞는)

-- Table EMP가 생

SELECT * 
FROM emp; -- 0 rows : 데이터가 없는 '구조'만 복사한 명령 결과

CREATE TABLE emp80 AS
SELECT *
FROM employees
WHERE department_id = 80;

SELECT *
FROM emp80;
-- emp : 빈 테이블에 employees 테이블의 50번 부서에 근무하는 사원들의 정보를 입력한다면
-- ITAS로 50번 부서원 정보를 emp 테이블에 저장하는 SQL (선택 + 이미 존재하는 테이블에 삽입)
INSERT INTO emp (employee_id, last_name, email, hire_date, job_id)
SELECT employee_id, last_name, email, hire_date, job_id
FROM employees
WHERE department_id = 50;

ROLLBACK;
-- CTAS로 50번 부서원을 필터링해서 emp50 테이블에 저장한는 SQL (선택 + 생성)
CREATE TABLE emp30
SELECT *
FROM employees
WHERE depatment_id= 30;

SELECT  *
FROM emp30;