SQL

Jeux de caractères

À chaque connexion à MySQL, exécutez donc la commande suivante:

SET NAMES 'utf8';

ou ajoutez une option lors de la connexion:

mysql -u utilisateur -p --default-character-set=utf8

Création d’une base de données

CREATE DATABASE elevage CHARACTER SET 'utf8';

Utiliser une base de données

USE nom_de_la_BDD;

ou

mysql -u nom_utilisateur -p nom_de_la_BDD --default-character-set=utf8

Suppression d’une base de données

DROP DATABASE IF EXISTS elevage;

Exécuter un fichier externe

SOURCE Users\taguan\dossierX\monFichier.sql;

sous windows:

SOURCE C:/"Document and Settings"/dossierX/monFichier.sql;

ou

\. monFichier.sql;

ou

mysql nom_base < chemin_fichier_de_sauvegarde.sql

LOAD DATA [LOCAL] INFILE 'nom_fichier'
INTO TABLE nom_table
[FIELDS
    [TERMINATED BY '\t']
    [ENCLOSED BY '']
    [ESCAPED BY '\\' ]
]
[LINES 
    [STARTING BY '']    
    [TERMINATED BY '\n']
]
[IGNORE nombre LINES]
[(nom_colonne,...)];

nom;prenom;date_naissance
Charles;Myeur;1994-12-30
Bruno;Debor;1978-05-12
Mireille;Franelli;1990-08-23

LOAD DATA LOCAL INFILE 'personne.csv'
INTO TABLE Personne
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n' -- ou '\r\n' selon l'ordinateur et le programme utilisés pour créer le fichier
IGNORE 1 LINES
(nom,prenom,date_naissance);

Sauvegarde d’une base de données

mysqldump -u user -p --opt nom_de_la_base > sauvegarde.sql

• mysqldump : client permettant de sauvegarder les bases.
• --opt : option de mysqldump qui lance la commande avec une série de paramètres qui font que la commande s’effectue très rapidement.
• nom_de_la_base : nom de la base que l’on veut sauvegarder.
• > sauvegarde.sql : le signe > indique que l’on va donner la destination de ce qui va être généré par la commande sauvegarde.sql. Il s’agit du nom du fichier qui contiendra la sauvegarde de notre base.

La base de données est donc sauvegardée. Notez que la commande pour créer la base elle-même n’est pas sauvée. Donc si vous effacez votre base par mégarde, il vous faut d’abord recréer la base de données (avec CREATE DATABASE nom_base;), puis exécuter la commande suivante:

mysql nom_base < chemin_fichier_de_sauvegarde.sql

Moteurs de table

Préciser un moteur lors de la création de la table:

ENGINE = INNODB;

Création d’une table

CREATE TABLE Animal (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    espece VARCHAR(40) NOT NULL,
    sexe CHAR(1),
    date_naissance DATETIME NOT NULL,
    nom VARCHAR(30),
    commentaires TEXT,
    PRIMARY KEY (id)
)
ENGINE=INNODB;

Vérification d’une table

SHOW TABLES;      -- liste les tables de la base de données
DESCRIBE Animal;  -- liste les colonnes de la table avec leurs caractéristiques

Suppression d’une table

DROP TABLE Animal;

Modification d’une table

ALTER TABLE nom_table ADD ... -- permet d'ajouter quelque chose (une colonne par exemple)
ALTER TABLE nom_table DROP ... -- permet de retirer quelque chose 
ALTER TABLE nom_table CHANGE ...
ALTER TABLE nom_table MODIFY ... -- permettent de modifier une colonne

Index d’une table

Lors de la création d’une table

CREATE TABLE Animal (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    espece VARCHAR(40) NOT NULL,
    sexe CHAR(1),
    date_naissance DATETIME NOT NULL,
    nom VARCHAR(30),
    commentaires TEXT,
    PRIMARY KEY (id),
    INDEX ind_date_naissance (date_naissance),  -- index sur la date de naissance
    INDEX ind_nom (nom(10))                     -- index sur le nom (le chiffre entre parenthèses étant le nombre de caractères pris en compte)
)
ENGINE=INNODB;

CREATE TABLE nom_table (
    colonne1 INT NOT NULL,   
    colonne2 VARCHAR(40), 
    colonne3 TEXT,
    UNIQUE [INDEX] ind_uni_col2 (colonne2),     -- Crée un index UNIQUE sur la colonne2, INDEX est facultatif
    FULLTEXT [INDEX] ind_full_col3 (colonne3)   -- Crée un index FULLTEXT sur la colonne3, INDEX est facultatif
)
ENGINE=MyISAM;

Après création d’une table

ALTER TABLE nom_table
ADD INDEX [nom_index] (colonne_index [, colonne2_index ...]); --Ajout d'un index simple

