Connection
| Command | Description |
|---|---|
psql -U user -d dbname | Connect to PostgreSQL |
mysql -u user -p dbname | Connect to MySQL |
\q / exit | Disconnect |
\c dbname | Switch DB (PostgreSQL) |
use dbname; | Switch DB (MySQL) |
Database Operations
| SQL | Description |
|---|---|
CREATE DATABASE dbname; | Create database |
DROP DATABASE dbname; | Drop database |
\l / SHOW DATABASES; | List databases |
\dt / SHOW TABLES; | List tables |
\d table / DESCRIBE table; | Show table structure |
Table Operations
| SQL | Description |
|---|---|
CREATE TABLE t (col type); | Create table |
DROP TABLE t; | Drop table |
TRUNCATE TABLE t; | Delete all data (fast) |
ALTER TABLE t ADD col type; | Add column |
ALTER TABLE t DROP col; | Drop column |
ALTER TABLE t RENAME TO new; | Rename table |
Data Types
| Type | Description |
|---|---|
INTEGER / INT | Integer |
BIGINT | Large integer |
DECIMAL(p,s) | Fixed-point |
FLOAT / REAL | Floating-point |
VARCHAR(n) | Variable-length string |
TEXT | Long text |
BOOLEAN | Boolean |
DATE | Date |
TIMESTAMP | Timestamp |
JSON / JSONB | JSON data |
UUID | Unique identifier |
SELECT (Data Retrieval)
| SQL | Description |
|---|---|
SELECT * FROM t; | Get all columns |
SELECT col1, col2 FROM t; | Get specific columns |
SELECT DISTINCT col FROM t; | Remove duplicates |
SELECT * FROM t WHERE cond; | Filter by condition |
SELECT * FROM t ORDER BY col; | Sort results |
SELECT * FROM t LIMIT 10; | Limit results |
SELECT * FROM t OFFSET 10; | Skip rows |
WHERE Conditions
| Condition | Description |
|---|---|
col = 'value' | Equals |
col != 'value' / col <> 'value' | Not equals |
col > / < / >= / <= | Comparison |
col BETWEEN a AND b | Range |
col IN ('a', 'b') | In list |
col LIKE '%pattern%' | Pattern match |
col ILIKE '%pattern%' | Case-insensitive (PostgreSQL) |
col IS NULL | Is null |
col IS NOT NULL | Is not null |
cond1 AND cond2 | AND condition |
cond1 OR cond2 | OR condition |
INSERT / UPDATE / DELETE
| SQL | Description |
|---|---|
INSERT INTO t (cols) VALUES (vals); | Insert data |
INSERT INTO t VALUES (vals); | Insert all columns |
UPDATE t SET col=val WHERE cond; | Update data |
DELETE FROM t WHERE cond; | Delete data |
JOIN
| SQL | Description |
|---|---|
INNER JOIN t2 ON t1.id = t2.id | Inner join |
LEFT JOIN t2 ON t1.id = t2.id | Left outer join |
RIGHT JOIN t2 ON t1.id = t2.id | Right outer join |
FULL OUTER JOIN t2 ON ... | Full outer join |
CROSS JOIN t2 | Cross join |
Aggregate Functions
| Function | Description |
|---|---|
COUNT(*) | Count rows |
SUM(col) | Sum |
AVG(col) | Average |
MIN(col) | Minimum |
MAX(col) | Maximum |
GROUP BY col | Group by |
HAVING cond | Group condition |
Indexes
| SQL | Description |
|---|---|
CREATE INDEX idx ON t(col); | Create index |
CREATE UNIQUE INDEX idx ON t(col); | Create unique index |
DROP INDEX idx; | Drop index |
\di / SHOW INDEX FROM t; | List indexes |
Transactions
| SQL | Description |
|---|---|
BEGIN; / START TRANSACTION; | Start transaction |
COMMIT; | Commit |
ROLLBACK; | Rollback |
SAVEPOINT name; | Create savepoint |
ROLLBACK TO name; | Rollback to savepoint |
Utilities
| Command | Description |
|---|---|
EXPLAIN query; | Show execution plan |
EXPLAIN ANALYZE query; | Show plan and timing |
VACUUM; | Reclaim storage (PostgreSQL) |
ANALYZE t; | Update statistics |
\timing | Show execution time (PostgreSQL) |