Formation Complet Langage SQL Server - Cours-Gratuit

UNIVERSITE PARIS XII – DESS MASERATI

INITIATION AUX BASE DE DONNEES RELATIONNELLES

LANGAGE SQL

Année 2004-2005

K. Tran-dai & O.Bretel

Avertissement :les informations présentes dans ce support de cours ne sont pas exhaustives. Pour une documentation complète, il est recommandé de consulter le manuel utilisateur du moteur MYSQL ().

Introduction : Le langage SQL

Le langage SQL (Structured Query Language) peut être considéré comme le langage d’accès normalisé aux bases de données. Il est aujourd’hui supporté par la plupart des produits commerciaux que ce soit par les systèmes de gestion de bases de données micro tel que Access ou par les produits plus professionnels tels que Oracle ou Sybase. Il a fait l’objet de plusieurs normes ANSI/ISO dont la plus répandue aujourd’hui est la norme SQL2 qui a été définie en 1992. Nous décrivons ici les principaux aspects de cette norme.

Le succès du langage SQL est du essentiellement à sa simplicité et au fait qu’il s’appuie sur le schéma conceptuel pour énoncer des requêtes en laissant le SGBD responsable de la stratégie d’exécution. Le langage SQL propose un langage de requêtes ensembliste et assertionnel. Néanmoins, le langage SQL ne possède pas la puissance d’un langage de programmation : entrées/sorties, instructions conditionnelles, boucles et affectations. Pour certains traitements il est donc nécessaire de coupler le langage SQL avec un langage de programmation complet au sens Turing du terme.

Le langage SQL comporte :

une partie sur la définition des données :

le langage de définition des données (LDD) qui permet de créer des tables, les modifier, définir des relations, des vues externes et des contraintes d’intégrité…

une partie sur les requêtes :

le langage de manipulation des données (LMD) qui permet d’interroger une base de données sous forme déclarative sans se préoccuper de l’organisation physique des données…

une partie sur le contrôle des données : le langage de contrôle des données (LCD) qui permet de contrôler la sécurité et les accès aux données, droits d’accès, mode d’accès…

Une instruction SQL se définie comme un ensemble de mot clé du langage terminé par un « ; ». Le « ; » termine une instruction. La majorité des outils d’exécution SQL sont capable d’exécuter plusieurs commandes SQL séparées par un « ; » à la suite (notion de script SQL).

1. Le Langage de définition des données LDD :

Ces instructions SQL permettent la création d’une base de données, de tables, d’index, d’insérer et modifier les données.

1.1Création d’une base de données :

Instruction SQL :

CREATE DATABASE [nom base] ;

Instruction SQL sur moteur MYSQL :

CREATE DATABASE (IF NOT EXISTS) [nom base]

DEFAULT CHARACTER SET [nom jeu de caractères] DEFAULT COLLATE [type decollation] ;

L’argument facultatif « IF NOT EXISTS » permet de créer une base seulement si celle-ci n’existe pas. (évite le message d’erreurs en cas d’existence d’une base du même nom).

Le CHARACTER SET : défini le jeu de caractères à utiliser sur cette base de données. Le COLLATE : défini la méthode de comparaison entre jeu de caractères (permet de jouer sur la sensibilité à la casse par exemple).

1.2Supprimer une base de données :

Instruction SQL :

DROP DATABASE [nom base] ;

Instruction SQL sur moteur MYSQL :

DROP DATABASE (IF EXISTS) [nom base] ;

L’argument facultatif “IF EXISTS” permet d’éviter le message d’erreur si la base de données n’existe pas.

NB : il n’existe pas d’instruction SQL qui permette de copier une base de données ou la renommer.

1.3 les types de données

Une base de données peut contenir différents types de données. Les types communs les plus courant entre les différents moteurs de base de données sont les suivants :

Types de données courants :

INTEGER[M] : nombre entier

VARCHAR[M] : chaîne de caractères

TEXT : chaîne de caractères longue

DECIMAL[M,D] : nombre décimal

DOUBLE[M] : nombre de la plus grande précision

DATE : date formatée

TIME : heure formatée

Le format de ces types dépend du moteur de base de données.

Types de données MYSQL (les plus courant) :

TINYINT [(M)] 8 bit entier

SMALLINT [(M)] 16 bit entier

INT [(M)] 32 bit entier

