07-02(금) DML,트랜잭션

※ 개발 수업 정리글입니다. 꾸준히 정리할 예정이고 틀린 부분이 있다면 언제든지 댓글 환영입니다.

---

DML  다중테이블 다중행 INSERT ALL INTO VALUES 서브쿼리

①다중테이블 다중행 INSERT ALL INTO 테이블 VALUES 컬럼 서브쿼리

<기본문법>
INSERT ALL
{ INTO 테이블 VALUES (컬럼1, 컬럼2, ...) }
{ INTO 테이블 VALUES (컬럼1, 컬럼2, ...) }
SELECT 컬럼1, 컬럼2, ... 서브쿼리 ;
*반드시 서브쿼리의 컬럼명=VALUES의 컬럼명

ex) 테이블 제작 CTAS시탁스

CREATE TABLE MYEMP_HIRE
AS
SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP WHERE 1=2;

CREATE TABLE MYEMP_MGR
AS
SELECT EMPNO,ENAME,MGR FROM EMP WHERE 1=2;

위 테이블 2개에 행 추가

INSERT ALL
INTO MYEMP_HIRE VALUES (EMPNO,ENAME,HIREDATE,SAL)
INTO MYEMP_MGR VALUES (EMPNO,ENAME,MGR)
SELECT EMPNO,ENAME, HIREDATE, SAL,MGR    --VALUES에 해당되는 서브쿼리
FROM EMP ;

 

 

②지정한 조건을 만족하는 행만

 INSERT ALL WHEN 조건 INTO 테이블 VALUES 컬럼 서브쿼리

<기본문법>
INSERT ALL
WHERE 조건 { INTO 테이블 VALUES (컬럼1, 컬럼2, ...) }
WHERE 조건 { INTO 테이블 VALUES (컬럼1, 컬럼2, ...) }
SELECT 컬럼1, 컬럼2, ... 서브쿼리 ;
*반드시 서브쿼리의 컬럼명=VALUES의 컬럼명

ex) 테이블 제작 CTAS시탁스

CREATE TABLE MYEMP_HIRE2
AS
SELECT EMPNO,ENAME,HIREDATE,SAL FROM EMP WHERE 1=2;

CREATE TABLE MYEMP_MGR2
AS
SELECT EMPNO, ENAME,MGR FROM EMP WHERE 1=2;

위 테이블 2개에 행 추가
INSERT ALL
WHEN SAL> 3000 THEN
INTO MYEMP_HIRE2 VALUES(EMPNO,ENAME,HIREDATE,SAL)
WHEN MGR=7698 THEN
INTO MYEMP_MGR2 VALUES (EMPNO, ENAME, MGR)
SELECT EMPNO, ENAME, HIREDATE,SAL,MGR    --VALUES에 해당되는 서브쿼리
FROM EMP ; 

---

DML  UPDATE 테이블명 SET 컬럼=변경할값 WHERE 조건

■ 기본문법

UPDATE 테이블명
SET 컬럼명=변경할값 [ , 컬럼명2=변경할값]
WHERE 조건 ;
ㄴWHERE절엔 어떤걸 찾아서 수정할지

★★★만약 WHERE이 빠지면 에러는 안남, 대신 컬럼명의 모든 값을, 변경할 값으로 바꿔버리니까 조심하기★★★

한번 COMMIT을 하면 롤백이 안됨
그래서 UPDATE ; 하곤 반드시 ★ROLLBACK★을 해줘야함

 

(예제) 

 ① updaTE MYDEPT
 SET DNAME='영업',LOC='경기'
 WHERE DEPTNO=50;    --WHERE 안 적으면 모든 DNAME이 영업으로, 모든 LOC이 경기로 바뀔것
 ROLLBACK;
 

② 사원이름이 FORD의 부서번호를 10번으로 수정하고 롤백하라

UPDATE EMP
 SET DEPTNO=10
 WHERE ENAME='FORD';
 ROLLBACK;

 

③ 부서번호가 20번인 사원의 MGR을 7839, JOB을 SALESMAN으로 수정하고 롤백하라
- 여러개의 값을 변경할땐,
UPDATE EMP
SET MGR=7839, JOB='SALESMAN'
WHERE DEPTNO=20;
ROLLBACK;

 

