Databases+SQL
1
Inhoud
Informatie verwerking en databases .............................................. 4
RDBMS en tabellen...................................................................... 8
SQL SELECT ............................................................................... 8
SQL WHERE ............................................................................. 10
SQL INSERT ............................................................................. 14
SQL UPDATE ............................................................................ 17
SQL DELETE ............................................................................. 18
SQL ORDER BY ......................................................................... 19
SQL Aggregate functies.............................................................. 21
SQL GROEP BY ......................................................................... 23
SQL HAVING ............................................................................ 24
SQL Tabel relaties, Primaire- en Foreign keys ............................... 26
De Primaire-key en Foreign key. .............................................. 27
Referentiële integriteit ............................................................ 27
Soorten relaties ..................................................................... 28
Benamingen van tabellen, velden en queries ................................ 30
Nulde normaalvorm (0NV) ....................................................... 31
Eerste normaalvorm (1NV) ...................................................... 31
Tweede normaalvorn (2NV) ..................................................... 31
Derde normaalvorm (3NV) ...................................................... 31
2
SQL UNION .............................................................................. 35
SQL genestelde queries ............................................................. 36
SQL CREATE............................................................................. 37
SQL ALTER............................................................................... 38
SQL Views................................................................................ 39
SQL Indexen ............................................................................ 40
SQL Hosting ............................................................................. 41
Bijlage: SQL Operators en functies (engels).................................. 42
3
Informatie verwerking en databases Een makkelijke manier om gegevens (feiten) bij te houden is een tabel (lijst). Op basis van gegevens, of wel feiten, kunnen verantwoorde en goed afgewogen keuzes gemaakt worden. Hiervoor moeten gegevens geïnterpreteerd worden, zodat de beschikbare gegevens informatie opleveren. Sinds de intrede van de computer worden die lijsten ook elektronisch bijgehouden. Als de lijst niet al te lang is, kan dit uitstekend met een EXCEL- spread sheet. Maar wanneer de lijsten complexer en groter worden, dan wordt het beheer en het correct invoeren van gegevens een hele klus. Zeker wanneer er ook nog andere lijsten komen waar gegevens uitgehaald moeten worden. Nog lastiger wordt het wanneer er foto’s, geluid en film beheerd moeten worden. Er ontstaan te veel situaties waarbij redundantie kan optreden (gegevens dubbel opgeslagen). Ook kunnen belangrijke gegevens per ongeluk worden verwijderd of gegevens kunnen elkaar gaan tegenspreken (inconsistentie), bijvoorbeeld: een persoon heeft twee verschillende woonplaatsen. Zulke fouten kunnen niet worden getolereerd als je het hebt over: de bedrijfsjaarcijfers, belastingbetalingen, relatiebeheer, verkoopcijfers, noem maar op… De ‘integriteit’ (juistheid) van de gegevens moet 100% zijn. ‘Kennis is macht’, mits deze kennis wordt onderbouwd met de interpretatie van de juiste gegevens (informatie).
4
Een database, ook wel gegevensbank, is een verzameling bij elkaar horende tabellen (lijsten), inclusief hun onderliggende relaties. Om gegevens uit de tabellen op te roepen is een Database Management Systeem (DBMS) nodig. Aan een DBMS worden vragen (queries) gesteld, zodat er een specifieke set gegevens wordt gepresenteerd. Een taal die gegevens uit databases kan halen is “SQL” (Structurated Query Language).
Er zijn naast de bestaande SQL standaarden enkele andere populaire relationele database-systemen zoals: MS SQL-Server, MySQL, Oracel, Sybase en DB2 die elk hun eigen SQL dialect hebben (ook bekend als SQL extentions of SQL implementations) van één van de twee SQL standaarden (ISO of ANSI). Er zijn vele SQL- implementaties en zogenaamde SQL-dialecten en SQL-extensions. Bijvoorbeeld: een ‘MS SQL-server’ specifieke versie van SQL wordt: ‘Transact SQL’ genoemd, de versie van het Orakel wordt: ‘PL/SQL’ genoemd en de versie van MSAccess wordt: ‘JET SQL’ genoemd. Deze reader is zo dialect onafhankelijk mogelijk gemaakt en de meeste voorbeelden kunnen op de meeste belangrijke SQL-implementaties worden toegepast. Om de in- en uitvoer te regelen wordt een interface gebruikt, maar de feitelijke opslag gebeurt in een database. De in- en uitvoer wordt de ‘front-end’ genoemd, die gekoppeld is met de ‘back-end’, waar bijvoorbeeld een Oracle database draait met daarin de tabellen. De front-end kan door MS Access worden gedaan, terwijl een DBMS een koppeling maakt met de back-end (de database) om de gevraagde gegevens te benaderen. Omdat niet iedereen zomaar alle gegevens mag opvragen, worden er in het DBMS accounts aangemaakt die geautoriseerd worden met de gewenste bevoegdheden per gebruiker. Omdat een database (meestal) niet door één gebruiker wordt benaderd, kan het voor komen dat twee gebruikers tegelijkertijd een record (tabel rij) willen wijzigen. Slechts één gebruiker kan een record wijzigen, anders zou het mogelijk zijn dat twee online-klanten beide het zelfde huisje huren. Dit wordt voorkomen door ‘record-locking’.
5
De basis van SQL SQL is een afkorting voor ‘Structurated Query Language’ (uitgesproken als: S-Q-L of see-quill) en is een standaard relationele query- taal (SQL is gestandaardiseerd door zowel ANSI als ISO) die voor gegevens uitwisseling met databases wordt gebruikt.
SQL werd ontwikkeld door IBM in jaren '70 en heeft zijn wortels in de relationele algebra gedefinieerd door dhr. Codd in 1972. De SQL functionaliteit gaat verder dan de relationele algebra, die gegevens kan terug vinden, toevoegen, wijzigen en verwijderen van/aan een RDBMS (Relationeel DataBase Management Systeem). SQL kan rekenkundige operators zoals: deling, vermenigvuldiging, aftrekking en optellen en vergelijkingsoperators (=, <, >, <=, >=, <= en <>). SQL ken select functies zoals: LIKE, IS NULL, IS NOT NULL, AS, MAX(), MIN(), AVG(), COUNT(), SUM(), FIRST() en LAST(). SQL definieert veel keywoorden, die in verscheidene categorieën kunnen worden verdeeld. De eerste SQL keywoorden categorie is voor het verkrijgen van gegevens zoals het SELECT keywoord. De tweede categorie is voor de SQL keywoorden die voor gegevensmanipulatie worden gebruikt zoals het INSERT, UPDATE, en DELETE SQL keywoorden. De derde categorie is de transact-SQL keywoorden categorie, die keywoorden zoals COMMIT en het ROLLBACK. Een andere SQL keywoorden categorie is de ‘SQL Data Definition Language’ zoals CREATE en DROP kenmerkt. Nog een andere categorie van SQL keywoorden controleert de rechten van RDBMS (GRANT en REVOKE keywoorden). SQL gebruikt opeenvolging van karakters als één enkel herkenningsteken van een lijncommentaar.
6
De SQL commando’s zijn niet hoofdlettergevoelig en de volgende SQL queries geven het zelfde resultaat: SELECT * FROM Gebruikers; select * from Gebruikers;
Voorkom veel type werk door jokers te gebruiken; * voor alle kolommen in die aangegeven tabel(len) % voor een onbekend aantal willekeurige tekens en cijfers (wildcard in strings) # voor een enkel getal ? voor een enkele letter NB: In MS Access wordt de * als wildcard gebruikt in teksten (strings)
7
RDBMS en tabellen DBMS is een afkorting voor het ‘Relationele Database Management Systeem’. Gegeven in een RDBMS worden opgeslagen in database objecten, de tabellen. De databasetabellen zijn de primaire gegevensopslag voor elke RDBMS en hoofdzakelijk zijn zij verzamelingen van data (gegevens) die meestal met elkaar zijn gerelateerd. De relaties komen later aanbod. Een beschrijving van een tabel met de naam ‘Gebruikers’ zou informatie over vele personen kunnen opslaan en elke invoering (rij of record) in deze tabel zal één unieke gebruiker vertegenwoordigen. Hoewel alle gebruikersinvoeringen in de tabel van ‘Gebruikers’ uniek zijn, zijn zij verwant in de betekenis dat zij gelijkaardige object beschrijven. De Gebruikers van de tabel VoorNaam
AchterNaam
GebDatum
John David Susan
Smit Bakker Advocaat
12/12/1969 30/07/1954 03/03/1970
Elke databasetabel bestaat uit kolommen en rijen. Elke tabelkolom bepaalt het type van de gegevens die daarin worden opgeslagen en dit gegevenstype is geldig voor alle onderliggende rijen in die kolom. Een tabel-rij (record) is een invoering van gegevens die voor elke kolom in deze bepaalde tabel waarden bevat. RDBMS slaan de gegevens op in groep van tabellen (database), die meestal (soms ook niet) een relatie met elkaar hebben (kolommen uit verschillende tabellen).
SQL SELECT SQL SELECT is zonder enige twijfel het meest gebruikte SQL commando. Het SQL SELECT commando wordt gebruikt om gegevens van één of meerdere databasetabellen op te vragen. Om het gebruik van het SELECT commando te illustreren wordt de tabel gebruiken van Gebruikers (uit het vorige hoofdstuk): VoorNaam
AchterNaam
GebDatum
John David Susan
Smit Bakker Advocaat
12/12/1969 01/03/1973 03/03/1970
De SQL statement (verklaring) toont hieronder een eenvoudig gebruik van het SQL SELECT commando: SELECT VoorNaam, AchterNaam, GebDatum FROM Gebruikers;
8
Dit SLQ-statement begint met het SELECT keywoord dat door tabelkolom(men) wordt gevolgd. De gespecificeerde kolommen in het SELECT statement worden aangeleverd door het RDBMS vanuit de tabel(len) die genoemd zijn bij FROM (waaruit de gegevens moeten komen). Er is een speciale syntaxis die met het SELECT commando kan worden gebruikt, als alle kolommen van een tabel moeten worden opgevraagd. , dan worden alle kolomnamen vervangen door het * symbool. Zoals hieronder: SELECT * FROM Gebruikers; Gegevens uit twee tabellen (‘Gebruikers’ en ‘ Aankopen’) ophalen gaat als volgt: SELECT VoorNaam, AchterNaam, GebDatum, Product, Aantal, Prijs FROM Gebruikers, Aankopen;
SELECT INTO statement wint gegevens van een databasetabel terug en neemt het in een andere tabel op. Het SELECT INTO voorbeeld staat hieronder: SELECT VoorNaam, AchterNaam, GebDatum INTO UsersBackup FROM Gebruikers; Het eerste deel van het statement lijkt vertrouwd, het selecteert alleen de verschillende kolommen uit een tabel. Het tweede deel van deze SQL statement is het belangrijke deel, dat specificeert om de rijen in de ‘UsersBackup- tabel’ op te nemen. Het laatste deel specificeert welke tabel er wordt gebruikt om de rijen van te verkrijgen. Dit voorbeeld veronderstelt dat zowel de ‘Gebruikers’ als de ‘UsersBackup- tabellen’ een identieke structuur hebben. Om een exacte kopie van de gegevens in de Gebruikers te maken wordt het volgende statement gebruikt: SELECT * INTO UsersBackup FROM Gebruikers;
9
SQL WHERE SQL WHERE het keywoord wordt gebruikt om gegevens voorwaardelijk te selecteren, uit de opgegeven tabel(en). Om de WHERE-keywoord toepassingen te illustreren; zijn er twee extra kolommen en extra rijen aan de tabel ‘Gebruikers’ toegevoegd, die in de vorige hoofdstukken al eerder is gebruikt. VoorNaam
AchterNaam
GebDatum
E-mail
Stad
John David Susan Paul Stephen
Smit Bakker Advocaat Janssen Advocaat
12/12/1969 01/03/1954 03/03/1970 09/17/1982 03/03/1974
[email protected] [email protected] advocaat@door_zee.com
[email protected] [email protected]
Zwolle Den Haag Hengelo Zwolle Hengelo
Bekijk de volgende SQL vraag: SELECT VoorNaam, AchterNaam, Stad FROM Gebruikers WHERE Stad = ' Hengelo '; Het resultaat van de SQL uitdrukking zal het volgende zijn: VoorNaam
AchterNaam
Stad
Susan Stephen
Advocaat Advocaat
Hengelo Hengelo
De SQL vraag gebruikte "=" (Gelijk aan) exploitant in de WHERE criteria: Stad = ' Hengelo ' Slechts de gebruikers die zijn geselecteerd en gelijk zijn met de waarde ‘Hengelo’ in de kolom Stad. Wanneer string-waarden (karakters) in queries worden gebruikt, dan moeten ze tussen aanhalingstekens (dubbele quotes) worden gezet. Bijvoorbeeld: bij de SQL query hieronder worden dubbele quotes in plaats van de enkele quotes gebruikt: SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE Stad = “ Den Haag “; Als alle gebruikers worden opgevraagd die in AchterNaam O’Brain hebben, volgt hier het SQL statement: SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE AchterNaam =” O’Brain ”;
10
Echter een string-opmaakt die gebruikt is in de WHERE criteria geeft een error. De SQL-engine zal proberen om de SQL-statement te interpreteren en zal enkele quote binnen de string als het einde van die string beschouwen. Het resterende deel van de SQL-statement wordt niet correct geïnterpreteerd. Door alle enkele quotes in een string met twee enkele quotes te vervangen worden zij geïnterpreteerd door SQL en aangezien één quote in een string. Hier onder staat de verbeterde SQL-statement die correct zal werken: SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE AchterNaam = ' O''Neil '; Het gebruik van ‘=’ (Gelijk aan) operator in de voorbeelden hierboven, maar er kan ook een van de volgende vergelijkingsoperators samen met SQL WHERE keywoord worden gebruikt: <> (Not Equal) SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE VoorNaam <> 'John'; > (Groter dan) SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE GebDatum > ' 02/03/1970 '; > = (Groter of Gelijk) SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE GebDatum > = ' 02/03/1970 '; < (Less than) SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE GebDatum < '02/03/1970'; <= (Less or Equal) SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE GebDatum <= '02/03/1970'; Er zijn meer mogelijkheden: BETWEEN 20 AND 80 IN (3, 73, `Veld1`) LIKE “Ker%” IS NULL, IS NOT NULL, AS, MAX(), MIN(), AVG(), COUNT(), SUM(), FIRST() en LAST(), DATEDIFF(), AND, OR en NOT Zie de bijlagen of de mySQL website met documentatie voor meer mogelijkheden.
11
Naast de vergelijkingsoperators kunnen ook logische operators worden gebruikt. Logische operators worden gebruikt om twee of meer criteria te combineren in een WHERE- statement. Om alle gebruikers uit de tabel Gebruikers te geselecteren die wonen in Zwolle en na 10/10/1975 geboren zijn: SELECT VoorNaam, AchterNaam, GebDatum, E-mail, Stad FROM Gebruikers WHERE Stad = ' Zwolle AND GebDatum > ' 10/10/1975 '; Hieronder staat het resultaat van bovengenoemde SELECT: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
Paul
Janssen
09/17/1982
[email protected]
Zwolle
Criteria worden doormiddel van de AND (logische operator) aaneengeschakeld, wat betekent dat beide voorwaarden WAAR moeten zijn. Als alle gebruikers van de tabel Gebruikers worden geselecteerd, welke wonen in Zwolle of na 10/10/1975 geboren zijn zullen in het volgen het statement worden gebruikt: SELECT VoorNaam, AchterNaam, GebDatum, E-mail, Stad FROM Gebruikers WHERE Stad = ' Zwolle ' OR GebDatum > ' 10/10/1975 '; Het resultaat is: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
John Paul Stephen
Smit Janssen Advocaat
12/12/1969 09/17/1982 03/03/1974
[email protected] [email protected] [email protected]
Zwolle Zwolle Hengelo
Dit keer werken de twee criteria samen met FROM, wat betekent dat minstens één van de twee criteria moet voldoen om te worden opgenomen in het resultaat. De logische operator NOT kan ook in de SQL-statements worden gebruiken, zie het volgende voorbeeld: SELECT VoorNaam, AchterNaam, GebDatum, E-mail, Stad FROM Gebruikers WHERE Stad NOT LIKE ‘ %dam% '; Dit statement zal alle gebruikers selecteren de van wonen in een stads zonder ‘dam’ in de kolom ‘Stad’. Meer logische operators zijn: BETWEEN .. AND .., IN(.., .., ..), IS NULL, IS NOT NULL, AND, OR, NOT en meer…
12
Jokers (wildcards) zijn: %, # en _ LIKE (gelijkwaardig aan) SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE VoorNaam LIKE ' S% ' Een SQL-statement waarin het LIKE keywoord is gebruikt, zal alle gebruikers met voornaam, die beginnen met de letter ‘S’, als resultaat geven. Wanneer het ‘%karakter’ binnen een LIKE statement wordt gebruikt, wordt het ‘%’ beschouwd als een vervanging voor verschillende letters. Het WHERE keywoord samen met het BETWEEN keywoord wordt gebruikt om een ‘gebied’ te bepalen: SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE GebDatum BETWEEN ' 02/03/1970 ' AND ' 10/10/1972 '
Om meerdere waarden bij een WHERE keywoord te gebruiken kan de IN operator worden gebruikt. SELECT VoorNaam, AchterNaam FROM Gebruikers WHERE Stad IN (‘Hengelo’, ‘Zwolle’) De SQL statement zal hierboven alle gebruikers van Hengelo en Zwolle terugkeren.
13
SQL INSERT De SQL INSERT INTO conditie wordt gebruikt om gegevens in een SQL tabel op te nemen. Het INSERT INTO wordt vaak gebruikt en heeft de volgende syntaxis: INSERT INTO TABEL1 (KOLOM1, KOLOM2, KOLOM3) VALUES (KolomWaarde1, KolomWaarde2, KolomWaarde3) INSERT INTO bestaat eigenlijk uit twee delen. Als eerste de gespecificeerde kolommen in de tabel en ten tweede de waarden die moeten worden toegevoegd in de kolomtabel. De tabel Gebruikers van het vorige hoofdstuk ziet er als volgt uit: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
John David Susan Paul Stephen
Smit Bakker Advocaat Janssen Advocaat
12/12/1969 01/03/1954 03/03/1970 09/17/1982 03/03/1974
[email protected] [email protected] advocaat@door_zee.com
[email protected] [email protected]
Zwolle Den Haag Hengelo Zwolle Hengelo
Als er een nieuw record in de tabel ‘Gebruikers’ wordt invoegt, kan het met het volgende SQL INSERT INTO statement: INSERT INTO Gebruikers (VoorNaam, AchterNaam, GebDatum, E-mail, Stad) VALUES (' Frank ', ' Drum ', ' 10/08/1955 ', '
[email protected] ', ' Arnhem '); Alle gegevens van de tabel Gebruikers worden geselecteerd vanaf het SQL INSERT INTO hierboven is doorgevoerd. Dat levert het volgende resultaat: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
John David Susan Paul Stephen Frank
Smit Bakker Advocaat Janssen Advocaat Drum
12/12/1969 01/03/1954 03/03/1970 09/17/1982 03/03/1974 10/08/1955
[email protected] [email protected] advocaat@door_zee.com
[email protected] [email protected] [email protected]
Zwolle Den Haag Hengelo Zwolle Hengelo Arnhem
Het SQL INSERT INTO kan ook een gedeelte van de kolommen, in plaats van alle kolommen in de tabel invoegen. Een voorbeeld om een nieuw record met waarden voor de eerste 4 kolommen en de vijfde niet invult: INSERT INTO Gebruikers (VoorNaam, AchterNaam, GebDatum, E-mail) VALUES (' Frank ', ' Drum ', ' 10/08/1955 ', '
[email protected] ');
14
In het bovengenoemde voorbeeld wordt verondersteld dat de waarden in de laatste kolom E-mail ‘leeg’ blijft (niet ingevuld). Het resultaat van het SQL INSERT zal hierboven zijn: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
John David Susan Paul Stephen Frank
Smit Bakker Advocaat Janssen Advocaat Drum
12/12/1969 01/03/1954 03/03/1970 09/17/1982 03/03/1974 10/08/1955
[email protected] [email protected] advocaat@door_zee.com
[email protected] [email protected] [email protected]
Zwolle Den Haag Hengelo Zwolle Hengelo NULL
Als een nieuwe record wordt opgenomen met waarden voor alle kolommen, dan kunnen de volledige kolomtabellen in het statement worden weggelaten. De volgende twee SQL statementen van het INSERT gelijkwaardig: INSERT INTO Gebruikers VALUES (' Frank ', ' Drum ', ' 10/08/1955 ', '
[email protected] ', ' Arnhem '); INSERT INTO Gebruikers (VoorNaam, AchterNaam, GebDatum, E-mail, Stad) VALUES (' Frank ', ' Drum ', ' 10/08/1955 ', '
[email protected] ', ' Arnhem '); Als er een kolom wordt overgeslagen en geen waarde krijgt, maar de andere kolommen wel, dan levert dat een fout op van de SQL INSERT INTO uitvoering: INSERT INTO Gebruikers VALUES (' Frank ', ' Drum ', ' 10/08/1955 ', '
[email protected] '); Het bovengenoemde SQL INSERT zal een fout veroorzaken, omdat er geen waarde voor de kolom Stad is gespecificeerd. De waarde moet bepaald worden om de fout op te lossen.
15
SQL DISTINCT
Het SQL DISTINCT commando dat samen met het SELECT keywoord wordt gebruikt geeft slechts unieke waarden in de kolom die zijn gespecificeerd. VoorNaam
AchterNaam
GebDatum
E-mail
Stad
John David Susan Paul Stephen
Smit Bakker Advocaat Janssen Advocaat
12/12/1969 01/03/1954 03/03/1970 09/17/1982 03/03/1974
[email protected] [email protected] advocaat@door_zee.com
[email protected] [email protected]
Zwolle Den Haag Hengelo Zwolle Hengelo
De ‘Gebruikers’ tabel heeft verscheidene gebruikers en het zou interessant zijn om een tabel met alle steden op te vragen waar onze gebruikers wonen. Het statement hieronder geeft een lijst met (eventueel) dubbele steden. Mensen kunnen in de zelfde stad wonen: SELECT Stad FROM Gebruikers; Een tabel maken met alle steden zonder die te herhalen in het resultaat wordt gemaakt met DISTINCT keywoord: SELECT DISTINCT Stad FROM Gebruikers; Het resultaat van de SQL DISTINCT uitdrukking van hierboven zal zijn: Stad
Zwolle Den Haag Hengelo Eigenlijk verwijdert het DISTINCT keywoord de duplicaten uit de resultatenreeks die door uw SELECT SQL statement is terug gegeven. Het DISTINCT keywoord kan meer dan één kolom uitvoeren. SELECT DISTINCT AchterNaam, Stad FROM Gebruikers; Het bovengenoemde statement waarin een combinatie wordt gemaakt van de kolom ‘AchterNaam’ en van ‘Stad’ zal het onderstaande resultaat geven: AchterNaam
Stad
Smit Bakker Advocaat Janssen
Zwolle Den Haag Hengelo Zwolle
16
In de originele tabel zijn er twee gebruikers met identieke namen (Advocaat), die om in de zelfde stad (Hengelo) wonen. Omdat de combinatie AchterNaam en waarden van de Stad voor beide gebruikers niet uniek zijn, komt slechts één record terug van de twee. Echter wanneer er één kolom (E-mail) bij wordt gevoegd: SELECT DISTINCT AchterNaam, E-mail, Stad FROM Gebruikers; Dan komen beide gebruikers met de gelijke achternaam wel weer terug. Eenvoudig omdat zij verschillende e-mail hebben en zo hun invoeringen wat betreft onze SQL statement uniek zijn geworden: AchterNaam
E-mail
Stad
Smit Bakker Advocaat Janssen Advocaat
[email protected] [email protected] advocaat@door_zee.com
[email protected] [email protected]
Zwolle Den Haag Hengelo Zwolle Hengelo
SQL UPDATE Het SQL commando UPDATE wordt gebruikt om gegevens te wijzigen die in databasetabellen zijn opgeslagen. Als een e-mail van één van de gebruikers in de tabel ‘Gebruikers’ wordt bijgewerkt, dan kan dit met het SQL- statement hieronder: UPDATE Gebruikers SET E-mail =
[email protected] ' WHERE E-mail = '
[email protected] '; In de eerste rij wordt het keywoord UPDATE gevolgd door de tabelnaam die wordt bijwerkt. De tweede regel bepaalt de kolomnaam die wordt gewijzigd, gevolgd door een gelijkaan-teken met de nieuwe waarde voor deze kolom. Meer dan één taak kan na de nieuwe waarde worden ingevoerd, bijvoorbeeld zowel ‘e-mail’ als de ‘stad’ kunnen worden bijwerken in het zelfde SQL statement: UPDATE Gebruikers SET E-mail = '
[email protected]', Stad = ' Den Haag ' WHERE E-mail = '
[email protected] '; De derde lijn is de WHERE conditie, die specificeert record(s) die worden geupdate. Hier wordt de kolom E-mail bijgewerkt. Wat gebeurt de WHERE de conditie wordt verwijdert zal de SQL query er als volgt uitzien: UPDATE Gebruikers SET E-mail = '
[email protected]';
17
Hierdoor zullen alle waarden in de kolom E-mail in de tabel Gebruikers veranderd worden in de waarde
[email protected]. Dit zal meestal niet de bedoeling zijn, maar het zou kunnen wanneer er verscheidene records bijwerken moeten worden. Bijvoorbeeld wanneer één een kantoor van een bedrijf verhuisd van Den Haag naar Hengelo (er vanuit gaande dat alle werknemers ook verhuisd zijn) kunnen alle records in één keer worden bijgewerkt. Om dat te doen, gebruik de volgende SQL statement: UPDATE Gebruikers SET Stad = ' Hengelo ' WHERE Stad = ' Den Haag '; In beide voorbeelden zorgt de UPDATE WHERE conditie ervoor dat kolomwaarden een nieuwe waarde krijgen, mits ze dezelfde WHERE conditiecriteria hebben. Er kunnen ook meerdere WHERE criteria worden gebruikt, bijvoorbeeld: UPDATE Gebruikers SET E-mail = '
[email protected]' WHERE VoorNaam = ' Stefan ' AND AchterNaam = ' Advocaat '; De WHERE criteria zorgen ervoor dat updates alleen in die rijen wordt toegepast, die genoemd zijn. Bij het voorbeeld zal VoorNaam = Stefan ervoor zorgen dat niet alle personen met de achternaam Advocaat worden bijgewerkt .
SQL DELETE Om gegevens uit een tabel te verwijderen is er het SQL DELETE commando. Eén van de gebruikers in de tabel Gebruikers (Stefan Advocaat) heeft net het bedrijf verlaten, en zijn record moet worden en verwijderen. Dat gaat als volgt: DELETE FROM Gebruikers WHERE AchterNaam = ' Bakker '; De eerste lijn in het SQL DELETE statement hierboven specificeert de tabel waar record(s) moeten worden verwijdert. De tweede lijn (WHERE conditie) specificeert welke rijen precies worden verwijdert (alle rijen waar AchterNaam gelijk is aan Advocaat). Een probleem dat zich voor kan doen bij DELETE is, dat er teveel records worden verwijderd. Er zijn in ons voorbeeld meer dan één gebruiker met dezelfde achternaam. Alle gebruikers met deze achternaam zullen worden verwijderd. Het is van belang om records zeer goed te specificeren (uniek maken). Onze betere SQL vraag die slechts het record van Stefan Advocaat record verwijderd zal er bijvoorbeeld als volgt uitzien: DELETE FROM Gebruikers WHERE E-mail = '
[email protected] ';
18
Wat gebeurt er als de WHERE conditie in DELETE niet is gespecificeerd? DELETE FROM Gebruikers; Het antwoord is dat alle records in de tabel van Gebruikers worden verwijderd. SQL TRUNCATE statement hieronder zal het zelfde effect hebben als het DELETE statement: TRUNCATE TABLE Gebruikers; De TRUNCATE statement zal alle rijen in de tabel van Gebruikers, verwijderen zonder de tabel zelf te verwijderen. Wees zeer zorgvuldig met het gebruik van DELETE en TRUNCATE, omdat deze statements niet ongedaan gemaakt kunnen worden. Zodra de record(s) verwijdert zijn uit de tabel is het niet meer terug te draaien.
SQL ORDER BY SQL ORDER BY conditie wordt gebruikt om de gegevensreeksen die van een SQL database worden ontvangen te ordenen. Een opdracht waar één of meerdere kolommen uit een tabel worden gehaald, komt vaak niet gestructureerd over. Dit wordt opgelost door gegevens in een van de kolomen te sorteren op (bijvoorbeeld) de VoorNaam. Zie de volgende ORDER BY SQL statement om dit toe te passen: SELECT * FROM Gebruikers ORDER BY VoorNaam; Het resultaat van de ORDER BY statement zal hierboven het volgende zijn: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
David John Paul Stefan Susan
Bakker Smit Janssen Advocaat Advocaat
01/03/1954 12/12/1969 09/17/1982 03/03/1974 03/03/1970
[email protected] [email protected] [email protected] [email protected] advocaat@door_zee.com
Den Haag Zwolle Zwolle Hengelo Hengelo
De rijen in de kolom VoorNaam zijn nu alfabetisch gesorteerd. ORDER BY kan ook gebruikt worden om de gegevens door meer dan één kolom toe te passen. Bijvoorbeeld, als er zowel de ‘AchterNaam’ als de kolom ‘Stad’ toevoegen, zou de volgende ORDER BY statement moeten werken: SELECT * FROM Gebruikers ORDER BY AchterNaam, GebDatum;
19
Hier is het resultaat van deze ORDER BY statement: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
Susan Stefan David Paul John
Advocaat Advocaat Bakker Janssen Smit
03/03/1970 03/03/1974 01/03/1954 09/17/1982 12/12/1969
advocaat@door_zee.com
[email protected] [email protected] [email protected] [email protected]
Hengelo Hengelo Den Haag Zwolle Zwolle
De kolommen kunnen ook worden omgekeerd in de ORDER BY statement, zoals hieronder: SELECT * FROM Gebruikers ORDER BY GebDatum, AchterNaam; Deze ORDER BY statement zal de zelfde resultaten terugkeren, maar de volgorde is anders. Hier is het resultaat: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
David John Susan Stefan Paul
Bakker Smit Advocaat Advocaat Janssen
01/03/1954 12/12/1969 03/03/1970 03/03/1974 09/17/1982
[email protected] [email protected] advocaat@door_zee.com
[email protected] [email protected]
Den Haag Zwolle Hengelo Hengelo Zwolle
In beide ORDER BY voorbeelden tot dusver, wordt voor karakterkolommen (VoorNaam, AchterNaam) op gesorteerd op alfabetische volgorde en van vroeger aan recentere data voor de kolom GebDatum. Om onze gegevens omgekeerd te sorteren wordt het keywoord DESC toegevoegd: SELECT * FROM Gebruikers ORDER BY VoorNaam DESC; Het resultaat is als volgt: VoorNaam
AchterNaam
GebDatum
E-mail
Stad
Susan Stefan Paul John David
Advocaat Advocaat Janssen Smit Bakker
03/03/1970 03/03/1974 09/17/1982 12/12/1969 01/03/1954
advocaat@door_zee.com
[email protected] [email protected] [email protected] [email protected]
Hengelo Hengelo Zwolle Zwolle Den Haag
Het tegengestelde van het keywoord DESC is het ASC keywoord waarbij de gespecificeerde kolommen alfabetisch worden gesorteerd. Wanneer er geen ASC of DESC na een kolom in de ORDER BY wordt specificeert, dan zal de opdracht worden uitgevoerd met ASC (alfabetisch, van laag aan hoogte).
20
Het is belangrijk om te onthouden dat wanneer er door meer dan één kolom opdracht geeft tot ASC en/of DESC, elke kolom moet worden gespecificeerd. Bijvoorbeeld zal het statement hieronder door zowel AchterNaam als GebDatum opdracht geven tot maar slechts zal AchterNaam in dalende ORDER BY zijn: SELECT * FROM Gebruikers ORDER BY GebDatum, AchterNaam DESC; Wanneer beide kolommen aflopend moeten zijn, dan moet het ORDER BY statement in dit veranderen: SELECT * FROM Gebruikers ORDER BY GebDatum DESC, AchterNaam DESC;
SQL Aggregate functies De SQL aggregate functies komt er één enkele waarde terug, waarbij waarden zijn gebruikt uit een kolom. Voor dit hoofdstuk wordt een nieuwe tabel gebruikt met de naam ‘Verkoop’, die de volgende kolommen en de gegevens zal hebben: ORDER Nr
ORDER Datum
ORDER Prijs
ORDER Aantal
KlantNaam
1 2 3 4 5 6 7
12/22/2005 08/10/2005 07/13/2005 07/15/2005 12/22/2005 10/2/2005 11/03/2005
160 190 500 420 1000 820 2000
2 2 5 2 4 4 2
Smit Johnson Baldwin Smit Hout Smit Baldwin
De SQL functie van de COUNT geeft voor alle rijen in een tabel een waarde terug die aan de criteria voldoet aan de specificaties in de WHERE conditie. Als de hoeveel orders van een klant met de KlantNaam ‘Smit’ moet worden bepaald, dan zal het volgende SQL uitdrukking van de COUNT worden gebruikt: SELECT COUNT (*) FROM Verkoop WHERE KlantNaam = ' Smit '; Het COUNT keywoord wordt gevolgd door het * karakter tussen haken. Het gaat om alle kolommen in de tabel. Het statement zal de zelfde resultaten geven als bij WHERE, mits de voorwaarden het zelfde zijn. Het resultaat van de bovengenoemde SQL statement zal aantal 3 zijn, omdat de klant met de naam Smit in totaal drie bestellingen heeft geplaatst. Als de specificaties in de WHERE conditie niet wordt gebruikt, zoals bij het statement hieronder. Er komen 7 records terug: SELECT COUNT(*) FROM Verkoop;
21
Het aantal unieke klanten bepalen die een bestelling hebben geplaatst kan door het DISTINCT keywoord toe te voegen aan een functie zoals COUNT: SELECT COUNT (DISTINCT KlantNaam) FROM Verkoop; De SQL functie SOM wordt gebruikt om de som van waarden in een numerieke kolom te bepalen. Bij de tabel Verkoop, kan de som van alle orders met de volgende SQL statement worden bepaald: SELECT SOM (OrderPrijs) FROM Verkoop; Het resultaat van de bovengenoemde SQL statement is aantal 4990. Als de hoeveel producten die in totaal verkocht zijn (de som van OrdersPrijs) bepaald moet worden: SELECT SOM (OrderPrijs) FROM Verkoop; De SQL AVG functie bepaald de gemiddelde waarde voor een numerieke kolom terug. Dit opvragen kan als volgt: SELECT AVG (OrdersPrijs) FROM Verkoop; Natuurlijk kan de functie AVG worden gebruikt in combinatie met de WHERE conditie, waarbij de gegevens worden beperkt met de conditie(s): SELECT AVG (OrderPrijs) FROM Verkoop WHERE ORDER Prijs > 200; De bovengenoemde SQL uitdrukking zal gemiddelde order Aantal voor alle orders met een orderprijs groter dan 200 terugkeren. De SQL MIN functie selecteert het kleinste getal van een numerieke kolom. Om te bepalen wat de minimumprijs van de OrdersPrijs in de Verkoop- tabel, de volgende SQL uitdrukking wordt gebruikt: SELECT MIN (OrdersPrijs) FROM Verkoop; De SQL MAXIMUM functie vraagt het maximum numerieke waarde van een numerieke kolom terug. De MAXIMUM SQL statement geeft de hoogste orderprijs van de tabel van de Verkoop terug: SELECT MAX(OrdersPrijs) FROM Verkoop;
22
SQL GROEP BY De SQL GROEP BY statement wordt gebruikt samen met de SQL aggregate functies om de ontvangen gegevens te groeperen voor één of meerdere kolommen. De GROUP BY is één van de lastigste onderwerpen voor mensen die nieuw zijn met SQL. Van de tabel Verkoop moeten de unieke klanten worden verzameld en het totale besteedde bedrag per klant worden bepaald. ORDER Nr
ORDER Datum
ORDER Prijs
ORDER Aantal
KlantNaam
1 2 3 4 5 6 7
12/22/2005 08/10/2005 07/13/2005 07/15/2005 12/22/2005 10/2/2005 11/03/2005
160 190 500 420 1000 820 2000
2 2 5 2 4 4 2
Smit Johnson Baldwin Smit Hout Smit Baldwin
Om uit een tabel de unieke klanten op te vragen wordt het DISTINCT keywoord gebruikt: SELECT DISTINCT KlantNaam FROM Verkoop; Dit werkt, maar geeft geen totale bestedingen per klant terug. De SQL SUM functie en de GROUP BY conditie samen gaat als volgt: SELECT KlantNaam, SOM (OrderPrijs) FROM Verkoop GROUP BY KlantNaam; Er worden 2 kolommen met SELECT gespecificeerd. KlantNaam en SOM (ORDER Prijs). Het probleem is dat de SOM (ORDER Prijs), één enkele waarde terugkeert, terwijl er vele klanten in onze tabel van de Verkoop hebben. De GROUP BY conditie combineert alle unieke klantnamen uit KlantNaam samen. In dit geval de GROUP BY conditie gelijk met het DISTINCT statement. Het resultaat dat wordt gegeven is: KlantNaam
ORDER Prijs
Baldwin Johnson Smit Hout
2500 190 1400 1000
Groepering met GROUP BY op meer dan één kolom gaat bijvoorbeeld als volgt: SELECT KlantNaam, OrderDatum, SOM (OrderPrijs) FROM Verkoop GROUP BY KlantNaam, OrderDatum; Alle kolommen in een SELECT statement verschijnen in moeten ook in de GROUP BY conditie worden vermeld, muv de aggretage kolommen.
23
SQL HAVING SQL HAVING conditie wordt gebruikt samen met de onderzoeksvoorwaarde voor een groep of een aggregate conditie gedraagt zich als WHERE de conditie, maar op groep vertegenwoordigen. In tegenstelling tot WHERE toegepast op individuele rijen, niet op groepen.
SELECT conditie om een te specificeren. De HAVING groepen - de rijen die een wordt de HAVING conditie
Verduidelijken hoe precies HAVING werkt: ORDER Nr
ORDER Datum
ORDER Prijs
ORDER Aantal
KlantNaam
1 2 3 4 5 6 7
12/22/2005 08/10/2005 07/13/2005 07/15/2005 12/22/2005 10/2/2005 11/03/2005
160 190 500 420 1000 820 2000
2 2 5 2 4 4 2
Smit Johnson Groot Smit Hout Smit Groot
In het vorige hoofdstuk werd uit een tabel met alle klanten, het totale bedrag per klant verkregen: SELECT KlantNaam, SOM (OrderPrijs) FROM Verkoop GROUP BY KlantNaam; Nu worden alle unieke klanten selecterend, die meer dan 1200 per artikel hebben besteed. Het SQL statement hier boven wordt gecombineerd met de HAVING conditie aan het eind van het wijzigen: SELECT KlantNaam, SOM (OrderPrijs) FROM Verkoop GROUP BY KlantNaam HAVING SOM (OrderPrijs) > 1200; Het resultaat van de SELECT vraag nadat er de HAVING onderzoeksvoorwaarde aan is toevoegd staat hieronder: KlantNaam
ORDER Prijs
Groot Smit
2500 1400
Een ander nuttig voorbeeld van de HAVING conditie is wanneer alle klanten geselecteerd moeten worden die meer dan tot 5 artikelen hebben besteld. Het HAVING statement zal er dan zo uitzien: SELECT KlantNaam, SOM (ORDER Aantal) FROM Verkoop GROUP BY KlantNaam HAVING SOM (OrderAantal) > 5;
24
WHERE en HAVING beide in één SELECT statement gebruiken kan ook. Bijvoorbeeld wanneer alle klanten geselecteerd worden die meer dan 1000, na 10/01/2005 hebben besteed. De SQL statement met inbegrip van de HAVING- en WHERE condities ziet er zo uit: SELECT KlantNaam, SOM (OrderPrijs) FROM Verkoop WHERE OrderDatum > 10/01/2005. GROUP BY KlantNaam HAVING SOM (OrderPrijs) > 1000; De voorwaarde van het conditieonderzoek in een WHERE conditie wordt toegepast op elke individuele record in de tabel van de Verkoop. Daarna wordt de HAVING conditie toegepast op het resultaat uit WHERE, om de rijen te bepalen voor het definitieve resultaat. Het belangrijke om te onthouden is dat de groepering slechts op de rijen wordt toegepast die in de WHERE conditievoorwaarde passen.
25
SQL Tabel relaties, Primaire- en Foreign keys Tot dusver zijn alle SQL voorbeelden die zijn toegepast op één enkele tabel. In werkelijkheid bestaan databases uit veel verschillende tabellen, die een relatie met elkaar hebben. Het ware kracht van ‘Relational Database Management Systems’ (RDBMS) is het feit dat ze ‘relationeel’ zijn. De relaties in een RDBMS zorgen ervoor dat er geen overbodige gegeven worden opgeslagen. Een voorbeeld van overbodige gegeven is bijvoorbeeld: Een online computerverkoper kan het makkelijkst zijn verkoop bijhouden in een database. Een tabel Product met alle artikelen bevatten met informatie over elke model, prijs en de fabrikant en details zoals fabrikanten-website en hun support e-mail adres. De tabel kan er als volgt uitzien: model
Prijs
Fabrikant
website
e-Mail
B120 B130 E1705 A100 P100
€499 €599 €949 €549 €934
Dello Dello Dello Tomma Tomma
www.dello.com www.dello.com www.dello.com www.tomma.com www.tomma.com
[email protected] [email protected] [email protected] [email protected] [email protected]
Om de overtollige fabrikantengegevens in de tabel van het Product te verwijderen, kan een nieuwe tabel worden gemaakt met de naam ‘Fabrikant’, die slechts één invoering (rij) voor elke fabrikant zal hebben en tussen deze tabel en de tabel ‘Product’ een relatie leggen. Om deze relatie tot stand te brengen moeten er een extra kolom in de tabel ‘Product’ worden toevoegt die de verwijzingen naar een specifieke fabrikant legt. Een verband tussen 2 tabellen wordt gelegd wanneer de gegevens in één van de kolommen in de eerste tabel, gegevens in een kolom uit de tweede tabel verband met elkaar hebben.
26
De Primaire-key en Foreign key. De Primaire-key, ook wel Globally Unique Identifier GUID), is een kolom of een combinatie van kolommen die elke rij in een tabel uniek maakt (identificatie voor die rij met gegevens). De Foreign-key is een kolom of een combinatie van kolommen waarvan de waarden overeen komen met een Primaire-key in een andere tabel. Meestal past een Primaire-key (tabel-X) op één van de andere tabellen met een Foreign-key (tabel-Y). De volgende tabellen hieronder gaan we gebruiken: Fabrikant FabrikantID
Fabrikant
website
e-Mail
1 2
Dell Toshiba
www.dell.com www.toshiba.com
[email protected] [email protected]
Product model
Inspiron Inspiron Inspiron Satelliet Satelliet
B120 B130 E1705 A100 P100
Prijs
FabrikantID
€499 €599 €949 €549 €934
1 1 1 2 2
De eerste tabel Fabrikant heeft 2 records, een voor Dell en een voor Toshiba. Elk van deze records heeft een waarde FabrikantID, een uniek interger getal. Omdat de kolom FabrikantID voor de tabel uniek is voor de fabrikant, kan dit als Primaire-key voor deze tabel worden gebruiken. De tabel Product heeft de kolommen Model en Prijs, maar heeft een nieuwe kolom met de naam FabrikantID, die gelijk is aan de Primary key uit de tabel Fabrikant. Alle waarden in de kolom FabrikantID moeten één een Primaire-key hebben die aansluit op de FabrikantID uit de tabel Fabrikant. (bijvoorbeeld: FabrikantID met waarde van 3 in de tabel Product kan niet, omdat FabrikantID 3 niet staat in de tabel fabrikanten). Uiteraard kunnen de keys elke gewenste waarde krijgen, die denkbaar is. De vraag is nu: hoe kan worden voorkomen dat er een ongeldige invoer in de tabel Product wordt gezet, zoals de laatste invoering hieronder een foutieve tabel geeft: model
Prijs
FabrikantID
Inspiron B120 Inspiron B130 Inspiron E1705 Satelliet A100 Satelliet P100 MacBook Pro
€499 €599 €949 €549 €934 €849
1 1 1 2 2 3
Referentiële integriteit Er bestaat geen fabrikant met de waarde 3 als FabrikantID in de tabel Fabrikant, vandaar dat deze invoering in de tabel Product ongeldig is. ‘Referentiële integriteit’ tussen de 2 tabellen moet worden afgedwongen. Verschillende RDBMS hebben verschillende manieren om ‘referentiële integriteit’ af te dwingen. Bij MS Access is dit een checkbox op de eigenschappen van de relatie. 27
Soorten relaties Er zijn 3 soorten relaties tussen tabellen. One-To-Many, Many-To-Many en One-ToOne. De relatie hierboven is One-To-Many en is het meest gebruikte. In One-ToMany relaties kan een rij in één van de tabellen een relatie hebben met meerdere rijen in een tweede tabel. In het voorbeeld, wordt elke fabrikant (een rij in de tabel van de Fabrikant) in relatie gelegd met verschillende computermodellen (verscheidene rijen in de tabel Product), maar elk bepaald product (een rij in de tabel van het Product) heeft slechts één fabrikant (een rij in de tabel van de Fabrikant). Het tweede type is de Many-To-Many relatie. In deze relatie kunnen vele rijen van de eerste tabel een relatie hebben met vele rijen in de tweede tabel en omgekeerd. Om dit relatie type uit te leggen is er een derde tabel nodig waar de Primaire-key wordt samengesteld uit 2 Foreign-keys (die uit twee andere tabellen komen). Wij hebben een tabel van het Artikel (ArtikelID is Primaire-key) en Categorie tabel (CategorieID is Primaire-key). Elk artikel dat in de tabel Artikel wordt gepubliceerd kan tot meerdere categorieën behoren. Er wordt in deze situaties een nieuwe tabel gemaakt met de naam ArtikelCategorie, die slechts 2 kolommen heeft; ArtikelID en CategorieID (deze 2 kolommen vormen samen de primaire sleutel voor deze tabel). Deze nieuwe tabel soms verbindingstabel (junction-tabel) genoemd en bepaalt het Many-To-Many verband tussen de 2 tabellen, zodat één artikel tot meerdere categorieën kan behoren, en elke categorie kan meer dan één artikel bevatten. In de One-To-One relatie kan elke rij in de eerste tabel slechts één rij in de tweede tabel. In deze gevallen is het vaak handiger om van die twee tabellen één tabel te maken. Een handige toepassing van One-to-One is: de gegevens van één grote tabel in twee tabellen te verdelen. Details worden uit de oorspronkelijke tabel verwijdert en in de nieuwe tabel gezet. Bij wijzigingen of toevoegingen worden enkele records gewijzigd in plaats van meerdere records. Dit verbeterd de prestaties en verkleint de grootte van de database. Het proces om overtollige gegevens uit tabellen te verwijderen en de onderlinge relaties tussen tabellen te bepalen wordt ‘Normalisatie’ genoemd. Het proces van de normalisatie gebruikt formele methodes om de database met daarin de nodige tabelverbanden te ontwerpen.
28
De eerste stappen om een database op te zetten De volgende stappen moeten worden doorlopen om een genormaliseerde database te maken: 1. Tabellen maken (incl normalisatie) 2. Relaties leggen 3. Queries maken 4. Forms maken (in- en uitvoer windows + de menustructuur + evt. macro’s) 5. Reports en grafieken maken 6. Testen Om te beginnen moeten er tabellen komen. De benodigde velden voor een tabel(len) zijn te halen uit documenten, facturen, bestellijsten, productlijsten, ed. Of ze moeten worden uitgedacht. Zo kunnen bijvoorbeeld de onderstaande documenten gebruikt worden om van een database op te zetten:
29
Als eerst is het handig om het volgende te weten, wanneer een tabel wordt gemaakt: • Maak geen kolommen aan waarvan de gegevens overal terug komen, bijv: bedrijfsnaam ‘ Expert’ • Als er geen directe sleutel (identificatie) voor rijen is aan te wijzen, introduceer die dan. • Namen bestaan uit: titulatuur (dhr, mevr, etc), de voornaam, tussenvoegsel, en de achternaam. • Voeg eventueel tabellen samen wanneer dit kan, bijv: woonadres en afleveradres. • Adressen bestaan uit: straatnaam, huisnummer en het appartementnummer. • Alle kolommen (attributen) moeten een unieke naam hebben. Dus de kolomnaam “product” komt slecht in één tabel voor. • Voorkom namen die het zelfde betekenen, zoals debiteur en klantnummer. Gegevens kunnen de volgende typen hebben: numeriek, tekst, kladblok/memo (string), datum en logisch.
Benamingen van tabellen, velden en queries Enkele algemene tips bij het benoemen van velden en tabellen: • Gebruik geen underscores _ ; het is slecht te lezen, automatisch code genereer programma’s hebben er soms moeite mee, • Gebruik een Mix van Hoofd- en kleineletters, • Gebruik geen spaties, Om enig overzicht te houden in een database is het wijs de verschillende databaseonderdelen in de ‘naamgeving’ terug te laten komen, bijvoorbeeld: Een tabelnaam begint met: tbl Een querynaam begint met: qry Een formuliernaam begint met: frm Een rapportnaam begint met: rpt Goede voorbeelden van benamingen zijn: tblKlant, tblOrder, tblOrderInvoer, tblFabricant en tblProduct. Kolomnamen (voor de in te vullen velden) moeten ook eenduidig worden gebruikt. Enkele tips speciaal voor velden in een tabel: • Geef een code mee tot welke tabel het veld behoord; In de tabel tblKlanten komen velden voor als: kntKlantNummer, kntKlantNaam, etc. • Velden met een algemeen doel zoals: notities (notes), status, naam (name) of een datum krijgen dit mee in hun naam. Enkele voorbeelden van dergelijk veldnamen: kntKlantNotes, ordOrderNotes, prdProductGUID (primary key) Een query kan het beste beginnen met het soort SQL-query dat gebruikt is, bijvoorbeeld: s voor SELECT, i voor INSERT, u voor UPDATE, d voor DELETE en x voor opgeslagen procedures die worden aangeroepen.
30
Normalisatie Normalisatie is een gestructureerde manier bij het opzetten van een databases, zodat een aantal gerelateerde tabellen gezamenlijk alle gewenste gegevens bevatten. Nulde normaalvorm (0NV) Uit de • • • •
beschikbare tabellen worden: Contanten zijn verwijdert. Proces gegevens / berekeningen verwijdert. Uitsluitend data en relevante gegevens worden bewaard. Een sleutel aangewezen.
0NV = sleutel, RG(datum, code, titel, naam, type, omschrijving) ‘RG’ betekend: ‘repeterende groep’. Kandidaatsleutels kunnen zijn: klantnummers, Sofi-numers, Leerlingnummers, en codes.
Eerste normaalvorm (1NV) Voor de repeterende groep worden gegevens vaak meerdere malen ingevoerd, zoals het veld code. Neem repeterende groepen appart, zodat er GEEN repeterende groepen meer zijn. 1NV = (datum) (datum, code, titel, naam, type, omschrijving)
Tweede normaalvorn (2NV) De gegevens die niet behoren tot de gehele sleutel (voor alle gegevens, hier is dat datum), maar slechts tot een deel worden verder opgesplitst. 2NV = (code, titel, naam, type, omschrijving) (datum, code) (datum) Derde normaalvorm (3NV) Bij de derde normaal vorm worden overtollige gegevens verwijdert. In de 2NV hierboven staat een “type”, dat enkele vastgestelde combinaties kent. Deze combinaties kunnen in een aparte tabel worden gezet. Daarnaast is er een tabel waarin één veld “datum” zit. Dit is zinloos, deze tabel wordt verwijdert. Het resultaat van de 3NV is: 3NV =(type, omschrijving) (code, titel, naam, type) (datum, code) 31
Uit de 3NV kunnen we tabellen met de benodigde kolomnamen (attributen) maken. De relaties worden van de eerste tabel trapsgewijs gelegd met de andere tabellen. TABEL1 met datum en code heeft de vreemde sleutel “code”, die meer betekenis krijgt in TABEL2 met de waarden: code, titel, naam, type. De vreemde sleutel “type” krijgt op zijn beurt weer meer betekenis door TABEL3 waarin de omschrijving staat. Daarbij moet de ‘referentiele integriteit’ van de relaties worden bepaald, zie hiervoor het hoofdstuk met “Soorten relaties”. Een ‘entiteit relatie diagram’ verduidelijkt en geeft een helder overzicht welke velden (kolommen) relaties onderhouden en op welke manier. In een ‘entiteit relatie diagram’ kunnen op de volgende manieren relaties naar andere tabellen verduidelijken en worden weergegeven. Eén op één combinatie; niet veel gebruikt Nul op één combinatie; Eén op meer combinatie; Nul op meer combinatie;
32
SQL JOIN De SQL JOIN conditie wordt gebruikt om van twee of meer tabellen gegevens op te vragen en die in een gemeenschappelijk weer te geven in één resultaat. Zoals bij de tabellen Product en Fabrikant. Wanneer een Primaire-key in één van de tabellen gelijk is aan een Foreign-key in de tweede tabel, dan kunnen de twee gerelateerde tabellen worden gebruikt in een JOIN statement: SELECT Fabrikant, website, e-Mail, AVG(Prijs) AS GemPrijs FROM Fabrikant JOIN Product ON Fabrikant.ManufacturerID = Product.ManufacturerID GROUP BY Fabrikant, website, e-Mail; Het eerste wat duidelijke wordt over het SELECT statement is dat de kolommen uit twee verschillende tabellen komen. De FROM conditie wordt gevolgd door een JOIN conditie. De JOIN conditie bestaat uit twee delen, ten eerste het deel dat de tabellen verklaart aanwijst: Fabrikant JOIN Product En het tweede deel, dat specificeert bij welke kolommen de gegevens komen: ON Fabrikant.ManufacturerID = Product.ManufacturerID Omdat de kolom Prijs een parameter voor de functie AVG is in het SQL statement, kan de GROUP BY conditie alleen voor de andere kolommen worden gebruikt die in het SELECT statement zijn gebruikt. Alle fabrikanten en de gemiddelde prijs van hun product worden als resultaat weergegeven: Fabrikant
website
e-Mail
AvgPrice
Dello Tomma
www.dello.com www.tomma.com
[email protected] [email protected]
€682.33 €741.50
Een JOIN voorwaarde nabootsen, kan ook in een WHERE conditie zijn geplaatst in plaats van een FROM conditie, zonder het JOIN keywoord te gebruiken, zoals hieronder gedaan is: SELECT Fabrikant, website, e-Mail, AVG(Prijs) AS “GemPrijs” FROM Fabrikant, Product WHERE Fabrikant.ManufacturerID = Product.ManufacturerID GROUP BY Fabrikant, website, e-Mail; Het wordt gezien als beter programmeerwerk om de JOIN te gebruiken ipv de FROM conditie. Bij het samenvoegen van tabellen (JOIN) moet ervoor worden opgepast dat er geen dubbelzinnig taalgebruik in de kolomnamen voorkomt. In het voorbeeld worden zowel de tabel: Fabrikant en Product worden de kolomnamen genoemd (bijv: ManufacturerID). Daarom zijn de kolommennaam en de gebruikte tabelnaam samengevoegd met een punt. 33
Een ander onderwerp om aan te halen is het volgende stukje SQL statement: AVG (Prijs) AS ”GemPrijs” Omdat de kolom voor de AVG functie wordt gemaakt, geven we die kolom ook een naam (in het voorbeeld: GemPrijs). In SQL termen is er een “alias” voor een nieuwe kolom aangemaakt. Er zijn 2 belangrijke soorten SQL JOINS; INNER JOINS en OUTER JOINS. In het voorbeeld is dit niet gespecificeerd, maar standaard is het een INNER JOIN. De INNER JOIN- en JOIN condities zijn algemeen verwisselbaar (JOIN condities kunnen voor de verschillende RDBMS verschillende syntaxis hebben!). INNER JOIN conditie zal alle rijen van beide tabellen opvragen zolang er een overeenkomst is tussen de kolommen. Als er een nieuwe fabrikant aan de tabel van Fabrikant wordt toevoegt, maar deze is niet toevoegt aan de Producten tabel, dan zal het resultaat het zelfde zijn als het alvorens de nieuwe fabrikant toe te voegen was. Dit gebeurt eenvoudig omdat er geen overeenkomst is voor deze nieuwe fabrikant in de Producten tabel en omdat er een INNER JOIN is gebruikt, die slechts de overeenkomende records terugkeert. Het definitieve resultaat is dat deze fabrikant met producten niet in de query gegevens verschijnt. Om alle fabrikanten uit de tabel weergegeven moeten worden, of ze nu wel of geen producten in de Product tabel hebben, dan is OUTER JOIN de oplossing. De OUTER JOIN conditie geeft alle records van minstens één van de aangesloten bij tabellen als resultaat geven, mits deze rijen aan de zoekvoorwaarden voldoen die in WHERE en HAVING conditie gespecificeerd zijn. Om alle fabrikanten en hun gemiddelde productprijs te krijgen, zonder dat er een enkele fabrikant nog geen vermelde producten heeft, dan zal het volgende SQL statement worden gebruikt: SELECT Fabrikant, website, e-Mail, AVG (Prijs) AS AvgPrice FROM Fabrikant LEFT OUTER JOIN Product IN Fabrikant.ManufacturerID = Product.ManufacturerID GROUP BY Fabrikant, website, e-Mail; Het verschil in het nieuwe statement is het keywoord LEFT OUTER voor het JOIN keywoord is toevoegt. LEFT OUTER heeft twee subtypes, namelijk een LEFT en een RIGHT . Wanneer de LEFT OUTER wordt gebruikt, wordt bedoeld dat alle records weergegeven moeten worden uit de tabel die LINKS is genoemd van het woord ’JOIN’ (ook wel de eerste tabel zelfs wanneer de records niet in de tweede tabel staan. Left is in het voorbeeld: Fabrikant). Wanneer er geen gemiddelde waarden (AVG) in de Product tabel aanwezig zijn, voor een opgevraagde Fabrikant, dan zal daar de ‘NULL’ worden ingevuld. Het resultaat van onze LEFT OUTER JOIN query zal er als volgt zien als Apple aan de tabel van de Fabrikant wordt toevoegt, terwijl er nog geen producten zijn toe gevoegd aan de tabel Producten: 34
Fabrikant
website
e-Mail
AvgPrice
Dello Tomma Apple
www.dello.com www.tomma.com www.apples.com
[email protected] [email protected] [email protected]
€682.33 €741.50 NULL
De RIGHT OUTER JOIN of simpel gezegd RIGHT JOIN doet precies het tegenover gestelde van de LEFT JOIN. Alle records uit de tabel Producten (tweede tabel) die aan de FROM conditie voldoen, worden vermeld. Producten waarvoor nog geen Fabrikant (eerste tabel) zijn aangemaakt zullen de waarde NULL krijgen. In het kort komt het hier op neer: JOIN: Geeft alle records als er minimal 1 overeenkomst is in de tabellen LEFT JOIN: Geeft alle records als er minimal 1 overeenkomst is in de linker tabel, zelfs als er geen overeenkomst is in de rechter tabel. RIGHT JOIN: Geeft alle records als er minimal 1 overeenkomst is in de rechter tabel, zelfs als er geen overeenkomst is in de linker tabel. FULL JOIN: Geeft records terug als ere en overeenkomst is in één van de tabellen Tot slot; een tabel kan ook aan zich worden gekoppeld (joined), daarvoor moeten er voor de gekoppelde waarden in die tabel twee verschillende aliassen in FROM conditie worden opgegeven.
SQL UNION De SQL UNION wordt gebruikt om de resultaten van twee of meer SELECT SQL statements in één enkel resultaat te combineren. Alle statements die met UNION worden aaneengeschakeld moeten de zelfde structuur hebben. Dit betekent dat de beide statements evenveel kolommen moeten hebben, en de overeenkomstige kolommen moeten ook de zelfde of uitwisselbare gegevenstypes hebben (impliciet aan het zelfde type of de uitdrukkelijk kan worden omgezet naar het zelfde type). De kolommen in elke SELECT statement moeten ook in precies de zelfde volgorde zijn vermeld. Zo ziet een eenvoudige UNION statement er uit: SELECT KOLOM1, KOLOM2 FROM TABEL1 UNION SELECT KOLOM1, KOLOM2 FROM TABEL2; De kolomnamen in het resultaat van een UNION zijn altijd gelijk met de kolomnamen in het eerste SELECT statement. De UNION operator verwijdert standaard alle dubbele records uit de lijst met resultaten. De ALL optie kan na het UNION keywoord worden gebruikt, hiermee wordt afgedwongen dat alle records met inbegrip van duplicaten terecht komen in de lijst met resultaten.
35
SQL genestelde queries Een SQL genestelde query (nested) is een SELECT vraag die een SELECT, UPDATE, INSERT of DELETE-query wordt gebruikt. Een eenvoudig voorbeeld van SQL genestelde vraag: SELECT Model FROM Product WHERE ManufacturerID IN (SELECT ManufacturerID FROM Fabrikant WHERE Fabrikant = ' Dell '); De genestelde vraag zal hierboven alle modellen van de tabel van het Product selecteren die door Dell wordt vervaardigd: Model
Inspiron B120 Inspiron B130 Inspiron E1705 Een geneste quety kan meer dan één niveau (zoals hierboven, waarbij er maar één enkele vraag is genesteld).
Een ander voorbeeld van genestelde query (subquery) is hier onder te zien: SELECT * FROM atrtikelen WHERE ArtikelNummer IN (SELECT ArtikelNummer FROM Orders WHERE OrderNummer IN (SELECT OrderNummer FROM Orders WHERE OrderDatum BETWEEN #01/12/2007# AND #29/05/2008#)); Doormiddel van de IN-keys worden er meerdere tabellen geraadpleegd.
36
SQL CREATE De CREATE DATABASE statement wordt gebruikt om tot een nieuw SQL database te maken heeft de volgende syntaxis: CREATE DATABASE DatabaseNaam De CREATE DATABASE implementatie en syntaxis verschilt tussen verschillende RDBMS. De CREATE TABLE statement wordt gebruikt om een nieuwe databasetabel te maken. Hier CREATE TABLE statement: CREATE TABLE TabelNaam ( Kolom1 DataType, Kolom2 DataType, Kolom3 DataType, .. ); DataType die na elke kolomnaam komt wordt gespecificeerd als een placeholder voor de echte gegevenstype van de kolom. Kolommen kunnen de volgende datatypes hebben: • Numeriek, • Tekst; CHAR • Kladblok (memo), • Datum, DATE • Logisch. LOGIC Het volgende CREATE TABLE statement maakt de tabel van Gebruikers die in één van de eerste hoofdstukken hebben gebruikt: CREATE TABLE SoCalledFriends ( VoorNaam CHAR (100), AchterNaam CHAR (100), GebDatum DATE );
37
SQL ALTER De CREATE TABLE statement hierboven heeft 3 kolommen; VoorNaam van type karakter (char) met lengte van 100 karakters, de kolom AchterNaam van het type karakter heeft een lengte van 100 karakters en kolom GebDatum heeft het type datum. De ALTER TABLE statement gebruikt om een tabel definitie te veranderen door het toevoegen van: kolommen, wijzigen of verwijderen van kolomen. Onder is de syntaxis van een ALTER TABLE statement te zien, waarin een nieuwe kolom aan de tabel wordt toevoegt: ALTER TABLE TableName ADD KolomNaam DataType; Om een kolom te verwijderen kan het volgende doen ALTER TABLE statement: ALTER TABLE TableName DROP KolomNaam;
38
SQL Views Een SQL VIEW is een virtuele tabel, die op SQL SELECT query gebaseerd is. Een ‘view’ ligt zeer dicht bij een echte databasetabel (het heeft kolommen en rijen zoals een gewone tabel), behalve dat echte tabellen gegevens opslaan, wat in een ‘view’ niet kan. Een ‘view’ bevat gegeven die dynamisch worden aangemaakt wanneer deze een verwijzing (referentie) heeft. Een views-verwijzing kan naar één of meer bestaande databasetabellen of andere ‘views’ worden gelegd. Elke ‘view’ bevat een soort filter voor tabelgegevens die daarin worden verwezen. Dit filter kan zowel naar de kolommen als de rijen van de tabellen worden toegepast. Hier volgt een voorbeeld van hoe een SQL VIEW gemaakt kan worden met de reeds eerder gebruikte tabellen ‘Product’ en ‘Fabrikant’: CREATE MANDING GemPrijs AS SELECT Fabrikant, website, e-Mail, AVG(Prijs) LIKE AvgPrice FROM Fabrikant JOIN Product ON Fabrikant.ManufacturerID = Product.ManufacturerID GROUP BY Fabrikant, website, e-Mail Een ‘view’ kan worden verwezen (referenced) en gebruikt in een andere ‘view’, van een SQL-vraag en van eerder opgeslagen procedures. Een ‘view’ kan worden verwezen zoals bij elke echte SQL databasetabel: SELECT * FROM `GemPrijs`
39
SQL Indexen De indexen in databases zijn te vergelijken met de indexen in een bibliotheek. Indexen zorgen ervoor dat informatie binnen een database snel terug te vinden is (lokaliseren). Als alle boeken in een bibliotheek alfabetisch geïndexeerd zouden zijn, dan zou nooit de gehele bibliotheek doorzocht worden om één enkel boek te vinden. Zoeken op de eerste letter van de boektitel en daarna de volgende letter zou de zoekactie beduidend verkleinen. Een Index kan op één enkele kolom of een combinatie van kolommen worden gemaakt. Een tabelindex is een databasestructuur die de waarden van één of meerdere kolommen in een databasetabel in een bepaalde volgorde sorteert (zoals de boeken in de bibliotheek ook gesorteerd staan). De tabelindex heeft pointers (wijzers) die naar een gespecificeerde tabel-kolom of combinatie tabel-kolommen verwijst. Deze wijzers worden afhankelijk van de soort orde en de index die is gespecificeerd toegepast. Een index maken kan met het CREATE INDEX SQL statement. Voor het ‘Model’ in de tabel Product wordt een index idxModel gemaakt: CREATE INDEX idxModel ON Product (Model); De syntaxis voor het maken van indexen varieert bij de verschillende RDBMSversies, De verdere details over indexen zullen per RDBMS bekeken moeten worden. Er zijn algemene regels voor het gebruik van indexen. Bij kleine tabellen, verbeteren de indexen geen prestaties. Indexen verbeteren alleen de prestaties van de database, wanneer de index wordt aangemaakt op tabel-join. Wanneer bij een query op een database kleine tabellen worden opgevraagd, dan zal het indexeren de zoeksnelheid verbeteren. Worden er bij een query grote delen van de database opgevraagd, dan zal indexeren juist vertragen. Het gebruik Indexen op kolommen die veel verschillende waarden bevat zal het de onderzoeksprestaties verbeteren, maar het zal de updates vertragen. Denk hierover na, voordat een index toegepast op een database.
40
SQL Hosting Wat is SQL hosting? De term SQL hosten verwijst naar een online dienst die database runt voor de ondersteuning van website(s). Een Web-hosting bedrijf kan SQL- hosting aanbieden, wat helpt om een dynamischere websites te ontwikkelen. De dagen van de statische websites zijn lang voorbij en achter elke populaire site zit tegenwoordig een database in de back-end. Om een kwalitatieve en gebruikers vriendelijke website te ontwikkelen en beheren is een SQL-account nodig. Groter ondernemingen gebruiken hun eigen database-servers, zoals: MS SQL-server, Oracel of MySQL. De SQL hosting aanbiedingen zijn de laatste jaren meer betaalbaar geworden. Perssonlijke websites gaan vaak via een webhostings bedrijf. Bekijk de mogelijkheden per hostings-bedrijf en bepaal zelf welke diensten nodig zijn tov van de kosten. SQL hosten op Linux De meeste hosting leveranciers van Linux bieden MySQL-database aan als standaard mogelijkheid. Oracel is een andere RDBMS die op Linux/Unix draait, maar die zijn zeldzaam tov het gebruik van SQL. Applicaties voor het lokaal-hosten van een web-server, MySQL, PHP en Peral op Linux. Zie: http://www.apachefriends.com SQL hosten op Windows Als Windows zijn er verschillende keuzes om SQL te hosten als back-end. De MS SQL-server is de meest populairste database server (deze vorm van hosten wordt vaak ‘SQL server hosting’ genoemd). Windows SQL hosting kan ook samen met een Oracel database werken, deze zijn kostbaarder – maar ook beter. Een goed alternatief voor Windows SQL hosting is MySQL. De meeste Windows bedrijfsoplossingen (applicaties en sites) ondersteunen MySQL-databases. Een andere populaire SQL database is MS-Access, maar deze database wordt alleen voor kleine websites en applicaties aanbevolen, het aantal dagelijkse te verwerken bezoekers voor dergelijke applicaties of websites is beperkt. Applicaties voor het lokaal-hosten van een web-server, MySQL, PHP en Peral op Windows. Zie: http://www.apachefriends.com SQL hosten op een Mac Op lokale systemen kan MySQL gehost worden bij zoals Windows als Linux. Veel webontwikkelaars gebruiken een Mac . Dus, op een Mac is het uiteraard ook mogelijk om SQL-t hosten. Applicaties voor het lokaal-hosten van een web-server, MySQL, PHP en Peral op een Mac. Zie: http://www.apachefriends.com of http://www.mamp.com
41
Bijlage: SQL Operators en functies (engels) Zie ook:
http://dev.mysql.com/doc/refman/5.0/en/func-op-summary-ref.html
Naam
Beschrijving:
ABS()
Return the absolute value
ACOS()
Return the arc cosine
ADDDATE()(v4.1.1)
Add dates
ADDTIME()(v4.1.1)
Add time
AES_DECRYPT()
Decrypt using AES
AES_ENCRYPT()
Encrypt using AES
AND, &&
Logical AND
ASCII()
Return numeric value of left-most character
ASIN()
Return the arc sine
ATAN2(), ATAN()
Return the arc tangent of the two arguments
ATAN()
Return the arc tangent
AVG()
Return the average value of the argument
BENCHMARK()
Repeatedly execute an expression
BETWEEN ... AND ...
Check whether a value is within a range of values
BIN()
Return a string representation of the argument
BINARY
Cast a string to a binary string
BIT_AND()
Return bitwise and
BIT_COUNT()
Return the number of bits that are set
BIT_LENGTH()
Return length of argument in bits
BIT_OR()
Return bitwise or
BIT_XOR()(v4.1.1)
Return bitwise xor
&
Bitwise AND
~
Invert bits
|
Bitwise OR 42
^
Bitwise XOR
CASE
Case operator
CAST()
Cast a value as a certain type
CEIL()
Return the smallest integer value not less than the argument
CEILING()
Return the smallest integer value not less than the argument
CHAR_LENGTH()
Return number of characters in argument
CHAR()
Return the character for each integer passed
CHARACTER_LENGTH()
A synonym for CHAR_LENGTH()
CHARSET()(v4.1.0)
Return the character set of the argument
COALESCE()
Return the first non-NULL argument
COERCIBILITY()(v4.1.1)
Return the collation coercibility value of the string argument
COLLATION()(v4.1.0)
Return the collation of the string argument
COMPRESS()(v4.1.1)
Return result as a binary string
CONCAT_WS()
Return concatenate with separator
CONCAT()
Return concatenated string
CONNECTION_ID()
Return the connection ID (thread ID) for the connection
CONV()
Convert numbers between different number bases
CONVERT_TZ()(v4.1.3)
Convert from one timezone to another
Convert()
Cast a value as a certain type
COS()
Return the cosine
COT()
Return the cotangent
COUNT(DISTINCT)
Return the count of a number of different values
COUNT()
Return a count of the number of rows returned
CRC32()(v4.1.0)
Compute a cyclic redundancy check value
CURDATE()
Return the current date
CURRENT_DATE(),
Synonyms for CURDATE() 43
CURRENT_DATE CURRENT_TIME(),
Synonyms for CURTIME()
CURRENT_TIME CURRENT_TIMESTAMP(),
Synonyms for NOW()
CURRENT_TIMESTAMP CURRENT_USER(),
Return the username and hostname combination
CURRENT_USER CURTIME()
Return the current time
DATABASE()
Return the default (current) database name
DATE_ADD()
Add two dates
DATE_FORMAT()
Format date as specified
DATE_SUB()
Subtract two dates
DATE()(v4.1.1)
Extract the date part of a date or datetime expression
DATEDIFF()(v4.1.1)
Subtract two dates
DAY()(v4.1.1)
Synonym for DAYOFMONTH()
DAYNAME()(v4.1.21)
Return the name of the weekday
DAYOFMONTH()
Return the day of the month (0-31)
DAYOFWEEK()
Return the weekday index of the argument
DAYOFYEAR()
Return the day of the year (1-366)
DECODE()
Decodes a string encrypted using ENCODE()
DEFAULT()
Return the default value for a table column
DEGREES()
Convert radians to degrees
DES_DECRYPT()
Decrypt a string
DES_ENCRYPT()
Encrypt a string
DIV(v4.1.0)
Integer division
/
Division operator
ELT()
Return string at index number
ENCODE()
Encode a string
ENCRYPT()
Encrypt a string 44
<=>
NULL-safe equal to operator
=
Equal operator
EXP()
Raise to the power of
EXPORT_SET()
Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
EXTRACT
Extract part of a date
FIELD()
Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()
Return the index position of the first argument within the second argument
FLOOR()
Return the largest integer value not greater than the argument
FORMAT()
Return a number formatted to specified number of decimal places
FOUND_ROWS()
For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause
FROM_DAYS()
Convert a day number to a date
FROM_UNIXTIME()
Format UNIX timestamp as a date
GET_FORMAT()(v4.1.1)
Return a date format string
GET_LOCK()
Get a named lock
>=
Greater than or equal operator
>
Greater than operator
GREATEST()
Return the largest argument
GROUP_CONCAT()(v4.1)
Return a concatenated string
HEX()
Return a hexadecimal representation of a decimal or string value
HOUR()
Extract the hour
IF()
If/else construct 45
IFNULL()
Null if/else construct
IN()
Check whether a value is within a set of values
INET_ATON()
Return the numeric value of an IP address
INET_NTOA()
Return the IP address from a numeric value
INSERT()
Insert a substring at the specified position up to the specified number of characters
INSTR()
Return the index of the first occurrence of substring
INTERVAL()
Return the index of the argument that is less than the first argument
IS_FREE_LOCK()
Checks whether the named lock is free
IS NOT NULL
NOT NULL value test
IS NOT
Test a value against a boolean
IS NULL
NULL value test
IS_USED_LOCK()(v4.1.0)
Checks whether the named lock is in use. Return connection identifier if true.
IS
Test a value against a boolean
ISNULL()
Test whether the argument is NULL
LAST_DAY(v4.1.1)
Return the last day of the month for the argument
LAST_INSERT_ID()
Value of the AUTOINCREMENT column for the last INSERT
LCASE()
Synonym for LOWER()
LEAST()
Return the smallest argument
<<
Left shift
LEFT()
Return the leftmost number of characters as specified
LENGTH()
Return the length of a string in bytes
<=
Less than or equal operator
<
Less than operator
LIKE
Simple pattern matching
LN()
Return the natural logarithm of the argument 46
LOAD_FILE()
Load the named file
LOCALTIME(), LOCALTIME
Synonym for NOW()
LOCALTIMESTAMP,
Synonym for NOW()
LOCALTIMESTAMP()(v4.0.6) LOCATE()
Return the position of the first occurrence of substring
LOG10()
Return the base-10 logarithm of the argument
LOG2()
Return the base-2 logarithm of the argument
LOG()
Return the natural logarithm of the first argument
LOWER()
Return the argument in lowercase
LPAD()
Return the string argument, left-padded with the specified string
LTRIM()
Remove leading spaces
MAKE_SET()
Return a set of comma-separated strings that have the corresponding bit in bits set
MAKEDATE()(v4.1.1)
Create a date from the year and day of year
MAKETIME(v4.1.1)
MAKETIME()
MASTER_POS_WAIT()
Block until the slave has read and applied all updates up to the specified position
MATCH
Perform full-text search
MAX()
Return the maximum value
MD5()
Calculate MD5 checksum
MICROSECOND()(v4.1.1)
Return the microseconds from argument
MID()
Return a substring starting from the specified position
MIN()
Return the minimum value
-
Minus operator
MINUTE()
Return the minute from the argument
MOD()
Return the remainder
%
Modulo operator
MONTH()
Return the month from the date passed 47
MONTHNAME()(v4.1.21)
Return the name of the month
NAME_CONST()(v5.0.12)
Causes the column to have the given name
NOT BETWEEN ... AND ...
Check whether a value is not within a range of values
!=, <>
Not equal operator
NOT IN()
Check whether a value is not within a set of values
NOT LIKE
Negation of simple pattern matching
NOT REGEXP
Negation of REGEXP
NOT, !
Negates value
NOW()
Return the current date and time
NULLIF()
Return NULL if expr1 = expr2
OCT()
Return an octal representation of a decimal number
OCTET_LENGTH()
A synonym for LENGTH()
OLD_PASSWORD()(v4.1)
Return the value of the old (pre-4.1) implementation of PASSWORD
||, OR
Logical OR
ORD()
Return character code for leftmost character of the argument
PASSWORD()
Calculate and return a password string
PERIOD_ADD()
Add a period to a year-month
PERIOD_DIFF()
Return the number of months between periods
PI()
Return the value of pi
+
Addition operator
POSITION()
A synonym for LOCATE()
POW()
Return the argument raised to the specified power
POWER()
Return the argument raised to the specified power
PROCEDURE ANALYSE()
Analyze the results of a query
QUARTER()
Return the quarter from a date argument
QUOTE()
Escape the argument for use in an SQL statement
RADIANS()
Return argument converted to radians 48
RAND()
Return a random floating-point value
REGEXP
Pattern matching using regular expressions
RELEASE_LOCK()
Releases the named lock
REPEAT()
Repeat a string the specified number of times
REPLACE()
Replace occurrences of a specified string
REVERSE()
Reverse the characters in a string
>>
Right shift
RIGHT()
Return the specified rightmost number of characters
RLIKE
Synonym for REGEXP
ROUND()
Round the argument
ROW_COUNT()(v5.0.1)
The number of rows updated
RPAD()
Append string the specified number of times
RTRIM()
Remove trailing spaces
SCHEMA()(v5.0.2)
A synonym for DATABASE()
SEC_TO_TIME()
Converts seconds to 'HH:MM:SS' format
SECOND()
Return the second (0-59)
SESSION_USER()
Synonym for USER()
SHA1(), SHA()
Calculate an SHA-1 160-bit checksum
SIGN()
Return the sign of the argument
SIN()
Return the sine of the argument
SLEEP()(v5.0.12)
Sleep for a number of seconds
SOUNDEX()
Return a soundex string
SOUNDS LIKE(v4.1.0)
Compare sounds
SPACE()
Return a string of the specified number of spaces
SQRT()
Return the square root of the argument
STD()
Return the population standard deviation
STDDEV_POP()(v5.0.3)
Return the population standard deviation
STDDEV_SAMP()(v5.0.3)
Return the sample standard deviation
STDDEV()
Return the population standard deviation 49
STR_TO_DATE()(v4.1.1)
Convert a string to a date
STRCMP()
Compare two strings
SUBDATE()
A synonym for DATE_SUB() when invoked with three arguments
SUBSTR()
Return the substring as specified
SUBSTRING_INDEX()
Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING()
Return the substring as specified
SUBTIME()(v4.1.1)
Subtract times
SUM()
Return the sum
SYSDATE()
Return the time at which the function executes
SYSTEM_USER()
Synonym for USER()
TAN()
Return the tangent of the argument
TIME_FORMAT()
Format as time
TIME_TO_SEC()
Return the argument converted to seconds
TIME()(v4.1.1)
Extract the time portion of the expression passed
TIMEDIFF()(v4.1.1)
Subtract time
*
Times operator
TIMESTAMP()(v4.1.1)
With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()(v5.0.0)
Add an interval to a datetime expression
TIMESTAMPDIFF()(v5.0.0)
Subtract an interval from a datetime expression
TO_DAYS()
Return the date argument converted to days
TRIM()
Remove leading and trailing spaces
TRUNCATE()
Truncate to specified number of decimal places
UCASE()
Synonym for UPPER()
-
Change the sign of the argument
UNCOMPRESS()(v4.1.1)
Uncompress a string compressed 50
UNCOMPRESSED_LENGTH()(
Return the length of a string before compression
v4.1.1) UNHEX()(v4.1.2)
Convert each pair of hexadecimal digits to a character
UNIX_TIMESTAMP()
Return a UNIX timestamp
UPPER()
Convert to uppercase
USER()
Return the current username and hostname
UTC_DATE()(v4.1.1)
Return the current UTC date
UTC_TIME()(v4.1.1)
Return the current UTC time
UTC_TIMESTAMP()(v4.1.1)
Return the current UTC date and time
UUID()(v4.1.2)
Return a Universal Unique Identifier (UUID)
VALUES()(v4.1.1)
Defines the values to be used during an INSERT
VAR_POP()(v5.0.3)
Return the population standard variance
VAR_SAMP()(v5.0.3)
Return the sample variance
VARIANCE()(v4.1)
Return the population standard variance
VERSION()
Returns a string that indicates the MySQL server version
WEEK()
Return the week number
WEEKDAY()
Return the weekday index
WEEKOFYEAR()(v4.1.1)
Return the calendar week of the date (0-53)
XOR
Logical XOR
YEAR()
Return the year
YEARWEEK()
Return the year and week
51
