Cours VB.NETDate de mise à jour : 05/12/2010
XVII-A. Notions sur les bases de données
XVII-A-1. Généralités
XVII-A-2. Tables
XVII-A-3. Exemple
XVII-A-4. Type de colonne
XVII-A-5. Clé primaire
XVII-A-6. Index
XVII-A-7. Relations entre les tables : différents types de relations
XVII-A-7-a. 1 à N (relation un à plusieurs)
XVII-A-7-b. 1 à 1
XVII-A-7-c. N à M
XVII-A-7-d. Relation N à M avec N fixe et petit
XVII-A-8. Contraintes
XVII-A-9. Serveur de fichier, Client serveur
XVII-A-10. Opérations sur les enregistrements
XVII-B. Généralités sur ADO.NET
XVII-B-1. Généralités
XVII-B-2. Les Managed Providers
XVII-B-3. Les Objets ADO.NET
XVII-B-4. Le DataReader
XVII-B-5. Le DataSet
XVII-C. Syntaxe SQL (Généralités)
XVII-C-1. Généralités
XVII-C-2. Les commandes SQL
XVII-C-3. SELECT : Interrogation
XVII-C-4. Tri des enregistrements
XVII-C-5. Statistiques
XVII-C-6. Extraction de données sur plusieurs tables
XVII-C-7. Ajout, suppression, modification d'enregistrement
XVII-C-8. Ajout de table
XVII-A. Notions sur les bases de données
Comment lire et écrire des informations complexes et structurées?
XVII-A-1. Généralités
Pour travailler avec du texte, des octets, des données très simple (sans nécessité d'index, de classement..), on utilise les fichiers séquentiels, aléatoires, binaires .
Mais dès que les informations sont plus structurées, il faut utiliser les bases de données (Data Base en anglais).
Une base de données peut être:
- locale: utilisable sur un ordinateur par un utilisateur.
- répartie, c'est-à-dire que la base est stockée sur des machines distantes et accessibles par réseau.
Plusieurs utilisateurs peuvent accédés à la base simultanément.
Exemple de type de base de données:
Dbase Format très utilisé, qui date maintenant un peu, les fichiers contenant ses bases ont l'extension .dbf
Paradox
FileMaker
FoxPro
Interbase
Access Format très répandu, les fichiers contenant ses bases ont l'extension .mdb
SQLServeur les fichiers contenant ses bases ont l'extension .dbo
SyBase
MySql
Oracle..
Pour pouvoir contrôler les données, l'accès à ces données et les utilisateurs utilisant une base de données, un système de gestion est nécessaire. La gestion de la base de données se fait grâce à un système appelé SGBD (système de gestion de bases de données), si la base de données est relationnelle (Existence de relation entre les tables) on parle de SGBDR (système de gestion de bases de données relationnelles)
Un SGBD est un logiciel qui joue le rôle d'interface entre les utilisateurs et la base de données.
Un SGBD permet de décrire, manipuler et interroger les données d'une 'Base de Données'.
XVII-A-2. Tables
Dans une base de données, il y a des tables:
Une table sert à stocker physiquement des données sous forme d'un tableau comportant des lignes (rows) et des colonnes (columns).
XVII-A-3. Exemple
Une base de données Access nommée Cabinet.mdb contient les patients d'un cabinet, leurs consultations, les ordonnances, les médicaments..
Dans cette base il y a plusieurs tables: une table patient, une table consultation...
Examinons la table patient:
Sur chaque ligne (row), il y a un patient,.
Chaque colonne (column) représente un type de données (première colonne= civilité, seconde=nom, troisième=prénom, quatrième= numéro interne propre à chaque patient. )
L'ancienne terminologie parlait d'enregistrements (lignes) et de champs (colonnes)
Ici la seconde ligne (le 2eme enregistrement, le second row) contient la civilité, le nom, le prénom, le numéro du patient Dupont Josette.
Chaque colonne à un type bien définie: dans notre cas la première colonne contient du texte, ainsi que la seconde, la troisième; la quatrième colonne contient un numérique long par exemple.
Examinons la table consultation:
Sur chaque ligne, il y a une consultation,.
Chaque colonne représente un type de données (première colonne= numéro correspondant au patient, seconde=date, troisième=texte de la consultation, quatrième= Courrier. )
Il n'est pas question pour chaque consultation d'enregistrer de nouveau le nom et le prénom du patient, cela enregistrerait 2 fois la même information puisque le nom et le prénom du patient sont déjà dans la table 'patient'. On va donc, pour éviter les redondances, utiliser un numéro interne: chaque patient a un numéro unique (4éme champ de la table 'nom'); il suffit de noter dans chaque consultation le numéro du patient.
Ensuite, si je consulte le patient Durand Luc, sachant que son numéro interne est '1', il suffit de rechercher dans la table consultation les consultations dont le premier champ est 1: Durand Luc à 2 consultations.
Le nom de la colonne est souvent nommé en utilisant le terme Id (pas ici) , 'IdPatient' par exemple, synonyme de 'numéro patient', cela permet de repérer les champs 'numéro interne'.
XVII-A-4. Type de colonne
Il existe des types de colonne (de champs) alphanumériques
- de longueur fixe (pour le champ 'nom' je prévois 30 caractères par exemple).
- de longueur variable (champ mémo dans la base Dbase par exemple)
Il existe aussi
- des champs numériques,
- des champs dates
- et dans certains base de données des champs booléens, image...
XVII-A-5. Clé primaire
Quand il est nécessaire de différencier chaque enregistrement de manière unique, il faut définir un champ comme clé primaire.
Ce champ doit être unique pour chaque enregistrement (il ne doit pas y avoir de doublons: 2 enregistrements ne peuvent pas avoir la même clé primaire), et la valeur de la clé primaire ne peut pas être égale à null.
Dans notre exemple de la table patient, on ne peut pas utiliser le champ 'nom' comme clé primaire car plusieurs patients peuvent avoir le même nom, il est judicieux de choisir le champ 'numéro interne' comme clé primaire car chaque patient (donc chaque enregistrement) à un numéro interne unique.
Quand on enregistre une nouvelle fiche patient, il faut donc donner un nouveau 'numéro interne' qui n'a jamais été utilisé, en pratique:
Il existe des champs numériques dont la valeur s'incrémente automatiquement d'une unité: Quand on crée un nouvel enregistrement, le champ 'numéro interne' prend automatiquement la plus grande valeur déjà présente dans la base+1.
XVII-A-6. Index
Un index permet d'optimiser les recherches dans une table, de les rendre beaucoup plus rapide.
Expliquons:
Si j'ai une table contenant les noms des médecins utilisateurs et que je veux chercher un nom, comme il y a au maximum 5 à 6 médecins dans un cabinet, pour rechercher un nom, il suffit de lire successivement chaque enregistrement et de voir si c'est celui recherché. C'est suffisamment rapide.
Par contre si je recherche dans la table patient un patient, comme il y a 4000 à 8000 enregistrements, je ne peux pas les lire un à un , c'est trop long, aussi je crée un index: c'est comme l'index d'un livre, le nom me donne directement l'endroit ou se trouve l'enregistrement correspondant.
On peut combiner plusieurs champs pour en faire la base d'un index.
Pour ma table 'patient', je peux créer un index nommé IndexPatient qui sera indexé sur Nom +Prenom.
Il peut y a voir plusieurs index sur une même table.
Les index accélèrent les recherches mais s'il y en a trop, cela alourdit le fonctionnement; on ne peut pas tout indexer!!
XVII-A-7. Relations entre les tables : différents types de relations
On a déjà vu que 2 tables peuvent être liées et avoir un champ commun présent dans les 2 tables.
Sur ce champ commun, il peut exister plusieurs types de relation:
Relation 1 à N
Relation 1 à 1
Relation N à M
Voyons cela en détail:
XVII-A-7-a. 1 à N (relation un à plusieurs)
Dans notre exemple la table 'patient' et la table 'consultation' ont chacune un champ numéro interne. Ce qui permet de lier à l'enregistrement du patient de numéro interne X toutes les consultations pour ce patient (elles ont dans leurs champs 'numéro interne' la valeur X.
Comme pour UN patient il peut y avoir N consultations, on parle de relation 1 à N.
Un enregistrement unique est lié à plusieurs enregistrements de l'autre table par un champ présent dans les 2 tables.
On remarque que le champ 'numéro interne' du coté patient est une clé primaire, pas du coté consultation.
Table 'patients'
Le patient Durand Luc a 2 consultations : le 02/12/2003 et le 05/04/2004 (Le numéro interne de ce patient est 1, mais l'utilisateur final n'a pas à le savoir ni à le gérer: la relation utilisant le numéro interne est transparente pour l'utilisateur final)
Il existe aussi les relations:
XVII-A-7-b. 1 à 1
Un enregistrement unique est lié à un autre enregistrement unique par un champ présent dans les 2 tables.
On peut imaginer dans notre exemple, créer une table Antécédents contenant aussi un champ numéro interne; pour chaque enregistrement de la table patient, il y a un enregistrement unique dans la table Antécédents, de même numéro interne contenant les antécédents du patient.
Enfin existe les relations:
XVII-A-7-c. N à M
Relation plusieurs à plusieurs. Plusieurs enregistrements de la première table peuvent être liés à plusieurs de la seconde table et vice versa.
Exemple:
J'ai une table 'ordonnances' qui peut contenir plusieurs médicaments, et une table 'médicaments' dont les médicaments peuvent être utilisé dans plusieurs ordonnances différentes.
Il faut dans ce cas avoir la table 'ordonnances' avec une clé primaire sur un numéro d'ordonnance (numéro d'ordonnance unique s'incrémentant à chaque nouvelle ordonnance), une table 'médicaments' avec une clé primaire sur le numéro unique du médicament, et créer une troisième table gérant la relation ordonnance-médicament.
Ici le patient de numéro interne 2 (Dupont Josette) a une ordonnance visible dans la table 'Ordonnances'(numéro interne: 2; numéro de l'ordonnance: 1); si on cherche dans la table 'Contenu ordonnance' (Index crée sur le numéro d'ordonnance) on retrouve 2 enregistrements (ayant un numéro d'ordonnance 1), on constate que l'ordonnance contient les médicaments 1 et 2 qui correspondent (table 'médicaments') à de l'amoxicilline et de l'oméprazone.
On remarque qu'une ordonnance peut avoir autant de médicaments que l'on veut.
XVII-A-7-d. Relation N à M avec N fixe et petit
Dernier cas non décrit dans les livres:
J'explique: si chaque ordonnance à au maximum 3 médicaments ( que la sécu serait contente si c'était vrai!!), il est possible de créer une table 'ordonnances' contenant 3 champs médicaments. Dans ce cas on se passe de la 3eme table.
XVII-A-8. Contraintes
Un champ peut avoir certaines contraintes:
- On peut interdire la valeur Null: Cela empêche d'enregistrer un champ vide. On peut aussi donner une valeur par défaut.
- On peut empêcher les doublons.
- On peut exiger l'intégrité référentielle: La valeur d'un champ doit exister dans le champ d'une autre table.(On ne peut pas enregistrer une consultation pour le patient de numéro interne 2000 s'il n'existe pas de fiche patient ayant le numéro 2000)
- On peut exiger des règles de validation pour un champ: interdire les valeurs négatives par exemple.
XVII-A-9. Serveur de fichier, Client serveur
Si plusieurs utilisateurs sont connectés à une base Access à travers un réseau, chaque utilisateur a sur son poste un 'moteur' Access, qui récupère l'ensemble des données à utiliser et qui les traite en local.
On parle de serveur de fichier.
Le moteur d'accès est présent sur chaque poste.
Si plusieurs utilisateurs sont connectés à une base SQLServer: la base est sur le serveur avec le logiciel SQLServeur.
Un logiciel utilisateur situé sur un autre ordinateur(le client) envoie au serveur une requête.
Le logiciel SQLServer traite la requête sur le serveur et retourne au logiciel client uniquement le résultat de la requête.
On parle d'architecture Client-serveur.
Le moteur d'accès est présent uniquement sur le serveur.
Si on cherche un enregistrement parmi 60 000 enregistrements, en serveur de fichiers, les 60 000 enregistrements sont envoyées par le réseau vers le moteur Access de l'utilisateur ; le moteur les traite pour en sortir un.
En client serveur, le logiciel utilisateur envoie une requête au serveur, le logiciel serveur cherche sur le serveur dans la base l'enregistrement, il le trouve et envoie à travers le réseau vers le logiciel client uniquement un enregistrement.
XVII-A-10. Opérations sur les enregistrements
De manière générale, on peut:
Ouvrir une base de données (Open)
Ajouter un enregistrement (Add)
Effacer un enregistrement (Delete)
Modifier un enregistrement (Update)
Chercher un ou des enregistrements.
Fermer la base. (Close)
Avant:
Il y a bien longtemps, on choisissait un index, on cherchait un enregistrement (avec Seek), on le lisait, le modifiait, on avançait (MoveNext) ou on reculait (MovePrevious) d'un enregistrement dans la base, mais c'est de l'histoire ancienne!!
Chaque type de base de données avait ses propres commandes.
Ensuite , il y a eu les RecordSet, sorte de tableau avec un curseur pointant un élément.
Avec ADO.NET :
Maintenant quelle que soit la base de données, on utilise un langage unique: le 'SQL' pour faire une requête sur la base de donnée: extraction de certains enregistrements ou de certains champs en fonction de critères), le résultat (un ensemble d'enregistrements ou de champs) se retrouvant dans un DataSet (sorte de tableau situé en local sur lequel on lit on modifie, on ajoute ou on enlève des lignes, les modifications étant répercutées sur la base de donnée du départ).
XVII-B. Généralités sur ADO.NET
Jusqu'en vb6 on utilisait DAO et ADO pour travailler sur les bases de données. En vb.Net on utilise ADO.NET.
Comment donc travailler sur les Base de données en VB.NET? Avec ADO.NET
XVII-B-1. Généralités
Pour avoir accès à partir de VB.NET aux bases de données il faut utiliser ADO.NET.
ADO veut dire Activex Database Objet .
C'est la couche d'accès aux bases de données, le SGBD (Système de Gestion de Base de Données) de VB.
ADO.NET est ".NET" donc managé et géré par le CLR.
Il est indépendant de la base de donnée: alors que initialement chaque type de gestionnaire de base de données avait ses instructions, sa manière de fonctionner, ADO.NET à un langage unique pour ouvrir, interroger, modifier une base de données quelle que soit la base de données.
Le langage de requête est le SQL.
En VB il y a 2 manières d'écrire un programme qui utilise une base de données:
- Ecrire du code pour créer des objets Ado.net, écrire du code pour ouvrir la base, créer la liaison entre la base et un DataSet avec des critères de sélection écrits en SQL.
- Utiliser l' 'Assistant de configuration de source de base de données' qui crée les objets et le code à votre place. On peut même lier une table à une list par exemple qui sera 'remplie' automatiquement par la table (On parle de Binding).
XVII-B-2. Les Managed Providers
Pour avoir accès aux données il faut charger les DRIVERS (ou providers).
Comme d'habitude, il faut:
- Charger les références des drivers (les Dll)
- Importer les espaces de nom.
Ainsi on a accès aux objets Ado.Net correspondant.
Voyons cela:
- OLE DB Managed Provider est fourni dans 'System'; après avoir importé le NameSpace System.Data.OLEDB, on peut travailler sur des bases Access par exemple.
- SQL Server Managed Provider est fourni dans 'System'; après avoir importé le NameSpace System.Data.SqlClient, on peut travailler sur des bases SqlServeur.
- Un composant ODBC et un composant ORACLE sont disponible sur le site MSDN , il faudra charger la référence de la Dll puis le NameSpace.
- Pour travailler sur une base MySQL lisez le très bon didacticiel MySQLDotNet (sur developpez.com bien sur);il utilise le Managed Provider ByteFX
Exemple, pour travailler sur une base Access, il faudra taper:
Imports System.Data.OLEDB
Avec Visual Basic Express 2010, vous pouvez accéder à trois types de bases de données : Microsoft SQL Server Compact Edition, Microsoft SQL Server Express ou Microsoft Access.
XVII-B-3. Les Objets ADO.NET
Il faut disposer d'un objet Connexion pour avoir accès à la base de données, on met dans la propriété ConnectionString les paramètres de la base de données (nom de la base de données, chemin, mot de passe..).
En fonction de la BD les paramètres sont différents. Un site nommé ConnetionStrings.com donne une mine de renseignements pour écrire les paramètres de connexion pour une BD.
Avec la méthode Open on ouvre la base.
on peut ensuite travailler de 2 manières:
A- On envoie une requête Sql 'SELECT' à la base, on récupère le résultat dans un objet.
- Avec un objet DataReader on extrait les données en lecture seule: une requête SQL (sur un objet command) charge le DataReader. c'est rapide; on peut lire uniquement les données et aller à l'enregistrement suivant. Il travaille en mode connecté. Pour gérer un DataReader on a besoin d'un objet Command.
- Avec un objet DataSet on manipule les données: une requête SQL (sur un objet command) charge le DataSet avec des enregistrements ou des champs, on travaille sur les lignes et colonnes du DataSet en local, en mode déconnecté(une fois que le DataSet est chargé, la connexion à la base de données est libérée). Pour alimenter un DataSet on a besoin d'un objet DataAdapter qui fait l'intermédiaire entre la BD et le DataSet.
B- On manipule directement la base. (sans retour de résultats)
- Avec un objet Command on peut manipuler directement la BD (en SQL avec UPDATE, INSERT, DELETE CREATE DROP..), on utilise la propriété ExecuteNonQuery pour cela.
Avec la méthodes Close on ferme la base.
Résumons les différents objets nécessaires pour travailler sur une BD:
Noter bien le sens des flèches:
- le DataReader est en lecture seule, les données lues dans la BD sont accessibles dans le DataReader.
- le DataSet peut lire et écrire des données dans la BD, il faut un DataAdapter en plus de la connexion.
- l'objet Command peut modifier la BD.
Ce schéma souligne aussi les objets intermédiaires nécessaires:
- un objet connexion dans tous les cas,
- un objet Command pour le DataReader,
- un objet DataAdapter plus un objet Command pour le DataSet .
L'objet Command permet d'envoyer des ordres en SQL à la BD et de la modifier, il permet aussi, quand on utilise un DataSet, d'envoyer une requête SELECT en SQL afin de remplir le DataSet avec le résultat de la requête.
Enfin certains contrôles comme les DataGrid, les ListBox par exemple peuvent afficher des données à partir d'un DataSet.
Pour mettre à jour la base après modification du DataSet ou de la Grid il faut un objet CommandBuilder.
Mode connecté ou déconnecté:
- le DataReader fonctionne en mode connecté,la connexion entre la BD et le DataReader est ouverte tant que le DataReader fonctionne.
- le DataSet peut travailler en mode déconnecté: on ouvre la connexion, on charge le DataSet, on ferme la connexion (il faut le faire, ce n'est pas automatique), on travaille sur le DataSet, on peut le rouvrir plus tard pour les mises à jour.
Remarque: En fonction du provider, le nom des objets change:
Avec le provider OleDb, après Imports System.Data.OleDb
on utilisera OleDbConnexion, OleDbAdapter...
Avec le provider SQL, après Imports System.Data.SqlClient
on utilisera SqlConnexion, SqlAdapter...
Un site nommé ConnetionStrings.com donne une mine de renseignements pour écrire les paramètres pour une BD.
XVII-B-4. Le DataReader
Le DataReader permet donc de lire très rapidement une table, enregistrement par enregistrement, du début à la fin.
Il n'y a pas possibilité d'écrire dans la base.
XVII-B-5. Le DataSet
Le DataSet a la structure d'une base de données mais en local; il contient:
Des DataTable qui contiennent des DataRow et des DataColumn.
Pour utiliser DataSet, DataTable, DataRow.. il faut importer l'espace de nom Data:
Imports System.Data
On peut créer un Dataset de toutes pièces, mais la plupart du temps, on charge le DataSet à partir d'une base de données.
Une requête SQL charge le DataSet, on travaille sur les lignes et colonnes du DataSet en local ( sur des enregistrements ou des champs), en mode déconnecté (une fois que le DataSet est chargé, la connexion à la base de données peut être libérée).
La structure de données du DataSet reflétera automatiquement et exactement celle des données extraites. Si j'extrais 2 colonnes de données avec l'instruction Sql fournis à l'objet Command, le DataSet aura une table (DataTable) de 2 colonnes avec les données extraites.
Exemple :
Avec ADO.NET je lance une requête SQL demandant toutes les fiches de la table 'nom' dont le champ 'prénom' est 'Philippe', je récupère un DataSet local contenant toutes les fiches (Le DataColumn "Prénom" ne contient que des 'Philippe'). Je peux modifier en local le DataSet, (modifier une date de naissance par exemple) et mettre à jour automatiquement la base de données distante.
Pour être complet il existe aussi les DataView qui représentent une vue d'un DataTable. (Un DataView peut contenir un champ d'une table d'un DataSet, ou les enregistrements répondant à un critère).
XVII-C. Syntaxe SQL (Généralités)
Comment adresser une requête vers une Base de données de ADO.NET? Avec SQL
Ici on va voir des généralités sur le langage SQL, on utilisera ce langage dans les requêtes ADO.net . En Ado.Net on verra qu'on exécute ces requêtes par la méthode 'ExecuteNoQuery' d'un objet Command. On verra plus bas qu'il est aussi possible d'interroger une base de données grâce à LINQ qui permet d'interroger en VB avec une syntaxe proche de SQL.
XVII-C-1. Généralités
SQL veut dire Structured Query Language : Langage d'interrogation structurée
SQL grâce au couplage avec un SGBD relationnelle permet un traitement interactif des requêtes.
SQL est le langage unique qui permet de décrire, manipuler, contrôler l'accès et interroger les bases de données relationnelles.
C'est un langage déclaratif, qui est régi par une norme (ANSI/ISO) qui assure la portabilité du langage sur différentes plates-formes aussi bien matérielles que logicielles. Une commande SQL écrite dans un environnement Windows sous ACCESS peut, souvent sans modification, être utilisée directement dans un environnement ORACLE sous Unix...
SQL est utilisé dans ADO.NET pour manipuler toutes les bases de données.
XVII-C-2. Les commandes SQL
XVII-C-3. SELECT : Interrogation
Permet d'extraire ,de sélectionner des données.
Syntaxe simplifiée:
SELECT champ FROM table WHERE condition
Dans la table 'table' sélectionner les enregistrements vérifiant la condition 'condition' et en afficher les champs 'champs'
Exemple
Exemple:
Soit la table patient:
SELECT Nom FROM Patient
Cela signifie: dans la table Patient extraire les champs 'nom'
SELECT Nom FROM Patient WHERE Prenom='Luc'
WHERE ajoute un critère de sélection.
Cela signifie: dans la table Patient extraire le champ Nom de tous les enregistrements dont le prénom est "Luc" .
SELECT Nom, Prenom FROM Patient WHERE Sexe='F'
Cela signifie: dans la table Patient extraire le champ Nom et prénom de tous les enregistrements dont le champ sexe est "F"( F comme féminin) .
Dans l'exemple on obtient :
SELECT * FROM Patient WHERE Datenais>=#01/01/1950#
Cela signifie: dans la table Patient extraire tous les champs de tous les enregistrements dont le champ date de naissance est supérieur ou égal à 01/01/1950 .
Dans l'exemple on obtient
On remarque que
* signifie : extraire tous les champs.
Pour utiliser les dates , il faut les entourer de "#".
Les dates sont au format mm/jj/aaaa
SELECT * FROM Patient WHERE Datenais>= #01/01/1950# AND Datenais<= #01/01/1980#
Cela signifie: dans la table Patient extraire tous les champs de tous les enregistrements dont le champ date de naissance est supérieur ou égal à 01/01/1950 et inférieur ou égal à 01/01/1980 .
On remarque que on peut utiliser avec Where, les opérandes
AND OR NOT.
Il est bien sur possible de combiner des conditions sur des champs différents:
Sexe='M' AND Prenom='Luc"
SELECT * FROM Patient WHERE BETWEEN #01/01/1950# AND #01/01/1980#
Même signification que le précèdent mais en utilisant BETWEN AND qui est plus performant.
SELECT Nom FROM Patient WHERE Prenom IN ('Luc' , 'Pierre', 'Paul')
Cela signifie qu'il faut extraire les enregistrements dont le prénom est Luc, Pierre ou Paul .
SELECT Nom FROM Patient WHERE Prenom LIKE 'D%'
Cela signifie qu'il faut extraire les enregistrements dont le prénom commence par un 'D'.
LIKE recherche des chaînes de caractères avec l'aide de caractères génériques:
% représente une chaîne de caractères même vide.
_ représente un caractère.
On peut spécifier une série de caractères en les mettant entre ""
Exemple :
LIKE 'D%' commence par D
LIKE '%D%' contient D
LIKE '[DF]%' commence par D ou F
LIKE '___' contient 3 caractères
SELECT Nom FROM Patient WHERE SEXE IS NULL
Cela signifie qu'il faut extraire les enregistrements dont le sexe n'a pas été enregistré.
SELECT DISTINCT Nom FROM Patient WHERE SEXE IS NULL
DISTINCT permet d'éviter les doublons
Si dans les Noms extraits il y a 2 fois le même (2 membres d'une même famille) , il n'en est gardé qu'un.
XVII-C-4. Tri des enregistrements
ORDER BY sert à trier les enregistrements.
Il est placé à la fin.
DESC sert à trier par ordre décroissant.
SELECT Nom, Prenom , Sexe, DateNais FROM Patient WHERE Sexe='F' ORDER BY DateNais
Trie les enregistrements de sexe 'F' par date de naissance
SELECT Nom, Prenom, DatNais, NumInt FROM Patient WHERE Sexe='F' ORDER BY DateNais DESC, NumInt
Trie les enregistrements de sexe 'F' par date de naissance mais décroissante et pour une même date de naissance par numéro interne croissant.
XVII-C-5. Statistiques
SELECT COUNT(*) AS NombrePatient FROM Patient
Compte le nombre total d'enregistrement dans la table Patient et met le résultat dans le champ NombrePatient
On peut aussi utiliser:
MIN retourner la plus petite valeur.
MAX retourner la plus grande valeur.
SUM retourner la somme.
AVG retourner la moyenne.
VAR retourner la variance
STDEV retourner l'écart type.
SELECT Prenom ,COUNT(*) AS NombrePrenom FROM Patient GROUP BY Prenom
Extrait la liste des prénom avec le nombre de fois que le prénom est utilisé.
GROUP BY regroupe les enregistrements par valeur.
SELECT Prenom ,COUNT(*) AS NombrePrenom FROM Patient GROUP BY Prenom HAVING CONT(*)>3
Extrait la liste des prénoms avec le nombre de fois que le prénom est utilisé. S'il est utilisé plus de 3 fois..
HAVING rajoute un critère au regroupement.
XVII-C-6. Extraction de données sur plusieurs tables
Parfois on a besoin d'extraire des champs de plusieurs tables différentes, mais ayant une relation (un champ commun); pour cela on utilise une jointure.
Pour chaque enregistrement de la première table, on affiche en regard les enregistrements de la 2eme table qui ont la même valeur de jointure.
Exemple:
Soit la table patient
Comment récupérer les champs Nom et ville (pas le numéro)?
SELECT Patient.Nom, Ville.NomVille From Patient INNER JOIN Ville ON Patient.NuméroVille= Ville.NuméroVille
On obtient:
En ADO.Net, on verra qu'on passe la chaîne SQL à un objet Command.
Ces données extraites à partir d'une base de données grâce à l'instruction SQL vont se retrouver dans un DataSet (sorte de Bd en local, en mémoire).
XVII-C-7. Ajout, suppression, modification d'enregistrement
Il est impossible d'insérer, de modifier ou de supprimer dans plusieurs tables simultanément
Une requête de mise à jour (INSERT, UPDATE ou SELECT) est une transaction ,tout doit être réalisé, sinon rien ne se passe (en particulier si une seule donnée viole une contrainte, toutes les opérations sont annulées )
Insertion d'enregistrement:
Syntaxe:
INSERT [INTO] nomdelatable [(listedescolonnes)]
{VALUES (listedesvaleurs) | requêteselect | DEFAULT VALUES }
Exemple:
INSERT INTO TablePatient (Civilité, Nom, Prenom, NumeroVille, Datenais, Sex)
VALUES ('M', 'Dupont', 'Pierre', '2',' 02/12/51/, 'M')
Effacement d'enregistrement:
Syntaxe:
DELETE [FROM] nomtable
[WHERE condition]
Exemple:
DELETE FROM Patient
WHERE Nom LIK'%d'
Mise à jour d'enregistrement:
Syntaxe:
UPDATE nomtable
SET colonne1 = valeur1 , colonne2 = valeur2 ...
WHERE condition
WHERE condition est facultatif et permet de sélectionner les enregistrements correspondant
à un critère et de modifier ceux là.
En Ado.Net on verra qu'il y a 2 méthodes pour modifier ajouter, supprimer un enregistrement:
- On peut effectuer directement les commandes en SQL: on exécute ces commandes par la méthode ExecuteNoQuery d'un objet Command.
- Il est plus simple de lier la base à un DataSet, de modifier le DataSet et de mettre à jour la base par un Update (du DataAdapter). Dans ce cas, on n'écrit pas de commande SQL.
XVII-C-8. Ajout de table
On utilise CREATE TABLE puis le nom de la table, on ajoute les différents champs (entre parenthèses et séparés par des virgules)avec pour chaque champ les conditions (NOT NULL..) et le type.
CREATE TABLE PARENT (CLI_ID INTEGER NOT NULL PRIMARY KEY, CLI_NOM CHAR(32) NOT NULL, CLI_PRENOM VARCHAR(32))
En Ado.Net on verra qu'on exécute ces commandes par la méthode ExecuteNoQuery d'un objet Command.
Pour aller plus loin:
Série d'articles sur SQL chez developpez.com:
Les sources présentés sur cette page sont libres de droits,
et vous pouvez les utiliser à votre convenance. Par contre cette page de présentation de ces sources constitue une oeuvre intellectuelle protégée par les droits d'auteurs. Copyright © .
Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu :
textes, documents, images, etc sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à 3 ans de prison et jusqu'à 300 000 E de dommages et intérêts.
Cette page est déposée à la SACD.
|
