show databases;
/* je ne vous les montre pas, mais il y en a une qui s'appelle "test" */
connect test;
show tables;
/*
+----------------+
| Tables_in_test |
+----------------+
| album          |
| artiste        |
| genre          |
| morceaux       |
| pays           |
| voitures       |
+----------------+
ah, elles sont déjà là. Pour tout faire proprement, je les efface (je garde évidement les autres, qui me servent pour d'autres cours). Attention, l'ordre est important, on ne peut pas effacer une clé primaire si une clé étrangère pointe dessus.
*/
drop table morceaux,album,genre,artiste; show tables;

create table genre (id integer primary key auto_increment , nom varchar(20) unique not null);
insert into genre(nom) values ("rock & roll"),("hard rock"),("variété française");
select * from genre;
/*
+----+----------------------+
| id | nom                  |
+----+----------------------+
|  2 | hard rock            |
|  1 | rock & roll          |
|  3 | variété française    |
+----+----------------------+
*/
create table artiste (id integer primary key auto_increment , nom varchar(20) unique not null , naiss date);
insert into artiste(nom,naiss) values ("Elvis Presley","1935-1-8"),("AC-DC","1973-12-31"),("Goldmann","1951-10-12"),("Téléphone","1976-11-12"),("Led Zeppelin","1968-8-19");
select * from artiste;
/*
+----+---------------+------------+
| id | nom           | naiss      |
+----+---------------+------------+
|  1 | Elvis Presley | 1935-01-08 |
|  2 | AC-DC         | 1973-12-31 |
|  3 | Goldmann      | 1951-10-12 |
|  4 | Téléphone     | 1976-11-12 |
|  5 | Led Zeppelin  | 1968-08-19 |
+----+---------------+------------+
*/
create table album (id integer primary key auto_increment , titre varchar(30) not null ,  annee integer, artiste integer, foreign key (artiste) references artiste(id), genre integer,  foreign key (genre) references genre(id));
insert into album (titre,annee,artiste,genre) values ("entre gris clair et gris foncé",1987,3,3),("crache ton venin",1979,4,2),("dure limite",1982,4,2),("Led Zeppelin IV",1971,5,2),("jailhouse rock",1957,1,1);
select * from album;
/*
+----+---------------------------------+-------+---------+-------+
| id | titre                           | annee | artiste | genre |
+----+---------------------------------+-------+---------+-------+
|  1 | entre gris clair et gris foncé  |  1987 |       3 |     3 |
|  2 | crache ton venin                |  1979 |       4 |     2 |
|  3 | dure limite                     |  1982 |       4 |     2 |
|  4 | Led Zeppelin IV                 |  1971 |       5 |     2 |
|  5 | jailhouse rock                  |  1957 |       1 |     1 |
+----+---------------------------------+-------+---------+-------+
*/
create table morceaux (id integer primary key auto_increment , titre varchar(40) not null ,  album integer, foreign key (album) references album(id),duree time,numplage integer);
insert into morceaux(titre,album,duree,numplage) values ("crache ton venin",2,"0:4:57",1),("la bombe humaine",2,"0:4:13",5),("là bas",1,"0:5:40",5),("elle a fait un bébé toute seule",1,"0:3:54",12), ("Cendrillon",3,"0:3:57",8),("Stairway to Heaven",4,"0:8:01",4);
select * from morceaux; select * from album; select * from artiste ; select * from genre;
/*
+----+-----------------------------------+-------+----------+----------+
| id | titre                             | album | duree    | numplage |
+----+-----------------------------------+-------+----------+----------+
|  1 | crache ton venin                  |     2 | 00:04:57 |        1 |
|  2 | la bombe humaine                  |     2 | 00:04:13 |        5 |
|  3 | là bas                            |     1 | 00:05:40 |        5 |
|  4 | elle a fait un bébé toute seule   |     1 | 00:03:54 |       12 |
|  5 | Cendrillon                        |     3 | 00:03:57 |        8 |
|  6 | Stairway to Heaven                |     4 | 00:08:01 |        4 |
+----+-----------------------------------+-------+----------+----------+
*/
select titre,duree from morceaux;
/*
+-----------------------------------+----------+
| titre                             | duree    |
+-----------------------------------+----------+
| crache ton venin                  | 00:04:57 |
| la bombe humaine                  | 00:04:13 |
| là bas                            | 00:05:40 |
| elle a fait un bébé toute seule   | 00:03:54 |
| Cendrillon                        | 00:03:57 |
| Stairway to Heaven                | 00:08:01 |
+-----------------------------------+----------+
*/
select * from morceaux where album=2;
/*
+----+------------------+-------+----------+----------+
| id | titre            | album | duree    | numplage |
+----+------------------+-------+----------+----------+
|  1 | crache ton venin |     2 | 00:04:57 |        1 |
|  2 | la bombe humaine |     2 | 00:04:13 |        5 |
+----+------------------+-------+----------+----------+
*/
select * from album where annee<1980 and genre=2;
/*
+----+------------------+-------+---------+-------+
| id | titre            | annee | artiste | genre |
+----+------------------+-------+---------+-------+
|  2 | crache ton venin |  1979 |       4 |     2 |
|  4 | Led Zeppelin IV  |  1971 |       5 |     2 |
+----+------------------+-------+---------+-------+
*/
select * from album where genre in (1,2);
/*
+----+------------------+-------+---------+-------+
| id | titre            | annee | artiste | genre |
+----+------------------+-------+---------+-------+
|  2 | crache ton venin |  1979 |       4 |     2 |
|  3 | dure limite      |  1982 |       4 |     2 |
|  4 | Led Zeppelin IV  |  1971 |       5 |     2 |
|  5 | jailhouse rock   |  1957 |       1 |     1 |
+----+------------------+-------+---------+-------+
*/
select * from album where titre like "%e";
/* : e à la fin
+----+---------------------------------+-------+---------+-------+
| id | titre                           | annee | artiste | genre |
+----+---------------------------------+-------+---------+-------+
|  1 | entre gris clair et gris foncé  |  1987 |       3 |     3 |
|  3 | dure limite                     |  1982 |       4 |     2 |
+----+---------------------------------+-------+---------+-------+
*/
select * from morceaux where titre like "la %";
/* : la suivi d'un espace
+----+------------------+-------+----------+----------+
| id | titre            | album | duree    | numplage |
+----+------------------+-------+----------+----------+
|  2 | la bombe humaine |     2 | 00:04:13 |        5 |
|  3 | là bas           |     1 | 00:05:40 |        5 |
+----+------------------+-------+----------+----------+
*/

