SQLite 치트시트

SQLite - Lightweight Database Engine

SQLite는 작고, 빠르며, 자체 포함된, 높은 신뢰성, 풍부한 기능을 가진 SQL 데이터베이스 엔진을 구현하는 C 언어 라이브러리입니다. SQLite는 세계에서 가장 많이 사용되는 데이터베이스 엔진입니다.

📋 Copy All Commands 📄 Generate PDF[No text to translate] ```bash # Install SQLite sudo apt-get update sudo apt-get install sqlite3

Install development libraries

sudo apt-get install libsqlite3-dev

Check version

sqlite3 —version

   sudo apt-get update
   sudo apt-get install sqlite3
   ```
```bash
# Install SQLite
sudo yum install sqlite
# or
sudo dnf install sqlite

# Install development libraries
sudo yum install sqlite-devel
# or
sudo dnf install sqlite-devel
``````bash
   sudo yum install sqlite
   ```
```bash
# SQLite is pre-installed on macOS
sqlite3 --version

# Install via Homebrew for latest version
brew install sqlite
``````bash
   brew install sqlite
   ```
```bash
# Download from https://www.sqlite.org/download.html
# Extract sqlite3.exe to a directory in your PATH

# Or install via Chocolatey
choco install sqlite
``````bash
   # Download SQLite from official website
   # Add to system PATH
   ```
```bash
# Download source
wget https://www.sqlite.org/2023/sqlite-autoconf-3420000.tar.gz
tar -xzf sqlite-autoconf-3420000.tar.gz
cd sqlite-autoconf-3420000

# Compile and install
./configure
make
sudo make install
``````bash
   # Download source code
   ./configure
   make
   sudo make install
   ```
```bash
# Start SQLite with a database file
sqlite3 mydatabase.db

# Start SQLite in memory
sqlite3 :memory:

# Start SQLite with options
sqlite3 -header -column mydatabase.db

# Execute SQL from command line
sqlite3 mydatabase.db "SELECT * FROM users;"

# Execute SQL from file
sqlite3 mydatabase.db < script.sql
``````bash
   sqlite3
   ```
```sql
-- Show help
.help

-- Show databases
.databases

-- Show tables
.tables

-- Show schema for all tables
.schema

-- Show schema for specific table
.schema users

-- Show indexes
.indexes

-- Show current settings
.show

-- Set output mode
.mode column
.mode csv
.mode html
.mode json
.mode line
.mode list
.mode tabs

-- Set headers on/off
.headers on
.headers off

-- Set column width
.width 10 20 15

-- Import CSV file
.mode csv
.import data.csv users

-- Export to CSV
.mode csv
.output users.csv
SELECT * FROM users;
.output stdout

-- Execute shell command
.shell ls -la

-- Quit SQLite
.quit
.exit

.help     # 도움말 보기
.quit     # SQLite 종료
.tables   # 테이블 목록
.schema   # 스키마 보기
```

-- Create/open database (from command line)
sqlite3 mydatabase.db

-- Attach additional database
ATTACH DATABASE 'other.db' AS other;

-- Detach database
DETACH DATABASE other;

-- List attached databases
.databases

-- Backup database
.backup backup.db

-- Restore database
.restore backup.db
``````sql
    -- 새 데이터베이스 생성
    sqlite3 database.db
    ```
```sql
-- Get SQLite version
SELECT sqlite_version();

-- Get database file size
SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();

-- Get database statistics
PRAGMA database_list;
PRAGMA table_info(users);
PRAGMA index_list(users);
PRAGMA foreign_key_list(users);

-- Analyze database
ANALYZE;

-- Vacuum database (reclaim space)
VACUUM;

-- Incremental vacuum
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(1000);
``````sql
    .databases   # 연결된 데이터베이스 보기
    ```
```sql
-- Basic table creation
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Table with check constraint
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK(price > 0),
    category TEXT DEFAULT 'general'
);

-- Temporary table
CREATE TEMPORARY TABLE temp_data (
    id INTEGER,
    value TEXT
);

