Simple SQL-forespørgsler - kort hjælp og eksempler. Grundlæggende om opbygning af SQL-forespørgsler

Flertal moderne web applikationer interagerer med databaser, normalt ved hjælp af et sprog kaldet SQL. Heldigvis for os er dette sprog meget nemt at lære. I denne artikel vil vi se på simple SQL forespørgsler og lær, hvordan du bruger dem til at interagere med MySQL database.

Hvad skal du bruge?

SQL (Structured Query Language) et sprog specielt designet til at interface med databasestyringssystemer som f.eks MySQL, Oracle, Sqlite og andre... At fuldføre SQL anmodninger i denne artikel, anbefaler jeg dig at installere MySQL på lokal computer. Jeg anbefaler også at bruge phpMyAdmin som en visuel grænseflade.

Alt dette er tilgængeligt i alles foretrukne Denver. Jeg synes alle burde vide hvad det er og hvor man kan få det :). Kan brug også WAMP eller MAMP.

Denver har en indbygget MySQL konsol. Det er det, vi vil bruge.

OPRET DATABASE:oprettelse af database

Her er vores første anmodning. Vi vil oprette vores første database til videre arbejde.

Åbn for at begynde MySQL konsol og log ind. Til WAMP Standardadgangskoden er tom. Altså ingenting :). Til MAMP - "rod". For Denver skal vi afklare.

Indtast efter login næste linje og tryk Gå ind:

OPRET DATABASE my_first_db;

Bemærk, at der tilføjes et semikolon (;) i slutningen af ​​forespørgslen, ligesom på andre sprog.

Også kommandoer i SQL case-sensitive. Vi skriver dem med store bogstaver.

Muligheder formelt: KaraktersætOg Samling

Hvis du vil installere tegnsæt (tegnsæt) og sammenstilling (sammenligning) kan være skriv følgende kommando:

OPRET DATABASE my_first_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Finder en liste over tegnsæt, der understøttes i MySQL.

VIS DATABASER:viser en liste over alle databaser

Denne kommando bruges til at liste alle tilgængelige databaser.

SLIP DATABASE:sletning af en database

Du kan slette en eksisterende DB ved hjælp af denne forespørgsel.

Vær forsigtig med denne kommando, da den kører uden varsel. Hvis der er data i din database, vil det hele blive slettet.

BRUG:Databasevalg

Teknisk set er dette ikke en forespørgsel, men en erklæring og kræver ikke et semikolon i slutningen.

Det fortæller MySQL vælg standarddatabasen for den aktuelle session. Nu er vi klar til at lave tabeller og lave andre ting med databasen.

Hvad er en tabel i en database?

Du kan repræsentere tabellen i databasen som Excel-fil.

Ligesom på billedet har tabeller kolonnenavne, rækker og information. Ved hjælp af SQL forespørgsler, vi kan oprette sådanne tabeller. Vi kan også tilføje, læse, opdatere og slette oplysninger.

OPRET TABEL: Oprettelse af et bord

C Ved hjælp af denne forespørgsel kan vi oprette tabeller i databasen. Desværre dokumentationen MySQL ikke særlig klar for begyndere om dette spørgsmål. Strukturen af ​​denne type forespørgsel kan være meget kompleks, men vi starter med noget nemt.

Følgende forespørgsel vil oprette en tabel med 2 kolonner.

CREATE TABLE-brugere (brugernavn VARCHAR(20), create_date DATE);

Bemærk venligst, at vi kan skrive vores forespørgsler på flere linjer og med faner til indrykning.

Den første linje er enkel. Vi laver simpelthen en tabel kaldet "brugere". Dernæst i parentes, adskilt af kommaer, er en liste over alle kolonner. Efter hvert kolonnenavn har vi informationstyper, såsom VARCHAR eller DATE.

VARCHAR(20) betyder, at kolonnen er af typen streng og kan maksimalt være på 20 tegn. DATO er også en informationstype, der bruges til at gemme datoer i følgende format: "ÅÅÅÅ - MM-DD".

PRIMÆRNØGLE ( primærnøgleh)

Før vi kører den næste forespørgsel, skal vi også inkludere en kolonne for "user_id", som vil være vores primære nøgle. Du kan tænke på PRIMÆR NØGLE som information, der bruges til at identificere hver række i en tabel.

CREATE TABLE-brugere (user_id INT AUTO_INCREMENT PRIMARY KEY, brugernavn VARCHAR(20), create_date DATE);

INT gør det 32 ​​bit hele typen(f.eks. tal). AUTO_INCREMENT genererer automatisk en ny værdi ID hver gang vi tilføjer nye serier af information. Dette er ikke nødvendigt, men det gør hele processen lettere.

Denne kolonne behøver ikke at være en heltalsværdi, men den bruges oftest. At have en primær nøgle er også valgfrit, men det anbefales til databasearkitektur og ydeevne.

Lad os køre forespørgslen:

VIS TABELLER:vis alle tabeller

Denne forespørgsel giver dig mulighed for at få en liste over tabeller, der er i databasen.

FORKLARE:Vis tabelstruktur

For at vise strukturen af ​​en eksisterende tabel kan du bruge denne forespørgsel.

Kolonner vises med alle egenskaber.

DROP TABEL:slet tabel

Samme som DROP DATABASER, denne forespørgsel sletter tabellen og dens indhold uden varsel.

ÆNDRINGSTABEL: skifte bord

Denne forespørgsel kan også indeholde kompleks struktur på grund af det større antal ændringer, den kan foretage i tabellen. Lad os se på eksempler.

(hvis du slettede tabellen i det forrige trin, skal du oprette den igen til test)

TILFØJELSE AF EN KOLONNE

ALTER TABLE-brugere TILFØJ e-mail VARCHAR(100) EFTER brugernavn;

På grund af den gode læsbarhed af SQL, synes jeg, det ikke nytter noget at forklare det i detaljer. Vi tilføjer ny kolonne"e-mail" efter "brugernavn".

FJERNELSE AF EN KOLONNE

Det var også meget nemt. Brug denne anmodning med forsigtighed, da dine data kan blive slettet uden varsel.

Gendan den kolonne, du lige har slettet, til yderligere eksperimenter.

FORETAGE ÆNDRINGER I EN KOLONNE

Nogle gange vil du måske lave ændringer i egenskaberne for en kolonne, og du behøver ikke at slette den helt for at gøre dette.

Denne forespørgsel omdøbte brugerkolonnen til "brugernavn" og ændrede dens type fra VARCHAR(20) til VARCHAR(30). Denne ændring bør ikke ændre dataene i tabellen.

INDSÆT: Tilføjelse af oplysninger til en tabel

Lad os tilføje nogle oplysninger til tabellen ved hjælp af følgende forespørgsel.

Som du kan se, indeholder VALUES() en liste over værdier adskilt af kommaer. Alle værdier er indesluttet i enkelte kolonner. Og værdierne skal være i rækkefølgen af ​​de kolonner, der blev defineret, da tabellen blev oprettet.

Bemærk, at den første værdi er NULL for feltet PRIMÆR NØGLE kaldet "bruger_id". Vi gør dette, så ID'et genereres automatisk, da kolonnen har AUTO_INCREMENT egenskaben. Når information tilføjes for første gang, vil ID være 1. Næste række vil være 2, og så videre...

ALTERNATIV MULIGHED

Der er en anden forespørgselsmulighed til at tilføje rækker.

Denne gang bruger vi nøgleordet SET i stedet for VALUES, og det har ikke parentes. Der er flere nuancer:

Du kan springe kolonnen over. For eksempel tildelte vi ikke en værdi til "user_id", som som standard ville have dens AUTO_INCREMENT-værdi. Hvis du udelader en kolonne med en VARCHAR-type, tilføjes en tom række.

Hver kolonne skal henvises til ved navn. På grund af dette kan de nævnes i enhver rækkefølge, i modsætning til den tidligere version.

ALTERNATIV MULIGHED 2

Her er en anden mulighed.

Igen, da der er referencer til kolonnenavnet, kan du indstille værdierne i enhver rækkefølge.

LAST_INSERT_ID()

Du kan bruge denne forespørgsel til at få det ID, der var AUTO_INCREMENT for den sidste række i den aktuelle session.

NU()

Nu er det tid til at vise, hvordan du kan bruge MySQL-funktionen i forespørgsler.

Funktionen NU() viser den aktuelle dato. Så du kan bruge den til automatisk at indstille datoen for en kolonne til den nuværende, når du indsætter en ny række.

Bemærk venligst, at vi har modtaget 1 advarsel, men ignorer den venligst. Grunden til dette er, at NOW() også tjener til at udlæse midlertidig information.

