Microsoft SQL Server Tutorial
Introduzione a Microsoft SQL Server

Osservazioni

Versioni

INSERT / SELECT / UPDATE / DELETE: le basi del linguaggio di manipolazione dei dati

D ana M anipolazione L anguage (DML in breve) include operazioni come INSERT , UPDATE e DELETE :

-- Create a table HelloWorld

CREATE TABLE HelloWorld (
    Id INT IDENTITY,
    Description VARCHAR(1000)
)


-- DML Operation INSERT, inserting a row into the table
INSERT INTO HelloWorld (Description) VALUES ('Hello World')


-- DML Operation SELECT, displaying the table 
SELECT * FROM HelloWorld  


-- Select a specific column from table
SELECT Description FROM HelloWorld


-- Display number of records in the table
SELECT Count(*) FROM HelloWorld


-- DML Operation UPDATE, updating a specific row in the table
UPDATE HelloWorld SET Description = 'Hello, World!' WHERE Id = 1


-- Selecting rows from the table (see how the Description has changed after the update?)
SELECT * FROM HelloWorld


-- DML Operation - DELETE, deleting a row from the table
DELETE FROM HelloWorld WHERE Id = 1


-- Selecting the table. See table content after DELETE operation 
SELECT * FROM HelloWorld

In questo script stiamo creando una tabella per dimostrare alcune query di base.

I seguenti esempi mostrano come interrogare le tabelle:

USE Northwind;
GO
SELECT TOP 10 * FROM Customers 
ORDER BY CompanyName

selezionerà i primi 10 record della tabella Customer , ordinati dalla colonna CompanyName dal database Northwind (che è uno dei database di esempio di Microsoft, può essere scaricato da qui ):

Notare che Use Northwind; cambia il database predefinito per tutte le query successive. È ancora possibile fare riferimento al database utilizzando la sintassi completa in formato [Database]. [Schema]. [Table]:

SELECT TOP 10 * FROM Northwind.dbo.Customers 
ORDER BY CompanyName

SELECT TOP 10 * FROM Pubs.dbo.Authors
ORDER BY City

Questo è utile se stai interrogando dati da diversi database. Si noti che dbo , specificato "in between" è chiamato schema e deve essere specificato mentre si utilizza la sintassi completa. Puoi considerarlo come una cartella all'interno del tuo database. dbo è lo schema predefinito. Lo schema predefinito può essere omesso. Tutti gli altri schemi definiti dall'utente devono essere specificati.

Se la tabella del database contiene colonne denominate come parole riservate, ad es. Date , è necessario racchiudere il nome della colonna tra parentesi, in questo modo:

-- descending order
SELECT TOP 10 [Date] FROM dbo.MyLogTable
ORDER BY [Date] DESC

Lo stesso vale se il nome della colonna contiene spazi nel suo nome (che non è raccomandato). Una sintassi alternativa è usare le virgolette anziché le parentesi quadre, ad es .:

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
order by "Date" desc 

è equivalente ma non così comunemente usato. Notare la differenza tra virgolette e virgolette singole: le virgolette singole sono usate per le stringhe, es

-- descending order
SELECT top 10 "Date" from dbo.MyLogTable
where UserId='johndoe'
order by "Date" desc 

è una sintassi valida. Si noti che T-SQL ha un prefisso N per i tipi di dati NChar e NVarchar, ad es

SELECT TOP 10 * FROM Northwind.dbo.Customers 
WHERE CompanyName LIKE N'AL%'
ORDER BY CompanyName