-- Create table from query
CREATE TABLE user_summary AS
SELECT 
    age,
    COUNT(*) as count,
    AVG(age) as avg_age
FROM users
GROUP BY age;
``````sql
    CREATE TABLE users (
        id INTEGER PRIMARY KEY,
        name TEXT,
        email TEXT
    );
    ```
```sql
-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Rename column (SQLite 3.25.0+)
ALTER TABLE users RENAME COLUMN name TO full_name;

-- Drop column (SQLite 3.35.0+)
ALTER TABLE users DROP COLUMN phone;

-- Drop table
DROP TABLE IF EXISTS temp_data;
``````sql
    ALTER TABLE users ADD COLUMN age INTEGER;
    ```
```sql
-- Show table structure
.schema users
PRAGMA table_info(users);

-- Show table statistics
SELECT 
    name,
    sql
FROM sqlite_master 
WHERE type = 'table' AND name = 'users';

-- Count rows in table
SELECT COUNT(*) FROM users;

-- Get table size
SELECT 
    name,
    COUNT(*) as row_count
FROM sqlite_master sm
JOIN pragma_table_info(sm.name) pti
WHERE sm.type = 'table'
GROUP BY sm.name;
``````sql
    .schema users   # 테이블 구조 보기
    ```
```sql
-- SQLite has dynamic typing with storage classes:
-- NULL, INTEGER, REAL, TEXT, BLOB

-- Type affinity examples
CREATE TABLE type_examples (
    id INTEGER PRIMARY KEY,           -- INTEGER affinity
    name TEXT,                        -- TEXT affinity
    price REAL,                       -- REAL affinity
    data BLOB,                        -- BLOB affinity
    flag BOOLEAN,                     -- NUMERIC affinity (stored as INTEGER)
    created_date DATE,                -- NUMERIC affinity
    created_time DATETIME,            -- NUMERIC affinity
    amount DECIMAL(10,2),             -- NUMERIC affinity
    description VARCHAR(255)          -- TEXT affinity
);

-- Date and time functions
INSERT INTO events VALUES (
    1,
    'Event 1',
    date('now'),                      -- Current date
    datetime('now'),                  -- Current datetime
    time('now'),                      -- Current time
    datetime('now', '+1 day'),        -- Tomorrow
    datetime('now', '-1 month'),      -- Last month
    strftime('%Y-%m-%d %H:%M:%S', 'now')  -- Formatted datetime
);

INTEGER  # 정수
TEXT     # 문자열
REAL     # 실수
BLOB     # 이진 데이터
```

-- Insert single row
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

-- Insert multiple rows
INSERT INTO users (name, email, age) VALUES
    ('Alice Smith', 'alice@example.com', 25),
    ('Bob Johnson', 'bob@example.com', 35),
    ('Carol Brown', 'carol@example.com', 28);

-- Insert with default values
INSERT INTO users (name, email) 
VALUES ('Default User', 'default@example.com');

-- Insert or replace
INSERT OR REPLACE INTO users (id, name, email, age)
VALUES (1, 'John Updated', 'john.new@example.com', 31);

-- Insert or ignore
INSERT OR IGNORE INTO users (name, email, age)
VALUES ('Duplicate', 'john@example.com', 30);

-- Insert from select
INSERT INTO user_backup 
SELECT * FROM users WHERE age > 25;
``````sql
    INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
    ```
```sql
-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with alias
SELECT name AS full_name, email AS email_address FROM users;

-- Select with conditions
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE name LIKE 'John%';
SELECT * FROM users WHERE email IS NOT NULL;

-- Select with multiple conditions
SELECT * FROM users 
WHERE age BETWEEN 25 AND 35 
AND email LIKE '%@example.com';

-- Select with ordering
SELECT * FROM users ORDER BY age DESC;
SELECT * FROM users ORDER BY name ASC, age DESC;

-- Select with limit
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Select distinct
SELECT DISTINCT age FROM users;

-- Select with aggregation
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MIN(age), MAX(age) FROM users;
SELECT age, COUNT(*) FROM users GROUP BY age;
SELECT age, COUNT(*) FROM users GROUP BY age HAVING COUNT(*) > 1;
``````sql
    SELECT * FROM users;
    SELECT name, email FROM users WHERE id = 1;
    ```
