닷넷 게시판 만들기 Part 22 - DB 다루기. (테이블 만들기, 쿼리문 만들기)

2011/02/16 - [ⓟrogramming/asp.net 게시판] - 닷넷 게시판 만들기 Part 20
2011/02/18 - [ⓟrogramming/asp.net 게시판] - 닷넷 게시판 만들기 Part 21

1. 닷넷 개발환경 준비, 테스트
2. 닷넷 알아보기 [7/7]
3. asp.net 컨트롤 [10/10]
4. 데이터베이스(DB) [3/..]

이 강의에서 다루는 DB관련 내용은 복잡할 수 있는 내용은 제외하려고 합니다.
깊숙한 DB학습을 위해서는 타 웹사이트에서 참고하는 것이 더 좋습니다.
(그렇다고 내용 자체가 막장(?)은 아니니 걱정마세요)

이전시간에 다뤘던 내용이다.

"DB를 알아야되는 이유는 우리가 직접 DB에 자료를 넣거나 빼야되기 때문이다"

[중요1] "DB는 테이블(Table)의 묶음이다.'
[중요2] 테이블은 1개이상의 컬럼이 꼭 존재해야 자료를 담을 수 있다.
하나 더 추가하면,
[중요3] ASP.NET과 DB는 아~무 관련없다. 자료저장소가 DB일 뿐이다.
[중요4] ASP.NET에서 DB서버로 내리는 명령을 SQL이라고 한다.
[중요5] DB서버는 받은 명령이 자료 요청이라면 결과를 응답할 것이다.

asp.net 과 DB는 아~무 관련없다는 점에 일단 집중하고, 4장 처음에 설치한 후 실행했던 DB관리툴을 실행해보자.
SQL Server Management Studio - 명칭이 길기 때문에 그냥 관리툴이라 부르겠다.

 

서버에 연결하는 창이 뜨면, 그 때 접속했던 방법처럼 "SQL Server 인증", "sa", "****" 를 이용해서 접속하자. 암호 저장에 체크해놓으면 편리하다.

#참고
Web(HTTP)의 기본 네트워크 Port 는 80번이다.
SQL Server의 기본 네트워크 Port 는 1443 이다. 그냥 참고하자. 서버프로그램은 접속하기 위한 포트를 열어놓게 된다. 지금 우리가 사용중이 컴퓨터는 웹서버 기능과 동시에 DB서버가 돌아가고 있지만, Port 번호가 다르기 때문에 가능한 것이다. IP도 필수다. 로컬(localhost, 127.0.0.1)이기 때문에 컴퓨터 이름으로 접속이 가능한 것이다. 실제 외부서버 접속은 서버 도메인 또는 서버의 IP로 접속할 수 있다. Port 는 관리자 임의로 변경가능하다. 실제 현업에서는 변경하는 것이 일반적이다. 알려진 Port 를 사용하는 자체가 보안에 취약하기 때문이다.

데이터베이스를 만들어보자.

위 그림처럼 데이터베이스 오른쪽 버튼, 새 데이터베이스를 선택한다.

asp.net 으로 연동하는 목적이니, 필자는 데이터베이스 이름을 aspnet 으로 하겠다. 여러분들은 아무거나 자신이 마음에 드는 것으로 해도 상관없다. DB명만 알면 되니까.

아래쪽에 있는 "소유자"는 보안에 관련된 것이다. 특정한 사람만 사용하게 하는 목적일 수 있겠다. 학습용은 그냥 기본값으로 두자. 우리가 관리자이다.
그 하단에 목록화 된 것은 그냥 읽어보기만 하고 넘어간다.
(프로그래머들은 나와 관련없어도, 이 강의에서 다루지 않더라도 뭐든지 눈에 보이는 모든 것들에 대해 관심가지는 버릇을 들이셔야 한다. 이런 버릇은 내공으로 발전된다)

DB가 만들어지면, 이제 테이블을 만들어야겠다. 이전 강의를 기억해보자.

Database(테이블의 묶음) ▶ Table(자료담는곳) ▶ Column의 모음

새 테이블 만들기를 실행하면 관리툴 중간에 입력하는 칸이 생긴다.

