SQL 첫걸음 CH06. 데이터베이스 객체 작성과 삭제

처음 RDBMS 소프트웨어를 설치하면 데이터베이스는 비어있는 상태다.

여기에 테이블이나 뷰, 인덱스, 프로시저 등의 데이터베이스 객체를 작성해 데이터베이스를 구축한다.

 

1 _ 데이터베이스 객체

 : 데이터베이스의 객체란 다른 프로그램에서 사용하는 객체와는 다른 의미다.

데이터베이스의 객체는 테이블, 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 요소를 말한다.

따라서 객체는 데이터베이스 내에 실체를 가지고있다.

객체를 조작하는 SQL의 명령(SELECT , INSERT)은 외부에서 온 것이기 때문에 실체가 없다

 

 - 데이터베이스 내부 명명규칙

기존 이름이나 예약어와 중복하지 않는다.

숫자로 시작할 수 없다.

언더스코어(_) 이외의 기호는 사용할 수 없다.

한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러싼다.

시스템이 허용하는 길이를 초과하지 않는다.

 

---

2 _ 스키마

 : 데이터베이스 객체는 스키마라는 그릇 안에 만들어진다. 따라서 객체의 이름이 같아도

스키마가 서로 다르다면 상관없다. 이와 같은 특징 때문에 데이터베이스 객체는 스키마 객체라고 불리기도 한다.

데이터베이스에서 테이블을 작성해서 구축해나가는 작업을 스키마 설계라고 한다.

각 데이터베이스 제품마다 스키마의 정의가 다르다

MySQL의 경우에는 CREATE DATABASE 명령으로 작성한 '데이터베이스'가 스키마가 된다.

 

스키마 내부에서는 이름이 중복되지 않도록 해야하고, 이름이 충돌하지 않도록 기능하는 그릇을 네임스페이스 라고 부른다.

 

---

3 _ 데이터베이스 작성

** mySQL은 내 컴퓨터의 이름이 한글로 설정되어있으면 다운로드가 되지 않는다.

 

1) MySQL 데이터베이스 생성

create database 데이터베이스명

 - sample 데이터베이스를 생성하는 명령어를 작성한 후 ctrl + enter를 누르자

 - 왼쪽의 스키마(Schemas)에 sample 데이터베이스가 생성되었다.

---

2) 테이블 작성

 : DDL을 이용해 스키마 내의 객체를 관리해보자.

DDL은 모두 같은 문법을 사용한다. CREATE로 작성, DROP으로 삭제, ALTER로 변경할 수 있다.

테이블을 작성하고 싶다면 CREATE TABLE 테이블명 , 뷰를 작성하고 싶다면 CREATE VIEW 뷰명

 

RDBMS에서 데이터베이스 상에 제일 먼저 만드는 것 중 하나가 바로 테이블이다.

테이블명 뒤에는 괄호로 묶어서 열을 정의할 수 있다

use 데이터베이스명  -> 어떤 데이터베이스에 테이블을 생성할지 지정해야함

CREATE TABLE 테이블명 (
열 정의1,
열 정의2,
...
)

테이블의 열구성 확인
DESC 테이블명;

테이블에 레코드 추가
1. INSERT INTO 테이블명(필드명1, 필드명2, 필드명3, ...)
   VALUES (데이터1, 데이터2, 데이터3, ...)
2. INSERT INTO 테이블명
   VALUES (데이터1, 데이터2, 데이터3, ...)

 - 열 정의

열명 자료형 [DEFAULT 기본값] [NULL|NOT NULL]

자료형은 INTEGER나 VARCHAR 등을 지정한다

* CHAR나 VARCHAR와 같은 문자열형으로 지정할 때는 최대길이를 괄호로 묶어준다.

CHAR(30)                                                   /           VARCHAR(30) 의 차이

고정길이( 무조건 칸수를 다 차지한다.)                  가변길이(최대값이 지정되있지만 부족한 값을 넣으면 축소된다)

 

