멋쟁이 사자처럼 22회차 ( 03 /31 )

 

[학습 목표]

1. 테이블 생성 시 다양한 제약 조건 (Primary Key , Unique Check, Default , Foreign Key)을 선언하고 적용할 수 있다. 2. INSERT. SELECT UPDATE , DELETE 문을 활용하여 데이터베이스의 기본적인 CRUD 작업을 수행할 수 있다.

 

---

primary key , unique key , check, default , foreign key

1) primary key 

행의 고유 식별자 역할을 한다. 중복이 불가하며 null도 불가하다. 한 테이블에 하나만 설정 가능하다. ( 단일 또는 복합 가능)

 

[사용 예]

-- 컬럼에 직접 선언

컬럼명 데이터타입 PRIMARY KEY

 

-- (복합키 가능)

PRIMARY KEY (컬럼명 1, 컬럼명 2)

CREATE TABLE STUDENTS02 (
STUDENT_ID INT  ,
STDUENT_NUMBER VARCHAR(10) ,
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50) ,
MIDDLE_NAME VARCHAR(50) ,
BIRTHDAY VARCHAR(50) ,
GENDER ENUM('M','F'),
PAID_FLAG bool,
PRIMARY KEY( STUDENT_ID)
);

2) unique key

중복을 허용하지 않지만 null은 허용한다. 한 테이블에 여러 개 존재 가능하고 식별이 아닌 고유 데이터를 보장한다.

 

[사용 예]

-- 컬럼에 직접 선언

컬럼명 데이터타입 UNIQUE

 

-- (복합 유니크 가능)

UNIQUE (컬럼명 1, 컬럼명 2)

 CREATE TABLE STUDENTS08 (
 STUDENT_ID INT,
 STUDENT_NUMBER VARCHAR(10),
 BIRTHDAY DATE,
 UNIQUE KEY ( STUDENT_ID),
 );

3) check

특정 컬럼 값의 범위 또는 조건 제한한다. 

 

[사용 예]

-- 컬럼에 직접 선언

컬럼명 데이터타입 CHECK (조건식)

 

-- 또는 테이블 레벨에서 선언

CHECK (컬럼명 BETWEEN 1 AND 100)

 CREATE TABLE STUDENTS08 (
 STUDENT_ID INT,
 STUDENT_NUMBER VARCHAR(10),
 BIRTHDAY DATE,
 UNIQUE KEY ( STUDENT_ID),
 CHECK(BIRTHDAY >= '2000-01-01') -- 범위 지정 
 );

4) default 

컬럼에 값이 입력되지 않을 경우 기본값 지정한다. 

 

5) foreign key 

다른 테이블의 기준 키 (Primary key ) 또는 Unique key를 참조한다. 

데이터 무결성(참조 관계) 보장한다. 또한 삭제 / 수정 시 동작 방식 지정이 가능하다 ( ON DELETE , ON UPDATE)

 

[사용 예]

FOREIGN KEY (자신의컬럼명)

REFERENCES 참조테이블(참조컬럼명)

 -- CREATE TABLE에서 외래키 (FOREIGN KEY) 제약 조건 생성 
 DROP TABLE STUDENTS06;
 CREATE TABLE STUDENTS06 (
STUDENT_ID INT AUTO_INCREMENT,
STUDENT_NUMBER VARCHAR(10) DEFAULT 'ABC' ,
PRIMARY KEY( STUDENT_ID, STUDENT_NUMBER)

);

INSERT INTO STUDENTS06 VALUES(1,1);
INSERT INTO STUDENTS06 VALUES(2,2);


-- 현재 STUDENT_MY 테이블의 STUDENT_ID를 STUDENTS05의 STUDNET_ID로 참조 시키자. 
CREATE TABLE STUDENT_MY(
STUDENT_ID INT,
FOREIGN KEY(STUDENT_ID) REFERENCES STUDENTS06(STUDENT_ID));

 

---

기본적인 CRUD와 트랜잭션 

1) crud

Create (삽입) : INSERT INTO 테이블명 (열1, 열2, ...) VALUES (값1, 값2, ...);
Read (조회) :  SELECT 열1, 열2 FROM 테이블명 [WHERE 조건];
Update (수정) : UPDATE 테이블명 SET 열1 = 값1, 열2 = 값2 WHERE 조건;
Delete (삭제) : DELETE FROM 테이블명 WHERE 조건;

 

2) 트랜잭션 제어 명령어

여기서 트랜잭션이란?

동시성 제어로 동시에 여러 트랜잭션이 접근을 하게 되면 충돌이 발생할 수 있으므로 서버는 LOCK이나 격리수준 관리를 진행해야 한다. 

 

START TRANSACTION; 트랜잭션 시작 (명시적으로 시작하고 싶을 때 사용)
COMMIT; 트랜잭션 완료 후 변경 사항을 영구 저장
ROLLBACK; 트랜잭션 내의 모든 변경 사항을 취소하고 되돌림
SAVEPOINT 포인트명; 중간 저장점 지정 – ROLLBACK TO SAVEPOINT로 일부 취소 가능
ROLLBACK TO SAVEPOINT 포인트명; 특정 지점까지 롤백

 

3) 트랜잭션 특징 (ACID 원칙 요약)

Atomicity (원자성) : 모든 작업이 모두 수행되거나, 하나도 수행되지 않아야 함
Consistency (일관성) : 트랜잭션 완료 후에도 데이터의 일관성 유지
Isolation (고립성) :트랜잭션끼리 서로 간섭하지 않아야 함
Durability (지속성) :COMMIT 후 변경 내용은 영구 저장되어야 함

 

-- SAVEPOINT 포인트명; 중간 저장점 지정 - FOLLBACK TO SAVEPOINT 로 일부 취소 가능 
-- ROLLBACK TO SAVEPOINT 포인트명; 특정 지점까지 롤백 
DROP TABLE TEST;
CREATE TABLE TEST(
ID INT, 
NAME  VARCHAR(20));

START TRANSACTION;
-- 데이터 추가 
INSERT INTO TEST VALUES(1,'A');
INSERT INTO TEST VALUES(2,'B');

-- 중간 저장 지점 설정 
SAVEPOINT SP1;
INSERT INTO TEST VALUE(3,'C');
INSERT INTO TEST VALUE(4,'D');

-- 현재 상태 확인 
SELECT * FROM TEST;

-- ROLLBACK TO 를 사용해서 1,2를 찾아서 되돌리자. 
-- 3,4는 롤백되었음 = 책갈피 이후의 작업만 취소한다.
ROLLBACK TO SP1;
SELECT * FROM TEST;
COMMIT;