DuckDB Cheatsheet
This cheatsheet is a quick reference for the most commonly used DuckDB 2.0 commands and features.
Installation & CLI
| Task |
Command |
| Install CLI (Linux/macOS) |
curl https://install.duckdb.org \| bash |
| Install CLI (Windows PowerShell) |
irm https://install.duckdb.org/install.ps1 \| iex |
| Run CLI with a database file |
duckdb file.duckdb |
| Run CLI in-memory |
duckdb |
| Docker |
docker run --rm -it -v "$(pwd):/workspace" -w /workspace duckdb/duckdb:2.0.0 |
| Exit the CLI |
.exit |
| CLI help |
.help |
Python
| Task |
Code |
| Installation |
pip install duckdb |
| Connect to a file |
con = duckdb.connect("db.duckdb") |
| In-memory connection |
con = duckdb.connect() |
| Run a query |
con.execute("SELECT * FROM table").fetchall() |
| Close the connection |
con.close() |
Database & Table Management
| Task |
SQL Command |
| Create a database file |
duckdb file.duckdb (CLI) or duckdb.connect("file.duckdb") (Python) |
| Create a table |
CREATE TABLE t (id INTEGER, name VARCHAR); |
| Create a table from a query |
CREATE TABLE t AS SELECT * FROM read_csv('f.csv'); |
| Drop a table |
DROP TABLE IF EXISTS t; |
| List databases |
SHOW DATABASES; |
| List tables |
SHOW TABLES; |
| Attach an external DB |
ATTACH 'other.duckdb' AS alias; |
| Detach a connection |
DETACH alias; |
Importing Data
| Task |
SQL Command |
| Read a CSV |
SELECT * FROM read_csv('file.csv'); |
| Save a CSV as a table |
CREATE TABLE t AS SELECT * FROM read_csv('file.csv'); |
| Read Parquet |
SELECT * FROM read_parquet('file.parquet'); |
| Bulk import into a table |
COPY t FROM 'file.csv'; |
Querying Data (SELECT)
| Task |
SQL Command |
| All data |
SELECT * FROM t; |
| Specific columns |
SELECT name, grades FROM t; |
| Filter |
SELECT * FROM t WHERE intake_year = 2023; |
| Sort |
SELECT * FROM t ORDER BY name DESC; |
| Unique values |
SELECT DISTINCT major FROM t; |
| Limit rows |
SELECT * FROM t LIMIT 10; |
| Combine clauses |
SELECT * FROM t WHERE ... ORDER BY ... LIMIT ...; |
JOIN
| Task |
SQL Command |
| Inner join |
SELECT * FROM a INNER JOIN b ON a.id = b.id; |
| Left join |
SELECT * FROM a LEFT JOIN b ON a.id = b.id; |
| Table alias |
SELECT m.name FROM students m; |
Aggregate Functions & GROUP BY
| Task |
SQL Command |
| Count rows |
SELECT COUNT(*) FROM t; |
| Sum |
SELECT SUM(score) FROM t; |
| Average |
SELECT AVG(score) FROM t; |
| Highest value |
SELECT MAX(score) FROM t; |
| Lowest value |
SELECT MIN(score) FROM t; |
| Group |
SELECT column, AGGREGATE(score) FROM t GROUP BY column; |
| Filter groups |
SELECT ... GROUP BY column HAVING AGGREGATE(score) > 80; |
Official References
For complete documentation and other advanced features, visit:
Summary
The cheatsheet above covers the most commonly used DuckDB 2.0 commands, from installation and database management to data import, SELECT queries, JOINs, and aggregate functions. Keep this page as a quick reference while you learn and work with DuckDB.
Happy learning DuckDB!
