SQL 시리즈 (6장) 🪴

<SQL 첫걸음> 을 보고 직접 학습하고 기록하는 포스트! 🔥

DATABASE 마스터를 꿈꾸며...🔥

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

💚 25강 데이터베이스 객체

• RDBMS 솝트웨어를 설치하면 데이터베이스는 비어있는 상태! 여기에 테이블이나 뷰, 인덱스, 프로시저 등의 데이터베이스 객체를 작성해 데이터 베이스를 구축.

1. 데이터베이스 객체

• 데이터베이스 객체? 테이블이나 뷰, 인덱스 등 데이터베이스 내에 정의하는 모든 것을 일컫는 말.
  (🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪🤪)

• 객체는 데이터베이스 내에 실체를 가지는 어떤 것. 객체의 종류에 따라 데이터베이스에 저장되는 내용도 달라짐. 지금까지 학습한 것 중 객체에 해당하는 것은 테이블! (고로 select나 insert 등은 SQL명령어로 데이터베이스 내에 존재하는 것이 아니니까 객체가 아님~!)

• 객체 이름 붙일 때 중요한 것?

• 기존 이름이나 예약어와 중복하지 않기
• 숫자로 시작할 수 없음
• 언더스코어(_) 이외의 기호는 사용할 수 없음
• 한글을 사용할 때는 더블쿼트(MySQL에서는 백쿼트)로 둘러쌈
• 시스템이 허용하는 길이를 초과하지 않기
  ex) foo라는 이름의 테이블을 한번 만들면, 같은 종류의 테이블은 물론이고 뷰와 같은 다른 종류의 객체 역시 똑같은 이름으로 작성할 수 없음!

2. 스키마

• 데이터베이스 객체는 스키마라는 그릇 안에서 만들어짐. 객체의 이름이 같아도 스키마가 서로 다르다면 상관없음! 스키마A의 foo테이블, 스키마B의 foo테이블 -> NO 상관.
  -> 이와 같은 특징 때문에 데이터베이스 객체는 '스키마 객체'라고 불리기도 함. 실제로 데이터베이스에 테이블을 작성해서 구축해나가는 작업을 '스키마 설계'라고 부르기도 함. 이때 스키마는 SQL명령의 DDL을 이용해 정의.

• MySQL에서는 CREATE DATABASE명령으로 작성한 '데이터베이스'가 스키마가 됨.

• 스키마는 '네임스페이스(namespace)'라고 부르기도 함!

💚 26강 테이블 작성/삭제/변경

• 데이터베이스 객체인 테이블을 작성, 삭제, 변경하는 방법을 알아볼까!?

1. 테이블 작성

• DDL은 모두 같은 문법을 사용함. create로 작성, drop으로 삭제, alter로 변경.
  ex) 테이블을 작성한다면, create table, 뷰를 작성한다면 create view!

💚 create tables 테이블명(
열 정의1,
열 정의2,
...
) 💚

• 열명은 열에 붙이는 이름, 자료형은 integer나 varchar 등을 지정. 특히, char나 varchar와 같은 문자열형으로 지정할 때는 최대길이를 괄호로 묶어주기.

• 기본값은 default로 지정하되 자료형에 맞는 리터럴로 기술. 기본값 생략 가능.

• 열이 NULL을 허용할 것인지 지정가능. NOT NULL이라고 지정하면 제약이 걸리고 NULL허용 안됨.

• 그렇다면!!1 create table 명령으로 sample62 테이블을 정의한 뒤 desc로 열 구성을 확인해보장.

💚 create table sample62( # 테이블이름: sample62
    -> no integer NOT NULL, # 열이름 no, a, b
    -> a VARCHAR(30), # 각 테이블의 특성들, integer, varchar, date
    -> b DATE); 💚

2. 테이블 삭제

💚 drop table 테이블명 💚

