삼팔광땡~ MYSQL 입문기 06 - 데이터베이스 객체, 스키마, 테이블 관리(create, insert, alter, drop), ALTER DEFAULT 설정, INDEX, EXPLAIN, VIEW

삼팔광땡~ MYSQL 입문기 06 - 데이터베이스 객체, 스키마, 테이블 관리(create, insert, alter, drop), 

 ALTER DEFAULT 설정, INDEX, EXPLAIN, VIEW

자, 이제 여섯보 전진.  어지저찌 무를 뽑을거 같은 이 이제 닦아보자! 

( 아, 미리 언지를 드리면 실습에 사용되는 테이블은 동일하지만 책에서 설명하고있는 예제와는 똑같지는 않습니다.  >< ) 

0. 이런 실수? 할 수 있다. 

비전공자 또는 낙엽줄로 개발 공부하면서 섭섭하게 느끼는 점이 하나 있다면 너무 당연?한 것은 알려주지 않는다는 것이다. 매번 포스팅을 작성하기 앞서 그런 내용들을 기억나는 데로 적어두려한다. 

1) 아직 생각나는 내용이 없어, 잠시 비워둠~ 니당 ;ㅇ

1. 데이터베이스 객체  - EXISTS 술어를 통해 다른 테이블의 상황을 판단(서브쿼리의 결과가 있고 없음을 판단)하여 UPDATE, SELECT, DELETE로 갱신할 수 있도록 서브쿼리를 사용할 수 있다.

1) 데이터베이스 내에 실체를 가지는 어떤 것을 말하는 것으로써 테이블, 뷰, 인덱스, 프로시저 가 그 대상이 될 수 있습니다. 

2. 스키마  - 순간 스칼라와 헷갈린건 정령 나 뿐인가?..   ;< 

1) 데이터베이스는 스키마 라는 그릇 안에서 만들어 진다고 힙니다. 따라서 객체(이를테면 테이블)의 이름은 같을 수 있어도 스키마가 같을 수는 없는거랄까요.

때문에 데이터베이스 객체를 그냥 스키마 라고 부르기도 합니다. 라고 하네요. 실제로 데이터베이스를 설계하고 실제로 구축하는 일련의 과정을 스키마 설계 라고 하기 때문 아닐까요. 더불어 스키마 설계는 DDL(데이터 정의어, Data Definition Language) 명령 규칙에 의해 진행됩니다.

마치 이런 헷갈림은 마치 열을 컬럼이라고 부르기도하고 행을 레코드라고 부르기도 하는 것과도 같은 헷갈림을 선사해주네요...하지만 책은 이에 아랑곳하지않고 본 챕터 말미에 헷갈림의 꽃을 선사하고 있습니다. 

스키마나 테이블은 네임스페이스이기도 하다.

+ α )  참고로 sql 과 관련해 알아두면 좋을 기본적인 이론이 정리되어 있는 블로그를 하나 걸어둡니다. 아는 데는 아니고요, 방금 구글링으로 찾은 처음 본 블로그입니다. 이곳은 감사하게도 복사 방지가 없네요. 

 SQL(Structured Query Language) 정의와 종류(DML, DDL, DCL, TCL) 

3. ALTER TABLE 로 테이블관리 - 테이블 최대 길이 연장, 열추가 해보기

1) alter는 수정하는 명령어이다. 그 전에 테이블 생성부터 해보자. 

(1) 아래 코드는 아래와 같이 선언되었습니다.

- 열 이름:no, 데이터타입:integer, 제약:null이면 안됨(값을 꼭 넣어줘야함)

- 열 이름:string, 데이터타입:varchar/사이즈는30, 제약:없음

- 열 이름:days, 데이터타입:date, 제약:없음

create 
table 
  sample62(
    no      integer not null,
    string  varchar(30),
    days    date
  );

 select * from sample62;

                (2) 아..차, select라니.. 뭘 기대한걸까요.. desc로 실행해보겠습니다.

 desc sample62;

2) 이번에는 생성한 테이블에 열(레코드) 데이터 추가해보겠습니다.

insert into sample62
values( 1, '꺄~', current_date);

insert into sample62
values( 1, 'hello', current_date);