ALTER TABLE nom_table
ADD UNIQUE [nom_index] (colonne_index [, colonne2_index ...]); --Ajout d'un index UNIQUE

ALTER TABLE nom_table
ADD FULLTEXT [nom_index] (colonne_index [, colonne2_index ...]); --Ajout d'un index FULLTEXT

CREATE INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);  -- Crée un index simple

CREATE UNIQUE INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);  -- Crée un index UNIQUE

CREATE FULLTEXT INDEX nom_index
ON nom_table (colonne_index [, colonne2_index ...]);  -- Crée un index FULLTEXT

Avec contrainte

CREATE TABLE nom_table (
    colonne1 INT NOT NULL,   
    colonne2 VARCHAR(40), 
    colonne3 TEXT,
    CONSTRAINT [symbole_contrainte] UNIQUE [INDEX] ind_uni_col2 (colonne2)
);

ALTER TABLE nom_table
ADD CONSTRAINT [symbole_contrainte] UNIQUE ind_uni_col2 (colonne2);

Suppression d’un index

ALTER TABLE nom_table 
DROP INDEX nom_index;

SELECT *                               -- Vous mettez évidemment les colonnes que vous voulez.
FROM nom_table
WHERE MATCH(colonne1[, colonne2, ...]) -- La (ou les) colonne(s) dans laquelle (ou lesquelles) on veut faire la recherche (index FULLTEXT correspondant nécessaire).
AGAINST ('chaîne recherchée');         -- La chaîne de caractères recherchée, entre guillemets bien sûr.
-- Exemple :
SELECT *
FROM Livre
WHERE MATCH(titre, auteur)
AGAINST ('Jules Verne');

SELECT *, MATCH(titre, auteur) AGAINST ('Jules Verne Lune')
FROM Livre;

SELECT * 
FROM nom_table
WHERE MATCH(colonne) 
AGAINST('chaîne recherchée' IN BOOLEAN MODE); -- IN BOOLEAN MODE à l'intérieur des parenthèses !

-- Recherche sur le titre, qui doit contenir "bonheur", mais ne peut pas contenir "ogres"
SELECT * 
FROM Livre
WHERE MATCH(titre)
AGAINST ('+bonheur -ogres' IN BOOLEAN MODE);

-- Recherche sur titre, qui doit contenir tout le groupe de mots entre guillemets doubles
SELECT * 
FROM Livre 
WHERE MATCH(titre) 
AGAINST ('"Terre à la Lune"' IN BOOLEAN MODE);

-- Recherche sur titre, sur tous les mots commençant par "petit"
SELECT * 
FROM Livre
WHERE MATCH(titre)
AGAINST ('petit*' IN BOOLEAN MODE);

-- Recherche sur titre et auteur, de tous les mots commençant par "d"
SELECT * 
FROM Livre
WHERE MATCH(titre, auteur)
AGAINST ('d*' IN BOOLEAN MODE);

-- Recherche sur titre, qui doit contenir un mot commençant par "petit", mais ne peut pas contenir le mot "prose"
SELECT * 
FROM Livre
WHERE MATCH(titre)
AGAINST ('+petit* -prose' IN BOOLEAN MODE); -- mix d'un astérisque avec les + et -

SELECT *
FROM Livre
WHERE MATCH(titre, auteur)
AGAINST ('Daniel' WITH QUERY EXPANSION);

INSERT

INSERT INTO Animal (espece, sexe, date_naissance) 
    VALUES ('tortue', 'F', '2009-08-03 05:12:00');

Insertion multiple

INSERT INTO Animal (espece, sexe, date_naissance, nom) 
VALUES ('chien', 'F', '2008-12-06 05:18:00', 'Caroline'),
       ('chat', 'M', '2008-09-11 15:38:00', 'Bagherra'),
       ('tortue', NULL, '2010-08-23 05:18:00', NULL);

SELECT

SELECT *
FROM table
WHERE condition
GROUP BY expression
HAVING condition
{ UNION | INTERSECT | EXCEPT }
ORDER BY expression
LIMIT count
OFFSET start

DELETE

DELETE FROM nom_table WHERE critères;
DELETE FROM Animal WHERE nom = 'Zoulou';
DELETE FROM Animal;

UPDATE

UPDATE nom_table SET col1 = val1 [, col2 = val2, ...] [WHERE ...];
UPDATE Animal SET sexe='F', nom='Pataude' WHERE id=21;
UPDATE Animal SET commentaires='modification de toutes les lignes';

WHERE

/* La liste des avions (code et nom) triés par vendeur et par quantité en stock décroissants */
SELECT productCode, productName
FROM products
WHERE productLine = 'Planes'
ORDER BY productVendor DESC, quantityInStock DESC
/* RESULTAT ==> 12 lignes / 1900s Vintage Tri-Plane */