```sql
-- Update single row
UPDATE users 
SET age = 31 
WHERE id = 1;

-- Update multiple columns
UPDATE users 
SET name = 'John Smith', age = 32 
WHERE id = 1;

-- Update with conditions
UPDATE users 
SET age = age + 1 
WHERE age < 30;

-- Update with subquery
UPDATE users 
SET age = (SELECT AVG(age) FROM users) 
WHERE age IS NULL;

-- Update or insert (upsert)
INSERT INTO users (id, name, email, age) 
VALUES (1, 'John', 'john@example.com', 30)
ON CONFLICT(id) DO UPDATE SET
    name = excluded.name,
    email = excluded.email,
    age = excluded.age;
``````sql
    UPDATE users SET email = 'new@example.com' WHERE id = 1;
    ```
```sql
-- Delete specific rows
DELETE FROM users WHERE id = 1;

-- Delete with conditions
DELETE FROM users WHERE age < 18;

-- Delete all rows
DELETE FROM users;

-- Delete with subquery
DELETE FROM users 
WHERE id IN (SELECT id FROM users WHERE age > 65);
``````sql
    DELETE FROM users WHERE id = 1;
    ```

Would you like me to continue with the remaining sections?```sql
-- Inner join
SELECT u.name, o.amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Left join
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Cross join
SELECT u.name, p.name
FROM users u
CROSS JOIN products p;

-- Self join
SELECT u1.name, u2.name AS manager
FROM users u1
LEFT JOIN users u2 ON u1.manager_id = u2.id;

Subqueries

-- Subquery in WHERE clause
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- Subquery in SELECT clause
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
FROM users;

-- Correlated subquery
SELECT * FROM users u1
WHERE age > (SELECT AVG(age) FROM users u2 WHERE u2.department = u1.department);

-- EXISTS subquery
SELECT * FROM users
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);

Window Functions (SQLite 3.25.0+)

-- Row number
SELECT 
    name,
    age,
    ROW_NUMBER() OVER (ORDER BY age) AS row_num
FROM users;

-- Rank
SELECT 
    name,
    age,
    RANK() OVER (ORDER BY age DESC) AS rank
FROM users;

-- Dense rank
SELECT 
    name,
    age,
    DENSE_RANK() OVER (ORDER BY age DESC) AS dense_rank
FROM users;

-- Partition by
SELECT 
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

-- Running total
SELECT 
    name,
    amount,
    SUM(amount) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

Common Table Expressions (CTE)

-- Basic CTE
WITH young_users AS (
    SELECT * FROM users WHERE age < 30
)
SELECT * FROM young_users WHERE name LIKE 'A%';

-- Recursive CTE
WITH RECURSIVE fibonacci(n, fib_n, next_fib_n) AS (
    SELECT 1, 0, 1
    UNION ALL
    SELECT n+1, next_fib_n, fib_n + next_fib_n 
    FROM fibonacci 
    WHERE n < 10
)
SELECT n, fib_n FROM fibonacci;

-- Multiple CTEs
WITH 
high_value_orders AS (
    SELECT * FROM orders WHERE amount > 1000
),
premium_users AS (
    SELECT DISTINCT user_id FROM high_value_orders
)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN premium_users pu ON u.id = pu.user_id
JOIN high_value_orders o ON u.id = o.user_id
GROUP BY u.name;

Indexes

Creating Indexes

-- Simple index
CREATE INDEX idx_users_email ON users(email);

-- Composite index
CREATE INDEX idx_users_age_name ON users(age, name);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index
CREATE INDEX idx_active_users ON users(name) WHERE active = 1;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index
CREATE INDEX idx_users_covering ON users(age) INCLUDE (name, email);

Managing Indexes

-- List indexes
.indexes
SELECT name FROM sqlite_master WHERE type = 'index';

-- Show index info
PRAGMA index_info(idx_users_email);
PRAGMA index_list(users);

-- Drop index
DROP INDEX idx_users_email;

-- Analyze index usage
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'john@example.com';

-- Reindex
REINDEX;
REINDEX idx_users_email;

Views

Creating Views

-- Simple view
CREATE VIEW active_users AS
SELECT id, name, email 
FROM users 
WHERE active = 1;

-- Complex view with joins
CREATE VIEW user_order_summary AS
SELECT 
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Updatable view
CREATE VIEW young_users AS
SELECT id, name, email, age
FROM users
WHERE age < 30;

Managing Views

-- List views
SELECT name FROM sqlite_master WHERE type = 'view';

-- Show view definition
.schema active_users

-- Drop view
DROP VIEW active_users;

-- Update through view (if updatable)
UPDATE young_users SET age = 25 WHERE id = 1;

Triggers

Creating Triggers

-- Before insert trigger
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    UPDATE users SET created_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- After update trigger
CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, old_value, new_value, timestamp)
    VALUES (NEW.id, 'UPDATE', OLD.name, NEW.name, CURRENT_TIMESTAMP);
END;

-- Instead of trigger (for views)
CREATE TRIGGER instead_of_user_view_insert
INSTEAD OF INSERT ON user_view
FOR EACH ROW
BEGIN
    INSERT INTO users (name, email) VALUES (NEW.name, NEW.email);
END;

-- Conditional trigger
CREATE TRIGGER validate_email
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.email NOT LIKE '%@%'
BEGIN
    SELECT RAISE(ABORT, 'Invalid email format');
END;

Managing Triggers

-- List triggers
SELECT name FROM sqlite_master WHERE type = 'trigger';

-- Show trigger definition
.schema before_user_insert

-- Drop trigger
DROP TRIGGER before_user_insert;

Transactions

Basic Transactions

-- Begin transaction
BEGIN TRANSACTION;

-- Perform operations
INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');
UPDATE users SET age = 25 WHERE name = 'Test User';

-- Commit transaction
COMMIT;

-- Rollback transaction
ROLLBACK;

Transaction Types

-- Deferred transaction (default)
BEGIN DEFERRED TRANSACTION;

-- Immediate transaction
BEGIN IMMEDIATE TRANSACTION;

-- Exclusive transaction
BEGIN EXCLUSIVE TRANSACTION;

-- Savepoints
BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
SAVEPOINT sp1;
INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
ROLLBACK TO sp1;  -- Rollback to savepoint
COMMIT;

Transaction Control

-- Check if in transaction
SELECT sqlite_version(), 
       CASE WHEN sqlite_compileoption_used('THREADSAFE') 
            THEN 'threadsafe' 
            ELSE 'not threadsafe' 
       END;

-- Set transaction mode
PRAGMA journal_mode = WAL;  -- Write-Ahead Logging
PRAGMA synchronous = NORMAL;

Backup and Restore

Backup Methods

-- SQL dump backup
.output backup.sql
.dump
.output stdout

-- Specific table backup
.output users_backup.sql
.dump users
.output stdout

-- Binary backup (from shell)
sqlite3 mydatabase.db ".backup backup.db"

-- Online backup using VACUUM
VACUUM INTO 'backup.db';

Restore Methods

-- Restore from SQL dump
sqlite3 newdatabase.db < backup.sql

-- Restore from binary backup
sqlite3 newdatabase.db ".restore backup.db"

-- Copy database file (when not in use)
cp mydatabase.db backup.db

Incremental Backup

-- Enable WAL mode for incremental backup
PRAGMA journal_mode = WAL;

-- Backup WAL file
-- Copy both .db and .db-wal files

Performance Optimization

Query Optimization

-- Use EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN 
SELECT * FROM users WHERE email = 'john@example.com';

-- Create appropriate indexes
CREATE INDEX idx_users_email ON users(email);

-- Use ANALYZE to update statistics
ANALYZE;

-- Optimize queries with LIMIT
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Use covering indexes
CREATE INDEX idx_users_covering ON users(email) INCLUDE (name, age);

Database Configuration

-- Set page size (before creating database)
PRAGMA page_size = 4096;

-- Set cache size
PRAGMA cache_size = 10000;  -- 10000 pages

-- Set journal mode
PRAGMA journal_mode = WAL;  -- Write-Ahead Logging

-- Set synchronous mode
PRAGMA synchronous = NORMAL;  -- FULL, NORMAL, OFF

-- Set temp store
PRAGMA temp_store = MEMORY;  -- MEMORY, FILE

-- Set mmap size
PRAGMA mmap_size = 268435456;  -- 256MB

-- Auto vacuum
PRAGMA auto_vacuum = INCREMENTAL;

Bulk Operations

-- Disable auto-commit for bulk inserts
BEGIN TRANSACTION;
-- Insert many rows
INSERT INTO users (name, email) VALUES ('User1', 'user1@example.com');
INSERT INTO users (name, email) VALUES ('User2', 'user2@example.com');
-- ... many more inserts
COMMIT;

-- Use prepared statements (in application code)
-- Prepare once, execute many times

-- Bulk insert from CSV
.mode csv
.import large_file.csv users

SQLite Extensions

Loading Extensions

-- Enable extension loading
.load ./extension_name

-- Common extensions
.load ./fts5        -- Full-text search
.load ./rtree       -- R-tree spatial index
.load ./json1       -- JSON functions

JSON Functions (json1 extension)

-- JSON functions
SELECT json('{"name":"John","age":30}');
SELECT json_extract('{"name":"John","age":30}', '$.name');
SELECT json_array('a', 'b', 'c');
SELECT json_object('name', 'John', 'age', 30);

-- JSON table
CREATE TABLE users_json (
    id INTEGER PRIMARY KEY,
    data JSON
);

INSERT INTO users_json (data) VALUES 
('{"name":"John","age":30,"city":"New York"}'),
('{"name":"Jane","age":25,"city":"Boston"}');

SELECT 
    id,
    json_extract(data, '$.name') AS name,
    json_extract(data, '$.age') AS age
FROM users_json;

전체 텍스트 검색 (FTS5)

-- Create FTS table
CREATE VIRTUAL TABLE documents USING fts5(title, content);

-- Insert documents
INSERT INTO documents VALUES 
('SQLite Tutorial', 'Learn SQLite database management'),
('Python Guide', 'Python programming tutorial');

-- Search documents
SELECT * FROM documents WHERE documents MATCH 'sqlite';
SELECT * FROM documents WHERE documents MATCH 'tutorial OR guide';

-- Highlight matches
SELECT highlight(documents, 0, '<b>', '</b>') AS title
FROM documents WHERE documents MATCH 'sqlite';

프로그래밍 인터페이스

Python (sqlite3)

import sqlite3

# Connect to database
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# Execute query
cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
rows = cursor.fetchall()

# Insert data
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", 
               ("John Doe", "john@example.com"))

# Commit and close
conn.commit()
conn.close()

# Context manager
with sqlite3.connect('mydatabase.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    rows = cursor.fetchall()

Node.js (sqlite3)

const sqlite3 = require('sqlite3').verbose();

// Open database
const db = new sqlite3.Database('mydatabase.db');

// Run query
db.all("SELECT * FROM users WHERE age > ?", [25], (err, rows) => {
    if (err) {
        console.error(err);
    } else {
        console.log(rows);
    }
});

// Insert data
db.run("INSERT INTO users (name, email) VALUES (?, ?)", 
       ["John Doe", "john@example.com"], function(err) {
    if (err) {
        console.error(err);
    } else {
        console.log(`Row inserted with ID: ${this.lastID}`);
    }
});

// Close database
db.close();

C/C++

#include <sqlite3.h>
#include <stdio.h>

int main() {
    sqlite3 *db;
    char *err_msg = 0;
    
    // Open database
    int rc = sqlite3_open("mydatabase.db", &db);
    
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        return 1;
    }
    
    // Execute SQL
    char *sql = "SELECT * FROM users";
    rc = sqlite3_exec(db, sql, callback, 0, &err_msg);
    
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
    }
    
    // Close database
    sqlite3_close(db);
    return 0;
}

// Callback function
static int callback(void *data, int argc, char **argv, char **azColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("\n");
    return 0;
}

모범 사례

스키마 설계

-- Use appropriate data types
CREATE TABLE users (
    id INTEGER PRIMARY KEY,           -- Use INTEGER for auto-increment
    name TEXT NOT NULL,               -- Use TEXT for strings
    age INTEGER CHECK(age >= 0),      -- Use constraints
    email TEXT UNIQUE,                -- Use UNIQUE for unique values
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Use foreign keys
PRAGMA foreign_keys = ON;
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Normalize data appropriately
-- Don't over-normalize for SQLite (it's not a server database)

성능 모범 사례

-- Create indexes for frequently queried columns
CREATE INDEX idx_users_email ON users(email);

-- Use LIMIT for large result sets
SELECT * FROM users ORDER BY created_at DESC LIMIT 100;

-- Use transactions for bulk operations
BEGIN TRANSACTION;
-- Multiple INSERT/UPDATE/DELETE statements
COMMIT;

-- Use prepared statements to prevent SQL injection
-- (in application code)

-- Analyze database regularly
ANALYZE;

-- Vacuum database periodically
VACUUM;

보안 모범 사례

-- Use parameterized queries (in application code)
-- Never concatenate user input into SQL strings

-- Validate input data
CREATE TABLE users (
    email TEXT CHECK(email LIKE '%@%')
);

-- Use triggers for additional validation
CREATE TRIGGER validate_user_age
BEFORE INSERT ON users
FOR EACH ROW
WHEN NEW.age < 0 OR NEW.age > 150
BEGIN
    SELECT RAISE(ABORT, 'Invalid age');
END;

유지 관리 모범 사례

-- Regular maintenance tasks
PRAGMA integrity_check;  -- Check database integrity
ANALYZE;                 -- Update query planner statistics
VACUUM;                  -- Reclaim unused space

-- Monitor database size
SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size();

-- Backup regularly
.backup backup.db

-- Use WAL mode for better concurrency
PRAGMA journal_mode = WAL;

---

요약

SQLite는 전체 데이터베이스 서버의 오버헤드 없이 간단하고 신뢰할 수 있는 데이터베이스가 필요한 애플리케이션에 완벽한 가벼운 임베디드 데이터베이스 엔진입니다. 이 치트시트는 효과적인 데이터베이스 관리를 위한 필수 SQLite 명령어와 모범 사례를 다룹니다.

주요 장점:

• 가벼움: 서버 설정 불필요, 데이터베이스는 단일 파일
• ACID 호환: 롤백 기능이 있는 완전한 트랜잭션 지원
• 크로스 플랫폼: 거의 모든 운영 체제에서 작동
• 제로 구성: 설치 또는 관리 불필요
• 자체 포함: 외부 종속성 없음

최적의 사용 사례:

• 모바일 애플리케이션 (iOS, Android)
• 데스크톱 애플리케이션
• 소규모 및 중규모 웹사이트
• 프로토타이핑 및 개발
• 데이터 분석 및 보고
• 임베디드 시스템

중요 고려 사항:

• 고동시성 쓰기 시나리오에 부적합
• 단일 작성자, 다중 읽기 접근으로 제한
• 기본 제공 사용자 관리 또는 네트워크 접근 없음
• 데이터베이스 크기는 실질적으로 몇 테라바이트로 제한
• 일부 SQL 기능 미지원 (RIGHT JOIN, FULL OUTER JOIN 등)

이 치트시트의 사례와 기술을 따르면 성능과 데이터 무결성을 유지하면서 다양한 애플리케이션에 SQLite를 효과적으로 사용할 수 있습니다.