EditerBases de données


Définitions



Une base de donnée permet de stocker des informations en rapport avec une activité. Exemple de logiciels sgbd (système de gestion de base de données) : Access, file maker pro, bento, mysql...

Il existe différentes manières d'organiser les données dans la base, ici, on parlera des bases de données relationnelles.

Excel peut gérer des listes de données mais il dispose de moins de fonctions : par exemple, la recherche est moins poussée que dans Access, difficile de stocker des longs textes dans les cases...

Il vaut mieux utiliser Access à la place d'Excel :
* Si on a besoin des liens de différentes tables entre elles (par exemple, un produit + son prix lié avec un client + l'adresse d'un client...).
* Si on a un très gros volume de données (plusieurs milliers d'entrées).
* Si on a besoin d'accéder à ces données à distance.
* Si on a besoin de requêtes complexes.

Comparatif Excel et Access sur le site de Microsoft.

En outre, une base de donnée peut généralement être utilisée par plusieurs utilisateurs en même temps ce qui n'est pas le cas d'un fichier.

Une base de donnée relationnelle est constituée de tables, de champs et d'enregistrements.

Une table peut être comparé à une boite à fiches. On aura ainsi une boite "client", une autre boite "fournisseur", une autre boite "produits"... le tout dans la même base de donnée. Une table est gérée par un moteur (engine). Pour MySQL, par exemple, différents moteurs sont disponibles plus ou moins efficace selon ce qu'on veut faire avec cette table. Avant MySQL 5.5, MySAM était le moteur par défaut, c'est désormais InnoDB (InnoDB permet d'effectuer des vérifications pour que la base reste cohérente au prix d'une vitesse moindre que MySAM). Il est plutôt recommandé d'utiliser le même moteur pour toute la base, mais certains préfèrent utiliser un moteur très rapide pour certaines tables et un moteur plus sécurisé pour d'autres dans une même base.

Dans la boite "client", on va trouver les noms, adresses et numéro de téléphones des clients.

Un champ désigne l'entête des colonnes (lignes verticales). Par exemple, une colonne "nom", une colonne "adresse", une colonne "Téléphone"...

Un enregistrement désigne une ligne du tableau. Par exemple "Bob 11 rue de la bière 05456514". On parle aussi de "tuple" ou de "nuplet" (ou "n-uplet").

Une clef primaire est une valeur unique à la table permettant de désigner un enregistrement. Souvent, même si un enregistrement est supprimé, sa clef primaire ne sera pas réutilisée. Elle permet d'identifier précisément un enregistrement (par exemple, deux employés peuvent avoir le même nom et prénom, le seul moyen de les différencier sera la clef primaire qui pourra être un n° d'employé).

Une clef étrangère est en général la clef primaire empruntée à une autre table (si ce n'est pas le cas, elle doit au moins être unique dans les 2 tables). C'est elle qui permet le lien entre les tables. Elle sert de champ de liaison. Attention, le moteur "MyISAM" (moteur de défaut de Mysql jusqu'à la version 5.5) ne prend pas en charge les clefs étrangères.

Id employé (Clef primaire)NomAdresseTéléphone
KL11527BBob11 rue de la bière05456514
JB22238CBill35 avenue des oiseaux0976342

Sur SQL (MySQL, MsSQL), différents utilisateurs peuvent modifier la même donnée en même temps. Pas sous Access.

Des indexes sur les champs permettent d'accélérer la recherche (au prix d'une augmentation de l'espace occupé par la base) : l'index d'un bottin téléphonique est le nom mais on pourrait avoir en plus un index par rue ou par numéro de téléphone pour accélérer la recherche. On peut mettre un index sur plusieurs champs (nom+ville) si on sait qu'on va les utiliser souvent ensemble.

On parle aussi de "relation" pour désigner une table, pas "relation" dans le sens de "lien" mais dans le sens où la table relate quelque chose.

MySQL différencie les utilisateurs selon l'hôte depuis lequel ils se connectent.


SQL


Un langage quasi-universel pour l'exploitation des bases. 3 types de commandes : de définition de données (création), commandes de manipulation de données (exploitation de la base) et d'administration des utilisateurs.

Deux commandes essentielles pour commencer :
Voir les bases de données : show databases;

Choisir sur quelle base travailler : use nomdelabase;

Login dans le programme de gestion : mysql -u root -p[mot de passe]

Commandes de contrôle des données


2 commandes pricipales : grant et revoke.

Pour donner les droits de modification de la structure, suppression de la table, suppression des données, insertion des données, affichage des données et mise à jour des données à Bob en l'autorisant à donner lui même ces droits à d'autres personnes.

grant alter, drop, delete, insert, select, update ON clients TO Bob WITH GRANT OPTION;

WITH GRANT OPTION : permet donc à l'utilisateur de transmettre les mêmes droit qu'il possède lui même à d'autres. Ils ne pourront pas par contre transmettre eux-mêmes des droits à d'autres.

Donne uniquement le droit à bob d'afficher le contenu de la table : grant select ON clients TO Bob;

Ces commandes offrent sécurité, traçabilité (qui a manipulé quoi) et facilite l'administration (par exemple, un utilisateur nous indique qu'il a fait une erreur et on peut la corriger en se basant sur son nom).

Commandes de définition de données



Les 3 commandes phares pour la définition des données sont create, drop et alter (il existe aussi rename qui peut se faire avec alter).

Créer une base de donnée : create database nomdelabase; On spécifiera l'encodage avec la commande collate ou collation, par exemple pour mysql : CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

Créer une table dans la base en cours : create table nomdelatable;

Renommer une base de donnée : La fonction n'est plus disponible avec mysql, voir http://dev.mysql.com/doc/refman/5.1/en/rename-database.html.

Voir les tables de la base en cours : show tables;

Voir la structure ("schéma") d'une table dans la base en cours : describe table nomdelatable;

Supprimer une base de donnée : drop database nomdelabase;

Supprimer une table : drop table nomdelatable;

Modifier le nom d'une table : alter table (ancien nom) rename (nouveau nom) (type). Exemple : alter table animaux change now nom varchar(20);

Ajouter un champ/colonne à une table: alter table employe add nom_employe varchar(64);

Changer un nom de colonne : alter table (nom de la table) change (ancien nom) (nouveau nom) int(10);

Agrandir ou réduire une colonne : alter table (nom de la table) modify (nom de la colonne) int(11);

Supprimer un champ/colonne : alter table (nom de la table) drop column (nom de la colonne);

Ajouter la contrainte unique sur une table : ALTER TABLE nomDeLaTable add unique (champQuOnVeutRendreUnique);

Retirer la contrainte unique d'une table : ALTER TABLE nomtable DROP INDEX nomchamp (c'est bien INDEX et pas UNIQUE)

Ajouter une contrainte sur une table : ALTER TABLE nomdelatable ADD CONSTRAINT nouveaunomcontrainte FOREIGN KEY (champ_qui_va_réferencer) references nom_de_la_table(champ _à_référencer);

Charger des données depuis un fichier externe : load data infile 'nomdufichier' into table nomdelatable; Le fichier peut se trouver dans /var/lib/mysql/(nom de la base)/, par exemple. On peut également copier/coller un fichier texte directement dans l'interface en ligne de commande de mysql.

