Lezione 1: Creare ed eseguire query per oggetti di database

Si applica a: SQL Server Database SQL di Azure Istanza gestita di SQL di Azure Piattaforma di strumenti analitici (PDW)

In questa lezione vengono illustrate le procedure per creare un database, creare in quest'ultimo una tabella, nonché come accedere ai dati della tabella e modificarli. Poiché l'obiettivo di questa lezione è offrire un'introduzione all'uso di Transact-SQL, non verranno usate né descritte le numerose opzioni disponibili per le istruzioni.

Le istruzioni Transact-SQL possono essere scritte e inviate al motore di database nei modi seguenti:

• Mediante SQL Server Management Studio. In questa esercitazione si presuppone che venga usato Management Studio, ma è anche possibile usare Management Studio Express, disponibile come download gratuito nell'Area download Microsoft.

• Mediante l' utilità sqlcmd.

• Mediante la connessione da un'applicazione creata dall'utente.

Il codice viene eseguito nel motore di database nello stesso modo e con le stesse autorizzazioni, indipendentemente da come vengono inviate le istruzioni.

Per eseguire istruzioni Transact-SQL in Management Studio, aprire Management Studio e connettersi a un'istanza del motore di database di SQL Server.

Prerequisiti

Per completare questa esercitazione è necessario avere SQL Server Management Studio e l'accesso a un'istanza di SQL Server.

• Installare SQL Server Management Studio.

Se non è disponibile un'istanza di SQL Server, crearne una. Per crearne una, selezionare la piattaforma nei collegamenti seguenti. Se si sceglie Autenticazione SQL, usare le credenziali di accesso di SQL Server.

• Windows: scaricare SQL Server 2022 Developer Edition.
• Linux: scaricare SQL Server 2022 in un contenitore.

Creazione di un database

Analogamente a numerose altre istruzioni, l'istruzione CREATE DATABASE ha un parametro obbligatorio, ovvero il nome del database. CREATE DATABASE include anche molti parametri facoltativi, ad esempio il percorso su disco in cui inserire i file del database. Quando si esegue CREATE DATABASE senza parametri facoltativi, SQL Server usa i valori predefiniti per molti di tali parametri.

1. In una finestra dell'editor di query digitare ma non eseguire il codice seguente:

   CREATE DATABASE TestData
   GO
   

2. Usare il puntatore per selezionare le parole CREATE DATABASEe quindi premere F1. L'articolo CREATE DATABASE dovrebbe essere aperto. È possibile usare questa tecnica per trovare la sintassi completa di CREATE DATABASE e delle altre istruzioni usate in questa esercitazione.

3. Nell'editor di query premere F5 per eseguire l'istruzione e creare un database denominato TestData.

Quando si crea un database, SQL Server esegue una copia del database model a cui assegna il nome del database specificato. L'operazione dovrebbe richiedere solo alcuni secondi a meno che non si specifichi una dimensione iniziale per il database particolarmente grande come parametro facoltativo.

Crea una tabella

Si applica a: SQL Server Database SQL di Azure Azure Synapse Analytics Piattaforma di strumenti analitici (PDW)

Per creare una tabella, è necessario specificare un nome per la tabella e i nomi e i tipi di dati di ogni colonna di quest'ultima. È inoltre consigliabile indicare se sono consentiti valori Null in ogni colonna. Per creare una tabella è necessario avere l'autorizzazione CREATE TABLE e l'autorizzazione ALTER SCHEMA per lo schema che conterrà la tabella. Il ruolo predefinito del database db_ddladmin dispone di tali autorizzazioni.

La maggior parte delle tabelle dispone di una chiave primaria costituita da una o più colonne. La chiave primaria è sempre univoca. Nel motore di database è applicata la limitazione per cui nessun valore di chiave primaria può essere ripetuto nella tabella.

Per un elenco di tipi di dati e collegamenti alle relative descrizioni, vedere Tipi di dati (Transact-SQL).

Connessione dell'editor di query al database TestData

Nella finestra dell'editor di query digitare ed eseguire il codice seguente per connettersi al database TestData .

USE TestData
GO

Creare la tabella

Nella finestra dell'editor di query digitare ed eseguire il codice seguente per creare una tabella denominata Products. Le colonne nella tabella vengono denominate ProductID, ProductName, Pricee ProductDescription. La colonna ProductID rappresenta la chiave primaria della tabella. int, varchar(25), moneye varchar(max) sono tutti tipi di dati. Le uniche colonne che possono non contenere dati quando viene inserita o modificata un riga sono Price e ProductionDescription . Questa istruzione contiene un elemento facoltativo (dbo.) che corrisponde a uno schema. Lo schema rappresenta l'oggetto di database a cui appartiene la tabella. Se si è un amministratore, dbo è lo schema predefinito. dbo corrisponde al proprietario del database.