VÆLG: Læsning af data fra en tabel

Hvis vi tilføjer information til en tabel, så ville det være logisk at lære at læse det derfra. Det er her, SELECT-forespørgslen vil hjælpe os.

Nedenfor er den enklest mulige SELECT-forespørgsel til at læse en tabel.

I dette tilfælde betyder stjernen (*), at vi har anmodet om alle felter fra tabellen. Hvis du kun ønsker bestemte kolonner, vil forespørgslen se sådan ud.

TilstandHVOR

Oftest er vi ikke interesserede i alle kolonner, men kun i nogle. Lad os for eksempel antage, at vi kun har brug for email adresse for bruger "nettuts".

WHERE giver dig mulighed for at angive betingelser i en forespørgsel og foretage detaljerede valg.

Bemærk, at for lighed bruges et lighedstegn (=), ikke to, som i programmering.

Du kan også bruge sammenligninger.

OG eller ELLER kan bruges til at kombinere betingelser:

Bemærk, at numeriske værdier må ikke stå i anførselstegn.

I()

Dette er nyttigt til sampling af flere værdier

SYNES GODT OM

Giver dig mulighed for at lave "wildcard"-anmodninger

%-ikonet bruges som et "jokertegn". Det vil sige, alt kunne være på sin plads.

TilstandBESTIL EFTER

Hvis du ønsker at få resultatet i en ordnet form efter ethvert kriterium

Standardrækkefølgen er ASC (mindst til størst). Til det modsatte bruges DESC.

LIMIT ... OFFSET ...

Du kan begrænse antallet af modtagne resultater.

LIMIT 2 tager kun de første 2 resultater. LIMIT 1 OFFSET 2 får 1 resultat efter de første 2. LIMIT 2, 1 betyder det samme (bemærk blot at offset kommer først og derefter limit ).

OPDATERING: Foretag ændringer i oplysningerne i tabellen

Denne forespørgsel bruges til at ændre oplysninger i en tabel.

I de fleste tilfælde bruges det sammen med en WHERE-sætning, da du højst sandsynligt vil lave ændringer i bestemte kolonner. Hvis der ikke er nogen WHERE-sætning, vil ændringerne påvirke alle rækker.

Du kan også bruge LIMIT til at begrænse antallet af rækker, som skal ændres.

SLET: Fjernelse af information fra en tabel

Ligesom UPDATE bruges denne forespørgsel med WHERE:

For at slette indholdet af en tabel kan du blot gøre dette:

SLET FRA brugere;

Men det er bedre at bruge TRUNCATE

Ud over at slette, nulstiller denne anmodning også værdier AUTO_INCREMENT og når du tilføjer rækker igen, starter nedtællingen fra nul. SLET gør ikke dette, og nedtællingen fortsætter.

Deaktivering af små bogstaver og specielle ord

Strengværdier

Nogle tegn skal deaktiveres ( flugt ), eller der kan være problemer.

Til dette bruges en omvendt skråstreg.(\).

Særlige ord

