SQL 3

원래 cmd로 공부했었는데, 얼마 전부터 MySQL Workbench를 사용하는 중이다.
아직 사용법은 익숙치 않지만 나중에 기회가 된 다면 더 자세히 다뤄야겠다.

6장 데이터베이스 객체 작성과 삭제

[데이터베이스 객체]

데이터베이스 내 실체를 가지는 어떤 것. (테이블, 뷰, 인덱스 …)

-이름을 가지며 중복 X
-다른 종류의 객체와도 중복이 되면 안됨

당연한 말이지만, SELECT 등의 명령어는 데이터베이스 내 실체를 가지지 않기에 객체가 아니다.

[스키마]

객체가 담기는 그릇
스키마가 다르면 이름이 같아도 괜찮음

[SQL명령의 종류]

• DML (Data Manipulation Language) 데이터 조작
  SELECT INSERT UPDATE DELETE

• DDL (Data Definition Language) 데이터 정의
  CREATE DROP ALTER

• DCL (Data Control Language) 데이터 제어

[테이블 작성]

CREATE TABLE 테이블명 ( (열 정의1) … )

(열 정의1) : 열명 자료형 DEFAULT default값 NULL허용여부

CREATE TABLE sample07(
	no INTEGER NOT NULL,
   	a VARCHAR(10),
    b DATE
);

[테이블 삭제]

DROP TABLE 테이블명;

만약 모든 행을 삭제하고 싶다면?
DML인 DELETE로 행을 삭제시 처리속도가 늦어지기에
DDL인 TRUNCATE TABLE명령으로 모든 행을 삭제할 수 있음(테이블은 X)

[테이블 변경]

ALTER TABLE
-열 추가,삭제,변경
-제약추가,삭제

-열 추가
ALTER TABLE 테이블명 ADD (열 정의);

열 추가시 기존에 존재하는 행의 추가된 열의 값은 NULL
DEFAULT를 지정했다면 그 값으로.

NOT NULL제약을 걸었다면, NULL이 아닌 DEFAULT값을 지정해줘야한다.

-열 속성 변경
ALTER TABLE 테이블명 MODIFY (열 정의);

기존 행이 존재하는 경우, 속성 변경에 따라 데이터도 변환됨,
자료형 변환시 기존 데이터의 자료형도 변환
!변환과정에서 에러 발생시 ALTER TABLE명령은 실행X!

-열 이름 변경
ALTER TABLE 테이블명 CHANGE 기존열이름 (신규열 정의);

-열 삭제
ALTER TABLE 테이블명 DROP 열명;

[제약]

-열 제약 : 열에 대해 정의하는 제약

-테이블 제약 : 한 개의 제약으로 복수의 열에 제약

-제약에 이름 붙이기 CONSTRAINT

CONSTRAINT pkey_sample PRIMARY KEY(no, sub_no)  

나중 관리를 위해 제약에 이름을 붙이는게 좋음.

-제약 추가 : 열제약과 테이블 제약은 서로 다른 방법으로 추가한다.

-열 제약추가

ALTER TABLE test1 MODIFY a int NOT NULL;  

-테이블 제약추각

ALTER TABLE test1 ADD CONSTRAINT pkey_test1 PRIMARY KEY(a);  

-제약 삭제

• 열 제약

ALTER TABLE test1 MODIFY a int;  

• 테이블 제약

ALTER TABLE test1 DROP CONSTRAINT pkey_test1;
ALTER TABLE test1 DROP PRIMARY KEY;

[기본키]

-테이블의 행 한 개를 특정할 수 있는 검색키 (검색키:검색할 때의 키워드)
-즉, 기본키로 설정된 열이 중복된 값을 가지는 것은 제약에 위반된다.

CREATE TABLE sample(
	p INTEGER NOT NULL,
	a VARCHAR(30),
	CONSTRAINT pkey_sample PRIMARY KEY(p)
);

sample테이블을 생성하고 열p를 sample 테이블의 기본키로 설정했다.

!기본키로 저장할 열은 NOT NULL 제약이 있어야함!

INSERT INTO sample VALUES(1,'FIRST');  
INSERT INTO sample VALUES(2,'SECOND');  
INSERT INTO sample VALUES(3,'THIRD');  

위와 같이 행을 추가 한 뒤, p열에 이미 있는 값인 행을 추가하거나, 이미 있는 값으로 수정할 경우 오류발생
=> 유일한 값을 가짐. 다른 의미에서 ‘유일성 제약’이라 불리기도 함

이는 고유제약(Unique Constraint)와 비슷한데, 고유제약은 NULL값을 허용하며, NULL값은 중복으로 취급하지 않는 차이가 있음.

-기본키를 구성하는 열은 복수도 가능함
만약 a,b열을 기본키로 지정했고 테이블이 다음과 같다하자.

a열	b열
1	1
1	2
1	3
2	1
2	2
2	3

