Le langage SQL (Structured Query Language = langage structuré d

IPT_résumé_SQL_PCSI
SQL
Le langage SQL (Structured Query Language = langage structuré d’interrogation) offre des commandes pour
créer/supprimer une table, ajouter des attributs, des n-uplets et pour interroger une base existante.
MySQL est un système de gestion de bases de données relationnelles SGBDR optimisé pour la lecture de données.
MySQL est souvent utilisé par les sites internet conjointement avec Apache (serveur http pour le web) et PHP (langage
de programmation pour générer des pages web dynamiques sur un serveur http).
SQLite permet également de piloter une base de données. Il peut-être utilisé en local, sans échange avec un serveur.
Ils sont tous les deux basés sur le langage SQL.
Il existe de nombreuses interfaces graphiques permettant de manipuler une base de données MySQL ou SQLite. Par
exemple phpMyAdmin pour MySQL; SQLite Manager, SQLite Browser pour SQLite.
On va décrire les principales commandes du langage SQL permettant d'interroger et modifier une base de données.
Les exemples sont relatifs à la base étudiée en cours.
On trouvera
• sur le site http://sql.sh/ un cours décrivant les principales commandes de SQL
(pdf téléchargeable : http://sql.sh/2143-cours-sql-pdf )
• sur le site http://fr.openclassrooms.com/informatique/cours/administrez-vos-bases-de-donnees-avec-mysql
Une instruction SQL est composée
• de mots-clé (écrits en majuscules pour plus de lisibilité).
• des identificateurs( noms de tables, d’attributs, ... )
• des expressions
Exemple
On considère la commande
SELECT désignation, prix FROM Articles WHERE stock >=15
SELECT, WHERE, FROM sont des mots-clé
désignation, prix, Articles, stock sont des identificateurs
stock >= 15 est une expression.
Remarques
Si les identificateurs comprennent des espaces, il faut mettre des guillemets.
Eviter de préférence les caractères accentués et les espaces dans les noms d'identification.
Deux commandes SQL sont séparée par un point virgule.
On peut mettre des commentaires encadrés par un délimiteur : -- commentaire ou bien /* commentaire /*
- page 1 -
IPT_résumé_SQL_PCSI
Types de données
• entiers (signés et non signés) (sur 1,2,3 4 ou 8 octets selon la taille requise
• NUMERIC ou DECIMAL : nombres décimaux stockés de façon exacte sous forme de chaîne.
• DOUBLE ou REAL : flottants sur 8 octets; attention aux problèmes d'arrondi éventuels.
• CHAR(n) : chaîne sur n caractères
• VARCHAR(n) : chaîne sur n caractères au plus (255 caractères au maximum)
• TEXT : pour des chaînes de longueurs > 255
• DATE : format 'AAAA-MM-JJ'
• DATETIME : format 'AAAA-MM-JJ HH:MM:SS'
Créer, effacer, mettre à jour les tables
CREATE base_de_donnees (IF NOT EXISTS) nom_base
DROP base_de_donnees (IF EXISTS) nom_base
CREATE TABLE nom_table (colonne1 spécifications1, colonne2 spécifications2, ... ) spécifications comprend
le type de données, la valeur par défaut éventuelle, ...
DROP TABLE nom_table
ALTER TABLE nom_table, action
action :
ADD colonne, description ; DROP colonne
UPDATE table SET colonne1 = 'valeur1' , colonne2 = 'valeur2' , ... WHERE condition
seules les lignes où la condition est satisfaite (modifications sur lignes existantes)
INSERT INTO table (colonne1, ... , ) VALUES (valeur1, ... ) rajout de lignes
DELETE FROM table WHERE condition
suppression de lignes
Syntaxe d'une requête
SELECT colonne(s) FROM table
WHERE condition
GROUP BY expression
HAVING conditions
ORDER BY expression DESC (ou expression ASC)
LIMIT nombre
SELECT
SELECT * FROM table : renvoie toutes les lignes de la table
SELECT colonne1, colonne2, ... FROM table : renvoie le contenu des colonnes spécifiées pour toutes les lignes
SELECT colonne(s) FROM table WHERE condition : contenu des colonnes spécifiées pour les lignes
remplissant la condition
SELECT DISTINCT colonne FROM table WHERE ... : plusieurs lignes peuvent avoir la même valeur sur la
colonne spécifiée; avec DISTINCT, on ne garde qu'une seule occurrence de chaque valeur.
SELECT colonne(s) FROM table WHERE ... LIMIT n : on ne retourne que les n premières lignes.
- page 2 -
IPT_résumé_SQL_PCSI
Exemples
SELECT DISTINCT nom FROM Clients
SELECT * FROM Magasins
SELECT nom, prenom FROM Clients
SELECT nom, prenom FROM Clients WHERE article_favori = 1
SELECT * FROM Achats WHERE date >= '2015-01-01'
Opérations
opérations arithmétiques : +, −, ×, / , DIV division entière, % modulo
opérations bouléennes : = , < , <=, >, >=, <> ou bien !=, EXISTS, NOT EXISTS, IN, BETWENN, ANY, ALL
ROUND(nombre, precision) 1e+n = 10n
Sélection sur la forme d'une chaîne
SELECT ... WHERE colonne LIKE "modèle"
modèle :
"%x" : chaîne se terminant par le caractère x
"x%" : chaîne débutant par le caractère x
"%x% : chaîne contenant le caractère x
SUBSTR(colonne, position)
Exemples
SELECT designation FROM Articles WHERE prix BETWEEN 10 AND 20
SELECT designation FROM Articles WHERE designation LIKE "%e"
SELECT * FROM Achats WHERE date LIKE "2015-%"
SELECT * FROM Achats WHERE date LIKE "%-01-%"
SELECT nom, num_tel FROM Clients WHERE num_tel LIKE "06%"
Regroupements
GROUP BY colonne : retourne une ligne unique pour toutes les instances ayant même attribut dans la colonne
HAVING : sélectionne les lignes ayant une certaine propriété dans un regroupement
Exemples
SELECT magasin, COUNT(*) FROM Achats GROUP BY magasin
SELECT magasin, COUNT(magasin) FROM Achats GROUP BY magasin
Appliquer une fonction
SELECT nom_fonction(colonne) : appliquer une fonction f à tous les termes d'une colonne A
Lors d'un regroupement, on applique une fonction d'agrégation sur les lignes regroupées
• SUM(colonne) : on fait la somme des valeurs contenues dans la colonne spécifiée pour toutes les lignes ayant
même occurrence dans la colonne spécifiée par GROUP BY
• AVG(colonne) : même chose mais calcul de la moyenne
• MAX(colonne), MIN(colonne)
• COUNT(colonne)
- page 3 -
IPT_résumé_SQL_PCSI
Exemples
SELECT AVG(prix_total) FROM Achats
SELECT COUNT(DISTINCT magasin) FROM Achats
SELECT client, SUM(prix_total) AS total FROM Achats GROUP BY client HAVING prix_total > 40
SELECT magasin, COUNT(client) FROM Achats GROUP BY magasin ORDER BY magasin DESC
SELECT magasin, COUNT(magasin) AS nb_achats FROM Achats GROUP BY magasin HAVING nb_achats >=2
Sous-requêtes
Dans les requêtes complexes, il arrive qu'on ait besoin de faire une sélection sur un tableau intermédiaire luimême résultat d'une requête.
Une sous-requête est encadrée par des parenthèses.
AS : introduit un alias pour renommer une colonne ou un tableau retourné par une requête. Utile pour renommer
les colonnes lors de l'affichage d'un résultat.
Deux types de sous-requêtes :
• sous-requête dans FROM : SELECT ... FROM (sous-requête) AS nom_provisoire
• sous-requête dans WHERE : SELECT .. FROM ... WHERE expression contenant une sous-requête
Exemples
SELECT client, AVG(prix_total) AS achat_moyen FROM Achats GROUP BY client
SELECT client, SUM(prix_total) AS total FROM Achats GROUP BY client HAVING total > 40
SELECT id, designation FROM Articles WHERE NOT EXISTS
(SELECT * FROM Achats WHERE Achats.article = Articles.id)
Jointures
Permettent de faire des requêtes sur plusieurs tables en précisant la condition de recollement entre les deux tables
SELECT colonne(s) FROM table1 JOIN table2 ON table1.colonneA = table2.colonneB WHERE ...
On peut réaliser la jointure avec WHERE :
SELECT colonnes(s) FROM table1, table2 WHERE table1.colonneA = table2.colonneB AND ...
Exemples
SELECT nom, designation FROM Clients JOIN Articles ON Clients.article_favori = Articles.id
SELECT nom, prenom, designation FROM Clients, Articles WHERE Clients.article_favori = Articles.id
SELECT Clients.nom, date, designation FROM Clients JOIN (Achats JOIN Articles
ON Achats.article = Articles.id) ON Achats.client = Clients.id WHERE article BETWEEN 3 AND 5
Clés et index
• Clé primaire : elles garantissent que deux lignes distinctes de la table ont des valeurs distinctes dans la
colonne correspondant à la clé primaire.
CREATE TABLE nom_table (nom_colonne, ... , PRIMARY KEY (colonne))
ALTER TABLE nom_table ADD PRIMARY KEY colonne
ALTER TABLE nom_table DROP PRIMARY KEY
- page 4 -