BIGINT [(M)] 64 bit entier

FLOAT [(M,D)] 32 bit réelle

DOUBLE [(M,D)] 64 bit réelle

DECIMAL [(M,D)] réelle, format fixe

DATE date, format aaaa-mm-jj

TIME heure, format hh:mm:ss

DATETIME date et heure, format aaaa-mm-jj hh:mm:ss

CHAR(L) texte max. 255 octets, longueur fixe

VARCHAR(L) texte max. 255 octets, longueur variable

TEXT texte max 2^16-1 octets

MEDIUMTEXT texte max 2^24-1 octets

LONGTEXT texte max 2^32-1 octets

ENUM(<valeur1>, <valeur2>, …) Une valeur parmi les valeurs de la liste

SET(<valeur1>, <valeur2>, …) Zéro ou plusieurs valeurs parmi les valeurs de la liste

SET et ENUM : les valeurs sont représentées comme chaînes de caractères, mais stockées comme entiers. Les listes sont limitées à max 64 membres

M … taille d’affichage – total

D … taille d’affichage – chiffre après virgule

L … longueur max de la chaîne de caractère en octets

1.4Création de tables

Instruction SQL :

CREATE TABLE [Nom Table] ([nom champ 1] [type champ 1] [Options],…..)

Les options que l’on peut définir pour un champ sont les suivantes :

PRIMARY KEY

Clef primaire, valeurs doubles et valeurs NULL interdites.

Si plusieurs colonnes font partie de la clef primaire, utiliser la propriété

PRIMARY KEY([champ 1],[champ 2], …) de la table

NOT NULL

Valeur NULL interdite. Utilisation fortement recommandée

UNIQUE

Valeurs doubles interdites (valeur NULL permise)

DEFAULT <valeur>

Spécification de la valeur par défaut sur insertion de ligne

AUTO_INCREMENT

La valeur est incrémentée par défaut (uniquement pour les nombres)

Instruction sur le moteur MYSQL :

CREATE (TEMPORARY) TABLE (IF NOT EXISTS) [nom table]

(

[nom champ1] [type champ1] [options champ1], … … … …, [nom champN] [type champN] [options champN],

(DEFINITION DES CLES ET INDEX)

)

(OPTIONS DE LA TABLE)

Exemple d’options de la table :

TYPE = type de table (HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM)

AUTO_INCREMENT = valeur de depart d’un champ autoincrement (1 par défaut)

COMMENT = ‘description ou commentaire associé à la table’

MAX_ROWS = valeur nombre maximum de lignes pour la table

MIN_ROWS = valeur nombre minimum de lignes pour la table

ROW_FORMAT = format des lignes dans la table ( DEFAULT | DYNAMIC | FIXED |

COMPRESSED )

INSERT_METHOD = méthode d’insertion de lignes ( NO | FIRST | LAST ) etc…

Les arguments facultatifs :

- TEMPORARY : Ce argument crée une table dite temporaire qui sera alors automatiquement effacée lorsque la connexion l’ayant créée se terminera.

- IF NOT EXISTS : cf. chap. 1.1

- DEFINITION CLES ET INDEX : permet de définir à la création les clés primaires et les index sur la table :

o PRIMARY KEY ([champ1], [champ2], …) o KEY [nom index] ([champ1], [champ2], …)

Exemple :

CREATE TABLE CLIENT (

Client_id INT(11) NOT NULL AUTO_INCREMENT,

Client_name VARCHAR(30) NOT NULL,

Client_firstname VARCHAR(30) NOT NULL,

Client_birthdate DATE NOT NULL DEFAULT '1900-01-01',

PRIMARY KEY (Client_id),

KEY idx_23 (Client_name,Client_firstname)

) TYPE=MYISAM AUTO_INCREMENT=1000 COMMENT=’table clients’;

Table générée :

Lors de l’insertion d’une ligne la valeur automatique attribuée au premier client_id sera 1000 (option AUTO_INCREMENT=1000).

Le champ Client_id est défini comme clé primaire (client_id unique) Un index est créé sur les champs Client_name, Client_firstname.

Cet index permet entre autre d’accélérer les recherches sur cette table par nom et prénom de client.

1.5 Supprimer une table

Cette instruction permet de supprimer une table (elle est alors effacée des disques durs des stockage)

Instruction SQL :

DROP TABLE [nom table] ;

