[데이터베이스] SQLite 사용법

1. 테이블 생성

1.1 테이블 생성

 

CREATE TABLE IF NOT EXISTS 테이블명( 
                컬럼명1 데이터타입1 제약조건, 
                컬럼명2 데이터타입2 제약조건, 
                컬럼명3 데이터타입3 제약조건, 
                테이블_제약조건
        ) WITHOUT ROWID;

 

create table user (
    ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    Name TEXT NOT NULL,
    Age INTEGER
);

 

1.2 데이터 타입

 

데이터 타입	내용
NULL	Null 값.
INTEGER	1, 2, 3, 4, 6, 8 bytes 정수값.
REAL	8 bytes 부동소수점 값.
TEXT	UTF-8, UTF-16BE, UTF-16LE인코딩 문자열.
BLOB	입력된 그대로 저장 됨. (이미지, 비디오, 오디오 파일을 데이터베이스에 저장)

 

1.3 제약조건

 

 ① PRIMARY KEY : 그 컬럼이 기본키가 됨.

    기본키는 하나 이상의 컴럼의 조합으로 설정되고, 테이블에 하나만 존재하게 됨. 

    기본키는 드른 데이터와 중복 된 값을 포함할 수 없음.

 

  <1> PRIMARY KEY 기본 형태

 

CREATE TABLE 테이블명(
   컬럼명1 INTEGER NOT NULL PRIMARY KEY,
   ...
);

 

CREATE TABLE table(
   pk INTEGER PRIMARY KEY
);

 

 <2> 여러 컬럼을 조합하는 형태

 

CREATE TABLE 테이블명(
   컬럼명1 데이터타입1 NOT NULL,
   컬럼명2 데이터타입2 NOT NULL,
   ...
   PRIMARY KEY(컬럼명1, 컬럼명2,...)
);

 

CREATE TABLE languages (
   id INTEGER,
   name TEXT NOT NULL,
   PRIMARY KEY (id, name)
);

 

② NOT NULL : 반드시 데이터가 존재해야 하는 컬럼 제약 조건.

 

CREATE TABLE 테이블명 (
    ...,
    컬럼명1 데이터타입 NOT NULL,
    ...
);

 

CREATE TABLE table1(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

 

③  DEFAULT : 컬럼에 아무 값이 없을 경우 NULL 대신 기본값이 입력 되는 제약 조건.

 

CREATE TABLE 테이블명 (
    컬럼명1 데이터타입 DEFAULT 값, 
    ...
);

 

CREATE TABLE table1(
    id INTEGER PRIMARY KEY,
    name default 'no name'
);

 

 ④ UNIQUE : 특정 컬럼에 입력되는 데이터가 유일한 값이여야 하는 제약 조건.

 

  <1> 컬럼 제약조건

 

CREATE TABLE 테이블명(
    ...,
    컬럼명1 데이터타입1 UNIQUE,
    ...
);

 

CREATE TABLE user(
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT NOT NULL UNIQUE
);

 

  <2> 테이블 제약조건

 

CREATE TABLE 테이블명(
    컬럼명1 데이터타입1,
    컬럼명2 데이터타입2,
    ... 
    UNIQUE(컬럼명)
);

 

CREATE TABLE user(
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT NOT NULL
    UNIQUE(email )
);

 

 ⑤ CHECK : 특정 조건이 참인 경우에만 테이블의 레코드를 입력/수정이 가능하도록 해 주는 제약 조건.

 

  <1> 컬럼 제약 조건

 

CREATE TABLE 테이블명(
    ...,
    컬럼명1 데이터타입1 CHECK(조건식),
    ...
);

 

CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,  
    phone TEXT NOT NULL CHECK (length(phone) >= 10) 
);

 

  <2> 테이블 제약 조건

 

CREATE TABLE 테이블명(
    컬럼명1 데이터타입1,
    컬럼명2 데이터타입2,
    ... 
    CHECK(조건식)
);

 

CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,  
    CHECK(email like '%@%') 
);

 

2. 테이블 삭제

DROP TABLE 테이블명;

 

3. 데이터베이스 빈공간 정리

VACUUM;

 

4. 테이블명 변경

ALTER TABLE 테이블명 RENAME TO 새테이블명;

 

5. 컬럼 추가

ALTER TABLE 테이블명 ADD COLUMN 컬럼명 데이터타입; 

 

6. CRUD(Create, Read, Update, Delete)

6.1 데이터 추가

 

 ① 모든 컬럼에 값을 지정하여 데이터 추가

 

INSERT INTO 테이블명 VALUES (값1, 값2, ...);

 

INSERT INTO Avengers VALUES ('Iron Man', 'Suit');

 

 ② 컬럼이 지정하여 데이터 추가

 

INSERT INTO 테이블명 (컬럼1, 컬럼2, ...) VALUES (값1, 값2, ...);

 

INSERT INTO Avengers (id, name, weapon) 
    values (1, 'Iron Man', 'Suit')
    (2, 'Thor', 'Mjǫllnir');

 

 ③ 기본값을 데이터로 추가

 

INSERT INTO 테이블명 DEFAULT VALUES;

 

INSERT INTO Avengers default values;

 

6.2 데이터 조회

 

 ① 테이블의 레코드 참조

 

SELECT 컬럼명 FROM 테이블명;

 

SELECT * FROM Avengers;

 

 ② 조건식으로 데이터 검색

 

SELECT 컬럼명 FROM 테이블명 WHERE 조건식;

 

SELECT * FROM Avengers WHERE name = 'Iron Man';

 

 ③ 데이터 검색 후 정렬

 

SELECT 컬럼명 FROM 테이블명 ORDER BY 컬럼명;

 

SELECT * FROM Avengers ORDER BY name;

 

 ④ 검색 후 결과 값 제한

 

SELECT 컬럼명 FROM 테이블명 LIMIT 개수;

 

SELECT * FROM Avengers LIMIT 10;

 

 ⑤ 검색시 특정 단위 포함 조건

 

SELECT * FROM 테이블명 WHERE LIKE 조건식 ;

 

  '_' : 글자숫자를 정해줌(EX 컬럼명 LIKE '코__로') 
  '%' : 글자숫자를 정해주지않음(EX 컬럼명 LIKE '코%')

 

6.3 데이터 수정

  ① UPDATE 

 

UPDATE 테이블명 SET 컬럼명1 = 값1, 컬럼명2 = 값2, ... WHERE 조건식;

 

UPDATE Avengers SET weapon = 'Mjolnir' where name = 'Thor'; 

 

 ② REPLACE INTO

REPLACE INTO 테이블명(컬럼리스트) 
    VALUES(값리스트);

 

INSERT INTO Avengers (name, weapon)
    VALUES ('Captain America', 'Shield'),
    ('Rocky', 'Chitauri Scepter');

 

③ 문자 변환

REPLACE(대상컬럼, 변환 될 문자열, 변환 할 문자열)

 

SELECT year REPLACE(year,'2012','2019') from Avengers;

 

6.4 데이터 삭제

 

DELETE FROM 테이블명 WHERE 조건식;

 

delete from user where age < 47;

 

6.5 참고 사이트

 

SQLite Tutorial : https://www.sqlitetutorial.net/