총 3개의 항목이다. 이것이 열(Column)을 만드는 것이다.

열 이름은 말 그대로 Column이름이라고 생각하자. 특별한 규칙은 없다. 그러나 일반적으로 C#의 변수명처럼 지정한다. 사람이 사용하고, 사람이 봐야되기 때문에 특별한 이유가 없다면 영문자로 컬럼의 의미를 알 수 있게 정하도록 한다.

그런데.. 가장 중요한 것이 빠졌다.
데이터베이스는 테이블의 묶음이라 일단 만들고 봤다 치더라도, 테이블은 실제 자료가 들어가는 곳인데 지금 우리가 새 테이블로 만들려는 목적은 무엇인가??
강의 진행은 필자가 하므로 임의로(강제로?) 정하겠다 ^_^

홈페이지에 회원가입을 받기 위한 테이블을 만들어보자.
회원가입에 필요한 것들을 수집해보자.

아이디, 비밀번호, E-Mail주소, 집주소, 우편번호, ..... 등등

우리는 회원가입을 처리하는 개발자이다. 개발자 입장에서 회원가입시에 눈에 보이는 입력 항목을 생각해야 되지만, 눈에 보이지 않는 값들도 생각하자.

회원가입날짜, 마지막로그인날짜, 마지막로그인장소(IP), 로그인횟수, ..... 등등

왜, 어떻게 이런 자료가 만들어진다고 했던가? 만들려는 목적이 있기 때문이라고 했다.
생각하자. 이 강의는 여러분들께 회원가입의 테이블을 설계해주는 로봇이 아니다. 여러분들이 만들 수 있는 회원가입을 하기 위한 DB구성 예제를 제시할 뿐이다. 여러분들이 회원가입하면서 봤던 항목도 있을 수 있다. 회원가입을 받는 웹사이트들이 서로서로 따라하는 개념이 아니다. 그들의 그들 나름대로 설계한 것들인데, 일반적으로 회원가입에 들어가는 입력에서, 공통 항목이 많이 있을 뿐이다.

테이블의 각각 열(Column)을 설계할 때는 다음처럼 나타낼 수 있을것이다.

열(Column)명	자료형식	목적
user_id	문자열(varchar), 20자	회원의 로그인 ID
user_password	문자열(varchar), 15자	로그인 비밀번호
user_name	문자열(varchar), 10자	회원 이름
user_regdate	날짜(datetime), 기본	회원 가입일자

간단하게 4개의 열로 이루어진 회원가입 테이블을 구상해보았다.
기본적으로 회원이 로그인이 필요하기 때문에, 아이디, 비밀번호는 필수로 들어갈 것이다.
그리고 회원 정보, 회원 가입일자 정도를 부가적인 정보로 받기로 했다.
일단, DB설계는 이렇게 나왔다.

이제 웹프로그래밍을 수행하는 개발자 입장에서 보자.
여기서 실제 입력받는 필드는 몇 개인가? 사용자가 귀찮게 지금 회원가입하는 시점의 가입일자를 입력받게 할 것인가? ^^; 그렇지 않기 때문에 3개가 되겠다. 가입일자는 회원 가입시에 자동으로 서버의 날짜를 저장하게 해도 될 것 같다.

다시 DB설계로 돌아와보자.
열이름은 그렇다 치고, 자료형식을 보면 문자열, 날짜로 되어 있는데 테이블을 사용하기 위해서는 테이블을 구성하는 "열 이름"과 그 열 이름이 어떤 "자료형'인지를 알아야된다.

아이디, 비밀번호, 이름은 문자열이다. 왜 그럴까? 잘 생각해보자.
간단하다. 문자니까 문자열이다. ^^

