Administering a SQL Database (M55316)
Beheer je SQL Server en Azure SQL databases? Dan is deze training iets voor jou. Gedurende deze training krijg je de kennis en vaardigheden die…
Bekijk training
Wat is SQL?
De aanwezigheid van dynamische websites op het World Wide Web is tegenwoordig grotendeels te danken aan de mogelijkheid om hun inhoud via databases te beheren. Het beheer van databases is een ingewikkeld proces, dat aanzienlijk is gestructureerd door de programmeertaal SQL. Zoals de volledige naam (Structured Query Language) al aangeeft, is SQL verantwoordelijk voor het bevragen en bewerken van informatie die in een bepaald databasemanagementsysteem is opgeslagen.
SQL Geschiedenis
De oorsprong van SQL gaat terug tot de jaren 70, toen IBM nieuwe database software werd ontwikkeld met de naam System R. En om de in System R opgeslagen gegevens te beheren, werd de vraagtaal SQL taal ontwikkeld. Aanvankelijk heette die SEQUEL, een naam die nog steeds wordt gebruikt als alternatieve uitspraak voor SQL maar die later werd hernoemd tot gewoon SQL.
In 1979 zag een bedrijf genaamd Relational Software, dat later Oracle werd, het commerciële potentieel van SQL en bracht het zijn eigen aangepaste versie uit, genaamd Oracle V2.
Nu in het derde decennium van zijn bestaan, biedt SQL grote flexibiliteit aan gebruikers door gedistribueerde databases te ondersteunen, d.w.z. databases die kunnen worden uitgevoerd op meerdere computernetwerken tegelijk. Gecertificeerd door ANSI en ISO, is SQL de standaard querytaal geworden, die aan de basis ligt van een verscheidenheid van gevestigde database toepassingen op het Internet. Het voorziet in zowel industriële als academische behoeften en wordt gebruikt op zowel individuele computers als bedrijfsservers. Met de vooruitgang in databasetechnologie zijn SQL-gebaseerde toepassingen steeds betaalbaarder geworden voor de gewone gebruiker. Dit is te danken aan de introductie van verschillende open-source SQL database oplossingen zoals MySQL, PostgreSQL, SQLite, Firebird, en nog veel meer.
SQL Standaard
De SQL Standaard heeft in de loop der jaren een groot aantal veranderingen ondergaan, die een groot aantal nieuwe functionaliteiten aan de standaard hebben toegevoegd, zoals ondersteuning voor XML, triggers, reguliere expressie matching, recursieve queries en nog veel meer. Door de enorme omvang van de SQL Standaard implementeren veel daarop gebaseerde database oplossingen, zoals MySQL of PostgreSQL, niet de gehele standaard. In veel gevallen is het gedrag van de database voor bestandsopslag of indexereingen niet goed gedefinieerd en is het aan de verkopers van de verschillende SQL implementaties om te beslissen hoe de database zich zal gedragen. Dit is de reden waarom, ook al hebben alle SQL-implementaties dezelfde basis, ze zelden compatibel zijn.
SQL dialecten
De community vraagt voortdurend om nieuwe functies en mogelijkheden die nog niet bestaan in de SQL-standaard, daarom zijn er, zelfs met de SQL-standaard veel verschillende SQL-dialecten in verschillende database-producten.
Omdat ANSI en ISO deze belangrijke functies nog niet hebben ontwikkeld, zijn RDBMS-leveranciers (of gemeenschappen) vrij om hun eigen nieuwe syntaxstructuur uit te vinden.
Hieronder volgen de meest populaire dialecten van SQL:
- PL/SQL staat voor procedurele taal/SQL. Het is ontwikkeld door Oracle voor de Oracle Database.
- Transact-SQL of T-SQL is ontwikkeld door Microsoft voor Microsoft SQL Server.
- PL/pgSQL staat voor Procedural Language/PostgreSQL die bestaat uit SQL-dialect en extensies geïmplementeerd in PostgreSQL
- MySQL heeft haar eigen procedurele taal sinds versie 5. Merk op dat MySQL werd overgenomen door Oracle.
Elementen van de SQL-taal
De SQL-taal is gebaseerd op verschillende elementen. Voor het gemak van SQL-ontwikkelaars worden alle noodzakelijke taalcommando’s in de overeenkomstige databasemanagementsystemen meestal uitgevoerd via een specifieke SQL command-line interface (CLI).
Clauses – de clauses zijn onderdelen van de statements en de query’s
Expressies – de expressies kunnen scalaire waarden of tabellen opleveren, die bestaan uit kolommen en rijen met gegevens
Predicates – zij specificeren voorwaarden, die worden gebruikt om de effecten van de statements en de query’s te beperken, of om het proces te wijzigen
Query’s – met een query worden gegevens opgehaald, gebaseerd op een bepaald criterium
Statements – met de statements kan men transacties, het programmaverloop, verbindingen, sessies of diagnostiek regelen. In databasesystemen worden de SQL-statements gebruikt om query’s van een client programma naar een server te sturen waar de databases zijn opgeslagen. Als antwoord verwerkt de server de SQL-statements en stuurt antwoorden terug naar de client. Dit stelt gebruikers in staat een breed scala van verbluffend snelle gegevensmanipulaties uit te voeren, van eenvoudige gegevensinvoer tot gecompliceerde query’s.
SQL-query’s
De SQL-query’s is de meest voorkomende en essentiële SQL-operaties. Via een SQL-query kan men de database doorzoeken naar de benodigde informatie. SQL queries worden uitgevoerd met het “SELECT” statement. Een SQL query kan specifieker zijn, met behulp van een aantal clausules:
FROM – hiermee wordt de tabel aangegeven waar gezocht zal worden.
WHERE – het wordt gebruikt om de rijen te definiëren, waarin gezocht zal worden. Alle rijen waarvoor de WHERE clausule niet waar is, zullen worden uitgesloten.
ORDER BY – dit is de enige manier om de resultaten in SQL te sorteren. Anders worden ze in een willekeurige volgorde geretourneerd.
Een SQL query voorbeeld
SELECT *
FROM persoon
WHERE achternaam = “Pietersen”
ORDER BY achternaam, voornaam
Hoe SQL werkt bij het samenvoegen van tabellen
Een SQL join clausule is als een join operatie in relationele algebra. Het combineert de kolommen van een of meer tabellen in een relationele database om een set te maken die kan worden opgeslagen als een tabel of kan worden gebruikt zoals het is. Een JOIN is een middel om kolommen uit een of meer tabellen te combineren door waarden te gebruiken die elke tabel gemeenschappelijk heeft.
JOINS zijn een kritische vaardigheid en helpen je om veel werk te verzetten met complexe databases. Het vermogen om JOIN queries nauwkeurig te manipuleren zal je een extra voordeel geven.
Er zijn 4 grote JOINS om gegevens of rijen uit twee of meer tabellen te combineren op basis van een gemeenschappelijk veld tussen beide. Verschillende types van Joins zijn:
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
Een JOIN is een SQL instructie in de FROM clausule van je query die wordt gebruikt om de tabellen te identificeren die je bevraagt en hoe ze moeten worden gecombineerd. Maar voor we echt tabellen gaan samenvoegen, leren we eerst meer over primary key & foreign key.
Primaire sleutels
Een primaire sleutel is een kolom (of soms een set van kolommen) in een tabel die een unieke identifier is voor elke rij. Het is heel gebruikelijk voor databases om een kolom met de naam id (KLantID, Medewerker) te hebben als een primaire sleutel voor elke tabel.
Primaire Verwijzende sleutels
Foreign keys zijn kolommen in een tabel die een link geven naar een primaire sleutel in een andere tabel.
SQL gegevenscontrole, -definitie en -manipulatie
SQL is een taal die is ontworpen om gegevens op te slaan, maar de gegevens die in een SQL-database zijn opgeslagen, zijn niet statisch. Ze kunnen op elk moment worden gewijzigd met behulp van een aantal zeer eenvoudige commando’s. De SQL-syntaxis spreekt voor zich, waardoor deze veel gemakkelijker te lezen en te begrijpen is.
SQL gegevens manipulatie
Datamanipulatie is essentieel voor SQL tabellen. Het stelt u in staat een reeds aangemaakte tabel te wijzigen met nieuwe informatie, de reeds bestaande waarden bij te werken of ze te verwijderen.
Met het INSERT statement kunt u nieuwe rijen toevoegen aan een reeds bestaande tabel. Nieuwe rijen kunnen informatie bevatten vanaf het begin, of kunnen met een NULL waarde zijn.
Een voorbeeld van een SQL INSERT
INSERT INTO persoon (voornaam, achternaam, adres, postcode, plaats)
VALUES(‘Rob’, Pietersen, Spoorstraat 12, ‘1122 AA’, ‘Amsterdam’);
Met het UPDATE statement kan je gemakkelijk de reeds bestaande informatie in een SQL tabel wijzigen.
Een voorbeeld van een SQL UPDATE
UPDATE Persoon SET adres = ‘Spoorstaat 14’, Postcode = ‘1111 AA’ WHERE voornaam = ‘Rob’ AND achternaam = ‘Pietersen’;
Met het DELETE statement kun je onnodige rijen uit een tabel verwijderen.
Een voorbeeld van een SQL DELETE
DELETE FROM Persoon WHERE voornaam = ‘Rob’ AND achternaam = ‘Pietersen’;
SQL Data definitie
Met een data definitie kan de gebruiker nieuwe tabellen en elementen definiëren.
CREATE: met het CREATE statement kan je een nieuwe tabel aanmaken in een bestaande database.
Een voorbeeld van een SQL CREATE
CREATE TABLE Persoon
(voornaam VARCHAR(30), achternaam VARCHAR(30), adres VARCHAR(50), postcode VARCHAR(6), Plaats VARCHAR(30));
DROP – met het DROP statement in SQL kan je tabellen verwijderen, die je niet langer nodig hebt.
Een voorbeeld van een SQL DROP
DROP TABLE Persoon;
TRUNCATE – met het TRUNCATE statement kan je alle inhoud van de tabel verwijderen, maar de tabel zelf intact houden en klaar voor verder gebruik
Een voorbeeld van een SQL TRUNCATE
TRUNCATE TABLE Persoon;
Het ALTER statement staat de gebruiker toe een bestaand object op verschillende manieren te wijzigen — bijvoorbeeld door een kolom aan een bestaande tabel toe te voegen.
Een voorbeeld van een ALTER
ALTER TABLE Persoon RENAME TO Personen
SQL gegevenscontrole
Met SQL kan de gebruiker bepalen welke toegang elke gebruiker tot de eigenlijke tabel mag hebben.
GRANT – met het GRANT statement, kunt u gebruikers machtigen om de geselecteerde tabel te wijzigen
Een voorbeeld van een SQL GRANT
GRANT ALL PRIVILEGES ON database_naam AAN database_gebruiker;
REVOKE – met het REVOKE statement kun je alle rechten verwijderen die eerder aan een gebruiker zijn toegekend.
Een voorbeeld van een SQL REVOKE
REVOKE ALLE PRIVILEGES ON database_naam AAN database_gebruiker ;
Wat zijn Database Constraints?
In SQL is een constraint een regel die wordt toegepast op een kolom of tabel die beperkt welke gegevens erin kunnen worden ingevoerd. Elke keer dat je probeert een bewerking uit te voeren die de gegevens wijzigt, zoals een INSERT, UPDATE, of DELETE statement, zal het RDBMS testen of die gegevens in strijd zijn met bestaande constraints en, zo ja, een foutmelding teruggeven.
Databasebeheerders vertrouwen vaak op constraints om ervoor te zorgen dat een database een reeks gedefinieerde bedrijfsregels opvolgt. In de context van een database is een bedrijfsregel procedure die de consistentie van de gegevens bewaakt. Bijvoorbeeld, stel dat je een database hebt die de winkelinventaris van een klant zal beheren. Als de klant specificeert dat elk product een uniek identificatie moet hebben, zou je een kolom kunnen maken met een UNIQUE constraint die ervoor zorgt dat geen twee records in die kolom hetzelfde kunnen zijn.
Constraints zijn ook nuttig bij het handhaven van de gegevensintegriteit. Data integriteit is een brede term die vaak wordt gebruikt om de algemene nauwkeurigheid, consistentie en rationaliteit van gegevens in een database te beschrijven, gebaseerd op het specifieke gebruik ervan. Tabellen in een database zijn vaak nauw met elkaar verbonden, waarbij kolommen in de ene tabel afhankelijk zijn van de waarden in een andere. Omdat het invoeren van gegevens vaak onderhevig is aan menselijke fouten, zijn constraints in dit soort gevallen nuttig, omdat ze ervoor kunnen zorgen dat verkeerd ingevoerde gegevens dergelijke relaties niet kunnen beïnvloeden en zo de gegevensintegriteit van de database kunnen schaden.
Stel je voor dat je een database ontwerpt met twee tabellen: een tabel voor de huidige leerlingen van een school en een andere tabel voor de leden van het voetbalteam van die school. U zou een FOREIGN KEY constraint kunnen toepassen op een kolom in de tabel met het basketbalteam die verwijst naar een kolom in de tabel met de school. Dit zal een relatie leggen tussen de twee tabellen door te vereisen dat elke invoer in de voetbalteam tabel verwijst naar een bestaande invoer in de leerlingen tabel.
Gebruikers definiëren constraints wanneer ze voor het eerst een tabel maken, of ze kunnen ze later toevoegen met een ALTER TABLE statement, zolang het niet conflicteert met gegevens die al in de tabel staan. Wanneer je een constraint aanmaakt, zal het database systeem er automatisch een naam voor genereren, maar in de meeste SQL implementaties kun je een aangepaste naam toevoegen voor elke constraint. Deze namen worden gebruikt om te verwijzen naar constraints in ALTER TABLE statements bij het wijzigen of verwijderen ervan.
De SQL standaard definieert formeel slechts vijf constraints:
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
NOT NULL
Waar wordt SQL voor gebruikt?
Nu we begrijpen wat SQL is en hoe het werkt, laten we eens proberen om te zien wat SQL kan doen. Deze programmeertaal heeft verschillende toepassingen voor data analisten & data science professionals. Het is vooral nuttig omdat het kan:
- Queries kan uitvoeren tegen een database
- Gegevens ophalen uit een database
- Records kan invoegen in een database
- Records in een database bijwerken
- Records uit een database verwijderen
- Nieuwe databases, of nieuwe tabellen in een database maken
- Opgeslagen procedures en overzichten in een database maken
- Rechten instellen op tabellen, procedures en viewsStel je voor hoe moeilijk het leven zou zijn als we geen manier hadden om de database in deze vorm te besturen. Nadat we een idee hebben gekregen van waar SQL voor wordt gebruikt, gaan we nu proberen SQL te leren in een echte database.
