Predkladám Vám do pozornosti voľný preklad článku SQL pre začiatočníkov

Čoraz viac moderných webových aplikácií interaguje s databázami, pričom zvyčajne používa daný jazyk SQL... Našťastie pre nás sa tento jazyk dá celkom ľahko naučiť. V tomto článku sa začneme učiť základy SQL dotazov a ako interagujú s databázou. MySQL.

Čo potrebuješ

SQL (Structured Query Language) je jazyk určený na interakciu so systémami správy relačných databáz (DBMS) ako napr. MySQL, Oracle, Sqlite a ďalšie. Na spustenie SQL dotazov z tohto článku predpokladám, že ste nainštalovali MySQL... Tiež odporúčam použiť phpMyAdmin ako nástroj na vizuálne zobrazenie MySQL.

Nasledujúce aplikácie vám uľahčia inštaláciu MySQL a phpMyAdmin do vášho počítača:

• WAMP pre Windows
• MAMP pre Mac

Začnime vykonávať dotazy na príkazovom riadku. WAMP už ho obsahuje v konzole MySQL... Pre MAMP, možno si budete musieť prečítať toto.

CREATE DATABASE: Vytvorenie databázy

Naša úplne prvá požiadavka. Vytvoríme databázu, s ktorou budeme pracovať.

Najprv otvorte konzolu MySQL a prihláste sa. Pre WAMP, predvolene sa používa prázdne heslo. Pre MAMP heslo musí byť "root".

Po prihlásení napíšte túto požiadavku a kliknite Zadajte:

VYTVORIŤ DATABÁZU my_first_db;

Všimnite si, že na koniec dotazu sa pridáva bodkočiarka (;), rovnako ako na koniec riadku vo vašom kóde.

Tiež kľúčové slová VYTVORIŤ DATABÁZU nerozlišujú veľké a malé písmená ako všetky kľúčové slová v SQL... Pre lepšiu čitateľnosť ich ale napíšeme veľkými písmenami.

Poznámka: sada znakov a zoraďovanie

Ak chcete nastaviť predvolenú znakovú sadu a poradie zoraďovania, použite podobný dotaz:

VYTVORIŤ DATABÁZU my_first_db PREDCHOZÍ SET ZNAKOV utf8 COLLATE utf8_general_ci;

Zoznam podporovaných znakových sád a porovnávaní nájdete na MySQL.

ZOBRAZIŤ DATABÁZY: Zoznam všetkých databáz

Tento dotaz sa používa na zobrazenie všetkých databáz.

DROP DATABASE: Zahodí databázu

Pomocou tohto dotazu môžete zrušiť existujúcu databázu.

Pri tomto dopyte buďte opatrní, pretože nezobrazuje žiadne upozornenia. Keď budete mať tabuľky a údaje v databáze, dotaz ich všetky okamžite odstráni.

Technicky povedané, toto nie je požiadavka. Je to "operátor" a nevyžaduje bodkočiarku na konci.

Informuje MySQLže je potrebné vybrať predvolenú databázu a pracovať s ňou až do konca relácie. Teraz sme pripravení vytvoriť tabuľky a zvyšok v tejto databáze.

Čo je databázová tabuľka?

Tabuľku v databáze si môžete predstaviť ako bežnú tabuľku alebo ako súbor csv so štruktúrovanými údajmi.

Ako v tomto príklade, tabuľka má názvy riadkov a stĺpce údajov. Pomocou SQL dotazov môžeme vytvoriť túto tabuľku. Údaje môžeme tiež pridávať, čítať, upravovať a mazať.

CREATE TABLE: Vytvorenie tabuľky

Pomocou tohto dotazu môžeme vytvoriť tabuľku v databáze. Prepáčte, dokumentácia pre MySQL nie je veľmi priateľský k novým používateľom. Štruktúra tohto dotazu môže byť veľmi zložitá, ale začneme jednoduchou.

Nasledujúci dotaz vytvorí tabuľku s dvoma stĺpcami.

Používatelia CREATE TABLE (používateľské meno VARCHAR (20), dátum vytvorenia DATE);

Upozorňujeme, že dopyt môžeme napísať na viacero riadkov a použiť Tab na odsadenie.

Prvý riadok je jednoduchý. Vytvoríme tabuľku s názvom používateľov... Ďalej sú v zátvorkách uvedené stĺpce tabuľky oddelené čiarkami. Za každým názvom stĺpca nasleduje napríklad typ údajov VARCHAR alebo DÁTUM.

VARCHAR (20) znamená, že stĺpec je typu reťazca a nemôže mať viac ako 20 znakov. DÁTUM- dátový typ určený na ukladanie dátumov vo formáte: "RRRR-MM-DD".

Primárny kľúč

Pred vykonaním tohto dotazu musíme vložiť stĺpec ID používateľa, ktorý bude PRIMÁRNYM KĽÚČOM. Bez toho, aby sme zachádzali do prílišných podrobností, môžete si primárny kľúč predstaviť ako spôsob, ako rozpoznať každý riadok údajov v tabuľke.

Žiadosť vyzerá takto:

Používatelia CREATE TABLE (user_id INT AUTO_INCREMENT PRIMARY KEY, meno používateľa VARCHAR (20), dátum vytvorenia DATE);

INT- 32-bitový typ celého čísla (numerický). AUTOMATICKÝ PRÍRASTOK automaticky vytvorí nové identifikačné číslo vždy, keď sa pridá riadok s údajmi. Nie je to potrebné, ale je to s ním pohodlnejšie.

Tento stĺpec nemusí byť celé číslo, hoci ide o najbežnejší typ údajov. Stĺpec primárneho kľúča je voliteľný, ale odporúča sa ho použiť na zlepšenie výkonu a architektúry vašej databázy.

Spustíme dotaz:

ZOBRAZIŤ TABUĽKY: Zoznam všetkých tabuliek

Dotaz vám umožňuje získať zoznam všetkých tabuliek v aktuálnej databáze.

VYSVETLITE: Zobrazte štruktúru tabuľky

Tento dotaz použite na zobrazenie štruktúry existujúcej tabuľky.

V dôsledku toho sa zobrazia polia (stĺpce) a ich vlastnosti.

DROP TABLE: Zahodí stôl

Tiež DOPRAŤ DATABÁZY, tento dotaz vymaže tabuľku a jej obsah bez akéhokoľvek varovania.

ALTER TABLE: Úprava tabuľky

Takýto dotaz môže byť zložitý, pretože môže vykonať viacero zmien v tabuľke. Pozrime sa na niekoľko jednoduchých príkladov.

Vďaka čitateľnosti SQL, táto požiadavka je samozrejmá.

Odstránenie je rovnako jednoduché. Dotaz používajte opatrne, údaje sa vymažú bez varovania.

Znova pridáme pole email, neskôr bude stále potrebné:

Používatelia ALTER TABLE PRIDAŤ e-mail VARCHAR (100) ZA užívateľským menom;

Niekedy možno budete musieť zmeniť vlastnosti stĺpca, nemusíte ho odstraňovať a vytvárať znova.

Táto požiadavka premenuje pole užívateľské meno v user_name a zmení jeho typ z VARCHAR (20) na VARCHAR (30)... Takéto zmeny neovplyvnia údaje v tabuľke.

INSERT: Pridanie údajov do tabuľky

Pridajme záznamy do tabuľky pomocou dotazov.

Ako môžeš vidieť, HODNOTY () obsahuje zoznam hodnôt oddelených čiarkami. Hodnoty reťazca sú uzavreté v jednoduchých úvodzovkách. Hodnoty musia byť v poradí špecifikovanom pri vytváraní tabuľky.

Všimnite si, že prvá hodnota je NULOVÝ pre primárny kľúč, ktorého pole sme pomenovali ID používateľa... Všetko preto, že pole je označené ako AUTOMATICKÝ PRÍRASTOK a id sa vygeneruje automaticky. Prvý riadok údajov bude mať id 1. Ďalší riadok, ktorý sa má pridať, je 2 atď.

Alternatívna syntax

Tu je ďalšia syntax na vkladanie reťazcov.

Tentokrát sme použili kľúčové slovo SET namiesto HODNOTY... Všimnime si niekoľko vecí:

• Stĺpec je možné vynechať. Pole sme napríklad nepriradili hodnotu ID používateľa pretože je označený ako AUTOMATICKÝ PRÍRASTOK... Ak do poľa s typom nepriradíte hodnotu VARCHAR, potom štandardne prevezme hodnotu prázdneho reťazca (pokiaľ nebola pri vytváraní tabuľky určená iná predvolená hodnota).
• Na každý stĺpec možno odkazovať názvom. Polia teda môžu byť v akomkoľvek poradí, na rozdiel od predchádzajúcej syntaxe.

Alternatívne číslo syntaxe 2

Tu je ďalší príklad.

Rovnako ako predtým, na polia môžete odkazovať podľa názvu, môžu ísť v ľubovoľnom poradí.

Tento dotaz použite na získanie ID posledného vloženého riadku.

TERAZ ()

Je čas ukázať vám, ako používať funkcie MySQL v žiadostiach.

Funkcia TERAZ () vráti aktuálny dátum. Použite ho na automatické pridanie aktuálneho dátumu do poľa typu DÁTUM.