가입일자는 날짜이다. 그러나 "2011-05-05" 같이 문자열 10자로 표현해도 상관없다.
그렇지만 자료형을 맞추면 좋은 이점이 있다. 예를 들어 회원가입한 사람이 회원가입한지 며칠이 지났는지 알기 위해서는 날짜를 계산하면 된다. 그런데 만약 회원 가입 일자가 "날짜"형태가 아닌 "문자열"형태면 계산이 가능할까? 문자열을 날짜형으로 변환한 후 계산이 가능하긴 하다만, 일반적으로 열의 실제 형식에 맞게 자료형을 맞추어 주는게 좋다.
또한, 날짜형은 최대 8byte로 밀리초까지 계산가능한 반면에, 문자열은 "년-월-일" 형식만 10byte를 차지한다. 내부적으로 값을 처리하는 크기는 해당 자료형이 더 작게 잡아먹는다고 생각하자.
회원의 나이 역시, 사람이 최대 150살까지 살 수 있다고 해도 문자열 3자리가 아닌, 정수형으로 선택하는 것이 좋다. 1byte만으로 0~255까지 int를 표현하는 자료형이 있기 때문이다. 

우리들은 DB가 처음이니 일단 자료형이라는 것은 최대한 필요한 것만 사용하도록 한다.
그러나, 개념은 꼭 알고 있길 바란다. DB에서 테이블의 열의 자료형은 여러가지가 존재하며, 최대한 이를 잘 활용하면 속도가 빠르고, 용량을 효율적으로 사용할 수 있다는 것. 

SQL Server 에서 자료형이라는게 꽤 많이 존재하지만 우리는 처음 시작하는 초보기 때문에 비슷한건 그냥 묶어서 혼동되지 않도록 한다. 본 강의에서는 다음처럼 해당 자료형을 사용할 것이다.

1. 정수형은 무조건 int 를 사용한다.
2. 문자열은 무조건 varchar 를 사용한다.
3. 날짜형은 무조건 datetime 을 사용한다.
4. 참/거짓 형은 bit 를 사용한다.

참고하길 바란다. ^^

SQL Server에 관련된 자료도 모~두 MSDN에 나와있다. 다음 링크에서 자료형이 어떤것들이 있는지 읽어보기만 하자.

▶  http://msdn.microsoft.com/ko-kr/library/ms187752.aspx

이제 필자가 설계해본 회원가입 테이블을 DB관리툴에서 만들어보자.

위 그림처럼 입력을 해준다.
자료형에서 괄호()의 내용은 예상하겠지만, 해당 자료형의 크기이다. 문자열은 크기를 DB설계자가 지정해준다. 그러나 정수형, 날짜, true/false인 bit 형 등은 해당 자료형의 크기가 고정이다. 지정할 수 없고, 지정할 필요가 없다는 말이다.

또한 "Null 허용"항목의 의미는 [이 컬럼의 자료가 입력되지 않음을 허용]이라는 말과 같다.
테이블에 자료를 넣을 때, 이 컬럼은 비울 수 있냐는 말이다. 체크하면 비울 수 있고, 체크하지 않으면 비울 수 없다. 자료를 넣을 때, 체크가 안 된(Null을 허용하지 않는) 컬럼에 값을 주지 않으면 DB에서 오류가 발생한다. 일단 이렇게 알고 넘어가자.

#참고
이 강의 전반에서 나오는 "일단 넘어가자"라는 말은, 다르게 말하면 언젠가 다시 여러분들이 스스로 알기 위해 자료를 찾게 될 것이라는 말과 같다.

입력이 다 되었으면 위쪽 디스켓 모양의 툴바 아이콘을 누르거나, 단축키 Ctrl + S 로 테이블을 저장한다.

필자는 이 테이블을 member_table 이라는 이름으로 저장하였다.

그리고 다음 화면처럼, 좌측 데이터베이스 트리에 "aspnet" DB 하위 테이블로 저장이 된 것을 볼 수 있다.

이렇게 만든 테이블은 오른쪽 마우스 버튼을 눌러 "디자인"에서 수정가능하다.

지금처럼 테이블을 만들면서 우리가 마우스로 선택하고, 키보드로 입력하여 만든 과정은 모두 내부적으로 DB명령어로 수행된 것이다. 앞에서 말했던 SQL(이하 쿼리)문이다. DB관리자 편의를 위해 DBMS는 대부분 GUI(그래픽 사용자 환경)로 DB관리 화면이 제공된다.

