1.1. Data Manipulation Language Statements (Transact-SQL)

Data Manipulation Language (DML) is een vocabulaire dat wordt gebruikt om gegevens op te halen en ermee te werken in SQL Server. Gebruik deze instructies om gegevens toe te voegen, aan te passen, op te vragen of te verwijderen uit een SQL Server-database.

De volgende lijst bevat de DML-instructies die SQL Server gebruikt.

De volgende tabel bevat de clausules die worden gebruikt in meerdere DML-instructies of clausules.

ClausuleKan in deze uitdrukkingen worden gebruikt
FROM (Transact-SQL)DELETE, SELECT, UPDATE
Hints (Transact-SQL)DELETE, INSERT, SELECT, UPDATE
OPTION Clause (Transact-SQL)DELETE, SELECT, UPDATE
OUTPUT Clause (Transact-SQL)DELETE, INSERT, MERGE, UPDATE
Search Condition (Transact-SQL)DELETE, MERGE, SELECT, UPDATE
Table Value Constructor (Transact-SQL)FROM, INSERT, MERGE
TOP (Transact-SQL)DELETE, INSERT, MERGE, SELECT, UPDATE
WHERE (Transact-SQL)DELETE, SELECT, UPDATE
WITH common_table_expression (Transact-SQL)DELETE, INSERT, MERGE, SELECT, UPDATE

1.1.1. SELECT Clausule

Logische volgorde van uitvoeren van het SQL commando SELECT.

5SELECTDe SQL SELECT-clausule specificeert de velden, constanten en uitdrukkingen die in de queryresultaten moeten worden weergegeven.
1FROMDe FROM-clausule specificeert een of meer tabellen met de gegevens waaruit de query ophaalt.
2WHEREDe WHERE-clausule geeft de voorwaarden voor join en filter aan die bepalen welke rijen de query retourneert. JOIN-bewerkingen in de WHERE-clausule werken hetzelfde als JOIN-bewerkingen in de FROM-clausule.
3GROUP BYDe GROUP BY-clausule specificeert een of meer kolommen die worden gebruikt om rijen te groeperen die door de query worden geretourneerd. Kolommen waarnaar wordt verwezen in de lijst met SQL SELECT-instructies, met uitzondering van aggregate-expressies, moeten worden opgenomen in de GROUP BY-clausule. U kunt niet groeperen op Memo-, General– of Blob-velden.
4HAVINGDe HAVING-clausule specificeert voorwaarden die de groepen bepalen die in de query zijn opgenomen. Als de SQL SELECT-instructie geen aggregatiefuncties bevat, kunt u een SQL SELECT-instructie gebruiken die een HAVING-clausule bevat zonder een GROUP BY-clausule.
6ORDER BYDe ORDER BY-clausule specificeert een of meer items die worden gebruikt om de uiteindelijke set met queryresultaten te sorteren en de volgorde voor het sorteren van de resultaten.
<SELECT statement> ::=  
    [WITH <common_table_expression> [,...n]]
    <query_expression>
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
  [ ,...n ] ]
    [ <FOR Clause>]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
<query_expression> ::=
    { <query_specification> | ( <query_expression> ) }
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }
        <query_specification> | ( <query_expression> ) [...n ] ]
<query_specification> ::=
SELECT [ ALL | DISTINCT ]
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]
    < select_list >
    [ INTO new_table ]
    [ FROM { <table_source> } [ ,...n ] ]
    [ WHERE <search_condition> ]
    [ <GROUP BY> ]
    [ HAVING < search_condition > ]

1.1.1.2. Samenvoeg functies

AVGRetourneert het gemiddelde van de waarden in een groep. Null-waarden worden genegeerd.
AVG ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )
COUNTRetourneert het aantal items in een groep. COUNT retourneert altijd een int-gegevenstypewaarde.
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
    OVER ( [ partition_by_clause ] order_by_clause )
MAXRetourneert de maximale waarde in de uitdrukking. Kan worden gevolgd door de OVER-clausule.
MAX ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )
MINRetourneert de minimumwaarde in de uitdrukking. Kan worden gevolgd door de OVER-clausule.
MIN ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause ) 
SUMRetourneert de som van alle waarden, of alleen de DISTINCT-waarden, in de uitdrukking. SUM kan alleen worden gebruikt met numerieke kolommen. Null-waarden worden genegeerd.
SUM ( [ ALL | DISTINCT ] expression )
   OVER ( [ partition_by_clause ] order_by_clause )

