Toon posts:

P&W FAQ - SQL

Pagina: 1
Acties:
  • 5.894 views sinds 30-01-2008

zaterdag 12 april 2003 11:33

Acties:
  • 0 Henk 'm!
  • drm
  • Registratie: Februari 2001
  • Laatst online: 19-05 13:58

drm

f0pc0dert

Topicstarter

Programming & Webscripting FAQ: SQL

Met dank aan EfBe, ACM, curry684 en whoami

Links

Boeken

Ontwerpen van Databases

  • An Introduction To Database Systems

    Door: C.J. Date
    ISBN: 0-201-68419-5
    Longman Higher Education Division

  • Access Database Design & Programming

    Door: Steven Roman
    ISBN: 1-56592-626-9
    O'Reilly & Associates, Inc.

  • Database Systems: A Practical Approach to Design, Implementation, and Management (3rd Edition)

    Door: Carolyn Begg, Thomas Connolly
    ISBN: 0201708574
    Addison Wesley Publishing Company

  • Fundamentals of Database Systems (3rd Edition)

    Door: Ramez Elmasri, Shamkant Navathe
    ISBN: 0805317554
    Addison Wesley Publishing Company

  • Leerboek databases, beginselen, ontwerp & implementatie

    Door: David Kroenke
    ISBN: 9039509026
    Academic Service

SQL

  • MySQL and mSQL

    Door: George Reese, Randy Ray Yarger, Tim King
    ISBN: 1565924347
    O'Reilly & Associates Inc.

  • Professional SQL Server 2000 Programming

    Door: Robert Vieira
    ISBN: 1861004486
    Wrox press

  • The Practical SQL Handbook (using Structured Query Language)

    Door:
    ISBN: 0201447878
    -n/a-

‹ · ^

Hoe werken joins?

door ACM en drm

‹ · ^

Wanneer een LEFT (OUTER) join?

Een left join gebruik je wanneer er een relatie bestaat tussen 2 entiteiten die 1:m is, waarbij m ? 0. Een voorbeeld hier van is de relatie "categorie" - "product". Een categorie hoeft geen producten te bevatten om een categorie te zijn. Er kunnen dus records in een categoriën tabel voorkomen waar geen producten naar verwijzen. Als je dan een LEFT JOIN doet, worden ook de categorien meegenomen waar geen producten naar verwijzen. In de velden uit de producten tabel die je dan in de SELECT clause hebt staan, komt NULL te staan.

SQL:
1
2
3
4
5
6
7
8
9

SELECT
   category.name,
   category.id,
   product.product_id
FROM
   category
      LEFT JOIN product 
         ON(category.id=product.category_id)
   ;
‹ · ^

Wanneer een INNER JOIN?

Een inner join gebruik je wanneer je enkel die resultaten wilt, waarbij de relatie (vastgelegd in de JOIN clause) voorkomt. Om uit te gaan van bovenstaand voorbeeld: een lege categorie weergeven is niet interessant. Je kunt dat oplossen door een LEFT JOIN te gebruiken en een COUNT() los te laten op de primaire sleutel van de producten tabel, maar je kunt ook gewoon een INNER JOIN gebruiken:

SQL:
1
2
3
4
5
6
7
8
9

SELECT
   category.name,
   category.id,
   product.product_id
FROM
   category 
      INNER JOIN product 
         ON(category.id=product.category_id)
   ;

Overigens is een andere manier om tot een INNER JOIN resultaat te komen de volgende manier:

SQL:
1
2
3
4
5
6
7
8
9
10

SELECT
   category.name,
   category.id,
   product.product_id
FROM
   category,
   product
WHERE
   category.id=product.category_id
   ;

Deze wijze van inner joinen staat ook wel bekend als de EQUI join, waar "equi" natuurlijk staat voor "gelijk".

‹ · ^

Wanneer een RIGHT (OUTER) join?

Een RIGHT join is feitelijk het tegenovergestelde van een LEFT JOIN. Je gebruikt deze wanneer bij een relatie n:1, waarbij n ? 0. Dus je zou bovenstaande query kunnen herschrijven tot een RIGHT join query door de 2 tabellen om te wisselen ;)

SQL:
1
2
3
4
5
6
7
8
9

SELECT
   category.name,
   category.id,
   product.product_id
FROM
   product 
      RIGHT JOIN category 
         ON(category.id=product.category_id)
   ;
‹ · ^

FULL OUTER JOIN

