SQL-huijauslehti: Pikaopas välttämättömiin komentoihin

Ohjelmoijana tai data-analyytikkona joudut usein työskentelemään suurten tietomäärien parissa. Yksi tehokkaimmista käytettävissä olevista työkaluista on SQL (Structured Query Language).

Tämä SQL-huijauslehti sisältää yleisimmät SQL-objektit ja -komennot, mukaan lukien tietotyypit, DML- ja DDL-käskyt, aggregaattifunktiot, merkkijono- ja päivämääräfunktiot sekä alikyselyt.

Se on suunniteltu nopeaksi ja käteväksi viitteeksi, jota voit käyttää aina, kun olet vuorovaikutuksessa relaatiotietokannan kanssa.

Jos olet kuin me ja haluat ladata ja tulostaa tai tallentaa huijausarkin tietokoneellesi, voit ladata sen alta.

Sisällysluettelo

• SQL-tietotyypit
  • 1. Numeeriset tyypit
  • 2. Päivämäärä- ja aikatyypit
  • 3. Merkkijonotyypit
• SELECT lausunto
• WHERE-lauseke
• TILAA lausekkeen mukaan
• Useiden taulukoiden yhdistäminen SQL:ssä
  • 1. SISÄLIITOS
  • 2. LEFT JOIN
  • 3. OIKEA LIITTYMINEN
  • 4. LIITTY TÄYSIN
• SQL-koontifunktiot
  • 1. COUNT-toiminto
  • 2. SUMMA-funktio
  • 3. AVG-toiminto
  • 4. MAX-toiminto
  • 5. MIN-toiminto
• Yleiset merkkijonofunktiot
• Yleiset numeeriset funktiot
• Yhteiset päivämäärätoiminnot
• RYHMÄT JA OTTAMINEN
  • 1. Ryhmittele lausekkeen mukaan
  • 2. Ottaa lauseke
  • Pikavinkkejä
• Alakyselyt
  • 1. IN-operaattori
  • 2. OLEMASSA Operaattori
  • 3. KAIKKI operaattorit
  • 4. KAIKKI operaattori
• Data Manipulation (DML)
  • 1. LISÄÄ lausunto
  • 2. PÄIVITYSlausunto
  • 3. POISTA lausunto
• Tietokannan hallinta DDL:llä
  • 1. LUO lausunto
  • 2. ALTER-lausunto
    • LISÄÄ UUSI SARAKE
    • Muokkaa sarakkeen tietotyyppiä
    • Pudota sarake
    • Lisää ainutlaatuinen rajoitus
    • Lisää vierasavain taulukoiden väliin
  • 3. DROP-lausunto
• Tapahtumat
  • 1. ALOITA
  • 2. SITO
  • 3. PALAUTUS
• Lopulliset ajatukset

SQL-tietotyypit

Tiettyjen tietokannanhallintajärjestelmien (DBMS) tietotyypit voivat vaihdella (esim. Microsoft SQL Server vs MySQL ). Useimmissa järjestelmissä on kuitenkin useita. Voit jakaa nämä kolmeen luokkaan:

1. Numeerinen

2. Päivämäärä ja aika

3. merkkijono

1. Numeeriset tyypit

Nämä ovat yleisimmät numeeriset tyypit:

• INTEGER : Kokonaisluku ilman desimaalipistettä.

• PIENI : Pienempi kokonaislukualue

• BIGINT : Suurempi kokonaislukualue.

• DESIMAALI(p, s) tai NUMERO(p, s) : Esimerkiksi desimaali(5,2) sopii 123,45:een.

• REAL : Liukuluku, jonka tarkkuus on vähintään 6 desimaalilukua.

• FLOAT(n) : Liukuluku, jonka tarkkuus on vähintään n numeroa.

2. Päivämäärä- ja aikatyypit

• DATE : Päivämäärän arvo, tyypillisesti muodossa VVVV-KK-PP.

• TIME : Aika-arvo, tyypillisesti muodossa 'HH:MM:SS'.

