스터디/KAKAOCLOUDSCHOOL

[DB-MariaDB] 개발자 지망생 스터디 - 16일차

shineIT 2022. 11. 23. 11:03

DDL (Data Definition Language)

  • 데이터 구조를 생성하고 변경하고 삭제하는 명령어

1. 테이블 생성

1) 기본형식

Create [Temporary] Table 테이블이름(컬럼이름 자료형 [컬럼 제약 조건], ... [테이블 제약 조건])조건나열;

2) 자료형

  • 숫자 : TINYINT(1Byte - true/false), INT(INTEGER), FLOAT, DOUBLE
  • 문자 : CHAR(길이 - 길이가 고정), VARCHAR(길이 - 길이가 가변), TEXT(긴 문자열), BLOB(파일의 내용 저장)
  • 날짜 : DATE(날짜), DATETIME(날짜와 시간), TIMESTAMP(날짜와 시간 - 2037년 까지 밖에 저장하지 못함), TIME(시간), YEAR(년도)
  • 기타 : JSON, GEOMETRY(공간 정보)

3) 조건 나열

  • ENGINE : MyISAM(Indexed Sequential Access Media - 삽입,삭제, 갱신에 불리하고, 조회에 유리함) 이나 InnoDB(삽입, 삭제, 갱신에 유리)를 설정할 수 있음
  • DEFAULT CHARSET : 한글이 깨지는 경우 한글 설정하는 옵션으로 utf8을 설정해주면 되는데 MariaDB는 기본이 utf8
  • auto_increament = 시작숫자 : 일련번호를 사용할 때 시작 숫자부터 시작함
  • Timezone 설정 : Mac에서 사용할 때 시간 대역이 안맞아서 설정해주어야 하는 경우가 있음

이어서

4) 테이블 생성

  • 테이블 이름 : contact

컬럼

    num - 정수

    name - 문자열이고 영문 20자까지  저장하고 자주 변경

    address - 문자열이고 영문 100자까지 저장하고 자주 변경되지 않음

    tel - 문자열로 영문 20자까지 저장하고 자주 변경됨

    email - 문자열로 영문 100자까지 저장하고 자주 변경됨

    birthday - 날짜

 

비밀번호를 저장할 때는 대부분의 경우 해시를 수행해서 해시코드를 저장하기 때문에 해시 방법에 따라 64, 128, 256으로 설정해야 함.

create table contact(
	num integer,
    name char(20),
    address varchar(100),
    tel char(20),
    email char(100),
    birthday date
)ENGINE=MyISAM;

 

2. 테이블 구조 변경

1) 기본 형식

  • ALTER TABLE 테이블 이름 작업 매개변수 나열

2) 테이블 구조 확인

  • DESC 컬럼이름

3) 컬럼 추가

  • ALTER TABLE 테이블 이름 ADD 컬럼이름 자료형 제약조건;
  • contact 테이블에 age라는 컬럼을 정수로 추가
  • 컬럼을 추가하는 경우 기존에 데이터가 존재했다면 모두 null로 삽입됨
ALTER TABLE contact ADD age integer;

4) 컬럼 삭제

  •  ALTER TABLE 테이블이름 DROP 컬럼이름;
ALTER TABLE contact DROP age;

5) 컬럼 변경

  • 이름과 자료형 변경
    • ALTER TABLE 테이블이름 CHANGE 기존컬럼이름 새로운컬럼이름 자료형 제약조건;
  • 자료형만 변경하는 경우 - NOT NULL에 대한 설정 포함
    • 크기가 변경되는 경우 기존의 크기보다 커지는 것은 아무런 문제가 없지만 작아지는 것은 데이터에 손실을 발생할 수 있어 주의
    • ALTER TABLE 테이블이름 MODIFY 기존컬럼이름 자료형;
  • contact 테이블에서 tel 이라는 컬럼을 phone 이라는 컬럼으로 변경
ALTER TABLE contact CHANGE tel phone varchar(11);

6) 컬럼 순서 조정

  • 새로운 컬럼이 추가되면 맨 뒤에 추가
  • 컬럼을 맨 앞으로 이동
    • ALTER TABLE 테이블이름 MODIFY COLUMN 컬럼이름 자료형 FIRST;
  • 컬럼을 특정 컬럼 뒤로 이동
    • ALTER TABLE 테이블이름 MODIFY COLUMN 컬럼이름 자료형 AFTER 앞에 있을 컬럼이름;

 