• 테이블삭제 가능 ! ('진짜 삭제할래?' 라고 안물어보니까 조심해서 삭제하기! 👊)
• 데이터 행 삭제: drop table 명령은 데이터베이스에서 테이블을 삭제하는데 이때 테이블에 저장된 데이터도 함께 삭제. <-> 테이블은 그대로 두고 데이터만 삭제할 때는 delete 명령을 사용. 이때도 delete 명령에 where 조건을 지정하지 않으면 테이블의 모든 행을 삭제할 수 있음!
• delete 명령은 행 단위로 여러 가지 내부처리가 일어나므로 삭제할 행이 많으면 처리속도가 상당히 늦어짐. 이때는 DDL로 분류되는 TRUNCATE TABLE 명령을 사용!
• TRUNCATE TABLE 명령은 1)삭제할 행을 지정할 수 없고 2)where구도 지정 못하지만 3)모든 행을 삭제해야 할 때 빠른 속도로 삭제할 수 있음!

💚 truncate table 테이블명 💚 

3. 테이블 변경

• 테이블 작성해도 열 구성은 언제든 변경가능! 이때 테이블 변경은 alter table 명령을 통해 이루어짐!

💚 alter table 테이블명 변경명령 💚 

• 테이블을 작성한 뒤에도 열을 추가하거나 데이터 최대길이를 변경하는 등 구성을 바꿔야 하는 경우가 종종 생김. 이때 테이블이 비어있으면 drop table로 테이블 삭제하고 변경할 테이블 구조에 맞춰 create table을 실행해 테이블을 변경할 수 있음!

• 근데 테이블에 데이터가 이미 존재하면 drop table로 테이블을 삭제하는 순간 기존 데이터도 모두 삭제됨 ㅠㅠ

• 이때 alter table 명령을 사용하면 테이블에 저장되어 있는 데이터는 그대로 남긴 채 구성만 변경 가능.

• alter table 기능: 1) 열추가, 삭제, 변경 2) 제약 추가, 삭제
  1) 열추가

💚 alter table 테이블명 add 열 정의 💚
💚 alter table sample62 add newcol integer; 💚 # newcol(열이름) integer(자료형속성)

• 여기서 주의할 것은!! NOT NULL 제약을 붙인 열을 추가하고 싶다면 먼저 NOT NULL로 제약을 건 뒤에 NULL 이외의 값으로 기본값을 지정할 필요가 있음!

• 열 속성 변경: alter table 열 속성을 변경할 경우에는 다음과 같이 modify 하부명령을 실행함.

💚 alter table 테이블명 modify 열 정의 💚

• 이때도 열 정의는 create table의 경우와 동일함. modify로 열 이름은 변경할 수 없지만, 자료형이나 기본값, not null 제약 등의 속성은 변경할 수 있음!

💚 alter table sample62 modify newcol varchar(20); 💚

-> 요렇게 int에서 varchar로 타입형이 바뀜!

• 열 이름 변경: alter table로 열 이름을 변경할 때는 change 하부명령으로 시행할 수 있음!

💚 alter table 테이블명 change [기존 열 이름] [신규 열 정의] 💚

• 열 이름을 변경할 때는 Modify 말고 change 사용!! change는 열 이름뿐만 아니라 열 속성도 변경할 수 있음!
  

• 열 삭제: alter table로 열을 삭제할 때는 drop 하부명령을 사용함.

💚 alter table 테이블명 drop 열명 💚
💚 alter table sample62 drop c; 💚

4. alter table로 테이블 관리

• 최대길이 연장: 대규모 데이터베이스에서는 데이터의 크기가 엄~~~청 커질수가 있음! 특히 행 개수가 많은 테이블에서는 데이터 하나의 크기를 최적화하는 것만으로도 저장 공간을 절약할 수 있음!
• 이럴 때 alter table로 열의 자료형만 변경해 대응할 수 있음!

💚 alter table sample modify col varchar(30) 💚 

• 주의할 점! 기존의 행에 존재하는 데이터의 길이보다 작게 지정할 수는 없음!

• 열 추가: 테이블에 열을 추가하는 일은 자주 일어남. alter table 명령을 써서!

💚 alter table sample add new_col integer 💚

💚 27강 제약

• create table로 테이블을 정의할 경우, 열 이외에 제약도 정의할 수 있었음! 그중 하나가 바로 NOT NULL! NOT NULL말고도 다양한 제약이 있음! 알아보장...^^

1. 테이블 작성시 제약 정의

• 제약은 테이블에 설정하는 것. create table로 테이블을 작성할 때 제약을 같이 정의함. 물론 alter table로 제약을 지정하거나 변경할 수 있음! 이때 NOT NULL 제약 등 하나의 열에 대해 설정하는 제약은 열을 정의할 때 지정함.

