03-6 테이블 생성하고 데이터 조작하기

 

데이터 조작언어 (DML)은 테이블에 데이터를 검색(SELECT), 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하는데 사용한다. 즉, DML의 대상은 테이블이므로 DML을 사용하려면 반드시 테이블이 있어야 한다. 바로 그 테이블을 조작하는 언어를 데이터 정의 언어 (DDL)이라고 한다. DDL은 데이터 베이스, 테이블, 뷰 , 인덱스 등의 개체를 생성 (CREATE), 삭제(DROP), 변경(ALTER)한다.

 

• 데이터 베이스 생성하고 삭제하기

데이터베이스를 생성하는 CREATE 문의 기본 형식
CREATE DATABASE [데이터베이스 이름]

-- CREATE문으로 데이터베이스 생성 : TestDB 
CREATE DATABASE TestDB

-- DROP문으로 데이터베이스 삭제 
DROP DATABASE TestDB

-- 테이블 생성하고 삭제하기

1. DoItSQL 데이터베이스 선택 --> 어떤 데이터베이스를 쓸건지
USE DoItSQL;

 

---

2. CREATE 문으로 테이블 생성하기 
-- 테이블을 생성하는 CREATE문의 기본 형식
CREATE TABLE 테이블 이름(
열1 자료형,
열2 자료형,
(...생략...)
)

-- CREATE문으로 테이블 생성
CREATE TABLE doit_create_table(
col_1 INT,
col_2 NVARCHAR(50),
col_3 DATETIME
)

-- DROP 문으로 테이블 삭제하기 
DROP TALBE doit_create_table
(현재 삭제하려는 테이블이 다른 테이블과 종속관계이면서 부모테이블 인 경우 삭제에 실패)

 

---

3. 테이블에 데이터 삽입, 수정, 삭제하기
-- INSERT 문으로 데이터 삽입하기 
INSERT INTO 테이블 [열1, 열2, ...] VALUES [값1,값2, ...]

-- 테이블을 생성하고 각 열에 데이터 입력
USE DoItSQL;
CREATE TABLE doit_dml(
col_1 INT,
col_2 NVARCHAR(50),
col_3 DATETIME
)

GO

INSERT INTO doit_dml (col_1,col_2, col_3) VALUES (1, 'DoItSQL', '2021-01-01')

1) INSERT 문에서는 특이사항 몇가지가 있다. 
 테이블에 데이터를 삽입할 때 열 이름을 생략할 수 있다. 하지만 열 이름을 생략하려면 VALUES 문 뒤에 
테이블의 열 순서와 개수에 맞춰 데이터를 채워야 한다. 

INSERT INTO doit_dml (col_1,col_2, col_3) VALUES (1, 'DoItSQL', '2021-01-01')

-- 열 이름을 생략하고 테이블에 데이터 삽입 
INSERT INTO doit_dml VALUES (2, N'열 이름 생략', '2021-01-02')


-- col_1, col_2 열에만 데이터를 삽입하려면 어떻게 해야 하는가 ?
INSERT INTO doit_dml(col_1,col_2) VALUES (3, N'col_3 값 생략')

-- 삽입하려는 데이터의 순서변경
INSERT INTO doit_dml(col_1, col_3, col_2) VALUES (4, '2021-01-03', N'열 순서 변경')

-- 여러 데이터를 한번에 삽입 
INSERT INTO doit_dml(col_1, col_2, col_3) 
VALUES (5, N'데이터 입력5','2021-01-03'), (6, N'데이터 입력6', '2021-01-03'),
(7, N'데이터 입력7', '2021-01-03')

!!! 데이터를 삽입할 때 자료형과 맞지 않으면 오류가 발생한다 !!!

2) UPDATE 문의 기본형식 
UPDATE [테이블 이름] SET [열1 = 값1, 열2 = 값2,...] WHERE [열] = [조건]

-- UPDATE문으로 특정 열 데이터 수정하기 : WHERE문 사용해서 
UPDATE doit_dml SET col_2 = N'데이터 수정' WHERE col_1 = 4

-- UPDATE문으로 전체 열 데이터 수정하기 : WHERE 문 생략 
UPDATE doit_dml SET col_1 = col_1 + 10

3) DELETE 문의 데이터 삭제하기 
DELETE [테이블 이름] WHERE [열] = [조건]