/* La liste des produits (code, nom, échelle et quantité) qui ont une échelle soit de 1:10, soit de 1:18 triés par quantité en stock décroissante */
SELECT productCode, productName, productScale, quantityInStock
FROM products
WHERE productScale IN ('1:10', '1:18')	/* équivalent à WHERE productScale = '1:10' OR productScale = '1:18' */
ORDER BY quantityInStock DESC
/* RESULTAT ==> 48 lignes / 1995 Honda Civic */

/* La liste des produits (nom, vendeur et prix de vente) qui sont vendus au moins 132$ triés par nom du produit */
SELECT productName, productVendor, MSRP
FROM products
WHERE MSRP >= 132
ORDER BY productName
/* RESULTAT ==> 24 lignes / 1903 Ford Model A */

/* La liste des produits (code, nom et prix d'achat) des produits achetés au moins 60$ au plus 90$ triés par prix d'achat */
SELECT productCode, productName, buyPrice
FROM products
WHERE buyPrice BETWEEN 60 AND 90	/* équivalent à WHERE buyPrice >= 60 AND buyPrice <= 90 */
ORDER BY buyPrice
/* RESULTAT ==> 34 lignes / 1937 Lincoln Berline */

SELECT * FROM Animal WHERE nom LIKE '%Lu%'; -- insensible à la casse

SELECT * FROM Animal WHERE nom LIKE BINARY '%Lu%'; -- sensible à la casse

SELECT * FROM Animal 
WHERE nom IN ('Moka', 'Bilba', 'Tortilla', 'Balou', 'Dana', 'Redbul', 'Gingko');

Colonnes calculées

/* La liste des motos (nom, vendeur, quantité et marge) triés par marge décroissante */
SELECT productName, productVendor, quantityInStock, (MSRP - buyPrice) AS margin
FROM products
WHERE productLine = 'Motorcycles'
ORDER BY margin DESC
/* RESULTAT ==> 13 lignes / 2003 Harley-Davidson Eagle Drag Bike */

/* La liste des commandes (numéro, date de commande, date d'expédition, écart en jours entre les deux dates et statut) qui sont en cours de traitement ou qui ont été expédiées et ont un écart de plus de 10j triés par écart décroissant puis par date de commande */
SELECT orderNumber, orderDate, shippedDate, (shippedDate - orderDate) AS processTime, status
FROM orders
WHERE status = 'In Process' OR (status = 'Shipped' AND (shippedDate - orderDate) > 10)
ORDER BY processTime DESC, orderDate
/* RESULTAT ==> 33 lignes / 10165 */

/*La liste des produits (nom et valeur du stock à la vente) des années 1960 */
SELECT productName, (quantityInStock * MSRP) AS stockValue
FROM products
WHERE productName LIKE '196%'
/* RESULTAT ==> 16 lignes / 1969 Harley Davidson Ultimate Chopper */

GROUP BY

/* Le prix moyen d'un produit vendu par chaque vendeur triés par prix moyen décroissant */
SELECT productVendor, AVG(MSRP) AS averagePrice
FROM products
GROUP BY productVendor
ORDER BY averagePrice DESC
/* RESULTAT ==> 13 lignes / Welly Diecast Productions / 113.9325 */

/* Le nombre de produits pour chaque ligne de produit */
SELECT productLine, COUNT(productCode)
FROM products
GROUP BY productLine
/* RESULTAT ==> 7 lignes / Classic Cars / 38 */

/* Le total du stock et le total de la valeur du stock à la vente de chaque ligne de produit pour les produits vendus plus de 100$ trié par total de la valeur du stock à la vente */
SELECT productLine, SUM(quantityInStock) AS totalStock, SUM(quantityInStock * MSRP) AS totalStockValue
FROM products
WHERE MSRP > 100
GROUP BY productLine
ORDER BY totalStockValue
/* RESULTAT ==> 7 lignes / Ships / 429177.74 */

/* La quantité du produit le plus en stock de chaque vendeur trié par vendeur */
SELECT productVendor, MAX(quantityInStock) AS maxInStock
FROM products
GROUP BY productVendor
ORDER BY productVendor
/* RESULTAT ==> 13 lignes / Autoart Studio Design / 9354 */

/* Le prix de l'avion qui coûte le moins cher à l'achat */
SELECT MIN(buyPrice) AS cheapestPricePlane
FROM products
WHERE productLine = 'Planes'
/* RESULTAT ==> 1 ligne / 29.34$ */

/* Le crédit des clients qui ont payé plus de 20000$ durant l'année 2004 trié par crédit décroissant */
SELECT customerNumber, SUM(amount) AS totalCredit
FROM payments
WHERE paymentDate BETWEEN '2004-01-01' AND '2004-12-31'
GROUP BY customerNumber
HAVING totalCredit > 20000
ORDER BY totalCredit DESC
/* RESULTAT ==> 69 lignes / 141 / 293 765.51 */