💚 create table sample631(
a integer NOT NULL, # a 열에는 NOT NULL 제약이 걸려있음!
b integer NOT NULL UNIQUE, # b열에는 NOT NULL + UNIQUE 제약.
c VARCHAR(30)
); 💚

• a,b 열에는 각각 제약이 걸려있는데 이걸 '열 제약'이라고 부름. 반면, 한 개의 제약으로 복수의 열에 제약을 설명하는 제약을 설명하는 경우 '복수열에 의한 기본키 제약' 이라고 부름!

💚 create table sample 632(
no INTEGER NOT NULL,
sub_no INTEGER NOT NULL,
name VARCHAR(30),
PRIMARY KEY (no, sub_no)
); 💚

-> 열은 no, sub_no, name으로 세개, integer 자료형은 not null 제약 걸어주고, name 열은 null true가 되어있다. 그리고 no, sub_no 열은 primary key로 지정!

• 제약에는 이름을 붙일 수 있음! 이름 붙이면 관리가 쉬워지게찌~~~
• 제약 이름은 constraint 키워드를 사용해 지정! 🌱

💚 create table sample632(
no integer NOT NULL,
sub_no INTEGER NOT NULL,
name VARCHAR(30),
CONSTRAINT 🌱pkey_sample🌱 PRIMARY KEY(no, sub_no) # 제약이름은 pkey_sample!
); 💚

2. 제약 추가
   기존 테이블에도 나중에 제약을 추가할 수 있음. 이때 열 제약과 테이블 제약은 조금 다른 방법으로 추가.
   1) 열 제약 추가

• 열 제약을 추가할 경우 alter table로 열 정의를 변경 가능. 기존 테이블 변경할 때는 제약을 위반하는 데이터가 있는지 먼저 검사함.
  ex) c열에 NULL값이 존재한다면 alter table 명령은 에러가 발생. c열에 NOT NULL 제약을 설정해보장!

💚 alter table sample632 modify name varchar (30) NOT NULL; 💚

요렇게 바뀌어찌~~~ 😇

-> sample632테이블의 name열을 NOT NULL로 제약 설정함!

2) 테이블 제약 추가
alter table의 add 하부명령으로 추가할 수 있음! 기본키 제약을 추가해볼까아...?^^ 기본키는 원래 테이블에 하나만 설정할 수 있음! 그래서 이미 기본키가 설정되어 있는 테이블에 추가로 기본키를 작성할 수 없듬! 또, 열 제약을 추가할 때와 마찬가지로 기존의 행을 검사해 추가할 제약을 위반하는 데이터가 있음 ㅇㅔ러 발생!

💚 alter table sample632 add constraint pkey_sample632 primary key(a); 💚

3. 제약 삭제

• 테이블 제약은 나중에 삭제 가능. 열 제약의 경우, 제약을 추가할 때와 동일하게 열 정의를 변경. 다음은 앞서 추가한 c 열의 NOT NULL 제약을 삭제하는 alter table의 예시!

💚 alter table sample632 modify sub_no varchar(30); 💚

• 테이블 제약은 alter table의 drop 하부명령으로 삭제할 수 있음! 삭제할 때는 제약명을 지정함. 근데 제약명을 지정하지 않고도 삭제는 가능 !

💚 alter table sample633 drop primary key; 💚

-> 요렇게 기본키 제약을 삭제해쥼 ㅎㅎ

4. 기본키

• 정리: NOT NULL 제약은 열 제약, 기본키 제약이 테이블 제약.
• 이때, NOT NULL 제약을 설정하려면 대상 열에는 NULL 값이 존재하지 않아야 함.
• 그럼, 기본키 제약을 설정하려면 테이블은 어떤 조건을 만족해야할까??

1)먼저 create table로 테이블 만들어주기!

💚 create table sample634(p INTEGER NOT NULL, a VARCHAR(30), CONSTRAINT pkey_sample634 PRIMARY KEY(p)); 💚

• 열 p가 sample634 테이블의 기본키. (+기본키로 지정할 열은 NOT NULL 제약이 설정되어 있어야함!!!👊)

