Impariamo a riassumere. No, questi non sono i risultati dello studio SQL, ma i risultati dei valori delle colonne delle tabelle del database. Le funzioni aggregate SQL operano sui valori di una colonna per produrre un unico valore risultante. Le funzioni di aggregazione SQL più comunemente utilizzate sono SUM, MIN, MAX, AVG e COUNT. È necessario distinguere tra due casi di utilizzo di funzioni aggregate. Innanzitutto, le funzioni aggregate vengono utilizzate da sole e restituiscono un unico valore risultante. In secondo luogo, le funzioni aggregate vengono utilizzate con la clausola SQL GROUP BY, ovvero il raggruppamento per campi (colonne) per ottenere i valori risultanti in ciascun gruppo. Consideriamo innanzitutto i casi di utilizzo di funzioni aggregate senza raggruppamento.
Funzione SOMMA SQL
La funzione SQL SUM restituisce la somma dei valori in una colonna della tabella del database. Può essere applicato solo alle colonne i cui valori sono numeri. Le query SQL per ottenere la somma risultante iniziano in questo modo:
SELEZIONA SOMMA(NOME_COLUMN)...
Questa espressione è seguita da FROM (TABLE_NAME), quindi è possibile specificare una condizione utilizzando la clausola WHERE. Inoltre, il nome della colonna può essere preceduto da DISTINCT, il che significa che verranno conteggiati solo valori univoci. Per impostazione predefinita, vengono presi in considerazione tutti i valori (per questo è possibile specificare specificamente non DISTINCT, ma ALL, ma la parola ALL non è richiesta).
Esempio 1. Esiste un database aziendale con dati sulle sue divisioni e dipendenti. La tabella Personale contiene anche una colonna con i dati sugli stipendi dei dipendenti. La selezione dalla tabella si presenta così (per ingrandire l'immagine, cliccarci sopra con il tasto sinistro del mouse):
Per ottenere la somma di tutti gli stipendi, utilizzare la seguente query:
SELEZIONA SOMMA(Stipendio) DA Personale
Questa query restituirà il valore 287664.63.
E adesso . Negli esercizi stiamo già iniziando a complicare i compiti, avvicinandoli a quelli incontrati nella pratica.
Funzione SQLMIN
La funzione SQL MIN opera anche su colonne i cui valori sono numeri e restituisce il minimo di tutti i valori nella colonna. Questa funzione ha una sintassi simile a quella della funzione SOMMA.
Esempio 3. Il database e la tabella sono gli stessi dell'esempio 1.
È necessario conoscere il minimo salari dipendenti del dipartimento con il numero 42. Per fare ciò, scrivere la seguente richiesta:
La query restituirà il valore 10505.90.
E di nuovo esercizio di auto-soluzione. In questo e in altri esercizi, avrai bisogno non solo della tabella Staff, ma anche della tabella Org, contenente i dati sulle divisioni dell'azienda:
Esempio 4. La tabella Org viene aggiunta alla tabella Staff, contenente i dati sui dipartimenti dell'azienda. Stampa il numero minimo di anni lavorati da un dipendente in un dipartimento situato a Boston.
Funzione SQL MAX
La funzione SQL MAX funziona in modo simile e ha una sintassi simile, che viene utilizzata quando è necessario determinare valore massimo tra tutti i valori di colonna.
Esempio 5.
Devi scoprire lo stipendio massimo dei dipendenti del dipartimento numero 42. Per fare ciò, scrivi la seguente richiesta:
La query restituirà il valore 18352.80
È tempo esercizi per la soluzione indipendente.
Esempio 6. Lavoriamo ancora con due tabelle: Staff e Org. Visualizza il nome del dipartimento e il valore massimo della commissione ricevuta da un dipendente del dipartimento appartenente al gruppo di dipartimenti (Divisione) Orientale. Utilizzo JOIN (unione di tabelle) .
Funzione AVG SQL
Quanto detto riguardo la sintassi per le funzioni precedentemente descritte vale anche per la funzione SQL AVG. Questa funzione restituisce la media di tutti i valori in una colonna.
Esempio 7. Il database e la tabella sono gli stessi degli esempi precedenti.
Supponiamo che tu voglia scoprire l'anzianità media di servizio dei dipendenti del dipartimento numero 42. Per fare ciò, scrivi la seguente query:
Il risultato sarà 6.33
Esempio 8. Lavoriamo con un tavolo: Staff. Ritirare stipendio medio dipendenti con dai 4 ai 6 anni di esperienza.
Funzione CONTEGGIO SQL
La funzione SQL COUNT restituisce il numero di record in una tabella di database. Se si specifica SELECT COUNT(COLUMN_NAME) ... nella query, il risultato sarà il numero di record senza tenere conto di quei record in cui il valore della colonna è NULL (non definito). Se si utilizza un asterisco come argomento e si avvia una query SELECT COUNT(*) ..., il risultato sarà il numero di tutti i record (righe) della tabella.
Esempio 9. Il database e la tabella sono gli stessi degli esempi precedenti.
Vuoi conoscere il numero di tutti i dipendenti che ricevono commissioni. Il numero di dipendenti i cui valori della colonna Comm non sono NULL verrà restituito dalla seguente query:
SELEZIONA CONTEGGIO(Comm) DA Staff
Il risultato sarà 11.
Esempio 10. Il database e la tabella sono gli stessi degli esempi precedenti.
Se vuoi conoscere il numero totale di record nella tabella, utilizza una query con un asterisco come argomento della funzione COUNT:
SELEZIONA COUNT(*) DA Personale
Il risultato sarà 17.
Nel prossimo esercizio di soluzione indipendente dovrai utilizzare una sottoquery.
Esempio 11. Lavoriamo con un tavolo: Staff. Visualizzare il numero di dipendenti nel reparto di pianificazione (Piani).
Funzioni aggregate con SQL GROUP BY
Ora esaminiamo l'utilizzo delle funzioni di aggregazione insieme all'istruzione SQL GROUP BY. L'istruzione SQL GROUP BY viene utilizzata per raggruppare i valori dei risultati per colonne in una tabella di database.
Esempio 12. C'è un database del portale pubblicitario. Ha una tabella Annunci contenente i dati sugli annunci inviati per la settimana. La colonna Categoria contiene informazioni su grandi categorie annunci pubblicitari (ad esempio, Immobiliare) e la colonna Parti riguarda le parti più piccole incluse nelle categorie (ad esempio, le parti Appartamenti e Cottage fanno parte della categoria Immobiliare). La colonna Unità contiene dati sul numero di annunci inviati e la colonna Denaro contiene dati sull'importo di denaro ricevuto per l'invio di annunci.
| Categoria | Parte | Unità | Soldi |
| Trasporto | Automobili | 110 | 17600 |
| Immobiliare | Appartamenti | 89 | 18690 |
| Immobiliare | Dacie | 57 | 11970 |
| Trasporto | Motociclette | 131 | 20960 |
| Materiali di costruzione | Tavole | 68 | 7140 |
| Ingegnere elettrico | televisori | 127 | 8255 |
| Ingegnere elettrico | Frigoriferi | 137 | 8905 |
| Materiali di costruzione | Regime | 112 | 11760 |
| Tempo libero | Libri | 96 | 6240 |
| Immobiliare | A casa | 47 | 9870 |
| Tempo libero | Musica | 117 | 7605 |
| Tempo libero | Giochi | 41 | 2665 |
Utilizzando l'istruzione SQL GROUP BY, trova l'importo di denaro guadagnato pubblicando annunci in ciascuna categoria. Scriviamo la seguente richiesta.
La lezione tratterà l'argomento SQL sulla ridenominazione di una colonna (campi) utilizzando la parola di servizio AS; Viene trattato anche l'argomento delle funzioni aggregate in SQL. Verrà smantellato esempi specifici richieste
I nomi delle colonne nelle query possono essere rinominati. Ciò rende i risultati più leggibili.
In SQL, la ridenominazione dei campi implica l'utilizzo AS parola chiave, che viene utilizzato per rinominare i nomi dei campi nei set di risultati
Sintassi:
SELEZIONARE<имя поля>COME<псевдоним>DA...
Diamo un'occhiata a un esempio di ridenominazione in SQL:
Esempio del database “Istituto”: Visualizza i nomi degli insegnanti e i loro stipendi, per quegli insegnanti il cui stipendio è inferiore a 15.000, rinomina il campo zarplata in "salario basso"
✍ Soluzione:
Spesso è necessario rinominare le colonne in SQL quando si calcolano valori associati a più campi tavoli. Diamo un'occhiata ad un esempio:
Esempio del database “Istituto”: Dalla tabella docenti, visualizza il campo nome e calcola l'importo dello stipendio e del bonus, nominando il campo "bonus_stipendio"
✍ Soluzione:
| 1 2 | SELECT nome, (zarplata+ premia) AS zarplata_premia FROM insegnanti; |
SELECT nome, (zarplata+premia) AS zarplata_premia FROM insegnanti;
Risultato:
Funzioni aggregate in SQL
Per ottenere valori totali e valutare le espressioni, vengono utilizzate le funzioni aggregate in sql:
Tutte le funzioni aggregate restituiscono un singolo valore.
Le funzioni COUNT, MIN e MAX si applicano a qualsiasi tipo di dati.
Le funzioni SUM e AVG vengono utilizzate solo per i campi numerici.
C'è una differenza tra le funzioni COUNT(*) e COUNT(): la seconda non tiene conto dei valori NULL durante il calcolo.
Importante: Quando si lavora con funzioni aggregate in SQL, viene utilizzata una parola funzione COME
Esempio del database “Istituto”: Ottieni il valore dello stipendio più alto tra gli insegnanti, visualizza il risultato come "salario_max"
✍ Soluzione:
| SELEZIONA MAX (zarplata) AS max_salary DA insegnanti; |
SELECT MAX(zarplata) AS max_salary FROM insegnanti;
Risultati:
Consideriamo di più esempio complesso utilizzando funzioni aggregate in sql.
✍ Soluzione:
Clausola GROUP BY in SQL
L'operatore group by in SQL viene solitamente utilizzato insieme alle funzioni aggregate.
Le funzioni aggregate vengono eseguite su tutte le righe di query risultanti. Se la query contiene una clausola GROUP BY, ogni set di righe specificato nella clausola GROUP BY costituisce un gruppo e vengono eseguite le funzioni aggregate per ciascun gruppo separatamente.
Vediamo un esempio con la tabella delle lezioni: 
Esempio:
Importante: Pertanto, utilizzando GROUP BY, tutte le righe di output della query vengono divise in gruppi caratterizzati dalle stesse combinazioni di valori in tali colonne (ovvero, le funzioni aggregate vengono eseguite su ciascun gruppo separatamente).
Vale la pena considerare che quando si raggruppa in base a un campo contenente valori NULL, tutti questi record rientreranno in un unico gruppo.
Per vari tipi le stampanti li identificano costo medio e quantità (ovvero separatamente per laser, getto d'inchiostro e matrice). Utilizza funzioni aggregate. Il risultato dovrebbe assomigliare a questo:
Avere un'istruzione SQL
La clausola HAVING in SQL è necessaria per verificare i valori, che si ottengono utilizzando la funzione aggregata dopo il raggruppamento(dopo aver utilizzato GRUPPO PER). Un tale controllo non può essere contenuto in una clausola WHERE.
Esempio: Negozio di computer DB. Calcola il prezzo medio dei computer con la stessa velocità del processore. Esegui i calcoli solo per quei gruppi il cui prezzo medio è inferiore a 30.000.
Le sottosezioni seguenti descrivono altre proposte Istruzione SELEZIONATA, che può essere utilizzato nelle query, nonché funzioni aggregate e insiemi di operatori. Lascia che te lo ricordi in questo momento Abbiamo esaminato l'utilizzo della clausola WHERE e in questo articolo esamineremo le clausole GROUP BY, ORDER BY e HAVING e forniremo alcuni esempi dell'utilizzo di queste clausole in combinazione con le funzioni di aggregazione supportate in Transact-SQL.
Clausola GRUPPO BY
Offerta RAGGRUPPA PER raggruppa un insieme selezionato di righe per produrre un insieme di righe di riepilogo in base ai valori di una o più colonne o espressioni. Un semplice caso di utilizzo della clausola GROUP BY è mostrato nell'esempio seguente:
USA CampioneDb; SELEZIONA Lavoro DA Lavori_Su GRUPPO PER Lavoro;
In questo esempio vengono selezionate e raggruppate le posizioni dei dipendenti.
Nell'esempio precedente, viene creata la clausola GROUP BY gruppo separato per tutti valori possibili(Compreso Valore NULL) colonna Lavoro.
L'utilizzo delle colonne in una clausola GROUP BY deve soddisfare determinate condizioni. Nello specifico, ciascuna colonna nell'elenco di selezione della query deve essere visualizzata anche nella clausola GROUP BY. Questo requisito non si applica alle costanti e alle colonne che fanno parte di una funzione aggregata. (Le funzioni di aggregazione sono trattate nella sottosezione successiva.) Ciò ha senso perché È garantito che solo le colonne nella clausola GROUP BY abbiano un valore per gruppo.
Puoi raggruppare una tabella in base a qualsiasi combinazione delle sue colonne. L'esempio seguente mostra il raggruppamento delle righe della tabella Works_on in due colonne:
USA CampioneDb; SELEZIONA NumeroProgetto, Lavoro DA Lavori_Su GRUPPO PER NumeroProgetto, Lavoro;
Il risultato di questa query:
In base ai risultati della query, puoi vedere che esistono nove gruppi con diverse combinazioni di numero di progetto e posizione. La sequenza dei nomi di colonna nella clausola GROUP BY non deve essere la stessa dell'elenco di colonne nella SELECT.
Funzioni aggregate
Le funzioni aggregate vengono utilizzate per ottenere valori totali. Tutte le funzioni aggregate possono essere suddivise nelle seguenti categorie:
funzioni aggregate ordinarie;
funzioni aggregate statistiche;
funzioni aggregate definite dall'utente;
funzioni aggregate analitiche.
Qui esamineremo i primi tre tipi di funzioni aggregate.
Funzioni aggregate comuni
Transact-SQL supporta le sei funzioni aggregate seguenti: MINIMO, MASSIMO, SOMMA, AVG, CONTARE, COUNT_BIG.
Tutte le funzioni aggregate eseguono calcoli su un singolo argomento, che può essere una colonna o un'espressione. (L'unica eccezione è la seconda forma delle due funzioni: COUNT e COUNT_BIG, vale a dire COUNT(*) e COUNT_BIG(*), rispettivamente.) Il risultato di qualsiasi calcolo di funzione aggregata è un valore costante, visualizzato in una colonna di risultati separata.
Le funzioni aggregate sono specificate nell'elenco delle colonne dell'istruzione SELECT, che può contenere anche una clausola GROUP BY. Se l'istruzione SELECT non ha una clausola GROUP BY e l'elenco delle colonne selezionate la contiene almeno, una funzione aggregata, non deve contenere colonne semplici (ad eccezione delle colonne che fungono da argomenti per la funzione aggregata). Pertanto il codice nell'esempio seguente non è corretto:
USA CampioneDb; SELECT Cognome, MIN(Id) FROM Dipendente;
In questo caso, la colonna LastName della tabella Employee non dovrebbe essere nell'elenco di selezione delle colonne perché non è un argomento della funzione di aggregazione. D'altro canto, l'elenco di selezione delle colonne può contenere nomi di colonne che non sono argomenti della funzione di aggregazione se tali colonne sono argomenti della clausola GROUP BY.
Un argomento per una funzione aggregata può essere preceduto da una delle due possibilità: parole chiave:
TUTTOSpecifica che i calcoli vengono eseguiti su tutti i valori nella colonna. Questo è il valore predefinito.
DISTINTOSpecifica che per i calcoli vengono utilizzati solo valori di colonna univoci.
Funzioni aggregate MIN e MAX
Le funzioni aggregate MIN e MAX calcolano il più piccolo e valore più alto colonna di conseguenza. Se una query contiene una clausola WHERE, le funzioni MIN e MAX restituiscono i valori più piccoli e più grandi delle righe che soddisfano le condizioni specificate. L'esempio seguente mostra l'uso della funzione aggregata MIN:
USA CampioneDb; -- Restituisce 2581 SELECT MIN(Id) AS "Valore ID minimo" FROM Dipendente;
Il risultato restituito nell'esempio precedente non è molto informativo. Ad esempio, il cognome del dipendente a cui appartiene questo numero è sconosciuto. Ma non è possibile ottenere questo cognome nel solito modo perché, come accennato in precedenza, non è consentito specificare esplicitamente la colonna LastName. Per ottenere anche il cognome di questo dipendente insieme alla matricola più bassa di un dipendente, viene utilizzata una sottoquery. L'esempio seguente mostra l'uso di tale sottoquery, dove la sottoquery contiene l'istruzione SELECT dell'esempio precedente:
Risultato della richiesta:
L'uso della funzione aggregata MAX è mostrato nell'esempio seguente:
Le funzioni MIN e MAX possono accettare anche stringhe e date come argomenti. Nel caso di un argomento stringa, i valori vengono confrontati utilizzando l'ordinamento effettivo. Per tutti gli argomenti relativi ai dati temporali di tipo "data", il valore della colonna più piccolo sarà il massimo data anticipata, e il più grande è l'ultimo.
È possibile utilizzare la parola chiave DISTINCT con le funzioni MIN e MAX. Prima di utilizzare le funzioni di aggregazione MIN e MAX, tutti i valori NULL vengono eliminati dalle colonne degli argomenti.
Funzione aggregata SOMMA
Aggregato Funzione SOMMA calcola importo totale valori delle colonne. L'argomento di questa funzione aggregata deve essere sempre un tipo di dati numerico. L'uso della funzione aggregata SUM è mostrato nell'esempio seguente:
USA CampioneDb; SELECT SUM (Budget) "Budget totale" DA Progetto;
In questo esempio viene calcolato l'importo totale dei budget per tutti i progetti. Risultato della richiesta:
In questo esempio, la funzione di aggregazione raggruppa tutti i valori del budget del progetto e ne determina l'importo totale. Per questo motivo la query contiene una funzione di raggruppamento implicita (come tutte le query simili). La funzione di raggruppamento implicita dell'esempio precedente può essere specificata esplicitamente, come mostrato nell'esempio seguente:
USA CampioneDb; SELECT SUM (Budget) "Budget totale" FROM Progetto GROUP BY();
L'utilizzo del parametro DISTINCT elimina tutti i valori duplicati in una colonna prima di applicare la funzione SUM. Allo stesso modo, tutti i valori NULL vengono rimossi prima di applicare questa funzione di aggregazione.
Funzione aggregata AVG
Aggregato Funzione AVG restituisce la media aritmetica di tutti i valori in una colonna. L'argomento di questa funzione aggregata deve essere sempre un tipo di dati numerico. Prima di utilizzare la funzione AVG, tutti i valori NULL vengono rimossi dal suo argomento.
L'utilizzo della funzione di aggregazione di AVG è mostrato nell'esempio seguente:
USA CampioneDb; -- Return 133833 SELECT AVG (Budget) "Budget medio per il progetto" FROM Progetto;
Qui viene calcolata la media valore aritmetico bilancio per tutti i budget.
Funzioni aggregate COUNT e COUNT_BIG
Aggregato funzione CONTEGGIO ha due forme diverse:
COUNT(nome_col) COUNT(*)
La prima forma della funzione conta il numero di valori nella colonna col_name. Se la query utilizza la parola chiave DISTINCT, tutti i valori duplicati nella colonna vengono rimossi prima di utilizzare la funzione COUNT. Questa forma della funzione COUNT non tiene conto dei valori NULL quando si conta il numero di valori in una colonna.
L'uso della prima forma della funzione aggregata COUNT è mostrato nell'esempio seguente:
USA CampioneDb; SELECT NumeroProgetto, COUNT(DISTINCT Lavoro) "Lavora nel progetto" FROM Lavori_su GRUPPO PER NumeroProgetto;
Qui viene conteggiato il numero di posizioni diverse per ciascun progetto. Il risultato di questa query:
Come puoi vedere dalla query di esempio, i valori NULL non sono stati presi in considerazione dalla funzione COUNT. (La somma di tutti i valori nella colonna della posizione risulta essere 7, non 11, come dovrebbe essere.)
La seconda forma della funzione COUNT, cioè La funzione COUNT(*) conta il numero di righe in una tabella. E se le istruzioni SELEZIONA interrogazione con la funzione COUNT(*) contiene una clausola WHERE con una condizione, la funzione restituisce il numero di righe che soddisfano condizione specificata. A differenza della prima versione della funzione COUNT, la seconda forma non ignora i valori NULL perché questa funzione opera su righe e non su colonne. L'esempio seguente dimostra l'uso della funzione COUNT(*):
USA CampioneDb; SELEZIONA Lavoro COME "Tipo di lavoro", COUNT(*) "Bisogno di lavoratori" DA Lavori_su GRUPPO PER Lavoro;
Qui viene calcolato il numero di posizioni in tutti i progetti. Risultato della richiesta:
COUNT_BIG funzione simile alla funzione COUNT. L'unica differenza tra loro è il tipo di risultato che restituiscono: la funzione COUNT_BIG restituisce sempre valori di tipo BIGINT, mentre la funzione COUNT restituisce valori di dati di tipo INTEGER.
Funzioni aggregate statistiche
Le seguenti funzioni costituiscono il gruppo delle funzioni aggregate statistiche:
VARCalcola la varianza statistica di tutti i valori rappresentati in una colonna o espressione.
VARPCalcola la varianza statistica della popolazione di tutti i valori rappresentati in una colonna o espressione.
DEV.STCalcola la deviazione standard (che viene calcolata come Radice quadrata dalla varianza corrispondente) di tutti i valori di una colonna o espressione.
DEV.ST.VPCalcola la deviazione standard della popolazione di tutti i valori in una colonna o espressione.
Funzioni aggregate definite dall'utente
Il Motore di database supporta inoltre l'implementazione di funzioni definite dall'utente. Questa funzionalità consente agli utenti di aumentare le funzioni aggregate del sistema con funzioni che possono implementare e installare autonomamente. Queste funzioni rappresentano una classe speciale di funzioni definite dall'utente e verranno discusse in dettaglio in seguito.
AVERE offerta
In una frase AVENDO definisce una condizione che si applica a un gruppo di righe. Pertanto, questa clausola ha per i gruppi di righe lo stesso significato della clausola WHERE per il contenuto della tabella corrispondente. Sintassi AVERE offerte Prossimo:
AVERE condizione
Qui il parametro condizione rappresenta la condizione e contiene funzioni o costanti aggregate.
L'uso della clausola HAVING insieme alla funzione aggregata COUNT(*) è illustrato nell'esempio seguente:
USA CampioneDb; -- Restituisce "p3" SELECT NumeroProgetto FROM Lavori_su GRUPPO BY NumeroProgetto HAVING COUNT(*)
In questo esempio, il sistema raggruppa tutte le righe in base ai valori della colonna ProjectNumber utilizzando la clausola GROUP BY. Successivamente, viene contato il numero di righe in ciascun gruppo e vengono selezionati i gruppi contenenti meno di quattro righe (tre o meno).
La clausola HAVING può essere utilizzata anche senza funzioni aggregate, come mostrato nell'esempio seguente:
USA CampioneDb; -- Restituisce "Consulente" SELECT Lavoro FROM Works_on GRUPPO PER Lavoro HAVING Lavoro LIKE "K%";
Questo esempio raggruppa le righe della tabella Works_on in base alla qualifica lavorativa ed elimina i lavori che non iniziano con la lettera "K".
La clausola HAVING può essere utilizzata anche senza la clausola GROUP BY, sebbene questa non sia una pratica comune. In questo caso, tutte le righe della tabella vengono restituite in un unico gruppo.
Clausola ORDER BY
Offerta ORDINATO DA determina l'ordinamento delle righe nel set di risultati restituito dalla query. Questa frase ha la seguente sintassi:
L'ordinamento è specificato nel parametro col_name. Il parametro col_number è un indicatore di ordinamento alternativo che identifica le colonne in base all'ordine in cui appaiono nell'elenco di selezione dell'istruzione SELECT (1 è la prima colonna, 2 è la seconda colonna e così via). Parametro ASC specifica l'ordinamento in ordine crescente e Parametro DESC- nella direzione verso il basso. L'impostazione predefinita è ASC.
I nomi delle colonne nella clausola ORDER BY non devono necessariamente essere nell'elenco delle colonne selezionate. Ma questo non si applica a query come SELECT DISTINCT, perché in tali query, i nomi delle colonne specificati nella clausola ORDER BY devono essere specificati anche nell'elenco delle colonne selezionate. Inoltre, questa clausola non può contenere nomi di colonne di tabelle non specificate nella clausola FROM.
Come puoi vedere dalla sintassi della clausola ORDER BY, il set di risultati può essere ordinato in base a più colonne. Questo ordinamento è mostrato nell'esempio seguente:
In questo esempio, i numeri di reparto e i nomi dei dipendenti vengono selezionati per i dipendenti il cui numero di personale è inferiore a 20.000 e ordinati per cognome e nome. Il risultato di questa query:
Le colonne nella clausola ORDER BY possono essere specificate non in base al nome, ma in base all'ordine nell'elenco di selezione. Di conseguenza, la frase dell’esempio sopra può essere riscritta come segue:
Come modo alternativo se il criterio di ordinamento contiene una funzione aggregata viene utilizzata la specifica delle colonne in base alla loro posizione anziché ai nomi. (Un altro modo consiste nell'utilizzare i nomi delle colonne, che poi verranno visualizzati nella clausola ORDER BY.) Tuttavia, nella clausola ORDER BY, si consiglia di specificare le colonne in base ai nomi anziché ai numeri, per semplificare l'aggiornamento delle colonne. interroga se le colonne devono essere aggiunte o rimosse dall'elenco di selezione. La specifica delle colonne nella clausola ORDER BY tramite i relativi numeri è mostrata nell'esempio seguente:
USA CampioneDb; SELEZIONA NumeroProgetto, COUNT(*) "Numero di dipendenti" DA Works_on GRUPPO PER NumeroProgetto ORDINA PER 2 DESC;
Qui, per ciascun progetto, vengono selezionati il numero del progetto e il numero di dipendenti che vi partecipano, disponendo il risultato in ordine decrescente in base al numero di dipendenti.
Transact-SQL inserisce i valori NULL all'inizio dell'elenco quando si ordina in ordine crescente e alla fine dell'elenco quando si ordina in ordine decrescente.
Utilizzo della clausola ORDER BY per impaginare i risultati
La visualizzazione dei risultati della query sulla pagina corrente può essere implementata in applicazione utente o istruire il server del database a farlo. Nel primo caso tutte le righe del database vengono inviate all'applicazione, il cui compito è selezionare le righe desiderate e visualizzarle. Nel secondo caso, lato server, solo le righe necessarie per pagina corrente. Come ci si potrebbe aspettare, la creazione di pagine lato server di solito fornisce prestazioni migliori, Perché Al client vengono inviate solo le righe necessarie per la visualizzazione.
Per supportare la creazione di pagine lato server in server SQL Il 2012 introduce due nuove clausole dell'istruzione SELECT: OFFSET e FETCH. L'applicazione di queste due frasi è dimostrata nell'esempio seguente. Qui, dal database AdventureWorks2012 (che puoi trovare nell'origine), vengono recuperati l'ID aziendale, la qualifica professionale e la data di nascita di tutte le dipendenti donne, ordinando il risultato in base alla qualifica professionale in ordine crescente. Il set di righe risultante viene suddiviso in pagine da 10 righe e viene visualizzata la terza pagina:
In una frase COMPENSARE specifica il numero di righe di risultato da saltare nel risultato visualizzato. Questo numero viene calcolato dopo che le righe sono state ordinate utilizzando la clausola ORDER BY. In una frase PRENDETE IL PROSSIMO indica il numero di soddisfazioni DOVE condizione e le righe ordinate da restituire. Il parametro di questa clausola può essere una costante, un'espressione o il risultato di un'altra query. La clausola FETCH NEXT è simile a PRENDETE PRIMA.
L'obiettivo principale quando si creano pagine lato server è essere in grado di implementare moduli di pagina comuni utilizzando le variabili. Questa attività può essere eseguita utilizzando Pacchetto SQL Server.
Istruzione SELECT e proprietà IDENTITY
Proprietà IDENTITÀ consente di definire valori per una colonna specifica della tabella sotto forma di un contatore che aumenta automaticamente. Le colonne con tipo di dati numerici come TINYINT, SMALLINT, INT e BIGINT possono avere questa proprietà. Per tale colonna di tabella, Motore di database crea automaticamente valori sequenziali a partire dal valore iniziale specificato. Pertanto, la proprietà IDENTITY può essere utilizzata per creare oggetti univoci valori numerici per la colonna selezionata.
Una tabella può contenere solo una colonna con la proprietà IDENTITY. Il proprietario della tabella ha la possibilità di specificare un valore iniziale e un incremento, come mostrato nell'esempio seguente:
USA CampioneDb; CREATE TABLE Prodotto (Id INT IDENTITY(10000, 1) NOT NULL, Nome NVARCHAR(30) NOT NULL, Prezzo MONEY) INSERT INTO Prodotto(Nome, Prezzo) VALUES ("Prodotto1", 10), ("Prodotto2", 15) , ("Prodotto3", 8), ("Prodotto4", 15), ("Prodotto5", 40); -- Restituisce 10004 SELECT IDENTITYCOL FROM Prodotto WHERE Nome = "Prodotto5"; -- Simile all'istruzione precedente SELECT $identity FROM Product WHERE Name = "Product5";
In questo esempio viene innanzitutto creata una tabella Product che contiene una colonna Id con una proprietà IDENTITY. I valori nella colonna Id vengono creati automaticamente dal sistema, partendo da 10.000 e aumentando con incrementi unitari per ogni valore successivo: 10.000, 10.001, 10.002, ecc.
Diverse funzioni e variabili di sistema sono associate alla proprietà IDENTITY. Ad esempio, il codice di esempio utilizza Variabile di sistema $identità. Come puoi vedere dall'output di questo codice, questa variabile fa automaticamente riferimento alla proprietà IDENTITY. In alternativa è possibile utilizzare anche la funzione di sistema IDENTITÀCOL.
Il valore iniziale e l'incremento di una colonna con la proprietà IDENTITY possono essere trovati utilizzando le funzioni IDENT_SEED E IDENT_INCR rispettivamente. Queste funzioni vengono utilizzate come segue:
USA CampioneDb; SELEZIONA IDENT_SEED("Prodotto"), IDENT_INCR("Prodotto")
Come già accennato i valori di IDENTITY vengono impostati automaticamente dal sistema. Ma l'utente può specificare esplicitamente i propri valori per determinate righe assegnando il parametro IDENTITY_INSERT Valore ON prima di inserire un valore esplicito:
IMPOSTA IDENTITY INSERT nome tabella su ON
Poiché è possibile utilizzare il parametro IDENTITY_INSERT per impostare una colonna con la proprietà IDENTITY su qualsiasi valore, incluso un valore duplicato, la proprietà IDENTITY in genere non impone l'unicità dei valori della colonna. Pertanto, per imporre l'unicità dei valori delle colonne, è necessario utilizzare i vincoli UNIQUE o PRIMARY KEY.
Quando si inseriscono valori in una tabella dopo aver impostato IDENTITY_INSERT su On, il sistema crea il valore successivo della colonna IDENTITY incrementando il valore corrente più grande di quella colonna.
Istruzione CREATE SEQUENCE
Esistono diversi svantaggi significativi nell'utilizzo della proprietà IDENTITY, i più significativi dei quali sono i seguenti:
l'applicazione della proprietà è limitata alla tabella specificata;
il nuovo valore della colonna non può essere ottenuto altrimenti che applicandolo;
la proprietà IDENTITY può essere specificata solo durante la creazione di una colonna.
Per questi motivi SQL Server 2012 introduce sequenze che hanno la stessa semantica della proprietà IDENTITY, ma senza gli svantaggi precedentemente elencati. In questo contesto, una sequenza è una funzionalità del database che consente di specificare i valori dei contatori per diversi oggetti del database, come colonne e variabili.
Le sequenze vengono create utilizzando le istruzioni CREA SEQUENZA. L'istruzione CREATE SEQUENCE è definita in Norma SQL ed è supportato da altri sistemi di database relazionali come IBM DB2 e Oracle.
L'esempio seguente mostra come creare una sequenza in SQL Server:
USA CampioneDb; CREA SEQUENZA dbo.Sequence1 AS INT INIZIA CON 1 INCREMENTO DI 5 MINVALUE 1 MAXVALUE 256 CYCLE;
Nell'esempio sopra, i valori di Sequenza1 vengono creati automaticamente dal sistema, iniziando dal valore 1 e incrementando 5 per ogni valore successivo. Quindi, dentro Offerta INIZIOè indicato il valore iniziale e in Offerta INCREMENTO- fare un passo. (Il passo può essere positivo o negativo.)
Nelle prossime due frasi facoltative VALOREMIN E VALORE MASSIMO Viene specificato il valore minimo e massimo dell'oggetto sequenza. (Si noti che il valore MINVALUE deve essere inferiore o uguale al valore iniziale e il valore MAXVALUE non può essere maggiore del limite superiore del tipo di dati specificato per la sequenza.) Nella clausola CICLO indica che la sequenza viene ripetuta dall'inizio quando viene superato il valore massimo (o minimo per una sequenza con passo negativo). Per impostazione predefinita, questa clausola è impostata su NO CYCLE, il che significa che il superamento del valore massimo o minimo della sequenza genererà un'eccezione.
La caratteristica principale delle sequenze è la loro indipendenza dalle tabelle, cioè possono essere utilizzati con qualsiasi oggetto di database, come colonne o variabili di tabella. (Questa proprietà ha un effetto positivo sulla memorizzazione e quindi sulle prestazioni. Non è necessario memorizzare la sequenza specifica; viene memorizzato solo il suo ultimo valore.)
Nuovi valori di sequenza vengono creati utilizzando NEXT VALUE FOR espressioni, la cui applicazione è mostrata nell'esempio seguente:
USA CampioneDb; -- Return 1 SELEZIONA IL VALORE SUCCESSIVO PER dbo.sequence1; -- Restituisce 6 (passaggio successivo) SELEZIONA VALORE SUCCESSIVO PER dbo.sequence1;
È possibile utilizzare l'espressione NEXT VALUE FOR per assegnare il risultato di una sequenza a una variabile o a una cella di colonna. L'esempio seguente mostra come utilizzare questa espressione per assegnare risultati a una colonna:
USA CampioneDb; CREATE TABLE Prodotto (Id INT NOT NULL, Nome NVARCHAR(30) NOT NULL, Prezzo MONEY) INSERT IN TO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Product1", 10); INSERISCI nei VALORI del prodotto (VALORE SUCCESSIVO PER dbo.sequence1, "Prodotto2", 15); --...
Nell'esempio sopra, creiamo prima una tabella Product composta da quattro colonne. Successivamente, due istruzioni INSERT inseriscono due righe in questa tabella. Le prime due celle della prima colonna avranno i valori 11 e 16.
L'esempio seguente mostra l'uso di una visualizzazione di directory sys.sequenze per visualizzare il valore corrente di una sequenza senza utilizzarla:
In genere, l'istruzione NEXT VALUE FOR viene utilizzata in un'istruzione INSERT per fare in modo che il sistema inserisca i valori generati. Questa espressione può essere utilizzata anche come parte di una query su più righe utilizzando la clausola OVER.
Per modificare una proprietà di una sequenza esistente, utilizzare Istruzione ALTER SEQUENCE. Uno degli usi più importanti di questa istruzione è con l'opzione RESTART WITH, che reimposta la sequenza specificata. L'esempio seguente mostra l'uso di ALTER SEQUENCE per reimpostare quasi tutte le proprietà di Sequence1:
USA CampioneDb; ALTERA SEQUENZA dbo.sequence1 RIAVVIA CON 100 INCREMENTO DI 50 VALORE MIN 50 VALORE MAX 200 NESSUN CICLO;
Eliminazione di una sequenza utilizzando un'istruzione SEQUENZA DI CADUTA.
Impostare gli operatori
Oltre agli operatori discussi in precedenza, Transact-SQL supporta altri tre operatori di set: UNION, INTERSECT ed EXCEPT.
operatore UNIONE
operatore UNIONE combina i risultati di due o più query in un unico set di risultati che include tutte le righe appartenenti a tutte le query nell'unione. Di conseguenza, il risultato dell'unione di due tabelle è una nuova tabella contenente tutte le righe incluse in una delle tabelle originali o in entrambe.
La forma generale dell'operatore UNION è simile alla seguente:
select_1 UNIONE select_2 ( select_3])...
I parametri select_1, select_2, ... sono istruzioni SELECT che creano un join. Se viene utilizzata l'opzione TUTTO, vengono visualizzate tutte le righe, inclusi i duplicati. In un operatore UNION, il parametro ALL ha lo stesso significato di un elenco di selezione SELECT, ma con una differenza: per un elenco di selezione SELECT, questo parametro viene utilizzato per impostazione predefinita, ma per un operatore UNION deve essere specificato esplicitamente.
Nella sua forma originale, il database SampleDb non è adatto a dimostrare l'uso dell'operatore UNION. Pertanto, questa sezione crea una nuova tabella EmployeeEnh, che è identica a tabella esistente Dipendente, ma ha una colonna Città aggiuntiva. In questa colonna è indicato il luogo di residenza dei dipendenti.
La creazione della tabella EmployeeEnh ci offre una buona opportunità per dimostrare l'uso della clausola IN nell'istruzione SELECT. L'istruzione SELECT INTO esegue due operazioni. Innanzitutto viene creata una nuova tabella con le colonne elencate nell'elenco SELECT. Quindi vengono inserite le righe della tabella di origine nuova tabella. Il nome della nuova tabella è specificato nella clausola INTO e il nome della tabella di origine è specificato nella clausola FROM.
L'esempio seguente mostra come creare la tabella EmployeeEnh dalla tabella Employee:
USA CampioneDb; SELEZIONA * IN EmployeeEnh DA Dipendente; ALTER TABLE EmployeeEnh ADD Città NCHAR(40) NULL;
In questo esempio, l'istruzione SELECT INTO crea la tabella EmployeeEnh, vi inserisce tutte le righe della tabella di origine Employee, quindi l'istruzione ALTER TABLE aggiunge la colonna City alla nuova tabella. Ma la colonna Città aggiunta non contiene alcun valore. I valori in questa colonna possono essere inseriti tramite l'ambiente Studio Direzionale oppure utilizzando il seguente codice:
USA CampioneDb; UPDATE EmployeeEnh SET Città = "Kazan" WHERE Id = 2581; UPDATE EmployeeEnh SET Città = "Mosca" WHERE Id = 9031; UPDATE EmployeeEnh SET Città = "Ekaterinburg" WHERE Id = 10102; UPDATE EmployeeEnh SET Città = "San Pietroburgo" WHERE Id = 18316; UPDATE EmployeeEnh SET Città = "Krasnodar" WHERE Id = 25348; UPDATE EmployeeEnh SET Città = "Kazan" WHERE Id = 28559; UPDATE EmployeeEnh SET Città = "Perm" WHERE Id = 29346;
Ora siamo pronti a dimostrare l'uso dell'istruzione UNION. L'esempio seguente mostra una query per creare un join tra le tabelle EmployeeEnh e Department utilizzando questa istruzione:
USA CampioneDb; SELEZIONA Città COME "Città" DA EmployeeEnh UNION SELECT Posizione DA Dipartimento;
Il risultato di questa query:
Solo le tabelle compatibili possono essere unite utilizzando l'istruzione UNION. Per tabelle compatibili intendiamo che entrambi gli elenchi di colonne selezionate devono contenere lo stesso numero di colonne e le colonne corrispondenti devono avere tipi di dati compatibili. (In termini di compatibilità, i tipi di dati INT e SMALLINT non sono compatibili.)
Il risultato di un join può essere ordinato solo utilizzando la clausola ORDER BY in ultima istruzione SELEZIONARE come mostrato nell'esempio seguente. Offerte del GRUPPO BY e HAVING possono essere utilizzati con istruzioni separate SELECT, ma non nell'unione stessa.
La query in questo esempio seleziona i dipendenti che lavorano nel dipartimento d1 o che hanno iniziato a lavorare al progetto prima del 1 gennaio 2008.
L'operatore UNION supporta il parametro ALL. L'utilizzo di questa opzione non rimuove i duplicati dal set di risultati. È possibile utilizzare l'operatore OR anziché l'operatore UNION se tutte le istruzioni SELECT unite da uno o più operatori UNION fanno riferimento alla stessa tabella. In questo caso, l'insieme di istruzioni SELECT viene sostituito da una singola istruzione SELECT con un insieme di istruzioni OR.
Operatori INTERSECT e EXCEPT
Altri due operatori per lavorare con i set, INTERSEZIONE E TRANNE, determinare rispettivamente l'intersezione e la differenza. Sotto l'intersezione in questo contesto c'è un insieme di righe che appartengono ad entrambe le tabelle. E la differenza tra due tabelle è definita come tutti i valori che appartengono alla prima tabella e non sono presenti nella seconda. L'esempio seguente mostra l'uso dell'operatore INTERSECT:
Transact-SQL non supporta l'utilizzo del parametro ALL con l'operatore INTERSECT o EXCEPT. L'uso dell'operatore EXCEPT è mostrato nell'esempio seguente:
Tieni presente che questi tre operatori di set hanno priorità di esecuzione diverse: Operatore INTERSECT Esso ha massima priorità, seguito dall'operatore EXCEPT e l'operatore UNION ha la precedenza più bassa. Disattenzione alla priorità di esecuzione quando si utilizzano più diversi operatori per lavorare con i set può portare a risultati imprevisti.
CASO Espressioni
Nel campo della programmazione di applicazioni di database, a volte è necessario modificare la rappresentazione dei dati. Ad esempio, le persone possono essere suddivise codificandole in base alla classe sociale, utilizzando i valori 1, 2 e 3, che indicano rispettivamente uomini, donne e bambini. Questa tecnica di programmazione può ridurre il tempo necessario per implementare un programma. Espressione CASO Il linguaggio Transact-SQL semplifica l'implementazione di questo tipo di codifica.
A differenza della maggior parte dei linguaggi di programmazione, CASE non è un'affermazione, ma un'espressione. Pertanto, un'espressione CASE può essere utilizzata quasi ovunque in cui Transact-SQL consente le espressioni. L'espressione CASE ha due forme:
semplice espressione CASE;
espressione di ricerca CASE.
La sintassi per una semplice espressione CASE è:
Istruzioni con espressione semplice CASE cerca innanzitutto l'elenco di tutte le espressioni in clausola QUANDO la prima espressione che corrisponde a espressione_1, quindi esegue la corrispondente ALLORA clausola. Se non esiste alcuna espressione corrispondente nell'elenco WHEN, il clausola ALTRO.
La sintassi dell'espressione di ricerca CASE è la seguente:
IN in questo caso cerca la prima condizione corrispondente e quindi esegue la clausola THEN corrispondente. Se nessuna delle condizioni soddisfa i requisiti, viene eseguita la clausola ELSE. L'uso di un'espressione di ricerca CASE è mostrato nell'esempio seguente:
USA CampioneDb; SELEZIONA NomeProgetto, CASO QUANDO Budget > 0 AND Budget 100000 AND Budget 150000 AND Budget
Il risultato di questa query:
In questo esempio vengono pesati i budget di tutti i progetti e quindi vengono visualizzati i pesi calcolati insieme ai nomi dei progetti corrispondenti.
L'esempio seguente mostra un altro modo di utilizzare un'espressione CASE, in cui la clausola WHEN contiene sottoquery che fanno parte dell'espressione:
USA CampioneDb; SELECT NomeProgetto, CASE WHEN p1.Budget (SELECT AVG(p2.Budget) FROM Progetto p2) THEN "sopra la media" END "Categoria Budget" FROM Progetto p1;
Il risultato di questa query è il seguente:
Utilizzo delle funzioni aggregate
SQL definisce molte funzioni integrate di varie categorie, tra le quali un posto speciale è occupato dalle funzioni aggregate, che operano sui valori di colonne di molte righe e restituiscono un unico valore. Gli argomenti delle funzioni aggregate possono essere sia colonne di tabella che risultati di espressioni su di esse. Le stesse funzioni aggregate possono essere incluse in altre espressioni aritmetiche. La tabella seguente mostra le funzioni di aggregazione unarie standard più comunemente utilizzate.
Il formato generale di una funzione aggregata unaria è il seguente:
nome_funzione([ALL | DISTINCT] espressione)
dove DISTINCT indica che la funzione deve solo considerare significati diversi argomento e ALL - tutti i valori, inclusi i duplicati (questa opzione è utilizzata per impostazione predefinita). Ad esempio, la funzione AVG con la parola chiave DISTINCT per le righe di colonna con valori 1, 1, 1 e 3 restituirà 2 e, se è presente la parola chiave ALL, restituirà 1,5.
Le funzioni aggregate vengono utilizzate nelle clausole SELECT e HAVING. Qui esamineremo il loro utilizzo nella clausola SELECT. In questo caso, l'espressione nell'argomento della funzione si applica a tutte le righe nella tabella di input della clausola SELECT. Inoltre, una clausola SELECT non può utilizzare sia funzioni aggregate che colonne di tabella (o espressioni con esse) in assenza di una clausola GROUP BY, che esamineremo in sezione successiva.
La funzione COUNT ha due formati. Nel primo caso viene restituito il numero di righe nella tabella di input; nel secondo caso viene restituito il numero di valori degli argomenti nella tabella di input:
- CONTARE(*)
- COUNT(espressione)
Il modo più semplice per utilizzare questa funzione è contare il numero di righe in una tabella (tutte o quelle che soddisfano una condizione specifica). Per questo, viene utilizzata la prima opzione di sintassi.
Query: numero di tipi di prodotto per i quali sono disponibili informazioni nel database.
SELEZIONA COUNT(*) COME "Numero di tipi di prodotto"
DAL Prodotto
La seconda versione della sintassi della funzione COUNT può accettare come argomento il nome di una singola colonna. In questo caso, viene conteggiato il numero di tutti i valori in questa colonna della tabella di input o solo di quelli non ripetuti (utilizzando la parola chiave DISTINCT).
Query: numero di nomi distinti contenuti nella tabella Clienti.
SELEZIONA CONTEGGIO (FNAME DISTINTO)
DAL Cliente
L'uso delle restanti funzioni aggregate unarie è simile a COUNT, tranne per il fatto che per le funzioni MIN e MAX l'uso delle parole chiave DISTINCT e ALL non ha senso. Con le funzioni COUNT, MAX e MIN, oltre ai campi numerici, si possono utilizzare anche campi di caratteri. Se l'argomento di una funzione aggregata non contiene valori, la funzione COUNT restituisce 0 e tutte le altre restituiscono NULL.
SELEZIONA MAX (DataOrdine)
DA
DOVEOrdDate"1.09.2010"
Incarico per lavoro autonomo: Formulare interrogazioni in SQL per recuperare i seguenti dati:
- Costo totale di tutti gli ordini;
- Il numero di città diverse contenute nella tabella Clienti.
Può produrre generalizzato elaborazione di gruppo valori del campo. Questo viene fatto utilizzando funzioni aggregate. Le funzioni aggregate producono un singolo valore per un intero gruppo di tabelle. SQL fornisce le seguenti funzioni aggregate:
- CONTARE– conta il numero di righe della tabella con valori non NULL del campo specificato come argomento.
- SOMMA– calcola la somma aritmetica di tutti i valori selezionati per un dato campo.
- AVG– calcola la media di tutti i valori selezionati di questo campo.
- MASSIMO– visualizza il valore più grande tra tutti i valori selezionati per questo campo.
- MINIMO– visualizza valore più piccolo da tutti i valori selezionati di questo campo.
Utilizzo delle funzioni aggregate
Le funzioni aggregate vengono utilizzate in modo simile ai nomi di campo nella clausola SELECT di una query, con un'eccezione: accettano i nomi di campo come argomento. Soltanto campi numerici può essere utilizzato con SOMMA E AVG. CON CONTARE, MASSIMO, E MINIMOÈ possibile utilizzare sia campi numerici che di caratteri. Se utilizzato con campi carattere MASSIMO E MINIMO li tradurrà nell'equivalente ASCII. Significa che MINIMO sceglierà il primo, e MASSIMO ultimo valore in ordine alfabetico.
Per trovare l'importo totale delle vendite nella tabella delle vendite, dobbiamo scrivere la seguente query:
SELECT SUM(SSum) FROM Vendite
Di conseguenza otteniamo:
Questa query contava il numero di valori non nulli nel campo SNum della tabella Sells. Se riscriviamo la query come segue:
SELECT COUNT(SDate) FROM Vendite
Di conseguenza otteniamo:
CONTEGGIO DI Data 4 Si ottengono risultati diversi della query quando si calcola ciò che sembra essere la stessa cosa perché uno dei valori del campo SDate è vuoto ( NULLO). Fai attenzione quando utilizzi tali query.
