Je ne parle ici que du langage, pas des bases de données, ni des relations
J'ai utilisé de nombreux documents, en particulier http://www.commentcamarche.net/sql/ et http://dev.mysql.com/doc/
SQL est indépendant de la casse, mais je mettrai les mots clef en majuscules pour les faire ressortir. Les espaces sont des séparateurs, donc interdits dans les noms de tables ou de colonnes. Une commande est envoyée à MySQL par son ; final (vous pouvez donc aller à la ligne dans une commande, ainsi qu'indenter).
En local (sous linux) : "mysql" ou "mysql -u nomuser -p" ou "mysql -h hôte -u nomuser -p".
On peut également utiliser OpenOffice
Il existe une interface Web : phpMyAdmin. Cela oblige de mettre un serveur Web sur le serveur MySQL. Mais c'est d'un usage très intuitif.
Il y a dans MySQL toute une gestion des utilisateurs, avec définition de droits divers pour chaque table, (grâce à la commande GRANT ACCESS), mais nous n'en parlerons pas pour l'instant, car c'est notre administrateur réseau qui le gère.
Se connecter à une base : CONNECT base; (SHOW DATABASES; pour voir lesquelles existent (du moins celles dont on a accès), CREATE DATABASE base; pour la créer, DROP DATABASE base; pour l'effacer...).
CREATE TABLE voitures ( Numero VARCHAR(12) unique, Marque VARCHAR(15), Modele VARCHAR(15), Serie VARCHAR(20), Km INTEGER, Prix FLOAT, MiseEnCirculation DATE, PRIMARY KEY(Numero) );
Les types possibles sont CHAR(n) (chaîne de caractères de longueur n fixe), VARCHAR(n) si la longueur est variable (<n), INTEGER (entier 32 bits, pouvant valoir + ou - 4G), SMALLINT (entier 16 bits, + ou - 32K), FLOAT (nombre à virgule), NUMERIC(n) ou NUMERIC(n,d) (entiers ou flottants dont un veut définir exactement le nombre de chiffres), DATE (2006-02-28), TIME (11:59:59.99) TIMESTAMP (date+time), et des tas d'autres qui dépendent du SGBD.
Chaque champ peut avoir des attributs : UNIQUE (pas deux identiques dans toute la colonne), NOT NULL (ne peut être vide), DEFAULT(valeur), AUTO_INCREMENT (donne automatiquement une nouvelle valeur si vous n'en donnez pas, utile pour les clés), PRIMARY KEY.
CREATE TABLE clients( Ref INTEGER unique default 1 auto_increment not null, Nom VARCHAR(30) NOT NULL, ...etc... );
En général il faut une clé primaire dans chaque table. On peut donner l'attribut PRIMARY KEY à un champ, ou utiliser la forme donnée dans l'exemple "voitures" qui permet de spécifier plusieurs champs (par exemple PRIMARY KEY(Nom,Prenom) accepte des frères (même nom) ou des prénoms identiques, mais refuse un deuxième couple nom,prénom identique).
INSERT INTO voitures
VALUES ('1234 RD 56','Renault','Twingo','1.2L',49700,4700,'1999-08-01'),
('1111 FGH 75','Renault','Twingo','expression',102000,3200,'2001-5-15'),
('2222 DAC 75','Renault','Espace','DCI',85000,15900,'2000-01-18'),
('4321 ABC 67','Renault','Espace','privilege 120CV',17000,23900,'2004-05-12'),
('4444 ZA 67','Renault','Espace','TD',157000,11900,'2000-04-22'),
('1252 XC 67','Ford','Fiesta','1.4 CLX',67000,3000,'1992-08-27' ),
('5544 BCD 57','Volkswagen','Golf','IV TDI 100CV',45000,11000,'2003-9-15'),
('5555 BCD 57','Volkswagen','Coccinelle',NULL,45000,NULL,'1972-9-15') ;On peut aussi directement mettre plusieurs Ici, on donne tous les champs, dans l'ordre de leur définition. Mais si on veut les donner dans un autre ordre, où qu'on ne se rappelle plus de l'ordre, ou qu'on veuille laisser vide (NULL) certains champs : INSERT INTO nomtable(col1,col2,col3) VALUES(val1,val2,val3);
SELECT [DISTINCT] <liste des noms de colonnes> | * | <calcul> FROM <Liste des tables> [WHERE <condition logique>] [GROUP BY...] [HAVING...] [ORDER BY <nom_ou_numéro_de_colonne> [ASC|DESC] ]
conditions logiques : AND, OR, NOT, comparateurs arithmétiques (=, != ou <>, >, <, >=, <=), comparateurs de chaîne (IN, BETWEEN, LIKE), opérateurs arithmétiques (+, -, *, /, %, &, -, ^, ~), opérateurs chaînes (CONCAT(liste args),...).
exemples :
SELECT * FROM voitures;
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
| 1234 RD 56 | Renault | Twingo | 1.2L | 49700 | 4700 | 1999-08-01 |
| 1111 FGH 75 | Renault | Twingo | expression | 102000 | 3200 | 2001-05-15 |
| 2222 DAC 75 | Renault | Espace | DCI | 85000 | 15900 | 2000-01-18 |
| 4321 ABC 67 | Renault | Espace | privilege 120CV | 17000 | 23900 | 2004-05-12 |
| 4444 ZA 67 | Renault | Espace | TD | 157000 | 11900 | 2000-04-22 |
| 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 |
| 5544 BCD 57 | Volkwagen | Golf | IV TDI 100CV | 45000 | 11000 | 2003-09-15 |
| 5555 BCD 57 | Volkwagen | Coccinelle | NULL | 45000 | NULL | 1972-09-15 |
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
SELECT Marque,Km,Prix AS Euros FROM voitures; /* j'ai changé un titre */
+-----------+--------+-------+
| Marque | Km | Euros |
+-----------+--------+-------+
| Renault | 49700 | 4700 |
| Renault | 102000 | 3200 |
| Renault | 85000 | 15900 |
| Renault | 17000 | 23900 |
| Renault | 157000 | 11900 |
| Ford | 67000 | 3000 |
| Volkwagen | 45000 | 11000 |
| Volkwagen | 45000 | NULL |
+-----------+--------+-------+
SELECT * FROM voitures WHERE (Km<100000) AND (MiseEnCirculation>'2001-01-01');
+-------------+-----------+--------+-----------------+-------+-------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+-----------+--------+-----------------+-------+-------+-------------------+
| 4321 ABC 67 | Renault | Espace | privilege 120CV | 17000 | 23900 | 2004-05-12 |
| 5544 BCD 57 | Volkwagen | Golf | IV TDI 100CV | 45000 | 11000 | 2003-09-15 |
+-------------+-----------+--------+-----------------+-------+-------+-------------------+
SELECT * FROM voitures WHERE Km BETWEEN 50000 AND 80000;
+------------+--------+--------+---------+-------+------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+------------+--------+--------+---------+-------+------+-------------------+
| 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 |
+------------+--------+--------+---------+-------+------+-------------------+
SELECT * FROM voitures WHERE Marque IN ('Renault','Ford');
+-------------+---------+--------+-----------------+--------+-------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+---------+--------+-----------------+--------+-------+-------------------+
| 1234 RD 56 | Renault | Twingo | 1.2L | 49700 | 4700 | 1999-08-01 |
| 1111 FGH 75 | Renault | Twingo | expression | 102000 | 3200 | 2001-05-15 |
| 2222 DAC 75 | Renault | Espace | DCI | 85000 | 15900 | 2000-01-18 |
| 4321 ABC 67 | Renault | Espace | privilege 120CV | 17000 | 23900 | 2004-05-12 |
| 4444 ZA 67 | Renault | Espace | TD | 157000 | 11900 | 2000-04-22 |
| 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 |
+-------------+---------+--------+-----------------+--------+-------+-------------------+
SELECT * FROM voitures WHERE Marque NOT LIKE 'R_n%'; /* _ = 1CHAR, % 0 OU PLUS*/
+-------------+-----------+------------+--------------+-------+-------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+-----------+------------+--------------+-------+-------+-------------------+
| 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 |
| 5544 BCD 57 | Volkwagen | Golf | IV TDI 100CV | 45000 | 11000 | 2003-09-15 |
| 5555 BCD 57 | Volkwagen | Coccinelle | NULL | 45000 | NULL | 1972-09-15 |
+-------------+-----------+------------+--------------+-------+-------+-------------------+
SELECT * FROM voitures WHERE Numero LIKE '% 67';
+-------------+---------+--------+-----------------+--------+-------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+---------+--------+-----------------+--------+-------+-------------------+
| 4321 ABC 67 | Renault | Espace | privilege 120CV | 17000 | 23900 | 2004-05-12 |
| 4444 ZA 67 | Renault | Espace | TD | 157000 | 11900 | 2000-04-22 |
| 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 |
+-------------+---------+--------+-----------------+--------+-------+-------------------+
SELECT * FROM voitures WHERE Prix IS NULL;
+-------------+-----------+------------+-------+-------+------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+-----------+------------+-------+-------+------+-------------------+
| 5555 BCD 57 | Volkwagen | Coccinelle | NULL | 45000 | NULL | 1972-09-15 |
+-------------+-----------+------------+-------+-------+------+-------------------+
SELECT * FROM voitures ORDER BY prix;
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
| 5555 BCD 57 | Volkwagen | Coccinelle | NULL | 45000 | NULL | 1972-09-15 |
| 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 |
| 1111 FGH 75 | Renault | Twingo | expression | 102000 | 3200 | 2001-05-15 |
| 1234 RD 56 | Renault | Twingo | 1.2L | 49700 | 4700 | 1999-08-01 |
| 5544 BCD 57 | Volkwagen | Golf | IV TDI 100CV | 45000 | 11000 | 2003-09-15 |
| 4444 ZA 67 | Renault | Espace | TD | 157000 | 11900 | 2000-04-22 |
| 2222 DAC 75 | Renault | Espace | DCI | 85000 | 15900 | 2000-01-18 |
| 4321 ABC 67 | Renault | Espace | privilege 120CV | 17000 | 23900 | 2004-05-12 |
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
SELECT * FROM voitures ORDER BY Marque ASC, Km DESC;
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
| Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation |
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
| 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 |
| 4444 ZA 67 | Renault | Espace | TD | 157000 | 11900 | 2000-04-22 |
| 1111 FGH 75 | Renault | Twingo | expression | 102000 | 3200 | 2001-05-15 |
| 2222 DAC 75 | Renault | Espace | DCI | 85000 | 15900 | 2000-01-18 |
| 1234 RD 56 | Renault | Twingo | 1.2L | 49700 | 4700 | 1999-08-01 |
| 4321 ABC 67 | Renault | Espace | privilege 120CV | 17000 | 23900 | 2004-05-12 |
| 5544 BCD 57 | Volkwagen | Golf | IV TDI 100CV | 45000 | 11000 | 2003-09-15 |
| 5555 BCD 57 | Volkwagen | Coccinelle | NULL | 45000 | NULL | 1972-09-15 |
+-------------+-----------+------------+-----------------+--------+-------+-------------------+
On peut faire des calculs : AVG (moyenne), COUNT (nb de lignes), MAX, MIN, SUM (total). GROUP BY permet de regrouper des lignes d'après un critère, c'est utile avec les calculs.On a encore le droit de trier, mais uniquement après le groupage. On peut rajouter une clause HAVING qui peut restreindre les lignes prises en compte dans le groupage
SELECT COUNT(Numero) AS nb, AVG(Prix) AS Prix_moy, SUM(Prix) AS Immobilisation FROM voitures; +----+-----------------+----------------+ | nb | Prix_moy | Immobilisation | +----+-----------------+----------------+ | 8 | 10514.285714286 | 73600 | +----+-----------------+----------------+ SELECT Marque, AVG(Km) AS Km_Moy FROM voitures GROUP BY Marque; +-----------+------------+ | Marque | Km_Moy | +-----------+------------+ | Ford | 67000.0000 | | Renault | 82140.0000 | | Volkwagen | 45000.0000 | +-----------+------------+ SELECT Marque, AVG(Km) AS Km_Moy FROM voitures GROUP BY Marque ORDER BY Km_Moy; +-----------+------------+ | Marque | Km_Moy | +-----------+------------+ | Volkwagen | 45000.0000 | | Ford | 67000.0000 | | Renault | 82140.0000 | +-----------+------------+ SELECT Marque, SUM(Prix) as Total, AVG(Km) AS Km_Moy FROM voitures GROUP BY Marque HAVING Total>15000; +---------+-------+------------+ | Marque | Total | Km_Moy | +---------+-------+------------+ | Renault | 59600 | 82140.0000 | +---------+-------+------------+
On peut même faire des sous-requêtes. Attention, on ne peut utiliser, à un endroit où la syntaxe de SQL n'accepte qu'un valeur, qu'une sous-requête n'envoyant qu'un résultat. Sil y a plusieurs résultats, on peut utiliser l'opérateur IN (ou EXISTS, ALL, ANY)
SELECT * FROM voitures WHERE Prix < (SELECT AVG(Prix) FROM voitures); +-------------+---------+--------+------------+--------+------+-------------------+ | Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation | +-------------+---------+--------+------------+--------+------+-------------------+ | 1234 RD 56 | Renault | Twingo | 1.2L | 49700 | 4700 | 1999-08-01 | | 1111 FGH 75 | Renault | Twingo | expression | 102000 | 3200 | 2001-05-15 | | 1252 XC 67 | Ford | Fiesta | 1.4 CLX | 67000 | 3000 | 1992-08-27 | +-------------+---------+--------+------------+--------+------+-------------------+ SELECT * FROM voitures WHERE Prix = (SELECT MAX(Prix) FROM voitures); +-------------+---------+--------+-----------------+-------+-------+-------------------+ | Numero | Marque | Modele | Serie | Km | Prix | MiseEnCirculation | +-------------+---------+--------+-----------------+-------+-------+-------------------+ | 4321 ABC 67 | Renault | Espace | privilege 120CV | 17000 | 23900 | 2004-05-12 | +-------------+---------+--------+-----------------+-------+-------+-------------------+
Pour IN j'aurais aimé afficher toutes les voitures dont on a plusieurs exemplaires (count>1), mais je l'ai résolu avec un tableau temporaire (plus bas)
C'est ici qu'on utilise les relations.
Soit la table Pays :
CREATE TABLE pays (Marque VARCHAR(15) unique, Pays VARCHAR(10));
insert into pays VALUES('Renault','France');
insert into pays VALUES('Peugeot','France');
insert into pays VALUES('Fiat','Italie');
insert into pays VALUES('Volkswagen','Allemagne');
insert into pays VALUES('Ford','USA');
+------------+-----------+
| Marque | Pays |
+------------+-----------+
| Renault | France |
| Peugeot | France |
| Fiat | Italie |
| Volkswagen | Allemagne |
| Ford | USA |
+------------+-----------+
SELECT DISTINCT Modele, Pays FROM voitures, pays WHERE voitures.Marque = pays.Marque;
+------------+-----------+
| Modele | Pays |
+------------+-----------+
| Twingo | France |
| Espace | France |
| Golf | Allemagne |
| Coccinelle | Allemagne |
| Fiesta | USA |
+------------+-----------+On n'est obligé de mettre le nom de la table (devant un champ, séparé par un .) que si le nom est identique dans 2 tables. Mais on peut aussi donner un raccourci au nom des tables
SELECT v.Marque, v.Modele, p.Pays, COUNT(Numero) AS Nb FROM voitures v,pays p WHERE v.Marque = p.Marque GROUP BY v.Marque, v.Modele; +-----------+------------+-----------+----+ | Marque | Modele | Pays | Nb | +-----------+------------+-----------+----+ | Ford | Fiesta | USA | 1 | | Renault | Espace | France | 3 | | Renault | Twingo | France | 2 | | Volkwagen | Coccinelle | Allemagne | 1 | | Volkwagen | Golf | Allemagne | 1 | +-----------+------------+-----------+----+
On supprime des lignes par DELETE FROM table WHERE condition (on ne peut plus restaurer, attention à ce qui est derrière WHERE !)
On peut retrouver la définition des champs d'une table : DESCRIBE table;
On peut aussi alimenter une table par un SELECT :
INSERT INTO table(col1,col2) SELECT col1,col2 FROM xxx WHERE......;
Pour modifier la structure d'une table :
ALTER TABLE nomtable DROP COLUMN nomchamp; ALTER TABLE nomtable MODIFY COLUMN nomchamp <nouveau_type>; ALTER TABLE nomtable ADD COLUMN nomchamp <type>;
UPDATE nomtable SET col1='valeur'... WHERE conditions; UPDATE produits SET TVA=19.6 WHERE TVA=17.6;
Comment afficher l'age quand je dispose de la date de naissance ? Ajouter dans le Select le champ :
(YEAR(CURRENT_DATE)-YEAR(naissance)) - (RIGHT(CURRENT_DATE,5) < RIGHT(naissance,5)) AS age
Plutôt que d'imbriquer des sous requêtes, on peut utiliser des variables internes :
SELECT @ma_variable:=MAX(Prix) FROM voitures; SELECT * FROM voitures WHERE Prix = @ma_variable;
voire un tableau temporaire (on peut utiliser le mot clef TEMPORARY mais ce n'est pas obligé) :
CREATE TEMPORARY TABLE tmp (Marque VARCHAR(15),Modele VARCHAR(15), Nb INTEGER); INSERT INTO tmp SELECT Marque,Modele,COUNT(Numero) FROM voitures GROUP BY Marque,Modele; SELECT V.* FROM voitures V,tmp WHERE V.Marque=tmp.Marque AND V.Modele=tmp.Modele AND tmp.Nb>1; DROP TABLE tmp;
Depuis le 7/2/06, vous êtes le
ème lecteur de cette page
P. Trau ULP-IPST pour toute remarque !
Patrick TRAU, ULP - IPST Strasbourg, Fev 06