Scopul limbajului SQL. Operatori de manipulare a datelor. Structura comenzilor SQL

SQL este un limbaj de interogare structurat. SQL nu există fără baze de date – nu poți scrie programe în el, iar în acest sens nu este un limbaj de programare precum PHP, dar atunci când ai de-a face cu un anumit SGBD, nu te mai poți descurca fără cunoștințe de SQL. Puteți scrie interogări simple pe el sau puteți efectua tranzacții mari constând din mai multe interogări complexe. O interogare SQL este un fel de comandă către o bază de date. O astfel de comandă poate solicita returnarea informațiilor care îndeplinesc anumite criterii sau poate oferi instrucțiuni de ștergere a oricăror înregistrări etc. O comandă SQL este un șir simplu, de exemplu:

SELECTAȚI * DIN departamentul Staff WHERE

Interogările SQL sunt de obicei aproape de o expresie simplă în limba engleză. Comanda de mai sus poate fi tradusă în rusă după cum urmează

SELECTAȚI TOTUL DIN Staff WHERE clwjiertme"

Este o comandă complet de înțeles, este doar păcat că este scrisă exclusiv în engleză. Ca rezultat al executării unei astfel de interogări, DBMS va returna toate înregistrările din tabelul Staff în care câmpul depart* Mit_id este egal cu trei. În exemplul nostru, această interogare selectează de fapt numai programatori din întreaga bază de angajați.

Dacă nu ați mai lucrat cu un SGBD, atunci este posibil să aveți o întrebare rezonabilă: unde și cum puteți executa această interogare? Există trei moduri de a executa interogări SQL.

