[SQL] SQLite

• 이 글은 SQLite를 기준으로 정리했습니다.

[1] Table 구조

• 엑셀이나 구글 스프레드 시트의 경우 sheet가 있다.
• 예를 들어 User / Product 시트는 각각의 행과 열로 이루어진 구조를 갖고 있다.
• 관계형 데이터 베이스에서는 이러한 sheet 구조를 동일하게 갖고 있다.
• 이러한 sheet 구조를 데이터 베이스에서는 table이라고 한다.
• 각 sheet의 행은 레코드 / 열은 레코드의 필드를 나타낸다.

[2] Table 생성 / 삭제해보기

• 주의점 쿼리문은 최종적이다.
• 최종적이라는 말은 생성 / 삭제는 그대로 데이터베이스에 적용됨으로 주의가 필요하다.
• 아래와 같은 구조를 갖는 데이터베이스를 생성한다고 가정

Table: User

username | email | password | address | age | 

• 아래와 같이 query문을 작성
• 다른 종류의 SQL 같은 경우 각각의 타입을 지정해 주거나 데이터에 대한 정보를 더 주어야 한다.

CREATE TABLE user (
  username,
  email,
  password,
  address,
  age
);

• 결과로 다음과 같이 테이블이 생성된다.
  
• 삭제는 다음과 같이 작성한다.

DROP TABLE user;

[3] 데이터를 넣어보기

[3.1] 기본형 (타입x)

• 데이터를 넣기위해 다음과 같이 쿼리를 작성해본다.

INSERT INTO user VALUES (
	'jimin',
  	'abc@google.com',
  	'abc1234',
  	'seoul',
  	26
);

• 데이터가 들어간 모습
• IDE에서 보이지 않는다면 새로고침을 해볼것
  
• 세미콜론이 아닌 ,와 다음 유저정보를 넣는다면 다량의 데이터 추가가 가능
• 위와 같은 방식의 단점
  - 항상 열의 순서를 알아야함
  - 모든 열의 값을 기입해야함
• 하나의 열 값만 추가하는 방법은 다음과 같다.
• 입력하지 않은 모든 값은 Null로 처리된다.

INSERT INTO user (username, age) VALUES (
  	'minjung',
  	25
);

• 하지만 아직 위의 방법은 여전히 값의 타입넣는 것을 실수한다면 값이 뒤죽박죽 되는 것을 막을수 없다.

[3.2] 타입을 포함하여 작성해보기

• 타입을 지정해주어 다음과 같이 테이블 생성을 작성해본다.

CREATE TABLE user (
  username TEXT,
  email TEXT,
  password TEXT,
  address TEXT,
  age INTEGER,
  height: REAL, -- 음수 값을 체크해야 한다.
  hasSubscription INTEGER, -- boolean 값을 0 이나 1 값으로 확인한다.
);

• 주석은 체크해야 하는 코드가 필요하다는 것을 뜻한다.

[3.3] 제약조건을 걸어주기

• 위의 조건은 NULL이 천국이 될 가능성이 크므로 제약조건을 걸어준다.

CREATE TABLE user (
  username TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL, -- DB에 해당 이메일이 있는지 확인한다.
  password TEXT NOT NULL,
  address TEXT,
  age INTEGER,
  height REAL, -- 음수 값을 체크해야 한다.
  hasSubscription INTEGER NOT NULL DEFAULT 0 -- 기본 값을 0 (false)로 설정한다.
);

• 설정 후 데이터 베이스의 구조는 다음과 같이 설정된다.
  
• 아래처럼 이메일을 중복시킨 코드를 넣는다면

INSERT INTO user (username, email, password) VALUES 
('minjung','abc@gmail.com',25),
('지민','abc@gmail.com',26);

• 사진처럼 에러가 발생한다.
  
• 또한 다음과 같이 키(height)값이 음수가 될 수 있다.
  
• 추가로 체크할 수 있는 제약 조건이 필요한 상황이다.
• 아래의 코드로 제약 조건을 추가한다.

CREATE TABLE user (
  username TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL, -- DB에 해당 이메일이 있는지 확인한다.
  password TEXT NOT NULL,
  address TEXT,
  age INTEGER CHECK (age BETWEEN 0 AND 100), -- 음수값과 나이제한을 걸어둔다.
  height REAL CHECK (height BETWEEN 10.0 AND 250), -- 음수 값과 최대 키값을 체크해야 한다.
  has_subscription INTEGER NOT NULL DEFAULT 0 CHECK (has_subscription BETWEEN 0 AND 1) -- 기본 값을 0 (false)로 설정한다.
);

[4] Primary Key 생성하기

• 기본 키는 데이터베이스 테이블의 각 행/레코드를 고유하게 식별하는 테이블의 필드
• 기본 키에는 고유한 값이 포함되어야 한다.
• 기본 키 열은 NULL 값을 가질 수 없다.
• 여러 필드가 기본 키로 사용되는 경우 이를 복합 키라고 한다.
• 불변성 (해당 값이 변경되지 않아야 함)
• 고유함 (해당 값은 유일해야 하며, 동일한 값을 가진 것이 존재하지 않아야 함)
• 종류는 자연 기본키

[4.1] 자연 기본키

• 테이블 데이터와 논리적인 관계가 있는 키
• 위의 경우 이메일로 다음과 같이 생성

CREATE TABLE user (
  username TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL PRIMARY KEY, -- DB에 해당 이메일이 있는지 확인한다.
  password TEXT NOT NULL,
  address TEXT,
  age INTEGER CHECK (age BETWEEN 0 AND 100), -- 음수값과 나이제한을 걸어둔다.
  height REAL CHECK (height BETWEEN 10.0 AND 250), -- 음수 값과 최대 키값을 체크해야 한다.
  has_subscription INTEGER NOT NULL DEFAULT 0 CHECK (has_subscription BETWEEN 0 AND 1) -- 기본 값을 0 (false)로 설정한다.
);

[4.2] 대체 기본키

• 테이블 데이터와 논리적인 관계가 없는 키
• user_id 값 생성
• 자연 기본키는 고유성 / 불변성을 유지하기 어렵기 때문에 대체 기본키를 주로 사용한다.

CREATE TABLE user (
  user_id INTEGER PRIMARY KEY AUTOINCREMENT, -- AUTOINCREMENT 사용시 항상 고유한 값을 갖을 수 있다.
  username TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL, -- DB에 해당 이메일이 있는지 확인한다.
  password TEXT NOT NULL,
  address TEXT,
  age INTEGER CHECK (age BETWEEN 0 AND 100), -- 음수값과 나이제한을 걸어둔다.
  height REAL CHECK (height BETWEEN 10.0 AND 250), -- 음수 값과 최대 키값을 체크해야 한다.
  has_subscription INTEGER NOT NULL DEFAULT 0 CHECK (has_subscription BETWEEN 0 AND 1) -- 기본 값을 0 (false)로 설정한다.
);