SQL Server 2005

• SQL Server 설치 및 전체과정 미리 실습

SQL Server 는 마이크로소프트에서 제작한 DBMS 소프트웨어다. DBMS 소프트웨어는 한마디로 대량의 데이터를 관리해주는 소프트웨어라고 생각하면 쉽다.

 SQL Server 2005 에디션별 기능 차이

 SQL Server 2005 설치

 하드웨어 요구사항

CPU가 Pentium3  미만이면  설치가 되지 않지만, CPU 속도가 최소 600MHz 이하더라도 경고가 나올 뿐 설치는 가능하다. 또한 메모리 크기는 최소 512MB를 권장하고 있지만, 256MB에서도 설치는 가능하다. 다만, 이런 경우에는 가상메모리를 1GB 정도로 설정해주는 것이 좋다.

 소프트웨어 요구사항

공통 - IE 6.0 SP1 이상 설치, MDAC 2.8 SP1 이상 설치, IIS 5.0 및 ASP.NET 2.0 설치

Windows 2003 Server - SP1 이상

Windows XP - SP2 이상

Windows 2000 - SP4 이상

 에디션별 설치 가능한 운영체제

 인증모드

인증 모드는 두 가지가 존재한다. Windows 인증 모드는 Windows의 사용자만이 SQL Server에 접속할 수 있다는 의미이다.(물론, 모든 Windows 사용자가 SQL Server에 접속된다는 의미는 아니며, Windows 사용자 중에 SQL Server에 접속할 수 있는 사용자를 별도로 지정해줘야 한다.). 또, 혼합 모드는 Windows 사용자가 아니더라도 SQL Server를 사용할 수 있는 별도의 사용자를 만들 수 있는 모드이다. 보안에 더 강력한 것은 Windows 인증 모드이며, 마이크로소프트도 이 모드를 권장한다. 하지만, 보안에는 좀 바람직하지 않을 수도 있지만 아직까지 실무에서는 혼합 모드를 많이 사용하고 있다.

만약, 혼합 모드를 선택한다면 SQL Server의 관리자인 'sa'(System Administrator)의 암호를 지정해야 한다.

 설치 후에 확인할 사항

C:\Program Files\Microsoft SQL Server\폴더

80 폴더는 이전 버전인 SQL Server 2000과의 호환을 위해 존재하는 파일이며, SQL Server2005가 공통적으로 사용될 폴더는 90 폴더 아래에 있다. 폴더의 역할은 다음과 같다.

       그리고 MSSQL.1 폴더는 인스턴스 폴더이다.

 데이터베이스 구축의 전체과정 미리 실습하기

  테이블의 구조와 필수 용어

데이터 : 당탕이, 냉장고, 2006.7과 같이 하나의 단편적인 정보를 뜻한다. 즉, 정보는 있으나 아직 체계화되지 못한 상태를 말한다.

테이블 : 회원(사람)이나 제품의 데이터를 입력하기 위해 표 형태로 표현한 것을 말한다. 위의 그림에서는 인터넷 쇼핑몰을 구현하기 위해서 회원에 대한 정보를 보관할 회원 테이블과 제품 정보를 보관할 제품 테이블을 만들었다.

데이터베이스(DB) : 테이블이 저장되는 저장소를 말한다. 그림과 같이 원통모양으로 주로 표현한다. 각 데이터베이스는 서로 다른 고유한 이름을 가지고 있어야 한다. 우리가 사용할 데이터베이스는 쇼핑몰 데이터베이스다.

DBMS : DataBase Management System의 약자로 데이터베이스를 관리하는 시스템 또는 소프트웨어를 말한다. SQL Server 2005가 바로 DBMS이다.

열(Column) : 각 테이블은 열로 구성된다. 회원 테이블의 경우에는 회원 이름, 주민번호, 주소 등 3개의 열로 구성되어 있다. 각각의 열을 구분하기 위한 이름을 열 이름이라고 하며, 열 이름은 각 테이블 내에서는 중복되지 않고 고유해야 한다.

데이터 형식 : 열의 데이터 형식을 말한다. 회원 테이블의 회원 이름 열은 당연히 문자 형식이어야 한다. 반면, 제품 테이블의 가격 열은 숫자 형식이어야 할 것이다. 데이터 형식은 테이블을 생성할 때 열이름과 함께 지정해줘야 한다.