Waarschijnlijk heb je nu zelf al bedacht wat een FULL OUTER JOIN doet ;) Om het nog even te visualiseren kun je onderstaande plaatjes even bekijken.

Samengevat

Om het even duidelijk te maken de volgende plaatjes.
Geselecteerde records
ON clause voldoet op deze records
ON clause voldoet niet op deze records

  1. LEFT JOIN
    Tabel 1 Tabel 2
    Records Records
    Records Records
    Records Records
  2. INNER JOIN
    Tabel 1 Tabel 2
    Records Records
    Records Records
    Records Records
  3. RIGHT JOIN
    Tabel 1 Tabel 2
    Records Records
    Records Records
    Records Records
  4. OUTER JOIN
    Tabel 1 Tabel 2
    Records Records
    Records Records
    Records Records
‹ · ^

CROSS JOIN

De cross join is een join die twee tabellen kruislings aan elkaar koppelt, dus voor elk record in tabel wordt een record van tabel2 bijgevoegd. Dit wordt ook wel een carthetisch product genoemd. Het voordeel van de cross join is dat je daarmee expliciet twee tabellen een carthetisch product kan laten vormen en verder alle andere tabellen op normale wijze kan joinen.

Cross joins zou je kunnen gebruiken wanneer je bijvoorbeeld een tabel met vormen en een tabel met kleuren hebt en hier alle mogelijke combinaties van terug wilt krijgen.

‹ · ^

NATURAL JOIN

Het NATURAL-keyword zorgt ervoor dat de database automatisch die kolommen bij elkaar past waadoor de "meest natuurlijke" overeenkomst genomen wordt. Dat zal bijvoorbeeld de primary key van de ene tabel zijn die matched op een foreign key in de andere tabel of simpelweg een lijst van alle kolommen met eenzelfde naam.

Je gebruikt de natural join als je wilt dat de database voor je uitzoekt welke kolommen de relatie het beste weergeven.

‹ · ^

Wat is database-indexering?

Met dank aan MrX en EfBe

Indexes kunnen je systeem zowel sneller als langzamer maken. Een goeie strategie opzetten voor indexeren kan je behoorlijk wat performance schelen. Een index kost performance in de volgende gevallen:

  • INSERTs in een tabel waar 1 of meer indexen op staan
  • DELETEs in een tabel waar 1 of meer indexen op staan
  • UPDATEs van een waarde in een kolom van een tabel waar 1 of meer indexen op staan

Hierbij geldt dus: hoe meer indexen er door een actie aangepast moeten worden, hoe meer performance dat kost.
Een performance voordeel van indexen is te behalen op de volgende wijzen:

  • Als je op een bepaalde waarde in een kolom zoekt of ermee vergelijkt

    WHERE kolom=value, WHERE kolom > value, WHERE kolom BETWEEN val1 AND val2, etc

  • Als je gaat sorteren

    ORDER BY kolom

  • Als je gaat groeperen

    (GROUP BY kolom)

  • Als je joins gaat maken

    FROM tabel1 JOIN tabel2 ON tabel1.kolom1=tabel2.kolom3

Vooral bij hele grote tabellen kan het gebruik van een index je applicatie gigantisch veel sneller maken (1000 maal sneller en meer).

Ga nadenken over welke kolommen je in 1 van de bovengenoemde manieren gebruikt, en hoe vaak je dat doet, hoe veel gegevens er in de tabel staan, etc. Op basis daarvan neem je beslissingen op welke kolommen een index moet staan.

Goeie vuistregels zijn:

  1. Zet altijd een index op foreign key kolommen in je tabellen, tenzij je een hele goeie reden hebt om dat niet te doen

    Als je je afvraagt of jouw reden een goeie is om het niet te doen: Dan is het geen goede reden :Y)

  2. Zet indexen op de meest gebruikte kolommen van alle grote tabellen, tenzij je weet dat er heel veel updates/inserts/deletes gebeuren terwijl er niet zo veel in de tabel gezocht zal worden
  3. Maak rustig wat indexen op tabellen waarvan de inhoud niet al te vaak zal veranderen
  4. Denk na of je eventueel indexen wil leggen op de combinatie van kolommen als deze kolommen vaak in combinatie gebruikt worden en er een performance bottleneck is
  5. Bedenk dat je altijd je indexen later kan tunen als je ziet waar de werkelijke bottlenecks bij gebruik zijn
  6. Indexen werken alleen goed met een goede balans. De settings voor indexes om de hash table balances te bepalen zijn van essentieelbelang. Bij het toepassen van indices op grotere tabellen moet je kijken naar de index settings als geheel, en test uit welke settings het beste werken voor die betreffende index.

