Selectie van materialen voor de studie van databases en SQL. SQL leren

zelfstudie

Waar gaat deze les over?

Deze tutorial is zoiets als een "stempel van mijn geheugen" in de SQL-taal (DDL, DML), d.w.z. dit is informatie die zich tijdens mijn professionele activiteiten heeft verzameld en voortdurend in mijn hoofd wordt opgeslagen. Dit is voor mij een voldoende minimum, dat het vaakst wordt gebruikt bij het werken met databases. Als het nodig is om completere SQL-constructies te gebruiken, wend ik me meestal tot de MSDN-bibliotheek op internet voor hulp. Alles in je hoofd houden is naar mijn mening erg moeilijk, en daar is ook geen specifieke behoefte aan. Maar het kennen van de basisconstructies is erg handig, omdat. ze zijn bijna in dezelfde vorm toepasbaar in veel relationele databases zoals Oracle, MySQL, Firebird. De verschillen zitten vooral in de datatypes, die in details kunnen verschillen. Er zijn niet zo veel elementaire SQL-taalconstructies, en met constante oefening worden ze snel onthouden. Om bijvoorbeeld objecten (tabellen, beperkingen, indexen, enz.) te maken, is het voldoende om een teksteditor van de omgeving (IDE) bij de hand te hebben om met een database te werken, en het is niet nodig om een visuele toolkit te leren die is aangescherpt voor werken met een specifiek type database (MS SQL, Oracle, MySQL, Firebird, …). Dit is ook handig omdat je de hele tekst voor ogen hebt en je niet talloze tabbladen hoeft te doorlopen om bijvoorbeeld een index of een restrictie aan te maken. Wanneer u constant met de database werkt, gaat het maken, wijzigen en vooral opnieuw maken van een object met behulp van scripts vele malen sneller dan wanneer het in de visuele modus wordt gedaan. Ook in scriptmodus (respectievelijk met de nodige zorgvuldigheid) is het gemakkelijker om de regels voor het benoemen van objecten in te stellen en te controleren (mijn subjectieve mening). Daarnaast zijn scripts handig in gebruik wanneer wijzigingen in de ene database (bijvoorbeeld een testdatabase) in dezelfde vorm naar een andere database moeten worden overgebracht (productief).

De SQL-taal is verdeeld in verschillende delen, hier zal ik de 2 belangrijkste delen ervan bespreken:

DML - Data Manipulation Language (taal voor gegevensmanipulatie), die de volgende constructies bevat:
- SELECT - gegevensselectie
- INSERT - nieuwe gegevens invoegen
- UPDATE - gegevensupdate
- VERWIJDEREN - gegevens verwijderen
- SAMENVOEGEN - gegevens samenvoegen

Omdat Ik ben een beoefenaar, daarom zal er weinig theorie in dit leerboek staan en zullen alle constructies worden uitgelegd met praktische voorbeelden. Daarnaast geloof ik dat een programmeertaal, en met name SQL, alleen in de praktijk te beheersen is, door hem zelf aan te raken en te begrijpen wat er gebeurt als je deze of gene constructie uitvoert.

Deze tutorial is gemaakt volgens het principe van Step by Step, d.w.z. het is noodzakelijk om het achtereenvolgens te lezen en bij voorkeur onmiddellijk na de voorbeelden. Maar als u onderweg meer over een opdracht wilt weten, gebruik dan een specifieke zoekopdracht op internet, bijvoorbeeld in de MSDN-bibliotheek.

Bij het schrijven van deze tutorial gebruikte ik een database van MS SQL Server versie 2014 en ik gebruikte MS SQL Server Management Studio (SSMS) om de scripts uit te voeren.

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 objecten en serverinstellingen. Het belangrijkste hulpmiddel van SQL Server Management Studio is de Object Explorer, waarmee de gebruiker serverobjecten kan bekijken, ophalen en beheren. Deze tekst is deels ontleend aan Wikipedia.

Gebruik de knop Nieuwe query om een nieuwe scripteditor te maken:

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

Om een specifieke opdracht (of een groep opdrachten) uit te voeren, selecteert u deze en drukt u op de knop Uitvoeren of de F5-toets. Als er op dit moment maar één commando in de editor is, of als je alle commando's moet uitvoeren, dan hoef je niets te selecteren.

Na het uitvoeren van scripts, vooral degene die objecten maken (tabellen, kolommen, indexen), om de wijzigingen te zien, gebruikt u Vernieuwen in het contextmenu, waarbij u de juiste groep (bijvoorbeeld Tabellen), de tabel zelf of de groep Kolommen daarin markeert .

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

Een beetje theorie

Een relationele database (RDB, of verder in de context van gewoon een database) is een verzameling onderling verbonden tabellen. Een database is grofweg een bestand waarin gegevens gestructureerd zijn opgeslagen.

DBMS - het systeem voor het beheren van deze databases, d.w.z. dit is een set tools voor het werken met een specifiek type database (MS SQL, Oracle, MySQL, Firebird, ...).

Opmerking
Omdat in het leven, in de omgangstaal, zeggen we meestal: "Oracle DB", of zelfs gewoon "Oracle", wat eigenlijk "Oracle DBMS" betekent, dan zal in de context van deze tutorial de term DB soms worden gebruikt. Uit de context denk ik dat het duidelijk zal zijn wat er precies op het spel staat.

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 regel voor regel in de kolommen van de tabel opgeslagen. Regels, records zijn ook synoniemen.

Voor elke tabel, evenals voor de kolommen, worden namen gegeven, waarmee vervolgens wordt verwezen.
De objectnaam (tabelnaam, kolomnaam, indexnaam, etc.) in MS SQL mag maximaal 128 tekens lang zijn.

Als referentie– in de ORACLE database mogen objectnamen maximaal 30 karakters lang zijn. Daarom moet u voor een bepaalde database uw eigen regels ontwikkelen voor het benoemen van objecten om aan de limiet van het aantal tekens te voldoen.

