SQLGrundlagen

Erste Schritte

Befehl
SQL Queries
Varia
DROP
DROP TABLE users
INSERT
INSERT INTO users (id, username) VALUES (“4″,”Sascha”)
INSERT INTO users (username) VALUES (‘Frank’)
UPDATE
UPDATE users SET username = ‘Fritz’ WHERE ‘users’.’id’ = 3;
UPDATE users SET username = ‘Andrea’ WHERE id = 2
UPDATE users SET username = ‘Chris’, id = 8 WHERE username = ‘Frank’
SELECT
SELECT * FROM `users`
SELECT * FROM `users` WHERE id = 2
SELECT * FROM `users` WHERE `username` = ‘tina’
SELECT `id` FROM `users`
SELECT `username` FROM `users`
SELECT `id`, `username` FROM `users` WHERE username = ‘max’
DELETE
DELETE FROM `users` WHERE `id` = 10
DELETE FROM `users` WHERE `username` = ‘Ben’
ALTER
ALTER TABLE `users` ADD `active` BOOLEAN NOT NULL AFTER `username`;

Challenge!

Du bekommst das Gerücht zu hören, dass ein Studierender mit dem
Vornamen „Lorenz“ ein Stipendium bekommen soll. Dich interessiert jetzt
natürlich, ob du eindeutig sagen kannst, welcher Studierender das
Stipendium bekommen soll.

Aufgabe: Wie viele Studierende mit dem Vornamen „Lorenz“ gibt es in der Datenbank?

SELECT * FROM `students` WHERE firstname = ‘Lorenz’

 

Challenge

Erinnerst du dich an den Bauernhof, den wir als Ort vor paar Lektionen hinzugefügt haben? 

Studierende haben sich beschwert, dass ihnen nicht genau klar ist, wo sie den Bauernhof genau finden.

Aufgabe: Benenne daher den Ort „Bauernhof“ in „Bauernhof (bei München)“ um.
Schreibe das entsprechende SQL-Kommando selbst, verwende dazu nicht die Bearbeiten-Funktion in phpmyadmin.

SELECT * FROM locations WHERE title = ‘Bauernhof’

UPDATE locations SET title = ‘Bauernhof (bei München)’ WHERE title = ‘Bauernhof’

 

 

SELECT Operationen

Befehl
SQL Queries
Varia
COUNT – Ergebnisse zählen
SELECT COUNT(*) FROM posts
SELECT COUNT(*) FROM `posts` WHERE title = ‘Hallo Welt’ 
MINIMUM
MAXIMUM
DURCHSCHNITT
SELECT MIN(views), MAX(views) FROM posts
SELECT AVG(views), MIN(views), MAX(views) FROM posts
WHERE
SELECT * FROM `students` WHERE age > 20 OR age <= 21
SELECT COUNT(*), MIN(age), MAX(age), AVG(age) FROM `students` WHERE age >= 19 AND age < 25
LIKE (Platzhalter)
% = Platzhalter für Text
_ = Platzhalter für nur ein Zeichen
SELECT * FROM posts WHERE title LIKE ‘Hallo Welt%’
SELECT * FROM posts WHERE title LIKE ‘%W%’
SELECT * FROM posts WHERE title LIKE ‘Hallo Welt (_)’
WHERE IN
SELECT * FROM posts WHERE id IN(1,2,3,4,5) AND views IN(50,24)
SELECT * FROM posts WHERE id IN(1,2,3,4,5) AND views >= 50
Filtern mit WHERE Klammersetzung
SELECT * FROM posts WHERE (id = 4) OR (views < 100 OR views > 10000)
SELECT * FROM posts WHERE (id = 4) AND (views < 100)

 

Challenge

Die Universitätsleitung benötigt dringend ein paar statistische Daten
zu den Studierenden an der Uni. Dafür sollst du folgende Fragen
beantworten:

  1. Wie viele Studierenden gibt es?
  2. Wie viele Studierenden gibt es, die 20 Jahre alt sind?
  3. Wie alt ist der / die jüngste Studierende?
  4. Wie alt ist sind die Studierenden im Durchschnitt?
 

Schreibe dazu passende SQL-Abfragen, die dir die entsprechenden Fragen beantworten.

1. SELECT COUNT(*) FROM students
Resultat: 1001

2. SELECT COUNT(*) FROM students WHERE age = 20
Resultat: 128

3. SELECT MIN(age) FROM students
Resultat: 19

4. SELECT AVG(age) FROM students
Resultat: 21.51

 

Challenge!

Die Universitätsleitung benötigt dringend weitere Informationen aus
der Datenbank. Einerseits muss unser Bundesland ein paar Statistische
Daten zum Alter wissen.

