SQL

introduction


SELECT variable from dataframe
AS trials;

Pour une selection de plusieurs colonnes

Select var1, var2 From dataframe ;

pour selectionner toute les colonnes

Select * from dataframe
Select * from dataframe limit 10 ;

pour selectionner les types distinct d’une colonne

select distinct variable from dataframe;

pour le nombre d’élément du dataframe

select count(*) From dataframe;

Pour le nombre d’élément variable

select count(variable) from dataframe;

Pour le nombre d’élément distinct

select count(distinct variable) from dataframe;

Filtrer les resultats

• outils de comparaisons = equal
<> not equal
< less than | > greater than
<= less than or equal to
>= greater than or equal to

filtrer les lignes d’une colonnes correspondants un caractéristique

SELECT variable FROM dataframe
WHERE  variable condition ;

Quand la condition est sous forme caractére

SELECT variable FROM dataframe
WHERE variable = 'condition' ;

Pour compter

SELECT count(variable ou *) FROM dataframe
WHERE condition ;

• Si l’on veut plusieurs condition

• utilisation de AND


SELECT variable FROM dataframe 

WHERE  variable  condition AND  variable condition ;

• OR

SELECT variable  FROM dataframe 
WHERE variable condition OR  variable condition ;

• AND et OR


SELECT variable  FROM dataframe
WHERE (variable condition OR  variable condition) 
AND (variable condition OR  variable condition)
OR (variable condition OR  variable condition)
;

• utilisation de Between

SELECT variable FROM dataframe 
WHERE variable BETWEEN valeur AND valeur1 ;

• Condition sequentiel

SELECT variable  FROM dataframe 
WHERE  variable IN (condition ,condition1, .....) ;

• NULL

SELECT variable FROM dataframe 
WHERE  variable IS NULL
;

• pattern

SELECT variable FROM dataframe 
WHERE variable (ou NOT LIKE ) LIKE 'caractere' cas complet 
ou 'carac%'  ceux commencent pas carac
ou 'car_c' chercche dans cette structure ;

agragate functions

• Pour recupére la moyenne

SELECT AVG(variable) FROM dataframe ;

• Pour recupérer le max

SELECT MAX(variable) FROM dataframe;

• Pour recupérer le MIN

SELECT MIN(variable) FROM dataframe;

• Pour recupérer le somme

SELECT SUM(variable) FROM dataframe;

SELECT(2*4);

• affecter un ou plusieurs Resultat

SELECT MAX(variable1) AS Nom1,
 MIN(vAriable2) AS Nom2
FROM dataframe;

SORT , GROUP , JOIN

• Pour ordonner par lettre ou chiffre croissant

SELECT Variable
FROM dataframe
ORDER BY Variable1 ;

• Order inverse

SELECT Variable 
FROM dataframe
ORDER BY Variable2 DESC ;

• Order by deux variables

SELECT Variable 
FROM dataframe
ORDER BY Variable2 ,Variable1;

• group BY (cas table)

SELECT variable count(*)
FROM dataframe 
GROUP BY variable;

• ORDER by et group BY

SELECT variable, variable1,MAX(variable3)
FROM dataframe
GROUP BY variable, variable1
ORDER BY variable,variable1;

• HAVING

SELECT variable 
FROM dataframe
GROUP BY variable
HAVING variable1 condition,

Postgresql

• Inner Join


SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id;

SELECT p1.var1 , p1.vars2, var3, var4
FROM Dataframe1 AS p1 
INNER JOIN dataframe2 AS p2 
ON p1.varid= p2.varid;

SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id
INNER JOIN another_table
ON left_table.id = another_table.id;

• USING Permet de faire un inner join sans ON left_table.id = another_table.id


SELECT *
FROM left_table
INNER JOIN right_table
ON USING(id);

• SELF-ish joins

Auto-jonctions des tables