Upozorňujeme, že sme dostali varovanie od MySQL ale to nie je až také dôležité. Dôvodom je funkcia TERAZ () vlastne vracia časovú informáciu.

Vytvorili sme pole dátum_vytvorenia ktorý môže obsahovať iba dátum a nie čas, takže údaje boli skrátené. Namiesto TERAZ () by sme mohli použiť AKTUÁLNE () ktorý vráti iba aktuálny dátum, ale konečný výsledok by bol rovnaký.

SELECT: Načítanie údajov z tabuľky

Je zrejmé, že údaje, ktoré sme napísali, sú zbytočné, kým ich nedokážeme prečítať. Na záchranu prichádza žiadosť VYBRAŤ.

Najjednoduchší príklad použitia žiadosti VYBRAŤ na čítanie údajov z tabuľky:

Hviezdička (*) znamená, že chceme získať všetky stĺpce v tabuľke. Ak potrebujete získať iba určité stĺpce, použite niečo takéto:

Častejšie chceme získať iba určité riadky, nie všetky. Napríklad získajme e-mailovú adresu používateľa nettuts.

Je to podobné ako podmienka IF. WHERE vám umožňuje nastaviť podmienku v dotaze a získať požadovaný výsledok.

Pre podmienku rovnosti sa používa jednoduché znamienko (=), nie dvojité (==), ktoré pravdepodobne používate pri programovaní.

Môžete použiť aj ďalšie podmienky:

A a ALEBO sa používajú na kombináciu podmienok:

Upozorňujeme, že číselné hodnoty nemusia byť uzavreté v úvodzovkách.

IN ()

Používa sa na porovnanie s viacerými hodnotami.

PÁČI SA MI TO

Umožňuje vám nastaviť šablónu pre vyhľadávanie.

Znak percenta (%) sa používa na určenie vzoru.

ORDER BY doložkou

Túto podmienku použite, ak chcete, aby sa výsledok vrátil zoradený:

Predvolené poradie je ASC(Vzostupne). Pridať DESC triediť v opačnom poradí.

LIMIT ... OFFSET ...

Môžete obmedziť počet vrátených riadkov.

LIMIT 2 berie prvé dva riadky. LIMIT 1 OFFSET 2 zaberie jeden riadok po prvých dvoch. LIMIT 2, 1 znamená to isté, iba prvé číslo je posun a druhé obmedzuje počet riadkov.

AKTUALIZÁCIA: Aktualizácia údajov v tabuľke

Tento dotaz sa používa na aktualizáciu údajov v tabuľke.

Väčšinou používané v spojení s KDE aktualizovať konkrétne riadky. Ak je podmienka KDE nezadané, zmeny sa použijú na všetky riadky.

Na obmedzenie meniteľných reťazcov môžete použiť LIMIT.

DELETE: Odstránenie údajov z tabuľky

Tiež , tento dotaz sa často používa v spojení s podmienkou KDE.

SKRAŤ TABUĽKU

Ak chcete odstrániť obsah z tabuľky, použite dotaz, ako je tento:

VYMAZAŤ Z používateľov;

Pre lepší výkon použitia .

Tiež vynuluje počítadlo poľa AUTOMATICKÝ PRÍRASTOK, takže novo pridané riadky budú mať ID rovné 1. Pri použití to sa nestane a počítadlo bude naďalej rásť.

Únikové hodnoty reťazca a špeciálne slová

Hodnoty reťazca

Niektoré postavy je potrebné utiecť, inak môžu nastať problémy.

Opačná lomka (\) sa používa na escapovanie.

Je to veľmi dôležité z bezpečnostných dôvodov. Všetky používateľské údaje musia byť pred zápisom do databázy escapované. V PHP použite funkciu mysql_real_escape_string () alebo pripravené príkazy.

Špeciálne slová

