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


Esempio

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 ):

Query del database Northwind

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 .