DB : SQLite (DDL, DML) 명령어

앞서 조회를 먼저 배웠는데, 이번에는 데이터 베이스를 만드는 것이 목표다. 조회하는 것보다 어려운 점은 없으니 걱정말자! 다만 SQL 명령어는 RDBMS 종류에 따라 다르니 꼭 확인하고 사용할 수 있는지 없는지 확인하자. 

---

DDL (Data Definition Language) : 데이터의 기본 구조 및 형식 변경

핵심 SQL 키워드

• CREATE
• DROP
• ALTER

저번 시간에 db 파일내 구성 요소를 그대로 가져왔다.

 

다시 한번 데이터 구성요소를 리마인드 하고 시작하자!

1. 전체 Data를 담고 있는 base
2. Data를 크게 분류한 Tables
3. Table 내부에 작게 분류한 Column(Field)
4. Column 별 세부 데이터

---

CREATE TABLE (테이블 생성)

데이터 생성 형식

 

순서 : 

• 데이터 베이스를 연결한다
• 만들 Table 명 설정
• 만들 Column(Field) 명 설정
• 데이터 타입 설정
• 각 데이터들의 제약 조건 설정
  

DATATYPE(데이터 타입)

• TEXT : 문자열
• INTEGER : 정수
• REAL : 부동 소수점
• BLOB : 이미지, 동영상 등 이진 데이터(바이너리 데이터)
• NULL : 값을 포함하지 않음

 

CONSTRAINTS(제약조건)

 

: 테이블 필드에 적용되는 규칙 또는 제한사항을 의미하며 데이터의 무결성을 유지하고 데이터 베이스의 일관성을 보장할 수 있다.

• PRIMARY KEY : 해당 필드를 기본 키(key)로 지정. INTEGER type에만 적용됨
• NOT NULL : 해당 필드에 NULL 값을 허용하지 않도록 지정
• FOREIGN KEY : 다른 테이블과의 외래 키 관계를 정의

 

AUTOINCREMENT(필드 자동 증가)

 

: 주로 INTEGER PRIMNARY KEY AUTOINCREMENT을 통해 primary key field에 적용

: 항상 새로운 레코드에 대해 이전 최대 값보다 큰 값을 할당

: 삭제된 값을 무시하고 재활용 하지 않음

 

브랜드 명(db)는 muzinzang으로 지었다!

 

위의 예시로 의류 종류인 상의 / 하의 / 신발로 나누어보았고, 그 내부 사항으로는 카테고리, 색깔, 사이즈 등등으로 나누었다. 내부 사항에 해당되는 형태를 명시해주고 값은 반드시 존재해야만 기입이 되게 만들었다.

SQLite에서는 Boolean 값을 허용하지 않기 때문에 Adult(성인용)을 기입할 때는 숫자 0 혹은 1로 기입한다.

모두 실행한 결과를 확인해보자!

Table과 Column(Field)가 모두 잘 작성된 것을 확인할 수 있다.

---

ALTER TABLE (테이블 및 필드 조작)

명령어	역할
ALTER TABLE ADD COLUMN	컬럼(필드) 추가
ALTER TABLE RENAME COLUMN	컬럼(필드) 이름 변경
ALTER TABLE RENAME TO	테이블 이름 변경
ALTER TABLE DROP COLUMN	컬럼(필드) 삭제 (* 상위 버전 필수)

 

조작 방식이 단순하기 때문에 하는 법은 사진으로 대체하겠다. A - > B 하는 수준이니!

 

 

◆ Column 추가

신발을 여성용 남성용을 나눠보기 위해 컬럼을 추가해보았다. db에도 잘 적용된 것을 확인할 수 있다

 

다만 테이블을 새로 추가 하는 것이기 때문에 기본값으로 설정한다. 추가할 컬럼만 기입한다면 기본적으로 NOT NULL 제약조건으로 생성되고 내부 DEFAULT 값은 'NULL'로 생성된다.

따라서 DEFAULT 를 통해 어떤 값으로 채워넣을지 정할 수 있다.

 

또한 여러가지 Column을 한번에 추가할 수 없는 주의사항이 있다.

 

 

◆ Column 이름 변경

 

방금 위에서 작성했던 column 명을 다른 이름으로 바꿔보자! Gender로 바꾸고 싶다면?

Sex - > Gender로 column명이 변경된 것을 확인할 수 있다

 

영어 A TO B 하는 느낌으로 작성만 해주면 간단하게 바꿔줄 수 있다.

 

 

◆ Table 이름 변경

 

테이블 명을 단수로 작성했던 'top'과 'bottom'을 복수형으로 고쳐보자

