SQLite - DB 생성과 변경

데이터베이스의 생성

CREATE DATABASE [DB이름]
SHOW TABLES
DROP DATABASE [DB이름]

• 비 SQLite와 SQLite 사이의 방법이 다르다.
• SQLite는 GUI 인터페이스를 조작해서 새로운 데이터베이스를 만들고, 패널에 불러오고, 연결하고, 연결을 끊는다.
• 비 SQL에서는 CREATE DATABASE [DB이름]으로 데이터베이스를 만든다.
• USE [DB이름] 으로 연결한다.
• SHOW TABLES 로 테이블 목록을 출력한다.
• DROP DATABASE [DB이름] 으로 데이터 베이스를 삭제한다.

테이블의 정의 및 생성

CREATE TABLE test (id INTEGER, name VARCHAR(255), address VARCHAR(255), city VARCHAR(255), state CHAR(2), zip CHAR(10));

• 테이블 생성시 COL들의 자료형을 명시할 수 있다.
• SQLite는 자료형이 큰 의미를 가지진 않는다.
• ex. CREATE TABLE test (id INTEGER, name VARCHAR(255), address VARCHAR(255), city VARCHAR(255), state CHAR(2), zip CHAR(10));
• cf. CHAR vs VARCHAR : CHAR는 자료형의 크기가 고정적이고, VARCHAR는 가변적이다. 그만큼 CHAR는 처리 속도가 빨라 길이가 고정적인 자료(ID, 주민등록번호 등)에 적합하다.
• 테이블 생성시 자료형 뒤에 UNIQUE / NOT NULL / DEFAULT 0 등의 옵션을 주어 제약을 둘 수도 있다.
• ex. b VARCHAR(255) UNIQUE NOT NULL
• AUTO_INCREMENT PRIMARY KEY 옵션을 주면 1씩 자동적으로 증가하는 정수 값을 넣어준다. (ID 부여 등에 활용)

테이블 다루기

ALTER TABLE [테이블이름] ADD COLUMN [칼럼 이름 및 자료형] --추가
ALTER TABLE [테이블이름] DROP COLUMN [칼럼 이름] --삭제
ALTER TABLE [테이블이름] MODIFY (컬럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],...) --수정
ALTER TABLE [테이블이름] RENAME TO [변경 이름] --이름변경
CREATE TABLE [새 테이블 이름] AS SELECT [가져올 COL] FROM [가져올 테이블] --테이블 생성 from 테이블

• 테이블 추가 : ALTER TABLE [테이블이름] ADD COLUMN [칼럼 이름 및 자료형]
• ex. ALTER TABLE test ADD COLUMN c VARCHAR(100);
• 테이블 삭제 : ALTER TABLE [테이블이름] DROP COLUMN [칼럼 이름]
• 테이블 수정 : ALTER TABLE [테이블이름] MODIFY (컬럼명1 데이터 유형 [DEFAULT 식] [NOT NULL],...)
• 수정은 SQLite에서는 불가능하다.
• 테이블 이름 변경 : ALTER TABLE [테이블이름] RENAME TO [변경 이름]
• 테이블 기반 테이블 생성 : CREATE TABLE [새 테이블 이름] AS SELECT [가져올 COL] FROM [가져올 테이블]

SQLite의 자료형

• Storage Class : SQLite의 기초 자료형. NULL, INTEGER, REAL, TEXT, BLOB 존재
• Affinity Class : SQLite의 유동적 class. TEXT, NUMERIC, INTEGER, REAL, NONE 존재
• DECIMAL(p,s) : 실수를 뜻하며, p는 전체 자릿수, s는 소수점 이하 자릿수이다. (=NUMERIC(p,s))
• boolean : BOOLEAN NOT NULL CHECK (aColumn IN (0,1)) 옵션을 주어 사용할 수 있다. 1과 0의 값만을 가진다.
• 날짜: 'YYYY-MM-DD' 문자열로 저장.
• 시간: 'HH:MM:SS' 문자열로 저장.

데이터베이스 관계 설정

FOREIGN KEY(대상 테이블의 FOREIGN KEY가 되어줄 COL) REFERENCES 대상 테이블(FOREIGN KEY가 되어줄 COL)

• 'FOREIGN KEY' 을 사용하여 테이블 사이 관계를 설정한다.
• 관계를 이룰 테이블의 FOREIGN KEY COL을 테이블의 COL 중 하나로 정한다.
• FOREIGN KEY(대상 테이블의 FOREIGN KEY가 되어줄 COL) REFERENCES 대상 테이블(FOREIGN KEY가 되어줄 COL)
• ex. FOREIGN KEY(FranchiseeID) REFERENCES Franchisee(FranchiseeID)
• 관계가 설정된 테이블은 먼저 삭제될 수 없다. (관계를 만든 테이블을 먼저 삭제해야 한다.)

데이터베이스 연결

SELECT [테이블별칭.조회할칼럼], [테이블별칭.조회할칼럼] FROM [기준테이블] AS [별칭] INNER JOIN [조인테이블] AS [별칭] ON [기준테이블별칭.기준키] = [조인테이블별칭.기준키]....

• 두 테이블을 연결하여 데이터를 검색하는 방법
• SELECT [테이블별칭.조회할칼럼], [테이블별칭.조회할칼럼] FROM [기준테이블] AS [별칭] INNER JOIN [조인테이블] AS [별칭] ON [기준테이블별칭.기준키] = [조인테이블별칭.기준키]....
• 기준키를 기준으로 테이블을 하나로 합쳐 데이터를 출력한다. 다만, 기준키가 공통적으로 존재하는 ROW만 출력되게 된다.
• JOIN의 방법은 다양하다. 다만 SQLite에서는 INNER JOIN 과 FULL LEFT JOIN만 지원한다.
• FULL LEFT JOIN은 왼쪽의 테이블은 다 살리고 오른쪽 테이블은 고통적으로 존재하는 ROW만 출력된다. (따라서 결측치 NULL이 새로 발생할 수 있다.)
• 비 SQLite에는 RIGHT JOIN, FULL OUTER JOIN 등 다양한 JOIN 명령어가 존재한다.
• ex. SELECT a.name, b.item_id, b.quantity, b.price FROM customer AS a INNER JOIN sale AS b ON a.id = b.customer_id;
• ex. SELECT a.name, b.item_id, b.quantity, b.price FROM customer AS a FULL LEFT JOIN sale AS b ON a.id = b.customer_id;

VIEW의 생성과 삭제

CREATE VIEW [뷰 이름] AS [SELECT 문을 활용한 READ]

• 출력결과를 VIEW의 형태로 저장해 둘 수 있다. 이러한 VIEW는 저장해두었다가 TABLE처럼 활용이 가능하다.
• CREATE VIEW [뷰 이름] AS [SELECT 문을 활용한 READ]
• ex. CREATE VIEW myView1 AS SELECT Name AS Country, Population / 1000 AS "Pop (1000s)" FROM Country;
• DROP VIEW [뷰 이름] 으로 삭제할 수 있다.