MSSQL사용, 테이블과 뷰

 

 

 

 

 

-- 테이블 생성 : []는 생략 가능을 의미함

CREATE TABLE [스키마_명].테이블_이름

(

필드명1 데이터형 [속성],

필드명2 데이터형 [속성],

필드명3 데이터형 [속성]

);

 

 

 

-- 기본적인 틀 구성

--추가 옵션 적용 : NULL, NOT NULL (기본 값은 NULL)

CREATE DATABASE tableDB;

USE tableDB;

 

CREATE TABLE userTbl ---회원 테이블

(

userID char(8) NOT NULL, --사용자 아이디

name nvarchar(10) NOT NULL, --이름

birthYear int NOT NULL, --출생년도

addr nchar(2) NOT NULL, --지역(경기, 서울, 경남)

mobile1 char(3) NULL, --휴대전화 국번(011, 016......)

mobile2 char(8) NULL, --전화번호(하이픈 - 제외)

height smallint NULL, --키

mDate date NULL --회원가입일

);

 

 

CREATE TABLE buyTbl

(

num int NOT NULL, --순번(PK)

userid char(8) NOT NULL, --아이디(FK)

prodName nchar(6) NOT NULL, --물품명

groupName nchar(4) NULL, --분류

price int NOT NULL, --단가

amount smallint NOT NULL --수량

);

 

 

select * from userTbl;

select * from buyTbl;

 

 

 

-- IDENTITY 속성

-- IDENTITY(N1,N2) = N1 시작값, N2 증가값

 

CREATE TABLE buyTbl

(

num int IDENTITY(1,1) NOT NULL,

userid char(8) NOT NULL, 

prodName nchar(6) NOT NULL,

groupName nchar(4) NULL,

price int NOT NULL,

amount smallint NOT NULL 

);

 

 

-- 기본키설정 : 주키(PRIMARYKEY)

CREATE TABLE userTbl

(

userID char(8) NOT NULL PRIMARY KEY, 

name nvarchar(10) NOT NULL, 

birthYear int NOT NULL, 

addr nchar(2) NOT NULL,

mobile1 char(3) NULL, 

mobile2 char(8) NULL, 

height smallint NULL, 

mDate date NULL

);

 

위 과정을 통해 기본키 설정 후 데이터를 넣어보면 PRIMARY KEY 제약조건에 의해서 오류가 뜬다.

 

INSERT INTO userTbl VALUES('YH', N'후니', 2000, N'서울', '010', '12345678', 200, '2020-07-29');

INSERT INTO userTbl VALUES('YH', N'만세', 2000, N'경기', '010', '12345678', 200, '2020-07-29');

 

값을 변경하면 실행이 되는 것을 볼 수 있다.

 

 

-- 외래 키 설정 : (FOREIGN KEY)

CREATE TABLE buyTbl

(

num int IDENTITY(1,1) NOT NULL PRIMARY KEY, --주키 설정

userid char(8) NOT NULL FOREIGN KEY REFERENCES userTbl(userid), --외래키 설정

prodName nchar(6) NOT NULL,

groupName nchar(4) NULL,

price int NOT NULL, 

amount smallint NOT NULL 

);

 

 

외래 키 조건을 건 후 다이어그램을 생성해 보면 아래와 같이 연결되어 있는 모습을 확인할 수 있다.

 

 

 

/* 제약 조건 - 제약 조건(Constraint : 제한 조건이라고도 부름)이란 데이터의 무결성을 지키기 위해 제한된 조 건을 의미 한다. 즉, 특정 데이터를 입력할 때 무조건적으로 입력되는 것이 아니라 어떠한 조건을 만족했을 때에만 입력되도록 제약을 할 수 있는 것이다.   - SQL Server는 데이터의 무결성을 위해서 다음 6가지 제약 조건을 제공한다.   1. PRIMARY KEY 제약 조건  2. FOREIGN KEY 제약 조건  3. UNIQUE 제약 조건  4. CHECK 제약 조건  5. DEFAULT 정의  6. Null 값 허용 */

 

 

* 기본 키 제약 조건 - 테이블에 존재하는 많은 행의 데이터를 구분할 수 있는 식별자를 ‘기본 키(Primary Key)라고 부른다. - 기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다. - 기본 키가 없이도 테이블의 구성이 가능하지만 실무적으로는 모든 테이블에는 기본 키를 설정해 줘야 한다고 생각하면 무리가 없다. - 기본 키는 각 테이블 별로 하나만 존재해야 하지만, 기본 키를 하나의 열로만 구성해야 하는 것 은 아니다. 필요에 따라서 두 개 또는 그 이상의 열을 합쳐서 하나의 기본 키로 설정하는 경우도 종종 있다.

 

 

 

 