• 기본키는 테이블의 행 한 개를 특정할 수 있는 검색키!(검색키는 검색할 때의 키워드라고 이해하장!🐼)

• 기본키로 설정된 열이 중복하는 데이터 값을 가질 수 없다~!

• 먼저 insert를 사용해서 sample634에 세개의 행을 추가함!

💚 insert into sample634 values(1, '첫째줄');
💚 insert into sample634 values(2, '둘째줄');
💚 insert into sample634 values(3, '셋째줄');

• 이때 p 열의 값이 각각 1,2,3으로 중복하지 않음! 그럼 이미 존재하는 값인 2로 다시 한번 행을 추가해볼까아~?ㅎㅎ
  
  -> 중복돼서 에러가 뜬당! 기본키제약에 위반됐다는 뜼!

• update 명령을 실행할 때도 제약을 위반하는 값이 없는지 검사함!

• p가 3인 행을 2로 갱신하는데 성공하면, p=2인 열이 두개나 존재하기 때문에 update 명령 역시 제약에 위반되므로 실행되지 않음.

💚 update sample634 SET p=2 where p=3; 💚

• 요렇게 에러가 뜸.(p는 primary key니까)

-> 이처럼 열을 기본키로 지정해 유일한 값을 가지도록 하는 구조가 바로 기본키 제약!

• 복수의 열로 기본키 구성하기: 기본키 제약에는 이를 구성할 열 지정이 필요함. 이때 지정된 열은 NOT NULL 제약이 설정되어 있어야 함. 즉, 기본키로는 NULL값이 허용되지 않음.
• 기본키를 구성하는 열은 복수라도 상관이 없음. 복수의 열을 기본키로 지정했을 경우, 키를 구성하는 모든 열을 사용해 중복하는 값이 있는지 없는지를 검사함.

💚 28강 인덱스 구조

• '색인'이라고도 불리며 인덱스는 데이터베이스 객체 중 하나.
• 테이블에는 인덱스를 작성할 수 있음!

1. 인덱스

• 인덱스는 테이블에 붙여진 색인이라 할 수 있음. 인덱스의 역할은 검색속도의 향상.
  -> 여기서 '검색'이란 select 명령에 where 구로 조건을 지정하고 그에 일치하는 행을 찾는 일련의 과정을 말함.
  -> 이 책의 목차나 색인 역시 인덱스라고 생각하면 좀더 이해하기 쉬울 것.
  -> 인덱스는 테이블과는 별개로 독립된 데이터베이스 객체로 작성됨. 하지만 인덱스만으로는 아무런 의미가 없음. 인덱스는 테이블에 의존하는 객체라 할 수 있음. 대부분의 데이터베이스에서는 테이블을 삭제하면 인덱스도 같이 삭제됨.

2. 검색에 사용하는 알고리즘

• 데이터베이스의 인덱스에 쓰이는 대표적인 검색 알고리즘으로는 '이진 트리(binary tree)'가 있으며 그 다음으로 '해시'가 있음!

• 풀 테이블 스캔(full table scan)
  인덱스가 지정되지 않은 테이블을 검색할 때는 풀 테이블 스캔이라 불리는 검색방법을 사용. 행이 1,000건 있다면 최대 1,000번 값을 비교함

• 이진 탐색(binary search)
  이진 탐색은 차례로 나열된 집합에 대해 유효한 검색 방법. 처음부터 순서대로 조사하는 것이 아니고 집합을 반으로 나누어 조사하는 검색방법.

• 이진 트리(binary tree)
  이진 탐색은 고속으로 검색할 수 있는 탐색 방법이지만 데이터가 미리 정렬되어있어야 함.

3. 유일성
   이진 트리의 구조를 살피다 보면, 같은 값을 가지는 노드가 여러 개 있을 때의 결과에 대한 의문이 생길 수도 있음. 사실 이진 트리에서는 집합 내 중복하는 값을 가질 수 없음. 즉, 노드의 가지는 큰 쪽과 작은 쪽의 두 가지로 나뉘며, 같은 값을 허용하기 위해서는 '같은'이라는 제 3의 가지를 가질 필요가 있음.
   -> 이진 트리에는 중복하는 값을 등록할 수 없음!

💚 29강 인덱스 작성과 삭제

