컴알못의 SQL 공부 (DDL, DCL)

목차

 

 

저번에는 DML에 대해서 알아보았습니다.

DML은 SQL의 기초라고 불리는데 그 이유는 가장 보편적으로 주어지는 권한이며, 데이터베이스에 조금이라도 맞닿아있는 사람이라면 누구나 사용할 수 있어야하기 때문이에요.

 

오늘은 DDL(Data Definition Language)에 대해서 알아보도록 합시다.

데이터 정의어는 단순히 DML처럼 데이터를 조작하는게 아니라 데이터베이스의 구조나 설정들을 정의하는데 사용합니다.

데이터베이스를 만들거나, 없애거나, 제약조건을 정의하거나 이런 기능들을 말하는거겠네요.

 

---

 

DDL

CREATE문

CREATE문은 테이블을 생성할 때 사용합니다.

데이터베이스가 다루는 가장 중요한 객체는 테이블입니다. 당연하죠. 관계형 데이터베이스는 데이터모델로 테이블 형식을 차용했으니까요. (사용자들에게 데이터베이스의 작동 구조를 이해시키기 위해 테이블 모형을 사용하지만 실제 데이터의 저장은 테이블처럼 저장되진 않고 좀 더 복잡한 처리를 거칩니다.)

 

CREATE TABLE은 테이블을 만들기 위해 테이블의 이름과 포함하는 속성과 속성들의 도메인 그리고 기본키 외래키 대체키 등의 제약조건까지 정의합니다.

CREATE TABLE 테이블
(
속성 데이터유형 (NULL&NOT NULL) (DEFAULT 기본값)
.
.
.
PRIMARY KEY 속성
UNIQUE 속성
FOREIGN KEY 속성 REFERENCES 테이블 속성
)

이게 CREATE문을 사용하는 기본 문법입니다.

DEFAULT는 값을 입력하지 않았을때 들어가는 기본값입니다. 생략하면 NULL이 설정됩니다.

PRIMARY KEY는 기본키를 지정하는 명령어로 개체 무결성을, FOREIGN KEY는 외래키를 지정하는 명령어로 참조 무결성을 만족하는 제약조건입니다.

 

데이터유형은 허용되는 속성의 값의 범위... 즉, 도메인을 말합니다.

CHAR, INT 같은 간단한건 넘기고 모르는 것만 살펴봅시다.

• VARCHAR : 가변길이 문자열
• TEXT : 긴 문자열(255자 이상)
• DECIMAL : 고정 소수점을 포함한 실수
• FLOAT : 단정도 부동 소수점 실수
• DOUBLE : 배정도 부동 소수점 실수
• DATE : 날짜(연도-월-일)
• DATETIME : 날짜시간(연도-월-일 시:분:초)
• TIME : 시간(시:분:초)
• YEAR : 날짜(연도)

날짜형도 도메인이 따로 분류되어있네요.

단정도 배정도는 뭔 말인지 모르겠습니다. 나중에 찾아볼게요.

 

그 외에는 뭐, PRIMARY KEY가 있는데 UNIQUE KEY가 굳이 필요하냐? 라고 생각했습니다만 UNIQUE KEY는 지정한 튜플에 중복을 허용하지 않는다는 제약조건이었습니다.

DESC 테이블

DESC 명령문은 테이블의 구조를 확인할 수 있습니다.

속성 이름이나 도메인 제약조건등을 말이죠.

그리고 테이블을 생성할 때의 CREATE문을 확인하고 싶다면 SHOW CREATE TABLE 테이블 을 입력하면 됩니다.

 

그리고 외래키에 대해서 알아보도록합시다.

다른 테이블을 참조하기 위해 기본키 속성을 외래키로 가져오는 걸 의미합니다. 당연히 외래키와 참조하는 테이블의 기본키의 도메인은 같아야겠죠.

참조하는 테이블이 자식 테이블이고 참조되는 테이블이 부모 테이블입니다.

부모테이블이 먼저 존재해야 자식테이블이 외래키를 가져올 수 있습니다.

반대로 자식테이블보다 먼저 부모테이블을 삭제해선 안됩니다.

그리고 참조 무결성 제약조건에 의해 외래키가 가지는 값들은 전부 이미 부모 테이블에 존재해야하는 값들입니다.

도메인이 같아야하는 건 물론이고, 부모테이블에 없는 값을 외래키가 가질 수는 없어요.

 

ALTER문

ALTER문은 테이블을 수정합니다.

