Microsoft SQL Server Tutoriel
Démarrer avec Microsoft SQL Server

Remarques

Versions

INSERT / SELECT / UPDATE / DELETE: les bases du langage de manipulation de données

D ata M anipulation L anguage (DML en abrégé) inclut des opérations telles que INSERT , UPDATE et 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

Dans ce script, nous créons un tableau pour illustrer certaines requêtes de base.

Les exemples suivants montrent comment interroger des tables:

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

sélectionnera les 10 premiers enregistrements de la table Customer , classés par la colonne CompanyName de la base de données Northwind (qui est l'une des bases de données exemple de Microsoft, téléchargeable ici ):

Notez que Use Northwind; modifie la base de données par défaut pour toutes les requêtes suivantes. Vous pouvez toujours référencer la base de données en utilisant la syntaxe complète sous la forme [Base de données]. [Schéma]. [Table]:

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

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

Ceci est utile si vous interrogez des données provenant de différentes bases de données. Notez que dbo , spécifié "in between" est appelé un schéma et doit être spécifié en utilisant la syntaxe complète. Vous pouvez le considérer comme un dossier dans votre base de données. dbo est le schéma par défaut. Le schéma par défaut peut être omis. Tous les autres schémas définis par l'utilisateur doivent être spécifiés.

Si la table de base de données contient des colonnes nommées comme des mots réservés, par exemple Date , vous devez inclure le nom de la colonne entre parenthèses, comme ceci:

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

La même chose s’applique si le nom de la colonne contient des espaces dans son nom (ce qui n’est pas recommandé). Une syntaxe alternative consiste à utiliser des guillemets au lieu de crochets, par exemple:

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

est équivalent mais pas si couramment utilisé. Notez la différence entre guillemets et guillemets simples: les guillemets simples sont utilisés pour les chaînes, c.-à-d.

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

est une syntaxe valide. Notez que T-SQL a un préfixe N pour les types de données NChar et NVarchar, par exemple

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

renvoie toutes les entreprises dont le nom de société commence par AL ( % est un caractère générique, utilisez-le comme vous l'utiliseriez dans une ligne de commande DOS, par exemple DIR AL* ). Pour LIKE , quelques jokers sont disponibles, regardez ici pour en savoir plus.

Joint

Les jointures sont utiles si vous souhaitez interroger des champs qui n'existent pas dans une seule table, mais dans plusieurs tables. Par exemple: vous souhaitez interroger toutes les colonnes de la table Region dans la base de données Northwind . Mais vous remarquez que vous avez également besoin de RegionDescription , qui est stocké dans une autre table, Region . Cependant, il existe une clé commune, RgionID que vous pouvez utiliser pour combiner ces informations en une seule requête, comme suit (le Top 5 renvoie simplement les 5 premières lignes, omet pour obtenir toutes les lignes):

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

affichera toutes les colonnes des Territories plus la colonne RegionDescription de la Region . Le résultat est ordonné par TerritoryDescription .

Alias ​​de table

Lorsque votre requête nécessite une référence à deux tables ou plus, il peut être utile d'utiliser un alias de table. Les alias de table sont des références abrégées à des tables pouvant être utilisées à la place d'un nom de table complet, ce qui peut réduire la saisie et la modification. La syntaxe pour utiliser un alias est la suivante:

<TableName> [as] <alias>

Où as est un mot - clé facultatif. Par exemple, la requête précédente peut être réécrite comme suit:

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

Les alias doivent être uniques pour toutes les tables d'une requête, même si vous utilisez la même table deux fois. Par exemple, si votre table Employee contient un champ SupervisorId, vous pouvez utiliser cette requête pour renvoyer un employé et le nom de son superviseur:

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

Les syndicats

Comme nous l'avons vu précédemment, une jointure ajoute des colonnes provenant de différentes sources de table. Mais que faire si vous souhaitez combiner des lignes provenant de différentes sources? Dans ce cas, vous pouvez utiliser un UNION. Supposons que vous planifiez une fête et que vous souhaitiez inviter non seulement les employés mais également les clients. Ensuite, vous pouvez exécuter cette requête pour le faire:

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

Il renverra les noms, adresses et villes des employés et des clients dans une seule table. Notez que les lignes en double (s'il y en a) sont automatiquement éliminées (si vous ne le souhaitez pas, utilisez plutôt UNION ALL ). Le numéro de colonne, les noms de colonne, l'ordre et le type de données doivent correspondre à toutes les instructions select faisant partie de l'union. C'est pourquoi le premier SELECT combine FirstName et LastName d'Employé dans ContactName .

Variables de table

Cela peut être utile, si vous avez besoin de gérer des données temporaires (en particulier dans une procédure stockée), pour utiliser des variables de table: La différence entre une table "réelle" et une variable de table

Exemple:

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

crée une table en mémoire. Dans ce cas, le préfixe @ est obligatoire car il s'agit d'une variable. Vous pouvez effectuer toutes les opérations DML mentionnées ci-dessus pour insérer, supprimer et sélectionner des lignes, par exemple

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

Mais normalement, vous le rempliriez sur la base d'une vraie table comme

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

qui lirait les valeurs filtrées de la table réelle dbo.Region et l'insérerait dans la table de mémoire @Region - où il pourrait être utilisé pour un traitement ultérieur. Par exemple, vous pouvez l'utiliser dans une jointure comme

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

qui dans ce cas retournerait tous Southern territoires Southern Northern et du Southern . Des informations plus détaillées peuvent être trouvées ici . Les tables temporaires sont discutées ici , si vous êtes intéressé pour en savoir plus sur ce sujet.

REMARQUE: Microsoft recommande uniquement l'utilisation de variables de table si le nombre de lignes de données de la variable de table est inférieur à 100. Si vous travaillez avec de plus grandes quantités de données, utilisez plutôt une table temporaire ou temporaire.

IMPRESSION

Afficher un message sur la console de sortie. À l'aide de SQL Server Management Studio, cela sera affiché dans l'onglet Messages plutôt que dans l'onglet Résultats:

PRINT 'Hello World!';

SÉLECTIONNEZ toutes les lignes et colonnes d'une table

Syntaxe:

SELECT *
FROM table_name

L'utilisation de l'opérateur astérisque * sert de raccourci pour sélectionner toutes les colonnes de la table. Toutes les lignes seront également sélectionnées car cette SELECT ne contient pas de clause WHERE , pour spécifier des critères de filtrage.

Cela fonctionnerait également de la même manière si vous ajoutiez un alias à la table, par exemple e dans ce cas:

SELECT *
FROM Employees AS e

Ou si vous voulez tout sélectionner dans une table spécifique, vous pouvez utiliser l'alias + ". *":

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

On peut également accéder aux objets de base de données en utilisant des noms complets:

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

Cela n'est pas nécessairement recommandé, car la modification des noms de serveur et / ou de base de données entraînerait la non-exécution des requêtes utilisant des noms qualifiés complets en raison de noms d'objet non valides.

Notez que les champs avant table_name peuvent être omis dans de nombreux cas si les requêtes sont exécutées sur un seul serveur, une base de données et un schéma, respectivement. Cependant, il est fréquent qu'une base de données ait plusieurs schémas et, dans ce cas, le nom du schéma ne doit pas être omis lorsque cela est possible.

Avertissement: l' utilisation de SELECT * dans le code de production ou les procédures stockées peut entraîner des problèmes ultérieurs (à mesure que de nouvelles colonnes sont ajoutées à la table ou si des colonnes sont réorganisées dans la table), en particulier si votre code ou nombre de colonnes renvoyées. Il est donc plus sûr de toujours spécifier explicitement les noms de colonnes dans les instructions SELECT pour le code de production.

SELECT col1, col2, col3
FROM table_name

Sélectionnez des lignes correspondant à une condition

Généralement, la syntaxe est la suivante:

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

Par exemple:

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

Les conditions peuvent être complexes:

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

UPDATE Ligne spécifique

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

Le code ci-dessus met à jour la valeur du champ "HelloWorld" avec "HELLO WORLD !!!" pour l'enregistrement où "Id = 5" dans la table HelloWorlds.

Remarque: Dans une instruction de mise à jour, il est conseillé d’utiliser une clause "where" pour éviter de mettre à jour la totalité de la table, sauf si votre exigence est différente.

Mettre à jour toutes les lignes

Une simple forme de mise à jour consiste à incrémenter toutes les valeurs dans un champ donné de la table. Pour ce faire, nous devons définir le champ et la valeur d'incrément

Voici un exemple qui incrémente le champ Score de 1 (dans toutes les lignes):

UPDATE Scores
SET score = score + 1  

Cela peut être dangereux car vous pouvez corrompre vos données si vous faites accidentellement une mise à jour pour une ligne spécifique avec une ligne UPDATE pour tous dans la table.

Commentaires dans le code

Transact-SQL prend en charge deux formes d'écriture de commentaires. Les commentaires sont ignorés par le moteur de base de données et sont destinés aux utilisateurs.

Les commentaires sont précédés de -- et sont ignorés jusqu'à ce qu'une nouvelle ligne soit rencontrée:

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

Les commentaires Slash Star commencent par /* et se terminent par */ . Tout le texte entre ces délimiteurs est considéré comme un bloc de commentaires.

/* 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';

Les commentaires en barre oblique ont l'avantage de garder le commentaire utilisable si l'instruction SQL perd de nouveaux caractères de ligne. Cela peut se produire lorsque SQL est capturé lors du dépannage.

Les commentaires Slash Star peuvent être imbriqués et un début /* dans un commentaire en barre oblique doit être terminé par un */ pour être valide. Le code suivant entraînera une erreur

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

L'étoile de barre oblique même si à l'intérieur de la citation est considérée comme le début d'un commentaire. Par conséquent, il doit être terminé avec une autre barre oblique finale. La bonne façon serait

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

Récupérer les informations de base du serveur

SELECT @@VERSION

Renvoie la version de MS SQL Server exécutée sur l'instance.

SELECT @@SERVERNAME

Renvoie le nom de l'instance MS SQL Server.

SELECT @@SERVICENAME

Renvoie le nom du service Windows sous lequel MS SQL Server s'exécute.

SELECT serverproperty('ComputerNamePhysicalNetBIOS');

Renvoie le nom physique de la machine sur laquelle SQL Server est exécuté. Utile pour identifier le noeud dans un cluster de basculement.

SELECT * FROM fn_virtualservernodes();

Dans un cluster de basculement, renvoie chaque noeud sur lequel SQL Server peut être exécuté. Il ne renvoie rien sinon un cluster.

Utilisation des transactions pour modifier les données en toute sécurité

Chaque fois que vous modifiez des données, dans une commande DML (Data Manipulation Language), vous pouvez envelopper vos modifications dans une transaction. DML inclut UPDATE , TRUNCATE , INSERT et DELETE . L'une des façons dont vous pouvez vous assurer que vous modifiez les bonnes données consiste à utiliser une transaction.

Les modifications DML prennent un verrou sur les lignes affectées. Lorsque vous commencez une transaction, vous devez mettre fin à la transaction ou tous les objets modifiés dans le DML resteront verrouillés par quiconque a commencé la transaction. Vous pouvez mettre fin à votre transaction avec ROLLBACK ou COMMIT . ROLLBACK renvoie tout ce qui se trouve dans la transaction à son état d'origine. COMMIT place les données dans un état final où vous ne pouvez pas annuler vos modifications sans une autre déclaration DML.

Exemple:

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

Remarques:

• Ceci est un exemple simplifié qui n'inclut pas la gestion des erreurs. Mais toute opération de base de données peut échouer et donc lancer une exception. Voici un exemple de la façon dont une gestion des erreurs requise peut se présenter. Vous ne devez jamais utiliser des transactions sans gestionnaire d'erreurs , sinon vous pourriez laisser la transaction dans un état inconnu.
• Selon le niveau d'isolement , les transactions placent des verrous sur les données interrogées ou modifiées. Vous devez vous assurer que les transactions ne sont pas exécutées pendant une longue période, car elles verrouillent les enregistrements dans une base de données et peuvent entraîner des blocages avec d'autres transactions parallèles. Gardez les opérations encapsulées dans les transactions aussi courtes que possible et minimisez l'impact avec la quantité de données que vous verrouillez.

SUPPRIMER toutes les lignes

DELETE
FROM Helloworlds

Cela supprimera toutes les données de la table. La table ne contiendra aucune ligne après avoir exécuté ce code. Contrairement à DROP TABLE , cela préserve la table et sa structure et vous pouvez continuer à insérer de nouvelles lignes dans cette table.

Une autre façon de supprimer toutes les lignes de la table est de la tronquer, comme suit:

TRUNCATE TABLE HelloWords

La différence avec l'opération DELETE est plusieurs:

1. L'opération de troncature ne stocke pas dans le fichier journal des transactions
2. S'il existe un champ IDENTITY , cela sera réinitialisé
3. TRUNCATE peut être appliqué sur une table entière et non sur une partie (avec la commande DELETE vous pouvez associer une clause WHERE )

Restrictions de TRUNCATE

1. Impossible de TRUNCATE une table s'il existe une référence FOREIGN KEY
2. Si la table participe à une vue INDEXED VIEW
3. Si la table est publiée en utilisant TRANSACTIONAL REPLICATION ou MERGE REPLICATION
4. Il ne déclenchera aucun TRIGGER défini dans le tableau

[sic]

TABLEAU TRUNCATE

TRUNCATE TABLE Helloworlds 

Ce code supprimera toutes les données de la table Helloworlds. Le tableau tronqué est presque similaire au code Delete from Table . La différence est que vous ne pouvez pas utiliser les clauses where avec Truncate. La table tronquée est considérée comme meilleure que la suppression car elle utilise moins d’espaces de journal des transactions.

Notez que si une colonne d'identité existe, elle est réinitialisée à la valeur initiale (par exemple, l'ID auto-incrémenté redémarrera à partir de 1). Cela peut entraîner une incohérence si les colonnes d'identité sont utilisées comme clé étrangère dans une autre table.

Créer une nouvelle table et insérer des enregistrements de l'ancienne table

SELECT * INTO NewTable FROM OldTable

Crée une nouvelle table avec la structure de l'ancienne table et insère toutes les lignes dans la nouvelle table.

Quelques restrictions

1. Vous ne pouvez pas spécifier une variable de table ou un paramètre de valeur de table en tant que nouvelle table.
2. Vous ne pouvez pas utiliser SELECT… INTO pour créer une table partitionnée, même lorsque la table source est partitionnée. SELECT ... INTO n'utilise pas le schéma de partition de la table source; à la place, la nouvelle table est créée dans le groupe de fichiers par défaut. Pour insérer des lignes dans une table partitionnée, vous devez d'abord créer la table partitionnée, puis utiliser l'instruction INSERT INTO ... SELECT FROM.
3. Les index, les contraintes et les déclencheurs définis dans la table source ne sont pas transférés dans la nouvelle table. Ils ne peuvent pas non plus être spécifiés dans l'instruction SELECT ... INTO. Si ces objets sont requis, vous pouvez les créer après avoir exécuté l'instruction SELECT ... INTO.
4. La spécification d'une clause ORDER BY ne garantit pas que les lignes sont insérées dans l'ordre spécifié. Lorsqu'une colonne fragmentée est incluse dans la liste de sélection, la propriété de colonne fragmentée n'est pas transférée dans la colonne de la nouvelle table. Si cette propriété est requise dans la nouvelle table, modifiez la définition de la colonne après avoir exécuté l'instruction SELECT ... INTO pour inclure cette propriété.
5. Lorsqu'une colonne calculée est incluse dans la liste de sélection, la colonne correspondante dans la nouvelle table n'est pas une colonne calculée. Les valeurs dans la nouvelle colonne sont les valeurs qui ont été calculées au moment où SELECT ... INTO a été exécuté.

[ sic ]

Obtenir le nombre de lignes de la table

L'exemple suivant peut être utilisé pour rechercher le nombre total de lignes d'une table spécifique dans une base de données si table_name est remplacé par la table que vous souhaitez interroger:

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

Il est également possible d'obtenir le nombre de lignes de toutes les tables en se joignant à la partition de la table basée sur l'index HEAP (index_id = 0) ou le cluster cluster (index_id = 1) des tables en utilisant le script suivant:

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;

Ceci est possible car chaque table est essentiellement une table de partition unique, à moins que des partitions supplémentaires ne lui soient ajoutées. Ce script a également l'avantage de ne pas interférer avec les opérations de lecture / écriture sur les lignes des tables.