SQL is een taal waarmee u de database kunt doorzoeken via het DBMS. In een bepaald 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 aan te maken 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-taal kunt u 2 soorten opmerkingen gebruiken (enkelregelig en meerregelig):

Enkele regel opmerking
En

/* commentaar met meerdere regels */

Eigenlijk zal alles voor de theorie hiervan voldoende zijn.

DDL - Data Definition Language (taal voor gegevensbeschrijving)

Denk bijvoorbeeld aan een tabel met gegevens over werknemers, in de vorm die bekend is bij een persoon die geen programmeur is:

In dit geval hebben de tabelkolommen de volgende namen: Personeelsnummer, Volledige naam, Geboortedatum, E-mail, Functie, Afdeling.

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

Personeelsnummer - geheel getal
volledige naam - string
Geboortedatum - datum
E-mail - tekenreeks
Positie - tekenreeks
afdeling - string

Kolomtype is een kenmerk dat aangeeft wat voor soort gegevens deze kolom kan opslaan.

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

Betekenis	Notatie in MS SQL	Beschrijving
String met variabele lengte	varchar(N) En nvarchar(N)	Met het getal N kunnen we de maximaal mogelijke stringlengte voor de corresponderende kolom specificeren. Als we bijvoorbeeld willen zeggen dat de waarde van de kolom "Naam" maximaal 30 tekens kan bevatten, moeten we het type instellen op nvarchar (30). Het verschil tussen varchar en nvarchar is dat je met varchar strings kunt opslaan in ASCII-formaat, waarbij één teken 1 byte in beslag neemt, terwijl nvarchar strings opslaat in Unicode-formaat, waarbij elk teken 2 bytes in beslag neemt. Het varchar-type mag alleen worden gebruikt als u er 100% zeker van bent dat het veld geen Unicode-tekens hoeft op te slaan. Varchar kan bijvoorbeeld worden gebruikt om e-mailadressen op te slaan omdat: ze bevatten meestal alleen ASCII-tekens.
String met vaste lengte	char (N) En nchar (N)	Dit type verschilt van een string met variabele lengte doordat als de lengte van de string kleiner is dan N tekens, deze altijd naar rechts wordt opgevuld tot de lengte van N spaties en in deze vorm in de database wordt opgeslagen, d.w.z. in de database neemt het precies N karakters in beslag (waarbij één karakter 1 byte inneemt voor char en 2 bytes voor nchar). In mijn praktijk wordt dit type zeer zelden gebruikt, en als het wordt gebruikt, wordt het voornamelijk gebruikt in het char (1)-formaat, d.w.z. wanneer het veld wordt gedefinieerd door een enkel teken.
Geheel getal	int	Met dit type kunnen we alleen gehele getallen, zowel positief als negatief, in de kolom gebruiken. Ter referentie (dit is nu niet zo relevant voor ons) - het bereik van getallen dat het int-type toestaat van -2 147 483 648 tot 2 147 483 647. Dit is meestal het hoofdtype dat wordt gebruikt om identifiers in te stellen.
Echt of echt getal	vlot	Simpel gezegd zijn dit getallen waarin een decimaalteken (komma) kan voorkomen.
datum	datum	Als de kolom alleen de Datum hoeft op te slaan, die uit drie componenten bestaat: Getal, Maand en Jaar. Bijvoorbeeld 15-02-2014 (15 februari 2014). Dit type kan worden gebruikt voor de kolom "Toelatingsdatum", "Geboortedatum", enz., d.w.z. in gevallen waarin het voor ons belangrijk is om alleen de datum vast te leggen, of wanneer de tijdcomponent niet belangrijk voor ons is en kan worden weggegooid of als deze niet bekend is.
Tijd	tijd	Dit type kan worden gebruikt als de kolom alleen tijdgegevens hoeft op te slaan, d.w.z. Uren, minuten, seconden en milliseconden. Bijvoorbeeld 17:38:31.3231603 Bijvoorbeeld de dagelijkse "Vertrektijd van de vlucht".
datum en tijd	datum Tijd	Met dit type kunt u zowel Datum als Tijd tegelijkertijd opslaan. Bijvoorbeeld 15-02-2014 17:38:31.323 PM Dit kan bijvoorbeeld de datum en tijd van een evenement zijn.
Vlag	beetje	Dit type is handig voor het opslaan van Ja/Nee-waarden, waarbij Ja wordt opgeslagen als 1 en Nee wordt opgeslagen als 0.

Ook mag de waarde van het veld, in het geval dat het niet verboden is, niet worden gespecificeerd, hiervoor wordt het NULL-sleutelwoord gebruikt.

Laten we, om de voorbeelden uit te voeren, een testdatabase maken met de naam Test.

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

MAAK DATABASE-test
U kunt de database verwijderen met het commando (je moet heel voorzichtig zijn met dit commando):

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

Amerikaanse test
U kunt ook de Testdatabase selecteren in de vervolgkeuzelijst in het SSMS-menugebied. Op mijn werk 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:

MAAK TABEL [Werknemers]([Personeelsnummer] int, [Naam] nvarchar(30), [Geboortedatum] datum, nvarchar(30), [Positie] nvarchar(30), [Afdeling] nvarchar(30))
In dit geval moeten we de namen tussen vierkante haken plaatsen […].

Maar in de database is het voor meer gemak beter om alle namen van objecten in het Latijn te specificeren en geen spaties in de namen te gebruiken. In MS SQL, meestal in dit geval, begint elk woord met een hoofdletter, bijvoorbeeld voor het veld "Personeelsnummer" zouden we de naam PersonnelNumber kunnen instellen. U kunt ook cijfers in de naam gebruiken, bijvoorbeeld Telefoonnummer1.

op een notitie
In sommige DBMS kan het volgende formaat van namen "PHONE_NUMBER" de voorkeur hebben, dit formaat wordt bijvoorbeeld vaak gebruikt in de ORACLE-database. Uiteraard is het bij het instellen van de veldnaam wenselijk dat deze niet overeenkomt met de trefwoorden die in het DBMS worden gebruikt.

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