행(Row) : 실질적인 데이터를 말한다. '지운이/030101-1111111/서울 서대문구'이 하나의 행 데이터다.

기본 키(Primary Key) 열 : 기본 키(또는 주키) 열은 각 행을 구분하는 유일한 열을 말한다. 기본 키 열은 중복되어서는 안 되며, 비어 있어서도 안 된다. 또, 각 테이블에는 기본 키가 하나만 지정되어 있어야 한다. 위 그림에서 회원 테이블의 기본 키가 주민번호 열로 지정되어 있다. 만약, 기본키를 회원 이름 열로 지정하면 어떻게 될까? 기본 키는 각 행을 구분하는 유일한 열이라고 했는데 '지운이'라는 이름만으로 그 사람이 서울 서대문구에 산다는 것을 확신할 수 있는가? 만약, '지운이'라는 이름이 또 있다면? 현실적으로 이름이 같은 사람은 얼마든지 있을 수 있기 때문에 회원 이름 열을 기본 키로 지정하기는 부적합하다.

SQL(Structured Query Language: 구조화된 질의 언어) : DBMS에서 무슨 작업을 하고 싶다면 어떻게 해야 할까? "SQL Server 2005야, 테이블 하나 만들어 볼래?"라고 말할 수는 없을 것이다. DBMS에 무슨 작업을 하고 싶다면 DBMS가 알아듣는 말로 해야 할 것이다. DBMS가 알아듣는 말, 그것이 SQL이다.

  SQL Server 2005를 이용한 데이터베이스 구축 절차

데이터베이스 생성 -> 테이블 생성 -> 데이터 입력 -> 데이터 활용

데이터베이스 생성

Windows의 [시작] 메뉴에서 [Microsoft SQL Server 2005] -> [SQL Server Management Studio]를 클릭해서 SSMS를 실행한다.

[서버에 연결]창이 나온다. 아래와 동일하게 된 것을 확인하자. 서버 이름에는 컴퓨터 이름이 들어 있다. 설정이 확인되었으면 <연결> 버튼을 클릭한다.

아래와 같이 SSMS의 초기 창이 나타날 것이다. SSMS의 역할은 SQL Server 2005 서버를 사용할 수 있도록 해주는 클라이언트 프로그램이다.

데이터 베이스를 생성하자. 쇼핑몰 데이터베이스(ShoppingDB)를 생성하자.

[새 데이터베이스] 창에서 데이터베이스 이름만 "ShoppingDB"로 입력한 후에 <확인> 버튼을 클릭한다.

개체 탐색기에서 쇼핑몰 데이터베이스(ShoppingDB)가 생성된 것을 확인할 수 있다.

테이블 생성

쇼핑몰 데이터베이스 안에 회원 테이블과 제품 테이블을 생성하도록 하자.

 회원 테이블의 데이터 형식 : 문자를 입력하기 위한 데이터 형식에는 CHAR, VARCHAR가 있고, 한글을 입력하기 위해 앞에 'N'을 붙인 NCHAR, NVARCHAR가 있다.

SSMS의 개체 탐색기에서 [데이터베이스]->[ShoppingDB]->[테이블]을 선택한 후 마우스 오른쪽 버튼을 클릭하고 [새 테이블]을 클릭한다.

회원 테이블의 내용을 우선 입력한다. 데이터 형식은 직접 써주거나, 드롭다운 버튼을 통해 선택할 수 있다. 또, 회원 이름과 주민번호는 'null 허용' 체크를 OFF 시키도록 한다.

주민번호 열을 선택한 후에 마우스 오른쪽 버튼을 클릭하고 [기본 키 설정]을 클릭해서 기본 키로 지정한다.

기본 키로 지정하고 나면 juminNo 앞에는 열쇠 모양의 아이콘이 표시된다.

<저장> 버튼을 클릭하거나, SSMS 메뉴의 [파일]->[Table_1 저장]을 클릭한 후에 'memberTbl'로 테이블 이름을 지정하자.

 테이블 이름 앞에 'dbo'라는 문자가 자동으로 붙는데, 이것을 '스키마'라고 부른다.

같은 방식으로 제품 테이블(productTbl)을 만든다.

 지금 SSMS에서 생성한 테이블은 SQL 문인 "CREATE TABLE ..." 구문을 사용해서도 동일하게 생성할 수 있다.

데이터 입력

개체탐색기의 [데이터베이스]->[ShoppingDB]->[테이블]->[dbo.memberTbl]을 선택한 후 마우스 오른쪽 버튼을 클릭하고 [테이블 열기]를 클릭한다.

데이터를 입력한다.

입력한 행 데이터는 입력하는 즉시 저장이 되므로 따로 저장할 필요가 없다. 또, NULL이라고 써진 것은 아무 것도 없다는 의미이므로 그냥 무시하면 된다.

 데이터를 입력하는 SQL은 "INSERT INTO ..."을 사용하면 된다.

동일한 방식으로 제품 테이블(productTbl)에 데이터를 입력한다.

데이터 활용

SQL 문을 직접 입력할 수 있는 쿼리창을 하나 열도록 하자.

SSMS의 왼쪽 상단의 새 쿼리 아이콘을 클릭하거나, 개체 탐색기에서 기본 인스턴스가 생성된 상태에서 마우스 오른쪽 버튼을 클릭하고 [새 쿼리]를 클릭한다.

먼저, 꼭 처리할 것은 왼쪽 상단의 'master'로 선택되어 있는 데이터베이스를 지금 생성한 'ShoppingDB'로 선택해 주는 것이다. 이것은 앞으로 쿼리창에 입력할 SQL 문이 선택된 ShoppingDB에 적용된다는 의미이다.

 입력한 SQL이 정확한데도, "개체 이름 'XXXX'이(가) 잘못되었습니다."라는 오류 메시지가 나온다면 위 그림과 같이 데이터베이스를 선택하지 않았기 때문인 경우가 많다.

SQL의 실행 방법은 쿼리창에 문법에 맞는 SQL을 입력한 후에, 실행 아이콘을 클릭하거나 F5 키를 누르면 된다. 또는, SSMS 메뉴의 [쿼리]->[실행]을 선택해도 된다.

SELECT문

회원 테이블의 모든 데이터를 조회해 보자. 아래 구문을 입력하고, F5 키를 눌러 실행한다.

1. SELECT * FROM memberTbl;

SQL은 대소문자를 구분하지 않는다. SELECT의 기본 형식은 "SELECT 열이름 FROM 테이블이름 WHERE 조건"이다. '*'는 모든 열을 의미한다. 그러므로, "SELECT * FROM memberTbl"은 회원 테이블의 모든 열을 보여주라는 의미이다. 또, 마지막에 세미콜론(;)은 입력해도 되고, 입력하지 않아도 관계없다.(예외적으로 꼭 있어야 하는 경우도 있다.)

 테이블 외의 데이터베이스 개체

인덱스

인덱스란 책의 제일 뒤에 붙어 있는 색인과 같은 개념이다. 즉, 책의 내용 중에서 특정 단어를 찾고자 할 때 책의 첫 페이지부터 마지막까지 한 페이지씩 찾아보는 것보다는, 책 뒤의 색인을 찾아보고 색인에 나와 있는 페이지로 바로 찾아가는 것이 훨씬 빠를 것이다.

인덱스는 테이블의 열 단위에 생성이 된다.(물론, 복합 인덱스도 있지만 지금은 그냥 하나의 열에 하나의 인덱스를 생성할 수 있다고 생각하자). 열을 기본 키로 설정하면 자동으로 인덱스가 생성된다.

 데이터베이스 튜닝에서 가장 집중적으로 보는 부분 중 하나가 바로 인덱스이다. 즉, 인덱스를 적절히 활용하고 있느냐에 따라서 시스템의 성능이 몇 배, 심하게는 몇 십 배 차이가 난다.

회원 테이블의 회원 이름(memberName)열에 인덱스를 생성해 보자.

1.   CREATE INDEX idx_memberTbl_memberName ON memberTbl (memberName);

이제 인덱스 이름 'idx_memberTbl_memberName'은 회원 테이블의 회원 이름 열로 생성된 색인이 된다. 사실 이름은 별로 중요하지 않지만, 지금처럼 이름만으로 어느 테이블의 어느 열에 설정된 인덱스인지를 알 수 있도록 지정해주는 것이 좋다.

이제 회원 이름을 조건으로 조회를 수행해 보자.

1.   SELECT * FROM memberTbl WHERE memberName = '한주연';

인덱스를 생성하기 전과 동일하게 출력되겠지만 그 내부적인 작동은 인덱스를 만들기 전과 지금 인덱스를 만든 후에는 큰 차이가 있다. 결론적으로 인덱스를 생성한 후 조회한 것이 데이터의 양에 따라서 심하게는 몇 십 배 이상 빠를 수 있다.

실무에서는 지금의 실습과 같이 필요한 열에 꼭 인덱스를 생성해줘야 한다. 하지만, 인덱스는 잘 사용하면 좋은 약이 되지만, 잘못 사용하거나 함부로 남용한다면 독이 될 수 있으므로 세심한 주의가 필요하다.

뷰

뷰(View)란 가상의 테이블이라고 생각하면 된다. 즉, 사용자의 입장에서는 테이블과 동일하게 보이지만, 뷰는 실제 행 데이터를 가지고 있지 않다. 그 실체는 없는 것이며, 진짜 테이블에 링크된 개념이라고 생각하면 된다. 그래서 뷰를 SELECT 하면 진짜 테이블의 데이터를 조회하는 것과 동일한 결과를 얻는다.

CREATE VIEW uv_memberTbl AS SELECT memberName, addr FROM memberTbl;

뷰의 실체는 사실 SELECT 문이다. 우리가 uv_memberTbl에 접근하게 되면 뷰 생성 시에 입력한 SELECT 문이 작동하는 것이다.

뷰를 조회해 보자. 다른 테이블과 동일하게 사용하면 된다.

1.   SELECT * FROM uv_memberTbl;

저장 프로시저

저장 프로시저(Stored Procedure)란 SQL Server에서 제공해주는 프로그래밍 기능을 말한다. 즉, SQL 문을 하나로 묶어서 편리하게 사용하는 기능이다. 물론, SQL을 묶는 것 외에도 다른 프로그래밍 언어와 같은 역할을 담당할 수 있다.

'myProc'라는 이름의 저장프로시저를 만들자.

1.   CREATE PROCEDURE myProc
2.   AS
3.      SELECT * FROM memberTbl WHERE memberName = '당탕이';
4.  SELECT * FROM productTbl WHERE prodName = '냉장고';
5.   GO

앞으로는 저장프로시저를 실행하기만 하면 된다. 저장프로시저는 "EXEC 저장프로시저이름"으로 실행하면 된다.

1.   EXEC myProc;

GO를 써주게 되면 그 이전의 문장을 실행시켜서 아래의 문장과 분리를 하게 된다. 대부분 하나의 문장만 수행하는 경우에는 GO를 사용하지 않아도 된다. 문장이 종료되는 부분에서 자동으로 GO가 붙여지는 효과가 일어나기 때문이다. 그러나, 여러 문장을 한꺼번에 수행할 때는 GO를 꼭 써줘야 하는 경우도 있다.

트리거

트리거란 테이블에 부착되어서 테이블에 INSERT나 UPDATE 또는 DELETE 작업이 발생되면 실행되는 코드를 의미한다.

예로 회원 테이블에서 삭제 작업이 발생하면 자동으로 삭제될 데이터를 다른 곳에 저장하는 트리거를 부착해 놓도록 해보자.

먼저, 지워진 데이터를 보관할 테이블(deletedMemberTbl)을 만들도록 하자.

1.   CREATE TABLE deletedMemberTbl
2. ( memberName nchar(5),
3.   juminNo char(14),
4.   addr nvarchar(30),
5.   modifyDate datetime  -- 삭제한 날짜/시간
6. );

회원 테이블에 DELETE 작업이 일어나면 백업 테이블에 지워진 데이터가 기록되는 트리거를 생성해 보자.

1.   CREATE TRIGGER trg_deletedMemberTbl -- 트리거 이름
2.   ON memberTbl -- 트리거를 부착할 테이블
3.   AFTER DELETE -- 삭제 후에 작동하도록 설정
4.   AS
5.     -- deleted 테이블의 내용을 백업 테이블에 삽입
6.     INSERT INTO deletedMemberTbl
7.         SELECT memberName, juminNo, addr, GETDATE() FROM deleted;

지금부터는 memberTbl 에 삭제 작업이 일어나면 삭제된 행이 deletedMemberTbl 에 저장된다.

 데이터베이스 스키마

스키마는 데이터 구조의 명세에 대한 제약 조건을 기술한 것을 말하며 데이터베이스에 접근하기 위한 기본 구조로 삼고 있다.