SQL-spiekbriefje: beknopte handleiding voor essentiële opdrachten

Als programmeur of data-analist werk je vaak met grote hoeveelheden data. Een van de krachtigste tools die u tot uw beschikking heeft, is SQL (Structured Query Language).

Deze SQL-spiekbrief geeft u de meest voorkomende SQL-objecten en -opdrachten, waaronder gegevenstypen, DML- en DDL-statements, statistische functies, tekenreeks- en datumfuncties en subquery's.

Het is ontworpen om een ​​snelle en handige referentie te zijn die u kunt bereiken wanneer u met een relationele database werkt.

Als je net als wij bent en het spiekbriefje wilt downloaden en afdrukken of opslaan op je computer, kun je het hieronder downloaden.

SQL-cheatsheet downloaden

Inhoudsopgave

• SQL-gegevenstypen
  • 1. Numerieke typen
  • 2. Datum- en tijdtypen
  • 3. Stringtypen
• SELECT-verklaring
• WAAR clausule
• BESTEL PER clausule
• Meerdere tabellen samenvoegen in SQL
  • 1. BINNENAANSLUITING
  • 2. LINKS MEEDOEN
  • 3. RECHTS DOE MEE
  • 4. VOLLEDIG DOE MEE
• SQL-aggregatiefuncties
  • 1. COUNT-functie
  • 2. SUM-functie
  • 3. AVG-functie
  • 4. MAX-functie
  • 5. MIN-functie
• Gemeenschappelijke tekenreeksfuncties
• Algemene numerieke functies
• Gemeenschappelijke datumfuncties
• GROEP OP EN HEBBEN
  • 1. Groeperen op clausule
  • 2. Clausule hebben
  • Snelle tips
• Subquery's
  • 1. IN-operator
  • 2. BESTAAT Operator
  • 3. ELKE operator
  • 4. ALLE-operator
• Gegevensmanipulatie (DML)
  • 1. INSERT-verklaring
  • 2. UPDATE-verklaring
  • 3. DELETE Verklaring
• Databasebeheer met DDL
  • 1. CREATE-verklaring
  • 2. ALTER-verklaring
    • VOEG EEN NIEUWE KOLOM TOE
    • Wijzig het gegevenstype van een kolom
    • Laat een kolom vallen
    • Voeg een unieke beperking toe
    • Voeg een externe sleutel toe tussen tabellen
  • 3. DROP-verklaring
• Transacties
  • 1. BEGIN
  • 2. TOEZEGGING
  • 3. ROLLBACK
• Laatste gedachten

SQL-gegevenstypen

De gegevenstypen voor specifieke databasebeheersystemen (DBMS) kunnen variëren (bijv. Microsoft SQL Server versus MySQL ). Er zijn er echter verschillende die op de meeste systemen te vinden zijn. Deze kun je onderverdelen in drie categorieën:

1. Numeriek

2. Datum en tijd

3. Snaar

1. Numerieke typen

Dit zijn de meest voorkomende numerieke typen:

• INTEGER : Een geheel getal zonder komma.

• SMALLINT : Een kleiner bereik van gehele getallen

• BIGINT : Een groter bereik van gehele getallen.

• DECIMAL(p, s) of NUMERIC(p, s) : Een decimaal(5,2) past bijvoorbeeld bij 123,45.

• REAL : Een getal met drijvende komma, met een precisie van ten minste 6 cijfers achter de komma.

• FLOAT(n) : Een getal met drijvende komma, met een precisie van ten minste n cijfers.

2. Datum- en tijdtypen

• DATE : Een datumwaarde, meestal in de notatie 'JJJJ-MM-DD'.

• TIME : Een tijdswaarde, meestal in het formaat 'UU:MM:SS'.

• DATETIME of TIMESTAMP : Een combinatie van datum- en tijdwaarden.

3. Stringtypen

• CHAR(n) : Een tekenreeks met een vaste lengte en n tekens.

• VARCHAR(n) of CHARACTER VARYING(n) : Een tekenreeks met variabele lengte.

• TEXT : Een tekenreeks met variabele lengte en een maximale lengte bepaald door het DBMS.

SELECT-verklaring

