Ogni sviluppatore web deve conoscere SQL per scrivere query di database. E, sebbene phpMyAdmin non sia stato cancellato, è spesso necessario sporcarsi le mani per scrivere SQL di basso livello.

Ecco perché abbiamo preparato un breve tour delle basi di SQL. Iniziamo!

1. Crea una tabella

L'istruzione CREATE TABLE viene utilizzata per creare tabelle. Gli argomenti devono essere il nome delle colonne, nonché i relativi tipi di dati.

Creiamo una semplice tabella denominata mese. Si compone di 3 colonne:

• id– Numero del mese dell'anno civile (numero intero).
• nome– Nome del mese (stringa, massimo 10 caratteri).
• giorni– Numero di giorni in questo mese (numero intero).

Ecco come sarebbe la query SQL corrispondente:

CREATE TABLE mesi (id int, nome varchar(10), giorni int);

Inoltre, durante la creazione di tabelle, è consigliabile aggiungere una chiave primaria per una delle colonne. Ciò manterrà i record univoci e accelererà le query selezionate. Sia nel nostro caso il nome del mese univoco (colonna nome)

CREATE TABLE mesi (id int, nome varchar(10), giorni int, PRIMARY KEY (nome));

data e ora

Tipo di dati	Descrizione
DATA	Valori data
APPUNTAMENTO	Valori di data e ora con precisione al minuto
VOLTA	Valori temporali

2. Inserisci le righe

Ora riempiamo la nostra tabella mesi informazioni utili. L'aggiunta di record a una tabella viene eseguita tramite l'istruzione INSERT. Ci sono due modi per scrivere questa istruzione.

Il primo modo è non specificare i nomi delle colonne in cui verranno inseriti i dati, ma specificare solo i valori.

Questa notazione è semplice, ma non sicura, perché non vi è alcuna garanzia che man mano che il progetto si espande e la tabella viene modificata, le colonne saranno nello stesso ordine di prima. Un modo più sicuro (e allo stesso tempo più macchinoso) di scrivere un'istruzione INSERT richiede di specificare sia i valori che l'ordine delle colonne:

Ecco il primo valore nell'elenco I VALORI corrisponde al primo nome di colonna specificato e così via.

3. Estrazione dei dati dalle tabelle

L'istruzione SELECT è la nostra migliore amica quando vogliamo recuperare i dati da un database. Viene utilizzato molto spesso, quindi leggere attentamente questa sezione.

L'uso più semplice dell'istruzione SELECT è una query che restituisce tutte le colonne e le righe di una tabella (ad esempio, una tabella denominata caratteri):

SELEZIONA * DA "personaggi"

Il carattere asterisco (*) significa che vogliamo ottenere i dati da tutte le colonne. Poiché i database SQL in genere sono costituiti da più di una tabella, è richiesta la parola chiave FROM, seguita da uno spazio seguito dal nome della tabella.

A volte non vogliamo ottenere dati da non tutte le colonne di una tabella. Per fare questo, invece di un asterisco (*), dobbiamo scrivere i nomi delle colonne desiderate separate da virgole.

SELECT id, nome FROM mese

Inoltre, in molti casi vogliamo che i risultati vengano ordinati in un ordine specifico. In SQL, lo facciamo con ORDER BY . Può richiedere un modificatore opzionale - ASC (predefinito) per ordinare in ordine crescente o DESC per ordinare in ordine decrescente:

SELECT id, nome FROM mese ORDER BY nome DESC

Quando si utilizza ORDER BY, assicurarsi che sia l'ultimo nell'istruzione SELECT. In caso contrario, verrà emesso un messaggio di errore.

4. Filtraggio dei dati

Hai imparato a selezionare colonne specifiche da un database utilizzando una query SQL, ma cosa succede se vogliamo recuperare anche righe specifiche? La clausola WHERE viene in soccorso qui, permettendoci di filtrare i dati in base a una condizione.

In questa query, selezioniamo solo quei mesi dalla tabella mese superiori a 30 giorni utilizzando l'operatore maggiore di (>).

SELECT id, nome FROM mese WHERE giorni > 30

5. Filtraggio avanzato dei dati. Operatori AND e OR

In precedenza, filtravamo i dati utilizzando un singolo criterio. Per filtrare i dati più complessi, puoi utilizzare gli operatori AND e OR e gli operatori di confronto (=,<,>,<=,>=,<>).

Qui abbiamo una tabella contenente i quattro album più venduti di tutti i tempi. Scegliamo quelli classificati come rock e con meno di 50 milioni di copie vendute. Questo può essere fatto facilmente inserendo un operatore AND tra queste due condizioni.

SELECT * FROM album WHERE genere = "rock" AND vendite_in_milioni<= 50 ORDER BY released

6. Dentro/Tra/Mi piace

WHERE supporta anche diversi comandi speciali, che consentono di controllare rapidamente le query più comunemente utilizzate. Eccoli:

• IN - utilizzato per specificare un intervallo di condizioni, ciascuna delle quali può essere soddisfatta
• BETWEEN - Controlla se il valore è compreso nell'intervallo specificato
• LIKE: ricerca determinati modelli

Ad esempio, se vogliamo selezionare gli album con pop e anima musica, possiamo usare IN("value1","value2") .

SELEZIONA * DA album WHERE genere IN ("pop","soul");

Se volessimo avere tutti gli album pubblicati tra il 1975 e il 1985, scriveremmo:

SELEZIONA * DAGLI album DOVE usciti TRA IL 1975 E IL 1985;

7. Funzioni

SQL è pieno zeppo di funzioni che fanno ogni sorta di cose utili. Ecco alcuni dei più comunemente usati:

• COUNT() - restituisce il numero di righe
• SUM() - restituisce la somma totale di una colonna numerica
• AVG() - restituisce il valore medio da un insieme di valori
• MIN() / MAX() - Ottiene il valore minimo/massimo da una colonna

Per ottenere l'anno più recente nella nostra tabella, dobbiamo scrivere la seguente query SQL:

SELEZIONA MAX (rilasciato) DA album;

8. Sottoquery

Nel paragrafo precedente, abbiamo imparato come eseguire semplici calcoli con i dati. Se vogliamo utilizzare il risultato di questi calcoli, non possiamo fare a meno delle query nidificate. Diciamo che vogliamo produrre artista, album e Anno di pubblicazione per l'album più vecchio in classifica.

Sappiamo come ottenere queste colonne specifiche:

SELEZIONA artista, album, pubblicato DA album;

Sappiamo anche come ottenere il primo anno:

SELEZIONA MIN (rilasciato) DA album;

Tutto ciò che serve ora è combinare le due query con un WHERE:

SELECT artista,album,rilasciato FROM album WHERE rilasciato = (SELECT MIN(rilasciato) FROM album);

9. Unione di tabelle

In database più complessi, ci sono più tabelle correlate tra loro. Ad esempio, di seguito sono riportate due tabelle sui videogiochi ( videogiochi) e sviluppatori di videogiochi ( game_developers).

Tavolo videogiochi c'è una colonna per sviluppatori ( ID_sviluppatore), ma contiene un numero intero, non il nome dello sviluppatore. Questo numero è un identificatore ( id) dello sviluppatore corrispondente dalla tabella degli sviluppatori di giochi ( game_developers) collegando logicamente le due liste, permettendoci di utilizzare contemporaneamente le informazioni memorizzate in entrambe.

Se vogliamo creare una query che restituisca tutto ciò che dobbiamo sapere sui giochi, possiamo utilizzare un INNER JOIN per collegare le colonne di entrambe le tabelle.

SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;

Questo è il tipo di JOIN più semplice e più comune. Ci sono molte altre opzioni, ma si applicano a casi meno frequenti.

10. Alias

Se guardi l'esempio precedente, noterai che ci sono due colonne chiamate nome. Questo crea confusione, quindi impostiamo un alias su una delle colonne ripetute, ad esempio, nome dal tavolo game_developers verrà chiamato sviluppatore.

Possiamo anche abbreviare la query aliasando i nomi delle tabelle: videogiochi chiamiamo Giochi, game_developers - sviluppatori:

SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games INNER JOIN game_developers AS devs ON games.developer_id = devs.id;

11. Aggiornamento dei dati

Spesso abbiamo bisogno di modificare i dati in alcune righe. In SQL, questo viene fatto con l'istruzione UPDATE. L'istruzione UPDATE è composta da:

• La tabella contenente il valore di sostituzione;
• Nomi delle colonne e loro nuovi valori;
• Le righe selezionate con WHERE che vogliamo aggiornare. Se ciò non viene fatto, tutte le righe nella tabella cambieranno.

Di seguito la tabella serie TV con serie con la loro valutazione. Tuttavia, un piccolo errore si è insinuato nella tabella: sebbene la serie Game of Thrones ed è descritta come una commedia, in realtà non lo è. Risolviamo questo problema!

dati tabella serie_tv UPDATE serie_tv SET genere = "dramma" WHERE id = 2;

12. Cancellazione dei dati

L'eliminazione di una riga di tabella con SQL è un processo molto semplice. Tutto quello che devi fare è selezionare la tabella e la riga che desideri eliminare. Rimuoviamo l'ultima riga nella tabella dall'esempio precedente serie TV. Questo viene fatto usando l'istruzione >DELETE.

DELETE FROM tv_series WHERE id = 4

Fai attenzione quando scrivi l'istruzione DELETE e assicurati che la clausola WHERE sia presente, altrimenti tutte le righe della tabella verranno cancellate!

13. Eliminazione di una tabella

Se vogliamo rimuovere tutte le righe, ma lasciare la tabella stessa, utilizzare il comando TRUNCATE:

TRUNCATE TABLE nome_tabella;

Nel caso in cui vogliamo davvero eliminare sia i dati che la tabella stessa, il comando DROP tornerà utile:

DROP TABLE nome_tabella;

Stai molto attento con questi comandi. Non possono essere annullati!/p>

Questo conclude il nostro tutorial SQL! Non abbiamo coperto molto, ma quello che sai già dovrebbe essere sufficiente per darti alcune abilità pratiche nella tua carriera web.

Come posso conoscere il numero di modelli di PC prodotti da un determinato fornitore? Come determinare il valore medio del prezzo dei computer con le stesse caratteristiche tecniche? Queste e molte altre domande relative ad alcune informazioni statistiche possono essere risolte con l'aiuto di funzioni di riepilogo (aggregazione).. Lo standard fornisce le seguenti funzioni aggregate:

Tutte queste funzioni restituiscono un singolo valore. Allo stesso tempo, le funzioni CONTE, MIN e MASSIMO si applicano a qualsiasi tipo di dati, mentre SOMMA e AVV utilizzato solo per i campi numerici. Differenza tra funzione CONTARE(*) e CONTARE(<имя поля>) è che il secondo non tiene conto dei valori NULL durante il calcolo.

Esempio. Trova il prezzo minimo e massimo per i personal computer:

Esempio. Trova il numero disponibile di computer prodotti dal produttore A:

Esempio. Se siamo interessati al numero di modelli diversi prodotti dal produttore A, allora la query può essere formulata come segue (sfruttando il fatto che ogni modello è registrato una volta nella tabella Prodotto):

