DDL (Data Definition Language)
데이터베이스 구조를 정의하고 수정하는 언어
SQL 명령어 작성 시
- 대문자로 쓰는 이유? SQL 은 대문자, 소문자인지 신경쓰지 않음
- 다만 대문자 작성 시 가독성이 좋다는 사람, 그냥 소문자가 좋다는 사람들이 있음
- SQL 키워드는 대문자로, 테이블명과 열 이름은 소문자로 쓰는 컨벤션도 있음
- 쿼리를 끝낼 때 항상 끝에 세미콜론을 붙일 것 (여러개의 쿼리가 있을때 각 쿼리의 시작과 끝을 알 수 있음)
테이블
- 엑셀 시트 처럼 여러 종류의 테이블을 가질 수 있고 행과 열이 있음
SQLite 로 연습
테이블 생성
-- 테이블 이름 (괄호 안 : 열 이름)
CREATE TABLE users (
name,
email,
password,
nickname
); -- 세미콜론으로 마무리
CREATE TABLE 명령어로 users 에 대한 테이블 생성 후 run 을 하면 아래와 같이 테이블이 생성된다.
다른 데이터베이스의 경우 데이터 타입 지정이 필수적이나 SQLite 의 경우 위와 같이 간단하게 작성해서 생성할 수도 있다.
그러나 타입을 지정하는 것이 안전함
테이블 삭제
DROP TABLE users;
users 에 대한 전체 데이터베이스가 사라지고, 모든 데이터가 없어진다.
SQL 은 작성자가 무엇을 하고 있는지 작성자 스스로 잘 알고 있다고 생각하므로 명령어 사용 시 항상 조심할 것
(모든 명령어는 최종적)
테이블을 안전하게 삭제하기 위해 'DROP TABLE IF EXISTS' 구문을 사용할 수도 있음
Type
CREATE TABLE users (
name TEXT,
age INTEGER,
email TEXT,
password TEXT,
nickname TEXT,
is_manager INTEGER
-- SQLite 에서 boolean 타입 설정 시 true false 가 없으므로 INTEGER 0, 1 의 값으로 제한
-- profile BLOB : 이미지를 DB에 저장하는 것은 권장X, 저장 시 BLOB(binary large object) 을 사용(보통 이미지 경로 TEXT 저장 권장)
);
-- SQLite 는 많은 타입을 가지고 있진 않음(쉽고 간단한 몇가지 type 만 가짐)- 데이터베이스마다 서로 다른 데이터 타입(함수도 마찬가지)이 있고 더 구체적인 타입을 지정할 수도 있게 해준다.
- SQLite는 동적 타입 시스템을 사용하여 저장된 값에 따라 타입을 유연하게 처리한다.
constraint (제약 조건)
CREATE TABLE users (
name TEXT UNIQUE NOT NULL,
age INTEGER NOT NULL CHECK (age > 0),
email TEXT NOT NULL,
password TEXT NOT NULL CHECK (LENGTH(password) <= 12), -- CHECK와 SQLite 함수 LENGTH 결합해서 적용
nickname TEXT CHECK (LENGTH(nickname) BETWEEN 0 AND 8),
is_manager INTEGER NOT NULL DEFAULT 0 CHECK (is_manager BETWEEN 0 AND 1) -- 0 or 1
) STRICT;- UNIQUE : 고유한 값
- NOT NULL : 빈값 x
- CHECK : 검증
- DEFAULT : 기본값 설정
- STRICT : 테이블의 모든 열에 대해 명시적인 타입 선언을 요구함
테이블이 가지는 두 가지 유형의 기본키
Primary Key
- 테이블의 각 행을 고유하게 식별하는 열 또는 열의 조합
- 각 행은 해당 행을 고유하게 식별할 수 있게 해주는 최소 한 개의 열을 가져야 함
- 고유한 식별자는 불변이어야 함
Natural Primary Key (자연 기본키)
CREATE TABLE users (
name TEXT PRIMARY KEY UNIQUE NOT NULL, -- natural primary key
age INTEGER NOT NULL CHECK (age > 0),
email TEXT NOT NULL,
password TEXT NOT NULL CHECK (LENGTH(password) <= 12),
nickname TEXT CHECK (LENGTH(nickname) BETWEEN 0 AND 8),
is_manager INTEGER NOT NULL DEFAULT 0 CHECK (is_manager BETWEEN 0 AND 1)
) STRICT;- 테이블의 데이터 중 고유 식별자로 사용할 수 있는 열을 선택
- 데이터와 논리적 관계가 있어 의미를 가짐
- 추가적인 열이 필요 없어 저장 공간을 절약할 수 있으나 불변성을 유지하기 어려울 수 있음
Surrogate Primary Key (대체 기본키)
CREATE TABLE users (
user_id INTERGER NOT NULL PRIMARY KEY AUTOINCREMENT, -- surrogate primary key
name TEXT UNIQUE NOT NULL,
age INTEGER NOT NULL CHECK (age > 0),
email TEXT NOT NULL,
password TEXT NOT NULL CHECK (LENGTH(password) <= 12),
nickname TEXT CHECK (LENGTH(nickname) BETWEEN 0 AND 8),
is_manager INTEGER NOT NULL DEFAULT 0 CHECK (is_manager BETWEEN 0 AND 1)
) STRICT;- 테이블의 데이터와 관련이 없는 열을 생성하여 식별자로 사용
- 주로 자동 증가하는 정수 값을 사용
주의사항
- 자연 기본키의 불변성을 유지하는 것은 어렵기 때문에 보통 대체 기본키를 사용
- 기본키를 명시적으로 지정하지 않으면, 일부 DBMS는 자동으로 대체 키를 생성할 있으나 DBMS에 따라 다르므로 명시적으로 지정하는 것이 좋음
* SQLite는 AUTOINCREMENT 열이 있는 일반 테이블이 생성될 때마다 자동으로 sqlite_sequence 테이블을 생성한다.
- SQLite 는 AUTOINCREMENT 를 가진 열에 대해 가장 큰 열의 id 값을 추적하기 위해 sqlite_sequence 테이블을 사용함
- 해당 테이블을 통해 각 테이블의 최신 id 를 알고 적절히 업데이트 함
VIEW - 가상 테이블
- VIEW : 쿼리의 단축어 같은 것, 쿼리를 기억하기 쉬운 이름으로 저장해서 계속 사용(참조)할 수 있도록 해줌
- 복잡한 쿼리를 단순화하여 재사용할 수 있음
- 특정 열만 보이도록 제한하여 데이터 보안을 강화할 수 있음
-- view 생성
CREATE VIEW v_name AS
SELECT column1, column2, ... -- 저장할 쿼리
FROM table_name
WHERE condition;
-- view 사용
SELECT * FROM v_name;
-- view 제거
DROP VIEW v_name;
주의사항
- VIEW는 실제 데이터를 저장하지 않으므로, 기본 테이블 변경 시 VIEW 결과도 변경됨
- 복잡한 VIEW는 성능에 영향을 줄 수 있으므로 적절히 사용할 것