7) 테이블 이름 수정

  • ALTER TABLE 원래테이블이름 RENAME 새로운테이블이름;

 

3. 테이블 삭제

1) 기본 형식

  • DROP TABLE 테이블이름;

2) 테이블이 삭제가 되지 않는 경우

  • 외래키로 참조되는 테이블은 외래키를 소유하고 있는 테이블이 먼저 삭제되어야 함

3) contact 테이블 삭제

DROP TABLE contact;

 

4. 테이블의 모든 데이터 삭제

  • TRUNCATE TABLE 테이블이름;

 

5. 테이블 압축

  • CREATE TABLE 다음에 ROW_FORMAT = COMPRESSED 옵션을 추가하면 테이블을 압축해서 생성됨
  • 저장공간은 줄일수 있지만 작업 속도는 느려짐
  • 과거에는 사용했으나 현재는 잘 사용하지 않음

 

6. 주석 설정

  • COMMENT ON TABLE 테이블이름 IS '주석';

 

7. 제약조건(Constraint)

1) 무결성 제약 조건

  • Entity Intergrity (개체 무결성) : 기본키는 NULL 이거나 중복될 수 없음
  • Referential Integrity (참조 무결성) : 외래키는 참조할 수 있는 값이나 NULL을 가져야 함
  • Domain Integrity (도메인 무결성) : 속성의 값은 정해진 도메인의 값을 가져야 함

2) NOT NULL

  • NULL 일 수 없다라는 제약 조건
  • 필수 입력
  • 컬럼의 크기와 관련이 있기 때문에 컬럼을 만들 때 제약 조건을 설정해야 함
    • 테이블 제약조건으로 만들 수 없음
    • 컬럼이름 자료형 NOT NULL 의 형태로 설정
  • 기본은 NULL을 허용하는 것
CREATE TABLE tNullable(
	name CHAR(10) NOT NULL,
	age INTEGER
);

INSERT INTO tNullable(name, age) values('itoriginal', 29);
INSERT INTO tNullable(name) values('adam');
INSERT INTO tNullable(age) values(29); -- ERROR : name은 필수

3) DEFAULT

  • 데이터베이스 이론에서는 DEFAULT는 제약조건이 아님
  • 입력하지 않았을 때 기본적으로 삽입되는 데이터
  • DEFAULT 값의 형태로 지정
  • 숫자는 0 문자열의 경우는 "이나 'N/A' 등을 많이 설정하고 날짜의 경우는 현재 시간(CURRENT_TIMESTAMP 나 NOW 등)을 많이 사용

4) CHECK

  • 값의 종류나 범위를 제한하기 위한 제약조건
  • 설정방법
    • CHECK(컬럼이름 조건);
  • GENDER 컬럼은 문자 3자인데 남 또는 여만 가져야 하는 경우
GENDER CHAR(3) CHECK(GENDER IN('남', '여'))
  • SCORE 컬럼은 정수인데 0 ~ 100까지의 값만 가져야 하는 경우
SCORE INTEGER CHECK(SCORE BETWEEN 0 AND 100)

5) PRIMARY KEY (기본키)

  • 테이블에서 PRIMARY KEY는 한 번만 설정 가능
  • 2개 이상의 컬럼으로 PRIMARY KEY(복합키)를 설정하는 경우는 테이블 제약조건으로 설정해야함
    • 학습할 때는 복합키를 거의 사용하지 않지만 실무에서 복합키를 사용하는 경우가 종종 발생 함
  • MEMBER 테이블에서 ID를 PRIMARY KEY로 설정
#컬럼 제약 조건으로 설정
CREATE TABLE MEMBER(
	ID VARCHAR(50) PRIMARY KEY
);

#테이블 제약 조건으로 설정
CREATE TABLE MEMBER(
	ID VARCHAR(50),
    PRIMARY KEY(ID)
);
  • MEMBER 테이블에서 ID 와 NAME 합쳐서 PRIMARY KEY로 설정
#PRIMARY KEY 1번만 사용할 수 있음
CREATE TABLE MEMBER(
	ID VARCHAR(50),
    NAME VARCHAR(50),
    ...
    PRIMARY KEY(ID, NAME)
);
  • PRIMARY KEY는 자동으로 클러스터 인덱스(저장순서대로 만들어지는 인덱스 - 하나만 생성)를 생성
    • PRIMARY KEY를 이용해서 조회할 때 가장 빠른 성능을 나타 냄
    • PRIMARY KEY를 설정하는 것은 중요한 작업 중의 하나임
  • PRIMARY KEY는 NOT NULL 이고 UNIQUE

