===== Synthaxes courantes en SQL ===== {{ :wiki:synthaxes-commandes-requete-sql-logo.png?100 | }} ---- Cette page comprend une liste de requêtes SQL étudié pendant [[http://iut-charlemagne.univ-lorraine.fr/content/licence-professionnelle-administration-de-systemes-reseaux-et-applications-base-de-logiciels|la licence ASRALL]]. Pour plus de renseignements, vous trouverez de l'[[http://www.w3schools.com/sql/|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 [[http://docs.postgresql.fr/9.2/sql-grant.html|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é [[http://docs.postgresql.fr/8.2/sql-createrole.html|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;

* Lister toutes les vues d'une table :


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

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