Zudem ist bekannt geworden dass ein Studierender im Internet mit dem
Pseudonym “M” positive Dinge über die Uni geschrieben hat. Vielleicht
war das ein Studierender, dessen echter Name auch mit „M“ anfängt – wie
viele gibt es dort?

  1. Wie viele Studierende sind zwischen (einschließlich) 20 und (einschließlich) 22 Jahre alt?
  2. Bei wie vielen Studierenden beginnt der Vorname mit dem Buchstaben „M“?
 
Lösung:
1. SELECT COUNT(*) FROM students WHERE age >= 20 AND age <= 22
Resultat: 422
2. SELECT COUNT(*) FROM students WHERE firstname LIKE ‘M%’
Resultat: 97
 

 

 

Befehl
SQL Queries
Varia
SELECT AS – Spaltenname umbenennen
SELECT COUNT(*) AS Anzahl FROM `posts`
SELECT `id`,`title` AS Titulo,`content` AS Contento FROM `posts`
SELECT DISTINCT – unterschiedliche Inhalte aufzählen – doppelte Einträge weglassen
SELECT DISTINCT title FROM `posts`
SELECT COUNT(DISTINCT title) FROM `posts`
GROUP BY – zusammenfassen
SELECT title, COUNT(*) FROM `posts`
WHERE views > 3
GROUP BY title

SELECT title, COUNT(*) AS Anzahl FROM `posts`
WHERE views > 3
GROUP BY title

SELECT title, COUNT(*) AS Anzahl FROM `posts`
WHERE views > 3
GROUP BY title
HAVING Anzahl > 1
ORDER BY
Sortieren nach einer Spalte z.B. Aufsteigen, Absteigend etc.
SELECT * FROM posts ORDER BY views ASC
SELECT * FROM posts ORDER BY views DESC
SELECT * FROM posts ORDER BY views DESC, id DESC
LIMIT (2,2 = überspringe die ersten zwei Einträge) usw.
SELECT * FROM posts ORDER BY views DESC, id DESC LIMIT 2
SELECT * FROM posts ORDER BY views DESC, id DESC LIMIT 2,2
SELECT * FROM posts ORDER BY views DESC, id DESC LIMIT 0,2
Kombinationen
SELECT id, title, content, views, COUNT(*) AS count FROM posts GROUP BY title ORDER BY count DESC
CONCAT
SELECT CONCAT(title, ‘ lustig’), COUNT(*) AS count FROM posts GROUP BY title ORDER BY count DESC
SELECT CONCAT(title, ‘ lustig ‘, content), COUNT(*) AS count FROM posts GROUP BY title ORDER BY count DESC
SELECT CONCAT(title, ‘ ::: ‘, content) FROM posts
SELECT CONCAT(title, ‘ ::: ‘, content) AS inhalte FROM posts

Challenge

Wir benötigen weitere Informationen aus unserer Studierenden-Datenbank 🙂

  1. Wie viele unterschiedliche Vornamen gibt es?
  2. Schreibe eine neue Abfrage, die zu jedem Vornamen ausgibt, wie oft dieser in der Datenbank vorkommt
  3. Sortiere diese Ergebnisse nach der Häufigkeit. Welcher Vorname kommt am Häufigsten vor? Sollte es mehrere Vornamen geben die gleich häufig vorkommen, interessiert uns nur der Name, der zuerst im Alphabet vorkommt.
    • Wichtig: Formuliere hierzu eine SQL-Query unter Verwendung eines GROUP BY, ORDER BY und LIMITs, sodass wirklich nur der eine Vorname zurückgegeben wird!
 
Lösung:
1. SELECT COUNT(DISTINCT firstname) AS unterschiedliche_vornamen FROM students
Resultat: 638
2. SELECT firstname, COUNT(*) AS unterschiedliche_vornamen FROM students
GROUP BY firstname
3. SELECT firstname, COUNT(*) AS unterschiedliche_vornamen FROM students
GROUP BY firstname
ORDER BY unterschiedliche_vornamen DESC, firstname ASC
LIMIT 1
Resultat: Caroline
 

 

Challenge

Wir benötigen für einen Export aus der Datenbank den vollen Namen
(„Nachname, Vorname“) eines jeden Studierenden. Der volle Name soll dann
in dem Export in einer eigenen Spalte stehen – wir müssen also den
vollen Namen mit der CONCAT – Funktion aus dem Vor- / Nachnamen
berechnen.

Aufgabe: Stelle eine Abfrage an die Datenbank sodass der volle Name der Studierenden mit Hilfe der CONCAT – Funktion berechnet wird.

Lösung: SELECT CONCAT (`lastname`, ‘, ‘, `firstname`) FROM students

 

Datentypen

