Database Tools Skill

数据库工具技能

Database operations for SQLite and PostgreSQL.

针对SQLite和PostgreSQL的数据库操作。

When to Use

适用场景

Query databases
Run migrations
Backup and restore
Check database health
Analyze queries

查询数据库
执行迁移
备份与恢复
检查数据库健康状态
分析查询语句

SQLite Operations

SQLite 操作

Connect & Query

连接与查询

bash

undefined

bash

undefined

Connect to database

sqlite3 my.db

List tables

sqlite3 my.db ".tables"

Schema of table

sqlite3 my.db ".schema users"

List indexes

sqlite3 my.db ".indexes"

undefined

sqlite3 my.db ".indexes"

undefined

Query Examples

查询示例

bash

undefined

bash

undefined

Select all from table

sqlite3 my.db "SELECT * FROM users;"

Select with where

sqlite3 my.db "SELECT * FROM users WHERE id = 1;"

Count rows

sqlite3 my.db "SELECT COUNT(*) FROM users;"

Insert row

sqlite3 my.db "INSERT INTO users (name, email) VALUES ('John', 'john@example.com');"

Update row

sqlite3 my.db "UPDATE users SET name = 'Jane' WHERE id = 1;"

Delete row

sqlite3 my.db "DELETE FROM users WHERE id = 1;"

undefined

sqlite3 my.db "DELETE FROM users WHERE id = 1;"

undefined

Export/Import

导出/导入

bash

undefined

bash

undefined

Export to CSV

sqlite3 my.db -header -csv "SELECT * FROM users;" > users.csv

Import from CSV

sqlite3 my.db ".import users.csv users"

Export schema

sqlite3 my.db ".schema" > schema.sql

Full database dump

sqlite3 my.db ".dump" > backup.sql

undefined

sqlite3 my.db ".dump" > backup.sql

undefined

Vacuum & Optimize

清理与优化

bash

undefined

bash

undefined

Check database size

ls -lh my.db

Vacuum (reclaim space)

sqlite3 my.db "VACUUM;"

Analyze (update statistics)

sqlite3 my.db "ANALYZE;"

undefined

sqlite3 my.db "ANALYZE;"

undefined

PostgreSQL Operations

PostgreSQL 操作

Connect & Query

连接与查询

bash

undefined

bash

undefined

Connect

psql -U user -d database

Connect to remote

psql -h hostname -U user -d database

With password

PGPASSWORD=secret psql -U user -d database

undefined

PGPASSWORD=secret psql -U user -d database

undefined

Common Commands

常用命令

bash

undefined

bash

undefined

List tables

\dt

Describe table

\d users

List indexes

\di

List sequences

\ds

List views

\dv

Quit

\q

undefined

\q

undefined

Query Examples

查询示例

bash

undefined

bash

undefined

Select all

SELECT * FROM users;

Select with limit

SELECT * FROM users LIMIT 10;

Join example

SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;

Aggregate

SELECT status, COUNT(*) FROM orders GROUP BY status;

Subquery

SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total > 100 );

undefined

SELECT * FROM users WHERE id IN ( SELECT user_id FROM orders WHERE total > 100 );

undefined

User Management

用户管理

bash

undefined

bash

undefined

Create user

CREATE USER newuser WITH PASSWORD 'secret';

Grant privileges

GRANT ALL PRIVILEGES ON DATABASE mydb TO newuser;

Grant table access

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO newuser;

Revoke access

REVOKE ALL PRIVILEGES ON DATABASE mydb FROM newuser;

undefined

REVOKE ALL PRIVILEGES ON DATABASE mydb FROM newuser;

undefined

Backup & Restore

备份与恢复

bash

undefined

bash

undefined

Dump single database

pg_dump -U user database > backup.sql

Dump with compression

pg_dump -U user database | gzip > backup.sql.gz

Restore

psql -U user database < backup.sql

Restore from gzipped

gunzip -c backup.sql.gz | psql -U user database

Dump all databases

pg_dumpall -U user > all_databases.sql

undefined

pg_dumpall -U user > all_databases.sql

undefined

Migration Examples

迁移示例

Create Table Migration

创建表迁移

sql

-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

sql

-- migrations/001_create_users.sql
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

Add Column Migration

添加列迁移

sql

-- migrations/002_add_status.sql
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active';

-- Rollback
ALTER TABLE users DROP COLUMN status;

sql

-- migrations/002_add_status.sql
ALTER TABLE users ADD COLUMN status VARCHAR(50) DEFAULT 'active';

-- Rollback
ALTER TABLE users DROP COLUMN status;

Rename/Migrate

重命名/迁移

sql

