Cheatography
Login Register

Bases de données relationnelles avec SQL Cheat Sheet

A Programming › MySQL cheat sheet in français (French) by Piotezaza

Résumé des principes mentionnés dans le cours OpenClassrooms "Implémentez vos bases de données relationnelles avec SQL".

MySQL SQL Bdd Oc
6,946 views 1,262 downloads 24 Jan 2024 History
PRs
Download

Abrévi­ations

BDD
Base de données
SGBD
Système de gestion de bases de données
SQL
Structured Query Language (en français, langage de requête struct­urée)

Défini­tions

SGBD
Logiciel qui va manipuler les données d’une base. C’est ce logiciel qui commande les intera­ctions avec la base pour y récupérer, ajouter, modifier ou supprimer des données.
SQL
Langage inform­atique qui permet d'in­teragir avec les bases de données.

Les différents SGBD

MySQL
Le plus connu des SGBD. C’est le plus utilisé, car il était open-s­ource avant son rachat par Oracle Corpor­ation. Connu pour être notamment utilisé par les sites WordPress.
MariaDB
"­Cop­ie" open-s­ource de MySQL qui suit les mêmes règles de langage que MySQL.
Oracle Database
Très cher, mais utile pour traiter un très gros volume de données. Ce sont presque exclus­ivement les grandes entrep­rises qui l’util­isent. Oracle tend à se faire rattraper par les SGBD open-s­ource type MariaDB ou Postgr­eSQL. Il est en réelle perte de vitesse sur le marché.
PostgreSQL
Grand SGBD open-s­ource disponible sur le marché. C’est le SGBD qui suit le plus les recomm­and­ations du SQL, ainsi que le plus rapide (ces dernières années). Il est notamment utilisé par Instagram ou par Spotify.
SQLite
Il stocke toute la base de données dans un seul et unique fichier. Peu propice à l’util­isation sur un grand nombre de données. Recommandé pour développer une base de données “en local”.

Quel est le lien entre le SGBD et le langage SQL ?

Quel est le lien entre le SGBD et le langage SQL ?
Un utilis­ateur arrive sur Foodly. Il scanne un aliment présent dans son superm­arché pour connaître ses caract­éri­stiques nutrit­ion­nelles. Que va faire l'ap­pli­cation ?
L’appl­ication va traduire cette recherche en SQL et l’envoyer au SGBD, qui va récupérer l’aliment en question dans le stockage de la base de données, pour ensuite le redonner à l’appl­ication. L’util­isateur retrouvera ainsi son aliment avec toutes ses caract­éri­sti­ques.

Commandes terminal MySQL

mysql -u root -p
Acceder à MySQL.
mysql -u root -p nom_de­_la­_ba­se_­de_­donnees < nom_du­_fi­chi­er.sql
Permet de "­cha­rge­r" une base de données en une seule commande, en chargeant le fichier.
use nom_de_la_base_de_donnees;
source nom_du­_fi­chi­er.sql;
Permet de "­cha­rge­r" les données d'une bdd en chargeant le fichier.
exit;
Retourner dans le terminal en mode "­nor­mal­".

Rédaction des valeurs selon leur type

Rédaction des valeurs selon leur type

Effectuer des opérations

SELECT COUNT(*)
FROM utilisateur
WHERE email LIKE "­%gm­ail.co­m";
COUNT(*) permet de compter le nombre de lignes répondant au filtre parmi toutes les colonnes.
SELECT COUNT(nom)
FROM utilisateur
WHERE email LIKE "­%gm­ail.co­m";
COUNT(nom) permet de compter le nombre de lignes répondant au filtre dans la colonne nom.
SELECT COUNT(­DIS­TINCT nom)
FROM utilisateur
WHERE email LIKE "­%gm­ail.co­m";
COUNT(­DIS­TINCT nom) permet d'éviter les doublons.
SELECT COUNT(­DIS­TINCT nom) AS "­Ali­as"
AS permet de créer un alias à la colonne précéd­emment citée.
SELECT MAX(sucre) AS "taux de sucre maximum"
FROM aliment;
Nous donne le maximum de la colonne sur la sélection.
MIN
Nous donne le minimum de la colonne sur la sélection.
AVG
Nous donne la moyenne de la colonne sur la sélection.
SUM
Nous donne la somme de la colonne sur la sélection.
SELECT ROUND(­AVG­(ca­lor­ies)) AS "­cal­ories moyennes des aliments > 30g"
FROM aliment
WHERE calories > 30;
ROUND(­AVG­(ca­lor­ies)) permet d'ar­rondir la valeur retournée.
UPPER
Transf­ormer le texte en majusc­ules.
NOW
Retourne la date actuelle.