• DATETIME tai TIMESTAMP : Päivämäärän ja kellonajan arvojen yhdistelmä.

3. Merkkijonotyypit

• CHAR(n) : Kiinteäpituinen merkkijono, jossa on n merkkiä.

• VARCHAR(n) tai CHARACTER VARYING(n) : Vaihtuvapituinen merkkijono.

• TEKSTI : Vaihtuvapituinen merkkijono, jonka enimmäispituus määrittää DBMS:n.

SELECT lausunto

SELECT-käskyä käytetään tietojen hakemiseen yhdestä tai useammasta taulukosta. Voit määrittää sarakkeet, jotka haluat noutaa ja mistä taulukosta. SELECT-peruslause näyttää tältä:

SELECT column1, column2
FROM table;

Jos haluat noutaa kaikki tietueet sarakkeista 'name' ja 'country_id' kaupunkitaulukosta, SQL-kyselysi näyttää tältä:

SELECT name, country_id
FROM city;

WHERE-lauseke

WHERE-lauseen avulla voit suodattaa SELECT-käskyn tuloksia tiettyjen ehtojen perusteella.

SELECT column1, column2
FROM table
WHERE condition;

Jos haluat hakea tietueita kaupunkitaulukosta, jossa väestömäärä on suurempi kuin 1 000 000, kyselysi näyttää tältä:

SELECT name, population
FROM city
WHERE population > 1000000;

TILAA lausekkeen mukaan

ORDER BY -lause antaa sinun lajitella SELECT-käskyn tulokset yhden tai useamman sarakkeen mukaan. Voit lajitella tulokset nousevaan (ASC) tai laskevaan (DESC) järjestykseen:

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

Esimerkiksi, jos haluat hakea tietueita kaupunkitaulukosta, jotka on lajiteltu väestön mukaan laskevassa järjestyksessä, kyselysi näyttää tältä:

SELECT name, population
FROM city
ORDER BY population DESC;

Useiden taulukoiden yhdistäminen SQL:ssä

SQL:ssä on neljä yleisesti käytettyä liitosta:

1. SISÄLIITTYMINEN

2. LEFT LIITY

3. OIKEA LIITTYMINEN

4. TÄYSI LIITTYMINEN

1. SISÄLIITOS

INNER JOIN hakee tietueet, joilla on vastaavat arvot molemmissa taulukoissa.

Otetaan esimerkki artistien ja albumien tietokannasta, ja haluat löytää kaikki esittäjien ja albumien yhdistelmät. Tämä on SISÄINEN LIITTYMINEN:

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

INNER JOIN -toiminnolla vain rivit, joiden arvot vastaavat määritetyissä kentissä, palautetaan tuloksiin.

2. LEFT JOIN

LEFT JOIN tunnetaan myös nimellä LEFT OUTTER JOIN. Se palauttaa kaikki tietueet vasemmasta taulukosta ja vastaavat tietueet oikeasta taulukosta. Jos oikeasta taulukosta ei löydy vastaavuutta, tulos sisältää NULL-arvoja.

Jos haluat esimerkiksi saada luettelon kaikista artisteista ja heidän albumeistaan ​​(jos heillä on sellaisia), voit käyttää LEFT JOIN -toimintoa:

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

Tämä kysely palauttaa kaikki artistit, vaikka heihin ei olisi liitetty yhtään albumia albumitaulukossa.

3. OIKEA LIITTYMINEN

RIGHT JOIN tunnetaan myös nimellä RIGHT OUTTER JOIN. Se palauttaa kaikki tietueet oikeasta taulukosta ja vastaavat tietueet vasemmasta taulukosta. Jos vasenta taulukkoa ei löydy, tulos sisältää NULL-arvoja.

Esimerkiksi saadaksesi tietoa kaikista albumeista ja niihin liittyvistä artisteista (jos sellaisia ​​on), käytä RIGHT JOIN -toimintoa:

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