Fordi i MySQL der er mange specielle ord ( VÆLG eller OPDATERE ), for at undgå fejl, når du bruger dem, skal du bruge anførselstegn. Men ikke almindelige citater, men sådan her(`).

Det vil sige, du skal tilføje en kolonne med navnet " slette ", du skal gøre det sådan her:

Konklusion

Tak fordi du læste med til ende. Jeg håber, du fandt denne artikel nyttig. Det er ikke slut endnu! Fortsættes:).

• Tutorial

Hvad handler denne tutorial om?

Denne tutorial er noget i retning af et "stempel af min hukommelse" i SQL-sproget (DDL, DML), dvs. Dette er information, der er akkumuleret i løbet af mine professionelle aktiviteter og konstant lagres i mit hoved. Det her er til mig tilstrækkeligt minimum, som oftest bruges, når man arbejder med databaser. Hvis der er behov for at bruge mere komplette SQL-konstruktioner, så henvender jeg mig normalt til MSDN-biblioteket på internettet for at få hjælp. Efter min mening er det meget svært at holde alt i hovedet, og det er der ikke noget særligt behov for. Men at kende de grundlæggende strukturer er meget nyttigt, fordi... de er anvendelige i næsten samme form i mange relationelle databaser, såsom Oracle, MySQL, Firebird. Forskellene ligger hovedsageligt i datatyperne, som kan variere i detaljer. Grundlæggende designs SQL sprog ikke så mange, og med konstant øvelse bliver de hurtigt lært udenad. For at oprette objekter (tabeller, begrænsninger, indekser osv.), er det for eksempel nok at have et tekstredigeringsmiljø (IDE) ved hånden til at arbejde med databasen, og der er ingen grund til at studere visuelle værktøjer, der er skræddersyet til at arbejde med en bestemt type database (MS SQL, Oracle, MySQL, Firebird, ...). Dette er også praktisk, fordi al tekst er foran dine øjne, og du ikke behøver at løbe gennem adskillige faner for at oprette for eksempel et indeks eller begrænsning. Når du konstant arbejder med en database, er oprettelse, ændring og især genskabelse af et objekt ved hjælp af scripts mange gange hurtigere, end hvis du gør det i visuel tilstand. Også i script-tilstand (henholdsvis med behørig omhu) er det lettere at indstille og kontrollere reglerne for navngivning af objekter (min Subjektiv mening). Derudover er scripts praktiske at bruge, når ændringer foretaget i en database (f.eks. test) skal overføres i samme form til en anden database (produktiv).

SQL-sproget er opdelt i flere dele, her vil jeg se på de 2 vigtigste dele:

• DML – Data Manipulation Language, som indeholder følgende konstruktioner:
  • SELECT – datavalg
  • INSERT – indsættelse af nye data
  • OPDATERING – dataopdatering
  • SLET – sletning af data
  • FLET – datasammenfletning

Fordi Jeg er praktiker; der vil være lidt teori som sådan i denne lærebog, og alle konstruktioner vil blive forklaret ved hjælp af praktiske eksempler. Derudover tror jeg på, at et programmeringssprog, og især SQL, kun kan mestres gennem praksis, ved selv at opleve det og forstå, hvad der sker, når man udfører den eller den konstruktion.

Denne lærebog er lavet efter Trin for Trin princippet, dvs. du skal læse den sekventielt og helst umiddelbart følge eksemplerne. Men hvis du undervejs har brug for at lære om en bestemt kommando mere detaljeret, så brug en specifik søgning på internettet, for eksempel i MSDN-biblioteket.

Da jeg skrev denne tutorial, brugte jeg MS SQL Server version 2014-databasen, og jeg brugte MS SQL Server Management Studio (SSMS) til at udføre scripts.

Kort om MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) er et værktøj til Microsoft SQL Server til konfiguration, styring og administration af databasekomponenter. Dette værktøj indeholder en script-editor (som vi primært vil bruge) og grafik program, som fungerer med serverobjekter og -indstillinger. Hoved SQL værktøj Server Management Studio er en Object Explorer, der giver brugeren mulighed for at se, hente og administrere serverobjekter. Denne tekst er delvist lånt fra Wikipedia.

For at oprette en ny script-editor skal du bruge knappen "Ny forespørgsel":

For at ændre den aktuelle database kan du bruge rullelisten:

For at udføre en bestemt kommando (eller gruppe af kommandoer), skal du vælge den og trykke på "Udfør"-knappen eller "F5"-tasten. Hvis der kun er én kommando i editoren i øjeblikket, eller du skal udføre alle kommandoer, behøver du ikke at vælge noget.

Efter at have kørt scripts, især dem, der opretter objekter (tabeller, kolonner, indekser), for at se ændringerne, brug opdatering fra kontekstmenu ved at vælge den relevante gruppe (f.eks. Tabeller), selve tabellen eller kolonnegruppen i den.

Faktisk er det alt, vi behøver at vide for at fuldende eksemplerne givet her. Resten af ​​SSMS-værktøjet er nemt at lære på egen hånd.

Lidt teori

En relationel database (RDB, eller i det følgende i sammenhængen blot DB) er en samling af tabeller, der er forbundet med hinanden. Groft sagt er en database en fil, hvor data gemmes i en struktureret form.

DBMS – Database Management System, dvs. dette er et sæt værktøjer til at arbejde med en bestemt type database (MS SQL, Oracle, MySQL, Firebird, ...).

Bemærk
Fordi i livet, i dagligdags tale, siger vi for det meste: "Oracle DB", eller endda bare "Oracle", hvilket faktisk betyder "Oracle DBMS", så i forbindelse med denne tutorial vil udtrykket DB nogle gange blive brugt. Ud fra konteksten tror jeg, det vil være klart, hvad vi præcist taler om.

En tabel er en samling af kolonner. Kolonner kan også kaldes felter eller kolonner; alle disse ord vil blive brugt som synonymer, der udtrykker det samme.

Tabellen er hovedobjektet for RDB; alle RDB-data lagres række for række i tabellens kolonner. Linjer og poster er også synonymer.

For hver tabel, såvel som dens kolonner, er der angivet navne, som de efterfølgende tilgås.
Navnet på et objekt (tabelnavn, kolonnenavn, indeksnavn osv.) i MS SQL kan have maksimal længde 128 tegn.

Til reference– i ORACLE-databasen kan objektnavne have en maksimal længde på 30 tegn. Derfor skal du for en specifik database udvikle dine egne regler for navngivning af objekter for at overholde grænsen for antallet af tegn.

SQL er et sprog, der giver dig mulighed for at forespørge i en database ved hjælp af et DBMS. I et specifikt DBMS kan SQL-sproget have en specifik implementering (sin egen dialekt).

DDL og DML er en undergruppe af SQL-sproget:

• DDL-sproget bruges til at skabe og ændre databasestrukturen, dvs. at oprette/ændre/slette tabeller og relationer.
• DML-sproget giver dig mulighed for at manipulere tabeldata, dvs. med hendes linjer. Det giver dig mulighed for at vælge data fra tabeller, tilføje nye data til tabeller samt opdatere og slette eksisterende data.

I SQL kan du bruge 2 typer kommentarer (enkeltlinje og flerlinje):

En linje kommentar
Og

/* kommentar med flere linier */

Faktisk vil dette være nok for teorien.

DDL – Data Definition Language

Overvej for eksempel en tabel med data om medarbejdere i en form, som en person, der ikke er programmør, kender:

I I dette tilfælde kolonnerne i tabellen har følgende navne: Personalenummer, Fulde navn, Fødselsdato, E-mail, Stilling, Afdeling.

Hver af disse kolonner kan karakteriseres ved den type data, den indeholder:

• Personalenummer – heltal
• Fulde navn – streng
• Fødselsdato – dato
• E-mail – streng
• Position - streng
• Afdeling - linje

Kolonnetype er en egenskab, der angiver, hvilken type data en given kolonne kan gemme.

Til at begynde med vil det være nok kun at huske følgende grundlæggende datatyper, der bruges i MS SQL:

Betyder	Notation i MS SQL	Beskrivelse
Snor med variabel længde	varchar(N) Og nvarchar(N)	Ved hjælp af tallet N kan vi angive den maksimalt mulige strenglængde for den tilsvarende kolonne. For eksempel, hvis vi vil sige, at værdien af ​​kolonnen "Navn" kan indeholde maksimalt 30 tegn, så skal vi indstille dens type til nvarchar(30). Forskellen mellem varchar og nvarchar er, at varchar giver dig mulighed for at gemme strenge i ASCII-format, hvor et tegn optager 1 byte, og nvarchar gemmer Unicode-strenge, hvor hvert tegn optager 2 byte. Varchar-typen bør kun bruges, hvis du er 100 % sikker på, at feltet ikke skal gemme Unicode-tegn. For eksempel kan varchar bruges til at gemme e-mailadresser, fordi... de indeholder normalt kun ASCII-tegn.
Fast længde streng	char(N) Og nchar(N)	Denne type adskiller sig fra en streng med variabel længde ved, at hvis længden af ​​strengen er mindre end N tegn, så er den altid polstret til højre til en længde på N med mellemrum og gemt i databasen i denne form, dvs. i databasen fylder det præcis N tegn (hvor et tegn fylder 1 byte for char og 2 byte for nchar). I min praksis bruges denne type meget sjældent, og hvis den bruges, bruges den hovedsageligt i char(1) formatet, dvs. når et felt er defineret af et enkelt tegn.
Heltal	int	Denne type giver os mulighed for kun at bruge heltal i kolonnen, både positive og negative. Til reference (nu er dette ikke så relevant for os), er rækkevidden af ​​tal, som int-typen tillader, fra -2.147.483.648 til 2.147.483.647. Normalt er dette hovedtypen, der bruges til at angive identifikatorer.
Reelt eller reelt tal	flyde	Hvis vi taler i et enkelt sprog, så er det tal, der kan indeholde et decimaltegn (komma).
dato	dato	Hvis kolonnen kun skal gemme Datoen, som består af tre komponenter: Dag, Måned og År. For eksempel 15.02.2014 (15. februar 2014). Denne type kan bruges til kolonnen "Optagelsesdato", "Fødselsdato" osv., dvs. i tilfælde, hvor det er vigtigt for os kun at registrere datoen, eller når tidskomponenten ikke er vigtig for os og kan kasseres, eller hvis den ikke er kendt.
Tid	tid	Denne type kan bruges, hvis kolonnen kun skal gemme tidsdata, dvs. Timer, minutter, sekunder og millisekunder. For eksempel 17:38:31.3231603 For eksempel daglig "Flyafgangstid".
dato og tid	dato tid	Denne type giver dig mulighed for at gemme både dato og klokkeslæt samtidigt. For eksempel 15/02/2014 17:38:31.323 Dette kan f.eks. være dato og klokkeslæt for en begivenhed.
Flag	lidt	Denne type er praktisk at bruge til at gemme værdier i formen "Ja"/"Nej", hvor "Ja" vil blive gemt som 1, og "Nej" vil blive gemt som 0.

Feltværdien, hvis den ikke er forbudt, kan heller ikke angives; nøgleordet NULL bruges til dette formål.

For at køre eksemplerne, lad os oprette en testdatabase kaldet Test.

En simpel database (uden at angive yderligere parametre) kan oprettes ved at køre følgende kommando:

OPRET DATABASE Test
Du kan slette databasen med kommandoen (du skal være meget forsigtig med denne kommando):

DROP DATABASE-test
For at skifte til vores database kan du køre kommandoen:

BRUG Test
Alternativt kan du vælge Testdatabasen fra rullelisten i SSMS-menuområdet. Når jeg arbejder, bruger jeg ofte denne metode til at skifte mellem databaser.

Nu i vores database kan vi oprette en tabel ved at bruge beskrivelserne, som de er, ved at bruge mellemrum og kyrilliske tegn:

OPRET TABEL [Medarbejdere]([Personalnummer] int, [Navn] nvarchar(30), [Fødselsdato] dato, nvarchar(30), [Position] nvarchar(30), [Afdeling] nvarchar(30))
I dette tilfælde bliver vi nødt til at sætte navne i kantede parenteser […].

Men i databasen, for større bekvemmelighed, er det bedre at angive alle objektnavne på latin og ikke bruge mellemrum i navne. I MS SQL begynder hvert ord normalt i dette tilfælde med et stort bogstav, for eksempel, for feltet "Personnelnummer", kunne vi angive navnet PersonnelNumber. Du kan også bruge numre i navnet, for eksempel PhoneNumber1.

På en seddel
I nogle DBMS'er kan følgende navngivningsformat "PHONE_NUMBER" være mere at foretrække; for eksempel bruges dette format ofte i ORACLE-databasen. Når man angiver et feltnavn, er det naturligvis ønskeligt, at det ikke falder sammen med de nøgleord, der bruges i DBMS.

Af denne grund kan du glemme syntaksen for firkantede parenteser og slette tabellen [Medarbejdere]:

DROP TABEL [Medarbejdere]
For eksempel kan en tabel med medarbejdere hedde "Medarbejdere", og dens felter kan gives følgende navne:

• ID – personalenummer (medarbejder-id)
• Navn - fulde navn
• Fødselsdag – fødselsdato
• E-mail – E-mail
• Stilling - Stilling
• Afdeling - Afdeling

Meget ofte bruges ordet ID til at navngive et identifikationsfelt.

Lad os nu oprette vores tabel:

OPRET TABEL Medarbejdere(ID int, Navn nvarchar(30), Fødselsdato, E-mail nvarchar(30), Stilling nvarchar(30), Afdeling nvarchar(30))
For at angive påkrævede kolonner kan du bruge NOT NULL-indstillingen.

For en eksisterende tabel kan felter omdefineres ved hjælp af følgende kommandoer:

Opdater ID-felt ÆNDRING TABEL Medarbejdere ÆNDRE KOLONNE ID int NOT NULL -- opdatering Navnefelt ÆNDRE TABEL Medarbejdere ÆNDRE KOLONNE Navn nvarchar(30) IKKE NULL

På en seddel
Det generelle koncept for SQL-sproget forbliver det samme for de fleste DBMS'er (det er i hvert fald, hvad jeg kan vurdere ud fra de DBMS'er, jeg har arbejdet med). Forskellene mellem DDL i forskellige DBMS'er ligger hovedsageligt i datatyperne (ikke kun deres navne kan afvige her, men også detaljerne i deres implementering), og de meget specifikke detaljer ved implementeringen af ​​SQL-sproget kan også afvige lidt (dvs. essensen af ​​kommandoerne er den samme, men der kan være små forskelle i dialekten, desværre, men der er ingen standard). Når du har mestret det grundlæggende i SQL, kan du nemt skifte fra et DBMS til et andet, fordi... I dette tilfælde skal du kun forstå detaljerne i implementeringen af ​​kommandoer i det nye DBMS, dvs. i de fleste tilfælde vil det være tilstrækkeligt at tegne en analogi.

Oprettelse af en tabel CREATE TABLE Employees(ID int, -- i ORACLE er int-typen ækvivalenten (wrapper) for nummer(38) Navn nvarchar2(30), -- nvarchar2 i ORACLE svarer til nvarchar i MS SQL Fødselsdato, e-mail nvarchar2(30) , Position nvarchar2(30), Afdeling nvarchar2(30)); -- opdatering af ID- og Navn-felterne (her bruges MODIFY(...) i stedet for ALTER COLUMN) ALTER TABLE Medarbejdere MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- tilføjelse af PK (i dette tilfælde ser konstruktionen ud som i MS SQL, den vil blive vist nedenfor) ÆNDRINGSTABEL Medarbejdere ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
For ORACLE er der forskelle med hensyn til implementering af varchar2-typen; dens kodning afhænger af databaseindstillingerne, og teksten kan for eksempel gemmes i UTF-8-kodning. Derudover kan længden af ​​feltet i ORACLE indstilles både i bytes og i tegn, til dette bruger vi yderligere muligheder BYTE og CHAR, som er angivet efter feltlængden, for eksempel:

NAVN varchar2(30 BYTE) -- feltkapaciteten vil være 30 bytes NAVN varchar2(30 CHAR) -- feltkapaciteten vil være på 30 tegn
Hvilken mulighed vil blive brugt som standard BYTE eller CHAR, i tilfælde af en simpel indikation i ORACLE type varchar2(30), afhænger af databaseindstillingerne, det kan også nogle gange indstilles i IDE-indstillingerne. Generelt kan man nogle gange nemt blive forvirret, så i tilfælde af ORACLE, hvis varchar2-typen bruges (og det er nogle gange berettiget her, f.eks. ved brug af UTF-8-kodning), foretrækker jeg udtrykkeligt at skrive CHAR (da det er normalt mere praktisk at beregne længden af ​​strengen i tegn ).

Men i dette tilfælde, hvis der allerede er nogle data i tabellen, er det for vellykket udførelse af kommandoer nødvendigt, at ID- og Navn-felterne udfyldes i alle rækker i tabellen. Lad os demonstrere dette med et eksempel: indsæt data i tabellen i felterne ID, Position og Afdeling; dette kan gøres med følgende script:

INDSÆT Medarbejdere(ID,Position,Afdeling) VÆRDIER (1000,N"Direktor",N"Administration"), (1001,N"Programmer",N"IT"), (1002,N"Regnskab",N"Regnskab" ), (1003,N"Senior Programmer",N"IT")
I dette tilfælde vil INSERT-kommandoen også generere en fejl, fordi Da vi indsatte, specificerede vi ikke værdien af ​​det påkrævede Navn-felt.
Hvis vi allerede havde disse data i den oprindelige tabel, ville kommandoen "ALTER TABLE Employees ALTER COLUMN ID int NOT NULL" blive udført med succes, og kommandoen "ALTER TABLE Employees ALTER COLUMN Name int NOT NULL" ville producere en fejlmeddelelse, at feltet Navn indeholder NULL (uspecificerede) værdier.

Lad os tilføje værdier for feltet Navn og udfylde dataene igen:

NOT NULL muligheden kan også bruges direkte ved oprettelse af en ny tabel, dvs. i sammenhæng med CREATE TABLE-kommandoen.

Først skal du slette tabellen ved hjælp af kommandoen:

DROP TABEL Medarbejdere
Lad os nu oprette en tabel med de påkrævede ID- og Navn-kolonner:

OPRET TABEL Medarbejdere(ID int NOT NULL, Navn nvarchar(30) NOT NULL, Fødselsdato, Email nvarchar(30), Stilling nvarchar(30), Afdeling nvarchar(30))
Du kan også skrive NULL efter kolonnenavnet, hvilket vil betyde, at NULL-værdier (ikke angivet) vil være tilladt i det, men det er ikke nødvendigt, da denne egenskab er underforstået som standard.

Hvis du tværtimod vil gøre en eksisterende kolonne valgfri, så brug følgende kommandosyntaks:

ÆNDRINGSTABEL Medarbejdere ÆNDRE KOLONNE Navn nvarchar(30) NULL
Eller blot:

ÆNDRINGSTABEL Medarbejdere ÆNDRE KOLONNE Navn nvarchar(30)
Med denne kommando kan vi også ændre felttypen til en anden kompatibel type eller ændre dens længde. Lad os f.eks. udvide feltet Navn til 50 tegn:

ÆNDRINGSTABEL Medarbejdere ÆNDRE KOLONNE Navn nvarchar(50)

Primærnøgle

Når du opretter en tabel, er det ønskeligt, at den har en unik kolonne eller et sæt kolonner, der er unikke for hver af dens rækker - en post kan identificeres entydigt med denne unikke værdi. Denne værdi kaldes tabellens primære nøgle. For vores medarbejdertabel kunne en sådan unik værdi være ID-kolonnen (som indeholder "Medarbejdernes personalenummer" - lad i vores tilfælde givet værdi unik for hver medarbejder og kan ikke gentages).

Du kan oprette en primær nøgle til en eksisterende tabel ved hjælp af kommandoen:

ÆNDRINGSTABEL Medarbejdere TILFØJ KONSTRAINT PK_Medarbejdere PRIMÆR NØGLE(ID)
Hvor "PK_Employees" er navnet på den begrænsning, der er ansvarlig for den primære nøgle. Typisk er den primære nøgle navngivet ved hjælp af præfikset "PK_" efterfulgt af tabelnavnet.

Hvis den primære nøgle består af flere felter, skal disse felter stå i parentes, adskilt af kommaer:

ÆNDRINGSTABEL tabelnavn TILFØJ BEGRÆNSNING begrænsning_navn PRIMÆR NØGLE(felt1,felt2,...)
Det er værd at bemærke, at i MS SQL skal alle felter, der er inkluderet i den primære nøgle, have karakteristikken NOT NULL.

Den primære nøgle kan også bestemmes direkte ved oprettelse af en tabel, dvs. i sammenhæng med CREATE TABLE-kommandoen. Lad os slette tabellen:

DROP TABEL Medarbejdere
Og så laver vi det ved hjælp af følgende syntaks:

OPRET TABEL Medarbejdere(ID int NOT NULL, Navn nvarchar(30) NOT NULL, Fødselsdato, E-mail nvarchar(30), Stilling nvarchar(30), Afdeling nvarchar(30), BEGRÆNSNING PK_Employees PRIMÆR NØGLE(ID) -- beskriv PK efter alle felter som en begrænsning)
Efter oprettelsen skal du udfylde tabellen med data:

INDSÆT Medarbejdere(ID,Position,Afdeling,Navn) VÆRDIER (1000,N"Direktor",N"Administration",N"Ivanov I.I."), (1001,N"Programmer",N"IT",N" Petrov P.P." ), (1002,N"Accountant",N"Accounting",N"Sidorov S.S."), (1003,N"Senior Programmer",N"IT",N"Andreev A.A.")
Hvis den primære nøgle i en tabel kun består af værdierne i én kolonne, kan du bruge følgende syntaks:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- specificer som en karakteristik af feltet Navn nvarchar(30) NOT NULL, Fødselsdato, E-mail nvarchar(30), Stilling nvarchar(30), Afdeling nvarchar(30) )
Faktisk behøver du ikke at angive navnet på begrænsningen, i hvilket tilfælde den vil blive tildelt et systemnavn (som "PK__Employee__3214EC278DA42077"):

OPRET TABEL Medarbejdere(ID int NOT NULL, Navn nvarchar(30) NOT NULL, Fødselsdato, Email nvarchar(30), Stilling nvarchar(30), Afdeling nvarchar(30), PRIMÆR NØGLE(ID))
Eller:

OPRET TABEL Medarbejdere(ID int NOT NULL PRIMÆR NØGLE, Navn nvarchar(30) NOT NULL, Fødselsdato, Email nvarchar(30), Stilling nvarchar(30), Afdeling nvarchar(30))
Men jeg vil anbefale, at du for permanente tabeller altid udtrykkeligt angiver navnet på begrænsningen, fordi Med et eksplicit specificeret og forståeligt navn vil det være lettere at manipulere det senere; for eksempel kan du slette det:

ÆNDRINGSTABEL Medarbejdere SLIP KONSTRAINT PK_Medarbejdere
Men sådan en kort syntaks, uden at angive navnene på begrænsningerne, er praktisk at bruge, når du opretter midlertidige databasetabeller (navnet på den midlertidige tabel begynder med # eller ##), som vil blive slettet efter brug.

Lad os opsummere

Indtil videre har vi set på følgende kommandoer:

• OPRET TABEL tabelnavn (liste over felter og deres typer, begrænsninger) – bruges til at oprette en ny tabel i den aktuelle database;
• DROP TABEL tabelnavn – bruges til at slette en tabel fra den aktuelle database;
• ÆNDRE TABEL tabelnavn ÆNDRE KOLONNE kolonnenavn... – bruges til at opdatere kolonnetypen eller ændre dens indstillinger (f.eks. til at indstille NULL eller NOT NULL karakteristikken);
• ÆNDRE TABEL tabelnavn TILFØJ BEGRÆNSNING constraint_name PRIMÆRNØGLE(felt1, felt2,...) – tilføjelse af en primærnøgle til en eksisterende tabel;
• ÆNDRE TABEL tabelnavn DROP BEGRÆNSNING constraint_name – fjerner en begrænsning fra tabellen.

Lidt om midlertidige borde

Uddrag fra MSDN. Der er to typer midlertidige tabeller i MS SQL Server: lokal (#) og global (##). Lokale midlertidige tabeller er kun synlige for deres skabere, indtil forbindelsessessionen til SQL Server-instansen slutter, når de først oprettes. Lokale midlertidige tabeller slettes automatisk, efter at en bruger afbryder forbindelsen til forekomsten af ​​SQL Server. Globale midlertidige tabeller er synlige for alle brugere under enhver forbindelsessession, efter at disse tabeller er oprettet, og slettes, når alle brugere, der refererer til disse tabeller, afbryder forbindelsen til forekomsten af ​​SQL Server.

Midlertidige tabeller oprettes i systemet tempdb database, dvs. Ved at oprette dem tilstopper vi ikke hoveddatabasen; ellers er midlertidige tabeller fuldstændig identiske med almindelige tabeller; de kan også slettes med kommandoen DROP TABLE. Lokale (#) midlertidige tabeller er mere almindeligt anvendte.

For at oprette en midlertidig tabel kan du bruge kommandoen CREATE TABLE:

OPRET TABEL #Temp(ID int, Navn nvarchar(30))
Da en midlertidig tabel i MS SQL ligner en almindelig tabel, kan den også slettes ved hjælp af kommandoen DROP TABLE:

DROP TABEL #Temp

Du kan også oprette en midlertidig tabel (som en almindelig tabel) og straks udfylde den med data returneret af forespørgslen vha. VÆLG syntaks... IND I:

VÆLG ID, Navn INTO #Temp FRA medarbejdere

På en seddel
Implementeringen af ​​midlertidige tabeller kan variere i forskellige DBMS'er. For eksempel, i ORACLE og Firebird DBMS, skal strukturen af ​​midlertidige tabeller bestemmes på forhånd af CREATE GLOBAL TEMPORARY TABLE kommandoen, der angiver detaljerne ved lagring af data i det, så ser brugeren det blandt hovedtabellerne og arbejder med det som med et almindeligt bord.

Databasenormalisering – opdeling i undertabeller (mapper) og identifikation af forbindelser

Vores nuværende medarbejdertabel har den ulempe, at brugeren i stillings- og afdelingsfelterne kan indtaste en hvilken som helst tekst, som primært er fyldt med fejl, da han for en medarbejder blot kan angive "IT" som afdelingen, og for en anden medarbejder, for for eksempel, indtast "IT-afdeling", den tredje har "IT". Som følge heraf vil det være uklart, hvad brugeren mente, dvs. Er disse medarbejdere ansatte i samme afdeling, eller beskrev brugeren sig selv og det er 3 forskellige afdelinger? Desuden vil vi i dette tilfælde ikke være i stand til at gruppere dataene korrekt for nogle rapporter, hvor det kan være nødvendigt at vise antallet af medarbejdere for hver afdeling.

Den anden ulempe er mængden af ​​lagring af denne information og dens duplikering, dvs. For hver medarbejder er afdelingens fulde navn angivet, hvilket kræver plads i databasen til at gemme hvert tegn fra afdelingsnavnet.

Den tredje ulempe er vanskeligheden ved at opdatere disse felter, hvis navnet på en stilling ændres, for eksempel hvis du skal omdøbe stillingen "Programmer" til "Junior Programmer". I dette tilfælde bliver vi nødt til at foretage ændringer i hver række i tabellen, hvis position er lig med "Programmer".

For at undgå disse mangler anvendes såkaldt databasenormalisering - opdeling i undertabeller og referencetabeller. Det er ikke nødvendigt at gå ind i teoriens jungle og studere, hvad de er normale former, det er nok at forstå essensen af ​​normalisering.

Lad os oprette 2 katalogtabeller "Positioner" og "Afdelinger", lad os kalde henholdsvis de første stillinger og den anden afdelinger:

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 PRIMARYn KEY,(3Name ) IKKE NULL)
Bemærk, at vi her brugte den nye IDENTITY-indstilling, som siger, at dataene i ID-kolonnen bliver nummereret automatisk, startende fra 1, i trin på 1, dvs. Når du tilføjer nye poster, vil de sekventielt blive tildelt værdierne 1, 2, 3 osv. Sådanne felter kaldes normalt auto-inkrementering. En tabel kan kun have ét felt defineret med egenskaben IDENTITY, og normalt, men ikke nødvendigvis, er det felt den primære nøgle for den pågældende tabel.

På en seddel
I forskellige DBMS'er kan implementeringen af ​​felter med en tæller udføres forskelligt. I MySQL, for eksempel, er et sådant felt defineret ved hjælp af AUTO_INCREMENT-indstillingen. I ORACLE og Firebird kunne denne funktionalitet tidligere emuleres ved hjælp af SEQUENCE. Men så vidt jeg ved, har ORACLE nu tilføjet muligheden GENERATED AS IDENTITY.

Lad os udfylde disse tabeller automatisk, baseret på de aktuelle data, der er registreret i stillings- og afdelingsfelterne i tabellen Medarbejdere:

Vi udfylder feltet Navn i Stillingstabellen med unikke værdier fra Stillingsfeltet i Medarbejdertabellen INSERT Positions(Name) SELECT DISTINCT Position FROM Employees WHERE Position IS NOT NULL -- kasser poster, for hvilke stillingen ikke er specificeret
Lad os gøre det samme for afdelingstabellen:

INDSÆT Afdelinger(Navn) VÆLG DISTINKT Afdeling FRA Medarbejdere, HVOR Afdeling IKKE ER NULL
Hvis vi nu åbner tabellerne Stillinger og Afdelinger, vil vi se et nummereret sæt værdier for ID-feltet:

VÆLG * FRA positioner

VÆLG * FRA afdelinger

Disse tabeller vil nu spille rollen som opslagsværker til at specificere stillinger og afdelinger. Vi vil nu henvise til job- og afdelings-id'er. Lad os først og fremmest oprette nye felter i tabellen Medarbejdere for at gemme identifikationsdata:

Tilføj et felt for stillings-ID ALTER TABLE Medarbejdere ADD PositionID int -- tilføj et felt for afdelings-ID ALTER TABLE Medarbejdere ADD DepartmentID int
Typen af ​​referencefelter skal være den samme som i mapper, i dette tilfælde er det int.

Du kan også tilføje flere felter til tabellen på én gang med én kommando, idet du angiver felterne adskilt af kommaer:

ÆNDRINGSTABEL Medarbejdere ADD PositionID int, DepartmentID int
Lad os nu skrive links (referencebegrænsninger - FOREIGN KEY) til disse felter, så brugeren ikke har mulighed for at skrive værdier i disse felter, som ikke er blandt de ID-værdier, der findes i mapperne.

ÆNDRINGSTABEL Medarbejdere ADD CONSTRAINT FK_Employees_PositionID UDENLANDSKE NØGLE(PositionID) REFERENCER Positioner(ID)
Og vi gør det samme for det andet felt:

ÆNDRINGSTABEL Medarbejdere TILFØJ BEGRÆNSNING FK_Employees_DepartmentID UDENLANDSKE KEY(DepartmentID) REFERENCER Afdelinger(ID)
Nu vil brugeren kun kunne indtaste ID-værdier fra den tilsvarende mappe i disse felter. For at bruge en ny afdeling eller stilling skal han derfor først tilføje en ny post til den tilsvarende telefonbog. Fordi Positioner og afdelinger er nu gemt i mapper i én enkelt kopi, så for at ændre navnet er det nok kun at ændre det i biblioteket.

Navnet på en referencebegrænsning er normalt et sammensat navn, der består af præfikset "FK_", efterfulgt af tabelnavnet og efterfulgt af en understregning, efterfulgt af navnet på det felt, der refererer til referencetabelidentifikatoren.

En identifikator (ID) er normalt en intern værdi, der kun bruges til relationer, og hvilken værdi der er gemt der er i de fleste tilfælde fuldstændig ligegyldigt, så der er ingen grund til at forsøge at slippe af med huller i talrækken, der opstår under arbejdet med tabellen, for eksempel efter sletning af poster fra biblioteket.

ALTER TABLE-tabel ADD CONSTRAINT constraint_name UDENLANDSKE NØGLE(felt1,felt2,...) REFERENCER referencetabel(felt1,felt2,...)
I dette tilfælde, i tabellen "reference_table", er den primære nøgle repræsenteret af en kombination af flere felter (felt1, felt2,...).

Faktisk, lad os nu opdatere felterne PositionID og DepartmentID med ID-værdier fra mapperne. Lad os bruge kommandoen DML UPDATE til dette formål:

OPDATERING e SET PositionID=(VÆLG ID FRA Stillinger WHERE Name=e.Position), DepartmentID=(SELECT ID FROM Departments WHERE Name=e.Department) FRA Medarbejdere e
Lad os se, hvad der sker ved at køre anmodningen:

VÆLG * FRA Medarbejdere

Det er det, felterne PositionID og DepartmentID er udfyldt med identifikatorerne, der svarer til stillinger og afdelinger; Stillings- og afdelingsfelterne er ikke længere nødvendige i tabellen Medarbejdere, du kan slette disse felter:

ÆNDRINGSTABEL Medarbejdere DROP KOLONNE Stilling, afdeling
Nu ser vores tabel sådan ud:

VÆLG * FRA Medarbejdere

ID	Navn	Fødselsdag	E-mail	Position ID	Afdelings-ID
1000	Ivanov I.I.	NUL	NUL	2	1
1001	Petrov P.P.	NUL	NUL	3	3
1002	Sidorov S.S.	NUL	NUL	1	2
1003	Andreev A.A.	NUL	NUL	4	3

De der. Til sidst slap vi for at gemme overflødige oplysninger. Nu, baseret på job- og afdelingsnumre, kan vi entydigt bestemme deres navne ved hjælp af værdierne i referencetabellerne:

VÆLG e.ID,e.Name,p.Name PositionName,d.Name Afdelingsnavn FRA Medarbejdere e LEFT JOIN Afdelinger d ON d.ID=e.DepartmentID LEFT JOIN Stillinger p ON p.ID=e.PositionID

I objektinspektøren kan vi se alle de objekter, der er oprettet for en given tabel. Herfra kan du udføre forskellige manipulationer med disse objekter - for eksempel omdøbe eller slette objekter.

Det er også værd at bemærke, at tabellen kan referere til sig selv, dvs. du kan oprette et rekursivt link. Lad os f.eks. tilføje endnu et felt ManagerID til vores tabel med medarbejdere, som vil angive den medarbejder, som han rapporterer til denne medarbejder. Lad os oprette et felt:

ÆNDRINGSTABEL Medarbejdere ADD ManagerID int
Dette felt tillader en NULL-værdi; feltet vil være tomt, hvis der for eksempel ikke er nogen overordnede over medarbejderen.

Lad os nu oprette en UDENLANDSKE NØGLE til tabellen Medarbejdere:

ÆNDRINGSTABEL Medarbejdere TILFØJ KONSTRAINT FK_Employees_ManagerID UDENLANDSKE NØGLE (ManagerID) REFERENCER Medarbejdere(ID)
Lad os nu oprette et diagram og se, hvordan relationerne mellem vores tabeller ser ud på det:

Som et resultat bør vi se følgende billede (tabellen Medarbejdere er forbundet med tabellerne Stillinger og Afdelinger og refererer også til sig selv):

Endelig er det værd at sige, at referencenøgler kan indeholde yderligere muligheder ON DELETE CASCADE og ON UPDATE CASCADE, som angiver, hvordan man skal opføre sig, når man sletter eller opdaterer en post, der henvises til i referencetabellen. Hvis disse muligheder ikke er angivet, kan vi ikke ændre ID'et i katalogtabellen for en post, der er refereret fra en anden tabel, og vi vil heller ikke være i stand til at slette en sådan post fra kataloget, før vi sletter alle rækker, der refererer til denne post eller lad os opdatere referencerne i disse linjer til en anden værdi.

Lad os f.eks. genskabe tabellen, der angiver indstillingen ON SLET CASCADE for FK_Employees_DepartmentID:

DROP TABLE Medarbejdere CREATE TABLE Medarbejdere(ID int NOT NULL, Navn nvarchar(30), Fødselsdato, Email nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees PRIMÆR NØGLE (ID), CONSTRAINTEID FKDepartment FOREIGNDepartment ) REFERENCER Afdelinger(ID) PÅ SLET CASCADE, BEGRÆNSNING FK_Employees_PositionID UDENLANDSKE NØGLE(PositionID) REFERENCES Positioner(ID), BEGRÆNSNING FK_Employees_ManagerID UDENLANDSKE NØGLE (ManagerID) REFERENCES Medarbejdere INSERTPost,Dag,Dag,Dag,Dag,Dag.ID) mentID, ManagerID )VÆRDIER (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)
Lad os slette afdelingen med ID 3 fra afdelingstabellen:

SLET afdelinger WHERE ID=3
Lad os se på dataene i tabellen Medarbejdere:

VÆLG * FRA Medarbejdere

ID	Navn	Fødselsdag	E-mail	Position ID	Afdelings-ID	ManagerID
1000	Ivanov I.I.	1955-02-19	NUL	2	1	NUL
1002	Sidorov S.S.	1976-06-07	NUL	1	2	1000

Som du kan se, blev data for afdeling 3 fra tabellen Medarbejdere også slettet.

Indstillingen ON UPDATE CASCADE opfører sig på samme måde, men den er effektiv ved opdatering af ID-værdien i mappen. Hvis vi for eksempel ændrer id'et for en stilling i stillingskataloget, vil afdelings-ID'et i tabellen Medarbejdere i dette tilfælde blive opdateret til den nye id-værdi, som vi sætter i kataloget. Men i dette tilfælde vil det simpelthen ikke være muligt at demonstrere dette, fordi ID-kolonnen i afdelingstabellen har IDENTITY-indstillingen, som ikke tillader os at udføre følgende forespørgsel (skift afdelings-ID 3 til 30):

OPDATERE afdelinger SÆT ID=30 HVOR ID=3
Det vigtigste er at forstå essensen af ​​disse 2 muligheder PÅ SLET CASCADE og PÅ OPDATERING CASCADE. Jeg bruger disse muligheder meget sjældent og anbefaler, at du tænker dig grundigt om, før du angiver dem i en referencebegrænsning, fordi hvis du ved et uheld sletter en post fra en katalogtabel, kan dette føre til store problemer og skabe en kædereaktion.

Lad os genoprette afdeling 3:

Vi giver tilladelse til at tilføje/ændre IDENTITY-værdi SET IDENTITY_INSERT afdelinger ON INSERT afdelinger(ID,navn) VALUES(3,N"IT") -- vi forbyder tilføjelse/ændring af IDENTITY-værdi SET IDENTITY_INSERT afdelinger FRA
Lad os rydde tabellen medarbejder fuldstændigt ved hjælp af kommandoen TRUNCATE TABLE:

TRUNCATE TABEL Medarbejdere
Og igen vil vi genindlæse dataene i det ved hjælp af den forrige INSERT-kommando:

INDSÆT Medarbejdere (ID,Navn,Fødselsdag,PositionID,DepartmentID,ManagerID)VÆRDIER (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)

Lad os opsummere

I øjeblikket er flere flere DDL-kommandoer blevet tilføjet til vores viden:

• Tilføjelse af egenskaben IDENTITY til et felt – giver dig mulighed for at gøre dette felt til et automatisk udfyldt felt (tællerfelt) for tabellen;
• ÆNDRE TABEL tabelnavn TILFØJE list_of_fields_with_characteristics – giver dig mulighed for at tilføje nye felter til tabellen;
• ÆNDRE TABEL tabelnavn SLIP KOLONNE list_fields – giver dig mulighed for at fjerne felter fra tabellen;
• ÆNDRE TABEL tabelnavn TILFØJ BEGRÆNSNING constraint_name FREMMED NØGLE(felter) REFERENCER table_reference (felter) – giver dig mulighed for at definere forholdet mellem tabellen og referencetabellen.

Andre begrænsninger – UNIK, STANDARD, KONTROL

Med hjælp UNIKE begrænsninger vi kan sige, at værdien for hver række i et givet felt eller sæt af felter skal være unik. I tilfældet med tabellen Medarbejdere kan vi pålægge en sådan begrænsning i feltet E-mail. Bare forudfyld e-mail med værdier, hvis de ikke allerede er defineret:

OPDATERING Medarbejdere SET Email=" [e-mail beskyttet]" WHERE ID=1000 OPDATERING Medarbejdere SET E-mail=" [e-mail beskyttet]" WHERE ID=1001 OPDATERING Medarbejdere SET Email=" [e-mail beskyttet]" WHERE ID=1002 OPDATERING Medarbejdere SET Email=" [e-mail beskyttet]"HVOR ID=1003
Nu kan du pålægge dette felt en unikhedsbegrænsning:

ÆNDRINGSTABEL Medarbejdere TILFØJ KONSTRAINT UQ_Medarbejdere_E-mail UNIQUE(E-mail)
Nu vil brugeren ikke kunne indtaste den samme e-mail for flere medarbejdere.

En unik begrænsning er normalt navngivet som følger - først kommer præfikset "UQ_", derefter navnet på tabellen og efter understregningen kommer navnet på det felt, som denne begrænsning anvendes på.

Derfor, hvis en kombination af felter skal være unikke i sammenhæng med tabelrækker, lister vi dem adskilt med kommaer:

ÆNDRINGSTABEL tabelnavn TILFØJ BEGRÆNSNING begrænsningsnavn UNIK(felt1,felt2,...)
Ved at tilføje en DEFAULT-begrænsning til et felt, kan vi specificere en standardværdi, der vil blive erstattet, hvis dette felt, når du indsætter en ny post, ikke er opført på listen over felter i INSERT-kommandoen. Denne begrænsning kan indstilles direkte ved oprettelse af tabellen.

Lad os tilføje et nyt ansættelsesdato-felt til tabellen Medarbejdere og kalde det HireDate og sige, at standardværdien er af dette felt vilje Den aktuelle dato:

ÆNDRINGSTABEL Medarbejdere TILFØJ HireDate dato IKKE NULL STANDARD SYSDATETIME()
Eller hvis kolonnen HireDate allerede eksisterer, kan følgende syntaks bruges:

ÆNDRINGSTABEL Medarbejdere TILFØJ STANDARDSYSDATETIME() FOR HireDate
Her har jeg ikke specificeret navnet på begrænsningen, fordi... i tilfælde af DEFAULT har jeg den opfattelse, at dette ikke er så kritisk. Men hvis du gør det på en god måde, så tror jeg, at du ikke behøver at være doven, og du bør sætte et normalt navn. Dette gøres som følger:

ÆNDRINGSTABEL Medarbejdere ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
Sådan af denne kolonne ikke eksisterede før, så når du tilføjer den til hver post, vil den aktuelle datoværdi blive indsat i feltet HireDate.

Når du tilføjer en ny post, indsættes den aktuelle dato naturligvis også automatisk, medmindre vi udtrykkeligt sætter det, dvs. Vi vil ikke angive det i listen over kolonner. Lad os vise dette med et eksempel uden at angive feltet HireDate på listen over tilføjede værdier:

INSERT Employees(ID,Name,Email)VALUES(1004,N"Sergeev S.S."," [e-mail beskyttet]")
Lad os se, hvad der skete:

VÆLG * FRA Medarbejdere

ID	Navn	Fødselsdag	E-mail	Position ID	Afdelings-ID	ManagerID	Ansættelsesdato
1000	Ivanov I.I.	1955-02-19	[e-mail beskyttet]	2	1	NUL	2015-04-08
1001	Petrov P.P.	1983-12-03	[e-mail beskyttet]	3	4	1003	2015-04-08
1002	Sidorov S.S.	1976-06-07	[e-mail beskyttet]	1	2	1000	2015-04-08
1003	Andreev A.A.	1982-04-17	[e-mail beskyttet]	4	3	1000	2015-04-08
1004	Sergeev S.S.	NUL	[e-mail beskyttet]	NUL	NUL	NUL	2015-04-08

CHECK check-begrænsningen bruges, når det er nødvendigt at kontrollere de værdier, der er indsat i feltet. Lad os for eksempel pålægge denne begrænsning på personalenummerfeltet, som for os er en medarbejder-id (ID). Med hjælp denne begrænsning Lad os sige, at personaletal skal have en værdi fra 1000 til 1999:

ÆNDRINGSTABEL Medarbejdere TILFØJ BEGRÆNSNING CK_Employees_ID CHECK(ID MELLEM 1000 OG 1999)
Begrænsningen navngives normalt på samme måde, først med præfikset "CK_", derefter navnet på tabellen og navnet på det felt, som denne begrænsning er pålagt.

Lad os prøve at indsætte en ugyldig post for at kontrollere, at begrænsningen virker (vi skulle få den tilsvarende fejl):

INSERT Employees(ID,Email) VALUES(2000," [e-mail beskyttet]")
Lad os nu ændre den indsatte værdi til 1500 og sørge for, at posten er indsat:

INSERT Employees(ID,Email) VALUES(1500," [e-mail beskyttet]")
Du kan også oprette UNIQUE og CHECK-begrænsninger uden at angive et navn:

ÆNDRING TABEL Medarbejdere TILFØJ UNIK(E-mail) ÆNDRING TABEL Medarbejdere TILFØJ KONTROL (ID MELLEM 1000 OG 1999)
Men det er ikke meget god øvelse og det er bedre at angive navnet på begrænsningen eksplicit, fordi For at finde ud af det senere, hvilket vil være vanskeligere, bliver du nødt til at åbne objektet og se på, hvad det er ansvarligt for.

Med et godt navn kan en masse information om begrænsningen læres direkte fra dens navn.

Og derfor kan alle disse begrænsninger oprettes med det samme, når du opretter en tabel, hvis den ikke eksisterer endnu. Lad os slette tabellen:

DROP TABEL Medarbejdere
Og vi vil genskabe det med alle de oprettede begrænsninger med én CREATE TABLE-kommando:

OPRET TABEL Medarbejdere(ID int NOT NULL, Navn nvarchar(30), Fødselsdato, Email nvarchar(30), PositionID int, DepartmentID int, Ansættelsesdato IKKE NULL STANDARD SYSDATETIME(), -- for STANDARD vil jeg gøre en undtagelse CONSTRAINT PK_Employees PRIMÆR NØGLE (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Afdelinger(ID), CONSTRAINT FK_Employees_PositionID UDENLANDSKE NØGLE(PositionID) REFERENCES Positions(ID), CONSTRAINT UQ_Employees_Eemployees_Eemployees_Eemail (BETWE-Employees_E EEN 1000 OG 1999) )

INDSÆT medarbejdere (ID, navn, fødselsdag, e-mail, positions-id, afdelings-ID) VÆRDI (1000,N"Ivanov I.I.","19550219"," [e-mail beskyttet]",2,1), (1001,N"Petrov P.P.","19831203"," [e-mail beskyttet]",3,3), (1002,N"Sidorov S.S.","19760607"," [e-mail beskyttet]",1,2), (1003,N"Andreev A.A.","19820417"," [e-mail beskyttet]",4,3)

Lidt om de indekser, der oprettes ved oprettelse af PRIMÆR NØGLE og UNIKKE begrænsninger

Som du kan se på skærmbilledet ovenfor, blev der automatisk oprettet indekser med de samme navne (PK_Employees og UQ_Employees_Email), når de oprettede PRIMARY KEY og UNIQUE begrænsninger. Som standard er indekset for den primære nøgle oprettet som KLUSTERET, og for alle andre indekser som IKKE KLUSTERET. Det er værd at sige, at konceptet med et klyngeindeks ikke er tilgængeligt i alle DBMS'er. En tabel kan kun have ét KLUSTERET indeks. CLUSTERED – betyder at tabelposterne bliver sorteret efter dette indeks, vi kan også sige at dette indeks har direkte adgang til alle data i tabellen. Dette er så at sige tabellens hovedindeks. For at sige det endnu mere groft er dette et indeks knyttet til en tabel. Et clustered index er et meget kraftfuldt værktøj, der kan hjælpe med forespørgselsoptimering, men lad os bare huske dette for nu. Hvis vi ønsker at fortælle det klyngede indeks, at det ikke skal bruges på den primære nøgle, men på et andet indeks, skal vi, når vi opretter den primære nøgle, angive indstillingen IKKE CLUSTERED:

ÆNDRINGSTABEL tabelnavn TILFØJ BEGRÆNSNING begrænsning_navn PRIMÆR NØGLE IKKE KLUSTERET(felt1,felt2,...)
Lad os f.eks. gøre begrænsningsindekset PK_Employees ikke-klynget, og begrænsningsindekset UQ_Employees_Email klynget. Først og fremmest, lad os fjerne disse begrænsninger:

ÆNDRING TABEL Medarbejdere SLIP BEGRÆNSNING PK_Medarbejdere ÆNDRING TABEL Medarbejdere SLIP BEGRÆNSNING UQ_Employees_Email
Lad os nu oprette dem med indstillingerne CLUSTERED og IKKE-CLUSTERED:

ÆNDRING TABEL Medarbejdere TILFØJ BEGRÆNSNING PK_Medarbejdere PRIMÆR NØGLE IKKE KLUNDET (ID) ÆNDRING TABEL Medarbejdere TILFØJ BEGRÆNSNING UQ_Employees_Email UNIK CLUSTERED (E-mail)
Nu, ved at vælge fra tabellen Medarbejdere, vil vi se, at posterne er sorteret efter UQ_Employees_Email-klyngeindekset:

VÆLG * FRA Medarbejdere

ID	Navn	Fødselsdag	E-mail	Position ID	Afdelings-ID	Ansættelsesdato
1003	Andreev A.A.	1982-04-17	[e-mail beskyttet]	4	3	2015-04-08
1000	Ivanov I.I.	1955-02-19	[e-mail beskyttet]	2	1	2015-04-08
1001	Petrov P.P.	1983-12-03	[e-mail beskyttet]	3	3	2015-04-08
1002	Sidorov S.S.	1976-06-07	[e-mail beskyttet]	1	2	2015-04-08

Tidligere, da det klyngede indeks var PK_Employees-indekset, blev poster som standard sorteret efter ID-feltet.

Men i dette tilfælde er dette blot et eksempel, der viser essensen af ​​et klynget indeks, fordi Mest sandsynligt vil der blive foretaget forespørgsler til tabellen Medarbejdere ved hjælp af ID-feltet, og i nogle tilfælde vil det måske selv fungere som en mappe.

For mapper er det normalt tilrådeligt, at det klyngede indeks bygges efter primærnøgle, fordi i forespørgsler henviser vi ofte til adressekartoteket for at få f.eks. navnet (stilling, afdeling). Lad os huske her, hvad jeg skrev ovenfor, at et klynget indeks har direkte adgang til tabelrækker, og det følger, at vi kan få værdien af ​​enhver kolonne uden yderligere overhead.

Det er fordelagtigt at anvende et klyngeindeks på felter, der samples oftest.

Nogle gange oprettes tabeller med en nøgle baseret på et surrogatfelt; i dette tilfælde kan det være nyttigt at gemme CLUSTERED-indeksindstillingen for et mere passende indeks og angive NOCLUSTERED-indstillingen, når du opretter en surrogat-primærnøgle.

Lad os opsummere

På på dette tidspunkt vi stiftede bekendtskab med alle typer begrænsninger, i deres enkleste form, som er skabt af en kommando som "ALTER TABLE table_name ADD CONSTRAINT constraint_name ...":

• PRIMÆRNØGLE- primærnøgle;
• FREMMED NØGLE– oprettelse af forbindelser og overvågning af referentiel integritet af data;
• ENESTÅENDE– giver dig mulighed for at skabe unikhed;
• KONTROLLERE– giver dig mulighed for at sikre korrektheden af ​​de indtastede data;
• STANDARD– giver dig mulighed for at indstille en standardværdi;
• Det er også værd at bemærke, at alle begrænsninger kan fjernes ved hjælp af kommandoen " ÆNDRE TABEL tabelnavn DROP BEGRÆNSNING constraint_name".

Vi berørte også delvist emnet indekser og undersøgte begrebet klynge ( KLUSTERET) og ikke-klyngede ( IKKE-KLYNDERET) indeks.

Oprettelse af selvstændige indekser

Med uafhængige mener vi her indekser, der ikke er oprettet under PRIMÆR NØGLE eller UNIK begrænsning.

Indekser på et eller flere felter kan oprettes med følgende kommando:

OPRET INDEX IDX_Employees_Name ON Employees(Name)
Her kan du også angive indstillingerne CLUSTERED, NOCLUSTERED, UNIQUE, og du kan også angive sorteringsretningen for hvert enkelt felt ASC (standard) eller DESC:

OPRET UNIKT IKKE-KLUSTERET INDEX UQ_Employees_EmailDesc ON Medarbejdere (E-mail DESC)
Når du opretter et ikke-klynget indeks, kan indstillingen IKKE-KLUSTERET udelades, fordi det er underforstået som standard og vises her blot for at angive placeringen af ​​CLUSTERED eller IKKE-CLUSTERED indstillingen i kommandoen.

Du kan slette indekset med følgende kommando:

DROP INDEX IDX_Employees_Name ON Medarbejdere
Simple indekser såvel som begrænsninger kan oprettes i forbindelse med CREATE TABLE-kommandoen.

Lad os f.eks. slette tabellen igen:

DROP TABEL Medarbejdere
Og vi vil genskabe det med alle de oprettede begrænsninger og indekser med en CREATE TABLE-kommando:

OPRET TABEL Medarbejdere(ID int IKKE NULL, Navn nvarchar(30), Fødselsdato, E-mail nvarchar(30), PositionID int, DepartmentID int, Ansættelsesdato IKKE NULL BEGRÆNSNING DF_Employees_HireDate DEFAULT SYSDATETIME(), Ansættelses-ID int, KONCERN ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERENCES Afdelinger(ID), CONSTRAINT FK_Employees_PositionID UDENLANDSKE NØGLE(PositionID) REFERENCES Positions(ID), CONSTRAINT FK_Employees_ManagerID UDENLANDSKE_Employees_ManagerID(Employees_ManagerID) CONSTRAINT(PositionsID) CONSTRAINT loyees_Email UNIQUE(E-mail), BEGRÆNSNING CK_Employees_ID CHECK(ID MELLEM 1000 OG 1999), INDEX IDX_Employees_Name(Name))
Lad os endelig indsætte vores medarbejdere i tabellen:

INDSÆT medarbejdere (ID, navn, fødselsdag, e-mail, stillings-id, afdelings-ID, leder-ID) VÆRDI (1000,N"Ivanov I.I.","19550219"," [e-mail beskyttet]",2,1,NULL), (1001,N"Petrov P.P.","19831203"," [e-mail beskyttet]",3,3,1003), (1002,N"Sidorov S.S.","19760607"," [e-mail beskyttet]",1,2,1000), (1003,N"Andreev A.A.","19820417"," [e-mail beskyttet]",4,3,1000)
Derudover er det værd at bemærke, at du kan inkludere værdier i et ikke-klynget indeks ved at angive dem i INCLUDE. De der. i dette tilfælde vil INCLUDE-indekset minde lidt om et klynget indeks, kun nu er indekset ikke knyttet til tabellen, men de nødvendige værdier er knyttet til indekset. Følgelig kan sådanne indekser i høj grad forbedre ydeevnen af ​​udvælgelsesforespørgsler (SELECT); hvis alle de anførte felter er i indekset, er der muligvis slet ikke behov for adgang til tabellen. Men det øger naturligvis indeksets størrelse, pga værdierne af de anførte felter duplikeres i indekset.

Uddrag fra MSDN. Generel kommandosyntaks til oprettelse af indekser

OPRET [UNIKK] [KLUSTERET | IKKE KLUSTERET ] INDEX index_name TIL