1.1.1.3. Filter functies

DISTINCTDeze functie kan niet worden gebruikt om waarden naar een cel of kolom op een werkblad te retourneren; in plaats daarvan nest je de functie DISTINCT in een formule om een lijst met verschillende waarden te krijgen die kunnen worden doorgegeven aan een andere functie en vervolgens kunnen worden geteld, opgeteld of gebruikt voor andere bewerkingen.

DISTINCT werkt op de hele rij, niet op een specifieke kolom. Als u de unieke namen wilt hebben, selecteert u alleen die kolom

DISTINCT(<column>)

1.1.1.3. String functies

CONCATRetourneert een tekenreeks die het resultaat is van het samenvoegen van twee of meer tekenreekswaarden.
LTRIMRetourneert een tekenuitdrukking nadat het voorloopspaties heeft verwijderd.
RTRIMRetourneert een tekenreeks na het afkappen van alle volgspaties.

1.1.1.4. Logische operatoren

ALLVergelijkt een scalaire waarde met een set waarden met één kolom.
scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery )
ANDCombineert twee Booleaanse uitdrukkingen en retourneert WAAR als beide uitdrukkingen WAAR zijn. Als in een instructie meer dan één logische operator wordt gebruikt, worden de AND-operators eerst geëvalueerd. U kunt de volgorde van evaluatie wijzigen door haakjes te gebruiken.
boolean_expression AND boolean_expression
ANYVergelijkt een scalaire waarde met een set waarden met één kolom. Zie voor meer informatie SOME | ANY (Transact-SQL).
BETWEENSpecificeert een bereik om te testen.
test_expression [ NOT ] BETWEEN begin_expression AND end_expression
EXISTSSpecificeert een subquery om te testen op het bestaan van rijen.
EXISTS subquery
INBepaalt of een opgegeven waarde overeenkomt met een waarde in een subquery of een lijst.
test_expression [ NOT ] IN
    ( subquery | expression [ ,...n ]
    )
LIKEBepaalt of een specifieke tekenreeks overeenkomt met een opgegeven patroon. Een patroon kan gewone tekens en jokertekens bevatten. Tijdens het matchen van patronen moeten reguliere tekens exact overeenkomen met de tekens die in de tekenreeks zijn opgegeven. Jokertekens kunnen echter worden vergeleken met willekeurige fragmenten van de tekenreeks. Het gebruik van jokertekens maakt de operator LIKE flexibeler dan het gebruik van de = en! = Stringvergelijkingsoperatoren. Als een van de argumenten niet van het gegevenstype tekenreeks is, converteert de SQL Server Database Engine deze naar het gegevenstype tekenreeks, indien mogelijk.
match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]
NOTOntkent een Booleaanse invoer.
[ NOT ] boolean_expression
ORCombineert twee voorwaarden. Als er meer dan één logische operator in een instructie wordt gebruikt, worden OF-operators geëvalueerd na EN-operators. U kunt de volgorde van evaluatie echter wijzigen door haakjes te gebruiken.
boolean_expression OR boolean_expression
SOMEVergelijkt een scalaire waarde met een set waarden met één kolom. SOME en ANY zijn gelijkwaardig.
scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < }
     { SOME | ANY } ( subquery )

1.1.1.5. String Operatoren

JokertekenOmschrijvingVoorbeeld
%Elke reeks van nul of meer tekens.WHERE title LIKE ‘%computer%’ vindt alle boektitels met het woord ‘computer’ ergens in de boektitel
_ (underscore)Elk willekeurig teken.
		WHERE au_fname LIKE ‘_ean’ vindt alle voornamen van vier letters die eindigen op ean (Dean, Sean, enzovoort).
[ ]Elk enkel teken binnen het opgegeven bereik ([a-f]) of set ([abcdef]).WHERE au_lname LIKE ‘[C-P]arsen’ vindt de achternaam van de auteur die eindigt op arsen en begint met een willekeurig teken tussen C en P, bijvoorbeeld Carsen, Larsen, Karsen, enzovoort. Bij het zoeken naar bereik kunnen de tekens in het bereik variëren, afhankelijk van de sorteerregels van de sortering.
[^]Elk enkel teken dat niet binnen het opgegeven bereik ([^ a-f]) of set ([^ abcdef]) valt.WHERE au_lname LIKE ‘de[^l]%’ alle achternamen van de auteur die beginnen met de en waarbij de volgende letter niet l is.