Tämä kysely palauttaa kaikki albumit, vaikka niillä ei olisi artistitaulukossa liittyviä artisteja.

4. LIITTY TÄYSIN

FULL JOIN tunnetaan myös nimellä FULL OUTTER JOIN. Se yhdistää sekä LEFT- että RIGHT-liitosten tulokset. Toisin sanoen se palauttaa kaikki rivit vasemmasta ja oikeasta taulukosta ja täyttää puuttuvat arvot NULL-arvoilla, kun vastaavuutta ei ole.

Tässä on esimerkki artistien ja albumien taulukoista:

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

Tämä kysely palauttaa kaikki rivit molemmista taulukoista ja täyttää NULL-arvot, jos kummassakaan taulukossa ei ole vastaavuutta.

SQL-koontifunktiot

Aggregaattifunktioita käytetään yksittäisen tuloksen laskemiseen syötearvojen joukosta. Niitä kutsutaan "aggregaatiksi", koska ne ottavat useita tuloja ja palauttavat yhden lähdön. Yleisimmät ovat:

1. KREIVI

2. SUMMA

3. AVG

4. MAX

5. MIN

1. COUNT-toiminto

COUNT-funktion avulla voit laskea kyselytuloksen rivien määrän. Tämän koontifunktion avulla voit määrittää taulukon tietueiden kokonaismäärän tai tiettyjä ehtoja vastaavien tietueiden määrän.

Tässä on esimerkki:

SELECT COUNT(*) FROM employees;

Tämä kysely palauttaa työntekijöiden kokonaismäärän "työntekijät" -taulukossa. Muista, että WHERE-lauseen lisääminen voi tarkentaa tuloksiasi:

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

2. SUMMA-funktio

SUMMA-funktio laskee numeerisen sarakkeen kokonaissumman. Siitä on hyötyä, kun sinun on laskettava tietyn numeerisen kentän kokonaisarvo. Esimerkiksi tämä kysely palauttaa kaikkien työntekijöiden palkkojen kokonaissumman:

SELECT SUM(salary) FROM employees;

3. AVG-toiminto

AVG-funktio laskee numeerisen sarakkeen keskiarvon. Tämä toiminto on hyödyllinen, kun haluat löytää tietyn numeerisen kentän keskiarvon. Esimerkiksi tämä kysely palauttaa kaikkien työntekijöiden keskipalkan:

SELECT AVG(salary) FROM employees;

4. MAX-toiminto

MAX-funktio etsii sarakkeen maksimiarvon. Tätä käytetään usein etsimään suurin arvo numerokentästä tai viimeisin päivämäärä päivämäärä-aika-kentässä. Esimerkiksi tämä kysely palauttaa korkeimman palkan:

SELECT MAX(salary) FROM employees;

5. MIN-toiminto

Lopuksi MIN-funktio auttaa sinua löytämään sarakkeen vähimmäisarvon. Esimerkiksi tämä kysely palauttaa pienimmän palkan:

SELECT MIN(salary) FROM employees;

Muista, että voit käyttää WHERE-lauseita näissä kyselyissä ja JOIN-toimintoa useiden taulukoiden kanssa.

Yleiset merkkijonofunktiot

Tässä ovat yleisimmät merkkijonofunktiot, joita löytyy useimmista SQL-murteista (tarkka syntaksi voi vaihdella):

1. PITUUS tai PITUUS(merkkijono) : Palauttaa merkkijonon pituuden.

2. UPPER(merkkijono) : Muuntaa merkkijonon isoiksi kirjaimiksi.

3. LOWER(merkkijono) : Muuntaa merkkijonon pieniksi kirjaimiksi.

4. ALAJÄRJESTELMÄ tai ALAJÄRJESTELMÄ(merkkijono, alku, pituus) : Poimii osan merkkijonosta.

5. TRIM(merkkijono) : Poistaa merkkijonosta alku- ja loppuvälilyönnit.

6. LTRIM(merkkijono) : Poistaa välilyönnit merkkijonosta.

