Μάθετε SQL με αυτές τις 5 εύκολες συνταγές

Το SQL (Structured Query Language) είναι μια ισχυρή και εκφραστική γλώσσα για την αντιμετώπιση δεδομένων από σχεσιακές βάσεις δεδομένων. Αλλά μπορεί να φαίνεται τρομακτικό για τους άγνωστους.

Οι "συνταγές" που θα μοιραστώ μαζί σας σήμερα είναι μερικά βασικά παραδείγματα από μια απλή βάση δεδομένων. Αλλά τα μοτίβα που θα μάθετε εδώ μπορούν να σας βοηθήσουν να γράψετε ακριβή ερωτήματα. Αυτά θα σας κάνουν να νιώσετε σαν τα δεδομένα ισοδύναμου ενός MasterChef σε χρόνο μηδέν.

Σημείωση σχετικά με τη σύνταξη: Τα περισσότερα από τα παρακάτω ερωτήματα γράφονται με το στυλ που χρησιμοποιείται για το PostgreSQL από τη γραμμή εντολών psql. Διαφορετικοί κινητήρες SQL μπορούν να χρησιμοποιούν ελαφρώς διαφορετικές εντολές.

Τα περισσότερα από τα παρακάτω ερωτήματα πρέπει να λειτουργούν στους περισσότερους κινητήρες χωρίς τροποποίηση, αν και ορισμένοι κινητήρες ή εργαλεία GUI ενδέχεται να απαιτούν παράλειψη εισαγωγικών γύρω από τα ονόματα των πινάκων και των στηλών.

Πιάτο 1: Επιστροφή όλων των χρηστών που δημιουργήθηκαν εντός ενός συγκεκριμένου εύρους ημερομηνιών

Συστατικά

  • ΕΠΙΛΕΓΩ
  • ΑΠΟ
  • ΟΠΟΥ
  • ΚΑΙ

Μέθοδος

SELECT * FROM "Users" WHERE "created_at" > "2020-01-01" AND "created_at" < "2020-02-01";

Αυτό το απλό πιάτο είναι μια ευέλικτη βάση. Εδώ επιστρέφουμε χρήστες που πληρούν δύο συγκεκριμένες προϋποθέσεις συνδέοντας τους WHEREόρους με μια ANDδήλωση. Μπορούμε να το επεκτείνουμε περαιτέρω με περισσότερες ANDδηλώσεις.

Ενώ το παράδειγμα εδώ είναι για ένα συγκεκριμένο εύρος ημερομηνιών, τα περισσότερα ερωτήματα απαιτούν κάποια συνθήκη για να φιλτράρουν τα δεδομένα χρήσιμα.

Πιάτο 2: Βρείτε όλα τα σχόλια για ένα βιβλίο, συμπεριλαμβανομένου του χρήστη που έκανε το σχόλιο

(Νέα) Συστατικά

  • ΣΥΜΜΕΤΟΧΗ

Μέθοδος

SELECT "Comments"."comment", "Users"."username" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" WHERE "Comments"."bookId" = 1;

Αυτό το ερώτημα προϋποθέτει την ακόλουθη δομή πίνακα:

Ένα από τα πράγματα που μπορούν να αρχίσουν να συγχέουν τους αρχάριους με το SQL είναι η χρήση των JOIN για την εύρεση δεδομένων από σχετικούς πίνακες.

Το ERD (Διάγραμμα σχέσης οντοτήτων) παραπάνω δείχνει τρεις πίνακες, Χρήστες, Βιβλία και Σχόλια και τους συσχετισμούς τους.

Κάθε πίνακας έχει μια idοποία είναι τολμηρή στο διάγραμμα για να δείξει ότι είναι το πρωτεύον κλειδί για τον πίνακα. Αυτό το πρωτεύον κλειδί είναι πάντα μια μοναδική τιμή και χρησιμοποιείται για να ξεχωρίζει τα αρχεία σε πίνακες.

Τα πλάγια ονόματα στηλών userIdκαι bookIdστον πίνακα Σχόλια είναι ξένα κλειδιά, που σημαίνει ότι είναι το πρωτεύον κλειδί σε άλλους πίνακες και χρησιμοποιούνται εδώ για την αναφορά αυτών των πινάκων.

Οι σύνδεσμοι στο παραπάνω ERD δείχνουν επίσης τη φύση των σχέσεων μεταξύ των 3 πινάκων.

Το άκρο ενός σημείου στον σύνδεσμο σημαίνει «ένα» και το διαχωριστικό άκρο στον σύνδεσμο σημαίνει «πολλά», οπότε ο πίνακας χρήστη έχει σχέση «με ένα προς πολλά» με τον πίνακα σχολίων.

Ένας χρήστης μπορεί να έχει πολλά σχόλια, για παράδειγμα, αλλά ένα σχόλιο μπορεί να ανήκει μόνο σε έναν μόνο χρήστη. Τα βιβλία και τα σχόλια έχουν την ίδια σχέση στο παραπάνω διάγραμμα.

Το ερώτημα SQL πρέπει να έχει νόημα με βάση αυτά που γνωρίζουμε τώρα. Επιστρέφουμε μόνο τις ονομαζόμενες στήλες, δηλαδή τη στήλη σχολίων από τον πίνακα σχολίων και το όνομα χρήστη από τον πίνακα συνδεδεμένων χρηστών (βάσει του ξένου κλειδιού αναφοράς). Στο παραπάνω παράδειγμα περιορίζουμε την αναζήτηση σε ένα μόνο βιβλίο, βάσει του ξένου κλειδιού στον πίνακα σχολίων.