Instruction MYSQL :

DROP TABLE (IF EXITS) [nom table1], [nom table2], … ;

Il est possible de supprimer plusieurs tables dans une même instruction. L’instruction facultative « IF EXISTS » permet d’éviter le message d’erreur dans le cas de non existence d’une ou plusieurs tables définies dans l’instruction DROP.

1.6 Modifier une table MYSQL

Les instructions suivantes permettent de modifier une table déjà créée.

1.6.1 Ajouter/Supprimer une colonne

Ajouter une colonne :

ALTER TABLE [nom table] ADD COLUMN [nom col][type col][options col] [POSITION];

[POSITION] : définit la position de la nouvelle colonne par rapport aux existantes. Cette option peut prendre les valeurs suivantes :

. FIRST (en première position)

. AFTER [nom colonne] après la colonne spécifiée en [nom colonne]

Supprimer une colonne/un index/ une clé primaire :

ALTER TABLE [nom table] DROP COLUMN [nom colonne] Ou DROP INDEX [nom index] Ou DROP PRIMARY KEY ;

1.6.2 Renommer une table

ALTER TABLE [nom table] RENAME [nouveau nom table] ;

(Voir manuel MYSQL pour autres instructions possibles)

1.7Ajouter un index à une table MYSQL existante

Les instructions suivantes permettent la création d’index sur les tables.

CREATE (TYPE) INDEX [nom index] ON [nom table] ([nom champ1], [nom champ2]) ;

L’option TYPE permet de préciser si l’index est de type UNIQUE : si oui, la combinaison des valeurs de champs doit être unique. Autrement dit si deux lignes présentent le même contenu pour les colonnes indexées alors le Moteur MYSQL refusera l’insertion de la ligne, celle-ci étant considérée comme un doublon sur l’index.

Il est possible de créer des index sur une partie seulement de la colonne.

Exemple :

CREATE UNIQUE INDEX idx_13 ON CLIENT (client_name(10), client_firstname(10)) ; L’index créé sera stocké comme la concaténation des 10 premiers caractères de la colonne client_name et les 10 premiers caractères de la colonne client_firstname.

Il est fortement recommandé de créer des index sur les colonnes utilisées dans les clauses WHERE (cf chapitre sur les requêtes SELECT) et notamment sur les colonnes de liaisons entre les tables. Le Moteur de base de données utilise alors les index les plus appropriés pour retourner les résultats de façon optimale.

1.8 L’insertion de données : les requêtes INSERT

Une fois les tables créées il faut les remplir ! Les instructions suivantes vous permettent d’insérer des lignes dans une table de différentes façons :

- par chargement de fichier

- par sélection sur autre table

- ligne par ligne

1.8.1 chargement d’un fichier délimité dans une table

Pour pouvoir être « chargée « en table, les données doivent tout de même être ordonnées dans le fichier. 2 caractères sont indispensables pour que le chargement se fasse correctement :

- le séparateur de colonne ( « ; », « , », « | »…)

- l’indicateur de fin de ligne (retour chariot…)

Instruction MYSQL :

LOAD DATA (LOCAL) INFILE ‘’[chemin fichier]’’ (GESTION_DOUBLONS) INTO TABLE [nom table] FIELDS TERMINATED BY ‘[séparateur de colonne]’ LINES TERMINATED BY ‘[indicateur fin de ligne]’;

L’option (GESTION DOUBLONS) permet de préciser comment l’insertion doit gérer la présence de doublons entre la table destination et le fichier source. Les valeurs de l’option sont :

- REPLACE (remplacement de la ligne déjà dans la table)

- IGNORE (pas de mise à jour de la ligne)

L’option (LOCAL) permet de définir si le fichier à charger est situé sur le poste « local » d’ou est lancé l’instruction LOAD, ou si celui-ci se trouve sur le serveur de base de données.

Exemple :

LOAD DATA LOCAL INFILE ‘’c:\\mes données\\donné ‘’

INTO TABLE CLIENTS

FIELDS TERMINATED BY ‘ ;’

LINES TERMINATED BY ‘\n’;

‘\n’ défini le retour chariot.

1.8.2insertion ligne par ligne

Cette commande permet d’insérer un seul enregistrement à la fois.

Instruction MYSQL :

