Transcript 개발 방법론과 Data Base Modeling

SQL Server 2000
기본 및 활용
이배현( [email protected] )
 수강 대상
1. 선수 과목
1) 운영체제와 네트워킹에 관한 기본적인 내용
2) 운영체제에서 계정 관리
3) 프로그래밍 기초 ( VB, Java, Asp, Jsp…. )
2. 수강 대상
1) SQL Server에 관한 체계적인 개념 정립을 원하는 학생,
학원 수강생 ( 초보자 )
2) 현업 프로그래머로 데이터베이스와 관련한 프로그램 개발
을 하는 개발자 ( 중급자 )
3) 현업 서버 관리자로 단순 데이터베이스 서버 관리를 넘어
효과적인 데이터베이스 관리를 원하는 관리자 ( 중급자 )
3. 강의 환경
1) 운영체제 : Windows XP
2) SQL Server : SQL Server 2000 Enterprise Edition
주제 : 관계형 데이터베이스 및
데이터베이스 기본
이배현(
[email protected] )
1. 관계형 데이터베이스의 기본 이해
1) 관계형 데이터베이스란?
: 1969년 IBM의 연구원으로 있던 .F.Codd가 수학적 기초에 근거를 두
고 고안한 것이 관계형 데이터베이스 (Relational Database) 이다.
기본 개념: 데이터베이스는 최소한의 의미를 가지는 테이블들로 구성되
며 그 테이블들에 있는 필드들로 연결한 것이다.
필드 또한 가장 작은 논리적인 단위로 구분하는 것이 좋다.
2) 관계와 참조 무결성
1) 관계란?
: 두개의 실체간에 명명되어진 의미 있는 연결이며 두 실체간에 업무
적인 연관성을 나타낸다.
2) 참조 무결성
: 관계 형성을 통해 부모 테이블과 자식테이블 간의 정의되는 데이터
무결성 구현 방법.
 관계형성을 통한 참조 무결성
제약 조건
부모 테이블
자식 테이블
입 력
제약 없음
부모 테이블에 데이터
가 존재하는지 검증
수 정
수정 하려는 데이터를
자식 테이블에서 참조
하고 있는지를 검증
부모 테이블에 존재하
는 다른 데이터로 변경
가능
삭 제
삭제 하려는 데이터를
자식 테이블에서 참조
하고 있는지를 검증
제약 없음
 CASCADE 옵션
