[ORACLE] DML - INSERT, DELETE, UPDATE, SELECT

DML (Data Manipulation Language)
= 데이터 조작어 - 데이터를 조작하는 명령어  - 데이터를 추가, 삭제, 변경, 검색(조회)	INSERT - 추가
	DELETE - 삭제(데이터의 전체, 일부 제거)
	UPDATE - 갱신(수정)
	DQL (Data Query Language) = 데이터 질의어  - 데이터의 가져오는 명령만 처리하는 명령문 SELECT - 원본은 유지되지만 데이터를 검색해서 가져올때 변형해서 가져올 수 있다. (검색, 조회)
**반드시 영구적인 반영을 위해서는 TCL (확정(commit), 되돌리기(rollback))을 이용해야만 영구반영이 이루어진다.

INSERT명령문
: DML중에서 데이터의 행 수를 추가할 때 사용하는 명령문
--정식형식 :테이블의 컬럼을 모두 지정하거나 일부만 지정할 때 사용가능  없는 값은 NULL값으로 자동처리(단 지정한 기본값이 있는 경우 제외) INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) VALUES (값1, 값2, ...) ;	INSERT INTO 테이블명 (컬럼명1, 컬럼명2, ...) VALUES (값1, 값2, ...) ;
--단축형식  반드시 테이블에 있는 컬럼의 갯수만큼 컬럼의 순서대로 모두 값을 넣어야 한다. INSERT INTO 테이블명 VALUES (값1, 값2, ...) ;	INSERT INTO 테이블명 VALUES (값1, 값2, ...) ;
INSERT 여러 개의 테이블에 데이터 한꺼번에 추가하기
--모든 테이블이 같은 형식, 구조, 컬럼의 나열 순서까지 동일한 경우 --테이블명1과 테이블명2의 컬럼기술부를 제거할 수 있는 이유 : 해당 테이블의 전체 컬럼에 대해 순서대로 값을 넣는 경우만 가능 INSERT ALL INTO 테이블명1 INTO 테이블명2 VALUES (값1, 값2, 값3, ...) --값자리에는 컬럼명으로 대체 SELECT * | 컬럼명1, 컬렴명2, 컬럼명3, ... FROM 데이터를 끌어올 테이블명;  [WHERE 조건들...] ;	--테이블 구조만 복사 CREATE TABLE DEPT01 AS SELECT * FROM DEPT WHERE 1=0; --동일 테이블 구조에 데이터 복사 추가(서브쿼리 이용) INSERT ALL INTO DEPT01 SELECT * FROM DEPT; --테이블 데이터 복사 붙여넣기 INSERT ALL INTO DEPT01 VALUES (DEPTNO, DNAME, LOC) SELECT * FROM DEPT;
--테이블의 나열 순서는 동일하지만, 컬럼의 갯수가 다른 경우 INSERT ALL INTO 테이블명 (컬럼1, 컬럼2) VALUES (값1, 값2, ...) INTO 테이블명 (컬럼1, 컬럼2, 컬럼3) VALUES (값1, 값2, 값3) SELECT 컬럼명1, 컬렴명2, 컬럼명3, ... FROM 데이터를 끌어올 테이블명;  [WHERE 조건들...] ;	INSERT ALL INTO EMP04 INTO EMP05 VALUES (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) SELECT * FROM EMP;

--[INSERT] 정식형식 
INSERT INTO DEPT01 (DEPTNO, DNAME, LOC) VALUES (10,'운영','서울');
INSERT INTO DEPT01 (DNAME, LOC, DEPTNO) VALUES ('운영','서울',10); --컬럼순서에 맞게끔 값 입력

--[INSERT] 단축형식 
INSERT INTO DEPT01 VALUES (10,'운영','서울') ; --(O)
INSERT INTO DEPT01 VALUES (10,'운영') ; --(X)
INSERT INTO DEPT01 VALUES ('서울', 10,'운영') ; --(X)

--추가할 데이터 10번부서, 부서명 : ACCOUNTING, 지점 : NEW YORK
INSERT INTO DEPT01 (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'NEW YORK'); --정식
INSERT INTO DEPT01 VALUES(10, 'ACCOUNTING', 'NEW YORK'); --단축
--추가 30, 'SALES'
INSERT INTO DEPT01 (DEPTNO, DNAME) VALUES (30, 'SALES');
INSERT INTO DEPT01 VALUES (30, 'SALES', NULL);
INSERT INTO DEPT01 VALUES (30, 'SALES', '');
--40, 'OPERATIONS'
--50, null, 'CHICAGO'
INSERT INTO DEPT01 (DEPTNO, DNAME) VALUES(40, 'OPERATIONS');
INSERT INTO DEPT01 VALUES(50, null, 'CHICAGO');​