Wat zijn Clustered Indexen?

Met dank aan EfBe

Dit gedeelte en volgende gedeelte "Overwegingen" gelden voor SqlServer 2000, maar is in grote lijnen ook toepasbaar op andere (gelijkwaardige) RDBMS'en

Een clustered index bepaalt de fysieke volgorde van gegevens in een tabel. Een clustered index is vergelijkbaar met een telefoonboek, waarin gesorteerd wordt op een achternaam. Omdat een clustered index de fysieke gegevens-opslag in een tabel bepaalt, kan een tabel slechts één clustured index hebben. Echter, een index kan wel meerdere kolommen beslaan (samengestelde index), net als dat in een telefoonboek ook eerst op achternaam, en daarna op voorletters gesorteerd wordt.

Een clustured index is vooral efficient op kolommen waarin vaak gezocht wordt op waarden of series van waarden. Zodra een record is gevonden waarin een waarde staat waarop gezocht wordt, betekent dat dat de volgende records ook een waarde hebben die daar dicht bij ligt. Bijvoorbeeld: wanneer een applicatie vaak een query uitvoert die records tussen 2 datums opvraagt, kan de clustered index heel snel de eerste record met de begin-datum vaststellen en alle volgende records totdat de eind-datum is bereikt. Zodoende wordt de performance van een dergelijke query sterk verbeterd. Ook wanneer vaak op een bepaalde kolom of kolommen wordt gesorteerd kan het een grote verbetering opleveren als juist op die kolommen een clustered index wordt gezet.

Wanneer een bepaald veld in de tabel uniek is, kan een clustered index een goede performance-boost geven. De snelste manier om bijvoorbeeld een bepaalde werknemer op te zoeken is door op de kolom werknemer_id een PRIMARY KEY constraint te zetten, of dat veld een clustered index te geven.

Opmerking: PRIMARY KEY constraints maken automatisch een clustered index op de tabel aan, wanneer er niet al een (andere) clustered index op de tabel bestaat en geen non-clustered index is gespecificeerd op het moment van zetten van de PRIMARY KEY

Een andere manier is om een clustered index te zetten op achternaam en voornaam, omdat werknemers vaak gegroepeerd en gesorteerd worden op deze velden, in plaats van op werknemer_id

Overwegingen

Het is belangrijke om een clustered index enkel te zetten op zo min mogelijk kolommen. Als een grote clustered index is gedefinieerd, worden non-clustered indexen ook direct stukken groter, omdat de non-clustered index-entries ook de geclusterde sleutel bevatten.

Voordat je clustered indexen aanmaakt is het handig te weten hoe je gegevens opgevraagd zullen worden. In de volgende gevallen kan een clustered index overwogen worden:

  • Kolommen die een grote varieteit aan verschillende waarden bevatten (denk aan bijvoorbeeld timestamps)
  • Queries die opeenvolgende gegevens teruggeven. Denk aan BETWEEN, >, <, >= en <=.
  • Kolommen die sequentieel opgevraagd worden
  • Queries die grote resultsets teruggeven
  • Kolommen die vaak opgevraagd worden door middel van JOINs (normaal gesproken foreign keys), GROUP BY of ORDER BY clausules.
  • OLTP-type applicaties waar snel opvragen van enkele records belangrijk is, meestal bij selectie op de primary key. Geef dan de clustered index aan op de primary key van de tabel.

Gebruik geen clustered indexen in de volgende gevallen:

  • Kolommen die onderhevig zijn aan regelmatige veranderingen

    Wanneer velden veel veranderen moet het RDBMS ook de volledige fysieke opslag van het record aanpassen, en dit kost veel tijd.

Wat is dan een non-clustered index?

Non-clustered indexen zijn indexen die in aparte indexeringstabellen opgeslagen worden.

‹ · ^

Hoe kom ik van dubbele resultaten in mijn query af?

Door gebruik te maken van SELECT DISTINCT kun je in de meeste gevallen voorkomen dat een DBMS dubbele resultaten teruggeeft. Let wel op dat DISTINCT filtert op die velden die uniek zijn uit je SELECT-clause. Als er dus non-unieke velden voorkomen in de SELECT list krijg je nog niet de resultaten die je wilt hebben. Het is dan vaak ook beter om gebruik te maken van GROUP BY en aggregated functions als COUNT, MAX, SUM, etcetera. Zie voor meer informatie de betreffende documentatie van je DBMS.

‹ · ^