-- 여러개의 필드로 기본키 설정 하는 예제 

(prodcode와 prodID 필드를 합쳐서 기본키를 만들려고 하는 경우)

 

CREATE TABLE prodtbl

(

prodcode nchar(3) not null,

prodid nchar(4) not null,

prodDate date not null,

prodCur nchar(10) null,

CONSTRAINT PK_prodCode_prodID PRIMARY KEY (prodcode, prodid)

);

 

위에서 관계 이름을 지정해주었으므로 GUI방식으로 확인 시 지정 이름으로 되어있는 것을 볼 수 있다.

 

 

 

 

 

* 외래 키 제약 조건 - 외래 키(Foreign Key) 제약 조건은 두 테이블 사이의 관계를 선언함으로써, 데이터의 무결성을 보장해 주는 역할을 한다.   - 외래 키 관계를 설정하게 되면 하나의 테이블이 다른 테이블에 의존하게 된다.   - [외래 키를 정의하는 테이블-외래 키 테이블]과 [외래 키가 참조하는 테이블-기준 테이블]   - ‘외래 키 테이블’에 데이터를 입력할 때는 꼭 ‘기준 테이블’을 참조해서 입력하므로, ‘기준 테이블’ 에 이미 데이터가 존재해야만 한다.   - ‘외래 키 테이블’이 참조하는 ‘기준 테이블’의 열은 반드시 Primary Key이거나, Unique 제약 조건 이 설정되어 있어야 한다.

 

 

 

CREATE TABLE buyTbl

(

num int IDENTITY(1,1) NOT NULL PRIMARY KEY,

userid char(8) NOT NULL 

CONSTRAINT FK_usertbl_buytbl  -- 제약 조건. 이름을 FK_usertbl_buytbl로 하겠다.

FOREIGN KEY  -- 외래키 제약 조건을 설정

REFERENCES usertbl(userID),  -- 참조할 기준 테이블 usertbl 테이블의 userID 필드를 참조

prodName nchar(6) NOT NULL,

groupName nchar(4) NULL,

price int NOT NULL, 

amount smallint NOT NULL 

);

 

위 문장을 실행 후 확인해보면 제약조건은 지정 이름으로 생성되어 있고, 외래 키 제약조건은 자동으로 이름이 생성되어있다.

 

 

 

 

-- 제약(제한) 조건이 있을 때의 입력 순서

INSERT INTO buyTbl VALUES('KKY', N'노트북', N'전자', 1000, 1);

 

오류내용 

INSERT 문이 FOREIGN KEY 제약 조건 "FK_usertbl_buytbl"과(와) 충돌했습니다. 데이터베이스 "tableDB", 테이블 "dbo.userTbl", column 'userID'에서 충돌이 발생했습니다.

   ㄴ KKY라는 사람이 없으므로 buyTbl에 값이 추가가 되지 않는 것이다..

 

 

 

위 오류를 해결하기 위해 KKY의 데이터를 입력해주자.

 

 

값을 넣어준 후 이전의 문장을 다시 입력하면 실행이 된다. 들어가 있는 내용 또한 확인이 가능하다.

제약조건에 의해서 KKY의 ID로 추가 생성이 되지 않는다.

 

 

 

 

 

INSERT INTO userTbl

VALUES('KKY', N'가영', 2000, N'서울', '010', '12345678', 200, '2020-07-29');

INSERT INTO buyTbl VALUES('KKY', N'노트북', N'전자', 1000, 1);

 

INSERT INTO userTbl VALUES('KKY', N'가영', 2000, N'서울', '010', '12345678', 200, '2020-07-29');

오류내용

PRIMARY KEY 제약 조건 'PK__userTbl__CB9A1CDFD70DF69E'을(를) 위반했습니다. 개체 'dbo.userTbl'에 중복 키를 삽입할 수 없습니다. 중복 키 값은 (KKY     )입니다.

 

 

 

ID를 변경 후 추가하면 당연히 데이터가 들어가진다.

INSERT INTO userTbl

VALUES('HAN', N'하나', 2000, N'경기', '010', '12345678', 200, '2020-07-29');

INSERT INTO buyTbl VALUES('HAN', N'노트북', N'전자', 1000, 1);

 