De SELECT-instructie wordt gebruikt om gegevens uit een of meer tabellen op te halen. U kunt aangeven welke kolommen u wilt ophalen en uit welke tabel. Een standaard SELECT-instructie ziet er als volgt uit:

SELECT column1, column2
FROM table;

Om alle records uit de kolommen 'name' en 'country_id' uit de tabel 'city' op te halen, ziet uw SQL-query er als volgt uit:

SELECT name, country_id
FROM city;

WAAR clausule

Met de WHERE-component kunt u de resultaten van een SELECT-instructie filteren op basis van specifieke voorwaarden.

SELECT column1, column2
FROM table
WHERE condition;

Om records op te halen uit de tabel 'stad' waar de 'bevolking' groter is dan 1.000.000, ziet uw zoekopdracht er als volgt uit:

SELECT name, population
FROM city
WHERE population > 1000000;

BESTEL PER clausule

Met de ORDER BY-component kunt u de resultaten van een SELECT-instructie sorteren op een of meer kolommen. U kunt de resultaten in oplopende (ASC) of aflopende (DESC) volgorde sorteren:

SELECT column1, column2
FROM table
ORDER BY column1 ASC, column2 DESC;

Om bijvoorbeeld records op te halen uit de tabel 'stad', gesorteerd op 'populatie' in aflopende volgorde, ziet uw zoekopdracht er als volgt uit:

SELECT name, population
FROM city
ORDER BY population DESC;

Meerdere tabellen samenvoegen in SQL

Er zijn vier veelgebruikte joins in SQL:

1. INNERLIJKE VERBINDING

2. LINKS DOE MEE

3. RECHTS DOE MEE

4. VOLLEDIG DOE MEE

1. BINNENAANSLUITING

Een INNER JOIN haalt records op die overeenkomende waarden hebben in beide tabellen.

Laten we een voorbeeld nemen van een database met artiesten en albums en u wilt alle artiest- en albumcombinaties vinden. Dit is de INNER JOIN:

SELECT *
FROM artists AS a
INNER JOIN albums AS b
ON a.artist_id = b.artist_id;

Met een INNER JOIN worden alleen de rijen met overeenkomende waarden in de opgegeven velden in de resultaten geretourneerd.

2. LINKS MEEDOEN

Een LEFT JOIN wordt ook wel een LEFT OUTER JOIN genoemd. Het retourneert alle records uit de linkertabel en de overeenkomende records uit de rechtertabel. Als er geen overeenkomst is in de rechtertabel, bevat het resultaat NULL-waarden.

Om bijvoorbeeld een lijst te krijgen van alle artiesten en hun respectieve albums (als ze die hebben), kun je een LEFT JOIN gebruiken:

SELECT *
FROM artists AS a
LEFT JOIN albums AS b
ON a.artist_id = b.artist_id;

Deze query retourneert alle artiesten, zelfs als er geen albums aan hen zijn gekoppeld in de albumtabel.

3. RECHTS DOE MEE

Een RIGHT JOIN wordt ook wel een RIGHT OUTER JOIN genoemd. Het retourneert alle records uit de rechtertabel en de overeenkomende records uit de linkertabel. Als er geen overeenkomst is in de linkertabel, bevat het resultaat NULL-waarden.

Om bijvoorbeeld informatie te krijgen over alle albums en hun bijbehorende artiesten (als ze bestaan), zou je een RIGHT JOIN gebruiken:

SELECT *
FROM artists AS a
RIGHT JOIN albums AS b
ON a.artist_id = b.artist_id;

Deze query retourneert alle albums, zelfs als ze geen geassocieerde artiesten in de artiestentabel hebben.

4. VOLLEDIG DOE MEE

Een FULL JOIN wordt ook wel een FULL OUTER JOIN genoemd. Het combineert de resultaten van zowel LEFT- als RIGHT-joins. Met andere woorden, het retourneert alle rijen van de linker- en rechtertabellen en vult de ontbrekende waarden in met NULL's als er geen overeenkomst is.

Hier is een voorbeeld waarbij de tabellen met artiesten en albums worden gebruikt:

SELECT *
FROM artists AS a
FULL JOIN albums AS b
ON a.artist_id = b.artist_id;