Filtres SQL

WHERE colonne = valeur
WHERE peut s'ex­écuter avec SELECT, mais aussi avec n’importe quelle autre commande : vous pouvez l’utiliser avec UPDATE ou DELETE pour ne mettre à jour ou supprimer qu’un objet spécif­ique.
>
Supérieur à
<
Inférieur à
>=
Supérieur ou égal à
<=
Inférieur ou égal à
LIKE = "­tex­te"
Permet de sélect­ionner les objets dont le texte d’une colonne répond à un modèle spécif­ique.
"­%gm­ail.co­m"
Texte se terminant par "­gma­il.c­om­".
"­gma­il.c­om­%"
Texte commençant par "­gma­il.c­om­".
"­%gm­ail.co­m%"
Texte comprenant "­gma­il.c­om­" au début ou à la fin.
AND
Permet d'aj­outer un filtre supplé­men­taire avec la condition ET
OR
Permet d'aj­outer un filtre supplé­men­taire avec la condition OU
Le caractère % à un rôle très spécif­ique. Il va permettre de faire corres­pondre des schémas spécif­iques, on parle parfois de pattern.

Types et options de champs

PRIMARY KEY (option)
Champ spécial obliga­toire dans toutes les tables. Indique à MySQL que ce champ sera l'id­ent­ifiant permettant d'id­ent­ifier les objets.
NOT NULL (option)
Ce champ ne peut pas être nul.
AUTO_I­NCR­EMENT (option)
Ce champ sera créé par MySQL automa­tiq­uement, pas besoin de s'en soucier ! MySQL va utiliser l'id précédent et y ajouter +1 lors de l'ajout d'un nouvel objet.
UNIQUE (option)
Ce champ ne peut pas avoir la même valeur en double.
DEFAULT value (option)
Ce champ sert à indiquer une valeur par défaut. Utile pour ne pas avoir à spécifier une valeur tout le temps.
INTEGER (type)
Champ numérique sous forme de nombre entier.
VARCHA­R(100) (type)
Champ sous forme de texte, limité à 100 caractères
FLOAT (type)
Ce champ contiendra des chiffres décimaux.
BOOLEAN (type)
Ce champ ne peut stocker que les valeurs true (vrai) ou false (faux).
Une option dans un champ est un attribut optionnel qui va modifier le compor­tement de ce champ. Le type lui est obliga­toire !

Commandes SQL

CREATE DATABASE nomdel­abase;
Création d'une nouvelle base de données.
USE nomdel­abase;
Utiliser une base de données.
SHOW DATABASES;
Montre toutes les bases de données.
CREATE TABLE utilis­ateur (
id INTEGER NOT NULL AUTO_I­NCR­EMENT PRIMARY KEY,
nom VARCHAR(100),
prenom VARCHAR(100),
email VARCHA­R(255) NOT NULL UNIQUE
);
Création d'une table.
SHOW TABLES;
Affiche les tables exista­ntes.
SHOW COLUMNS FROM nomdel­atable;
Affiche le schéma d'une table.
FROM nomdel­atable
Spécifie la table de la base de donnée qui nous intéresse
INSERT INTO `nomde­lat­able` (`nom`, `prenom`, `email`)
VALUES
('Doe', '­Joh­n', 'john.doe@gmail.com'),
('Smith', '­Jan­e', '­jan­e@h­otm­ail.co­m');
Permet d'in­sérer des données dans une table.
SELECT *
FROM `nomde­lat­able`;
SELECT indique à MySQL que nous souhaitons récupérer de la donnée ;
* indique que l’on souhaite récupérer toutes les colonnes (ou champs) présents dans cette table ;
FROM permet à MySQL de comprendre depuis quelle table nous souhaitons récupérer de la donnée.
SELECT `nom`, `prenom`, `email`
FROM `nomde­lat­able`;
Indiquer les colonnes à afficher permet de choisir les champs que MySQL va montrer.
UPDATE `nomdelatable`
SET `email` = 'iloverammstein@gmail.com'
WHERE `id` = '­1';
UPDATE table mettre à jour de la donnée en indiquant la table dans laquelle se trouve(nt) le ou les objets que l'on veut modifier.
SET colonne = valeur sert à indiquer quelles sont la ou les colonnes à modifier, et quelles sont la ou les valeurs qu’elles doivent désormais prendre.
DELETE FROM `nomdelatable`
WHERE `id` = '­1';
Supprime une ligne de table en fonction du filtre.
DROP TABLE `nomde­lat­able`;
Supprime toutes les données d'une table et la table elle-même.
DROP DATABASE `nomde­lab­ase`;
Supprime entièr­ement et de façon irréve­rsible la base de données.
ORDER BY DATABASE `nom` ASC;
Ce mot clé vous permet d’ordonner une colonne par ordre croissant (ascending en anglais, d’où le mot clé SQL ASC), ou décroi­ssant (descending en anglais, soit le mot clé DESC).
CREATE VIEW utilis­ate­urs­_gm­ail_vw AS
 ( SELECT *
   FROM utilisateur
   WHERE email LIKE "%gmail.com"
 );