select * from morceaux order by duree desc; /*tri décroissant*/
/*
+----+-----------------------------------+-------+----------+----------+
| id | titre                             | album | duree    | numplage |
+----+-----------------------------------+-------+----------+----------+
|  6 | Stairway to Heaven                |     4 | 00:08:01 |        4 |
|  3 | là bas                            |     1 | 00:05:40 |        5 |
|  1 | crache ton venin                  |     2 | 00:04:57 |        1 |
|  2 | la bombe humaine                  |     2 | 00:04:13 |        5 |
|  5 | Cendrillon                        |     3 | 00:03:57 |        8 |
|  4 | elle a fait un bébé toute seule   |     1 | 00:03:54 |       12 |
+----+-----------------------------------+-------+----------+----------+
*/
select album,sum(duree) from morceaux group by album;
/* : il compte en secondes par défaut
+-------+------------+
| album | sum(duree) |
+-------+------------+
|     1 |        894 |
|     2 |        870 |
|     3 |        357 |
|     4 |        801 |
+-------+------------+
*/
select album,sec_to_time(sum(duree)) from morceaux group by album;
/*
+-------+-------------------------+
| album | sec_to_time(sum(duree)) |
+-------+-------------------------+
|     1 | 00:14:54                |
|     2 | 00:14:30                |
|     3 | 00:05:57                |
|     4 | 00:13:21                |
+-------+-------------------------+
*/