이 경우 a열의 값이 1 1 1, 2 2 2로 같더라도 b열의 값이 다르기에 제약에 위반되지 않는다.
그러나 (a,b):(1,1)을 추가한다면 제약에 위반되어 실행되지 않음

[인덱스 구조]

-인덱스

• 테이블에 붙여진 색인으로 검색속도 향상의 역할을 한다.(WHERE의 과정)
• 검색 시 사용되는 키워드와 대응하는 데이터 행의 장소가 저장됨
• 독립된 데이터베이스 객체로 작성되지만, 테이블에 의존하는 객체임(테이블 삭제시, 인덱스도 삭제)
• 이진트리나 해시 알고리즘에 사용됨

[탐색방법]

-풀 테이블 스캔
-이진탐색
이진탐색은 정렬된 데이터에 사용하는 반면, 테이블의 행을 항상 정렬된 상태로 유지하기는 어려움

테이블에 인덱스 작성시, 이진트리 데이터 구조로 작성됨(중복된 값X)

[인덱스 작성 및 삭제]

MySQL, SQL Server : 테이블 내의 객체
Oracle, DB2 : 스키마 객체

DDL명령(표준X)
CREATE INDEX 인덱스명 ON 테이블명(열명, ... );

스키마 객체의 삭제
DROP INDEX 인덱스명;

테이블 내 객체의 삭제
DROP INDEX 인덱스명 ON 테이블명;

인덱스는 SELECT WHERE 명령의 처리속도가 빨라지나,
INSERT명령의 경우 인덱스를 갱신해야하기에 처리속도가 조금 떨어짐.

인덱스를 사용한 SELECT명령을 하려면, WHERE구에서 인덱스로 지정된 열을 사용해야함.

CREATE INDEX isample65 ON sample62(a);
SELECT * FROM sample62 WHERE a = 'aaa';

SELECT * FROM sample62 WHERE no = 1; 인덱스 사용X

[EXPLAIN]

명령 앞에 EXPLAIN을 붙여 어떤 상태로 실행되는지 확인
실제로 명령이 실행되는 것은 아님

EXPLAIN SELECT * FROM sample62 WHERE a='a';

possible_keys : 사용될 수 있는 인덱스
key : 사용된 인덱스

[실행계획]

• 데이터베이스 내부에서 명령 실행 전 실행계획을 세움
• EXPLAIN을 통해 실행계획을 확인
  WHERE에 사용된 열의 인덱스 유무를 확인 + 인덱스 사용여부를 판단
  예를들어 인덱스가 ‘예’, ‘아니오’ 값만 가진다면 이진트리라도 좋은 구조를 가질 수 없기에 인덱스를 사용하지 않을 가능성이 큼

[뷰 작성과 삭제]

DDL명령
CREATE VIEW 뷰명 AS SELECT명령;
CREATE VIEW 뷰명(열명, ...) AS SELECT명령; 
(열명 생략시 모든 열. 지정시, SELECT명령에서 열 지정해주기)

DROP VIEW 뷰명;

SELECT명령은 객체가 아니기에 명령에 이름을 지정할 수도, 데이터베이스에 등록할 수도 없음
이런 SELECT명령을 객체로서 이름을 붙여 관리할 수있도록 한 것을 ‘View’ 라고 함

뷰를 참조하면 SELECT명령의 실행결과를 테이블처럼 사용할 수 있음

복잡한 SELECT명령을 데이터베이스에 등록해 두었다가 나중에 간단히 실행할 수도 있음

뷰는 실체가 존재하지 않기에 가상테이블 이라 불리며 저장공간을 가지지 않음.
UPDATE INSERT DELETE명령에서도 조건이 맞으면 뷰를 사용할 수 있으나 주의가 필요함.

뷰의 단점
1)
저장공간을 사용하지 않으나, 참조시 명령을 실행하는 것.
뷰의 근원이 되는 테이블에 데이터양이 많은 경우 집계처리시 뷰를 사용하거나, 뷰를 중첩해 사용한 다면 처리속도가 떨어짐

• 이러한 단점을 보완하기위해 머티리얼라이즈드 뷰(Materialized View) 를 사용할 수 있음(MySQL X)
  일시적으로 데이터를 저장했다가 쿼리 종료시 삭제되는 뷰 와 달리 뷰를 저장장치에 저장해두고 사용함

뷰에 지정된 테이블의 데이터가 변경된 경우, RDBMS에서 자동으로 SELECT명령을 재실행해 데이터를 다시 저장함

2)
뷰를 구성하는 SELECT명령은 단독으로 실행 가능해야함
부모쿼리와 어떤식으로든 연관된 서브쿼리는 뷰의 SELECT명령으로 사용할 수 없다.

• 함수 테이블의 사용으로 해결
  함수 테이블 : 테이블을 결괏값으로 반환해 주는 사용자정의 함수