Esempio. Trova il numero di modelli diversi disponibili prodotti dal produttore A. La query è simile alla precedente, in cui era necessario determinare il numero totale di modelli prodotti dal produttore A. Qui, è necessario trovare il numero di modelli diversi nella tabella PC (cioè disponibile per la vendita).

Al fine di garantire che vengano utilizzati solo valori univoci quando si ottengono indicatori statistici, quando argomento della funzione aggregata può essere utilizzata Parametro DISTINTO. Altro parametro TUTTIè l'impostazione predefinita e prevede di contare tutti i valori restituiti nella colonna. Operatore,

Se abbiamo bisogno di ottenere il numero di modelli di PC prodotti da a testa produttore, sarà necessario utilizzare GRUPPO PER offerta, che segue sintatticamente dopo clausole WHERE.

GRUPPO PER offerta

GRUPPO PER offerta viene utilizzato per definire gruppi di linee di output a cui possono essere applicati funzioni aggregate (COUNT, MIN, MAX, AVG e SUM). Se questa clausola è mancante e vengono utilizzate funzioni di aggregazione, tutte le colonne con i nomi menzionati in SELEZIONARE, dovrebbe essere incluso in funzioni aggregate e queste funzioni verranno applicate all'intero set di righe che soddisfano il predicato della query. Altrimenti, tutte le colonne dell'elenco SELECT, non incluso in funzioni di aggregazione, deve essere specificato nella clausola GROUP BY. Di conseguenza, tutte le righe di output della query vengono suddivise in gruppi caratterizzati dalle stesse combinazioni di valori in queste colonne. Successivamente, le funzioni di aggregazione verranno applicate a ciascun gruppo. Si noti che per GROUP BY, tutti i valori NULL vengono trattati come uguali, ad es. quando si raggruppa per un campo contenente valori NULL, tutte queste righe rientreranno in un unico gruppo.
Se se è presente una clausola GROUP BY, nella clausola SELECT nessuna funzione aggregata, la query restituirà semplicemente una riga per ogni gruppo. Questa funzione, insieme alla parola chiave DISTINCT, può essere utilizzata per eliminare le righe duplicate in un set di risultati.
Considera un semplice esempio:

SELEZIONA modello, COUNT(modello) AS Qtà_modello, AVG(prezzo) AS Prezzo_medio DA PC Raggruppa per modello;

In questa query, per ogni modello di PC, vengono determinati il ​​numero e il costo medio. Tutte le righe con lo stesso valore del modello (numero del modello) formano un gruppo e l'output SELECT calcola il numero di valori e i valori del prezzo medio per ciascun gruppo. Il risultato della query sarà la seguente tabella:

modello	Qtà_modello	Prezzo_medio
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Se ci fosse una colonna con una data nel SELECT, allora sarebbe possibile calcolare questi indicatori per ogni data specifica. Per fare ciò, è necessario aggiungere la data come colonna di raggruppamento, quindi le funzioni di aggregazione verranno calcolate per ogni combinazione di valori (modello-data).

Ce ne sono diversi specifici regole per l'esecuzione di funzioni di aggregazione:

• Se a seguito della richiesta nessuna riga ricevuta(o più di una riga per questo gruppo), non ci sono dati iniziali per il calcolo delle funzioni di aggregazione. In questo caso, il risultato dell'esecuzione delle funzioni COUNT sarà zero e il risultato di tutte le altre funzioni sarà NULL.
• Discussione funzione aggregata non può contenere funzioni di aggregazione(funzione da funzione). Quelli. in una richiesta è impossibile, diciamo, ricevere un massimo di valori medi.
• Il risultato dell'esecuzione della funzione COUNT è numero intero(NUMERO INTERO). Altre funzioni di aggregazione ereditano i tipi di dati dei valori elaborati.
• Se durante l'esecuzione della funzione SOMMA è stato ottenuto un risultato che ha superato il valore massimo del tipo di dato utilizzato, sbaglio.

Quindi, se la richiesta non contiene GRUPPO PER offerte, poi funzioni aggregate incluso in Clausola SELECT, vengono eseguiti su tutte le stringhe di query risultanti. Se la richiesta contiene GRUPPO PER offerta, ogni insieme di righe che ha gli stessi valori della colonna o del gruppo di colonne specificato in offerta GRUPPO PER, costituisce un gruppo, e funzioni aggregate eseguite separatamente per ciascun gruppo.

AVERE offerta

Se Dove la clausola definisce un predicato per il filtraggio delle stringhe, quindi Clausola HAVING applicato dopo il raggruppamento per definire un predicato simile filtrando i gruppi per valori funzioni aggregate. Questa clausola è necessaria per convalidare i valori ottenuti con funzione aggregata non da righe separate dell'origine record definita in Clausola FROM, e da gruppi di tali linee. Pertanto, tale controllo non può essere contenuto in Dove la clausola.

Sintassi:

* dove campi1- campi per la selezione separati da virgole, puoi anche specificare tutti i campi con *; tavolo- il nome della tabella da cui estraiamo i dati; condizioni— condizioni di campionamento; campi2— campo o campi, separati da virgole, in base ai quali ordinare; contare— il numero di righe da scaricare.
* la query tra parentesi quadre è facoltativa per il recupero dei dati.

Casi d'uso semplici per select

1. Campionamento normale dei dati:

> SELEZIONA * DA utenti

2. Selezione dei dati con unione di due tabelle (JOIN):

SELECT u.name, r.* FROM utenti u JOIN users_rights r ON r.user_id=u.id

* in questo esempio, i dati vengono selezionati con l'unione delle tabelle utenti e utenti_diritti. Sono raggruppati per campi. ID utente(nella tabella users_rights) e id(utenti). Il campo del nome viene recuperato dalla prima tabella e tutti i campi dalla seconda.