SELECT * FROM userTbl;

SELECT * FROM buyTbl;

 

 

 

 

 

 

-- 제약(제한) 조건이 있을 때의 입력 순서

DELETE userTbl WHERE userID='KKY';

오류내용 

DELETE 문이 REFERENCE 제약 조건 "FK_usertbl_buytbl"과(와) 충돌했습니다. 데이터베이스 "tableDB", 테이블 "dbo.buyTbl", column 'userid'에서 충돌이 발생했습니다.

 

↓조건이 없어도 지워지지 않음(참조키 제약)

오류내용 위와 같음.

ㄴ buyTbl에 userID가 엮여 있으므로 구매 테이블의 값을 먼저 지워주어야 함.

 

 

 

 

 

BuyTbl – userTbl 순으로 데이터를 지우고 확인해보면 KKY의 데이터가 사라진 것을 확인할 수 있다.

DELETE buyTbl WHERE userid='KKY';

DELETE userTbl WHERE userID='KKY';

SELECT * FROM userTbl;

 

 

 

 

 

 

-- 기준 테이블 수정이 가능한가????

UPDATE userTbl set userid='HAN' WHERE userid='GHO';

 

 


↓기존의 userTbl 결과

 

UPDATE userTbl set userid='GHO' WHERE userid='HAN';

ㄴ 구매 테이블과 관계되어 있기 때문에 에러가 뜬다.

 

 

 

 

 

↓만약 구매 테이블에서 값을 지워준다면 수정이 가능하다.

DELETE buyTbl WHERE userid='HAN';

UPDATE userTbl set userid='GHO' WHERE userid='HAN';

 

 

 

 

 

-- 관계가 설정 되어 있는 경우에도 값의 변경이 필요 할 수 있음!!!

-- 기준 테이블의 data를 변경(UPDATE) 시 외래 키 테이블의 data에도 자동 적용 ON UPDATE CASCADE

 

INSERT INTO buyTbl VALUES('GHO', N'노트북', N'전자', 1000, 1);

SELECT * FROM userTbl;

SELECT * FROM buyTbl;

 

 

 

 

UPDATE userTbl SET userID='HAN' WHERE userID='GHO';

위 문장 입력 시 관계가 설정되어 있어 업데이트가 되지 않는다. 이걸 ON UPDATE CASCADE CASCADE를.

 

 

 

 

↓관계 삭제

ALTER TABLE buyTbl DROP CONSTRAINT FK_usertbl_buytbl; 

 

↓관계 수정

ALTER TABLE buyTbl

   ADD CONSTRAINT FK_usertbl_buytbl

   FOREIGN KEY (userid)

   REFERENCES userTbl(userid)

   ON UPDATE CASCADE;                      -- 기준 테이블 수정 시 참조 테이블에 자동 적용(수정)

 

 

 

↓변경 전의 테이블 데이터

 

↓변경 후의 테이블 데이터

UPDATE userTbl SET userID='HAN' WHERE userID='GHO';

↑위 제약조건의 ON UPDATE CASCADE가 있으므로, buyTbl의 userid도 함께 바뀐다.

 

 

 

 

 

 

 

-- 기준 테이블 삭제시 참조시 테이블에도 자동 삭제 : ON DELETE CASCADE

↓관계 수정

ALTER TABLE buyTbl

     ADD CONSTRAINT FK_usertbl_buytbl

     FOREIGN KEY (userid)

     REFERENCES userTbl(userid)

     ON UPDATE CASCADE             -- 기준 테이블 수정 시 참조 테이블에 자동 적용(수정)

     ON DELETE CASCADE;              -- 기준 테이블 삭제 시 참조 테이블에 자동 적용(삭제)

 

 

 

 

↓변경 전의 테이블 데이터

 

 

 

 

 

↓변경 후의 테이블 데이터

DELETE userTbl WHERE userid='HAN';

SELECT * FROM userTbl;

SELECT * FROM buyTbl;

↑위 제약조건의 ON DELETE CASCADE가 있으므로, buyTbl의 userid도 함께 삭제된다.

-- 옵션의 기본 값은 ON UPDATE NO ACTION 및 ON DELETE NO ACTION 이다.

 

 

 

 

 

 

 

 

 

* UNIQUE 제약 조건 - '중복되지 않는 유일한 값'을 입력해야 하는 조건   - PRIMARY KEY와 거의 비슷하며, 차이점은 UNIQUE가 NULL값을 허용한다는 점이다.   - (단 NULL)은 한 개만 허용된다.

 