Over het gebruik van Parametrized Queries

Met dank aan: whoami

Dit stuk is niet van toepassing op de combinatie PHP/MySQL. Over hoe je SQL injection e.d. kan voorkomen lees je meer in de PHP FAQ over escapen in MySQL en beveiliging van websites

In dit voorbeeld wordt gebruik gemaakt van C# en MS SQL Server. Sla de documentatie van jouw gebruikte platform en DBMS er op na of parametrized queries ondersteund worden.

Database applicaties maken gebruik van SQL om gegevens in een databank te stoppen en gegevens eruit te halen. Al te vaak zie je maar dat mensen hun SQL statements door middel van string concatenation opbouwen, bv:

Fout

code:
1
2

string sSQL = "INSERT INTO personen (naam, voornaam) " +
              "VALUES ( " + txtNaam.Text + ", " + txtVoornaam.Text + ")";

Dit is echter een slechte manier om je queries op te bouwen. Allereerst is het moeilijk leesbaar, moeilijk onderhoudbaar, maar het weegt vooral op de performance van je query en het zorgt ook voor een veiligheidsrisico.

Waarom weegt dit op de performance?

Door de manier waarop je je query hebt opgebouwd, kan hij niet gecached worden door het DBMS als hij later nog eens moet worden uitgevoerd. Nu kan je zeggen: Ok, ik voer die query zowiezo slechts 1x uit. Wat is dat dan ivm die veiligheid? :?.

Stel dat je in txtNaam een naam ingeeft waar een quote in voorkomt. Dan moet jij als programmeur allerhande truuks gaan toepassen om die quote goed in de DB te krijgen. Aangezien we ons niet graag met geneuzel bezig houden, en dat bovendien nogal eens foutgevoelig kan zijn, gaan we liever een andere methode gebruiken nl, parametrized queries :Y)

Wat is dat dan :?

Bij deze methode hoef je je als programmeur helemaal geen zorgen meer te maken over die quotes.

In plaats van je query met behulp van string concatenation samen te stellen, ga je hem als volgt schrijven:

code:
1
2

string sSQL = "INSERT INTO personen (naam, voornaam) " +
              "VALUES (@p_naam, @p_voornaam)";

Zoals je kunt zien gaan we nu nog geen waarden in de query gaan stoppen, maar maken we gebruik van parameters. Die kan je als 'placeholders' beschouwen waar we later de waarden gaan invullen.

Het voordeel van deze aanpak is dat het DBMS in staat is om de query te cachen. Het optimale execution plan wordt dus bewaard, en zal hergebruikt worden als die query nog eens uitgevoerd wordt. (Dit is vooral van belang bij SELECT queries).

Wat moet je nu doen om die parameters te gaan opvullen? :?

De DbCommand class heeft een property 'Parameters'. Je zult daarmee dus aan de slag moeten:

code:
1
2
3
4
5
6
7
8
9
10

string sSQL = "INSERT INTO personen (naam, voornaam) " +
              "VALUES (@p_naam, @p_voornaam)";
              
SqlCommand1.CommandText = sSQL;

SqlCommand1.Parameters.Add ("@p_naam", SqlDbType.Varchar);
SqlCommand1.Parameters.Add ("@p_voornaam", SqlDbType.Varchar);

SqlCommand1.Parameters["@p_naam"].Value = txtNaam.Text;
SqlCommand1.Parameters["@p_voornaam"].Value = txtVoornaam.Text;

Zoals je ziet gaan we de 2 parameters 'kenbaar' maken bij het DbCommand object. We geven de naam van de parameter mee, en het datatype, daarna geven we de waarde die moet gebruikt worden ook mee.

Indien er quotes oid in de naam staan, dan zal het DBMS er zelf voor zorgen dat die naam probleemloos in de DB ingevoerd wordt. Verder kan je ook nog bijkomende zaken over die parameter specifieren, zoals: is het een input of een outputparameter, etc...

Om daar meer informatie over te verkrijgen kan je eens kijken naar de verschillende overloads van de Add() method, of naar de SqlParameter of OleDbParameter class (en de betreffende constructors).

Als je gebruik maakt van stored procedures, dan kan je natuurlijk dezelfde werkwijze ivm parameters gebruiken.

Opgepast:

Als je gebruik maakt van de OleDbClient, moet je je parameters in dezelfde volgorde 'adden' zoals je ze in je SQL statement gedefinieerd hebt. Dit komt omdat OleDb geen rekening gaat gaan houden met de parameternamen.

