Transcript 슬라이드 1
Chapter 7. DDL 소단원 소단원 : 학습목표 학습목표 본 장은 데이터베이스를 조작하기 위한 SQL 문중에서 특별히 데 이터베이스의 정의와 관련된 명령어인 DDL을 쿼리분석기를 사 용하여 하나씩 실습을 통해서 학습한다. DDL 명령어로 데이터베 이스 생성과 삭제, 테이블 생성, 수정, 삭제 등을 할 수 있다. 또한 데이터 검색의 효율을 높이기 위해 사용하는 인덱스의 생성과 삭 제 에 대해 학습한다. Chapter 7. DDL 소단원 소단원 : SQL 1. SQL • 특징 1. 초기의 SQL : 데이터 삽입, 삭제, 조회, 갱신 기능 제공 2. 개선된 SQL : 데이터베이스 관련 전반적인 기능 제공 3. 1992 년 ANSI 에서 채택된 관계 데이터 언어의 표준 4. 비절차적 언어 5. 단말기에서 데이터베이스 직접 조작용으로도 사용 가능 하고 응용 프로그램 속에 삽입된 형태로도 사용 가능 • 기능에 따른 SQL의 종류 1. DDL(Data Definition Language) 2. DCL(Data Control Language) 3. DML(Data Manipulation Language) Chapter 소단원 소단원 : 7. DDL DDL 2. D D L(데이터 정의어) 란? 2.1 D D L 종류 • 데이터베이스, 테이블, 뷰, 인덱스, 도메인, 제약조건 등 각종 개체를 생성, 수정, 삭제하는 관리기능을 제공하는 명령어 DDL종류 적용 기능 CREATE Database, Domain, Table, Index, View 데이터베이스, 도메인, 테이블, 인덱 스, 뷰 등 개체 생성 명령 DROP Database, Domain, Table, Index, Constraint, View 데이터베이스, 도메인, 테이블, 인덱 스, 제약조건, 뷰 등 개체 삭제 명령 Table, Domain 테이블, 도메인 수정 ALTER Chapter 소단원 소단원 : 7. DDL DDL 2.2 데이터 형식 종류 데이터 타입 정수 INT -231 ~ 231 - 1 사이의 정수 고정소숫점 DECIMAL -1038 + 1 ~ 1038 -1 사이의 십 기본 9 bytes 진수 실수 FLOAT(N) -1.79E+308 ~ 1.79E+308 사이 8 bytes 의 실수 문자 CHAR(N) N 자리 고정길이 문자열 최대 8000 bytes BINARY 고정길이 이진데이터 저장 최대 8000 bytes BIT(N) 0, 또는 1 1 bytes TABLE 결과 집합을 테이블형태로 저장 - DATETIME 1753.1.1 ~ 9997.12.31 사이의 8 bytes 날짜 MONEY -263 ~ 263 - 1 사이의 숫자 이진데이터 기능 기본크기 4 bytes 특수 날짜/시간 통화 8 bytes Chapter 7. DDL 소단원 소단원 : DDL 2.2 데이터 형식-계속 <엔터프라이즈 관리자 창에서 테이블 생성 시의 데이터 형식 선택 화면> Chapter 7. DDL 소단원 소단원 : DDL 2.2 데이터 형식-계속 << 사용자 정의 데이터 형식 >> 용도 : 여러 테이블에서 공동으로 사용되는 항목을 따로 등록해 두고 사용하는 방법 • 생성 예 : EXEC sp-addtype id_newpass ‘char(20)’ • 사용 예 : CREATE TABLE 고객(고객ID char(20) PRIMARY KEY, 패스워드 char(20) not null, 새패스워드 id_newpass, 주소 char(50), ......) • 삭제 예 : EXEC sp-droptype id_newpass Chapter 7. DDL 소단원 소단원 : 2.2 데이터 형식-실습하기 2 1 3 <<쿼리분석기에서 사용자 정의 데이터 형식 실행 결과 >> DDL Chapter 7. DDL 소단원 소단원 : DDL 2.3 도메인 테이블에서 사용하는 열(column)을 미리 정의해 두는 방법 시스템에 따라 지원하지 않는 경우도 있다. • 생성 예 : CREATE DOMAIN dm_degree char(10) DEFAULT 'silver' • 사용 예 : CREATE TABLE memberTable ( m_id char(10) not null, ..... m_degree dm_degree, ..... ) • 수정 예 : ALTER DOMAIN dm_degree char(12) • 삭제 예 : DROP DOMAIN dm_degree RESTRICT Chapter 소단원 소단원 : 7. DDL 3. 데이터베이스 생성 및 삭제하기 3.1 데이터베이스 생성 실습 : ‘test_db' 라는 데이터베이스를 생성하라. 명령 : CREATE DATABASE test_db 결과 : 1 3 2 DB 생성 및 삭제 Chapter 7. DDL 소단원 소단원 : DB 생성 및 삭제 3.2 데이터베이스 삭제 사용하지 않은 데이터베이스를 삭제한다. 테이블 등을 열고 사용 중인 데이터베이스나 복제용으로 지정된 데이터베이스 는 삭제할 수 없다. 삭제 작업은 master 데이터베이스에서 해야 한다. 삭제 작업은 데이터베이스 소유자, 또는 데이터베이스 관리자 만이 할 수 있고 이 권한은 양도할 수 없다. 지정된 데이터베이스내의 모든 디스크 파일이 삭제된다. 실습 : ‘test_db' 라는 데이터베이스를 삭제하라. 명령 : DROP DATABASE test_db Chapter 7. DDL 4. 테이블 생성 및 관리하기 4.1 테이블 생성 구문 형식 <참고> 개체이름 명명규칙 서버이름.데이터베이스이름.소유주이름.개체이름 예 : test_server.manpower.dbo.member_table 소단원 소단원 : 테이블 생성 및 관리 Chapter 7. DDL 소단원 소단원 : 4.1 테이블 생성-계속 실습 : 아래 명령을 이용하여 ‘상품’ 테이블을 생성해 보라. 명령 : CREATE TABLE 상품( 상품코드 CHAR(10) NOT NULL PRIMARY KEY, 소코드 CHAR(10) NOT NULL, 색상코드 CHAR(10) NOT NULL, 사이즈 CHAR(10) NOT NULL, 재고수량 INT ) 2 결과 : 3 1 테이블 생성 및 관리 Chapter 7. DDL 소단원 소단원 : 테이블 생성 및 관리 4.2 테이블 수정 실습 : ‘상품’ 테이블에 ‘비고’ 라는 열을 추가하고 기본 값을 ‘정품’ 으로 주어라. 명령 : ALTER TABLE 상품 ADD 비고 char(10) default '정품' 결과 : <<쿼리분석기에서 실행하고 개체 브라우저 창에서 확인한 모습>> Chapter 7. DDL 소단원 소단원 : 4.2 테이블 수정-계속 실습 : ‘상품’ 테이블에서 ‘비고’라는 열의 크기를 CHAR(30)으로, 기본값을 ‘기획’ 으로 변경하라. 명령 : ALTER TABLE 상품 ALTER COLUMN 비고 CHAR(30) SET DEFAULT '기획' 실습 : ‘상품’ 테이블에서 ‘비고’라는 열의 기본값을 제거하라. 명령 : ALTER TABLE 상품 ALTER COLUMN 비고 DROP DEFAULT 실습 : ‘상품’ 테이블에서 ‘비고’라는 열을 제거하라. 명령 : ALTER TABLE 상품 DROP COLUMN 비고 CASCADE 4.3 테이블 삭제 실습 : ‘상품’ 테이블을 삭제하여라. 명령 : DROP TABLE 상품 테이블 생성 및 관리 Chapter 소단원 소단원 : 7. DDL 테이블 생성 및 관리 4.4 테이블의 제약조건과 데이터 무결성 적절한 제약조건을 테이블에 정의하면 DBMS는 데이터의 무결성을 지원한다. 실습 : 다양한 제약조건을 가진 ‘상품1’ 테이블을 생성하여라. 명령 : CREATE TABLE 상품1( 번호 INT IDENTITY PRIMARY KEY, 상품코드 CHAR(10) NOT NULL, 상품명 CHAR(30) NOT NULL UNIQUE, 분류코드 CHAR(10) NOT NULL CHECK(분류코드 IN (‘셔츠‘,’쟈켓‘,‘바지’,‘점퍼’)), 색상코드 CHAR(10) NOT NULL, 사이즈 CHAR(10) NOT NULL DEFAULT ‘XL’, 재고수량 INT, FOREIGN KEY(색상코드) references 색상분류(색상코드) ON DELETE NO ACTION ON UPDATE CASCADE ); Chapter 7. DDL 소단원 소단원 : 테이블 생성 및 관리 4.4 테이블의 제약조건과 데이터 무결성- 계속 결과 : <<쿼리분석기에서 실행하고 개체 브라우저 창에서 확인한 모습>> Chapter 7. DDL 소단원 소단원 : 4.4 테이블의 제약조건과 데이터 무결성- 계속 Foreign Key 정의에 의해 DBMS가 검사한 데이터 오류 1. 상품 테이블의 색상코드는 색상테이블의 색상코드를 참조한다. 2. 노랑이라는 값은 참조할 수 없어 오류 처리하였다. 테이블 생성 및 관리 Chapter 7. DDL 소단원 소단원 : 인덱스 사용 5. 인덱스를 사용하자 5.1 인덱스 사용 이유 대규모 데이터 파일에서 특정 항목을 이용한 질의의 효율을 높여 응답 시간을 단축 하는 것이 인덱스를 사용하는 목적이다. 기본 키를 지정하면 테이블은 기본 인덱스가 자동 생성된다. 기본 키 외의 다른 항목을 이용한 검색을 위해서는 인덱스를 생성해 주어야 한다. 모든 인덱스는 오름차순으로 정렬된다. 인덱스를 이용하면 특정 레코드에 대한 임의 접근이 가능하다. 인덱스를 이용하면 데이터의 삽입, 삭제 시에 인덱스의 변경도 수행되므로 소규모 데이터인 경우 데이터베이스의 효율을 떨어뜨리게 된다. Chapter 7. DDL 소단원 소단원 : 인덱스 사용 5.2 인덱스의 종류 기본인덱스와 보조인덱스 기본인덱스 : 인덱스 지정 항목이 기본 키인 경우 보조인덱스 : 인덱스 지정 항목이 기본 키가 아닌 경우 단순인덱스와 복합인덱스 단순인덱스 : 단일 항목으로 구성된 인덱스 복합인덱스 : 두 개 이상의 항목으로 구성된 인덱스 CLUSTED 인덱스와 NONCLUSTED 인덱스 CLUSTED 인덱스 : 한 테이블의 테이블 자체를 인덱스화 하여 하나만 존재, 데이터 테이블이 정렬되어 있다. NONCLUSTED 인덱스 : 한 테이블에 여러 개의 인덱스가 존재, 데이터와 인 덱스가 분리된 일반적인 형태 UNIQUE 인덱스와 NONUNIQUE 인덱스 UNIQUE 인덱스 : 인덱스 항목의 값이 중복되지 않고 유일한 값만 올 수 있는 인덱스 NONUNIQUE 인덱스: 중복이 허용되는 인덱스 Chapter 7. DDL 소단원 소단원 : 5.3 인덱스의 생성과 삭제 인덱스의 생성 구문형식 CREATE [UNIQUE] [ CLUSTED | NONCLUSTED] INDEX 인덱스이름 ON 테이블이름(열이름 [, 열이름, 열이름...] ) 실습1 : ‘고객’ 테이블에 ‘주민번호’를 이용한 ‘jumin_no_idx’ 란 인덱스 생성. 명령 : CREATE UNIQUE INDEX jumin_no_idx ON 고객(주민번호) 실습2 : ‘고객’ 테이블의 ‘성명’ 필드와 ‘전화번호’ 필드를 묶어 복합인덱스 ‘member_idx’ 생성 명령 : CREATE INDEX member_idx ON 고객(성명, 전화번호) 실습3 : ‘고객’ 테이블에 기본키를 추가하여 기본 인덱스를 생성. 명령 : ALTER TABLE 고객 ADD CONSTRAINT PK_member Primary Key (고객ID) 인덱스의 삭제 실습 : ‘고객’ 테이블의 ‘m_name_idx’라는 인덱스를 제거하라. 명령 : DROP INDEX 고객.m_name_idx 인덱스 사용 Chapter 7. DDL 소단원 소단원 : 인덱스 사용 5.3 인덱스의 생성과 삭제-계속 엔터프라이즈 관리자에서 인덱스의 생성 작업순서 : 엔터프라이즈 관리자->인덱스설정 테이블 선택-> 단축메뉴의 모든 작업 선택>인덱스 관리->새로 만들기 버튼 클릭->인덱스 설정할 필드 선택, 인덱스 이름 정하기-> 확인 Chapter 7. DDL 소단원 소단원 : 인덱스 사용 5.3 인덱스의 생성과 삭제-계속 쿼리분석기에서 인덱스의 생성 실습 : ‘고객’ 테이블의 ‘성명’ 을 이용한 인덱스 ‘m_name_idx’ 생성 명령 : CREATE INDEX m_name_idx ON 고객(성명) 결과 : 2 1 3 Chapter 7. DDL 소단원 소단원 : 인덱스 사용 5.4 성능을 위한 인덱스의 검사 CLUSTED 인덱스 사용 기본 키 보다는 주 검색대상을 CLUSTED 인덱스로 지정 CLUSTED 인덱스 항목은 20 byte 가 넘지 않도록 설계 갱신보다는 읽기 전용인 항목을 CLUSTED 인덱스로 지정 NONCLUSTED 복합인덱스 사용 검색에 자주 이용되는 항목을 NONCLUSTED 복합인덱스로 구성하면 단일 CLUSTED 인덱스보다 효율적 인덱스 페이지에 빈 공간 확보하기 삽입, 삭제 등이 빈번한 데이터의 경우 인덱스 페이지 구성을 위해서 이동이 많으므로 약간의 여유 공간이 있으면 효율적 인덱스 다시 만들기 삭제가 많이 진행된 인덱스 페이지는 빈 공간이 많아 속도가 떨어지므로 주 기적으로 인덱스를 다시 생성하는 것이 효율적 WITH DROP_EXISTING을 이용하여 인덱스를 생성하면 기존의 관계를 유지 하면서 새로 생성 가능 Chapter 7. DDL 소단원 소단원 : 단원요약 1 ★ SQL은 기능에 따라 DDL, DCL, 로 나누어진다. ? 이중에서 DDL은 데이터베이스, , 뷰, 인덱스 등 ? 각종 개체를 생성하고 관리하는 명령어이다. 답: DML, 테이블 단원요약 2 ★ 개체를 생성하는 데는 문이 사용되고 개체를 수정 ? 하는 데는 ALTER 문이 사용된다. 사용이 더 이상 되지 않는 개체는 DROP 문으로 제거할 수 있다. 답: CREATE 단원 요약 Chapter 7. DDL 소단원 소단원 : 단원 요약 단원요약 3 ★ 데이터의 무결성을 위해서 테이블을 생성할 때 기본 값, 유효 범위 지정, 기본 키 및 외래 키 지정, NULL 허용 등 다양한 종 류의 을 지정할 수 있다. ? 답: 제약조건 단원요약 4 ★ 효과적인 검색 성능을 위해 인덱스를 검사해 볼 필요가 있다. 삽입, 삭제 등이 빈번한 데이터인 경우는 인덱스 페이지에 여유 공간이 있는 것이 좋고 삭제가 많이 진행된 데이터의 인덱스는 기존의 인덱스를 하는 것이 좋다. ? 답: 제거하고 다시 생성