Transcript SQL 강의노트

MS-SQL7.0 Implementation
강의 노트
Written by 남현주
1 일
 history, 설치, R-DBMS
 Chapter 1. SQL Server개요
 Chapter 2. Transact- SQL 개요
 Chapter 3. 데이터베이스 생성하기 (간략히)
History (SQL Server의 역사)
1970년 IBM :
데이터베이스 질의를 위한 언어 만듬 – SEQUEL
(구조적 질의 언어 : Structured English Query Language)
Sybase 와 Microsoft 가 협력
 v1.0을 OS/2용으로 , v4.2 부터 NT 서버로 이식
 V6.0 부터는 Microsoft 독자적으로 개발
 6.5, 7.0으로 진보된 기능들을 선보임
(위저드 제공, UniCode지원)
제1장 SQL Server 개요

SQL Server란
클라이언트/서버형의 관계형 데이타베이스 관리 시스
템(RDBMS)이다.

클라이언트/서버(Client/Server Architecture)
클라이언트 : 다른 시스템 구성요소 들에게 서비스나
자원을 요청하는 시스템 구성요소
서
버 : 서비스나 자원을 다른 시스템 구성 요소
들에 제공하는 시스템 구성요소

클라이언트/서버 환경
클라이언트는 서버에게 자신이 원하는 바를 요청
(Request)한다. 그러면 서버는 그에 대해 처리하고
클라이언트에게 응답(Response)한다

데이터베이스의 필요 배경
SAM(Sequential Access Method)
I-SAM(Indexed SAM) 파일 출현
 PC :dBase, 호스트 :네트워크 DB, 계층적 DB 등장
 DBMS(DataBase Management System) 등장
SQL Server는
관계형 데이터베이스 관리 시스템
(RDBMS:Relational Database Management System)
- 데이터에 기반한 시스템의 3가지 범주
. 파일 기반 시스템(File-based systems)
. 호스트 기반 시스템(Host_based systems)
. 클라이언트/서버 시스템(C/S systems)

관계형 데이터베이스 관리 시스템(RDBMS)
구조 : 2차원 테이블
Data base
table
column
row
Data modeling
entity
attribute
instance
Application
record set
field
record

Transact_SQL
SQL Server는 구조화된 쿼리 언어의 한 버전인
Transact-SQL을 데이터베이스 쿼리 및 프로그래
밍언어로 이용한다. Transact-SQL로는 데이터를
액세스하고 관리할 수 있다.
조 작 : DB에 저장된 데이터의 입력, 수정, 삭제, 조
회 등의 작업을 SQL에 의해서 최종사용자에
게 보여지게 함
무결성 : 데이터의 일관성, 믿을 만한 데이터로 만듬

NT 서버와의 관계
SQL Server는 NT Server가 주된 플랫폼.
NT의 기능들 중에서 SQL Server가 그대로 사용하는 것
은 다음과 같다.
1.
2.
3.
4.
5.
6.
7.
보안 체계
멀트 프로세서의 지원
NT 이벤트 지원
서비스로서의 동작
성능 모니터
Index서버
백 오피스와 관련성

SQL Server 구성요소
- SQL Server 서비스
. MSSQLServer 서비스
. SQL Server Agent 서비스
. MS DTC
(Microsoft Distributed Transaction Coordinator)
. MS Search 서비스
- SQL Server 클라이언트 소프트웨어
.
.
.
.
SQLServer Enterprise Manager
SQL Server Query Analyzer
SQL Server 관리도구와 마법사
SQL Server 명령 프롬프트 관리 도구들

SQL Server 구조개요
- 통신 구조
.
.
.
.
어플리케이션
데이터베이스 인터페이스
네트워크 라이브러리
개방형 데이터 서비스
- 데이터 액세스 구조
. 응용 프로그램 프로그래밍 인터페이스
OLE DB
ODBC
. 데이터 객체 인터페이스
ADO
RDO
제2장 Transact-SQL 개요
 SQL Server 프로그래밍 도구