테이블의 구조를 변경하거나, 제약조건 등의 설정을 변경하는게 가능해요.

 

하지만 기존에 운영되는 테이블에 이러한 수정을 가하면 거기에 대한 파급으로 에러가 발생할 수도 있기 때문에 가능하면 ALTER의 사용은 최소화하는 게 좋습니다.

 

ALTER의 문법은 대충 다음과 같습니다.

ALTER TABLE 테이블
(ADD&MODIFY) 속성 도메인 (NULL&NOT NULL) (DEFAULT 기본값)
| ADD CONSTRAINT 제약조건 제약조건내용
| DROP COLUMN 속성
| DROP CONSTRAINT 제약조건

보면은 ADD가 추가, MODIFY가 수정, DROP이 삭제의 역할을 하는 걸 볼 수 있죠.

그리고 제약조건에 수정을 가하고 싶으면 CONSTRAINT, 속성에 수정을 가하고 싶으면 COLUMN을 사용합니다.

 

그리고 이미 생성된 테이블의 속성 이름을 변경하는 것도 가능합니다.

ALTER TABLE 테이블 CHANGE 기존속성이름 새로운속성이름 새로운속성도메인 이런 식으로요.

CHANGE를 사용해서 속성의 설정값을 변화시킬 수 있어요. MODIFY와는 좀 다르게 동작하죠.

또 속성이 아니라 테이블의 이름을 변경하고 싶다라고 하면은 RENAME을 사용하면 됩니다.

ALTER TABLE 테이블 RENAME TO 새로운테이블이름 이런 식으로요.

 

DROP문

DROP문은 테이블을 삭제합니다.

삭제만 하면 그만인 명령문이기 때문에 문법도 아주 간단합니다.

DROP TABLE 테이블

하지만 위에서 언급했듯이 자식 테이블에게 참조되는 부모테이블의 경우에는 DROP명령문이 거절될 수도 있습니다.

그렇기 때문에 부모 테이블을 삭제하기 위해서는 외래키 관계를 먼저 정리해줘야 합니다.

 

---

 

DCL

DCL(DATA CONTROL LANGUAGE)은 권한 부여나 회복 같이 데이터베이스의 데이터를 제어하기 위한 명령어입니다.

권한을 부여하는 등의 특정 작업을 수행해요.

 

데이터베이스에는 여러 사용자가 있고 사용자별로 권한이 다르다고 했습니다.

사용자를 생성하고 권한을 주거나 뺏거나 하는 것도 DCL의 역할일 겁니다.

 

먼저 계정을 생성하는 방법부터 봅시다. 사실 계정의 생성은 DCL의 영역은 아닙니다. 사용자를 정의하기 때문에 DDL의 영역이죠. CREATE문을 사용해서 만들어봅시다.

CREATE USER 사용자계정 IDENTIFIED BY 비밀번호

저번 포스트에도 확인했었듯이 사용자 계정은 아이디@호스트 개념으로 묶여있었죠. 어디에서 접속을 할건지에 대해서도 명시가 필요했다고 적어두었습니다.

이렇게 CREATE 문을 사용해서 계정을 생성하는 거는 ROOT이라는 수퍼 사용자만 가능합니다. 수퍼 사용자는 최초의 계정으로서 모든 권한을 쥐고 있어요.

참고로 DROP문도 같은 방식으로 사용하여 사용자 계정을 삭제할 수 있습니다.

 

만약 이렇게 생성된 계정 정보를 조회하고 싶다면 SELECT문을 사용하면 됩니다.

테이블의 위치는 mysql의 user 테이블이에요. mysql은 사용자가 정의한 테이블 이외의 데이터베이스 운영에 필요한 시스템 데이터가 들어갑니다. 계정 정보도 그 일부에요.

SELECT host, user FROM mysql.user

GRANT문

계정을 생성한 뒤 계정을 부여해야 데이터베이스에 대해 특정 작업을 수행할 수 있겠죠.

권한부여는 GRANT문을 사용하여 이루어집니다.

GRANT 권한내용 ON 데이터베이스.테이블 TO 사용자계정 (WITH GRANT OPTION)

사용법은 위와 같습니다. 뒤에 WITH GRANT OPTION은 권한을 부여받은 사용자가 부여받은 권한을 다른사용자에게도 부여할 수 있도록 권한을 줍니다. 즉, 너도 GRANT를 사용할 수 있어! 라는 의민가봐요.

 

