SQL (4)

01 Apr 2019

8. VIEW

가상 테이블
정의: 실제 테이블을 여러 관점에서 투영한 가상 테이블
사용자에게 편리한 접근 제공
컬럼 단위의 보완 관리를 위해
NOTE: DML 작업 (INSERT, UPDATE, DELETE)

CREATE VIEW vw30
AS
    SELECT empno, ename, sal, comm 
    FROM scott_emp
    WHERE deptno = 30;

SELECT * FROM vw30;

+-------+--------+------+------+
| empno | ename  | sal  | comm |
+-------+--------+------+------+
|  7499 | ALLEN  | 1600 |  300 |
|  7521 | WARD   | 1250 |  500 |
|  7654 | MARTIN | 1250 | 1400 |
|  7698 | BLAKE  | 2850 | NULL |
|  7844 | TURNER | 1500 |    0 |
|  7900 | JAMES  |  950 | NULL |
+-------+--------+------+------+

DROP VIEW vw30;
CREATE VIEW vw30
AS
    SELECT ename, sal, job 
    FROM scott_emp
    WHERE deptno = 30;

-- empno: Not null. Hence, empno는 항상 추가
INSERT INTO vw30 VALUES('john', 2500, 'developer');
    --> ERROR 1423 (HY000): Field of view 'dbexample.vw30' underlying table does not have a default value

DROP VIEW vw30;
CREATE VIEW vw30
AS
    SELECT empno, ename, sal, job 
    FROM scott_emp
    WHERE deptno = 30;

-- deptno 지정을 안해줘서 vw30에 insert 되지 않고 scott_emp table에만 insert 됨
INSERT INTO vw30 VALUES(1, 'john', 2500, 'developer');
DESC scott_emp;

+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int(11)     | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int(11)     | YES  |     | NULL    |       |
| HIREDATE | datetime    | YES  |     | NULL    |       |
| SAL      | double      | YES  |     | NULL    |       |
| COMM     | double      | YES  |     | NULL    |       |
| DEPTNO   | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

9. INDEX

DB의 빠른 검색을 위한 색인 기능
Primary Key는 기본적으로 자동 index로 설정됨
기본키: 절대 중복 불허, null 값도 허용 안 함

CREATE TABLE tableINDEX(
    id  int,
    name varchar(10),
    point float
);

INSERT INTO tableINDEX values(5, 'scott', 2.3);
INSERT INTO tableINDEX values(2, 'adam', 6.4);
INSERT INTO tableINDEX values(7, 'john', 4.2);
INSERT INTO tableINDEX values(1, 'tom', 7.9);

SELECT * from tableINDEX;

+------+-------+-------+
| id   | name  | point |
+------+-------+-------+
|    5 | scott |   2.3 |
|    2 | adam  |   6.4 |
|    7 | john  |   4.2 |
|    1 | tom   |   7.9 |
+------+-------+-------+

-- 제약을 통해 생성
CREATE INDEX idx_point ON tableINDEX(point);

SELECT * FROM tableINDEX WHERE point >= 4;

+------+------+-------+
| id   | name | point |
+------+------+-------+
|    2 | adam |   6.4 |
|    7 | john |   4.2 |
|    1 | tom  |   7.9 |
+------+------+-------+

-- alot faster after making index
EXPLAIN SELECT * FROM tableINDEX WHERE point >= 4;

+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tableINDEX | NULL       | range | idx_point     | idx_point | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+

EXPLAIN SELECT * FROM tableINDEX WHERE id >= 4;

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tableINDEX | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

DROP INDEX idx_point ON tableINDEX;

ALTER TABLE tableINDEX ADD CONSTRAINT pk_id PRIMARY KEY(id);

10. TRANSACTION

ALL OR NOTHING
ROLLBACK: commit 한 시점으로 돌아감
COMMIT
DML 작업만 기록 (DDL x)
동기화 작업 수행
ACID: 원자성, 일관성, 고립성, 지속성

SELECT @@autocommit;

+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

SET @@autocommit = false;
SELECT @@autocommit;

+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+

DELETE FROM tableINDEX;
ROLLBACK;
SELECT * FROM tableINDEX;

+----+-------+-------+
| id | name  | point |
+----+-------+-------+
|  1 | tom   |   7.9 |
|  2 | adam  |   6.4 |
|  5 | scott |   2.3 |
|  7 | john  |   1.2 |
+----+-------+-------+

DELETE FROM tableINDEX;
COMMIT;
ROLLBACK;
SELECT * FROM tableINDEX;
    --> Empty set

-- 동기화 작업
/* open 2 consoles */
SELECT * from tableEXAM;

+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | scott   |   20 |
|  2 | no name |   30 |
|  3 | miller  |   20 |
+----+---------+------+

UPDATE tableEXAM
SET age = 40
WHERE id = 1;
SELECT * FROM tableEXAM;

-- 실행한 콘솔창
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | scott   |   40 |
|  2 | no name |   30 |
|  3 | miller  |   20 |
+----+---------+------+

-- 다른 콘솔창: 아직 업데이트 적용이 안됨
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | scott   |   20 |
|  2 | no name |   30 |
|  3 | miller  |   20 |
+----+---------+------+

COMMIT;
SELECT * FROM tableEXAM;

-- 두 콘솔창 모두 scott의 age가 바뀜
-- 다른 콘솔창
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | scott   |   40 |
|  2 | no name |   30 |
|  3 | miller  |   20 |
+----+---------+------+