SQL Server Enterprise Management 사용방법과
SQL Server Query Analyzer 사용방법
-
세션
쿼리저장
GO 배치 분리자
판독의 용이를 위한 여러 라인 코딩
실행(전체, 일부분)
Parse : 분석은 하지만 실행하지는 않음
단축키
osql 유틸리티 : 명령 프롬프트에서 쿼리나
스크립트 실행

Transact-SQL 구성요소
1.
데이터 제어 언어(Data Control Language, DCL)
2.
데이터 정의 언어(Data Definition Language, DDL)
3.
데이터 조작 언어(Data Manipulation Language, DML)
4.
변수, 연산자, 함수, 처리 제어 언어, 주석
(데이터 제어 언어(Data Control Language, DCL))
DCL문은 데이터베이스 사용자 혹은 역할과 관련된 권
한을 변경할 때 사용.
문(Statement)
설명
GRANT
보안이 요구되는 시스템에서 데이터를
조작하고 T-SQL 문을 실행하는 사용
자를 허가하는 엔트리를 생성한다.
DENY
보안이 요구되는 시스템에서 계정의
권한을 거부하고 사용자, 그룹, 역할로
부터 그들의 그룹과 역할 멤버쉽을 통
해 권한을 상속 받는 것 을 막 는다 .
REVOKE 이전에 허가 또는 거부되었
던 권한을 제거한다.
(데이터 정의 언어(Data Definition Language, DDL))
DDL문은 데이터베이스와 테이블 및 저장 프로시저 같
은 데이터베이스 객체의 생성을 통해 데이터베이스
구조를 정의.
CREATE , ALTER, DROP 문
* 참고 : 객체의 이름
Fully Qualified Names : 서버이름, 데이터베이스 이
름, 소유자 이름, 객체 이름
대부분의 객체 참조는 로컬 서버를 디폴트로 하고 세
부분의 이름을 사용한다
(데이터 조작 언어(Data Manipulation Language, DML))
DML문은 데이터베이스에서 데이터를 조작. 이 문을 이
용하여 데이터 변경 또는 정보를 추출할 수 있음.
SELECT, INSERT, UPDATE, DELETE
* 기본적인 SELECT 문
SELECT 이후에는 가져올 컬럼이나 표현식을 나열하면
된다. 모든 컬럼 지정시 * FROM 이후에는 어떤 테이블
로부터 데이터를 가져오는지 지시한다. 여러 테이블이
올 수 도 있고, 뷰가 올 수도 있다. WHERE 절 이후에는
어떤 행을 가져올 것인지를 지시한다. 생략하면 전체행
을 가져온다.
예)
SELECT * FROM titles
– titles테이블의 전체 자료 가져오기
SELECT title_id, pub_id, price, title
FROM titles
– 원하는 컬럼 순으로 가져오기
SELECT ‘책 번호:’, title_id, pub_id, price, title
FROM titles
– 임의의 컬럼 만들기
SELECT Title_no = title_id, ‘출판사 번호’ = pub_id,
price, title
FROM titles – 컬럼의 제목 바꾸기(1)
SELECT title_id as Title_no , pub_id as [출판사 번호],
price, title
FROM titles – 컬럼의 제목 바꾸기(2)
SELECT title_id Title_no , pub_id [출판사 번호], price,
title
FROM titles – 컬럼의 제목 바꾸기(3)
SELECT title_id as Title_no, pubid as ‘출판사 번호’,
price, Convert(char(30), title) as ‘줄인 제목’
FROM titles – 자료를 변형시킴(1)
SELECT title_id as Title_no, pubid as ‘출판사 번호’,
price * 1.1 as ‘가격(부가세 포함)’
FROM titles – 자료를 변형시킴(2)
* 주의 : SELECT 10/3 은?
(로컬 변수)
변수는 할당된 값을 갖는 언어 구성요소이다. 사용자
정의 로컬 변수는 @로 시작한다.
예)
DECALRE @lvname char(20)
SET @ lvname = ‘Dodsworth’
SELECT * FROM employees
WHERE lastname = @lvname
(연산자)
치환 연산자(=), 산술 연산자, 단항 연산자, 비교 연산
자, 문자열 연결 연산자(+), 논리 연산자
(함
수)
. Rowset함수 :
T-SQL 문 테이블에서 사용될 수 있는 객체를 반환한다.
. 집계(Aggregate)함수 :
일련의 값으로부터 하나의 요약된 값을 반환한다.
. 스칼라(Scalar)함수 :
하나의 값을 반환한다. (p96 ~102)
형변환 함수 )
CONVERT()
연산 함수)
ABS() – 절대값
CEILING() – 소수점 이하 버림
SELECT CEILING(29.9)
FLOOR() – 소수점 이상 절상
SELECT FLOOR(29.9)
ROUND() – 반올림
SELECT ROUND(29.1, 0), ROUND(29.953, 1)
문자열 함수)
LTRIM() : 앞쪽의 빈칸을 모두 삭제한다.
RTRIM() : 뒤에 오는 빈 칸을 삭제한다.
SUBSTRING() : 문자나 이진 문자열의 일부분을 돌려준
다. (한글도 한 글자가 한 문자로 인식)
SELECT SUBSTRING(‘아버지 나는 누구예요?’, 3,6)
SELECT SUBSTRING(‘ABFZ!!!’, 1,3)
REVERSE() – 문자 표현식을 거꾸로 돌려준다.
날짜 함수)
GETDATE() – 현재의 날짜와 시간
SELECT CONVERT(varchar(30), GETDATE(), 102)
DATEADD() – datepart부분에 지정한 숫자 값을 더한다.
SELECT DATEADD(DD, 100, GETDATE()) – 오늘로
부터 100일 후는?
datepart
약어
범위
year
Yy
1753-9999
quarter
Qq
1-4
month
Mm
1-12
Day of year
Dy
1-366
Day
Dd
1-31
Week
Wk
0-51
Weekday
Dw
1-7(1이 일요일)
Hour
Hh
0-23
Minute
Mi
0-59
Second
Ss
0-59
millisencond
Ms
0-999
시스템 함수)
OBJECT_ID() : 데이터베이스의 오브젝트 아이디 번호.
OBJECT_NAME() : 데이터베이스 오브젝트명
보안 함수)
SUSER_NAME() : 시스템 유저명을 돌려준다.
*
조건에 맞는 행 가져오기
SELECT title_id, qty
FROM sales
WHERE title_id = ‘BU1032’
*
비 교
= > < >= <= <> != !> !<
*
NULL
SELECT title_id, price
FROM titles
WHERE price IS NULL
주의)
SELECT title_id, price
FROM titles
WHERE price = NULL
*
정 렬
SELECT title_id, qty
FROM sales
ORDER BY title_id, qty
주의)
SELECT title_id, qty
FROM sales
ORDER BY title_id, qty DESC
*범 위
SELECT title_id, qty
FROM sales
WHERE qty BETWEEN 10 AND 20
(WHERE qty >= 10 AND qty <= 20)
*목 록
SELECT title_id, qty
FROM sales
WHERE title_id IN (‘BU1032’,’BU1111’,’MC3021’)
(WHERE title_id =‘BU1032’ OR title_id =’BU1111’
OR title_id =’MC3021’)
문자열에서 LIKE와 패턴 매칭
패턴
설명
_
정확히 한 문자가 와야 한다.
%
아무것도 없는 경우를 포함하여 어떤 것이라도 상관
없다.
[ ]안에 있는 글자들
[]
[^]
^ 다음에 이는 글자는 제외한 나머지
*
표현식
해당되는 것
해당되지 않는 것
book_
books, booka, booky
book, booked
book%
book, books, booked
abook, atbooks, adbooked
%book%
abook, abooks, book
brook, brooks
[st]ing
sing, ting
ving, king, k, v, vin
[b-f]ing
bing, cing, ding, eing ,fing
aing, b, f, ging
M[^c]%
Mike, Many, Mickey
McDonald, McAthur, M, Mc
중복된 행 제거 – DISTINCT
SELECT DISTINCT title_id
FROM sales
( ! DISTINCT가 사용되면 자동으로 정렬해줌 )
/* 튜닝을 위한 제안 */
. NOT 연산자를 사용하지 말것
: 색인의 도움을 받을 수 없다.
. 연산자 앞에는 컬럼만 오게 할 것
. 적절한 ( )와 띄어쓰기
: 가독성을 좋게한다.
(흐름 제어 언어 구성요소)
BEGIN… END 블록
IF… ELSE 블록
WHILE 구조 : 지정된 조건이 참인 동안 블록을 반복
적으로 수행
RETURN : 쿼리 혹은 프로시저로부터 무조건 빠져 나
옴. RETURN 뒤의 문은 실행되지 않는다.
0 값이 정상, 0이 아닌 값이 에러지시를
위해 사용된다.
인 라인 주석( -- ) : 주석문자의 오른쪽 텍스트는 무
시된다.
블록 주석(/* */)
(배치 이용하기)
여러 개의 SQL문을 모아 놓고 GO라는 키워드로 구분
한 것을 배치라고 한다.
배치 구분자 GO를 이용하여 배치 정의하기
 사용자 정의된 변수 범위는 배치에서 제한되므로 배치