그리고 자료를 넣거나 보는 것도 모두 쿼리문으로 명령을 한다.
즉, asp.net 에서 DB에 자료를 넣거나 빼기위해서 쿼리문을 이용하기 때문에 SQL문을 알아야 되는 것이다. 웹 프로그래밍을 배우기 위해서 <tag>를 알아야 하고, 인터넷을 하기 위해서는 마우스 사용법을 먼저 알아야 하는 것 처럼..

우리가 알아야 할 중요한 것은 쿼리문은 DB단에서 이루어진다는 것이다. 테이블 단위가 아니라는 것을 알아놓자. DB에서 1개 이상의 테이블에게 명령을 내리게 된다.

DB관리툴에서 상단에 "새 쿼리(N)"를 눌러보자.

그럼 중간에 빈 화면이 뜬다. 여기에 명령을 입력할 수 있다.

1. 테이블에 자료 넣기 - INSERT 
   ▶ 게시판 글 쓰기(리플 쓰기)
2. 테이블의 자료 가져오기 - SELECT
   ▶ 게시판(리플) 목록
3. 테이블 자료 수정 - UPDATE
   ▶ 게시물(리플) 수정
4. 테이블 자료 삭제 - DELETE
   ▶ 게시물(리플) 삭제

일단 우리가 꼭 알아야 되는 것이 위의 4가지라고 할 수 있다. 뒤에 구문이 해당 쿼리문이다.
쉽게 생각하면, 이 4개의 쿼리문 형식만 안다면 게시판(또는 기타 프로그램)에서 DB를 연동하는 점은 문제가 없다는 것이다.

현재 우리 상황을 보면, DB는 aspnet 이고, member_table 이름의 테이블이 1개 들어있다. (앞에 dbo.은 무시한다) 이 테이블에는 당연히 자료가 없을 것이다.

C#에서 문자열 상수는 큰따옴표로 묶었다. ("상수값")
DB에서 문자열 상수는 작은따옴표로 묶인다. ('상수값')
DB에서 정수형 상수는 C#처럼 그냥 넣으면 된다.
DB에서 변수는 컬럼명이라고 생각하면 쉽다.

member_table 에는 총 4개의 열이 있다. 이 4개의 열은 각각 자료가 모두 들어가야지만 하나의 자료가 만들어진다. 이 하나의 자료를 레코드(Record)라고 부른다.
즉, 회원 1명의 자료는 1개의 레코드라고 보면 된다.
다음에 만들어볼 게시판에서, 게시물 하나는 1개의 레코드라고 보면 되겠다.
리플도 하나의 레코드이다.
일반적으로 우리가 보는 한 개의 자료 라고 하면, 한 개의 레코드라고 보면 된다.

DB구조를 거꾸로 뒤집어 올라가보자.

Column -> Record -> Table -> Database

이렇게 된다고 할 수 있다.

우리가 쿼리문으로 자료를 넣는 다는 것은, 상수값으로 넣는다는 것과 같다. 별별 프로그램과 DB를 연동하는 이유는, 테이블에 들어가는 자료는 "사용자 또는 어떤 외부의 변하는 값"이기 때문이다. 가장 큰 목적이다. 우리는 웹과 연동하기 때문에 회원가입/게시판/쇼핑몰 등을 생각할 수 있을 것이다.

member_table 에 자료를 넣어보자.  "INSERT" 구문을 이용한다. 이용하는 방법은 다음과 같다.

INSERT                               - 자료입력
INTO                                   - 어디에?
테이블명                              - 이 테이블에.
( 컬럼1, 컬럼2,  .. )               - 이 컬럼 순서대로..
VALUES                              - 값은
( 컬럼1값, 컬럼2값,  .. )         - ( '홍길동', 35, ... )