6) UNIQUE

  • 중복 값을 가질수 없도록 하는 제약 조건
  • NULL을 허용
  • 인덱스를 생성하는데 PRIMARY KEY가 없으면 UNIQUE가 클러스터 인덱스가 되고 PRIMARY KEY가 있으면 보조 인덱스가 됨
  • PRIMARY KEY와 더불어 다른 테이블에서의 FOREIGN KEY가 될 수 있음
    시험에서는 PRIMARY KEY 만 FOREIGN KEY가 될 수 있다고 함

7) 제약조건 이름 설정

  • 제약조건을 설정할 때 앞에 CONSTRAINT 제약조건이름을 추가하면 제약조건 이름이 생성 됨
  • 일반적으로 테이블 이름과 제약조건의 약자를 조합해서 만드는 경우가 많음
  • PRIMARY KEY - pk
  • NOT NULL - nn
  • UNIQUE - uk
  • CHECK - ck
  • FOREIGN KEY - fk
CREATE TABLE MEMBER(
	ID VARCHAR(50),
    NAME VARCHAR(50),
    ...
    CONSTRAINT member_pk PRIMARY KEY(ID, NAME) -- _형태의 표기 : 스네이크 표기법
);

8) 제약조건 수정

  • 제약조건 수정
    • ALTER TABLE 테이블이름 MODIFY 컬럼이름 자료형[CONSTAINT 이름] 제약조건;
  • 제약조건 추가
    • ALTER TABLE 테이블이름 ADD[CONSTAINT 이름] 제약조건(컬럼이름);
    • NOT NULL을 추가로 설정하는 경우는 제약조건을 추가하는 것이 아니고 컬럼의 자료형을 수정하는 것임
  • 제약조건 삭제
    • ALTER TABLE 테이블이름 DROP CONSTAINT 제약조건이름;

9) Sequence(일련번호)

  • 컬럼 이름 뒤에 AUTO_INCREMENT 를 설정하면 일련번호가 만들어 짐
    • AUTO_INCREMENT가 설정된 컬럼은 값을 대입하지 않아도 됨
  • 테이블을 생성할 때 초기 값을 설정할 수 있음
  • 일련번호 초기값 수정
    • ALTER TABLE 테이블이름 AUTO_INCREMENT = 값;
  • AUTO_INCREMENT는 PK나 UK를 설정해야하고 테이블에서 한 번만 설정 가능 함

10) 참조 무결성

  • tEmployee 테이블과 tProject 라는 테이블 생성
    • tEmployee 테이블은 직원에 대한 정보를 가진 테이블
    • tProject 테이블은 직원이 수행한 프로젝트에 대한 정보를 가진 테이블
  • FOREIGN KEY를 설정하지 않은 경우
CREATE TABLE tEmployee(
	name VARCHAR(20) PRIMARY KEY,
    salary INTEGER NOT NULL,
    addr CHAR(100) NOT NULL
);

INSERT INTO tEmployee(name, salary, addr)
VALUES('김부장', 7000, '제주');

INSERT INTO tEmployee(name, salary, addr)
VALUES('강과장', 5000, '서울');

INSERT INTO tEmployee(name, salary, addr)
VALUES('박사원', 3000, '경기');

CREATE TABLE tProject(
	projectid INTEGER AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20),
	project VARCHAR(50),
	cost INTEGER
);

INSERT INTO tProject (name, project, cost) VALUES('김부장', 'KB', 5000);
INSERT INTO tProject (name, project, cost) VALUES('강과장', 'KB', 5000);
INSERT INTO tProject (name, project, cost) VALUES(NULL, 'KB', 5000);
INSERT INTO tProject (name, project, cost) VALUES('이대리', 'KB', 5000);
-- 이대리는 tEmployee 테이블에 존재하지 않지만 외래키가 설정되지 않았으므로 삽입이 가능함
-- 이러한 구조로 테이블을 만들게 되면 tProject 테이블에 데이터를 삽입할 때마다 name을 확인해서 삽입해야 함
  • tProject를 삭제
    • DROP TABLE tProject;
  • 외래키 설정
    • 외래키는 상대방 테이블에서 PRIMARY KEY 나 UNIQUE 제약 조건이 설정되어 있어야 함
  • 컬럼 제약 조건으로 설정
    • 컬럼이름 자료형[CONSTRAINT 제약조건이름] REFERENCES 참조하는데이블이름(컬럼이름) 옵션
  • 테이블 제약조건으로 설정
    • [CONSTRAINT 제약조건이름] FOREIGN KEY(컬럼이름) REFERENCES 참조하는데이블이름(컬럼이름) 옵션