구분자 GO 뒤에서는 참조할 수 없다. SQL Server는
GO가 나타날 때까지 문법 검사와 실행을 하지 않음.
규칙 : CREATE DEFAULT, CREATE TRIGGER, CREATE
PROC(EDURE), CREATE RULE, CREATE VIEW는
배치의 처음에 나타나야 한다.
(스크립트 사용하기)
파일로 저장되는 하나 이상의 T-SQL문이다. 즉, 배치
를 여러 개 모은 것을 스크립트라고 한다. 일반적으로
파일로 저장해두고 반복적인 작업을 할 때 사용한다.
표준 확장자는 .SQL 이다.
(쿼리가 처리되는 과정 P112~)
(캐시 쿼리)
(다이나믹하게 문 구성하기)
(트랜잭션 사용하기)
제3장 데이터베이스 생성하기

R-DBMS(관계형 데이터베이스) 설계 소개
데이터베이스란 어느 한 조직의 다수 응용 시스템들
이 사용하기 위해 통합, 저장되어진 서로 관련된 데
이터의 집합 
“서로 관련된 데이터의 집합”
행(Row),열(Column)로 이루어진 2차원 테이블 구조
용어정리>
데이터베이스
데이터 모델링
어플리케이션
테이블(table)
실체(entity)
레코드셋(record set)
열(column)
속성(attribute)
필드(field)
행(row)
인스턴스(instance)
레코드(record)
관계형 데이터베이스는 설계시에 꼭 중요한
이라는 작업이 들어간다. 설계에 들어가는
개발에 들어가는 시간보다 월등히 많이
다.(70~80%) 모델링을 하는 가장 큰 이유는
의 중복성을 제거하는 것이다.
모델링
시간이
필요하
데이터
* 모델링의 3단계 : 실체, 관계, 속성
실체(entity) :
하나로 묶을 수 있는 것의 실질적인
집합
예) 사원 테이블, 고객 테이블 등…
관계(Relationship) : 실체에 대한 설명이나 추가적
인 구분자. 기본키와 참조키로 정의
예) 사원과 부서의 관계, 고객과 주문의 관계 등…
속성(Attribute) : 실체나 관계에 대한 서술적 속성.
컬럼
예) 사원명, 사원번호, 주소, 전화번호 등…
* 논리적인 데이터베이스 설계
.테이블 및 해당 이름
.각 테이블의 컬럼명
.유일 값 요구, 널 허용, 컬럼에 저장될 자료형 같
은 컬럼의 특성
.각 테이블의 기본키 : 테이블의 컬럼을 유일하게 식
별할 수 있는 값들을 저장하는 컬럼이다.
.테이블간의 관계 : 어떤 테이블의 행들은 다른 테이
블의 하나 이상의 행에 종속적이다.이러한 테이블
사이의 종족성을 관계라 한다. 관계를 정의하기 위
해서 다른 테이블의 기본 키를 참조하는 이러한 컬
럼을 참조 키라 한다.
.관계에는 일대일, 일대다, 다대일, 다대다와 같은 경
우가 발생할 수 있다. 참고로 다대다는 R-DB에서는
인정되지 않는 관계로 정규화를 거쳐야 한다.
(정 규 화)
1.제 1정규화(1NF)
규칙 : 여러 값을 가진 컬럼이 존재할 수 없다. 반
복되는 그룹이 존재해서는 안된다. 각 행
과 열에는 오직 한 값만이 존재해야 한다.
사번
사원명
취미
1
홍길동
여행
골프
2
사미자
등산
1번 사원은 두 가지 취미를 갖고 있다. 이 그룹은 제1
정규화의 대상이 된다.
다음과 같이 정규화 한다.
사번
사원명
사번
취미
1
홍길동
1
여행
2
사미자
1
골프
2
등산
2.제 2정규화(2NF)
규칙 : 모든 키가 아닌 컬럼은 기본 키 전체에 의존
적이어야 한다. 기본키의 일부분에 의존적
이어서는 안된다.
사번
프로젝트번호
부서
프로젝트 역할
고과율
1
A
전산
팀장
A
1
B
전산
조원
C
1
C
전산
부팀장
B
2
C
경리
팀장
A
3
C
기획
팀장
A
위의 테이블에서 기본키는 (사번+프로젝트 번호)이
다. 그런데 부서 컬럼은 사번에 의존적이다. 즉, 부
서컬럼은 (사번+프로젝트 번호)에 의존적이지 않고
사번에 의존적이므로 아래와 같이 제 2정규화를 거
쳐야 한다.
사번
프로젝트
번호
프로젝트
역할
고과율
1
A
팀장
A
1
B
조원
C
1
C
부팀장
B
2
C
팀장
A
3
C
팀장
A
사번
부서
1
전산
2
경리
3
기획
3.제 3정규화(3NF)
규칙: 키가 아닌 컬럼은, 다른 키가 아닌 컬럼에 의
존적이어서는 안된다.
사번
프로젝트번호
프로젝트 역할
고과율
1
A
팀장
A
1
B
조원
C
1
C
부팀장
B
2
C
팀장
A
3
C
팀장
A
고과율 컬럼은 프로젝트 역할에 따라 변하고 있음을
볼 수 있다. 즉, 고과율은 키가 아닌, 프로젝트 역
할 컬럼에 의존적이다. 아래와 같이 정규화한다.
사번
프로젝트번호
프로젝트 역할
프로젝트 역할
고과율
1
A
팀장
팀장
A
1
B
조원
조원
C
1
C
부팀장
부팀장
B
2
C
팀장
3
C
팀장
4.제 4정규화(4NF)
규칙 : 3NF 테이블은 의존적인 다대다 관계를 가질
수 없다.
학번
이름
과목번호
과목명
1
최철호
K
국어
2
이상진
K
국어
3
김철민
K
국어
3
김철민
M
수학
3
김철민
E
영어
2
이상진
H
역사
2
이상진
P
정치
1
최철호
P
정치
위의 테이블은 학생과 과복 간의 관계가 다 : 다 관계
이다. 이를 해결하면 다음과 같다.
<학생>
<수강신청>
<과목>
학번
이름
학번
과목번호
과목번호
과목명
1
최철호
1
K
K
국어
2
이상진
2
K
M
수학
3
김철민
3
K
E
영어
3
M
H
역사
3
E
P
정치
2
H
2
P
1
P
5. 비 정규화
정규화를 하는 것은 되도록 중복된 데이터를 제거
해서 성능 향상에 도움을 주는 것에 초점을 두고
있으나, 자주 JOIN을 사용하면 차라리 테이블을 다
시 합치는 것이 나을 수도 있기 때문에 이를 고려
한 방법이 비 정규화이다. 뿐만 아니라 파생된 컬
럼을 테이블에 하나의 컬럼으로 저장하는 경우나,
한 테이블에서 자주 쓰는 행과 그렇지 않은 행을
분리하여 테이블을 별도로 관리하는 것 또한 비 정
규화에 속한다.

