DUT Génie Biologique Option Bioinformatique Les bases de

DUT Génie Biologique
Option Bioinformatique
Les bases de données relationnelles
avec MySQL
Éric Pipard
Travaux Pratiques n° 1
Prise en main du SGBDR MySQL
Objectifs :
Se familiariser avec les instructions de bases de SQL en utilisant le moteur MySQL : ­ créer et gérer une base,
­ créer et gérer les tables SQL,
­ sélectionner des enregistrements.
Table des matières
Travail demandé......................................................................................................................................3
1 – Le serveur et le moteur MySQL........................................................................................................4
1.1 – Démarrage et arrêt du serveur....................................................................................................4
1.2 – Démarrage du moteur MySQL..................................................................................................4
2 – Création et gestion d'une base de données........................................................................................4
2.1 – Création d'une base de données : CREATE DATABASE <uneBase>;.....................................4
2.2 – Utilisation d'une base de données : USE <uneBase>;...............................................................4
3 – Création et gestion d'une table d'une base de données......................................................................5
3.1 – Création d'une table d'une base : CREATE TABLE <...>;........................................................5
3.2 – Description d'une table d'une base : DESC <uneTable>;..........................................................5
3.3 – Valeur NOT NULL d'une colonne.............................................................................................6
3.4 – Valeur DEFAULT d'une colonne...............................................................................................6
3.5 – Suppression d'une table d'une base : DROP TABLE <uneTable>;...........................................7
3.6 – Ajouter des données : INSERT INTO <uneTable> (...) VALUES (...);.....................................7
3.7 – Visualiser le contenu d'une table : SELECT * FROM <uneTable>;.........................................7
4 – Quelques types de données................................................................................................................8
5 – L'instruction SELECT <liste d'attributs> FROM <nom de table> WHERE <prédicat>;.................9
5.1 – Sélectionner certaines lignes.....................................................................................................9
5.2 – Sélectionner certaines lignes et certaines colonnes...................................................................9
5.3 – Sélectionner avec AND et OR...................................................................................................9
5.4 – Sélectionner avec des comparateurs..........................................................................................9
5.5 – Les valeurs NULL et le prédicat IS NULL...............................................................................9
5.6 – Le prédicat LIKE.......................................................................................................................9
5.7 – Les prédicats BETWEEN et IN...............................................................................................10
5.8 – Les prédicats NOT, NOT IN, IS NOT NULL .........................................................................10
6 – Sélectionner des enregistrements selon l'ordre alphabétique : ORDER BY....................................10
6.1 – Enregistrements ordonnés selon une colonne..........................................................................10
6.2 – Enregistrements ordonnés selon une colonne et l'ordre descendant : DESC...........................11
6.3 – Enregistrements ordonnés selon plusieurs colonnes................................................................11
7 – Quelques fonctions de colonnes.......................................................................................................11
7.1 – La fonction somme : SUM(<nom_de_colonne>).....................................................................11
7.2 – La clause : GROUP BY <nom_de_colonne>...........................................................................11
7.3 – La fonction moyenne : AVG(<nom_de_colonne>)..................................................................11
7.4 – Les fonctions : MIN(<nom_de_colonne>) et MAX(<nom_de_colonne>)..............................11
7.5 – La fonction : COUNT(<nom_de_colonne>)............................................................................11
7.6 – L'instruction : SELECT DISTINCT <nom_de_colonne>........................................................12
7.7 – Les clauses : LIMIT <nb_de_ligne> et LIMIT <nième_ligne> <nb_de_ligne>......................12
7.8 – La clause : HAVING................................................................................................................12
Annexe...................................................................................................................................................13
2
Travail demandé
1. Tout d'abord, vous devez lire attentivement les paragraphes suivants et comprendre les exemples.
2. À partir des données en annexe :
2.1. Créer une base de données nommée Liste, une table de nom mes_contacts, décrivez la table. Ne vous souciez pas pour l'instant ni des valeurs NULL, ni des valeurs de défaut.
2.2. Ajouter une colonne sexe en 4ème position ; pour ce faire vous devez supprimer la table et la recréer.
2.3. Ajouter les données se trouvant en annexe.
3. Visualiser le contenu de votre table. Que remarquez­vous ? La correction de la table se fera au second TP.
4. Donner le centre d'intérêt et ce que recherche Mr Gros.
5. Donner toutes les informations de Mme Neveu.
6. Chercher le nom et prénom des personnes qui habitent à Nice.
7. Chercher le nom et prénom des personnes qui habitent à Nice et qui soient célibataires.
8. Exécuter ces requêtes :
a) INSERT INTO mes_contacts (nom, prenom, sexe, anniversaire, profession, lieu, centres_interet,cherche) VALUES ('Souba', 'Delphine', 'F', '1986­06­25','Ingénieur', 'Grenoble, 38', 'Ski', 'Rien');
b) SELECT * FROM mes_contacts WHERE nom='Souba'; c) Que remarquez­vous ?
9. Quels sont les personnes célibataires ou mariés ? Pour quelle raison Melle Neveu n'apparait­il pas ?
10. Quels sont les personnes dont la première lettre du nom est compris entre 'A' et 'F' ?
11. Quels sont les personnes avec un email NULL ? Quels sont les personnes avec un email non NULL ?
12. Quels sont les personnes habitant Grenoble ?
13. Quels sont les personnes dont le second caractère du nom est 'e' ?
14. Quels sont les personnes nées en 1962 et 1964 ?
15. Idem que 14 mais on désire le résultat ordonné sur les prénoms.
16. Combien de personnes ont une adresse email ?
3
1 – Le serveur et le moteur MySQL
1.1 – Démarrage et arrêt du serveur
[root@aldebaran Desktop]# /etc/init.d/mysqld start
[root@aldebaran Desktop]# mysqld_safe &
[root@aldebaran Desktop]# /etc/init.d/mysqld restart
[root@aldebaran Desktop]# /etc/init.d/mysqld stop
[root@aldebaran Desktop]# mysqladmin shutdown ­p
[root@aldebaran Desktop]# /etc/init.d/mysqld status
1.2 – Démarrage du moteur MySQL
[pipard@aldebaran Desktop]$ mysql ­p ­h crick2
// ­h 194.214.236.16
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.24a­log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> 2 – Création et gestion d'une base de données
2.1 – Création d'une base de données : CREATE DATABASE <uneBase>;
Les espaces ne sont pas autorisés dans les noms de bases de données.
mysql> CREATE DATABASE ma_base;
Query OK, 1 row affected (0.38 sec)
mysql> 2.2 – Utilisation d'une base de données : USE <uneBase>;
La commande USE permet de charger une base et de travailler sur celle­ci.
mysql> USE ma_base;
Database changed
mysql> 4
3 – Création et gestion d'une table d'une base de données
3.1 – Création d'une table d'une base : CREATE TABLE <...>;
Les espaces ne sont pas autorisés dans les noms de tables.
La commande CREATE TABLE de création d'une table prend en paramètre le nom de la table ainsi que la liste des colonnes à créer. Cette liste comprend le nom et le type des données.
mysql> CREATE TABLE uneTable
­> (
­> nom VARCHAR(30),
­> anniversaire DATE,
­> courriel VARCHAR(50)
­> );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE uneTable
­> (
­> nom VARCHAR(30) NOT NULL,
­> anniversaire DATE NOT NULL,
­> courriel VARCHAR(50)
­> );
Query OK, 0 rows affected (0.06 sec)
// NULL est interdite comme valeur
// NULL est interdite comme valeur
mysql> Classez vos données par catégorie (nom de colonne) avant de créer une table; déterminer bien le type des données de chaque colonne.
3.2 – Description d'une table d'une base : DESC <uneTable>;
5
3.3 – Valeur NOT NULL d'une colonne
mysql> CREATE TABLE uneAutreTable
­> (
­> nom VARCHAR(10) NOT NULL,
­> prenom VARCHAR(10) NOT NULL,
­> age INT
­> );
Query OK, 0 rows affected (0.02 sec)
mysql>
3.4 – Valeur DEFAULT d'une colonne
mysql> CREATE TABLE uneAutreTable
­> (
­> nom VARCHAR(10) NOT NULL,
­> prenom VARCHAR(10) NOT NULL,
­> age INT DEFAULT 39);
6
3.5 – Suppression d'une table d'une base : DROP TABLE <uneTable>;
DROP TABLE supprime une table vide ou avec des données.
mysql> DROP TABLE uneTable;
Query OK, 0 rows affected (0.00 sec)
mysql> DESC uneTable;
ERROR 1146 (42S02): Table 'ma_base.uneTable' doesn't exist
mysql> 3.6 – Ajouter des données : INSERT INTO <uneTable> (...) VALUES (...);
Les valeurs doivent être dans le même ordre que les noms de colonnes spécifiés. Les noms de colonnes ne suivent pas nécessairement l'ordre des colonnes donnés à la création de la table. Tous les noms des colonnes peuvent être ommis ; dans ce cas, toutes les valeurs des colonnes doivent être spécifiées dans l'ordre des colonnes données lors de la création de la table. Des noms de colonnes peuvent être ommis ; dans ce cas, les valeurs correspondantes sont à NULL ou à une valeur de DEFAULT précisée lors de la création de la table. mysql> INSERT INTO uneTable
­> (courriel, anniversaire, nom)
­> VALUES
­> ('[email protected]', '1959­27­06', 'Pipard');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO uneTable
// omission des noms de colonne
­> VALUES
­> ('[email protected]', '1959­27­06', 'Pipard');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO uneTable
­> (nom)
­> VALUES
­> ('Toto');
Query OK, 1 row affected (0.00 sec)
// omission de certaines colonnes
mysql> Une colonne sans valeur assignée par INSERT contient NULL.
3.7 – Visualiser le contenu d'une table : SELECT * FROM <uneTable>;
Permet de sélectionner toutes (*) des données de la table.
7
mysql> INSERT INTO uneAutreTable
­> (nom, prenom)
­> VALUES
­> ('rt', 'uty');
Query OK, 1 row affected (0.00 sec)
4 – Quelques types de données
VARCHAR(n)
CHAR (n)
BLOB
DATE
TIME
DATETIME
DEC(n, p)
INT
'azerty'
'aerty'
'gdhjdhsskjdkd'
'YYYY­MM­DD'
'HH:MM:SS'
DATE+TIME
3.5
17
chaîne longueur variable
chaîne longueur fixe
objet binaire
255 caractères
255 caractères
65535
8
5 – L'instruction SELECT <liste d'attributs> FROM <nom de table> WHERE <prédicat>;
5.1 – Sélectionner certaines lignes
mysql> SELECT * FROM uneTable
­> WHERE age = 39;
5.2 – Sélectionner certaines lignes et certaines colonnes
mysql> SELECT nom, prenom FROM uneTable
­> WHERE age = 39;
5.3 – Sélectionner avec AND et OR
mysql> SELECT nom, prenom FROM uneTable
­> WHERE age = 39 AND nom = 'Pipard';
mysql> SELECT nom, prenom FROM uneTable
­> WHERE age = 39 OR nom = 'Pipard';
5.4 – Sélectionner avec des comparateurs
Les comparateurs permettent des comparaisons numérique et alphabétique.
mysql> SELECT nom, prenom FROM uneTable
­> WHERE age > 39 OR nom = 'Pipard';
Liste des comparateurs : =, <>, <, >, <=, >=
mysql> SELECT nom, prenom FROM uneTable
­> WHERE age > 39 OR nom < 'P';
5.5 – Les valeurs NULL et le prédicat IS NULL
Les valeurs NULL correspondent à des valeurs indéfinies; elles ne peuvent pas être sélectionnées directement.
mysql> SELECT nom, prenom FROM uneTable
­> WHERE nom IS NULL;
5.6 – Le prédicat LIKE
Le prédicat LIKE permet de comparer avec des sous­chaînes de caractères; il est utilisé avec deux caractères joker :
­ % pour substituer n'importe quelle chaîne,
­ _ pour substituer un unique caractère.
mysql> SELECT * FROM uneTable
­> WHERE ville LIKE 'Saint%';
9
mysql> SELECT * FROM uneTable
­> WHERE ville LIKE '%Saint%';
mysql> SELECT * FROM uneTable
­> WHERE ville LIKE 'Saint_Malo';
5.7 – Les prédicats BETWEEN et IN
Le prédicat BETWEEN permet de sélectionner sur une plage de valeurs; les bornes sont comprises pour les entiers et exclues pour les caractères ; les bornes sont lues de la gauche vers la droite ; ainsi BETWEEN 30 AND 20 retournera rien.
Le prédicat IN permet d'énumérer les valeurs d'une plage.
mysql> SELECT * FROM uneTable
­> WHERE age BETWEEN 40 AND 50;
mysql> SELECT * FROM uneTable
­> WHERE ville BETWEEN 'D' AND 'G';
mysql> SELECT * FROM uneTable
­> WHERE ville IN ('D' , 'G');
5.8 – Les prédicats NOT, NOT IN, IS NOT NULL Le prédicat NOT exprime la négation. Il s'écrit juste après WHERE.
mysql> SELECT * FROM uneTable
­> WHERE NOT age BETWEEN 40 AND 50;
mysql> SELECT * FROM uneTable
­> WHERE ville NOT IN ('D' , 'G');
mysql> SELECT * FROM uneTable
­> WHERE ville IS NOT NULL;
6 – Sélectionner des enregistrements selon l'ordre alphabétique : ORDER BY
Le classement est effectué selon l'ordre ascendant sur les valeurs d'une (des) colonne(s) donnée(s). L'ordre descendant est indiqué par l'instruction DESC.
6.1 – Enregistrements ordonnés selon une colonne
mysql> SELECT titre, genre
­> FROM uneTable
­> WHERE genre = 'familial'
­> ORDER BY titre;
10
6.2 – Enregistrements ordonnés selon une colonne et l'ordre descendant : DESC
mysql> SELECT titre, genre
­> FROM uneTable
­> WHERE genre = 'familial'
­> ORDER BY titre DESC;
6.3 – Enregistrements ordonnés selon plusieurs colonnes
Les colonnes sont triées dans l'ordre où elles apparaissent dans la clause ORDER BY.
mysql> SELECT titre, genre, achat
­> FROM uneTable
­> ORDER BY genre, achat;
7 – Quelques fonctions de colonnes
7.1 – La fonction somme : SUM(<nom_de_colonne>)
Cette fonction somme toutes les valeurs d'une colonne.
mysql> SELECT SUM(vente)
­> FROM uneTable
­> WHERE nom = 'Pipard';
7.2 – La clause : GROUP BY <nom_de_colonne>
GROUP BY permet de regrouper les résultat par rapport à une colonne donnée; élimine les duplicata.
mysql> SELECT nom, SUM(vente)
­> FROM uneTable
­> GROUP BY nom;
7.3 – La fonction moyenne : AVG(<nom_de_colonne>)
La moyenne se calcule par rapport aux nombres de lignes d'une colonne donnée.
mysql> SELECT nom, AVG(vente)
­> FROM uneTable
­> GROUP BY nom;
7.4 – Les fonctions : MIN(<nom_de_colonne>) et MAX(<nom_de_colonne>)
Calculent la plus petite et la plus grande valeur d'une colonne donnée.
mysql> SELECT nom, MAX(vente)
­> FROM uneTable
­> GROUP BY nom;
7.5 – La fonction : COUNT(<nom_de_colonne>)
Calcule le nombre de lignes d'une colonne donnée sans prendre en compte les valeurs nulles.
mysql> SELECT COUNT(date_vente)
­> FROM uneTable;
11
7.6 – L'instruction : SELECT DISTINCT <nom_de_colonne>
Cette fonction sélectionne des valeurs distinctes d'une colonne donnée (donc élimine les duplicata).
mysql> SELECT DISTINCT date_vente
­> FROM uneTable;
mysql> SELECT COUNT(DISTINCT date_vente)
­> FROM uneTable;
7.7 – Les clauses : LIMIT <nb_de_ligne> et LIMIT <nième_ligne> <nb_de_ligne>
LIMIT <nb_de_ligne> limite le résultat d'une requête à un nombre de lignes nb_de_ligne en commençant par les premières.
mysql> SELECT nom, SUM(vente)
­> FROM uneTable
­> GROUP BY nom
­> ORDER BY SUM(vente) DESC
­> LIMIT 2;
LIMIT <nième_ligne> <nb_de_ligne> limite le résultat d'une requête à un nombre de lignes nb_de_ligne en commençant par la nième (la numérotation des lignes commence à 0).
mysql> SELECT nom, SUM(vente)
­> FROM uneTable
­> GROUP BY nom
­> ORDER BY SUM(vente) DESC
­> LIMIT 12, 2;
7.8 – La clause : HAVING
La clause HAVING spécifie un critère de recherche pour un groupe ou une fonction d'agrégation (SUM, AVG, ...). mysql> SELECT nom, SUM(vente)
­> FROM uneTable
­> GROUP BY nom
­> HAVING SUM(vente) > 123;
12
Annexe
nom
Neveu
Gros
Souk
Mendoza
prenom
Anne
Etienne
Alain
Lucie
email
anne@
nessayepas@
souk@
lucie@
planchesagogo.com pizzaprestissima.com pizzaprestissima.com cafestarbuzz.com
anniversaire
7­1­1962
10­9­1964
1­7­1966
19­8­1979
profession
Ingénieur Informatique
Administrateur Système
Ingénieur Aéronautique
Administrateur Système Unix
lieu
Grenoble, 38
Nice, 06
Orléans, 45
Nice, 06
statut
Célibataire mais à un copain
Célibataire
Marié
Marié
centres_interet
Collection de livres, fabrication de la bière, équitation
cherche
Nouveau boulot
Randonnée, écriture Java, programmation
amis, rdv
Rien
Théâtre, danse
Nouveau boulot
13