기본값을 설정할 때는 DEFAULT로 지정하되, 자료형에 맞는 리터럴로 기술한다.

또한 기본값은 생략할 수 있다.

 

열이 NULL을 허용할 것인지 지정할 수 있다. NULL을 명시적으로 지정하거나 생략한 경우에는

NULL을 허용한다. NOT NULL로 지정한 경우에, 테이블에서 NULL을 허용하지 않는다.

 - sample 데이터베이스에 sample62를 생성했다.

이후 INSERT로 데이터 행을 추가할 수 있다.

 - DESC sample62; 명령어로 열 구성을 확인한 모습

 

---

3) 테이블 삭제

drop table 테이블명

 - sample 데이터베이스에 있던 sample62 table이 삭제되었다.

 

 - 데이터 행 삭제

테이블 정의는 그대로 둔 채 데이터만 삭제
delete from 테이블명
-> where 조건을 지정하지 않으면 테이브의 모든 행을 삭제한다.

delete명령은 처리속도가 느리다. 모든 행을 빠르게 삭제하려면 DDL로 분류되는
turncate table 테이블명
-> 삭제할 행을 지정할 수도 없고 where구를 지정할 수 없다.

 : where 조건을 지정하지 않으면 테이블의 모든 행을 삭제한다.

 

---

4) 테이블 변경

 : 테이블을 작성해버린 뒤에도 열 구성은 변경할 수 있다.

ALTER TABLE 테이블명 변경명령

ex) alter table sample62 add newcol int; -- newcol 열을 추가 (add)

변경 명령 종류

열 추가
ALTER TABLE 테이블명 add 열 정의

 - 열 정의는 create table과 동일하게 열 이름과 자료형을 지정하고 필요에 따라 기본값과 not null 제약을 지정하면 된다.

열을 ㅊ추가할 때 기존 데이터행이 존재하면 추가한 열의 값이 모두 NULL이 된다

열 속성 변경
ALTER TABLE 테이블명 MODIFY 열 정의

 - 열 이름은 변경할 수 없지만 잘형이나 기본값, NOT NULL 제약 등의 속성은 변경할 수 있다.

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

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

 

---

4 _ 제약

  : 테이블에 제약을 설정함으로서 저장될 데이터를 제한할 수 있다

제약조건

not null : 칸을 비워두면 안된다. ( 중복값은 허용한다 ) 

unique : 중복값을 허용하지 않는다. ( NULL은 허용 )

primary key : null도 넣을 수 없고, 중복된 값도 넣을 수 없다.

foreign key

check

등이 있고, table을 생성할 때 제약조건을 건다

 - sample62의 a,c에 값을 넣으려해도 제약조건에 걸린다.

-> no 필드에 NULL NO 가 설정되어 있으므로 no 필드에 값이 주어지지 않으면 행이 만들어지지 않는다.

 - 다음과 같이 no 필드에도 값을 주면 행이 생성된다.

 

여러가지 제약을 걸어둔 테이블을 만들어서 확인해보자.

 - sample631 테이블의 c 필드에 값을 넣으려고 해도, a 와 b에 걸려있는 제약 때문에

행을 생성하지 못 한다.

 - a,b 에도 value를 주고 select * from sample631 으로 확인해본 결과 행이 생성됐다.

 

제약 추가

열 제약 추가
ALTER TABLE sample631 MODIFY c VARCHAR (30) NOT NULL;
-> C열에 NOT NULL 제약을 걸었다

테이블 제약 추가
ALTER TABLE sample631 ADD CONSTRAINT pkey_sample631 PRIMARY KEY(a);
-> 기본키 제약을 추가했다.

 

제약 삭제

열 제약 삭제하기
ALTER TABLE sample631 MODIFY c VARCHAR(30);
-> C열의 NOT NULL 제약을 삭제했다. (아무런 제약을 걸지 않으므로서)

