Bent u klaar om te beginnen met het maken van databases en tabellen met de Structured Query Language? In dit artikel onderzoeken we het proces van het handmatig maken van tabellen met de opdrachten CREATE DATABASE en CREATE TABLE. Als SQL nieuw voor je is, wil je misschien wat lezen Basisprincipes van SQL eerste.
Zakelijke vereisten
Voordat we achter het toetsenbord gaan zitten, moeten we ervoor zorgen dat we een goed begrip hebben van de eisen van de klant. Wat is de beste manier om dit inzicht te krijgen? In gesprek met de klant natuurlijk! Na een gesprek met de Human Resources Director van XYZ, hebben we vernomen dat ze een widgetverkoopbedrijf zijn en vooral geïnteresseerd zijn in het bijhouden van informatie over hun verkooppersoneel.
XYZ Corporation verdeelt zijn verkoopteam in oostelijke en westelijke regio's, die elk zijn verdeeld in vele gebieden die worden gedekt door individuele verkopers. De HR-afdeling wil het gebied dat door elke werknemer wordt bestreken, volgen, evenals de salarisinformatie en toezichtstructuur van elke werknemer. Om aan deze vereisten te voldoen, hebben we een database ontworpen die bestaat uit drie tabellen, weergegeven in de:
Entiteit-relatiediagram op deze pagina.Een databaseplatform kiezen
We hebben besloten om een databasemanagementsysteem (of DBMS) dat is gebouwd op de Structured Query Language (SQL). Daarom moeten al onze opdrachten voor het maken van databases en tabellen worden geschreven met standaard ANSI SQL in gedachten.
Als bijkomend voordeel zorgt het gebruik van ANSI-compatibele SQL ervoor dat deze opdrachten werken op elk DBMS dat de SQL-standaard ondersteunt, inclusief Oracle en Microsoft SQL Server. Als u nog geen platform voor uw database heeft geselecteerd, leidt Database Software Options u door het selectieproces.
De database maken
Onze eerste stap is om de database zelf te maken. Veel databasebeheersystemen bieden een reeks opties om databaseparameters bij deze stap aan te passen, maar onze database staat alleen de eenvoudige creatie van een database toe. Zoals bij al onze commando's, wilt u wellicht de documentatie voor uw DBMS raadplegen om te bepalen of geavanceerde parameters die door uw specifieke systeem worden ondersteund, aan uw behoeften voldoen. Laten we de opdracht CREATE DATABASE gebruiken om onze database in te stellen:
MAAK DATABASE personeel
Let vooral op het hoofdlettergebruik in het bovenstaande voorbeeld. Het is gebruikelijk onder SQL-programmeurs om alle hoofdletters te gebruiken voor SQL-sleutelwoorden zoals "CREATE" en "DATABASE" terwijl alle kleine letters worden gebruikt voor door de gebruiker gedefinieerde namen zoals de "personeels"-database naam. Deze conventies zorgen voor een gemakkelijke leesbaarheid.
Nu we onze database hebben ontworpen en gemaakt, zijn we klaar om te beginnen met het maken van de drie tabellen die worden gebruikt om de personeelsgegevens van XYZ Corporation op te slaan.
Onze eerste tafel maken
Onze eerste tabel bestaat uit persoonlijke gegevens voor elke medewerker van ons bedrijf. We moeten de naam, het salaris, de ID en de manager van elke werknemer vermelden. Het is een goede ontwerppraktijk om de voor- en achternaam in afzonderlijke velden te scheiden om het zoeken en sorteren van gegevens in de toekomst te vereenvoudigen. We houden ook de manager van elke werknemer bij door een verwijzing naar de werknemers-ID van de manager in elk werknemersrecord in te voegen. Laten we eerst eens kijken naar de gewenste medewerkerstafel.
Het attribuut ReportsTo slaat de manager-ID voor elke werknemer op. Uit de getoonde voorbeeldrecords kunnen we afleiden dat Sue Scampi de manager is van zowel Tom Kendall als John Smith. Er is echter geen informatie in de database over Sue's manager, zoals aangegeven door de NULL-vermelding in haar rij.
Nu kunnen we SQL gebruiken om de tabel in onze personeelsdatabase aan te maken. Voordat we dit doen, moeten we ervoor zorgen dat we ons in de juiste database bevinden door een USE-commando te geven:
GEBRUIK personeel;
Als alternatief kan het "DATABASE-personeel;" commando zou dezelfde functie vervullen. Nu kunnen we kijken naar de SQL-opdracht die is gebruikt om onze werknemerstabel te maken:
MAAK TAFEL medewerkers
(employeeid INTEGER NIET NULL,
achternaam VARCHAR(25) NIET NULL,
voornaam VARCHAR(25) NIET NULL,
rapporteert naar INTEGER NULL);
Houd er, net als bij het bovenstaande voorbeeld, rekening mee dat de programmeerconventie dicteert dat we alle hoofdletters gebruiken voor SQL-sleutelwoorden en kleine letters voor kolommen en tabellen met gebruikersnamen. Het bovenstaande commando lijkt in eerste instantie misschien verwarrend, maar er zit eigenlijk een eenvoudige structuur achter. Hier is een algemene weergave die de zaken een beetje kan ophelderen:
CREATE TABLE table_name
(attribute_name datatype opties,
...,
attributennaam datatype opties);
Attributen en gegevenstypen
In het vorige voorbeeld is de tabelnaam werknemers en nemen we vier attributen: werknemer-ID, achternaam, voornaam en rapporten aan. Het datatype geeft aan welk type informatie we in elk veld willen opslaan. De werknemer-ID is een eenvoudig geheel getal, dus we gebruiken het gegevenstype INTEGER voor zowel het veld employeeid als het veld reportsto. De namen van de werknemers zijn tekenreeksen van variabele lengte en we verwachten niet dat een werknemer een voor- of achternaam heeft die langer is dan 25 tekens. Daarom gebruiken we voor deze velden het type VARCHAR(25).
NULL-waarden
We kunnen ook specificeren: NULL of NIET NULL in het optieveld van de CREATE-instructie. Dit vertelt de database eenvoudig of NULL (of lege) waarden zijn toegestaan voor dat kenmerk bij het toevoegen van rijen aan de database. In ons voorbeeld vereist de HR-afdeling dat voor elke werknemer een werknemers-ID en volledige naam worden opgeslagen. Niet elke medewerker heeft echter een manager (de CEO rapporteert aan niemand!) dus we staan NULL-invoer toe in dat veld. Merk op dat NULL de standaardwaarde is en dat het weglaten van deze optie impliciet NULL-waarden voor een attribuut toelaat.
De resterende tabellen bouwen
Laten we nu eens kijken naar de tabel met gebieden. Uit een snelle blik op deze gegevens blijkt dat we een geheel getal en twee strings met variabele lengte moeten opslaan. Net als bij ons vorige voorbeeld verwachten we niet dat de regio-ID meer dan 25 tekens zal gebruiken. Sommige van onze territoria hebben echter langere namen, dus we breiden de toegestane lengte van dat kenmerk uit tot 40 tekens.
Laten we eens kijken naar de bijbehorende SQL:
MAAK TABELgebieden
(territoryid INTEGER NIET NULL,
territorium Beschrijving VARCHAR(40) NOT NULL,
regionid VARCHAR (25) NIET NULL);
Ten slotte gebruiken we de tabel EmployeeTerritories om de relaties tussen werknemers en territoria op te slaan. Gedetailleerde informatie over elke werknemer en territorium is opgeslagen in onze vorige twee tabellen. Daarom hoeven we alleen de twee gehele identificatienummers in deze tabel op te slaan. Als we deze informatie moeten uitbreiden, kunnen we een JOIN gebruiken in onze gegevensselectiecommando's om informatie uit meerdere tabellen te verkrijgen.
Deze methode van gegevensopslag vermindert de redundantie in onze database en zorgt voor een optimaal gebruik van de ruimte op onze opslagschijven. We zullen het JOIN-commando diepgaand behandelen in een toekomstige zelfstudie. Hier is de SQL-code om onze laatste tabel te implementeren:
MAAK TABEL werknemersgebieden
(employeeid INTEGER NIET NULL,
territoriumid INTEGER NIET NULL);
Het mechanisme dat SQL biedt om de structuur van een database na het maken te wijzigen
Als je tegenwoordig bijzonder scherpzinnig bent, is het je misschien opgevallen dat we "per ongeluk" een van de ontwerpvereisten hebben weggelaten bij het implementeren van onze databasetabellen. De HR-directeur van XYZ Corporation verzocht de database om salarisgegevens van werknemers bij te houden en we hebben verzuimd hierin te voorzien in de databasetabellen die we hebben gemaakt.
Alles is echter niet verloren. We kunnen het ALTER TABLE commando gebruiken om dit attribuut toe te voegen aan onze bestaande database. We willen het salaris opslaan als een geheel getal. De syntaxis lijkt veel op die van de opdracht CREATE TABLE, hier is het:
ALTER TABLE medewerkers
VOEG salaris INTEGER NULL toe;
Merk op dat we hebben opgegeven dat NULL-waarden zijn toegestaan voor dit kenmerk. In de meeste gevallen is er geen optie bij het toevoegen van een kolom aan een bestaande tabel. Dit komt doordat de tabel al rijen bevat zonder invoer voor dit kenmerk. Daarom voegt het DBMS automatisch een NULL-waarde in om de leegte te vullen.