DELETE 명령문
: 데이터를 줄 단위로 삭제하는 경우 처리하는 명령문
DELETE [FROM] 테이블명 WHERE 조건들... ;	--EMP01테이블에서 데이터를 제거하는데 부서명이 RESEARCH, SALES 인 사원을 제거하세요. DELETE FROM EMP01 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE DNAME IN ('RESEARCH','SALES')); --[SUB QUERY를 이용한 데이터 삭제] DELETE FROM EMP01 WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME ='SALES');
** DROP TABLE 테이블명 PURGE(테이블자체 완전제거);

--[DELETE]
--사원정보(EMP01)테이블에서 부서번호가 10번인 사원만 제거하세요.
DELETE FROM EMP01 WHERE DEPTNO = 10;
ROLLBACK;

DELETE FROM EMP01;
ROLLBACK;

--82년 이후 입사한 모든 직원 삭제
DELETE FROM EMP01 WHERE HIREDATE>='1982/1/1';
ROLLBACK;

DELETE FROM EMP01 WHERE SAL>1000 AND SAL<2000;
ROLLBACK;

UPDATE 명령문
: 데이터를 부분 수정할 때 필요한 명령문 (줄 단위로 처리됨)
UPDATE 테이블명 SET 변경할컬럼명1 = 변경할값1, 변경할컬럼명2, 값2,... WHERE 조건 -- SUB QUERY	--DEPT01 테이블에서 30번 부서인 부서명을 CLERK로 변경 UPDATE DEPT01 SET DNAME = 'CLERK' WHERE DEPTNO=30;
	--[SUB QUERY_UPDATE] UPDATE DEPT01 SET LOC = (SELECT LOC FROM DEPT01 WHERE DEPTNO=40) WHERE DEPTNO = 10; --SUB QUERY를 이용한 UPDATE 2건 UPDATE DEPT01 SET (DNAME, LOC) = (SELECT DNAME, LOC FROM DEPT WHERE DEPTNO =30) WHERE DEPTNO = 10; UPDATE EMP01 SET DNAME = (SELECT DNAME FROM DEPT WHERE DEPT.DEPTNO = EMP01.DEPTNO);

--[UPDATE]
--DEPT01 테이블에서 30번 부서인 부서명을 CLERK로 변경
UPDATE DEPT01 SET DNAME = 'CLERK' WHERE DEPTNO=30;
-- 30번 지점 SEOUL
UPDATE DEPT01 SET LOC = 'SEOUL' WHERE DEPTNO=30;
-- 40번 지점 DAEGU
UPDATE DEPT01 SET LOC = 'DAEGU' WHERE DEPTNO=40;

--영구적인 반영
COMMIT;

UPDATE DEPT01 SET DEPTNO = 30;
--되돌리기
ROLLBACK;

UPDATE DEPT01 SET DEPTNO = 30;
COMMIT;
ROLLBACK;

CREATE TABLE EMP01 AS 
SELECT E.*, SAL+100 AS SAL1 
FROM EMP E;

UPDATE EMP01 SET SAL1 =0;
COMMIT;
--연봉 10%인상
UPDATE EMP01 SET SAL1 = SAL*1.1;
ROLLBACK;

UPDATE EMP01 SET SAL1 = SAL*1.1 WHERE SAL>=3000;
ROLLBACK;

UPDATE EMP01 SET HIREDATE = SYSDATE WHERE HIREDATE BETWEEN '1982/1/1' AND '1982/12/31';
ROLLBACK;

UPDATE EMP01 SET DEPTNO = 20, JOB = 'MANAGER' WHERE ENAME = 'JAMES';
ROLLBACK;

UPDATE EMP01 SET MGR = 7698 WHERE DEPTNO = 10;
ROLLBACK;

--변경
UPDATE EMP01 SET COMM = 50 WHERE NVL(COMM, 0) = 0;

