105.3 - Gérer des données avec SQL (4/60)

Les chaînes de caractères doivent être entourées de ' ou de “.

Exemples

'Linux est incroyable'

“Windows est …….”

“Je lui dit : ”“Je t'aime””“

Il existe 3 types de nombres :

Une séquence de chiffres sans espaces

Exemple

999256

0

Utilisent le point comme séparateur.

Exemple

120.54

5566.8956e+12

Sont précédés par le signe -.

Exemple

-458

-147.36

Une chaîne sans données.

<note warning> N'est pas la même chose qu'une chaîne vide ou un 0 dans le cas d'un nombre. </note>

Les noms de bases, tables et colonnes :

• peuvent contenir jusqu'à 64 caractères
• peuvent commencer par un chiffre
• ne peuvent pas contenir que de chiffres
• ne peuvent pas contenir un /, une \ ou un point

Les alias :

• peuvent contenir jusqu'à 255 caractères
• peuvent contenir un /, une \ ou un point

Les variables n'ont pas besoin d'être initialisées. Elles sont automatiquement crées avec la valeur NULL.

Les commentaires d'une ligne sont précédés par le caractère # ou - -.

Les commentaires sous plusieurs lignes sont entourés comme suit :

/*
Ceci est
un commentaire
sur
plusieurs lignes
*/

Obtenir un ensemble de données à partir d'une ou de plusieurs tables.

Syntaxe