1.1.2. JOIN Clausule

Join-voorwaarden kunnen worden gespecificeerd in de FROM- of WHERE-clausule; het wordt aanbevolen om ze op te geven in de FROM-component. WHERE- en HAVING-clausules kunnen ook zoekvoorwaarden bevatten om de rijen die zijn geselecteerd op basis van de join-voorwaarden verder te filteren.

Joins kunnen worden gecategoriseerd als:

  • Inner Joins (de typische join-bewerking, die een vergelijkingsoperator zoals = of <> gebruikt). Deze omvatten equi-joins en natuurlijke joins.
    Inner joins gebruiken een vergelijkingsoperator om rijen uit twee tabellen te matchen op basis van de waarden in gemeenschappelijke kolommen uit elke tabel. Bijvoorbeeld het ophalen van alle rijen waarin het studentidentificatienummer hetzelfde is in zowel de studententabel als de cursustabel.
  • Outer Joins. Outer joins kunnen een linker, rechter of volledige outer join zijn.
    Outer joins worden gespecificeerd met een van de volgende sets sleutelwoorden wanneer ze worden gespecificeerd in de FROM-component:
    • LEFT JOIN of LEFT OUTER JOIN – De resultatenset van een linksbuitenkoppeling omvat alle rijen van de linkertabel die zijn opgegeven in de clausule LEFT OUTER, niet alleen die waarin de samengevoegde kolommen overeenkomen. Als een rij in de linkertabel geen overeenkomende rijen in de rechtertabel heeft, bevat de bijbehorende resultaatsetrij null-waarden voor alle geselecteerde lijstkolommen die uit de rechtertabel komen.
    • RIGHT JOIN of RIGHT OUTER JOIN – Een rechter buitenste verbinding is het omgekeerde van een linker buitenste verbinding. Alle rijen uit de rechtertabel worden geretourneerd. Null-waarden worden geretourneerd voor de linkertabel telkens wanneer een rechtertabelrij geen overeenkomende rij in de linkertabel heeft.
    • FULL JOIN of FULL OUTER JOIN – Een volledige outer join retourneert alle rijen in zowel de linker- als de rechtertabel. Elke keer dat een rij geen overeenkomst heeft in de andere tabel, bevatten de selectielijstkolommen uit de andere tabel null-waarden. Als er een overeenkomst is tussen de tabellen, bevat de volledige resultaatsetrij gegevenswaarden uit de basistabellen.
  • Cross Joins. Cross joins retourneren alle rijen van de linkertabel. Elke rij uit de linker tafel wordt gecombineerd met alle rijen uit de rechter tafel. Dwarsverbindingen worden ook wel Cartesiaanse producten genoemd.
enter image description here

1.1.4. WHERE Clausule

1.1.4.1. Samengestelde WHERE clausules

SQL-WHERE-clausules kunnen meer voorwaarden omvatten door de operators AND, OR, IN en NOT IN te gebruiken. We kunnen bijvoorbeeld het volgende schrijven als we alle rijen in ARTIKEL willen vinden die een Afdeling met de naam Watersport en een Inkoper met de naam Najma Barends bevatten:

SELECT * 
FROM ARTIKEL 
WHERE Afdeling = 'Watersport'
AND Inkoper = 'Najma Barends';
ArtikelnummerArtikelomschrijvingAfdelingInkoper
101100Duikmasker, small, helderWatersportNajma Barends
101200Duikmasker, medium, helderWatersportNajma Barends

We kunnen op een soortgelijke manier alle rijen in ARTIKEL vinden voor de afdelingen Camping of Klimmen door het volgende te schrijven:

SELECT * 
FROM ARTIKEL 
WHERE Afdeling = 'Camping' OR Afdeling = 'Klimmen';
ArtikelnummerArtikelomschrijvingAfdelingInkoper
201000HalvekoepeltentCampingCharlotte Laan
202000Grondzeil halvekoepeltentCampingCharlotte Laan
301000Lichtgewicht klimharnasKlimmenJohan Martens
302000Karabijnsluiting, ovaalKlimmenJohan Martens