SELECT문
SELECT문 (조회, 선택) - DATABASE에 저장되어 있는 자원들을 검색할때 사용 - 구성 (문법 : 형식 : Syntax)   SELECT [DISTINCT] { *, 컬럼명1, 컬럼명2, ... }   FROM 테이블명   WHERE 조건;
<필수> SELECT * | 컬럼명1, 컬럼명2 => 가져올 컬럼을 지정 - 모든 컬럼을 선택할 때는 "*"를 사용한다.  하지만 "*"는 컬럼 검색과 함께 연산이 많아서 데이터베이스의 속도를 저하시킨다. - 그룹컬럼 [DISTINCT] 중복값 제거 FROM 테이블명 => 가져올 데이터가 존재하는 테이블명을 지정
WHERE => 가져올 행 수의 조건을 지정	SELECT NO, GENDER FROM USERLIST WHERE NO = 2 ; SELECT * FROM USERLIST WHERE GENDER = '남' --WHERE NO=1 OR NO=2 OR NO=6
GROUP BY : 조건으로 가져온 것중에서 동일한 값을 가진 컬럼이 있을 경우 그 값의 중복을 제거하는 절 GROUP BY에서 설정한 컬럼들, 또는 그룹함수들 FROM 테이블명  WHERE 조건(어떤 컬럼이던 가능) GROUP BY 그룹으로 묶을 컬럼명들 + HAVING : 위 그룹 조건에서 다시 조건을 거는 경우(반드시 GROUP BY이 있어야 한다) HAVING 그룹함수로 지정하는 조건 또는 GROUP BY에 설정한 칼럼으로 만드는 조건만 가능	SELECT NM, GENDER FROM USERLIST GROUP BY NM, GENDER
ORDER BY : 컬럼의 오름차순(ASC-생략가능), 내림차순(DESC) 지정하는 절	ORDER BY NM, GENDER ORDER BY NM, GENDER DESC; --NM, GENDER 순으로
; 세미콜론으로 끝냄

--[SELECT]
--EMP테이블에서 DEPTNO값이 30인 데이터 중에서 EMPNO, DEPTNO컬럼의 값을 가져오세요.
SELECT EMPNO, DEPTNO
FROM EMP
WHERE DEPTNO = 30;

--EMP테이블에서 DEPTNO 컬럼만 오름차순으로 다 가져오세요.
SELECT DEPTNO FROM EMP ORDER BY DEPTNO;

SET LINESIZE 200

--DEPT테이블에서 지역이 보스톤인 데이터의 부서명을 가져오세요
SELECT DNAME FROM DEPT WHERE LOC = 'BOSTON';

--이름이 SMITH인 사원의 사번, 이름, 부서번호를 가져오세요.
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE ENAME = 'SMITH';

SELECT EMPNO, ENAME FROM EMP WHERE MGR=7839;

--입사일자가 81년 5월 1일 미만인 사원의 사번, 이름, 입사일자를 가져오세요.
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE <'1981/05/01'
ORDER BY HIREDATE DESC;


SELECT EMPNO, ENAME, SAL, SAL+SAL*0.1 FROM EMP;

--사원정보에서 사번, 이름, 연봉, 월급을 가져오세요.
SELECT EMPNO, ENAME, SAL, SAL/12 FROM EMP;


--연봉이 3000이상, 5000이하인 사원정보를 가져오세요.
SELECT * FROM EMP WHERE SAL >= 3000 ORDER BY SAL;
SELECT * FROM EMP WHERE SAL BETWEEN 3000 AND 5000 ORDER BY SAL;

SELECT EMPNO, ENAME, SAL, SAL+SAL*0.1 FROM EMP;

--사원정보에서 사번, 이름, 연봉, 월급을 가져오세요.
SELECT EMPNO AS "사원 번호", ENAME 이름, SAL 연봉, SAL/12 월급 FROM EMP;
--문자열에 공백이 들어 갈 경우 ""으로 표기

SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 1300 AND 1600 ORDER BY SAL;

--[SELECT]
--EMP테이블에서 DEPTNO값이 30인 데이터 중에서 EMPNO, DEPTNO컬럼의 값을 가져오세요.
SELECT EMPNO, DEPTNO
FROM EMP
WHERE DEPTNO = 30;

--EMP테이블에서 DEPTNO 컬럼만 오름차순으로 다 가져오세요.
SELECT DEPTNO FROM EMP ORDER BY DEPTNO;

SET LINESIZE 200

--DEPT테이블에서 지역이 보스톤인 데이터의 부서명을 가져오세요
SELECT DNAME FROM DEPT WHERE LOC = 'BOSTON';

--이름이 SMITH인 사원의 사번, 이름, 부서번호를 가져오세요.
SELECT EMPNO, ENAME, DEPTNO FROM EMP WHERE ENAME = 'SMITH';

SELECT EMPNO, ENAME FROM EMP WHERE MGR=7839;