3. Campionamento con intervallo di tempo e/o data

a) si conosce il punto di partenza e un certo intervallo di tempo:

* verranno selezionati i dati dell'ultima ora (campo Data).

b) sono note la data di inizio e la data di fine:

25.10.2017 e 25.11.2017 .

c) le date di inizio e fine + l'ora sono note:

* selezionare i dati tra 25/03/2018 0 ore 15 minuti e 25.04.2018 15 ore 33 minuti e 9 secondi.

d) estrarre i dati per un mese e un anno specifici:

* estrarre i dati dove nel campo Data ci sono valori per aprile 2018 dell'anno.

4. Campionamento del valore massimo, minimo e medio:

> SELEZIONA max(area), min(area), avg(area) FROM country

* max- valore massimo; min- minimo; media- media.

5. Utilizzo della lunghezza della stringa:

* questa query dovrebbe mostrare tutti gli utenti il ​​cui nome è composto da 5 caratteri.

Esempi di query più complesse o raramente utilizzate

1. Unione con raggruppamento dei dati selezionati in una riga (GROUP_CONCAT):

* da tavola utenti recuperare i dati per campo id, stanno tutti su una riga, i valori sono separati virgole.

2. Raggruppamento dei dati per due o più campi:

> SELEZIONA * DA utenti GRUPPO PER CONCAT(titolo, "::", nascita)

* totale, in questo esempio scaricheremo i dati dalla tabella utenti e li raggrupperemo per campi titolo e nascita. Prima di raggruppare, uniamo i campi in una riga con un separatore :: .

3. Combinazione dei risultati di due tabelle (UNION):

> (SELEZIONA id, fio, indirizzo, "Utenti" come tipo FROM utenti)
UNIONE
(SELEZIONA id, fio, indirizzo, "Clienti" come tipo FROM clienti)

* in questo esempio, i dati vengono recuperati dalle tabelle utenti e clienti.

4. Un campione di medie raggruppate per ogni ora:

SELECT avg(temperatura), DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H") come hour_datetime FROM archivio GROUP BY DATE_FORMAT(datetimeupdate, "%Y-%m-%d %H")

* qui estraiamo il valore medio del campo temperatura dal tavolo archivio e raggruppa per campo aggiornamento data/ora(con divisione del tempo per ogni ora).

Inserisci (INSERIRE)

Sintassi 1:

> INSERIRE IN

() I VALORI ( )

Sintassi 2:

> INSERIRE IN

I VALORI ( )

* dove tavolo- il nome della tabella in cui inseriamo i dati; campi- enumerazione dei campi separati da virgole;i valori— enumerazione di valori separati da virgole.
* la prima opzione ti consentirà di inserire solo sui campi elencati - il resto riceverà valori predefiniti. La seconda opzione richiederà l'inserimento per tutti i campi.

inserire esempi

1. Inserisci più righe con una query:

> INSERT INTO città ("nome", "paese") VALORI ("Mosca", "Russia"), ("Parigi", "Francia"), ("Funafuti", "Tuvalu");

* In questo esempio, aggiungeremo 3 record con una query SQL.

2. Inserisci da un'altra tabella (copia di righe, INSERT + SELECT):

* estrarre tutti i record dalla tabella città, i cui nomi iniziano con "M" ed entrano nella tabella città-nuove.

Aggiorna (AGGIORNA)

Sintassi:

* dove tavolo- nome della tabella; campo- il campo per il quale cambieremo il valore; valore— nuovo valore; condizioni- una condizione (è pericoloso eseguire un aggiornamento senza di essa - è possibile sostituire tutti i dati nell'intera tabella).

Aggiorna utilizzando la sostituzione (REPLACE):

AGGIORNARE

IMPOSTARE = SOSTITUIRE( , "<что меняем>", "<на что>");

UPDATE città SET nome = REPLACE(nome, "Maskva", "Mosca");

Se vogliamo andare sul sicuro, il risultato della sostituzione può essere verificato prima con SELEZIONA:

Cancellazione (CANCELLA)

Sintassi:

* dove tavolo- nome della tabella; condizioni- una condizione (come nel caso di UPDATE, è pericoloso usare DELETE senza una condizione - il DBMS non chiederà conferma, ma semplicemente cancellerà tutti i dati).

Crea una tabella

Sintassi:

> CREA TABELLA

( , )

> CREA TABELLA SE NON ESISTE `users_rights` (
`id` int(10) senza segno NOT NULL,
`user_id` int(10) non firmato NOT NULL,
`diritti` int(10) unsigned NOT NULL
) MOTORE=InnoDB CARATTERI PREDEFINITI=utf8;

* dove tavolo- nome della tabella (nell'esempio utenti_diritti); campo1, campo2— il nome dei campi (nell'esempio vengono creati 3 campi — id, user_id, diritti); opzioni1, opzioni2— parametri di campo (nell'esempio int(10) unsigned NOT NULL); opzioni della tabella— parametri generali della tabella (nell'esempio MOTORE=InnoDB CARATTERI PREDEFINITI=utf8).

Utilizzo delle richieste in PHP

Collegamento alla banca dati:

mysql_connect("localhost", "login", "password") o die("MySQL connect error");
mysql_select_db("nome_db");
mysql_query("IMPOSTA NOMI "utf8"");

* dove viene effettuata la connessione al database sul server locale ( localhost); credenziali di connessione - Accedere e parola d'ordine(rispettivamente login e password); utilizzato come base nome_db; codifica utilizzata UTF-8.

Puoi anche creare una connessione permanente:

mysql_pconnect("localhost", "login", "password") o die("MySQL connect error");

*è comunque possibile raggiungere il limite massimo di hosting consentito. Questo metodo dovrebbe essere utilizzato sui nostri server, dove noi stessi possiamo controllare la situazione.

Fine connessione:

* viene eseguito automaticamente in PHP, ad eccezione delle connessioni persistenti (mysql_pconnect).

Una query a MySQL (Mariadb) in PHP viene eseguita dalla funzione mysql_query() e il recupero dei dati da una query viene eseguito da mysql_fetch_array():

$result = mysql_query("SELECT * FROM users");
while ($mass = mysql_fetch_array($result)) (
echo $massa . "
";
}

* in questo esempio, è stata effettuata una query alla tabella utenti. Il risultato della query viene inserito in una variabile $risultato. Viene utilizzato il seguente ciclo mentre, ogni cui iterazione estrae una matrice di dati e la inserisce in una variabile $massa- in ogni iterazione, lavoriamo con una riga del database.

La funzione mysql_fetch_array() utilizzata restituisce un array associativo, con cui è conveniente lavorare, ma esiste un'altra alternativa: mysql_fetch_row(), che restituisce un normale array numerato.

Schermatura

Se è necessario includere un carattere speciale nella stringa di query, ad esempio %, è necessario utilizzare l'escape con il carattere barra rovesciata - \

Per esempio:

* se tale query viene eseguita senza escape, il segno % verrà interpretato come qualsiasi numero di caratteri dopo 100.

È tutto. Se hai bisogno di aiuto con una richiesta, inviami un'e-mail.

Espressioni tabellari chiamate sottoquery, che vengono utilizzate dove è prevista una tabella. Esistono due tipi di espressioni di tabella:

tabelle derivate;

espressioni di tabella generalizzate.

Queste due forme di espressioni di tabella sono discusse nelle seguenti sottosezioni.

Tabelle derivate

Tavola derivataè un'espressione di tabella inclusa nella clausola FROM di una query. Le tabelle derivate possono essere utilizzate quando l'utilizzo di alias di colonna non è possibile perché il traduttore SQL elabora un'altra istruzione prima che l'alias sia noto. L'esempio seguente mostra un tentativo di utilizzare un alias di colonna in una situazione in cui un'altra clausola viene elaborata prima che l'alias sia noto:

USE SampleDb; SELECT MONTH(EnterDate) as enter_month FROM Works_on GROUP BY enter_month;

Il tentativo di eseguire questa query restituirà il seguente messaggio di errore:

Msg 207, livello 16, stato 1, riga 5 Nome colonna non valido "enter_month". (Messaggio 207: Livello 16, Stato 1, Riga 5 Nome colonna non valido enter_month)

Il motivo dell'errore è che la clausola GROUP BY viene elaborata prima dell'elaborazione dell'elenco corrispondente dell'istruzione SELECT e l'alias della colonna enter_month non è noto quando il gruppo viene elaborato.

Questo problema può essere risolto utilizzando una vista contenente la query precedente (senza la clausola GROUP BY), poiché la clausola FROM viene eseguita prima della clausola GROUP BY:

USE SampleDb; SELECT enter_month FROM (SELECT MONTH(EnterDate) as enter_month FROM Works_on) AS m GROUP BY enter_month;

Il risultato di questa query sarà così:

In genere, un'espressione di tabella può essere inserita ovunque in un'istruzione SELECT in cui potrebbe apparire il nome della tabella. (Il risultato di un'espressione di tabella è sempre una tabella o, in casi speciali, un'espressione.) L'esempio seguente mostra l'utilizzo di un'espressione di tabella nell'elenco di selezione di un'istruzione SELECT:

Il risultato di questa query è:

Espressioni di tabella generiche

Common Table Expression (OTB) (CTE in breve)è un'espressione di tabella denominata supportata dal linguaggio Transact-SQL. Le espressioni di tabella comuni vengono utilizzate nei seguenti due tipi di query:

non ricorsivo;

ricorsivo.

Questi due tipi di richieste sono discussi nelle sezioni seguenti.

OTB e query non ricorsive

La forma non ricorsiva di OTB può essere utilizzata come alternativa alle tabelle e alle viste derivate. Di solito OTB è definito da CON clausole e una query aggiuntiva che fa riferimento al nome utilizzato nella clausola WITH. In Transact-SQL, il significato della parola chiave WITH è ambiguo. Per evitare ambiguità, l'istruzione che precede l'istruzione WITH deve terminare con un punto e virgola.

USA AdventureWorks2012; SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") AND Freight > (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005 ")/2,5;

La query in questo esempio seleziona gli ordini le cui imposte totali (TotalDue) sono superiori alla media di tutte le imposte e le cui spese di trasporto (Freight) sono superiori al 40% delle imposte medie. La proprietà principale di questa query è la sua voluminosità, poiché la query annidata deve essere scritta due volte. Un modo possibile per ridurre l'ambito del costrutto di query consiste nel creare una vista che contenga una sottoquery. Ma questa soluzione è un po' complicata perché richiede la creazione della vista e quindi l'eliminazione al termine della query. L'approccio migliore sarebbe quello di creare un OTB. L'esempio seguente mostra l'uso di OTB non ricorsivo, che accorcia la definizione della query sopra:

USA AdventureWorks2012; WITH price_calc(year_2005) AS (SELECT AVG(TotalDue) FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = "2005") SELECT SalesOrderID FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT anno_2005 FROM price_calc) AND Freight > (SELECT year_2005 FROM price_calc) /2,5;

La sintassi della clausola WITH nelle query non ricorsive è la seguente:

Il parametro cte_name è il nome OTB che definisce la tabella risultante e il parametro column_list è l'elenco di colonne nell'espressione della tabella. (Nell'esempio precedente, OTB è denominato price_calc e ha una colonna, year_2005.) Il parametro inner_query rappresenta un'istruzione SELECT che specifica il set di risultati dell'espressione di tabella corrispondente. L'espressione di tabella definita può quindi essere utilizzata in outer_query. (La query esterna nell'esempio precedente utilizza OTB price_calc e la relativa colonna year_2005 per semplificare la query double nested.)

OTB e query ricorsive

Questa sezione presenta materiale di maggiore complessità. Pertanto, quando lo leggi per la prima volta, si consiglia di saltarlo e tornarci più tardi. Gli OTB possono essere ricorsivi perché gli OTB possono contenere riferimenti a se stessi. La sintassi OTB di base per una query ricorsiva è la seguente:

I parametri cte_name e column_list hanno lo stesso significato di OTB per le query non ricorsive. Il corpo della clausola WITH è costituito da due query unite dall'istruzione UNIONE TUTTO. La prima query viene chiamata solo una volta e inizia ad accumulare il risultato della ricorsione. Il primo operando dell'operatore UNION ALL non fa riferimento a un OTB. Questa query è chiamata query di riferimento o origine.

La seconda query contiene un collegamento a OTB e ne rappresenta la parte ricorsiva. Per questo motivo, viene chiamato membro ricorsivo. Nella prima chiamata alla parte ricorsiva, il riferimento OTB rappresenta il risultato della query di riferimento. Il membro ricorsivo utilizza il risultato della prima chiamata alla query. Successivamente, il sistema richiama nuovamente la parte ricorsiva. Una chiamata a un membro ricorsivo termina quando una chiamata precedente a esso restituisce un set di risultati vuoto.

L'operatore UNION ALL unisce le righe accumulate fino a quel momento, nonché le righe aggiuntive aggiunte dalla chiamata corrente al membro ricorsivo. (La presenza dell'operatore UNION ALL significa che le righe duplicate non verranno rimosse dal risultato.)

Infine, il parametro outer_query definisce la query esterna utilizzata da OTB per ottenere tutte le chiamate all'unione di entrambi i membri.

Per dimostrare la forma ricorsiva di OTB, utilizziamo una tabella Airplane definita e popolata con il codice mostrato nell'esempio seguente:

USE SampleDb; CREATE TABLE Aeroplano (Assemblaggio contenente VARCHAR(10), Assemblaggio contenuto VARCHAR(10), QuantitàContenuta INT, Costo unitario DECIMAL(6,2)); INSERT INTO Airplane VALUES ("Aereo", "Fusoliera", 1, 10); INSERT INTO Airplane VALUES ("Aeroplano", "Ali", 1, 11); INSERT INTO Airplane VALUES ("Aeroplano", "Coda", 1, 12); INSERT INTO Airplane VALUES ("Fusoliera", "Salone", 1, 13); INSERT INTO Airplane VALUES ("Fusoliera", "Cockpit", 1, 14); INSERT INTO Airplane VALUES ("Fusoliera", "Muso", 1, 15); INSERT IN Aereo VALUES("Salon", NULL, 1,13); INSERT IN Aereo VALUES("cabina", NULL, 1, 14); INSERT IN Aereo VALUES("Muso", NULL, 1, 15); INSERT IN Aereo VALUES("Wings", NULL,2, 11); INSERT IN Aereo VALUES("Coda", NULL, 1, 12);

La tabella Aeroplano ha quattro colonne. La colonna ContainingAssembly definisce l'assieme e la colonna ContainedAssembly definisce le parti (una per una) che compongono l'assieme corrispondente. La figura seguente mostra un'illustrazione grafica di un possibile tipo di aeromobile e delle sue parti costitutive:

La tabella Aereo è composta dalle seguenti 11 righe:

L'esempio seguente utilizza la clausola WITH per definire una query che calcola il costo totale di ogni build:

USE SampleDb; WITH list_of_parts(assembly1, quantity, cost) AS (SELECT ContainingAssembly, QuantityContained, UnitCost FROM Airplane WHERE ContainedAssembly IS NULL UNION ALL SELECT a.ContainingAssembly, a.QuantityContained, CAST(l.quantity * l.cost AS DECIMAL(6,2) ) FROM list_of_parts l, Aeroplano a WHERE l.assembly1 = a.ContainedAssembly) SELECT assembly1 "Part", quantità "Quantity", costo "Price" FROM list_of_parts;

La clausola WITH definisce un elenco OTB denominato list_of_parts, costituito da tre colonne: assembly1, quantity e cost. La prima istruzione SELECT nell'esempio viene chiamata una sola volta per archiviare i risultati del primo passaggio del processo di ricorsione. L'istruzione SELECT nell'ultima riga dell'esempio visualizza il seguente risultato.

SQL viene utilizzato per recuperare i dati dal database. SQL è un linguaggio di programmazione molto simile all'inglese, ma destinato ai programmi di gestione dei database. SQL viene utilizzato in ogni query in Access.

Comprendere il funzionamento di SQL consente di creare query più accurate e semplifica la correzione delle query che restituiscono risultati errati.

Questo articolo fa parte della serie di articoli SQL per Access. Descrive le basi dell'utilizzo di SQL per recuperare i dati e fornisce esempi di sintassi SQL.

In questo articolo

Cos'è SQL?

SQL è un linguaggio di programmazione progettato per funzionare con insiemi di fatti e le relazioni tra di essi. I programmi di database relazionali come Microsoft Office Access utilizzano SQL per manipolare i dati. A differenza di molti linguaggi di programmazione, SQL è facile da leggere e comprendere anche per i principianti. Come molti linguaggi di programmazione, SQL è uno standard internazionale riconosciuto da comitati di standard come ISO e ANSI.

I set di dati sono descritti in SQL per aiutare a rispondere alle domande. Quando si utilizza SQL, è necessario utilizzare la sintassi corretta. La sintassi è un insieme di regole che consentono di combinare correttamente gli elementi di una lingua. La sintassi SQL si basa sulla sintassi inglese e condivide molti elementi con la sintassi del linguaggio Visual Basic for Applications (VBA).

Ad esempio, una semplice istruzione SQL che recupera un elenco dei cognomi dei contatti di nome Mary potrebbe avere il seguente aspetto:

SELEZIONA Cognome
DA Contatti
WHERE Nome_= "Maria";

Nota: Il linguaggio SQL viene utilizzato non solo per eseguire operazioni sui dati, ma anche per creare e modificare la struttura degli oggetti del database, come le tabelle. La parte di SQL utilizzata per creare e modificare gli oggetti del database è chiamata DDL. DDL non è trattato in questo articolo. Per altre informazioni, vedere creare e modificare tabelle o indici utilizzando una query di definizione dei dati.

SELECT istruzioni

L'istruzione SELECT viene utilizzata per descrivere un set di dati in SQL. Contiene una descrizione completa dell'insieme di dati da recuperare dal database, inclusi i seguenti:

tabelle che contengono dati;

collegamenti tra dati provenienti da diverse fonti;

campi o calcoli in base ai quali vengono selezionati i dati;

condizioni di selezione che devono essere soddisfatte dai dati inclusi nel risultato della query;

necessità e metodo di smistamento.

istruzioni SQL

Un'istruzione SQL è composta da diverse parti chiamate clausole. Ogni clausola in un'istruzione SQL ha uno scopo. Alcune offerte sono obbligatorie. La tabella seguente elenca le istruzioni SQL più comunemente utilizzate.

Clausola SQL	Descrizione	Obbligatorio
	Definisce i campi che contengono i dati desiderati.
	Definisce le tabelle che contengono i campi specificati nella clausola SELECT.
	Definisce i criteri di selezione del campo che devono soddisfare tutti i record inclusi nei risultati.
	Specifica l'ordinamento dei risultati.
	In un'istruzione SQL che contiene funzioni di aggregazione, specifica i campi per i quali non viene calcolato alcun valore di riepilogo nella clausola SELECT.	Solo se ci sono tali campi
	In un'istruzione SQL che contiene funzioni di aggregazione, definisce le condizioni applicate ai campi per i quali viene calcolato il valore di riepilogo nella clausola SELECT.

Termini SQL

Ogni frase SQL è composta da termini che possono essere paragonati a parti del discorso. La tabella seguente elenca i tipi di termini SQL.

Termine SQL	Parte comparabile del discorso	Definizione	Esempio
identificatore	sostantivo	Un nome utilizzato per identificare un oggetto di database, ad esempio un nome di campo.	Clienti.[Numero di telefono]
operatore	verbo o avverbio	Una parola chiave che rappresenta o modifica un'azione.
costante	sostantivo	Un valore che non cambia, ad esempio un numero o NULL.
espressione	aggettivo	Una combinazione di identificatori, operatori, costanti e funzioni che restituisce un singolo valore.	>= Merce.[Prezzo]

Clausole SQL di base: SELECT, FROM e WHERE

Il formato generale delle istruzioni SQL è:

SELEZIONA campo_1
DA tabella_1
WHERE criterio_1
;

Appunti:

L'accesso non rispetta le interruzioni di riga nell'istruzione SQL. Nonostante ciò, si consiglia di iniziare ogni frase su una nuova riga in modo che l'istruzione SQL sia di facile lettura sia per la persona che l'ha scritta sia per tutti gli altri.

Ogni istruzione SELECT termina con un punto e virgola (;). Il punto e virgola può trovarsi alla fine dell'ultima frase o su una riga separata alla fine dell'istruzione SQL.

Esempio in Access

L'esempio seguente mostra l'aspetto di un'istruzione SQL in Access per una semplice query di selezione.

1. Clausola SELECT

2. Clausola FROM

3. Clausola WHERE

Suddividiamo l'esempio per frasi per capire come funziona la sintassi SQL.

Clausola SELECT

SELEZIONA , Azienda

Questa è una clausola SELECT. Contiene un'istruzione (SELECT) seguita da due identificatori ("[Indirizzo e-mail]" e "Azienda").

Se l'identificativo contiene spazi o caratteri speciali (ad esempio, "Indirizzo e-mail"), deve essere racchiuso tra parentesi quadre.

Nella clausola SELECT non è necessario specificare le tabelle che contengono i campi e non è possibile specificare le condizioni di selezione che devono essere soddisfatte dai dati da includere nei risultati.

In un'istruzione SELECT, la clausola SELECT precede sempre la clausola FROM.

Clausola FROM

DA Contatti

Questa è la clausola FROM. Contiene un'istruzione (FROM) seguita da un identificatore (Contatti).

La clausola FROM non specifica i campi da selezionare.

Dove la clausola

DOVE Città="Seattle"

Questa è una clausola WHERE. Contiene un operatore (WHERE) seguito da un'espressione (City="Rostov").

Puoi fare molte cose con le clausole SELECT, FROM e WHERE. Per ulteriori informazioni su come utilizzare queste offerte, vedere i seguenti articoli:

Ordina i risultati: ORDER BY

Come Microsoft Excel, Access consente di ordinare i risultati di una query in una tabella. Utilizzando la clausola ORDER BY, puoi anche specificare come verranno ordinati i risultati quando viene eseguita la query. Se viene utilizzata una clausola ORDER BY, deve trovarsi alla fine dell'istruzione SQL.

La clausola ORDER BY contiene un elenco di campi in base ai quali eseguire l'ordinamento, nello stesso ordine in cui verrà applicato l'ordinamento.

Supponiamo, ad esempio, di voler ordinare i risultati prima in base al campo "Azienda" in ordine decrescente e poi, se sono presenti record con lo stesso valore nel campo "Azienda", ordinarli in base all'"Indirizzo di posta elettronica " campo in ordine crescente. La clausola ORDER BY sarebbe simile a questa:

ORDINE PER AZIENDA DISC,

Nota: Per impostazione predefinita, Access ordina i valori in ordine crescente (dalla A alla Z, dal più piccolo al più grande). Per ordinare invece i valori in ordine decrescente è necessario specificare la parola chiave DESC.

Per ulteriori informazioni sulla clausola ORDER BY, vedere Clausola ORDER BY.

Lavorare con i dati di riepilogo: clausole GROUP BY e HAVING

A volte è necessario lavorare con dati di riepilogo, come le vendite mensili totali o gli articoli più costosi in magazzino. Per fare ciò, la clausola SELECT applica una funzione di aggregazione al campo. Ad esempio, se il risultato di una query è restituire il numero di indirizzi e-mail per ciascuna azienda, la clausola SELECT potrebbe avere il seguente aspetto:

La possibilità di utilizzare una particolare funzione di aggregazione dipende dal tipo di dati nel campo e dall'espressione desiderata. Per ulteriori informazioni sulle funzioni di aggregazione disponibili, vedere l'articolo sulle funzioni di aggregazione SQL.

Specifica di campi non utilizzati in una funzione di aggregazione: clausola GROUP BY

Quando si utilizzano funzioni di aggregazione, in genere è necessario creare una clausola GROUP BY. La clausola GROUP BY specifica tutti i campi a cui non viene applicata la funzione di aggregazione. Se le funzioni di aggregazione si applicano a tutti i campi in una query, non è necessario creare una clausola GROUP BY.

La clausola GROUP BY deve seguire immediatamente la clausola WHERE o FROM se non è presente alcuna clausola WHERE. Nella clausola GROUP BY, i campi sono specificati nello stesso ordine della clausola SELECT.

Continuiamo l'esempio precedente. Se la clausola SELECT applica solo la funzione di aggregazione al campo [Indirizzo email], la clausola GROUP BY avrà il seguente aspetto:

RAGGRUPPARE PER Società

Per ulteriori informazioni sulla clausola GROUP BY, vedere Clausola GROUP BY.

Vincolo di valori aggregati con condizioni di raggruppamento: la clausola HAVING

Se si desidera specificare condizioni per limitare i risultati, ma il campo a cui si desidera applicarle viene utilizzato in una funzione aggregata, non è possibile utilizzare una clausola WHERE. Al suo posto dovrebbe essere usata la clausola HAVING. La clausola HAVING funziona allo stesso modo della clausola WHERE ma viene utilizzata per i dati aggregati.

Supponiamo, ad esempio, che la funzione AVG (che calcola il valore medio) sia applicata al primo campo nella clausola SELECT:

SELECT COUNT(), Società

Se desideri limitare i risultati della query in base al valore della funzione COUNT, non puoi applicare una condizione di filtro a questo campo nella clausola WHERE. Invece, la condizione dovrebbe essere inserita in una clausola HAVING. Ad esempio, se desideri che la query restituisca righe solo se l'azienda ha più indirizzi email, puoi utilizzare la seguente clausola HAVING:

HAVING COUNT()>1

Nota: Una query può includere sia una clausola WHERE che una clausola HAVING, con le condizioni per i campi che non sono utilizzati nelle funzioni di aggregazione specificate nella clausola WHERE e le condizioni per i campi che sono utilizzati nelle funzioni di aggregazione sono specificate nella clausola HAVING .

Per ulteriori informazioni sulla clausola HAVING, vedere la clausola HAVING.

Combinazione dei risultati della query: l'operatore UNION

L'operatore UNION viene utilizzato per visualizzare contemporaneamente tutti i dati restituiti da più query di selezione simili come un set combinato.

L'operatore UNION consente di combinare due istruzioni SELECT in una sola. Le istruzioni SELECT combinate devono avere lo stesso numero e lo stesso ordine di campi di output con tipi di dati uguali o compatibili. Quando viene eseguita una query, i dati di ogni set di campi corrispondenti vengono combinati in un singolo campo di output, quindi l'output della query ha tanti campi quanti sono le singole istruzioni SELECT.

Nota: Nelle query di unione, i tipi di dati numerici e di testo sono compatibili.

Utilizzando l'operatore UNION, è possibile specificare se le eventuali righe duplicate devono essere incluse nei risultati della query. Per fare questo, usa la parola chiave ALL.

Una query per unire due istruzioni SELECT ha la seguente sintassi di base:

SELEZIONA campo_1
DA tabella_1
UNIONE
SELEZIONA campo_a
DA tabella_a
;

Supponiamo, ad esempio, che ci siano due tabelle denominate "Prodotti" e "Servizi". Entrambe le tabelle contengono campi con il nome del prodotto o servizio, informazioni sul prezzo e sulla garanzia, nonché un campo che indica l'esclusività del prodotto o servizio offerto. Sebbene le tabelle Prodotti e Servizi forniscano diversi tipi di garanzie, le informazioni di base sono le stesse (se viene fornita una garanzia di qualità per singoli prodotti o servizi). Per unire quattro campi da due tabelle, puoi utilizzare la seguente query di unione:

SELEZIONA nome, prezzo, garanzia_disponibile, offerta_esclusiva
DA Prodotti
UNIONE TUTTO
SELEZIONA nome, prezzo, garanzia_disponibile, offerta_esclusiva
DA Servizi
;

Per ulteriori informazioni sulla combinazione di istruzioni SELECT utilizzando l'operatore UNION, vedere l'articolo