CREATE TABLE dbo.Products
    (ProductID int PRIMARY KEY NOT NULL,
    ProductName varchar(25) NOT NULL,
    Price money NULL,
    ProductDescription varchar(max) NULL)
GO

Inserire e aggiornare dati in una tabella

Dopo aver creato la tabella Products è possibile inserirvi dati con l'istruzione INSERT. Dopo aver inserito i dati, si procederà alla modifica del contenuto di una riga mediante l'istruzione UPDATE. Per limitare l'operazione di aggiornamento a una sola riga viene utilizzata la clausola WHERE dell'istruzione UPDATE. Le quattro istruzioni immettono i dati seguenti.

La sintassi di base è INSERT, nome tabella, elenco colonne, VALUES, a cui segue quindi un elenco dei valori da inserire. I due trattini davanti a una riga indicano che si tratta di un commento il cui testo viene ignorato dal compilatore. In questo caso il commento descrive una variazione consentita della sintassi.

Inserire dati in una tabella

1. Eseguire l'istruzione seguente per inserire una riga nella tabella Products creata nell'attività precedente.

   -- Standard syntax
   INSERT dbo.Products (ProductID, ProductName, Price, ProductDescription)
       VALUES (1, 'Clamp', 12.48, 'Workbench clamp')
   GO
   

   Se l'inserimento ha esito positivo, procedere con il passaggio successivo.

   Se l'inserimento non riesce, è possibile che la tabella Product includa già una riga con tale ID prodotto. Per continuare, eliminare tutte le righe della tabella e ripetere il passaggio precedente. TRUNCATE TABLE elimina tutte le righe nella tabella.

   Eseguire il comando seguente per eliminare tutte le righe nella tabella:

   TRUNCATE TABLE TestData.dbo.Products;
   GO
   

   Dopo aver troncato la tabella, ripetere il comando INSERT in questo passaggio.

2. L'istruzione seguente illustra come modificare l'ordine in cui vengono specificati i parametri scambiando la posizione di ProductID e ProductName in entrambi gli elenchi di campi (tra parentesi) e nell'elenco dei valori.

   -- Changing the order of the columns
   INSERT dbo.Products (ProductName, ProductID, Price, ProductDescription)
       VALUES ('Screwdriver', 50, 3.17, 'Flat head')
   GO
   

3. L'istruzione seguente illustra che i nomi delle colonne sono facoltativi a condizione che i valori siano elencati nell'ordine corretto. Questa sintassi comune non è tuttavia consigliata poiché potrebbe rendere il codice di difficile comprensione per gli altri utenti. NULL viene specificato per la colonna Price perché il prezzo del prodotto corrispondente non è ancora noto.

   -- Skipping the column list, but keeping the values in order
   INSERT dbo.Products
       VALUES (75, 'Tire Bar', NULL, 'Tool for changing tires.')
   GO
   

4. Il nome dello schema è facoltativo a condizione che si acceda per la modifica a una tabella inclusa nello schema predefinito. Poiché la colonna ProductDescription supporta valori Null e non viene specificato alcun valore, il nome e il valore della colonna ProductDescription verranno eliminati completamente dall'istruzione.

   -- Dropping the optional dbo and dropping the ProductDescription column
   INSERT Products (ProductID, ProductName, Price)
       VALUES (3000, '3 mm Bracket', 0.52)
   GO
   

Aggiornare la tabella dei prodotti

Digitare ed eseguire l'istruzione UPDATE seguente per modificare il valore ProductName del secondo prodotto da Screwdriverin Flat Head Screwdriver.

UPDATE dbo.Products
    SET ProductName = 'Flat Head Screwdriver'
    WHERE ProductID = 50
GO

Leggere dati da una tabella

Utilizzare l'istruzione SELECT per leggere i dati archiviati in una tabella. L'istruzione SELECT è una delle istruzioni Transact-SQL più importanti e la relativa sintassi presenta numerose varianti. Ai fini di questa esercitazione ne verranno utilizzate cinque versioni di base.

Leggere i dati di una tabella

