* DDL과 DML
- QUERY : SELECT
- DML : INSERT, UPDATE, DELETE
- DDL : CREATE, ALTER, DROP, RENAME
- 다음 중 성격이 다른 SQL 문은 무엇인가? 답 : 5번 > 구조의 정의라서 조금 다른 성격
1. SELECT 2. INSERT 3. UPDATE 4. DELETE 5. DROP
|
QUERY와 DML은
‘DQL’ |
* TRANSACTION : 일련의 작업 처리를 위한 연관된 DML의 모음
> 예를 들어, 은행에서 이체를 하려면 A은행과 B은행을 둘 다 UPDATE 해야 함
- ALL OR NOTING > 되려면 전부 되거나, 아니면 전부 안되거나 > TCL
- TCL : COMMIT(>ALL), ROLLBACK(>NOTHING)
> COMMIT: 지금 했던 트랜잭션 사이의 DML들은 데이터베이스에 모두 반영하겠다.
> ROILLBACK: 취소하겠다.
- 읽기 일관성 > 읽는 시점에는 COMMIT한 데이터만 보여줌
> ROLLBACK : 지금까지 트랜잭션한 것을 취소한다. UNDO SEGMENT에서 다시 원래 값을 끌어옴
>> LOGIN == > LOGOUT 까지가 한 개의 트랜잭션
중간에 TCL을 만나면 현재 트랜잭션이 종료되고,
다음 첫 번째 DML부터 새로운 트랜잭션이 시작됨
(+ JAVA는 DML마다 각각 트랜잭션 > 따라서 DML 하나마다 COMMIT)
- 자료 수정 후 COMMIT 안하고 다른 CMD창에서 또 값 수정 시도하면 창이 멈춰 버림
- 그럼 A/B/C가 동시에 사용하는 방법은?
1. 순서대로(선착순)
2. 사용시간을 쪼개서
>> 멀티 태스크 환경 : 여러 작업을 동시에 처리 가능(굉장히 짧은 시간마다 일을 처리)
내가 쓰는 동안에는 데이터를 LOCK함
* LOCK : 트랜잭션 간 상호파괴적인 행위를 막기 위한 오라클의 보호관리 메커니즘
> DML LOCK : 행단위로 발생, 무한대기, 자동으로 발생, ROW LEVEL LOCK
- DEAD LOCK : 자기들 스스로 절대 상황을 빠져나갈 수 없는 LOCK
- DEAD LOCK 해결 방법 : 유사한 작업을 같은 시점에 실행
1. 시간을 달리 한다.
2. 업무이관
* AUTO COMMIT / AUTO ROLLBACK
- AUTO COMMIT : 정상 종료일 때 발생, DDL/DCL이 사용될 때 발생
- AUTO ROLLBACK : 비 정상 종료일 때 발생
* 데이터베이스의 성질 : 동시성/무결성
- 무결성: 데이터베이스에서 잘못된 데이터는 없어야 한다
> DML을 실행할 때마다 아래와 같은 규칙들을 잘 따르고 있는지 검사해 본 후,
문제가 없을 때만 반영하면 무결하다.
EMP
사원번호 사원명 급여 부서번호
중복 X 중복 O 500~5000 (외부값 참조)
NULL X NULL X
: PRIMARY KEY : NOT NULL : CHECK : FOREIGN KEY
DEPT
부서번호 부서명
중복 X 고유한 값
NULL X
: PRIMARY KEY : UNIQUE
* 제약조건(CONSTRAINT) : 무결성을 보호해주는 보호 장치
- PRIMARY KEY : 중복 X / NULL X
- NOT NULL
- CHECK : 값 CHECK
- UNIQUE
- FOREIGN KEY : 다른 값 참조, 다른 값과 중복되면 안됨
> 제약조건은 테이블 생성 시 제약 지정 가능하고,
테이블 생성 이후에도 제약 지정이 가능하다.
> 컬럼명 자료형 [CONSTRAINT 제약명] 제약종류
>> [CONSTRAINT 제약명]은 생략 가능
테이블명_컬럼_제약의 종류 방식으로 이름을 지어놓으면
어떤 제약을 위반했는지 알기 쉬움
> 지정 안하면 자동으로 SYS_C숫자형으로 이름 지어줌
|
* FOREIGN KEY <-> PRIMARY KEY 관계
- CHILD KEY <-> PARENT KEY |
* 제약 방식
- COLUMN LEVEL 제약 방식 > COLUMN과 제약 조건을 같이 씀
SQL> CREATE TABLE DEPT1
2 (DEPTNO NUMBER(2) PRIMARY KEY,
3 DNAME VARCHAR2(20) UNIQUE,
4 LOC VARCHAR2(20));
- TABLE LEVEL 제약 방식
컬럼명 자료형,
컬럼명 자료형,
CONSTRAINT 제약명 제약종류 (컬럼명)
CONSTRAINT 제약명 제약종류 (컬럼명)
CONSTRAINT 제약명 제약종류 (컬럼명)
> COLUMN을 다 써놓은 다음 마지막에 제약을 씀
따라서, 끝에 어떤 COLUMN에 제약이 걸렸는지도 써줘야 함
SQL> CREATE TABLE EMP1
2 (EMPNO NUMBER(4) CONSTRAINT EMP1_EMPNO_PK PRIMARY KEY,
3 ENAME VARCHAR2(20) NOT NULL,
4 SAL NUMBER(7,2),
5 DEPTNO NUMBER(2),
6 CONSTRAINT EMP1_SAL_CK CHECK (SAL BETWEEN 500 AND 5000),
7 CONSTRAINT EMP1_DEPTNO_FK FOREIGN KEY (DEPTNO)
8 REFERENCES DEPT1 (DEPTNO)); > FOREIGH KEY의 참조 컬럼(PK나 UK 중 하나여야 함)
* 오류 생겼을 때 뭐가 잘못되었는지 알기 위해서는
- '데이터 딕셔너리'를 봐야 함 : USER_대상S
> 제약에 대한 정보를 볼 수 있음
SQL> DESC USER_CONSTRAINTS
- 두 개의 테이블에 대한 제약정보 확인 가능
> COL 컬럼헤딩 FORMAT A숫자로 테이블 크기 조절 가능
SQL> COL CONSTRAINT_NAME FORMAT A18
SQL> COL TABLE_NAME FORMAT A10
SQL> COL SEARCH_CONDITION FORMAT A25
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME IN ('EMP1', 'DEPT1');
- 제약 이름 / 타입(P: PRIMARY KEY, U:UNIQUE, C:CHECK, R:FOREIGN KEY) / 테이블 이름 / 컨디션
(NOT NULL은 체크제약 중 하나)
CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION
------------------ - ---------- -------------------------
SYS_C0011129 P DEPT1
SYS_C0011130 U DEPT1
SYS_C0011131 C EMP1 "ENAME" IS NOT NULL
EMP1_SAL_CK C EMP1 SAL BETWEEN 500 AND 5000
EMP1_EMPNO_PK P EMP1
EMP1_DEPTNO_FK R EMP1
- 테이블을 생성한 이후에도 제약을 추가
> DEPT1 테이블에 LOC 컬럼에 UNIQUE 제약을 추가
SQL> ALTER TABLE DEPT1
2 ADD CONSTRAINT DEPT1_LOC_UK UNIQUE (LOC);
- 제약 삭제
SQL> ALTER TABLE DEPT1
2 DROP CONSTRAINT DEPT1_LOC_UK;
* DDL
테이블을 생성하는 CREATE, 변경하는 ALTER, 삭제하는 DROP
이름 변경 RENAME, 테이블 안의 모든 행을 다 삭제 TRUNCATE,
테이블에 주석 달기 COMMENT
>> AUTO COMMIT 발생
< 실습 >
* TABLE 만들기
(예시)
MEMBER
MEMNO NUMBER(5)
ID VARCHAR2(20)
PW VARCHAR2(20)
MNAME VARCHAR(20)
MPOINT NUMBER(8)
REGDATE 날짜
ADDRS VARCHAR2(50)
MEMBER
MEMNO ID PW MNAME MPOINT REGDATE ADDRS
1111 AAA BBB 홍길동 500 23/02/27 서울강남구
2222 BBB CCC 고길동 1500 23/02/27 인천
CREATE TABLE MEMBER
(MEMNO NUMBER(5),
ID VARCHAR2(20),
PW VARCHAR2(20),
MNAME VARCHAR(20),
MPOINT NUMBER(8),
REGDATE DATE,
ADDRS VARCHAR2(50));
INSERT INTO MEMBER
VALUES (1111, 'AAA', 'BBB', '홍길동', 500, SYSDATE, '서울강남구');
INSERT INTO MEMBER
VALUES (2222, 'BBB', 'CCC', '고길동', 1500, SYSDATE, '인천');
* PW 컬럼 길이를 50자로 변경
- DDL : 테이블과 같은 구조를 정의하는 언어 > ALTER
SQL> ALTER TABLE MEMBER
2 MODIFY PW VARCHAR2(50);
* MPOINT 컬럼 삭제
SQL> ALTER TABLE MEMBER
2 DROP COLUMN MPOINT;
* 테이블 삭제
SQL> DROP TABLE MEMBER;
* 삭제된 데이터 휴지통 확인 및 되살리기
SQL> SHOW RECYCLEBIN
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
DEPT BIN$t7SEE3J0QUK/KT2KfF/CXA==$0 TABLE 2023-02-27:12:08:15
MEMBER BIN$dSNmA8v2Rd2RB5qvKWVxnA==$0 TABLE 2023-02-27:15:32:30
SQL> FLASHBACK TABLE MEMBER
2 TO BEFORE DROP;
* 테이블 이름 변경
SQL> RENAME MEMBER TO MEM;
< 실습 2 >
* 테이블 안의 모든 행을 삭제
- DELETE FROM MEM;
- TRUNCATE TABLE MEM;
> DELETE: DML / TRUNCATE : DDL
> DDL, DCL을 수행 ==> AUTO COMMIT이 발생
>> 따라서, TRUNCATE를 수행하면 AUTO COMMIT이 발생하여 ROLLBACK 불가!
DELETE는 DML이기 때문에 ROLLBACK 가능
>> 그렇기 때문에 지우는 시간은 TRUNCATE가 더 빠름
>> 단, TRUNCATE는 일부만 지우는 것이 안됨!
* 테이블에 주석 주기
SQL> COMMENT ON TABLE MEM
2 IS '회원테이블 입니다.';
> 주석은 DESC로 확인할 수 없음
* 테이블의 주석 확인하는 법 : USER_TAB_COMMENTS
SQL> SELECT *
2 FROM USER_TAB_COMMENTS;
> 깔끔하게 보려면
SQL> COL COMMENTS FORMAT A25
SQL> SELECT *
2 FROM USER_TAB_COMMENTS;
| * 'SQL>'을 '서울A>'로 변경 SQL> SET SQLPROMPT "서울A>" |
++ 응용 문제
1.그림1처럼 TABLE을 생성 하는 SQL문을 작성하시오.
CREATE TABLE CUST
(CUST_ID NUMBER(6),
CUST_GENDER NUMBER,
CUST_NAME VARCHAR2(10));
2.그림2처럼 데이터를 추가 하는 SQL문을 작성하시오.
INSERT INTO CUST
VALUES (1, 1, '이창길');
INSERT INTO CUST
VALUES (2, 0, '이지연');
INSERT INTO CUST
VALUES (3, 1, NULL);
INSERT INTO CUST
VALUES (4, 0, '이나영');
INSERT INTO CUST
VALUES (5, 1, '김경숙');
3.CUST 테이블에 GRADE 컬럼을 추가 하는 SQL문을 작성하시오
(단 GRADE 는 숫자2자리)
ALTER TABLE CUST
ADD GRADE NUMBER(2);
4.GRADE 컬럼에 TYPE을 숫자 4자리로 변경 시키는 SQL문을 작성하시오
ALTER TABLE CUST
MODIFY GRADE NUMBER(4);
5.CUST_ID 컬럼에 PRIMARY KEY 제약을 추가시키시오 (단 제약명은 CUST_ID_PK )
ALTER TABLE CUST
ADD CONSTRAINT CUST_ID_PK PRIMARY KEY (CUST_ID);
6.CUST 테이블을 복사하여 CUSTOMER 테이블을 생성
CREATE TABLE CUSTOMER
AS
SELECT * FROM CUST;
7.그림3 처럼 정보를 확인할수 있는 있는 SQL문을 작성하시오.
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'CUST';
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
CUST_ID_PK P CUST
8. BONUS테이블에 EMPNO 컬럼추가
(혹 테이블이 없다면, 만들어서 실습할 것)
ALTER TABLE BONUS
ADD EMPNO NUMBER(4);
9. EMPNO 컬럼에 PK 제약 추가
ALTER TABLE BONUS
ADD CONSTRAINT BONUS_EMPNO_PK PRIMARY KEY (EMPNO);
10. ENAME 컬럼에 UK 제약 추가
ALTER TABLE BONUS
ADD CONSTRAINT BONUS_ENAME_UK UNIQUE (ENAME);
11. SAL 500~5000 사이의 값만 올수 있다 (CHECK 제약 추가)
ALTER TABLE BONUS
ADD CONSTRAINT BONUS_SAL_CK CHECK (SAL BETWEEN 500 AND 5000);
12. 제약이 잘 설정되었는지 데이터 딕셔너리를 조회
COL CONSTRAINT_NAME FORMAT A18
COL TABLE_NAME FORMAT A10
COL SEARCH_CONDITION FORMAT A25
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('BONUS');
CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION
------------------ - ---------- -------------------------
BONUS_EMPNO_PK P BONUS
BONUS_ENAME_UK U BONUS
BONUS_SAL_CK C BONUS SAL BETWEEN 500 AND 5000
13. 샘플데이터 추가해보기 (제약조건을 만족하게 데이터 INSERT 하기, 5개)
INSERT INTO BONUS
VALUES ('AAA', 'BBB', 700, 100, 10);
INSERT INTO BONUS
VALUES ('BBB', 'CCC', 800, 150, 20);
INSERT INTO BONUS
VALUES ('CCC', 'DDD', 900, 200, 30);
INSERT INTO BONUS
VALUES ('DDD', 'EEE', 1000, 250, 40);
INSERT INTO BONUS
VALUES ('EEE', 'FFF', 1100, 300, 50);
ENAME JOB SAL COMM EMPNO
---------- --------- ---------- ---------- ----------
AAA BBB 700 100 10
BBB CCC 800 150 20
CCC DDD 900 200 30
DDD EEE 1000 250 40
EEE FFF 1100 300 50
SQL> DESC BONUS
이름
-----------------
ENAME
JOB
SAL
COMM
EMPNO <-- NUMBER(4)
그림1)
SQL> desc cust
Name Null? Type
----------------------------------------- -------- ----------------------
CUST_ID NUMBER(6)
CUST_GENDER NUMBER
CUST_NAME VARCHAR2(10)
그림2)
SQL> select * from cust;
CUST_ID CUST_GENDER CUST_NAME
---------- ----------- ----------
1 1 이창길
2 0 이지연
3 1
4 0 이나영
5 1 김경숙
그림3)
SQL> /
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
CUST_ID_PK P NCUST