예제를 봅시다.

GRANT ALL ON *.* TO 'user'@'%' WITH GRANT OPTION

이건 user라는 계정한테 모든 데이터베이스와 모든 테이블을 대상으로 모든 권한(INSERT, UPDATE, DELETE, SEARCH)를 부여한다는 뜻입니다. (줄 수 있는 권한이래봤자 DML이네요)

계정이 가지고 있는 권한을 확인하기 위해서는 SHOW GRANTS FOR 'user'@'localhost'를 입력하면 됩니다.

 

REVOKE문

권한을 부여할 때는 GRANT, 철회하고 싶을 때는 REVOKE를 사용합니다.

즉, 권한을 다시 회수하는 명령어입니다.

REVOKE 권한내용 ON 데이터베이스.테이블 FROM 사용자계정

사용방법이 GRANT와 거의 같기 때문에 특별히 설명할 건 없습니다.

 

뷰

테이블 객체와 뷰 객체는 서로 다른 개념입니다.

뷰는 데이터베이스를 바라보는 창문이라고들 합니다. 저장된 테이블들을 서로 다른 관점에서 바라볼 수 있도록 해줍니다.

 

뷰는 실제로 데이터를 저장하지 않는 가상테이블입니다. SELECT문을 사용하면 검색결과로 테이블이 나오잖아요? 이 테이블은 저장된 데이터는 아니지만, 이걸 가상의 테이블로 정의하여 필요할 때만 실제 테이블처럼 사용할 수 있도록 만든게 뷰입니다.

실제로 존재하는 테이블은 아니지만 테이블처럼 사용하는거죠.

 

위에 말한것처럼 뷰는 SELECT문의 검색결과로서 만들어집니다.

뷰는 실제로 데이터베이스에 저장되지 않기 때문에 DBMS는 뷰를 만들기 위한 SELECT문만 저장하고 있다가, 사용자가 뷰를 사용하려고 할 때 저장한 SELECT문을 실행시켜서 즉석으로 검색 결과 테이블 뷰를 반환합니다.

뷰는 SELECT문으로 생성되기 때문에 여러 테이블을 참조하여 만들어질 수도 있습니다.

 

그럼 왜 이런걸 사용하는걸까요?

뷰의 장점은 다음 4가지로 정의할 수 있습니다.

1. 편의성 - 뷰 자체가 SELECT문의 검색 결과 테이블이기 때문에 복잡한 SELECT문을 수행하지 않고 VIEW로 저장을 하면 접근할 때마다 복잡한 SQL문을 입력하지 않아도 되는 편리함이 있습니다.
2. 보안성 - 실제 테이블에는 사용자에 따라서 접근이 제한되는 민감한 속성이 있을 수도 있기 때문에 VIEW를 사용하면 같은 테이블을 참조하더라도 다른 검색 결과를 보여줄 수 있습니다.
3. 재사용성 - 작성 빈도가 높거나 빈번하게 참조되는 검색 결과가 있다면 미리 뷰로 지정해놓는게 편리할 수 있습니다. 이건 위에서 언급한 편의성과 비슷한 의미 같군요.
4. 독립성 - 스키마의 변경으로부터 어느정도 자유롭다는 의미입니다. 테이블의 구조가 바뀌더라도, 뷰라는 테이블을 새롭게 수정하는게 아니라 그저 질의문만 바꿔주면 되기 때문입니다. 그렇기 때문에 데이터베이스 관리자도 뷰를 적극 활용하면 데이터베이스를 사용하는 응용 프로그램들에게 영향을 최소화하면서 자유롭게 스키마를 변경할 수 있다는 뜻입니다.

 

 

뷰의 생성도 CREATE문의 영역입니다.

CREATE VIEW 뷰이름 [속성리스트] AS SELECT 검색문

보면은 VIEW 생성을 위해 SELECT문을 사용한 걸 볼 수 있죠.

생성된 VIEW 역시 평범하게 SELECT문으로 검색가능합니다.

일반 테이블처럼 다뤄주면 돼요.

SHOW TABLES 라는 명령어를 입력하면 테이블들과 함께 목록에 나타나기도 합니다.

DESC를 통해 구조를 분석할 수도 있습니다.

 

 

뷰를 통해서 데이터를 검색하는것 외에도 데이터를 변경하는 것도 가능합니다. 하지만 제약이 붙어요.

뷰의 변경은 즉, 실제 테이블의 변경이기 때문에 만약 여러 테이블이 결합하여 만들어진 뷰라면 변경이 까다로울 수 있습니다.

 