보기 쉽게 엔터로 구문했다. 우측은 설명이다.
단어의 구분은 태그문처럼, 1칸 이상 띄우거나 개행하면 된다.
첫번째, 두번째, 세번째 줄은 필수이다. 무조건 써준다.
네번재줄은 "테이블명"에 들어있는 컬럼이 나열된다. 어떤 컬럼값을 넣는지 지정하는 것이다. 또한 생략할 수도 있다. 단, 생략할 수 있는 조건은 모든 컬럼의 값을 다 넣어주는 조건이다. 때때로 테이블 레코드의 값이 특정 컬럼 값을 비울 경우가 있을 것이다. 그러나 생략하지 않고 다 써주기를 추천한다.
다섯번째줄인 VALUES 역시 그대로 써줘야 한다.
여섯번째줄에서 실제 들어갈 값을 나열한다. 문자열 상수라면 '하하', '홍길동' 처럼 작은 따옴표를 붙여준다.

이 형식만 생각하면 된다. (손에 익히기 위해 열심히 연습하셔야 겠지만 말이다)


실전으로 가보자.
우리가 만든 member_table 은 다음의 구조이기 때문에,

자료를 넣는 쿼리문은,

INSERT
INTO
member_table
( user_id, user_password, user_name, user_regdate )
VALUES
( 'test_id', 'testpwd', '홍길동', getdate() )

이렇게 사용가능하다.
진하게 처리된 문자는, 말 그대로 INSERT 구문의 기본이다. 빨간색은 넣을 테이블이름, 컬럼, 값 등 테이블 구조대로 넣어주면 된다.

결국 해당 컬럼명에 맞는 자료를 넣으면 된다.

백문이 불여일견이다. 이대로 일단 DB관리툴에 백지에 입력하고(붙여넣기 금지!) 실행해보자. 실행하는 방법은 "F5" 단축키를 누르면 쿼리문이 전체적으로 실행되고,  Shift 로 지정하여 F5를 누르면 해당 지정된 부분만 쿼리문이 실행된다.

빨간색 밑줄 쳐지는 것은 무시하자.
(또는 없애는 방법은 메뉴에서 "쿼리", "IntelliSense사용"을 없애면 된다)

실행 후 하단 메시지에 (1개 행이 영향을 받음)이라고 메시지가 뜬다. 입력이 잘 실행되었다.
컬럼 값을 이리저리 바꾼 후 한번 더 INSERT문을 실행해보자.

#참고
getdate() 항목은 SQL Server 에 내장된 함수이다. 현재의 날짜, 시간을 출력한다. C#과 비교해본다면 DateTime.Now 라고 할 수 있겠다.

이제 자료를 조회하는 SELECT 문을 알아보자.

SELECT
컬럼1, 컬럼2, ... (혹은) *
FROM
테이블명
WHERE
조건문

자료 조회는 위와 같이 사용가능하다. 
간단하게, member_table 의 모든 자료를 조회하는 구문은,

SELECT * FROM member_table

이렇게 입력하면 된다. * 은 모든 컬럼을 뜻한다. 

SELECT user_id, user_password, user_name, user_regdate FROM member_table

와 동일한 구문이다. 

자료가 나오는 것을 확인할 수 있다.

SELECT user_id, user_name, FROM member_table

이렇게 실행도 해보자. 해당 컬럼만 결과를 얻을 수 있다.