④ FORD의 입사일을 오늘날짜로 수정하고 롤백하라
- 현재 날짜로 변경할땐 
UPDATE EMP
SET HIREDATE=SYSDATE
WHERE ENAME='FORD';
ROLLBACK;

 

⑤ 부서번호가 10인 사원들의 월급을 1000으로 수정하고 롤백하라
UPDATE EMP
SET SAL=1000
WHERE DEPTNO=10;
ROLLBACK;

 

*책 258쪽 7-32

select DNAME FROM DEPT WHERE DEPTNO=10;
SELECT LOC FROM DEPT WHERE DEPTNO=20;
INSERT INTO MYDEPT VALUES(60,'AA','AA');
COMMIT;
UPDATE MYDEPT
SET 
DNAME=(
select DNAME FROM DEPT WHERE DEPTNO=10),
LOC=(
SELECT LOC FROM DEPT WHERE DEPTNO=20)
WHERE DEPTNO=60;

-select DNAME FROM DEPT WHERE DEPTNO=10;
SELECT LOC FROM DEPT WHERE DEPTNO=20;
INSERT INTO MYDEPT VALUES(60,'AA','AA');
COMMIT;

 

■ UPDATE 테이블 SET 컬럼=(서브쿼리) 넣는 문

UPDATE 테이블
SET 컬럼=(서브쿼리1) , 컬럼=(서브쿼리2)
WHERE 조건;
ㄴWHERE절엔 어떤걸 찾아서 수정할지

(예제) 부서명이 ACCOUNTING인 부서에 해당하는 직원의 월급을 500으로 수정하고 롤백하라

-서브쿼리를 WHERE조건에 넣어서 변경하기
UPDATE emp
SET SAL=500
WHERE DEPTNO=
(SELECT DEPTNO FROM DEPT WHERE DNAME='ACCOUNTING');
ROLLBACK;

 

---

DML  DELETE FROM 테이블명 WHERE 조건

하고 커밋해야 반영되는데 이전 내용으로 돌아갈거면 롤백해주기
★★★만약 WHERE 조건이 없으면, 테이블명의 모든 데이터가 날아가고 껍데기만 남으니까 조심하기★★★
delete from emp;
rollback;

★★UPDATE, DELETE에서 레코드를 못찾는건 에러가 아님★★
ex) UPDATE EMP SET ENAME='AAAA' WHERE EMPNO=1111;  --EMPNO엔 1111이 없음,업데이트가 안됐으니까
      ㄴ0개 행 이(가) 업데이트되었습니다.

■ DELETE FROM 테이블명 WHERE 컬럼 = (서브쿼리) 넣는 문

DELETE FROM MYDEPT
WHERE LOC=(SELECT LOC FROM DEPT WHERE DEPTNO=20) ; 

 

* mysql은 UPDATE, DELETE의 테이블에 그냥 별칭 쓰면 오류날 수 있다.

  하나의 테이블만 있을땐 되도록 아예 안 쓰지 않거나,
  별칭을 꼭 써야한다면(ex. JOIN으로 다른 테이블과 구분필요) 아래와 같이 써야함

--아예 별칭 미사용
DELETE FROM TB_fruit
WHERE name ='apple'
AND   price='1000'

--써야한다면 DELETE 뒤에 별칭 작성
DELETE F FROM TB_fruit F
WHERE a.name ='apple'
AND   a.price='1000'

--써야한다면 별칭대신 테이블 풀네임 작성
DELETE FROM TB_fruit
WHERE TB_fruit.name ='apple'
AND   TB_fruit.price='1000'

---

DML  MERGE 

(예제)

--테이블 생성
CREATE TABLE pt_01
( 판매번호 VARCHAR2(8),
제품번호 NUMBER,
수량 NUMBER,
금액  NUMBER);

CREATE TABLE pt_2
( 판매번호 VARCHAR2(8),
제품번호 NUMBER,
수량 NUMBER,
금액  NUMBER);

CREATE TABLE p_total
( 판매번호 VARCHAR2(8),
제품번호 NUMBER,
수량 NUMBER,
금액  NUMBER);

--데이터입력
INSERT INTO pt_01 VALUES ( '20170101',1000,10,500);
INSERT INTO pt_01 VALUES ( '20170102',1001,10,400);
INSERT INTO pt_01 VALUES ( '20170103',1002,10,300);