• 실제로 테이블에 인덱스를 작성하는 방법에 대해서 알아보장!

💚 create index
💚 drop index

• 인덱스는 데이터베이스 객체의 하나로 DDL을 사용해서 작성하거나 삭제함.
• 표준 SQL에는 create index 명령은 없음.

1. 인덱스 작성

• 인덱스는 create index 명령으로 만든당! 인덱스에 이름을 붙여 관리하는데, 데이터베이스 객체가 될지 테이블의 열처럼 취급될지는 데이터베이스 제품에 따라 다름! SQL Server나 MySQL에서 인덱스는 테이블 내 객체가 됨. 그래서 테이블 내 이름이 중복되지 않도록 지정해 관리!

• 인덱스를 작성할 때는 해당 인덱스가 어느 테이블의 어느 열에 관한 것인지 지정할 필요가 있음. 열은 복수로도 지정가능.

💚 create index 인덱스명 on 테이블명 (열명1, 열명2, ...) 💚

2. 인덱스 삭제

• 인덱스는 drop index 명령으로 삭제함. drop 할 때는 다른 객체와 동일하게 인덱스 이름만 지정하면 됨. 다만 테이블 내 객체로서 작성하는 경우에는 테이블 이름도 지정함.

💚 drop index 인덱스명 on 테이블명 💚

-> 위에서 만들어놓은거 지워줌!

• 인덱스는 테이블에 의존하는 객체임. drop table로 테이블을 삭제하면 테이블에 작성된 인덱스도 자동으로 삭제. 인덱스만 삭제하는 경우에는 drop index 사용.

• 인덱스를 작성해두면 검색이 빨라짐. 작성한 인덱스의 열을 where 구로 조건을 지정해 select 명령으로 검색하면 처리속도가 향상.

💚 create index isample65 On sample634(a); 💚

-> sample634 테이블에 isample65라는 인덱스를 생성함.
-> where 구에 a 열에 대한 조건식을 지정한 경우, select 명령은 인덱스를 사용해 빠르게 검색 가능.

💚 select *from sample634 where a = 'a'; 💚

-> 그치만 where 구의 조건식에 a열이 전혀 사용되지 않으면 select 명령은 isample62라는 인덱스를 사용할 수 없음.

3. EXPLAIN

• 인덱스 작성을 통해 쿼리의 성능 향상을 기대할 수 있음 ! 실제로 인덱스를 사용해 검색하는지를 확인하려면 EXPLAIN 명령을 사용함!

💚 EXPLAIN SQL 명령 💚

• EXPLAIN에 뒤이어 확인하고 싶은 select 명령 등의 SQL 명령을 지정하면 됨! 다만 이 SQL 명령은 실제로는 실행되지 않음 ㅠㅠ 어떤 상태로 실행되는지 데이터베이스가 설명해줄 뿐!

• EXPLAIN은 표준 SQL에는 존재하지 않는, 데이터베이스 제품 의존형 명령!

💚 EXPLAIN select * from sample634 where a = 'a'; 💚

• sample634의 a열에는 isample65라는 index가 작성되어있음. EXPLAIN의 뒤를 잇는 select 명령은 a 열의 값을 참조해 검색하므로 isample65을 사용해 검색함.

• 그럼 where 조건을 바꾸면 어떻게 변할까?

EXPLAIN select * from sample634 where no > 10;

-> a 열을 사용하지 않도록 조건을 변경하면 인덱스 사용 NONO!

4. 최적화

• 내부 처리에서 select 명령을 실행하기에 앞서 실행계획을 세움. 실행계획에서는 '인덱스가 지정된 열이 where 조건으로 지정되어 있으니 인덱스를 사용하자'와 같은 처리가 이루어짐. EXPLAIN 명령은 이 실행계획을 확인하는 명령.

• 실행계획에서는 인덱스의 유무뿐만 아니라 인덱스를 사용할 것인지 여부에 대해서도 데이터베이스 내부의 최적화 처리를 통해 판단.

💚 30강 뷰 작성과 삭제

• 뷰는 테이블과 같은 부류의 데이터베이스 객체 중 하나!

💚 create view 뷰명 as select명령
💚 drop view 뷰명

• from 구에 기술된 서브쿼리에 이름을 붙이고 데이터베이스 객체화하여 쓰기 쉽게 한 것을 뷰라고 함!