-- Rename table
ALTER TABLE users RENAME TO accounts;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

sql

-- Rename table
ALTER TABLE users RENAME TO accounts;

-- Rename column
ALTER TABLE users RENAME COLUMN name TO full_name;

Performance Analysis

性能分析

Explain Queries

解释查询语句

sql

-- Explain query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Explain without running
EXPLAIN SELECT * FROM users;

sql

-- Explain query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- Explain without running
EXPLAIN SELECT * FROM users;

Find Slow Queries

查找慢查询

bash

undefined

bash

undefined

PostgreSQL: Enable slow query log

In postgresql.conf:

log_min_duration_statement = 1000

View logs

tail -f /var/log/postgresql/postgresql.log

Using pg_stat_statements

SELECT query, calls, mean_time, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

undefined

SELECT query, calls, mean_time, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

undefined

Index Recommendations

索引建议

sql

-- Find missing indexes from queries
SELECT 
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC;

sql

-- Find missing indexes from queries
SELECT 
    schemaname,
    relname,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_scan DESC;

Database Health Check

数据库健康检查

SQLite Health

SQLite 健康检查

bash

#!/bin/bash

bash

#!/bin/bash

SQLite health check

DB="$1"

echo "=== SQLite Health Check ===" echo "Database: $DB" echo ""

echo "Size:" ls -lh "$DB" echo ""

echo "Table count:" sqlite3 "$DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table';" echo ""

echo "Index count:" sqlite3 "$DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='index';" echo ""

echo "Integrity check:" sqlite3 "$DB" "PRAGMA integrity_check;"

undefined

DB="$1"

echo "=== SQLite Health Check ===" echo "Database: $DB" echo ""

echo "Size:" ls -lh "$DB" echo ""

echo "Table count:" sqlite3 "$DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='table';" echo ""

echo "Index count:" sqlite3 "$DB" "SELECT COUNT(*) FROM sqlite_master WHERE type='index';" echo ""

echo "Integrity check:" sqlite3 "$DB" "PRAGMA integrity_check;"

undefined

PostgreSQL Health

PostgreSQL 健康检查

bash

#!/bin/bash

bash

#!/bin/bash

PostgreSQL health check

echo "=== PostgreSQL Health ===" echo ""

echo "Database size:" psql -U user -d db -c "SELECT pg_database_size(current_database());"

echo "Table sizes:" psql -U user -d db -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 5;"

echo "Active connections:" psql -U user -d db -c "SELECT count(*) FROM pg_stat_activity;"

echo "Long running queries:" psql -U user -d db -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY duration DESC LIMIT 5;"

undefined

echo "=== PostgreSQL Health ===" echo ""

echo "Database size:" psql -U user -d db -c "SELECT pg_database_size(current_database());"

echo "Table sizes:" psql -U user -d db -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 5;"

echo "Active connections:" psql -U user -d db -c "SELECT count(*) FROM pg_stat_activity;"

echo "Long running queries:" psql -U user -d db -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY duration DESC LIMIT 5;"

undefined

Notes

注意事项

Always backup before migrations
Use transactions for multi-step changes
Test migrations on staging first
Use parameterized queries to prevent SQL injection
Thepopebot stores data in SQLite at
```
data/thepopebot.sqlite
```

迁移前务必备份数据
多步骤变更使用事务
先在测试环境测试迁移
使用参数化查询防止SQL注入
Thepopebot 将数据存储在SQLite数据库
```
data/thepopebot.sqlite
```
中

database-tools

Original

Translation

Database Tools Skill

数据库工具技能

When to Use

适用场景

SQLite Operations

SQLite 操作

Connect & Query

连接与查询

Connect to database

Connect to database

List tables

List tables

Schema of table

Schema of table

List indexes

List indexes

Query Examples

查询示例

Select all from table

Select all from table

Select with where

Select with where

Count rows

Count rows

Insert row

Insert row

Update row

Update row

Delete row

Delete row

Export/Import

导出/导入

Export to CSV

Export to CSV

Import from CSV

Import from CSV

Export schema

Export schema

Full database dump

Full database dump

Vacuum & Optimize

清理与优化

Check database size

Check database size

Vacuum (reclaim space)

Vacuum (reclaim space)

Analyze (update statistics)

Analyze (update statistics)

PostgreSQL Operations

PostgreSQL 操作

Connect & Query

连接与查询

Connect

Connect

Connect to remote

Connect to remote

With password

With password

Common Commands

常用命令

List tables

List tables

Describe table

Describe table

List indexes

List indexes

List sequences

List sequences

List views

List views

Quit

Quit

Query Examples

查询示例

Select all

Select all

Select with limit