====== Langage SQL ======
Ce chapitre aborde uniquement le langage SQL, voir les tutoriels [[informatique:linux:mysql]] ou [[informatique:bdd:oracle]] pour toute information relative à la création de bases de données, de description de table etc...

===== Base de données =====







===== Tables =====





==== Création de table ====
Créer la table **users** avec comme champs :
^id^username^email^
| | | |

  create table users (
    id INT,
    username VARCHAR(100),
    email VARCHAR(100)
    );

ou
  create table users ( id INT, username VARCHAR(100), email VARCHAR(100) );

Pour créer une vue d'une table :
  CREATE VIEW vusers (Nom, courriel) AS SELECT username, email FROM users;

==== Modification de table ====
=== Renommer une table ===
Pour renommer la table **users** en **utilisateurs** :
  ALTER TABLE users RENAME utilisateurs;

=== Modifier les caratéristiques d'une colonne ===
Pour obliger que le champs **id** à être **non vide**, **auto incrémenter** et être la **clé primaire** :
  ALTER TABLE users MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Pour permettre de nouveau le champs **id** à être vide :
  ALTER TABLE users MODIFY id INT NULL AUTO_INCREMENT PRIMARY KEY;

Pour obliger la colonne **username** à ne pas avoir de doublon :
  ALTER TABLE users MODIFY username varchar(100) UNIQUE;

Pour supprimer la clé primaire de la table **users** :
  ALTER TABLE users DROP PRIMARY KEY;

Pour ajouter un index nommé **indexuser** sur la colonne **username** :
  ALTER TABLE users ADD INDEX indexuser (username);

Note :!: : L'ajout d'index à une colonne permet de rendre plus rapide les recherches sur celle-ci. Par défaut lorsqu'on fait une recherche dans une colonne non indexée, Mysql parcourt toutes les entrées de la colonne avant d'afficher le résultat. Par contre une colonne indexée permet à Mysql de déterminer rapidement la position des entrées recherchées.

Pour effacer un l'index nommé **indexuser** :
  ALTER TABLE users DROP INDEX indexuser;

Pour changer l'encodage de caractères par défaut d'une table : 
  ALTER TABLE `ma_table` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

=== Gestion des colonnes d'une table ===
Pour **ajouter** la colonne **phonenb** à la table **users** :
  ALTER TABLE users ADD COLUMN phonenb VARCHAR(10);

Pour **supprimer** la colonne **phonenb** : 
  ALTER TABLE users DROP COLUMN phonenb;

Pour **renommer** la colonne **username** en **utilisateur** :
  ALTER TABLE users CHANGE username utilisateur VARCHAR(100);

Note :!: : Pour changer l'ordre des colonnes il est nécessaire de créer une nouvelle table, le mode opératoire est le suivant :
  * Créer une nouvelle table avec le nouvel ordre des colonnes.
  * Copier les données de l'ancienne table vers la nouvelle : ''INSERT INTO nouvelletable SELECT colonne1, colonne3,  colonne2 FROM anciennetable;''
  * Effacer l'ancienne table.
  * Renommer la nouvelle table avec le nom de l'ancienne table.




===== Données =====


==== Insertion de données ====

Pour ajouter la valeur **0** dans la colonne **id** :
  INSERT INTO users (id) VALUE ('0');

Ajouter des valeurs ou l'**id** est égal à **0** : 

  UPDATE users SET username='benoit' WHERE id=0;
  UPDATE users SET email='benoit@domaine.com' WHERE id=0;

Pour remplir les 3 champs en une seule fois :
  INSERT INTO users (id, username, email) VALUES ('1', 'magalie', 'magalie@domaine.com');




==== Copie de données ====
Pour copier les données de la table **users** dans la table **users2** :
  INSERT INTO users2 SELECT * FROM users;


Pour copier le contenu de la colonne **id** dans la colonne **id2** dans la table **users** :
  UPDATE users SET id2 = id;

Copier la valeur **id2** de **benoit** depuis la table **users** vers la colonne **id** ou se trouve la valeur **benoit** :
  UPDATE users2 SET id = (SELECT id2 FROM users WHERE username='benoit') WHERE username='benoit';


==== Afficher les données ====

Pour voir toutes les entrées saisit dans la table **users** :
  SELECT * FROM users;
ou
  SELECT * FROM dbperso.users;

  +------+----------+---------------------+
  | id   | username | email               |
  +------+----------+---------------------+
  |    0 | benoit   | benoit@domaine.com  |
  |    1 | magalie  | magalie@domaine.com |
  +------+----------+---------------------+

Pour voir seulement les colonnes **username** et **email**
  SELECT username, email FROM users;

  +----------+---------------------+
  | username | email               |
  +----------+---------------------+
  | benoit   | benoit@domaine.com  |
  | magalie  | magalie@domaine.com |
  +----------+---------------------+