#외래키를 설정해서 테이블을 생성하고 데이터를 삽입
CREATE TABLE tProject(
	projectid INTEGER AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20) REFERENCES tEmployee(name),
	project VARCHAR(50),
	cost INTEGER
);
INSERT INTO tProject (name, project, cost) VALUES('김부장', 'KB', 5000);
INSERT INTO tProject (name, project, cost) VALUES('강과장', 'KB', 5000);
INSERT INTO tProject (name, project, cost) VALUES(NULL, 'KB', 5000); -- 외래키는 NULL 가능
INSERT INTO tProject (name, project, cost) VALUES('이대리', 'KB', 5000);
-- ERROR : '이대리'는 tEmployee 테이블에 존재하지 않는 데이터이기 때문

11) 외래키 옵션

  • 옵션 없이 FOREIGN KEY를 설정하게  되면 외래키로 참조되는 데이터는 삭제할 수 없음
    • 참조되지 않는 데이터는 삭제가 가능
  • 외래키에 의해서 참조되는 테이블은 먼저 삭제 할 수 없고 외래키를 소유하고 있는 테이블을 삭제한 후 삭제가 가능함
DELETE FROM tEmployee WHERE name = '강과장';
DELETE FROM tEmployee WHERE name = '박사원';
-- '박사원'은 참조 되지 않고 있기 때문에 삭제가 가능
-- '강과장'은 참조되고 있기 때문에 삭제 불가능
  • 외래키 설정할 때 옵션
    • ON DELETE [NO ACTION | CASCADE | SET NULL | SET DEFAULT]
      ON UPDATE [NO ACTION | CASCADE | SET NULL | SET DEFAULT]
      • NO ACTION은 아무것도 하지 않음
      • CASCADE는 같이 삭제되거나 수정
      • SET NULL은 NULL로 변경
      • SET DEFAULT 는 DEFAULT 값으로 변경
    • ON UPDATE는 잘 사용하지 않는데 이유는 일반적으로 PRIMARY KEY는 불변의 성격을 갖기 때문임.
CREATE TABLE tProject(
	projectid INTEGER AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(20) REFERENCES tEmployee(name) ON DELETE SET NULL,
	project VARCHAR(50),
	cost INTEGER
);

INSERT INTO tProject (name, project, cost) VALUES('김부장', 'KB', 5000);
INSERT INTO tProject (name, project, cost) VALUES('강과장', 'KB', 5000);
INSERT INTO tProject (name, project, cost) VALUES(NULL, 'KB', 5000);

DELETE FROM tEmployee WHERE name = '강과장'; -- 삭제 가능

SELECT *
FROM tProject;
-- '강과장'의 데이터는 NULL로 수정된 것을 확인할 수 있음

DML & Transaction

1. DML(Data Manipolation Language)

  • 데이터를 테이블에 삽입, 삭제, 갱신하는 SQL
  • 개발자가 사용하는 언어

 

2. 데이터 삽입

1) 기본 형식

INSERT INTO 테이블이름(컬럼 이름 나열)

VALUES(값을 나열);

  • 컬럼 이름을 생략하면 모든 컬럼의 값을 테이블을 만들 때 사용했던 순서대로 대입해야 함
  • tCity 테이블에 데이터 삽입
#컬럼 이름과 함께 삽입
INSERT INTO tCity (name, area, popu, metro, region)
VALUES('목포', 100, 22, 'n', '전라');

#컬럼 이름을 생략하고 삽입
INSERT INTO tCity
VALUES('마산', 200, 45, 'n', '경상');

2) NULL 삽입

  • 본값이 없는 경우에는 컬럼 이름을 생략하고 삽입
  • 명시적으로 값을 NULL 이라고 설정
  • 문자열의 경우는 ''(작은따옴표) 형태로 입력해도 NULL로 간주하는데 데이터베이스가 있음
INSERT INTO DEPT(DEPTNO, DNAME) VALUES(60, '영업');

INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(80, '비서', NULL);

#MariaDB 에서는 NULL이 아니고 공백
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(90, '기획','');

SELECT * FROM DEPT;

3) 여러개의 데이터를 한꺼번에 삽입

  • INSERT INTO 테이블이름(컬럼 이름 나열) VALUES(값을 나열), (값을 나열) ... ;
INSERT INTO DEPT(DEPTNO, DNAME, LOC) VALUES(100, '홍보', NULL), (110,'마케팅', NULL);

4) 다른 테이블로 부터 조회해서 삽입

  • INSERT INTO 테이블이름(컬럼 이름 나열) SELECT 구문

5) 조회한 결과를 가지고 테이블 생성

  • CREATE TABLE 테이블이름 AS SELECT 구문

6) 에러 무시하고 삽입

  • 스크립트를 이용할 때 중간에 에러가 발생해도 데이터를 삽입하고자 하는 경우는 INSERT IGNORE INTO 구문을 이용하면 됨

 

3. 데이터 삭제

DELETE FROM 테이블이름 [WHERE 조건]

  • WHERE 절을 생략하면 테이블의 모든 데이터가 삭제
    • TRUNCATE 와 유사한데 다른 점은 DELETE는 트랜잭션을 설정하면 복구가 가능함
  • DEPT 테이블에서 DEPTNO가 40보다 큰 데이터를 전부 삭제
DELETE FROM DEPT
WHERE DEPTNO > 40;
  • INSERT는 성공을 하면 반드시 1개 이상의 행이 영향을 받지만 DELETE나 UPDATE는 0개 이상의 행이 영향을 받음
    • WHERE 절이 있기 때문에 조건에 맞는 데이터가 없으면 영향받은 행의 개수는 0임
  • 외래키 옵션 없이 생성되면 삭제가 되지 않을 수도 있음

 

4. 데이터 수정

UPDATE 테이블이름 SET 수정할컬럼 = 값, ... [WHERE 조건];

  • WHERE 절을 생략하면 테이블의 모든 데이터가 수정 됨
  • tCity 테이블의 name이 서울인 데이터의 popu는 1000으로 region은 전라로 수정
UPDATE tCity
SET popu = 1000, region = '전라'
WHERE name = '서울';
-- Updated Rows : 1

UPDATE tCity
SET popu = 10, region = '강원'
WHERE name = '원주';
-- Updated Rows : 0 '원주'가 없는 것이지 ERROR는 아님

 

5. Transaction

  • 한번에 수행되어야하는 논리적인 작업의 단위
  • 1개 이상의 DML 문장으로 구성이 됨
  • A와 B가 SWORD ITEM 1만원 거래 (데이터 베이스는 1개씩 작업을 수행)
    • A에게서 SWORD 삭제
    • B SWORD 추가
    • A에게서 1만원 추가
    • B에게서 1만원 삭감

1) 트랜잭션이 가져야 하는 성질

  • Atomicity(원자성) : ALL OF NOTING - 전부 아니면 전무
  • Consistency(일관성) : 트랜잭션 수행 전과 후의 결과가 일관성이 있어야 함
  • Isolation(격리성, 독립성) : 하나의 트랜잭션은 다른 트랜잭션의 영향을 받으면 안되고 독립적으로 수행되어야 함
  • Durabiliry (영속성, 지속성) : 한 번 완료된 트랜잭션은 영원히 반영되어야 함 (수정할 수 없음)

2) 트랜잭션 구현의 원리

  • DML 작업을 수행할 때는 원본 데이터에 작업을 수행하는 것이 아니고 임시 작업 영역을 만들어서 그 영역에 데이터를 복제해서 작업을 수행
  • 작업에 전부 완료하면 원본에 변경 내역을 반영하는데 이 작업을 COMMIT 이라고 함
  • 작업을 수행하는 도중 실패를 했다면 변경 내역을 원본에 반영하지 않는데 이를 ROLLBACK이라고 함

3) 트랜잭션 명령어

  • COMMIT : 원본에 반영하는 것
  • ROLLBACK : 원본에 반영하지 않는 것
  • SAVEPOINT : ROLLBACK 할 위치를 설정

