Overblog Suivre ce blog
Administration Créer mon blog

Présentation

  • : Romagny13 - Du .NET,du pur .NET
  • Romagny13 - Du .NET,du pur .NET
  • : Cycle de progression Faire (quelque chose qui marche) -> comprendre ce que l’on fait/comment cela marche -> pousser plus loin les notions
  • Contact

Recherche

Articles Récents

5 février 2014 3 05 /02 /février /2014 17:30

 

PL/SQL

T-SQL

Plage/exemple

C#

tinyint

tinyint

0 à 255

byte (System.Byte)

smallint

smallint

-32 768 à 32 767

short(System.Int16)

 

 

 

 

int

int

-2 147 483 648

à 2 147 483 647

int(System.Int32)

bigint

bigint

-9 223 372 036 854 775 808 à 9 223 372 036 854 775 807

long(System.Int64)

numeric(5,2)        

numeric(5,2)        

Exemple : 40,77

float(System.Single)

ou double/voir string

decimal(5,2)        

decimal(5,2)        

Exemple : 40,77

float(System.Single)

ou double/voir string

float,real

float

 

double (System.Double)

bool

bit

                     

bool(System.Boolean)

char(50)            

char(50)            

de 1 à 4000 caractères

string(System.String)

varchar(100)        

varchar(100)        

de 1 à 8000 caractères

string(System.String)         

national char(1000)

nchar(1000)

de 1 à 8000 caractères

string(System.String)

text,longtext

text

 

string(System.String)

date

date

Exemple : 2003-10-30

DateTime(System.DateTime)

time

Time(7)

Exemple : 13:00:00

DateTime(System.DateTime)

datetime

datetime

Exemple : 2003-10-30 13:00:00

DateTime(System.DateTime)

timestamp

timestamp

 

TimeSpan(System.TimeSpan)

varbinary(100), blob    

varbinary(100)     

de 1 à 8000

byte[](System.Byte[])

longblob

image

 

