Excel à SQL
Share
Explore

SQL Basics

Voir et sélectionner des champs
0
Action
Code SQL
1
Limiter à 1 champ
SELECT
last_name
FROM
Employees;
2
Plusieurs champs (virgule) Changer l’ordre d’affichage
SELECT
first_name, last_name, id
FROM
Employees;
3
Renommer un champ en sortie : alias
SELECT
first_name, last_name
AS
'Last Name', id
FROM
Employees;
There are no rows in this table

Filtrer
0
Action
Code SQL
1
Filtrer sur un champ “Texte” Parfois sensible à la casse Parfois Quote ou Double Quote
SELECT
*
FROM
Employees
WHERE
department='Marketing'
2
Sélection de champs : le champ de la clause WHERE n’est pas dans la clause SELECT
SELECT
first_name, last_name
FROM
Employees
WHERE
department
LIKE
'Marketing'
3
Insensible à la casse
SELECT
first_name, last_name
FROM
Employees
WHERE
department
LIKE
'Marketing'
4
Tous les employés dont le nom commence par s ou S. Pour le paramètre “Contient”, utiliser %. %s : se termine par, %s% : contient.
SELECT
first_name, last_name
FROM
Employees
WHERE
department
LIKE
's%'
5
Filtre sur champ Numérique
SELECT
*
FROM
Employees
WHERE
id=10
There are no rows in this table
Filtres combinés
0
Action
Code SQL
1
Clause OR
SELECT
*
FROM
Employees
WHERE
department='Marketing'
OR
department='Sales'
2
Clause AND
SELECT
*
FROM
Employees
WHERE
department='Marketing'
AND
tshirt_size = 'L'
3
Clause IS NOT
SELECT
*
FROM
Employees
WHERE
department
IS NOT
'Marketing'
4
Si pas de parenthèses, interprétation de gauche à droite Pas possible dans Excel. 9 lignes
SELECT
*
FROM
Employees
WHERE
department='Sales'
OR
department='Marketing'
AND
tshirt_size='L'
AND
first_name
IS NOT
'Rikki'
5
Avec parenthèses autour du OR. 2 lignes.
SELECT
*
FROM
Employees
WHERE
(
department='Sales'
OR
department='Marketing'
)
AND
tshirt_size='L'
AND
first_name
IS NOT
'Rikki'
There are no rows in this table
Limiter le résultat
0
Action
Code SQL
1
Clause LIMIT Renvoi les 10 premiers résultats.
SELECT
*
FROM
Employees
WHERE
tshirt_size='L'
LIMIT
10
2
Clause OFFSET Renvoie le 12e.
SELECT
*
FROM
Employees
WHERE
tshirt_size='L'
LIMIT
2
OFFSET
10
There are no rows in this table
Combiner les opérateurs
0
Action
Code SQL
1
Opérateurs > >= < <= et IS NOT
SELECT
*
FROM
Employees
WHERE
vacation_taken > 9
There are no rows in this table
Fonctions
0
Action
Code SQL
1
Fonction SUM
SELECT
SUM
(vacation_taken)
FROM
Employees
WHERE
id>5
2
Fonction AVG
SELECT
AVG
(vacation_taken)
FROM
Employees
WHERE
department='Marketing'
3
Fonction MIN et MAX
SELECT
MIN
(vacation_taken)
FROM
Employees
WHERE
department='Marketing'
4
Fonction COUNT Non vides !
SELECT
COUNT
(id)
FROM
Employees
WHERE
vacation_taken> 20
5
Fonctions UPPER et LOWER
SELECT
UPPER
(first_name)
FROM
Employees
6
Fonction LENGTH (longueur)
SELECT
first_name
FROM
Employees
WHERE
LENGTH
(first_name) > 8
There are no rows in this table
Valeur unique
0
Action
Code SQL
1
DISTINCT
SELECT DISTINCT
(tshirt_size)
FROM
Employees
WHERE
tshirt_size
IS NOT NULL
There are no rows in this table
Organiser ses données
0
Action
Code SQL
1
ORDER BY Clause ASC (toujours facultatif) et DESC
SELECT
first_name, last_name, tshirt_size
FROM
Employees
ORDER BY
tshirt_size
ASC
, first_name
DESC
2
GROUP BY : regrouper
SELECT
department,
COUNT
(department)
FROM
Employees
GROUP BY
department
ORDER BY
department
3
HAVING : filtrer sur un champ calculé de la clause SELECT. La clause peut utiliser la fonction select - ici COUNT(department) - ou son alias - ici NumberOfEmployees.
SELECT
department,
COUNT
(department) AS NumberOfEmployees
FROM
Employees
GROUP BY
department
HAVING COUNT
(department) < 10
ORDER BY
department
4
Sous-requête. Affiche 1 seule ligne.
SELECT
*
FROM
Employees
WHERE
vacation_taken = (
SELECT MAX
(vacation_taken)
FROM
Employees)
5
Sous-requête dans une autre table (Chaque employé est dans un département, chaque département est dans un Etat, ici Californie). Clause IN
SELECT
*
FROM
Employees
WHERE
department
IN
(
SELECT
name
FROM
Departments
WHERE
state='CA')
There are no rows in this table
Données de plusieurs tables
0
Action
Code SQL
1
JOIN. Retourne ici toutes les combinaisons possibles (cross join)
SELECT
first_name, last_name, name
FROM
Employees
JOIN
Departments
2
JOIN et ON. Egal à INNER JOIN
SELECT
first_name, last_name, name
FROM
Employees
JOIN
Departments
ON
Employees.id = Departments.office_managere_id
3
Retourne les noms et prénom des employés ayant suivi la formation: JOIN (46 lignes) Retourne la formation suivie par chaque employé : LEFT JOIN (100 lignes, avec des NULL)
SELECT
first_name, last_name, date_complete
FROM
Employees
LEFT
JOIN
ComplianceTraining
ON
Employees.id = ComplianceTraining.employee_id
There are no rows in this table
Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.