SQL 첫걸음 6장
25. 데이터베이스 객체
처음 RDBMS 소프트웨어를 설치하면 데이터베이스는 비어있는 상태임
사용자는 여기에 테이블 / 뷰 / 인덱스 /프로시저 등의 데이터베이스 객체를 작성해 데이터베이스를 구축
데이터베이스 객체
데이터베이스 객체 : 테이블 / 뷰 / 인덱스 등 데이터베이스 내에 정의하는 모든 것
- 테이블의 경우 행과 열이 저장되는 등 객체의 종류에 따라 데이터베이스에 저장되는 내용도 달라짐
SELECT,INSERT등은 데이터베이스 내에 존재하는 것이 아니므로 객체라 부를 수 없음- 객체는 이름을 가지며, 객체의 종류와 관계없이 중복되지 않아야 하고 임의의 규칙에 맞게 지정해야함
- 기존 이름이나 예약어와 중복되지 않아야 함
- 숫자로 시작할 수 없음
- 언더스코어(_) 이외의 기호는 사용할 수 없음
- 한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러싸야 함
- 시스템이 허용하는 길이를 초과하지 않아야 함
스키마
데이터베이스 객체는 스키마라는 그릇 안에 만들어짐
따라서 데이터베이스 객체를 스키마 객체라고 부르기도 하고, 데이터베이스에 테이블을 작성하고 구축해나가는 작업을 스키마 설계라고 부름
- 각각의 스키마는 독립적이므로 속해있는 스키마가 다르다면 객체의 이름이 같아도 됨
- 스키마는 SQL 명령의 DDL을 이용하여 정의함
- 어떤 것이 스키마가 되는지는 제품에 따라 달라짐
- MySQL에서는 CREATE DATABASE명령으로 작성한 데이터베이스가 스키마가 됨
- Oracle 등에서는 데이터베이스와 데이터베이스 사용자가 계층적 스키마가 됨
이름이 충돌하지 않도록 기능하는 그릇을 네임스페이스(namespace)라고 부르기도 함 따라서 테이블이나 스키마는 네임스페이스이기도 함
26. 테이블 작성 / 삭제 / 변경경
DML : 데이터를 조작하는 명령, SELECT / INSERT / DELETE / UPDATE 등이 속함
DDL : 데이터를 정의하는 명령, 스키마 내의 객체를 관리할 때 사용
CREATE로 작성,DROP으로 삭제,ALTER로 변경- 각 명령어 뒤에 어떤 종류의 객체를 다룰 것인지 지정함
테이블 작성
CREATE TABLE 테이블명(
열명 자료형 DEFAULT 기본값 NULL/NOT NULL,
열 정의2,
...
)
CREATE TABLE 테이블명으로 테이블을 생성
- 테이블명 뒤에 괄호 속에 생성할 열을 정의하며, 코마로 구분해 연속하여 지정함
- 테이블명과 열명은 명명규칙에 맞게 붙여줘야함
CHAR이나VARCHAR와 같은 문자열형으로 지정시에는 최대길이를 괄호로 묶어줘야함- 기본값은 생략할 수 있으며 설정시엔
DEFAULT로 지정하되 자료형에 맞는 리터럴로 기술함 NULL을 명시적으로 지정하거나 생략했을 경우는NULL을 허용하며,NOT NULL으로 지정시에는NULL이 허용되지 않음
테이블 삭제
DROP TABLE 테이블명으로 테이블을 제거
단, SQL 명령 실행시 확인을 요구하지 않는 데이터베이스가 많으므로 신중해야함
DROP을 사용시 테이블에 저장된 데이터도 함께 삭제됨DELETE는 테이블 정의를 그대로 둔 채 데이터만 삭제하며, 삭제할 행이 많으면 처리속도가 상당히 늦어짐- 따라서 모든 행을 삭제해야 할 때는
TRUNCATE TABLE 테이블명의 형식으로TRUNCATE명령을 사용함
테이블 변경
ALTER TABLE 테이블명 변경명령으로 테이블을 변경
열 추가 / 삭제 / 변경, 제약 추가 / 삭제 등이 가능함
비교적 새로운 명령으로 데이터베이스에 따라 고유한 방언이 존재함
열 추가 : 변경명령 부분을 ADD 열 정의의 형태로 사용, 열 정의는 CREATE TABLE의 경우와 동일
- 기존 데이터행이 존재하면 추가한 열의 값이 모두
NULL이 됨 - 단, 기본값이 지정되어있는 경우엔 기본값으로 데이터가 저장됨
NOT NULL제약이 걸려있다면 반드시 기본값을 지정해야함
열 속성 변경 : 변경명령 부분을 MODIFY 열 정의의 형태로 사용, 열 정의는 CREATE TABLE의 경우와 동일
- 열 이름은 변경할 수 없음
- 자료형이나 기본값,
NOT NULL제약 등의 속성은 변경할 수 있음 - 기존 데이터 행이 존재한다면 속성 변경에 따라 데이터 역시 변환되지만, 자료형 변경 과정에서 에러가 발생하면
ALTER TABLE명령은 실행되지 않음
열 이름 변경 : 변경명령 부분을 CHANGE 기존 열이름, 신규 열 정의의 형태로 사용
- 열 이름 뿐만 아니라 열 속성도 변경할 수 있음
- 단, Oracle의 경우엔 열 이름 변경시
RENAME TO 하부명령을 사용
열 삭제 : 변경명령 부분을 DROP 열명의 형태로 사용
- 테이블에 존재하지 않는 열을 지정시 에러 발생
ALTER TABLE로 테이블 관리
처음에 지정했던 용량이 부족할 경우 문자열형의 최대길이를 늘림
반대로 최대길이를 줄일 경우에는 여러가지 문제가 발생하며, 실제 저장공간이 늘어나는 경우도 적으므로 줄이는 경우는 별로 없음
시스템의 기능 확장 등 열을 추가하는 일은 자주 일어남
그러나 이 또한 테이블의 정의가 바뀌는 만큼 시스템 쪽에 영향을 꽤 미치기 때문에 변경한 테이블에 행을 추가하는 INSERT 명령은 반드시 확인해야 함
27. 제약
테이블에 제약을 설정함으로써 저장될 데이터를 제한할 수 있음
NOT NULL / 기본키(Primary Key) / 외부참조(정합) 제약 등
테이블 작성시 제약 정의
제약은 테이블에 설정됨
CREATE TABLE로 테이블을 작성할 때 제약을 같이 정의함
- 열에 대해 정의하는 제약을 열 제약이라고 부름
- 한 개의 제약으로 복수의 열에 제약을 걸 경우에는 테이블 제약이라고 부름
- 제약에는
CONSTRAINT키워드를 통해 이름을 붙일 수 있음
제약 추가
기존 테이블에도 나중에 제약을 추가할 수 있음
열 제약을 추가할 경우 ALTER TABLE로 열 정의를 변경할 수 있음
ALTER TABLE 테이블명 MODIFY 열명 데이터형 제약;의 형식으로 사용- 기존 테이블을 변경할 경우에는 제약을 위반하는 데이터가 있는지 먼저 검사하며, 제약에 걸린다면 에러가 발생함
테이블 제약은 ALTER TABLE의 ADD 하부명령으로 추가할 수 있음
- 기본키 제약을 추가할 경우
ALTER TABLE 테이블명 ADD CONSTRAINT 제약명 PRIMARY KEY(열명);의 형태로 사용 - 기본키는 테이블에 하나만 설정할 수 있으며, 마찬가지로 기존의 행에 추가할 제약을 위반하는 데이터가 있을시 에러가 발생함
제약 삭제
열 제약은 제약을 추가할 때와 동일하게 열 정의를 변경하여 제거
테이블 제약은 ALTER TABLE의 DROP 하부명령으로 제거할 수 있음
ALTER TABLE 테이블명 DROP CONSTRAINT 제약명;의 형식으로 사용- 단, 기본키는 어짜피 하나만 설정할 수 있기 때문에 제약명을 지정하지 않아도 삭제가 가능함
기본키
기본키 : 테이블의 행 한 개를 특정할 수 있는 검색키, 기본키로 검색했을 때 복수의 행이 일치하는 데이터를 작성할 수 없음
- 즉, 기본키로 설정된 열이 중복하는 데이터 값을 가지는 경우 제약에 위배됨
- 행이 유일성을 필요로 한다는 의미에서 유일성 제약이라 불리는 경우도 있음
- 기본키로 지정할 열은
NOT NULL제약이 설정되어있어야 함
기본키를 구성하는 열을 복수로 지정하는 것도 가능함
이 때의 중복 체크는 해당하는 열들의 조합으로 판단함
28. 인덱스 구조
인덱스
인덱스 : 테이블에 붙여진 색인, 검색(탐색)속도 향상이 목적
- 테이블에 인덱스가 지정되어있으면 효율적인 검색이 가능하여
WHERE로 조건이 지정된SELECT명령의 처리 속도가 향상됨 - 검색시에 쓰이는 키워드와 대응하는 데이터 행의 장소가 저장되어있음
- 테이블과는 별개의 독립된 데이터베이스로 작성됨
- 대부분의 데이터베이스에서는 테이블을 삭제하면 인덱스도 같이 삭제됨
검색에 사용하는 알고리즘
풀 테이블 스캔(full table scan) : 인덱스가 지정되지 않은 테이블을 검색
테이블에 저장된 모든 값을 처음부터 차례로 조사
이진 탐색(binary search) : 차례로 나열된 집합에 대해 유효, 절반씩 나누어 조사하며 대량의 데이터 검색에 유리
이진 트리(binary tree) : 검색의 진행 방법은 이진 탐색과 비슷하지만 데이터가 정렬되어있지 않아도 되며, 인덱스는 이진 트리의 구조로 작성됨
- 이진 트리에서는 집합 내에 중복하는 값을 가질 수 없음
29. 인덱스 작성과 삭제
인덱스도 데이터베이스의 객체이기 때문에 DDL을 사용하여 작성하거나 삭제함
인덱스 작성
CREATE INDEX 명령으로 인덱스를 생성
CREATE INDEX 인덱스명 ON 테이블명(열명1, 열명2, ...);의 형식으로 사용
- Oracle, DB2 등에서는 인덱스를 스키마 객체로 취급하여 스키마 내에 중복된 이름이 없도록 지정하여 관리
- SQL Server, MySQL 등에서는 인덱스를 테이블 내의 객체로 취급하여 테이블 내에 이름이 중복되지 않도록 지정하여 관리
- 인덱스 작성시 테이블 크기에 따라 작성시간이 달라지며, 행이 대량 존재시 시간과 저장공간이 많이 소비됨
인덱스 삭제
DROP INDEX 명령으로 인덱스를 삭제
DROP INDEX 인덱스명(스키마 객체의 경우) 또는 DROP INDEX 인덱스면 ON 테이블명(테이블 내 객체의 경우)의 형식으로 사용
인덱스 작성시 WHERE구에 조건을 지정한 SELECT 명령으로 검색할 시의 처리속도는 향상되지만 모든 SELECT의 명령에 적용되는 만능 인덱스는 존재하지 않음
즉, WEHRE구에 걸린 조건식이 인덱스로 작성되어있지 않다면 SELECT 명령은 해당 인덱스를 사용할 수 없음
INSERT 명령의 경우 인덱스 또한 같이 갱신해줘야 하므로 처리속도가 조금 떨어지게 됨
EXPLAIN
EXPLAIN : 인덱스를 사용해 검색하는지 여부를 확인
EXPLAIN SQL명령의 형식으로 사용EXPALIN은 표준이 아닌 데이터베이스 제품 의존형 명령이지만 대부분의 데이터베이스 제품이 비슷한 명령을 지원함
최적화
데이터베이스는 내부 최적화에 의해 SELECT 명령 실행시 인덱스 사용 여부를 처리하며, 내부 처리시 SELECT 명령 실행에 앞서 실행계획을 세움
실행계획에서는 인덱스의 유무 및 인덱스를 사용할 것인지 여부에 대해 판단하며, 이 때 인덱스의 품질이 고려됨
- 데이터의 종류가 적을수록 인덱스의 효율이 떨어짐
EXPLAIN명령은 실행계획을 확인함
- 뷰 작성과 삭제
뷰
뷰 : FROM구에 서술된 서브쿼리에 이름을 붙이고 데이터베이스 객체화하여 쓰기 쉽게 한 것
- 즉, 본래 데이터베이스 객체로 등록할 수 없는
SELECT명령을 객체로서 이름을 붙여 관리할 수 있도록 한 것 - 뷰를 참조하면 그에 정의된
SELECT명령의 실행결과를 테이블처럼 사용할 수 있음 SELECT * FROM (SELECT * FROM 테이블명) 별명;=SELECT * FROM 뷰명;- 테이블처럼 취급할 수 있으나 저장공간을 가지지 않기 때문에 실체가 존재하지는 않으므로 가상 테이블이라고 불리기도 함
- 따라서
SELECT명령에서만 사용하는 것이 권장되며,INSERT/UPDATE/DELETE등의 명령에서도 조건이 맞으면 사용 가능하지만 주의가 필요함
뷰 작성과 삭제
뷰 역시 데이터베이스 객체이므로 DDL로 작성하거나 삭제함
작성시에는 CREATE VIEW, 삭제시엔 DROP VIEW를 사용
CREATE VIEW 뷰명 (열명1, 열명2, ...) AS SELECT 명령의 형식으로 작성, 이 때AS는 생략할 수 없음- 열 지정 생략시엔
SELECT구에서 지정하는 열 정보가 자동적으로 뷰의 열로 지정됨
- 열 지정 생략시엔
DROP VIEW 뷰명의 형식으로 삭제
뷰의 약점
뷰는 저장공간을 소비하지 않는 대신 CPU 자원을 사용함
뷰를 참조할 때마다 뷰에 등록되어있는 SELECT 명령이 실행되며, 이 때 실행 결과는 일시적으로 보존됨
뷰의 근원이 되는 테이블에 보관하는 데이터양이 많은 경우, 집계처리시 뷰를 사용하거나 뷰를 중첩하여 사용하는 경우 처리속도가 많이 떨어지게 됨
이런 상황을 회피하기 위해 머터리얼라이즈드 뷰(Materialized View)를 사용함
- 데이터를 일시적으로 저장하고 쿼리 종료시 함께 삭제되는 일반적인 뷰와 달리 데이터를 테이블처럼 저장장치에 저장해두고 사용함
- 처음 참조시 데이터를 저장하고, 다시 참조할 때는 이전에 저장해 두었던 데이터를 그대로 사용함
- 뷰에 지정된 테이블의 데이터가 변경된 경우에만
SELECT명령을 재실행하여 데이터를 다시 저장하며, 이 과정은 RDBMS가 자동으로 처리함 - 단, MySQL에서는 사용할 수 없으며 Oracle과 DB2에서만 사용할 수 있음
뷰를 구성하는 SELECT 명령은 단독으로 실행할 수 있어야 하지만, 부모쿼리와 연관된 경우에는 뷰의 SELECT 명령으로 사용할 수 없음
이 문제를 회피하기 위해 함수 테이블을 사용함
- 테이블을 결과값으로 반환해주는 사용자 정의 함수
- 함수에는 인수를 지정할 수 있기 때문에 인수의 값에 따라
WHERE조건을 붙여 결과값을 변경할 수 있고, 따라서 상관 서브쿼리처럼 동작함