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.

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

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

• 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 un simple utilisateur :

CREATE USER mon_utilisateur WITH PASSWORD 'mon_utilisateur';

• Supprimer un utilisateur :

DROP USER mon_utilisateur;

• 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’;

• 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

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

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

• Lister toutes les vues d'une table :

SHOW FULL TABLES IN ma_table WHERE TABLE_TYPE LIKE 'VIEW';

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

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;

• Remplire une table rapidement :

INSERT INTO ma_table (ma_collone) (SELECT round(random()*10) FROM generate_series(1,10000000));