Een selectie materialen voor het bestuderen van databases en SQL. SQL leren

• Handleiding

Waar gaat deze tutorial over?

• DML – Data Manipulation Language, die de volgende constructies bevat:
  • SELECT – gegevensselectie
  • INSERT – nieuwe gegevens invoegen
  • UPDATE – gegevensupdate
  • DELETE – gegevens verwijderen
  • MERGE – gegevens samenvoegen

Dit leerboek is gemaakt volgens het Step by Step-principe, d.w.z. je moet het in volgorde lezen en bij voorkeur meteen de voorbeelden volgen. Maar als u onderweg meer over een bepaald commando wilt leren, gebruik dan een specifieke zoekopdracht op internet, bijvoorbeeld in de MSDN-bibliotheek.

Voor het schrijven van deze tutorial is gebruik gemaakt van de MS SQL-database. Serverversie 2014, ik gebruikte MS SQL Server om scripts uit te voeren Managementstudio(SSMS).

Kort over MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) is een hulpprogramma voor Microsoft SQL Server voor het configureren, beheren en beheren van databasecomponenten. Dit hulpprogramma bevat een scripteditor (die we voornamelijk zullen gebruiken) en een grafisch programma dat werkt met serverobjecten en instellingen. Het belangrijkste hulpmiddel van SQL Server Management Studio is Object Explorer, waarmee de gebruiker serverobjecten kan bekijken, ophalen en beheren. Deze tekst Gedeeltelijk overgenomen van Wikipedia.

Om een ​​nieuwe scripteditor te maken, gebruikt u de knop “Nieuwe query”:

Om de huidige database te wijzigen kunt u de vervolgkeuzelijst gebruiken:

Om een ​​specifieke opdracht (of groep opdrachten) uit te voeren, selecteert u deze en drukt u op de knop “Uitvoeren” of de toets “F5”. Als er momenteel slechts één opdracht in de editor staat, of als u alle opdrachten moet uitvoeren, hoeft u niets te selecteren.

Nadat u scripts hebt uitgevoerd, vooral scripts die objecten maken (tabellen, kolommen, indexen), kunt u, om de wijzigingen te zien, vernieuwen gebruiken vanuit het contextmenu door de juiste groep (bijvoorbeeld Tabellen), de tabel zelf of de groep Kolommen daarin te markeren.

Eigenlijk is dat alles wat we moeten weten om de hier gegeven voorbeelden te voltooien. De rest van het SSMS-hulpprogramma is eenvoudig zelf te leren.

Een beetje theorie

DBMS – Databasebeheersysteem, d.w.z. dit is een set tools om met een specifiek type database te werken (MS SQL, Oracle, MySQL, Firebird, ...).

Opmerking
Omdat in het leven zeggen we in de omgangstaal meestal: “Oracle DB”, of zelfs gewoon “Oracle”, wat eigenlijk “Oracle DBMS” betekent, en in de context van dit leerboek zal soms de term DB worden gebruikt. Uit de context denk ik dat het duidelijk zal zijn waar we het precies over hebben.

Een tabel is een verzameling kolommen. Kolommen kunnen ook velden of kolommen worden genoemd; al deze woorden worden gebruikt als synoniemen die hetzelfde uitdrukken.

De tabel is het hoofdobject van de RDB; alle RDB-gegevens worden rij voor rij opgeslagen in de kolommen van de tabel. Lijnen en records zijn ook synoniemen.

Voor elke tabel, evenals voor de kolommen, worden namen gespecificeerd waarmee ze vervolgens worden benaderd.
De naam van een object (tabelnaam, kolomnaam, indexnaam, etc.) kan in MS SQL hebben maximale lengte 128 tekens.

Als referentie– in de ORACLE-database mogen objectnamen een maximale lengte van 30 tekens hebben. Daarom moet u voor een specifieke database uw eigen regels voor de naamgeving van objecten ontwikkelen om aan de limiet van het aantal tekens te voldoen.

SQL is een taal waarmee u een database kunt bevragen met behulp van een DBMS. In een specifiek DBMS kan de SQL-taal een specifieke implementatie hebben (een eigen dialect).

DDL en DML zijn een subset van de SQL-taal:

• De DDL-taal wordt gebruikt om de databasestructuur te creëren en te wijzigen, d.w.z. om tabellen en relaties te maken/wijzigen/verwijderen.
• Met de DML-taal kunt u tabelgegevens manipuleren, d.w.z. met haar lijnen. Hiermee kunt u gegevens uit tabellen selecteren, nieuwe gegevens aan tabellen toevoegen en bestaande gegevens bijwerken en verwijderen.

In SQL kunt u 2 soorten commentaar gebruiken (enkelregelig en meerregelig):

Eén regel commentaar
En

/* commentaar met meerdere regels */

Eigenlijk is dit genoeg voor de theorie.

DDL – Taal voor gegevensdefinitie

Elk van deze kolommen kan worden gekarakteriseerd door het type gegevens dat het bevat:

• Personeelsnummer – geheel getal
• Volledige naam – tekenreeks
• Geboortedatum - datum
• E-mail – tekenreeks
• Positie - tekenreeks
• Afdeling - lijn

Om te beginnen is het voldoende om alleen de volgende basisgegevenstypen te onthouden die in MS SQL worden gebruikt:

Om de voorbeelden uit te voeren, gaan we een testdatabase maken met de naam Test.

Een eenvoudige database (zonder op te geven aanvullende parameters) kan worden gemaakt door de volgende opdracht uit te voeren:

DATABASE MAKEN Test
Je kunt de database verwijderen met het commando (je moet heel voorzichtig zijn met dit commando):

DROP DATABASE-test
Om naar onze database over te schakelen, kunt u het commando uitvoeren:

