MSSQL DB - EX07

-- 10/8일
--DML : Data Manipulation Language
--      INSERT, SELECT, UPDATE, DELETE  (CRUD)
--DDL : Data Definition Language
--      CREATE(생성), ALTER(수정), DROP(삭제)

--CREATE : CREATE 객체종류 객체이름 [OPTIONS]
--DROP   : DROP   객체종류 객체이름
--1. DATABASE 생성
--CREATE DATABASE exampledb

--2. DATABASE 삭제
--DROP DATABASE exampledb

---------------------------------------------------

--example 데이터베이스 컨텍스트로 이동
USE exampledb

--3. TABLE 생성
--A
CREATE TABLE Person
(
 Number int,
 Name nvarchar (20),
 Phone varchar (13),
 email varchar (50),
 Relation nvarchar (10),
 RegDate Date
)

sp_help Person

INSERT INTO Person
VALUES
(
 1, '장동건', '123', 'skjd@sada.com', 'business', GETDATE()
)

SELECT * FROM Person

DROP TABLE Person

--B UBIQUE, PRIMARY KEY
CREATE TABLE Person
(
 Number int PRIMARY KEY,  -- 같은 값 중복 허용 안되게, NULL 안됨
 --Number int UNIQUE,     -- 같은 값 중복 허용 안되게, NULL 허용
 Name nvarchar (20),
 Phone varchar (13),
 email varchar (50),
 Relation nvarchar (10),
 RegDate Date
)

INSERT INTO Person
VALUES
(
 1, '장동건', '123', 'skjd@sada.com', 'business', GETDATE()
)

SELECT * FROM Person
DROP TABLE Person

--C. NULL, NOT NULL

CREATE TABLE Person
(
 Number int PRIMARY KEY,  -- 같은 값 중복 허용 안되게, NULL 안됨
 --Number int UNIQUE,     -- 같은 값 중복 허용 안되게, NULL 허용
 Name nvarchar (20) NOT NULL,
 Phone varchar (13) NOT NULL,
 email varchar (50) NULL,
 Relation nvarchar (10) NULL,
 RegDate Date NULL
)
--오류 : Name에 null을 삽입할 수 없음
INSERT INTO Person(Number, Phone,email, Relation, RegDate)

VALUES
(
 1, '123', 'skjd@sada.com', 'business' , GETDATE()
)

SELECT * FROM Person
DROP TABLE Person
 
 
--D. DEFAULT
CREATE TABLE Person
(
 Number int PRIMARY KEY,  -- 같은 값 중복 허용 안되게, NULL 안됨
 --Number int UNIQUE,     -- 같은 값 중복 허용 안되게, NULL 허용
 Name nvarchar (20) NOT NULL,
 Phone varchar (13) NOT NULL,
 email varchar (50) NULL,
 Relation nvarchar (10) NULL,
 RegDate Date NULL DEFAULT (GETDATE())
)
--regdate에 값을 지정하지 않으면 gotdate()가 사용됨
INSERT INTO Person(Number,Name, Phone,email, Relation)

VALUES
(
 1, '장동건', '123', 'skjd@sada.com', 'business'
)

SELECT * FROM Person

--E- CHECK 제약 조건

CREATE TABLE Person
(
 Number int NOT NULL PRIMARY KEY CHECK ( Number > 0),
 --Number int UNIQUE,     -- 같은 값 중복 허용 안되게, NULL 허용
 Name nvarchar (20) NOT NULL,
 Phone varchar (13) NOT NULL,
 email varchar (50) NULL,
 Relation nvarchar (10) NOT NULL
  CHECK(Relation IN ('familt' ,'friend', 'busindess')),
 RegDate Date NULL DEFAULT (GETDATE())
)
--CHECK 제약조건에 만족하는 데이터만 삽입가능
INSERT INTO Person(Number,Name, Phone,email, Relation)
VALUES
(
 1, '장동건', '123', 'skjd@sada.com', 'familt'
)

SELECT * FROM Person
DROP TABLE Person

-- 외래키  IDENTITY 자동증가컬럼  -- ex) IDENTITY(1,1) 1부터 1개씩 증가
CREATE TABLE Appointment
(
 Number int NOT NULL IDENTITY(1,1) PRIMARY KEY,
 PersonNumber int NOT NULL REFERENCES Person (Number),
 Title nvarchar (50) NOT NULL,
 Content nvarchar (200) NOT NULL,
 AppointmentDate DateTime NOT NULL,
 CONSTRAINT fk_person_appointment   --?
 FOREIGN KEY (personNumber) REFERENCES Person (Number)
)

INSERT INTO Appointment
(PersonNumber, Title, Content, AppointmentDate)
VALUES (1, '데이트', '역삼동 남자 친구와 논현동에서', '2010-10-08')
SELECT * FROM Appointment

DROP TABLE Appointment

--4. 테이블 수정
CREATE TABLE TableToModify
(
 column1 int,
 column2 int not null
)

INSERT INTO TableToModify
VALUES
(
 14, 20
 
 )

 

--A 컴럼 속성 변경
ALTER TABLE TableToModify
ALTER COLUMN column1 int not null

INSERT INTO TableToModify (column2) VALUES (10)

--B 제약 조건 추가
ALTER TABLE TableToModify
ADD CONSTRAINT pk_TableToModify PRIMARY KEY(column1)

ALTER TABLE TableToModify
ADD CONSTRAINT fk_Person_TableTOModify
FOREIGN KEY (column2) REFERENCES Person(Number)

--C. 컬럼 추가
ALTER TABLE TableToModify
ADD column3 nvarchar (100) NOT NULL

--D. 컬럼 삭제
ALTER TABLE TableToModify
DROP COLUMN column3

--E. 제약 조건 삭제
ALTER TABLE TableToModify
DROP CONSTRAINT fk_person_tabletomodify

SELECT * FROM TableToModify
Sp_help TableToModify
DROP TABLE TableToModify