SQL basics

November 17, 2017

Grundlagen fuer SQL in relationalen Datenbanken.

Regeln

SQL Statements enden mit ;
Keywords gross schreiben ist Konvention
* bedeutet "alle Spalten"

SELECT

code

SELECT title FROM movies;
SELECT id, title FROM movies;
SELECT * FROM movies;

WHERE (Filter)

code

SELECT * FROM movies WHERE id = 2;
SELECT * FROM movies WHERE title = 'The Kid';

Operatoren:

Operator	Bedeutung
`=`	gleich
`<>` / `!=`	ungleich
`>` `>=`	groesser
`<` `<=`	kleiner

AND / OR

code

SELECT * FROM movies
WHERE id = 1 AND genre = 'Comedy';

ORDER BY

code

SELECT title FROM movies ORDER BY duration;
SELECT title FROM movies ORDER BY duration DESC;

INSERT

code

INSERT INTO movies (id, title, genre, duration)
VALUES (5, 'The Circus', 'Comedy', 71);

UPDATE

code

UPDATE movies
SET genre = 'Romance'
WHERE id = 5;

DELETE

code

DELETE FROM movies WHERE id = 5;

Tabellen/DB verwalten

code

CREATE DATABASE chaplin;
DROP DATABASE chaplin;

CREATE TABLE movies (
  id INT,
  title VARCHAR(320),
  genre VARCHAR(160),
  duration INT
);

DROP TABLE movies;

ALTER TABLE

code

ALTER TABLE movies ADD COLUMN rating INT;
ALTER TABLE movies DROP COLUMN rating;

Tipps

SELECT vor UPDATE/DELETE testen
Ohne WHERE wirkt UPDATE/DELETE auf alle Zeilen