1. Per leggere i dati archiviati nella tabella Products , digitare ed eseguire le istruzioni seguenti.

   -- The basic syntax for reading data from a single table
   SELECT ProductID, ProductName, Price, ProductDescription
       FROM dbo.Products
   GO
   

2. È possibile usare un asterisco (*) per selezionare tutte le colonne nella tabella. L'asterisco è per le query ad hoc. Specificare l'elenco delle colonne nel codice permanente in modo che l'istruzione restituisca le colonne previste anche se viene successivamente aggiunta una nuova colonna alla tabella.

   -- Returns all columns in the table
   -- Does not use the optional schema, dbo
   SELECT * FROM Products
   GO
   

3. È possibile omettere le colonne che non si desidera vengano restituite. Le colonne vengono restituite nell'ordine in cui sono elencate.

   -- Returns only two of the columns from the table
   SELECT ProductName, Price
       FROM dbo.Products
   GO
   

4. Utilizzare una clausola WHERE per limitare le righe restituite.

   -- Returns only two of the records in the table
   SELECT ProductID, ProductName, Price, ProductDescription
       FROM dbo.Products
       WHERE ProductID < 60
   GO
   

5. È possibile utilizzare i valori nelle colonne quando vengono restituiti. L'esempio seguente esegue un'operazione matematica sulla colonna Price . Alle colonne modificate in questo modo non viene assegnato un nome a meno che non se ne specifichi uno utilizzando la parola chiave AS.

   -- Returns ProductName and the Price including a 7% tax
   -- Provides the name CustomerPays for the calculated column
   SELECT ProductName, Price * 1.07 AS CustomerPays
       FROM dbo.Products
   GO
   

Funzioni utili in un'istruzione SELECT

Per informazioni su alcune funzioni che consentono di utilizzare i dati in un'istruzione SELECT, vedere gli articoli seguenti:

Creare viste e stored procedure

Una vista è costituita da un'istruzione SELECT, mentre una stored procedure da una o più istruzioni Transact-SQL eseguite in batch.

Sulle viste è possibile eseguire query come con le tabelle, ad eccezione del fatto che le viste non accettano parametri. Le stored procedure sono più complesse delle viste e possono avere parametri di input e output, nonché contenere istruzioni per controllare il flusso del codice, ad esempio istruzioni IF e WHILE. È consigliabile dal punto di vista della programmazione utilizzare le stored procedure per tutte le azioni ripetitive sul database.

In questo esempio si usa CREATE VIEW per creare una vista che seleziona solo due delle colonne incluse nella tabella Products. Si usa quindi CREATE PROCEDURE per creare una stored procedure che accetta un parametro di prezzo e restituisce solo i prodotti il cui costo è inferiore al valore di tale parametro.

Crea una visualizzazione

Eseguire l'istruzione seguente per creare un vista che esegue un'istruzione SELECT e restituisce i nomi e i prezzi dei prodotti.

CREATE VIEW vw_Names
   AS
   SELECT ProductName, Price FROM Products;
GO

Test della vista

Le viste vengono utilizzate in modo analogo alle tabelle. Utilizzare un'istruzione SELECT per accedere a una vista.

SELECT * FROM vw_Names;
GO

Creare una stored procedure

L'istruzione seguente crea una stored procedure denominata pr_Namesche accetta un parametro di input denominato @VarPrice con tipo di dati money. La stored procedure visualizza il testo Products less than concatenata al parametro di input modificato da tipo di dati money in tipo di dati carattere varchar(10) . La procedura esegue quindi un'istruzione SELECT sulla vista, passando il parametro di input come parte della clausola WHERE . Ciò restituisce tutti i prodotti il cui costo è inferiore al valore del parametro di input.

CREATE PROCEDURE pr_Names @VarPrice money
   AS
   BEGIN
      -- The print statement returns text to the user
      PRINT 'Products less than ' + CAST(@VarPrice AS varchar(10));
      -- A second statement starts here
      SELECT ProductName, Price FROM vw_Names
            WHERE Price < @VarPrice;
   END
GO

Test della stored procedure

Per testare la stored procedure, digitare ed eseguire l'istruzione seguente. La procedura restituirà i nomi di due prodotti immessi nella tabella Products nella lezione 1 con prezzo inferiore a 10.00.

EXECUTE pr_Names 10.00;
GO

Passaggi successivi

L'articolo successivo illustra come configurare autorizzazioni per gli oggetti di database. Gli oggetti creati nella lezione 1 verranno usati anche nella lezione 2.

Per altre informazioni, vedere l'articolo successivo: