Web

SQL

Sources :


A NOTER :

  • VARCHAR ou TEXT = “ ”
  • INT ou BIGINT il n’y a pas besoin d’utiliser de guillemet, il suffit juste d’indiquer le nombre.
  • Attention!! Ne pas oublier les ; à la fin d’une requête!!


Dans un terminal :

  1. Se connecter à mysql

mysql -u root -p

  1. Définir quel base de donnée on utilise

USE [nom_base];


Vocabulaire :

Champs : nom des colonnes

Voir toutes les tables de la base de données

SHOW tables;

Voir toutes les bases de données

SHOW DATABASES;

Pour voir les champs (nom de colonnes) d’une table

SHOW  COLUMNS FROM [nom_table];

Pour voir le contenu d’une table

SELECT * FROM [table];

Structure d’une requête type :

SELECT [nom_champ], [nom_champ2]

FROM [noms_table] [nom_table2]

WHERE [Requête];

Désigne tous les champs

* (toute seule sans rien autour)

Compter les entrées d’un tableau :

SELECT COUNT(*) FROM [table];


Recherche

Commande LIKE = recherche dans les chaînes de caractères

Structure d’une requête LIKE dans WHERE :

WHERE [champ] LIKE [modele]

modele :

  • LIKE ‘%a’ : toutes les chaines de caractère qui se termine par un « a ».
  • LIKE ‘a%’ : ce modèle permet de rechercher toutes les lignes de « colonne » qui commence par un « a ».
  • LIKE ‘%a%’ : ce modèle est utilisé pour rechercher tous les enregistrement qui utilisent le caractère « a ».
  • LIKE ‘pa%on’ : ce modèle permet de rechercher les chaines qui commence par « pa » et qui se terminent par « on », comme « pantalon » ou « pardon ».
  • LIKE ‘a_c’ : peu utilisé, le caractère « _ » (underscore) peut être remplacé par n’importe quel caractère, mais un seul caractère uniquement (alors que le symbole pourcentage « % » peut être remplacé par un nombre incalculable de caractères . Ainsi, ce modèle permet de retourner les lignes « aac », « abc » ou même « azc ».


Ajout / Suppression / Modification

UPDATE

La commande UPDATE permet d’effectuer des modifications sur des lignes existantes. Très souvent cette commande est utilisée avec WHERE pour spécifier sur quelles lignes doivent porter la ou les modifications.

Structure d’un UPDATE type :

UPDATE [table]
SET [colonne_à_modifier] = ‘nouvelle_valeur’, [colonne_à_modifier] = ‘nouvelle_valeur’

WHERE [colonne_à_modifier] = ‘valeur_à_remplacer’

AND [colonne_à_modifier] = ‘valeur_à_remplacer’;

Exemple :

UPDATE film
SET title = ‘Tarzan’

WHERE title= ‘Dalida’;

INSERT INTO

Insérer une ligne en modifiant TOUTES les colonnes prédéfinies de la table

INSERT INTO table VALUES (‘valeur’, ‘valeur’);

Insérer une ligne pour modifiant seulement les colonnes voulues

INSERT INTO table (nom_colonne_1, nom_colonne_2, …)
VALUES (‘valeur 1’, ‘valeur 2’, …);

Exemple:

INSERT INTO film (nom, année)
VALUES (‘tarzan’, “2016’’);

Insérer plusieurs lignes à la fois

INSERT INTO client (prenom, nom, ville, age)
VALUES
(‘Rébecca’, ‘Armand’, ‘Saint-Didier-des-Bois’, 24),
(‘Aimée’, ‘Hebert’, ‘Marigny-le-Châtel’, 36),
(‘Marielle’, ‘Ribeiro’, ‘Maillères’, 27),
(‘Hilaire’, ‘Savary’, ‘Conie-Molitard’, 58);


Jointures

Dans le langage SQL la commande INNER JOIN, aussi appelée EQUIJOIN, est un type de jointures très communes pour lier plusieurs tables entre-elles. Cette commande retourne les enregistrements lorsqu’il y a au moins une ligne dans chaque colonne qui correspond à la condition.

Structure:

SELECT *
FROM table1
INNER JOIN table2 ON table1.id1 = table2.id2

ou

SELECT *
FROM table1
INNER JOIN table2
WHERE table1.id1 = table2.id2

Exemples:

SELECT city, country

FROM city

INNER JOIN country

ON city.country_id=country.country_id

WHERE city.city=Adoni;

SELECT city.city, country.country

FROM city

INNER JOIN country

ON city.country_id = country.country_id

WHERE city.city = ‘Adoni’;

SELECT c.city, co.country

FROM city

AS c

INNER JOIN country  

AS co

ON c.country_id = co.country_id

WHERE c.city = ‘Adoni’;


Comment ça fonctionne?

Serveur

Un serveur contient plusieurs bases. Une base contient un ensemble de données, on peut résumer ainsi : une base de données == une application

Base de données

Une base de données contient plusieurs tables. Les tables sont un set de données (utilisateurs, articles, commentaires …)

Table

Chaque table est composée d’une série de colonnes (id, nom, prénom, date de naissance, date de dernière connexion …). Chaque colone a un type. Le type indique les informations qu’on peut enregistrer dans la colonne.

Types courants :

  • int (nombre)
  • varchar 255 (texte court) ou text (texte long)
  • date (date)
  • blop (binaire, pour les images)

Chaque ligne de la table correspond à un enregistrement

Illustration

serveur base de données

|

|- base de données 1 : 100m

|          |- table 1 : coureurs

|          |       |- id (int)

|          |       |- nom (varchar 255)

|          |       |- prenom (varchar 255)

|          |- table 2 : résultats

|                  |- coureurId (int)

|                  |- temps (int)

|

|- base de données 2 : PopSchool

|          |- table 1 : promotions

|          |       |- id (int)

|          |       |- nom (varchar 255)

|          |       |- datedébut (date)

|          |       |- datefin (date)

|          |- table 2 : élèves

|          |       |- id (int)

|          |       |- nom (varchar 255)

|          |       |- promotionId (int)

|          |       |- datedenaissance (date)

|          |- table 3 : attestations

|                  |- eleveId (int)

|                  |- attestation (int. 0: non, 1: oui)

|

|- base de données 3 : annuaire de jeux

|- table 1 : constructeurs

|       |- id (int)

|       |- nom (varchar 255)

|- table 2 : consoles

|       |- id (int)

|       |- constructeurId (int)

|       |- nom (varchar 255)

|- table 3 : jeux

|       |- id (int)

|       |- nom (varchar 255)

|- table 4 : consolesJeu

|- jeuId (int)

|- consoleId (int)


Schéma de la table :

base de données 3 : annuaire de jeux

|- table 1 : constructeurs

|       |- id (int)

|       |- nom (varchar 255)

|- table 2 : consoles

|       |- id (int)

|       |- constructeurId (int)

|       |- nom (varchar 255)

|- table 3 : jeux

|       |- id (int)

|       |- nom (varchar 255)

|- table 4 : consolesJeu

|- jeuId (int)

|- consoleId (int)

Stocker ou compter ?

Il existe un dilemme éternel en SQL : stocker ou compter ?

Doit-on stocker dans une base à part le meilleur score pour un mot ?

  • + l’information est plus rapide et plus facile à stocker
  •  on créera une table inutile, on risque les race condition ou situation de concurrence.

Il se passe quoi si le score minimal stocké est différent du score minimal constaté dans la base des essais ? Ce genre de choses arrivent via des bugs ou des sa race de race condition (deux enregistrement d’un même meilleur score dans un cours laps de temps)

Exemple de race condition:

Supposons un programme mal pensé

  • Aline commence une partie, le programme récupère le meilleur score (5)
  • Samuel commence une partie, le programme récupère le meilleur score (5)
  • Aline a terminé, son score de 3 est enregistré dans les essais et dans la table des meilleurs score
  • Samuel a terminé, son score de 4 est enregistré dans les essais et dans la table des meilleurs score

Nous avons donc 3 essais : 5, 3 et 4. Cependant, la table du meilleur score contient 4 comme record

AI est auto-increment, une case à cocher à la création. Le champ sera une clef primaire, c’est à dire que chaque valeur sera unique et servira à identifier chaque ligne


Index, auto-increment

Clef primaire, index

Les colonnes désignées comme clef ou index servent à accéder rapidement aux données. Ces données seront uniques: on n’aura jamais deux fois la même valeur. Le plus souvent une telle colonne aura pour nom id, sera du type int (un nombre donc) et sert à *id*entifier la ligne. Un peu comme ton numéro de sécurité sociale, oui. Mais en plus simple.

auto-increment

On a une colonne d’index, la belle affaire. On n’a pas le droit d’y avoir des doublons. Pour ce faire, on peut indiquer que ce champ sera automatiquement rempli à chaque requête INSERT (voir plus bas) avec la valeur précédente + 1. Par exemple, si le dernier enregistrement était 4, automatiquement la valeur suivante sera 5.

SQL dispose de 4 requêtes de base

  • INSERT
  • SELECT
  • UPDATE

  • DELETE Aussi intelligemment appelés CRUD (Create, Read, Update, Delete) …

Ces dernières fonctionnent partout : MySQL, PostgeSQL, H2DB & cie

Voir mémo SQL

PHPMyAdmin affiche toujours la requête de l’opération que vous réalisez, vous pouvez vous en servir pour découvrir comment rédiger une requête.


INSERT

//insérer 1 mot

INSERT INTO mots (‘mot‘) VALUES (‘tartiflette’);

//insérer plusieurs mots
INSERT INTO mots (‘mot‘) VALUES (‘trotinette’),(‘éléphant’),(‘livre’),(‘tomate’);

INSERT crée une nouvelle ligne dans une table de données

Il est inutile d’indiquer les champs auto-incrémentaux, ils ont une valeur automatiquement attribuée. D’ailleurs, regardez dans votre table comment la colonne id s’est vue attribuer automatiquement des valeurs.


SELECT

//affiche toutes les infos de la table mots

SELECT * FROM mots;

//Affiche toutes les infos ayant un id suppérieux (plus grand) à 3
SELECT * FROM mots WHERE id > 3;

SELECT récupère des informations à partir d’une table de données


UPDATE

//remplace le mot ayant un id de 1 contenant ‘mot’ par ‘cornichon’

UPDATE mots SET ‘mot‘=’cornichon’ WHERE id=1;

UPDATE met à jour une ligne dans une table de données


DELETE

//efface l’entrée ayant pour id : 2

DELETE FROM mots WHERE id=2;

DELETE supprime une ligne dans une table de données


Lexique

  • SQL structured query language
  • BDD base de données
  • DB database
  • SGBD système de gestion de base de données
  • DBMS database management system
  • DBA database administrator

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.