1. 뷰

• 데이터베이스 객체란 테이블이나 인덱스 등 데이터베이스 안에 정의하는 모든 것을 말함! 뷰도 데이터베이스 객체 중 하나! <-> 반면, select 명령은 객체가 아님. select 명령에 이름을 지정할 수도 없고 데이터베이스에 등록되지도 않기 떄문.
• 이처럼 본래 데이터베이스 객체로 등록할 수 없는 select 명령을 객체로서 이름을 붙여 관리할 수 있도록 한 것이 뷰.
• select 명령은 실행했을 때 테이블에 저장된 데이터를 결괏값으로 반환. 따라서 뷰를 참조하면 그에 정의된 select 명령의 실행결과를 테이블처럼 사용할 수 있음!

-> 뷰는 select 명령을 기록하는 데이터베이스 객체!

• from 구에는 서브쿼리를 사용할 수 있었음!

💚 select * from (select * from sample634) sq; 💚

-> 요런식으로!

• 앞의 예에서 서브쿼리 부분을 '뷰 객체'로 만들면 다음과 같은 select 명령이 됨.

💚 select * from sample_view_67; 💚

• 자주 사용하거나 복잡한 select 명령을 뷰로 만들어 편리하게 사용할 수 있음!

• 가상 테이블: 뷰는 테이블처럼 취급할 수 있찌만, '실체가 존재하지 않는다'는 의미로 '가상 테이블'이라 불리기도 함.

• select 명령으로 이루어지는 뷰는 테이블처럼 데이터를 쓰거나 지울 수 있는 저장공간을 가지지 않음. 이 때문에 테이블처럼 취급할 수 있다고는 해도 'select 명령에서만 사용'하는 것을 권장함.

2. 뷰 작성과 삭제

• 뷰는 데이터베이스 객체이기 때문에 DDL로 작성하거나 삭제.

• 작성: create view, 삭제: drop view.

• 뷰 작성

💚 create view 뷰명 as select 명령 💚 # as 생략 불가능!

• create view로 뷰를 작성한 뒤 select 명령의 from 구에 지정해 사용할 수 있음. 뷰는 필요에 따라 열을 지정할 수도 있는데, 이 경우에는 이름 뒤에 괄호로 묶어 열을 나열함.

💚 create view 뷰명(열명1, 열명2, ...) as select 명령 💚

• 뷰의 열 지정을 생략한 경우에는 select 명령의 select 구에서 지정하는 열 정보가 수집되어 자동적으로 뷰의 열로 지정됨! 반대로 열을 지정한 경우에는 select 명령의 select 구에 지정한 열보다 우선됨.

• 뷰 삭제

💚 drop view sample_view_67; 💚

3. 뷰의 약점

• 뷰는 데이터베이스 객체로서 저장장치에 저장됨. but 테이블과 달리 대량의 저장공간을 필요로 하지 않음! 데이터베이스에 저장되는 것은 select 명령뿐이기 때문.

1) 머티리얼라이즈드 뷰

• 뷰의 근원이 되는 테이블에 보관하는 데이터양이 많은 경우, 집계처리를 할 때도 뷰가 사용된다면 처리속도가 많이 떨어짐. -> 이를 위해 머티리얼라이즈드 뷰를 사용!
• 머티리얼라이즈드 뷰는 데이터를 일시적으로 저장해 사용하는 것이 아니라 테이블처럼 저장장치에 저장해두고 사용! 1. 뷰가 처음 참조되었을 때 데이터를 저장 2. 이후 다시 참조할 때 이전에 저장해 두었던 데이터 그대로 사용.
• MySQL에서는 머티리얼라이즈드 뷰를 사용불가.

2) 함수테이블

• 서브쿼리의 경우 뷰의 select 명령으로 사용 불가. -> 함수테이블을 사용!
• 함수 테이블은 테이블을 결괏값으로 반환해주는 사용자정의 함수! 함수에는 인수를 지정할 수 있기 때문에 인수의 값에 따라 where 조건을 붙여 결괏값을 바꿀 수 있음 ! 그에 따라 상관 서브쿼리처럼 동작할 수 있음!

🦖 6장 완료...! 어지러워... 그렇지만 할수이따! 🦖