SQL deel 1: SQL queries

SQL deel 1:SQL queries Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Database Architectuur • Een datamodel is het fundament van je toepassing: Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Sql, php, html enzo Front-End Client : Browser | Flash | Feedreader | … URL HTML + CSS (+ JavaScript ) | XML WebServer ( + scripting) : PHP | ASP | JSP SQL querie Datatabel (recordset ) DataBase ( gegevens ) : mySQL| Oracle | MSSQL Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Wat is een database • DB: Database • Een verzameling tabellen gevuld met data • DBMS: Database management systeem • Software om een database te creëren en te manipuleren • Oracle • mySQL • SQL-server • MS ACCESS • SQL: Structured Query Language • Taal om met een database te praten • Informatie selecteren • Informatie toevoegen, veranderen, wijzigen • Tabellen definiëren • Relaties tussen tabellen definiëren Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Client-Server architectuur • Onderscheid • Database-server ( SQL-server, Oracle ) • Database-cliënt ( MS ACCESS, web-server ) • NB: MS ACCESS kan ook zonder server direct met een database werken Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

SQL 1 : SELECT Het selecteren van data uit een enkelvoudige tabel Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Tabel: • rijen en kolommen • Datatype: • tekst, getal, datum, etc… • Selecties uit één tabel: SELECT query • Selectie criteria • Berekeningen • Statistieken • Groeperen Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

tabellen • Een database bestaat uit een verzameling tabellen Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Hoe zit data in een database? • Tabellen, bijvoorbeeld : telefoonnummers Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

tabel Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

rij Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

kolom Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

veld Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

‘Datatype’ • De informatie in de cellen van een kolom zijn allemaal van hetzelfde ‘type’ Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

datatype • Type informatie in een veld • Bepaalt het soort operaties dat je er op kan uitvoeren. • basis datatypen • Numeriek optellen, aftrekken, gemiddelde • Tekst aan_elkaar_plakken, substring, teveel_spaties_verwijderen, zoek_en_vervang • Boolean and, or, xor, not • Binair/BLOB geen operaties • *Datum/tijd * • *Lange tekst * Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Speciale datatype: datum/tijd • Datum/tijd • Eigenlijk ‘numeriek’: • aantal milliseconden sinds het jaar nul ( of sinds 1900 ) • Echter : • Speciale onregelmatige rekeneenheden ( maanden, schrikkeljaar, etc ) • Speciale manier van weergeven ‘1-1-2004’ of ‘2004-01-01’ of ‘1 jan 2004’ Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Speciaal datatype: lange tekst • Tekst kan worden onderverdeeld in • Korte tekst ( <255 karakters ) • Lange tekst ( >255 karakter ) • Slecht een puur technische onderscheidHeeft te maken met • Efficiënt gebruik van geheugenruimte. • Andere manier van zoeken Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Onzinnige datatypen in ACCESS • Valuta • Hyperlink Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Basis datatype • Type informatie in een veld • Bepaald het soort operaties dat je er op kan uitvoeren. • basis datatypen die in iedere database of programmeertaal op één of andere manier worden ondersteund: • Numeriek • Tekst • Boolean • Binair • Datum/tijd • Lange tekst Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Vraag? Welke data type is • Telefoonnummer? • Huisnummer? • Leeftijd? Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

En dan nu... SQL Structured Query Language • Een taal om database te bevragen en te manipuleren. • Als sinds begin 70 relatief stabiel • Ondersteund door ieder DBMS Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