4) 트랜잭션 모드

  • Manual : 사용자가 직접 COMMIT 과 ROLLBACK을 하도록 하는 모드
  • Auto : 하나의 명령어가 성공적으로 수행되면 자동으로 COMMIT을 하는 모드
  • 프로그래밍 언어에서 데이터베이스를 연결하거나 접속 도구 등에서 데이터베이스 서버에 접속해서 작업을 수행하는 경우 Auto 로 설정 되는 경우가 있음. (JAVA, Node, DBeaver의 기본 값은 Auto 모드, Spring은 Manual 모드)

5) 트랜잭션 생성 시점

  • DML 문장이 성공적으로 완료되면 생성

6) 트랜잭션 종료 시점

  • COMMIT 이나 ROLLBACK을 수행한 경우

7) AUTO COMMIT : 자동으로 COMMIT 되는 경우

  • DDL(CREATE, ALTER, DROP, TRUNCATE) 이나 DCL(GRANT, REVOKE) 문장을 수행
  • 접속 프로그램을 정상적으로 종료한 경우

8) AUTO ROLLBACK : 자동으로 ROLLBACK 되는 경우

  • 접속이 비정상적으로 종료되는 경우

9) 실습

#DEPT 테이블의 모든 내용을 가지고 DEPT01 테이블 생성
CREATE TABLE DEPT01 AS SELECT * FROM DEPT;

#DEPT01 테이블의 모든데이터 삭제
#DELETE 문장을 성공적으로 수행하므로 TRANSACTION이 생성됨
DELETE FROM DEPT01;

#데이터 확인
SELECT * FROM DEPT01;

#TRANSACTION 시작점으로 이동
ROLLBACK;

#데이터 확인
SELECT * FROM DEPT01;

#DEPTNO가 20인 데이터를 삭제
DELETE FROM DEPT01 WHERE DEPTNO = 20;

SELECT * FROM DEPT01;

#현재까지 내역을 원본에 반영 - 트랜잭션 종료
COMMIT;

#트랙잭션이 이미 종료되서 ROLLBACK을 해도 데이터가 복구되지 않음
ROLLBACK;

SELECT * FROM DEPT01;

#DEPT01 테이블에서 DEPTNO가 10인 데이터를 삭제
DELETE FROM DEPT01 WHERE DEPTNO = 10;

#DEPT02 테이블 생성 - DDL 문장을 수행하면 트랜잭션은 종료
CREATE TABLE DEPT02(DEPTNO INT);

#CREATE 구문이 성공적으로 수행되면 트랜잭션은 종료됨
#ROLLBACK을 해도 돌아가지 못함
ROLLBACK;

SELECT * FROM DEPT01;

#DEPT01 테이블을 삭제
DROP TABLE DEPT01;

#DEPT 테이블을 그대로 복제해서 DEPT01 테이블 생성
CREATE TABLE DEPT01 AS SELECT * FROM DEPT;

#DEPTNO가 40인 데이터 삭제
DELETE FROM DEPT01 WHERE DEPTNO = 40;

#SAVEPOINT 생성
SAVEPOINT s1;

#DEPTNO가 30인 데이터 삭제
DELETE FROM DEPT01 WHERE DEPTNO = 30;

#SAVEPOINT 생성
SAVEPOINT s2;

#DEPTNO가 20인 데이터 삭제
DELETE FROM DEPT01 WHERE DEPTNO = 20;

#ROLLBACK 할 방법이 3가지
ROLLBACK;
-- TABLE을 생성한 지점으로 롤백 : 데이터가 4개 
-- 만든 SAVEPOINT는 모두 삭제

ROLLBACK TO s1;
-- 40을 지운 지점까지 롤백 : 데이터가 3개
-- 다시 ROLLBACK 하면 DATA가 4개가 되지만 s2 SAVEPOINT는 제거

ROLLBACK TO s2;
-- 30을 지운 지점까지 롤백 : 데이터가 2개
-- ROLLBACK을 다시하면 데이터가 4개가 되고 ROLLBACK TO s1도 가능

ROLLBACK TO s1;

SELECT * FROM DEPT01;

ROLLBACK TO s2; -- 이 구문은 에러

ROLLBACK;

SELECT * FROM DEPT01;

 