byte[](System.Byte[])

           

  • types numériques, ne pas mettre d’espace      
  • Pour les types numeric, decimal, float mettre une virgule (et pas un point)        
  • decimal [ (p[ ,s] )] et numeric[ (p[ ,s]) -10^38 +1 et 10^38 - 1.

p (précision)  Nombre maximal de chiffres décimaux à gauche et à droite de la virgule.Comprise entre 1 et 38(par défaut 18)

s (échelle) Nombre de chiffres décimaux à droite de la virgule. Ce nombre est soustrait de p. Comprise entre 0 et p (par défaut 0)

  •  Insérer une image dans une colonne en T-SQL :

INSERT INTO NOMTABLE(NOMCOLONNE)

   SELECT 'avatarcsharp.png' FROM OPENROWSET(BULK N'C:\Users\romagny\Pictures\avatarcsharp.png', SINGLE_BLOB) AS Document

GO

Exemple de table en T-SQL (SQL Server 2012)

create table DEMOTABLE (

   DEMOOCTET            tinyint              null,

   DEMOSMALLINT         smallint             null,

   DEMOSEQUENCIEL       int                  null,

   DEMOBIGINT           bigint               null,

   DEMONUMERIC          numeric(5,2)         null,

   DEMODECIMAL          decimal(5,2)         null,

   DEMOFLOAT            float                null,

   DEMOBOOL             bit                  null,

   DEMOCHAR             char(50)             null,

   DEMOVARCHAR          varchar(100)         null,

   DEMOTEXT             text                 null,

   DEMONATIONALCHAR     nchar(1000)         null,

   DEMODATE             date                null,

   DEMOTIME             time(7)              null,

   DEMODATETIME         datetime             null,

   DEMOHEURESYSTEME     timestamp            null,

   DEMOVBIN             varbinary(1000)      null,

   DEMOIMAGE            image                null

)

Repost 0
Published by Romagny13 - dans SQL
commenter cet article
2 février 2014 7 02 /02 /février /2014 01:33

Récupérer les infos de base -SQL ServerCe – ce qui marche

 

 On peut facilement récupérer la liste des tables,des colonnes,pour les clés étrangères c’est un peu plus compliqué .  

 J’utilise les mêmes vues qu’avec Sql Server mais toutes ne sont pas disponibles .  

 http://msdn.microsoft.com/fr-fr/library/ms186778.aspx  

-- liste les tables

select TABLE_NAME,TABLE_TYPE from INFORMATION_SCHEMA.TABLES

 

-- informations de colonne

select TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,COLUMN_HASDEFAULT,

COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,AUTOINC_SEED

from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME='Client'

 

 

select CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME

from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

 

select CONSTRAINT_TABLE_NAME,CONSTRAINT_NAME,

UNIQUE_CONSTRAINT_TABLE_NAME,UNIQUE_CONSTRAINT_NAME

from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

 

-- CONSTRAINT_TYPE renvoie "FOREIGN KEY" ou "PRIMARY KEY"

select CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

 

Clé étrangères  

select CONSTRAINT_TABLE_NAME as TABLE_FILLE,

CONSTRAINT_NAME as FK_NAME

,UNIQUE_CONSTRAINT_TABLE_NAME as TABLE_MERE,UNIQUE_CONSTRAINT_NAME as PK_NAME

from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

 

select COLUMN_NAME as CLE_ETRANGERE

from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Where CONSTRAINT_NAME ='FK_Client_Category' ;

 

 

select COLUMN_NAME as CLE_TABLE_MERE from INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Where CONSTRAINT_NAME ='PK_Category'

  

Et voilà on récupère toutes les informations de clé étrangères en une seule requête  

select a.CONSTRAINT_TABLE_NAME as TABLE_FILLE,

a.CONSTRAINT_NAME as FK_NAME

,a.UNIQUE_CONSTRAINT_TABLE_NAME as TABLE_MERE,a.UNIQUE_CONSTRAINT_NAME as PK_NAME,b.COLUMN_NAME as CLE_ETRANGERE,

b.CONSTRAINT_NAME,c.COLUMN_NAME as CLE_TABLE_MERE

from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as a

,INFORMATION_SCHEMA.KEY_COLUMN_USAGE as b,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE as c

where b.CONSTRAINT_NAME=a.CONSTRAINT_NAME

and c.CONSTRAINT_NAME=a.UNIQUE_CONSTRAINT_NAME          

 

Autrement il est possible de passer par DbConnection .GetSchema()

 

(Très efficace pour récupérer les informations des bases Microsoft Acces , cela liste même les vues avec le corps .Beaucoup moins avec SqlServerCe qui reste assez compliqué à utiliser )

Repost 0
Published by Romagny13 - dans SQL
commenter cet article
2 février 2014 7 02 /02 /février /2014 00:56

1-Création de tables

 

CREATE TABLE [Products]

(

[ProductId] int NOT NULL IDENTITY (1,1),

[CategoryId] int NOT NULL,

[Title] char(100) NOT NULL,

[NumberOfPages] int NOT NULL DEFAULT 100

);

 

Identity(valeur de départ,incrément)

Types de données

NULL ou NOT NULL : accepte ou non la valeur nulle

DEFAULT : valeur attribuée par défaut

 

2 – Mise à jour de tables ALTER TABLE

A-  Suppresion de colonne

 

ALTER TABLE [Products]

DROP COLUMN [NumberOfPages]

 

B-Ajout de colonne

 

ALTER TABLE [Products]

ADD COLUMN [NumberOfPages] int NOT NULL DEFAULT 100

 

 

C – Clés primaires et étrangères

 

ALTER TABLE [Products]

ADD CONSTRAINT PK_Products PRIMARY KEY ([ProductId]);

 

ALTER TABLE [Products]

ADD CONSTRAINT FK_Products_Categories FOREIGN KEY ([CategoryId])

REFERENCES [Categories]([CategoryId]);

 

 

Astuce possibilité de déclarer la clé primaire lors de la création de la table

 

[ProductId] int NOT NULL IDENTITY (1,1) PRIMARY KEY,

 

De même pour la clé étrangère

[CategoryId] int NOT NULL REFERENCES [Categories]([CategoryId]),

 

3 – Suppresion de table

 

DROP TABLE[Products];

 

Astuce : renommer une table faire appel à la procédure stockée sp_rename

Repost 0
Published by Romagny13 - dans SQL
commenter cet article
2 février 2014 7 02 /02 /février /2014 00:56

4 – Procédures stockées /Vues (Sql Server)

A-  SELECT

CREATE PROCEDURE SELECTContacts

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[ContactAge],[CountryId]

FROM [Contact]

GO

 

CREATE PROCEDURE SELECTContact(@ContactId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[ContactAge],[CountryId]

FROM [Contact]

 WHERE [ContactId]=@ContactId

GO

 

CREATE PROCEDURESELECTContactsOfCountry(@CountryId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[ContactAge],[CountryId]

FROM [Contact] WHERE [CountryId]=@CountryId

GO

 

B – INSERT / UPDATE / DELETE

CREATE PROCEDURE INSERTContact(@ContactName char(100),@ContactFirstName char(100),@ContactAge int,@CountryId int)

AS

INSERT INTO [Contact]([ContactName] ,[ContactFirstName] ,[ContactAge] ,[CountryId] )

VALUES(@ContactName,@ContactFirstName,@ContactAge,@CountryId)

SELECT SCOPE_IDENTITY();

GO

CREATE PROCEDURE UPDATEContact(@ContactId int,@ContactName char(100),@ContactFirstName char(100),@ContactAge int,@CountryId int)

AS

UPDATE [Contact] SET[ContactName]=@ContactName,[ContactFirstName]=@ContactFirstName,[ContactAge]=@ContactAge,[CountryId]=@CountryId

WHERE [ContactId]=@ContactId

GO

CREATE PROCEDURE DELETEContact(@ContactId int)

AS

DELETE FROM [Contact]

WHERE [ContactId]=@ContactId

GO

 

C - mêmes exemples pour MS ACCESS

CREATE PROCEDURE SELECTContacts

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[CountryId]

FROM [Contact]

GO

CREATE PROCEDURESELECTContactsOfCountry(parCountryId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[CountryId]

FROM [Contact] WHERE [CountryId]=parCountryId

GO

CREATE PROCEDURE INSERTContact(parContactName VarChar(100),parContactFirstName VarChar(100),parCountryId int)

AS

INSERT INTO [Contact]([ContactName] ,[ContactFirstName] ,[CountryId] )

VALUES(parContactName,parContactFirstName,parCountryId)

GO

CREATE PROCEDURE SELECTContact(parContactId int)

AS

SELECT [ContactId],[ContactName],[ContactFirstName],[CountryId]

FROM [Contact]

 WHERE [ContactId]=parContactId

GO

CREATE PROCEDURE UPDATEContact(parContactId int,parContactName VarChar(100),parContactFirstName VarChar(100),parCountryId int)

AS

UPDATE [Contact] SET[ContactName]=parContactName,[ContactFirstName]=parContactFirstName,[CountryId]=parCountryId

WHERE [ContactId]=parContactId

GO

CREATE PROCEDURE DELETEContact(parContactId int)

AS

DELETE FROM [Contact]

WHERE [ContactId]=parContactId

GO

D – EXECUTE

EXECUTE INSERTCountry 'France'

EXECUTE INSERTContact 'Dupond','paul',20,1

EXECUTEINSERTContact'Durand','luc',40,1

 

EXECUTE SELECTCountrys

 

 

Astuces :

-    en transact-sql les vues sont précédées de v_ et les procédures stockées de sp_

 

E- Variable

CREATE PROCEDURE sp_contactcount

AS

DECLARE @result Int

SELECT @result=count(*)

FROM Contact

RETURN @result

GO

 

5 – Astuce /ordre des requêtes

 

Pour éviter les conflits avec les contraintes lorsque l’on doit ajouter des données en même temps que l’on crée des tables

 

a-  création des tables

b-  ajout des données

c-  ajout des contraintes(clé primaires,secondaires)

 

exemples : ajout de données sous sql server, le problème ici étant la clé auto incrémentée

SET IDENTITY_INSERT [Country] ON INSERT INTO [Country]([CountryId],[CountryName])

VALUES(1,'France');

 

 

Sous access une clé auto incrément n’empèche pas de définir la valeur voulue

INSERT INTO [Country]([CountryId],[CountryName])

VALUES(1,'France') ;                                                                           

 

 

6 – TRANSACTIONS –

 

A-  BEGIN / COMMIT /ROLLBACK

 

Exemple : on supprime tous les contacts mais rollback annule cette suprression

 

BEGIN TRANSACTION

DELETE FROM [Contact];

ROLLBACK;

COMMIT TRANSACTION

 

Attention à la ponctuation , ne pas mettre de virgules

 

Astuce : pour garder l’intégrité de la base

B - nommer la transaction/@@ERROR <>0

 

BEGIN TRANSACTION T

DELETE FROM [Contact]

if @@error <>0 ROLLBACK

COMMIT TRANSACTION T

 

 

Autre exemple

BEGIN TRANSACTION T

INSERT INTO [Country]([CountryName])VALUES('Suisse')

IF @@ERROR <>0 ROLLBACK

INSERT INTO [Country]([CountryName])VALUES('Bulgarie')

IF @@ERROR <>0 ROLLBACK

COMMIT TRANSACTION T

 

 

B-  Point de sauvegarde

 

SAVE TRANSACTION MySave

ROLLBACK TRANSACTION MySave

 

Exemple

 

BEGIN TRANSACTION T

INSERT INTO [Country]([CountryName])VALUES('Belgique')

SAVE TRANSACTION MySave

INSERT INTO [Country]([CountryName])VALUES('Espagne')

IF @@ERROR <>0 ROLLBACK TRANSACTION MySave

COMMIT TRANSACTION T

Repost 0
Published by Romagny13 - dans SQL
commenter cet article
2 février 2014 7 02 /02 /février /2014 00:55

1 – Insérer des données

 

A-INSERT - Insérer des données : respecter l’ordre des colonnes pour indiquer les valeurs ajoutées, ne pas donner de valeurs pou les champs auto incrémentés,et.

 

INSERT INTO[Products]([ProductId],[CategoryId],[Title],[NumberOfPages])

VALUES(1,2,'Le rouge et le noir',400);

 

B- INSERT SELECT - Insérer des données extraites

 

INSERT INTO[Products]([ProductId],[CategoryId],[Title],[NumberOfPages])

SELECT [ArticleId],[CategoryId],[Title],[NumberOfPages]

FROM [Articles]

 

C– INSERT INTO -  Copier une table

 

Exemple crée la table Contacts et y copie l’ensemble des colonnes et données à partir de la table Amis

 

SELECT *

INTO [Contacts]

FROM [Amis] ;

 

 

2 – UPDATE/modification de données

 

UPDATE [Contacts]

SET [Prenom] = 'paul',[Nom] = 'Dupond'

WHERE ID = 25

 

3 – Suppression de données

 

A – suppression de rangées spécifiques

DELETE FROM [Contacts]

WHERE ID=25

 

b- supression de toutes les rangées d’une table

DELETE FROM [Contacts]

Repost 0
Published by Romagny13 - dans SQL
commenter cet article
2 février 2014 7 02 /02 /février /2014 00:54

Ordre des clauses

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

 

1-      EXTRAIRE

A – une colonne

SELECT [Nom]

FROM [Amis];

 

b- Plusieurs colonnes (les colonnes apparaissent dans le même ordre que dans la requête)

SELECT [ID],[Prenom],[Nom]

FROM [Amis];

 

c – toutes les colonnes

SELECT *

FROM [Amis];

 

Astuces :

-    il est préférable d’énumérer toutes les colonnes plutôt que d’utiliser *

-    Pour un accès plus précis aux tables [NomBase].[NomTable] et pour les colonnes [NomTable].[NomColonne]

-    Le SQL  est insensible à la casse(SELECT ou select c’est idem) par contre il n’en est pas de même pour les noms des tables,colonnes,…

2-  TRIER

A-  Sur une colonne

SELECT *

FROM [Amis]

ORDER BY [Nom]

 

B-  Sur plusieurs colonnes

SELECT *

FROM [Amis]

ORDER BY [Nom],[Prenom]

 

       c- Spécifier ordre de tri5(DESC de z à a,ASC ordre de tri par défaut donc inutile à spécifier)

SELECT *

FROM [Amis]

ORDER BY [Nom] DESC

 

3-  FILTRER

 

A-  Filtrage sur une condition

 

Opérateurs  de la clause where :

<> différent de

!= différent de

<=

>=

!> pas supérieur à

!< pas inférieur à

BETWEEN de l’une à l’autre valeurs spécifiées

IS NULL est de valeur nulle

 

 

SELECT [ProductId],[CategoryId],[Title],[NumberOfPages]

FROM [Products]

WHERE[NumberOfPages]> 100

 

SELECT [ProductId],[CategoryId],[Title],[NumberOfPages]

FROM [Products]

WHERE [NumberOfPages] BETWEEN 20 AND 200

 

SELECT [ProductId],[CategoryId],[Title],[NumberOfPages]

FROM [Products]

WHERE [Title]='La bonne cuisine'

 

SELECT *

FROM [Products]

WHERE [Title] IS NULL

 

B-  Filtrer sur plusieurs conditions Opérateur AND et OR

 

SELECT *

FROM [Products]

WHERE[NumberOfPages]<100 AND [Title]='La bonne cuisine'

SELECT *

FROM [Products]

WHERE[NumberOfPages]<100 OR [NumberOfPages]>300

 

Astuce : utiliser des parenthèses si plusieurs conditions en cas de probème d’évaluation

c- Opérateur IN

(exemple sélection de tous les produits de catégorie 2 et 5)

SELECT *

FROM [Products]

WHERE [CategoryId] IN (2,5);

 

Attention si les conditions sont des textes, les placer entre ‘’

exemple WHERE [Category] IN (‘Littérature’,’jeunesse’);

 

C-  Opérateur NOT (utile pour les clauses complexes)

(exemple sélectionne tous les produits sauf ceux dont la category est 5)

SELECT *

FROM [Products]

WHERE NOT[CategoryId]=5

 

D-  LIKE et Caractères de substitution

 

_ remplace un caractère

% Remplace plusieurs carcatères

[] les crochets servent à psécifier pour un emplcement donné un ensemble de caractères possibles

^ caractère d’exclusion

 

Exemple : sélectionne tous les produits dont le titre commence par un L

SELECT *

FROM [Products]

WHERE [Title] LIKE 'L%'

 

Exemple sélectionne tous les produits commençant par C ou L

SELECT *

FROM [Products]

WHERE [Title] LIKE '[CL]%'

 

4-  CHAMPS CALCULES

A-  Concaténation

Exemple on place les titres entre parenthèses

SELECT '(' + [Title] + ')'

FROM [Products];

 

Astuce : il existe des fonctions comme RTRIM()

 

B-  Alias

Exemple on donne un nom à la colonne créée par concaténation et on reutilise cet alias(ici pour trier)

 

SELECT '(' + [Title] + ')' AS My_Title

FROM [Products]

ORDER BY My_Title;

 

C – Calculs arithmétiques

 

Exemple pour chaque article on calcule le prix par la quantité

SELECT [SalesDetailId],[Amount]*[Quantity] As Result

FROM [SalesDetails]

 

5-  LES FONCTIONS

 

Fonctions de texte ,de date et heure,numériques

http://msdn.microsoft.com/fr-fr/library/ms181984.aspx

 

Fonctions d’agregation

AVG(),COUNT(),MAX(),MIN(),SUM()

 

SELECT COUNT(*) As Result

FROM [Categories];

 

Astuce ALL par défaut et DISTINCT

 

6-  GROUPER

 

affiche le nombre de produits par categorie

SELECT COUNT(*) As Result

FROM [Products]

GROUP BY CategoryId

 

  + HAVING : filtrer

SELECT COUNT(*) AS Result

FROM [Products]

GROUP BY CategoryId

HAVING COUNT(*)>1

 

7-  SOUS REQUETES

// TODO

 

    8 – JOINTURES

Ici on affiche chaque roduit avec le nom de la catégorie au lieu de la clé

 

SELECT[Products].[ProductId],[Products].[Title],[Products].[NumberOfPages],[Categories].[Name]

FROM [Products],[Categories]

WHERE [Products].[CategoryId]=[Categories].[CategoryId]

 

Astuce si plusieurs jointures, utiliser AND

 

Jointure interne >>

SELECT[Products].[ProductId],[Products].[Title],[Products].[NumberOfPages],[Categories].[Name]

FROM [Products] INNER JOIN [Categories]

ON [Products].[CategoryId]=[Categories].[CategoryId]

 

9 – UNION et UNION ALL

SELECT [CustomerId],[CountryId],[FirstName],[LastName],[CompanyName],[Address]

FROM [Customers]

WHERE [CountryId]=3

UNION

SELECT [CustomerId],[CountryId],[FirstName],[LastName],[CompanyName],[Address]

FROM [Customers]

WHERE [CountryId]=4

 

Astuce : union all n’élimine pas les doublons

Repost 0
Published by Romagny13 - dans SQL
commenter cet article