1. Mediu interactiv pentru interacțiunea cu SGBD. Pentru majoritatea serverelor DBMS, există programe client (încorporate sau furnizate de terți), în mediul de lucru al cărora puteți scrie interogări SQL, le puteți executa și obține rezultatul. De obicei, astfel de instrumente sunt folosite de administratorii bazelor de date și nu au legătură directă cu programarea PHP. Un exemplu de program client pentru lucrul cu MySQL este programul MySQL Administrator (http: /www.mysgl.coin/product-s/administratoT/) sau foarte popularul sistem PHP phpMyAdmin (http: / /www. phpmyadi'ln. r»et /itummjiage/index.php). Pentru a începe, va fi suficient un kit de distribuție deja instalat, care are o interfață de consolă. Pe Linux, trebuie să tastați comanda mysql din linia de comandă pentru a deschide o fereastră care vă solicită să introduceți interogări SQL, iar pe Windows, pentru a lansa aceeași interfață, trebuie să rulați fișierul mysql. exe din directorul bin.

2. Interogări SQL statice. De obicei, astfel de interogări sunt scrise în cadrul procedurilor stocate în bazele de date în sine sau codificate în aplicațiile în sine. O interogare SQL statică este predefinită și se modifică numai dacă programul sau codul procedurii stocate este rescris manual. Din PHP, o astfel de interogare SQL este executată folosind funcții speciale, despre care vor fi discutate mai târziu.

3. Interogări SQL dinamice. Acest tip include interogări care nu pot fi complet definite la scrierea unei aplicații. i" exemplu, atunci când scrie un program pentru a obține o listă de angajați" mt diferite divizii ale întreprinderii, programatorul nu știe, ci "> despre diviziile din companie și care angajați vor fi incluși în ele i„. Desigur , aceste date pot fi scrise strict în program, dar atunci când Prima modificare a structurii companiei, programul poate fi aruncat sau va trebui rescris. Interogările dinamice vă permit să creați programe de program care sunt flexibile la schimbările în date.În PHP, astfel de interogări sunt efectuate cu aproape aceleași funcții ca și cele statice, doar că în ele există mai mult '^v'> Nu este posibil să treci unii parametri.

Ca un rezumat al celor trei puncte descrise mai sus, putem spune că interogările SQL sunt executate din programe administrative speciale sau în diverse moduri din scripturi PHP.

Deoarece un SGBD rezolvă multe probleme, SQL este, de asemenea, forțat să fie un limbaj multifuncțional. Există mai multe tipuri de operații care pot fi< \ ществлять с помощью SQL.

1. Definirea structurii bazei de date. Acest tip include interogări care creează și modifică tabele și indecși. Acestea sunt de obicei comenzi CRE; „E TA’ LE, ALI’R TA’ LE, ‘ ” ’.TE INDEX Etc.

2. Manipularea datelor. Acest tip include interogări de inserare (mutare), ștergere sau modificare a datelor din tabele. Acestea sunt cele trei comenzi principale: INSERT. ȘTERGEȚI ȘI ACTUALIZAȚI.

3. Selectarea datelor Aceasta include o singură comandă SELECT. Nu aduce modificări datelor în sine, dar vă permite să le preluați din baza de date. Chiar dacă o singură comandă este folosită pentru a prelua date, este foarte puternică și este folosită foarte des în aplicații.

4. Managementul serverului DBMS. Acest tip include în principal interogări pentru a gestiona utilizatorii și drepturile lor de acces (de exemplu, comanda GRANT).

Cunoașterea bună a SQL facilitează foarte mult munca unui programator atunci când lucrează cu o bază de date. Aplicațiile pot fi mici, dar au funcționalități grozave doar datorită faptului că SQL preia multe sarcini.

Ca în orice altă zonă a IT, există standarde în SQL - acestea sunt ANSI SQL. Abrevierea ANSI înseamnă Institutul Național de Standarde din America. Cu toate acestea, nu în ultimul rând din cauza diferențelor de funcționalitate ale SGBD-urilor SQL în sine pentru diferite

SGBD-urile sunt încă diferite unele de altele. În prezent, aproape fiecare DBMS are propriul său dialect, care de obicei nu diferă mult de standardul general, dar are propriile sale caracteristici. De exemplu, limbajul PL/SQL este compatibil cu Oracle și PostgreSQL, iar T-SQL este folosit pentru a lucra cu MS SQL Server.

Pentru munca ulterioară cu bazele de date, vă recomandăm să studiați imediat standardul cu care intenționați să lucrați în viitor. Pentru majoritatea dezvoltatorilor Web în acest moment, funcționalitatea SGBD-ului MySQL este suficientă (și poate fi folosită gratuit), așa că în această carte toate exemplele cu MySQL vor fi date, în consecință, în dialectul acestui SGBD. Documentația despre limbajul de interogare pentru MySQL poate fi găsită la www.mysql.com.

PHP și baze de date

Capacitatea de a stoca și de a prelua eficient cantități mari de informații a adus o contribuție uriașă la dezvoltarea cu succes a Internetului. De obicei, bazele de date sunt folosite pentru a stoca informații. Funcționarea site-urilor cunoscute precum Yahoo, Amazon și Ebay depinde în mare măsură de fiabilitatea bazelor de date care stochează cantități enorme de informații. Desigur, suportul pentru baze de date nu este doar în beneficiul corporațiilor gigantice – programatorii web au la dispoziție câteva implementări puternice de baze de date, distribuite la un cost relativ scăzut (sau chiar gratuit).

Organizarea corectă a bazei de date oferă capabilități de recuperare a datelor mai rapide și mai flexibile. Simplifică foarte mult implementarea instrumentelor de căutare și sortare, iar problemele privind drepturile de acces la informații sunt rezolvate folosind controalele de privilegii găsite în multe sisteme de gestionare a bazelor de date (DBMS). În plus, procesele de replicare și arhivare a datelor sunt simplificate.

Capitolul începe cu o descriere detaliată a recuperării și actualizării datelor în MySQL, probabil cel mai popular DBMS folosit în PHP (http://www.mysql.com). Folosind MySQL ca exemplu, vom arăta cum datele din baza de date sunt încărcate și actualizate în PHP; Vom analiza instrumentele de bază de căutare și sortare utilizate în multe aplicații web. Vom trece apoi la suportul PHP pentru ODBC (Open Data Base Connectivity), o interfață generică care poate fi folosită pentru a se conecta la diferite SGBD-uri în același timp. Suportul ODBC în PHP va fi demonstrat prin conectarea și preluarea datelor dintr-o bază de date Microsoft Access. Capitolul se încheie cu un proiect care folosește PHP și MySQL pentru a crea un director ierarhic care conține informații despre site-urile preferate. Când site-uri noi sunt incluse în catalog, utilizatorul le atribuie uneia dintre categoriile standard definite de administratorul site-ului.

Înainte de a discuta despre MySQL, vreau să spun câteva cuvinte despre SQL, cel mai comun limbaj pentru lucrul cu baze de date. Limbajul SQL este baza aproape a tuturor SGBD-urilor existente. Pentru a continua să priviți exemple de lucru cu baze de date, trebuie să aveți cel puțin o înțelegere generală a modului în care funcționează SQL.

SQL este în general descris ca limbajul standard folosit pentru a interacționa cu bazele de date relaționale (vezi mai jos). Cu toate acestea, SQL nu este un limbaj de programare precum C, C++ sau PHP. Mai degrabă, este un instrument de interfață pentru efectuarea diferitelor operațiuni de bază de date, oferind utilizatorului un set standard de comenzi. Capacitățile SQL nu se limitează la preluarea datelor dintr-o bază de date. SQL acceptă o varietate de opțiuni pentru interacțiunea cu o bază de date, inclusiv:

• definirea structurii datelor-- definirea structurilor utilizate la stocarea datelor;
• eșantionarea datelor-- încărcarea datelor din baza de date și prezentarea acestora într-un format convenabil pentru ieșire;
• procesarea datelor-- inserarea, actualizarea și ștergerea informațiilor;
• controlul accesului-- capacitatea de a permite/interzice eșantionarea, inserarea, actualizarea și ștergerea datelor la nivelul utilizatorilor individuali;
• controlul integrității datelor-- Păstrarea structurii datelor în cazul unor probleme precum actualizări paralele sau defecțiuni ale sistemului.

Vă rugăm să rețineți că definiția SQL a declarat că acest limbaj este destinat să funcționeze relaționale baze de date. În SGBD-urile relaționale, datele sunt organizate ca un set de tabele interconectate. Relațiile dintre tabele sunt implementate sub formă de legături către date din alte tabele. Masa poate fi gândit ca o matrice bidimensională în care locația fiecărui element este caracterizată de valori specifice de rând și coloană. Un exemplu de bază de date relațională este prezentat în Fig. 11.1.

Orez. 11.1.Exemplu de bază de date relațională

După cum se poate observa din fig. 11.1, fiecare tabel este format din rânduri (înregistrări) și coloane (câmpuri). Fiecărui câmp i se atribuie un nume unic (în cadrul tabelului). Observați relația dintre tabelele client și comenzi, indicată de o săgeată. Un scurt identificator de client este inclus în informațiile despre comandă, ceea ce evită stocarea redundantă a numelui clientului și a altor detalii. Mai există o relație în baza de date prezentată - între tabelele comenzi și produse. Această conexiune se stabilește folosind câmpul prod_id, care stochează identificatorul produsului comandat de acest client (definit de câmpul custjd). Având aceste conexiuni, este ușor să faceți referire la date complete despre clienți și produse folosind identificatori simpli. O bază de date bine organizată devine un instrument puternic pentru organizarea și stocarea eficientă a datelor cu redundanță minimă. Amintiți-vă această bază de date, mă voi referi la ea des în exemple suplimentare.

Deci, cum sunt efectuate operațiunile de baze de date relaționale? Pentru a face acest lucru, SQL are un set special de comenzi generale - cum ar fi SELECT, INSERT, UPDATE și DELETE. De exemplu, dacă trebuie să obțineți adresa de e-mail a unui client cu ID 2001cu (vezi Figura 11.1), trebuie doar să rulați următoarea comandă SQL:

SELECTAȚI cust_email FROM clienții WHERE custjd = "2001cu"

Totul este destul de logic, nu-i așa? În rezumat, comanda arată astfel:

SELECTAȚI nume_câmp FROM nume_tabel [condiția WHERE]

Parantezele pătrate indică faptul că partea finală a comenzii este opțională. De exemplu, pentru a obține adresele de e-mail ale tuturor clienților din tabelul clienți, trebuie doar să rulați următoarea interogare:

SELECTează cust_email FROM clienți

Să presupunem că doriți să adăugați o nouă intrare la tabelul cu produse. Cea mai simplă comandă de inserare arată astfel:

INSERT în produse VALORI ("1009pr", "Roșii roșii", "1.43");

Dacă mai târziu trebuie să ștergeți aceste date, utilizați următoarea comandă:

DELETE FROM produsele WHERE prod_id = 1009r";

Există multe tipuri de comenzi SQL și o descriere completă a acestora depășește scopul acestei cărți. O carte separată ar putea fi scrisă cu ușurință pe această temă! Am încercat să păstrez comenzile SQL folosite în exemple relativ simple, dar suficient de realiste. Există o mulțime de informații și resurse de instruire SQL pe Web. Unele link-uri sunt furnizate la sfârșitul acestei secțiuni.

Nu este necesar să scrieți comenzile SQL cu caractere mari. Cu toate acestea, prefer această notație deoarece ajută la distingerea componentelor cererii.

Din moment ce citiți această carte, probabil vă întrebați cum să lucrați cu baze de date pe Web? De regulă, mai întâi, folosind unele

sau un limbaj de interfață (PHP, Java sau Perl), se creează o conexiune la baza de date, după care programul accesează baza de date cu interogări folosind un set standard de instrumente. Un limbaj de interfață poate fi gândit ca un fel de „clei” care leagă baza de date de Web. Mă îndrept către limbajul meu favorit pentru front-end - PHP.

Leran2002 9 aprilie 2015 la 12:31

Un manual despre limbajul SQL (DDL, DML) folosind dialectul MS SQL Server ca exemplu. Prima parte

• Microsoft SQL Server,
• SQL

• Tutorial

Despre ce este acest tutorial?

Acest tutorial este ceva ca o „ștampilă a memoriei mele” în limbajul SQL (DDL, DML), adică. Acestea sunt informații care s-au acumulat pe parcursul activităților mele profesionale și sunt stocate constant în capul meu. Acesta este un minim suficient pentru mine, care este folosit cel mai des atunci când lucrez cu baze de date. Dacă este nevoie să utilizez constructe SQL mai complete, de obicei apelez la biblioteca MSDN aflată pe Internet pentru ajutor. După părerea mea, este foarte greu să ții totul în cap și nu este nevoie în mod special de acest lucru. Dar cunoașterea structurilor de bază este foarte utilă, pentru că... sunt aplicabile aproape în aceeași formă în multe baze de date relaționale, cum ar fi Oracle, MySQL, Firebird. Diferențele sunt în principal în tipurile de date, care pot diferi în detaliu. Nu există multe construcții SQL de bază și, cu o practică constantă, acestea sunt memorate rapid. De exemplu, pentru a crea obiecte (tabele, constrângeri, indexuri etc.), este suficient să aveți la îndemână un mediu de editor de text (IDE) pentru a lucra cu baza de date și nu este nevoie să studiați instrumente vizuale adaptate pentru lucrul cu un anumit tip de bază de date (MS SQL, Oracle, MySQL, Firebird, ...). Acest lucru este, de asemenea, convenabil, deoarece tot textul este în fața ochilor și nu trebuie să parcurgeți numeroase file pentru a crea, de exemplu, un index sau o constrângere. Când lucrați în mod constant cu o bază de date, crearea, modificarea și mai ales re-crearea unui obiect folosind scripturi este de multe ori mai rapidă decât dacă o faceți în modul vizual. Tot în modul script (și, în consecință, cu atenția cuvenită), este mai ușor să stabiliți și să controlați regulile de denumire a obiectelor (parerea mea subiectivă). În plus, scripturile sunt convenabile de utilizat atunci când modificările făcute într-o bază de date (de exemplu, test) trebuie transferate în aceeași formă într-o altă bază de date (productivă).

Limbajul SQL este împărțit în mai multe părți, aici mă voi uita la cele mai importante 2 părți:

• DML – Limbajul de manipulare a datelor, care conține următoarele constructe:
  • SELECT – selectarea datelor
  • INSERT – introducerea de date noi
  • UPDATE – actualizare de date
  • DELETE – ștergerea datelor
  • MERGE – fuziunea datelor

Deoarece Sunt un practicant; va exista puțină teorie ca atare în acest manual și toate construcțiile vor fi explicate folosind exemple practice. În plus, cred că un limbaj de programare, și mai ales SQL, poate fi stăpânit doar prin practică, experimentându-l singur și înțelegând ce se întâmplă când executați cutare sau cutare construct.

Acest manual a fost creat conform principiului Step by Step, adică. trebuie să-l citiți secvențial și, de preferință, să urmați imediat exemplele. Dar dacă pe parcurs trebuie să aflați mai detaliat despre o anumită comandă, atunci utilizați o căutare specifică pe Internet, de exemplu, în biblioteca MSDN.

Când am scris acest tutorial, am folosit baza de date MS SQL Server versiunea 2014 și am folosit MS SQL Server Management Studio (SSMS) pentru a executa scripturile.

Pe scurt despre MS SQL Server Management Studio (SSMS)

SQL Server Management Studio (SSMS) este un utilitar pentru Microsoft SQL Server pentru configurarea, gestionarea și administrarea componentelor bazei de date. Acest utilitar conține un editor de scripturi (pe care îl vom folosi în principal) și un program grafic care funcționează cu obiectele și setările serverului. Instrumentul principal al SQL Server Management Studio este Object Explorer, care permite utilizatorului să vizualizeze, să preia și să gestioneze obiectele serverului. Acest text este împrumutat parțial de la Wikipedia.

Pentru a crea un nou editor de scripturi, utilizați butonul „Interogare nouă”:

Pentru a schimba baza de date curentă, puteți utiliza lista derulantă:

Pentru a executa o anumită comandă (sau un grup de comenzi), selectați-o și apăsați butonul „Execute” sau tasta „F5”. Dacă în editor există o singură comandă în prezent sau trebuie să executați toate comenzile, atunci nu trebuie să selectați nimic.

După rularea scripturilor, în special a celor care creează obiecte (tabele, coloane, indexuri), pentru a vedea modificările, utilizați reîmprospătarea din meniul contextual prin evidențierea grupului corespunzător (de exemplu, Tabele), tabelul în sine sau grupul Coloane din acesta.

De fapt, asta este tot ce trebuie să știm pentru a completa exemplele date aici. Restul utilitarului SSMS este ușor de învățat pe cont propriu.

Puțină teorie

O bază de date relațională (RDB, sau în continuare în context pur și simplu DB) este o colecție de tabele interconectate. În linii mari, o bază de date este un fișier în care datele sunt stocate într-o formă structurată.

DBMS – Sistem de management al bazelor de date, de ex. acesta este un set de instrumente pentru lucrul cu un anumit tip de bază de date (MS SQL, Oracle, MySQL, Firebird, ...).

Notă
Deoarece în viață, în vorbirea colocvială, spunem mai ales: „Oracle DB”, sau chiar doar „Oracle”, adică de fapt „Oracle DBMS”, apoi în contextul acestui manual se va folosi uneori termenul DB. Din context, cred că va fi clar despre ce vorbim exact.

Un tabel este o colecție de coloane. Coloanele pot fi numite și câmpuri sau coloane; toate aceste cuvinte vor fi folosite ca sinonime care exprimă același lucru.

Tabelul este obiectul principal al RDB; toate datele RDB sunt stocate rând cu rând în coloanele tabelului. Liniile și înregistrările sunt, de asemenea, sinonime.

Pentru fiecare tabel, precum și coloanele sale, sunt specificate nume prin care acestea sunt accesate ulterior.
Numele obiectului (numele tabelului, numele coloanei, numele indexului etc.) în MS SQL poate avea o lungime maximă de 128 de caractere.

Pentru trimitere– în baza de date ORACLE, numele obiectelor pot avea o lungime maximă de 30 de caractere. Prin urmare, pentru o anumită bază de date, trebuie să vă dezvoltați propriile reguli de denumire a obiectelor pentru a îndeplini limita numărului de caractere.

SQL este un limbaj care vă permite să interogați o bază de date folosind un SGBD. Într-un anumit SGBD, limbajul SQL poate avea o implementare specifică (propul său dialect).

DDL și DML sunt un subset al limbajului SQL:

• Limbajul DDL este folosit pentru a crea și modifica structura bazei de date, de ex. pentru a crea/modifica/sterge tabele si relatii.
• Limbajul DML vă permite să manipulați datele din tabel, de ex. cu replicile ei. Vă permite să selectați date din tabele, să adăugați date noi în tabele, precum și să actualizați și să ștergeți datele existente.

În SQL, puteți utiliza 2 tipuri de comentarii (pe o singură linie și pe mai multe rânduri):

Comentariu pe un rând
Și

/* comentariu pe mai multe linii */

De fapt, asta va fi suficient pentru teorie.

DDL – Limbajul de definire a datelor

De exemplu, luați în considerare un tabel cu date despre angajați, într-o formă familiară unei persoane care nu este programator:

În acest caz, coloanele din tabel au următoarele nume: Număr de personal, Nume complet, Data nașterii, E-mail, Funcție, Departament.

Fiecare dintre aceste coloane poate fi caracterizată prin tipul de date pe care le conține:

• Număr de personal – număr întreg
• Nume complet – șir
• Data nașterii - data
• E-mail – șir
• Poziție - șir
• Departament - linie

Tipul de coloană este o caracteristică care indică ce tip de date poate stoca o coloană dată.

Pentru început, va fi suficient să ne amintim doar următoarele tipuri de date de bază utilizate în MS SQL:

Sens	Notare în MS SQL	Descriere
Șir de lungime variabilă	varchar(N) Și nvarchar(N)	Folosind numărul N, putem specifica lungimea maximă posibilă a șirului pentru coloana corespunzătoare. De exemplu, dacă vrem să spunem că valoarea coloanei „Nume” poate conține maximum 30 de caractere, atunci trebuie să setăm tipul acesteia la nvarchar(30). Diferența dintre varchar și nvarchar este că varchar vă permite să stocați șiruri în format ASCII, unde un caracter ocupă 1 octet, iar nvarchar stochează șiruri în format Unicode, unde fiecare caracter ocupă 2 octeți. Tipul varchar ar trebui folosit numai dacă sunteți 100% sigur că acest câmp nu va trebui să stocheze caractere Unicode. De exemplu, varchar poate fi folosit pentru a stoca adrese de e-mail deoarece... de obicei conțin doar caractere ASCII.
Snur de lungime fixă	char(N) Și nchar(N)	Acest tip diferă de un șir de lungime variabilă prin faptul că, dacă lungimea șirului este mai mică de N caractere, atunci este întotdeauna completat în dreapta la o lungime de N cu spații și stocat în baza de date sub această formă, adică. în baza de date ocupă exact N caractere (unde un caracter ocupă 1 octet pentru char și 2 octeți pentru nchar). În practica mea, acest tip este foarte rar folosit, iar dacă este folosit, este folosit în principal în formatul char(1), adică. când un câmp este definit de un singur caracter.
Întreg	int	Acest tip ne permite să folosim numai numere întregi în coloană, atât pozitive, cât și negative. Pentru referință (acum acest lucru nu este atât de relevant pentru noi), intervalul de numere pe care le permite tipul int este de la -2.147.483.648 la 2.147.483.647. De obicei, acesta este tipul principal care este utilizat pentru a specifica identificatorii.
Număr real sau real	pluti	În termeni simpli, acestea sunt numere care pot conține un punct zecimal (virgulă).
Data	Data	Dacă coloana trebuie să stocheze doar Data, care constă din trei componente: Ziua, Luna și Anul. De exemplu, 15.02.2014 (15.02.2014). Acest tip poate fi folosit pentru coloana „Data admiterii”, „Data nașterii”, etc., adică în cazurile în care este important pentru noi să înregistrăm doar data, sau când componenta de timp nu este importantă pentru noi și poate fi aruncată sau dacă nu este cunoscută.
Timp	timp	Acest tip poate fi utilizat dacă coloana trebuie să stocheze numai date de timp, de exemplu. Ore, minute, secunde și milisecunde. De exemplu, 17:38:31.3231603 De exemplu, zilnic „Ora de plecare a zborului”.
data si ora	datetime	Acest tip vă permite să salvați simultan atât data, cât și ora. De exemplu, 15/02/2014 17:38:31.323 De exemplu, aceasta ar putea fi data și ora unui eveniment.
Steag	pic	Acest tip este convenabil de utilizat pentru a stoca valori de forma „Da”/„Nu”, unde „Da” va fi stocat ca 1, iar „Nu” va fi stocat ca 0.

De asemenea, valoarea câmpului, dacă nu este interzisă, poate să nu fie specificată; în acest scop este folosit cuvântul cheie NULL.

Pentru a rula exemplele, să creăm o bază de date de testare numită Test.

O bază de date simplă (fără a specifica parametri suplimentari) poate fi creată prin rularea următoarei comenzi:

Test CREATE DATABASE
Puteți șterge baza de date cu comanda (ar trebui să fiți foarte atenți cu această comandă):

Testul DROP DATABASE
Pentru a trece la baza noastră de date, puteți rula comanda:

Test de utilizare
Alternativ, selectați baza de date Test din lista derulantă din zona de meniu SSMS. Când lucrez, folosesc adesea această metodă de comutare între baze de date.

Acum în baza noastră de date putem crea un tabel folosind descrierile așa cum sunt, folosind spații și caractere chirilice:

CREATE TABLE [Angajați]([Număr de personal] int, [Nume] nvarchar(30), [Data nașterii] data, nvarchar(30), [Posiție] nvarchar(30), [Departament] nvarchar(30))
În acest caz, va trebui să introducem numele între paranteze drepte […].

Dar în baza de date, pentru o mai mare comoditate, este mai bine să specificați toate numele obiectelor în latină și să nu folosiți spații în nume. În MS SQL, de obicei, în acest caz, fiecare cuvânt începe cu o literă majusculă, de exemplu, pentru câmpul „Personnel Number”, am putea seta numele PersonnelNumber. De asemenea, puteți utiliza numere în nume, de exemplu, PhoneNumber1.

Pe o notă
În unele SGBD, următorul format de denumire „PHONE_NUMBER” poate fi mai preferabil; de exemplu, acest format este adesea folosit în baza de date ORACLE. Desigur, atunci când se specifică un nume de câmp, este de dorit ca acesta să nu coincidă cu cuvintele cheie utilizate în SGBD.

Din acest motiv, puteți uita de sintaxa parantezelor drepte și puteți șterge tabelul [Angajați]:

DROP TABLE [Angajați]
De exemplu, un tabel cu angajați poate fi numit „Angajați”, iar câmpurile sale pot primi următoarele nume:

• ID – Număr de personal (ID de angajat)
• Nume - nume complet
• Ziua de naștere – Data nașterii
• E-mail – E-mail
• Poziție - Poziție
• Departament - Departament

Foarte des, cuvântul ID este folosit pentru a denumi un câmp de identificare.

Acum să creăm tabelul nostru:

CREATE TABLE Angajații (ID int, Nume nvarchar(30), Data nașterii, Email nvarchar(30), Poziția nvarchar(30), Departament nvarchar(30))
Pentru a specifica coloanele necesare, puteți utiliza opțiunea NOT NULL.

Pentru un tabel existent, câmpurile pot fi redefinite folosind următoarele comenzi:

Actualizați câmpul ID ALTER TABLE Angajații ALTER COLUMN ID int NOT NULL -- update Nume câmp ALTER TABLE Angajații ALTER COLUMN Nume nvarchar(30) NOT NULL

Pe o notă
Conceptul general al limbajului SQL rămâne același pentru majoritatea SGBD-urilor (cel puțin, asta este ceea ce pot judeca din SGBD-urile cu care am lucrat). Diferențele dintre DDL în diferite SGBD-uri constă în principal în tipurile de date (nu numai numele lor pot diferi aici, ci și detaliile implementării lor), iar specificul implementării limbajului SQL poate diferi, de asemenea, ușor (adică, esența comenzilor este aceeași, dar pot exista mici diferențe în dialect, din păcate, dar nu există un singur standard). După ce stăpânești elementele de bază ale SQL, poți trece cu ușurință de la un SGBD la altul, deoarece... În acest caz, va trebui doar să înțelegeți detaliile implementării comenzilor în noul SGBD, de exemplu. în cele mai multe cazuri, este suficient să desenezi o analogie.

Crearea unui tabel CREATE TABLE Angajații (ID int, -- în ORACLE tipul int este echivalentul (învelișului) pentru numărul (38) Nume nvarchar2(30), -- nvarchar2 în ORACLE este echivalent cu nvarchar în MS SQL Data nașterii, e-mail nvarchar2(30) , Poziția nvarchar2(30), Departamentul nvarchar2(30)); -- actualizarea câmpurilor ID și Nume (aici se folosește MODIFY(...) în loc de ALTER COLUMN) ALTER TABLE Angajații MODIFY(ID int NOT NULL,Name nvarchar2(30) NOT NULL); -- adăugarea PK (în acest caz construcția arată la fel ca în MS SQL, va fi afișată mai jos) ALTER TABLE Angajații ADD CONSTRAINT PK_Employees PRIMARY KEY(ID);
Pentru ORACLE există diferențe în ceea ce privește implementarea tipului varchar2; codificarea acestuia depinde de setările bazei de date și textul poate fi salvat, de exemplu, în codificare UTF-8. În plus, lungimea câmpului în ORACLE poate fi specificată atât în ​​octeți, cât și în caractere; pentru aceasta se folosesc opțiuni suplimentare BYTE și CHAR, care sunt specificate după lungimea câmpului, de exemplu:

NAME varchar2(30 BYTE) -- capacitatea câmpului va fi de 30 de octeți NAME varchar2(30 CHAR) -- capacitatea câmpului va fi de 30 de caractere
Ce opțiune va fi folosită implicit BYTE sau CHAR, în cazul pur și simplu specificării tipului varchar2(30) în ORACLE, depinde de setările bazei de date, iar uneori poate fi setată în setările IDE. În general, uneori te poți încurca cu ușurință, așa că în cazul ORACLE, dacă se folosește tipul varchar2 (și uneori acest lucru este justificat aici, de exemplu, când folosești codificarea UTF-8), prefer să scriu în mod explicit CHAR (deoarece de obicei este mai convenabil să se calculeze lungimea șirului în caractere).

Dar în acest caz, dacă există deja unele date în tabel, atunci pentru executarea cu succes a comenzilor este necesar ca câmpurile ID și Nume să fie completate în toate rândurile tabelului. Să demonstrăm acest lucru cu un exemplu: inserați date în tabel în câmpurile ID, Poziție și Departament; acest lucru se poate face cu următorul script:

INSERT Angajații (ID, Poziție, Departament) VALORI (1000,N"Director",N"Administrație"), (1001,N"Programator",N"IT"), (1002,N"Contabil", N"Contabilitate" ), (1003,N"Programator senior",N"IT")
În acest caz, comanda INSERT va genera și o eroare, deoarece La inserare, nu am specificat valoarea câmpului obligatoriu Nume.
Dacă aveam deja aceste date în tabelul original, atunci comanda „ALTER TABLE Employees ALTER COLUMN ID int NOT NULL” ar fi executată cu succes, iar comanda „ALTER TABLE Employees ALTER COLUMN Name int NOT NULL” ar produce un mesaj de eroare, că câmpul Nume conține valori NULL (nespecificate).

Să adăugăm valori pentru câmpul Nume și să completăm din nou datele:

Opțiunea NOT NULL poate fi folosită și direct atunci când se creează un tabel nou, de exemplu. în contextul comenzii CREATE TABLE.

Mai întâi, ștergeți tabelul folosind comanda:

DROP TABLE Angajații
Acum să creăm un tabel cu coloanele necesare ID și Nume:

CREATE TABLE Angajații (ID int NU NULL, Nume nvarchar(30) NU NULL, Data nașterii, E-mail nvarchar(30), Poziția nvarchar(30), Departament nvarchar(30))
De asemenea, puteți scrie NULL după numele coloanei, ceea ce va însemna că valorile NULL (nespecificate) vor fi permise în ea, dar acest lucru nu este necesar, deoarece această caracteristică este implicită implicită.

Dacă, dimpotrivă, doriți să faceți opțională o coloană existentă, atunci utilizați următoarea sintaxă a comenzii:

ALTER TABLE Angajații ALTER COLUMN Nume nvarchar(30) NULL
Sau pur și simplu:

ALTER TABLE Angajații ALTER COLUMN Nume nvarchar(30)
Cu această comandă putem, de asemenea, să schimbăm tipul câmpului cu un alt tip compatibil, sau să modificăm lungimea acestuia. De exemplu, să extindem câmpul Nume la 50 de caractere:

ALTER TABLE Angajații ALTER COLUMN Nume nvarchar(50)

Cheia principala

La crearea unui tabel, este de dorit ca acesta să aibă o coloană unică sau un set de coloane unic pentru fiecare dintre rândurile sale - o înregistrare poate fi identificată în mod unic prin această valoare unică. Această valoare se numește cheia primară a tabelului. Pentru tabelul nostru de angajați, o astfel de valoare unică ar putea fi coloana ID (care conține „Numărul de personal al angajatului” - chiar dacă în cazul nostru această valoare este unică pentru fiecare angajat și nu poate fi repetată).

Puteți crea o cheie primară pentru un tabel existent utilizând comanda:

ALTER TABLE Angajații ADD CONSTRAINT PK_Angajații CHEIE PRIMĂRĂ(ID)
Unde „PK_Angajați” este numele constrângerii responsabile pentru cheia primară. De obicei, cheia primară este numită folosind prefixul „PK_” urmat de numele tabelului.

Dacă cheia primară constă din mai multe câmpuri, atunci aceste câmpuri trebuie listate între paranteze, separate prin virgulă:

ALTER TABLE nume_tabel ADD CONSTRAINT nume_constrângere PRIMARY KEY(câmp1, câmp2,...)
Este de remarcat faptul că în MS SQL, toate câmpurile care sunt incluse în cheia primară trebuie să aibă caracteristica NOT NULL.

Cheia primară poate fi, de asemenea, determinată direct la crearea unui tabel, de ex. în contextul comenzii CREATE TABLE. Să ștergem tabelul:

DROP TABLE Angajații
Și apoi îl vom crea folosind următoarea sintaxă:

CREATE TABLE Angajații(ID int NU NUL, Nume nvarchar(30) NU NULL, data nașterii, e-mail nvarchar(30), Poziția nvarchar(30), Departament nvarchar(30), CONSTRAINT PK_Employees PRIMARY KEY(ID) -- descrieți PK după toate câmpurile ca o limitare)
După creare, completați tabelul cu date:

INSERT Angajații (ID, Poziție, Departament, Nume) VALORI (1000,N"Director",N"Administrație",N"Ivanov I.I.", (1001,N"Programator",N"IT",N" Petrov P.P." ), (1002,N"Contabil",N"Contabilitate",N"Sidorov S.S.", (1003,N"Programator senior",N"IT",N"Andreev A. A.")
Dacă cheia primară dintr-un tabel constă numai din valorile unei coloane, atunci puteți utiliza următoarea sintaxă:

CREATE TABLE Employees(ID int NOT NULL CONSTRAINT PK_Employees PRIMARY KEY, -- specificați ca caracteristică a câmpului Nume nvarchar(30) NOT NULL, Data nașterii, Email nvarchar(30), Poziția nvarchar(30), Departament nvarchar(30) )
De fapt, nu trebuie să specificați numele constrângerii, caz în care i se va atribui un nume de sistem (cum ar fi „PK__Employee__3214EC278DA42077”):

CREATE TABLE Angajații(ID int NU NULL, Nume nvarchar(30) NU NULL, Data nașterii, E-mail nvarchar(30), Poziția nvarchar(30), Departament nvarchar(30), CHEIE PRIMARIA (ID))
Sau:

CREATE TABLE Angajații (ID int NOT NULL CHEIE PRIMARĂ, Nume nvarchar(30) NU NULL, Data nașterii, E-mail nvarchar(30), Poziția nvarchar(30), Departament nvarchar(30))
Dar aș recomanda ca pentru tabelele permanente să setați întotdeauna în mod explicit numele constrângerii, deoarece Cu un nume specificat în mod explicit și ușor de înțeles, va fi mai ușor să îl manipulați mai târziu; de exemplu, îl puteți șterge:

ALTER TABLE Angajații DROP CONSTRAINT PK_Angajați
Dar o astfel de sintaxă scurtă, fără a specifica numele restricțiilor, este convenabilă de utilizat la crearea tabelelor de baze de date temporare (numele tabelului temporar începe cu # sau ##), care vor fi șterse după utilizare.

Să rezumam

Până acum ne-am uitat la următoarele comenzi:

• CREAȚI TABEL table_name (lista de câmpuri și tipurile acestora, restricții) – folosit pentru a crea un nou tabel în baza de date curentă;
• DROP TABLE table_name – folosit pentru a șterge un tabel din baza de date curentă;
• ALTER TABLE nume_tabel ALTER COLONA nume_coloană... – folosit pentru a actualiza tipul coloanei sau pentru a modifica setările acesteia (de exemplu, pentru a seta caracteristica NULL sau NOT NULL);
• ALTER TABLE nume_tabel ADĂUGAȚI CONSTRINGERE nume_constrângere CHEIA PRINCIPALA(câmp1, câmp2,...) – adăugarea unei chei primare la un tabel existent;
• ALTER TABLE nume_tabel CONSTRINGERE DE CĂDERARE constraint_name – elimină o constrângere din tabel.

Câteva despre mesele temporare

Extras din MSDN. Există două tipuri de tabele temporare în MS SQL Server: locale (#) și globale (##). Tabelele temporare locale sunt vizibile numai pentru creatorii lor până când sesiunea de conectare la instanța SQL Server se termină când sunt create pentru prima dată. Tabelele temporare locale sunt șterse automat după ce un utilizator se deconectează de la instanța SQL Server. Tabelele temporare globale sunt vizibile pentru toți utilizatorii în timpul oricărei sesiuni de conexiune după crearea acestor tabele și sunt șterse atunci când toți utilizatorii care fac referire la aceste tabele se deconectează de la instanța SQL Server.

Tabelele temporare sunt create în baza de date a sistemului tempdb, de exemplu. Prin crearea acestora nu înfundam baza de date principală; în caz contrar, tabelele temporare sunt complet identice cu cele obișnuite; ele pot fi șterse și folosind comanda DROP TABLE. Tabelele temporare locale (#) sunt mai frecvent utilizate.

Pentru a crea un tabel temporar, puteți folosi comanda CREATE TABLE:

CREATE TABLE #Temp(ID int, Nume nvarchar(30))
Deoarece un tabel temporar în MS SQL este similar cu un tabel obișnuit, poate fi șters și folosind comanda DROP TABLE:

DROP TABLE #Temp

De asemenea, puteți crea un tabel temporar (cum ar fi un tabel obișnuit) și îl puteți completa imediat cu datele returnate de interogare folosind sintaxa SELECT ... INTO:

SELECT ID, Nume INTO #Temp FROM Angajati

Pe o notă
Implementarea tabelelor temporare poate diferi în diferite SGBD. De exemplu, în DBMS-ul ORACLE și Firebird, structura tabelelor temporare trebuie determinată în prealabil de comanda CREATE GLOBAL TEMPORARY TABLE, indicând specificul stocării datelor în acesta, apoi utilizatorul îl vede printre tabelele principale și lucrează cu el. ca la o masă obișnuită.

Normalizarea bazei de date – împărțirea în subtabele (directoare) și identificarea conexiunilor

Tabelul nostru actual de angajați are dezavantajul că în câmpurile Poziție și Departament utilizatorul poate introduce orice text, care este în primul rând plin de erori, deoarece pentru un angajat poate indica pur și simplu „IT” ca departament, iar pentru un al doilea angajat, pentru de exemplu, introduceți „departamentul IT”, al treilea are „IT”. Ca urmare, nu va fi clar ce a vrut să spună utilizatorul, adică. Acești angajați sunt angajați ai aceluiași departament sau s-a descris utilizatorul și acestea sunt 3 departamente diferite? Mai mult, în acest caz, nu vom putea grupa corect datele pentru un raport, unde poate fi necesar să arătăm numărul de angajați pe fiecare departament.

Al doilea dezavantaj este volumul de stocare a acestor informații și duplicarea acesteia, adică. Pentru fiecare angajat este indicat numele complet al departamentului, care necesită spațiu în baza de date pentru a stoca fiecare caracter din numele departamentului.

Al treilea dezavantaj este dificultatea actualizării acestor câmpuri dacă numele unei poziții se schimbă, de exemplu, dacă trebuie să redenumiți poziția „Programator” în „Programator junior”. În acest caz, va trebui să facem modificări fiecărui rând al tabelului a cărui Poziție este egală cu „Programator”.

Pentru a evita aceste neajunsuri, se folosește așa-numita normalizare a bazei de date - împărțirea acesteia în subtabele și tabele de referință. Nu este necesar să intri în jungla teoriei și să studiezi ce sunt formele normale; este suficient să înțelegem esența normalizării.

Să creăm 2 tabele de director „Poziții” și „Departamente”, să le numim pe primele poziții, respectiv pe al doilea, Departamente:

CREATE TABLE Poziții(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Positions CHEIE PRIMARĂ, Nume nvarchar(30) NOT NULL) CREATE TABLE Departamente(ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Departments CHEIE PRIMARĂ, Nume nvarchar(30) ) NU NUL)
Rețineți că aici am folosit noua opțiune IDENTITATE, care spune că datele din coloana ID vor fi numerotate automat, începând de la 1, în trepte de 1, adică. La adăugarea de noi înregistrări, li se vor atribui succesiv valorile 1, 2, 3 etc. Astfel de câmpuri sunt de obicei numite auto-incrementare. Un tabel poate avea un singur câmp definit cu proprietatea IDENTITY și, de obicei, dar nu neapărat, acel câmp este cheia primară pentru acel tabel.

Pe o notă
În diferite SGBD, implementarea câmpurilor cu contor se poate face diferit. În MySQL, de exemplu, un astfel de câmp este definit folosind opțiunea AUTO_INCREMENT. În ORACLE și Firebird, această funcționalitate ar putea fi emulată anterior folosind SEQUENCE. Dar din câte știu, ORACLE a adăugat acum opțiunea GENERATE CA IDENTITATE.

Să completăm automat aceste tabele, pe baza datelor curente înregistrate în câmpurile Poziție și Departament din tabelul Angajați:

Completam câmpul Nume din tabelul de poziții cu valori unice din câmpul Poziție din tabelul Angajați INSERT Posiții (Nume) SELECTARE DISTINCT Poziția FROM Angajații WHERE Poziția NU ESTE NULL -- eliminați înregistrările pentru care nu este specificată postul
Să facem același lucru pentru tabelul Departamente:

INSERT Departments(Nume) SELECT DISTINCT Department FROM Angajații WHERE Departamentul NU ESTE NUL
Dacă deschidem acum tabelele Poziții și Departamente, vom vedea un set numerotat de valori pentru câmpul ID:

SELECTAȚI * FROM Poziții

SELECT * FROM Departamente

Aceste tabele vor juca acum rolul de cărți de referință pentru specificarea posturilor și departamentelor. Ne vom referi acum la ID-urile postului și departamentului. Mai întâi de toate, să creăm noi câmpuri în tabelul Angajați pentru a stoca datele de identificare:

Adăugați un câmp pentru ID-ul poziției ALTER TABLE Angajații ADD PositionID int -- adăugați un câmp pentru ID-ul departamentului ALTER TABLE Angajații ADD DepartmentID int
Tipul câmpurilor de referință trebuie să fie același ca în directoare, în acest caz este int.

De asemenea, puteți adăuga mai multe câmpuri la tabel simultan cu o singură comandă, listând câmpurile separate prin virgule:

ALTER TABLE Angajații ADD PositionID int, DepartmentID int
Acum să scriem linkuri (restricțiuni de referință - CHEIE STRĂINĂ) pentru aceste câmpuri, astfel încât utilizatorul să nu aibă posibilitatea de a scrie în aceste câmpuri valori care nu se află printre valorile ID găsite în directoare.

ALTER TABLE Angajații ADD CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERINȚE Poziții(ID)
Și vom face același lucru pentru al doilea câmp:

ALTER TABLE Angajații ADD CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERINȚE Departamente(ID)
Acum, utilizatorul va putea introduce în aceste câmpuri numai valori de ID din directorul corespunzător. În consecință, pentru a utiliza un nou departament sau poziție, va trebui mai întâi să adauge o nouă intrare în directorul corespunzător. Deoarece Pozițiile și departamentele sunt acum stocate în directoare într-o singură copie, așa că pentru a schimba numele, este suficient să îl schimbați doar în director.

Numele unei constrângeri de referință este de obicei un nume compus, constând din prefixul „FK_”, urmat de numele tabelului și urmat de un caracter de subliniere, urmat de numele câmpului care se referă la identificatorul tabelului de referință.

Un identificator (ID) este de obicei o valoare internă care este folosită numai pentru relații și ce valoare este stocată acolo este complet indiferentă în majoritatea cazurilor, așa că nu este nevoie să încerci să scapi de găurile din succesiunea de numere care apar în timpul lucrului. cu tabelul, de exemplu, după ștergerea înregistrărilor din director.

ALTER TABLE table ADD CONSTRAINT nume_constrângere FOREIGN KEY(câmp1, câmp2,…) REFERINȚE tabel_referință(câmp1, câmp2,…)
În acest caz, în tabelul „reference_table”, cheia primară este reprezentată de o combinație de mai multe câmpuri (câmp1, câmp2,...).

De fapt, acum să actualizăm câmpurile PositionID și DepartmentID cu valorile ID din directoare. Să folosim comanda DML UPDATE în acest scop:

UPDATE e SET PositionID=(SELECT ID FROM Poziții WHERE Nume=e.Posiție), DepartmentID=(SELECT ID FROM Departments WHERE Nume=e.Department) FROM Angajați e
Să vedem ce se întâmplă prin rularea cererii:

SELECTAȚI * FROM Angajați

Gata, câmpurile PositionID și DepartmentID sunt completate cu identificatorii corespunzătoare posturilor și departamentelor; câmpurile Poziție și Departament nu mai sunt necesare în tabelul Angajați, puteți șterge aceste câmpuri:

ALTER TABLE Angajații DROP COLUMN Poziția, Departamentul
Acum tabelul nostru arată astfel:

SELECTAȚI * FROM Angajați

ID	Nume	Zi de nastere	E-mail	ID poziție	ID departament
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

Acestea. În cele din urmă, am scăpat de stocarea informațiilor redundante. Acum, pe baza numerelor postului și departamentelor, putem determina fără ambiguitate numele acestora folosind valorile din tabelele de referință:

SELECT e.ID,e.Name,p.Name PositionName,d.Name DepartmentName FROM Angajați e LEFT JOIN Departamente d ON d.ID=e.DepartmentID LEFT JOIN Poziții p ON p.ID=e.PositionID

În inspectorul de obiecte putem vedea toate obiectele create pentru un tabel dat. De aici puteți efectua diverse manipulări cu aceste obiecte - de exemplu, redenumiți sau ștergeți obiecte.

De asemenea, este de remarcat faptul că tabelul se poate referi la el însuși, de ex. puteți crea un link recursiv. De exemplu, să adăugăm un alt câmp ManagerID la tabelul nostru cu angajați, care va indica angajatul căruia îi raportează acest angajat. Să creăm un câmp:

ALTER TABLE Angajații ADD ManagerID int
Acest câmp permite o valoare NULL; câmpul va fi gol dacă, de exemplu, nu există superiori peste angajat.

Acum să creăm o CHEIE STRĂINĂ pentru tabelul Angajați:

ALTER TABLE Angajații ADD CONSTRAINT FK_Employees_ManagerID CHEIE STRĂINĂ (ManagerID) REFERINȚE Angajații (ID)
Să creăm acum o diagramă și să vedem cum arată relațiile dintre tabelele noastre pe ea:

Ca rezultat, ar trebui să vedem următoarea imagine (tabelul Angajați este conectat la tabelele Poziții și Departamente și se referă și la sine):

În cele din urmă, merită spus că cheile de referință pot include opțiuni suplimentare ON DELETE CASCADE și ON UPDATE CASCADE, care indică cum să se comportă la ștergerea sau actualizarea unei înregistrări la care se face referință în tabelul de referință. Dacă aceste opțiuni nu sunt specificate, atunci nu putem schimba ID-ul din tabelul de director pentru o înregistrare care este referită dintr-un alt tabel și, de asemenea, nu vom putea șterge o astfel de înregistrare din director până când vom șterge toate rândurile care fac referire la această înregistrare. sau, Să actualizăm referințele din aceste rânduri la o valoare diferită.

De exemplu, să recreăm tabelul care specifică opțiunea ON DELETE CASCADE pentru FK_Employees_DepartmentID:

DROP TABLE Angajații CREATE TABLE Angajații(ID int NOT NULL, Nume nvarchar(30), data nașterii, e-mail nvarchar(30), PositionID int, DepartmentID int, ManagerID int, CONSTRAINT PK_Employees PRIMARY KEY (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY ) Referințe departamente (ID) pe delete cascade, constrângere fk_employees_posițiad cheie străină (pozițied) Referințe de referințe (ID), constrângere fk_employees_managerid Cheie străină (managerId) Referințe angajaților (ID)) Introduceți angajați (ID, nume, naștere, poziție, departamentd, manager, manager ID )VALORI (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)
Să ștergem departamentul cu ID 3 din tabelul Departamente:

DELETE Departamentele WHERE ID=3
Să ne uităm la datele din tabelul Angajații:

SELECTAȚI * FROM Angajați

ID	Nume	Zi de nastere	E-mail	ID poziție	ID departament	ID manager
1000	Ivanov I.I.	1955-02-19	NUL	2	1	NUL
1002	Sidorov S.S.	1976-06-07	NUL	1	2	1000

După cum puteți vedea, datele pentru departamentul 3 din tabelul Angajați au fost și ele șterse.

Opțiunea ON UPDATE CASCADE se comportă în mod similar, dar este eficientă la actualizarea valorii ID din director. De exemplu, dacă schimbăm ID-ul unei poziții din directorul de poziții, atunci în acest caz DepartmentID din tabelul Angajați va fi actualizat la noua valoare ID pe care am setat-o ​​în director. Dar în acest caz pur și simplu nu va fi posibil să se demonstreze acest lucru, deoarece coloana ID din tabelul Departamente are opțiunea IDENTITATE, care nu ne va permite să executăm următoarea interogare (modificați ID-ul departamentului de la 3 la 30):

UPDATE Departamente SET ID=30 WHERE ID=3
Principalul lucru este să înțelegeți esența acestor 2 opțiuni ON DELETE CASCADE și ON UPDATE CASCADE. Folosesc foarte rar aceste opțiuni și vă recomand să vă gândiți bine înainte de a le specifica într-o constrângere de referință, deoarece dacă ștergeți accidental o intrare dintr-un tabel de director, acest lucru poate duce la probleme mari și poate crea o reacție în lanț.

Să restabilim departamentul 3:

Oferim permisiunea de a adăuga/modifica valoarea IDENTITY SET IDENTITY_INSERT Departments ON INSERT Departments(ID,Name) VALUES(3,N"IT") -- interzicem adăugarea/modificarea IDENTITY value SET IDENTITY_INSERT Departments OFF
Să ștergem complet tabelul de angajați folosind comanda TRUNCATE TABLE:

TRUNCATE TABLE Angajații
Și din nou vom reîncărca datele în ele folosind comanda anterioară INSERT:

INSERT Angajații (ID, Nume, Zi de naștere, ID Poziție, ID Departament, ID Manager) VALORI (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)

Să rezumam

În acest moment, mai multe comenzi DDL au fost adăugate la cunoștințele noastre:

• Adăugarea proprietății IDENTITATE la un câmp – vă permite să faceți din acest câmp un câmp populat automat (câmp contor) pentru tabel;
• ALTER TABLE nume_tabel ADĂUGA list_of_fields_with_characteristics – vă permite să adăugați noi câmpuri la tabel;
• ALTER TABLE nume_tabel COLOCARE COLONA list_fields – vă permite să eliminați câmpuri din tabel;
• ALTER TABLE nume_tabel ADĂUGAȚI CONSTRINGERE nume_constrângere CHEIE EXTERNĂ(câmpuri) REFERINȚE table_reference (câmpuri) – vă permite să definiți relația dintre tabel și tabelul de referință.

Alte restricții – UNIC, DEFAULT, VERIFICARE

Folosind o constrângere UNIQUE, puteți spune că valoarea pentru fiecare rând dintr-un anumit câmp sau set de câmpuri trebuie să fie unică. În cazul tabelului Angajați, putem impune o astfel de constrângere câmpului Email. Doar precompletați e-mailul cu valori dacă acestea nu sunt deja definite:

UPDATE Angajații SET Email=" [email protected]" WHERE ID=1000 UPDATE Angajații SET Email=" [email protected]" WHERE ID=1001 UPDATE Angajații SET Email=" [email protected]" WHERE ID=1002 UPDATE Angajații SET Email=" [email protected]„WHERE ID=1003
Acum puteți impune o constrângere de unicitate pe acest câmp:

ALTER TABLE Angajații ADD CONSTRAINT UQ_Employees_Email UNIQUE(E-mail)
Acum utilizatorul nu va putea introduce același e-mail pentru mai mulți angajați.

O constrângere unică este de obicei numită după cum urmează - mai întâi vine prefixul „UQ_”, apoi numele tabelului și după liniuță vine numele câmpului pe care se aplică această constrângere.

În consecință, dacă o combinație de câmpuri trebuie să fie unică în contextul rândurilor de tabel, atunci le enumerăm separate prin virgule:

ALTER TABLE nume_tabel ADD CONSTRAINT nume_constrângere UNIQUE(câmp1, câmp2,...)
Adăugând o constrângere DEFAULT unui câmp, putem specifica o valoare implicită care va fi înlocuită dacă, la inserarea unei noi înregistrări, acest câmp nu este listat în lista de câmpuri a comenzii INSERT. Această restricție poate fi setată direct la crearea tabelului.

Să adăugăm un nou câmp pentru Data angajării în tabelul Angajați și să îl numim HireDate și să spunem că valoarea implicită pentru acest câmp va fi data curentă:

ALTER TABLE Angajații ADD HireDate data NOT NULL DEFAULT SYSDATETIME()
Sau dacă coloana HireDate există deja, atunci se poate folosi următoarea sintaxă:

ALTER TABLE Angajații ADĂUGAȚI IMPLICIT SYSDATETIME() PENTRU HireDate
Aici nu am specificat numele constrângerii, pentru că... în cazul DEFAULT, am părerea că acest lucru nu este atât de critic. Dar dacă o faci într-un mod bun, atunci cred că nu trebuie să fii leneș și ar trebui să setezi un nume normal. Acest lucru se face după cum urmează:

ALTER TABLE Angajații ADD CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME() FOR HireDate
Deoarece această coloană nu exista înainte, atunci când este adăugată la fiecare înregistrare, valoarea datei curente va fi inserată în câmpul HireDate.

Când adăugați o nouă intrare, data curentă va fi de asemenea inserată automat, desigur, cu excepția cazului în care o setăm în mod explicit, de exemplu. Nu o vom indica în lista de coloane. Să arătăm acest lucru cu un exemplu fără a specifica câmpul HireDate din lista de valori adăugate:

INSERT Angajații(ID,Nume,E-mail)VALUES(1004,N"Sergeev S.S."," [email protected]")
Să vedem ce s-a întâmplat:

SELECTAȚI * FROM Angajați

ID	Nume	Zi de nastere	E-mail	ID poziție	ID departament	ID manager	Data angajării
1000	Ivanov I.I.	1955-02-19	[email protected]	2	1	NUL	2015-04-08
1001	Petrov P.P.	1983-12-03	[email protected]	3	4	1003	2015-04-08
1002	Sidorov S.S.	1976-06-07	[email protected]	1	2	1000	2015-04-08
1003	Andreev A.A.	1982-04-17	[email protected]	4	3	1000	2015-04-08
1004	Sergheev S.S.	NUL	[email protected]	NUL	NUL	NUL	2015-04-08

Constrângerea de verificare CHECK este utilizată atunci când este necesară verificarea valorilor introduse într-un câmp. De exemplu, să impunem această restricție în câmpul numărului de personal, care pentru noi este un identificator de angajat (ID). Folosind această constrângere, spunem că numerele de personal trebuie să aibă o valoare de la 1000 la 1999:

ALTER TABLE Angajații ADD CONSTRAINT CK_Employees_ID VERIFICARE (ID ÎNTRE 1000 ȘI 1999)
Constrângerea este de obicei denumită la fel, mai întâi cu prefixul „CK_”, apoi numele tabelului și numele câmpului asupra căruia se impune această constrângere.

Să încercăm să inserăm o înregistrare nevalidă pentru a verifica dacă constrângerea funcționează (ar trebui să obținem eroarea corespunzătoare):

INSERT Angajații(ID,E-mail) VALUES(2000," [email protected]")
Acum să schimbăm valoarea inserată la 1500 și să ne asigurăm că înregistrarea este inserată:

INSERT Angajații(ID,E-mail) VALUES(1500," [email protected]")
De asemenea, puteți crea constrângeri UNIQUE și CHECK fără a specifica un nume:

ALTER TABLE Angajații ADD UNIQUE(E-mail) ALTER TABLE Angajații ADD CHECK(ID ÎNTRE 1000 ȘI 1999)
Dar aceasta nu este o practică foarte bună și este mai bine să specificați în mod explicit numele constrângerii, deoarece Pentru a vă da seama mai târziu, ceea ce va fi mai dificil, va trebui să deschideți obiectul și să vă uitați la ce este responsabil.

Cu un nume bun, multe informații despre constrângere pot fi învățate direct din numele acesteia.

Și, în consecință, toate aceste restricții pot fi create imediat la crearea unui tabel, dacă acesta nu există încă. Să ștergem tabelul:

DROP TABLE Angajații
Și îl vom recrea cu toate restricțiile create cu o singură comandă CREATE TABLE:

CREATE TABLE Angajații(ID int NOT NULL, Nume nvarchar(30), Data nașterii, Email nvarchar(30), PositionID int, DepartmentID int, HireDate date NOT NULL DEFAULT SYSDATETIME(), -- pentru DEFAULT voi face o excepție CONSTRAINT PK_Employees CHEIE PRIMARĂ (ID), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERINȚE Departamente (ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERINȚE Poziții (ID), CONSTRAINT UQ_Employees_EmployeCKIDECKIDE-mail (Angajați_E-mail) WEEN 1000 ȘI 1999))

INSERT Angajații (ID, Nume, Zi de naștere, E-mail, ID Poziție, ID Departament) VALORI (1000,N"Ivanov I.I.","19550219"," [email protected]",2,1), (1001,N"Petrov P.P.","19831203"," [email protected]",3,3), (1002,N"Sidorov S.S.","19760607"," [email protected]",1,2), (1003,N"Andreev A.A.","19820417"," [email protected]",4,3)

Câteva despre indecșii creați la crearea constrângerilor PRIMARY KEY și UNIQUE

După cum puteți vedea în captura de ecran de mai sus, la crearea constrângerilor PRIMARY KEY și UNIQUE, au fost creați automat indici cu aceleași nume (PK_Employees și UQ_Employees_Email). În mod implicit, indexul pentru cheia primară este creat ca CLUSTERED, iar pentru toți ceilalți indecși ca NONCLUSTERED. Merită spus că conceptul de index de cluster nu este disponibil în toate SGBD-urile. Un tabel poate avea un singur index CLUSTERED. CLUSTERED – înseamnă că înregistrările din tabel vor fi sortate după acest index, mai putem spune că acest index are acces direct la toate datele din tabel. Acesta este indexul principal al tabelului, ca să spunem așa. Pentru a spune și mai aproximativ, acesta este un index atașat unui tabel. Un index grupat este un instrument foarte puternic care poate ajuta la optimizarea interogărilor, dar să ne amintim doar acest lucru pentru moment. Dacă vrem să spunem ca indexul grupat să fie folosit nu pe cheia primară, ci pe un alt index, atunci când creăm cheia primară trebuie să specificăm opțiunea NONCLUSTERED:

ALTER TABLE nume_tabel ADD CONSTRAINT nume_constrângere PRIMARY KEY NONCLUSTERED(câmp1,câmp2,…)
De exemplu, să facem ca indicele de constrângere PK_Employees să nu fie în cluster, iar indicele de constrângere UQ_Employees_Email să fie grupat. În primul rând, să eliminăm aceste restricții:

ALTER TABLE Angajații DROP CONSTRAINT PK_Angajați ALTER TABLE Angajații DROP CONSTRAINT UQ_Employees_Email
Acum să le creăm cu opțiunile CLUSTERED și NONCLUSTERED:

ALTER TABLE Angajații ADD CONSTRAINT PK_Employees CHEIE PRIMARIA NONCLUSTERED (ID) ALTER TABLE Angajații ADD CONSTRAINT UQ_Employees_Email UNIQUE CLUSTERED (E-mail)
Acum, selectând din tabelul Angajați, vom vedea că înregistrările sunt sortate după indexul grupat UQ_Employees_Email:

SELECTAȚI * FROM Angajați

ID	Nume	Zi de nastere	E-mail	ID poziție	ID departament	Data angajării
1003	Andreev A.A.	1982-04-17	[email protected]	4	3	2015-04-08
1000	Ivanov I.I.	1955-02-19	[email protected]	2	1	2015-04-08
1001	Petrov P.P.	1983-12-03	[email protected]	3	3	2015-04-08
1002	Sidorov S.S.	1976-06-07	[email protected]	1	2	2015-04-08

Anterior, când indexul grupat era indexul PK_Employees, înregistrările erau sortate în mod implicit după câmpul ID.

Dar în acest caz, acesta este doar un exemplu care arată esența unui index cluster, deoarece Cel mai probabil, interogările vor fi făcute către tabelul Angajați folosind câmpul ID și, în unele cazuri, poate, acesta va acționa ca un director.

Pentru directoare, este de obicei recomandabil ca indexul clusterizat să fie construit pe cheia primară, deoarece în cereri ne referim adesea la identificatorul directorului pentru a obține, de exemplu, numele (Posiție, Departament). Să ne amintim aici ceea ce am scris mai sus, că un index grupat are acces direct la rândurile tabelului și rezultă că putem obține valoarea oricărei coloane fără suprasarcină suplimentară.

Este avantajos să aplicați un index de cluster câmpurilor care sunt eșantionate cel mai frecvent.

Uneori, tabelele sunt create cu o cheie bazată pe un câmp surogat; în acest caz, poate fi util să salvați opțiunea index CLUSTERED pentru un index mai potrivit și să specificați opțiunea NONCLUSTERED atunci când creați o cheie primară surogat.

Să rezumam

În această etapă, ne-am familiarizat cu toate tipurile de restricții, în forma lor cea mai simplă, care sunt create printr-o comandă precum „ALTER TABLE table_name ADD CONSTRAINT constraint_name...”:

• CHEIA PRINCIPALA- cheia principala;
• CHEIE EXTERNĂ– stabilirea conexiunilor și monitorizarea integrității referențiale a datelor;
• UNIC– vă permite să creați unicitate;
• VERIFICA– vă permite să asigurați corectitudinea datelor introduse;
• MOD IMPLICIT– vă permite să setați o valoare implicită;
• De asemenea, merită remarcat faptul că toate restricțiile pot fi eliminate folosind comanda „ ALTER TABLE nume_tabel CONSTRINGERE DE CĂDERARE nume_constrângere”.

De asemenea, am atins parțial subiectul indicilor și am examinat conceptul de cluster ( CLUSTRATE) și non-cluster ( NEGRUPAT) index.

Crearea de indici autonomi

Prin independent ne referim aici la indecși care nu sunt creați sub constrângerea PRIMARY KEY sau UNIQUE.

Indecșii unui câmp sau câmpuri pot fi creați cu următoarea comandă:

CREATE INDEX IDX_Employees_Name ON Angajati(Nume)
Tot aici puteți specifica opțiunile CLUSTERED, NONCLUSTERED, UNIQUE și, de asemenea, puteți specifica direcția de sortare a fiecărui câmp individual ASC (implicit) sau DESC:

CREAȚI INDEX UNIC NONCLUSTERED UQ_Employees_EmailDesc ON Angajații (E-mail DESC)
La crearea unui index non-clustered, opțiunea NONCLUSTERED poate fi omisă, deoarece este implicit implicit și este afișat aici pur și simplu pentru a indica poziția opțiunii CLUSTERED sau NONCLUSTERED în comandă.

Puteți șterge indexul cu următoarea comandă:

DROP INDEX IDX_Employees_Name ON Angajații
Indecșii simpli, precum și constrângerile, pot fi creați în contextul comenzii CREATE TABLE.

De exemplu, să ștergem din nou tabelul:

DROP TABLE Angajații
Și îl vom recrea cu toate restricțiile și indecșii creați cu o singură comandă CREATE TABLE:

CREATE TABLE Angajații(ID int NOT NULL, Nume nvarchar(30), Data zilei de naștere, Email nvarchar(30), PositionID int, DepartmentID int, HireDate data NOT NULL CONSTRAINT DF_Employees_HireDate DEFAULT SYSDATETIME(), ManagerID int, CONSTRAINT PK_Employees PRIMARY (PRIMARY) ), CONSTRAINT FK_Employees_DepartmentID FOREIGN KEY(DepartmentID) REFERINȚE Departamente(ID), CONSTRAINT FK_Employees_PositionID FOREIGN KEY(PositionID) REFERINȚE Poziții(ID), CONSTRAINT FK_Employees_ManagerID FOREIGN KEY(Employees_ManagerID) REFERINȚE CHEIE STRĂINĂ(Employees_Manager) REEMPLICAȚII es_Email UNIQUE(E-mail), CONSTRAINT CK_Employees_ID CHECK(ID ÎNTRE 1000 ȘI 1999), INDEX IDX_Employees_Name(Nume))
În cele din urmă, să introducem angajații noștri în tabel:

INSERT ANgajați (ID, Nume, Zi de naștere, E-mail, ID poziția, ID departament, ID manager) VALORI (1000,N"Ivanov I.I.","19550219"," [email protected]",2,1,NULL), (1001,N"Petrov P.P.","19831203"," [email protected]",3,3,1003), (1002,N"Sidorov S.S.","19760607"," [email protected]",1,2,1000), (1003,N"Andreev A.A.","19820417"," [email protected]",4,3,1000)
În plus, este de remarcat faptul că puteți include valori într-un index non-cluster, specificându-le în INCLUDE. Acestea. în acest caz, indicele INCLUDE va ​​aminti oarecum de un index grupat, doar că acum indicele nu este atașat la tabel, dar valorile necesare sunt atașate la index. În consecință, astfel de indecși pot îmbunătăți considerabil performanța interogărilor de selecție (SELECT); dacă toate câmpurile listate sunt în index, atunci accesul la tabel poate să nu fie deloc necesar. Dar acest lucru crește în mod natural dimensiunea indicelui, deoarece valorile câmpurilor enumerate sunt duplicate în index.

Extras din MSDN. Sintaxă generală de comandă pentru crearea indecșilor

CREATE [UNIQUE] [CLUSTER | NONCLUSTERED ] INDEX nume_index ACTIVAT