Sql

Links

• https://linuxwiki.de/MySQL
• https://wiki.hamatoma.de/index.php?title=Sql

Zielsetzung

Einführung in die Datenbanksprache Structured Query Language. Damit kann man Datenbanktabellen erstellen, Daten eintragen, ändern, löschen und abfragen.

Es gibt Standards für SQL, der Kurs formuliert die Beispiele im Dialekt von MariaDB (oder MySql).

Relationen (Tabellen)

Die Welt besteht aus Entitäten: materielle und ideelle Dinge, Sachen oder Ideen. Beispiele: Person Farbe Abteilung.

Ist eine Entität unteilbar, wird sie Attribut genannt. Beispiele: Farbe, Identifikationsnummer, Nachname

Eine Relation, auch Datenbanktabelle genannt, ist ein Menge von Datensätzen (auch Zeilen genannt). Jeder Datensatz beschreibt eine normalerweise teilbare Entität.

Jeder Datensatz besteht aus (für jeden Datensatz gleichen) Menge von Attributen, auch Spalten genannt.

In Relationen können beliebig viele Entitäten gespeichert werden.

Beispiel

Eine Adresse ist eine Entität, die aus den Attributen Name, Vorname, Ort, Postleitzahl, Straße und Hausnummer besteht.

Name	Vorname	Ort	PLZ	Straße	Nr
Müller	Hans	Berlin	10122	Hauptgasse	10
Maier	Ulricke	Augsburg	80123	Siemensalee	3

Diese Relation hat 2 Datensätze (Zeilen).

Erstellen einer Relation

Wir legen eine Datei test.sql an:

create table users (
   id integer primary key auto_increment,
   name varchar(64),
   email varchar(255),
   password varchar(255),
   last_login timestamp
);

• Konvention: Wir vergeben immer eine "technische Id" als Identifikation. Weil alle anderen Attribute können sich evt. ändern.
• Konvention: Tabellennamen sind immer in Mehrzahl.
• id integer primary key auto_increment: Das Attribut "id" ist eine Zahl, die den Benutzer identifiziert, das wird Primärschlüssel genannt. Die Zahl wird automatisch generiert (auto_increment)
• name varchar(64): Das Attribut heißt name, der Datentyp ist eine Zeichenkette mit maximaler Länge 64.
• last_login timestamp: Mit diesem Attribut wird der Zeitpunkt (Datum + Uhrzeit) des letzten Logins festgehalten.

SQL-Befehle in Datenbank bringen

mysql -utester -pTopSecret dbtest1 < test.sql

Datentypen

• Texttypen:
  • char(<length>) Zeichenkette mit fester Länge. Beispiel deutsche Postleitzahl: plz char(5)
  • text: Variable Zeichenkette bis 65535 Zeichen.
  • longtext Variable Zeichenkette bis 4 GByte
  • char(<length>): Zeichenkette mit variabler Länge. Beispiel name varchar(64)
• Zahltypen:
  • decimal(<length>) oder decimal(<length>, <precisision>): Dezimalzahl mit maximaler Länge von <length> und maximal <precisision> Nachkommastellen. Beispiel: price decimal(13,2)
  • float : Gleitpunktzahlen Beispiel: article_weight float
  • integer oder int: eine ganze Zahl. Beispiel: birth_year int
• Datumstypen:
  • date: nur Datum. Beispiel: birthday date
  • datetime: Datum und Uhrzeit. Beispiel: term datetime
  • timestamp: Zeitpunkt mit Genauigkeit 1 Sekunde, gilt nur ab 1.1.1970. Beispiel: created_at timestamp
• Sonstiges:
  • bool: ein Wahrheitswert. Beispiel: active bool

Datenbank und Benutzer anlegen

Wie eine Datenbank angelegt wird, hängt vom Datenbanksystem ab.

Wir legen eine MySql-Datenbank "dbtest1" an:

sudo mysqladmin -u root create dbtest1

Wir brauchen noch eine Datenbankbenutzer "tester":

sudo -u root mysql <<EOS
GRANT ALL on dbtest1.* to 'tester'@'localhost' identified by 'TopSecret' with grant option;
EOS

Mit der Datenbank arbeiten

sudo mysql -u tester -pTopSecret dbtest1

Daten einfügen

insert into users (name, email, last_login) values
('Adam', 'a@gmx.com', '2023-09-27 07:44'),
('Bob', 'b@gmx.de', '2023-09-27 07:44'),
('Charly', 'c@gmx.com', '2023-09-27 07:44'),
('Eve', 'e@gmx.net', '2023-09-28 13:44'),
('Diana', 'd@gmx.de', '2023-09-28 13:44');

• Es muss spezifiziert werden, welche Attribute (Spalten) belegt werden sollen: hier sind es 3 Spalten
• Dann folgt eine Liste von Einträgen.
• Zeichenketten werden in einfache Anführungszeichen gesetzt.
• Ein Datum/Zeitpunkt wird mit der amerikanischen Schreibweise angegeben: <year>-<month>-<day> <hour>:<minute>

Daten ändern

update users set
last_login = NOW(),
password = 'PleaseChange'
where password is null;

• Wir ändern in Tabelle users.
• die Spalte last_login wird mit dem aktuellen Datum belegt.
• das Passwort wird auf 'PleaseChange' gesetzt
• aber nur die Datensätze, in denen noch kein Passwort gesetzt ist.

