====== 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';