Pour afficher dans l'ordre alphabétique de la colonne username : 
  SELECT * FROM users ORDER BY username;

Pour rechercher l'adresse **email** de l'utilisateur **benoit** :
  SELECT email FROM users WHERE username='benoit';

  +--------------------+
  | email              |
  +--------------------+
  | benoit@domaine.com |
  +--------------------+

Pour sélectionner les lignes contenant id égal à **1** ou **9** de la table **table** uniquement :
  SELECT * FROM table WHERE id='1' OR id='9'\G;

Pour sélectionner tout ce qui commence par **chaine** dans la colonne **description** de la table **host** :
  SELECT * FROM host WHERE description LIKE 'chaine%'

Pour sélectionner tout ce qui commence par **chaine** ou contient **contient** dans la colonne **description** de la table **host** :
  SELECT * FROM host WHERE description LIKE 'chaine%' OR description LIKE '%contient%';

Pour afficher uniquement les 20 dernières lignes d'une table triée par **id** :  
  select * from cc_call ORDER BY id DESC LIMIT 20;

Pour afficher les droits utilisateurs sur les tables avec un affichage par section (\G) :
  select * from mysql.tables_priv \G;

==== Fonctions ====

=== Scalaires ===
Sélection avec conversion des entrées du champ **nom** en majuscule :
  SELECT UPPER(nom) FROM ma_table

Par défaut, le nom de l'entête de la colonne s'appellera **UPPER(nom)**

Il est recommandé toutefois de donner un nom de champ virtuelle (alias) pour les entrées transformées (pour les utiliser dans un programme)
  SELECT UPPER(nom) AS nom_maj FROM ma_table
  SELECT UPPER(nom) AS nom_maj, LOWER(possesseur) AS min_possesseur FROM ma_table

Quelques fonctions scalaires pouvant également être utilisées dans la même manière :
  * **UPPER** : Pour convertir les lettres en majuscule
  * **LOWER** : Pour convertir les lettres en minuscule
  * **LENGTH** : Pour connaître la longueur d'un champs
  * **ROUND** : Pour arrondir un chiffre (ex: ''SELECT ROUND(prix, 2) AS prix_arrondi FROM ma_table'')