Deze query retourneert alle rijen van beide tabellen en vult NULL's in waar er geen overeenkomst is in een van beide tabellen.

SQL-aggregatiefuncties

Geaggregeerde functies worden gebruikt om één resultaat te berekenen uit een reeks invoerwaarden. Ze worden "geaggregeerd" genoemd omdat ze meerdere invoer gebruiken en een enkele uitvoer retourneren. De meest voorkomende zijn:

1. GRAAF

2. SOM

3. AVG

4. MAX

5. MIN

1. COUNT-functie

Met de functie COUNT kunt u het aantal rijen in een queryresultaat tellen. U kunt deze aggregatiefunctie gebruiken om het totale aantal records in een tabel te bepalen of het aantal records dat aan specifieke criteria voldoet.

Hier is een voorbeeld:

SELECT COUNT(*) FROM employees;

Deze query zal het totale aantal werknemers in de tabel 'werknemers' teruggeven. Houd er rekening mee dat het toevoegen van een WHERE-clausule uw resultaten kan verfijnen:

SELECT COUNT(*) FROM employees WHERE department = 'HR';

2. SUM-functie

The SUM function calculates the total sum of a numeric column. It’s useful when you need to calculate the total value of a particular numeric field. For example, this query returns the total sum of all employee salaries:

SELECT SUM(salary) FROM employees;

3. AVG Function

The AVG function computes the average value of a numeric column. This function is helpful when you want to find the average of a particular numeric field. For instance, this query returns the average salary of all employees:

SELECT AVG(salary) FROM employees;

4. MAX Function

The MAX function finds the maximum value of a column. This is often used to find the highest value in a numeric field or the most recent date in a datetime field. For example, this query returns the highest salary:

SELECT MAX(salary) FROM employees;

5. MIN Function

Lastly, the MIN function helps you find the minimum value of a column. For example, this query returns the lowest salary:

SELECT MIN(salary) FROM employees;

Remember, you can use WHERE clauses in these queries and JOIN with multiple tables.

Common String Functions

Here are the most common string functions that are found in most SQL dialects (the exact syntax can vary):

1. LEN or LENGTH(string): Returns the length of a string.

2. UPPER(string): Converts a string to upper case.

3. LOWER(string): Converts a string to lower case.

4. SUBSTR or SUBSTRING(string, start, length): Extracts a portion from a string.

5. TRIM(string): Removes leading and trailing spaces from a string.

6. LTRIM(string): Removes leading spaces from a string.

7. RTRIM(string): Removes trailing spaces from a string.

Common Numeric Functions

Here are the most common numeric functions that are found in most SQL dialects (the exact syntax can vary):

1. ABS(number): Returns the absolute value of a number.

2. ROUND(number, decimal_places): Rounds a number to a certain number of decimal places.

3. FLOOR(number): Rounds down the number to the nearest integer.

4. CEIL or CEILING(number): Rounds up the number to the nearest integer.

5. RAND(): Returns a random float value from 0 to 1.

6. MOD(n, m): Returns the remainder of n divided by m.

7. POWER(base, exponent): Raises a number to the power of another number.

8. LOG(number): Returns the natural logarithm of a number.

Common Date Functions

Here are the most common date functions that are found in most SQL dialects (the exact syntax can vary):

1. NOW(): Returns the current date and time.

2. DATE(datetime): Extracts the date part of a date or datetime expression.

3. TIME(datetime): Extracts the time part of a date or datetime expression.

4. YEAR(date): Returns the year part.

5. MONTH(date): Returns the month part.

6. DAY(date): Returns the day of the month part.

7. HOUR(time): Returns the hour part from a time.

8. MINUTE(time): Returns the minute part from a time.

9. SECOND(time): Returns the second part from a time.

GROUP BY And HAVING

When working with SQL queries, you may want to further summarize and filter your aggregated data. The GROUP BY and HAVING clauses provide this functionality.

1. Group By Clause

The GROUP BY clause allows you to group rows that share the same values in specified columns. It is commonly used with aggregate functions. This is the syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2;

For example, if you want to calculate the total sales amount for each product category, this is the query:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category;

