Share
Este important ca fiecare dezvoltator web să fie familiarizat cu interacțiunile bazei de date. În partea a doua a seriei, vom continua să explorăm limbajul SQL și să aplicăm ceea ce am învățat într-o bază de date MySQL. Vom învăța despre indici, tipuri de date și structuri de interogare mai complexe.
Consultați secțiunea "Ce aveți nevoie" în primul articol aici: SQL pentru începători (partea 1).
Dacă doriți să urmați exemplele din acest articol pe propriul server de dezvoltare, procedați în felul următor:
Indicele (sau cheile) sunt utilizate în principal pentru îmbunătățirea vitezei operațiilor de recuperare a datelor (de exemplu, SELECT) pe tabele.
Ele sunt o parte atât de importantă a unui design bun de baze de date, este greu de clasificat ca "optimizare". În majoritatea cazurilor ele sunt incluse în proiectul inițial, dar pot fi adăugate mai târziu cu o interogare ALTER TABLE.
Cele mai frecvente motive pentru indexarea coloanelor bazei de date sunt:
Aproape fiecare tabelă trebuie să aibă un KEY PRIMARY, în majoritatea cazurilor ca INT cu opțiunea AUTO_INCREMET.
Dacă vă reamintim din primul articol, am creat un câmp "user_id" în tabelul utilizatorilor și acesta a fost un KEY PRIMARY. În acest fel, într-o aplicație web, putem să ne referim la toți utilizatorii prin numerele lor de identificare.
Valorile stocate într-o coloană PRIMARY KEY trebuie să fie unice. De asemenea, nu poate exista mai mult de un KEY PRIMAR pe fiecare masă.
Să vedem o interogare eșantion, creând un tabel pentru lista Statelor Unite:
CREATE TABLE state (id INT AUTO_INCREMENT PRIMARY KEY, denumire VARCHAR (20));
De asemenea, poate fi scris astfel:
CREATE TABLE state (id INT AUTO_INCREMENT, nume VARCHAR (20), KEY PRIMARY (id));
Deoarece așteptăm ca numele de stat să fie o valoare unică, ar trebui să schimbăm exemplul interogării anterioare un pic:
CREATE TABLE state (ID INT AUTO_INCREMENT, nume VARCHAR (20), KEY PRIMARY (id), UNIQUE (nume));
În mod prestabilit, indexul va fi denumit după numele coloanei. Dacă doriți, puteți să îi alocați un nume diferit:
CREATE TABLE state (id INT AUTO_INCREMENT, nume VARCHAR (20), KEY PRIMARY (id), UNIQUE state_name (nume));
Acum, indexul este denumit "state_name" în loc de "name".
Să presupunem că dorim să adăugăm o coloană care să reprezinte anul în care fiecare stat sa alăturat.
CREATE TABLE state (id INT AUTO_INCREMENT, nume VARCHAR (20), join_year INT, KEY PRIMARY (id), UNIQUE (nume), INDEX (join_year));
Tocmai am adăugat coloana join_year și l-am indexat. Acest tip de index nu are restricția de unicitate.
De asemenea, puteți să îl numiți KEY în loc de INDEX.
CREATE TABLE state (id INT AUTO_INCREMENT, nume VARCHAR (20), join_year INT, KEY PRIMARY (id), UNIQUE (nume), KEY (join_year));
Adăugarea unui index reduce performanțele interogărilor INSERT și UPDATE. Deoarece de fiecare dată când sunt adăugate date noi în tabel, datele de index sunt, de asemenea, actualizate automat, ceea ce necesită o muncă suplimentară. Câștigurile de performanță din interogările SELECT depășesc de obicei acest lucru de departe. Dar, totuși, nu adăugați doar indici pe fiecare coloană de tabel fără să vă gândiți la întrebările pe care le veți rula.
Înainte de a merge mai departe cu mai multe interogări, aș dori să creez un tabel cu anumite date.
Aceasta va fi o listă a Statelor Unite, cu datele lor de aderare (data la care statul a ratificat Constituția Statelor Unite sau a fost admisă în Uniune) și populațiile lor actuale. Puteți să copiați următoarele pe consola MySQL:
CREATE TABLE state (id INT AUTO_INCREMENT, nume VARCHAR (20), join_year INT, populație INT, KEY PRIMARY (id), UNIQUE (nume), KEY (join_year)); INSERT ÎN STATELE VALORI (1, "Alabama", 1819, 4661900), (2, "Alaska", 1959, 686293), 3 Arizona, 1912, 6500180, 4, Arkansas 1836, 2855390 ), (5, 'California', 1850, 36756666), (6, 'Colorado', 1876, 4939456), (7, Connecticut 1788, 3501252) (11, "Hawaii", 1959, 1288198), (12, "Idaho", 1890, 1523816), (13, Florida, 1845, 18328340) , Illinois, 1818, 12901563), (14, Indiana, 1816, 6376792), (15, Iowa, 1846, 3002555) Kentucky ", 1792, 4269245), (18," Louisiana ", 1812, 4410796), (19, Maine, 1820, 1316456), (20, Maryland, 1788, 5633597) , 1788, 6497967), (22, Michigan, 1837, 10003422), (23, Minnesota, 1858, 5220393), Mississippi 1817, 2938618, (27, "Nebraska", 1867, 1783432), (28, "Nevada", 1864, 2600167), (29, New Hampshire, 1788, 1315809), (30, New Jersey, 1787, 8682) (31, "New Mexico", 1912, 1984356), (32, "New York", 1788, 19490297), (33, North Carolina, 1789, 9222414) 1889, 641481), (35, "Ohio", 1803, 11485910), (36, "Oklahoma", 1907, 3642361), 37, Oregon, 1859, 3790060) (40, "South Carolina", 1788, 4479800), (41, South Dakota, 1889, 804194), (42, 'Tennessee', 1796 , 6214888), (43, 'Texas', 1845, 24326974), (44,'Utah ', 1896, 2736424), (45,' Vermont ', 1791, 621270), (46, Virginia, 1788, 7769089 ), (47, "Washington", 1889, 6549224), (48, West Virginia, 1863,1814468), 49, Wisconsin, 1848, 5627967, 50, Wyoming, ;
Clauza GROUP BY grupează rândurile de date rezultate în grupuri. Iată un exemplu:
Ce sa întâmplat? Avem 50 de rânduri în tabel, dar 34 de rezultate au fost returnate de această interogare. Acest lucru se datorează faptului că rezultatele au fost grupate în coloana "join_year". Cu alte cuvinte, vedem doar un rând pentru fiecare valoare distinctă a join_year. Deoarece unele state au acelasi join_year, avem mai putin de 50 de rezultate.
De exemplu, a existat un singur rând pentru anul 1787, dar în acest grup există 3 state:
Există trei state aici, dar numai numele lui Delaware a apărut după interogarea GROUP BY mai devreme. De fapt, ar fi putut fi oricare dintre cele trei state și nu ne putem baza pe această piesă de date. Atunci, care este punctul de utilizare a clauzei GROUP BY?
Ar fi inutilă fără a utiliza o funcție agregată cum ar fi COUNT (). Să vedem ce fac unele dintre aceste funcții și cum ne pot obține niște date utile.
Aceasta este probabil funcția cea mai frecvent utilizată împreună cu interogările GROUP BY. Acesta returnează numărul de rânduri din fiecare grup.
De exemplu, îl putem folosi pentru a vedea numărul de stări pentru fiecare join_year:
Dacă utilizați o funcție agregată GROUP BY și nu specificați o clauză GROUP BY, toate rezultatele vor fi plasate într-un singur grup.
Numărul tuturor rândurilor din tabel:
Numărul de rânduri care îndeplinesc o clauză WHERE:
Aceste funcții returnează valorile minime, maxime și medii:
Această funcție concatetează toate valorile din interiorul grupului într-un singur șir, cu un separator dat.
În primul exemplu de interogare GROUP BY, am putut vedea doar un nume de stat pe an. Puteți utiliza această funcție pentru a vedea toate numele din fiecare grup:
Dacă imaginea redimensionată este greu de citit, aceasta este interogarea:
SELECT GROUP_CONCAT (nume SEPARATOR ','), join_year din stările GROUP BY join_year;
Puteți utiliza această funcție pentru a adăuga valorile numerice.
Similar cu alte limbi de programare, SQL are un suport pentru fluxul de control.
Aceasta este o funcție care necesită trei argumente. Primul argument este condiția, al doilea argument este utilizat dacă condiția este adevărată, iar al treilea argument este folosit dacă condiția este falsă.
Iată un exemplu mai practic în care îl folosim cu funcția SUM ():
SELECT SUM (DACĂ (populație> 5000000, 1, 0)) AS big_states, SUM (IF (populație <= 5000000, 1, 0) ) AS small_states FROM states;
Primul apel SUM () numără numărul de state mari (populație de peste 5 milioane), iar al doilea numără numărul de state mici. Apelul IF () din cadrul acestor apeluri SUM () returnează fie 1, fie 0 în funcție de condiție.
Iată rezultatul:
Acest lucru funcționează similar cu instrucțiunile de la caz la care s-ar putea să fiți familiarizați din programare.
Să presupunem că vrem să clasificăm fiecare stat într-una din cele trei categorii posibile.
SELECT COUNT (*), CASE CÂND populația> 5000000 THEN 'mare' WHEN populație> 1000000 THEN 'mediu' ELSE 'mici' END AS state_size din state GROUP BY state_size;
După cum puteți vedea, putem de fapt GROUP de valoarea returnată din instrucțiunea CASE. Iată ce se întâmplă:
Clauza HAVING ne permite să aplicăm condiții în câmpurile "ascunse", cum ar fi rezultatele returnate ale funcțiilor agregate. Deci este de obicei folosit împreună cu GROUP BY.
De exemplu, să aruncăm o privire la interogarea pe care am folosit-o pentru a număra numărul de state pe anul de înscriere:
SELECT COUNT (*), join_year din stările GROUP BY join_year;
Rezultatul a fost de 34 de rânduri.
Cu toate acestea, să presupunem că suntem interesați doar de rânduri care au un număr mai mare decât 1. Nu putem folosi clauza WHERE pentru aceasta:
Acesta este locul în care HAVING devine util:
Rețineți că este posibil ca această caracteristică să nu fie disponibilă în toate sistemele de baze de date.
Este posibil să obțineți rezultatele unei interogări și să o utilizați pentru o altă interogare.
În acest exemplu, vom obține statul cu cea mai mare populație:
SELECT * FROM state WHERE populație = (SELECT MAX (populație) FROM state);
Interogarea interioară va returna cea mai mare populație din toate statele. Și interogarea exterioară va căuta din nou tabelul folosind acea valoare.
S-ar putea să vă gândiți că acesta este un exemplu rău, și sunt oarecum de acord. Aceeași interogare ar putea fi scrisă mai eficient ca aceasta:
SELECT * FROM stări ORDERE după populație DESC LIMIT 1;
Rezultatele în acest caz sunt aceleași, însă există o diferență importantă între aceste două tipuri de interogări. Poate un alt exemplu va demonstra asta mai bine.
În acest exemplu, vom obține ultimele state care au aderat la Uniune:
SELECT * FROM state WHERE join_year = (SELECT MAX (join_year) FROM state);
Există două rânduri în rezultate de această dată. Dacă am fi folosit tipul de interogare ORDER BY ... LIMIT 1, nu am fi primit același rezultat.
Uneori este posibil să doriți să utilizați mai multe rezultate returnate de interogarea internă.
În urma interogării se află anii, când mai multe state au aderat la Uniune și returnează lista acestor state:
SELECT * din stări WHERE join_year IN (SELECT join_year din stările GROUP BY join_year HAVING COUNT (*)> 1) ORDER BY join_year;
Subchizitele pot deveni destul de complexe, prin urmare nu voi ajunge mult mai mult în ele în acest articol. Dacă doriți să citiți mai multe despre acestea, consultați manualul MySQL.
De asemenea, merită remarcat faptul că sub-cererile pot avea uneori performanțe proaste, deci ar trebui folosite cu prudență.
Cu o interogare UNION, putem combina rezultatele mai multor interogări SELECT.
Acest exemplu combină state care încep cu litera "N" și state cu populații mari:
(SELECT * FROM state WHERE nume LIKE 'n%') UNION (SELECT * FROM state WHERE populație> 10000000);
Rețineți că New York este atât de mare, iar numele său începe cu litera "N". Dar se afișează o singură dată, deoarece rândurile duplicate sunt eliminate din rezultate în mod automat.
Un alt lucru frumos despre UNION este că puteți combina interogări pe diferite mese.
Să presupunem că avem mese pentru angajați, manageri și clienți. Și fiecare tabel are un câmp de poștă electronică. Dacă vrem să preluăm toate e-mailurile cu o singură interogare, putem rula acest lucru:
(SELECT e-mail de la angajați) UNION (SELECT email de la manageri) UNION (SELECT e-mail de la clienți WHERE subscris = 1);
Aceasta ar aduce toate e-mailurile tuturor angajaților și managerilor, dar numai e-mailurile clienților care s-au abonat la primirea e-mailurilor.
Am vorbit deja despre interogarea INSERT din ultimul articol. Acum, că am explorat indici de baze de date astăzi, putem vorbi despre caracteristici mai avansate ale interogării INSERT.
Aceasta este aproape ca o declarație condiționată. Interogarea mai întâi încearcă să execute un INSERT dat și, dacă nu reușește din cauza unei valori duplicate pentru un KEY PRIMARY sau UNIQUE KEY, acesta efectuează în schimb un UPDATE.
Să facem mai întâi un tabel de testare.
Este un tabel care conține produse. Coloana "stoc" este numărul de produse pe care le avem în stoc.
Acum încearcă să introduci o valoare duplicată și să vezi ce se întâmplă.
Am apărut o eroare conform așteptărilor.
Să presupunem că am primit un nou producător de pâine și doriți să actualizați baza de date și nu știm dacă există deja o înregistrare pentru aceasta. Am putea verifica înregistrările existente și apoi am făcut o altă interogare bazată pe asta. Sau am putea face totul într-o singură interogare:
Funcționează exact ca INSERT cu o excepție importantă. Dacă se găsește un rând duplicat, îl șterge mai întâi și apoi execută INSERT-ul, deci nu primim mesaje de eroare.
Rețineți că, deoarece acesta este de fapt un rând complet nou, codul a fost incrementat.
Aceasta este o modalitate de a suprima erorile duplicate, de obicei pentru a împiedica ruperea aplicației. Uneori este posibil să doriți să încercați să inserați un rând nou și să lăsați-o să eșueze fără plângeri în cazul în care există un duplicat găsit.
Nu s-au returnat erori și nu s-au actualizat rânduri.
Fiecare coloană de tabelă trebuie să aibă un tip de date. Până acum, am folosit tipurile INT, VARCHAR și DATE, dar nu am vorbit despre ele în detaliu. De asemenea, există și alte câteva tipuri de date pe care ar trebui să le explorăm.
În primul rând, să începem cu tipurile de date numerice. Îmi place să le pun în două grupuri separate: Întregi vs. Non-Integratori.
O coloană întregă poate conține numai numere naturale (fără zecimale). Implicit ele pot fi numere negative sau pozitive. Dar dacă opțiunea UNSIGNED este setată, aceasta poate conține numai numere pozitive.
MySQL suportă 5 tipuri de numere întregi, cu diferite dimensiuni și intervale:
Aceste tipuri de date pot conține numere zecimale: FLOAT, DOUBLE și DECIMAL.
FLOAT este de 4 octeți, DOUBLE este de 8 octeți și funcționează similar. Cu toate acestea, DOUBLE are o precizie mai bună.
DECIMAL (M, N) are o mărime diferită bazată pe nivelul de precizie, care poate fi personalizat. M este numărul maxim de cifre și N este numărul de cifre din dreapta punctului zecimal.
De exemplu, DECIMAL (13,4) are maximum 9 cifre întregi și 4 cifre fracționate.
După cum sugerează și numele, putem stoca șiruri de caractere în aceste coloane tip de date.
CHAR (N) poate să dețină până la N de caractere și are o dimensiune fixă. De exemplu, CHAR (50) va dura întotdeauna 50 de caractere de spațiu, pe rând, indiferent de mărimea șirului din ea. Maximul absolut este de 255 de caractere
VARCHAR (N) funcționează la fel, dar dimensiunea stocării nu este fixă. N este utilizat numai pentru dimensiunea maximă. Dacă un șir mai scurt decât caracterele N este stocat, va dura mult mai puțin spațiu pe hard disk. Dimensiunea maximă absolută este de 65535 de caractere.
Variațiile tipului de date TEXT sunt mai potrivite pentru șiruri lungi. TEXT are o limită de 65535 de caractere, MEDIUMTEXT 16,7 milioane de caractere și LONGTEXT 4,3 miliarde de caractere. MySQL le stochează de obicei pe locații separate de pe server, astfel încât spațiul de stocare principal al mesei rămâne relativ mic și rapid.
DATE stochează datele și le afișează în acest format "AAAA-MM-DD", dar nu conține informațiile despre timp. Are o gamă de 1001-01-01 până la 9999-12-31.
DATETIME conține atât data cât și ora și este afișat în acest format "AAAA-MM-DD HH: MM: SS". Are o serie de '1000-01-01 00:00:00' la '9999-12-31 23:59:59'. Este nevoie de 8 octeți de spațiu.
TIMESTAMP funcționează ca DATETIME cu câteva excepții. Este nevoie de doar 4 octeți de spațiu, iar intervalul este '1970-01-01 00:00:01' UTC până '2038-01-19 03:14:07' UTC. De exemplu, este posibil să nu fie bine pentru stocarea datelor de naștere.
TIME memorează numai ora și YEAR stochează numai anul.
Există și alte tipuri de date acceptate de MySQL. Puteți vedea o listă a acestora aici. De asemenea, trebuie să verificați dimensiunile de stocare ale fiecărui tip de date aici.
Vă mulțumim că ați citit articolul. SQL este un limbaj important și un instrument în arsenalul dezvoltatorilor web.
Vă rugăm să lăsați comentariile și întrebările dvs. și să aveți o zi minunată!
Sunteți gata să vă abilitați la nivelul următor și să începeți să profitați de scripturile și componentele dvs.? Verificați piața sora noastră, CodeCanyon.
Accentsconagua