04 [Ubuntu] SQLite CRUD 3
순서
- SQLite3 Tool 실행, 종료, 도움말
- SQLite3 DB File 생성, 삭제
- SQLite3 DB Table 생성, 삭제
- SQLite3 DB Table Values 생성, 조회, 갱신, 삭제
Table 모델
아래와 같은 형태로 Data 모델을 사용하기 위해서, Table 을 생성한다고 이해하시면 됩니다.
파일 구조에서, field(필드)는 파일을 구성하는 요소 중에서 의미를 갖는 가장 작은 논리적 단위이다.
DDL
Data Definition Language
데이터 정의 언어
DDL은 테이블 등을 생성, 변경, 제거하는 데 사용한다
CREATE, ALTER, DROP, (TRUNCATE) 문이 있다.
Table 생성하고 삭제하기
Table 생성하기
CRUD를 실행하기 위해서는 테이블을 생성해야한다.
테이블 같은 경우 SQL은 CREATE 명령어를 사용한다.
위와 같이 실행했을 시 테이블 명이라는 테이블이 생성되고 속성값으로 컬럼들이 들어가게 된다.
CREATE TABLE <테이블 명> (컬럼명1 자료형, 컬럼명2 자료형, 컬럼명3 자료형, ...);
CREATE TABLE IF NOT EXISTS "tbl1"(
"No" INTEGER NOT NULL,
"Id" TEXT NOT NULL,
"Pw" TEXT NOT NULL,
"Age" INTEGER,
"Name" TEXT,
"Ph" TEXT,
"Birth" TEXT,
"Active" INTEGER NOT NULL,
PRIMARY KEY("No" AUTOINCREMENT)
);
생성된 Table 확인하기
생성된 Table의 schema 확인하기
schema 어원: 계획이나 도식(圖式), 설계도
schema: 데이터베이스의 아키텍처, 관계형 데이터베이스에서 데이터 구조 관계
테이블 이름, 필드, 데이터 유형, 그리고 이러한 엔티티 간의 관계 등 논리적 제약조건이 포함합니다.
.dump 파일로 내보내기
sqlite3 <Database Name>.db .dump > <name>.sql
Table 삭제하기
DB 용어에서 테이블과 데이터베이스 삭제는 DROP 이라는 용어를 사용한다.
DROP TABLE <TABLE NAME>;
DROP TABLE IF EXISTS <TABLE NAME>;
.dump 파일로 불러오기
sqlite3 <Database Name>.db < <name>.sql
Database 에 Table 여러개 만들기
학생 테이블 생성 예시
CREATE TABLE student (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
student_email TEXT NOT NULL,
class TEXT
);
일반적으로 CREATE TABLE 은 main 으로 연결된 DB 파일에 테이블을 생성하기 때문에, 연결된 DB 파일이 여러개라면, 테이블 이름만 지정하지 않고, 보다 명시적으로 아래와 같이 DB명.테이블 이름으로 지정한다.
CREATE TABLE test.student (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
student_email TEXT NOT NULL,
class TEXT
);
Table 수정
테이블명 수정
ALTER TABLE <테이블명> RENAME TO <새 테이블명>;
테이블에 컬럼 추가하기
ALTER TABLE <테이블명> ADD COLUMN <컬럼명> [데이터 타입];
이미 생성한 테이블에 컬럼을 추가합니다.
- PRIMARY KEY 또는 UNIQUE 제약 조건을 설정할 수 없다.
- DEFAULT 제약 조건을 설정할 때는 CURRENT_TIME / CURRENT_DATE / CURRENT_TIMESTAMP는 지정할 수 없다.
- NOT NULL 제약 조건을 설정할 때는 NULL이 아닌 기본값 설정이 필요하다.
테이블에 컬럼 삭제하기
SQLite에서는 DROP COLUMN 문을 지원하지 않는다.
new테이블 생성 > 기존 테이블 삭제 > new 테이블 이름을 기존 테이블 이름로 변경
제약조건
CREATE TABLE IF NOT EXISTS "tbl1"(
"No" INTEGER NOT NULL,
"Id" TEXT NOT NULL,
"Pw" TEXT NOT NULL,
"Age" INTEGER,
"Name" TEXT,
"Ph" TEXT,
"Birth" TEXT,
"Active" INTEGER NOT NULL,
PRIMARY KEY("No" AUTOINCREMENT)
);
PRIMARY KEY 제약 조건
CREATE TABLE 테이블명 (컬럼명 PRIMARY KEY, ...);CREATE TABLE 테이블명 (컬럼명 1, 컬럼명 2, ...,
PRIMARY KEY (컬럼명 1, 컬럼명 2, ...));
기본 키는 하나 이상의 컬럼의 조합으로 설정되고, 테이블에 하나만 존재한다.
기본 키가 설정된 컬럼에서는 다른 데이터와 중복 된 값을 포함할 수 없다.
NOT NULL 제약 조건
CREATE TABLE 테이블명 (컬럼명 NOT NULL, ...);
컬럼에 NOT NULL 제약 조건을 설정하면 해당 열에 NULL을 저장할 수 없다.
NULL을 저장하려고 하면 “Error: NOT NULL constraint failed: user.name"라는 오류 메시지가 표시된다.
UNIQUE 제약 조건
CREATE TABLE 테이블명 (컬럼명 UNIQUE, ...);CREATE TABLE 테이블명 (컬럼명1, 컬럼명2, ... , UNIQUE (컬럼명1, 컬럼명2, ...));
컬럼에 저장하는 값으로 이미 저장되어 있는 데이터의 값과 중복 값을 입력되지 않도록 하고 싶은 경우, 컬럼에 UNIQUE 제약 조건을 설정한다.
중복된 값이 저장되려고 하면, UNIQUE 제약 조건이 설정되어 있기 때문에 “Error: UNIQUE constraint failed: employee.no, employee.unit"라는 오류 메시지가 표시된다.
DEFAULT 제약 조건
CREATE TABLE 테이블명 (컬럼명 DEFAULT 값, ...);create table product (id integer, name text default 'no name', price integer default 0);
테이블에 데이터를 추가할 때, 값을 생략한 컬럼은 보통 NULL이 저장되지만 NULL 대신에 기본으로 저장되는 값을 설정할 경우에는 DEFAULT 제약 조건으로 설정한다.
날짜를 기본값으로 설정
| 지정값 | 형식 |
| CURRENT_TIME | HH : MM : SS |
| CURRENT_DATE | YYYY-MM-DD |
| CURRENT_TIMESTAMP | YYYY-MM-DD HH : MM : SS |
DEFAULT 제약 조건에 지정값형식을 설정하면, 날짜와 시간을 가져 디폴트 값으로 컬럼 저장 할 수 있다.
create table user (id integer, name text, date_time default CURRENT_TIMESTAMP);
결과는 아래와 같다.
sqlite> insert into user (id, name) values (1, 'devkuma');
sqlite> insert into user (id, name) values (3, 'kimkc');
sqlite>
sqlite> select * from user;
id name date_time
---------- ---------- -------------------
1 devkuma 2019-10-22 14:43:07
3 kimkc 2019-10-22 14:43:08
sqlite>
CHECK 제약 조건
CREATE TABLE 테이블명 (컬럼명 CHECK (조건식), ...);
create table user (id integer, name text, old integer check (old> 18));
데이터를 추가 할 때 값이 지정된 조건을 충족하는지 여부를 확인 할 수 있다.
CHECK 제약 조건 AND / OR 적용
create table user (id integer, name text,
old integer check (old > 18 and old < 30),
gender text check (gender = 'man' or gender = 'woman'));
CHECK 제약 조건으로 작성하는 조건식을 AND 또는 OR를 사용하여 더 복잡한 조건식을 작성할 수 있다.
CHECK 제약 조건의 조건식에 맞지 않는 데이터를 저장하려고 하면 “Error: CHECK constraint failed: user"라는 오류 메시지가 표시된다.