Befehl
SQL Queries
Varia
Einführung
VARCHAR
Ganzzahlen (TINYINT, INT, BIGINT, etc.)
ID
Primary Key = Eindeutiger Wert
AI = Auto Increment
BOOLEAN
0 oder 1
NULL
NULL = nicht definierts
Bsp: SELECT * FROM posts WHERE title IS NULL
SELECT * FROM posts WHERE title IS NOT NULL
BLOB, TINYBLOB
Für Binärdaten wie Angaben zu Bilder
CHAR, TEXT, LONGTEXT, ETC.
DATE, DATETIME, TIMESTAMP, ETC.

Challenge

Schaue dir die Tabelle „students“ an. Dort wird zu jedem Studierenden in der Spalte „age“ das Alter gespeichert. Bei einigen Studierenden fehlt allerdings diese Info. Bei wie vielen muss das Alter noch nachgetragen werden?

Lösung: SELECT COUNT(*) FROM `students` WHERE age IS NULL
Resultat: 152

Komplizierte Datenbankabfragen (Subqueries)

Befehl
SQL Queries
Varia
Einführung
Im Beispiel wird eine neue Tabelle “Kommentare” erstellt. Darin wird ein Fremdschlüssel zur Tabelle Post eingefügt.
Tabellenformat immer innoDB nehmen
Fremdschlüssel – cascade / restrict
Bsp. Post Eintrag kann nicht gelöscht werden, weil Kommentare über den Fremdschlüssel verlinkt sind. Mit Cascade werden der Post und die zugehörigen Kommentare auch gleich mitgelöscht.

Mit restrict geht das eben nicht. Kommentare werden nicht gelöscht und der Post kann so auch nicht gelöscht werden.

Fremdschlüssel so einstellen:
ON DELETE = CASCADE
ON UPDATE = RESTRICT
Subqueries
SELECT comments.title, comments.post_id,
(SELECT posts.title FROM posts WHERE posts.id = comments.post_id) AS post_title
FROM comments
weitere Subqueries
SELECT id, title, (SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id) AS count_comments FROM posts

SELECT id, title, (SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id) AS count_comments FROM posts WHERE (SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id) >= 1

SELECT * FROM posts WHERE EXISTS (SELECT COUNT(*) FROM comments WHERE comments.post_id = posts.id)

SELECT * FROM posts WHERE EXISTS (SELECT * FROM comments WHERE comments.post_id = posts.id)

Challenge

Schaue dir die Tabelle „courses_students“ an. Dort wird gespeichert, welcher Studierende in welchem Kurs eingeschrieben ist. Allerdings nur die ID des Studierenden / die ID des Kurses. Du sollst diese Auflistung aber ausdrucken, benötigst also den ausgeschriebenen Namen.

Aufgabe: Erstelle die Auflistung von den Spalten „Vorname des Studenten“, „Name des Kurses“, und verwende dazu entsprechende SUBSELECT – Befehle.

SELECT CONCAT(
    (SELECT firstname FROM students WHERE students.id = courses_students.student_id),
    ‘, ‘,
    (SELECT title FROM courses WHERE courses.id = courses_students.course_id)) AS student_und_seine_Kurse
FROM courses_students

oder in zwei Spalten

SELECT
    (SELECT firstname FROM students WHERE students.id = courses_students.student_id) AS student_name,
    (SELECT title FROM courses WHERE courses.id = courses_students.course_id) AS kurs_name
FROM courses_students

 

Challenge

Die Universitätsleitung fragt mal wieder ein paar Reports von uns an. Folgende Infos möchte sie wissen:

  1. Wie viele Studierende sind in keinem Kurs eingeschrieben?
  2. Wie viele Studierende besuchen genau 4 Kurse?

Schreibe dazu entsprechende Datenbankabfragen! 🙂

SELECT COUNT(*)
FROM `students`
WHERE NOT EXISTS (SELECT * FROM courses_students WHERE courses_students.student_id = students.id)
Resultat: 97

SELECT COUNT(*)
FROM `students`
WHERE (SELECT COUNT(*) FROM courses_students WHERE courses_students.student_id = students.id) = 4
Resultat: 259


Eigene Challenge
Zeige mir den Namen und ID des am meisten gebuchten Kurses an – wieviele in Anzahl Studiernde haben den Kurs gebucht.
Absteigend auflisten.

SELECT
    (SELECT title FROM courses WHERE courses.id = courses_students.course_id) AS course_title,
    (SELECT id FROM courses WHERE courses.id = courses_students.course_id) AS course_id,
    course_id,
    COUNT(*) AS gebucht_von_studierenden
FROM courses_students
GROUP BY course_id
ORDER BY gebucht_von_studierenden DESC


 

Share this: