MS-SQL7.0 Implementation 강의 노트 Written by 남현주 Written by 남현주

2 일 chapter 4. 데이터베이스 객체 생성하기 chapter 5. 데이터 무결성의 종류 chapter 8. 다중 테이블 쿼리하기

제4장 DB 객체생성하기 데이터 형식 생성하기 (시스템 제공 데이터 형식)

Type of data System-supplied data types Number of bytes Binary binary[(n)] varbinary[(n)] 1-8000 Character char[(n)] varchar[(n)] (8000 characters) Unicode character nchar[(n)] nvarchar[(n)] 2-8000 (1 - 4000 characters) Date and time Datetime, smalldatetime 8 (2 4-byte integers) 4 (2 2-byte integers) Exact numeric decimal[(p[, s])] numeric[(p[, s])] 5-17 Approximate float[(n)] real 4-8 4 Global Identifier uniqueidentifier 16 Integer int smallint, tinyint integer 2, 1 Monetary money, smallmoney 8, 4 Special bit, cursor, sysname, timestamp 1, 0-8   Text and image text, image 0-2 GB Unicode text ntext

정밀형 숫자 데이터 유형 : decimal, numeric 근사형 숫자 데이터 형식 : real, float   (사용자 정의 데이터 형식 생성 및 삭제하기) 생성 예) EXEC sp_addtype isbn, ‘smallint’, NOT NULL 삭제 예) EXEC sp_droptype isbn

테이블 생성하기 (테이블 생성 및 삭제하기) 생성 예) CREATE TABLE mydb.dbo.emp ( Id INT , name CHAR(10) ) 삭제 예) DROP TABLE emp

 해당 테이블에 대한 권한 특성 뿐만 아니라 테이 테이블 삭제  해당 테이블에 대한 권한 특성 뿐만 아니라 테이 블 정의 및 모든 데이터가 제거된다. 그러나 다른 테이블과의 종속성은 먼저 삭제해야 하므로, sp_depends 시스템 저장 프로시저를 실행해서 종 속성을 살핀다. 또한 삭제된 테이블에 VIEW가 있을 경우 별도로 삭제해야 한다.

(IDENTITY 속성 이용하기) 고려사항 : 테이블에 하나의 IDENTITY컬럼만이 허용된다. IDENTITY 컬럼은 NULL 값을 허용하지 않는다. IDENTITY 컬럼은 정수, 숫자 혹은 소수형 데이터 형식으로 사용되어야 한다. IDENT_SEED : 초기값 반환, IDENT_INCR : 증가분 세션 동안 마지막으로 삽입된 IDENTITY 값을 추축하기 위해 @@ IDENTITY 함수를 사용

uniqueidentifier 데이터 형식 사용하기) 예) CREAT TABLE class ( student_id int IDENTITY(100,5) NOT NULL, name VARCHAR(16) ) (NEWID 함수 및 uniqueidentifier 데이터 형식 사용하기) 전역적 유일한 식별자를 저장하기 위해 사용된다.

(컬럼 추가 및 삭제하기) 추가하기 예) ALTER TABLE sales ADD commission money NULL 삭제하기 예) DROP COLUMN customer_name  컬럼 변경하기 예) ALTER TABLE stores ALTER COLUMN store_id smallint

제5장 데이터 무결성 구현 무결성 : 유효한 데이터만이 데이터베이스에 저장될 수 있도록 지켜주는 것 무결성 : 유효한 데이터만이 데이터베이스에 저장될 수 있도록 지켜주는 것 - 무엇을 지켜주는가(종류)   설명 방법들 영역(domain) 특정한 범위의 값만이 와야한다. CHECK, DEFAULT 실체, 개체(entity) 행의 존재는 고유해야 한다. PRIMARY KEY, UNIQUE 참조(referential) 다른 속성과의 관계를 위반하지 않아야 한다. FOREIGN KEY

어떻게 지켜주는가(구현) 설명 방법들 절차적 방법   설명 방법들 절차적 방법 먼저 정의하고, 다시 바인딩하는 방법으로 절차를 거쳐 정의하고 사용한다. 재활용이 가능하다. CREATE INDEX, CREATE RULE, CREATE DEFAULT 서술적 방법 테이블을 서술할 때 함께 서술한다. 재활용이 불가능하지만, 문서화 하기에 매우 쉽고, 읽기 좋다. CHECK, DEFAULT, PRIMARY KEY, FOERIGN KEY

