Przejdź do treści
Biblioteka skilli
Programowanie · Nauka i wyjaśnianie

sql-cheatsheet

/sql-cheatsheet

Podaje zwięzłą ściągę SQL: SELECT, JOIN, agregacje, DML, indeksy, transakcje i funkcje okna w MySQL, PostgreSQL i SQLite.

Licencja
MIT
Ostatnia zmiana repozytorium
11.11.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
1
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
11
Przestarzałość

Brak sygnałów. Repozytorium zmienione 11.11.2026, bez odwołań do wycofanych modeli i API.

Licencja

MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.

Co robi i kiedy

Podaje zwięzłą ściągę SQL: SELECT, JOIN, agregacje, DML, indeksy, transakcje i funkcje okna w MySQL, PostgreSQL i SQLite.

Oryginalny opis (EN)

Use when the user needs SQL: SELECT queries, WHERE filters, JOINs, GROUP BY and aggregation, subqueries, INSERT, UPDATE and DELETE, CREATE TABLE, indexes, transactions, and window functions across MySQL, PostgreSQL and SQLite.

Jak działa

  1. Test data (every example runs against this)
  2. 🔍 Query Basics
  3. 📊 Aggregation & Grouping
  4. 🔗 JOINs
  5. 🧩 Subqueries & CTEs
  6. ✏️ Writes & Transactions
  7. 🗂️ Indexes & Schema
  8. More cheatsheets
  9. License
Sekcje z nagłówków SKILL.md, w oryginale · 303 linii, 1 957 słów
Podgląd SKILL.md
---
name: sql-cheatsheet
description: 'Use when the user needs SQL: SELECT queries, WHERE filters, JOINs, GROUP BY and aggregation, subqueries, INSERT, UPDATE and DELETE, CREATE TABLE, indexes, transactions, and window functions across MySQL, PostgreSQL and SQLite.'
---

# SQL Cheatsheet — Queries That Actually Work

One page. Thirty statements. Each entry tells you **what it does**, **a statement you can copy-paste**, and **tags**. Standard SQL throughout, with MySQL / PostgreSQL dialect differences flagged inline. **Every statement was actually executed and verified against SQLite 3.45** — no made-up functions, no untested syntax.

> 🌍 Read this project in your language: [中文](README.md) · [Español](README.es.md) · [Português](README.pt.md)

## Test data (every example runs against this)

```sql
CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept_id INTEGER, salary INTEGER, manager_id INTEGER, hired_on TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, emp_id INTEGER, amount REAL, ordered_on TEXT);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales'), (3, 'HR');
INSERT INTO employees VALUES
  (1, 'Alice', 1, 120000, NULL, '2019-03-01'),
  (2, 'Bob',   1,  95000, 1,    '2020-07-15'),
  (3, 'Carol', 1,  88000, 1,    '2021-01-10'),
  (4, 'David', 2,  72000, NULL, '2018-05-20'),
  (5, 'Eve',   2,  68000, 4,    '2022-09-01'),
  (6, 'Frank', 3,  54000, NULL, '2021-11-30'),
  (7, 'Grace', NULL, 60000, NULL, '2023-02-14');
INSERT INTO orders VALUES
  (101, 2, 250.00, '2024-01-05'),
  (102, 2, 120.50, '2024-01-20'),
  (103, 4, 980.00, '2024-02-02'),
  (104, 5, 430.75, '2024-02-14'),
  (105, 4, 150.00, '2024-03-01'),
  (106, 1, 2000.00, '2024-03-11'),
  (107, 5, 89.99,  '2024-03-15');
```

Note: Grace has no department (`dept_id` is NULL), and Carol / Frank / Grace have no orders — several examples exist precisely to make that visible.

---

## 🔍 Query Basics

