Samenvatting databases 2
Collegesheets
Pascal Cokart
Juni 2018
Versie 1.5
,Inhoud
College 1.................................................................................................................................................4
Surrogate key (surrogaatsleutel)........................................................................................................4
Waarom surrogaatsleutel gebruiken..................................................................................................4
Auto increment...................................................................................................................................4
College 2.................................................................................................................................................5
Algemeen SQL....................................................................................................................................5
SQL onderdelen..................................................................................................................................5
MySQL datatypen...............................................................................................................................5
CHAR...................................................................................................................................................6
INTEGER..............................................................................................................................................6
BOOLEAN en ENUM............................................................................................................................6
Kiezen van datatypen.........................................................................................................................6
Data integriteit...................................................................................................................................6
Primaire sleutel en unieke sleutels.....................................................................................................6
SQL DDL (Data Defniion Language)...................................................................................................7
Access control.....................................................................................................................................7
Rechten niveaus binnen een database...............................................................................................7
Rechten binnen een tabel...................................................................................................................7
College 3.................................................................................................................................................8
Volgorde SELECT statement:..............................................................................................................8
Funcies..............................................................................................................................................8
WHERE clause.....................................................................................................................................8
HAVING...............................................................................................................................................9
Geaggregeerde funcies:....................................................................................................................9
GROUP BY...........................................................................................................................................9
Storage engines..................................................................................................................................9
Joins....................................................................................................................................................9
Soorten joins.....................................................................................................................................10
Syntax joins.......................................................................................................................................10
Syntax ON.....................................................................................................................................10
Syntax USING................................................................................................................................10
Syntax NATURAL JOIN...................................................................................................................10
Syntax WHERE..............................................................................................................................10
Subquerys.........................................................................................................................................10
1
, User variables...................................................................................................................................11
College 4...............................................................................................................................................12
Indices..............................................................................................................................................12
Gebruik indices in MySQL.................................................................................................................12
Views................................................................................................................................................12
Waarom views..................................................................................................................................12
Updatable views...............................................................................................................................12
Beperkingen van views.....................................................................................................................13
Stored procedures en funcies..........................................................................................................13
Waarom stored procedures of funcies............................................................................................13
Procedureel SQL...............................................................................................................................14
Variabelen in funciessprocedures...................................................................................................14
Syntax procedures en funcies..........................................................................................................14
Syntax procedure..........................................................................................................................14
Syntax funcie...............................................................................................................................14
Voorbeeld funcie.........................................................................................................................15
DELIMITER........................................................................................................................................15
RETURN............................................................................................................................................15
Comments........................................................................................................................................15
View vs funcion...............................................................................................................................15
Triggers.............................................................................................................................................15
Waarom triggers...............................................................................................................................16
Beperkingen.....................................................................................................................................16
Syntax trigger....................................................................................................................................16
Syntax trigger:...............................................................................................................................16
College 5...............................................................................................................................................17
Database transacions......................................................................................................................17
Waarom transacies.........................................................................................................................17
ACID (Atomacity Concistency Isolaion Durability)...........................................................................17
Concurrency problems:....................................................................................................................18
Klassieke concurrency problemen....................................................................................................18
Isolaion levels..................................................................................................................................19
Concurrency control strategies.........................................................................................................20
Locking..............................................................................................................................................20
Locks gebruiken............................................................................................................................21
Niveau van vergrendeling (granularity of locking)............................................................................21
2
Collegesheets
Pascal Cokart
Juni 2018
Versie 1.5
,Inhoud
College 1.................................................................................................................................................4
Surrogate key (surrogaatsleutel)........................................................................................................4
Waarom surrogaatsleutel gebruiken..................................................................................................4
Auto increment...................................................................................................................................4
College 2.................................................................................................................................................5
Algemeen SQL....................................................................................................................................5
SQL onderdelen..................................................................................................................................5
MySQL datatypen...............................................................................................................................5
CHAR...................................................................................................................................................6
INTEGER..............................................................................................................................................6
BOOLEAN en ENUM............................................................................................................................6
Kiezen van datatypen.........................................................................................................................6
Data integriteit...................................................................................................................................6
Primaire sleutel en unieke sleutels.....................................................................................................6
SQL DDL (Data Defniion Language)...................................................................................................7
Access control.....................................................................................................................................7
Rechten niveaus binnen een database...............................................................................................7
Rechten binnen een tabel...................................................................................................................7
College 3.................................................................................................................................................8
Volgorde SELECT statement:..............................................................................................................8
Funcies..............................................................................................................................................8
WHERE clause.....................................................................................................................................8
HAVING...............................................................................................................................................9
Geaggregeerde funcies:....................................................................................................................9
GROUP BY...........................................................................................................................................9
Storage engines..................................................................................................................................9
Joins....................................................................................................................................................9
Soorten joins.....................................................................................................................................10
Syntax joins.......................................................................................................................................10
Syntax ON.....................................................................................................................................10
Syntax USING................................................................................................................................10
Syntax NATURAL JOIN...................................................................................................................10
Syntax WHERE..............................................................................................................................10
Subquerys.........................................................................................................................................10
1
, User variables...................................................................................................................................11
College 4...............................................................................................................................................12
Indices..............................................................................................................................................12
Gebruik indices in MySQL.................................................................................................................12
Views................................................................................................................................................12
Waarom views..................................................................................................................................12
Updatable views...............................................................................................................................12
Beperkingen van views.....................................................................................................................13
Stored procedures en funcies..........................................................................................................13
Waarom stored procedures of funcies............................................................................................13
Procedureel SQL...............................................................................................................................14
Variabelen in funciessprocedures...................................................................................................14
Syntax procedures en funcies..........................................................................................................14
Syntax procedure..........................................................................................................................14
Syntax funcie...............................................................................................................................14
Voorbeeld funcie.........................................................................................................................15
DELIMITER........................................................................................................................................15
RETURN............................................................................................................................................15
Comments........................................................................................................................................15
View vs funcion...............................................................................................................................15
Triggers.............................................................................................................................................15
Waarom triggers...............................................................................................................................16
Beperkingen.....................................................................................................................................16
Syntax trigger....................................................................................................................................16
Syntax trigger:...............................................................................................................................16
College 5...............................................................................................................................................17
Database transacions......................................................................................................................17
Waarom transacies.........................................................................................................................17
ACID (Atomacity Concistency Isolaion Durability)...........................................................................17
Concurrency problems:....................................................................................................................18
Klassieke concurrency problemen....................................................................................................18
Isolaion levels..................................................................................................................................19
Concurrency control strategies.........................................................................................................20
Locking..............................................................................................................................................20
Locks gebruiken............................................................................................................................21
Niveau van vergrendeling (granularity of locking)............................................................................21
2