INNER JOIN

/* La liste des employés (nom, prénom et fonction) et des bureaux (adresse et ville) dans lequel ils travaillent */
SELECT lastName, firstName, jobTitle, addressLine1, addressLine2, city
FROM employees
INNER JOIN offices ON offices.officeCode = employees.officeCode
/* RESULTAT ==> 23 lignes / Diane Murphy */

/* La liste des clients français ou américains (nom du client, nom, prénom du contact et pays) et de leur commercial dédié (nom et prénom) triés par nom et prénom du contact */
SELECT customerName, contactLastName, contactFirstName, country, lastName, firstName
FROM customers
INNER JOIN employees ON employees.employeeNumber = customers.salesRepEmployeeNumber
WHERE country IN ('France', 'USA')
ORDER BY contactLastName, contactFirstName
/* RESULTAT ==> 48 lignes / Miguel Barajas */

/* La liste des lignes de commande (numéro de commande, code, nom et ligne de produit) et la remise appliquée aux voitures ou motos commandées triées par numéro de commande puis par remise décroissante */
SELECT orderNumber, orderdetails.productCode, productName, productLine, (MSRP - priceEach) AS discount
FROM orderdetails
INNER JOIN products ON products.productCode = orderdetails.productCode
WHERE productLine IN ('Classic Cars', 'Vintage Cars', 'Motorcycles')
ORDER BY orderNumber, discount DESC
/* RESULTAT ==> 2026 lignes / 34 */

Requêtes complexes

/* Le total des paiements effectués de chaque client (numéro, nom et pays) américain, allemand ou français de plus de 50000$ trié par pays puis par total des paiements décroissant */
SELECT customers.customerNumber, customerName, country, SUM(amount) AS totalPayment
FROM customers
INNER JOIN payments ON payments.customerNumber = customers.customerNumber
WHERE country IN ('France', 'Germany', 'USA')
GROUP BY customers.customerNumber, customerName, country
HAVING totalPayment > 50000
ORDER BY country, totalPayment DESC
/* RESULTAT ==> 38 lignes / 146 / 130305.35 */

/* Le montant total de chaque commande (numéro et date) des clients New-Yorkais (nom) trié par nom du client puis par date de commande */
SELECT customerName, orders.orderNumber, orderDate, SUM(quantityOrdered * priceEach) AS totalAmount
FROM customers
INNER JOIN orders ON orders.customerNumber = customers.customerNumber
INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
WHERE city = 'NYC'
GROUP BY city, customerName, orderNumber, orderDate
ORDER BY customerName, orderDate

/* RESULTAT ==> 16 lignes / Classic Legends / 10115 / 21665.98 */

Sous-requêtes

SELECT *
FROM nom_table1
WHERE [ROW](colonne1, colonne2) = ( -- le ROW n'est pas obligatoire
    SELECT colonneX, colonneY
    FROM nom_table2
    WHERE...);                      -- Condition qui ne retourne qu'UNE SEULE LIGNE

SELECT id, nom, espece_id
FROM Animal
WHERE espece_id IN (
    SELECT id 
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

-- Sélectionne les lignes de la table Animal dont espece_id est inférieur à au moins une des valeurs sélectionnées dans la sous-requête
SELECT *
FROM Animal
WHERE espece_id < ANY (
    SELECT id
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

-- Sélectionne les lignes de la table Animal dont espece_id est inférieur à toutes les valeurs sélectionnées dans la sous-requête
SELECT *
FROM Animal
WHERE espece_id < ALL (
    SELECT id
    FROM Espece
    WHERE nom_courant IN ('Tortue d''Hermann', 'Perroquet amazone')
);

-- sélectionne toutes les races dont on ne possède aucun animal.
SELECT * FROM Race
WHERE NOT EXISTS (SELECT * FROM Animal WHERE Animal.race_id = Race.id);

PHP

$db = null;

function openBDD() {
    global $db;
    try {
        $db = new PDO("mysql:host=localhost;dbname=Cinema;charset=utf8", "root", "password"); 
    }
    catch (Exception $e) {
        die ("error, not good : " . $e->getMessage());
    }
}

function closeBDD() {
    global $db;
    $db = null;
}

Requête

function descRealisator() {
    global $db;
    $sql = "SELECT * FROM directors ORDER BY birthDate";
    $req = $db->query($sql);
    $req->setFetchMode(PDO::FETCH_OBJ); // création d'objet
    
    $description = "";
    while ($data = $req->fetch()) {
        $description .= "<section>";
        $description .= "<h2>".$data->firstName." ".$data->lastName."</h2>";
        $description .= "<p>".$data->comment."</p>";
        $description .= "</section>";
    }
    echo $description;
}