SELECT [table.][colonne]|expression [AS nom][,[table.[colonne]
FROM nom-table [, nom_table ...]
[WHERE condition [AND|OR condition ...]
[GROUP BY [table.][colonne],[table].[colonne] ...]
[ORDER BY [table.][colonne][Description],[table].[colonne] ...]

Exemples

SELECT nom, prenom FROM familles ORDER BY nom

SELECT nom, prenom FROM familles GROUP BY ville

Mettre à jour des données dans une table.

Syntaxe

UPDATE [LOW_PRIORITY][IGNORE] nom_table
SET colonne = expression
WHERE expression
ORDER BY expression
LIMIT valeur

Exemples

UPDATE familles SET Adresse2='*******', nb_enfants=2;

UPDATE familles SET nb_enfants=8 ORDER BY Nom LIMIT 3;

Supprimer des enregistrements d'une table.

Syntaxe

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM nom_table
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT row_count]

Exemple

DELETE FROM familles WHERE Nom = Durant;

Syntaxe

DROP DATABASE [IF EXISTS] db_name

Supprimer une table d'une base de données.

Exemple

DROP TABLE Test;

Syntaxe

INSERT [LOW_PRIORITY] | [DELAYED] [IGNORE]
[INTO] nom_table [(nom_colonne ...)]
VALUES ((expression | DEFAULT) ...) , (...)

ou

INSERT [LOW_PRIORITY] | [DELAYED] [IGNORE]
[INTO] nom_table [(nom_colonne ...)]
SELECT ...

INSERT VALUES

Insérer des valeurs dans une table.

Exemple

INSERT INTO familles (Nom, Prenom) VALUES ('Smith', 'John');

INSERT SELECT

Insérer des valeurs en provenance d'une autre table dans la table cible.

Exemple

INSERT INTO familles (Prenom) SELECT enfants.prenom FROM enfants;

Ajouter, supprimer ou modifier une colonne, index ou nom de table.

ALTER ADD

Exemples

ALTER TABLE familles ADD Sports VARCHAR(50) NOT NULL;

ALTER TABLE familles ADD INDEX (Sports);

ALTER CHANGE

Permet de modifier le nom ou le type d'une colonne.

Exemple

ALTER TABLE familles CHANGE PrenomPere Prenom_Pere VARCHAR(45) NOT NULL;

ALTER DROP

Exemple

ALTER TABLE familles DROP Sports;

ALTER KEYS

Permet de désactiver la clef primaire momentanément.

Exemples

ALTER TABLE familles DISABLE KEYS;

ALTER TABLE familles ENABLE KEYS;

ALTER RENAME

Permet de renommer une table.

Exemple

ALTER TABLE enfants RENAME familles_enfants;

ALTER ORDER BY

Permet de reclasser physiquement une table.

Exemple

ALTER TABLE familles ORDER BY Prenom_Pere;

Permet la recherche d'un mot, d'une phrase ou d'une expression sur un texte entier

Exemple

SELECT * FROM familles WHERE MATCH (Commentaire) AGAINST ('fleuve');

Par exemple :

mysql> select DATE_FORMAT(now(), '%W %d %M %Y');
+-----------------------------------+
| DATE_FORMAT(now(), '%W %d %M %Y') |
+-----------------------------------+
| Friday 19 October 2012            |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> 

• TEXT,
  • TEXT respecte la casse des données,
• BLOB (Binary Large OBject),
  • BLOB permet de stocker toute information binaire telle une image ou un son.

• ENUM,
  • permet de prendre une valeur ou NULL parmi une liste de valeurs prédéfinies à la création de la colonne,
• SET
  • permet de prendre un maximum de 64 valeurs ou NULL parmi une liste de valeurs prédéfinies à la création de la colonne,

Le type de moteur est spécifié lors de la création de la table. Le moteur d'une table existant peut être modifié avec la commande ALTER TABLE. Les différents types principaux sont résumés ci-après :

Bien que possible techniquement, il n'est pas préférable d'utiliser des types de moteurs différents dans la même base de données car ceci complique la tâche d'optimisation, augmente le nombre de buffers et de caches et complexifie le travail de l'administrateur.

Le choix d'un moteur de stockage se fait en fonction de l'adéquation entre les besoins et les caractéristiques suivants :

Les jointures permettent de créer un lien entre deux tables. Le champ utilisé pour la jointure dans la première table est souvent la clef primaire. Le champs utilisé dans la deuxième table est appelé le clef étrangère. La clef primaire et la clef étrangère doivent être du même type.

Dans le cas où on utilise un champs autre que la clef primaire pour assurer la jointure, cet autre champs doit être indexé.

Ce type de jointure permet de renvoyer uniquement les enregistrements des deux tables 1 et 2 ayant une correspondance :

mysql> SELECT table1.champ1, table2.champ1 FROM table1, table2 WHERE table1.clefprimaire = table2.clefetrangere;

ou

mysql> SELECT table1.champ1, table2.champ1 FROM table1 JOIN table2 WHERE table1.clefprimaire = table2.clefetrangere;

Ce type de jointure permet de renvoyer les enregistrements de la première table qui ont une correspondance dans la deuxième table. La syntaxe est la suivante :

mysql> SELECT table1.champ1, table2.champ1 FROM table1 LEFT JOIN table2 ON table1.clefprimaire = table2.clefetrangere;

Le résultat de cette requête est l'affichage de tous les enregistrements de la table 1 avec ou sans les enregistrements correspondants de la table 2. Dans le cas où la table 2 ne contient pas d'enregistrement correspondant un un enregistrement de la table 1, la valeur retournée est NULL. Cette information nous permet de rechercher uniquement les enregistrements dans la table1 n'ayant pas d'enregistrements dans la table 2 :

mysql> SELECT table1.champ1, table2.champ1 FROM table1 LEFT JOIN table2 ON table1.clefprimaire = table2.clefetrangere WHERE table2.clefetrangere IS NULL;

Ce type de jointure est l'inverse d'un LEFT JOIN.

Créez maintenant la base de données CarnetAdresses :

mysql> CREATE DATABASE `CarnetAdresses` ;
Query OK, 1 row affected (0.00 sec)

mysql> 

Créez ensuite deux tables familles et enfants dans la base CarnetAdresses :

mysql> USE CarnetAdresses ;
Database changed
mysql> 

mysql> CREATE TABLE familles (CodeFamille BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Nom VARCHAR(40),
    -> PrenomPere VARCHAR(40) , PrenomMere VARCHAR(40) , Adresse1 VARCHAR(40) , Adresse2 VARCHAR(40),
    -> CodePostal VARCHAR(5), Ville VARCHAR(40), ProfPere VARCHAR(40), ProfMere VARCHAR(40));
Query OK, 0 rows affected (0.03 sec)

mysql> 

Pour plus de facilité, copiez simplement la requête et collez-la dans votre terminal :

familles

CREATE TABLE familles (CodeFamille BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, Nom VARCHAR(40),\\
PrenomPere VARCHAR(40) , PrenomMere VARCHAR(40) , Adresse1 VARCHAR(40) , Adresse2 VARCHAR(40),\\
CodePostal VARCHAR(5), Ville VARCHAR(40), ProfPere VARCHAR(40), ProfMere VARCHAR(40));

mysql> CREATE TABLE Enfants ( CodeFamille BIGINT NOT NULL, Prenom VARCHAR(40) NOT NULL,
    -> Sexe VARCHAR(1) NOT NULL, DateNaissance DATE NOT NULL, PRIMARY KEY (CodeFamille));
Query OK, 0 rows affected (0.01 sec)

mysql> 

Pour plus de facilité, copiez simplement la requête et collez-la dans votre terminal :

enfants

CREATE TABLE Enfants ( CodeFamille BIGINT NOT NULL, Prenom VARCHAR(40) NOT NULL,\\
Sexe VARCHAR(1) NOT NULL, DateNaissance DATE NOT NULL, PRIMARY KEY (CodeFamille));

Utilisez ensuite la commande SHOW pour visualiser le résultat de chaque instruction, par exemple :

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| CarnetAdresses     |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>  

mysql> SHOW TABLES FROM CarnetAdresses;
+--------------------------+
| Tables_in_CarnetAdresses |
+--------------------------+
| Enfants                  |
| familles                 |
+--------------------------+
2 rows in set (0.00 sec)

mysql> 

mysql> SHOW COLUMNS FROM familles FROM CarnetAdresses;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| CodeFamille | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| Nom         | varchar(40) | YES  |     | NULL    |                |
| PrenomPere  | varchar(40) | YES  |     | NULL    |                |
| PrenomMere  | varchar(40) | YES  |     | NULL    |                |
| Adresse1    | varchar(40) | YES  |     | NULL    |                |
| Adresse2    | varchar(40) | YES  |     | NULL    |                |
| CodePostal  | varchar(5)  | YES  |     | NULL    |                |
| Ville       | varchar(40) | YES  |     | NULL    |                |
| ProfPere    | varchar(40) | YES  |     | NULL    |                |
| ProfMere    | varchar(40) | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

mysql> 

mysql> SHOW INDEX FROM familles;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| familles |          0 | PRIMARY  |            1 | CodeFamille | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

mysql> 

mysql> SHOW TABLE STATUS FROM CarnetAdresses;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Enfants  | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   9437184 |           NULL | 2014-01-27 10:46:10 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
| familles | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   9437184 |              1 | 2014-01-27 10:45:39 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
2 rows in set (0.00 sec)

mysql> 

Créez les enregistrements dans la table familles :

data

INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Durant', 'Jacques', 'Jane', '23 rue Dutor','','92200', 'Neuilly', 'Plombier',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tortua', 'Benoît', 'Carole', '7 rue Verget','', '75005', 'Paris', 'Cadre', 'Cadre'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Portier', 'Pierre', 'Elisabeth', '5 rue Toulet','', '92200', 'Neuilly', 'Dentiste',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Renault', 'Damien', 'Anne', '2 rue Ragon','', '75007', 'Paris', 'Comptable', 'Enseignante'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darduet', 'Jean','','','','','','',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Rodier', 'Gérard', 'Aurélie', '6 rue Agien', '77000', 'Fontainebleau', 'Professeur',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tarte', 'Alla', 'Crème', '1 allée Durond','', '92200', 'Neuilly', 'Cuisinier', 'Cuisinière'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Cohen', 'David', 'Sarah', '7 Av d\'Eylau','', '75016', 'Paris', 'PDG', 'Assistante Direction'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Dupont2', 'Bruno', 'Odile', '12 rue Sébastien','', '75008', 'PARIS', 'Electricien', 'Comptable'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Durand2', 'Jacques', 'Jane', '23 rue Dutor','', '92200', 'Neuilly', 'Plombier',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darmon2', 'Bruno', 'Béatrice', '2 rue Nicolo','', '13008', 'Marseille', 'Cadre', 'Peintre'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Darduet2', 'Jean', '','' ,'' ,'' ,'','',''); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Tarte2', 'Alla', 'Crème', '1 allée Durond','', '92200', 'Neuilly', 'Cuisinier', 'Cuisinière'); INSERT INTO familles (Nom , PrenomPere , PrenomMere , Adresse1 , Adresse2 , CodePostal , Ville , ProfPere , ProfMere) VALUES ('Cohen2', 'David', 'Sarah', '7 Av d\'Eylau','', '75016', 'Paris', 'PDG', 'Assistante Direction');

Saisissez maintenant chacune des commandes suivantes et expliquez le résultat obtenu. Les instructions sont numérotées pour faciliter vos prises de notes.

<note tip> LIMIT - Avec un argument, la valeur spécifie le nombre de lignes à retourner depuis le début du jeu de résultat. Si deux arguments sont donnés, le premier indique le décalage du premier enregistrement à retourner, le second donne le nombre maximum d'enregistrement à retourner. Le décalage du premier enregistrement est 0 (pas 1). </note>

<note> Créez la colonne familles.nb_enfants. </note>

<html>

</html>