‘SELECT’ query • Het selecteren van informatie uit een tabelbijvoorbeeld ‘product’ -tabel • selecteer productnamen en productprijzen uit de tabel ‘product’ • selecteer namen van producten uit de categorie 'boeken' • Selecteer alle producten die duurder zijn dan 5 euro • Selecteer een lijst met alle categorie-namen • Selecteer de gemiddelde prijs van de producten per categorie Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Verschillende soorten selecties • Rijen • Selecteren van kolommen (hst 2) • Sorteren (hst 3) • Filteren van rijen (hst 4,5,6) • Het berekenen van kolommen (hst 7,8) • Aggregaties • Lijst van voorkomende waarden ( ? ) • Statistieken ( hst 9 ) • Groeperen ( hst 10 ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Syntax van een eenvoudige SELECT • SELECT ID,naam, prijs FROM product WHERE categorie = ‘boek’ ORDER BY prijs • SELECT {kolomnamen} FROM {tabelnaam} WHERE {criteria} ORDER BY {kolomnaam} Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

SELECT resultaat • Het resultaat van een SQL query is altijd een tabel. • ID naam prijs8 groentensoep 2,157 appelsap 2,959 blik tonijn 3,15 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Syntax van een eenvoudige SELECTvariaties • SELECT naam, prijs FROM product WHERE categorie = ‘boek’ ORDER BY prijs • SELECT product.naam, product.prijs FROM product WHERE product.categorie = ‘boek’ ORDER BY product.prijs • SELECT [product.naam], [product.prijs] FROM product WHERE [product.categorie] = ‘boek’ ORDER BY [product.prijs] Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

SELECT WHERE • SELECT naam, prijs FROM product WHERE categorie = ‘eten’ AND prijs < 5 ORDER BY producent,prijs • Logische operaties: AND, OR, NOT • Vergelijkingen: =, >, < , <> , <=, <=, LIKE Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Berekeningen in SELECT (numeriek) • Selecteer de naam, prijs en prijs_plus_btw • SELECT naam, prijs, prijs + prijs* btw_tarief/100 AS prijs_met_btw FROM product • Naam prijs prijs_met_btwfiets 100 120brood 1 1,05 boek 30 36 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Berekeningen in SELECT (datum) • Selecteer de naam en de ‘leeftijd’ van een product • SELECT naam, beschikbaar_vanaf, DATEDIFF( 'yyyy' , beschikbaar_vanaf , DATE() ) AS leeftijdFROM product; WHERE beschikbaar_vanaf <= DATE() • Naam beschikbaar_vanaf leeftijdfiets 1-1-2003 1brood 1-2-1999 5 boek 3-2-2004 0 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

SELECT • Tot nu toe: • Selecteren van rijen uit een tabel • Kolommen • Rijen filteren • Berekende kolommen • Ook mogelijk: • Aggregatie functies • Gemiddelde prijs van de producten • Gemiddelde prijs per categorie • Lijst van alle categorieën • Lijst van producenten met meer dan één product Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

SELECT DISTINCT • SELECT DISTINCT categorie, producent FROM product • categorievervoervoedselboek Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

statistieken • SELECT COUNT(*) AS aantal, AVG(prijs) AS midPrijs, MAX(prijs) AS maxPrijs, MIN(prijs) AS minPrijsFROM product • Aantal midPrijs maxPrijs minPrijs11 1188 8000 2.05 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Statistieken per groep • SELECT categorie, COUNT(*) AS aantal, AVG(prijs) AS midPrijs, MAX(prijs) AS maxPrijs, MIN(prijs) AS minPrijsFROM productGROUP BY categorie • Categorie aantal gemPrijs maxPrijs minPrijsboek 5 30,38 50 20voedsel 3 2996 8000 300vervoer 3 2,75 3,15 2.05 Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

Herhaal: Verschillende soorten selecties • Rijen • Selecteren van kolommen (hst 2) • Sorteren (hst 3) • Filteren van rijen (hst 4,5,6) • Het berekenen van kolommen (hst 7,8) • Aggregaties • Lijst van voorkomende waarden ( ? ) • Statistieken ( hst 9 ) • Groeperen ( hst 10 ) Hogeschool van Amsterdam - Interactieve Media – Internet Development – Jochem Meuwese - j.meuwese@interactievemedia.hva.nl - http://oege.ie.hva.nl/~meuwj/ - http://hva.jochem.nl

SQL deel 1: SQL queries