6. LOCK

  • 2가지 LOCK 존재
  • Shared LOCK 이고 Exclusive LOCK 2가지 종류인데 Shared Lock은 공유 가능한 LOCK이고 Exclusive LOCK은 공유가 불가능한 LOCK
  • 읽기 작업을 할 때는 Shared LOCK이 설정되고 그 이외의 작업을 할 때는 Exclusive LOCK이 걸리게 되는데 이 경우는 트랜잭션이 종료되어야만 LOCK이 해제됨
  • TRANSACTION MODE를 Manual 로 사용하는 경우 하나의 컴퓨터에서 DML 작업을 수행하고 COMMIT이나 ROLLBACK을 하지 않은 상태에서 다른 컴퓨터에서 SELECT를 하는 것은 아무런 문제가 없지만 DML 작업이나 DDL작업을 수행하는 것은 안되는데 무한 루프에 빠지게 됨
  • LOCK의 기본 단위는 테이블

BACK LOG

테이블 이외의 개체

  • VIEW 나 PROCEDURE, TRIFFER, INDEX 가 데이터 베이스 사용 성능을 향상시키기 위한 개체인데 최근의 프로그래밍에서는 IN MEMORY DB 개념의 형태를 사용하기 때문에 이 개체들을 사용하는 이점이 별로 없음

IN MEMORY DB

1. VIEW

  • 자주 사용하는 SELECT 구문을 하나의 테이블의 형태로 사용하기 위한 객체

1) 장점

  • SELECT 구문을 메모리에 적재하기 때문에 속도가 향상
  • 필요한 부분만 노출하면 되기 때문에 보안이 향상

2) Inline View

  • FROM 절에 사용한 SELECT 구문
  • SELECT 구문이 리턴하는 결과는 하나의 테이블 처럼 사용이 가능하기 때문에 FROM 절에 사용하는 것이 가능
  • SELECT 구문의 결과는 이름이 없어서 다른 절에서 사용할 수 없기 때문에 반드시 이름을 새로 만들어 주어야 함
  • ORACLE에서 OFFSET FETCH가 적용되기 전에는 Inline View를 이용해서 TOP-N을 구현했기 때문에 오라클에서는 매우 중요
#Inline View : (SELECT * FROM tStaff WHERE grade = '과장' OR grade = '부장')
SELECT *
FROM (SELECT * FROM tStaff WHERE grade = '과장' OR grade = '부장') IMSI
WHERE IMSI.score >= 70;

3) VIEW 생성

CREATE [OR REPLACE] VIEW 뷰이름
AS
SELECT 구문
[WITH CHECK OPTION]
[WITH READ ONLY]
  • VIEW는 ALTER로 수정이 불가능하기 때문에 수정하고자 할 때 OR REPLACE 사용
  • VIEW는 테이블처럼 사용할 수 있기 때문에 읽기와 쓰기 작업이 모두 가능함
  • WITH CHECK OPTION은 VIEW를 만들 때 사용한 조건과 일치하는 데이터만 수정하거나 삭제 또는 삽입 할수 있도록 해주는 옵션
  • WITH READ ONLY는 쓰기를 못하게 하는 옵션

4) VIEW 삭제

DROP VIEW 뷰이름;

[ 실습 ]

  • DEPT 테이블에서 DEPTNO가 30인 데이터를 자주 사용
SELECT * FROM DEPT WHERE DEPTNO = 30 -- 이렇게 사용해야 함

CREATE VIEW DEPTVIEW
AS
SELECT *
FROM DEPT
WHERE DEPTNO = 30;

#이제 부터는 DEPTVIEW가 SELECT * FROM DEPT WHERE DEPTNO = 30의 역할을 수행
#한번 컴파일 되면 SQL이 메모리에 상주하기 때문에 빠름
#DEPT 테이블의 모든 내용을 가지고 DEPTCOPY 테이블을 생성
CREATE TABLE DEPTCOPY
AS
SELECT *
FROM DEPT

#DEPTCOPY 테이블에서 DEPTNO가 20이 넘는 데이터를 가지고 DEPTVIEW라는 VIEW 생성
CREATE OR REPLACE VIEW DEPTVIEW
AS
SELECT *
FROM DEPTCOPY
WHERE DEPTNO > 20;

#DEPTVIEW는 테이블처럼 사용이 가능
#VIEW는 SQL을 가지고 있는 것이지 실제 데이터를 가지고 있는 것이 아님.
SELECT *
FROM DEPTVIEW

#VIEW에 데이터 삽입 - 원본 테이블에 삽입
INSERT INTO DEPTVIEW(DEPTNO, DNAME, LOC)
VALUES(50, '영업', '분당');