INSERT INTO [nom table] ([col1],[col2], …,[colN]) VALUES([valeur_nombre], ‘[valeur_texte]’ , …, ‘[valeur_date]’)

(ON DUPLICATE KEY UPDATE [colX]=[Expression]);

Si l’on ne précise pas le nom des colonnes après le nom de la table, il est nécessaire de donner autant de valeurs dans la clause “VALUES” qu’il existe de colonnes dans la table. La valeur à donner pour un champ de type AUTOINCREMENT est la valeur vide ‘’.

L’option facultative « ON DUPLICATE KEY UPDATE » permet de préciser quel action le moteur doit effectuer en cas d’existence dans la table de la ligne que l’on veut insérer. Cette option quels sont les colonnes à modifier et quels actions doivent être exécutées pour que le doublon disparaisse.

Exemple :

INSERT INTO TABLE CLIENT (client_name)

VALUES (‘DURANT’);

(instruction possible si les autres champs n’ont pas l’option “NOT NULL”)

INSERT INTO TABLE CLIENT

VALUES (‘’,’DURANT’,’GILLES’,’2004-10-01’);

INSERT INTO TABLE CLIENT (id_client,client_name,client_firstname,client_birthdate)

VALUES (‘’,’DURANT’,’GILLES’,’2004-10-01’)

ON DUPLICATE KEY UPDATE client_name=CONCAT(client_name,id_client);

1.8.3insertion par requite SELECT

Il est aussi possible d’insérer des données dans une table à partir des résultats d’une autre commande SQL. Une requête de type « SELECT » retourne un « tableau » de données qu’il est possible d’insérer dans une autre table en une seule commande.

Instruction MYSQL :

INSERT INTO [nom table] ([col1],[col2], …,[colN]) SELECT [col1],[col2], …,[colN] FROM [nom table];

Exemple :

INSERT INTO CLIENT (Client_name, Client_firstname, Client_birthdate)

SELECT Client_name, Client_firstname, Client_birthdate

FROM OLD_CLIENT ;

Cette instruction insert dans les colonnes Client_name, Client_firstname, Client_birthdate de la table CLIENT toutes les lignes des colonnes Client_name, Client_firstname, Client_birthdate de la table OLD_CLIENT.

1.9 Mise à jour des données, Modification, Suppression de données

Les commandes SQL présentées ci-après permettent de modifier des données déjà existantes dans les tables d’un base de données : ce sont les requêtes « UPDATE »

- Instruction MYSQL pour une seule table :

UPDATE (LOW_PRIORITY) [nom table] SET [colX]=[expression 1], [colY]=[expression 2], …,[colN]=[expression N] (WHERE [filtre sur les données]) (ORDER BY [col1],[col2], …,[colN])

(LIMIT [nombre de lignes à traiter])

L’option « LOW_PRIORITY » permet de retarder automatiquement l’exécution de la mise à jour des données au moment ou il n’y a plus aucune lecture en cours sur la table impactée.

La clause « WHERE [ filtre sur les données] » permet de sélectionner quelles sont les données à mettre à jour sur la table. Sans cette clause, toutes les lignes de la table seront alors modifiées.

La clause « ORDER BY » permet de trier les données dans la table selon les colonnes indiquées (utile dans le cas de requête UPDATE avec restriction LIMIT)

L’option « LIMIT […] » permet de restreindre la mise à jour à une nombre de lignes spécifié par le paramètre [nombre de lignes à traiter).

Exemple :

UPDATE CLIENT

SET Client_name=’DUPUIS’ #action à exécuter sur la colonne Client_name#

WHERE Client_name=’DUPONT’ #sélection des lignes à mettre à jour#

ORDER BY Client_id #spécifie un tri sur la colonne Client_id#

LIMIT 2 #restriction de la mise à jour aux 2 premières lignes#

Cette commande va modifier le nom des 2 premiers Client d’appelant ‘DUPONT’ en transformant leur nom en ‘DUPUIS’.

Dans l’exemple nous trions les données par la colonne Client_id, qui comme il est un champ numérique de type auto_increment va trier les données selon leur ancienneté dans la table. En effet le numéro client_id est automatiquement incrémenté d’une unité supplémentaire à chaque insertion de ligne. Chaque ligne obtient donc un numéro unique qui augmente avec le nombre de lignes dans la table. Ainsi les numéros les plus faibles sont les premières lignes insérées dans cette table.