7. RTRIM(merkkijono) : Poistaa välilyönnit merkkijonosta.

Yleiset numeeriset funktiot

Tässä ovat yleisimmät numeeriset funktiot, joita löytyy useimmista SQL-murteista (tarkka syntaksi voi vaihdella):

1. ABS(luku) : Palauttaa luvun itseisarvon.

2. ROUND(luku, desimaali_paikat) : Pyöristää luvun tiettyyn määrään desimaaleja.

3. FLOOR(luku) : Pyöristää luvun alaspäin lähimpään kokonaislukuun.

4. CEIL tai CEILING(luku) : Pyöristää luvun ylöspäin lähimpään kokonaislukuun.

5. RAND() : Palauttaa satunnaisen kelluvan arvon 0 - 1.

6. MOD(n, m) : Palauttaa n:n jäännöksen jaettuna m:llä.

7. TEHO(kantaosa, eksponentti) : Nostaa luvun toisen luvun potenssiin.

8. LOG(luku) : Palauttaa luvun luonnollisen logaritmin.

Yhteiset päivämäärätoiminnot

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 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;

For example, if you want to delete a user with the id ‘1’ from the ‘users’ table, your query would look like this:

DELETE FROM users
WHERE id = 1;

TIP: always include a WHERE clause to specify which rows to delete and to avoid deleting all the rows in the table.

Database Management With DDL

Data Definition Language (DDL) is the SQL sub-language used for creating and altering tables and the database itself. The most common DDL statements are:

1. CREATE

2. ALTER

3. DROP

1. CREATE Statement

The CREATE statement allows you to create new database objects, such as new tables, views, or indexes. When creating a new table, you need to define the columns, their data types, and any constraints.

Here’s an example of creating an orders table:

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

TIP: choose appropriate data types and constraints to ensure data integrity in your tables.

For a more detailed look, check out our article on .

2. ALTER Statement

The ALTER statement helps you modify existing database objects. Common uses include:

• adding, modifying, or dropping columns.

• adding or removing constraints from an existing table.

• adding primary and foreign keys.

ADD A NEW COLUMN

ALTER TABLE users ADD COLUMN age INTEGER;

Modify a Column’s Data Type

ALTER TABLE users ALTER COLUMN age TYPE FLOAT;

Drop A Column

ALTER TABLE users DROP COLUMN age;

Add A Unique Constraint

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE(email);

Add a foreign key between tables

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

3. DROP Statement

The DROP statement allows you to remove database objects like tables, views, or indexes. Use it with caution, as it will permanently delete the specified object and all its data.

Here’s an example:

DROP TABLE users;

TIP: Ensure you have proper backups in place before executing a DROP statement.

If you want to learn more about data modeling, check out this video:

Transactions

Transactions play a crucial role in maintaining database integrity, especially when multiple related operations are executed concurrently. There are three fundamental operations in handling transactions:

1. BEGIN

2. COMMIT

3. ROLLBACK

1. BEGIN

The BEGIN statement signifies the beginning of a transaction. Upon executing this command, you’re establishing a starting point for your set of SQL statements.

BEGIN;

2. COMMIT

To finalize your changes and persist them in the database, use the COMMIT statement. This ensures that all the operations within the transaction are executed successfully and permanently.

COMMIT;

Here’s an example of a full transaction using the classic example of transferring funds between accounts:

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

3. ROLLBACK

When working with transactions, it’s also essential to know how to undo changes when an error occurs. The ROLLBACK statement reverses all the changes made since the start of the transaction:

ROLLBACK;

Here’s an example of a transaction with error handling using 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;

Final Thoughts

As you’ve seen in this SQL cheat sheet, SQL offers a multitude of commands and functions that allow you to create, manipulate, and query data in a relational database.

From the foundational commands like SELECT, INSERT, UPDATE, and DELETE, to the more complex constructs like JOINs and subqueries, to the aggregate functions we’ve covered, SQL provides the flexibility to handle a wide range of data tasks.

Keep coming back to this cheat sheet to speed up your !