bases_de_donnees:sqlserver:divers
Différences
Ci-dessous, les différences entre deux révisions de la page.
Prochaine révision | Révision précédente | ||
bases_de_donnees:sqlserver:divers [2020/06/11 05:15] – créée sgariepy | bases_de_donnees:sqlserver:divers [2022/02/02 00:42] (Version actuelle) – modification externe 127.0.0.1 | ||
---|---|---|---|
Ligne 1: | Ligne 1: | ||
+ | ====== Curseurs ====== | ||
+ | |||
+ | Code de base pour un curseur: | ||
+ | <code sql> | ||
+ | DECLARE @Id int | ||
+ | |||
+ | DECLARE MY_CURSOR CURSOR | ||
+ | LOCAL STATIC READ_ONLY FORWARD_ONLY | ||
+ | FOR | ||
+ | SELECT DISTINCT Id | ||
+ | FROM TableName | ||
+ | |||
+ | OPEN MY_CURSOR | ||
+ | FETCH NEXT FROM MY_CURSOR INTO @Id | ||
+ | WHILE @@FETCH_STATUS = 0 | ||
+ | BEGIN | ||
+ | PRINT @Id | ||
+ | FETCH NEXT FROM MY_CURSOR INTO @Id | ||
+ | END | ||
+ | CLOSE MY_CURSOR | ||
+ | DEALLOCATE MY_CURSOR | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
====== Classements ====== | ====== Classements ====== | ||
Un classement, ou // | Un classement, ou // | ||
- | ====== Convertion de classement | + | ===== Convertion de classement ===== |
On peut convertir un classement, si par exemple, on fait une requête qui implique plusieurs bases de données qui n'ont pas le même classement. | On peut convertir un classement, si par exemple, on fait une requête qui implique plusieurs bases de données qui n'ont pas le même classement. | ||
Ligne 10: | Ligne 36: | ||
CAST(nomColonne AS VARCHAR(50)) COLLATE French_CI_AS ) | CAST(nomColonne AS VARCHAR(50)) COLLATE French_CI_AS ) | ||
</ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ====== Opérations de base ====== | ||
+ | |||
+ | ===== Insertion ===== | ||
+ | |||
+ | Une fois un enregistrement inséré, on peut récupérer le dernier Id inséré avec '' | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== PIVOT et UNPIVOT ===== | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | [[https:// | ||
+ | |||
+ | ===== Limiter le nombre d' | ||
+ | On peut utiliser le mot '' | ||
+ | <code tsql> | ||
+ | SELECT TOP 10 * FROM [Etudiant]; | ||
+ | </ | ||
+ | |||
+ | ===== Declarer des variables scalaires ===== | ||
+ | <code tsql> | ||
+ | DECLARE @p0 nvarchar(30) | ||
+ | SET @p0 = ' | ||
+ | </ | ||
+ | |||
+ | ====== Séquence ====== | ||
+ | |||
+ | <code tsql> | ||
+ | WITH Units ( nbr ) AS ( | ||
+ | SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION | ||
+ | SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION | ||
+ | SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION | ||
+ | SELECT 9 ) | ||
+ | SELECT u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 AS " | ||
+ | FROM Units u1, Units u2, Units u3 | ||
+ | WHERE u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 <= 500 | ||
+ | ORDER BY Number ASC; | ||
+ | </ | ||
+ | |||
+ | ====== DDL ====== | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Contraintes ===== | ||
+ | |||
+ | ==== Foreign key ==== | ||
+ | |||
+ | <code tsql> | ||
+ | CREATE TABLE Employes | ||
+ | ( | ||
+ | Id int NOT NULL PRIMARY KEY, | ||
+ | Nom varchar(100) NOT NULL, | ||
+ | PersonneId int FOREIGN KEY REFERENCES Personnes(Id) | ||
+ | ) | ||
+ | </ | ||
+ | |||
+ | <code tsql> | ||
+ | CREATE TABLE Employes | ||
+ | ( | ||
+ | Id int NOT NULL, | ||
+ | int NOT NULL, | ||
+ | PersonneId int, | ||
+ | PRIMARY KEY (Id), | ||
+ | CONSTRAINT fk_PerEmployes FOREIGN KEY (PersonneId) REFERENCES Persons(Id) | ||
+ | ) | ||
+ | </ | ||
+ | |||
+ | <code tsql> | ||
+ | ALTER TABLE dbo.EMPLOYE | ||
+ | ADD CONSTRAINT FK_EMPLOYE_EMPLOYE_PERSONNE FOREIGN KEY (EMPLOYE_PK) REFERENCES dbo.EMPLOYE_EMPLOYE_PERSONNE(EMPLOYE_PK) | ||
+ | </ | ||
+ | |||
+ | ==== UNIQUE ==== | ||
+ | |||
+ | <code tsql> | ||
+ | CREATE TABLE EMPLOYES ( | ||
+ | Id int NOT NULL, | ||
+ | Code varchar(15) NOT NULL, | ||
+ | PersonneId int NOT NULL, | ||
+ | CONSTRAINT uc_PersonID UNIQUE (Code, PersonneId) | ||
+ | ) | ||
+ | </ | ||
+ | |||
+ | |||
+ | <code tsql> | ||
+ | ALTER TABLE EMPLOYES ADD CONSTRAINT uk_employe_personne UNIQUE (EMPLOYE_PK, | ||
+ | </ | ||
+ | |||
+ | |||
+ | ====== Variables ====== | ||
+ | |||
+ | ===== Assigner une variable à partir d'un SELECT ===== | ||
+ | |||
+ | <code sql> | ||
+ | DECLARE @variable INT; | ||
+ | |||
+ | SET @variable = ( | ||
+ | SELECT COUNT(*) | ||
+ | FROM Employes | ||
+ | ) | ||
+ | </ | ||
+ | |||
+ | ===== Assigner plusieurs variables à partir d'un SELECT ===== | ||
+ | |||
+ | <code sql> | ||
+ | DECLARE | ||
+ | @variable1 INT, | ||
+ | @variable2 VARCHAR(100); | ||
+ | |||
+ | SELECT @variable1 = CodeEmploye, | ||
+ | FROM Employes | ||
+ | WHERE IdEmploye = 1 | ||
+ | </ | ||
+ | |||
+ | ====== Fonctions ====== | ||
+ | |||
+ | |||
+ | ===== DENSE_RANK() ===== | ||
+ | |||
+ | Le '' | ||
+ | |||
+ | Par exemple, si on veut numéroter par 1 tous les Alain, par 2 tous les Albert, par 3 tous les Annie, etc. | ||
+ | |||
+ | |||
+ | | ||
+ | <code sql> | ||
+ | SELECT | ||
+ | DENSE_RANK() OVER (ORDER BY Nom DESC) AS DenseRank, | ||
+ | Nom, | ||
+ | AutreValeur | ||
+ | FROM Personnes | ||
+ | </ | ||
+ | |||
+ | ^ DenseRank | ||
+ | | 1 | Alain | 990 | | ||
+ | | 1 | Alain | 400 | | ||
+ | | 2 | Albert | ||
+ | | 3 | Annie | 500 | | ||
+ | | 3 | Annie | 100 | | ||
+ | | 3 | Annie | 75 | | ||
+ | |||
+ | |||
+ | ====== Transactions ====== | ||
+ | |||
+ | <code sql> | ||
+ | SET xact_abort ON | ||
+ | |||
+ | BEGIN TRAN T1; | ||
+ | -- code sql.... | ||
+ | COMMIT TRAN T1; | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | BEGIN TRY | ||
+ | BEGIN TRANSACTION | ||
+ | |||
+ | INSERT INTO myTable (myColumns ...) VALUES (myValues ...); | ||
+ | INSERT INTO myTable (myColumns ...) VALUES (myValues ...); | ||
+ | INSERT INTO myTable (myColumns ...) VALUES (myValues ...); | ||
+ | |||
+ | COMMIT TRAN -- Transaction Success! | ||
+ | END TRY | ||
+ | BEGIN CATCH | ||
+ | IF @@TRANCOUNT > 0 | ||
+ | ROLLBACK TRAN --RollBack in case of Error | ||
+ | |||
+ | -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception | ||
+ | RAISERROR(ERROR_MESSAGE(), | ||
+ | END CATCH | ||
+ | </ | ||
+ | |||
+ | Source: [[http:// | ||
+ | |||
+ | ====== Ressources ====== | ||
+ | * [[http:// | ||
bases_de_donnees/sqlserver/divers.1591845302.txt.gz · Dernière modification : 2022/02/02 00:43 (modification externe)