INSERT INTO pt_02 VALUES ( '20170201',1003,5,500);
INSERT INTO pt_02 VALUES ( '20170202',1004,5,400);
INSERT INTO pt_02 VALUES ( '20170203',1005,5,300);
COMMIT;

--P01 테이블 연결
MERGE INTO P_TOTAL TOTAL
USING PT_01 P01
ON(TOTAL.판매번호=P01.판매번호)
WHEN MATCHED THEN
    UPDATE SET TOTAL.제품번호=P01.제품번호
WHEN NOT MATCHED THEN
INSERT VALUES(P01.판매번호,P01.제품번호,P01.수량,P01.금액);

--P02도 마찬가지로 만들고 COMMIT;

 

 

---

 트랜잭션 

데이터베이스조작=DML ( INSERT DELETE UPDATE MERGE )

1. 트랜잭션: 논리적인 작업단위

-DML은 명령시 자동으로 트랜잭션이 시작됨. 종료는 명시해야함
-INSERT INTO를 많이 하면,  전부 실행과 동시에 자동으로 시작은 됐음.
 그걸 커밋 반영 안하고 ROLLBACK하면 전부 종료가 되면서 되돌아갔음

 

(예제)

INSERT INTO DEPT VALUES (50, 'AA','AA'); --트랜잭션 자동시작(DB에 반영은안된상태)

ROLLBACK ; --트랜잭션 종료

INSERT INTO DEPT VALUES (50, 'AA','AA'); --여기서부터

INSERT INTO DEPT VALUES (60, 'AA','AA'); --여기까지 전부 하나의 트랜잭션 자동시작(DB에 반영은안된상태)

ROLLBACK ; --트랜잭션 종료

INSERT INTO DEPT VALUES (50, 'AA', 'AA')'; --시작

UPDATE DEPT SET LOC 'AAAA' ;

ROLLBACK; --롤백, TX종료

ㄴ만약 위에 INSERT랑 UPDATE 사이에 SELECT문이 들어있다? 그거는 트랙잭션이랑 전혀 상관이 없음

(예제) 계좌이체

UPDATE문과 INSERT문 모두 성공

 

 

책269쪽

2. 종료 전의 진행 중인 데이터상태

-원래 계정 접속하고 그 계정 안에선 그 계정의 테이블의 스키마 없이 쓸 수 있지만,

 타계정에선 앞에 스키마를 붙여줘야함

<계정 바꾸는 단축키> 접속 선택: ALT + F10

 

① TX종료 전까진 모두 임시적 상태. 복구가능

② 책270쪽 *Lock 경합

SCOTT계정➤ UPDATE DEPT SET LOC='부산' WHERE DEPTNO=40 ; --TX종료가 안되어있어서 사번40에 락이 걸려있음

SYS계정➤ DELETE FROM SCOTT.DEPT WHERE DEPTNO=40 ;

ㄴ 위에서 사번 40번에 락이 걸려있어서 풀릴때까지 ScriptRunner작업하고, 결국 안된다는거임..

SCOTT계정➤가서 롤백하고, DEPT테이블의 DEPTNO=40 제대로 있는지 확인

③ '읽기 일관성' 다른 사람은 락을 풀어줄때까지 못 본다

SCOTT계정➤ UPDATE DEPT SET LOC='부산' WHERE DEPTNO=40 ; --TX종료가 안되어있음

SCOTT계정➤ SELECT * FROM DEPT ; --바뀌어있음

SYS계정➤ SELECT * FROM SCOTT.DEPT ;  --TX종료 전의 데이터로 나타남

∴ LOCK 락 = TX이 종료되지 않은 상태

 

 

★★★웤샵★★★

 

 

<JAVA언어 어려워하는거니까 대략적으로 잡고 오기>

1. 반복문 - FOR문의 사용

2. 조건문 - IF ELSE IF문

3. 함수의 개념 - 매개변수, 리턴값 (계산기 프로그램 덧셈,뺄셈 함수로 구현)

 

---

 DDL  (definition) 

자동 커밋

 

TABLE 생성

CREATE TABLE [스키마].테이블명
(1개의컬럼명  데이터타입 [조건]) ;