D'autre fonctions sont disponibles :
  * Fonctions mathématiques : [[http://dev.mysql.com/doc/refman/5.0/fr/mathematical-functions.html]]
  * Fonctions pour les chaînes de caractères : [[http://dev.mysql.com/doc/refman/5.0/fr/string-functions.html]]

=== Agrégats ===
Quelques fonctions agrégeant les résultats :
  * **AVG** : Pour faire une moyenne (ex: ''SELECT AVG(prix) AS prix_moyen FROM table_prix'')
  * **SUM** : Faire la somme total
  * **MAX** : Donne la valeur maximale
  * **MIN** : Donne la valeur minimale
  * **COUNT** : Compte le nombre d'entrées. **Remarque** : COUNT permet aussi compter le nombre de valeurs distinctes sur un champ précis (ex: ''SELECT COUNT(DISTINCT proprio) AS nbproprio FROM ma_table'')

Remarque : il est possible de cumuler plusieurs fonctions ainsi. Exemple : 
  SELECT ROUND(AVG(prix), 2) AS prix_moyen, console FROM ma_table

=== Groupement ===
L'exemple ci-dessous permet d'afficher le prix moyen par type d'article en utilisant **GROUP BY** :
  SELECT AVG(prix) AS prix_moyen, type_article FROM ma_table GROUP BY type_article

Pour filtrer le résultat après le groupement avec **HAVING** :
  SELECT AVG(prix) AS prix_moyen, type_article FROM ma_table GROUP BY type_article HAVING prix_moyen <= 10
  
==== Date et temps ====
Rechercher les entrées après une certaine date : 
  SELECT pseudo, message, date_creation FROM minichat WHERE date_creation >= '2010-04-02 15:28:22'

Pour rechercher entre deux dates :
  SELECT pseudo, message, date_creation FROM minichat WHERE date_creation >= '2010-04-02 00:00:00' AND date_creation <= '2010-04-18 00:00:00'

Ou bien (recommandé) :
  SELECT pseudo, message, date_creation FROM minichat WHERE date_creation BETWEEN '2010-04-02 00:00:00' AND '2010-04-18 00:00:00'

Pour insérer une date : 
  INSERT INTO minichat(pseudo, message, date_creation) VALUES('Toto', 'Message !', '2010-04-02 16:32:22')

Cependant nous privilégierons les fonctions de gestion des dates. 

Pour insérer une date actuelle : 
  INSERT INTO minichat(pseudo, message, date) VALUES('Toto', 'Message !', NOW())

Extraire juste l'année :
  SELECT pseudo, message, YEAR(date_creation) AS jour FROM minichat

Extraire juste l'heure stockée dans un alias :
  SELECT pseudo, message, HOUR(date_creation) AS heure FROM minichat

Formater la date dans différents Alias :
  SELECT pseudo, message, DAY(date_creation) AS jour, MONTH(date_creation) AS mois, YEAR(date_creation) AS annee, HOUR(date_creation) AS heure, MINUTE(date_creation) AS minute, SECOND(date_creation) AS seconde FROM minichat

Autrement on peut formater directement notre résultat avec la fonction **DATE_FORMAT** :
  SELECT pseudo, message, DATE_FORMAT(date_creation, 'le %d/%m/%Y à %H:%i:%s') AS date_creation FROM minichat

Pour plus de détails : [[http://dev.mysql.com/doc/refman/5.0/fr/date-and-time-functions.html]]

Pour ajouter 15 jours à la date :
  SELECT pseudo, message, DATE_ADD(date_creation, INTERVAL 15 DAY) AS date_expiration FROM minichat

Autre exemple pour afficher 2 mois avant la date :
  SELECT pseudo, message, DATE_SUB(date_creation, INTERVAL 2 MONTH) AS date_expiration FROM minichat

==== Jointures ====
=== Méthode WHERE ===

Pour joindre deux les champs de deux table avec mise en commun des **id**
  SELECT articles.nom AS nom_article, proprietaires.prenom AS prenom_proprietaire
  FROM proprietaires, articles
  WHERE articles.id_proprietaire = proprietaires.id

Il est possible de créer de alias sur le nom de tables :
  SELECT a.nom AS nom_article, p.prenom AS prenom_proprietaire
  FROM proprietaires AS p, articles AS a
  WHERE a.id_proprietaire = p.id

On peut également se passer du AS :
  SELECT a.nom nom_article, p.prenom prenom_proprietaire
  FROM proprietaires p, articles a
  WHERE a.id_proprietaire = p.id

=== Méthode JOIN (recommandée) ===
** Jointures internes**

Pour joindre deux les champs de deux table avec mise en commun des **id**
  SELECT articles.nom AS nom_article, proprietaires.prenom prenom_proprietaire
  FROM proprietaires INNER JOIN articles
  ON articles.id_proprietaire = proprietaires.id

Avec l'utilisation d'alias et sans AS :
  SELECT a.nom nom_article, p.prenom prenom_proprietaire
  FROM proprietaires p INNER JOIN articles a 
  ON a.id_proprietaire = p.id

**Jointure de 3 tables**

  SELECT 
    profiles.*, profiles_times.*, times.*
  FROM profiles
    INNER JOIN profiles_times ON profiles.id = profiles_times.profile_id
    INNER JOIN times ON profiles_times.time_id = times.id

**Jointure de 4 tables** :!: Attention les Alias AS ont toutes leur importance :
  SELECT 
     pr.*, p.*, u.*, lu.*, rh.*
  FROM resolved_hosts AS rh
    INNER JOIN profiles_routing AS pr ON rh.fqdn = pr.address
    INNER JOIN profiles AS p ON pr.profile_id = p.id
    INNER JOIN users AS u ON p.id = u.profile_id
    INNER JOIN logged_users AS lu ON u.username = lu.user
  WHERE rh.timestamp > 1447090110

**Jointures externes**

Le **LEFT JOIN** demande à récupérer tout le contenu de la table de gauche (donc tous les propriétaires) même s'ils n'ont pas d'équivalence dans la table articles.
  SELECT a.nom nom_article, p.prenom prenom_proprietaire
  FROM proprietaires p LEFT JOIN articles a
  ON j.ID_proprietaire = p.ID

Le **RIGHT JOIN** demande à récupérer tout le contenu de la table de droite (donc tous les articles) même s'ils n'ont pas d'équivalence dans la table proprietaires.
  SELECT a.nom nom_article, p.prenom prenom_proprietaire
  FROM proprietaires p RIGHT JOIN articles a
  ON j.ID_proprietaire = p.ID

==== Effacer des données ====
Effacer la ligne ou se trouve l'**email** **benoit@domaine.com** :
  DELETE FROM users WHERE email='benoit@domaine.com';

Effacer la ligne ou se trouve 2 critères et limiter une seule suppression :
  DELETE FROM user WHERE users='benoit' AND email='benoit@domaine.com' LIMIT 1;

Pour effacer l'entrée **email** de toutes les lignes :
  UPDATE users SET email=''

Pour effacer l'entrée **email** de l'utilisateur **benoit** :
  UPDATE users SET email='' WHERE username='benoit';