* 제약 사용하기 제약을 생성할 때 SQL Server는 시스템이 정하는 복잡한 이름을 붙이기 때문에, 제약에 대한 이름은 지정하는 것이 좋다. (수정시 용이) 컬럼 레벨 제약 – 컬럼 하나에 대한 제약 테이블 레벨 제약 – 다중 컬럼에 대해 제약

CREATE TABLE authors 컬럼 레벨 제약 예) ( au_id int NOT NULL CONSTRAINT au_PK PRIMARY KEY, firstname char(30) NOT NULL, lastname char(30) NOT NULL, status char(10) NOT NULL CONSTRAINT cat_CHK CHECK(staus IN (‘CONTRACT’,‘EMPLOYEE’)) )

테이블 레벨 제약 예) CREATE TABLE employee (emp_num int NOT NULL, lastname char(30) NOT NULL, firstname char(30) NOT NULL, employed datetime NOT NULL, terminated datetime NULL, CONSTRAINT emp_PK PRIMARY KEY(emp_num), CONSTRAINT date_CHK CHECK(employed < terminated))

(DEFAULT 제약) DEFAULT 제약은 INSERT문에 값이 지정되지 않았을 때 컬럼에 값을 삽입한다. 고려사항 : .IDENTITY속성을 가진 컬럼이나 데이터 유형이 timestamp인 컬럼에는 사용될 수 없다. .함수를 사용하여 디폴트 값을 제공할 수 있다.

(CHECK 제약) CHECK 제약은 하나 이상의 컬럼에 저장되는 데이터 값을 제한하는 일을 한다. 고려사항 : .INSERT나 UPDATE 문을 실행할 때마다 CHECK 제 약은 항상 데이터를 확인한다. .CHECK 제약은 같은 테이블에 있는 다른 컬럼들 을 참조할 수 있다. .CHECK 제약은IDENTITY속성을 가진 컬럼 또는 데이터 유형이 timestamp인 컬럼에는 사용될 수 없다. .CHECK 제약은 서브 쿼리를 포함할 수 없다. 

(PRIMARY KEY제약) PRIMARY KEY제약은 테이블에 있는 기본 키를 정의한다. 기본 키 값은 테이블에 있는 각각의 행을 유일하게 식별한다. 고려사항 : .테이블마다 오직 하나의 PRIMARY KEY제약이 정 의될 수 있다. .기본 키 값은 고유해야 한다. .NULL값은 허용되지 않는다. .클러스터 또는 비클러스터 인덱스가 생성될 지 를 지정할 수 있다.

(UNIQUE 제약) UNIQUE 제약은 한 컬럼 상의 두 개의 행이 동일한 값을 갖지 못하도록 지정한다. 고려사항 : .NULL값을 허용한다. .하나의 테이블에 여러 개의 UNIQUE 제약을 적용 할 수 없다. .테이블 상에서 기본 키를 제외한, 고유한 값들 로 이루어진 하나 이상의 컬럼에 적용할 수 있다. .UNIQUE 제약은 지정된 하나 또는 여러 개의 컬 럼 상에 고유한 인덱스를 생성함으로써 강화된다. .디폴트로 UNIQUE 제약은 클러스터 인덱스 사용 이 지정되어 있지 않는 한 유일한 비클러스터 인덱 스를 사용한다.

(FOREIGN KEY 제약) FOREIGN KEY 제약은 동일한 혹은 다른 테이블 상의 PRIMARY KEY제약이나 UNIQUE 제약이 적용된 컬럼들에 대한 참조를 정의한다. 고려사항 : .FOREIGN KEY 제약은 단일 또는 다중의 컬럼에 대해 참조 무결성을 제공한다. .FOREIGN KEY 제약문에 설정된 컬럼의 개수나 데이터 유형은 REFERENCES절에 있는 데이터 유형과 반드시 일치해야 한다. .FOREIGN KEY 제약은 PRIMARY KEY, UNIQUE제약 과는 달리 자동으로 인덱스를 생성하지 않는 다. 그렇지만 인덱스 작업에 유리하다.