SELECT DEPTNO, DNAME, LOC
FROM SCOTT.DEPT ; --굵은 글씨가 스키마

①테이블명 짓는데★테이블명 주의사항★
-대소문자구분X, 문자로 시작, 길이는 30문자 이내
-한글가능하지만 비추
-예약어로는 이름 사용불가
-데이터사전에선 반드시 대문자로

②테이블 생성 후, 컬럼이름과 데이터타입 지정해야함
③새 데이터 입력하기

 

 

 

 

고정길이 ▼

 

가변길이 ▼

★자주 쓰이는 꼭 알아야할 데이터 타입★
CHAR(size)	고정길이
VARCHAR2	가변길이
NUMBER(p, s)	가변길이  p:전체자릿수  s:소수점자릿수
DATE	SYSDATE INSERT
ROWID	이런 거 있다는 개념만 잡고 나중에 배움

 

책276쪽

기본키를 SELECT하는게 제일 빠름. 오라클이 알아서 ROWID란걸 만들어서 정렬해줌
SELECT ROWID, DEPTNO FROM DEPT;
SELECT * FROM DEPT WHERE ROWID='AAAE5cAABAAALCJAAA';
대충하고 뒤에 인덱스에서 제대로 배움

 

LOB는 지금 안하지만 나중에 프로그래머 깊게 들어가면 공부해야함. BLOB,CLOB, BFILE.. 우린 안배움..

(예제 시작)
1.제약조건 없는 테이블 만들기
①② CREATE TABLE SCOTT.employee
(EMPNO NUMBER(4),
ENAME VARCHAR2(20),
HIREDATE DATE,
SAL NUMBER(7,2));

③새 데이터 입력하기
INSERT INTO EMPLOYEE
VALUES(100, 'AA', SYSDATE, 500);  --어차피 값을 다 입력할거니까 위에 테이블 뒤엔 컬럼 나열 안했음
COMMIT;
SELECT *FROM EMPLOYEE;


2.DEFAULT옵션 사용   =   DEFAULT 옵션 사용  자동으로 기본값이 입력되어 NULL값이 저장되는걸 방지
①② CREATE TABLE employee2
(EMPNO NUMBER(4) DEFAULT 10,
ENAME VARCHAR2(20),
HIREDATE DATE DEFAULT SYSDATE,
SAL NUMBER(4));

 

③ INSERT INTO EMPLOYEE2(ENAME, SAL)
VALUES( 'AA', 100);  -- EMPNO와 HIREDATE 뺐으니까, 위에 테이블 뒤에 빠진컬럼 제외하고 컬럼나열해줌
COMMIT;
SELECT * FROM EMPLOYEE2;   --EMPNO와 HIREDATE를 안적어도 NULL값이 아니고 설정해둔 디폴트값으로 뜸

=>EMPLOYEE2 테이블 삭제했음. 나중에 다시 만들거임

 

(예제) CREATE TABLE employee2
(EMPNO NUMBER(4) DEFAULT 10,
ENAME VARCHAR2(20),
HIREDATE DATE DEFAULT SYSDATE,
SAL NUMBER(4));

INSERT INTO employee2 (ENAME, SAL)
VALUES('AA', 500);
SELECT *FROM EMPLOYEE2;

 

---

제약조건 (CONSTRAINTS RULE)

INSERT가 안되고, UPDATE도 다 검사한다는거임
문제가 없는 데이터라는 걸 보장 받기 위해서임

<제약조건 적는 위치>

컬럼레벨    : 컬럼 옆에 직접 제약조건을 각각 적음
테이블레벨 : 컬럼 다 쓰고나서 컬럼들 마지막에 컬럼 순서대로 모아서 적음
결론은, 컬럼 옆이냐 OR 컬럼 아래냐

★자주 쓰이는 꼭 알아야할 제약조건 타입★
NOT NULL	★컬럼레벨 만 지원★
UNIQUE	중복불가, NULL은 가능
PRIMARY KEY 기본키	중복불가, NULL도 불가
FOREIGN KEY 외래키, 참조키	다른 테이블의 컬럼값을 참조 ex) DEPT 테이블에 DEPTNO 55가 없는데 참조키로 쓰면 안뜸
CHECK	값의 범위나 사용자 조건 지정