select * from album,genre;
/* toutes les combinaisons possibles, pour les 2 tables
+----+---------------------------------+-------+---------+-------+----+----------------------+
| id | titre                           | annee | artiste | genre | id | nom                  |
+----+---------------------------------+-------+---------+-------+----+----------------------+
|  1 | entre gris clair et gris foncé  |  1987 |       3 |     3 |  2 | hard rock            |
|  1 | entre gris clair et gris foncé  |  1987 |       3 |     3 |  1 | rock & roll          |
|  1 | entre gris clair et gris foncé  |  1987 |       3 |     3 |  3 | variété française    |
|  2 | crache ton venin                |  1979 |       4 |     2 |  2 | hard rock            |
|  2 | crache ton venin                |  1979 |       4 |     2 |  1 | rock & roll          |
|  2 | crache ton venin                |  1979 |       4 |     2 |  3 | variété française    |
|  3 | dure limite                     |  1982 |       4 |     2 |  2 | hard rock            |
|  3 | dure limite                     |  1982 |       4 |     2 |  1 | rock & roll          |
|  3 | dure limite                     |  1982 |       4 |     2 |  3 | variété française    |
|  4 | Led Zeppelin IV                 |  1971 |       5 |     2 |  2 | hard rock            |
|  4 | Led Zeppelin IV                 |  1971 |       5 |     2 |  1 | rock & roll          |
|  4 | Led Zeppelin IV                 |  1971 |       5 |     2 |  3 | variété française    |
|  5 | jailhouse rock                  |  1957 |       1 |     1 |  2 | hard rock            |
|  5 | jailhouse rock                  |  1957 |       1 |     1 |  1 | rock & roll          |
|  5 | jailhouse rock                  |  1957 |       1 |     1 |  3 | variété française    |
+----+---------------------------------+-------+---------+-------+----+----------------------+
*/
select * from album,genre where album.genre=genre.id;
/* il faut éliminer des lignes, donc "where". Je précise la relation que j'ai déssinée dans le schéma structurel
+----+---------------------------------+-------+---------+-------+----+----------------------+
| id | titre                           | annee | artiste | genre | id | nom                  |
+----+---------------------------------+-------+---------+-------+----+----------------------+
|  2 | crache ton venin                |  1979 |       4 |     2 |  2 | hard rock            |
|  3 | dure limite                     |  1982 |       4 |     2 |  2 | hard rock            |
|  4 | Led Zeppelin IV                 |  1971 |       5 |     2 |  2 | hard rock            |
|  5 | jailhouse rock                  |  1957 |       1 |     1 |  1 | rock & roll          |
|  1 | entre gris clair et gris foncé  |  1987 |       3 |     3 |  3 | variété française    |
+----+---------------------------------+-------+---------+-------+----+----------------------+
*/

select titre,annee,artiste.nom,genre.nom from album,genre,artiste where album.genre=genre.id and album.artiste=artiste.id;
/* pareil, mais 3 fichiers et 2 relations
+---------------------------------+-------+---------------+----------------------+
| titre                           | annee | nom           | nom                  |
+---------------------------------+-------+---------------+----------------------+
| crache ton venin                |  1979 | Téléphone     | hard rock            |
| dure limite                     |  1982 | Téléphone     | hard rock            |
| Led Zeppelin IV                 |  1971 | Led Zeppelin  | hard rock            |
| jailhouse rock                  |  1957 | Elvis Presley | rock & roll          |
| entre gris clair et gris foncé  |  1987 | Goldmann      | variété française    |
+---------------------------------+-------+---------------+----------------------+
*/
select titre,annee,artiste.nom as artiste,genre.nom as genre from album,genre,artiste where album.genre=genre.id and album.artiste=artiste.id;
/* je change juste les entêtes des champs
+---------------------------------+-------+---------------+----------------------+
| titre                           | annee | artiste       | genre                |
+---------------------------------+-------+---------------+----------------------+
| crache ton venin                |  1979 | Téléphone     | hard rock            |
| dure limite                     |  1982 | Téléphone     | hard rock            |
| Led Zeppelin IV                 |  1971 | Led Zeppelin  | hard rock            |
| jailhouse rock                  |  1957 | Elvis Presley | rock & roll          |
| entre gris clair et gris foncé  |  1987 | Goldmann      | variété française    |
+---------------------------------+-------+---------------+----------------------+
*/
quit;