CREATE TABLE userTbl

(

userID char(8) NOT NULL PRIMARY KEY, 

name nvarchar(10) NOT NULL, 

birthYear int NOT NULL, 

addr nchar(2) NOT NULL,

mobile1 char(3) NULL, 

mobile2 char(8) NULL, 

height smallint NULL, 

mDate date NULL,

email char(30) NULL

CONSTRAINT AK_email UNIQUE              -- 중복은 안되지만 빈칸으로 둘 수 있다.

);

 

 

INSERT INTO userTbl VALUES('HAN', N'하나', 2000, N'서울', '010', '12345678', 200, '2020-08-08', 'han@mail.com');

INSERT INTO userTbl(userID, name, birthYear, addr, mobile1, mobile2, height, mDate)

VALUES('DUL', N'두리', 2001, N'경기', '010', '12344321', 180, '2020-08-08');        -- email 생략

↑NULL도 데이터로 취급하므로 이후에 또다시EMAIL을 생략할 경우 그때는 에러가 발생한다.

 

오류내용

UNIQUE KEY 제약 조건 'AK_email'을(를) 위반했습니다. 개체 'dbo.userTbl'에 중복 키를 삽입할 수 없습니다. 중복 키 값은 (<NULL>)입니다.

 

 

 

 

 

 

* CHECK 제약 조건 - CHECK 제약 조건은 입력되는 데이터를 점검하는 기능을 할 수 있다. - 키에 –(마이너스) 값이 들어올 수 없도록 한다든지, 출생년도가 1900년 이후이고 현재 시점 이 전이어야 한다는 등의 조건을 지정.

 

 

-- 출생 년도가 1900년 이후 그리고 현재 연도 이하의 조건을 건다.

ALTER TABLE userTbl

           ADD CONSTRAINT CK_birthYear

           CHECK (birthYear >= 1900 AND birthYear <= YEAR(GETDATE()));

 

+++ GETDATE() : 현재 날짜와 시간을 출력함

 

 

 

 

 

 

INSERT INTO userTbl

VALUES('SET', N'세시', 2002, N'강원', '010', '11112222', 100, '2025-08-08', 'set@mail.com');  --입력됨

 

INSERT INTO userTbl VALUES('SET2', N'세시2', 2200, N'강원', '010', '11112222', 100, '2025-08-08', 'set2@mail.com');

INSERT INTO userTbl VALUES('SET3', N'세시3', 1300, N'강원', '010', '11112222', 100, '2025-08-08', 'set3@mail.com');

오류내용

INSERT 문이 CHECK 제약 조건 "CK_birthYear"과(와) 충돌했습니다. 데이터베이스 "tableDB", 테이블 "dbo.userTbl", column 'birthYear'에서 충돌이 발생했습니다.

 

ㄴ위에서의 제약조건을 걸었으므로 CK_birthYearCK_birthYear에난다.

 

 

 

 

 

 

 

-- 전화 번호 국번 체크 010, 011, 016, 017, 018, 019

ALTER TABLE userTbl

       ADD CONSTRAINT CK_mobile1

       CHECK (mobile1 IN('010', '011', '016', '017', '018', '019'));

 

 

INSERT INTO userTbl VALUES('SET4', N'세시4', 2020, N'강원', '012', '11112222', 100, '2025-08-08', 'set4@mail.com');

ㄴ위에서의 제약조건을 걸었으므로 CK_mobile1 CK_mobile1에 대한 오류가 난다.

 

 

 

INSERT INTO userTbl

VALUES('SET4', N'세시4', 2020, N'강원', '011', '11112222', 100, '2025-08-08', 'set4@mail.com'); -- 입력됨

SELECT * FROM userTbl;

 

 

 

 

 

 

-- 키는 0 초과여야 함

ALTER TABLE userTbl

       ADD CONSTRAINT CK_mobile1

       CHECK (height > 0);

INSERT INTO userTbl VALUES('SET3', N'세시3', 2010, N'서울', '011', '11112222', -50, '2025-08-08', 'set3@mail.com');

ㄴ 키의 데이터를 0보다 크게 입력하면 데이터가 들어가진다.

 

 

 

 

 

-- 전화번호 국번 체크, 사용하다가 없어진 경우 ex) 016

ALTER TABLE userTbl

ADD CONSTRAINT CK_mobile1