컬럼과 동일하게 쉽게 고쳐줄 수 있다. 

 

동일하게 A TO B 하는 느낌으로!

 

◆ Table, Column 삭제

 

Table 삭제는 매우 신중하게 해야한다. 내부 데이터가 전부 다 날아가고 살릴 수도 없다. 그러면서 가장 간단한 문법형태를 띄고 있는데, 

이거면 충분하다

실습파일은 사용해야하니.. 예시만 보여주고 넘어가겠다.

 

column 삭제 같은 경우는 SQLite 버전이 중요하다.  .sql 파일에서 select sqlite_version(); 을 작성하면 본인의 splite 버전을 확인할 수 있다.

나는 3.26.0 버전을 사용하고 있다.

3.26 버전에서는 DROP 에 대한 문법 오류가 뜬다

 

왜 그런지 열심히 검색해보니 위에 표에서 기입된 것처럼 버전의 문제다. SQLite에서는 3.35 버전 이상부터 업데이트 되어 DROP COLUMN이 가능하다.

업데이트 후 삭제 하면 결과는 !!!

삭제가 완료 되었다. 아래 데이터가 존재 해도 모두 삭제되니 유의해서 사용하자

---

DML (Data Manipulation Language) : 데이터 조작 (추가, 수정, 삭제)

핵심 SQL 키워드

• INSERT
• UPDATE
• DELETE

위에서 DDL을 통해 데이터의 기본 구조를 만들었다면 데이터를 직접 조작해보자.

INSERT (레코드 삽입)

위의 형식을 따르며, 어렵지 않으니 그대로 입력해보자.

데이터가 올바르게 삽입되었음을 확인할 수 있다.

 

하지만 일일이 다 넣을것인가? 당연히 여러개를 한번에 넣을 수 있다.

 

오른쪽 column들은 짤리긴 했지만, 모두데이터가 삽입된 것을 확인할 수 있다.

---

UPDATE(레코드 수정)

어딜가나 수정은 항상 어렵게 나오는것 같다..

• UPDATE로 'table'을 설정해준다
• SET를 통해 '변경할 column = 바꿀 값' 을 설정해준다
• 이때 WHERE을 통해 특정값을 골라서 바꾼다 (사용하지않는다면 해당 column 값 모두 변경되니 조심)  단일 데이터를 꺼낼때는 PRIMARY KEY에 해당하는 Id로 특정 가능하다.

초기에 넣었던 'tan' 에서 'grey'로 바뀐것을 확인할 수 있다.

한번에 많이 바꿀 수 있으니 잘 활용하자!

---

DELETE(레코드 삭제)

어딜가나 삭제가 가장 간단하다. 하지만 주의할점은 수정과 동일하다. WHERE을 사용하지 않는다면 모든 레코드가 삭제되는 경험을 할 수 있으니 유의하자.

수정에서 SET가 사라졌다. 그리고 그냥 DELETE가 아니라 FROM 이 추가되는 것만 기억하자. 그 외 수정하는 문구만 사라졌다고 보면된다

 

4번째 레코드가 삭제된 것을 확인할 수 있다.

혹시 이런상황에서 극단적으로 사이즈가 작은 순서대로 4개 데이터를 삭제한다고 할 때 어떡할까? 직접 id를 입력해야만 할까?

 

우리는 WHERE 을 사용할 수 있다는 것은 다른 문법도 사용 가능한지를 확인할 수 있다. SELECT에서 배웠던 방식중에 정렬과 LIMIT(OFFSET) 등을 사용하면 다양한 방식으로 데이터를 처리할 수 있다.

 

오래된 데이터 4개는 PRIMARY KEY가 AUTOINCREMENT되어있기 때문에 생성순서를 지킬것이다. 그래서 Size column을 정렬하여 낮은 순으로 삭제하면 가능하다.

삭제된 것을 확인할 수 있다.

 

사실 위에서 큰 실수를 했다. 왜 사이즈별로 삭제했는데 s가 살아있지? 했는데 ORDER BY를 사용할 때 정수 값이 아닌 문자열에 사용했기 때문에 문자열 중에 빠른 ' l ' 부터 상위 4개가 삭제되었다. 데이터를 되돌릴 수 없으니 그냥 무시하자!

---

JOIN 까지 정리하고 싶었는데, 따로 짧게 정리하는 것이 좋을거 같아 나눠서 작성하려한다. 사실 여태까지가 맛보기였다면 이런 데이터 테이블을 실질적으로 활용할 수 있는 유용한 이유? 를 보여주려한다. 놀랍게도 db에서 하던 것을 django에서 데이터 호출때 사용되면서 심화되니까 잘 쫓아 가보자.