TIP: Combining GROUP BY and COUNT is a good way of finding duplicate values.

2. Having Clause

If you want to filter the aggregated results further, you can use the HAVING clause. The syntax is:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition;

If you want to find product categories with total sales of more than $1,000,000, you would write:

SELECT product_category, SUM(sales_amount)
FROM sales_data
GROUP BY product_category
HAVING SUM(sales_amount) > 1000000;

Quick Tips

• Always use the GROUP BY clause before the HAVING clause.

• The SELECT statement can only contain specified column names, aggregate functions, constants, and expressions.

• When using the HAVING clause, filter conditions should be applied to the aggregate functions rather than directly to the grouped columns.

By understanding and properly applying the GROUP BY and HAVING clauses, you can better organize and analyze your data using SQL.

Subqueries

A subquery is also known as an inner or nested query. This is a query embedded within another SQL statement (such as a SELECT statement) or even inside another subquery.

Subqueries allow you to retrieve data based on the output of another query. The most common operators used with subqueries are:

1. IN

2. EXISTS

3. ANY

4. ALL

1. IN Operator

The IN operator tests if a value is within a set of values generated by the inner query. The syntax for using the IN operator with a subquery is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name FROM other_table);

This returns rows from the outer query where the specified column value matches any of the values provided by the subquery.

Suppose you have an employee table and a departments table. You want to find employees who work in departments based at the head office. Here is a sample query

SELECT first_name, last_name
FROM employee
WHERE department IN (SELECT department FROM departments
                     WHERE location = "HQ");

For a more in-depth look, check out our article on the SQL WHERE IN syntax.

2. EXISTS Operator

The EXISTS operator checks if there is at least one row resulting from the subquery. You can use the EXISTS operator to filter rows based on the existence of related data in another table. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM other_table WHERE condition);

When the subquery returns at least one row, the EXISTS operator returns true, and the relevant rows from the outer query are included in the result.

3. ANY Operator

The ANY operator is used to compare a value to any value in a set of values provided by a subquery. It’s commonly used with comparison operators like =, <, >, <=, or >=.

This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY (SELECT column_name FROM other_table WHERE condition);

This will return rows from the outer query where the specified column value meets the condition against any value from the subquery.

4. ALL Operator

The ALL operator compares a value to all values within a set of values provided by a subquery. The conditions must be true for every value in the subquery’s result. This is the syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL (SELECT column_name FROM other_table WHERE condition);

This returns rows from the outer query only if the specified column value satisfies the condition against all values in the subquery’s output.

Data Manipulation (DML)

Data Manipulation Language (DML) is a sub-language within SQL for managing and updating data. The most common statements are:

1. INSERT

2. UPDATE

3. DELETE

1. INSERT Statement

The INSERT statement allows you to insert rows into a table. Here’s the basic syntax:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

For example, if you want to insert a new row into a ‘users’ table with columns ‘id’, ‘name’, and ’email’, you would use the following query:

INSERT INTO users (id, name, email)
VALUES (1, 'John Doe', '[email protected]');

2. UPDATE Statement

The UPDATE statement allows you to modify existing row data in a table. This is the syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

For example, if you want to update the email address of a user with the id ‘1’ in the ‘users’ table, your query would look like this:

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

TIP: remember to include a WHERE clause to avoid updating all rows in the table by mistake.

3. DELETE Statement

The DELETE statement allows you to remove rows from a table. Here’s the syntax:

DELETE FROM table_name
WHERE condition;

Als u bijvoorbeeld een gebruiker met de id '1' uit de tabel 'users' wilt verwijderen, ziet uw zoekopdracht er als volgt uit:

DELETE FROM users
WHERE id = 1;

TIP : neem altijd een WHERE-clausule op om te specificeren welke rijen moeten worden verwijderd en om te voorkomen dat alle rijen in de tabel worden verwijderd.

Databasebeheer met DDL

Data Definition Language (DDL) is de SQL-subtaal die wordt gebruikt voor het maken en wijzigen van tabellen en de database zelf. De meest voorkomende DDL-statements zijn:

1. CREËREN

2. WIJZIGEN

3. DRUPPEL

