[SQL 첫걸음] 29강 - 31강

29강 :인덱스 작성과 삭제

# 인덱스 작성
# CREATE INDEX isample65 ON sample62(no);
mysql> CREATE INDEX isample65 ON sample62(no);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 인덱스 삭제
# DROP INDEX 인덱스명(스키마 객체)
# DROP INDEX 인덱스명 ON 테이블명(테이블 내 객체)
mysql> DROP INDEX isample65 ON sample62;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

 

EXPLAIN

- 실제로 인덱스를 사용해 검사하는지 확인 가능

mysql> CREATE INDEX isample65 ON sample62(a);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sample62 WHERE a = 'a';
Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM sample62 WHERE a = 'a';
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | sample62 | NULL       | ref  | isample65     | isample65 | 93      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# a열을 사용하지 않도록 조건 변경
mysql> EXPLAIN SELECT * FROM sample62 WHERE no > 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | sample62 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

 

30강 : 뷰 작성과 삭제

1. 뷰(가상 테이블)

테이블처럼 테이터베이스 객체 중 하나

앞에서 FROM 구에 기술된 서브쿼리에 이름을 붙이고 데이터베이스 객체화하여 쓰기 쉽게 한 것

SELECT 명령을 객체로서 관리 가능

SELECT에서만 사용 권장 -> 테이블처럼 데이터를 쓰고 지우는 저장공간이 없으므로 INSERT 등 사용 시엔 주의 필요

 

2. 뷰 작성과 삭제

# CREATE VIEW 뷰명 AS SELECT 명령
mysql> CREATE VIEW sample_view_67 AS SELECT * FROM sample54;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM sample_view_67;
+------+------+
| no   | a    |
+------+------+
|    1 |  100 |
|    2 |  900 |
|    4 |   80 |
+------+------+
3 rows in set (0.00 sec)

# 열을 지정해 뷰 작성하기
mysql> CREATE VIEW sample_view_672(n, v, v2) AS
    -> SELECT no, a, a*2 FROM sample54;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM sample_view_672 WHERE n = 1;
+------+------+------+
| n    | v    | v2   |
+------+------+------+
|    1 |  100 |  200 |
+------+------+------+
1 row in set (0.00 sec)

# 뷰 삭제
mysql> DROP VIEW sample_view_67;
Query OK, 0 rows affected (0.01 sec)

 

3. 뷰의 약점

저장공간 대신 CPU 자원 사용

 

Materialized View

- 일반적으로 뷰는 데이터를 일시적으로 저장했다 쿼리 실행 종료 시 함께 삭제됨

- Materialized View는 테이블처럼 처음 참조 되었을 때 데이터를 저장장치에 저장해두고 사용함

- 뷰에 지정된 테이블의 데이터가 변경된 경우, SELECT 명령 재실행 -> 데이터 재저장(일련의 과정은 RDBMS가 자동으로 실행)

- 현재는 Oracle과 DB2에서만 사용 가능

 

함수 테이블

- 뷰를 구성하는 SELECT 명령은 단독 실행 가능이 전제임 -> 부모 쿼리와 연관된 서브 쿼리는 사용 불가

-> 함수 테이블을 사용하면 회피 가능

- 테이블을 결괏값으로 반환해주는 사용자정의 함수이므로 인수 지정하여 WHERE 조건으로 결괏값 바꿀 수 있음

 

7장 : 복수의 테이블 다루기

31강 : 집합 연산

1. SQL과 집합

벤 다이어그램에선 하나의 원이 곧 하나의 집합

데이터베이스에서는 테이블의 행이 곧 요소에 해당

SELECT 명령 실행 결과 반환된 여러 행이 곧 하나의 집합

 

2. UNION으로 합집합 구하기

각각의 SELECT 명령의 열의 내용은 서로 일치해야 함

mysql> SELECT * FROM sample71_a;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM sample71_b;
+------+
| b    |
+------+
|    2 |
|   10 |
|   11 |
+------+
3 rows in set (0.01 sec)

mysql> SELECT * FROM sample71_a
    -> UNION
    -> SELECT * FROM sample71_b;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
+------+
5 rows in set (0.00 sec)

 

UNION 사용 시 ORDER BY

- 마지막 SELECT 명령에만 지정

mysql> SELECT a FROM sample71_a ORDER BY a
    -> UNION
    -> SELECT b FROM sample71_b;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
SELECT b FROM sample71_b' at line 2

# 합집합의 결과를 정렬하기 때문에 두 개의 SELECT 명령에서 열 이름이 서로 일치한단 보장이 없어 에러 발생
mysql> SELECT a FROM sample71_a
    -> UNION
    -> SELECT b FROM sample71_b ORDER BY b;
ERROR 1054 (42S22): Unknown column 'b' in 'order clause'

# 별명 붙여서 이름 일치 시켜 정렬 가능
mysql> SELECT a AS c FROM sample71_a
    -> UNION
    -> SELECT b AS c FROM sample71_b ORDER BY c;
+------+
| c    |
+------+
|    1 |
|    2 |
|    3 |
|   10 |
|   11 |
+------+
5 rows in set (0.00 sec)

 

UNION ALL

일반적으로 UNION한 결과엔 중복값이 제거되어 있는데, 중복값 제거 없이 그대로 합치고 싶을 때 사용

mysql> SELECT * FROM sample71_a
    -> UNION ALL
    -> SELECT * FROM sample71_b;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    2 |
|   10 |
|   11 |
+------+
6 rows in set (0.00 sec)

 

3. 교집합과 차집합

MySQL에선 지원 안함

INTESECT : 교집합

EXCEPT : 차집합(Oracle은 MINUS)