ソース情報
- リポジトリ
- tomevault-io/tomes
- ソースの最終更新活動
- 2026年7月23日 21:48
- 検出された SKILL.md の言語
- 英語
- スター
- 1
- フォーク
- 0
インストール方法
デフォルトでは、最初にソースを確認する Prompt が選択されています。直接コマンドに切り替えるか、ローカルコピーをダウンロードすることもできます。
ソースファイルを確認
インストールを決める前に、SKILL.md と SkillsMP に表示されている付属ファイルをお読みください。
SKILL.md を表示中
SKILL.md
ソースの指示 · 読み取り専用プレビュー- name
- database
- description
- > Use when this capability is needed.
# Database CLI Operations
## Quick Reference
| Action | SQLite | PostgreSQL | MySQL |
|--------|--------|------------|-------|
| Connect | `sqlite3 db.sqlite` | `psql -U user -d dbname` | `mysql -u user -p dbname` |
| List databases | `.databases` | `\l` | `SHOW DATABASES;` |
| List tables | `.tables` | `\dt` | `SHOW TABLES;` |
| Describe table | `.schema tablename` | `\d tablename` | `DESCRIBE tablename;` |
| Quit | `.quit` | `\q` | `\q` or `exit` |
| Run file | `.read file.sql` | `\i file.sql` | `source file.sql` |
## SQLite (sqlite3)
### Connection and Configuration
```bash
# Open or create a database
sqlite3 mydb.sqlite
# Open read-only
sqlite3 -readonly mydb.sqlite
# Execute SQL directly (non-interactive)
sqlite3 mydb.sqlite "SELECT * FROM users;"
# Execute SQL from file
sqlite3 mydb.sqlite < queries.sql
sqlite3 mydb.sqlite ".read queries.sql"
# Output modes
sqlite3 mydb.sqlite -header -column "SELECT * FROM users;"
sqlite3 mydb.sqlite -json "SELECT * FROM users;"
sqlite3 mydb.sqlite -csv "SELECT * FROM users;"
sqlite3 mydb.sqlite -markdown "SELECT * FROM users;"
```
### Dot Commands
```bash
.help # List all dot commands
.tables # List all tables
.tables %user% # List tables matching pattern
.schema # Show CREATE statements for all tables
.schema users # Show CREATE statement for specific table
.indexes # List all indexes
.indexes users # List indexes for specific table
.headers on # Show column headers
.mode column # Columnar output (also: csv, json, markdown, table, line)
.width 20 10 30 # Set column widths
.timer on # Show query execution time
.dbinfo # Show database metadata
.dump # Dump entire database as SQL
.dump users # Dump specific table
.import file.csv users # Import CSV into table
.output result.txt # Redirect output to file
.output stdout # Reset output to terminal
.changes on # Show number of rows changed
.eqp on # Show query plan automatically
```
### Schema Operations
```sql
-- Create table
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at TEXT DEFAULT (datetime('now'))
);
-- Add column
ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user';
-- Rename table
ALTER TABLE users RENAME TO app_users;
-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_name ON users(name);
-- Drop index
DROP INDEX idx_users_email;
-- Analyze (update query planner statistics)
ANALYZE;
```
### CRUD Operations
```sql
-- Insert
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');
-- Select
SELECT * FROM users WHERE role = 'admin' ORDER BY name LIMIT 10;
SELECT name, COUNT(*) as cnt FROM orders GROUP BY name HAVING cnt > 5;
-- Update
UPDATE users SET role = 'admin' WHERE email = 'alice@example.com';
-- Delete
DELETE FROM users WHERE created_at < datetime('now', '-1 year');
-- Upsert (insert or replace)
INSERT OR REPLACE INTO users (id, name, email) VALUES (1, 'Alice', 'alice@new.com');
INSERT INTO users (name, email) VALUES ('Alice', 'alice@new.com')
ON CONFLICT(email) DO UPDATE SET name = excluded.name;
```
### Import and Export
```bash
# Export to CSV
sqlite3 -header -csv mydb.sqlite "SELECT * FROM users;" > users.csv
# Export to JSON
sqlite3 -json mydb.sqlite "SELECT * FROM users;" > users.json
# Import CSV
sqlite3 mydb.sqlite <<'EOF'
.mode csv
.import users.csv users
EOF
# Backup (SQL dump)
sqlite3 mydb.sqlite .dump > backup.sql
# Restore from dump
sqlite3 newdb.sqlite < backup.sql
# Binary backup (online, safe while database is in use)
sqlite3 mydb.sqlite ".backup backup.sqlite"
```
### Query Analysis
```sql
-- Query plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'alice@example.com';
-- Full explain
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Integrity check
PRAGMA integrity_check;
-- Database size info
PRAGMA page_count;
PRAGMA page_size;
-- Table info
PRAGMA table_info(users);
-- Foreign key check
PRAGMA foreign_key_check;
-- WAL mode (recommended for concurrent access)
PRAGMA journal_mode=WAL;
-- Optimize after bulk operations
VACUUM;
```
## PostgreSQL (psql)
### Connection
```bash
# Connect to local database
psql -U postgres -d mydb
# Connect with host and port
psql -h localhost -p 5432 -U myuser -d mydb
# Connection string (URI)
psql "postgresql://user:password@host:5432/dbname?sslmode=require"
# Execute SQL directly
psql -U postgres -d mydb -c "SELECT * FROM users;"
# Execute SQL file
psql -U postgres -d mydb -f queries.sql
# Output formatting
psql -U postgres -d mydb --csv -c "SELECT * FROM users;"
psql -U postgres -d mydb -t -A -c "SELECT count(*) FROM users;" # tuples only, unaligned
```
### Meta-Commands
```
\l -- List all databases
\c dbname -- Connect to database
\dt -- List tables in current schema
\dt public.* -- List tables in public schema
\dt+ users -- Table details with size
\d users -- Describe table (columns, types, constraints)
\d+ users -- Extended description (storage, stats)
\di -- List indexes
\di+ idx_users_email -- Index details
\dn -- List schemas
\df -- List functions
\dv -- List views
\du -- List roles/users
\dp users -- Show table privileges
\x -- Toggle expanded display (vertical rows)
\timing -- Toggle query timing display
\i file.sql -- Execute SQL file
\o output.txt -- Send output to file
\o -- Reset output to terminal
\! command -- Execute shell command
\e -- Edit query in $EDITOR
\g -- Execute last query again
\s -- Show command history
\pset format csv -- Set output format (csv, html, latex, wrapped)
```
### Schema Inspection
```sql
-- List all tables with sizes
SELECT schemaname, tablename,
pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;
-- List columns for a table
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;
-- List indexes
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';
-- List foreign keys
SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'users'::regclass;
-- Active connections
SELECT pid, usename, datname, state, query
FROM pg_stat_activity
WHERE state = 'active';
```
### Backup and Restore
```bash
# Dump single database (SQL)
pg_dump -U postgres mydb > backup.sql
# Dump with compression
pg_dump -U postgres -Fc mydb > backup.dump
# Dump schema only
pg_dump -U postgres --schema-only mydb > schema.sql
# Dump data only
pg_dump -U postgres --data-only mydb > data.sql
# Dump single table
pg_dump -U postgres -t users mydb > users.sql
# Dump all databases
pg_dumpall -U postgres > all_databases.sql
# Restore from SQL dump
psql -U postgres mydb < backup.sql
# Restore from custom format
pg_restore -U postgres -d mydb backup.dump
# Restore single table
pg_restore -U postgres -d mydb -t users backup.dump
```
### Query Analysis
```sql
-- Execution plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Execution plan with actual runtime stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'alice@example.com';
-- Table statistics
SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables;
-- Index usage statistics
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public';
-- Update statistics
ANALYZE users;
ANALYZE; -- all tables
-- Slow queries (requires pg_stat_statements extension)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
```
## MySQL (mysql)
### Connection
```bash
# Connect
mysql -u root -p
mysql -u myuser -p mydb
mysql -h hostname -P 3306 -u myuser -p mydb
# Execute SQL directly
mysql -u root -p -e "SELECT * FROM users;" mydb
# Execute SQL file
mysql -u root -p mydb < queries.sql
# Output formatting
mysql -u root -p -N -B -e "SELECT count(*) FROM users;" mydb # raw value
```
### Meta-Commands
```sql
SHOW DATABASES;
USE mydb;
SHOW TABLES;
SHOW TABLE STATUS;
DESCRIBE users; -- column details
SHOW CREATE TABLE users; -- full CREATE statement
SHOW INDEX FROM users;
SHOW PROCESSLIST; -- active connections
SHOW VARIABLES LIKE '%max%'; -- server variables
SHOW STATUS LIKE 'Threads%'; -- server status
```
### Backup and Restore
```bash
# Dump single database
mysqldump -u root -p mydb > backup.sql
# Dump with compression
mysqldump -u root -p mydb | gzip > backup.sql.gz
# Dump schema only
mysqldump -u root -p --no-data mydb > schema.sql
# Dump specific tables
mysqldump -u root -p mydb users orders > tables.sql
# Dump all databases
mysqldump -u root -p --all-databases > all.sql
# Restore
mysql -u root -p mydb < backup.sql
# Restore compressed
gunzip < backup.sql.gz | mysql -u root -p mydb
```
### Query Analysis
```sql
-- Execution plan
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';
-- Extended explain (JSON format, MySQL 5.7+)
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'alice@example.com';
-- Analyze (update index statistics)
ANALYZE TABLE users;
-- Check table integrity
CHECK TABLE users;
-- Optimize table (reclaim space)
OPTIMIZE TABLE users;
-- Show index cardinality
SHOW INDEX FROM users;
```
## Common SQL Patterns
```sql
-- Pagination
SELECT * FROM items ORDER BY id LIMIT 20 OFFSET 40;
-- Count with grouping
SELECT status, COUNT(*) as cnt FROM orders GROUP BY status ORDER BY cnt DESC;
-- Join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01';
-- Subquery
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
-- CTE (Common Table Expression)
WITH active_users AS (
SELECT * FROM users WHERE last_login > '2024-01-01'
)
SELECT * FROM active_users WHERE role = 'admin';
-- Window functions
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
-- Conditional aggregation
SELECT
COUNT(*) as total,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) as inactive
FROM users;
```
## Important Notes
- Always back up before destructive operations (DROP, DELETE, TRUNCATE, ALTER)
- Use transactions for multi-statement changes: `BEGIN; ... COMMIT;` (or `ROLLBACK;`)
- Prefer `EXPLAIN ANALYZE` over `EXPLAIN` to see actual vs estimated row counts
- For SQLite, use WAL mode (`PRAGMA journal_mode=WAL`) for concurrent read/write
- For PostgreSQL, use `\x` for wide tables to get vertical output
- For MySQL, add `\G` at the end of a query for vertical output
- Use `-t` (tuples only) and `-A` (unaligned) in psql for scriptable output
- Never store passwords in command-line arguments; use `.pgpass` (psql) or `.my.cnf` (mysql)
- Use parameterized queries in scripts to prevent SQL injection
---
> Source: [bug-ops/zeph](https://github.com/bug-ops/zeph) — distributed by [TomeVault](https://tomevault.io).
<!-- tomevault:4.0:skill_md:2026-07-20 -->
GitHubで見る