TCL(Transaction Control Language) : 트랜잭션 제어 언어
TRANSACTION이란?- 데이터베이스의 논리적 연산 단위★
- 데이터 변경사항(DML)을 묶어 하나의 트랜잭션에 담아서 처리함.
- 트랜잭션의 대상이 되는 데이터 변경 사항 : INSERT, UPDATE, DELETE (DML)
1) COMMIT : 메모리버퍼(트랜잭션)에 임시 저장된 데이터 변경사항을 DB에 반영
2) ROLLBACK : 메모리버퍼(트랜잭션)에 임시 저장된 데이터 변경사항을 삭제하고 마지막 COMMIT 상태로 돌아감 (== 트랜잭션 내용삭제)
3) SAVEPOINT : 메모리버퍼(트랜잭션)에 저장지점을 정의하여 ROLLBACK 수행 시 전체작업을 삭제하는 것이 아닌 저장 지점까지만 일부 ROLLBACK
DDL(DATA DEFINITION LANGUAGE) : 데이터 정의 언어란?
객체(OBJECT)를 만들고(CREATE), 수정(ALTER)하고, 삭제(DROP)등데이터의 전체구조를 정의하는 언어로 주로 DB관리자, 설계자가 사용함
오라클에서의 객체 : 테이블(TABLE), 뷰(VIEW), 시퀀스(SEQUENCE),인덱스(INDEX), 패키지(PACKAGE), 트리거(TRIGGER), 프로시져(PROCEDURE),함수(FUNCTION), 동의어(SYNONYM), 사용자(USER)
- 데이터 딕셔너리란?자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블데이터 딕셔너리는 사용자가 테이블을 생성하거나 사용자를 변경하는 등의작업을 할 때 데이터베이스 서버에 의해 자동으로 갱신되는 테이블
★CREATE
- 테이블이나 인덱스, 뷰 등 다양한 데이터베이스 객체를 생성하는 구문
- 테이블로 생성된 객체는 DROP구문을 통해 제거할 수 있음
EX)DROP TABLE USER_TEST;
1. 테이블 생성하기
- 테이블이란?
- 행(ROW)과 열(COLUMN)으로 구성되는 가장 기본적인 데이터베이스 객체
- 데이터베이스 내에서 모든 데이터는 테이블을 통해 저장된다.
[표현식]
CREATE TABLE 테이블명 (
컬럼명 자료형(크기),
컬럼명 자료형(크기),
....);
* 1. 자료형
NUMBER : 숫자형(정수, 실수)
CHAR(크기) : "고정"길이 문자형 (2000BYTE)
VARCHAR2(크기) : "가변"길이 문자형(4000BYTE)
NVARCHAR2(문자개수) : "가변" 길이 문자형 (4000BYTE)
- BYTE크기 단위가 아닌 글자수(문자개수)로 크기를 지정하는 타입
예시를 통해 알아보자!
CREATE TABLE "MEMBER"(
MEMBER_ID VARCHAR2(20BYTE), -- ID는 20BYTE까지
MEMBER_PW VARCHAR2(20BYTE), --PW는 20BYTE까지
MEMBER_NAME VARCHAR2(15BYTE), --NAME은 15BYTE까지
MEMBER_SSN CHAR(14BYTE), --SSN(주민번호) 14BYTE까지!!
ENROLL_DATE DATE DEFAULT CURRENT_DATE -- 기본값 설정
);
* 2. 컬럼에 주석달기!
COMMENT ON COLUMN 테이블명.컬럼명 IS '주석내용';
예)
COMMENT ON COLUMN "MEMBER".MEMBER_ID IS '회원 아이디';
COMMENT ON COLUMN "MEMBER".MEMBER_PW IS '회원 비밀번호';
COMMENT ON COLUMN "MEMBER".MEMBER_NAME IS '회원 이름';
COMMENT ON COLUMN "MEMBER".MEMBER_SSN IS '회원 주민등록번호';
COMMENT ON COLUMN "MEMBER".ENROLL_DATE IS '회원 가입일';
* 3. 샘플 데이터 삽입
INSERT INTO "MEMBER"
VALUES('MEM01', '123ABC', '홍길동',
'990102-1234567', CURRENT_DATE);
-- 아이디, 비밀번호, 이름, 주민등록번호, 가입일
제약조건(CONSTRAINTS)
사용자가 원하는 조건의 데이터만 유지하기 위해서 특정 컬럼에 설정하는 제약.
데이터 무결성 보장을 목적으로함.
-> 무결성보장 == 데이터의 신뢰도 확보
제약조건 종류
1) NOT NULL
2) UNIQUE
3) PRIMARY KEY
4) FOREIGN KEY
5) CHECK
1. NOT NULL
-- 해당컬럼에 반드시 값이 기록되어야 하는 경우 사용
-- 삽입/수정시 NULL 값을 허용하지 않도록 컬럼레벨에서 제한
-- 컬럼레벨 : 테이블 생성 시 생성할 컬럼의 정보를 작성하는 부분
2. UNIQUE 제약조건
-- 컬럼의 입력값에 대해서 "중복을 제한"하는 제약조건
-- 컬럼레벨에서 설정가능, 테이블레벨에서 설정가능
-- 단, UNIQUE 제약조건이 설정된 컴럼에 NULL 값은 중복 삽입가능
-- UNIQUE 복합키
-- 두개 이상의 컬럼을 묶어서 하나의 UNIQUE 제약조건을 설정함
--> 복합키 설정은 오직 "테이블 레벨" 에서만 가능!!!
3. PRIMARY KEY(기본키) 제약조건
-- 테이블에서 한 행의 정보를 찾기위해 사용할 컬럼을 의미함
-- 테이블에 대한 식별자 역할을 함
-- NOT NULL + UNIQUE 제약조건의 의미
-- 한 테이블당 한 개만 설정할 수 있음
-- 컬럼레벨, 테이블레벨 둘다 설정 가능함
-- 한개 컬럼에 설정할 수도 있고, 여러개의 컬럼을 묶어서 설정할 수 있음
--> 복합키 가능
4. FOREIGN KEY(외부키/외래키) 제약조건
-- 참조(REFERENCES)된 다른 테이블의 컬럼이 제공하는 값만 사용할 수 있음
-- FOREIGN KEY제약 조건에 의해서 테이블간의 관계가 형성됨
-- 제고오디는 값 외에는 NULL을 사용할 수있음
-- 컬럼레벨일 경우
-- 컬럼명 자료형(크기) [CONSTRAINT 이름]
-- REFERENCES 참조할 테이블명 [(참조할컬럼)] [삭제룰]
-- 테이블레벨일 경우
-- [CONSTRAINT 이름] FOREIGN KEY (적용할컬럼명)
-- REFERENCES 참조할테이블명 [(참조할컬럼)] [삭제룰]
-- * 참조될 수 있는 컬럼은 PRIMARY KEY컬럼과, UNIQUE 지정된 컬럼만
-- 외래키로 사용할 수 있음
-- 참조할 테이블의 참조할 컬럼명이 생략되면
-- PRIMARY KEY로 설정된 컬럼이 자동참조할 컬럼이됨
*FOREIGN KEY 삭제옵션
-- 부모테이블의 데이터 삭제시 자식테이블의 데이터를 어떤식으로 처리할지에 대한 내용을 설정할 수 있음
1) ON DELETE RESTRICTED(삭제 제한)로 기본지정되어 있음
-- FOREIGN KEY로 지정된 커럼에서 사용되고 있는 값일경우
-- 제공하는 컬럼의 값은 삭제하지 못함
--> 자식 테이블 참조하고 있는 값은 부모테이블에서 삭제할 수 없다!!!
2) ON DELETE SET NULL : 부모키 삭제시 자식키를 NULL로 변경하는 옵션
-- ON DELETE SET NUL 삭제 옵션이 적용된 테이블 생성
CREATE TABLE USER_USED_FK2(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
GENDER VARCHAR2(10),
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50),
-- 컬럼 레벨로 FK 제약조건 작성
--> 컬럼 레벨에서는 FOREIGN KEY 단어를 사용하지 않음!!!!!
GRADE_CODE NUMBER
CONSTRAINT GRADE_CODE_FK2
REFERENCES USER_GRADE2 (GRADE_CODE)
ON DELETE SET NULL -- 삭제 옵션 추가
-- 3) ON DELETE CASCADE : 부모키 삭제시 자식키도 함께 삭제됨
-- 부모키 삭제시 값을 사용하는 자식 테이블의 컬럼에 해당하는 행이 삭제가 됨
5번 - CHECK!!!!!!!!!!!!!!!!!
-- 5. CHECK 제약조건 : 컬럼에 기록되는 값에 조건 설정을 할 수 있음
-- CHECK (컬럼명 비교연산자 비교값)
-- 주의 : 비교값은 리터럴만 사용할 수 있음, 변하는 값이나 함수 사용 못함
CREATE TABLE USER_USED_CHECK(
USER_NO NUMBER PRIMARY KEY,
USER_ID VARCHAR2(20) UNIQUE,
USER_PWD VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(30),
-- 컬럼 레벨 설정
GENDER VARCHAR2(10)
CONSTRAINT GENDER_CHECK
CHECK(GENDER IN ('남', '여')),
--> GENDER 컬럼에 저장되는 값이 '남' 또는 '여' 중 하나인지 검사
PHONE VARCHAR2(30),
EMAIL VARCHAR2(50)
);
INSERT INTO USER_USED_CHECK
VALUES(1, 'user01', 'pass01', '홍길동', '남', '010-1234-5678', 'hong123@kh.or.kr');
INSERT INTO USER_USED_CHECK
VALUES(2, 'user02', 'pass02', '홍길동', '남자', '010-1234-5678', 'hong123@kh.or.kr');
-- GENDER 컬럼에 CHECK 제약조건으로 '남' 또는 '여'만 기록 가능한데 '남자'라는 조건 이외의 값이 들어와 에러 발생
-- ORA-02290: 체크 제약조건(KH_HYK.GENDER_CHECK)이 위배되었습니다
-- CHECK 제약 조건은 범위로도 설정 가능.
----------------------------------------------------------------------------------------------------------------
-- [연습 문제]
-- 회원가입용 테이블 생성(USER_TEST)
-- 컬럼명 : USER_NO(회원번호) - 기본키(PK_USER_TEST),
-- USER_ID(회원아이디) - 중복금지(UK_USER_ID),
-- USER_PWD(회원비밀번호) - NULL값 허용안함(NN_USER_PWD),
-- PNO(주민등록번호) - 중복금지(UK_PNO), NULL 허용안함(NN_PNO),
-- GENDER(성별) - '남' 혹은 '여'로 입력(CK_GENDER),
-- PHONE(연락처),
-- ADDRESS(주소),
-- STATUS(탈퇴여부) - NOT NULL(NN_STATUS), 'Y' 혹은 'N'으로 입력(CK_STATUS)
-- 각 컬럼의 제약조건에 이름 부여할 것
-- 5명 이상 INSERT할 것
-- NOT NULL은 컬럼명에만 쓸 수 있다.@@@@@@@@@@@@@@@@@@@@@@
/* 테이블 잘못 만들었을 경우 삭제 구문 수행!! */
DROP TABLE USER_TEST;
CREATE TABLE USER_TEST(
USER_NO NUMBER,
USER_ID VARCHAR2(20),
USER_PWD VARCHAR2(20)
-- NOT NULL은 컬럼 레벨만 가능!
CONSTRAINT NN_USER_PWD NOT NULL,
PNO CHAR(14)
CONSTRAINT NN_PNO NOT NULL ,
GENDER CHAR(3),
PHONE CHAR(13),
ADDRESS VARCHAR2(300),
STATUS CHAR(1)
CONSTRAINT NN_STATUS NOT NULL,
-- 테이블 레벨
CONSTRAINT PK_USER_TEST PRIMARY KEY(USER_NO), -- PK
CONSTRAINT UK_USER_ID UNIQUE(USER_ID),
CONSTRAINT UK_PNO UNIQUE(PNO),
CONSTRAINT CK_GENDER CHECK(GENDER IN ('남', '여')),
CONSTRAINT CK_STATUS CHECK(STATUS IN ('Y', 'N'))
);
COMMENT ON COLUMN USER_TEST.USER_NO IS '회원번호';
COMMENT ON COLUMN USER_TEST.USER_ID IS '회원아이디';
COMMENT ON COLUMN USER_TEST.USER_PWD IS '비밀번호';
COMMENT ON COLUMN USER_TEST.PNO IS '주민등록번호';
COMMENT ON COLUMN USER_TEST.GENDER IS '성별';
COMMENT ON COLUMN USER_TEST.PHONE IS '연락처';
COMMENT ON COLUMN USER_TEST.ADDRESS IS '주소';
COMMENT ON COLUMN USER_TEST.STATUS IS '탈퇴여부';
INSERT INTO USER_TEST
VALUES(1, 'user01', 'pass01', '880122-1234567', '남', '010-1234-1234', NULL, 'N');
INSERT INTO USER_TEST
VALUES (2, 'user02', 'pass02', '890222-2234567', '여', '010-2222-9999', '서울시 강남구 삼성동', 'N');
INSERT INTO USER_TEST
VALUES (3, 'user03', 'pass03', '900322-2234567', '여', '010-3333-9999', '서울시 강남구 청담동', 'Y');
INSERT INTO USER_TEST
VALUES (4, 'user04', 'pass04', '910422-1234567', '남', '010-4444-9999', '서울시 강남구 도곡동', 'N');
INSERT INTO USER_TEST
VALUES (5, 'user05', 'pass05', '920522-2234567', '여', '010-5555-9999', '서울시 강남구 대치동', 'N');
SELECT * FROM USER_TEST;
----------------------------------------------------------------------------------------------------------------
-- 8. SUBQUERY를 이용한 테이블 생성
-- 컬럼명, 데이터 타입, 값이 복사되고, 제약조건은 NOT NULL 만 복사됨
-- 1) 테이블 전체 복사
CREATE TABLE EMPLOYEE_COPY
AS (SELECT * FROM EMPLOYEE);
SELECT * FROM EMPLOYEE_COPY;
-- 2) JOIN 후 원하는 컬럼만 테이블로 복사
CREATE TABLE EMPLOYEE_COPY2
AS SELECT
EMP_NAME,
NVL(DEPT_TITLE, '부서없음') AS DEPT_TITLE,
JOB_NAME
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
ORDER BY JOB_CODE ASC;
SELECT * FROM EMPLOYEE_COPY2;
-- 9. 제약조건 추가
-- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명)
-- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명]
-- FOREIGN KEY(컬럼명) REFERENCES 참조 테이블명(참조컬럼명)
--> 참조 테이블의 PK를 기본키를 FK로 사용하는 경우 참조컬럼명 생략 가능
-- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] UNIQUE(컬럼명)
-- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] CHECK(컬럼명 비교연산자 비교값)
-- ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;
-- 테이블 제약 조건 확인
SELECT *
FROM USER_CONSTRAINTS C1
JOIN USER_CONS_COLUMNS C2 USING(CONSTRAINT_NAME)
WHERE C1.TABLE_NAME = 'EMPLOYEE_COPY';
-- NOT NULL 제약 조건만 복사된 EMPLOYEE_COPY 테이블에
-- EMP_ID 컬럼에 PRIMARY KEY 제약조건 추가
ALTER TABLE EMPLOYEE_COPY ADD CONSTRAINT PK_EMP_COPY PRIMARY KEY(EMP_ID);
-- ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명)
-- * 수업시간에 활용하던 테이블에는 FK 제약조건 없는상태이므로 추가!!
/*
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명]
FOREIGN KEY(컬럼명) REFERENCES 참조 테이블명(참조컬럼명)
*/
-- EMPLOYEE테이블의 DEPT_CODE에 외래키 제약조건 추가
-- 참조 테이블은 DEPARTMENT, 참조 컬럼은 DEPARTMENT의 기본키
ALTER TABLE EMPLOYEE
ADD CONSTRAINT DEPT_CODE_FK
FOREIGN KEY(DEPT_CODE)
REFERENCES DEPARTMENT; -- 컬럼명 미작성 시 자동으로 PK 컬럼 참조
-- == DEPARTMENT(DEPT_ID)
-- EMPLOYEE테이블의 JOB_CODE 외래키 제약조건 추가
-- 참조 테이블은 JOB, 참조 컬럼은 JOB의 기본키
ALTER TABLE EMPLOYEE
ADD CONSTRAINT JOB_CODE_FK
FOREIGN KEY(JOB_CODE)
REFERENCES JOB;
-- EMPLOYEE테이블의 SAL_LEVEL 외래키 제약조건 추가
-- 참조 테이블은 SAL_GRADE, 참조 컬럼은 SAL_GRADE의 기본키
ALTER TABLE EMPLOYEE
ADD CONSTRAINT SAL_LEVEL_FK
FOREIGN KEY(SAL_LEVEL)
REFERENCES SAL_GRADE;
-- DEPARTMENT테이블의 LOCATION_ID에 외래키 제약조건 추가
-- 참조 테이블은 LOCATION, 참조 컬럼은 LOCATION의 기본키
ALTER TABLE DEPARTMENT
ADD CONSTRAINT LOCATION_ID_FK
FOREIGN KEY(LOCATION_ID)
REFERENCES LOCATION;
-- LOCATION테이블의 NATIONAL_CODE에 외래키 제약조건 추가
-- 참조 테이블은 NATIONAL, 참조 컬럼은 NATIONAL의 기본키
ALTER TABLE LOCATION
ADD CONSTRAINT NATIONAL_CODE_FK
FOREIGN KEY(NATIONAL_CODE)
REFERENCES NATIONAL;