테이블 제약 삭제하기 -> 기본키는 테이블당 하나만 설정이 가능하기 때문에 두 가지 방법이 있다
ALTER TABLE sample631 DROP CONSTRAINT pkey_sample631;
ALTER TABLE sample631 DROP PRIMARY KEY;

제약에 이름을 설정해서 추가하고 삭제하는 방법

 

---

5 _ 기본키

: 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조를 기본키 제약 이라고 한다.

 

1) 테이블 작성, 행 추가하기, 중복행 추가하기

** 기본키가 두개라면?

 - 기본키를 구성하는 열은 복수라도 상관없다. 대신, 여러개의 기본키 열들이 다 합쳐져서 하나의 기본키가 된다

(자물쇠 번호 맞추는 것 처럼)  -> 슈퍼키 라고 한다.

 

후보키 : 기본키가 될 수 있는 열

대체키 : 기본키를 대체 할 수 있는 키

기본키 : 하나의 열로 구성된 유일성을 갖춘 키

슈퍼키 : 두 개 이상의 열을 합쳐서 구성된 유일성을 갖춘 키

외래키 : 두 테이블의 데이터 간 연결을 설정하고 강제 적용하여 외래 키 테이블에 저장될 수 있는 데이터를 제어하는 데 사용되는 열입니다. 외래 키 참조에서는 한 테이블의 기본 키 값을 가지고 있는 열을 다른 테이블의 열이 참조할 때 두 테이블 간에 연결이 생성됩니다

 

---

6 _ 인덱스

1) 인덱스 : 테이블에 붙여진 색인, 인덱스의 역할은 검색속도의 향상이다.

검색 이란 select 명령에 where 구로 조건을 지정하고 그에 일치하는 행을 찾는 과정이다.

 

인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성된다. 하지만 인덱스 혼자서는

아무런 의미가 없기 때문에 데이터베이스에서 테이블을 삭제하면 인덱스도 삭제된다.

---

2) 검색에 필요한 알고리즘

 - 인덱스를 사용하면 효율적으로 검색할 수 있는 이유 : 이진트리 or 해시 알고리즘을 사용하기 때문

 

이진트리를 이용한 탐색 방법을 이진 탐색이라고 한다.

---

3) 탐색 종류

 

풀 테이블 스캔

 : 인덱스가 지정되지 않은 테이블을 검색할 때는 풀 테이블 스캔을 한다.

테이블에 지정된 모든 값을 처음부터 차례로 조사하는 방법.

 

이진 탐색(binary search)

 : 차례로 나열된 집합에 대해 유효한 검색 방법, 처음부터 순서대로 조사하는 것이 아니고 집합을

반으로 나누어 조사하는 검색 방법 

-> 조사하고자 하는 데이터의 가운데 부터 찾고자하는 요소가 있는 쪽이 어딘지 판단해서 요소가 없는 부분은

버린다. 요소가 있다고 생각되는 부분에서 다시 가운데를 찾고 요소가 어느쪽에 위치하는지 판단한다.

찾고자하는 요소를 찾을 때 까지 반복한다.

 

이진 트리(binary tree)

 : 고속으로 검색할 수 있는 방법이지만 데이터가 미리 정렬되어 있어야한다.

이진 탐색과 비슷하지만 이진 탐색과 다른점은 데이터들의 가운데서 반으로 가르는 것이 아니라

기존에 저장되어 있던(미리 정렬되어있던) 노드를 기준으로 데이터를 찾는다.

-> 따라서 이진트리에는 중복값이 허용되지 않는 경우가 많다.

 

---

4) 인덱스의 작성과 삭제

---

7 _ 뷰

 : 뷰는 테이블 내부의 데이터안에 속할 수 있다.

간단하게 말하면 테이블 내부에 있는 데이터가 행렬이나 dictionary처럼 복잡현 형태일 수도 있다.

이 때 데이터를 좀 더 편하게 보기위해서 뷰로 설정한다.

뷰는 Select 명령이기 때문에 저장공간을 필요하지는 않지만 CPU 자원을 사용한다.