Het is ook mogelijk drie of meer OR– en AND-voorwaarden te combineren, maar het is in dergelijke gevallen makkelijker de operators IN and NOT IN te gebruiken. Stel dat we alle rijen in ARTIKEL willen vinden voor de inkopers Najma Barends, Charlotte Laan en Johan Martens. We kunnen een WHERE-clausule met twee OR’s opstellen, maar het is makkelijker het sleutelwoord IN als volgt te gebruiken.

SELECT *
FROM ARTIKEL
WHERE Inkoper IN ('Najma Barends', 'Charlotte Laan', 'Johan Martens');
ArtikelnummerArtikelomschrijvingAfdelingInkoper
101100Duikmasker, small, helderWatersportNajma Barends
101200Duikmasker, medium, helderWatersportNajma Barends
201000HalvekoepeltentCampingCharlotte Laan
202000Grondzeil halvekoepeltentCampingCharlotte Laan
301000Lichtgewicht klimharnasKlimmenJohan Martens
302000Karabijnsluiting, ovaalKlimmenJohan Martens

We kunnen op een soortgelijke manier het volgende schrijven als we alle rijen van ARTIKEL willen vinden waarin de verkoper iemand anders is dan Najma Barends, Charlotte Laan en Johan Martens.

SELECT *
FROM ARTIKEL
WHERE Inkoper NOT IN ('Najma Barends', 'Charlotte Laan', 'Johan Martens');
ArtikelnummerArtikelomschrijvingAfdelingInkoper
100100Standaard SCUBA, geelWatersportPeter Jansen
100200Standaard SCUBA, magentaWatersportPeter Jansen

1.1.4.2. Bereik in WHERE-clausules

Met het sleutelwoord BETWEEN kan voor een SQL-WHERE-clausule een bereik voor gegevenswaarden gespecificeerd worden.

SELECT * 
FROM BESTELLING_ITEM 
WHERE Totaalprijs BETWEEN 100 and 200;
BestellingnummerArtikelnummerAantalStukprijsTotaalprijs
20001011004500.0002.000.000
30001011002500.0001.000.000
20001012002500.0001.000.000
100020200011.300.0001.300.000

Merk op dat beide uitersten van het bereik, 100 en 200 wel in de resulterende tabel zijn opgenomen. Het vorige SQL-statement is gelijkwaardig met:

SELECT * 
FROM BESTELLING_ITEM 
WHERE Totaalprijs >= 100 and Totaalprijs <= 200;

1.1.4.3. Wildcards in WHERE-clausules

Het sleutelwoord LIKE kan in WHERE-clausules worden gebruikt om kolomwaarden te vinden, waarvan een deel wordt opgegeven. Stel dat we rijen in de tabel ARTIKEL willen vinden voor alle inkopers met de voornaam Pete. We vinden deze rijen door het sleutelwoord LIKE als volgt te gebruiken met de wildcard %:

SELECT * 
FROM ARTIKEL 
WHERE Inkoper LIKE 'Pete%';
ArtikelnummerArtikelomschrijvingAfdelingInkoper
100100Standaard SCUBA, geelWatersportPeter Jansen
100200Standaard SCUBA, magentaWatersportPeter Jansen

Het procentteken (%) is een wildcard (of joker) die een willekeurige reeks tekens representeert. De string ‘Pete%‘ geeft dus een willekeurige reeks tekens weer die met de letters Pete begint.

Stel dat we de rijen in ARTIKEL willen vinden waarin het woord Tent ergens in de Artikelomschrijving voorkomt. Het woord Tent zou aan het begin, ergens in het midden of aan het eind van de beschrijving kunnen staan. We moeten daarom als volgt een jokerteken aan beide uiteinden van de LIKE-zinsnede gebruiken:

SELECT * 
FROM ARTIKEL 
WHERE Artikelomschrijving LIKE '%Tent%';
ArtikelnummerArtikelomschrijvingAfdelingInkoper
201000HalvekoepeltentCampingCharlotte Laan
202000Grondzeil halvekoepeltentCampingCharlotte Laan

We moeten soms ook op een specifieke plaats in de kolom naar een bepaalde waarde zoeken. Stel dat Artikelnummer-waarden zo zijn gecodeerd dat een 2 op de derde positie van rechts een bepaalde betekenis heeft – bijvoorbeeld dat et product een variant is van een ander product. Stel dat we om wat voor reden dan ook alle Artikelnummers moeten vinden die een 2 bevatten op de derde positie van rechts. Om het gewenste overzicht te maken, moeten we underscores (_) gebruiken. Het volgende SQL-statement zal alle rijen in ARTIKEL vinden met de waarde 2 in de derde positie van rechts:

