Ce chapitre aborde uniquement le langage SQL, voir les tutoriels mysql ou oracle pour toute information relative à la création de bases de données, de description de table etc…

Créer la table users avec comme champs :

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;

Pour renommer la table users en utilisateurs :

ALTER TABLE users RENAME utilisateurs;

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;

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.

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

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

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;

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

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

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

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

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

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