1. CREATE-verklaring

Met de instructie CREATE kunt u nieuwe database-objecten maken, zoals nieuwe tabellen, weergaven of indexen. Wanneer u een nieuwe tabel maakt, moet u de kolommen, hun gegevenstypen en eventuele beperkingen definiëren.

Hier is een voorbeeld van het maken van een ordertabel:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    product VARCHAR(255) NOT NULL,
    customer_id INT NOT NULL
);

TIP: kies geschikte gegevenstypen en beperkingen om de gegevensintegriteit in uw tabellen te waarborgen.

Raadpleeg ons artikel over basisbewerkingen voor SQL-tabellen voor een meer gedetailleerd overzicht .

2. ALTER-verklaring

De ALTER-instructie helpt u bij het wijzigen van bestaande database-objecten. Veelvoorkomende toepassingen zijn onder meer:

• kolommen toevoegen, wijzigen of verwijderen.

• beperkingen toevoegen aan of verwijderen uit een bestaande tabel.

• primaire en externe sleutels toevoegen.

VOEG EEN NIEUWE KOLOM TOE

ALTER TABLE users ADD COLUMN age INTEGER;

Wijzig het gegevenstype van een kolom

ALTER TABLE users ALTER COLUMN age TYPE FLOAT;

Laat een kolom vallen

ALTER TABLE users DROP COLUMN age;

Voeg een unieke beperking toe

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

Voeg een externe sleutel toe tussen tabellen

ALTER TABLE users ADD FOREIGN KEY (country_id) REFERENCES Country(country_id);

3. DROP-verklaring

Met de DROP-instructie kunt u database-objecten zoals tabellen, weergaven of indexen verwijderen. Gebruik het met de nodige voorzichtigheid, omdat het het opgegeven object en al zijn gegevens permanent zal verwijderen.

Hier is een voorbeeld:

DROP TABLE users;

TIP : Zorg ervoor dat u de juiste back-ups hebt voordat u een DROP-instructie uitvoert.

Als je meer wilt weten over datamodellering, bekijk dan deze video:

Transacties

Transacties spelen een cruciale rol bij het handhaven van de database- integriteit , vooral wanneer meerdere gerelateerde bewerkingen gelijktijdig worden uitgevoerd. Er zijn drie fundamentele bewerkingen bij het afhandelen van transacties:

1. BEGINNEN

2. VERBINDEN

3. TERUGROLLEN

1. BEGIN

De BEGIN-instructie geeft het begin van een transactie aan. Bij het uitvoeren van deze opdracht stelt u een startpunt vast voor uw set SQL-instructies.

BEGIN;

2. TOEZEGGING

Gebruik de COMMIT-instructie om uw wijzigingen af ​​te ronden en in de database te bewaren. Dit zorgt ervoor dat alle bewerkingen binnen de transactie succesvol en permanent worden uitgevoerd.

COMMIT;

Hier is een voorbeeld van een volledige transactie waarbij gebruik wordt gemaakt van het klassieke voorbeeld van het overboeken van geld tussen rekeningen:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

3. ROLLBACK

Bij het werken met transacties is het ook essentieel om te weten hoe u wijzigingen ongedaan kunt maken als er een fout optreedt. De ROLLBACK-instructie maakt alle wijzigingen ongedaan die sinds het begin van de transactie zijn aangebracht:

ROLLBACK;

Hier is een voorbeeld van een transactie met foutafhandeling met behulp van ROLLBACK:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
IF @@ERROR <> 0
    ROLLBACK;
ELSE
    COMMIT;

Laatste gedachten

Zoals u in dit SQL-spiekbriefje hebt gezien, biedt SQL een groot aantal opdrachten en functies waarmee u gegevens in een relationele database kunt maken, manipuleren en opvragen.

Van de basiscommando's zoals SELECT, INSERT, UPDATE en DELETE, tot de meer complexe constructies zoals JOIN's en subquery's, tot de samengevoegde functies die we hebben behandeld, SQL biedt de flexibiliteit om een ​​breed scala aan gegevenstaken uit te voeren.

Kom steeds terug naar dit spiekbriefje om uw reis naar het leren van SQL te versnellen !