### `SELECT` — pick the columns you need
```sql
SELECT name, salary FROM employees;
-- 7 rows. Avoid SELECT * in real code: naming columns is clearer and faster
```
🏷️ `#query` `#basics`
### `WHERE` — filter rows
```sql
SELECT name, salary FROM employees WHERE salary > 80000 ORDER BY salary DESC;
-- Alice 120000 / Bob 95000 / Carol 88000
```
🏷️ `#query` `#filtering`
### `ORDER BY ... LIMIT` — top N
```sql
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;
-- Top 3 salaries: Alice / Bob / Carol
-- LIMIT works in MySQL / PostgreSQL / SQLite; SQL Server uses TOP 3, the SQL:2008 spelling FETCH FIRST 3 ROWS ONLY works in Oracle and PostgreSQL 10+
```
🏷️ `#query` `#sorting` `#pagination`
### `DISTINCT` — deduplicate
```sql
SELECT DISTINCT dept_id FROM employees;
-- 1 / 2 / 3 / NULL (Grace has no department; NULL counts as a value here)
```
🏷️ `#query` `#dedupe`
### `LIKE` — pattern matching
```sql
SELECT name FROM employees WHERE name LIKE 'A%';
-- Alice. % matches any run of characters, _ matches exactly one
-- Dialect: LIKE is case-insensitive for ASCII in SQLite / MySQL, but case-sensitive in PostgreSQL.
--          PostgreSQL also has ILIKE for case-insensitive matching (PG only; MySQL / SQLite don't have it)
```
🏷️ `#query` `#filtering` `#strings`
### `IN` / `BETWEEN` — set membership and ranges
```sql
SELECT name, salary FROM employees
WHERE dept_id IN (1, 2) AND salary BETWEEN 70000 AND 100000
ORDER BY salary DESC;
-- Bob 95000 / Carol 88000 / David 72000 (BETWEEN includes both endpoints)
```
🏷️ `#query` `#filtering`

## 📊 Aggregation & Grouping

### `COUNT` / `SUM` / `AVG` — basic aggregates
```sql
SELECT COUNT(*) AS headcount, SUM(salary) AS total_payroll, AVG(salary) AS avg_salary FROM employees;
-- 7 people / total 557000 / average 79571.43
```
🏷️ `#aggregation`
### `GROUP BY` — per-group stats
```sql
SELECT dept_id, COUNT(*) AS n, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id;
-- dept 1: 3 people, avg 101000; dept 2: 2, 70000; dept 3: 1, 54000; NULL group: 1, 60000
```
🏷️ `#aggregation` `#grouping`
### `HAVING` — filter groups, not rows
```sql
SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id HAVING AVG(salary) > 60000;
-- Only dept 1 (101000) and dept 2 (70000) survive
-- WHERE filters rows before grouping; HAVING filters groups after. Don't mix them up
```
🏷️ `#aggregation` `#grouping` `#filtering`
### `COUNT(DISTINCT ...)` — count unique values
```sql
SELECT COUNT(DISTINCT dept_id) AS dept_count FROM employees;
-- 3. COUNT skips NULLs, so Grace's missing department isn't counted
```
🏷️ `#aggregation` `#dedupe`
### `CASE WHEN` — conditional aggregation
```sql
SELECT COUNT(*) AS total,
       SUM(CASE WHEN salary >= 90000 THEN 1 ELSE 0 END) AS high_earners
FROM employees;
-- total 7 / high earners 2 (Alice, Bob). Several metrics with different rules in one pass
```
🏷️ `#aggregation` `#conditional`

## 🔗 JOINs

### `INNER JOIN` — matching rows from both tables
```sql
SELECT e.name, d.name AS dept FROM employees e JOIN departments d ON e.dept_id = d.id;
-- 6 rows: Grace has no department and drops out
```
🏷️ `#join`
### `LEFT JOIN` — keep every row of the left table
```sql
SELECT e.name, d.name AS dept FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;
-- 7 rows: Grace is there with dept = NULL. Full roster → LEFT JOIN; only matched → INNER JOIN
```
🏷️ `#join`
### `LEFT JOIN ... IS NULL` — rows with no match
```sql
SELECT e.name FROM employees e LEFT JOIN orders o ON o.emp_id = e.id WHERE o.id IS NULL;
-- Carol / Frank / Grace: employees who never ordered. The classic orphan-finder
```
🏷️ `#join` `#debugging`
### Self join — a table joined to itself
```sql
SELECT e.name AS employee, m.name AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;
-- Bob→Alice, Carol→Alice, Eve→David; people without a manager get NULL
-- Org charts, threaded comments, any hierarchy stored in one table works this way
```
🏷️ `#join` `#hierarchy`
### Three-table JOIN + aggregation — orders per department
```sql
SELECT d.name AS dept, COUNT(o.id) AS order_count, SUM(o.amount) AS revenue
FROM departments d
JOIN employees e ON e.dept_id = d.id
JOIN orders o ON o.emp_id = e.id
GROUP BY d.id, d.name;
-- Engineering: 3 orders, 2370.50; Sales: 4 orders, 1650.74; HR has no orders and doesn't appear (INNER JOIN)
```
🏷️ `#join` `#aggregation` `#real-world`

