SQL Server 2000 기본 및 활용

1. SQL Server 2000기본 및 활용 이배현( leebhhan@naver.com )

2.  수강 대상 1. 선수 과목 • 운영체제와 네트워킹에 관한 기본적인 내용 • 운영체제에서 계정 관리 • 프로그래밍 기초 ( VB, Java, Asp, Jsp…. ) 2. 수강 대상 • SQL Server에 관한 체계적인 개념 정립을 원하는 학생, • 학원 수강생 ( 초보자 ) • 현업 프로그래머로 데이터베이스와 관련한 프로그램 개발을 하는 개발자 ( 중급자 ) • 현업 서버 관리자로 단순 데이터베이스 서버 관리를 넘어 효과적인 데이터베이스 관리를 원하는 관리자 ( 중급자 ) 3. 강의 환경 • 운영체제 : Windows XP • SQL Server : SQL Server 2000 Enterprise Edition

3. 주제 : 관계형 데이터베이스 및 데이터베이스 기본 • 이배현( leebhhan@naver.com )

4. 1. 관계형 데이터베이스의 기본 이해 1) 관계형 데이터베이스란?: 1969년 IBM의 연구원으로 있던 .F.Codd가 수학적 기초에 근거를 두 고 고안한 것이 관계형 데이터베이스 (Relational Database) 이다.기본 개념: 데이터베이스는 최소한의 의미를 가지는 테이블들로 구성되 며 그 테이블들에 있는 필드들로 연결한 것이다.필드 또한 가장 작은 논리적인 단위로 구분하는 것이 좋다. 2) 관계와 참조 무결성 1) 관계란? : 두개의 실체간에 명명되어진 의미 있는 연결이며 두 실체간에 업무 적인 연관성을 나타낸다. 2) 참조 무결성: 관계 형성을 통해 부모 테이블과 자식테이블 간의 정의되는 데이터 무결성 구현 방법.

5.  관계형성을 통한 참조 무결성 제약 조건 부모 테이블 자식 테이블 입 력 제약 없음 부모 테이블에 데이터가 존재하는지 검증 수 정 수정 하려는 데이터를자식 테이블에서 참조하고 있는지를 검증 부모 테이블에 존재하는 다른 데이터로 변경가능 삭 제 삭제 하려는 데이터를자식 테이블에서 참조하고 있는지를 검증 제약 없음  CASCADE 옵션UPDATE CASCADE : 부모 테이블의 데이터를 수정할 때 이를 참조하고 있는 자식 테이블의 데이터도 함께 수정한다. DELETE CASCADE : 부모 테이블의 데이터를 삭제할 때 이를 참조하고 있는 자식 테이블의 데이터도 함께 삭제한다.

6. 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가 주로 이용하는 데이터베이스 이다.

7. 2.3) Model 데이터베이스: 관리자가 만드는 모든 데이터베이스의 원형으로 존재하는 데이터베이 스 이다. Model 데이터베이스를 바꾸면 앞으로 만들어 지는 모든 데이터베이스 에 영향을 미친다. 2.4) Tempdb 데이터베이스: 임시 테이블이나 정렬 작업등을 일시 저장하기위한 테이블이다. Tempdb는 SQL Server Service가 시작될 때 항상 초기화 되어진다.2.5) Distribution 데이터베이스: 복제 설정을 했을 때만 생성되는 데이터베이스로 복제에 사용되는 정보 나 트렌젝션 데이터를 저장한다. 3) 사용자 정의 데이터베이스란? : 관리자나 개발자들이 필요에 의해서 생성한 데이터베이스를 말하며 일반 적으로 업무에서 사용하는 데이터베이스를 말한다.