Daten anzeigen

select * from users;
select id, name, email from users where id in (2, 3);
select count(*) from users;
select id, name, email from users where email like '%gmx.de';
select concat(name, ': ', email) from users;
select min(last_login) from users;

• Es wird alles in der Tabelle users angezeigt.
• Es werden nur die Spalten id, name, und email von den Datensätzen angezeigt, bei denen id den Wert 2 oder 3 hat
• Es wird die Anzahl Datensätze von users angezeigt
• Es werden id, name, und email von den Datensätzen angezeigt, deren email gmx.de enthält. '%' ist der universelle Platzhalter (beliebiger String)
• Es können Zeichenketten mit der Funktion concat() zusammengesetzt werden: Eine Ausgabe wäre "Adam: a@gmx.com"
• Es wird der jüngste Eintrag der Spalte last_login angezeigt

Bezüge zwischen Relationen

Tabelle für Kurse

CREATE TABLE courses (
   id integer primary key auto_increment,
   name varchar(64),
   leader_id integer,
   description text 
);
INSERT INTO courses (name, leader_id, description) VALUES
 ('Französisch für Anfänger', 1, 'Max. 5 Teilnehmer'),
 ('Italienisch für Anfänger', 2, 'Max. 5 Teilnehmer'),
 ('Französisch für Fortgeschrittene', 1, 'Max. 3 Teilnehmer'),
 ('Italienisch für Fortgeschrittene', 2, 'Max. 3 Teilnehmer');

• Ein Kurs hat einen Namen, eine Beschreibung und einen Leiter, der ein Verweis auf die Tabelle users ist.

Anzeige der Attribute von Attributen aus verschiedenen verbundenen Tabellen

SELECT c.id, c.name, u.name as leader 
 FROM courses c
 JOIN users u ON u.id=c.leader_id;

• Um die 2 Tabellen einfacher ansprechen zu können: Die Tabellen bekommen kurze Namen: ... FROM courses c ... JOIN users u ...
• c.id gibt die Id des Kurses aus, c.name dessen Name.
• u.name ist der Name des Benutzers, dessen Id mit c.leader_id übereinstimmt. Die Überschrift lautet "leader" wegen "as leader"
• JOIN users u ON u.id=c.leader_id
  • Es wird eine Verknüpfung ("Join") mit der Tabelle users durchgeführt. Die Verknüpfungsbedingung ("ON ...") lautet:
  • die Id in der Tabelle users stimmt mit dem Attribut leader_id der Tabelle courses überein

Speichern der Mitglieder

• Beispiel: Eine Kurs kann 2 Teilnehmer haben, aber auch 200, oder ein Online-Kurs auch 2 Millionen.
• Wenn also in der Tabelle courses Platz für alle Teilnehmer sein soll, müssten wir 2 Millionen Attribute anlegen.
• Das geht besser:
• Wir legen eine Tabelle members an. In der können dann problemlos beliebig viele Einträge gespeichert sein.

CREATE TABLE members (
   id integer primary key auto_increment,
   user_id integer,
   course_id integer);
INSERT INTO members (user_id, course_id) VALUES
  (3, 1),
  (4, 1),
  (3, 3),
  (4, 2),
  (5, 2);

• member bekommt ein Attribut course_id, in dem die Id des Kurses gespeichert wird.
• Wenn wir alle Teilnehmer eines Kurses mit der Id 3 haben wollen, dann fragen wir einfach nach den Datensätzen von members, in denen das Attribut course_id den Wert 3 hat.

SELECT * FROM members WHERE course_id=3;

• Eleganter mittels Joins:

SELECT u.name, c.name
  FROM users u
  JOIN members m ON u.id=m.user_id
  JOIN courses c ON c.id=m.course_id=c.id
WHERE c.name like 'Franz%Anf%';

• Wir wollen den Namen des Mitglieds, also das Attribut name der Tabelle users: SELECT u.name from users u...
• Es gibt zwei verknüpfte Tabellen: die Tabelle courses und members. Daher zwei Joins.
• members ist mit users verknüpft über ON u.id=m.user_id
• courses ist mit members verknüft über ON c.id=m.course_id=c.id
• Wir wählen den Kurs über ein Suchmuster: WHERE c.name like 'Franz%Anf%'

Unterabfragen (Subselects)

Es geht auch noch anders: Gib mir alle Teilnehmer des Kurses mit dem Namen "Italienisch für Anfänger":

SELECT * FROM members WHERE course_id IN (SELECT id FROM courses WHERE name = 'Italienisch für Anfänger');

• Das Datenbanksystem führt zuerst die Unterabfrage SELECT id FROM courses WHERE name = 'Italienisch für Anfänger' aus.
• Das Ergebnis ist eine Menge von Ids, im Beispiel die einzelne Id 3.
• Dann wird die Abfrage ausgeführt: SELECT * FROM members WHERE course_id IN (3);

Aufgabe

• Definiere eine Relation "notes" für Notizen. Attribute: Id, title, body, created at, user_id.
• Trage ein paar Notizen ein
• Gib alle Notiztitel und die Namen des zugehörigen Benutzers aus, deren Inhalt (body) ein "e" enthält.