UPDATE CASCADE : 부모 테이블의 데이터를 수정할 때 이를 참조하고
있는 자식 테이블의 데이터도 함께 수정한다.
DELETE CASCADE : 부모 테이블의 데이터를 삭제할 때 이를 참조하고
있는 자식 테이블의 데이터도 함께 삭제한다.
2. 시스템 데이터베이스와 사용자 정의 데이터베이스
1) 시스템 데이터베이스란?
: 시스템 데이터베이스란 SQL Server가 동작하는데 있어서 필요한 관련 옵
션과 정보 그리고 데이터베이스 로그인과 잡 등을 저장하고 관리하는 데
이터베이스로 SQL Server를 설치할 때 자동으로 만들어 지고 역할에 따라
자동으로 관리 된다. 이러한 시스템 데이터베이스는 다음과 같이 총 5가
지가 있다. ( Master, Msdb, Model, Tempdb, Distributed )
2) 시스템 데이터베이스의 역할
2.1) Master 데이터베이스
: SQL Server의 운영에 필요한 내용을 전반적으로 관리하는 데이터베이
스로 로그인과 서버Role, 시스템 에러 메시지 그리고 데이터베이스 정
보 등을 관리 한다.
가장 중요한 시스템 데이터베이스로 운영체제 입장에서 보면 레지스
트리와 같은 역할을 하는 데이터베이스 이다.
2.2) Msdb 데이터베이스
: 자동화와 관련한 잡이나 경고 등이 저장되고 관리하는 데이터베이스로
SQL Agent Service가 주로 이용하는 데이터베이스 이다.
2.3) Model 데이터베이스
: 관리자가 만드는 모든 데이터베이스의 원형으로 존재하는 데이터베이
스 이다.
Model 데이터베이스를 바꾸면 앞으로 만들어 지는 모든 데이터베이스
에 영향을 미친다.
2.4) Tempdb 데이터베이스
: 임시 테이블이나 정렬 작업등을 일시 저장하기위한 테이블이다.
Tempdb는 SQL Server Service가 시작될 때 항상 초기화 되어진다.
2.5) Distribution 데이터베이스
: 복제 설정을 했을 때만 생성되는 데이터베이스로 복제에 사용되는 정보
나 트렌젝션 데이터를 저장한다.
3) 사용자 정의 데이터베이스란?
: 관리자나 개발자들이 필요에 의해서 생성한 데이터베이스를 말하며 일반
적으로 업무에서 사용하는 데이터베이스를 말한다.
4) 데이터베이스 만들기
: 데이터베이스는 최소한 하나 이상의 데이터파일과 하나 이상의 로그 파일
로 구성된다.
30 Mb
10Mb 10Mb 10Mb
Data File
10Mb
Log File
3.1) 엔터프라이즈 관리자를 이용하여 데이터베이스 만들기(SAMPLEDB)
3.2) Query 분석기를 이용하여 데이터베이스 만들기
3.3) 데이터 파일 확인
3.4) 데이터베이스 삭제
3. 데이터베이스 개체 ( Object )
1) 테이블 ( Table )
: 2차원 배열 구조로 실제 데이터를 저장하는 핵심 오브젝트 이다.
2) 뷰 ( View )
: 가상의 논리적인 테이블로 View를 사용하는 이유는 편의성과 보안성이
다. View를 통해 데이터의 입력, 수정, 삭제가 가능하며 View와 테이블
간의 조인도 가능하다.
3) 저장 프로시저 ( Stored Procedure )
: View와 비슷한 모습을 갖지만 파라미터를 활용할 수 있어서 융통성이 뛰
어나고 절차적인 프로그래밍이 가능하며 속도가 빠르다.
4) 트리거 ( Trigger )
: 테이블에 데이터가 입력, 수정, 삭제될 때 동작하는 프시저의 한 형태이
다. 업무 규칙 ( Business Rule )등을 정의할 때 주로 사용한다.
5) 사용자 정의 함수 ( Function )
: 사용자가 임의로 리턴값을 구하는 환경에서 직접 함수를 정의해서 사용
할 수 있다.
6) 사용자 정의 데이터 타입
: 동일한 데이터타입이 반복적으로 사용될 때 하나의 사용자 정의 데이터
타입을 만들어 적용할 수 있다.
7) 제약조건 ( Constraint )
: 논리적으로 잘못된 데이터가 입력되는 경우의 수를 제거하기 위해서 사용
되는 데이터 무결성 구현 방법이다.
( 컬럼에 적용되는 3가지 제약조건 : Not Null, No Duplicate, No Change )
7.1) 기본키(PK) 제약 조건 : Not Null, No Duplication
7.2) Unique 제약 조건 : No Duplicate
7.3) 외래키(FK) 제약 조건 : 관계 형성에 따른 자식 테이블의 입력, 수정 제
약 조건.
7.3) Check, Rule : 특정 컬럼에 입력이 허용 가능한 데이터나 데이터 범위를
지정한다.
7.4) 디폴트(Default) : 하나의 레코드를 입력할 때 아무런 값도 입력되지 않
은 컬럼에는 Null값이 입력되게 되는데 이때 기본적으로
입력되는 데이터를 정의한다.
8) 인덱스 ( Index )
: 데이터의 검색 속도를 향상시키기 위해 하나의 컬럼 혹은 여러 컬럼에 인
덱스를 정의한다.
9) 데이터베이스 다이어그램
: 데이터베이스 내부의 테이블과 관계를 시각적으로 보여주는 인터페
이스로 관계 설정 및 기타 테이블과 관련한 관리작업을 할 수 있다.
주제 : DML (Data Manipulation Language)
이배현(
[email protected] )
1. SQL 문장에 대한 소개
1) SQL ( Structured Query Language )문장의 특징
: SQL은 어떻게 작업을 수행할 지가 아니라 무엇을 해야 하는지에 관해서
정의하는 결과 중심적인 언어이다.
사용자가 RDBMS에 명령을 실행할 때 SQL문장을 사용하며 RDBMS는 물
리적으로 데이터베이스에서 명령을 수행하고 결과를 사용자에게 반환한
다.
사용자
사용자
DB관리도구
사용자
Application
사용자
사용자
Application
SQL
데이터
베이스
관리시스템
( DBMS)
데이터
베이스
2) SQL ( Structured Query Language )문장의 유형
: SQL 문장은 유형에 따라 다음과 같이 3가지로 구분할 수 있다.
1) DDL ( Data Definition Language )
1.1) CREATE : 데이터베이스 및 데이터베이스 오브젝트 생성
1.2) DROP : 데이터베이스 및 데이터베이스 오브젝트 삭제
1.3) ALTER : 데이터베이스 및 데이터베이스 오브젝트 수정
2) DML ( Data Manipulation Language )
2.1)
2.2)
2.3)
2.4)
SELECT
INSERT
UPDATE
DELETE
:
:
:
:
테이블의 데이터 조회
테이블에 데이터 입력
테이블에 데이터 수정
테이블에 데이터 삭제
3) DCL ( Data Control Language)
3.1) GRANT : 특정 자원 사용에 대한 권한 부여
3.2) REVOKE : 특정 자원에 부여된 권한 취소 ( 권한을 주기 전 상태 )
3.3) DENY : 특정 자원에 대한 권한 모두 취소 ( 모두 부정 )
2) SELECT 문장 기본 I
2.1) 기본 구문
: SELECT 컬럼 LIST
FROM 테이블명
WHERE 조회의 조건
예) USE PUBS
SELECT * FROM TITLES
SELECT * FROM TITLES WHERE PRICE >= 20
2.2) 임의의 컬럼 만들기
예) SELECT ‘책 제목 : ‘, TITLES, PRICE FROM TITLES
2.3) 컬럼 해더 정의하기
예) SELECT TITLE_ID AS ‘책 번호’, TITLE AS ‘책 제목’, ‘가격’ = PRICE
FROM TITLES
2.4) WHERE 절 ( 조회의 조건을 정의 한다. )
예) SELECT * FROM TITLES WHERE 1 = 1
SELECT * FROM TITLES WHERE 1 <> 1
3) 자료형 ( Data Type )
3.1) 정수형 : 소수점이 없는 숫자형 데이터 타입
=> BIGINT, INT, SMALLINT, TINYINT, BIT
3.2) 문자형 : 문자열을 정의하기 위한 데이터 타입
=> CHAR : 8000바이트 이하의 고정형 문자.
VARCHAR : 8000바이트 이하의 가변형 문자.
TEXT : 8000바이트 이상의 문자열 데이터. ( 0 ~ 2 GB )
( NCHAR, NVARCHAR, NTEXT : 유니코드 형식의 문자 데이터 )
3.3) 실수형 : 소수점을 가진 숫자형 데이터 타입
3.3.1) 고정 소수점형 : 소수점 이하 몇 자리까지 정의
( NUMERIC, DECIMAL)
3.3.2) 부동 소수점형 : 소수점이 있는 만큼 정의 ( FLOAT, REAL )
3.4) 날짜형 : 날짜와 시간을 저장하기 위한 데이터 타입
=> DateTime : 날짜와 시간을 밀리세컨드 단위까지 저장.
SmallDateTime : 날짜와 시간을 분 단위까지 저장.
3.5) 이진형 : BINARY데이터를 저장하기 위한 데이터 타입.
=> BINARY, VARBINARY, IMAGE
3.6) 화폐형 : 금액을 저장하기 위한 데이터 타입.
=> MONEY, SMALLMONEY
3.7) 특수형 : 특수한 용도로 사용되는 데이터 타입.
=> TABLE, SQL_VARIANT, CURSOR,
UNIQUEIDENTIFIER, TIMESTAMP
4) 변수
: 변수란? 임의의 값을 저장하기 위한 메모리상의 임시 기억 공간이며 변수의
종류에는 사용자 정의 변수와 시스템 전역 변수가 있다.
4.1) 사용자 정의 변수
: 사용자의 필요에 의해 정의하는 변수를 사용자 정의 변수라 하며 @로 변
수명을 정의한다.
4.1.1) 변수 선언 : DECLARE
4.1.2) 변수에 값 할당 : SET, SELECT
4.1.3) 변수에 값을 출력 : SELECT, PRINT
예) DECLARE @AAA INT, @BBB INT
SET @AAA = 10
SELECT @BBB = 20
SELECT @AAA, @BBB => 변수 값을 출력한다.
PRINT @AAA + @BBB => 문자 형식으로 메시지를 출력 한다.
4.2) 시스템 정의 변수 ( 함수 )
: 시스템의 상태 정보 등을 저장하기 위한 목적으로 미리 만들어진 변수이
며 @@로 변수명이 시작 된다.
예) @@SERVERNAME, @@VERSION, @@ROWCOUNT
@@ERROR , @@NESTLEVEL, @@TRANCOUNT
SELECT * FROM TITLES
SELECT @@ROWCOUNT
SELECT @@ERROR
1. 시스템 제공 함수
1) 연산 함수
: 매개 변수로 제공된 값을 연산하여 숫자 값을 리턴한다.
= > CELING, FLOWER, RAND, ROUND
2) 문자 함수
= > +, LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, LOWER, UPPER,
CHARINDEX, SPACE, REPLACE….
3) 날짜 함수
= > GETDATE, DATEADD, DATEDIFF, DATEPART, DAY, MONTH,
YEAR
4) 계산 함수
= > SUM, MAX, MIN, AVG, COUNT
( 주의 : NULL값을 포함한 값은 계산 대상에서 제외 )
5) 변환 함수
= > CONVERT, CAST
6) 시스템 함수
= > DB_ID, DB_NAME, HOST_NAME, ISDATE, ISNULL,
ISNUMERIC, NULLIF
7) 보안 함수
= > SUSER_SANME(), SUSER_SID, USER_NAME()
8) 행 집합 함수
= > OPENQUERY, OPENROWSET
2. SQL 문장의 기본 II
1) Null값 조회 ( IS NULL, IS NOT NULL )
: Null값은 아무런 값도 입력되지 않은 컬럼이 가지는 값의 형식이다.
Null은 아무 값도 없기 때문에 Null = Null은 일치하지 않는다.
2) 데이터 정렬 ( ORDER BY절 )
: 특정 컬럼을 기준으로 데이터를 정렬하고자 할 때 정의하며 기본 오름차
순 ( ASC ) 이고 내림차순 ( DESC )으로 정렬할 수도 있다.
SELECT * FROM TITLES ORDER BY PRICE ASC ( 오름차순 )
SELECT * FROM TITLES ORDER BY PRICE DESC ( 내림차순 )
3) 범위 조회 (BETWEEN A AND B )
: SELECT * FROM TITLES WHERE PRICE BETWEEN 20 AND 30
SELECT * FROM TITLES
WHERE LEFT( TITLE_ID, 1 ) BETWEEN 'B' AND 'M'
4) 출력 레코드 제한
(SET ROWCOUNT N, TOP N ( WITH TIES )
: SET ROWCOUNT 10
SELECT * FROM TITLES
SELECT TOP 10 * FROM SALES
SELECT TOP 10 * FROM SALES ORDER BY QTY DESC
SELECT TOP 10 WITH TIES * FROM SALES ORDER BY QTY DESC
5) 목록 조건 조회 ( IN )
: SELECT * FROM TITLES WHERE TITLE_ID IN ('BU1032', 'MC3021')
SELECT * FROM TITLES WHERE TITLE_ID = 'BU1032'
OR TITLE_ID = 'BU1111' OR TITLE_ID = 'MC3021'
6) 유사 문자 조회 ( LIKE 유형 : _ , %, [], [^] )
: SELECT * FROM TITLES WHERE TITLE_ID LIKE 'BU103_‘
SELECT * FROM TITLES WHERE TITLE_ID LIKE 'B%‘
SELECT * FROM TITLES WHERE TITLE_ID LIKE '[BM]%‘
SELECT * FROM TITLES WHERE TITLE_ID LIKE 'B[^U]%‘
7) 중복된 행 제거 ( DISTINCT )
: SELECT TITLE_ID FROM SALES
SELECT DISTINCT TITLE_ID FROM SALES
8) 권장 사항
: 조회의 조건에 자주 등장하는 컬럼에 인덱스를 정의하자.
조회의 조건에 포함되는 컬럼을 가공하지 말자.
SELECT TITLE_ID, PRICE FROM TITLES
WHERE PRICE * 1.1 < 20 ORDER BY PRICE
SELECT TITLE_ID, PRICE FROM TITLES
WHERE PRICE < 20 / 1.1 ORDER BY PRICE
3. GROUP BY, HAVING 절
1) GROUP BY 절
: 항목별로 요약된 결과값을 출력한다.
SELECT TITLE_ID, QTY AS ‘판매 수량’ FROM SALES
SELECT TITLE_ID, SUM(QTY) AS ‘판매 수량’ FROM SALES
GROUP BY TITLE_ID
2) HAVING 절
: GROUP BY절에 의해 출력된 결과에 대한 조건을 정의한다.
SELECT TITLE_ID, SUM(QTY) AS '판매 수량' FROM SALES
GROUP BY TITLE_ID
HAVING SUM(QTY)>= 30
SELECT TITLE_ID, SUM(QTY) AS '판매 수량' FROM SALES
WHERE QTY > 10
GROUP BY TITLE_ID
HAVING SUM(QTY)>= 30
4. COMPUTE, COMPUTE BY 절
1) COMPUTE 절
: 세부 데이터의 조회와 전체 요약된 결과값을 출력한다.
SELECT TYPE, TITLE_ID, PRICE FROM TITLES
COMPUTE AVG(PRICE)
2) COMPUTE BY 절
: 세부 데이터의 조회와 항목별 요약된 결과값을 출력한다.
SELECT TYPE, TITLE_ID, PRICE FROM TITLES
ORDER BY TYPE
COMPUTE AVG(PRICE) BY TYPE
1. INSERT, UPDATE, DELETE
1) 데이터 입력 ( INSERT )
: 테이블이나 뷰를 통해 테이블에 데이터를 입력한다.
구문 : INSERT [INTO] 테이블명 ( COL1, COL2, COL3,… )
VALUES ( VAL1, VAL2, VAL3,… )
2) 데이터 수정 ( UPDATE )
: 테이블에 입력된 데이터를 수정한다.
구문 : UPDATE 테이블명
SET COL1 = VAL1 ,
COL2 = VAL2
WHERE COL3 = VAL3
3) 데이터 삭제 ( DELETE )
: 테이블에 입력된 데이터를 삭제한다.
구문 : DELETE FROM 테이블명
WHERE COL1 = VAL1
2. TRANSACTION의 기본 개념
1) TRANSACTION이란?
: 데이터베이스에서 작업의 처리 단위를 TRANSACTION이라고 한다.
트랜잭션의 종류는 암시적 트랜잭션과 명시적 트랜잭션 두가지가 있다.
2) 암시적 트랜잭션
: 사용자가 트랜잭션의 시작을 직접 지정할 필요 없이 각 트랜잭션을 커
밋( COMMIT ) 또는 롤백 ( ROLLBACK ) 하기만 하면 된다.
암시적 트랜잭션 설정 : SET IMPLICIT_TRANSACTIONS ON
암시적 트랜잭션 해재 : SET IMPLICIT_TRANSACTIONS OFF
3) 명시적 트랜잭션
: 개발자가 직접 트랜잭션의 시작과 끝을 정의 한다.
BEGIN TRANSACTION 으로 트랜잭션을 시작하고 COMMIT이나
ROLLBACK으로 트랜잭션을 종료 한다.
3. SUB QUERY, 상관관계 SUB QUERY
: DML문장 ( SELECT, INSERT, UPDATE, DELETE ) 안에 SELECT문장을
SUB QUERY라고 한다.
1) 단순 SUB QUERY
특징 : 괄호로 묶여 있다.
SUB QUERY만 실행해도 실행된다.
안쪽 질의가 먼저 실행된 후 바깥 질의가 실행 된다.
2) 단순 SUB QUERY 활용
예) SELECT STOR_ID, SUM(QTY),
ROUND(CONVERT(FLOAT, SUM(QTY)) /
(SELECT SUM(QTY) FROM SALES) * 100 , 2)
FROM S ALES
GROUP BY STOR_ID
예) SELECT TITLE FROM TITLES
WHERE TITLE_ID IN ( SELECT TITLE_ID FROM SALES )
3) 상관관계 SUB QUERY
특징 : 바깥 질의의 결과가 안쪽 질의에 영향을 미치고 안쪽 질의의 결과가
다시 바깥 질의에 영향을 준다.
안쪽 질의만 수행하면 문장이 실행되지 않는다.
4) 상관관계 SUB QUERY 활용
예) SELECT A.* FROM SALES A
WHERE 10 > ( SELECT COUNT(*) FROM SALES B
WHERE A.QTY < B.QTY )
ORDER BY QTY DESC
4. 다른 테이블을 기반으로 한 데이터 입력
1) SELECT ~ INTO 문
: 새로운 테이블을 만들면서 기존에 있는 테이블의 데이터를 입력 한다.
SELECT * INTO TITLES2 FROM TITLES
SELECT TITLE_ID, TITLE, PRICE INTO TITLES3 FROM TITLES
WHERE PRICE >= 20
SELECT * INTO #TITLES FROM TITLES
SELECT * INTO ##TITLES FROM TITLES
2) INSERT ~ SELECT 문
: 기존의 있는 테이블에 다른 테이블의 데이터를 입력한다.
INSERT TITLES2
SELECT * FROM TITLES
INSERT TITLES3
SELECT TITLE_ID, TITLE, PRICE FROM TITLES
WHERE PRICE < 20