SQL Server 데이터베이스 소개
(데이터가 어떻게 저장 되는가)
(SQL Server 데이터베이스 파일)
일차 데이터 파일 (.mdf)
이차 데이터 파일 (.ndf)
로그 파일 (.ldf)
*
데이트베이스의 아키텍처:
데이터를 저장하는 데이터 파일은 그 내부에 여러 개
의 익스텐트 단위 구조를 가지고 있고, 다시 익스텐트
는 8개의 연속된 페이지를 기본 단위로 한다.
(SQL Server에서 Input/Output단위는 page이다)
페이지가 가들 차게 되면 다음 페이지에 저장된다. 그
러나 이 때에 하나의 행은 여러 페이지에 동시에 저장
되어 질 수 없기 때문에 익스텐트가 가득 차게 되면 새
로운 익스텐트를 할당받게 된다. 즉 64KB를 더 사용
할 수 있게 된다.
(트랜잭션 로그의 작동 방법)
트랜잭션
여러 개의 작업이 하나의 작업처럼 전부 처
리되거나 아니면 전부 처리가 안 되도록
(All or Nothing) 하는 일의 최소 단위
* 디스크의 성능을 높이기 위해서 캐쉬 사용
캐쉬는 디스크의 내용 중 일부를 메모리에
저장해 두는 원리나 메모리를 뜻함.
장점: 빠른 수행 속도
단점: 정전시 데이터가 하드디스크에
저장하지 못함
SQL Server는 트랜잭션 처리를 위해서 잠금과 로그 사용
* 데이터 변경 작업
로그에 먼저 기록 -> 트랜잭션 완료 -> 로그를 메모
리에서 디스크로 기록

데이터베이스 생성 및 삭제하기
* 생성하기
CREATE DATABASE mydb1 만 입력해도 데이터베이
스는 생성된다. 모두 기본 설정값을 그대로 반영한 것
이다.
예)
CREATE DATABASE mydb2 -- 데이터베이스 명은 mydb2이다
ON -- 데이터를 저장하는 공간이다.
( NAME = mydb2data -- 논리적 파일명을 지정한다.
, FILENAME = ‘c:\mssql7\data\mydb2.mdf’ -- 물리적 파일명을
지정한다.
, SIZE = 10MB --크기는 MB단위
, MAXSIZE = 50MB -- 최대 몇 MB까지 자동적으로 늘릴 것인지
지정한다.
, FILEGROWTH = 5% -- 늘어날 때 5% 단위로 늘어나도록 지정한다.
)
LOG ON
( NAME = mydb2log
, FILENAME = ‘c:\mssql7\data\mydb2.ldf’
, SIZE = 5MB
, MAXSIZE = 25MB
, FILEGROWTH = 5MB -- 늘어날 때 MB 단위로 늘어나도록
지정한다.
)
- 삭제하기
DROP DATABASE database_name [,…n]