변경할 수 없는 뷰의 조건은 다음과 같습니다.

• 기반테이블의 기본키를 포함하지 않았다.
• 집계 함수 결과를 포함하고 있다.
• DISTINC 키워드를 적용했다.
• GROUP BY 절을 적용했다.
• 다수의 기반 테이블의 조인으로 만들어졌다.
• 뷰에 포함되지 않은 기반 테이블의 열이 NOT NULL이다.

굉장히 까다롭다는 걸 알 수 있습니다.

 

뷰의 삭제는 일반 테이블처럼 DROP으로 처리하면 됩니다.

뷰를 삭제하더라도 기반 테이블은 삭제되지 않습니다.

반대로 테이블을 삭제하면 이걸 기반으로 삼던 뷰들은 전부 먹통이 됩니다.

 

인덱스

인덱스란 대량의 데이터를 빠르게 검색하기 위해서 필요한 겁니다.

즉, 인덱스란 테이블 안의 데이터를 쉽고 빠르게 찾을 수 있도록 만든 데이터베이스 객체입니다.

 

실제로 테이블의 데이터는 디스크와 같은 보조기억장치에 기억됩니다. 예, 하드디스크 같은 곳이요. 램 같은 곳에는 데이터가 계속 머물수가 없잖아요.

하지만 아시다시피 보조기억장치들은 주기억장치들에 비하면 끔찍하게 느립니다. 그래서 DBMS는 고유한 방식으로 데이터는 보조기억장치에 저장하고 주기억장치로 읽어옵니다.

 

문제는 보조기억장치의 접근횟수를 줄이고 싶다는 겁니다.

그러기 위해서 사용하는게 인덱스입니다.

 

대부분의 DBMS가 B트리 구조의 인덱스를 지원한다고 합니다.

자세하게 말하긴 힘들지만, 어쨌든 루트 노드와 가장 밑바닥인 리프 노드로 구성된 트리 구조가 존재하고, 가장 밑바닥인 루프 노드에 실제 데이터의 위치가 저장되어 있으며, 루트노드로부터 아래로 내려가면서 탐색을 진행합니다.

장점을 말하자면 모든 데이터로부터 일정 수준의 검색 시간을 보장한다고 하네요.

 

아무튼 인덱스를 사용을 하면, 테이블의 64번째에 존재하는 튜플을 찾기 위해 보조기억장치에 64번이나 접근하는 끔찍한 일 없이, 접근 전에 먼저 인덱스로 데이터가 저장된 위치를 파악하고 한번만 접근하면 된다는 이점이 있습니다.

 

유능한 CREATE가 역시 인덱스의 생성을 도와주네요.

CREATE (REVERSE) (UNIQUE) INDEX 인덱스이름 ON 테이블이름 (
	속성이름 (ASC|DESC),
	.
	.
	.
	)

이게 인덱스를 생성하는 방법입니다.

REVERSE는 인덱스를 역순으로 생성하는 옵션이고 UNIQUE는 테이블의 열 값에 중복이 없는 유일한 인덱스를 생성하도록 합니다. 기본키는 자동으로 UNIQUE가 설정되어 있습니다.

 

그리고 생성된 색인을 확인하려면 SHOW INDEX FROM 테이블을 입력하면 됩니다.

 

인덱스의 삭제는 역시 DROP으로.

DROP INDEX 인덱스이름 ON 테이블이름으로 처리해줍니다.

 

인덱스는 기능을 향상시키지만, 이 인덱스를 많이 사용한다고해서 반드시 검색 기능이 개선되는 건 아닙니다.

인덱스는 꼭 필요한 경우에만, 불필요한 인덱스는 공간 낭비일 수도 있어요.

인덱스를 적용하면 좋은 경우는 다음과 같습니다.

검색조건식으로 자주 사용하는 속성의 경우

조인조건식으로 자주 사용하는 속성의 경우

ORDER나 GROUP절에 자주 사용하는 속성의 경우

그리고 주의할 점은 하나의 테이블에 너무 많은 속성을 인덱스로 삼지 않도록, 그리고 되도록이면 정수형, 고정길이 문자형 속성에 인덱스를 설정하는 게 바람직하다는 겁니다.

갱신이 잦거나 도메인의 범위가 좁거나 튜플의 수가 적은 경우 역시 인덱스를 생성하지 않는 편이 좋습니다.