2021-10-05 ========== .. sectnum:: SQL --- SQL staat voor Structured Query Language, en het is een veelgebruikte taal om met databases te spreken. Je kan deze taal dan ook gebruiken om met vele databases te werken zoals b.v.: - PostgreSQL: populaire open source database - MySQL/MariaDB: populaire open source database - SQLite: populaire embedded database - IBM Db2 - Oracle database SQLite ------ Omwille van de eenvoud, gebruiken we hier SQLite. Download SQLite hier: https://www.sqlite.org/download.html De directe link: https://www.sqlite.org/2021/sqlite-tools-win32-x86-3360000.zip Start de commandline shell "sqlite3.exe". SQL - create database --------------------- Start de sqlite3 executable:: sqlite3 Je krijgt nu een prompt:: sqlite> Je kan nu een database aanmaken met het '.open' commando:: sqlite> .open moviedb.sqlite3 SQL - create table --------------------- We kunnen een database maken door een parameter mee te geven op de commandline:: sqlite> create table movie (name varchar(100) primary key, year int); "Create table" geeft aan dat je een nieuwe tabel wil aanmaken. "Movie" is de naam van deze nieuwe tabel. Het lijstje tussen de haakjes geeft de kolommen in deze tabel aan. De eerste kolom heet 'name' en bevat maximaal 100 letters. De tweede kolom heet 'year' en bevat een geheel getal. "Primary key" geeft aan dat dit de uniek identificerende kolom is. Er kunnen met deze tabel-structuur geen twee films met dezelfde naam opgeslagen worden. Opgave ^^^^^^ Maak een database aan met naam 'seriesdb.sqlite3'. Maak hierin een tabel met naam 'series' met drie kolommen: - name: naam van de serie, maximaal 100 letters - year: jaartal, integer - season: seizoen, integer Opgave ^^^^^^ Gebruik het '.help' commando om een overzicht van de beschikbare commando's te krijgen. Opgave ^^^^^^ Toon de tabellen met het ".tables" commando. SQL - insert rows --------------------- We kunnen rijen aan de tabel toevoegen met het 'INSERT' statement:: sqlite> insert into movie (name, year) values('The Matrix', 1999); Als alle kolommen ingevuld worden mag dit ook verkort worden tot:: sqlite> insert into movie values('The Matrix Reloaded', 2003); Hier voegden we twee films toe aan de tabel 'movie'. Opgave ^^^^^^ Open je database met naam 'seriesdb.sqlite3'. Voeg 3 series toe met behulp van het 'insert' statement: - Fargo, 2014, 1 - Peaky Blinders, 2013, 1 - Misfits, 2009, 1 SQL - list rows --------------- Met het 'select' statement kunnen we rijen in de tabel tonen:: sqlite> select * from movie; The Matrix|1999 The Matrix Reloaded|2003 Het sterretje geeft aan dat we alle kolommen willen zien. We kunnen ook expliciet opgeven welke kolommen we willen zien:: sqlite> select name from movie; The Matrix The Matrix Reloaded sqlite> select year from movie; 1999 2003 Opgave ^^^^^^ Open je database met naam 'seriesdb.sqlite3'. Toon alle series in je tabel 'series' met behulp van het 'select' statement. SQL - primary key uniqueness ---------------------------- We proberen nu de twee Dune films toe te voegen, maar dit faalt:: sqlite> insert into movie values('Dune', 1984); sqlite> insert into movie values('Dune', 2021); Error: UNIQUE constraint failed: movie.name De reden hiervoor is dat we de kolom 'name' als primary key instelden bij het aanmaken van de tabel. Deze primary key dient uniek te zijn. Opgave ^^^^^^ Open je database met naam 'seriesdb.sqlite3'. Voeg het tweede seizoen van 'Fargo' toe, met release jaar 2015. Merk op dat dit niet lukt. SQL - ID kolom -------------- Om dit probleem te kunnen omzeilen, kunnen we een andere kolom toevoegen die we als uniek beschouwen ('primary key'), waardoor de kolom 'name' niet meer uniek hoeft te zijn:: sqlite3 moviedb2.sqlite3 sqlite> create table movie (id integer primary key, name varchar(100), year int); sqlite> insert into movie values(1, 'The Matrix', 1999); sqlite> insert into movie values(2, 'The Matrix Reloaded', 2003); sqlite> insert into movie values(3, 'Dune', 1984); sqlite> insert into movie values(4, 'Dune', 2021); Opgave ^^^^^^ Maak een nieuwe database aan met naam 'series2db.sqlite3'. Maak hierin een tabel met naam 'series' met drie kolommen: - id: integer, primary key - name: naam van de serie, maximaal 100 letters - year: jaartal, integer - season: seizoen, integer Voeg nu het eerste en het tweede seizoen van de serie 'Fargo' toe. SQL - Autoincrement -------------------- Als er geen waarde voor de ID opgegeven wordt, maakt SQLite3 automatisch een nieuwe ID aan:: sqlite3 moviedb2.sqlite3 sqlite> insert into movie (name, year) values('Fargo', 1996); sqlite> insert into movie (name, year) values('Pulp Fiction', 1994); Opgave ^^^^^^ Open je database met naam 'series2db.sqlite3'. Voeg nu de volgende series toe, gebruik makend van de verkorte notatie: - Peaky Blinders, 2013, 1 - Misfits, 2019, 1 SQL - Update ------------ Met behulp van het "update" statement kunnen we bestaanden rijen in de tabel aanpassen:: sqlite> select * from movie2; 1|The Matrix|1999 2|The Matrix Reloaded|2003 3|Dune|1984 4|Dune|2021 5|Fargo|1996 6|Pulp Fiction|1994 sqlite> update movie set year=2021 where id=2; Dit zou het jaartal van de film met ID 2 op 2021 instellen:: sqlite> select * from movie2; 1|The Matrix|1999 2|The Matrix Reloaded|2021 3|Dune|1984 4|Dune|2021 5|Fargo|1996 6|Pulp Fiction|1994 sqlite> update movie set year=2021; Dit zou het jaartal van alle films op 2021 instellen:: sqlite> select * from movie2; 1|The Matrix|2021 2|The Matrix Reloaded|2021 3|Dune|2021 4|Dune|2021 5|Fargo|2021 6|Pulp Fiction|2021 Opgave ^^^^^^ Open je database met naam 'series2db.sqlite3'. Corrigeer het jaartal van de serie Misfits aan naar 2009. SQL - Delete ------------ Met het 'DELETE' statement kunnen rijen uit een tabel verwijderd worden:: sqlite> delete from movie2 where id > 2; sqlite> select * from movie2; 1|The Matrix|2021 2|The Matrix Reloaded|2021 Opgave ^^^^^^ Open je database met naam 'series2db.sqlite3'. Verwijder "Peaky Blinders' uit de tabel series. Django dbshell --------------- Kopieer de sqlite3.exe executable naar de map waarin je manage.py zich bevindt. Voer vervolgens het volgende commando uit:: python manage.py dbshell Je krijgt nu een database-shell te zien waarin je SQL commando's kan gebruiken om de data te bekijken of aan te passen. Opgave ^^^^^^ Gebruik het ".tables" commando om te zien welke tabellen beschikbaar zijn. Opgave ^^^^^^ Gebruik het "select" statement om alle tweets in je database te bekijken. Opgave ^^^^^^ Gebruik het "update" statement om het aantal likes van je eerste tweet in te stellen op 10000.