#원본 테이블 확인
SELECT * FROM DEPTCOPY -- 원본 테이블에 삽입되어 있는 것을 확인 할 수 있음

# VIEW 삭제
DROP VIEW DEPTVIEW

 

2. 절차적 프로그래밍

  • SQL은 비절차적(작성 순서로 동작하지 않음)
    • 작성 : SELECT → FROM
    • 실제 실행 순서 : FROM →SELECT 순임
  • 관계형 데이터베이스에서도 절차적 프로그래밍이 가능함
  • 문법은 관계형 데이터베이스 종류마다 다름
    • Oracle은 이 문법을 PL/SQL 이라고 하고 MS SQL Server는 T SQL이라 부름

1) Stored Procedure

  • 자주 SQL 구문을 함수처럼 미리 만들어두고 이름만으로 실행하도록 해주는 개체
  • 함수와 다른 점은 함수는 리턴을 하지만 리턴을 하지 않음
  • 장점
    1. 성능 향상 : 한 번 실행하고 나면 메모리에 상주하기 때문
    2. 보안 향상 : 테이블의 구조를 몰라도 작업이 가능하기 때문
  • 생성 방법
  • DELIMITER 기호 2개
CREATE [OR REPLACE] PROCEDURE 이름(매개변수)
BEGIN
필요한 SQL 프로그래밍
END 기호2개
DELEMITER;

#생성을 할 떄는 하나의 SQL 구문이 아니기 때문에 스크립트 실행의 형태로 수행
  • 호출
CALL 프로시저이름(매개변수 나열);
  • 삭제
DROP PROCEDURE 이름;
  • ORM이 아닌 SQL Mapper를 이용하는 경우 필수적으로 1개 이상 사용

[ 실습 ]

  • DEPT TABLE에 데이터를 삽입하는 프로시저를 만들고 호출
#프로시저 생성 - 블록을 잡고 스크립트로 실행해야 함
DELIMITER //
CREATE OR REPLACE PROCEDURE myproc(
	vdeptno int(2),
	vdname varchar(14),
	vloc varchar(13))
	
	BEGIN 
		INSERT INTO DEPT(DEPTNO, DNAME, LOC)
		VALUES(vdeptno, vdname, vloc);
	END //
DELIMITER ;

#프로시저 호출
CALL myproc(77,'회계','서울');

#프로시저 호출 결과 확인
SELECT * FROM DEPT;

#프로시저 삭제
DROP PROCEDURE myproc;

2) TRIGGER

  • 어떤 사건 (INSERT, UPDATE, DELETE)이 발생했을 때 절차적 프로그래밍 부분을 자동으로 수행하기 위한 개체
  • 유효성 검사를 해서 SQL을 실행하지 않도록 하거나 DML 작업을 수행했을 때 로그를 기록하거나 다른 의 작업을 연쇄적으로 실행하는 등의 작업을 주로 수행
  • 애플리케이션 개발자 입장에서는 대부분 프로그래밍으로 처리하려 하기 때문에 잘 사용하지는 않지만 보안을 위해서는 사용하는 것도 나쁘지 않음

  • 삽입 트리거 - 하나의 테이블에 데이터를 삽입하면 다른 테이블에 데이터가 자동으로 삽입되도록 하는 트리거(블로그 같은곳에서 회원가입을 하면 가입한 회원의 데이터를 관리할 수 있는 테이블을 별도로 생성하는 경우가 있음)
#데이터를 삽입할 테이블
CREATE TABLE EMP01(
	EMPNO INT PRIMARY KEY,
    ENAME VARCHAR(10),
    JOB VARCHAR(30));
    
#트리거로 데이터를 삽입할 테이블
CREATE TABLE SAL01(
	SALNO INT PRIMARY KEY AUTO_INCREMENT,
    SAL FLOAT(7, 2),
    EMPNO INT REFERNECES EMP01(EMPNO) ON DELETE CASCADE);
    
#삽입 트리거
DELIMITER //
CREATE OR REPLACE TRIGGER TRG_01
AFTER INSERT
ON EMP01
FOR EACH ROW
BEGIN
	INSERT INTO SAL01(SAL, EMPNO) VALUES(100, NEW. EMPNO);
END //
DELIMITER ;

#데이터를 삽입하고 확인
INSERT INTO EMP01 VALUES(1,'adam','singer');

SELECT * FROM EMP01;

#트리거로 인해서 데이터가 삽입이됨
SELECT * FROM SAL01;