SELECT * 
FROM ARTIKEL 
WHERE Artikelnummer LIKE '%2___';
ArtikelnummerArtikelomschrijvingAfdelingInkoper
202000Grondzeil halvekoepeltentCampingCharlotte Laan
302000Karabijnsluiting, ovaalKlimmenJohan Martens

De SQL HAVING-clausule wordt gebruikt om de uitvoer van een SQL-instructie voorwaardelijk te beperken door een statistische SQL-functie die wordt gebruikt in uw SELECT-lijst met kolommen.
U kunt geen criteria opgeven in een SQL WHERE-clausule voor een kolom in de SELECT-lijst waarvoor de statistische SQL-functie wordt gebruikt.

De volgende SQL-instructie genereert bijvoorbeeld een fout:

SELECT Employee, SUM (Hours)
FROM EmployeeHours 
WHERE SUM (Hours) > 24
GROUP BY Employee

De SQL HAVING-clausule wordt gebruikt om precies dit te doen, om een voorwaarde op te geven voor een verzamelfunctie die in uw query wordt gebruikt:

SELECT Employee, SUM (Hours)
FROM EmployeeHours 
GROUP BY Employee
HAVING SUM (Hours) > 24

De bovenstaande SQL-instructie selecteert alle werknemers en de som van hun respectieve uren, zolang deze som groter is dan 24. Het resultaat van de SQL HAVING-clausule is hieronder te zien: * Bron http: //www.sql-tutorial.net/SQL-HAVING.asp.

1.1.5. GROUP BY Clausule

Rijen kun je in SQL groeperen op basis van gemeenschappelijke waarden door het sleutelwoord GROUP BY te gebruiken. Als je bijvoorbeeld GROUP BY Afdeling opgeeft in een SELECT-statement op de tabel ARTIKEL, dan zal het DBMS alle rijen  eerst op Afdeling sorteren en alle rijen die dezelfde waarde bevatten daarna combineren tot een groep van die Afdeling. Er zullen zoveel groepen ontstaan als er unieke waarden zijn voor die Afdeling.

SELECT Afdeling, COUNT(*) AS AantalArtikelenPerAfdeling
FROM ARTIKEL 
GROUP BY Afdeling;
AfdelingAantalArtikelenPerAfdeling
Camping2
Klimmen2
Watersport4

Het DBMS verkrijgt het resultaat door de rijen eerst op Afdeling te sorteren en dan het aantal rijen te tellen dat dezelfde waarde heeft voor Afdeling. Een ander voorbeeld het het gebruikt GROUP BY is:

SELECT Artikelnummer, AVG (Totaalprijs) AS GemiddeldeTP
FROM BESTELLING_ITEM 
GROUP BY Artikelnummer;
ArtikelnummerGemiddeldeTP
100200300.000.000
101100150.000.000
10120075.000.000
201000300.000.000
202000130.000.000

De rijen zijn hier gesorteerd en gegroepeerd op Artikelnummer. De gemiddelde Totaalprijs is berekend voor elke groep Artikelnummer-items. Er kunnen meer kolommen worden opgenomen in een GROUP BY-expressie. Het volgend SQL-statement groepeert de rijen bijvoorbeeld eerst op de waarde van de Afdeling en dan op die van Inkoper. Het statement telt daarna het aantal rijen voor elke combinatie van Afdeling en Inkoper.

SELECT Afdeling, Inkoper, COUNT(*) AS AantalArtikelenPerAfdelingPerInkoper
FROM ARTIKEL 
GROUP BY Afdeling,Inkoper;
AfdelingInkoperAantalArtikelenPerAfdelingPerInkoper
CampingCharlotte Laan2
KlimmenJohan Martens2
WatersportNajma Barends2
WatersportPeter Jansen2

Je kunt natuurlijk ook WHERE– en ORDER BY-clausules gebruiken met SELECT-statements.

SELECT Afdeling, COUNT(*) AS AantalArtikelenNiet30200PerAfdelingPerInkoper
FROM ARTIKEL 
WHERE Artikelnummer <> 30200
GROUP BY Afdeling
ORDER BY COUNT(*);
AfdelingAantalArtikelenNiet30200PerAfdelingPerInkoper
Camping2
Klimmen2
Watersport4