Créé une vue. MySQL a un système de “vues” qui permet de créer des tables tempor­aires à partir d’une commande SQL. Il est donc possible de “sauveg­arder” une commande SQL.
SELECT * FROM utilis­ate­urs­_gm­ail_vw;
Utilis­ation d'une vue. À noter que _vw permet de faire la distin­ction avec les "­vra­ies­" tables.
SELECT *
FROM utilisateur
JOIN langue
ON utilis­ate­ur.l­an­gue_id = langue.id;
La table langue a été jointe grâce à JOIN langue.
Pour pouvoir faire cette jointure, il faut préciser à MySQL la corres­pon­dance entre la table langue et la table utilis­ateur. Ici, cette corres­pon­dance est effectuée via la clé langue_id pour la table utilis­ateur et id pour la table langue. Celà se fait grâce à ON `utili­sat­eur­`.`­lan­gue_id` = `langu­e`.`id`.
SELECT *
FROM utilisateur
JOIN utilis­ate­ur_­aliment
ON (utili­sat­eur.id = utilisateur_aliment.utilisateur_id)
JOIN aliment
ON (alime­nt.id = utilis­ate­ur_­ali­men­t.a­lim­ent­_id);
- MySQL sélect­ionne tous les utilis­ateurs avec SELECT * FROM utilis­ateur
- On joint la table utilis­ate­ur_­aliment avec JOIN utilis­ate­ur_­aliment
- On relie en consid­érant que l’id de l’util­isateur est stocké en tant que utilis­ate­ur_id dans la table utilis­ate­ur_­aliment ON (utili­sat­eur.id = utilis­ate­ur_­ali­men­t.u­til­isa­teu­r_id)
- À ce JOIN , on lie de la donnée de la table aliment, soit un nouveau JOIN avec JOIN aliment
- On précise que l’id de l’aliment est stocké dans utilis­ate­ur_­aliment en tant que aliment_id avec ON (alime­nt.id = utilis­ate­ur_­ali­men­t.a­lim­ent_id)
ALTER TABLE nomdel­atable ADD colonne FLOAT;
Modifi­cation de la table en ajoutant une colonne de type FLOAT.
ALTER TABLE nomdel­atable DROP colonne;
Modifi­cation de la table en supprimant une colonne.
ALTER TABLE nomdel­atable MODIFY colonne;
Modifi­cation de la table en changeant le type de la colonne.
ALTER TABLE nomdel­atable CHANGE truc colonne;
Modifi­cation de la table en changeant le nom de la colonne.
ALTER TABLE aliment
ADD FOREIGN KEY (famille_id)
REFERENCES famille (id)
ON DELETE CASCADE;
FOREIGN KEY (famil­le_id) indique que la colonne “famil­le_id” est une clé étrangère
REFERENCES famill­e(id) on indique ensuite ce à quoi cette clé fait référence
ON DELETE permet de savoir quel compor­tement avoir en cas de suppre­ssion de la référence
 Options :
 - RESTRICT ou NO ACTION empêche la suppre­ssion sous conditions
 - SET NULL supprime la référence et remplace les objets par NULL
 - CASCADE supprime tous les objets reliés
ALTER TABLE aliment
ADD FOREIGN KEY (famil­le_id)
REFERENCES famille (id)
ON DELETE CASCADE ON UPDATE CASCADE;
Options de ON UPDATE :
- RESTRICT ou NO ACTION empêche la mise à jour sous conditions
- SET NULL met à jour la référence et remplace les objets par NULL
- CASCADE met à jour tous les objets reliés

Badges

Downloaded from Unusual Locations × 4

Comments

No comments yet.

Cheatography n. The art of mapping complex subjects into clear, instantly navigable reference guides.