Bases 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 (organisation hiérarchique, en objet, en étoile...), ici, on parlera des bases de données relationnelles (les plus utilisées sauf pour les projets avec de gros moyens et énormément de données où les bases de type NoSQL seront préférées).
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 une BDD à 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 vraiment 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) | Nom | Adresse | Téléphone
|
|---|
| KL11527B | Bob | 11 rue de la bière | 05456514
|
| JB22238C | Bill | 35 avenue des oiseaux | 0976342
|
Des index 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 (index couvrant). Des index sont mis automatiquement sur les clefs primaires. Il est recommandé de les mettre sur les clefs étrangères souvent sollicitées (par exemple dans ta table "commande", on mettra un index sur la FK idclient).
create index mon_index ON ma_table(ma_colonne)
On parle aussi de "relation" pour désigner une table. En fait, il parait que ce n'est pas "relation" dans le sens de "lien" mais dans le sens où la table
relate quelque chose.
Une opération sur les données s'appelle une
transaction. La plupart des SGBD veulent rendre cette transaction ACID, c'est à dire respectueuse de l'
Atomicité (la transaction forme un bloc indivisible, impossible de n'en faire qu'une partie), de la
Cohérence (la base doit rester cohérente une fois la transaction finie), Isolation (cette transaction ne doit dépendre d'aucune autre) et
Durabilité (modifications sur les données enregistrée de façon permanente).
SQL
Un langage quasi-universel pour l'exploitation des bases. 3 types de commandes : de définition de données (agissant sur la structure de la base - DDL, Data Definition Language), commandes de manipulation de données (exploitation de la base en modifiant les données -DML, Data Manipulation Language) et d'administration des utilisateurs (qui a quelles autorisations pour faire quoi dans la base, DCL Data Control Language).
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 principales : 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).
Lock : verrouille des données (utilisé par exemple si on fait une maintenance rapide sur la base).
On met parfois aussi ici les commandes
commit (demander l'exécution d'une transaction) et
rollback (si un commit n'a pas été fait).
SET AUTOCOMMIT=0;
SET SQL_SAFE_UPDATES = 0;
START TRANSACTION;
DELETE FROM COMMERCIAUX
WHERE COMMISSION>3500;
Notez qu'on a pas mis "commit", donc on peut faire :
ROLLBACK
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;
Supprimer une table temporaire :drop temporary table TMP_VOITURE;
Modifier le nom d'une table : RENAME TABLE old_table TO new_table;
Ajouter un champ/colonne à une table: alter table employe add nom_employe varchar(64);
Changer un nom de colonne : alter table ma_table change ancien_nom nouveauNom int(10);
Modifier le type d'une colonne : alter table maTable modify maColonne int(11);
Supprimer un champ/colonne : alter table ma_table drop column ma_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);
Supprimer une clef étrangère sur une table :
ALTER TABLE ma_table DROP FOREIGN KEY nom_de_la_contrainte;
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. Moins à stocker mais oblige le SGBD à calculer la taille donc prend du CPU.
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. Les contraintes se mettent à fin.
Autre exemple avec deux contraintes (ci-dessous c'est un peu exagéré de nommer la contrainte d'unicité) :
CREATE TABLE INTERNAUTE (
id INT AUTO_INCREMENT NOT NULL,
nom VARCHAR(40),
age INT,
adresse VARCHAR(500),
CSP VARCHAR(50),
CONSTRAINT PKinternaute PRIMARY KEY (id),
CONSTRAINT UNage UNIQUE(age) -- deux internautes ne pourront pas avoir le même âge
);
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 :
Table B :
| Numéro catégorie | Nom catégorie | Id employé (clef étrangère)
|
|---|
| 3 | Chef Informatique | 1
|
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)
On peut mettre aussi le distinct dans un count :
SELECT COUNT(DISTINCT mon_champ)
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. En SQL Oracle :
BETWEEN TO_DATE('05-AUG-18 18:00', 'DD-MM-YY SS:MI') AND TO_DATE('06-AUG-18 17:00', 'DD-MM-YY SS:MI')
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*". Voir plus bas "caractères de substitution". On peut aussi concaténer like avec des fonctions, par exemple
SELECT capital,name FROM world WHERE capital LIKE concat('%', name, '%');
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 : Renvoie TRUE si un résultat existe, FALSE sinon. A utiliser avec une sous requête en général.
Dans l'exemple ci-dessous on parcourt la table et pour chaque employé on vérifie qu'il ne sort pas de la requête
select * from PARTICIPER where PARTICIPER.NUMEMP = EMPLOYE.NUMEMP (attention, ce qui est perturbant est qu'on se sert de "EMPLOYE.NUMEM" alors qu'EMPLOYE est en dehors de la parenthèse -on appelle ça une requête synchronisée) :
SELECT NOMEMP FROM EMPLOYE WHERE
NOT EXISTS (select * from PARTICIPER where PARTICIPER.NUMEMP = EMPLOYE.NUMEMP)
Pour éviter que la sous-requête ne soit dépendante de la première, on peut ajouter employe comme ceci, mais apparemment on perd en performances :
SELECT NOMEMP FROM EMPLOYE WHERE
NOT EXISTS (select * from PARTICIPER,EMPLOYE where PARTICIPER.NUMEMP = EMPLOYE.NUMEMP)
"Cherche moi le nom dans la table employé qui n'existe pas dans la table participer.
Le même résultat avec un IN :
SELECT NOMEMP FROM EMPLOYE WHERE NUMEMP
NOT IN (select NUMEMP from PARTICIPER)
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. On peut aussi utiliser offset :
SELECT * FROM base_exemple.EMPLOYE ORDER BY SALAIRE DESC LIMIT 10 OFFSET 9
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")
UNION : Pour mettre deux tables l'une derrière l'autre.
Ci-dessous on aura une seule colonne avec les prénoms en haut et les noms en bas :
SELECT PRENOMEMP FROM EMPLOYE
UNION
SELECT NOMEMP FROM EMPLOYE
Autre exemple, la somme de commission par pays ET genre puis que par pays, que par genre et enfin somme totale :
select SUM(COMMISSION), PAYS, GENRE from COMMERCIAUX
GROUP BY PAYS, GENRE
UNION
select SUM(COMMISSION),PAYS,"N/A" from COMMERCIAUX
GROUP BY PAYS
UNION
select SUM(COMMISSION),GENRE,"N/A" from COMMERCIAUX
GROUP BY GENRE
UNION
select SUM(COMMISSION),"N/A","N/A" from COMMERCIAUX
+-----------------+--------+-------+
| SUM(COMMISSION) | PAYS | GENRE |
+-----------------+--------+-------+
| 4000 | FRANCE | Femme |
| 8000 | FRANCE | Homme |
| 5000 | UK | Femme |
| 11500 | UK | Homme |
| 12500 | USA | Femme |
| 10000 | USA | Homme |
| 12000 | FRANCE | N/A |
| 16500 | UK | N/A |
| 22500 | USA | N/A |
| 21500 | Femme | N/A |
| 29500 | Homme | N/A |
| 51000 | N/A | N/A |
+-----------------+--------+-------+
Copier une colonne dans une autre (oracle sql) :
update MA_TABLE set MA_COLONNE = MA_COLONNE_A_COPIER;
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)
Si on fait un "from A left join B", B va venir "compléter" les infos de la table A. Si B a moins de lignes, des NULL viendront compléter.
Ci-dessous, il y a moins de lignes dans la table B que dans la table A :
select * from A left join B on identifiantA=recup_identifiantA;
+--------------+--------------+--------------------+
| identifiantA | identifiantB | recup_identifiantA |
+--------------+--------------+--------------------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
| 6 | 6 | 6 |
| 7 | 7 | 7 |
| 8 | 8 | 8 |
| 9 | NULL | NULL |
| 10 | NULL | NULL |
| 11 | NULL | NULL |
| 12 | NULL | NULL |
| 13 | NULL | NULL |
| 14 | NULL | NULL |
| 15 | NULL | NULL |
Si on retrouve plusieurs fois la même clef étrangère du côté de B, les lignes de A seront répétées autant de fois.
Ci-dessous, la table B a été changée par rapport à l'exemple plus haut. Il n'y a en réalité qu'un seul "1" dans la table A mais la ligne est répétée car "1" est trouvée plusieurs fois dans la table B :
select * from A left join B on identifiantA=recup_identifiantA;
+--------------+--------------+--------------------+
| identifiantA | identifiantB | recup_identifiantA |
+--------------+--------------+--------------------+
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 1 |
| 1 | 4 | 1 |
| 1 | 5 | 1 |
| 2 | 6 | 2 |
| 2 | 7 | 2 |
| 2 | 8 | 2 |
| 3 | NULL | NULL |
| 4 | NULL | NULL |
| 5 | NULL | NULL |
| 6 | NULL | NULL |
| 7 | NULL | NULL |
| 8 | NULL | NULL |
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.
Theta jointure
Jointure d'une table sur elle même, pas jointe pas un égal mais avec un supérieur à par exmeple. On aura ainsi un résultat avec 2 tables collées ensemble, avec des lignes face à face n'ayant pas de rapport. Utile pour comparer deux tables visuellement par exemple.
Alias
Deux types d'alias : alias de colonne et alias de table.
Le premier est simple :
select NOMEMP as nom_employe from EMPLOYE;
+-------------+
| nom_employe |
+-------------+
| DUPONT |
| JOLIBOIS |
| BEAUMONT |
+-------------+
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.
CROSS JOIN
Permet de faire un produit cartésien, càd afficher toutes les possibilités entre plusieurs colonnes (aaa,aab,aac,aad,aae,aaf...).
Si on met une condition de jointure (un "ON"), il se comportera comme un "INNER JOIN".
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. Si on met une étoile, compte toute la ligne. S'il est fait avec un group by, ça comptera par group by.
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... impérativement à mettre !). 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 |f
| 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 |
Autre exemple :
select SUM(COMMISSION), PAYS, GENRE from COMMERCIAUX
GROUP BY PAYS, GENRE
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 |
+----------+-----------+-----------+
Avec un COUNT :
SELECT EMPLOYE.NOMEMP, EMPLOYE.PRENOMEMP, COUNT(*) FROM base_exemple.INSCRIT
JOIN EMPLOYE on EMPLOYE.NUMEMP=INSCRIT.NUMEMP
group by EMPLOYE.NOMEMP,EMPLOYE.PRENOMEMP;
+----------+-------------+----------+
| NOMEMP | PRENOMEMP | COUNT(*) |
+----------+-------------+----------+
| ANTHONY | Henri | 3 |
| BEAUMONT | Jean | 3 |
| BERNARDI | Patrick | 4 |
| BEUGNIES | Maurice | 4 |
| CANE | Michel | 3 |
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.
Autre exemple :
SELECT NOMEMP, PRENOMEMP, PROJET.NOMPROJET, count(EMPLOYE.CODEPROJET) as NombreProjet FROM base_exemple.EMPLOYE
LEFT JOIN PROJET on PROJET.CODEPROJET=EMPLOYE.CODEPROJET
GROUP BY NOMEMP -- , PRENOMEMP, PROJET.NOMPROJET
Affichera :
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'base_exemple.EMPLOYE.PRENOMEMP' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Il faut ajouter l'expression #2 dont parle le message d'erreur : PRENOMEMP
Autre exemple qui provoque une erreur :
SELECT CLIENTS.code_client, CLIENTS.nom, CLIENTS.prenom, COUNT(LOCATIONS.num_dvd) as nombre FROM CLIENTS
LEFT JOIN FACTURES on FACTURES.code_client=CLIENTS.code_client
LEFT JOIN LOCATIONS on LOCATIONS.num_facture=FACTURES.num_facture
GROUP BY CLIENTS.nom
HAVING nombre=0
ORDER BY nombre DESC;
On demande d'afficher le code client, groupé par nom de client. Or, il peut y avoir deux noms de client identique avec un code client différent, c'est donc impossible ! Il faut mettre
GROUP BY CLIENTS.nom, CLIENTS.code_client par exemple.
L'option
WITH ROLLUP est très pratique car elle fait un tri et affiche des sous-totaux.
HAVING : HAVING filtre après qu'on ait regroupé les données avec un group by. Sorte de WHERE pour le 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);
REPLACE : dans telle chaîne, remplacer telle chaîne par telle chaîne.
SELECT name, REPLACE(capital, name,'') as ext
FROM world WHERE capital LIKE concat('%', name, '_%');
Affichera :
name ext
Andorra la Vella
Guatemala City
Kuwait City
Mexico City
Monaco -Ville
Panama City
CAST : permet de changer un type de données par un autre, par exemple un double en int ou un datetime en time. La fonction n'est pas très complète en mysql.
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 virtuelle, éventuellement temporaire (selon les SGBD elle peut s'effacer lorsqu'on éteint le PC) créée à partir d'autres tables.
Sous MYSQL elle n'est pas temporaire et mise à jour en permanence. Si on met à jour des données dans la vue, ces données seront mises à jour dans les tables dont les données proviennent.
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.
A noter qu'on peut créer des tables temporaires, qui ne seront visibles que par l'utilisateur durant le temps de sa session :
create temporary table TMP_VOITURE (
id INT UNSIGNED PRIMARY KEY,
marque varchar(30),
annee_production DATE,
modele VARCHAR(30)
);
describe TMP_VOITURE;
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. Préférer des jointures si possibles, elles sont plus rapides.
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".f
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.
Un exemple de sous-requète qui remplace un join :
SELECT SUM(SALAIRE) FROM EMPLOYE
WHERE EMPLOYE.NUMEMP IN
(SELECT NUMEMP FROM INSCRIT)
Une sous requete pour afficher le pourcentage de salaire en face de l'employé :
SELECT NOMEMP, SALAIRE, SALAIRE * 100 / (
select sum(SALAIRE) FROM EMPLOYE
) AS '% of total'
FROM EMPLOYE
Résultat :
+----------+----------+------------+
| NOMEMP | SALAIRE | % of total |
+----------+----------+------------+
| DUPONT | 8000.00 | 2.729444 |
| JOLIBOIS | 10500.00 | 3.582395 |
| BEAUMONT | 12000.00 | 4.094166 |
| DUCHATEL | 11500.00 | 3.923576 |
On peut aussi utiliser des sous requètes pour remplir ou créer des tables :
CREATE TABLE ma_table
AS (SELECT * from mon_autre_table);
Ca marche également avec SQL Server d'Oracle, ci-dessous on récupère le max de la table en cours en tant qu'id, on récupère un ID d'une autre table en tant que valeur et on met aussi une valeur manuelle :
INSERT INTO MA_BELLE_TABLE (MON_CHAMP1, MON_CHAMP2, MON_CHAMP3)
VALUES (
(select max(MA_BELLE_TABLE.MON_CHAMP1)+1 from MA_BELLE_TABLE),
(select AUTRE_TABLE.MON_CHAMP2 from AUTRE_TABLE WHERE AUTRE_TABLE.CEN_NOM = 'COCHEH'),
'VALEUR_MANUELLE',
);
On peut aussi faire comme ça, il n'y a plus le mot clef "VALUES" :
INSERT INTO CLIENTS_LOIRE_ATLANTIQUE
SELECT CLIENTS.code_client,CLIENTS.titre,CLIENTS.nom, CLIENTS.prenom, YEAR(CURDATE())-YEAR(CLIENTS.date_naissance) FROM CLIENTS
WHERE CLIENTS.code_postal LIKE "44%";
Avec la technique ci-dessus on peut mixer des données récupérées, même dans plusieurs tables.
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;
Attention, les tables étant stockées comme des fichiers dans linux (/var/lib/mysql/ma_base), la casse sera prise en compte.
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 => 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
Afficher l'employé avec le plus bas salaire:
SELECT NOMEMP, MIN(SALAIRE) as s FROM EMPLOYE
GROUP BY NOMEMP
order by s
limit 1
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
| nom | capacite | localisation
|
|---|
| avion1 | 50 | paris
|
| avion2 | 250 | Nice
|
| avion3 | 250 | Grenoble
|
| avion4 | 300 | paris
|
| avion5 | 80 | Nice
|
SELECT min(capacite), max(capacite),localisation
FROM avion
group by localisation;
Liste des villes ayant plus de 1 avion
| nom | localisation
|
|---|
| avion1 | paris
|
| avion2 | Nantes
|
| avion3 | Grenoble
|
| avion4 | paris
|
SELECT *
FROM avion
GROUP BY localisation
HAVING count(localisation)>1;
Capacité moyenne des villes ayant plus de 1 avion
| nom | localisation
|
|---|
| avion1 | paris
|
| avion2 | Nantes
|
| avion3 | Grenoble
|
| avion4 | paris
|
SELECT AVG(capacite)
FROM avion
GROUP BY localisation
HAVING count(localisation)>1;
Comparer 2 maximum
| idavion | marque
|
|---|
| 100 | Airbus
|
| 101 | Boeing
|
| 102 | Airbus
|
| VOL | Avion | P |
|---|
| IT100 | 100 | 1
|
| IT101 | 100 | 1
|
| IT102 | 100 | 2
|
#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.
Insérer si les données n'existent pas
Avec Oracle SQL Server, on veut insérer 'NOUVELLE_VALEUR' dans 'MA_TABLE' seulement si 'NOUVELLE_VALEUR' n'existe pas.
MERGE INTO MA_TABLE T1
USING (
SELECT max(ID)+1 as ID, 'NOUVELLE_VALEUR'as MA_VALEUR FROM MA_TABLE
) T2
ON (T1.MA_VALEUR = T2.MA_VALEUR)
WHEN NOT MATCHED THEN
insert (ID, CON_PARAM);
Ajouter une colonne si elle n'existe pas
Oracle SQL Server :
DECLARE
v_column_exists number := 0;
BEGIN
Select count(*) into v_column_exists
from user_tab_cols
where upper(column_name) = 'LA_COLONNE_A_AJOUTER'
and upper(table_name) = 'LA_TABLE_DE_LA_COLONNE_A_AJOUTER';
--and owner = 'SCOTT' --*peut avoir besoin de ça si on utilise "all/dba views" (?)
if (v_column_exists = 0) then
execute immediate 'alter table LA_TABLE_DE_LA_COLONNE_A_AJOUTER add LA_COLONNE_A_AJOUTER number';
end if;
end;
/
Supprimer toutes les tables sous Oracle SQL Server
Ce script génèrera un autre script qu'on pourra utiliser pour supprimer les tables :
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;
Génèrera quelque chose comme :
DROP TABLE "MABELLETABLE1" CASCADE CONSTRAINTS;
DROP TABLE "MABELLETABLE2" CASCADE CONSTRAINTS;
DROP TABLE "MABELLETABLE3" CASCADE CONSTRAINTS;
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.
Différences entre les SGBD
Access : vieillissant, à éviter ou pour de toutes petites bases.
MySQL : différencie les utilisateurs selon l'hôte depuis lequel ils se connectent. N'utilise pas de séquence mais des "auto increment". Un schema désigne la structure de la BDD (un seul schema par BDD donc).
Oracle SQL Server : un "schema" désigne un utilisateur et ce qu'il peut voir dans la BDD (plusieurs schema par BDD). Pas d'auto increment mais un système de sequence : chaque colonne peut avoir un indice que l'on va incrémenter à l'envie.
Reset mdp Oracle expiré
https://hecpv.wordpress.com/2014/10/16/how-to-solve-ora-28001-the-password-has-expired/
SQL> ALTER USER monBelUser IDENTIFIED BY monBeauPassword;
ALTER USER monBelUser IDENTIFIED BY monBeauPassword
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers //faut le faire en sysdba http://dbacentre.blogspot.com/2015/01/ora-65066-specified-changes-must-apply.html
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> ALTER USER monBelUser IDENTIFIED BY monBeauPassword;
User altered.
SQL> ALTER USER system ACCOUNT UNLOCK;
User altered.
SQL> exit
Importer un dump Oracle
Un dump est un fichier .dmp créé à partir d'un utilitaire oracle type
exp ou
expdp. Il contient une partie ou toute les informations d'une BDD.
Ne pas le confondre avec la commande export de Oracle SQL Developper qui va générer un ficher .sql avec les instructions SQL pour construire la BDD : un fichier .dmp ne sera pas lisible ouvert avec notepad. Un dump est également beaucoup plus petit qu'un fichier .sql (d'expérience, 2,5go contre 180mo...).
Il y a 2 grands types pour les dumps : ceux créés avec l'ancien programme exp et ceux créés avec le nouveau expdp (dp signifiant "data pump"). Pour les différencier, il faut lancer les programmes "imp" ou "impdp" ((dans /opt/oracle/product/12.1.0.2/dbhome_1/bin/)) pour les décompresser et voir avec quoi ils marchent.
Si le fichier est d'un ancien type, càd créé avec "exp", il faut utiliser le programme "imp" :
#si le dump est d'un ancien type, mieux vaut supprimer l'user en se reco au CLI mysql sinon on va nous dire que les objets existent déjà
drop user monBeauUser cascade;
#toujours dans le CLI mysql, recréer l'user
create user monBeauUser identified by monBeauUser DEFAULT TABLESPACE "TBS_monBeauUser_DAT" QUOTA 10G ON "TBS_monBeauUser_DAT" QUOTA 10G ON "TBS_monBeauUser_IDX" TEMPORARY TABLESPACE TEMP;
#retour dans le CLI mysql, donner les droits à l'user
#GRANT CONNECT, RESOURCE TO "monBeauUser";
#GRANT DEBUG CONNECT SESSION, DEBUG ANY PROCEDURE TO "monBeauUser";
#GRANT CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, #CREATE VIEW, CREATE DATABASE LINK TO "monBeauUser";
#ou bien juste
grant all privileges to monBeauUser identified by monBeauUser;
#quitter sqplus, return dans le shell bash par ex, importer le dump
imp system/manager@MABELLEBASE file=/MonBeauDump.dmp log=/MonBeauDump.log fromuser=monBeauUser touser=monBeauUser
Si le fichier a été créé avec "expdp", il faut utiliser le programme "impdp" :
impdp system/manager@D12UTF8BLAHBLAH directory=dump_dir dumpfile=mon_fichier_de_dump.dmp logfile=mon_fichier_de_dump.log schemas=MON_SCHEMA
Si vous utilisez docker, bizarrement vous n'aurez pas les mêmes résultats avec
docker exec -ti mon_beau_docker sqlplus user_admin_sql/mot_de_passe_sql@D12P15PDB1 et en vous connectant dans le docker en bash PUIS à sqlplus.
En cas d'erreur ORA-01658 et erreur sur un tablespace dans les logs, il faut mettre kes tablespace en autoextend :
#chercher l'adresse du ou des tables spaces en erreur
SELECT * FROM DBA_DATA_FILES;
ALTER DATABASE
DATAFILE '/adresse/du/tablespace/TBS_monBeauUser_DAT.dat'
AUTOEXTEND ON
MAXSIZE UNLIMITED;
Data Pump
Il existe un utilitaire très pratique dans SQL developper pour faire un export/import datapump :
-cliquer sur "view" puis "dba".
-dans la petite fenêtre qui s'ouvre, ajoutez la connexion vers votre schema.
-puis dépliez les dossiers jusqu'à Data Pump.
-clic droit dessus puis datapump export wizard.
Plusieurs erreurs sont alors possibles :
-l'utilisateur n'a pas les droits de Database Administrator (DBA). Se connecter avec un admin à un cli sql et
grant DBA TO UTILISATEUR_QUI_VEUT_LES_DROITS;.
-Il n'y a aucun dossier pour exporter les logs. Soit le dossier n'existe pas, soit l'utilisateur n'a pas les droits pour le voir.
Le dossier qui nous intéresse est DATA_PUMP_DIR :
SELECT directory_path FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';
Pour le créer, se connecter avec un admin au cli sql et :
conn / as sysdba
DROP DIRECTORY DATA_PUMP_DIR;
CREATE DIRECTORY DATA_PUMP_DIR as '/dossier/ou/mettre/les/dump';
Sortir, revenir sur le cli puis :
GRANT read,write ON DIRECTORY DATA_PUMP_DIR TO UTILISATEUR_QUI_VEUT_LES_DROITS;
Vous aurez certainement besoin de vous déco/reco de SQL developpeur pour récupérer les nouveaux droits.
Divers
Voir les utilisateurs de sa BDD Oracle :
select username from dba_users;
Liens
[http://webtic.free.fr/sql/exint/ResultReq3.php|Bons exercices sur SQL]