GEBRUIK Test
U kunt ook de Testdatabase selecteren in de vervolgkeuzelijst in het SSMS-menugebied. Tijdens het werken gebruik ik deze manier van schakelen tussen databases vaak.

Nu kunnen we in onze database een tabel maken met de beschrijvingen zoals ze zijn, met spaties en Cyrillische tekens:

TABEL MAKEN [Medewerkers]([Personeelsnummer] int, [Naam] nvarchar(30), [Geboortedatum] date, nvarchar(30), [Positie] nvarchar(30), [Afdeling] nvarchar(30))
In dit geval zullen we de namen tussen vierkante haakjes moeten zetten […].

Maar voor meer gemak is het in de database beter om alle objectnamen in het Latijn op te geven en geen spaties in namen te gebruiken. In MS SQL begint in dit geval meestal elk woord met een hoofdletter. Voor het veld “Personeelsnummer” kunnen we bijvoorbeeld de naam Personeelsnummer instellen. U kunt ook cijfers in de naam gebruiken, bijvoorbeeld Telefoonnummer1.

Op een briefje
In sommige DBMS'en kan het volgende naamgevingsformaat “PHONE_NUMBER” meer de voorkeur hebben; dit formaat wordt bijvoorbeeld vaak gebruikt in de ORACLE-database. Bij het opgeven van een veldnaam is het uiteraard wenselijk dat deze niet samenvalt met de trefwoorden die in het DBMS worden gebruikt.

Om deze reden kunt u de syntaxis van vierkante haakjes vergeten en de tabel [Employees] verwijderen:

DROP TABLE [Medewerkers]
Een tabel met werknemers kan bijvoorbeeld de naam “Werknemers” krijgen, en de velden ervan kunnen de volgende namen krijgen:

• ID – Personeelsnummer (werknemer-ID)
• Naam - volledige naam
• Verjaardag – Geboortedatum
• E-mail – E-mail
• Positie - Positie
• Afdeling - Afdeling

Laten we nu onze tabel maken:

TABEL MAKEN Medewerkers(ID int, Naam nvarchar(30), Verjaardagsdatum, E-mailadres nvarchar(30), Functie nvarchar(30), Afdeling nvarchar(30))
Om de vereiste kolommen op te geven, kunt u de optie NOT NULL gebruiken.

Voor een bestaande tabel kunnen velden opnieuw worden gedefinieerd met behulp van de volgende opdrachten:

ID-veld bijwerken ALTER TABLE Medewerkers ALTER COLUMN ID int NOT NULL -- update Naamveld ALTER TABLE Medewerkers ALTER COLUMN Naam nvarchar(30) NOT NULL

Op een briefje
Het algemene concept van de SQL-taal blijft voor de meeste DBMS'en hetzelfde (althans, dit is wat ik kan beoordelen op basis van de DBMS'en waarmee ik heb gewerkt). De verschillen tussen DDL in verschillende DBMS'en liggen voornamelijk in de gegevenstypen (niet alleen hun namen kunnen hier verschillen, maar ook de details van hun implementatie), en de specifieke kenmerken van de implementatie van de SQL-taal kunnen ook enigszins verschillen (dat wil zeggen de de essentie van de commando's is hetzelfde, maar er kunnen kleine verschillen in dialect zijn, helaas, maar er is niet één standaard). Als u de basisprincipes van SQL onder de knie heeft, kunt u eenvoudig van het ene DBMS naar het andere overstappen, omdat... In dit geval hoeft u alleen de details van de implementatie van opdrachten in het nieuwe DBMS te begrijpen, d.w.z. in de meeste gevallen zal het eenvoudigweg trekken van een analogie volstaan.

Een tabel maken CREATE TABLE Medewerkers(ID int, -- in ORACLE is het int-type het equivalent (wrapper) voor number(38) Naam nvarchar2(30), -- nvarchar2 in ORACLE is equivalent aan nvarchar in MS SQL Verjaardagsdatum, E-mail nvarchar2(30) , Positie nvarchar2(30), Afdeling nvarchar2(30)); -- bijwerken van de velden ID en Naam (hier wordt MODIFY(...) gebruikt in plaats van ALTER COLUMN) ALTER TABLE Werknemers MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- PK toevoegen (in dit geval ziet de constructie eruit als in MS SQL, deze wordt hieronder weergegeven) ALTER TABLE Medewerkers ADD CONSTRAINT PK_Employees HOOFDSLEUTEL(ID KAART);
Voor ORACLE zijn er verschillen wat betreft de implementatie van het varchar2-type; de ​​codering ervan is afhankelijk van de database-instellingen en de tekst kan bijvoorbeeld worden opgeslagen in UTF-8-codering. Daarnaast kan de veldlengte in ORACLE zowel in bytes als in karakters worden opgegeven, hiervoor worden extra opties BYTE en CHAR gebruikt, die achter de veldlengte worden opgegeven, bijvoorbeeld:

NAAM varchar2(30 BYTE) -- de veldcapaciteit is 30 bytes NAAM varchar2(30 CHAR) -- de veldcapaciteit is 30 tekens
Welke optie standaard zal worden gebruikt BYTE of CHAR, in het geval van het simpelweg specificeren van het varchar2(30) type in ORACLE, hangt af van de database-instellingen, en kan soms worden ingesteld in de IDE-instellingen. Over het algemeen kun je soms gemakkelijk in de war raken, dus in het geval van ORACLE, als het type varchar2 wordt gebruikt (en dit is hier soms gerechtvaardigd, bijvoorbeeld bij gebruik van UTF-8-codering), geef ik er de voorkeur aan om expliciet CHAR te schrijven (aangezien het is meestal handiger om de lengte van de string in karakters te berekenen).

Maar in dit geval, als er al gegevens in de tabel staan, is het voor een succesvolle uitvoering van opdrachten noodzakelijk dat de velden ID en Naam in alle rijen van de tabel worden ingevuld. Laten we dit met een voorbeeld demonstreren: gegevens in de tabel invoeren in de velden ID, Positie en Afdeling; dit kan met het volgende script:

INSERT Medewerkers(ID,Functie,Afdeling) WAARDEN (1000,N"Directeur",N"Administratie"), (1001,N"Programmeur",N"IT"), (1002,N"Accountant",N"Accounting" ), (1003,N"Senior Programmeur",N"IT")
In dit geval genereert de opdracht INSERT ook een fout, omdat Bij het invoegen hebben we de waarde van het vereiste veld Naam niet opgegeven.
Als we deze gegevens al in de originele tabel hadden, zou het commando “ALTER TABLE Employees ALTER COLUMN ID int NOT NULL” met succes worden uitgevoerd, en zou het commando “ALTER TABLE Employees ALTER COLUMN Name int NOT NULL” een foutmelding opleveren, dat het veld Naam NULL (niet-gespecificeerde) waarden bevat.

Laten we waarden toevoegen voor het veld Naam en de gegevens opnieuw invullen:

De NOT NULL optie kan ook direct gebruikt worden bij het aanmaken van een nieuwe tabel, d.w.z. in de context van de opdracht CREATE TABLE.

Verwijder eerst de tabel met behulp van de opdracht:

DROP TABLE Medewerkers
Laten we nu een tabel maken met de vereiste kolommen ID en Naam:

TABEL MAKEN Werknemers(ID int NOT NULL, Naam nvarchar(30) NOT NULL, Verjaardagsdatum, E-mailadres nvarchar(30), Positie nvarchar(30), Afdeling nvarchar(30))
Je kunt ook NULL achter de kolomnaam schrijven, wat betekent dat NULL-waarden (niet gespecificeerd) erin zijn toegestaan, maar dit is niet nodig, aangezien dit kenmerk standaard wordt geïmpliceerd.

Als u daarentegen een bestaande kolom optioneel wilt maken, gebruik dan de volgende opdrachtsyntaxis:

ALTER TABEL Medewerkers ALTER KOLOM Naam nvarchar(30) NULL
Of gewoon:

ALTER TABEL Medewerkers ALTER KOLOM Naam nvarchar(30)
Met dit commando kunnen we ook het veldtype wijzigen in een ander compatibel type, of de lengte ervan wijzigen. Laten we het veld Naam bijvoorbeeld uitbreiden naar 50 tekens:

ALTER TABEL Medewerkers ALTER KOLOM Naam nvarchar(50)

Hoofdsleutel

U kunt een primaire sleutel voor een bestaande tabel maken met behulp van de opdracht:

ALTER TABEL Werknemers VOEG CONSTRAINT toe PK_Employees PRIMAIRE SLEUTEL(ID)
Waarbij "PK_Employees" de naam is van de beperking die verantwoordelijk is voor de primaire sleutel. Normaal gesproken wordt de primaire sleutel benoemd met het voorvoegsel “PK_”, gevolgd door de tabelnaam.

Als de primaire sleutel uit meerdere velden bestaat, moeten deze velden tussen haakjes worden vermeld, gescheiden door komma's:

ALTER TABLE tabelnaam ADD CONSTRAINT beperkingsnaam PRIMAIRE SLEUTEL(veld1,veld2,…)
Het is vermeldenswaard dat in MS SQL alle velden die in de primaire sleutel zijn opgenomen, het NOT NULL-kenmerk moeten hebben.

De primaire sleutel kan ook direct worden bepaald bij het maken van een tabel, d.w.z. in de context van de opdracht CREATE TABLE. Laten we de tabel verwijderen:

DROP TABLE Medewerkers
En dan maken we het met de volgende syntaxis:

MAAK TABEL Werknemers(ID int NOT NULL, Naam nvarchar(30) NOT NULL, Verjaardagsdatum, E-mail nvarchar(30), Positie nvarchar(30), Afdeling nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY(ID) -- beschrijf PK daarna alle velden als beperking)
Vul na het maken de tabel met gegevens:

INSERT Medewerkers(ID,Positie,Afdeling,Naam) WAARDEN (1000,N"Directeur",N"Administratie",N"Ivanov I.I."), (1001,N"Programmeur",N"IT",N" Petrov P.P." ), (1002,N"Accountant",N"Accounting",N"Sidorov S.S."), (1003,N"Senior Programmeur",N"IT",N"Andreev A.A."")
Als de primaire sleutel in een tabel alleen uit de waarden van één kolom bestaat, kunt u de volgende syntaxis gebruiken:

TABEL MAKEN Medewerkers(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- specificeer als kenmerk van het veld Naam nvarchar(30) NOT NULL, Verjaardagsdatum, E-mail nvarchar(30), Positie nvarchar(30), Afdeling nvarchar(30) )
In feite hoeft u de naam van de beperking niet op te geven; in dat geval krijgt deze een systeemnaam toegewezen (zoals “PK__Employee__3214EC278DA42077”):

TABEL MAKEN Werknemers(ID int NOT NULL, Naam nvarchar(30) NOT NULL, Verjaardagsdatum, E-mailadres nvarchar(30), Positie nvarchar(30), Afdeling nvarchar(30), PRIMAIRE SLEUTEL(ID))
Of:

TABEL MAKEN Werknemers(ID int NOT NULL PRIMAIRE SLEUTEL, Naam nvarchar(30) NOT NULL, Verjaardagsdatum, E-mailadres nvarchar(30), Positie nvarchar(30), Afdeling nvarchar(30))
Maar ik zou aanraden dat je voor permanente tabellen altijd expliciet de naam van de beperking instelt, omdat Met een expliciet gespecificeerde en begrijpelijke naam zal het gemakkelijker zijn om deze later te manipuleren; u kunt deze bijvoorbeeld verwijderen:

ALTER TABEL Werknemers DROP CONSTRAINT PK_Employees
Maar zo'n korte syntaxis, zonder de namen van de beperkingen op te geven, is handig om te gebruiken bij het maken van tijdelijke databasetabellen (de naam van de tijdelijke tabel begint met # of ##), die na gebruik worden verwijderd.

Laten we het samenvatten

• MAAK TABEL tabelnaam (lijst met velden en hun typen, beperkingen) – gebruikt om een ​​nieuwe tabel in de huidige database te maken;
• DRUPTAFEL tabelnaam – gebruikt om een ​​tabel uit de huidige database te verwijderen;
• WIJZIG TABEL tafel naam WIJZIG KOLOM column_name... – gebruikt om het kolomtype bij te werken of de instellingen ervan te wijzigen (bijvoorbeeld om het kenmerk NULL of NOT NULL in te stellen);
• WIJZIG TABEL tafel naam BEPERKING TOEVOEGEN beperkingsnaam HOOFDSLEUTEL(veld1, veld2,...) – een primaire sleutel toevoegen aan een bestaande tabel;
• WIJZIG TABEL tafel naam DALINGSBEPERKING constraint_name – verwijdert een beperking uit de tabel.

Een beetje over tijdelijke tabellen

Uittreksel uit MSDN. Er zijn twee soorten tijdelijke tabellen in MS SQL Server: lokaal (#) en globaal (##). Lokale tijdelijke tabellen zijn alleen zichtbaar voor de makers ervan totdat de verbindingssessie met het SQL Server-exemplaar eindigt wanneer ze voor het eerst worden gemaakt. Lokale tijdelijke tabellen worden automatisch verwijderd nadat een gebruiker de verbinding met het exemplaar van SQL Server heeft verbroken. Globale tijdelijke tabellen zijn zichtbaar voor alle gebruikers tijdens verbindingssessies nadat deze tabellen zijn gemaakt, en worden verwijderd wanneer alle gebruikers die naar die tabellen verwijzen, de verbinding met het exemplaar van SQL Server verbreken.

Om een ​​tijdelijke tabel te maken, kunt u de opdracht CREATE TABLE gebruiken:

MAAK TABEL #Temp(ID int, Naam nvarchar(30))
Omdat een tijdelijke tabel in MS SQL vergelijkbaar is met een gewone tabel, kan deze ook worden verwijderd met behulp van het DROP TABLE-commando:

DROP TAFEL #Temp

U kunt ook een tijdelijke tabel maken (zoals een gewone tabel) en deze onmiddellijk vullen met de gegevens die door de query worden geretourneerd met behulp van de SELECT ... INTO-syntaxis:

SELECTEER ID,Naam IN #Temp VAN Werknemers

Op een briefje
De implementatie van tijdelijke tabellen kan in verschillende DBMS'en verschillen. In de ORACLE en Firebird DBMS moet de structuur van tijdelijke tabellen bijvoorbeeld vooraf worden bepaald door de opdracht CREATE GLOBAL TEMPORARY TABLE, waarbij de details worden aangegeven van het opslaan van gegevens daarin, waarna de gebruiker deze tussen de hoofdtabellen ziet en ermee werkt. zoals bij een gewone tafel.

Databasenormalisatie – opsplitsen in subtabellen (directories) en verbindingen identificeren

Het tweede nadeel is het opslagvolume van deze informatie en de duplicatie ervan, d.w.z. Voor elke medewerker wordt de volledige naam van de afdeling aangegeven, waardoor er ruimte in de database nodig is om elk teken van de afdelingsnaam op te slaan.

Het derde nadeel is de moeilijkheid om deze velden bij te werken als de naam van een functie verandert, bijvoorbeeld als u de functie “Programmeur” moet hernoemen naar “Junior Programmeur”. In dit geval zullen we wijzigingen moeten aanbrengen in elke rij van de tabel waarvan de positie gelijk is aan "Programmeur".

Om deze tekortkomingen te voorkomen wordt gebruik gemaakt van zogenaamde databasenormalisatie, waarbij deze wordt opgesplitst in subtabellen en referentietabellen. Het is niet nodig om de jungle van theorieën in te gaan en te bestuderen wat ze zijn normale vormen, is het voldoende om de essentie van normalisatie te begrijpen.

Laten we 2 directorytabellen "Posities" en "Afdelingen" maken, laten we de eerste Posities en de tweede respectievelijk Afdelingen noemen:

MAAK TABEL Posities(ID int IDENTITEIT(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY, Naam nvarchar(30) NOT NULL) MAAK TABEL Afdelingen(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY, Naam nvarchar(30 ) NIET NUL)
Merk op dat we hier de nieuwe IDENTITEITsoptie hebben gebruikt, die zegt dat de gegevens in de ID-kolom automatisch worden genummerd, beginnend bij 1, in stappen van 1, d.w.z. Bij het toevoegen van nieuwe records krijgen deze opeenvolgend de waarden 1, 2, 3, etc. toegewezen. Dergelijke velden worden gewoonlijk automatisch ophogen genoemd. Voor een tabel kan slechts één veld gedefinieerd zijn met de IDENTITY-eigenschap, en meestal, maar niet noodzakelijkerwijs, is dat veld de primaire sleutel voor die tabel.

Op een briefje
In verschillende DBMS'en kan de implementatie van velden met een teller anders gebeuren. In MySQL wordt een dergelijk veld bijvoorbeeld gedefinieerd met de optie AUTO_INCREMENT. In ORACLE en Firebird kon deze functionaliteit voorheen worden geëmuleerd met behulp van SEQUENCE. Maar voor zover ik weet heeft ORACLE nu de optie GEGENEREERD ALS IDENTITEIT toegevoegd.

Laten we deze tabellen automatisch invullen, op basis van de huidige gegevens die zijn vastgelegd in de velden Positie en Afdeling van de tabel Werknemers:

We vullen het veld Naam van de tabel Posities met unieke waarden uit het veld Positie van de tabel Werknemers INSERT Positions(Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL - verwijder records waarvoor de positie niet is opgegeven
Laten we hetzelfde doen voor de tabel Afdelingen:

INSERT Departments(Name) SELECT DISTINCT Department FROM Werknemers WAAR Afdeling NIET NULL is
Als we nu de tabellen Posities en Afdelingen openen, zien we een genummerde reeks waarden voor het ID-veld:

SELECTEER * VAN Posities

SELECTEER * VAN Afdelingen

Deze tabellen zullen nu de rol spelen van naslagwerken voor het specificeren van functies en afdelingen. We zullen nu verwijzen naar functie- en afdelings-ID's. Laten we eerst nieuwe velden maken in de tabel Werknemers om identificatiegegevens op te slaan:

Voeg een veld toe voor positie-ID ALTER TABLE Medewerkers ADD PositionID int -- voeg een veld toe voor afdelings-ID ALTER TABLE Medewerkers ADD DepartmentID int
Het type referentievelden moet hetzelfde zijn als in mappen, in dit geval is het int.

U kunt ook meerdere velden tegelijk aan de tabel toevoegen met één opdracht, waarbij u de velden gescheiden door komma's opsomt:

ALTER TABEL Medewerkers TOEVOEGEN PositieID int, AfdelingID int
Laten we nu links (referentiebeperkingen - FOREIGN KEY) voor deze velden schrijven, zodat de gebruiker niet de mogelijkheid heeft om in deze velden waarden te schrijven die niet tot de ID-waarden in de mappen behoren.

ALTER TABEL Werknemers VOEG CONSTRAINT toe FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENTIES Posities(ID)
En we doen hetzelfde voor het tweede veld:

TABEL WIJZIGEN Medewerkers TOEVOEGEN CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(AfdelingsID) REFERENTIES Afdelingen(ID)
Nu kan de gebruiker in deze velden alleen ID-waarden uit de overeenkomstige map invoeren. Om een ​​nieuwe afdeling of functie te kunnen gebruiken, zal hij dus eerst een nieuw item aan de overeenkomstige directory moeten toevoegen. Omdat Functies en afdelingen worden nu in één enkele kopie in mappen opgeslagen, dus om de naam te wijzigen volstaat het om deze alleen in de map te wijzigen.

De naam van een referentiebeperking is doorgaans een samengestelde naam, bestaande uit het voorvoegsel "FK_", gevolgd door de tabelnaam en gevolgd door een onderstrepingsteken, gevolgd door de naam van het veld dat verwijst naar de referentietabel-ID.

Een identificatie (ID) is meestal een interne waarde die alleen voor relaties wordt gebruikt en welke waarde daar wordt opgeslagen, is in de meeste gevallen volkomen onverschillig, dus het is niet nodig om te proberen gaten in de reeks getallen weg te werken die tijdens het werken ontstaan met de tabel, bijvoorbeeld na het verwijderen van records uit de directory.

ALTER TABLE tabel ADD CONSTRAINT beperkingsnaam FOREIGN KEY(veld1,veld2,…) REFERENTIES referentie_tabel(veld1,veld2,…)
In dit geval wordt in de tabel “reference_table” de primaire sleutel weergegeven door een combinatie van verschillende velden (veld1, veld2,...).

Laten we nu de velden PositieID en AfdelingID bijwerken met ID-waarden uit de mappen. Laten we hiervoor de opdracht DML UPDATE gebruiken:

UPDATE e SET PositionID=(SELECT ID FROM Posities WHERE Name=e.Position), AfdelingID=(SELECT ID FROM Afdelingen WHERE Name=e.Afdeling) FROM Medewerkers e
Laten we eens kijken wat er gebeurt als u het verzoek uitvoert:

SELECTEER * VAN Medewerkers

Dat is alles, de velden PositieID en AfdelingID zijn gevuld met de identificatiegegevens die overeenkomen met posities en afdelingen; deze velden zijn niet langer nodig in de tabel Werknemers. U kunt deze velden verwijderen:

ALTER TABEL Medewerkers DROP COLUMN Positie,Afdeling
Nu ziet onze tabel er als volgt uit:

SELECTEER * VAN Medewerkers

SELECT e.ID,e.Name,p.Name Positienaam,d.Name Afdelingsnaam FROM Medewerkers e LEFT JOIN Afdelingen d ON d.ID=e.AfdelingID LEFT JOIN Posities p ON p.ID=e.PositionID

In de objectinspecteur kunnen we alle objecten zien die voor een bepaalde tabel zijn gemaakt. Vanaf hier kunt u verschillende manipulaties met deze objecten uitvoeren, bijvoorbeeld objecten hernoemen of verwijderen.

Het is ook vermeldenswaard dat de tabel naar zichzelf kan verwijzen, d.w.z. U kunt een recursieve koppeling maken. Laten we bijvoorbeeld nog een veld ManagerID toevoegen aan onze tabel met medewerkers, waarin wordt aangegeven aan welke medewerker hij rapporteert deze medewerker. Laten we een veld maken:

ALTER TABEL Medewerkers TOEVOEGEN ManagerID int
Dit veld staat een NULL-waarde toe; het veld zal leeg zijn als er bijvoorbeeld geen meerderen over de werknemer zijn.

Laten we nu een FOREIGN KEY maken voor de tabel Werknemers:

ALTER TABEL Werknemers VOEG CONSTRAINT toe FK_Employees_ManagerID FOREIGN SLEUTEL (ManagerID) REFERENTIES Werknemers(ID)
Laten we nu een diagram maken en kijken hoe de relaties tussen onze tabellen er uitzien:

Als resultaat zouden we het volgende beeld moeten zien (de tabel Werknemers is verbonden met de tabellen Posities en Afdelingen, en verwijst ook naar zichzelf):

Ten slotte is het de moeite waard om te zeggen dat referentiesleutels extra opties ON DELETE CASCADE en ON UPDATE CASCADE kunnen bevatten, die aangeven hoe zich te gedragen bij het verwijderen of bijwerken van een record waarnaar wordt verwezen in de referentietabel. Als deze opties niet zijn opgegeven, kunnen we de ID in de directorytabel niet wijzigen voor een record waarnaar wordt verwezen vanuit een andere tabel, en kunnen we zo'n record ook niet uit de directory verwijderen totdat we alle rijen hebben verwijderd die naar dit record verwijzen. of laten we de verwijzingen in deze regels bijwerken naar een andere waarde.

Laten we bijvoorbeeld de tabel opnieuw maken waarin de optie ON DELETE CASCADE wordt opgegeven voor FK_Employees_DepartmentID:

DROP TABLE Medewerkers CREATE TABLE Medewerkers(ID int NOT NULL, Naam nvarchar(30), Verjaardagsdatum, E-mail nvarchar(30), PositieID int, AfdelingsID int, ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(AfdelingsID) ) REFERENTIES Afdelingen(ID) BIJ DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Posities(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENTIES Werknemers(ID)) INSERT Werknemers (ID,Naam,Verjaardag,PositieID,AfdelingID,Man agerID )WAARDEN (1000,N"Ivanov I.I.","19550219",2,1,NULL), (1001,N"Petrov P.P.","19831203",3,3,1003), (1002 ,N"Sidorov S.S. ","19760607",1,2,1000), (1003,N"Andreev AA","19820417",4,3,1000)
Laten we de afdeling met ID 3 verwijderen uit de tabel Afdelingen:

VERWIJDER Afdelingen WAAR ID=3
Laten we eens kijken naar de gegevens in de tabel Werknemers:

SELECTEER * VAN Medewerkers

De optie ON UPDATE CASCADE gedraagt ​​zich op dezelfde manier, maar is effectief bij het bijwerken van de ID-waarde in de directory. Als we bijvoorbeeld de ID van een positie in de positiedirectory wijzigen, wordt in dit geval de AfdelingID in de tabel Werknemers bijgewerkt naar de nieuwe ID-waarde die we in de directory hebben ingesteld. Maar in dit geval zal het simpelweg niet mogelijk zijn om dit aan te tonen, omdat de ID-kolom in de Afdelingentabel heeft de IDENTITEIT-optie, waardoor we de volgende query niet kunnen uitvoeren (wijzig afdelings-ID 3 in 30):

UPDATE Afdelingen SET ID=30 WHERE ID=3
Het belangrijkste is om de essentie van deze 2 opties ON DELETE CASCADE en ON UPDATE CASCADE te begrijpen. Ik gebruik deze opties zeer zelden en raad u aan goed na te denken voordat u ze opgeeft in een referentiebeperking, omdat Als u per ongeluk een item uit een directorytabel verwijdert, kan dit tot grote problemen leiden en een kettingreactie veroorzaken.

Laten we afdeling 3 herstellen:

We geven toestemming om IDENTITY-waarde toe te voegen/wijzigen SET IDENTITY_INSERT Afdelingen AAN INSERT Departments(ID,Name) VALUES(3,N"IT") - we verbieden het toevoegen/wijzigen van IDENTITY-waarde SET IDENTITY_INSERT Afdelingen UIT
Laten we de tabel Werknemers volledig leegmaken met de opdracht TRUNCATE TABLE:

AFKORTE TABEL Medewerkers
En opnieuw zullen we de gegevens erin opnieuw laden met behulp van de vorige INSERT-opdracht:

INSERT Medewerkers (ID,Naam,Verjaardag,PositieID,AfdelingsID,ManagerID)VALUES (1000,N"Ivanov I.I.","19550219",2,1,NULL), (1001,N"Petrov P.P." ,"19831203",3 ,3,1003), (1002,N"Sidorov S.S.","19760607",1,2,1000), (1003,N"Andreev A.A.","19820417" ,4,3,1000)

Laten we het samenvatten

• Door de IDENTITY-eigenschap aan een veld toe te voegen – kunt u van dit veld een automatisch ingevuld veld (tellerveld) voor de tabel maken;
• WIJZIG TABEL tafel naam TOEVOEGEN list_of_fields_with_characteristics – hiermee kunt u nieuwe velden aan de tabel toevoegen;
• WIJZIG TABEL tafel naam DALEN KOLOM list_fields – hiermee kunt u velden uit de tabel verwijderen;
• WIJZIG TABEL tafel naam BEPERKING TOEVOEGEN beperkingsnaam VREEMDE SLEUTEL(velden) REFERENTIES table_reference (velden) – hiermee kunt u de relatie tussen de tabel en de referentietabel definiëren.

Andere beperkingen – UNIEK, STANDAARD, CONTROLEREN

UPDATE Medewerkers SET E-mail = " [e-mailadres beveiligd]" WHERE ID=1000 UPDATE Werknemers SET E-mail=" [e-mailadres beveiligd]" WHERE ID=1001 UPDATE Werknemers SET E-mail=" [e-mailadres beveiligd]" WHERE ID=1002 UPDATE Werknemers SET E-mail=" [e-mailadres beveiligd]"WAAR ID=1003
Nu kunt u een uniciteitsbeperking opleggen aan dit veld:

WIJZIG TABEL Werknemers VOEG CONSTRAINT TOE UQ_Employees_Email UNIQUE(Email)
Nu kan de gebruiker niet voor meerdere medewerkers hetzelfde e-mailadres invoeren.

Een unieke beperking wordt gewoonlijk als volgt genoemd: eerst komt het voorvoegsel “UQ_”, dan de naam van de tabel en na het onderstrepingsteken komt de naam van het veld waarop deze beperking wordt toegepast.

Als een combinatie van velden uniek moet zijn in de context van tabelrijen, dan vermelden we ze gescheiden door komma's:

ALTER TABLE tabelnaam ADD CONSTRAINT beperkingsnaam UNIQUE(veld1,veld2,…)
Door een DEFAULT-beperking aan een veld toe te voegen, kunnen we een standaardwaarde specificeren die zal worden vervangen als dit veld bij het invoegen van een nieuw record niet wordt vermeld in de lijst met velden van het INSERT-commando. Deze beperking kan direct worden ingesteld bij het maken van de tabel.

Laten we een nieuw veld 'Inhuurdatum' toevoegen aan de tabel Werknemers en dit HireDate noemen en zeggen dat de standaardwaarde voor dit veld is De huidige datum:

ALTER TABLE Werknemers ADD HireDate datum NOT NULL DEFAULT SYSDATETIME()
Of als de HireDate-kolom al bestaat, kan de volgende syntaxis worden gebruikt:

ALTER TABLE Werknemers VOEG STANDAARD SYSDATETIME() TOE VOOR HireDate
Hier heb ik de naam van de beperking niet gespecificeerd, omdat... in het geval van DEFAULT ben ik van mening dat dit niet zo cruciaal is. Maar als je het op een goede manier doet, dan denk ik dat je niet lui hoeft te zijn en een normale naam moet bedenken. Dit gebeurt als volgt:

WIJZIG TABEL Werknemers VOEG CONSTRAINT toe DF_Employees_HireDate STANDAARD SYSDATETIME() VOOR HireDate
Dat is hoe van deze kolom bestond nog niet eerder, en wanneer u deze aan elke record toevoegt, wordt de huidige datumwaarde ingevoegd in het veld HireDate.

Bij het toevoegen van een nieuw item wordt uiteraard ook de huidige datum automatisch ingevoegd, tenzij we deze expliciet instellen, d.w.z. We zullen dit niet aangeven in de lijst met kolommen. Laten we dit met een voorbeeld laten zien zonder het HireDate-veld op te geven in de lijst met toegevoegde waarden:

INSERT Medewerkers(ID,Naam,E-mail)VALUES(1004,N"Sergeev S.S."," [e-mailadres beveiligd]")
Laten we eens kijken wat er gebeurd is:

SELECTEER * VAN Medewerkers

WIJZIG TABEL Werknemers TOEVOEGEN BEPERKING CK_Employees_ID CHECK(ID TUSSEN 1000 EN 1999)
De beperking wordt meestal op dezelfde manier genoemd, eerst met het voorvoegsel “CK_”, vervolgens de naam van de tabel en de naam van het veld waaraan deze beperking wordt opgelegd.

Laten we proberen een ongeldig record in te voegen om te controleren of de beperking werkt (we zouden de bijbehorende foutmelding moeten krijgen):

INSERT Werknemers(ID,E-mail) WAARDEN(2000," [e-mailadres beveiligd]")
Laten we nu de ingevoegde waarde wijzigen in 1500 en ervoor zorgen dat de record wordt ingevoegd:

INSERT Werknemers(ID,E-mail) WAARDEN(1500," [e-mailadres beveiligd]")
U kunt ook UNIQUE- en CHECK-beperkingen maken zonder een naam op te geven:

WIJZIG TABEL Medewerkers VOEG UNIEK TOE (E-mail) WIJZIG TABEL Medewerkers VOEG CHEQUE TOE (ID TUSSEN 1000 EN 1999)
Maar dit is geen goede praktijk en het is beter om de naam van de beperking expliciet te specificeren, omdat Om er later achter te komen, wat moeilijker zal zijn, moet je het object openen en kijken waar het verantwoordelijk voor is.

Met een goede naam kan veel informatie over de beperking rechtstreeks uit de naam worden afgeleid.

En dienovereenkomstig kunnen al deze beperkingen onmiddellijk worden gecreëerd bij het maken van een tabel, als deze nog niet bestaat. Laten we de tabel verwijderen:

DROP TABLE Medewerkers
En we zullen het opnieuw creëren met alle gemaakte beperkingen met één CREATE TABLE-opdracht:

MAAK TABEL Werknemers(ID int NOT NULL, Naam nvarchar(30), Verjaardagsdatum, E-mail nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL DEFAULT SYSDATETIME(), -- voor DEFAULT maak ik een uitzondering CONSTRAINT PK_Employees PRIMAIRE SLEUTEL (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(AfdelingsID) REFERENTIES Afdelingen(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENTIES Posities(ID), CONSTRAINT UQ_Employees_Email UNIQUE (E-mail), CONSTRAINT CK_Employees_ID CHECK (ID TUSSEN EN 10 00 EN 1999) )

INSERT Medewerkers (ID,Naam,Verjaardag,E-mail,PositieID,AfdelingsID)VALUES (1000,N"Ivanov I.I.","19550219"," [e-mailadres beveiligd]",2,1), (1001,N"Petrov P.P.","19831203"," [e-mailadres beveiligd]",3,3), (1002,N"Sidorov S.S.","19760607"," [e-mailadres beveiligd]",1,2), (1003,N"Andreev A.A.","19820417"," [e-mailadres beveiligd]",4,3)

Iets over de indexen die worden gemaakt bij het maken van PRIMARY KEY- en UNIQUE-beperkingen

ALTER TABLE tabelnaam ADD CONSTRAINT beperkingsnaam PRIMARY KEY NONCLUSTERED(veld1,veld2,…)
Laten we bijvoorbeeld de beperkingsindex PK_Employees niet-geclusterd maken, en de beperkingsindex UQ_Employees_Email geclusterd. Laten we eerst deze beperkingen verwijderen:

ALTER TABLE Werknemers DROP CONSTRAINT PK_Employees ALTER TABLE Werknemers DROP CONSTRAINT UQ_Employees_Email
Laten we ze nu maken met de GECLUSTERDE en NIET-GECLUSTERDE opties:

ALTER TABLE Medewerkers ADD CONSTRAINT PK_Employees PRIMAIRE SLEUTEL NIET GECLUSTERD (ID) ALTER TABLE Medewerkers ADD CONSTRAINT UQ_Employees_Email UNIEK GCLUSTERED (E-mail)
Als we nu een keuze maken uit de tabel Werknemers, zullen we zien dat de records worden gesorteerd op de geclusterde index UQ_Employees_Email:

SELECTEER * VAN Medewerkers

Maar in dit geval is dit slechts een voorbeeld dat de essentie van een geclusterde index laat zien, omdat Hoogstwaarschijnlijk zullen er zoekopdrachten worden uitgevoerd naar de tabel Werknemers met behulp van het ID-veld en in sommige gevallen zal deze misschien zelf als een directory fungeren.

Voor mappen is het doorgaans raadzaam dat de geclusterde index op de primaire sleutel wordt gebouwd, omdat bij verzoeken verwijzen we vaak naar de directory-ID om bijvoorbeeld de naam (Functie, Afdeling) te verkrijgen. Laten we hier onthouden wat ik hierboven schreef, dat een geclusterde index directe toegang heeft tot tabelrijen, en hieruit volgt dat we de waarde van elke kolom kunnen krijgen zonder extra overhead.

Het is voordelig om een ​​clusterindex toe te passen op velden die het vaakst worden bemonsterd.

Soms worden tabellen gemaakt met een sleutel op basis van een surrogaatveld; in dit geval kan het handig zijn om de indexoptie GCLUSTERED op te slaan voor een geschiktere index en de optie NIET-GECLUSTERD te specificeren bij het maken van een surrogaat-primaire sleutel.

Laten we het samenvatten

• HOOFDSLEUTEL- hoofdsleutel;
• VREEMDE SLEUTEL– het opzetten van verbindingen en het bewaken van de referentiële integriteit van data;
• UNIEK– stelt je in staat uniekheid te creëren;
• REKENING– stelt u in staat de juistheid van de ingevoerde gegevens te garanderen;
• STANDAARD– hiermee kunt u een standaardwaarde instellen;
• Het is ook vermeldenswaard dat alle beperkingen kunnen worden verwijderd met het commando “ WIJZIG TABEL tafel naam DALINGSBEPERKING beperkingsnaam".

Zelfstandige indexen maken

Indexen op een veld of velden kunnen worden gemaakt met de volgende opdracht:

INDEX MAKEN IDX_Employees_Name AAN Werknemers(naam)
Ook hier kunt u de opties GCLUSTERED, NONCLUSTERED, UNIQUE opgeven, en u kunt ook de sorteerrichting van elk afzonderlijk veld ASC (standaard) of DESC opgeven:

MAAK EEN UNIEKE NIET-GECLUSTERDE INDEX UQ_Employees_EmailDesc ON Employees (E-mail DESC)
Bij het maken van een niet-geclusterde index kan de optie NONCLUSTERED worden weggelaten, omdat dit wordt standaard geïmpliceerd en wordt hier eenvoudigweg weergegeven om de positie van de optie CLUSTERED of NONCLUSTERED in de opdracht aan te geven.

U kunt de index verwijderen met het volgende commando:

DROP INDEX IDX_Employees_Name ON Werknemers
Eenvoudige indexen, maar ook beperkingen, kunnen worden gemaakt in de context van de opdracht CREATE TABLE.

Laten we de tabel bijvoorbeeld opnieuw verwijderen:

DROP TABLE Medewerkers
En we zullen het opnieuw maken met alle gemaakte beperkingen en indexen met één CREATE TABLE-opdracht:

TABEL MAKEN Medewerkers(ID int NOT NULL, Naam nvarchar(30), Verjaardagsdatum, E-mail nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(), ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(AfdelingsID) REFERENTIES Afdelingen(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositieID) REFERENTIES Posities(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENTIES Werknemers(ID), CONSTRAINT UQ_Emplod_E mail UNIEK(E-mail), BEPERKING CK_Employees_ID CHECK(ID TUSSEN 1000 EN 1999), INDEX IDX_Employees_Name(Naam))
Laten we tot slot onze medewerkers in de tabel plaatsen:

INSERT Medewerkers (ID,Naam,Verjaardag,E-mail,PositieID,AfdelingsID,ManagerID)VALUES (1000,N"Ivanov I.I.","19550219"," [e-mailadres beveiligd]",2,1,NULL), (1001,N"Petrov P.P.","19831203"," [e-mailadres beveiligd]",3,3,1003), (1002,N"Sidorov S.S.", "19760607", " [e-mailadres beveiligd]",1,2,1000), (1003,N"Andrejev A.A.","19820417"," [e-mailadres beveiligd]",4,3,1000)
Bovendien is het vermeldenswaard dat u waarden kunt opnemen in een niet-geclusterde index door ze op te geven in INCLUDE. Die. in dit geval zal de INCLUDE-index enigszins doen denken aan een geclusterde index, alleen nu is de index niet aan de tabel gekoppeld, maar zijn de benodigde waarden aan de index gekoppeld. Dienovereenkomstig kunnen dergelijke indexen de prestaties van selectiequery's (SELECT) aanzienlijk verbeteren; als alle genoemde velden in de index voorkomen, is toegang tot de tabel mogelijk helemaal niet nodig. Maar dit vergroot uiteraard de omvang van de index, omdat... de waarden van de vermelde velden worden gedupliceerd in de index.

Uittreksel uit MSDN. Algemene opdrachtsyntaxis voor het maken van indexen

MAAK [UNIEK] [GECLUSTEREERD | NIET-GECLUSTERED ] INDEX indexnaam AAN