!! DELETE 문도 UPDATE 문과 마찬가지로 WHERE 문의 조건이 누락되면 
전체 데이터를 삭제하므로 사용할 때 주의 !!

-- DELETE 문으로 특정 열 데이터 삭제하기 : WHERE문 사용
DELETE doit_dml WHERE col_1 = 14

--  DELETE 문으로 전체 열 데이터 삭제하기 : WHERE문 생략
DELETE doit_dml

-- 테이블 삭제 
DROP TABLE doit_dml

 

---

 

4) 외래키로 연결된 데이터 입력, 삭제하기 
관계형 데이터 베이스는 데이터의 무결성을 유지해야 하므로 부모 테이블에 없는 데이터를 자식 테이블이
가지면 안되는 것이 원칙이다. 이 원칙을 유지하면 데이터가 잘못 입력되거나 삭제 되는것을 방지할 수 있다.

-- 부모,자식 테이블 생성
CREATE TABLE doit_parent(col_1 INT PRIMARY KEY);
CREATE TABLE doit_child(col_1 INT);

ALTER TABLE doit_child
ADD FOREIGN KEY(col_1) REFERENCES doit_parent(col_1);

!! 부모테이블에 데이터를 먼저 입력해야 해결할 수 있다. 부모테이블에 데이터를 입력하고 같은 데이터를 
자식 테이블에 입력하면 정상적으로 데이터를 입력 할 수 있다 !!

-- 부모 테이블에 데이터 입력한뒤 , 자식 테이블에 같은 데이터 입력
INSERT INTO doit_parent VALUES (1)
INSERT INTO doit_child VALUES (1)

SELECT * FROM doit_parent
SELECT * FROM doit_child

-- 자식 테이블에서 데이터 삭제한뒤, 부모 테이블의 데이터 삭제 
!! 자식테이블의 데이터를 먼저 삭제하고 부모 테이블의 데이터를 삭제한다. 그래야 정상적으로 삭제가능!!

DELETE doit_child WHERE col_1 = 1
DELETE doit_parent WHERE col_1 = 1

-- 자식 테이블 삭제한 뒤 부모 테이블 삭제 
DROP TABLE doit_child
DROP TABLE doit_parent

자식테이블의 데이터는 유지하면서 부모 테이블을 삭제하고 싶다면 제약 조건을 제거해 테이블 삭제를 진행하는
방법도 있다. 제약조건을 삭제하는 명령어는 DROP CONSTRAINT를 사용하는데 이때 제약조건의 이름을
명시해야한다. 제약조건의 이름은 각자 다를 수 있으므로 자신의 제약조건의 이름을 확인해 입력한다. 

-- 제약조건을 제거한 뒤 부모테이블 삭제
-> 제약조건을 가진 테이블 다시 생성
CREATE TABLE doit_parent (col_1 INT PRIMARY KEY);
CREATE TABLE doit_child (col_1 INT);

ALTER TABLE doit_child
ADD FOREIGN KEY (col_1) REFERENCES doit_parent(col_1);

-> 제약조건을 제거한 뒤 부모테이블 삭제 
ALTER TABLE doit_child 
DROP CONSTRAINT FK_doit_chil__col_1_44FF419A;

DROP TABLE doit_parent;

제약 조건과 제약 조건이름을 확인하고 싶다면 sp_help [테이블 이름]을 입력하거나 또는 테이블 이름을
드래그한 상태에서 Alt + F1 을 눌러 실행하면 확인할 수 있다. 

 

4. 다른 테이블에 검색 결과 입력하기 

-- 검색결과를 다른 테이블에 입력하고 싶을 때 !
INSERT .. SELECT문의 기본형식

INSERT 대상 테이블 SELECT 열 FROM 기존 테이블
INSERT doit_stock SELECT * FROM stock WHERE symbol = 'MSFT' and date >= '2021-01-01';

5. 새 테이블을 생성하며 검색결과 입력하기
테이블의 데이터를 복사할 때 자주 사용하는 방법 ! SELECT 문으로 검색한 데이터를 입력할 때 사용자가
정의한 테이블 이름으로 테이블을 자동으로 생성하고 데이터를 입력한다. 
SELECT ... INTO 기본형식

SELECT 열 INTO 새로운 테이블 FROM 기존 테이블
SELECT * INTO doit_stock2 FROM stock WHERE symbol = 'MSFT' and date >= '2021-01-01';