restituisce tutte le società che hanno un nome di società che inizia con AL ( % è una wild card, usala come se dovessi usare l'asterisco in una riga di comando DOS, ad esempio DIR AL* ). Per LIKE , ci sono un paio di caratteri jolly disponibili, guarda qui per scoprire maggiori dettagli.

Si unisce

I join sono utili se si desidera eseguire query su campi che non esistono in una singola tabella, ma in più tabelle. Per esempio: Si vuole interrogare tutte le colonne della Region tabella nel Northwind database. Ma si nota che è necessario anche RegionDescription , che è memorizzato in una tabella diversa, Region . Tuttavia, esiste una chiave comune, RgionID che è possibile utilizzare per combinare queste informazioni in una singola query come segue ( Top 5 restituisce solo le prime 5 righe, omettendole per ottenere tutte le righe):

SELECT TOP 5 Territories.*, 
    Regions.RegionDescription 
FROM Territories 
INNER JOIN Region 
    ON Territories.RegionID=Region.RegionID
ORDER BY TerritoryDescription

mostrerà tutte le colonne da Territories più la colonna RegionDescription da Region . Il risultato è ordinato da TerritoryDescription .

Alias ​​di tabella

Quando la tua query richiede un riferimento a due o più tabelle, potresti trovare utile utilizzare un alias di tabella. Gli alias di tabella sono riferimenti abbreviati a tabelle che possono essere utilizzati al posto di un nome di tabella completo e possono ridurre la digitazione e la modifica. La sintassi per l'utilizzo di un alias è:

<TableName> [as] <alias>

Dove as è una parola chiave opzionale. Ad esempio, la query precedente può essere riscritta come:

SELECT TOP 5 t.*, 
    r.RegionDescription 
FROM Territories t
INNER JOIN Region r 
    ON t.RegionID = r.RegionID
ORDER BY TerritoryDescription

Gli alias devono essere univoci per tutte le tabelle di una query, anche se si utilizza la stessa tabella due volte. Ad esempio, se la tabella Employee include un campo SupervisorId, è possibile utilizzare questa query per restituire un dipendente e il nome del proprio supervisore:

SELECT e.*, 
    s.Name as SupervisorName -- Rename the field for output
FROM Employee e
INNER JOIN Employee s
    ON e.SupervisorId = s.EmployeeId
WHERE e.EmployeeId = 111

sindacati

Come abbiamo visto prima, un join aggiunge colonne da diverse origini di tabelle. Ma cosa succede se si desidera combinare righe provenienti da fonti diverse? In questo caso puoi usare UNION. Supponiamo che tu stia pianificando una festa e desideri invitare non solo i dipendenti, ma anche i clienti. Quindi puoi eseguire questa query per farlo:

SELECT FirstName+' '+LastName as ContactName, Address, City FROM Employees
UNION
SELECT ContactName, Address, City FROM Customers

Restituirà nomi, indirizzi e città da dipendenti e clienti in un'unica tabella. Tieni presente che le righe duplicate (se ce ne dovrebbero essere) vengono automaticamente eliminate (se non lo desideri, utilizza invece UNION ALL ). Il numero di colonna, i nomi di colonna, l'ordine e il tipo di dati devono corrispondere a tutte le istruzioni select che fanno parte dell'unione: ecco perché il primo SELECT combina FirstName e LastName da Employee in ContactName .

Variabili di tabella

Può essere utile, se hai bisogno di gestire i dati temporanei (specialmente in una procedura memorizzata), per utilizzare le variabili di tabella: la differenza tra una tabella "reale" e una variabile di tabella è che esiste solo in memoria per l'elaborazione temporanea.

Esempio:

DECLARE @Region TABLE
(
  RegionID int, 
  RegionDescription NChar(50)
)

crea una tabella in memoria. In questo caso il prefisso @ è obbligatorio perché è una variabile. Puoi eseguire tutte le operazioni DML sopra menzionate per inserire, eliminare e selezionare righe, ad es

INSERT INTO @Region values(3,'Northern')
INSERT INTO @Region values(4,'Southern')

Ma normalmente, lo si popolerebbe sulla base di un tavolo reale come

INSERT INTO @Region
SELECT * FROM dbo.Region WHERE RegionID>2;

che legge i valori filtrati dalla reale tabella dbo.Region e lo inserisce nella tabella di memoria @Region - dove può essere utilizzato per ulteriori elaborazioni. Ad esempio, potresti usarlo in un join come

SELECT * FROM Territories t
JOIN @Region r on t.RegionID=r.RegionID

che in questo caso restituirebbe tutti i territori del Northern e del Southern . Informazioni più dettagliate possono essere trovate qui . Le tabelle temporanee sono discusse qui , se sei interessato a saperne di più su questo argomento.

NOTA: Microsoft consiglia solo l'uso di variabili di tabella se il numero di righe di dati nella variabile di tabella è inferiore a 100. Se si utilizzano quantità maggiori di dati, utilizzare invece una tabella temporanea o una tabella temporanea .

STAMPARE

Mostra un messaggio alla console di output. Utilizzando SQL Server Management Studio, questo verrà visualizzato nella scheda dei messaggi, anziché nella scheda dei risultati:

PRINT 'Hello World!';

SELEZIONA tutte le righe e le colonne da una tabella

Sintassi:

SELECT *
FROM table_name

L'utilizzo dell'operatore asterisco * funge da scorciatoia per selezionare tutte le colonne nella tabella. Verranno inoltre selezionate tutte le righe poiché questa istruzione SELECT non ha una clausola WHERE , per specificare eventuali criteri di filtro.

Ciò funzionerebbe allo stesso modo se hai aggiunto un alias alla tabella, ad esempio e in questo caso:

SELECT *
FROM Employees AS e

O se vuoi selezionare tutto da una tabella specifica puoi usare l'alias + ". *":

SELECT e.*, d.DepartmentName
FROM Employees AS e
    INNER JOIN Department AS d 
        ON e.DepartmentID = d.DepartmentID

È possibile accedere agli oggetti del database anche utilizzando nomi completi:

SELECT * FROM [server_name].[database_name].[schema_name].[table_name]

Ciò non è necessariamente raccomandato, in quanto la modifica dei nomi di server e / o database causerebbe l'interruzione delle query utilizzando nomi completi per i nomi di oggetti non validi.

Si noti che i campi prima di table_name possono essere omessi in molti casi se le query vengono eseguite su un singolo server, database e schema, rispettivamente. Tuttavia, è comune che un database abbia più schemi e, in questi casi, il nome dello schema non dovrebbe essere omesso quando possibile.

Avvertenza: l' utilizzo di SELECT * nel codice di produzione o nelle stored procedure può causare problemi in seguito (quando nuove colonne vengono aggiunte alla tabella o se le colonne vengono ridisposte nella tabella), specialmente se il codice fa presupposti semplici sull'ordine delle colonne, o numero di colonne restituite. Quindi è più sicuro specificare sempre i nomi delle colonne nelle istruzioni SELECT per il codice di produzione.

SELECT col1, col2, col3
FROM table_name

Seleziona le righe che corrispondono a una condizione

Generalmente, la sintassi è:

SELECT <column names>
FROM <table name>
WHERE <condition>

Per esempio:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith'

Le condizioni possono essere complesse:

SELECT FirstName, Age
FROM Users
WHERE LastName = 'Smith' AND (City = 'New York' OR City = 'Los Angeles')

UPDATE Specifica riga

UPDATE HelloWorlds
SET HelloWorld = 'HELLO WORLD!!!'
WHERE Id = 5

Il codice sopra riportato aggiorna il valore del campo "HelloWorld" con "CIAO MONDO !!!" per il record in cui "Id = 5" nella tabella HelloWorlds.

Nota: in una dichiarazione di aggiornamento, si consiglia di utilizzare una clausola "where" per evitare di aggiornare l'intera tabella a meno che e fino a quando il requisito non sia diverso.

AGGIORNA Tutte le righe

Una semplice forma di aggiornamento sta incrementando tutti i valori in un dato campo della tabella. Per fare ciò, dobbiamo definire il campo e il valore dell'incremento

Di seguito è riportato un esempio che incrementa il campo Score di 1 (in tutte le righe):

UPDATE Scores
SET score = score + 1  

Questo può essere pericoloso poiché puoi danneggiare i tuoi dati se accidentalmente fai un UPDATE per una riga specifica con un UPDATE per Tutte le righe nella tabella.

Commenti nel codice

Transact-SQL supporta due forme di scrittura di commenti. I commenti vengono ignorati dal motore del database e sono pensati per essere letti.

I commenti sono preceduti da -- e vengono ignorati fino a quando non viene rilevata una nuova riga:

-- This is a comment
SELECT *
FROM MyTable -- This is another comment
WHERE Id = 1;

I commenti barra iniziale iniziano con /* e terminano con */ . Tutto il testo tra quei delimitatori è considerato un blocco di commenti.

/* This is
a multi-line
comment block. */
SELECT Id = 1, [Message] = 'First row'
UNION ALL
SELECT 2, 'Second row'
/* This is a one liner */
SELECT 'More';

I commenti barra stella hanno il vantaggio di mantenere il commento utilizzabile se l'istruzione SQL perde caratteri di nuova riga. Questo può accadere quando SQL viene catturato durante la risoluzione dei problemi.

I commenti delle stelle di tipo slash possono essere nidificati e l'inizio /* all'interno di un commento a stella barra deve essere terminato con un */ per essere valido. Il seguente codice provocherà un errore

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/

La stella di taglio anche se all'interno della citazione è considerata come l'inizio di un commento. Quindi deve essere terminato con un altro taglio di chiusura. Il modo corretto sarebbe

/*
SELECT *
FROM CommentTable
WHERE Comment = '/*'
*/  */

Recupera informazioni sul server di base

SELECT @@VERSION

Restituisce la versione di MS SQL Server in esecuzione nell'istanza.

SELECT @@SERVERNAME

Restituisce il nome dell'istanza MS SQL Server.

SELECT @@SERVICENAME

Restituisce il nome del servizio Windows MS SQL Server è in esecuzione come.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Restituisce il nome fisico della macchina su cui è in esecuzione SQL Server. Utile per identificare il nodo in un cluster di failover.

SELECT * FROM fn_virtualservernodes();

In un cluster di failover restituisce tutti i nodi su cui è possibile eseguire SQL Server. Non restituisce nulla se non un cluster.

Utilizzo delle transazioni per modificare i dati in modo sicuro

Ogni volta che si modificano i dati, in un comando DML (Data Manipulation Language), è possibile includere le modifiche in una transazione. DML include UPDATE , TRUNCATE , INSERT e DELETE . Uno dei modi in cui puoi essere sicuro che stai cambiando i dati giusti sarebbe utilizzare una transazione.

Le modifiche DML prenderanno un blocco sulle righe interessate. Quando inizi una transazione, devi terminare la transazione o tutti gli oggetti modificati nel DML rimarranno bloccati da chiunque abbia iniziato la transazione. Puoi terminare la transazione con ROLLBACK o COMMIT . ROLLBACK restituisce tutto nella transazione allo stato originale. COMMIT inserisce i dati in uno stato finale in cui non è possibile annullare le modifiche senza un'altra istruzione DML.

Esempio:

--Create a test table

USE [your database]
GO
CREATE TABLE test_transaction (column_1 varchar(10))
GO

INSERT INTO 
 dbo.test_transaction
        ( column_1 )
VALUES
        ( 'a' )

BEGIN TRANSACTION --This is the beginning of your transaction

UPDATE dbo.test_transaction
SET column_1 = 'B'
OUTPUT INSERTED.*
WHERE column_1 = 'A'
  

ROLLBACK TRANSACTION  --Rollback will undo your changes
           --Alternatively, use COMMIT to save your results

SELECT * FROM dbo.test_transaction   --View the table after your changes have been run

DROP TABLE dbo.test_transaction

Gli appunti:

• Questo è un esempio semplificato che non include la gestione degli errori. Ma qualsiasi operazione di database può fallire e quindi generare un'eccezione. Ecco un esempio di come potrebbe essere una tale gestione degli errori richiesta. Non dovresti mai usare le transazioni senza un gestore di errori , altrimenti potresti lasciare la transazione in uno stato sconosciuto.
• A seconda del livello di isolamento , le transazioni stanno mettendo i blocchi sui dati interrogati o modificati. È necessario assicurarsi che le transazioni non siano in esecuzione per un lungo periodo, poiché bloccano i record in un database e possono portare a deadlock con altre transazioni parallele in esecuzione. Mantieni le operazioni incapsulate nelle transazioni il più breve possibile e minimizza l'impatto con la quantità di dati che stai bloccando.

CANCELLA Tutte le righe

DELETE
FROM Helloworlds

Questo cancellerà tutti i dati dalla tabella. La tabella non conterrà righe dopo aver eseguito questo codice. A differenza di DROP TABLE , questo conserva la tabella stessa e la sua struttura e puoi continuare a inserire nuove righe in quella tabella.

Un altro modo per eliminare tutte le righe nella tabella è troncarlo, come segue:

TRUNCATE TABLE HelloWords

Le differenze con l'operazione DELETE sono diverse:

1. L'operazione di troncamento non viene archiviata nel file di registro delle transazioni
2. Se esiste il campo IDENTITY , questo sarà resettato
3. TRUNCATE può essere applicato su tutta la tabella e no su parte di esso (invece con il comando DELETE è possibile associare una clausola WHERE )

Restrizioni di TRUNCATE

1. Impossibile TRONCATARE una tabella se è presente un riferimento FOREIGN KEY
2. Se il tavolo è partecipato a una INDEXED VIEW
3. Se la tabella viene pubblicata utilizzando TRANSACTIONAL REPLICATION o MERGE REPLICATION
4. Non verrà generato alcun TRIGGER definito nella tabella

[sic]

TABELLA DEI TRONCATI

TRUNCATE TABLE Helloworlds 

Questo codice cancellerà tutti i dati dalla tabella Helloworlds. La tabella troncata è quasi simile a Delete from Table codice Delete from Table . La differenza è che non è possibile utilizzare le clausole where con Truncate. La tabella troncata è considerata migliore dell'eliminazione perché utilizza meno spazi del log delle transazioni.

Si noti che se esiste una colonna Identity, viene reimpostata sul valore di inizializzazione iniziale (ad esempio, l'ID con incremento automatico verrà riavviato da 1). Ciò può causare incoerenza se le colonne Identity vengono utilizzate come chiavi esterne in un'altra tabella.

Crea una nuova tabella e inserisci i record dalla vecchia tabella

SELECT * INTO NewTable FROM OldTable

Crea una nuova tabella con la struttura della vecchia tabella e inserisce tutte le righe nella nuova tabella.

Alcune restrizioni

1. Non è possibile specificare una variabile di tabella o un parametro con valori di tabella come nuova tabella.
2. Non è possibile utilizzare SELECT ... INTO per creare una tabella partizionata, anche quando la tabella di origine è partizionata. SELECT ... INTO non usa lo schema di partizione della tabella di origine; al contrario, la nuova tabella viene creata nel filegroup predefinito. Per inserire righe in una tabella partizionata, è necessario prima creare la tabella partizionata e quindi utilizzare l'istruzione INSERT INTO ... SELECT FROM.
3. Gli indici, i vincoli e i trigger definiti nella tabella di origine non vengono trasferiti alla nuova tabella, né possono essere specificati nell'istruzione SELECT ... INTO. Se questi oggetti sono richiesti, è possibile crearli dopo aver eseguito l'istruzione SELECT ... INTO.
4. La specifica di una clausola ORDER BY non garantisce che le righe vengano inserite nell'ordine specificato. Quando una colonna sparsa è inclusa nell'elenco di selezione, la proprietà della colonna sparsa non viene trasferita alla colonna nella nuova tabella. Se questa proprietà è richiesta nella nuova tabella, modificare la definizione della colonna dopo aver eseguito l'istruzione SELECT ... INTO per includere questa proprietà.
5. Quando una colonna calcolata è inclusa nell'elenco di selezione, la colonna corrispondente nella nuova tabella non è una colonna calcolata. I valori nella nuova colonna sono i valori che sono stati calcolati al momento in cui SELECT ... INTO è stato eseguito.

[ sic ]

Ottenere il conteggio delle righe della tabella

L'esempio seguente può essere utilizzato per trovare il numero totale di righe per una tabella specifica in un database se table_name viene sostituito dalla tabella che si desidera interrogare:

SELECT COUNT(*) AS [TotalRowCount] FROM table_name;

È anche possibile ottenere il conteggio delle righe per tutte le tabelle tornando alla partizione della tabella in base alle tabelle HEAP (index_id = 0) o cluster cluster index (index_id = 1) utilizzando il seguente script:

SELECT  [Tables].name                AS [TableName],
        SUM( [Partitions].[rows] )    AS [TotalRowCount]
FROM    sys.tables AS [Tables]
JOIN    sys.partitions AS [Partitions]
    ON  [Tables].[object_id]    =    [Partitions].[object_id]
    AND [Partitions].index_id IN ( 0, 1 )
--WHERE    [Tables].name = N'table name' /* uncomment to look for a specific table */
GROUP BY    [Tables].name;

Ciò è possibile in quanto ogni tabella è essenzialmente una singola tabella di partizione, a meno che non vengano aggiunte ulteriori partizioni. Questo script ha anche il vantaggio di non interferire con le operazioni di lettura / scrittura sulle righe delle tabelle '.