SQL Basics
SQL Basics
Voir et sélectionner des champs
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
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
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
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
Action
Code SQL
1
Opérateurs > >= < <= et IS NOT
SELECT * FROM Employees WHERE vacation_taken > 9
There are no rows in this table
Fonctions
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
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
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
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
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.