Exemple :
Créer une table dans la base de donnée avec les champs numéro primaire (numéro d'identité unique qui restera même si on supprime le champ), nom, prénom, adresse et âge :
create table nom_de_la_table(id INT AUTO_INCREMENT primary key not null, nom VARCHAR(40), prenom VARCHAR(40), adresse VARCHAR(500), age INT(3) );

Créer une table dans la base de donnée avec les champs numéro primaire, marque, type, capacité et localisation :
create table avion(idavion int auto_increment primary key not null, marque varchar(20), type varchar(20), capacite SMALLINT(4), localisation varchar(25));
Puis, pour mettre l'auto_increment à 100 lors de la prochaine entrée : alter table avion AUTO_INCREMENT=100;

Création de tables pour une banque :

create table banque(id_banque int unique not null AUTO_INCREMENT, nom varchar(32), constraint PKbanque PRIMARY KEY (id_banque)); create table client(id_client int unique not null AUTO_INCREMENT, sexe varchar(8), nom varchar(64), prenom varchar(64), telephone varchar(16), voie varchar(64), cp varchar (16), ville varchar(32), annee_naissance int, id_banque int NOT NULL, constraint PKclient PRIMARY KEY (id_client)); create table compte(id_compte int unique not null AUTO_INCREMENT, solde int, id_banque int NOT NULL, IBAN int unique not null, constraint PKcompte PRIMARY KEY (id_compte), constraint FKbanqueDuCompte FOREIGN KEY (id_banque) REFERENCES banque(id_banque)); create table posseder(id_client int, id_compte int, constraint PKposseder PRIMARY KEY (id_client, id_compte), constraint FKclientPossedant FOREIGN KEY (id_client) REFERENCES client(id_client) ON DELETE CASCADE ON UPDATE CASCADE, constraint FKcomptePossede FOREIGN KEY (id_compte) REFERENCES compte(id_compte) ON DELETE CASCADE ON UPDATE CASCADE); create table calendrier(annee_mois_jour DATE UNIQUE NOT NULL, constraint PKcalendrier PRIMARY KEY (annee_mois_jour)); create table operation_bancaire(id_operation int unique not null AUTO_INCREMENT, demandeur varchar(128), montant int not null, id_compte_debite int, id_compte_credite int, annee_mois_jour date not null, constraint PKid_operation PRIMARY KEY (id_operation), constraint FKid_compte_debite FOREIGN KEY (id_compte_debite) REFERENCES compte(id_compte), constraint FKid_compte_credite FOREIGN KEY (id_compte_credite) REFERENCES compte(id_compte), constraint FKcompteVersDate FOREIGN KEY (annee_mois_jour) REFERENCES calendrier(annee_mois_jour));


Pour un site marchand :

create table calendrier(jjmmaa date unique not null, constraint PKcalendrier PRIMARY KEY (jjmmaa)); create table utilisateur(id int unique not null,pseudo varchar(32) unique not null, nom varchar(32), prenom varchar(32),adresse varchar(64), CP varchar(16), ville varchar(64), motdepasse varchar(64), constraint PKutilisateur primary key (id)); create table commande (numero int unique not null, etat varchar(16), dateCmdPassee date, dateCmdLivree date, idUtilisateur int, constraint PKcommande primary key (numero), constraint FKdateCmdPassee foreign key (dateCmdPassee) references calendrier(jjmmaa),constraint FKdateCmdLivree foreign key (dateCmdLivree) references calendrier(jjmmaa), constraint FKcommandeVersUtilisateur foreign key (idUtilisateur) references utilisateur(id)); create table categorie (id int unique not null, libelle varchar(32), constraint PKproduit primary key (id)); create table produit(id int unique not null, marque varchar(32), modele varchar(64), descriptif varchar(128), prix int, idCategorie int, constraint PKproduit primary key (id), constraint FKproduitVersCategorie foreign key (idCategorie) references categorie(id)); create table ligneCommande(id int unique not null, qte int, numeroCommande int, idProduit int, constraint PKligneCommande primary key (id), constraint FKligneCommandeVersCommande foreign key (numeroCommande) references commande(numero), constraint FKligneCOmmandeVersProduit foreign key (idProduit) references produit(id));


Types de champs


Lors de la création de la table, on devra donner un type aux champs.

Formats numériques


TINYINT : Entier très petit, de 0 à 255. (1 octet)

SMALLINT : Entier petit compris entre -32768 et 32767, si l'option UNSIGNED est utilisée, ce nombre sera compris entre 0 et 65535. (2 octets)

MEDIUMINT : Entier moyen compris entre -8388608 et 8388607, si l'option UNSIGNED est utilisée, ce nombre sera compris entre 0 et 16777215 (3 octets)

INT : Entier standard compris entre –2 147 483 648 et 2 147 483 647. Si l'option UNSIGNED est utilisée, ce nombre sera compris entre 0 et 4 294 967 295 (4 octets)

BIGINT : Entier grand 8 octets

FLOAT : Décimal de simple précision 4 octets. Pour les nombres à virgules flottantes.

DOUBLE, REAL : Décimal de double précision 8 octets (le nombre d'octet peut changer selon les SGBD : pour certaines REAL ne stocke que 4 octets).

DECIMAL (nombre de chiffres incluant ceux après la virgule,nombre de chiffre après la virgule). Pour un prix utiliser decimal(8,2), ce qui signifie que vous pourrez stocker un prix jusqu'à 999999.99.

A noter que de préciser "UNSIGNED" force les valeurs à être positives, ce qui double les valeurs positives possibles des nombres :
create table test (nombre int unsigned);

Chaînes de caractères



CHAR (n) : Chaîne de n caractère réservant n caractères en mémoire.
VARCHAR(n) : Chaîne de caractères variable. n peut être compris entre 1 et 255.

TINYBLOB,TINYTEXT : Petite zone de texte. Objet d'une longueur maximale de 255 caractères,
TINYTEXT aura un contenu de type ASCII (casse insensible) et TINYBLOB aura un contenu de type binaire (casse sensible).

BLOB, TEXT : Zone de texte standard Objet d'une longueur maximale de 65535 caractères,
TEXT aura un contenu de type ASCII (casse insensible) et BLOB aura un contenu de typebinaire (casse sensible). 65 535 caractères. maximum

MEDIUMBLOB, MEDIUMTEXT : Zone de texte moyenne. Objet d'une longueur maximale de 16777216 caractères, MEDIUMTEXT aura un contenu de type ASCII (casse insensible) et MEDIUMBLOB aura un contenu de type binaire (casse sensible). 16 millions caractères. maximum

LONGBLOB, LONGTEXT : Grande zone de texte. Objet d'une longueur maximale de 4294967295
caractères, LONGTEXT aura un contenu de type ASCII (casse insensible) et LONGBLOB aura un contenu de
type binaire (casse sensible). 4 milliards caractères maximum.

Dates



DATE : Date (ex: "2000-08-24") 3 octets

TIME : Heure (ex: "12:34:56") 3 octets

DATETIME : Date et heure (ex: "2012-08-24 12:34:56") 8 octets

YEAR : Année (ex: "2000") 1 octet

TIMESTAMP : nombres de secondes depuis le 1er janvier 1970 à 0h. Utile pour stocker un évènement sans tenir compte des fuseaux horaires.

Pour Access


#06/07/2008# : 7 juin 2008 (le format américain est utilisé, même sur un access français).

#7-jun-2008# : 6 juin 2008.

N'importe quelle date de l'année 2008.

WHERE DATE_ACTIVITE LIKE "##/##/2008"


day(champ) : extrait le jour du champ.

month(champ) : extrait le mois du champ, "where month(champ)>6".

year(champ) : extrait l'année du champ.

Contraintes


Les contraintes sont des commandes utilisées lors de la définition des champs visant à les forcer à être rempli de manière appropriée.

DEFAULT : donne une valeur par défaut lorsqu'un champ est créé sans valeur. create table test (nom varchar(60) DEFAULT 'Anonyme', prenom);
NOT NULL : si on essaie de créer un enregistrement sans ce champ, il ne sera pas enregistré et un message d'erreur se produira.
UNIQUE : si un enregistrement avec le même champ existe déjà dans la table, une erreur s'affichera. Par exemple, on peut vouloir qu'un numéro de sécurité social soit unique dans la table "employé".
CHECK : fait une vérification (exemple : check (Email LIKE "%@%")) et insère la donnée si elle a réussie.
create table tabletest(id integer primary key,nom char(20), prenom char(20),ville char(20),age integer(150), check (age>0));

Contrainte de clef primaire


Il est possible de rendre un champ clef primaire de la façon suivante :
create table employe(id_employe int primary key);

Mais il est plus souple de créer le champ normalement puis d'appliquer une contrainte :
create table employe(id_employe int unique not null, constraint PKemploye PRIMARY KEY (id_employe));

Ici, on applique une contrainte nommée "PKemploye" au champ "id_employe". Si on décidait un jour de changer de clef primaire, il suffirait de modifier la contrainte. On aurait pas besoin de modifier la table, il est parfois difficile de modifier la primary key selon cetaines SGBD.

Attention, il ne peut pas y avoir deux contraintes portant le même nom dans la base.

On peut concaténer deux clefs primaires :constraint PKemploye PRIMARY KEY (idetudiant,idmatiere).

Contraintes d'intégrité sur les clefs étrangères


S'applique uniquement sur des clefs étrangères. Elle précise les actions à effectuer lors de la suppression ou la modification de la clef primaire référencée.
Table A :
Id employéNom
1Bob

Table B :
Numéro catégorieNom catégorieId employé (clef étrangère)
3Chef Informatique1

Que se passe-t-il si on supprime Bob de la table A ? Que se passe-t-il si on modifiait l'ID employé de Bob par 2 dans la table A ?

Si une contrainte "restrict" (c'est souvent la valeur par défaut) est précisée : il est impossible de détruire Bob de la table A. Il faut d'abord supprimer le numéro de Bob de la table "catégorie".

Si une contrainte "en cascade" est précisée : toute la ligne "3, chef informatique, 1" est supprimé de la table B. A utiliser avec prudence !

Il existe aussi les contraintes "set null" et "set default" pour mettre dans "Id employé (clef étrangère)" une valeur nulle ou par défaut. InnoDB ne supporte pas "Set Default".

"Cascade" et "Restrict" sont les plus utilisés.


Pour créer une contrainte sur une clef étrangère, on utilise la syntaxe "constraint nom_de_la_contrainte FOREIGN KEY (champ_qui_va_referencer_la_clef_etrangere) REFERENCES table_referencee(champ_reference) ON DELETE CASCADE ON UPDATE CASCADE" :
create table categorie(id_categorie int primary key, nom_categorie varchar (64), id_employe_pr_categorie int not null, constraint FKcategorieEmploye FOREIGN KEY (id_employe_pr_categorie) REFERENCES employe(id_employe) ON DELETE CASCADE ON UPDATE CASCADE);

Attention : deux contraintes ne peuvent pas porter le même nom dans toute la base. Penser à mettre les foreign key en not null si nécessaire.

Autre exemple :

create table ecran (id_ecran int unique not null auto_increment,nom_ecran varchar(64), id_atelier int, constraint PKecran primary key (id_ecran), constraint FKecran_vers_atelier foreign key (id_atelier) references atelier(id_atelier)) ENGINE=INNODB; create table page_ecran (id_page int unique not null auto_increment, nom_page varchar(255), duree_affichage int, ordre int, id_ecran int, constraint PKpage primary key (id_page), constraint FKecran_vers_page foreign key(id_ecran) references ecran(id_ecran) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=INNODB;


Ci-dessus, 2 tables sont créées : une table "ecran" et une table "page_ecran". Un écran peut avoir plusieurs pages. Si on supprime un écran, toutes ses pages seront elles aussi être supprimées

Liste les contraintes :

select * from information_schema.table_constraints where constraint_schema = 'LE_NOM_DE_MA_BASE'



Commandes de manipulation de données



SELECT : indique les champs qu'on veut afficher. On appelle ça une "projection". Pour afficher le contenu de la colonne employe : select id_employe from employe;. On peut afficher plusieurs colonnes en même temps :


mysql> select id_employe,nom_employe from employe; +------------+-------------+ | id_employe | nom_employe | +------------+-------------+ | 1 | Paul | | 2 | Jose | | 3 | Bob | +------------+-------------+ 3 rows in set (0.00 sec)


Si deux champs on le même nom dans deux tables différentes et qu'on va utiliser ces deux tables, il peut être utile de préciser sur quel table on travaille de la forme suivante : table.champ. Exemple : categorie.id_employe (champ id_employe de la table catégorie) ou employe.id_employe.

On peut utiliser une étoile pour afficher tous les champs : select * from employe;

SELECT DISTINCT : Même chose que précédemment mais n'affiche pas les lignes étant en doublon dans le résultat.

mysql> select distinct nomproduit from produit; +---------------+ | nomproduit | +---------------+ | canne a peche | | voiture | | cigare | +---------------+ 3 rows in set (0.00 sec)


Ci-dessous "cigare" reste en doublon car on affiche les autres champs :

mysql> select distinct * from produit; +-----------+---------------+ | idproduit | nomproduit | +-----------+---------------+ | 1 | canne a peche | | 2 | voiture | | 3 | cigare | | 4 | cigare | +-----------+---------------+ 4 rows in set (0.00 sec)




FROM : indique depuis quelle table(s) on veut afficher.

WHERE : indique qu'on veut filtrer certains enregistrements. On appelle ça une "restriction" ou une sélection. WHERE salarié.poste = 'chef de service' signifie qu'on ne veut afficher que les enregistrements dont le champ "poste" vaut 'chef de service'.

mysql> select id_employe,nom_employe from employe where nom_employe="bob"; +------------+-------------+ | id_employe | nom_employe | +------------+-------------+ | 3 | bob | +------------+-------------+ 1 row in set (0.05 sec)

On peut également se servir de where pour réaliser des jointures (voir plus bas).

WHERE DISTINCT : A ajouter juste après le mot "where" pour filtrer les lignes identiques, par exemple, si on veut la liste des clients du mois et que clients sont venus plusieurs jours dans le mois, on ne veut pas répéter ces clients.)

AND : Utilisé avec WHERE, 2 filtres, par exemple PRIXINVENT > 100 AND STOCK > 1.

OR : Utilisé avec WHERE, pour afficher les résultats qui correspondent à un filtre ou l'autre. Where adresse="Paris" OR adresse="bordeaux" affichera les personnes dont l'adresse est à bordeaux et celles dont l'adresse est à paris. Penser à mettre des parenthèses pour ne pas faire un or sur toute la requête (affiche si 1 et 2 et (20 ou 21)).

IN : Recherche et filtre via une liste, par exemple "WHERE adresse in("Limoges", "St Junien", "Cognac")". Equivalent à "where adresse="Limoges" OR adresse="St Junien" OR adresse="Cognac". Peut servir à rechercher dans des sous requêtes.


SELECT * FROM clients WHERE ville IN ('Paris','London');



NOT IN : Si quelque chose n'est pas présent dans la liste.

-- renvoie les noms dans la table nomcoureur n'étant pas dans la liste "kelly","Larson","Mills" select nomcoureur from coureur where nomcoureur not in ("kelly","Larson","Mills")


= : est égal à.

!= : Différent de.


<> : Variante pour "différent de".

> : Supérieur.

>= : Supérieur ou égal.

< : Inférieur.

<= : Inférieur ou égal.

Between... and... : Entre 2 nombres (inclus), "Where prix between 15 and 20", ou deux dates.

LIKE : Les champs contenant une chaine spécifiée. Where nom like "A%" filtre les champs où le nom commence par A. Dans Access, on utiliserait Where nom like "A*".

NOT LIKE : Inverse de LIKE.

IS NULL : Teste si le champ d'un enregistrement est vide. WHERE nom IS NULL / IS NOT NULL.

ANY : Au moins un est égal/différent/supérieur.... S'utilise avec un opérateur arithmétique (si la condition est vraie pour au moins 1 résultat) et une sous-requête. Si 1>=Any(1,2,3,4)... 1 égal 1 donc ça fonctionne.
Ci-dessous, on demande d'afficher les prixachat présents dans la table "numerosinterdits".
Si 15 est présent dans 10 ou 15, on l'affiche (oui).
Si 10 est présent dans 10 ou 15, on l'affiche (oui).
Si 18 est présent dans 10 ou 15, on l'affiche (non).

mysql> select * from numerosinterdits; +----------------+ | numerointerdit | +----------------+ | 10 | | 15 | +----------------+ 2 rows in set (0.01 sec) mysql> select * from testprix; +-----------+-----------+-----------+ | prixachat | prixvente | idproduit | +-----------+-----------+-----------+ | 10 | 20 | 1 | | 15 | 20 | 2 | | 18 | 28 | 3 | +-----------+-----------+-----------+ 3 rows in set (0.00 sec) mysql> select * from testprix where prixachat = any (select numerointerdit from numerosinterdits); +-----------+-----------+-----------+ | prixachat | prixvente | idproduit | +-----------+-----------+-----------+ | 10 | 20 | 1 | | 15 | 20 | 2 | +-----------+-----------+-----------+ 2 rows in set (0.00 sec)

Ci-dessus, c'est équivalent à un IN. La différence est que ANY s'arrête dès qu'il a trouvé une correspondance alors que IN teste toutes les valeurs.

Attention, l'exemple ci-dessous est plus compliqué.
Si 10 est différent de 10 ou 15, on l'affiche.
Si 15 est différent de 10 ou 15, on l'affiche.
Si 18 est différent de 10 ou 15, on l'affiche.

mysql> select * from testprix where prixachat <> any (select numerointerdit from numerosinterdits); +-----------+-----------+-----------+ | prixachat | prixvente | idproduit | +-----------+-----------+-----------+ | 10 | 20 | 1 | | 15 | 20 | 2 | | 18 | 28 | 3 | +-----------+-----------+-----------+ 3 rows in set (0.00 sec)


ALL : Tous sont égal/inférieur/différents. S'utilise avec une sous requête. 1>=ALL (1,2,3,4,5) n'est pas bon car 1 n'est pas supérieur ou égal à 2, 3, 4 et 5.
Dans l'exemple ci dessous on affiche tous les prixachat différents de 10 ou 15.

mysql> select * from testprix where prixachat <> all (select numerointerdit from numerosinterdits); +-----------+-----------+-----------+ | prixachat | prixvente | idproduit | +-----------+-----------+-----------+ | 18 | 28 | 3 | +-----------+-----------+-----------+ 1 row in set (0.00 sec)


EXISTS : Si un résultat existe, à utiliser avec une sous requête en général.

NOT : Non.

ORDER BY : pour afficher les résultats en les triant. ORDER BY nomSalarié. On peut classer dans le sens contraire en ajoutant "desc". On peut utiliser plusieurs critères pour trier : select nom, espece, naissance from animaux order by espece, naissance desc. On peut également trier selon un ordre prédéfini avec la commande field : SELECT * FROM maTable ORDER BY FIELD(prenom,"José","Paul","Benoît");

DELETE : Supprime des enregistrements. DELETE FROM ANIMAUX supprime tous les enregistrements de la table animaux. Peut se filtrer avec where.

INSERT INTO : Ajoute un enregistrement. INSERT INTO nomdelatable values (champs_dans_le_bon_ordre_séparés_par une_virgule);, exemple : insert into banque values (1,"Banque de Paris");. Utiliser des guillemets si type texte ou date. La forme précédente suppose que l'on remplit chaque champ, on peut aussi utiliser INSERT INTO nom_table (nom_colonne_1, nom_colonne_2) VALUES (valeur_1, valeur_2) pour spécifier quel champ on remplit (et éventuellement ne pas tous les remplir).
On peut remplir plusieurs lignes avec une seule requête :
insert into num_employe values (14,"Bob"),(22,"Jose"),(310,"John");

On peut aussi se baser sur des champs pour remplir (ci-dessous on donne à la nouvelle page un numéro d'ordre équivalent au maximum existant pour cet écran) :
insert into page_ecran (id_page, ordre) SELECT 19,MAX(ordre)+1 from page_ecran where id_ecran=1;

UPDATE : Met à jour un enregistrement. update animaux set naissance='1989-08-31' WHERE nom = 'Chienchien'; change le champ naissance là où le champ nom vaut Chienchien. Change le 'f' par 'femelle' dans toute la table : update animaux set genre='femelle' where genre='f'.
On peut aussi modifier plusieurs champs en même temps : update animaux set naissance='1989-08-31', race='bulldog' WHERE nom = 'Chienchien';

LIMIT : Ne renvoie qu'un certain nombre de résultats. SELECT * FROM table LIMIT 10. On peut aussi renvoyer du résultat x à y SELECT * FROM billets ORDER BY date_creation DESC LIMIT 0, 5, ce qui est pratique pour faire un système de pages.

MONTH : Pour trouver le mois d'une date month("2009-05-08"), where month(vente.date)=12.

YEAR : Pour trouver l'année d'une date month("2009-05-08")

Conditions


On peut utiliser IF ou CASE lors des requêtes.
if(est vrai, faire ceci, sinon faire cela).

Ci-dessous, si le commentaire du chef existe, il est concaténé avec le commentaire de l'ouvrier, sinon il n'y a que le commentaire du chef qui s'affiche.
IF(commentaireChef<>"",CONCAT(commentaireOuvrier, "\n Commentaire Chef: ", commentaireChef),commentaireOuvrier) as expertise,

Renvoie le nombre de commande dont le statut est supérieur à 6 parmi les 3 commandes citées :

SELECT SUM(IF(idStatut>=6,1,0)) FROM gestprodfnclocale.app_commande WHERE numOF IN( "Commande1", "Commande2", "Commande3");



Un exemple de CASE. Parmi les identifiants "123" et "456", 2 champs seront changés.

UPDATE maTable SET monChamp= CASE identifiant WHEN "123" THEN 4 WHEN "456" THEN 4 END , monAutreChamp= CASE numOF WHEN "123" THEN "A" WHEN "456" THEN "B" END WHERE identifiant IN("123", "456");


Un autre mot clef pratique, "coalesce", permet de renvoyer ce qui n'est pas nul dans une liste de valeur.

Par exemple ci-dessous, l'ordre d'une page est égal à l'ordre le plus grand de la base...mais grâce au mot clef coalesce, si celui-ci est nul, l'ordre sera mis à 1 :
insert into page_ecran (nom_page ordre) SELECT "monNom",coalesce(MAX(ordre)+1,1) from page_ecran where id_ecran=1;



Jointure


Lorsqu'on affiche deux tables sans préciser la façon dont elles doivent être liées, elles apparaissent l'une à côté de l'autre sans cohérence :

mysql> select * from categorie, employe; +--------------+-------------------+------------+------------+-------------+ | id_categorie | nom_categorie | id_employe | id_employe | nom_employe | +--------------+-------------------+------------+------------+-------------+ | 2 | chef informatique | 1 | 1 | Paul | | 5 | chef RH | 2 | 1 | Paul | | 2 | chef informatique | 1 | 2 | Jose | | 5 | chef RH | 2 | 2 | Jose | | 2 | chef informatique | 1 | 3 | bob | | 5 | chef RH | 2 | 3 | bob | +--------------+-------------------+------------+------------+-------------+ 6 rows in set (0.00 sec)

On voit dans l'exemple ci-dessous qu'il n'y a que 2 catégories et 3 employés, mais on a 6 résultats sans aucune cohérence.

Pour "aligner" deux tables l'une avec l'autre ("faire une jointure"), on peut demander à ce qu'un champ soit égal à un autre avec un WHERE. Il s'agit de l'ancienne syntaxe Mysql. Dans l'exemple ci dessous, on demande à ce que les champs "id_employe" des deux tables soient alignés :

mysql> select * from categorie, employe where categorie.id_employe=employe.id_employe; +--------------+-------------------+------------+------------+-------------+ | id_categorie | nom_categorie | id_employe | id_employe | nom_employe | +--------------+-------------------+------------+------------+-------------+ | 2 | chef informatique | 1 | 1 | Paul | | 5 | chef RH | 2 | 2 | Jose | +--------------+-------------------+------------+------------+-------------+ 2 rows in set (0.04 sec)


Mais WHERE étant utilisé pour filtrer les données, il est plus clair d'utiliser le mot clef JOIN.

Nouvelle syntaxe :

mysql> select * from categorie join employe on categorie.id_employe=employe.id_employe; +--------------+-------------------+------------+------------+-------------+ | id_categorie | nom_categorie | id_employe | id_employe | nom_employe | +--------------+-------------------+------------+------------+-------------+ | 2 | chef informatique | 1 | 1 | Paul | | 5 | chef RH | 2 | 2 | Jose | +--------------+-------------------+------------+------------+-------------+ 2 rows in set (0.00 sec)


Lorsque les champs à joindre ont le même nom comme ci-dessus, on peut faire (les parenthèses sont importantes) :
join employe using (id_employe);

On peut utiliser plusieurs "join", attention il n'y a pas de virgules entre les join.


select NomEquipe, NomCoureur,NumeroCoureur,coureur.CodePays From coureur join pays on pays.codepays=coureur.codepays join equipe on equipe.CodeEquipe=coureur.CodeEquipe;


right join


Avec un left join, on met toujours les lignes en face, mais en plus, on va également mettre des lignes ne correspondant à rien.

Dans l'exemple ci-dessous, bob n'a aucune catégorie mais on a demandé à ce que toute la table à gauche se joigne à la table categorie.

A gauche, la table catégorie. A droite, la table employe.


mysql> select * from categorie right join employe on categorie.id_employe=employe.id_employe; +--------------+-------------------+------------+------------+-------------+ | id_categorie | nom_categorie | id_employe | id_employe | nom_employe | +--------------+-------------------+------------+------------+-------------+ | 2 | chef informatique | 1 | 1 | Paul | | 5 | chef RH | 2 | 2 | Jose | | NULL | NULL | NULL | 3 | bob | +--------------+-------------------+------------+------------+-------------+ 3 rows in set (0.00 sec)


Left join


Ici on fait l'inverse : la table gauche possède une catégorie sans aucun employé mais on demande à l'afficher.

mysql> select * from categorie left join employe on categorie.id_employe=employe.id_employe; +--------------+-------------------+------------+------------+-------------+ | id_categorie | nom_categorie | id_employe | id_employe | nom_employe | +--------------+-------------------+------------+------------+-------------+ | 2 | chef informatique | 1 | 1 | Paul | | 5 | chef RH | 2 | 2 | Jose | | 8 | Chef accueil | NULL | NULL | NULL | +--------------+-------------------+------------+------------+-------------+ 3 rows in set (0.00 sec)


Inner join


Par défaut, un "join" est un inner join.

select * from categorie innner join employe on categorie.id_employe=employe.id_employe;


On parle "d'inner" car on aligne les champs entre eux, en face, sans se préoccuper de ce qui dépasse, sans notion de droite ou gauche.


Alias


Lors d'un join on peut donner un alias à une table pour s'en resservir plus facilement dans le reste de la requête. Ci-dessous employe devient "EEE". L'alias disparait une fois que la requête a été traitée.

select * from categorie innner join employe as EEE on categorie.id_employe=EEE.id_employe;


On peut aussi donner un alias aux champs.

Fonctions


AVG : "Average", calcule la moyenne.

SUM : Fait la somme.


mysql> select AVG(Note) from evaluer; +-----------+ | AVG(Note) | +-----------+ | 10.4083 | +-----------+


MIN : Renvoie la plus petite valeur. select MIN(Note) from evaluer;

MAX : Renvoie la plus grande valeur.

COUNT : Compte le nombre d'occurences (de lignes, de tuples...), attention ne compte pas les "null" donc un count est à faire sur clef primaire.

Dans la table suivante :

+--------------+-------------------+------------+ | id_categorie | nom_categorie | id_employe | +--------------+-------------------+------------+ | 2 | chef informatique | 1 | | 5 | chef RH | 2 | | 8 | Chef accueil | NULL | +--------------+-------------------+------------+


On utilise un count :

mysql> select count(id_categorie) from categorie; +---------------------+ | count(id_categorie) | +---------------------+ | 3 | +---------------------+ 1 row in set (0.00 sec)


GROUP_CONCAT : rassemble des chaines de texte.

mysql> select group_concat(nom_categorie) from categorie; +----------------------------------------+ | group_concat(nom_categorie) | +----------------------------------------+ | chef informatique,chef RH,Chef accueil | +----------------------------------------+ 1 row in set (0.00 sec)


GROUP BY : Permet de regrouper des colonnes pour faire des opérations dessus (AVG, COUNT, SUM, MAX, MIN, GROUP_CONCAT). Par exemple, on a plusieurs notes pour des élèves, si on veut la moyenne pour chaque élève on pourra faire quelque chose du genre : "SELECT avg(note),nom FROM eleves GROUP BY note".



-- Affiche la moyenne générale de chaque étudiant mysql> select num_etudiant, avg(note) from evaluer -> group by evaluer.num_etudiant; +--------------+-----------+ | num_etudiant | avg(note) | +--------------+-----------+ | 1 | 10.1905 | | 2 | 8.2800 |


On peut grouper par deux champs, ci-dessous le numero d'étudiant+ le code de la matière, ce qui donne beaucoup plus de résultats :

mysql> select num_etudiant, CodeMat, avg(note) from evaluer -> group by num_etudiant, CodeMat; +--------------+---------+-----------+ | num_etudiant | CodeMat | avg(note) | +--------------+---------+-----------+ | 1 | 1 | 12.8000 | | 1 | 2 | 8.5000 | | 1 | 3 | 9.2500 | | 1 | 4 | 12.5000 | | 2 | 1 | 8.0000 | | 2 | 2 | 7.5714 | | 2 | 3 | 9.4545 | | 2 | 4 | 6.5000 |


On peut demander à la fois AVG, SUM, MAX... en utilisant le même group by (ci dessous la note max de l'élève 1 est 20, sa note min est 10) :

mysql> select * from note; +----+------------+------+----------+----------+ | id | date_note | note | id_eleve | id_cours | +----+------------+------+----------+----------+ | 1 | 2016-10-01 | 14 | 2 | 1 | | 2 | 2016-10-01 | 10 | 1 | 1 | | 3 | 2016-10-02 | 20 | 2 | 2 | | 4 | 2016-10-02 | 20 | 1 | 2 | +----+------------+------+----------+----------+ 4 rows in set (0,00 sec) mysql> select id_eleve, MAX(note), MIN(note) from note GROUP BY id_eleve; +----------+-----------+-----------+ | id_eleve | MAX(note) | MIN(note) | +----------+-----------+-----------+ | 1 | 20 | 10 | | 2 | 20 | 14 | +----------+-----------+-----------+


Un group by sans sum, avg, ou autre fonction ne sert à rien car son but est de rassembler des lignes : sans fonction il ne sait pas comment les rassembler et n'affiche que le premier résultat :


mysql> select * from testprix; +-----------+-----------+-----------+ | prixachat | prixvente | idproduit | +-----------+-----------+-----------+ | 10 | 20 | 1 | | 15 | 20 | 2 | | 18 | 28 | 3 | +-----------+-----------+-----------+ 3 rows in set (0.01 sec) mysql> select * from testprix group by prixvente; +-----------+-----------+-----------+ | prixachat | prixvente | idproduit | +-----------+-----------+-----------+ | 10 | 20 | 1 | | 18 | 28 | 3 | +-----------+-----------+-----------+ 2 rows in set (0.00 sec)

Ci-dessus, une mauvaise utilisation de group by : le produit 2 disparait car group by n'a pris que le 1er résultat.

Parfois on se sert de "group by" pour éviter les doublons (on groupe des lignes identiques donc group by fonctionne car rien à calculer) mais on peut l'éviter en utilisant "select distinct".

Attention également au cas ci-dessous, encore une mauvaise utilisation de group by :

SELECT MIN(PRIXACHAT), NOMFOURNISSEUR FROM ARTICLES JOIN ACHETER USING (NOARTICLE) JOIN FOURNISSEURS USING (NOFOURNISSEUR) GROUP BY NOARTICLE


Dans le mauvais exemple plus haut on groupe par numéro d'article et on demande le prix d'achat minimum. Jusque là tout va bien, mais on demande également le nom du fournisseur sans rien filtrer, c'est à dire que va nous être retourné le nom d'un fournisseur au hasard (très grand risque d'erreur, on peut forcer MySQL à afficher un message d'erreur avec sql_mode=ONLY_FULL_GROUP_BY). On peut utiliser "group_concat(NOMFOURNISSEUR)" pour voir s'afficher les 2 noms de fournisseurs. Dans ce genre de cas, il faudra sans doute faire une sous requête.


HAVING : HAVING filtre après qu'on ait regroupé les données avec un group by.


-- Affiche la moyenne générale de chaque étudiant supérieure à celle de la classe select avg(note) from evaluer group by evaluer.num_etudiant having avg(note)>(select avg(note) from evaluer);


Il existe beaucoup d'autres fonctions comme ROUND (arrondit), UPPER (met en majuscule, select upper(nom) from etudiant;)...

Vues et sous-requêtes


Vues


Une vue est une sorte de table temporaire (selon les SGBD elle peut s'effacer lorsqu'on éteint le PC) créée à partir d'autres tables.

create view nomdelavue as select champ1,champ2 from table_depuis_ou_on_veut_creer_la_vue;

Evidemment on peut utiliser plusieurs tables en même temps avec des jointures, des filtres... pour faire des vues plus intéressantes.


mysql> select * from testprix; +-----------+-----------+-----------+ | prixachat | prixvente | idproduit | +-----------+-----------+-----------+ | 10 | 20 | 1 | | 15 | 20 | 2 | | 18 | 28 | 3 | +-----------+-----------+-----------+ 3 rows in set (0.15 sec) mysql> create view vuetestprix as select prixvente,idproduit from testprix; Query OK, 0 rows affected (0.11 sec) mysql> select vuetestprix; ERROR 1054 (42S22): Champ 'vuetestprix' inconnu dans field list mysql> select * from vuetestprix; +-----------+-----------+ | prixvente | idproduit | +-----------+-----------+ | 20 | 1 | | 20 | 2 | | 28 | 3 | +-----------+-----------+ 3 rows in set (0.02 sec)


Une vue est intéressante pour éviter de faire des requêtes complexes plusieurs fois. Par contre, selon les SGDB, elle n'est pas forcément à jour.

Sous-requêtes


Une sous requête (ou requête imbriqué) est une requête SQL à l'intérieur d'une autre. Une première requête fournit un résultat à une deuxième qui va le traiter.

On créé donc une table, puis on va faire une requête sur la table qu'on vient de créer.

Exemple simple, on sélectionne la colonne "prix vente" de la sous requête :

mysql> select prixvente from (select prixvente, idproduit from testprix) as sousrequete; +-----------+ | prixvente | +-----------+ | 20 | | 20 | | 28 | +-----------+ 3 rows in set (0.00 sec)


Exemple plus complexe :

select prix, traversee.numero_traversee from traversee inner join liaison on code_liaison_traversee=liaison.code_liaison inner join tarif on tarif.code_liaison=liaison.code_liaison where tarif.prix=(select min(tarif.prix) from tarif where tarif.id_periode=2);


Attention, en utilisant "=" ou "<>" avant une sous-requête , il faut que la sous-requête ne renvoie qu'un seul résultat. Si on veut tester dans plusieurs résultats, il faut utiliser "in".

On peut mettre une sous requête dans le SELECT, le FROM, le JOIN ou le WHERE.

Ici on multiplie le prix HT par un taux qui vient d'une sous-requête. C'est rare de faire comme ça car le calcul s'effectuera à chaque ligne :
select prixHT*(select AVG(taux) from taux) as taux moyen

Dans un from, plus courant :
select * from client,select( * from departement where code=44) as dep44 where client.code=dep44.code

C'est encore plus courant dans un where comme vu plus haut.

Caractères de substitution


Access


Where nom like '?i*' : Le nom contient un i en deuxième lettre.

Where nom like 'd*d' : Le nom contient un d au début et un d à la fin.

MYSQL


Where nom like '_i%' : Le nom contient un i en deuxième lettre.

Where nom like 'd%d' : Le nom contient un d au début et un d à la fin (avec un * au lieu d'un % sous Access).

On peut aussi utiliser des expressions régulières :

Where nom like 'b%' est équivalent à WHERE nom regexp '^b', trouver un nom commençant par b.

– ”.” remplace n’importe quel caractère qui n’apparaît qu’une fois.

– Une classe de caractères ”[...]” remplace n’importe quel caractère qui apparaît dans les crochets. Par exemple,
”[abc]” peut remplacer ”a”, ”b” ou ”c”. Pour un intervalle de caractères, utilisez le tiret :. ”[a-z]” remplace
n’importe quelle lettre minuscule, et ”[0-9]” remplace n’importe quel nombre.

– ”*” remplace zéro ou plus occurrences du caractère le précédent immédiatement. Par exemple, ”x*” remplace
n’importe quelle nombre de ”x”. ”[0-9]*” ‘” remplace n’importe quelle nombre de chiffres, et ”.*” remplace
n’importe quelle nombre de caractères.

– Les expression régulières sont sensibles à la casse, mais vous pouvez utiliser une classe de caractères pour
les rendre insensible à la casse. Par exemple, ”[aA]” remplace n’importe quel ”a”, minuscule ou majuscule, et
”[a-zA-Z]” remplace n’importe quelle lettre, minuscule ou majuscule.

– La recherche est positive, si elle est vérifiée à n’importe quel endroit de la valeur (en SQL, ce n’est vrai que
sur la valeur entière).

– Pour contraindre une expression au début ou à la fin de la valeur, utilisez les caractères spéciaux ”ˆ” pour le
début ou ”$” pour la fin.

Exemple :
un nom qui comporte le caractere 'w' : select * from animaux where nom regexp 'w';
Un nom commençant par 'C' et se terminant par 'y' : select * from animaux where nom regexp ‘^c .*y$’;


COMMANDES MYSQL



Reconfiguration : sudo dpkg-reconfigure mysql-server-5.1

Voir toutes les colones de la table host : select * from host where (nom du champ) = "(chaine recherchée)";

Ajouter des données manuellement :
insert into (nom de la table) (liste des champs) values (valeurs dans le même ordre que les champs, séparé par une virgule, entre guillemets).

Exemple : insert into tabletest (id,nom,prenom,age) values (3,"dupont","josie","35");

Si on souhaite remplir tous les champs, il n'est pas utile de tous les préciser : insert into tabletest values (2,"arktor","bob","paris","35");

Créer un utilisateur et son mot de passe : create user toto@localhost identified by 'password';

Appliquer les modifications de privilèges (inutile avec grant, revoke ou set password) : flush privileges;

Donner tous les privilèges à un utilisateur d’administrer une bdd : grant all privileges on [database_name].* to username@localhost;

Donner le privilège affichage des données d’une table spécifique : grant select on [database_name].[table_name] to username@localhost;

Donner le privilège d’afficher les données des tables de la bdd : grant select on [database_name].* to username@localhost;

Donner le privilège d’afficher, créer, modifier et supprimer les données d’une table spécifique : grant select, insert, update, delete on [database_name]. [table_name] to username@localhost;

Donner le privilège de créer et modifier (pas supprimer) les champs des tables de la bdd : grant create, alter on [database_name].* to username@localhost;

Executer un fichier :
Depuis le bash, utiliser "mysql (nom de la base) -u (nom de l'utilisateur) -p(mot de passe) < (fichier texte)
Depuis mysql, taper "source (nom du fichier)"

Voir les infos sur les moteurs :
show engines;

Avoir des infos sur les erreurs de clefs etrangères
SHOW ENGINE INNODB STATUS;

PLSQL



A faire...

Access


Access permet :
* de créer et de gérer des bases de données comme si on utilisait un tableur.
* de créer/mettre en page des formulaires pour qu'un utilisateur puisse interroger ou modifier la base de données.
* d'un assistant pour générer des requêtes SQL sans taper une seule ligne de code.
* d'établir et de visualiser les liens entre les bases de données.

Requête



Les requêtes permettent de chercher/modifier des données en masse en utilisant des filtres.

Avec l'outil création de requêtes :
- Créer Création de requête.
- Sélectionner la table qui nous intéresse.
- Cliquer sur les champs, par exemple, nom du client, ville.
- Dans la colonne "ville", ligne "critères", marquer un critère, par exemple "Marseille".
- Dans la colonne "nom", ligne "critères", marquer "pas bob" pour exclure bob.

En tapant le SQL (utiliser "créer -> création de requète -> définition des données", ne pas confondre avec le bouton "SQL direct" qui va demander une source externe) :

SELECT Clients.Nom, Clients.Adresse, Clients.CP, Clients.Ville FROM Clients WHERE ((Not (Clients.Nom)="paul") AND ((Clients.Ville)="marseille"));


Depuis 2 tables, afficher les commandes d'avril (taper "Mois([DteCmd])='4'" dans les critères) :

SELECT Clients.Nom, Commandes.DteCmd, Commandes.EtatCmd FROM Clients INNER JOIN Commandes ON Clients.IDClient = Commandes.IDclient WHERE ((Month("DteCmd")='4'));


Requête interactives


Pour que l'utilisateur choisisse lui même la ville pour laquelle il veut lister les utilisateurs. Avec l'outil création de requêtes :
- Créer Création de requête.
- Sélectionner la table qui nous intéresse.
- Cliquer sur les champs, par exemple, nom du client, ville.
- Dans la colonne "ville", ligne "critères", marquer [Nom de la ville ?].

Une fenêtre va s'ouvrir et demander le nom de la ville.

Pour le mois : "Mois([DteCmd])=[Quel mois ?]"


SELECT Clients.Nom, Commandes.DteCmd FROM Clients INNER JOIN Commandes ON Clients.IDClient = Commandes.IDclient WHERE ((Month([DteCmd])=[Quel mois ?]));


On peut rajouter une colonne qui fera des calculs sur un autre. Pour multiplier par 2 le contenu de la colonne 1, mettre en haut de la nouvelle colonne "nomdelacolonne:[colonne1]*2". Utile pour afficher des prix hors taxes, par exemple.


Php


Nouvelle nouvelle façon :

try { $bdd = new PDO ( 'mysql:host=localhost;dbname=nomdelabase;charset=utf8', 'utilisateur', 'motdepasse'); //on peut rajouter array(PDO::ATTR_ERRMODE <big>&rArr;</big> PDO::ERRMODE_EXCEPTION) en paramètre pour afficher le détail des erreurs sql $reponse = $bdd->query ( 'select * from ETUDIANT' ); while ( $donnees = $reponse->fetch () ) { echo($donnees[1]); } $reponse->closeCursor(); } catch ( Exception $e ) { die ( 'Erreur : ' . $e->getMessage () ); }


Nouvelle façon :

<?php if($connexion = mysqli_connect('localhost', 'root', '', 'nom_de_la_base')){ $requete='select * from table'; $resultat = mysqli_query($connexion, $requete); while($champs = mysqli_fetch_assoc($resultat)){ //var_dump($champs); foreach ($champs as $enregistrement){//affiche tous les champs echo $enregistrement.','; } while($donnees = mysqli_fetch_assoc($resultat)){ echo $donnees['id']; //affiche uniquement le champ id } } mysqli_free_result($resultat); mysqli_close($connexion); } else{ echo 'Impossible de se connecter au serveur.'; } ?>



Anciennement :

//connexion au serveur $CNX=mysql_connect('localhost','root','') or die(mysql_error()); //connexion à la bdd $BDD=mysql_select_db('finances', $CNX) or die(mysql_error()); //envoyer une question $REP=mysql_query('SELECT * FROM `produits`',$CNX) or die(mysql_error()); //tant que msql récupère un tableau, le mettre dans $row while ($row = mysql_fetch_array($REP)){ //afficher $row echo print_r($row).'<hr>'; }


Exemples de requêtes


http://sqlzoo.net/wiki/SELECT_basics

Exemple


Affichage d'une table entière (table "salarié") :

SELECT * FROM salarié;


Exemple


Nom, prénom, service et poste des salariés :

SELECT salarié.nomSalarié,salarié.prénom,salarié.service,salarié.Poste FROM salarié;


Exemple


Sélectionner tous les champs, de la table absence, où le champ "codesalarié" ressemble à MARAU au début.

SELECT * FROM absence WHERE codeSalarié LIKE 'MARAU*';

'MARAU*' est la syntaxe access, en Mysql on écrirait 'MARAU%'.

Exemple


Sélectionner le champ nomSalarié (de la table salarié) et le champ codeSalarié (de la table absence), des tables absence et salarié, où le champ nomSalarié (de la table salarié) ressemble à MARAU au début ET codeSalarié (de la table absence) est la même chose que codeSalarié (de la table salarié).


SELECT salarié.nomSalarié, absence.codeSalarié FROM absence, salarié WHERE absence.codeSalarié LIKE 'MARAU*' AND absence.codeSalarié = salarié.codeSalarié;


Si on faisait :

SELECT salarié.nomSalarié, absence.codeSalarié FROM absence, salarié WHERE absence.codeSalarié LIKE 'MARAU*';

On affiche le champ codeSalarié, de la table absence, quand le champ "codesalarié" ressemble à MARAU au début. Mais on affiche aussi les noms de tous les salariés de la table salarié, autant de fois qu'on a le code MARAU dans le champ codeSalarié de la table absence.

Exemple


Nom, prénom, date de naissance des chefs de service, trié par ordre alphabétique.

Sélectionner les champs nomSalarié de la table salarié, prénom de la table salarié, dateNaissance de la table salarié, depuis la table salarié, où le champ poste de la table salarié vaut "chef de service". Trier en utilisant le champ nomSalarié de la table salarié.

SELECT salarié.nomSalarié, salarié.prénom, salarié.dateNaissance FROM salarié WHERE salarié.poste = 'chef de service' ORDER BY salarié.nomSalarié;


Exemple


Nom, prénom, des salariés qui n’ont pas de mail :

SELECT salarié.nomSalarié,salarié.prénom FROM salarié WHERE salarié.courriel is Null;

WHERE salarié.courriel='' pour mysql.

Exemple


Nombre de jours d’absences prévus par motif :

SELECT motif.LibelléMotif, motif.nbJoursPrévu FROM motif;


Exemple


Liste de motifs de congés pour chaque employé

SELECT DISTINCT salarié.nomSalarié, motif.LibelléMotif FROM salarié, absence, motif WHERE salarié.codeSalarié=absence.codeSalarié AND motif.codeMotif = absence.codeMotif ORDER BY absence.dateDebAbsence


Exemple


Liste d’absences (nom, prénom salariés, date de début et fin et nombre de jours), triés par salarié.


Pour chaque enregistrement salarié.nomSalarié,salarié.prénom, absence.dateDebAbsence, absence.dateFinAbsence, absence.nbJoursAbsence.
Pour chaque enregistrement de la table "absence.codeSalarié", on va chercher salarié dans la table codeSalarié et l'afficher.


SELECT salarié.nomSalarié,salarié.prénom, absence.dateDebAbsence, absence.dateFinAbsence, absence.nbJoursAbsence FROM salarié, absence WHERE absence.codeSalarié = salarié.codeSalarié ORDER BY salarié.nomSalarié


Exemple


8 Liste des congés payés après le 15 juin en affichant le nom et le service du salarié :

SELECT salarié.nomSalarié,salarié.service, salarié.codeSalarié, absence.dateDebAbsence, absence.codeMotif, absence.codeSalarié FROM salarié, absence WHERE month(absence.dateDebAbsence) >=6 AND day(absence.dateDebAbsence) >15 AND salarié.codeSalarié=absence.codeSalarié AND absence.codeMotif = "P01" ORDER BY absence.dateDebAbsence


Exemple


3 tables liées entre elles.

Les noms, lieu d'habitation, numéro de client,pays des personnes + toutes les infos sur les chambres classé par date:

SELECT clients.nom, clients.ville, clients.numeroClient, chambres.numeroChambre, planning.jour, chambres.* FROM clients, planning, chambres WHERE clients.numeroClient = planning.numeroClient AND chambres.numeroChambre=planning.numeroChambre ORDER BY planning.jour


Exemple


Les noms, lieux d'habitation et pays des personnes ayant occupé une chambre de 4 personnes au cours du mois d'octobre 2005.


SELECT DISTINCT clients.nom, clients.ville, clients.pays FROM clients, planning, chambres WHERE clients.numeroClient = planning.numeroClient AND chambres.numeroChambre=planning.numeroChambre AND chambres.capacite=4 AND planning.jour BETWEEN #10/01/2005# AND #10/31/2005#


Exemple


Afficher une ligne du tableau aléatoire en mysql :

SELECT * FROM animaux ORDER BY rand() LIMIT 1;


Afficher des dates de naissance


Sélectionne les champs nom et naissance (soustrait à la date d'aujourd'hui, divisé par 365 car il est exprimé en jours :

SELECT nom, (to_days(now()) - to_days(naissance))/365 FROM animaux;


Maximum et minimum dans un groupe


nomcapacitelocalisation
avion150paris
avion2250Nice
avion3250Grenoble
avion4300paris
avion580Nice

SELECT min(capacite), max(capacite),localisation FROM avion group by localisation;


Liste des villes ayant plus de 1 avion


nomlocalisation
avion1paris
avion2Nantes
avion3Grenoble
avion4paris

SELECT * FROM avion GROUP BY localisation HAVING count(localisation)>1;


Capacité moyenne des villes ayant plus de 1 avion


nomlocalisation
avion1paris
avion2Nantes
avion3Grenoble
avion4paris

SELECT AVG(capacite) FROM avion GROUP BY localisation HAVING count(localisation)>1;


Comparer 2 maximum


idavionmarque
100Airbus
101Boeing
102Airbus

VOLAvionP
IT1001001
IT1011001
IT1021002

#sélectionner les numéros des pilotes qui conduisent tous les airbus
#Autrement dit, dans la première sous requete : compte le nombre d'airbus dans la première table.
#La seconde sous-requète fait le total des avions différents par pilote.
#Enfin, on compare les deux résultats.



SELECT * FROM ( #compte tous les airbus select count(avion.idavion) as nbr FROM avion WHERE avion.marque='AIRBUS') as compteairbus, ( #compte les airbus de chaque pilote select count(distinct vol.avion) as nbrparpilote,avion.marque,vol.pilote FROM vol,avion WHERE avion.idavion=vol.avion AND avion.marque='AIRBUS' GROUP BY vol.pilote) as compteairbusparpilote HAVING compteairbus.nbr=compteairbusparpilote.nbrparpilote;


Une base de donnée n'est parfois pas édité en temps réel, il peut y avoir 2 bases. Si la temps réelle plante, l'autre ne sera pas cohérente.

Faire un "+1" sauf si la valeur est déjà au maximum de la table


On ajoute 1 à l'ordre de page_ecran là où l'id correspond à ce qu'on cherche et là où il est inférieur au maximum de la table :

update page_ecran set ordre=ordre+1 WHERE id_page=22 and ordre<(select * from (SELECT MAX(ordre) FROM page_ecran) as tableTemp);


Utiliser max avec des varchar


Dans l'exemple ci dessous, on veut supprimer les doublons tout en triant par un texte :

+----------+------------+----------------------+ | id_ecran | id_atelier | atelier | +----------+------------+----------------------+ | 3 | NULL | NULL | | 2 | 4 | Alimentation | | 4 | 5 | Confection | | 2 | 1 | Metrologie | | 2 | 2 | Module informatiques | +----------+------------+----------------------+


Solution :

select id_ecran, min(id_atelier),min(nom_atelier) as atelier from ecran LEFT join liaison_atelier_ecran using (id_ecran) LEFT join atelier using (id_atelier) GROUP BY id_ecran ORDER BY atelier



+----------+-----------------+--------------+ | id_ecran | min(id_atelier) | atelier | +----------+-----------------+--------------+ | 3 | NULL | NULL | | 2 | 1 | Alimentation | | 4 | 5 | Confection | +----------+-----------------+--------------+


Mais ce que je voulais était ça :

+----------+------------+----------------------------------------------+ | id_ecran | id_atelier | atelier | +----------+------------+----------------------------------------------+ | 6 | 4 | Alimentation | | 2 | 1 | Alimentation,Metrologie,Module informatiques | | 4 | 5 | Confection | | 5 | 6 | Engin speciaux | | 3 | 3 | Relais | +----------+------------+----------------------------------------------+


C'est à dire :

select id_ecran, id_atelier,GROUP_CONCAT(nom_atelier order by nom_atelier asc) as atelier from ecran LEFT join liaison_atelier_ecran using (id_ecran) LEFT join atelier using (id_atelier) GROUP BY id_ecran ORDER BY atelier;


Utiliser JOIN pour utiliser 2 tables



Un exemple sur http://sqlzoo.net/wiki/Music_Tutorial nous demande d'utiliser JOIN alors qu'on peut s'en passer.

Avec Join :

SELECT artist FROM album JOIN track ON album.asin = track.album WHERE track.song = 'Exodus'



Sans utiliser JOIN (il est sous entendu, c'est juste une autre syntaxe) :

SELECT artist FROM album, track WHERE track.song="Exodus" AND album.asin=track.album


A priori la première syntaxe serait meilleure.

Transactions


Transactions : plusieurs requêtes vers la BDD considérées comme un tout. Si une de ces requêtes échoue, toutes les autres requêtes sont annulées.


Exemple d’un virement bancaire :

Début de la transaction.
Retrait de 200€ au compte A.
Dépot de 200€ sur le compte B.
Fin de la transaction.

http://flylib.com/books/en/1.142.1.79/1/

Voir aussi transactions optimistes/pessimistes.

Modèles NoSQL


NoSQL est parfois interprété comme "Not Only SQL". Il désigne les SGBD créés dans les années 2010 s'écartant du modèle relationnel afin de gérer le nombre important de données lié au web.

Base de données orientée colonnes


En orienté colonnes, les données sont stockées sur le disque de la façon "nom,id;nom,id" au lieu de "nom,prénom,ville..." :
Smith:001;Jones:002,004;Johnson:003;

On voit que Jones n'est stocké qu'une seule fois et qu'il est déjà classé. Ce type de stockage peut optimiser la vitesse de requête et l'espace disque si on fait des requêtes qui s'y prêtent.

MariaDB propose un moteur orienté colonnes.

Base de données orientée documents


Les données sont stockés dans des "documents" (type JSON, XML...) qui peuvent être imbriqués les uns dans les autres, ce qui facilite la recherche et le stockage dans certains cas (les enregistrements contiennent eux mêmes des enregistrements différents : un auteur contient des livres) mais peut complexifier dans d'autres (les enregistrements peuvent se contenir eux-mêmes : un utilisateur contient des amis, eux mêmes utilisateurs qui contiennent des amis...).

Bonne explication sur MongoDB


SSSQLLLL


Des trucs qui m'énervent en SQL :
-lors de la création d'une table,pas besoin de préciser la longueur d'un INT mais il faut le faire pour un varchar.
-il n'y a pas de virgules, ni de and, ni rien entre deux join. Bon ok, yen a pas non plus entre select etfrom...
-quand on fait une contrainte de clef étrangère, la syntaxe est table(clef) au lieu de table.clef qu'on retrouve partout ailleurs...
-drop database base mais use database. Bon ok on peut drop une table et on peut pas use une table, m'enfin...
-insert into table mais update table sans rien entre update et table...
-Il faut des parenthèses pour le champ qui suit USING (USING (MONCHAMP)) alors qu'il n'en faut pas avec ON.

Liens


[http://webtic.free.fr/sql/exint/ResultReq3.php|Bons exercices sur SQL]