Πιάτο 3: Μετρήστε τον αριθμό των σχολίων που προστέθηκαν από κάθε χρήστη

(Νέα) Συστατικά

  • ΜΕΤΡΩ
  • ΟΠΩΣ ΚΑΙ
  • ΟΜΑΔΑ ΑΠΟ

Μέθοδος

SELECT "Users"."username", COUNT("Comments"."id") AS "CommentCount" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" GROUP BY "Users"."id";

Αυτό το μικρό ερώτημα κάνει μερικά ενδιαφέροντα πράγματα. Το πιο εύκολο να κατανοηθεί είναι η ASδήλωση. Αυτό μας επιτρέπει να μετονομάσουμε αυθαίρετα και προσωρινά τις στήλες στα δεδομένα που επιστρέφονται. Εδώ μετονομασία του προέρχεται στήλη, αλλά είναι επίσης χρήσιμη όταν έχετε πολλές idστήλες, αφού μπορείτε να μετονομάσετε τα πράγματα όπως userIdή commentIdκαι ούτω καθεξής.

Η COUNTδήλωση είναι μια συνάρτηση SQL που, όπως θα περίμενε, μετράει τα πράγματα. Εδώ μετράμε τον αριθμό των σχολίων που σχετίζονται με έναν χρήστη. Πώς λειτουργεί; Λοιπόν, GROUP BYείναι το σημαντικό τελικό συστατικό.

Ας φανταστούμε εν συντομία ένα ελαφρώς διαφορετικό ερώτημα:

SELECT "Users"."username", "Comments"."comment" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id";

Παρατηρήστε, χωρίς καταμέτρηση ή ομαδοποίηση. Θέλουμε απλώς κάθε σχόλιο και ποιος το έκανε.

Η έξοδος μπορεί να μοιάζει με αυτό:

|----------|-----------------------------| | username | comment | |----------|-----------------------------| | jackson | it's good, I liked it | | jackson | this was ok, not the best | | quincy | excellent read, recommended | | quincy | not worth reading | | quincy | I haven't read this yet | ------------------------------------------

Now imagine we wanted to count Jackson's and Quincy's comments - easy to see at a glance here, but harder with a larger dataset as you can imagine.

The GROUP BY statement essentially tells the query to treat all the jackson records as one group, and all the quincy records as another. The COUNT function then counts the records in that group and returns that value:

|----------|--------------| | username | CommentCount | |----------|--------------| | jackson | 2 | | quincy | 3 | ---------------------------

Dish 4: Find users that have not made a comment

(New) Ingredients

  • LEFT JOIN
  • IS NULL

Method

SELECT "Users"."username" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId" WHERE "Comments"."id" IS NULL;

The various joins can get very confusing, so I won't unpack them here. There is an excellent breakdown of them here: Visual Representations of SQL Joins, which also accounts for some of the syntax differences between various flavours or SQL.

Let's imagine an alternate version of this query quickly:

SELECT "Users"."username", "Comments"."id" AS "commentId" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId";

We still have the LEFT JOIN but we've added a column and removed the WHERE clause.

The return data might look something like this:

|----------|-----------| | username | commentId | |----------|-----------| | jackson | 1 | | jackson | 2 | | quincy | NULL | | abbey | 3 | ------------------------

So Jackson is responsible for comments 1 and 2, Abbey for 3, and Quincy has not commented.

The difference between a LEFT JOIN and an INNER JOIN (what we've been calling just a JOIN until now, which is valid) is that the inner join only shows records where there are values for both tables. A left join, on the other hand, returns everything from the first, or left, table (the FROM one) even if there is nothing in the right table. An inner join would therefore only show the records for Jackson and Abbey.

Now that we can visualize what the LEFT JOIN returns, it's easier to reason about what the WHERE...IS NULL part does. We return only those users where the commentId is a null value, and we don't actually need the null value column included in the output, hence its original omission.

Dish 5: List all comments added by each user in a single field, pipe separated

(New) Ingredients

  • GROUP_CONCAT or STRING_AGG

Method (MySQL)

SELECT "Users"."username", GROUP_CONCAT("Comments"."comment" SEPARATOR " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

Method (Postgresql)

SELECT "Users"."username", STRING_AGG("Comments"."comment", " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

This final recipe shows a difference in syntax for a similar function in two of the most popular SQL engines.

Here is a sample output we might expect:

|----------|---------------------------------------------------| | username | comments | |----------|---------------------------------------------------| | jackson | it's good, I liked it | this was ok, not the best | | quincy | excellent read, recommended | not worth reading | ----------------------------------------------------------------

We can see here that the comments have been grouped and concatenated / aggregated, that is joined together in a single record field.

BonAppetit

Τώρα που έχετε μερικές συνταγές SQL για να επιστρέψετε, να γίνετε δημιουργικοί και να σερβίρετε τα δικά σας πιάτα δεδομένων!

Μου αρέσει να σκέφτομαι WHERE, JOIN, COUNT, GROUP_CONCATόπως το αλάτι, λίπος, οξύ, θερμότητα του μαγειρέματος βάσης δεδομένων. Μόλις μάθετε τι κάνετε με αυτά τα βασικά στοιχεία, είστε έτοιμοι να κυριαρχήσετε.

Εάν αυτή ήταν μια χρήσιμη συλλογή, ή έχετε άλλες αγαπημένες συνταγές για κοινή χρήση, ρίξτε μου ένα σχόλιο ή ακολουθήστε στο Twitter: @JacksonBates.