Cheatography
Login Register

DB rules Cheat Sheet

A Programming › SQL cheat sheet in English by jasperdelaat

DB rules for out projects

SQL
10,234 views 1,932 downloads 18 Mar 2020 History
PRs
Download

General rules

Engels
Unieke naam zonder gerese­rveerde MSSQL keywords
Namen maximaal 30 karakters lang
snake_­casing
SQL keywoorden altijd met hoofdl­etters (SELECT, WHERE)
Altijd AS gebruiken bij aliassen
Aliassen volledig uitsch­rijven, niet één letter
Joins altijd specif­iceren welke join het is
Joins sorteren op volgorde: LEFT, RIGHT, INNER
Where clause altijd op een nieuwe regel
FROM altijd maar op één tabel

Select

GOOD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
...


BAD:

SELECT
projec­ts.name projec­t_name,
COUNT(­bac­kin­gs.id) backin­gs_­count
...

Where

GOOD:

SELECT
name,
goal
FROM projects AS projects
WHERE
country = '­US'
AND deadline >= '­201­5-0­1-0­1'
...


BAD:

SELECT
name,
goal
FROM projects AS projects
WHERE country = '­US' AND deadline >= '­201­5-0­1-0­1'
...

Tables

Namen in meervoud
Geen prefix
Koppel­tabel niet table1­_ta­ble2, correcte naam geven

Achter­voe­gsels

_id
unieke identifier
_total
totaal of som van andere veden
_number
voor numerieke velden
_name
voor naamvelden
_date
datumv­elden

Join Sorting

GOOD:

INNER JOIN backings AS backings ON ...
INNER JOIN users AS users ON ...
INNER JOIN locations AS locations ON ...
LEFT JOIN backer­_re­wards AS backer­_re­wards ON ...
LEFT JOIN ...


BAD:

LEFT JOIN backer­_re­wards AS backer­_re­wards ON backings
INNER JOIN users AS users ON ...
LEFT JOIN ...
INNER JOIN locations AS locations ON ...

From

GOOD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects
INNER JOIN backings AS backings ON backin­gs.p­ro­ject_id = projec­ts.id
...


BAD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects, backings AS backings
WHERE
backin­gs.p­ro­ject_id = projec­ts.id
...

Columns

Enkelv­oudige naam
Id velden: tabeln­aam_id
Geen column met de naam van de tabel
Foreign key velden: tabeln­aam­_ve­ldnaam

Stored procedures

Prefix sp_
Naam moet een werkwo­ord­/ha­ndeling bevatten
Comments alleen indien echt nodig, deze dan boven de select zetten

Join Specif­iceren

GOOD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects
INNER JOIN backings AS backings ON ...
INNER JOIN ...
LEFT JOIN backer­_re­wards AS backer­_re­wards ON ...
LEFT JOIN ...


BAD:

SELECT
projec­ts.name AS projec­t_name,
COUNT(­bac­kin­gs.id) AS backin­gs_­count
FROM projects AS projects
JOIN backings AS backings ON ...
LEFT JOIN backer­_re­wards AS backer­_re­wards ON ...
LEFT JOIN ...

Case

GOOD:

CASE WHEN category = '­Art­'
THEN backer_id
ELSE NULL
END


BAD:

CASE
WHEN category = '­Art­'
THEN backer_id
ELSE NULL
END

Badges

Nice Cheat Sheet

Comments

No comments yet.

Cheatography n. The art of mapping complex subjects into clear, instantly navigable reference guides.