Synthaxes courantes en SQL


Cette page comprend une liste de requêtes SQL étudié pendant la licence ASRALL. Pour plus de renseignements, vous trouverez de l'aide sur w3schools.

Les pricipales requêtes

  • Retourne les données correspondant à l'Identifiant 25
SELECT nom, prenom FROM T1 WHERE identifiant = 25;
  • Retourne les données correspondant au client qui a plus de 50€ :
SELECT nom_client, SUM(argent) FROM client GROUP BY nom_client HAVING SUM(argent) > 40
  • concaténation de chaines :
SELECT prenom||' '||nom AS personne FROM utilisateurs;
  • Enlever les doublons :
SELECT DISTINCT * FROM utilisateurs_lambda, utilisateurs_avancer;
  • Si une valeur fait partie d'une liste :
SELECT * FROM utilisateurs WHERE ages IN (18, 20, 22);
  • Recherche grâce à des filtres :
SELECT * FROM utilisateurs LIKE "_o%";
  • le caractère _ signifie : n'importe quel caractères mais 1 seul
  • le caractère % signifie : n'importe quel caractères et plusieurs

Retourne :

lorent, loriane
  • Filtre par ordre décroissant :
SELECT * FROM utilisateurs ORDER BY age DESC
  • Trouve les utilisateurs agé entre 20 et 25 ans :
SELECT * FROM utilisateurs WHERE age BETWEEN 20 AND 25;
  • Concaténer les résultats dans une chaine :
SELECT "Vous êtes "||nom||" "||prenom FROM utilisateurs;
  • Créer une table :
CREATE TABLE T1 (C1 DATE, C2 INT, C3 VARCHAR(10));
  • Supprimer une table :
DROP TABLE T1 CASCADE;

L'option CASCADE est facultative mais sa présence signifie que les objets qui dépendent de la table (vues, par exemple) seront automatiquement supprimés.

  • Supprimer une ligne spécifique :
DELETE FROM ma_table WHERE ma_collone IS NULL;
  • Insérer des valeurs :