Keďže v r MySQL veľa rezervovaných slov ako VYBRAŤ alebo Aby ste sa vyhli nejasnostiam, uzavrite názvy stĺpcov a tabuliek do úvodzoviek. Okrem toho musíte používať nie obyčajné úvodzovky, ale spätné začiarknutia (`).

Povedzme, že z nejakého dôvodu chcete pridať stĺpec s názvom :

Záver

Ďakujem za prečítanie článku. Dúfam, že som vám ten jazyk mohol ukázať SQL veľmi funkčné a ľahko sa učí.

Tento tutoriál je niečo ako "pečiatka mojej pamäte" pre jazyk SQL (DDL, DML), t.j. sú to informácie, ktoré sa nahromadili počas mojej profesionálnej činnosti a neustále sa mi ukladajú v hlave. To je pre mňa postačujúce minimum, ktoré sa pri práci s databázami využíva najčastejšie. Ak je potrebné použiť úplnejšie konštrukty SQL, zvyčajne sa obrátim na knižnicu MSDN umiestnenú na internete. Podľa môjho názoru je veľmi ťažké udržať všetko v hlave a nie je to potrebné. Ale je veľmi užitočné poznať základné konštrukcie, pretože sú použiteľné v takmer rovnakej forme v mnohých relačných databázach ako Oracle, MySQL, Firebird. Rozdiely sú najmä v dátových typoch, ktoré sa môžu v detailoch líšiť. Základných konštrukcií jazyka SQL nie je až tak veľa a pri neustálom precvičovaní sa rýchlo zapamätajú. Napríklad na vytváranie objektov (tabuľky, obmedzenia, indexy atď.) stačí mať po ruke textový editor prostredia (IDE) na prácu s databázou a nie je potrebné študovať vizuálne nástroje zaostrené na prácu so špecifickým typom databázy (MS SQL, Oracle, MySQL, Firebird, ...). Je to pohodlné aj preto, že všetok text máte na očiach a nemusíte behať cez množstvo kariet, aby ste vytvorili napríklad index alebo obmedzenie. Pri neustálej práci s databázou je vytváranie, zmena a najmä opätovné vytváranie objektu pomocou skriptov mnohonásobne rýchlejšie, ako keby sa to dialo vo vizuálnom režime. Aj v režime skriptu (teda s náležitou starostlivosťou) je jednoduchšie nastaviť a ovládať pravidlá pre pomenovanie objektov (môj subjektívny názor). Okrem toho je vhodné použiť skripty, keď zmeny vykonané v jednej databáze (napríklad testovacej) je potrebné preniesť v rovnakej forme do inej (produktívnej) databázy.

Jazyk SQL je rozdelený do niekoľkých častí, tu rozoberiem jeho 2 najdôležitejšie časti:

• DML - Data Manipulation Language, ktorý obsahuje nasledujúce konštrukcie:
  • SELECT - výber údajov
  • INSERT - vloženie nových údajov
  • UPDATE - aktualizácia údajov
  • DELETE - vymazanie údajov
  • MERGE - zlúčenie dát

Pretože Som praktik, v tomto návode bude málo teórie ako takej a všetky konštrukcie budú vysvetlené na praktických príkladoch. Okrem toho verím, že programovací jazyk a najmä SQL sa dá v praxi zvládnuť len tak, že ho precítite a pochopíte, čo sa stane, keď spustíte tú či onú konštrukciu.

Tento tutoriál je založený na princípe Krok za krokom, t.j. je potrebné čítať postupne a najlepšie hneď po príkladoch. Ak sa však počas cesty potrebujete dozvedieť viac o príkaze podrobnejšie, použite špecifické vyhľadávanie na internete, napríklad v knižnici MSDN.

Pri písaní tohto návodu bola použitá databáza MS SQL Server verzie 2014, na spúšťanie skriptov som použil MS SQL Server Management Studio (SSMS).

Stručne o MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) je nástroj pre Microsoft SQL Server na konfiguráciu, správu a správu databázových komponentov. Tento nástroj obsahuje editor skriptov (ktorý budeme hlavne používať) a grafický program, ktorý pracuje s objektmi a nastaveniami servera. Hlavným nástrojom v SQL Server Management Studio je Object Explorer, ktorý umožňuje používateľovi prezerať, získavať a manipulovať s objektmi servera. Tento text je čiastočne prevzatý z Wikipédie.

Ak chcete vytvoriť nový editor skriptov, použite tlačidlo Nový dotaz:

Ak chcete zmeniť aktuálnu databázu, môžete použiť rozbaľovací zoznam:

Ak chcete vykonať konkrétny príkaz (alebo skupinu príkazov), vyberte ho a stlačte tlačidlo Vykonať alebo kláves F5. Ak je v editore momentálne iba jeden príkaz alebo potrebujete vykonať všetky príkazy, nemusíte nič vyberať.

Po spustení skriptov, najmä tých, ktoré vytvárajú objekty (tabuľky, stĺpce, indexy), sa zmeny zobrazia pomocou aktualizácie z kontextového menu, zvýraznením príslušnej skupiny (napríklad Tabuľky), samotnej tabuľky alebo skupiny Columns v nej.

V skutočnosti je to všetko, čo potrebujeme vedieť, aby sme mohli vykonať tu uvedené príklady. Zvyšok nástroja SSMS sa ľahko naučíte sami.

Trochu teórie

Relačná databáza (RDB, alebo ďalej v kontexte len databáza) je kolekcia tabuliek, ktoré sú vzájomne prepojené. Zhruba povedané, databáza je súbor, v ktorom sú dáta uložené v štruktúrovanej forme.

DBMS je riadiaci systém pre tieto databázy, t.j. ide o súbor nástrojov pre prácu s konkrétnym typom databázy (MS SQL, Oracle, MySQL, Firebird, ...).

Poznámka
Pretože v reálnom živote, v hovorovej reči, väčšinou hovoríme: "Oracle DB", alebo dokonca len "Oracle", čo v skutočnosti znamená "Oracle DBMS", potom sa v kontexte tohto tutoriálu niekedy použije termín DB. Z kontextu bude myslím jasné, o čo ide.

Tabuľka je zbierka stĺpcov. Stĺpce môžu byť tiež nazývané polia alebo stĺpce, všetky tieto slová sa budú používať ako synonymá na vyjadrenie toho istého.

Tabuľka je hlavným objektom RDB, všetky údaje RDB sú uložené riadok po riadku v stĺpcoch tabuľky. Reťazce, záznamy sú tiež synonymá.

Pre každú tabuľku, ako aj pre jej stĺpce sú uvedené názvy, pomocou ktorých sa k nim následne pristupuje.
Názov objektu (názov tabuľky, názov stĺpca, názov indexu atď.) v MS SQL môže mať maximálnu dĺžku 128 znakov.

Pre referenciu- v databáze ORACLE môžu mať názvy objektov maximálnu dĺžku 30 znakov. Preto si pre konkrétnu databázu musíte vypracovať vlastné pravidlá pre pomenovanie objektov, aby ste dodržali limit na počet znakov.

SQL je jazyk, ktorý vám umožňuje vykonávať dotazy v databáze pomocou DBMS. V špecifickom DBMS môže mať jazyk SQL špecifickú implementáciu (vlastný dialekt).

DDL a DML sú podmnožinou jazyka SQL:

• Jazyk DDL sa používa na vytváranie a úpravu štruktúry databázy, t.j. vytvárať / upravovať / mazať tabuľky a odkazy.
• Jazyk DML umožňuje manipuláciu s údajmi tabuľky, t.j. s jej líniami. Umožňuje vám vyberať údaje z tabuliek, pridávať do tabuliek nové údaje a aktualizovať a odstraňovať existujúce údaje.

V SQL sa dajú použiť 2 typy komentárov (jednoriadkové a viacriadkové):

Jednoriadkový komentár
a

/ * viacriadkový komentár * /

V skutočnosti to na teóriu bude stačiť.

DDL - Data Definition Language

Zoberme si napríklad tabuľku s údajmi o zamestnancoch vo forme obvyklej pre osobu, ktorá nie je programátorom:

V tomto prípade majú stĺpce tabuľky tieto názvy: Osobné číslo, celé meno, Dátum narodenia, E-mail, Pozícia, Oddelenie.

Každý z týchto stĺpcov možno charakterizovať typom údajov, ktoré obsahuje:

• Osobné číslo - celé číslo
• Celé meno - reťazec
• Dátum narodenia - dátum
• E-mail - reťazec
• Pozícia - reťazec
• Oddelenie - reťazec

Typ stĺpca je charakteristika, ktorá označuje, aký druh údajov môže daný stĺpec uchovávať.

Na začiatok bude stačiť zapamätať si iba tieto základné dátové typy používané v MS SQL:

Význam	Notácia MS SQL	Popis
Šnúrka s premenlivou dĺžkou	varchar (N) a nvarchar (N)	Pomocou čísla N môžeme určiť maximálnu možnú dĺžku riadku pre príslušný stĺpec. Napríklad, ak chceme povedať, že hodnota stĺpca „name“ môže obsahovať maximálne 30 znakov, tak ju musíme nastaviť na typ nvarchar (30). Rozdiel medzi varchar a nvarchar je v tom, že varchar ukladá reťazce vo formáte ASCII, kde jeden znak je 1 bajt, a nvarchar ukladá reťazce vo formáte Unicode, kde každý znak má 2 bajty. Typ varchar by ste mali použiť iba vtedy, ak ste si 100% istý, že pole nepotrebuje ukladať znaky Unicode. Napríklad varchar možno použiť na ukladanie e-mailových adries, pretože zvyčajne obsahujú iba znaky ASCII.
Šnúrka s pevnou dĺžkou	znak (N) a nchar (N)	Tento typ sa líši od reťazca s premennou dĺžkou tým, že ak je reťazec kratší ako N znakov, potom sa vždy doplní doprava na dĺžku N medzerami a v tejto podobe sa uloží do databázy, t.j. zaberá presne N znakov v databáze (kde jeden znak zaberá 1 bajt pre char a 2 bajty pre nchar). V mojej praxi sa tento typ používa veľmi zriedkavo a ak sa používa, tak sa používa najmä vo formáte char (1), t.j. keď je pole definované jedným znakom.
Celé číslo	int	Tento typ nám umožňuje používať v stĺpci iba celé čísla, kladné aj záporné. Pre informáciu (teraz to nie je pre nás také podstatné) - rozsah čísel, ktoré typ int umožňuje, je od -2 147 483 648 do 2 147 483 647. Zvyčajne ide o hlavný typ, ktorý sa používa na nastavenie identifikátorov.
Skutočné alebo skutočné číslo	plavák	Zjednodušene povedané, ide o čísla, v ktorých môže byť prítomná desatinná čiarka.
dátum	dátum	Ak potrebujete uložiť iba Dátum do stĺpca, ktorý pozostáva z troch komponentov: Číslo, Mesiac a Rok. Napríklad 15.02.2014 (15.2.2014). Tento typ je možné použiť pre stĺpec „Dátum prijatia“, „Dátum narodenia“ atď. v prípadoch, keď je pre nás dôležité opraviť iba dátum, alebo keď časová zložka nie je pre nás dôležitá a možno ju vyradiť alebo ak nie je známa.
čas	čas	Tento typ je možné použiť, ak je potrebné do stĺpca uložiť len časové údaje, t.j. Hodiny, minúty, sekundy a milisekundy. Napríklad 17:38: 31,3231603 Napríklad denný "Flight Departure Time".
dátum a čas	Dátum Čas	Tento typ vám umožňuje súčasne uložiť dátum aj čas. Napríklad 15.02.2014 17:38: 31.323 Môže to byť napríklad dátum a čas udalosti.
Vlajka	trocha	Tento typ je vhodný na ukladanie hodnôt ako „Áno“ / „Nie“, kde „Áno“ bude uložené ako 1 a „Nie“ bude uložené ako 0.

Taktiež nesmie byť špecifikovaná hodnota poľa, ak to nie je zakázané, na tento účel sa používa kľúčové slovo NULL.

Ak chcete spustiť príklady, vytvorte základ testu s názvom Test.

Jednoduchú databázu (bez zadania ďalších parametrov) je možné vytvoriť spustením nasledujúceho príkazu:

Test VYTVORENIA DATABÁZY
Databázu môžete vymazať príkazom (pri tomto príkaze by ste mali byť veľmi opatrní):

Test DROP DATABASE
Ak chcete prejsť do našej databázy, môžete spustiť príkaz:

Test POUŽITIA
Prípadne vyberte Testovať databázu z rozbaľovacieho zoznamu v oblasti ponuky SSMS. Pri práci často používam tento spôsob prepínania medzi základňami.

Teraz v našej databáze môžeme vytvoriť tabuľku pomocou popisov tak, ako sú, pomocou medzier a znakov cyriliky:

VYTVORIŤ TABUĽKU [Zamestnanci] ([Osobné číslo] int, [Celé meno] nvarchar (30), [Dátum narodenia] dátum, nvarchar (30), [Pozícia] nvarchar (30), [Oddelenie] nvarchar (30))
V tomto prípade musíme mená uzavrieť do hranatých zátvoriek [...].

V databáze je však pre väčšie pohodlie lepšie špecifikovať všetky názvy objektov v latinskej abecede a nepoužívať v názvoch medzery. V MS SQL zvyčajne v tomto prípade každé slovo začína veľkým písmenom, napríklad pre pole „Personálne číslo“ by sme mohli nastaviť názov PersonnelNumber. V mene môžete použiť aj čísla, napríklad PhoneNumber1.

Na poznámku
V niektorých DBMS môže byť vhodnejší nasledujúci formát pomenovania „PHONE_NUMBER“, napríklad tento formát sa často používa v databáze ORACLE. Prirodzene, pri zadávaní názvu poľa je žiaduce, aby sa nezhodoval s kľúčovými slovami používanými v DBMS.

Z tohto dôvodu môžete zabudnúť na syntax hranatých zátvoriek a vymazať tabuľku [Zamestnanci]:

ZOBRAZIŤ TABUĽKU [Zamestnanci]
Napríklad tabuľka so zamestnancami môže byť pomenovaná „Zamestnanci“ a jej polia môžu byť pomenované takto:

• ID – osobné číslo (ID zamestnanca)
• Meno - celé meno
• Narodeniny - Dátum narodenia
• Email – E-mail
• Pozícia - Pozícia
• Oddelenie - Oddelenie

Veľmi často sa na pomenovanie poľa identifikátora používa slovo ID.

Teraz si vytvoríme tabuľku:

VYTVORIŤ TABUĽKU Zamestnanci (ID int, meno nvarchar (30), dátum narodenia, e-mail nvarchar (30), pozícia nvarchar (30), oddelenie nvarchar (30))
Na zadanie požadovaných stĺpcov môžete použiť voľbu NOT NULL.

Pre existujúcu tabuľku je možné polia predefinovať pomocou nasledujúcich príkazov:

Aktualizácia poľa ID ALTER TABLE Zamestnanci ALTER COLUMN ID int NOT NULL - Aktualizácia poľa Názov ALTER TABLE Zamestnanci ALTER COLUMN Name nvarchar (30) NOT NULL

Na poznámku
Všeobecný koncept jazyka SQL pre väčšinu DBMS zostáva rovnaký (aspoň to môžem posúdiť podľa DBMS, s ktorými som pracoval). Rozdiel medzi DDL v rôznych DBMS spočíva najmä v dátových typoch (tu sa môžu líšiť nielen ich názvy, ale aj detaily implementácie), mierne sa môžu líšiť aj špecifiká implementácie jazyka SQL (tj podstata príkazy sú rovnaké, ale môžu existovať malé rozdiely v dialekte, bohužiaľ, neexistuje jeden štandard). Keď poznáte základy SQL, môžete ľahko prechádzať z jedného DBMS na druhý, pretože v tomto prípade stačí pochopiť detaily implementácie príkazov v novom DBMS, t.j. vo väčšine prípadov postačí jednoduché prirovnanie.

Vytvoriť tabuľku VYTVORIŤ TABUĽKU Zamestnanci (ID int, - v ORACLE je typ int ekvivalentom (obal) pre číslo (38) Meno nvarchar2 (30), - nvarchar2 v ORACLE je ekvivalentom nvarchar v MS SQL Dátum narodenia, E-mail nvarchar2 (30 ) , Pozícia nvarchar2 (30), Oddelenie nvarchar2 (30)); - aktualizácia polí ID a Name (tu sa používa MODIFY (…) namiesto ALTER COLUMN) ALTER TABLE Zamestnanci MODIFY (ID int NOT NULL, Name nvarchar2 (30) NOT NULL); - pridanie PK (v tomto prípade konštrukcia vyzerá ako v MS SQL, bude zobrazená nižšie) ALTER TABLE Zamestnanci ADD OBMEDZENIE PK_Zamestnanci PRIMÁRNY KĽÚČ (ID);
Pre ORACLE sú rozdiely z hľadiska implementácie typu varchar2, jeho kódovanie závisí od nastavenia databázy a text je možné uložiť napríklad v kódovaní UTF-8. Okrem toho, dĺžka poľa v ORACLE môže byť nastavená v bajtoch aj v znakoch, na to sa používajú ďalšie možnosti BYTE a CHAR, ktoré sú špecifikované za dĺžkou poľa, napríklad:

NAME varchar2 (30 BYTE) - kapacita poľa bude 30 bajtov NAME varchar2 (30 CHAR) - kapacita poľa bude 30 znakov
Ktorá možnosť bude štandardne použitá BYTE alebo CHAR, v prípade jednoduchej indikácie typu varchar2 (30) v ORACLE závisí od nastavenia databázy, dá sa to niekedy nastaviť aj v nastaveniach IDE. Vo všeobecnosti sa niekedy môžete ľahko zmiasť, takže v prípade ORACLE, ak sa používa typ varchar2 (a to je tu niekedy opodstatnené, napríklad pri použití kódovania UTF-8), radšej výslovne napíšem CHAR ( pretože je zvyčajne pohodlnejšie čítať dĺžku reťazca v znakoch).

Ale v tomto prípade, ak tabuľka už obsahuje nejaké údaje, potom pre úspešné vykonanie príkazov je potrebné, aby boli vyplnené polia ID a Name vo všetkých riadkoch tabuľky. Ukážme si to na príklade, vložte údaje do tabuľky do polí ID, Pozícia a Oddelenie, to sa dá urobiť pomocou nasledujúceho skriptu:

VLOŽTE HODNOTY zamestnancov (IČO, Pozícia, Oddelenie) (1000, N "Riaditeľ", N "Administratíva"), (1001, N "Programátor", N "IT"), (1002, N "Účtovník", N "Účtovníctvo" ), (1003, N "Senior Programátor", N "IT")
V tomto prípade príkaz INSERT tiež vygeneruje chybu, pretože pri vkladaní sme neuviedli hodnotu požadovaného poľa Názov.
Ak by sme už tieto údaje mali v pôvodnej tabuľke, tak príkaz „ALTER TABLE Zamestnanci ALTER COLUMN ID int NOT NULL“ by bol úspešný a príkaz „ALTER TABLE Zamestnanci ALTER COLUMN Name int NOT NULL“ by vypísal chybové hlásenie, že v poli Názov sú hodnoty NULL (nešpecifikované).

Pridajte hodnoty do poľa Názov a znova vyplňte údaje:

Taktiež možnosť NOT NULL je možné použiť priamo pri vytváraní novej tabuľky, t.j. v kontexte príkazu CREATE TABLE.

Najprv vymažte tabuľku pomocou príkazu:

DROP TABLE Zamestnanci
Teraz vytvorte tabuľku s povinnými stĺpcami ID a Názov:

VYTVORIŤ TABUĽKU Zamestnanci (ID int NOT NULL, Meno nvarchar (30) NOT NULL, Dátum narodenia, Email nvarchar (30), Pozícia nvarchar (30), Oddelenie nvarchar (30))
Za názov stĺpca môžete napísať aj hodnotu NULL, čo bude znamenať, že v ňom budú povolené hodnoty NULL (nešpecifikované), ale nie je to potrebné, pretože táto charakteristika sa štandardne predpokladá.

Ak je naopak potrebné, aby bol existujúci stĺpec voliteľný na vyplnenie, potom použijeme nasledujúcu syntax príkazu:

ALTER TABLE Zamestnanci ALTER STĹPEC Názov nvarchar (30) NULL
Alebo jednoducho:

ALTER TABLE Zamestnanci ALTER STĹPEC Meno nvarchar (30)
Taktiež pomocou tohto príkazu môžeme zmeniť typ poľa na iný kompatibilný typ, prípadne zmeniť jeho dĺžku. Rozšírme napríklad pole Názov na 50 znakov:

ALTER TABLE Zamestnanci ALTER STĹPEC Meno nvarchar (50)

Primárny kľúč

Pri vytváraní tabuľky je žiaduce, aby mala jedinečný stĺpec alebo množinu stĺpcov, ktorá je jedinečná pre každý jej riadok – podľa tejto jedinečnej hodnoty je možné záznam jednoznačne identifikovať. Táto hodnota sa nazýva primárny kľúč tabuľky. Pre našu tabuľku Zamestnanci môže byť takouto jedinečnou hodnotou stĺpec ID (ktorý obsahuje „Personálne číslo zamestnanca“ – aj keď v našom prípade je táto hodnota pre každého zamestnanca jedinečná a nemožno ju opakovať).

Primárny kľúč k existujúcej tabuľke môžete vytvoriť pomocou príkazu:

ALTER TABLE Zamestnanci ADD CONSTRAINT PK_Employees PRIMÁRNY KĽÚČ (ID)
Kde „PK_Employees“ je názov obmedzenia zodpovedného za primárny kľúč. Na pomenovanie primárneho kľúča sa zvyčajne používa predpona „PK_“, za ktorou nasleduje názov tabuľky.

Ak primárny kľúč pozostáva z niekoľkých polí, tieto polia musia byť uvedené v zátvorkách oddelené čiarkami:

ALTER TABLE table_name ADD CONSTRAINT názov_obmedzenia PRIMÁRNY KĽÚČ (pole1, pole2, ...)
Stojí za zmienku, že v MS SQL všetky polia, ktoré sú zahrnuté v primárnom kľúči, nesmú byť NULL.

Taktiež primárny kľúč je možné definovať priamo pri vytváraní tabuľky, t.j. v kontexte príkazu CREATE TABLE. Vymažeme tabuľku:

DROP TABLE Zamestnanci
A potom ho vytvorte pomocou nasledujúcej syntaxe:

VYTVORIŤ TABUĽKU Zamestnanci (ID int NIE JE NULL, Meno nvarchar (30) NIE JE NULL, Dátum narodenia, Email nvarchar (30), Pozícia nvarchar (30), Oddelenie nvarchar (30), OBMEDZENIE PK_Zamestnanci PRIMÁRNY KĽÚČ (ID) - popíšte predsa PK polia ako obmedzenie)
Po vytvorení vyplňte údaje do tabuľky:

VLOŽIŤ Zamestnanci (IČO, Pozícia, Oddelenie, Meno) HODNOTY (1000, N "Riaditeľ", N "Administratíva", N "Ivanov I.I."), (1001, N "Programátor", N "IT", N " Petrov PP " ), (1002, N" účtovník ", N" účtovníctvo ", N" Sidorov SS "), (1003, N" hlavný programátor ", N" IT ", N" Andreev A. A.")
Ak primárny kľúč v tabuľke pozostáva iba z hodnôt jedného stĺpca, možno použiť nasledujúcu syntax:

VYTVORIŤ TABUĽKU Zamestnanci (ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, - špecifikujte ako charakteristiku poľa Meno nvarchar (30) NOT NULL, Dátum narodenia, Email nvarchar (30), Pozícia nvarchar (30), Oddelenie nvarchar (30))
V skutočnosti môže byť názov obmedzenia vynechaný, v takom prípade mu bude priradený systémový názov (napríklad „PK__Employee__3214EC278DA42077“):

VYTVORIŤ TABUĽKU Zamestnanci (ID int NOT NULL, Meno nvarchar (30) NOT NULL, Dátum narodenia, E-mail nvarchar (30), Pozícia nvarchar (30), Oddelenie nvarchar (30), PRIMÁRNY KĽÚČ (ID))
alebo:

VYTVORIŤ TABUĽKU Zamestnanci (ID int NIE JE NULL PRIMÁRNY KĽÚČ, Meno nvarchar (30) NIE JE NULL, Dátum narodenia, E-mail nvarchar (30), Pozícia nvarchar (30), Oddelenie nvarchar (30))
Odporúčam však vždy explicitne špecifikovať názov obmedzenia pre trvalé tabuľky, pretože explicitne daným a zrozumiteľným názvom, bude s ním následne jednoduchšie manipulovať, napríklad ho môžete vymazať:

ALTER TABLE Zamestnanci DROP CONSTRAINT PK_Employees
Ale takú krátku syntax bez uvedenia názvov obmedzení je vhodné použiť pri vytváraní dočasných databázových tabuliek (názov dočasnej tabuľky začína # alebo ##), ktoré sa po použití vymažú.

Poďme si to zhrnúť

Doteraz sme pokryli nasledujúce príkazy:

• VYTVORIŤ TABUĽKU názov_tabuľky (vyčíslenie polí a ich typov, obmedzenia) - slúži na vytvorenie novej tabuľky v aktuálnej databáze;
• DOP TABLE table_name - slúži na vymazanie tabuľky z aktuálnej databázy;
• ALTER TABLE názov_tabuľky ZMENIŤ STĹPEC názov_stĺpca… - používa sa na aktualizáciu typu stĺpca alebo na zmenu jeho nastavení (napríklad na určenie charakteristiky NULL alebo NOT NULL);
• ALTER TABLE názov_tabuľky PRIDAŤ OBMEDZENIE názov_obmedzenia PRIMÁRNY KĽÚČ(pole1, pole2, ...) - pridanie primárneho kľúča do existujúcej tabuľky;
• ALTER TABLE názov_tabuľky OBMEDZENIE DROP názov_obmedzenia - odstráni obmedzenie z tabuľky.

Trochu o dočasných stoloch

Výňatok z MSDN. V MS SQL Server existujú dva typy dočasných tabuliek: lokálne (#) a globálne (##). Lokálne dočasné tabuľky sú viditeľné iba pre ich tvorcov, kým sa neskončí pripojenie k inštancii SQL Server, hneď ako sú prvýkrát vytvorené. Lokálne dočasné tabuľky sú automaticky zrušené po odpojení používateľa od inštancie SQL Server. Globálne dočasné tabuľky sú viditeľné pre všetkých používateľov počas akýchkoľvek relácií pripojenia po vytvorení týchto tabuliek a sú zrušené, keď sa všetci používatelia, ktorí odkazujú na tieto tabuľky, odpoja od inštancie SQL Server.

V systémovej databáze tempdb sa vytvárajú dočasné tabuľky, t.j. ich vytvorením nezasypávame hlavnú základňu, inak sú dočasné stoly úplne totožné s obyčajnými stolmi, dajú sa aj zahodiť príkazom DROP TABLE. Bežnejšie sa používajú lokálne (#) dočasné tabuľky.

Na vytvorenie dočasnej tabuľky môžete použiť príkaz CREATE TABLE:

VYTVORIŤ TABUĽKU #Temp (ID int, názov nvarchar (30))
Keďže dočasná tabuľka v MS SQL je podobná bežnej tabuľke, môžete ju zrušiť aj sami pomocou príkazu DROP TABLE:

DROP TABLE #Temp

Tiež je možné vytvoriť dočasnú tabuľku (ako samotná riadna tabuľka) a okamžite ju vyplniť údajmi vrátenými dotazom pomocou syntaxe SELECT ... INTO:

VYBERTE ID, Meno DO #Temp FROM Zamestnancov

Na poznámku
Implementácia dočasných tabuliek sa môže v rôznych DBMS líšiť. Napríklad v ORACLE a Firebird DBMS musí byť štruktúra dočasných tabuliek vopred definovaná príkazom CREATE GLOBAL TEMPORARY TABLE s uvedením špecifík ukladania údajov do nej, potom ju používateľ vidí medzi hlavnými tabuľkami a pracuje s ňou ako s obyčajný stôl.

Normalizácia databázy - rozdelenie do podtabuľiek (referenčných kníh) a definovanie vzťahov

Naša súčasná tabuľka Zamestnanci má nevýhodu v tom, že do polí Pozícia a Oddelenie môže používateľ zadať ľubovoľný text, ktorý je primárne plný chýb, keďže jeden zamestnanec môže jednoducho zadať „IT“ ako oddelenie a druhý zamestnanec napr. zadajte „IT oddelenie“, na treťom „IT“. V dôsledku toho bude nejasné, čo tým používateľ myslel, t.j. Sú títo zamestnanci zamestnancami toho istého oddelenia, alebo je používateľ opísaný sám a ide o 3 rôzne oddelenia? Navyše v tomto prípade nebudeme môcť správne zoskupiť údaje pre niektoré zostavy, kde môže byť potrebné zobraziť počet zamestnancov v kontexte každého oddelenia.

Druhou nevýhodou je množstvo uloženia týchto informácií a ich duplicita, t.j. pri každom zamestnancovi je uvedený celý názov oddelenia, čo si vyžaduje miesto v databáze na uloženie každého znaku z názvu oddelenia.

Treťou nevýhodou je zložitosť aktualizácie týchto polí, ak sa zmení názov pozície, napríklad ak potrebujete premenovať pozíciu „Programátor“ na „Junior Programmer“. V tomto prípade budeme musieť vykonať zmeny v každom riadku tabuľky, v ktorom sa pozícia rovná "Programátor".

Aby sa predišlo týmto nedostatkom, používa sa takzvaná normalizácia databázy – jej rozdelenie na podtabuľky, referenčné tabuľky. Netreba zachádzať do džungle teórie a študovať, čo sú normálne formy, stačí pochopiť podstatu normalizácie.

Vytvorme si 2 tabuľky „Pozície“ a „Útvary“, prvá sa bude volať Pozície a druhá Útvary:

VYTVORIŤ TABUĽKU Pozície (ID int IDENTITA (1,1) NIE JE NULL OBMEDZENIE PK_Positions PRIMÁRNY KĽÚČ, Názov nvarchar (30) NIE JE NULL) VYTVORENIE TABUĽKY Oddelenia (ID int IDENTITY (1,1) NIE JE NULL OBMEDZENIE PK_Oddelenia PRIMÁRNY KĽÚČ (30nvar) ) NIE JE NULL)
Všimnite si, že tu sme použili novú možnosť IDENTITA, ktorá hovorí, že údaje v stĺpci ID sa budú číslovať automaticky od 1 s krokom 1, t.j. pri pridávaní nových záznamov im budú postupne priradené hodnoty 1, 2, 3 atď. Takéto polia sa bežne označujú ako automatické zvyšovanie. V tabuľke môže byť definované iba jedno pole s vlastnosťou IDENTITY a zvyčajne, ale nie nevyhnutne, je takéto pole primárnym kľúčom pre túto tabuľku.

Na poznámku
V rôznych DBMS môže byť implementácia polí s počítadlom vykonaná vlastným spôsobom. Napríklad v MySQL je takéto pole definované pomocou voľby AUTO_INCREMENT. V skorších verziách ORACLE a Firebird bolo možné túto funkciu emulovať pomocou SEQUENCE. Ale pokiaľ viem, ORACLE teraz pridal možnosť GENERATED AS IDENTITY.

Vyplňte tieto tabuľky automaticky na základe aktuálnych údajov zaznamenaných v poliach Pozícia a Oddelenie v tabuľke Zamestnanci:

Vyplňte pole Názov v tabuľke Pozície jedinečnými hodnotami z poľa Pozícia v tabuľke Zamestnanci VLOŽTE Pozície (Názov) VYBERTE ODDIELNU Pozíciu OD zamestnancov, KDE pozícia NIE JE NULL - vyraďte záznamy, ktorých pozícia nie je zadaná
Urobme to isté pre tabuľku oddelení:

INSERT Departments (Name) SELECT DISTINCT Department FROM Zamestnanci, KDE oddelenie NENÍ NULL
Ak teraz otvoríme tabuľky Pozície a Oddelenia, uvidíme očíslovanú množinu hodnôt pre pole ID:

VYBERTE * Z pozícií

VYBERTE * Z oddelení

Tieto tabuľky budú teraz zohrávať úlohu referenčných kníh na priraďovanie pozícií a oddelení. Teraz sa budeme odvolávať na ID práce a oddelenia. Najprv vytvorte nové polia v tabuľke Zamestnanci na uloženie údajov identifikátora:

Pridajte pole pre ID pozície ALTER TABLE Zamestnanci ADD PositionID int - pridajte pole pre ID oddelenia ALTER TABLE Zamestnanci ADD DepartmentID int
Typ referenčných polí by mal byť rovnaký ako v odkazoch, v tomto prípade je to int.

Do tabuľky môžete tiež pridať niekoľko polí naraz pomocou jedného príkazu, pričom polia budú oddelené čiarkami:

ALTER TABLE Zamestnanci ADD PositionID int, DepartmentID int
Teraz pre tieto polia napíšeme odkazy (referenčné obmedzenia - FOREIGN KEY), aby používateľ nemohol do týchto polí zapísať hodnoty, ktoré chýbajú medzi hodnotami ID nájdenými v referenčných knihách.

ALTER TABLE Zamestnanci PRIDAŤ OBMEDZENIE FK_Employees_PositionID CUDZÍ KĽÚČ (ID pozície) REFERENCIE Pozície (ID)
A to isté urobíme pre druhé pole:

ALTER TABLE Zamestnanci PRIDAŤ OBMEDZENIE FK_Employees_DepartmentID CUDZÍ KĽÚČ (ID oddelenia) REFERENCIE Oddelenia (ID)
Teraz bude môcť používateľ do týchto polí zadať iba hodnoty ID z príslušného adresára. Preto, aby mohol použiť nové oddelenie alebo pozíciu, bude musieť najprv pridať nový záznam do príslušného adresára. Pretože pozície a oddelenia sú teraz uložené v adresároch v jednej kópii, potom na zmenu názvu stačí zmeniť iba v adresári.

Názov referenčného obmedzenia je zvyčajne zložený, pozostáva z predpony "FK_", potom nasleduje názov tabuľky a za podčiarkovníkom je názov poľa, ktoré odkazuje na identifikátor referenčnej tabuľky.

Identifikátor (ID) je zvyčajne interná hodnota, ktorá sa používa iba pre odkazy a aká hodnota je tam uložená, je vo väčšine prípadov úplne ľahostajná, takže sa nemusíte snažiť zbaviť sa dier v postupnosti čísel, ktoré vznikajú. v priebehu práce s tabuľkou, napríklad po vymazaní záznamov z referenčnej knihy.

Tabuľka ALTER TABLE ADD CONSTRAINT názov_obmedzenia CUDZÍ KĽÚČ (pole1, pole2, ...) REFERENCIE referenčná_tabuľka (pole1, pole2, ...)
V tomto prípade je v tabuľke "dir_table" primárny kľúč reprezentovaný kombináciou niekoľkých polí (field1, field2, ...).

V skutočnosti teraz aktualizujme polia PositionID a DepartmentID s hodnotami ID z adresárov. Na tento účel použijeme príkaz DML UPDATE:

UPDATE e SET PositionID = (SELECT ID FROM Positions WHERE Name = e.Position), DepartmentID = (SELECT ID FROM Departments WHERE Name = e.Department) FROM Zamestnanci e
Pozrime sa, čo sa stalo spustením dotazu:

VYBERTE * OD zamestnancov

To je všetko, polia PositionID a DepartmentID sú vyplnené podľa pozícií a oddelení s identifikátormi, nie sú potrebné polia Pozícia a Oddelenie v tabuľke Zamestnanci, tieto polia môžete vymazať:

ZMENIŤ TABUĽKU Zamestnanci VYPUSTIŤ STĹPEC Pozícia, oddelenie
Teraz má tabuľka nasledujúcu podobu:

VYBERTE * OD zamestnancov

ID	názov	narodeniny	Email	PositionID	ID oddelenia
1000	Ivanov I.I.	NULOVÝ	NULOVÝ	2	1
1001	Petrov P.P.	NULOVÝ	NULOVÝ	3	3
1002	Sidorov S.S.	NULOVÝ	NULOVÝ	1	2
1003	Andreev A.A.	NULOVÝ	NULOVÝ	4	3

Tie. v dôsledku toho sme sa zbavili ukladania nadbytočných informácií. Teraz môžeme podľa čísla pozície a oddelenia jednoznačne určiť ich názvy pomocou hodnôt v referenčných tabuľkách:

SELECT e.ID, e.Name, p.Name PositionName, d.Name DepartmentName FROM Zamestnanci e LEFT JOIN Departments d ON d.ID = e.DepartmentID LEFT JOIN Pozície p ON p.ID = e.PositionID

V inšpektorovi objektov môžeme vidieť všetky objekty vytvorené pre túto tabuľku. Odtiaľ môžete s týmito objektmi vykonávať rôzne manipulácie – napríklad objekty premenovať alebo odstrániť.

Za zmienku tiež stojí, že tabuľka môže odkazovať sama na seba, t.j. môžete vytvoriť rekurzívny odkaz. Do našej tabuľky so zamestnancami si napríklad pridáme ďalšie pole ManagerID, ktoré bude ukazovať na zamestnanca, ktorému je tento zamestnanec podriadený. Vytvorme pole:

ALTER TABLE Zamestnanci ADD ManagerID int
V tomto poli je povolená hodnota NULL, pole bude prázdne, ak napríklad nad zamestnancom nie sú nadriadení.

Teraz vytvorte CUDZÍ KĽÚČ v tabuľke Zamestnanci:

ALTER TABLE Zamestnanci ADD CONSTRAINT FK_Employees_ManagerID CUDZÍ KĽÚČ (ID manažéra) REFERENCIE Zamestnanci (ID)
Poďme teraz vytvoriť diagram a uvidíme, ako na ňom vyzerajú vzťahy medzi našimi tabuľkami:

V dôsledku toho by sme mali vidieť nasledujúci obrázok (tabuľka Zamestnanci je prepojená s tabuľkami Pozície a Odbory a odkazuje aj sama na seba):

Nakoniec by sa malo povedať, že referenčné kľúče môžu obsahovať ďalšie možnosti ON DELETE CASCADE a ON UPDATE CASCADE, ktoré hovoria, ako sa správať pri odstraňovaní alebo aktualizácii záznamu, na ktorý sa odkazuje v referenčnej tabuľke. Ak tieto možnosti nie sú špecifikované, potom nemôžeme zmeniť ID v referenčnej tabuľke pre záznam, na ktorý sú odkazy z inej tabuľky, taktiež nebudeme môcť vymazať takýto záznam z referencie, kým nevymažeme všetky riadky odkazujúce na tento záznam, alebo odkazy v týchto riadkoch aktualizujeme na inú hodnotu.

Napríklad, poďme znova vytvoriť tabuľku s možnosťou ON DELETE CASCADE pre FK_Employees_DepartmentID:

DROP TABLE Zamestnanci CREATE TABLE Zamestnanci (ID int NOT NULL, Meno nvarchar (30), Dátum narodenia, E-mail nvarchar (30), PositionID int, DepartmentID int, ManagerID int, OBMEDZENIE PK_Employees PRIMÁRNY KĽÚČ (ID), OBMEDZENIE Depart FK_EmployeID FORD ) REFERENCIE Oddelenia (ID) NA VYMAZANIE KASKÁDY, OBMEDZENIE FK_Employees_PositionID CUDZÍ KĽÚČ (ID pozície) REFERENCIE Pozície (ID), OBMEDZENIE FK_Employees_ManagerID CUDZÍ KĽÚČ (ID manažéra) REFERENCIE ID pozícií (ID), Meno zamestnávateľa (ID) NIvanovSERT,01 VALUESERT, zamestnávateľ "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)
Odstránime oddelenie s ID 3 z tabuľky oddelení:

DELETE Departments WHERE ID = 3
Pozrime sa na údaje v tabuľke Zamestnanci:

VYBERTE * OD zamestnancov

ID	názov	narodeniny	Email	PositionID	ID oddelenia	ManagerID
1000	Ivanov I.I.	1955-02-19	NULOVÝ	2	1	NULOVÝ
1002	Sidorov S.S.	1976-06-07	NULOVÝ	1	2	1000

Ako vidíte, údaje pre oddelenie 3 boli vymazané aj z tabuľky Zamestnanci.

Voľba ON UPDATE CASCADE sa správa podobne, ale funguje, keď sa aktualizuje hodnota ID vo vyhľadávaní. Ak napríklad zmeníme ID pozície v adresári úloh, potom sa DepartmentID v tabuľke Zamestnanci aktualizuje na novú hodnotu ID, ktorú sme zadali v adresári. Ale v tomto prípade to jednoducho nebude možné preukázať, pretože stĺpec ID v tabuľke Oddelenia má možnosť IDENTITA, ktorá nám nedovolí vykonať nasledujúcu požiadavku (zmena ID oddelenia 3 na 30):

AKTUALIZÁCIA ID SADY oddelení = 30, KDE ID = 3
Hlavná vec je pochopiť podstatu týchto 2 možností ON DELETE CASCADE a ON UPDATE CASCADE. Tieto možnosti používam vo veľmi zriedkavých prípadoch a odporúčam vám, aby ste si ich použitie v obmedzení odkazovania dobre premysleli. ak omylom vymažete záznam z vyhľadávacej tabuľky, môže to viesť k veľkým problémom a vytvoriť reťazovú reakciu.

Obnovme oddelenie 3:

Udeliť povolenie na pridanie / zmenu hodnoty IDENTITY SET IDENTITY_INSERT Oddelenia ZAPNUTÉ VLOŽIŤ Oddelenia (ID, Názov) HODNOTY (3, N "IT") - zakázať pridávanie / zmenu hodnoty IDENTITY SET IDENTITY_INSERT Oddelenia VYPNUTÉ
Poďme úplne vyčistiť tabuľku Zamestnanci pomocou príkazu TRUNCATE TABLE:

SKRAŤ TABUĽKU Zamestnanci
A znova do nej znova načítajte údaje pomocou predchádzajúceho príkazu INSERT:

VLOŽTE HODNOTY zamestnancov (ID, Meno, Narodeniny, ID pozície, ID oddelenia, ID manažéra) (1000, N "Ivanov I.I.", "19550219", 2,1, NULL), (1001, N "Petrov P.P." , "19831203", 3 ,3,1003), (1002, N "Sidorov SS", "19760607", 1,2,1000), (1003, N "Andreev AA", "19820417" , 4,3,1000)

Poďme si to zhrnúť

V súčasnosti bolo do našich vedomostí pridaných niekoľko ďalších príkazov DDL:

• Pridanie vlastnosti IDENTITY do poľa - umožňuje automaticky vyplniť toto pole (pole počítadla) pre tabuľku;
• ALTER TABLE názov_tabuľky PRIDAŤ list_of_fields_with_characteristics - umožňuje pridávať nové polia do tabuľky;
• ALTER TABLE názov_tabuľky PUSTIŤ STĹPEC zoznam_polí - umožňuje vymazať polia z tabuľky;
• ALTER TABLE názov_tabuľky PRIDAŤ OBMEDZENIE názov_obmedzenia CUDZÍ KĽÚČ(polia) LITERATÚRA referenčná_tabuľka (polia) - umožňuje definovať vzťah medzi tabuľkou a referenčnou tabuľkou.

Ďalšie obmedzenia - UNIQUE, DEFAULT, CHECK

S obmedzením UNIQUE môžete povedať, že hodnoty pre každý riadok v danom poli alebo skupine polí musia byť jedinečné. V prípade tabuľky Zamestnanci môžeme takéto obmedzenie uložiť na pole Email. Stačí predvyplniť e-mail hodnotami, ak ešte nie sú definované:

AKTUALIZOVAŤ NASTAVENIE E-mailu zamestnancov = " [e-mail chránený]"WHERE ID = 1 000 AKTUALIZÁCIA NASTAVENIA E-mailu zamestnancov =" [e-mail chránený]"WHERE ID = 1 001 AKTUALIZÁCIA NASTAVENIA E-mailu zamestnancov =" [e-mail chránený]"WHERE ID = 1 002 AKTUALIZÁCIA NASTAVENIA E-mailu zamestnancov =" [e-mail chránený]"KDE ID = 1003
A teraz môžete na toto pole uložiť obmedzenie jedinečnosti:

ALTER TABLE Zamestnanci ADD CONSTRAINT UQ_Employees_Email UNIQUE (E-mail)
Používateľ teraz nebude môcť zadať rovnaký e-mail pre viacerých zamestnancov.

Obmedzenie jedinečnosti je zvyčajne pomenované nasledovne – najskôr nasleduje predpona „UQ_“, potom názov tabuľky a za podčiarkovníkom je názov poľa, na ktoré sa toto obmedzenie vzťahuje.

Ak by teda mala byť kombinácia polí v kontexte riadkov tabuľky jedinečná, uvádzame ich oddelené čiarkami:

ALTER TABLE table_name ADD CONSTRAINT názov_obmedzenia UNIQUE (pole1, pole2, ...)
Pridaním obmedzenia DEFAULT do poľa môžeme nastaviť predvolenú hodnotu, ktorá bude nahradená, ak pri vkladaní nového záznamu toto pole nie je uvedené v zozname polí príkazu INSERT. Toto obmedzenie je možné nastaviť priamo pri vytváraní tabuľky.

Pridajme nové pole Dátum schôdzky do tabuľky Zamestnanci a nazvime ho HireDate a povedzme, že predvolená hodnota pre toto pole je aktuálny dátum:

ALTER TABLE Zamestnanci PRIDAŤ dátum prenájmu NOT NULL DEFAULT SYSDATETIME ()
Alebo ak stĺpec HireDate už existuje, môžete použiť nasledujúcu syntax:

ALTER TABLE Zamestnanci PRIDAŤ PREDCHOZÝ SYSTÉMDATETIME () PRE HireDate
Tu som neuviedol názov obmedzenia, keďže v prípade DEFAULT som bol toho názoru, že to nie je až také kritické. Ale ak to urobíte priateľsky, potom si myslím, že by ste nemali byť leniví a mali by ste dať normálne meno. Toto sa vykonáva takto:

ALTER TABLE Zamestnanci PRIDAŤ OBMEDZENIE DF_Employees_HireDate PREDVOLANÝ SYSDATETIME () FOR HireDate
Keďže tento stĺpec predtým neexistoval, pri jeho pridaní do každého záznamu sa do poľa HireDate vloží aktuálna hodnota dátumu.

Pri pridávaní nového záznamu sa automaticky vloží aj aktuálny dátum, samozrejme, ak ho výslovne nenastavíme, t.j. nebudú uvedené v zozname stĺpcov. Ukážme si to na príklade bez zadania poľa HireDate v zozname pridaných hodnôt:

INSERT Zamestnanci (ID, meno, e-mail) VALUES (1004, N "Sergeev S.S.", " [e-mail chránený]")
Pozrime sa, čo sa stalo:

VYBERTE * OD zamestnancov

ID	názov	narodeniny	Email	PositionID	ID oddelenia	ManagerID	HireDate
1000	Ivanov I.I.	1955-02-19	i [e-mail chránený]	2	1	NULOVÝ	2015-04-08
1001	Petrov P.P.	1983-12-03	[e-mail chránený]	3	4	1003	2015-04-08
1002	Sidorov S.S.	1976-06-07	[e-mail chránený]	1	2	1000	2015-04-08
1003	Andreev A.A.	1982-04-17	[e-mail chránený]	4	3	1000	2015-04-08
1004	Sergeev S.S.	NULOVÝ	[e-mail chránený]	NULOVÝ	NULOVÝ	NULOVÝ	2015-04-08

Obmedzenie CHECK sa používa, keď je potrebné skontrolovať hodnoty vložené do poľa. Toto obmedzenie uveďme napríklad na pole osobného čísla, čo je identifikátor (ID) zamestnanca. Pomocou tohto obmedzenia povedzme, že personálne čísla by mali mať hodnotu medzi 1000 a 1999:

ALTER TABLE Zamestnanci PRIDAŤ OBMEDZENIE CK_Employees_ID CHECK (ID MEDZI 1000 A 1999)
Obmedzenie sa zvyčajne nazýva rovnako, najskôr príde predpona „CK_“, potom názov tabuľky a názov poľa, na ktoré je toto obmedzenie uložené.

Skúsme vložiť neplatnú položku, aby sme skontrolovali, či obmedzenie funguje (mali by sme dostať zodpovedajúcu chybu):

INSERT HODNOTY zamestnancov (ID, E-mail) (2000, " [e-mail chránený]")
Teraz zmeňte vloženú hodnotu na 1500 a uistite sa, že záznam je vložený:

INSERT HODNOTY zamestnancov (ID, E-mail) (1500, " [e-mail chránený]")
Môžete tiež vytvoriť obmedzenia UNIQUE a CHECK bez zadania názvu:

ALTER TABLE Zamestnanci PRIDAŤ JEDINEČNÝ (E-mail) ALTER TABLE Zamestnanci ADD CHECK (ID 1000 AŽ 1999)
Ale to nie je dobrý postup a je lepšie špecifikovať názov obmedzenia explicitne, pretože aby ste na to prišli neskôr, čo bude náročnejšie, budete musieť objekt otvoriť a zistiť, za čo je zodpovedný.

Pri dobrom mene sa mnohé informácie o obmedzení dajú spoznať priamo podľa jeho názvu.

A preto všetky tieto obmedzenia môžu byť vytvorené okamžite pri vytváraní tabuľky, ak ešte neexistuje. Vymažeme tabuľku:

DROP TABLE Zamestnanci
A znova ho vytvoríme so všetkými vytvorenými obmedzeniami pomocou jedného príkazu CREATE TABLE:

VYTVORIŤ TABUĽKU Zamestnanci (ID int NIE JE NULL, Meno nvarchar (30), Dátum narodenia, E-mail nvarchar (30), ID pozície int, ID oddelenia int, Dátum náboru NOT NULL DEFAULT SYSDATETIME (), - pre DEFAULT hodím výnimku CONSTRAINT PK_Employees PRIMARY KEY (ID), OBMEDZENIE FK_Employees_DepartmentID CUDZÍ KĽÚČ (ID oddelenia) REFERENCIE Oddelenia (ID), OBMEDZENIE FK_Employees_PositionID CUDZÍ KĽÚČ (ID pozície) REFERENCIE Pozície (ID), OBMEDZENIE UQ_Employees_Email JEDINEČNÝ E-mail

VLOŽTE HODNOTY zamestnancov (ID, meno, narodeniny, e-mail, ID pozície, ID oddelenia) (1 000, N "Ivanov I.I.", "19550219", " [e-mail chránený]", 2,1), (1001, N" Petrov P.P. "," 19831203 "," [e-mail chránený]", 3,3), (1002, N" Sidorov S.S. "," 19760607 "," [e-mail chránený]", 1,2), (1003, N" Andreev A.A. "," 19820417 "," [e-mail chránený]",4,3)

Trochu o indexoch vytvorených pri vytváraní obmedzení PRIMARY KEY a UNIQUE

Ako môžete vidieť na obrázku vyššie, pri vytváraní obmedzení PRIMARY KEY a UNIQUE sa automaticky vytvorili indexy s rovnakými názvami (PK_Employees a UQ_Employees_Email). V predvolenom nastavení je index pre primárny kľúč vytvorený ako ZAHRNUTÝ a pre všetky ostatné indexy ako NEZAHRNUTÝ. Treba povedať, že nie všetky DBMS majú koncept klastrovaného indexu. Tabuľka môže mať iba jeden CLUSTERED index. CLUSTERED - znamená, že záznamy tabuľky budú zoradené podľa tohto indexu, môžete tiež povedať, že tento index má priamy prístup ku všetkým údajom tabuľky. Toto je takpovediac hlavný index tabuľky. Zhruba je to index priskrutkovaný k stolu. Klastrovaný index je veľmi výkonný nástroj, ktorý vám môže pomôcť optimalizovať vaše dotazy, zatiaľ na to pamätajte. Ak chceme povedať, že klastrovaný index sa nemá použiť v primárnom kľúči, ale v inom indexe, potom pri vytváraní primárneho kľúča musíme zadať možnosť NONCLUSTERED:

ALTER TABLE názov_tabuľky PRIDAŤ OBMEDZENIE názov_obmedzenia PRIMÁRNY KĽÚČ NEZAHRNUTÝ (pole1, pole2, ...)
Urobme napríklad index obmedzení PK_Employees nezhlukovaný a index obmedzenia UQ_Employees_Email zoskupený. V prvom rade odstránime tieto obmedzenia:

ALTER TABLE Zamestnanci DROP CONSTRAINT PK_Employees ALTER TABLE Zamestnanci DROP CONSTRAINT UQ_Employees_Email
Teraz ich poďme vytvoriť s možnosťami KLUSTEROVANÉ a NEZAHRNUTÉ:

ALTER TABLE Zamestnanci ADD CONSTRAINT PK_Employees PRIMÁRNY KĽÚČ NEZAHRNUTÝ (ID) ALTER TABLE Zamestnanci PRIDAŤ OBMEDZENIE UQ_Employees_Email UNIQUE CLUSTERED (E-mail)
Teraz, po načítaní z tabuľky Zamestnanci, vidíme, že záznamy sú zoradené podľa zoskupeného indexu UQ_Employees_Email:

VYBERTE * OD zamestnancov

ID	názov	narodeniny	Email	PositionID	ID oddelenia	HireDate
1003	Andreev A.A.	1982-04-17	[e-mail chránený]	4	3	2015-04-08
1000	Ivanov I.I.	1955-02-19	[e-mail chránený]	2	1	2015-04-08
1001	Petrov P.P.	1983-12-03	[e-mail chránený]	3	3	2015-04-08
1002	Sidorov S.S.	1976-06-07	[e-mail chránený]	1	2	2015-04-08

Predtým, keď bol index PK_Employees zoskupeným indexom, boli záznamy štandardne zoradené podľa ID.

Ale v tomto prípade je to len príklad, ktorý ukazuje podstatu klastrovaného indexu, pretože s najväčšou pravdepodobnosťou sa do tabuľky Zamestnanci budú odosielať dotazy pomocou poľa ID a v niektorých prípadoch môže samo fungovať ako referenčná kniha.

Pri vyhľadávaní sa zvyčajne odporúča, aby bol klastrovaný index vytvorený na primárnom kľúči v žiadostiach často odkazujeme na identifikátor adresára, aby sme získali napríklad meno (pozícia, oddelenie). Tu si pripomenieme, čo som napísal vyššie, že klastrovaný index má priamy prístup k riadkom tabuľky a z toho vyplýva, že môžeme získať hodnotu ľubovoľného stĺpca bez ďalšej réžie.

Na najčastejšie vzorkované polia je výhodné aplikovať zhlukovaný index.

Niekedy je kľúč vytvorený v tabuľkách náhradným poľom, v takom prípade je užitočné uložiť možnosť CLUSTERED indexu pre vhodnejší index a zadať možnosť NONCLUSTERED pri vytváraní náhradného primárneho kľúča.

Poďme si to zhrnúť

V tejto fáze sme sa zoznámili so všetkými typmi obmedzení v ich najjednoduchšej forme, ktoré sa vytvárajú príkazom v tvare "ALTER TABLE názov_tabuľky ADD CONSTRAINT názov_obmedzenia ...":

• PRIMÁRNY KĽÚČ- primárny kľúč;
• CUDZÍ KĽÚČ- nastavenie prepojení a kontrola referenčnej integrity údajov;
• UNIKÁTNY- umožňuje vytvárať jedinečnosť;
• KONTROLA- umožňuje správnosť zadaných údajov;
• DEFAULT- umožňuje nastaviť predvolenú hodnotu;
• Za zmienku tiež stojí, že všetky obmedzenia je možné odstrániť pomocou príkazu „ ALTER TABLE názov_tabuľky OBMEDZENIE DROP názov_obmedzenia ".

Čiastočne sme sa dotkli aj témy indexov a analyzovali sme koncept klastra ( KLUSTROVANÝ) a nezhlukovaný ( NEZAHRNUTÝ) index.

Vytvárajte samostatné indexy

Sebadôvera sa tu vzťahuje na indexy, ktoré nie sú vytvorené pre obmedzenie PRIMÁRNY KĽÚČ alebo UNIKÁTNE.

Indexy podľa poľa alebo polí možno vytvoriť pomocou nasledujúceho príkazu:

CREATE INDEX IDX_Employees_Name ON Zamestnanci (meno)
Tiež tu môžete určiť možnosti CLUSTERED, NENCLUSTERED, UNIQUE a tiež môžete určiť smer triedenia pre každé jednotlivé pole ASC (predvolene) alebo DESC:

VYTVORIŤ JEDINEČNÝ NEZAHRNUTÝ INDEX UQ_Employees_EmailDesc ON Zamestnanci (E-mail DESC)
Pri vytváraní indexu bez klastrov možno uvoľniť možnosť NONCLUSTERED, pretože je predvolená a zobrazuje sa tu jednoducho na označenie polohy možnosti ZAHRNUTÉ alebo NEZAHRNUTÉ v príkaze.

Index môžete odstrániť pomocou nasledujúceho príkazu:

DROP INDEX IDX_Employees_Name ON Zamestnanci
Jednoduché indexy, ako sú obmedzenia, možno vytvoriť v kontexte príkazu CREATE TABLE.

Napríklad znova zhodíme tabuľku:

DROP TABLE Zamestnanci
A znova ho vytvoríme so všetkými vytvorenými obmedzeniami a indexmi pomocou jedného príkazu CREATE TABLE:

VYTVORIŤ TABUĽKU Zamestnanci (ID int NOT NULL, Meno nvarchar (30), Dátum narodenia, E-mail nvarchar (30), PositionID int, DepartmentID int, Dátum náboru NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME (), ManagerID int, PRIERMARYINT OBMEDZENIE FK_Employees_DepartmentID CUDZÍ KĽÚČ (ID oddelenia) REFERENCIE Oddelenia (ID), OBMEDZENIE FK_Employees_PositionID CUDZÍ KĽÚČ (ID pozície) REFERENCES Pozície (ID), OBMEDZENIE FK_Employees_ManagerID)01 INDEX IDENTIFIKÁTOR CUDZÍHO KĽÚČ (IDme INDEX 1010CHECKE)
Nakoniec vložíme do tabuľky našich zamestnancov:

VLOŽTE HODNOTY zamestnancov (ID, Meno, Narodeniny, E-mail, ID pozície, ID oddelenia, ID manažéra) (1000, N "Ivanov I.I.", "19550219", " [e-mail chránený]", 2,1, NULL), (1001, N" Petrov P.P. "," 19831203 "," [e-mail chránený]", 3,3,1003), (1002, N" Sidorov S.S. "," 19760607 "," [e-mail chránený]", 1,2,1000), (1003, N" Andreev A.A. "," 19820417 "," [e-mail chránený]",4,3,1000)
Okrem toho stojí za zmienku, že hodnoty môžete zahrnúť do nezhlukovaného indexu tak, že ich zadáte do poľa INCLUDE. Tie. v tomto prípade bude index INCLUDE trochu pripomínať zoskupený index, len teraz index nie je priskrutkovaný k tabuľke, ale potrebné hodnoty sú priskrutkované k indexu. V súlade s tým môžu takéto indexy výrazne zlepšiť výkon výberových dotazov (SELECT), ak sú v indexe prítomné všetky uvedené polia, potom nemusí byť vôbec potrebné pristupovať k tabuľke. To však prirodzene zvyšuje veľkosť indexu, pretože hodnoty uvedených polí sú v indexe duplikované.

Výňatok z MSDN. Všeobecná syntax príkazu na vytváranie indexov

VYTVORIŤ [JEDINEČNÉ] [ZOSTAVENÉ | NEZAHRNUTÉ] INDEX index_name ZAPNUTÝ