--입사일자가 81년 5월 1일 미만인 사원의 사번, 이름, 입사일자를 가져오세요.
SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE HIREDATE <'1981/05/01'
ORDER BY HIREDATE DESC;


SELECT EMPNO, ENAME, SAL, SAL+SAL*0.1 FROM EMP;

--사원정보에서 사번, 이름, 연봉, 월급을 가져오세요.
SELECT EMPNO, ENAME, SAL, SAL/12 FROM EMP;


--연봉이 3000이상, 5000이하인 사원정보를 가져오세요.
SELECT * FROM EMP WHERE SAL >= 3000 ORDER BY SAL;
SELECT * FROM EMP WHERE SAL BETWEEN 3000 AND 5000 ORDER BY SAL;

SELECT EMPNO, ENAME, SAL, SAL+SAL*0.1 FROM EMP;

--사원정보에서 사번, 이름, 연봉, 월급을 가져오세요.
SELECT EMPNO AS "사원 번호", ENAME 이름, SAL 연봉, SAL/12 월급 FROM EMP;
--문자열에 공백이 들어 갈 경우 ""으로 표기

SELECT ENAME, JOB FROM EMP WHERE SAL BETWEEN 1300 AND 1600 ORDER BY SAL;

 

 

--[GROUP BY]
--사원정보 테이블에서 부서별 인원수를 구하세요.
SELECT DEPTNO, COUNT(*) 인원수 FROM EMP GROUP BY DEPTNO;

--사원정보 테이블에서 부서별 연봉의 총합계와 연봉의 평균을 구하세요.
SELECT DEPTNO, SUM(SAL) 연봉합계 , AVG(SAL) "연봉 평균" FROM EMP GROUP BY DEPTNO;

--사원정보 테이블에서 부서별 최대연봉의 최소연봉을 구하세요.
SELECT DEPTNO, MAX(SAL) 최대연봉, MIN(SAL) 최소연봉 FROM EMP GROUP BY DEPTNO;

--사원정보 테이블에서 1981년 이후의 사원 중에 부서별로 인원수가 몇명인지 구하세요.
SELECT DEPTNO, COUNT(*) FROM EMP WHERE HIREDATE >='1981/1/1' GROUP BY DEPTNO ORDER BY DEPTNO;

SELECT DEPTNO, COUNT(*) FROM EMP WHERE DEPTNO IN(10,20) GROUP BY DEPTNO;

--[HAVING]
SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>=5;
SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING SUM(SAL)>=7000;

--부서별로 연봉의 평균이 2000이상인 경우 부서별, 연봉평균 자료만 산출
SELECT DEPTNO, AVG(SAL) 연봉평균 FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>=2000;

--부서별로 연봉의 최대값이 2900을 초과하는경우, 부서별 연봉의 최대값과 최소값 자료만 산출
SELECT DEPTNO, MAX(SAL) 연봉최대값 , MIN(SAL) 연봉최소값 FROM EMP GROUP BY DEPTNO HAVING MAX(SAL)>2900;

--테이블에서 1000이상의 연봉을 가지고 있는 사람들에 대해서만 부서별로 연봉의 평균을 구한 후 
--구해진 부서별 평균 급여가 2000이상인 부서번호와 부서별 평균연봉을 출력
SELECT DEPTNO 부서번호, AVG(SAL) 평균연봉 FROM EMP WHERE SAL>=1000 GROUP BY DEPTNO HAVING AVG(SAL)>=2000;

--직종별, 부서별 급여의합과 인원수를 출력, 이 때 20번 부서의 내용만 확인
SELECT JOB 직종별, DEPTNO 부서별, SUM(SAL) 급여의합, COUNT(*)인원수 FROM EMP GROUP BY DEPTNO, JOB HAVING DEPTNO =20;
--WHERE->단일컬럼
SELECT JOB 직종별, DEPTNO 부서별, SUM(SAL) 급여의합, COUNT(*)인원수 FROM EMP WHERE DEPTNO =20 GROUP BY DEPTNO, JOB;

--각 부서별 인원이 5명 이상인 부서의 부서별 연봉평균과 연봉합을 출력하세요.
SELECT DEPTNO, AVG(SAL) 연봉평균, SUM(SAL)연봉합 FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>=5 ;



--WHERE FALSE 조건 복제한거라 테이블 내 데이터 없음> 구조만
CREATE TABLE DEPT01 AS SELECT * FROM SCOTT.DEPT WHERE 1=0;