©2014 Olivier Parent
[TOC]
In de voorbeelden wordt de ontwikkelomgeving AHS Laravel Homestead gebruikt.
De beschikbare DBMS'en in deze ontwikkelomgeving zijn:
Het is ook mogelijk om extra DBMS'en te installeren zoals MongoDB.
In de meeste voorbeelden wordt MySQL gebruikt.
Tip: Mocht je wijzigingen moeten aanbrengen aan de standaardinstallatie, dan vind je in Handleiding Ubuntu-server een hoofdstuk over de installatie en configuratie van MySQL.
Structured Query Language is een gestandaardiseerde taal om gegevens te beheren in een (relationele) database.
Het net zoals HTML een declaratieve taal. Dit wil zeggen dat het beschrijft wat er moet gebeuren, niet hoe het moet gebeuren.
In deze cursus gaan we de populaire opensource databaseserver MySQL 5.6 van Oracle gebruiken. Er is ook een opensource kloon van de hand van de oorspronkelijke maker van MySQL: MariaDB.
Weetjes: My en Maria zijn de twee dochters van Michael Widenius. Vandaar de namen MySQL en MariaDB. Widenius is de medeoprichter van MySQL AB de Fins-Zweedse firma achter MySQL. De firma werd overgenomen door Sun Microsystems die later op zijn beurt door Oracle werd overgenomen. Uit onvrede hierover creëerde Widenius het opensource alternatief MariaDB.
Begin jaren 70 ontwikkelde IBM de taal onder de naam Sequel. Vandaar dat veel ontwikkelaars SQL nog altijd uitspreken als "Sequel". De officiële uitspraak is echter “Es-Ku-El” (/ɛsk'juːˈɛl /).
De uitspraak van de afkorting "SQL" verschilt ook volgens softwareleverancier:
- Oracle MySQL → My-“Es-Ku-El”
- Microsoft SQL Server → Microsoft “Sie-kwel” Server
De SQL-standaard is ISO/IEC 9075 waarvan de meest recente versie dateert van 2011.
Elk Relationeel Databasemanagementsysteem (RDBMS) gebruikt zijn eigen SQL-dialect dat een beetje afwijkt van de standaard.
Deze dialecten zijn ontstaan uit technische noodzaak, omdat de standaard niet alle functionaliteiten van de RDBMS omvat of omgekeerd. Ook de datatypen kunnen afwijken van de standaard.
Zo werkt Microsoft SQL Server met een uitbreiding van SQL-standaard: Transact-SQL (of kortweg T-SQL).
SQL-code is niet-hoofdlettergevoelig, maar sleutelwoorden schrijft men bij voorkeur in kapitalen.
SQL-code heeft een vrije vorm. Dit wil zeggen dat regeleinden, extra spaties of tabtekens geen invloed hebben.
Elke regel SQL-code wordt begrensd door een puntkomma (;). Dit is bij de meeste RDBMS'en verplicht, maar soms is het niet nodig als er maar één regel per keer uitgevoerd kan worden.
Commentaar begint met twee koppeltekens en een spatie (-- ), maar niet elk RDBMS laat commentaar toe. Zo laat Microsoft Access laat dit bijvoorbeeld niet toe.
Met SQL-statements kunnen de vier bewerkingen op databasegegevens uitgevoerd worden:
| Bewerking | Betekenis |
|---|---|
| Create | aanmaken/invoeren |
| Read | uitlezen |
| Update | wijzigen |
| Delete/Drop | verwijderen |
Data Definition Language beschrijft de structuur van de database. Wordt gebruikt door de DBD (Database Designer) en softwareontwerpers.
SQL-sleutelwoorden:
ALTERCREATEDROPRENAMETRUNCATE- …
Data Manipulation Language bewerkt de gegevens in de database verwerkt. Wordt gebruikt door softwareontwerpers en softwareontwikkelaars.
SQL-sleutelwoorden:
INSERTSELECTUPDATEREPLACE- …
De speciale statements:
- Administration Statements
- Utility Statements: hebben een speciaal nut.
Deze statements gebruikt je normaal gezien enkel via de Command-Line Interface van het DBMS, en nooit bij het programmeren van gewone webapps.
Deze statements gebruikt een DBA (Database Administrator) om het DBMS te beheren (o.a. databasegebruikers en databases aanmaken).
SQL-sleutelwoorden:
CREATE USERDROP USERGRANTREVOKESHOW DATABASESSHOW SCHEMAS- …
Deze statements hebben een speciaal nut.
SQL-sleutelwoorden:
DESCRIBEEXPLAINUSE- …
Afspraken voor codevoorbeelden
- Accolades (
{en}): bevatten een placeholder die door een echte waarde moet worden vervangen.- Rechte haakjes (
[en]): bevatten optionele code.- Haakjes (
<en>): bevatten een toets of teken.- Ellips, beletselteken (
…): enzovoort, of: zet hier een operand.- Verticale streep (
|): scheidt mogelijke sleutelwoorden van elkaar.
Aanmelden op de MySQL Server vanaf de Command-Line Interface doe je met de MySQL Command-Line Tool mysql.
Hulp over het gebruik vraag je met de optie --help of -?:
vagrant@homestead$ mysql -?
Je kan de versie van MySQL controleren met:
vagrant@homestead$ mysql --version
Laten we bijvoorbeeld aanmelden op de MySQL Server van Laravel Homestead. De databasegebruiker is homestead en het wachtwoord is secret.
Opmerking: Je wordt gevraagd om het wachtwoord in te typen, maar de cursor zal niet bewegen. Deze beveiligingsinstelling voorkomt dat de lengte van het wachtwoord zichtbaar is.
OPGELET: Een
<backspace>wordt als nieuw teken beschouwd!
vagrant@homestead$ mysql --user=homestead --password
password: _
mysql>
Je kan ook onmiddellijk inloggen met het wachtwoord.
vagrant@homestead$ mysql --user=homestead --password=secret
Er bestaat ook een verkorte vorm van de opties:
vagrant@homestead$ mysql -uhomestead -psecret
OPGELET: Als je het wachtwoord onmiddellijk invult, zal dit zichtbaar zijn met de shellopdracht
history. Dit houdt een behoorlijk veiligheidsrisico in! Gebruik deze manier daarom nooit op een productieserver!
Afmelden doe je met exit:
mysql> exit
Bye
vagrant@homestead$ _
Je kan met \! vanuit de MySQL Command-Line Tool shellopdrachten aanroepen. Om bijvoorbeeld het scherm te wissen met de shellopdracht clear:
mysql> \! clear
Dit heeft hetzelfde effect als:
vagrant@homestead$ clear
Enkel de Databasebeheerder, beter bekend als DBA (Database Administrator), wordt verondersteld zich aan te melden de Credentials van de rootgebruiker. Deze rootgebruiker die meestal root als databasegebruikersnaam heeft, heeft alle mogelijke rechten.
In Laravel Homestead heeft de rootgebruiker deze Credentials:
- Databasegebruikersnaam:
root - Databasewachtwoord:
secret
Developers mogen in principe enkel databaseaccounts met beperkte(re) rechten gebruiken.
In Laravel Homestead is dat dan bijvoorbeeld:
- Databasegebruikersnaam:
homestead - Databasewachtwoord:
secret
Het is erg aangewezen om per applicatie een aparte databasegebruiker aan te maken die enkel rechten heeft op de door de applicatie gebruikte databases. Zo voorkom je dat een applicatie per ongeluk (of moedwillig na een hack) databases van een andere applicatie kan beschadigen.
Alle databasegebruikers staan in de tabel user van de database mysql. Je kan ze eenvoudig oplijsten met een SELECT-statement.
Bijvoorbeeld:
mysql> SELECT * FROM `mysql`.`user`;
Opmerking: De asterisk (
*) betekent alle kolommen van de tabel, niet alle rijen!
Om het overzicht te bewaren kan je enkel bepaalde kolommen laten tonen:
mysql> SELECT Host, User, Password
-> FROM `mysql`.`user`;
Een databasegebruiker toevoegen:
-- Databasegebruiker aanmaken
CREATE USER '{db_gebruikersnaam}'
IDENTIFIED BY '{db_wachtwoord}';OPGELET: MySQL laat maximaal 16 tekens toe in de databasegebruikersnaam!
Bijvoorbeeld:
mysql> CREATE USER 'Olivier'
-> IDENTIFIED BY 'GeheimWachtwoord';
Tip: Je kan aan de databasegebruikersnaam ook een host of IP-adres toevoegen, zodat de databasegebruiker enkel vanaf die host of dat IP-adres kan aanmelden.
Bijvoorbeeld:
'Olivier'@'%''Olivier'@'127.0.0.1''Olivier'@'localhost'De standaardwaarde is
%en wil zeggen: om het even welke host of welk IP-adres. Net omdat het de standaardwaarde is, mag@'%'weggelaten worden.
Om wachtwoorden te hashen gebruikt MySQL de functie PASSWORD().
Bijvoorbeeld:
mysql> SELECT PASSWORD('GeheimWachtwoord') AS `Password Hashcode`;
+-------------------------------------------+
| Password Hashcode |
+-------------------------------------------+
| *3D79C803D354D9E2929EEE4F6FE9C151728C2801 |
+-------------------------------------------+
1 row in set (0.00 sec)
OPGELET: De
PASSWORD()-functie mag enkel gebruikt worden om de wachtwoorden van databasegebruikers te hashen. De toegang tot databaseservers is meestal zo goed beveiligd dat een hacker al fysieke toegang tot de server moet hebben om te kunnen inbreken. Voor bijvoorbeeld de wachtwoorden van websitegebruikers is de functie onvoldoende. Met Rainbow Tables (tabellen met hashcodes en de oorspronkelijke tekst) kan een hacker snel en makkelijk het oorspronkelijk wachtwoord achterhalen. Rainbow Tables maken hackers zelf, maar ze zijn ook gratis te vinden op websites zoals https://www.freerainbowtables.com/
Met onderstaand SELECT-statement kan je een overzicht krijgen van alle databasegebruikers, en zien wie als wachtwoord GeheimWachtwoord heeft:
mysql> SELECT Host, User, Password,
-> Password = PASSWORD('GeheimWachtwoord')
-> AS `Is het wachtwoord 'GeheimWachtwoord'?`
-> FROM `mysql`.`user`;
Je kan de functie PASSWORD() gebruiken om het wachtwoord van databasegebruikers te wijzigen met behulp van een UPDATE-statement. Bijvoorbeeld:
mysql> UPDATE `mysql`.`user`
-> SET Password = PASSWORD('AnderWachtwoord')
-> WHERE User = 'Olivier';
Een databasegebruiker verwijderen doe je met een DROP USER-statement.
-- Databasegebruiker verwijderen
DROP USER '{db_gebruikersnaam}';Bijvoorbeeld:
mysql> DROP USER 'Olivier';
Zie ook:
Een databasegebruiker heeft een aantal Rechten (Privileges) om dingen te doen met of in een database. Je kan een lijst alle mogelijke rechten die de server ondersteunt opvragen met:
-- Alle mogelijke rechten oplijsten (voor deze databaseserver)
SHOW PRIVILEGES;Bijvoorbeeld:
mysql> SHOW PRIVILEGES;
Om de rechten van een bepaalde databasegebruiker op te vragen:
-- Alle toegekende rechten voor een databasegebruiker
SHOW GRANTS FOR '{db_gebruiker}';Bijvoorbeeld:
mysql> SHOW GRANTS FOR 'Olivier';
Zie ook:
Een databasegebruiker alle rechten (ALL PRIVILEGES) op alle tabellen (*) van een bepaalde database geven (GRANT … TO):
-- Alle rechten toekennen aan een databasegebruiker
GRANT ALL PRIVILEGES
ON `{databasenaam}`.*
TO '{db_gebruikersnaam}';Het bovenstaande SQL-statement geeft de gebruiker alle rechten behalve GRANT OPTION (zelf rechten kunnen geven en ontnemen).
Opmerking: De backtick (
`) is meestal optioneel tenzij de naam een teken bevat die een speciale betekenis heeft in SQL, zoals bijvoorbeeld een koppelteken (-).
Bijvoorbeeld:
mysql> GRANT ALL PRIVILEGES
-> ON `database_arteveldehogeschool_be`.*
-> TO 'Olivier';
De database hoeft zelfs niet te bestaan, want de databasegebruiker heeft ook rechten om de database aan te maken.
Tip: Bij een
GRANT…TOmetIDENTIFIED BYzal de databasegebruiker automatisch aangemaakt worden als die niet bestaat.-- Alle rechten toekennen aan nog niet bestaande databasegebruiker GRANT ALL PRIVILEGES ON `{databasenaam}`.`{tabel}` TO '{db_gebruikersnaam}' IDENTIFIED BY '{db_wachtwoord}';Bijvoorbeeld:
mysql> GRANT ALL PRIVILEGES -> ON `database_arteveldehogeschool_be`.* -> TO 'Olivier' IDENTIFIED BY 'GeheimWachtwoord';In bovenstaand voorbeeld gelden de rechten op alle tabellen (
*) van de database.
Rechten kunnen ook meer specifiek toegekend worden. Bijvoorbeeld:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
-> ON `database_arteveldehogeschool_be`.*
-> TO 'Olivier';
Zie ook:
Een databasegebruiker alle rechten (ALL PRIVILEGES) op alle tabellen (*) van een bepaalde database ontnemen (REVOKE … FROM):
-- Alle rechten ontnemen van een databasegebruiker
REVOKE ALL PRIVILEGES
ON `{databasenaam}`.*
FROM '{db_gebruikersnaam}';Bijvoorbeeld:
mysql> REVOKE ALL PRIVILEGES
-> ON `database_arteveldehogeschool_be`.*
-> FROM 'Olivier';
Een database is een verzameling van tabellen. Die tabellen kunnen daarenboven ook nog eens gegroepeerd worden in schema's.
Opmerking: MySQL maakt geen onderscheid tussen een database en een schema. Daarom zijn de de sleutelwoorden
DATABASEenSCHEMAaliassen. Andere DBMS'en, zoals PostgreSQL, maken wel degelijk een onderscheid tussen de twee!
-- Databases tonen
SHOW DATABASES;Bijvoorbeeld:
mysql> SHOW DATABASES;
Zie ook:
Een database aanmaken doe je met een CREATE-statement
-- Database aanmaken
CREATE DATABASE `{databasenaam}`;Tip: Voorkom foutmeldingen door voorwaardelijke statements te gebruiken. Hiervoor gebruik je
IF EXISTSofIF NOT EXISTSBijvoorbeeld:
mysql> CREATE DATABASE IF NOT EXISTS `database_arteveldehogeschool_be`;
In MySQL kan je exact hetzelfde bereiken met SCHEMA omdat dit een alias is voor DATABASE:
-- Schema aanmaken
CREATE SCHEMA [IF NOT EXISTS] `{schemanaam}`;Je kan ook een standaard tekencodering (CHARACTER SET) en sorteervolgorde (COLLATE) instellen.
-- Database aanmaken
CREATE DATABASE [IF NOT EXISTS] `{databasenaam}`
DEFAULT CHARACTER SET {tekencodering}
COLLATE {sorteervolgorde};Opmerking: De collatie (collation) is de sorteervolgorde van de tekens. In bepaalde talen is dit heel belangrijk. Zo wordt bijvoorbeeld de Duitse letter 'Ä' als gewone 'A' aanzien bij het sorteren, terwijl de Zweedse letter 'Å' na de 'Z' komt in het alfabet. Bij een sorteervolgorde die hoofdlettergevoelig is komt bovenkast voor onderkast. Dus eerst 'A' tot en met 'Z' en dan pas 'a' tot en met 'z'.
Bijvoorbeeld:
mysql> CREATE DATABASE IF NOT EXISTS `database_arteveldehogeschool_be`
-> DEFAULT CHARACTER SET utf8
-> COLLATE utf8_general_ci;
Dit wil zeggen:
IF NOT EXISTS: Het create statement wordt enkel uitgevoerd als er nog geen database is met deze naam.CHARACTER SET utf8: De standaardtekencodering van de tabellen is UTF-8 (8-bit Unicode Transformation Format).COLLATE utf8_general_ciwil zeggen dat de sorteervolgorde gebeurt volgens:utf8: UTF-8-tekencoderinggeneral: algemeen (niet-taalspecifiek)ci: niet-hooflettergevoelig (case insensitive)
Tip: Je can het
CREATE DATABASE-statement van een bestaande database opvragen met:SHOW CREATE DATABASE `{databasenaam}`
Zie ook:
Met USE geef je aan op welke database de SQL-statements van toepassing zijn. Dit blijft zo voor de rest van de sessie.
-- Database (of schema) als standaard instellen
USE `{databasenaam}`;Bijvoorbeeld:
mysql> USE `database_arteveldehogeschool_be`;
Zie ook:
Een database verwijderen doe je met een DROP-statement
-- Database verwijderen
DROP DATABASE [IF EXISTS] `{databasenaam}`;Bijvoorbeeld:
mysql> DROP DATABASE IF EXISTS `database_arteveldehogeschool_be`;
Zie ook:
Een backup maken van een database kan met het programma mysqldump. Je gebruikt dezelfde credentials als voor mysql (bijv. databasegebruiker homestead met als wachtwoord secret).
Hulp over het gebruik vraag je met de optie --help of -?:
vagrant@homestead$ mysqldump -?
Je kan de SQL-statements naar het scherm laten dumpen. Bijvoorbeeld:
vagrant@homestead$ mysqldump -uhomestead -p database_arteveldehogeschool_be
Enter password: _
Je kan de dump ook omleiden naar een bestand door de redirect output to file-operator (>) van de shell te gebruiken. Bijvoorbeeld:
vagrant@homestead$ mysqldump -uhomestead -p database_arteveldehogeschool_be > ~/Code/dump.sql
Enter password: _
Als je een historiek wil bijhouden kan je de dumps ook een timestamp geven met $(date +"%Y-%m-%d_%H%M%S"). Bijvoorbeeld:
vagrant@homestead$ mysqldump -uhomestead -p database_arteveldehogeschool_be > ~/Code/dump_$(date +"%Y-%m-%d_%H%M%S").sql
Enter password: _
Om de CREATE DATABASE-statements ook te laten generen moet je de optie --databases of -B toevoegen. Met deze optie kan je ook meerdere databases tegelijk back-uppen door de databasenamen na elkaar op te geven, telkens met een spatie ertussen.
vagrant@homestead$ mysqldump -uhomestead -p --databases database_arteveldehogeschool_be > ~/Code/dump.sql
Enter password: _
Je kan ook alle databases laten dumpen, (dus ook die databases die MySQL zelf gebruikt zoals mysql, door de optie --all-databases of -A te gebruiken.
vagrant@homestead$ mysqldump -uhomestead -p --all-databases > ~/Code/dump.sql
Enter password: _
Je kan de dumpbestanden ook comprimeren met gzip en de optie -c (output to console):
vagrant@homestead$ mysqldump -uhomestead -p --databases database_arteveldehogeschool_be | gzip -c > dump.sql.gz
De dumpbestanden terugzetten doe je met mysql.
Een dumpbestand zonder DATABASE CREATE-statement terugzetten:
vagrant@homestead$ mysql -uhomestead -p database_arteveldehogeschool_be < ~/Code/dump.sql
Enter password: _
Een dumpbestand met DATABASE CREATE-statement terugzetten:
vagrant@homestead$ mysql -uhomestead -p < ~/Code/dump.sql
Enter password: _
Een gecomprimeerd dumpbestand terugzetten door het te decomprimeren met gunzip en de optie -c (output to console):
vagrant@homestead$ gunzip -c ~/Code/dump.sql.gz | mysql -uhomestead -p
Enter password: _
Zie ook:
Een database bevat Tabellen (Tables). Elke tabel bestaat uit Kolommen (Columns) en kan Rijen (Rows) bevatten. Een rij heeft voor elke kolom een overeenkomstig Veld (Field) waarin de Gegevens (Data) opgeslagen worden. De eigenschappen van een veld worden door de kolom bepaald.
Met SHOW TABLES kan je alle tabellen in een database of schema opvragen.
SHOW TABLES;Bijvoorbeeld:
mysql>USE database_arteveldehogeschool_be;
mysql>SHOW TABLES;
Empty set (0.00 sec)
mysql> _
Zie ook:
-- Tabel met kolommen maken
CREATE TABLE [IF NOT EXISTS] {tabel} (
{kolom_1} {DATATYPE}[,
{kolom_2} {DATATYPE}…]
);Bijvoorbeeld:
mysql> CREATE TABLE IF NOT EXISTS `users` (
-> id INT,
-> first_name VARCHAR(255),
-> last_name VARCHAR(255),
-> username VARCHAR(255),
-> password CHAR(60),
-> created_at TIMESTAMP
-> );
Tip: Vraag het
CREATE-statement van de tabel op metSHOW CREATE TABLE.Hiermee kan je:
- een backup van de structuur maken;
- gedetailleerde informatie over de structuur van een tabel te zien.
-- CREATE-statement tonen SHOW CREATE TABLE {tabel};
Zie ook:
EXPLAIN (of DESCRIBE) is een Utility Statement waarmee de eigenschappen van de kolommen omschreven worden:
- Field;
- Type;
- Null;
- Key;
- Default;
- Extra.
-- Eigenschappen van de kolommen
EXPLAIN|DESCRIBE {tabel};Bijvoorbeeld:
mysql> EXPLAIN users;
Zie ook:
Met ALTER TABLE kan je een bestaande tabel wijzigen.
ADD- Voeg een kolom toe.
COLUMNis optioneel.
-- Kolom toevoegen
ALTER TABLE {tabel}
ADD [COLUMN] {kolom} {DATATYPE};
-- Meerdere kolommen toevoegen
ALTER TABLE {tabel}
ADD [COLUMN] (
{kolom_1} {DATATYPE}[,
{kolom_2} {DATATYPE}…]
);Met CHANGE (of CHANGE COLUMN) wijzig je de naam van de kolom. Je moet ook het datatype meegeven, zodat je er bovendien ook het het datatype mee kan wijzigen.
-- Kolom nieuwe naam geven, en eventueel ook een ander datatype
ALTER TABLE {tabelnaam}
CHANGE [COLUMN] {kolomnaam_oud}
{kolomnaam_nieuw} {DATATYPE};Met MODIFY (of MODIFY COLUMN) kan je enkel het datatype van de kolom wijzigen.
-- De kolom een ander datatype geven
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolom} {DATATYPE};UNSIGNED- Enkel positieve getallen.
- Verdubbelt de toegelaten maximumwaarde.
- Gebruik dit bijvoorbeeld samen met de beperkingen
PRIMARY KEYenAUTO_INCREMENT.
SIGNED- Positieve en negatieve getallen.
- Standaardinstelling, maar kan gebruikt worden om
UNSIGNEDte verwijderen.
-- Kolom wijzigen naar enkel positieve getallen
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolomnaam} {DATATYPE} UNSIGNED;
-- Kolom terigzetten naar standaardinstelling voor getallen
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolomnaam} {DATATYPE} SIGNED;Een kolom kan je een aantal randvoorwaarden (constraints) opleggen:
PRIMARY KEY: PK, primaire sleutel van de tabel.NOT NULL: de waarde moet ingevuld worden.NULL: de waarde mag leeggelaten worden.UNIQUE: elke rij moet een unieke waarde hebben in deze kolom van de tabel.AUTO_INCREMENT: bij elke nieuwe rij wordt de waarde van de integer met 1 verhoogd (geïncrementeerd). De kolom is een sleutel. Wordt vaak wordt vaak gebruikt om een surrogaatsleutel te maken.
-- Kolom wijzigen naar PK
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolomnaam} {DATATYPE} PRIMARY KEY;
-- PK verwijderen
ALTER TABLE {tabelnaam}
DROP PRIMARY KEY;
-- Kolom wijzigen naar AUTO_INCREMENT
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolomnaam} {DATATYPE} AUTO_INCREMENT;NOT NULL- Dit attribuut mag geen null-waarde (ontbrekende waarde) hebben.
- Verwijder de beperking met
NULL
-- Kolom wijzigen zodat er een waarde ingevuld moet worden
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolomnaam} {DATATYPE} NOT NULL;
-- De beperking ongedaan maken
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolomnaam} {DATATYPE} NULL;UNIQUE- Elke rij moet een unieke waarde hebben in deze kolom van de tabel.
-- Kolom wijzigen naar unieke waarden
ALTER TABLE {tabelnaam}
MODIFY [COLUMN] {kolomnaam} {DATATYPE} UNIQUE;Met DEFAULT kan je een standaardwaarde voorzien voor een kolom. Telkens een nieuwe rij wordt ingevoegd zonder een waarde op te geven voor die kolom, wordt deze standaardwaarde gebruikt.
-- Standaardwaarde voor de kolom
ALTER TABLE {tabel}
MODIFY [COLUMN] {kolom} {DATATYPE} DEFAULT {waarde};Met COMMENT kan je commentaar toevoegen aan een kolom. Dit is handig voor DBA's (databasebeheerders) of developers zodat die weten welk soort gegevens in de kolom staan.
-- Commentaar voor de kolom
ALTER TABLE {tabel}
MODIFY [COLUMN] {kolom} {DATATYPE} COMMENT {waarde};Voorgaande wijzigingen kunnen in één statement gecombineerd worden. De volgorde is niet van belang.
-- Kolom wijzigen
ALTER TABLE {tabel}
MODIFY {kolom} {DATATYPE} PRIMARY KEY NOT NULL� UNSIGNED;CONSTRAINT PRIMARY KEY
-- PK toevoegen
ALTER TABLE {tabel}
ADD CONSTRAINT PRIMARY KEY ({kolom});
-- Samengestelde PK toevoegen
ALTER TABLE {tabel}
ADD CONSTRAINT PRIMARY KEY ({kolom_1}[, {kolom_2}…]);FOREIGN KEY- Externe Sleutel(s).
- Hebben een naam.
- Kunnen verwijderd worden aan de hand van die naam.
REFERENCES- De tabel en de PK waarnaar verwezen wordt.
- De tabel moet reeds bestaan!
-- FK toevoegen
ALTER TABLE {tabel_A}
ADD CONSTRAINT fk_{tabel_A}_{tabel_B}{0}
FOREIGN KEY ({kolom_A1})
REFERENCES {tabel_B} ({kolom_B1});
-- FK en PK toevoegen
ALTER TABLE {tabel_A}
ADD CONSTRAINT PRIMARY KEY ({kolom_A1}),
ADD CONSTRAINT fk_{tabel_A}_{tabel_B}{0}
FOREIGN KEY ({kolom_A1})
REFERENCES {tabel_B} ({kolom_B1});CONSTRAINT CHECK- Expressie waaraan voldaan moet worden.
OPGELET: Voorlopig in nog geen enkele storage engine van MySQL geïmplementeerd. Gebruik een
TRIGGERom hetzelfde effect te bereiken.
-- Controle toevoegen
ALTER TABLE {tabel}
ADD CONSTRAINT CHECK ({expressie});
-- Gebruiker moet minstens 18 jaar zijn
-- WERKT NIET IN MySQL!
ALTER TABLE users
ADD CONSTRAINT CHECK (user_age >= 18);Zie ook:
Met TRUNCATE TABLE kan je een tabel leegmaken. In feite is dit hetzelfde als de tabel verwijderen en daarna opnieuw aanmaken (to truncate betekent afknotten).
-- Tabel leegmaken
TRUNCATE TABLE {tabel};Met RENAME TABLE kan je een tabel een nieuwe naam geven.
-- Tabel nieuwe naam geven
RENAME TABLE
{tabelnaam_oud} TO {tabelnaam_nieuw};
-- Meerdere tabellen een nieuwe naam geven
RENAME TABLE
{tabelnaam_A_oud} TO {tabelnaam_A_nieuw}[,
{tabelnaam_B_oud} TO {tabelnaam_B_nieuw}…];DROP COLUMN: Kolom verwijderen.DROP PRIMARY KEY: Primaire Sleutel verwijderen.DROP FOREIGN KEY: Externe Sleutel verwijderen.DROP KEYSleutel verwijderen. Na het verwijderen van een Externe Sleutel blijft er nog een gewone Sleutel over.
-- Kolom verwijderen
ALTER TABLE {tabel}
DROP COLUMN {kolom};
-- PK (Primaire Sleutel) verwijderen
ALTER TABLE {tabel}
DROP PRIMARY KEY;
-- FK (Externe Sleutel) verwijderen
ALTER TABLE {tabel_A}
DROP FOREIGN KEY fk_{tabel_A}_{tabel_B}{0},
DROP KEY fk_{tabel_A}_{tabel_B}{0};De vier basisbewerkingen die je op rijen kan uitvoeren zijn CRUD:
- Create
- Read
- Update
- Delete (Drop)
Zie ook:
Om waardes van het type string toe te voegen gebruikt men enkele aanhalingstekens (').
De set van waarden staan tussen ronde haakjes.
-- Rij toevoegen aan tabel
INSERT INTO {tabel} (
{kolom_1}[,
{kolom_2}…]
)
VALUES (
{waarde_1}[,
{waarde_2}…]
);Bijvoorbeeld:
mysql> INSERT INTO addresses (
-> street,
-> number
-> )
-> VALUES (
-> 'Industrieweg',
-> 232
-> );
In bovenstaand voorbeeld gaan we ervan uit dat:
- er een kolom
idbestaat die de PK is en als datatypeINTEGER AUTO_INCREMENTheeft, zodat elke nieuwe rij automatisch een veldidkrijgt met een waarde die 1 hoger is dan die van de vorige rij; numbereen integer (geheel getal) is, zodat we de waarde zonder rechte aanhalingstekens schrijven.
Meerdere rijen toevoegen kan ook. De sets van waarden worden door een komma (,) gescheiden
INSERT [INTO] {tabel} (
{kolom_1}[,
{kolom_2}…]
)
VALUES (
{waarde_1a}[,
{waarde_2a}…]
)[, (
{waarde_1b}[,
{waarde_2b}…]
)…];Bijvoorbeeld:
mysql> INSERT INTO addresses
-> (street, number)
-> VALUES
-> ('Industrieweg', 232),
-> ('Hoogpoort', 15);
Zie ook:
De asterisk (*) is een jokerteken waarmee je alle kolommen selecteert, niet alle rijen!
Als er uit meerdere tabellen tegelijk geselecteerd wordt, moet de tabelnaam gespecificeerd worden.
-- Toon de rijen met alle kolommen (*) van de tabel
SELECT * FROM {tabel};
--
SELECT
{kolom_1}[,
{kolom_2}…]
FROM {tabel};
--
SELECT
{tabel}.{kolom_1}[,
{tabel}.{kolom_2}…]
FROM {tabel};Tip: Gebruik een alias (
AS) om query's in te korten of beter leesbaar te maken. Een alias kan dan doorheen de query gebruikt worden.Er zijn twee soorten:
- kolomalias
- tabelalias
Voor tabelaliassen is het sleutelwoord
ASoptioneel. Laat het daarom bij voorkeur weg.
-- Alias voor de tabelnaam
SELECT
{tabelalias}.{kolom_1}[,
{tabelalias}.{kolom_2}…]
FROM {tabel} [AS] {tabelalias};Bijvoorbeeld:
mysql> SELECT u.first_name
-> FROM users AS u;
of beter en korter:
mysql> SELECT u.first_name
-> FROM users u;
-- Aliassen voor de tabelnaam en de kolomnaam
SELECT
{tabelalias}.{kolom_1} AS {kolomalias_1} [,
{tabelalias}.{kolom_2} AS {kolomalias_2}…]
FROM {tabel} [AS] {tabelalias};Tip: Gebruik back ticks (`) als de alias een spatie of een gereserveerd teken of woord bevat.
mysql> SELECT -> u.first_name �AS `De gebruikersnaam` -> FROM users u;
Met het sleutelwoord LIMIT kan het aantal rijen uit het queryresultaat beperkt worden.
Om {n} rijen te tonen.
Met een optionele offset kan de selectierij opgegeven worden vanaf waar de {n} volgende rijen getoond moeten worden.
OPGELET: Microsoft SQL Server gebruikt hiervoor
TOP, maar de syntaxis is volledig anders.
-- {n} rijen tonen
SELECT {kolom(men)}
FROM {tabel}
LIMIT [{offset},]{n};Voorbeeld 1: de 4 eerste rijen tonen uit het queryresultaat.
mysql> SELECT *
-> FROM users
-> LIMIT 4;
Voorbeeld 2: eerst 5 rijen overslaan uit het queryresultaat en dan de eerste 4 resterende rijen tonen.
mysql> SELECT *
-> FROM users
-> LIMIT 5,4;
-- Alternatieve manier
SELECT {kolom(men)}
FROM {tabel}
LIMIT {n} [OFFSET {offset}];Voorbeeld 3: eerst 5 rijen overslaan uit het queryresultaat en dan de eerste 4 resterende rijen tonen.
mysql> SELECT *
-> FROM users
-> LIMIT 4 OFFSET 5;
Standaard worden alle selectierijen getoond, ook selectierijen die er identiek uitzien (hoewel de eigenlijke rij altijd uniek is). Omdat ALL de standaard is, wordt dit eigenlijk nooit geschreven.
Met DISTINCT (of DISTINCTROW) worden enkel de onderscheiden rijen getoond. De duplicaten worden uit de selectie gefilterd.
-- Selecteer alle rijen
SELECT [ALL] {kolom(mem)}
FROM {tabel};
-- Filter duplicaten uit de selectie
SELECT DISTINCT {kolom(mem)}
FROM {tabel};
-- Synoniem voor DISTINCT
SELECT DISTINCTROW {kolom(mem)}
FROM {tabel};Er kan uit meerdere tabellen tegelijk geselecteerd worden.
Hierbij wordt elke rij van de ene tabel met elke rij van de andere tabellen gecombineerd.
OPGELET: Deze manier van tabellen combineren wordt AFGERADEN!
-- Selectie uit meerdere tabellen
SELECT {kolom(mem)}
FROM tabel_A [, tabel_B…];Tabellen kunnen in een selectie gecombineerd worden met een NATURAL JOIN. Kolommen met dezelfde kolomnaam in beide tabellen worden samengevoegd. Toont enkel de rijen waarvan de waarde in de gemeenschappelijke kolom overeenkomt.
-- Selectie uit meerderetabellen
SELECT {kolom(mem)}
FROM
{tabel_A} {tabelalias_A}
NATURAL JOIN {tabel_B} {tabelalias_B};Met de optionele sleutelwoorden LEFT en RIGHT wordt de selectie uitgebreid, zodat respectievelijk alle rijen van de linker of alle rijen van de rechter tabel getoond worden.
-- Alle rijen uit {tabel_A} plus
-- overeenkomstige rijen uit {tabel_B}
SELECT *
FROM {tabel_A} {tabelalias_A}
NATURAL LEFT JOIN {tabel_B} {tabelalias_B};
-- Alle rijen uit {tabel_B} plus
-- overeenkomstige rijen uit {tabel_A}
SELECT *
FROM {tabel_A} {tabelalias_A}
NATURAL RIGHT JOIN {tabel_B} {tabelalias_B};De INNER JOIN … USING|ON is een alternatief voor een gewone NATURAL JOIN.
LEFT JOINis een alternatief voor eenNATURAL LEFT JOIN.RIGHT JOINis een alternatief voor eenNATURAL RIGHT JOIN.
INNER JOIN … USING
Met USING (…) geef je de kolomnaam of -namen op waarop de tabellen samengevoegd moeten worden.
-- INNER JOIN … USING (…)
SELECT {kolom(mem)}
FROM {tabel_A} {tabelalias_A}
INNER JOIN {tabel_B} {tabelalias_B}
USING ({kolom_1}[, {kolom_2}…]);INNER JOIN … ON
Met ON … gebruik je een expressie, bijvoorbeeld:
linker_tabel.id = rechter_tabel.id
-- INNER JOIN … ON vergelijking
SELECT {kolom(mem)}
FROM {tabel_A} {tabelalias_A}
INNER JOIN {tabel_B} {tabelalias_B}}
ON {tabelalias_A}.{kolom_A1} = {tabelalias_B}.{kolom_B1};LEFT|RIGHT JOIN … USING
-- LEFT JOIN … USING (…)
SELECT {kolom(mem)}
FROM {tabel_A} {tabelalias_A}
LEFT|RIGHT JOIN {tabel_B} {tabelalias_B}
USING ({kolom_1}[, {kolom_2}…]);LEFT|RIGHT JOIN … ON
-- LEFT JOIN … ON vergelijking
SELECT {kolom(mem)}
FROM {tabel_A} {tabelalias_A}
LEFT|RIGHT JOIN {tabel_B} {tabelalias_B}
ON {tabelalias_A}.{kolom} = {tabelalias_B}.{kolom};- Werkt met expressies.
- De evaluatievolgorde hangt af van de gebruikte logische operator
- Gebruik
(en)om de evaluatievolgorde te bepalen.
-- selectie van rijen met een
-- WHERE-clausule
SELECT {kolom(mem)}
FROM {tabel}
WHERE
{kolom_1} {OPERATOR} {waarde_1}
[AND|OR|XOR
{kolom_2} {OPERATOR} {waarde_2}…];LIKE werkt met patronen.
-- Selectie van rijen met een
-- WHERE-clausule
SELECT {kolom(mem)}
FROM {tabel}
WHERE
{kolom} [NOT] LIKE {patroon};
-- Voorbeeld
-- Selecteer zowel Tim, Timoty,
-- Tom als Tomas
SELECT first_name
FROM users
WHERE first_name LIKE 'T[io]m%';BETWEEN {min} AND {max}NOT BETWEEN {min} AND {max}
OPGELET: Zowel de minimum- als maximumwaarde zijn inbegrepen in de selectie!
SELECT {kolom(men)}
FROM {tabel}
WHERE
{kolom} [NOT] BETWEEN {min} AND {max};
-- Bovenstaande query is de iets kortere versie van dit:
SELECT {kolom(men)}
FROM {tabel}
WHERE
[NOT] ({min} <= {kolom} AND {kolom} <= {max});IN: waarde moet in de reeks staanNOT IN: waarde mag niet in de reeks staan
SELECT {kolom(men)}
FROM {tabel}
WHERE
{kolom} [NOT] IN ({waarde_1}[, {waarde_2}…]);Controleren op nullwaarde:
IS NULLIS NOT NULL
SELECT {kolom(men)}
FROM {tabel}
WHERE
{kolom} IS [NOT] NULL;Rijen kan je groeperen met een GROUP BY-voorwaarde.
-- Selectie van rijen, gegroepeerd volgens de waarde van een kolom
SELECT {kolom(men)}
FROM {tabel}
WHERE {expressie}
GROUP BY
{kolom_1}[,
{kolom_2}…];Het is te vergelijken met een WHERE-voorwaarde, maar laat wel het gebruik van statistische functies toe in de expressie.
-- HAVING in plaats van WHERE
SELECT {STATISTISCHE_FUNCTIE}(*)
FROM {tabel}
GROUP BY
{kolom_1}[,
{kolom_2}…]
HAVING {expressie};Sorteren doe je met een ORDER BY-voorwaarde. Deze moet na een WHERE- of �GROUP BY-voorwaarde, maar voor een LIMIT-voorwaarde.
Sorteren gebeurt:
ASC(ascending): oplopend (0-9 en A-Z).DESC(descending): aflopend (9-0 en Z-A ).
Standaard wordt oplopend gesorteerd, daarom mag je ASC weglaten.
-- Sorteren
SELECT {kolom(men)}
FROM {tabel}
ORDER BY kolomnaamX [ASC|DESC];
-- Met meerdere kolommen
SELECT {kolom(men)}
FROM {tabel}
ORDER BY {kolom_1} [DESC][, {kolom_2} [DESC]…];Voorbeeld:
mysql> SELECT *
-> FROM users
-> WHERE first_name LIKE 'A%'
-> ORDER BY first_name DESC, last_name
-> LIMIT 10;
Met UNION voeg je de resultaten van 2 of meer SELECT-query’s samen.
Duplicaten worden uit de resultaten gefilterd. Gebruik UNION DISTINCT indien dit niet vanzelf gebeurt.
De kolommen uit de andere query(’s) moeten wel gelijkaardig zijn aan die uit de eerste query.
-- 2 query’s samenvoegen
({query_1}) UNION [DISTINCT|ALL] ({query_2})
ORDER BY …
LIMIT …
-- Voorbeeld
(SELECT user_familyname `name` FROM users)
UNION
(SELECT admin_familyname `name` FROM admins)
ORDER BY name DESC;Zie ook:
UPDATE- Expressies in
SETenWHERE - Dubbele gelijkheidstekens worden nooit gebruikt.
- Zowel vergelijking als toekenning gebeurt met een enkel gelijkheidsteken.
- Expressies in
-- Rij(en) updaten in een tabel
UPDATE {tabel}
SET
{kolom_1} = {waarde_1}[,
{kolom_2} = {waarde_2}…]
WHERE
{kolom_8} {OPERATOR} {waarde_8}
[AND|OR
{kolom_9} {OPERATOR} {waarde_9}…];Bijvoorbeeld:
mysql> UPDATE users
-> SET first_name = 'Jane'
-> WHERE user_id = 1;
Zie ook:
DELETE FROM {tabel}
WHERE {expressie(s)};Bijvoorbeeld:
mysql> DELETE FROM users
-> WHERE user_id = 1;
Een subquery is een query in een query.
-- Eenvoudige subquery
SELECT {kolom(mem)}
FROM {tabel}
WHERE
{kolom} {OPERATOR} {subquery};
-- Voorbeeld
SELECT *
FROM emails
WHERE
id = (SELECT user_id FROM users WHERE first_name = 'Olivier' LIMIT 1);Soms kan het nodig zijn om variabelen te definiëren op databaseniveau. Je kan een variabele definiëren met SET. De variabelenaam begint met een @.
-- Variabele definiëren
SET @{variabele} = {waarde_of_expressie};
-- Voorbeeld
SET @id = (SELECT user_id FROM users WHERE first_name = 'Olivier' LIMIT 1);Een view is een query die als virtuele tabel opgeslagen wordt. Views worden automatisch geüpdatet op de achtergrond. Views zijn vooral geschikt voor complexe query's die vaak uitgevoerd worden of heel lang duren om uit te voeren.
Begin de naam van de view met vw_ zodat je ze makkelijk kan onderscheiden van gewone tabellen.
-- Eenvoudige view maken
CREATE VIEW vw_{view}
AS {query};
-- Voorbeeld
CREATE VIEW vw_user_addresses
AS SELECT *
FROM users NATURAL JOIN addresses
ORDER BY last_name, first_name;Een Transactie is een geheel van SQL-statements (voor CRUD) dat in zijn geheel wordt uitgevoerd of helemaal niet wordt uitgevoerd.
De ACID-eigenschappen van een Transactie:
Atomiciteit (Atomicity) In zijn geheel uitgevoerd of totaal niet. Verantwoordelijk: DBMS.
Consistentie (Consistency) Van begin tot eind uitgevoerd zonder inmenging van andere transacties. Verantwoordelijk: developer.
Geïsoleerdheid (Isolation) Uitgevoerd alsof er geen andere transacties bestaan, ook al gebeuren die gelijktijdig. Verantwoordelijk: DBMS.
Duurzaamheid (Durablility) Wijzigingen mogen niet verloren gaan door een defect of fout. Verantwoordelijk: DBMS.
Geld overschrijven is een typisch voorbeeld van een transactie:
- Geld moet van de ene rekening naar de andere gaan.
- Op geen enkel moment mag er geld uit het systeem verdwijnen.
- Als iets misloopt, dan moet het systeem naar de oude toestand terugkeren.
Een transactie begint met START TRANSACTION en wordt ofwel ongedaan gemaakt met ROLLBACK of definitief uitgevoerd met COMMIT.
-- 1. Een transactie beginnen
START TRANSACTION;
-- 2. SQL-statements
…
-- 3.a Om de wijzigingen ongedaan te maken
ROLLBACK;
-- 3.b Om de wijzigigen definitief te maken
COMMIT;OPGELET: Of een tabel Transacties ondersteunt hangt af van de gebruikte Storage Engine. Zie het betreffende hoofdstuk hieronder.
De database slaat gegevens op volgens een bepaald datatype dat vooraf bepaald moet worden. Dit datatype heeft gevolgen voor de snelheid waarmee de gegevens opgezocht kunnen worden en de grootte van de nodige opslagruimte.
Probeer een datatype te kiezen dat zo goed mogelijk past bij het soort gegevens:
- Kleinst mogelijke opslagruimte.
- Voldoet aan de vereisten: snelheid, precisie, doorzoekbaarheid …
- Houd er ook rekening mee dat vereisten in de toekomst kunnen wijzigen.
- Een database wijzigen die reeds miljoenen rijen bevat is tijdrovend en bovendien is wijzigingen uitvoeren misschien te riskant.
Gehele getallen (Integers) stellen een exacte waarde voor.
De integerdatatypes van klein naar groot:
TINYINT;SMALLINT;MEDIUMINT;INT(ofINTEGER);BIGINT.
Tussen ronde haakjes kan ({n}) het minimum aantal getoonde tekens opgegeven worden. Dit heeft geen invloed op de maximumwaarde van het bereik, maar heeft enkel invloed in combinatie met ZEROFILL, waarbij de niet gebruikte plaatsen tot het n-de teken met een 0 opgevuld worden.
-- Integers
{DATATYPE}
{DATATYPE}({n})
{DATATYPE}({n}) ZEROFILL
{DATATYPE}({n}) UNSIGNED
{DATATYPE}({n}) UNSIGNED ZEROFILLVoor MySQL is een booleaanse waarde de kleinste integer (TINYINT) die slechts 1 teken toont.
- ONWAAR:
0 - WAAR: alle waarden die niet
0zijn, bijv.1,-3of9
-- Synoniemen booleaanse waarde
-- opslag: 1 byte (8 bits!)
BOOL
BOOLEAN
TINYINT(1)| Datatype | Signed | Opslagruimte | Minimumwaarde | Maximumwaarde |
|---|---|---|---|---|
TINYINT |
1 byte | -128 |
127 |
|
TINYINT |
UNSIGNED |
1 byte | 0 |
255 |
SMALLINT |
2 bytes | -32768 |
32767 |
|
SMALLINT |
UNSIGNED |
2 bytes | 0 |
65535 |
MEDIUMINT |
3 bytes | -8388608 |
8388607 |
|
MEDIUMINT |
UNSIGNED |
3 bytes | 0 |
16777215 |
INT |
4 bytes | -2147483648 |
2147483647 |
|
INT |
UNSIGNED |
4 bytes | 0 |
4294967295 |
BIGINT |
8 bytes | -9223372036854775808 |
9223372036854775807 |
|
BIGINT |
UNSIGNED |
8 bytes | 0 |
18446744073709551615 |
Zwevendekommagetallen (floating-point values) hebben een waarde bij benadering.
FLOAT({p})
{p}: Precisie
({m},{d})
{m}: Mantisse (totaal aantal cijfers){d}: Decimaal (maximaal aantal cijfers na de komma)
-- enkele-precisiewaarde
-- {p}: 0 t.e.m. 23
-- 4 bytes opslagruimte
FLOAT
FLOAT({p})
FLOAT({m},{d})
-- 0.00 tot 9999.99
FLOAT(6,2)
-- dubbele-precisiewaarde
-- {p}: 24 t.e.m. 53
-- 8 bytes opslagruimte
DOUBLE
DOUBLE PRECISION
FLOAT({p})Vastekommagetallen (Decimal values) Hebben een exacte waarde.
Tip: Gebruik vastekommagetallen voor valuta, want bij geldtransacties mogen geen afrondingsfouten ontstaan.
DECIMAL({m},{d})
{m}: Mantisse (totaal aantal cijfers){d}: Decimaal (exact aantal cijfers na de komma)
-- {m}: 1 tot 65
-- {d}: 0 tot m
-- standaardwaarde {m} is 10
DECIMAL
DECIMAL({m}) -- = DECIMAL({m},0)
DECIMAL({m},{d})Zie ook:
Wordt gebruikt voor tekst.
-- Vaste lengte
-- {n}: van 0 tot 255
-- opslagruimte: {n} bytes
CHAR({n})
-- Variabele lengte
-- {n}: van 0 tot 65535
-- opslagruimte:
-- (lengte + 1) bytes als {n} <= 255
-- (lengte + 2) bytes als 255 < {n}
-- lengte <= {n}
VARCHAR({n})
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT| Datatype | Alternatief | Opslagruimte | Maximumlengte |
|---|---|---|---|
CHAR(1) |
1 byte | 1 |
|
CHAR(255) |
(lengte) bytes | 255 |
|
VARCHAR(255) |
TINYTEXT |
(lengte + 1) bytes | 255 |
VARCHAR(65535) |
TEXT |
(lengte + 2) bytes | 65535 |
MEDIUMTEXT |
(lengte + 3) bytes | 16777215 |
|
LONGTEXT |
(lengte + 4) bytes | 4294967295 |
Wordt gebruikt om binaire (niet-tekstuele) gegevens op te slaan.
- Geen tekenset.
- Alfabetische sorteringen en vergelijkingen gebeuren op basis van de numerieke waarden van de bytes.
- Voor binaire (niet-tekstuele) gegevens. Bijvoorbeeld foto- of andere bestanden.
BLOB: Binary Large Object
-- Vaste lengte
-- {n}: 0 tot 255
-- opslagruimte: {n} bytes
BINARY({n})
-- Variabele lengte
-- {n}: van 0 tot 65535
-- opslagruimte:
-- (lengte + 1) bytes als {n} <= 255
-- (lengte + 2) bytes als 255 < {n}
-- lengte <= {n}
VARBINARY({n})
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB| Datatype | Alternatief | Opslagruimte | Maximumlengte |
|---|---|---|---|
BINARY(1) |
1 byte | 1 |
|
BINARY(255) |
(lengte) bytes | 255 |
|
VARBINARY(255) |
TINYBLOB |
(lengte + 1) bytes | 255 |
VARBINARY(65535) |
BLOB |
(lengte + 2) bytes | 65535 |
MEDIUMBLOB |
(lengte + 3) bytes | 16777215 |
|
LONGBLOB |
(lengte + 4) bytes | 4294967295 |
SQL-92 was de derde revisie en meteen ook een grote. Deze standaard uit 1992 wordt soms SQL2 genoemd. Nog veel RDBMS’en gebruiken op deze standaard geïnspireerde SQL.
| Operator | Betekenis | SQL-92 |
|---|---|---|
… = … |
gelijk aan/toekenning | Ja |
… < … |
kleiner dan | Ja |
… > … |
groter dan | Ja |
… <= … |
kleiner of gelijk aan | Ja |
… >= … |
groter of gelijk aan | Ja |
… <> … |
niet gelijk aan | Ja |
… != … |
niet gelijk aan | Nee |
| Booleaanse waarde | Betekenis |
|---|---|
UNKOWN |
nullwaarde |
FALSE |
0 |
TRUE |
1 |
-- Boolaanse waarden
… IS [NOT] {bool}
-- Is nullwaarde?
… IS [NOT] NULL
-- In een bereik van getallen
… [NOT] BETWEEN min AND max
-- In een lijst van waarden
… [NOT] IN(waarde1[, waarde2…]) Zie ook:
Eenvoudige patroonherkenning gebeurt met LIKE of NOT LIKE.
-- Eenvoudige patroonherkenning
… [NOT] LIKE '{patroon}'
-- Patronen
% -- 0 of meer tekens
_ -- Exact 1 teken
-- Selecteer zowel Tim, Timoty, Tom als Tomas.
SELECT
'Tanya' LIKE 'T_m%',
'Tim' LIKE 'T_m%',
'Timoty' LIKE 'T_m%',
'Tom' LIKE 'T_m%',
'Tomas' LIKE 'T_m%';Bijvoorbeeld:
mysql> SELECT *
-> FROM users
-> WHERE first_name LIKE 'T_m%';
Zie ook:
Patroonherkenning met reguliere expressies gebeurt met REGEXP of NOT REGEXP.
-- Patroonherkenning met reguliere expressies
… [NOT] REGEXP '{reguliere_expressie}'
-- Reguliere expressies
[{tekenset}] -- Exact 1 teken uit de tekenset
[^{tekenset}] -- Exact 1 teken niet uit de tekenset
-- Selecteer zowel Tim en Tom.
SELECT
'Tam' REGEXP 'T[^io]m',
'Tam' REGEXP 'T[io]m',
'Tim' REGEXP 'T[io]m',
'Tom' REGEXP 'T[io]m';Bijvoorbeeld:
mysql> SELECT *
-> FROM users
-> WHERE first_name REGEXP 'T[io]m';
Zie ook:
Logische operatoren hebben als resultaat WAAR (1 of TRUE), ONWAAR (0 of FALSE) of nullwaarde (NULL) en worden van links naar rechts geëvalueerd.
{expressie_1} AND {expressie_2}{expressie_1} OR {expressie_2}{expressie_1} XOR {expressie_2}
In de Booleaanse algebra geldt:
$ p $ $ q $ $ (p \land q) $ $ 0 $ $ 0 $ $ 0 $ $ 0 $ $ 1 $ $ 0 $ $ 1 $ $ 0 $ $ 0 $ $ 1 $ $ 1 $ $ 1 $ Beide termen moeten WAAR zijn.
-- Beide expressies moeten WAAR zijn
{expressie_1} AND {expressie_2}Bijvoorbeeld:
mysql> SELECT
-> 0 AND 0,
-> 0 AND 1,
-> 1 AND 0,
-> 1 AND 1;
| Expressie | Resultaat |
|---|---|
0 AND 0 |
0 |
0 AND 1 |
0 |
1 AND 0 |
0 |
1 AND 1 |
1 |
In de Booleaanse algebra geldt:
$ p $ $ q $ $ (p \lor q) $ $ 0 $ $ 0 $ $ 0 $ $ 0 $ $ 1 $ $ 1 $ $ 1 $ $ 0 $ $ 1 $ $ 1 $ $ 1 $ $ 1 $ Minstens één van de termen moet WAAR zijn.
-- Minstens één van de expressies moet waar zijn
{expressie_1} OR {expressie_2}Bijvoorbeeld:
mysql> SELECT
-> 0 OR 0,
-> 0 OR 1,
-> 1 OR 0,
-> 1 OR 1;
| Expressie | Resultaat |
|---|---|
0 OR 0 |
0 |
0 OR 1 |
1 |
1 OR 0 |
1 |
1 OR 1 |
1 |
In de Booleaanse algebra geldt:
$ p $ $ q $ $ (p \oplus q) $ $ 0 $ $ 0 $ $ 0 $ $ 0 $ $ 1 $ $ 1 $ $ 1 $ $ 0 $ $ 1 $ $ 1 $ $ 1 $ $ 0 $ Beide termen moeten verschillend zijn.
-- Beide expressies moeten een verschillende logische uitkomst hebben
{expressie_1} XOR {expressie_2}Bijvoorbeeld:
mysql> SELECT
-> 0 XOR 0,
-> 0 XOR 1,
-> 1 XOR 0,
-> 1 XOR 1;
| Expressie | Resultaat |
|---|---|
0 XOR 0 |
0 |
0 XOR 1 |
1 |
1 XOR 0 |
1 |
1 XOR 1 |
0 |
Aggregate functions
Te gebruiken met een GROUP BY met zowel: SELECT-clausule HAVING-clausule
AVG() -- Gemiddelde
COUNT() -- Aantal
COUNT(DISTINCT) -- Aantal unieke
MAX() -- Grootste
MIN() -- Kleinste
SUM() -- Som
…Deze functies mag je overal toepassen.
-- Afronden
ROUND({getal}) -- afronden
CEIL({getal}) -- naar boven afronden
FLOOR({getal}) -- naar beneden afronden
-- Aantal decimalen ({d})
TRUNCATE({getal},{d})
…Deze functies mag je overal toepassen.
CURDATE() -- Datum nu
CURTIME() -- Tijd nu
NOW() -- Datum+tijd nu
YEAR({datum}) -- Jaartal
-- Datum formatteren
DATE_FORMAT({datum}, {formaat})
…
-- Huidige dag, datum en tijd
SELECT DATE_FORMAT(NOW(), '%a %d/%m/%Y %H:%i:%S');Deze functies mag je overal toepassen.
CONCAT({tekenstring_1}[, {tekenstring_2}…]) -- Concateneren (samenvoegen)
LCASE({tekenstring}) -- Onderkast
LENGTH({tekenstring}) -- Stringlengte
UCASE({tekenstring}) -- Kapitalen
TRIM({tekenstring}) -- Spaties trimmen
SUBSTR({tekenstring},{positie}) -- Substring
…MySQL Server gebruikt Storage Engines om tabellen op te slaan. Een storage engine bepaalt het Tabeltype.
- Per tabel kan een andere engine gebruikt worden.
- Elke Storage Engine of Tabeltype heeft voor- en nadelen.
- De beschikbare engines zijn afhankelijk van de MySQL-installatie!
Tabeltypes:
- NTST (Non-Transaction-Safe Tables)
- Sneller;
- Kleiner opslaggeheugen nodig;
- Kleiner werkgeheugen nodig.
- TST (Transaction-Safe Tables)
- Ondersteunt Transacties;
- Veiliger: crashbestendig.
| Engine | Tabeltype |
|---|---|
InnoDB |
TST |
Memory |
NTST |
MyISAM |
NTST |
Naast de bovenstaande engines, zijn er nog vele andere:
ArchiveBerkeleyDBCSVIBMDB2IMerge- …
-- Tabel maken met bepaalde storage engine
CREATE TABLE users (id)
ENGINE = MyISAM;
-- Storage engine wijzigen
ALTER TABLE users
ENGINE = InnoDB;
-- Tabel in het werkgeheugen tot de server herstart.
ALTER TABLE users
ENGINE = Memory;Met EXPLAIN kun je nagaan hoe MySQL de query's uitvoert:
- Welke kolommen komen in aanmerking voor een
INDEX. - Hoe verloopt een
JOIN.
Met EXPLAIN EXTENDED krijg je meer uitgebreide informatie.
-- Informatie over de query
EXPLAIN [EXTENDED] {query};
-- Voorbeeld
EXPLAIN
SELECT * FROM users
ORDER BY last_name;