8. 4) 데이터베이스 만들기: 데이터베이스는 최소한 하나 이상의 데이터파일과 하나 이상의 로그 파일 로 구성된다. 30 Mb 10Mb 10Mb 10Mb 10Mb Log File Data File 3.1) 엔터프라이즈 관리자를 이용하여 데이터베이스 만들기(SAMPLEDB) 3.2) Query 분석기를 이용하여 데이터베이스 만들기3.3) 데이터 파일 확인 3.4) 데이터베이스 삭제

9. 3. 데이터베이스 개체 ( Object ) 1) 테이블 ( Table ) : 2차원 배열 구조로 실제 데이터를 저장하는 핵심 오브젝트 이다. 2) 뷰 ( View ) : 가상의 논리적인 테이블로 View를 사용하는 이유는 편의성과 보안성이 다. View를 통해 데이터의 입력, 수정, 삭제가 가능하며 View와 테이블 간의 조인도 가능하다. 3) 저장 프로시저 ( Stored Procedure ) : View와 비슷한 모습을 갖지만 파라미터를 활용할 수 있어서 융통성이 뛰 어나고 절차적인 프로그래밍이 가능하며 속도가 빠르다. 4) 트리거 ( Trigger ) : 테이블에 데이터가 입력, 수정, 삭제될 때 동작하는 프시저의 한 형태이 다. 업무 규칙 ( Business Rule )등을 정의할 때 주로 사용한다. 5) 사용자 정의 함수 ( Function ) : 사용자가 임의로 리턴값을 구하는 환경에서 직접 함수를 정의해서 사용 할 수 있다.

10. 6) 사용자 정의 데이터 타입 : 동일한 데이터타입이 반복적으로 사용될 때 하나의 사용자 정의 데이터 타입을 만들어 적용할 수 있다. 7) 제약조건 ( Constraint ): 논리적으로 잘못된 데이터가 입력되는 경우의 수를 제거하기 위해서 사용 되는 데이터 무결성 구현 방법이다. ( 컬럼에 적용되는 3가지 제약조건 : Not Null, No Duplicate, No Change )7.1) 기본키(PK) 제약 조건: Not Null, No Duplication7.2) Unique 제약 조건: No Duplicate7.3) 외래키(FK) 제약 조건: 관계 형성에 따른 자식 테이블의 입력, 수정 제 약 조건.7.3) Check, Rule : 특정 컬럼에 입력이 허용 가능한 데이터나 데이터 범위를 지정한다.7.4) 디폴트(Default) : 하나의 레코드를 입력할 때 아무런 값도 입력되지 않 은 컬럼에는 Null값이 입력되게 되는데 이때 기본적으로 입력되는 데이터를 정의한다.

11. 8) 인덱스 ( Index ): 데이터의 검색 속도를 향상시키기 위해 하나의 컬럼 혹은 여러 컬럼에 인 덱스를 정의한다. 9) 데이터베이스 다이어그램 : 데이터베이스 내부의 테이블과 관계를 시각적으로 보여주는 인터페 이스로 관계 설정 및 기타 테이블과 관련한 관리작업을 할 수 있다.

12. 주제 : DML (Data Manipulation Language) • 이배현( leebhhan@naver.com )

13. 1. SQL 문장에 대한 소개 1) SQL ( Structured Query Language )문장의 특징 : SQL은 어떻게 작업을 수행할 지가 아니라 무엇을 해야 하는지에 관해서 정의하는 결과 중심적인 언어이다.사용자가 RDBMS에 명령을 실행할 때 SQL문장을 사용하며 RDBMS는 물 리적으로 데이터베이스에서 명령을 수행하고 결과를 사용자에게 반환한 다. 사용자 데이터베이스 DB관리도구 데이터베이스관리시스템( DBMS) SQL 사용자 Application 사용자 사용자 Application 사용자

14. 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) SELECT : 테이블의 데이터 조회2.2) INSERT : 테이블에 데이터 입력2.3) UPDATE : 테이블에 데이터 수정2.4) DELETE : 테이블에 데이터 삭제 3) DCL ( Data Control Language) 3.1) GRANT : 특정 자원 사용에 대한 권한 부여3.2) REVOKE : 특정 자원에 부여된 권한 취소 ( 권한을 주기 전 상태 ) 3.3) DENY : 특정 자원에 대한 권한 모두 취소 ( 모두 부정 )

