[혼공S] CH2. 실전용 SQL 미리 맛보기

건물을 짓기 위한 설계도: 데이터베이스 모델링

• 데이터베이스 모델링: 건축 설계도를 그리는 것처럼, 테이블의 구조를 미리 설계하는 개념. 데이터베이스 모델링은 우리가 살고있는 세상에서 사용되는 사물이나 작업을 DBMS의 데이터베이스 개체로 옮기기 위한 과정. 
• 우리는 테이블이라는 개체로 변환하게 됨. 
• 프로젝트를 구성할 때 폭포수 모델(Waterfall model)을 사용하고, 데이터베이스 모델링은 폭포수 모델의 업무 분석과 설계 단계에 해당. 

* 폭포수 모델

1. 프로젝트 계획 
2. 업무 분석
3. 시스템 설계
4. 프로그램 구현 
5. 테스트
6. 유지보수 

폭포수 모델의 가장 큰 단점은 폭포에서 내려가는 건 쉬워도 다시 거슬러 올라가는 것이 힘들다는 것. 하지만 단계가 명확하기 때문에 지금도 많이 사용되고 있음. 

 

• 기본 키(Primary key): 기본 키 열은 각 행을 구분하는 유일한 열을 말함. 예를 들어, 네이버의 회원 아이디, 학번, 주민등록번호와 같은 것들. 중복되어서도 안 되고 비어서도 안 됨. 
• MySQL에서 EER diagram을 이용하여 데이터베이스 모델링 구축을 진행할 수 있음. 
• 데이터베이스 구축 절차: 데이터베이스 만들기 → 테이블 만들기 → 데이터 입력/수정/삭제하기 → 데이터 조회/만들기 

 

데이터베이스 만들기 (Schema)

MySQL 창을 키면, 왼쪽의 Schemas 창에는 기본적으로 있는 데이터베이스 세 개가 있다. 여기서 나는 나만의 데이터베이스를 추가적으로 만들도록 한다. 

 

Schemas창에 마우스 커서를 대고 오른쪽 버튼을 누른 후 'Create Schema'를 누르면 새로운 데이터베이스를 만들 수 있다. 

'shop_db'라고 데이터베이스의 이름을 지정해준 후 오른쪽 아래의 apply를 클릭하면 생성된다. 

(사실 이렇게 코드 한 줄로 만들 수 있는데 메뉴를 통해서 만들어본 것이다. ^-^)

쫘라랑. 데이터베이스 만들어졌다! 

 

 

테이블 만들기 

테이블을 만들 데이터베이스의 삼각형 모양을 클릭한 후, Tables에 마우스 커서를 대고 오른쪽 마우스를 클릭한다. 

오른쪽 마우스를 클릭하면 아래와 같이 창이 뜨는데, 여기서 Create Table을 클릭한다. 

Table Name을 지정한다. (여기서는 슈퍼마켓의 고객에 대한 테이블을 만들기로 한다. 

Column name의 빈 칸을 더블클릭하면, 아래와 같이 column name을 수정할 수 있다. 해당 열의 Datatype이나 PK, NN 등의 여부를 체크하면 된다. 

테이블에 포함할 열에 대한 정보를 다 입력한 후 Apply를 누르면 table이 생성된다.

테이블 만드는 것도 마찬가지로 아래의 다섯 줄의 코드를 이용하여 만들 수 있다. 일단 여기서는 MySQL의 메뉴를 이용하여 만들어보기로 한다. 

쫘라랑, member와 product 두 개의 테이블을 만들었다! 

 

데이터 입력하기

데이터베이스와 테이블까지 만들었으니 테이블 안에 들어갈 내용을 입력해보기로 한다. 

왼쪽의 메뉴바에서 테이블 이름에 커서를 대고 오른쪽 버튼을 클릭한 후 'SelectRows-Limit 1000'을 선택한다. 

아래처럼 테이블이 보이면 데이터를 입력하면 된다. 

아래와 같이 데이터를 입력할 수 있고, 수정 또는 삭제도 용이하게 할 수 있다.

 

데이터 활용하기

쿼리문을 작성하여 데이터베이스이 데이터를 다음과 같이 활용할 수 있다. 

# shop_db 내의 member 테이블의 모든 데이터를 불러들이기
select * from member ; 

# member 테이블의 member_name과 member_addr column만 출력
select member_name, member_addr from member ; 

# member 테이블의 모든 열을 불러들이지만, member_name이 아이유인 경우만 출력
select * from member where member_name = "아이유" ;

 

데이터베이스 개체 

데이터베이스에서는 테이블 외에 인덱스, 뷰, 스토어드 프로시저, 트리거, 커서 등의 개체도 필요함. 

1. 인덱스: 데이터를 조회할 때 결과가 나오는 시간을 줄여줄 수 있음. 

 

2. 뷰: 테이블과 동일한 성격의 데이터베이스 개체. 보안이 강화되고 SQL문도 간단하게 사용할 수 있음. 

- 가상의 테이블

- 진짜 테이블에 링크된 것이라고 보면 됨. (뷰의 실체는 select 문이라고 보면 됨)

 

3. 스토어드 프로시저 (Stored procedure): MySQL에서 제공하는 프로그래밍 기능. 

 

## 인덱스 (index)
# member_name이 아이유인 모든 행을 출력
select * from member where member_name = "아이유" ; 

# 회원 테이블에 인덱스 만들기 
# on member(member_name) : member 테이블의 member_name의 열에 인덱스를 지정하라는 의미
create index idx_member_name on member(member_name) ;

# 인덱스를 만든 후에, 같은 데이터 출력 
# Key lookup : 인덱스를 통해 결과를 찾아냈다고 보면 됨. 이런 방법을 인덱스 검색(Index scan)이라고 함. 
select * from member where member_name = "아이유" ; 


## 뷰(view)
# member_view 만들기 
create view member_view 
as select * from member ; 

# 회원 테이블이 아닌 member_view에 접근 
select * from member_view ; 


## 스토어드 프로시저 (Stored procedure)
# 아래의 두 쿼리문이 계속 반복되는 문이라고 가정. 
select * from member where member_name = "나훈아" ;
select * from product where product_name = "삼각김밥" ; 

# 스토어드 프로시저 만들기
delimiter //
create procedure myProc() 
begin 
	select * from member where member_name = "나훈아" ; 
    select * from product where product_name =  "삼각김밥" ; 
end  //
delimiter ;

# 만든 procedure 호출하기
call myProc() ;

 

 

Reference

혼자 공부하는 SQL, 우재남 지음