insert into sample62
values( 1, 'world', current_date);

 select * from sample62;

3) 드디어 테이블을 수정해보자, 응답하라 ALTER 

(1) 테이블에 열을 추가 할 수 있다.   

alter table 
  sample62
add 
  addColum 
integer;

 desc sample62;

+ α )   아, 날이면 날마다 오는게 아닙니다~ 돌고돌아 

다시 돌아온 DEFAULT 설정(by ALTER 문) <-- 구글링 블로그 참고(링크)

α 01. 새로 컬럼을 추가하면서 디폴트 값을 지정하는 경우

alter table 
  sample62
add 
  addSetDefaultCol 
varchar(50) DEFAULT '디폴트값';

 desc sample62;

α 02. 이미 있는 컬럼에 디폴트 값만 추가로 지정하는 경우

a) alter modify column 방식

alter table 
  sample62
modify column  
  addSetDefaultCol varchar(50) DEFAULT '디폴트값 수정';

 desc sample62;

b) alter alter set 방식

alter table 
  sample62
alter addSetDefaultCol set DEFAULT '디폴트값 한번 더 수정';

 desc sample62;

(2) 열 속성을 변경할 수 있다.

alter table
  sample62
modify
  addColum
varchar(20);

 desc sample62;

(3) 열의 이름을 변경할 수 있다.

alter table
  sample62
change
  addColum reNameColum
varchar(20);

 desc sample62;

(4) 열을 삭제할 수 있다.

alter table
  sample62
drop
  reNameColum;

 desc sample62;

4. 제약정의

1) 처음부터 정의하기 

(1) 열제약 - 테이블을 생성할때 테이블이 포함하고 있는 열(컬럼)에 대한 제약을 정의할 수 있다

create table sample631(
  a integer not null,
  b integer not null unique,
  c varchar(30)
 );

 desc sample631;

(2) 테이블(기본키) 제약 - 복수열에 의한 기본키(primary key) 제약(한 개의 제약으로 복수의 열에 제약을 설명한다)

create table sample632(
  no1 integer not null,
  no2 integer not null,
  name varchar(30),
  primary key(no1, no2)
 );

 desc sample632;

(3) 기본키에 이름붙이기 - 테이블 제약에 CONSTRAINT 키워드를 사용하여 이름 붙이기 

create table sample632(
  no1 integer not null,
  no2 integer not null,
  name varchar(30),
  constraint pk_colkey primary key (no1, no2)
 );

 desc sample632;

*) 뭐지? 이해되지 않는 이슈가 있다. 

- PK 이름을 정의할때 named_4_key, named_4key를 이름으로 하면 아래와 같은 에러가 뜬다. 

- MySQL에서는 name_으로 시작하면 안되는 건가?, 혹시 이 아시는 분 계시면 고견을 구한다.

2) 나중에 제약 추가하기 

(1) 열 제약 추가 

desc sample631; 

alter table 
  sample631 
modify 
  c varchar(30) not null;

desc sample631; 

(2) 이름있는 기본키 제약 추가 - add constraint 프라이머리_키이름 primary key(컬럼이름)  사용

desc sample631; 

alter table 
  sample631 
add constraint 
  pk_s631 
primary key(a);

 desc sample631;

3) 제약 제거 

(1) 열 제약 제거 - 덮어쓰는 느낌으로 불필요해진 내용은 생략하고 필요해진 내용은 추가해서 (재)정의 

desc sample631; 

alter table 
  sample631 
modify c varchar(30);

 desc sample631;

(2) 테이블(기본키) 제약 제거 - drop primary key 사용

desc sample631; 

alter table 
  sample631 
drop primary key;

 desc sample631;

4) 기본키의 속성 

기본 키로 지정할 열은 not null로 제약이 설정되어 있어야한다.

(1) 테이블 제약과 열 제약을 포함한 테이블 생성 

create table sample634(
  p integer not null,
  a varchar(30),
constraint 
  pk_a634 
primary key(p)
);   

desc sample634;

(2) 행 추가

insert into sample634 values(1, '첫째줄');

insert into sample634 values(2, '둘째줄');

insert into sample634 values(3, '셋째줄');

select * from sample634;