15. 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

16. 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 : 날짜와 시간을 분 단위까지 저장.

17. 3.5) 이진형 : BINARY데이터를 저장하기 위한 데이터 타입. => BINARY, VARBINARY, IMAGE 3.6) 화폐형 : 금액을 저장하기 위한 데이터 타입. => MONEY, SMALLMONEY 3.7) 특수형 : 특수한 용도로 사용되는 데이터 타입. => TABLE, SQL_VARIANT, CURSOR, UNIQUEIDENTIFIER, TIMESTAMP 4) 변수: 변수란? 임의의 값을 저장하기 위한 메모리상의 임시 기억 공간이며 변수의 종류에는 사용자 정의 변수와 시스템 전역 변수가 있다. 4.1) 사용자 정의 변수: 사용자의 필요에 의해 정의하는 변수를 사용자 정의 변수라 하며 @로 변 수명을 정의한다.

18. 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

19. 1. 시스템 제공 함수 1) 연산 함수 : 매개 변수로 제공된 값을 연산하여 숫자 값을 리턴한다. = > CELING, FLOWER, RAND, ROUND 2) 문자 함수= > +, LEFT, RIGHT, SUBSTRING, LTRIM, RTRIM, LOWER, UPPER, CHARINDEX, SPACE, REPLACE…. 3) 날짜 함수= > GETDATE, DATEADD, DATEDIFF, DATEPART, DAY, MONTH, YEAR4) 계산 함수= > SUM, MAX, MIN, AVG, COUNT ( 주의 : NULL값을 포함한 값은 계산 대상에서 제외 )

20. 5) 변환 함수 = > CONVERT, CAST 6) 시스템 함수= > DB_ID, DB_NAME, HOST_NAME, ISDATE, ISNULL, ISNUMERIC, NULLIF 7) 보안 함수= > SUSER_SANME(), SUSER_SID, USER_NAME() 8) 행 집합 함수= > OPENQUERY, OPENROWSET

21. 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'

22. 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 DESC5) 목록 조건 조회 ( 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]%‘

23. 7) 중복된 행 제거 ( DISTINCT ) : SELECT TITLE_ID FROM SALES SELECT DISTINCT TITLE_ID FROM SALES8) 권장 사항: 조회의 조건에 자주 등장하는 컬럼에 인덱스를 정의하자.조회의 조건에 포함되는 컬럼을 가공하지 말자. 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

24. 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)>= 30SELECT TITLE_ID, SUM(QTY) AS '판매 수량' FROM SALES WHERE QTY > 10 GROUP BY TITLE_ID HAVING SUM(QTY)>= 30

25. 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

26. 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

27. 2. TRANSACTION의 기본 개념1) TRANSACTION이란?: 데이터베이스에서 작업의 처리 단위를 TRANSACTION이라고 한다.트랜잭션의 종류는 암시적 트랜잭션과 명시적 트랜잭션 두가지가 있다.2) 암시적 트랜잭션: 사용자가 트랜잭션의 시작을 직접 지정할 필요 없이 각 트랜잭션을 커 밋( COMMIT ) 또는 롤백 ( ROLLBACK ) 하기만 하면 된다.암시적 트랜잭션 설정 : SET IMPLICIT_TRANSACTIONS ON 암시적 트랜잭션 해재 : SET IMPLICIT_TRANSACTIONS OFF 3) 명시적 트랜잭션: 개발자가 직접 트랜잭션의 시작과 끝을 정의 한다. BEGIN TRANSACTION 으로 트랜잭션을 시작하고 COMMIT이나 ROLLBACK으로 트랜잭션을 종료 한다.

28. 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 )

29. 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

30. 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