Les 2 Eenvoudige queries

Les 2 Eenvoudige queries

 

XAMP      

 

IAM SQL oefeningen Database  

   

Apache server ( http ) mySql server PHP myAdmin

phpmyAdmin Import : sql_producten.sql , sql_winkel.sql, sql_festival.sql

SAMS’ SQL in 10 minuten mySQL reference

   

Week 1 – 4 : SQL queries oefeningen Week 5 – 7 : datamodel ontwerp-opdracht

Front-End Client : Browser | Flash | Feedreader | App

URL

HTML + CSS (+ JavaScript ) | XML

WebServer ( + scripting/framework) : PHP | ASP | JSP SQL querie

Datatabel (recordset )/2D-array

DataBase ( gegevens ) : mySQL| Oracle | MSSQL

 

DB: Database  

 

Een verzameling tabellen gevuld met data

RDBMS: Relationeel 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

 

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

Wat is phpMyAdmin?   [ ] een database server   [ ] een database client

Het selecteren van data uit een enkelvoudige tabel

1 --------------------------  Tabel: rijen en kolommen   SELECT … FROM … WHERE …ORDER BY .. 2 -------------------------  Datatypen (numeriek, tekst, boolean, datum/tijd)   Diverse functies ( SQL reference ) 3 ---------------------------  Aggregaties en statistieken   GROUP BY   DISTINCT

 

Tabellen, bijvoorbeeld : telefoonnummers

 

Data met betrekking tot één entiteit (persoon)

 

Data-element

 

SELECT ID, naam, prijs FROM product

 

SELECT {kolomnamen} FROM {tabelnaam}

 

Het resultaat van een SQL query is altijd een tabel.

SELECT ID, naam, prijs FROM product!  

ID 1 7 8 9

naam Koken voor … appelsap groentensoep blik tonijn

prijs 20,10 2,95 2,15 3,10

 

SELECT id, naam, prijs FROM product WHERE categorie = ‘voedsel’ ORDER BY prijs

 

SELECT {kolomnamen} FROM {tabelnaam} WHERE {criteria} ORDER BY {kolomnaam}

SELECT ID, naam, prijs 
 FROM product
 WHERE categorie =‘voedsel’
 ORDER BY prijs!  

ID 8 7 9

naam groentensoep appelsap blik tonijn

prijs 2,15 2,95 3,15

 

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]

 

Doe je eerste SQL querie op de ‘producten’-database (t/m vraag 7)

SELECT …
 FROM …
 WHERE …
 ORDER BY …!

 

Type informatie in een veld  

 

Bepaalt het soort operaties dat je er op kan uitvoeren.

basis datatypen en operaties    

       

Numeriek Tekst

Boolean Binair/BLOB Datum/tijd Lange tekst

optellen, aftrekken, gemiddelde aan_elkaar_plakken, substring, teveel_spaties_verwijderen, zoek_en_vervang and, or, not geen operaties speciale rekenoperaties

Operaties:   Optellen , aftrekken, delen, percentages Vergelijkingen:   > , < , = Subtype: -integer -float

Operaties:   Zoek en vervang   Hoofdletters zetten   Aan elkaar plakken   Reguliere expressies (voor gevorderden ) Vergelijkingen:   = , LIKE, bevat, begint_met Subtype -  Varchar (255) -  Text

Operaties:   And, Or, Not Vergelijkingen:   IS

Operaties:   Er bestaan speciale databases met speciale functies voor speciale objecten als beeld en geluid Vergelijkingen:  

NB: Vaak niet in een database maar als aparte bestanden, wel bestandsnaam in de database

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’   Speciale operaties, zoals maand(), tijdsverschill()

Subtype: -  Date -  Time

Operaties   (optellen, aftrekken, vermengvuldigen)   YEAR(), MONTH(), DAY, WEEKDAY() HOUR(), MINUTE(),SECOND()   DATEDIFF(), TIMEDIFF()  

DATE_FORMAT()

Zie reference !

 

Type informatie in een veld  

 

Bepaald het soort operaties en vergelijkingen 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

Welk datatype moet je definiëren voor:      

Telefoonnummer ? Huisnummer ? Leeftijd ?

Numeriek? Tekst? Boolean? Datum/tijd?

Selecteer de naam, prijs en prijs_plus_btw  

SELECT naam, prijs, prijs + prijs* btw_tarief/100 AS prijs_met_btw FROM product

 

Naam fiets brood boek

prijs 100 1 30

prijs_met_btw 120 1,05 36

SELECT naam , 
 SUBSTRING( omschrijving,1,6 ) AS omschr
 FROM product" naam fiets appelsap blik tonijn

omschr een le sap va blik t

 

 

 

SELECT naam, prijs FROM product WHERE categorie = ‘eten’ AND prijs < 5 ORDER BY producent , prijs Logische operaties: AND, OR, NOT Vergelijkingen: =, >, < , <> , <=, >=, LIKE

Selecteer de naam en de ‘leeftijd’ van alle producten die u beschikbaar zijn.  

SELECT naam, beschikbaar_vanaf, DATEDIFF( 'yyyy' , beschikbaar_vanaf , DATE() ) AS leeftijd FROM product; WHERE beschikbaar_vanaf <= DATE()

 

Naam fiets brood boek

beschikbaar_vanaf 1-1-2003 1-2-1999 3-2-2004

leeftijd 1 5 0

‘geen’ ‘niks’ ‘afwezig’ ‘niet ingevuld’ ‘leeg’ Operatie:   IS NULL() test of een waarde NULL is.

 

Gebruik de (my)SQL reference :

 

Doe je de volgende SQL queries op de ‘producten’-database (t/m vraag 14)

SELECT …
 FROM …
 WHERE …
 ORDER BY …!

Dwz: Informatie uit een tabel als geheel (ipv uit een rij) Voorbeelden:   Gemiddelde prijs   Aantal producten   Hoogste aantal in voorrad

 

SELECT COUNT(*) AS aantal, AVG(prijs) AS midPrijs, MAX(prijs) AS maxPrijs, MIN(prijs) AS minPrijs FROM product

 

Aantal midPrijs maxPrijs minPrijs 11 1188 8000 2.05

 

SELECT categorie, COUNT(*) AS aantal, AVG(prijs) AS midPrijs, MAX(prijs) AS maxPrijs, MIN(prijs) AS minPrijs

FROM product GROUP BY categorie  

Categorie aantal boek 5 voedsel 3 vervoer 3

midPrijs 30,38 2996 2,75

maxPrijs 50 8000 3,15

minPrijs 20 300 2.05

GROUP BY Alleen in combinatie met een statistische functie bijv.: gemiddelde prijs per categorie ORDER BY Sortering van rijen op volgorde van iets

 

Rijen        

 

Selecteren van kolommen (hst 2) Sorteren (hst 3) Filteren van rijen (hst 4,5,6) Berekeningen in kolommen (hst 7,8)

Aggregaties/statistieken      

Statistieken ( hst 9 ) Groeperen ( hst 10 ) Lijst van voorkomende waarden ( ? )

 

SELECT DISTINCT categorie, FROM product

categorie vervoer voedsel boek Is hetzelfde als:   SELECT categorie FROM product
 GROUP BY categorie!

 

 

Doe de oefeningen uit de reader ! Maak met mySQLWorkbench een tabel met persoonsgegevens van klasgenoten

 

Meerdere tabellen in de database