CHECK (mobile1 IN('010', '011', '016', '017', '018', '019')); --현재

 

 

 

↓아래와 같이 입력하면 실행이 되지 않는다. 이미 016의 데이터가 있기 때문이다.

ALTER TABLE userTbl

    ADD CONSTRAINT CK_mobile1

    CHECK (mobile1 IN('010', '011', '017', '018', '019'));

 

 

 

 

 

 

-- 새로 입력 받을 때만 제외, 이미 입력된 값이 제약 조건에 위배되는 경우 무시: WITH NOCHECK

↓기존의 제약조건 삭제

ALTER TABLE userTbl DROP CONSTRAINT CK_mobile1;

↓WITH NOCHECK를 이용하여 수정 후 실행

ALTER TABLE userTbl

    WITH NOCHECK

    ADD CONSTRAINT CK_mobile1

    CHECK (mobile1 IN('010', '011', '017', '018', '019'));

 

↓기존의 016은 남아있다.

 

↓하지만 새로 016으로 데이터를 입력하면 위의 제약조건으로 새로 추가는 불가능하다.

 

 

 

 

 

* DEFAULT 정의

- DEFAULT는 값을 입력하지 않았을 때, 자동으로 입력되는 디폴트 값을 정의하는 방법이다.

 

+++ 테이블 만들 때 자료형 고려사항 - NULL값 허용 - NULL을 저장 시 고정 컬럼(char, nchar)은 공간을 모두 차지하지만, 반면 가변 컬럼(varchar, nvarchar)은 공간을 차지 하지 않는다. - NULL값이 많은 경우에는 가변 크기의 데이터 형식을 고려하는게 유리하다.

 

 

 

 

-- 출생 년도 미 입력 시 현재 연도 입력, 주소 미 입력 시 '서울', 키 미 입력 시 '170'입력

CREATE TABLE userTbl

( userID char(8) NOT NULL PRIMARY KEY, 

name nvarchar(10) NOT NULL, 

birthYear int NOT NULL DEFAULT YEAR(GETDATE()), 

addr nchar(2) NOT NULL DEFAULT N'서울',

mobile1 char(3) NULL, 

mobile2 char(8) NULL, 

height smallint NULL DEFAULT 170, 

mDate date NULL );

 

INSERT INTO userTbl

VALUES ('HAN',N'하나,DEFAULT,DEFAULT, '011','12345678',DEFAULT,'2020.12.12');

SELECT * FROM userTbl;

ㄴ 연도, 지역, 키가 DEFAULT값으로 들어가 있다.

 

INSERT INTO userTbl(userID, name) VALUES('DUL',N'두나');

SELECT * FROM userTbl; 

 

 

 

 

 

 

 

 

-- 테이블 삭제

DROP TABLE 테이블_이름

-- 외래 키(FOREIGN KEY) 제약 조건의 '기준 테이블'은 삭제 안됨

-- 삭제 순서가 있음. (먼저 외래 키가 생성된 '외래 키 테이블'을 삭제 -> 기준 테이블 삭제)

 

 

-- 여러개의 테이블 동시 삭제

DROP TABLE 테이블1, 테이블2, 테이블3

 

 

-- 테이블 수정

ALTER TABLE

 

-- 필드 추가 (usertbl에 홈페이지 필드 추가)

ALTER TABLE userTbl

ADD homepage NVARCHAR(30)              -- 추가 할 필드의 이름, 데이터 형 지정

DEFAULT 'http://www.homepage.com'       -- 디폴트 정의

NULL; -- NULL허용

SELECT * FROM userTbl;

 

↓homepage 필드가 추가되어있다.

 

 

INSERT INTO userTbl(userID, name, mDate) VALUES('HGD', N'홍길동', '2020-07-29');

SELECT * FROM userTbl;

 

-- 열의 삭제 (제약 조건이 걸린 열을 삭제할 경우에는 제약 조건을 먼저 삭제한 후에 한다.)

ALTER TABLE usertbl DROP COLUMN mobile1;

SELECT * FROM userTbl;

 

 

 

 

 

-- 열의 이름을 변경하기 위해서는‘sp_rename' 시스템 저장 프로시저를 사용.

-- 가급적 열의 이름은 변경하지 않는 것이 바람직 함.

EXEC SP_RENAME 'userTbl.name', 'username', 'COLUMN';

SELECT * FROM userTbl;

 

-- 열의 제약 조건 삭제

ALTER TABLE userTbl DROP CONSTRAINT 제약조건이름;