(3) 역할 확인 

a) 중복 추가 -> 에러 발생 : 테이블을 생성할 때 'p'열을 기본키로 설정했다. 기본키로 설정하면 중복되는 값은 들어갈 수 없다. (2)번 실습 결과를 보면 테이블의 'p'열의 값 중에 이미 '2'라는 값이 있기 때문에 중복이 발생되어 에러가 발생되었다. 

insert into sample634 values(2, '2행에 중복해서 추가');

b) 중복 갱신  -> 에러 발생 : p가 3인 열에 2를 넣으면 앞의 행에 있는 2와 중복되어 상동의 원인을 발생시킨다.

update sample634 set p=2 where p=3;

5. INDEX(색인), EXPLAIN 

인덱스는 테이블에 이름 붙여진 색인이다. 용도는 검색 속도 향상이다. 인덱스는 '이진 트리(binary tree)', '해시(hash)'와 같은 알고리즘을 사용하여 검색한다. 

인덱스는 테이블과 독립된 데이터베이스 객체로 작성된다. 테이블에 추가되는(된) 데이터들은 항상 순차적으로 쌓이는 것은 아니지만 인덱스는 순차적으로 나열된다. 테이블 의존 객체로 테이블을 삭제하면 자동으로 함께 지워진다. 

인덱스는 데이터베이스의 객체의 하나로 DDL을 사용하여 작성하거나 삭제하지만 표준 SQL 명령은 없다. 인덱스 자체가 데이터베이스에 의존하는 선택적 항목으로 취급받기 때문이다. 그럼에도불구하고 모든 데이터베이스 제품에 인덱스가 도입되어있어 대부분 유사한 방법으로 인덱스를 사용할 수있다. 

1) INDEX(색인) 생성하기

create index indexNoName on sample62(no);

(1) 인덱스 확인하기

show index from sample62; 

2) INDEX(색인) 삭제하기 - index + 아까만든 인덱스명 + on + 테이블명 

drop index indexNoName on sample62;

3) EXPLAIN

실제로 인텍스를 활용하는지 확인 할 수 있는 명령어 

(1) 인덱스가 사용되는 사례를 실습하기 위해 (1)새로 인덱스를 생성하고 (2)인덱스가 적용된 컬럼에 대한 조건 검색을 진행해보겠습니다. 

-> possible_keys 와 key에 인덱스 이름인 indexNoName가 작성되어 있다. 

create index indexNoName on sample62(string);

explain select * from sample62 where string = 'hello';

(2) 인덱스가 사용되지 않는 사례를 -> possible_keys 와 key가 모두 Null이다 

 explain select * from sample62 where no = 1;

6.뷰 작성과 삭제

뷰는 가상테이블이라고도 한다. 결과가 저장된 것이 아니라 실행되기 전 select문이 저장되어 데이터베이스를 저장한 것과 동일한 결과를 도출하기 때문이다. 

데이터 베이스 객체가 될 수 없는 select 명령어들 기록해둘 수 있는 데이터베이스 객체로써 나름의 비유를 들자면 사용자 정의 단축키 정도가 되지 않을까 싶다. 서브쿼리 부분을 뷰로 활용하여 작성하면 복잡한 쿼리도 직관적으로 작성할 수 있다.

1) 뷰 생성 실습 - sample54 테이블과 같은(as) testView01 뷰테이블 를 만들어보자 

select * from sample54;

create view testView01 as select * from sample54;

select * from testView01;

2)  뷰를 삭제해보자 

drop view testView01;

3) 열을 지정해 뷰 작성하기 

select * from sample54;

create view testView02(n,v,v2) as select no, a, a*2 from sample54;

select * from testView02 where n=1;

3) 뷰의 단점

(1) 집계처리를 하거나 뷰가 참조하고 있는 테이블에 보관하고 있는 데이터가 많은 경우, 또는 뷰를 중첩해서 사용하는 경우 처리속도가 떨어지기 쉽니다.

(2) 부모 쿼리와 어떤 식으로든 연관된 서브쿼리의 경우에는 뷰의 select를 사용할 수 없다. 이와같은 이슈는 함수테이블을 사용하여 회피할 수 있다고 한다