TASIOMIND.DEV — OPERATIONAL▸▸▸FULL STACK DEVELOPER @ GWQ SERVICEPLUS AG▸▸▸FOUNDER — K8SGPT.AI▸▸▸OPEN SOURCE: ACTIVE▸▸▸DISTRIBUTED SYSTEMS / KUBERNETES / AI▸▸▸RUST + GO + PYTHON▸▸▸FIELD TESTED / STATUS — NOMINAL▸▸▸LOCATION: EUROPE/BERLIN▸▸▸TASIOMIND.DEV — OPERATIONAL▸▸▸FULL STACK DEVELOPER @ GWQ SERVICEPLUS AG▸▸▸FOUNDER — K8SGPT.AI▸▸▸OPEN SOURCE: ACTIVE▸▸▸DISTRIBUTED SYSTEMS / KUBERNETES / AI▸▸▸RUST + GO + PYTHON▸▸▸FIELD TESTED / STATUS — NOMINAL▸▸▸LOCATION: EUROPE/BERLIN▸▸▸
← databases

Database Foundations

February 20, 2015

Grundlagen zu relationalen Datenbanken: Datenbank anlegen, Tabellen, CRUD und einfache Abfragen.

Datenbank anlegen

code
CREATE DATABASE shop
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

SCHEMA ist ein Synonym:

code
CREATE SCHEMA shop;

Datenbank aktivieren:

code
USE shop;

Tabellen anlegen

code
CREATE TABLE actors (
  name VARCHAR(50)
);

Mit mehreren Spalten:

code
CREATE TABLE movies (
  title VARCHAR(200),
  year INT
);

Beispiel mit Typen:

code
CREATE TABLE IF NOT EXISTS products (
  sku INT,
  name VARCHAR(255),
  img VARCHAR(255),
  price DECIMAL(10,2),
  paypal VARCHAR(32)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

NULL vs NOT NULL

  • NULL: Wert darf fehlen
  • NOT NULL: Wert ist Pflicht

CRUD

CRUDSQL
CreateINSERT
ReadSELECT
UpdateUPDATE
DeleteDELETE

INSERT

code
INSERT INTO movies (title, year)
VALUES ("Avatar", 2009);

Mehrere Zeilen:

code
INSERT INTO movies (title, year)
VALUES ("Avatar", 2009), ("The Godfather", NULL);

SELECT

code
SELECT * FROM movies;
SELECT title, year FROM movies;

WHERE (Filter)

code
SELECT * FROM movies WHERE year = 1999;
SELECT * FROM movies WHERE year != 1999;

Operatoren:

OperatorBedeutung
=gleich
!=ungleich
> >=groesser
< &lt;=kleiner

AND / OR / BETWEEN

code
SELECT * FROM movies
WHERE year < 1999 AND title = "The Matrix";

SELECT * FROM movies
WHERE year BETWEEN 1988 AND 2000;

LIKE (Teilstring)

code
SELECT * FROM movies WHERE title LIKE "%godfather%";

ORDER BY

code
SELECT * FROM movies ORDER BY year DESC, title ASC;

LIMIT / OFFSET

code
SELECT * FROM movies LIMIT 10 OFFSET 20;

UPDATE

code
UPDATE movies SET year = 2015 WHERE title = "Avatar 2";

DELETE

code
DELETE FROM movies WHERE title = "Avatar Reloaded";

Tipps

  • SELECT vor UPDATE/DELETE pruefen
  • Keywords gross schreiben fuer Lesbarkeit
  • utf8mb4 fuer Emoji/Unicode

Ressourcen