FOREIGN KEY 제약이 정의되어 있는 테이블에서는, 데이터를 수정하려고 할 때, FOREIGN KEY 제약에 의해 참조되는 테이블에 대한 SELECT나 REFERENCES 권한을 가지고 있어야 한다. 동일한 테이블 상에서는 FOREIGN KEY절 없이 REFERENCES절만을 사용한다. (제약 중지 또는 기존 데이터 검사하지 않기) 기존의 데이터 검사 무시 : WITH NOCHECK 새로운 데이터 검사 무시 : NOCHECK 참고> 어떤 경우에도 중지나 연기 할 수 없는 것  기본 키, 디폴트, UNIQUE

* 디폴트 및 규칙 사용하기   디폴트 및 규칙은 하나 이상의 컬럼 또는 사용자 정의 데이터 유형에 한정되어서 이들은 한번 정의하고 반복적으로 쓸 수 있는 객체이다. 절차적 방법으로, 객체를 먼저 생성하고 바인드 시킬 수 있다. 디폴트 생성 예) CREATE DEFAULT phon_no_default AS ‘(000)000-0000’ GO EXEC sp_binddefault phone_no_defalut, customer.hone_no_col

규칙 생성 예) CREATE RULE statecode_rule AS @statecode in (‘IA’,’IL’,’KS’,’MO’) GO EXEC sp_bindrule statecode_rule, state_type 디폴트 삭제 예) DROP DEFAULT phon_no_default 규칙 삭제 예) DROP RULE statecode_rule

(무결성 강화 객체들을 사용할 때 고려할 사항) 부하 trigger Stored procedure Rule, Default 제약 자료형, null 사용자정의자료형, 기 능

제8장 다중 테이블 쿼리하기 다중 테이블로부터 데이터 결합하기 조인이란 두 개 이상의 테이블에 쿼리하여 각 테이블의 행과 컬럼을 통합하여 하나의 결과 세트를 만들어 내는 작업이다. 즉, 정규화로 나누어진 테이블 혹은 컬럼들을 다시 모아오는 것이다. ANSI 문법을 사용하도록 하자

(INNER JOIN) (EquiJOIN) 가장 일반적인 JOIN이다. 디폴트임 예) SELECT title, price, pub_name FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id   (EquiJOIN) 전체 컬럼을 가져와서 키가 되는 컬럼이 중복되게 하는 것

(CROSS JOIN) – Cartesian Product 양쪽 테이블의 모든 행에 대해 서로 연결되게 한다. 학술적인 의미외엔 의미없다.   (OUTER JOIN) INNER JOIN은 두 테이블에 있는 키 값이 일치하는 데이터만 가져오는 것에 비해 OUTER JOIN은 어느 한 쪽의 데이터를 모두 가져온다. 실무에서 빈번히 사용된다. 기준인 테이블을 지정해서 LEFT, RIGHT를 적어준다.

예) SELECT t.title_id, qty, title FROM titles t LEFT OUTER JOIN slaes s ON t.title_id = s.title_id (T-SQL 문법 FROM titles t, slaes s WHERE t.title_id *= s.title_id )

ANSI 문법과 T-SQL 문법의 차이  팔린 적이 없는 책만 보고싶다면…? SELECT t.title_id, qty, title FROM titles t LEFT OUTER JOIN sales s ON t.title_id = s.title_id WHERE qty IS NULL   FROM titles t ,sales s WHERE t.tile_id *= s.title_id AND qty IS NULL 

 결과는 전혀 다르게 나온다. 이유는 WHERE절의 조건이 JOIN 의 조건인지 WHERE의 조건인지 명확하지 않기 때문이다. 따라서 ANSI 문법을 사용해야 한다. (SELF JOIN) 자기 자신을 다시 조인하는 경우이다. SELECT * FROM titleauthor ORDER BY title_id, au_ord

작가가 두 명 이상인 책 목록을 보고 싶다고 할 경우, 다음과 같이 자신을 조인할 수 있다. SELECT t1.title_id, t1.au_id, t2.au_id FROM titleauthor t1, titleauthor t2  셀프 조인은 반드시 알리아스를 써야한다. WHERE t1.title_id = t2.title_id AND t1.au_id < t2.au_id ORDER BY t1.title_id