## 🧩 Subqueries & CTEs

### Scalar subquery — compare against an overall average
```sql
SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
-- Alice / Bob / Carol: everyone paid above the company average (79571.43)
```
🏷️ `#subquery`
### `IN` subquery — filter by another table's result
```sql
SELECT name FROM employees WHERE id IN (SELECT emp_id FROM orders WHERE amount > 500);
-- Alice / David: employees who placed an order over 500
```
🏷️ `#subquery`
### `EXISTS` — existence check
```sql
SELECT d.name FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id AND e.salary > 100000);
-- Engineering: departments with at least one employee earning over 100k.
-- EXISTS only cares whether a row exists — a common pick for large tables
```
🏷️ `#subquery`
### `WITH` (CTE) — name an intermediate result
```sql
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary FROM employees e
JOIN dept_avg a ON e.dept_id = a.dept_id
WHERE e.salary > a.avg_salary;
-- Alice (above dept avg 101000) / David (above 70000): paid more than their own department's average
-- CTEs make complex queries read top-to-bottom instead of inside-out; MySQL 8.0+ / PostgreSQL / SQLite 3.8.8+ support them
```
🏷️ `#cte` `#subquery`
### Window function `RANK()` — ranking inside groups
```sql
SELECT name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees;
-- In dept 1: Alice 1, Bob 2, Carol 3; in dept 2: David 1, Eve 2
-- GROUP BY collapses rows; window functions keep every row and attach a rank — that's the key difference
-- Dialect: MySQL 8.0+ / PostgreSQL / SQLite 3.25+. Older MySQL can't do this
```
🏷️ `#window-functions` `#ranking`

## ✏️ Writes & Transactions

### `INSERT INTO` — add a row
```sql
INSERT INTO employees (id, name, dept_id, salary, manager_id, hired_on)
VALUES (8, 'Henry', 2, 71000, 4, '2024-06-01');
-- Always name the columns; multi-row inserts chain more (...) groups after VALUES
```
🏷️ `#writes`
### `UPDATE ... WHERE` — change rows
```sql
UPDATE employees SET salary = salary + 5000 WHERE name = 'Eve';
-- Eve: 68000 → 73000, exactly 1 row affected
-- ⚠️ Forget the WHERE and you update the whole table. Run a SELECT with the same condition first to count the rows
```
🏷️ `#writes` `#danger`
### `DELETE ... WHERE` — remove rows
```sql
DELETE FROM orders WHERE amount < 100;
-- Removes 1 row (the 89.99 order). Without WHERE it empties the table
-- Business data is usually soft-deleted instead (a deleted_at flag), not hard-DELETEd
```
🏷️ `#writes` `#danger`
### Transactions — all or nothing
```sql
BEGIN;
UPDATE employees SET dept_id = 2 WHERE name = 'Frank';
COMMIT;
-- Frank moves from HR to Sales. If any step fails, ROLLBACK undoes everything as if it never happened
-- Money transfers and stock deductions must run inside a transaction. In MySQL, START TRANSACTION is the same as BEGIN
```
🏷️ `#transactions` `#writes`
### `UPSERT` — update if present, insert if not
```sql
INSERT INTO departments (id, name) VALUES (3, 'People')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;
-- id=3 already exists → the department is renamed HR → People; if it didn't exist, it would be inserted
-- This is the SQLite / PostgreSQL spelling. MySQL: ON DUPLICATE KEY UPDATE name = VALUES(name) — but VALUES() is deprecated since MySQL 8.0.20; modern spelling uses a row alias: ... VALUES (3, 'People') AS new ON DUPLICATE KEY UPDATE name = new.name
```
🏷️ `#writes` `#dialects`

## 🗂️ Indexes & Schema