‹ · ^

Hoe werkt dat GROUP BY nu eigenlijk?

door curry684

De GROUP BY clausule is een krachtig onderdeel van SQL, en tegelijk een van de minst begrepen helaas. In dit stukje zal ik proberen helder en duidelijk uit te leggen wat het nu eigenlijk doet, en wat het niet doet. Bij de voorbeelden gaan we even uit van de volgende tabel met data (alle data is integers):

ABC
111
112
123
124
235
236
247
248

Wat doet GROUP BY?

Middels een GROUP BY-clausule in je SELECT-query kun je de database vragen om identieke resultaten te groeperen, oftewel 'samen te vatten'. Om het bovenstaande voorbeeld even te pakken met de eenvoudigste GROUP BY query:

SQL:
1

SELECT a FROM Table GROUP BY a

Dit geeft 2 rows, namelijk:

A
1
2

wat alleszins duidelijk en logisch is. Het wordt moeilijker als je meerdere kolommen wil hebben. Stel je de volgende query eens voor:

SQL:
1

SELECT a, b FROM Table GROUP BY a

SQL Server, Oracle en andere fatsoenlijke databases slikken deze query niet. Om SQL2000 vrij te citeren:

Column 'Table.B' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Waarom zegt ie dat? Kijk eens mee naar de tabel waar we deze query op doen. We vragen hem te groeperen op identieke waarden in kolom A. Echter, als we dat doen, welke waarde van B moeten we dan selecteren bij waarde 1 in A? Daar kan je zowel 1 als 2 bij verwachten in kolom B, en omdat dat niet gespecificeerd is staat de database het terecht niet toe.

MySQL is een hele brakke database, die deze laatste constructie wel toestaat. En volgens de handleiding is het 'by design' dat je vervolgens random waardes in kolom B aantreft. Don't do it.

Aggregate functies

ag·gre·ga·tie (de ~ (v.), ~s, ~s, ~s)
1 samenvoeging => vereniging

Om dit probleem op te lossen hebben we 'Aggregate functies'. Waar het in bovenstaand voorbeeld onduidelijk was hoe B samengevat moest worden om bij de unieke A's te passen, kunnen we de DBMS natuurlijk vertellen welke waarde hij dan moet gebruiken. Zie bijvoorbeeld deze query:

SQL:
1

SELECT a, max(b) FROM Table GROUP BY a

Hier komt het volgende resultaat uit:

Amax(B)
12
24

Door de max-functie los te laten op kolom B, hebben we de database duidelijk gemaakt hoe hij moet groeperen. Dus in dit geval groupeert hij op A, en vervolgens laat ie de 'aggregerende functie' los op de verzamelde waardes van B die bij die ene A terecht horen. Andere voorbeelden van aggregate functies zijn min(), avg() en count().

Een ander voorbeeld:

SQL:
1

SELECT a, max(b), count(c) FROM Table GROUP BY a

Hier komt uit:

Amax(B)count(C)
124
244

Oftewel we grijpen de A's bij elkaar, en selecteren daarbinnen de hoogste B's en het aantal C's dat we hebben 'weggegroepeerd'.

Hoeveel kolommen kan ik aggregeren en groeperen?

Zoveel je wilt :) Maar let op de hoofdregel: zodra je een GROUP BY-clause gebruikt, moeten alle geselecteerde kolommen terugkomen in de GROUP BY list oftewel een aggregate functie hebben. Een voorbeeld van groeperen op meerdere kolommen:

SQL:
1

SELECT a, b, count(c) FROM Table GROUP BY a, b

Levert op:

ABcount(C)
112
122
232
242

Wat is hier nu gebeurd? Allereerst hebben we gegroepeerd op A's, en daarna op de B's. Van de resulterende set hebben we vervolgens het aantal C's genomen dat 'weggeaggregeerd' is, en dat zijn er inderdaad 2 per groep. Klopt exact dus :)

[ Voor 255% gewijzigd door NMe op 29-06-2005 14:29 ]

Music is the pleasure the human mind experiences from counting without being aware that it is counting
~ Gottfried Leibniz

Dit topic is gesloten.

Apple iPhone 16e LG OLED evo G5 Google Pixel 10 Samsung Galaxy S25 Star Wars: Outlaws Nintendo Switch 2 Apple AirPods Pro (2e generatie) Sony PlayStation 5 Pro

Tweakers is onderdeel van DPG Media B.V.
Alle rechten voorbehouden - Auteursrecht © 1998 - 2025 • Hosting door TrueFullstaq