DROP TABEL [Medewerkers]
Een tabel met werknemers kan bijvoorbeeld "Werknemers" worden genoemd en de velden kunnen de volgende namen krijgen:

ID - Personeelsnummer (Werknemers-ID)
Naam - volledige naam
Verjaardag - Geboortedatum
E-mail
Positie
Afdeling - Afdeling

Heel vaak wordt het woord ID gebruikt om het identificatieveld een naam te geven.

Laten we nu onze tabel maken:

MAAK TABEL Werknemers(ID int, Naam nvarchar(30), Verjaardagsdatum, E-mail nvarchar(30), Functie nvarchar(30), Afdeling nvarchar(30))
U kunt de optie NOT NULL gebruiken om de vereiste kolommen op te geven.

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

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

op een notitie
Het algemene concept van de SQL-taal voor de meeste DBMS blijft hetzelfde (tenminste, ik kan dit beoordelen aan de hand van die DBMS waarmee ik de kans heb gehad om te werken). Het verschil tussen DDL in verschillende DBMS'en zit vooral in datatypes (niet alleen hun namen kunnen hier verschillen, maar ook de details van hun implementatie), de specifieke kenmerken van de implementatie van de SQL-taal kunnen ook enigszins verschillen (dwz de essentie van de commando's is hetzelfde, maar er kunnen kleine verschillen zijn in het dialect, helaas, maar er is geen standaard). Als u de basisprincipes van SQL kent, kunt u gemakkelijk van het ene DBMS naar het andere overschakelen. 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 is het voldoende om een analogie te trekken.
Een tabel maken CREATE TABLE Employees(ID int, -- in ORACLE type int is het equivalent (wrapper) voor nummer (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)); -- ID- en naamvelden bijwerken (hier wordt MODIFY(...) gebruikt in plaats van ALTER COLUMN ALTER TABLE Werknemers MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- het toevoegen van een PK (in dit geval ziet de constructie eruit als in MS SQL, deze wordt hieronder getoond) WIJZIG TABEL Werknemers ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
Voor ORACLE zijn er verschillen in de implementatie van het varchar2-type, de codering is afhankelijk van de database-instellingen en de tekst kan worden opgeslagen, bijvoorbeeld in UTF-8-codering. Daarnaast kan de veldlengte in ORACLE zowel in bytes als in karakters worden ingesteld, hiervoor worden extra opties BYTE en CHAR gebruikt, die na de veldlengte worden opgegeven, bijvoorbeeld:
NAAM varchar2 (30 BYTE) -- veldcapaciteit zal 30 bytes zijn NAAM varchar2 (30 CHAR) -- veldcapaciteit zal 30 tekens zijn
Welke optie standaard wordt gebruikt BYTE of CHAR, in het geval van een eenvoudige specificatie van het type varchar2(30) in ORACLE, hangt af van de database-instellingen, het kan soms ook 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 het gebruik van UTF-8-codering), geef ik er de voorkeur aan expliciet CHAR te schrijven (omdat het is meestal handiger om de lengte van een string in karakters te lezen).

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

INSERT Employees(ID,Position,Afdeling) VALUES (1000,N"Director",N"Administration"), (1001,N"Programmer",N"IT"), (1002,N"Accountant",N"Accounting" ), (1003,N"Senior programmeur",N"IT")
In dit geval zal het INSERT-commando ook een foutmelding geven, 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, dan zou het commando "ALTER TABLE Employees ALTER COLUMN ID int NOT NULL" met succes zijn uitgevoerd, en het commando "ALTER TABLE Employees ALTER COLUMN Name int NOT NULL" zou een foutmelding hebben gegeven bericht dat er NULL-waarden (niet gespecificeerd) in het veld Naam staan.

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

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

Verwijder eerst de tabel met het commando:

DROP TAFEL Medewerkers
Laten we nu een tabel maken met verplichte kolommen ID en Naam:

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

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

WIJZIG TABEL Werknemers WIJZIG KOLOM Naam nvarchar(30) NULL
Of gewoon:

WIJZIG TABEL Werknemers WIJZIG KOLOM Naam nvarchar(30)
Met deze opdracht kunnen we ook het veldtype wijzigen in een ander compatibel type of de lengte ervan wijzigen. Laten we bijvoorbeeld het veld Naam uitbreiden tot 50 tekens:

WIJZIG TABEL Werknemers WIJZIG KOLOM Naam nvarchar(50)

hoofdsleutel

Bij het maken van een tabel is het wenselijk dat deze een unieke kolom heeft of een set kolommen die uniek is voor elk van zijn rijen - een record kan uniek worden geïdentificeerd aan de hand van deze unieke waarde. Deze waarde wordt de primaire sleutel van de tabel genoemd. Voor onze tabel Werknemers kan deze unieke waarde de ID-kolom zijn (die "Personeelsnummer werknemer" bevat - zelfs als in ons geval deze waarde uniek is voor elke werknemer en niet kan worden herhaald).

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

WIJZIG TABEL Werknemers ADD CONSTRAINT PK_Employees PRIMAIRE SLEUTEL(ID)
Waarbij "PK_Employees" de naam is van de beperking die verantwoordelijk is voor de primaire sleutel. Gewoonlijk wordt de primaire sleutel genoemd met het voorvoegsel "PK_" gevolgd door de tabelnaam.

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

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

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

DROP TAFEL Medewerkers
En maak het vervolgens 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 na alle velden als beperking)
Vul na het aanmaken de tabelgegevens in:

INSERT Employees(ID,Position,Department,Name) VALUES (1000,N"Director",N"Administration",N"Ivanov II.), (1001,N"Programmer",N"IT",N" Petrov PP" ), (1002,N"Accountant",N"Accounting",N"Sidorov SS"), (1003,N"Senior Programmer",N"IT",N"Andreev A. MAAR.")
Als de primaire sleutel in de tabel alleen uit de waarden van één kolom bestaat, kan de volgende syntaxis worden gebruikt:

MAAK TABEL Werknemers(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 kan de naam van de beperking worden weggelaten, in welk geval deze een systeemnaam krijgt (zoals "PK__Employee__3214EC278DA42077"):

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

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

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

Laten we samenvatten

Tot nu toe hebben we de volgende opdrachten behandeld:

MAAK TAFEL tabelnaam (opsomming van velden en hun typen, beperkingen) - gebruikt om een nieuwe tabel in de huidige database te maken;
DROP TAFEL table_name - gebruikt om een tabel uit de huidige database te verwijderen;
WIJZIG TABEL tafel naam WIJZIG KOLOM column_name … – wordt gebruikt om het kolomtype bij te werken of om de instellingen ervan te wijzigen (bijvoorbeeld om het NULL- of NOT NULL-kenmerk in te stellen);
WIJZIG TABEL tafel naam BEPERKING TOEVOEGEN constraint_name HOOFDSLEUTEL(field1, field2,…) – het toevoegen van een primaire sleutel aan een bestaande tabel;
WIJZIG TABEL tafel naam DROP-BEPERKING constraint_name - verwijder de beperking uit de tabel.

Een beetje over tijdelijke tafels

Knippen van MSDN. Er zijn twee soorten tijdelijke tabellen in MS SQL Server: lokaal (#) en globaal (##). Lokale tijdelijke tabellen zijn alleen zichtbaar voor hun makers totdat de verbindingssessie met het SQL Server-exemplaar wordt beëindigd zodra ze voor het eerst zijn gemaakt. Lokale tijdelijke tabellen worden automatisch verwijderd nadat een gebruiker de verbinding met een 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 deze tabellen verwijzen, de verbinding met het exemplaar van SQL Server verbreken.

Tijdelijke tabellen worden aangemaakt in de tempdb-systeemdatabase, d.w.z. als we ze maken, verstoppen we de hoofddatabase niet, anders zijn tijdelijke tabellen volledig identiek aan gewone tabellen, ze kunnen ook worden verwijderd met de opdracht DROP TABLE. Lokale (#) tijdelijke tabellen worden vaker gebruikt.

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

MAAK TABEL #Temp(ID int, Naam nvarchar(30))
Aangezien een tijdelijke tabel in MS SQL vergelijkbaar is met een gewone tabel, kunt u deze ook dienovereenkomstig verwijderen met de opdracht DROP TABLE:

DROP TABEL #Temp

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

SELECT ID,Name INTO #Temp FROM Employees

op een notitie
In verschillende DBMS kan de implementatie van tijdelijke tabellen verschillen. In de ORACLE- en Firebird-DBMS moet de structuur van tijdelijke tabellen bijvoorbeeld vooraf worden gedefinieerd door de opdracht CREATE GLOBAL TEMPORARY TABLE, waarbij de details van het opslaan van gegevens erin worden aangegeven, waarna de gebruiker deze tussen de hoofdtabellen ziet en ermee werkt zoals bij een gewone tafel.

Normalisatie van de database - opsplitsen in subtabellen (directories) en bepalen van relaties

Onze huidige medewerkerstabel heeft het nadeel dat de gebruiker in de velden Functie en Afdeling elke tekst kan invoeren, wat vooral veel fouten bevat, aangezien hij voor de ene medewerker gewoon "IT" als afdeling kan aangeven, en voor de tweede medewerker voor voer bijvoorbeeld "IT-afdeling" in, heb de derde "IT". Hierdoor zal niet duidelijk zijn wat de gebruiker bedoelde, d.w.z. Zijn deze medewerkers medewerkers van dezelfde afdeling, of heeft de gebruiker zichzelf beschreven en zijn dit 3 verschillende afdelingen? En meer nog, in dit geval kunnen we de gegevens voor een rapport niet correct groeperen, waar het nodig kan zijn om het aantal werknemers in de context van elke afdeling weer te geven.

Het tweede nadeel is de hoeveelheid opslag van deze informatie en de duplicatie ervan, d.w.z. voor elke medewerker wordt de volledige naam van de afdeling aangegeven, wat een plaats in de database vereist om elk teken uit de naam van de afdeling op te slaan.

Het derde nadeel is de moeilijkheid om deze velden bij te werken als de naam van een positie verandert, bijvoorbeeld als je de positie "Programmer" moet hernoemen naar "Junior programmeur". In dit geval moeten we wijzigingen aanbrengen in elke regel van de tabel, waarin de Positie gelijk is aan "Programmer".

Om deze tekortkomingen te voorkomen, wordt de zogenaamde normalisatie van de database gebruikt - deze wordt opgesplitst in subtabellen, referentietabellen. Het is niet nodig om in de jungle van theorie te klimmen en te bestuderen wat normale vormen zijn, het is voldoende om de essentie van normalisatie te begrijpen.

Laten we 2 referentietabellen "Posities" en "Afdelingen" maken, de eerste heet Posities en de tweede respectievelijk Afdelingen:

CREATE TABLE Positions(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions PRIMARY KEY, Name nvarchar(30) NOT NULL) CREATE TABLE Departments(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments PRIMARY KEY, Name nvarchar(30 ) NIET NUL)
Merk op dat we hier de nieuwe IDENTITY-optie hebben gebruikt, wat betekent dat de gegevens in de ID-kolom automatisch worden genummerd, beginnend bij 1, met een stap van 1, d.w.z. wanneer nieuwe records worden toegevoegd, krijgen deze achtereenvolgens de waarden 1, 2, 3, enzovoort. Dergelijke velden worden meestal auto-incrementing genoemd. Er kan slechts één veld met de eigenschap IDENTITY in een tabel worden gedefinieerd, en gewoonlijk, maar niet noodzakelijk, is zo'n veld de primaire sleutel voor die tabel.

op een notitie
In verschillende DBMS kan de implementatie van velden met een teller anders worden gedaan. In MySQL wordt zo'n veld bijvoorbeeld gedefinieerd met de optie AUTO_INCREMENT. In ORACLE en Firebird kon deze functionaliteit voorheen worden geëmuleerd met behulp van SEQUENCEs. Maar voor zover ik weet, heeft ORACLE nu de optie GEGENEREERD ALS IDENTITEIT toegevoegd.

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

Vul het veld Naam van de tabel Posities in 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 zonder opgegeven positie
We zullen hetzelfde doen voor de tabel Afdelingen:

INSERT Afdelingen(Naam) SELECTEER AFDELING UIT Medewerkers WAAR Afdeling NIET NULL is
Als we nu de tabellen Posities en Afdelingen openen, zien we een genummerde set waarden bij het ID-veld:

KIES * UIT Posities

KIES * UIT Afdelingen

Deze tabellen zullen nu de rol spelen van mappen voor het instellen van posities en afdelingen. We zullen nu verwijzen naar taak- en afdelings-ID's. Laten we eerst nieuwe velden maken in de tabel Werknemers om de ID-gegevens op te slaan:

Veld toevoegen voor positie-ID WIJZIG TABEL Werknemers ADD PositionID int -- voeg veld toe voor afdelings-ID WIJZIG TABEL Werknemers ADD Afdelings-ID int
Het type referentievelden moet hetzelfde zijn als in de directory's, in dit geval is het int.

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

WIJZIG TABEL Werknemers TOEVOEGEN PositionID int, DepartmentID int
Laten we nu links schrijven (referentiebeperkingen - BUITENLANDSE SLEUTEL) voor deze velden, zodat de gebruiker niet de mogelijkheid heeft om in deze velden te schrijven, waarden die niet tot de ID-waarden in de mappen behoren.

ALTER TABLE Werknemers ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Posities(ID)
En we zullen hetzelfde doen voor het tweede veld:

ALTER TABLE Werknemers ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENTIES Afdelingen(ID)
Nu kan de gebruiker alleen ID-waarden uit het bijbehorende naslagwerk in deze velden invoeren. Om een nieuwe afdeling of functie te kunnen gebruiken, moet hij dus eerst een nieuwe vermelding toevoegen aan de overeenkomstige directory. Omdat posities en afdelingen worden nu in een enkele kopie in mappen opgeslagen, en om de naam te wijzigen, volstaat het om deze alleen in de map te wijzigen.

De naam van een referentiële beperking is meestal samengesteld, het bestaat uit het voorvoegsel "FK_", gevolgd door de tabelnaam, en na het onderstrepingsteken komt de naam van het veld dat verwijst naar de identifier van de opzoektabel.

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

ALTER TABLE tabel ADD CONSTRAINT constraint_name FOREIGN KEY(field1,field2,...) REFERENCES opzoektabel(field1,field2,...)
In dit geval wordt in de tabel "table_reference" de primaire sleutel weergegeven door een combinatie van verschillende velden (field1, field2, ...).

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

UPDATE e SET PositionID=(SELECT ID FROM Positions WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FROM Medewerkers e
Laten we eens kijken wat er gebeurt door de query uit te voeren:

SELECTEER * VAN Medewerkers

Dat is alles, de velden PositieID en AfdelingsID worden nu gevuld met de corresponderende functies en afdelingen met benodigde ID's in de velden Positie en Afdeling in de tabel Medewerkers, u kunt deze velden verwijderen:

WIJZIG TABEL Werknemers DROP COLUMN Positie,Afdeling
De tabel ziet er nu als volgt uit:

SELECTEER * VAN Medewerkers

ID kaart	Naam	verjaardag	E-mail	Positie-ID	Afdelings-ID
1000	Ivanov II	NUL	NUL	2	1
1001	Petrov PP	NUL	NUL	3	3
1002	Sidorov SS	NUL	NUL	1	2
1003	Andreev AA	NUL	NUL	4	3

Die. we zijn uiteindelijk verlost van het opslaan van overtollige informatie. Nu kunnen we aan de hand van de positie- en afdelingsnummers hun namen op unieke wijze bepalen met behulp van de waarden in de opzoektabellen:

SELECT e.ID,e.Name,p.Name PositionName,d.Name Afdelingsnaam FROM Medewerkers e LEFT JOIN Departments d ON d.ID=e.DepartmentID LEFT JOIN Posities p ON p.ID=e.PositionID

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

Het is ook vermeldenswaard dat een tabel naar zichzelf kan verwijzen, d.w.z. u kunt een recursieve link maken. Laten we bijvoorbeeld nog een veld ManagerID toevoegen aan onze tabel met medewerkers, die de medewerker aangeeft aan wie deze medewerker rapporteert. Laten we een veld maken:

WIJZIG TABEL Werknemers VOEG ManagerID toe int
De NULL-waarde is toegestaan in dit veld, het veld zal leeg zijn als er bijvoorbeeld geen meerderen zijn over de werknemer.

Laten we nu een BUITENLANDSE SLEUTEL maken in de tabel Werknemers:

ALTER TABLE Werknemers ADD CONSTRAINT FK_Employees_ManagerID BUITENLANDSE SLEUTEL (ManagerID) REFERENTIES Werknemers (ID)
Laten we nu een diagram maken en kijken hoe de relaties tussen onze tabellen erop staan:

Als resultaat zouden we de volgende afbeelding moeten zien (de tabel Werknemers is gerelateerd aan de tabellen Posities en Afdelingen, en verwijst ook naar zichzelf):

Ten slotte is het vermeldenswaard dat referentiesleutels extra opties kunnen bevatten ON DELETE CASCADE en ON UPDATE CASCADE, die vertellen hoe te handelen bij het verwijderen of bijwerken van een record waarnaar in de opzoektabel wordt verwezen. Als deze opties niet zijn gespecificeerd, kunnen we de ID in de directorytabel van het item dat links uit een andere tabel heeft niet wijzigen, noch kunnen we een dergelijk item uit de directory verwijderen totdat we alle rijen hebben verwijderd die naar dit item verwijzen of, laten we update deze referentieregels naar een andere waarde.

Laten we bijvoorbeeld de tabel opnieuw maken met de ON DELETE CASCADE-optie voor FK_Employees_DepartmentID:

DROP TABEL Werknemers MAAK TABEL Werknemers(ID int NOT NULL, Naam nvarchar(30), Verjaardagsdatum, E-mail nvarchar(30), Positie-ID int, Afdelings-ID int, ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID FOREIGN KEY(DepartmentID FOREIGN KEY(DepartmentID FOREIGN KEY) ) REFERENTIES Afdelingen(ID) ON DELETE CASCADE, CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY (ManagerID) REFERENTIES Werknemers(ID)ID,ManagerID, INSERT Werknemers (ID) )WAARDEN (1000,N"Ivanov II","19550219",2,1,NULL), (1001,N"Petrov PP","19831203",3,3,1003), (1002 ,N"Sidorov SS" ,"19760607",1,2,1000), (1003,N"Andreev AA","19820417",4,3,1000)
Laten we de afdeling met ID 3 uit de tabel Afdelingen verwijderen:

DELETE Afdelingen WHERE ID=3
Laten we eens kijken naar de gegevens in de tabel Werknemers:

SELECTEER * VAN Medewerkers

ID kaart	Naam	verjaardag	E-mail	Positie-ID	Afdelings-ID	Manager-ID
1000	Ivanov II	1955-02-19	NUL	2	1	NUL
1002	Sidorov SS	1976-06-07	NUL	1	2	1000

Zoals u kunt zien, zijn de gegevens voor afdeling 3 ook verwijderd uit de tabel Medewerkers.

De ON UPDATE CASCADE-optie gedraagt zich op dezelfde manier, maar wordt van kracht wanneer de ID-waarde in de directory wordt bijgewerkt. Als we bijvoorbeeld de positie-ID in de functiedirectory wijzigen, wordt in dit geval de afdelings-ID in de tabel Werknemers bijgewerkt naar de nieuwe ID-waarde die we in de directory hebben ingesteld. Maar in dit geval zal het gewoon niet mogelijk zijn om dit aan te tonen, omdat. de ID-kolom in de Afdelingen-tabel heeft de IDENTITEIT-optie, waardoor we de volgende query niet kunnen uitvoeren (verander afdelings-ID 3 in 30):

UPDATE Afdelingen SET ID=30 WHERE ID=3
Het belangrijkste is om de essentie van deze 2 opties te begrijpen ON DELETE CASCADE en ON UPDATE CASCADE. Ik gebruik deze opties in zeer zeldzame gevallen, en ik raad u aan goed na te denken voordat u ze specificeert in een referentiële beperking. als u per ongeluk een record uit de referentietabel verwijdert, kan dit tot grote problemen leiden en een kettingreactie veroorzaken.

Laten we afdeling 3 herstellen:

Geef toestemming om IDENTITY-waarden toe te voegen/wijzigen SET IDENTITY_INSERT Afdelingen ON INSERT Departments(ID,Name) VALUES(3,N"IT") -- weiger toevoegen/wijzigen IDENTITY-waarden SET IDENTITY_INSERT Afdelingen UIT
Maak de tabel Werknemers volledig leeg met de opdracht TRUNCATE TABLE:

TRUNCATE TAFEL Medewerkers
En nogmaals, laad de gegevens er opnieuw in met behulp van de vorige INSERT-opdracht:

INSERT Employees (ID,Name,Birthday,PositionID,DepartmentID,ManagerID)VALUES (1000,N"Ivanov II","19550219",2,1,NULL), (1001,N"Petrov P.P." ,"19831203",3 ,3.103), (1002,N"Sidorov SS","19760607",1,2,1000), (1003,N"Andreev AA","19820417" ,4,3,1000)

Laten we samenvatten

Op dit moment zijn er voor zover wij weten nog een paar DDL-commando's toegevoegd:

Het toevoegen van de IDENTITY-eigenschap aan het veld - stelt u in staat om dit veld automatisch gevuld te maken (tellerveld) voor de tabel;
WIJZIG TABEL tafel naam TOEVOEGEN list_of_fields_with_characteristics – hiermee kunt u nieuwe velden aan de tabel toevoegen;
WIJZIG TABEL tafel naam DROP KOLOM list_of_fields - hiermee kunt u velden uit de tabel verwijderen;
WIJZIG TABEL tafel naam BEPERKING TOEVOEGEN constraint_name VREEMDE SLEUTEL(velden) REFERENTIES lookup_table(fields) – hiermee kunt u een relatie definiëren tussen een tabel en een opzoektabel.

Andere beperkingen - UNIEK, STANDAARD, CONTROLE

Met de UNIQUE-beperking kunt u zeggen dat de waarde voor elke rij in een bepaald veld of een reeks velden uniek moet zijn. In het geval van de tabel Medewerkers kunnen we een dergelijke beperking opleggen aan het veld E-mail. Vul gewoon e-mail vooraf in met waarden als deze nog niet zijn gedefinieerd:

UPDATE Werknemers SET Email=" [e-mail beveiligd]"WHERE ID=1000 UPDATE Werknemers SET Email=" [e-mail beveiligd]" WHERE ID=1001 UPDATE Werknemers SET Email=" [e-mail beveiligd]"WHERE ID=1002 UPDATE Werknemers SET Email=" [e-mail beveiligd]" WAAR ID=1003
En nu kunt u een unieke beperking opleggen aan dit veld:

ALTER TABLE Werknemers ADD CONSTRAINT UQ_Employees_Email UNIEK(E-mail)
Nu kan de gebruiker niet voor meerdere medewerkers hetzelfde e-mailadres invoeren.

De uniciteitsbeperking wordt gewoonlijk als volgt genoemd - eerst komt het voorvoegsel "UQ_", dan de naam van de tabel en na het onderstrepingsteken is de naam van het veld waarop deze beperking wordt opgelegd.

Dienovereenkomstig, als een combinatie van velden uniek moet zijn in de context van de rijen van de tabel, dan vermelden we ze gescheiden door komma's:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE(field1,field2,...)
Door een DEFAULT-beperking aan een veld toe te voegen, kunnen we een standaardwaarde instellen die wordt vervangen als het veld niet wordt vermeld in de lijst met velden van het INSERT-commando wanneer een nieuw record wordt ingevoegd. Deze beperking kan direct worden ingesteld bij het maken van een tabel.

Laten we een nieuw veld "Aanwervingsdatum" toevoegen aan de tabel Werknemers en deze de naam HireDate geven en zeggen dat de standaardwaarde voor dit veld de huidige datum is:

WIJZIG TABEL Werknemers TOEVOEGEN Huurdatum datum NIET NULL STANDAARD SYSDATETIME()
Of als de HireDate-kolom al bestaat, kan de volgende syntaxis worden gebruikt:

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

ALTER TABLE Werknemers ADD CONSTRAINT DF_Employees_HireDate STANDAARD SYSDATETIME() VOOR HireDate
Aangezien deze kolom voorheen niet bestond, wordt bij het toevoegen aan elk record de huidige datumwaarde in het veld HireDate ingevoegd.

Bij het toevoegen van een nieuwe invoer wordt de huidige datum natuurlijk ook automatisch ingevoegd, als we deze niet expliciet instellen, d.w.z. niet gespecificeerd in de lijst met kolommen. Laten we dit met een voorbeeld laten zien zonder het veld HireDate op te geven in de lijst met toegevoegde waarden:

INSERT Employees(ID,Name,Email)VALUES(1004,N"Sergeev S.S."," [e-mail beveiligd]")
Laten we eens kijken wat er gebeurd is:

SELECTEER * VAN Medewerkers

ID kaart	Naam	verjaardag	E-mail	Positie-ID	Afdelings-ID	Manager-ID	Huur datum
1000	Ivanov II	1955-02-19	[e-mail beveiligd]	2	1	NUL	2015-04-08
1001	Petrov PP	1983-12-03	[e-mail beveiligd]	3	4	1003	2015-04-08
1002	Sidorov SS	1976-06-07	[e-mail beveiligd]	1	2	1000	2015-04-08
1003	Andreev AA	1982-04-17	[e-mail beveiligd]	4	3	1000	2015-04-08
1004	Sergeev SS	NUL	[e-mail beveiligd]	NUL	NUL	NUL	2015-04-08

De controlebeperking CHECK wordt gebruikt wanneer het nodig is om de in het veld ingevoegde waarden te controleren. Laten we deze beperking bijvoorbeeld opleggen aan het veld personeelsnummer, dat is onze werknemersidentificatie (ID). Laten we met behulp van deze beperking zeggen dat personeelsnummers een waarde moeten hebben van 1000 tot 1999:

WIJZIG TABEL Werknemers ADD CONSTRAINT CK_Employees_ID CHECK (ID TUSSEN 1000 EN 1999)
De beperking heeft meestal dezelfde naam, eerst met het voorvoegsel "CK_", daarna de tabelnaam en de naam van het veld waarop de beperking wordt toegepast.

Laten we proberen een ongeldige invoer in te voeren om te controleren of de beperking werkt (we zouden de bijbehorende foutmelding moeten krijgen):

INSERT Employees(ID,Email)VALUES(2000," [e-mail beveiligd]")
Laten we nu de in te voegen waarde wijzigen in 1500 en ervoor zorgen dat het record wordt ingevoegd:

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

WIJZIG TABEL Werknemers UNIEK TOEVOEGEN (e-mail) WIJZIG TABEL Werknemers VOEG CONTROLE TOE (ID TUSSEN 1000 EN 1999)
Maar dit is geen goede gewoonte en het is beter om de naam van de beperking expliciet te specificeren, omdat om er later achter te komen wat moeilijker is, moet je het object openen en kijken waar het verantwoordelijk voor is.

Met een goede naam kan veel informatie over een beperking direct uit de naam worden geleerd.

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

DROP TAFEL Medewerkers
En maak het opnieuw met alle gemaakte beperkingen met één CREATE TABLE-opdracht:

MAAK TABEL Werknemers (ID int NOT NULL, naam nvarchar (30), verjaardagsdatum, e-mail nvarchar (30), positie-ID int, afdelings-ID int, HireDate date NOT NULL STANDAARD SYSDATETIME(), -- voor STANDAARD gooi ik een CONSTRAINT PK_Employees PRIMARY KEY exception (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT UQ_Employees_Email UNIQUEINTEmail), CONSTRAINT UQ_Employees_Email UNIQUEINTEmail),

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

Een beetje over indexen die zijn gemaakt bij het maken van PRIMARY KEY en UNIQUE-beperkingen

Zoals u kunt zien in de bovenstaande schermafbeelding, werden bij het maken van de PRIMARY KEY en UNIQUE-beperkingen automatisch indexen met dezelfde namen (PK_Employees en UQ_Employees_Email) gemaakt. Standaard wordt de index voor de primaire sleutel gemaakt als GECLUSTERD en voor alle andere indexen als NIET GECLUSTERD. Het is vermeldenswaard dat het concept van een geclusterde index niet in alle DBMS beschikbaar is. Een tabel kan slechts één CLUSTERED-index hebben. GECLUSTERD - betekent dat de records van de tabel worden gesorteerd op deze index, het kan ook worden gezegd dat deze index directe toegang heeft tot alle tabelgegevens. Het is als het ware de hoofdindex van de tabel. Om het nog ruiger te zeggen, het is een index die op de tafel is geschroefd. De geclusterde index is een zeer krachtige tool die kan helpen bij het optimaliseren van zoekopdrachten, houd daar rekening mee. Als we willen zeggen dat de geclusterde index niet in de primaire sleutel wordt gebruikt, maar voor een andere index, moeten we bij het maken van de primaire sleutel de optie NONCLUSTERED specificeren:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMAIRE SLEUTEL NIET GECLUSTERD(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
En laten we ze nu maken met de opties GECLUSTERD en NIET GECLUSTERD:

ALTER TABEL Werknemers ADD CONSTRAINT PK_Employees PRIMAIRE SLEUTEL NIET-CLUSTERED (ID) ALTER TABLE Werknemers ADD CONSTRAINT UQ_Employees_Email UNIEKE CLUSTERED (E-mail)
Wanneer we nu selecteren uit de tabel Werknemers, kunnen we zien dat de records zijn gesorteerd op de geclusterde index UQ_Employees_Email:

SELECTEER * VAN Medewerkers

ID kaart	Naam	verjaardag	E-mail	Positie-ID	Afdelings-ID	Huur datum
1003	Andreev AA	1982-04-17	[e-mail beveiligd]	4	3	2015-04-08
1000	Ivanov II	1955-02-19	[e-mail beveiligd]	2	1	2015-04-08
1001	Petrov PP	1983-12-03	[e-mail beveiligd]	3	3	2015-04-08
1002	Sidorov SS	1976-06-07	[e-mail beveiligd]	1	2	2015-04-08

Voorheen, toen de geclusterde index de PK_Employees-index was, werden records standaard gesorteerd op het veld ID.

Maar in dit geval is dit slechts een voorbeeld dat de essentie van de geclusterde index laat zien, want. hoogstwaarschijnlijk zullen zoekopdrachten naar de tabel Werknemers worden gedaan door het ID-veld, en in sommige gevallen kan het zelf als referentie dienen.

Voor mappen is het meestal raadzaam om de geclusterde index op de primaire sleutel te bouwen, omdat: in verzoeken verwijzen we vaak naar de directory-ID om bijvoorbeeld de naam te verkrijgen (Positie, Afdeling). Hier herinneren we ons wat ik hierboven schreef, dat de geclusterde index directe toegang heeft tot de rijen van de tabel, en daaruit volgt dat we de waarde van elke kolom kunnen krijgen zonder extra overhead.

De geclusterde index is handig om toe te passen op de velden die het vaakst worden geselecteerd.

Soms creëren tabellen een sleutel door een surrogaatveld, in welk geval het handig is om de CLUSTERED index-optie te behouden voor een meer geschikte index en de NONCLUSTERED-optie op te geven bij het maken van een surrogaat-primaire sleutel.

Laten we samenvatten

In dit stadium hebben we kennis gemaakt met alle soorten beperkingen, in hun eenvoudigste vorm, die worden gemaakt door een commando als "ALTER TABLE table_name ADD CONSTRAINT constraint_name ...":

HOOFDSLEUTEL- hoofdsleutel;
VREEMDE SLEUTEL- het opzetten van koppelingen en het bewaken van de referentiële integriteit van data;
UNIEK- stelt u in staat om uniciteit te creëren;
REKENING- stelt u in staat om de juistheid van de ingevoerde gegevens uit te voeren;
STANDAARD– hiermee kunt u de standaardwaarde instellen;
Het is ook vermeldenswaard dat alle beperkingen kunnen worden verwijderd met het commando " WIJZIG TABEL tafel naam DROP-BEPERKING constraint_name".

We hebben ook gedeeltelijk het onderwerp indices aangeroerd en het concept van cluster geanalyseerd ( GECLUSTERD) en niet-geclusterd ( NIET-GECLUSTERD) inhoudsopgave.

Zelfstandige indexen maken

Zelfvoorziening verwijst hier naar indexen die niet zijn gemaakt voor een PRIMARY KEY of UNIEKE beperking.

Indexen op een veld of velden kunnen worden aangemaakt met het volgende commando:

CREATE INDEX IDX_Employees_Name ON Werknemers(Naam)
U kunt hier ook de opties CLUSTERED, NONCLUSTERED, UNIQUE specificeren, en u kunt ook de sorteerrichting specificeren voor elk afzonderlijk veld ASC (standaard) of DESC:

MAAK UNIEKE NIET-GECLUSTERDE INDEX UQ_Employees_EmailDesc ON werknemers (e-mail DESC)
Bij het maken van een niet-geclusterde index kan de optie NIET-GECLUSTERD worden weggelaten, zoals: het wordt standaard geïmpliceerd, het wordt hier eenvoudig getoond om de positie van de GECLUSTERD of NIET GECLUSTERD optie in de opdracht aan te geven.

U kunt de index verwijderen met het volgende commando:

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

Laten we de tabel bijvoorbeeld weer verwijderen:

DROP TAFEL Medewerkers
En maak het opnieuw met alle gemaakte beperkingen en indexen met één CREATE TABLE-opdracht:

MAAK TABEL Werknemers (ID int NOT NULL, naam nvarchar (30), verjaardagsdatum, e-mail nvarchar (30), positie-ID int, afdelings-ID int, HireDate date NOT NULL CONSTRAINT DF_Employees_HireDate STANDAARD SYSDATETIME(), ManagerID int, CONSTRAINT PK_Employees PRIMARY ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Departments(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY) 1999), INDEX IDX_Employees_Name(Name))
Voeg tot slot in de tabel van onze medewerkers:

INSERT Werknemers (ID,Naam,Verjaardag,E-mail,Positie-ID,DepartmentID,ManagerID)VALUES (1000,N"Ivanov II","19550219"," [e-mail beveiligd]",2,1,NULL), (1001,N"Petrov P.P.","19831203"," [e-mail beveiligd]",3,3,1003), (1002,N"Sidorov S.S.","19760607"," [e-mail beveiligd]",1,2,1000), (1003,N"Andreev A.A.","19820417"," [e-mail beveiligd]",4,3,1000)
Bovendien is het vermeldenswaard dat waarden kunnen worden opgenomen in een niet-geclusterde index door ze op te geven in INCLUDE. Die. in dit geval zal de INCLUDE-index enigszins lijken op een geclusterde index, alleen is de index nu 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 vermelde velden in de index staan, is het mogelijk dat het helemaal niet nodig is om de tabel te openen. Maar dit vergroot natuurlijk de grootte van de index, omdat de waarden van de vermelde velden worden gedupliceerd in de index.