Softwareontwikkeling FAQ - SQL

[NMe]

Softwareontwikkeling FAQ: SQL Met dank aan EfBe, ACM, curry684 en whoami. Laatste wijziging: zondag 05 oktober 2008. Inhoudsopgave 1: Inhoudsopgave 2: Links 3: Boeken Ontwerpen van databases SQL 4: Hoe werken joins? Wanneer een LEFT (OUTER) join? Wanneer een INNER JOIN? Wanneer een RIGHT (OUTER) JOIN? Wanneer een FULL OUTER JOIN? Samenvatting tot nu toe Wanneer een CROSS JOIN? Wanneer een NATURAL JOIN? 5: Wat is database-indexering? Wat zijn Clustered Indexen? Overwegingen Wat is dan een non-clustered index? 6: Hoe kom ik van dubbele resultaten in mijn query af? 7: Over het gebruik van Parametrized Queries Waarom weegt dit op de performance? Wat is dat dan? Wat moet je nu doen om die parameters te gaan opvullen? 8: Hoe werkt dat GROUP BY nu eigenlijk? Wat doet GROUP BY? Aggregate functies Hoeveel kolommen kan ik aggregeren en groeperen? « · ^ Links Object Related Modelling Een abstracte modelleringstechniek, bedachtdoor Halpin, een van de bedenkers van NIAM. http://www.mysql.com/ http://www.postgresql.org/ SQL tutorial 1 SQL tutorial 2 Normaliseren http://www.sql-server-performance.com/ Comparison of different SQL implementations Website over de verschillen van de bestaande SQL-dialecten. « · ^ 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 « · ^ Hoe werken joins? Met dank aan 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: 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: 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: 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: SELECT category.name, category.id, product.product_id FROM product RIGHT JOIN category ON(category.id=product.category_id) ; Wanneer een 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. Samenvatting tot nu toe Om het even duidelijk te maken de volgende plaatjes. Wanneer een 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. Wanneer een 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 één 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: 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. 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 Maak rustig wat indexen op tabellen waarvan de inhoud niet al te vaak zal veranderen 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 Bedenk dat je altijd je indexen later kan tunen als je ziet waar de werkelijke bottlenecks bij gebruik zijn 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 belangrijk om een clustered index op zo min mogelijk kolommen te zetten. 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 C#: 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 namelijk, parametrized queries. 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: C#: 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: C#: 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? Met dank aan 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): A B C 1 1 1 1 1 2 1 2 3 1 2 4 2 3 5 2 3 6 2 4 7 2 4 8 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: 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: 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 Uit de Van Dale: ag·gre·ga·tie (de ~ (v.), ~s, ~s, ~s) 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: SELECT a, max(b) FROM Table GROUP BY a Hier komt het volgende resultaat uit: A max(B) 1 2 2 4 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: SELECT a, max(b), count(c) FROM Table GROUP BY a A max(B) count(C) 1 2 4 2 4 4 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 ofwel een aggregate functie hebben. Een voorbeeld van groeperen op meerdere kolommen: SQL: SELECT a, b, count(c) FROM Table GROUP BY a, b levert op: A B count(C) 1 1 2 1 2 2 2 3 2 2 4 2 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 222% gewijzigd door NMe op 21-12-2019 18:17]