INSERT INTO T1(C1,C2,C3) VALUES (1, 'chaine2', 'chaine2'),(2, 'chaine3',chaine3');
  • Mettre à jour des valeurs :
UPDATE T1 SET C1="valeur à maj" WHERE C1='mon_utilisateur_par_ex';
  • Créer une base de donnée :
CREATE DATABASE ma_bdd;
  • Supprimer ma base de donnée :
DROP DATABASE ma_bdd;
  • Copier une table :
CREATE TABLE nouvelle_table AS SELECT * FROM table_cible;

Les Unions

  • Unifier les tables :
SELECT col1 FROM t1 UNION SELECT col1 FROM t2;
  • Récupérer les données communes des tables :
SELECT col1 FROM t1 INTERSECT SELECT col1 FROM t2;
  • Récupérer les données inclus dans t1 seulement s'is ne sont pas présent dans t2 :
SELECT col1 FROM t1 EXCEPT SELECT col1 FROM t2;

Les Jointures

  • Jointure classique entre deux tables :
SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id;
/* Identique à : */
SELECT * FROM T1,T2 WHERE T1.id = T2.id;
  • Jointure classique entre deux tables MAIS affiche les valeurs NULL :
SELECT * FROM T1 FULL JOIN T2 ON T1.id = T2.id
  • fusionner le contenu de deux tables :
SELECT * FROM t1 CROSS JOIN t2;
/* Identique à : */
SELECT * FROM t1,t2;
  • Injecter des données dans la table de gauche :
SELECT * FROM T1 LEFT JOIN T2 T1.id = T2.id;
/* T1 = table de gauche */
/* T2 = table de droite */

Si les collones de l'ensemble de droite sont absents dans la table de gauche alors leurs contenues seront renseigné par la valeur NULL.

  • Injecter des données dans la table de droite :
SELECT * FROM T1 RIGHT JOIN T2 T1.id = T2.id;
/* T1 = table de gauche */
/* T2 = table de droite */

Si les collones de l'ensemble de droite sont absents dans la table de gauche alors leurs contenues seront renseigné par la valeur NULL.

Créer / supprimer un utilisateur

Postgres

  • Créer un simple utilisateur :
CREATE USER mon_utilisateur WITH PASSWORD 'mon_utilisateur';
  • Supprimer un utilisateur :
DROP USER mon_utilisateur;

MySQL

  • Créer un simple utilisateur :
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
  • Supprimer un utilisateur :
DROP USER ‘newuser’@‘localhost’;

Gestion des privilèges (droits)

  • Donner tous les droits sur une base de donnée :
GRANT ALL PRIVILEGES ON DATABASE ma_bdd TO mon_utilisateur;

Liste des droits applicable présenté à cette page

  • On peut également donner certain droits sur des collones spécifique :
GRANT SELECT, INSERT, UPDATE (ma_collone1, ma_collone2) ON ma_table TO mon_utilisateur;

ou

  • On peut également donner tous les droits sur une table spécifique :
GRANT ALL PRIVILEGES ON TABLE ma_table TO mon_utilisateur;
  • Créer un rôle dont les utiliseurs pourront créer des BDD, des rôles mais pas le droit de s'authentifier :
CREATE ROLE mon_role CREATEDB CREATEROLE NOLOGIN;

D'autres rôles sont présenté ici

  • appliquer un rôle aux utilisateurs :
GRANT mon_role TO utilisateur, utilisateur2;
  • Supprimer un rôle d'un utilisateur :
REVOKE mon_role TO utilisateur;
  • Supprimer un rôle :
DROP ROLE mon_role

Modifier le comportement d'une table

  • Changer le mot de passe d'un utilisateur :
ALTER USER 'binome' WITH PASSWORD 'nouveau_password';
  • Ajouter une clé primaire sur un champ (C1) :
ALTER TABLE ma_table ADD CONSTRAINT mon_label_cle_primaire PRIMARY KEY (ma_collone_C1);
  • Ajouter une clé étrangère sur un champ (C1) :
ALTER TABLE ma_table ADD CONSTRAINT mon_label2_cle_etrangere FOREIGN KEY (ma_collone_C1bis) REFERENCES ma_table(ma_collone);
  • Ajouter une collone dans une table :
ALTER TABLE T1 ADD C6 VARCHAR(50);
  • Supprimer une collone dans une table :
ALTER TABLE T1 DROP C6;
  • Ajouter une contrainte d'unicité dans une collone :
ALTER TABLE ma_table ADD CONSTRAINT mon_label UNIQUE (ma_collone);
  • Ajouter une contrainte empêchant de renseigner des valeurs NULL dans une collonne :
ALTER TABLE ma_table ALTER COLUMN ma_collone SET NOT NULL;
  • Ajouter une contrainte qui force la saisie d'une valeur positive :
ALTER TABLE ma_table ALTER COLUMN ma_collone CHECK(ma_collone > 0);

Créer une vue

  • Avoir une vue qui détient toutes les collones de la table T1 :
CREATE VIEW ma_vue AS SELECT * FROM ma_table;
  • Donner le droit d'exécuter un SELECT à cette vue par mon_groupe :
GRANT SELECT ON ma_vue TO mon_groupe;

Créer une règle

  • Si un utilisateur ajoute un donnée dans la table alors on fait une action :
CREATE RULE ma_regle AS ON INSERT TO ma_table DO 
INSERT INTO ma_table2 VALUES (now(), 'INSERT', NEW.col1, NEW.col2);
  • On peut retrouver les infos sur ces règles via :
SELECT * FROM pg_rules WHERE tablename='ma_table';

Manipuler les commits

Par défaut les commits sont automatique, ci-dessous on les gères manuellement :

\pset AUTOCOMMIT off;
BEGIN;
UPDATE T1 SET C1=2;
COMMIT;                     -- END; Fonctionne également.
  • Rollback :
\SET AUTOCOMMIT off;
BEGIN;
UPDATE T1 SET C1=2;
SELECT * FROM T1;

Affiche : C1=2

SAVEPOINT r0;
UPDATE t1 SET C1=4;
SELECT * FROM T1;

Affiche : C1=4

ROLLBACK TO SAVEPOINT r0;
SELECT * FROM T1;

Affiche : C1=2;

COMMIT;
  • Isolation des utilisateurs (celà fonctionne avec l'autocommit à off) :
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

D'autres instructions

  • Remplire une table rapidement :
INSERT INTO ma_table (ma_collone) (SELECT round(random()*10) FROM generate_series(1,10000000));