조건문을 넣어보자. 조건문은 테이블명 뒤이 WHERE 을 넣어주고, 그 뒤에 항목을 넣어준다.
다시 말했지만, 닷넷(C#)은 생각하지말자. 아~무 상관없다.
항목은 "컬럼명 [연산자] 값" 이다.

조건문을 예제 몇개로 알아보자.

(..) WHERE A = 'abc'
  ▶ A컬럼 값이 abc인 자료 (A컬럼은 문자형 컬럼일 것이다)
(..) WHERE A <> 'gg'
  ▶ A컬럼 값이 gg가 아닌 자료 (닷넷의 != 와 동일하다.)
(..) WHERE age=30 AND name='이수근'
  ▶ age컬럼값이 30 이고, name이 '이수근'인 자료.(age는 정수형이다. '...'가 없다)
(..) WHERE age=30 OR name LIKE '김%'
  ▶ age컬럼값이 30 이거나, name이 김으로 시작하는 자료
(..) WHERE (id=null OR age=20) AND name LIKE '%김%'
  ▶ id가 빈 값이거나 age가 20인 자료인 조건에, name 에는 무조건 '김'이 포함된 자료
(..) WHERE A IN (5,2,3)
▶ A컬럼 값이 5 또는 2또는 3인 값 (정수형)
(..) WHERE B IN ('김','정','박')
▶ A컬럼 값이 '김'또는 '정' 또는 '박'인 값. (문자열)

[컬럼명] LIKE '%문자열%' 
이런 형식은 문자열에서 밖에 사용하지 못한다. %는 어느 위치든 들어있을 수 있다. 이런 것은 주로 게시물 검색에 사용할 수 있겠다.

[컬럼명] IN (자료1,자료2,...)
자료형에 상관없다. 자료를 여러개 중에 하나라도 있다면 매치되는 조건이다.

좀 복잡해 보일 수 있으나, 형식만 봐도 알 수 있을 것이다.
AND, OR, <>
이렇게 3개는 꼭 알아두자.

우리가 만든 member_table 테이블에서 이런 각종 WHERE ........ 에 들어갈 수 있는 조건문을 연습해보자.

SELECT user_id, user_name FROM member_table
WHERE user_name='홍길동'
SELECT * FROM member_table
WHERE user_id='test_id' AND user_name='홍길동'

이런 등등의 SELECT 문의 문법을 볼 수 있을 것이다.

DB의 쿼리문 역시, 프로그래밍 언어처럼 "문법이 맞으면 잘 실행되고, 그렇지 않으면 오류를 발생시킬 뿐"이다. 내가 원하는 자료가 나오는지 나오지 않는지는 다음 문제이다. 오류가 나지 않고, 내가 원하는 자료가 나오지 않는다면 문법은 알고 있는데, 조건문의 사용이 틀렸을 것이다.
백문이 불여일행이다. 문법을 알았으니, 직접 여러가지 방식으로 자료를 입력, 조회해보자.

다음은 자료를 수정하는 UPDATE 문이다.
자료의 수정, 삭제 역시 조건문인 WHERE 문이 들어간다. 문법은 동일하다.

UPDATE
테이블명
SET
컬럼명1='컬럼값', 컬럼명2=컬럼값,  ..
WHERE
조건문

WHERE 조건문이 없다면 테이블 전체가 수정된다. 일반적으로, UPDATE문은 WHERE 절이 들어가게 된다. 게시물 수정시, 5번글을 수정해라~ 처럼 될 것이니까 말이다.

UPDATE member_table SET user_name='김철수'
WHERE user_id='test_id'
UPDATE member_table SET user_password='1234', user_name='손님'
WHERE user_id='test_id'

별로 어렵지 않다.


마지막으로, 자료를 삭제하는 DELETE 문이다.
앞의 쿼리문 형태와 거의 동일하다. WHERE 문이 존재할 수 있다.

DELETE
FROM
테이블명
WHERE
조건문

UPDATE 수정 쿼리문처럼, DELETE 도 WHERE 조건문이 없다면 테이블 전체가 삭제된다. 꽤 무서운 녀석이기도 하다. ^^;

DELETE FROM member_table WHERE user_id='test_id'
DELETE FROM member_table WHERE user_name='홍길동' OR user_name LIKE '김%'

역시 뭐, 비슷비슷하다


이렇게, 쿼리문의 기본인 4개의 형식을 알아보았다.
이제 여러분들이 자유롭게 가지고 놀아볼 시간이다.
테이블을 또 만들어보고, 자료를 입력해보고, 수정해보고, 삭제해보자. 가지고 놀아보자. 이 문법을 외울 필요없다. 형식만 옆에 두고 쿼리문 만들 때에만 참고하자. 계속 해보면 자동으로 외워진다. 외우지말자.


웬지 이제 시작일 것 같지만, SQL Server 를 다루는 내용은 오늘로 끝이다.
DB에서는 이 정도만 잘 숙지하시면 된다. 

쿼리문은 우리가 앞으로 DB와 연동할 때마다 사용하는 DB명령문이다.
그러므로, 익숙해져야 한다.

또한 4장의 핵심을 꼭 잊지마시길.. "asp.net 과 DB는 아~무 관련없다"


다음시간에..