### `CREATE TABLE` — define a table
```sql
CREATE TABLE projects (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  lead_id INTEGER REFERENCES employees(id)
);
-- Primary keys, NOT NULL and foreign keys belong in the schema, not in application code
-- SQLite note: foreign keys are parsed but NOT enforced unless you run PRAGMA foreign_keys = ON
-- Dialect: INTEGER PRIMARY KEY is SQLite's auto-incrementing row id;
--          MySQL writes INT AUTO_INCREMENT PRIMARY KEY;
--          PostgreSQL writes INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or the older SERIAL)
```
🏷️ `#ddl` `#schema`
### `ALTER TABLE` — add a column
```sql
ALTER TABLE employees ADD COLUMN email TEXT;
-- New columns start as NULL. Backfill first, add NOT NULL later — small steps avoid long table locks
```
🏷️ `#ddl`
### `CREATE INDEX` — speed up frequent lookups
```sql
CREATE INDEX idx_employees_dept ON employees(dept_id);
-- After this, WHERE dept_id = ? uses the index instead of scanning the whole table
-- Verify: EXPLAIN QUERY PLAN in SQLite, EXPLAIN in MySQL / PostgreSQL
-- Every index slows writes down, so index the columns you actually filter on — not everything
```
🏷️ `#ddl` `#index` `#performance`
### `DROP` — remove an index or a table
```sql
DROP INDEX idx_employees_dept;
-- MySQL spelling: DROP INDEX idx_employees_dept ON employees;
DROP TABLE projects;
-- ⚠️ Irreversible. Check for foreign keys pointing at the table first, and back up before you run it in production
```
🏷️ `#ddl` `#danger`

---

Found an error, a wrong dialect note, or a missing everyday query? PRs are welcome — keep the format (one-line description / sql block / tags) and **run the statement against a real database before submitting**. Unverified statements don't get in.

## More cheatsheets

- [ffmpeg-cheatsheet](https://github.com/Ezra-Zhao/ffmpeg-cheatsheet) — 28 FFmpeg commands
- [git-cheatsheet](https://github.com/Ezra-Zhao/git-cheatsheet) — 30 daily + rescue Git commands
- [docker-cheatsheet](https://github.com/Ezra-Zhao/docker-cheatsheet) — 28 Docker commands
- [curl-cheatsheet](https://github.com/Ezra-Zhao/curl-cheatsheet) — 29 curl commands for API debugging
- [regex-cheatsheet](https://github.com/Ezra-Zhao/regex-cheatsheet) — 30 real-world regex patterns
- [jq-cheatsheet](https://github.com/Ezra-Zhao/jq-cheatsheet) — 30 jq filters for JSON
- [terminal-superpowers-cheatsheet](https://github.com/Ezra-Zhao/terminal-superpowers-cheatsheet) — 14 modern terminal tools
- [adobe-free-alternatives-cheatsheet](https://github.com/Ezra-Zhao/adobe-free-alternatives-cheatsheet) — 16 free Adobe alternatives
- [desubscribe-cheatsheet](https://github.com/Ezra-Zhao/desubscribe-cheatsheet) — 10 subscription swaps, ≈ $1,260/year saved
- [kubectl-cheatsheet](https://github.com/Ezra-Zhao/kubectl-cheatsheet) — everyday Kubernetes commands
- [ssh-cheatsheet](https://github.com/Ezra-Zhao/ssh-cheatsheet) — logins, tunnels and file transfer

## License

MIT © 2026 Guangyi Zhao — see [LICENSE](LICENSE).

Wersje

W bazie nie ma innych skilli o nazwie sql-cheatsheet.

Migawka bazy z 13.11.2026 · wersja paczki e963bb6b741c (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100sql-expert
    Pomaga pisać i optymalizować zapytania SQL oraz projektować bazy w PostgreSQL, MySQL, SQLite i BigQuery.
  • 100frappe-syntax-query-builder
    Pomaga budować zapytania do bazy przez frappe.qb opartego na PyPika: SELECT, INSERT, UPDATE, DELETE, joiny, agregacje, podzapytania i zgodność z MariaDB i PostgreSQL.
  • 100sql-fundamentals
    Podaje podstawy SQL według standardu ANSI: DML, DDL, joiny, transakcje i podstawowe pojęcia baz danych, jako fundament pracy z bazami SQL.
  • 100sql
    Pisze, poprawia i uruchamia zapytania SQL oraz analizuje pliki CSV i SQLite przy pomocy narzędzia sqlite3.
  • 100sql-query-optimization
    Diagnozuje i przepisuje wolne SQL na podstawie planów EXPLAIN w PostgreSQL, MySQL, SQL Server i SQLite.
  • 100sql-expert
    Wspiera projektowanie, zapytania, optymalizację i administrację baz SQL w PostgreSQL, MySQL i SQL Server: indeksy, wydajność, transakcje i współbieżność.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.