probleme de requete SQL compliqué (Mysql)

[jejemimi]

Bonjour,
pour mon travail, je doit faire une requête SQL qui me donne les informations sur la base de données.
J'ai donc travaillé sur information_schema et le problème est que en une seule requête je dois avoir toutes les infos.

3 tables (fictives puisque je ne les utilise pas vraiment):

Animal : idAnimal , type , dangereux
client idClient , non , prénom , apte
Enfant : idEnfant , age , photo

Les informations relatives à ces table sont dans la base information_schema et plus particulièrement les tables TABLES et COLUMNS

Il me faut en même temps le nombre de colonne de chaque table et le détail pour chaque table le nom des colonnes

j'ai essayé:

Code :

1
2
3
4
5
6
7
8
SELECT DISTINCT  t.TABLE_NAME, t.TABLE_ROWS, 
  count(c.COLUMN_NAME) AS NBCOLUMN, 
  c.COLUMN_NAME 
FROM information_schema.TABLES AS t, information_schema.COLUMNS AS c 
 WHERE c.TABLE_SCHEMA = "mabase" 
  AND t.TABLE_SCHEMA ="mabase" 
  AND t.TABLE_NAME = c.TABLE_NAME 
GROUP BY TABLE_NAME

le résultat est :
animal : 0 : 3 : idAnimal
client : 0 : 4 : idClient
enfant : 0 : 3 : idEnfant

le résultat que j'aimerai avoir :
animal : 0 : 3 : idAnimal
animal : 0 : 3 : type
animal : 0 : 3 : dangereux
client : 0 : 4 : idClient
client : 0 : 4 : non
client : 0 : 4 : prénom
client : 0 : 4 : apte
et ainsi de suite

c'est bien possible que ce que je demande soit impossible
Je vous remercie donc d'avance de votre réponse
Cordialement

[CinePhil]

Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN !

Les valeurs textuelles s'écrivent entre apostrophes et pas entre guillemets !

Combien de colonnes par table ?

Code :

1
2
3
4
5
SELECT TABLE_SCHEMA, TABLE_NAME, 
    COUNT(*) AS NBCOLUMN
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mabase'
GROUP BY TABLE_SCHEMA, TABLE_NAME

On fait une jointure de cette requête avec les autres vues pour obtenir le détail des infos :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT t.TABLE_NAME, t.TABLE_ROWS,
    tmp.NBCOLUMN,
    c.COLUMN_NAME
FROM information_schema.TABLES AS t
INNER JOIN information_schema.COLUMNS AS c 
    ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND c.TABLE_NAME = t.TABLE_NAME
INNER JOIN 
(
    SELECT TABLE_SCHEMA, TABLE_NAME, 
        COUNT(*) AS NBCOLUMN
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'mabase'
    GROUP BY TABLE_SCHEMA, TABLE_NAME
) AS tmp
    ON tmp.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND tmp.TABLE_NAME = t.TABLE_NAME

[fsmrel]

Bonsoir,

Envoyé par CinePhil

Les jointures s'écrivent depuis 1992 avec l'opérateur JOIN !

On peut même aller jusqu’à dire que l’utilisation de la paire (FROM, WHERE) pour exprimer la jointure peut être considérée comme obsolète depuis 25 ans. En effet, c’est dans son ouvrage Relational Databases, Selected Writings, paru en 1986 que C. J. Date écrivit (chapitre 16, Outer join) :

[...]We propose the introduction of an explicit JOIN operation into SQL —where the keyword JOIN refers specifically to the (inner or outer) natural join [...]

et il fournit la BNF qui va bien :

Code :

1
2
3
4
join-expression 
   ::= [ OUTER | INNER ] JOIN relation-commalist on-clause
on-clause
    ::= ON ( attribute-commalist ) [ AS introduced-name ]

Dans Relational Databases, Writings 1989-1991, paru en 1992, ce même C. J. Date écrivit par ailleurs (au chapitre 19, Watch Out for Outer join, initialement publié dans InfoDB 5, No. 1 (Spring/Summer 1990)) :

I am glad to see that the SQL standards committees are in fact planning such an extension in their proposed follow-on to the existing SQL standard known as SQL2.

Cela dit, les éditeurs de SGBD ont pris plus ou moins leur temps avant de proposer d’effectuer la jointure au niveau du seul FROM, sans que l’on ait à fournir la condition de jointure au sein du WHERE. Par exemple, IBM n’a proposé la construction INNER JOIN qu’en novembre 1995, avec la version 4 de DB2 for MVS/ESA (autant dire en 1998 pour nous, pauvres utilisateurs, toujours en retard d’une version, contexte de production, plans de tests, de régression, etc. obligent). A la décharge d’IBM, la V3 de DB2 avait été livrée en 1993, et il n’était évidemment pas possible de la chambouler, d’envoyer au pilon des wagons de documentation prêts depuis un an, tout cela parce que simultanément naquit SQL/2.

Quant à ORACLE... Si j’ai bien suivi les pointeurs (merci de me corriger si j’ai lu de travers), ça n’est qu’avec Oracle10g Release 2 que l’on a pu coder JOIN pour effectuer une jointure naturelle, c'est-à-dire en 2006. Là encore, avec une version de retard, les développeurs ont des excuses...

Quant à MySQL... Je suis incompétent pour répondre, mais vu sa date de naissance (1995 si j’en crois Wikipedia), il a probablement dû prendre en compte la norme dès le départ.

En ce qui concerne SQL Server, SQLpro sera sans doute à même de nous donner les informations correspondantes.

Bref, que cela fasse 19 ou 20 ans que la norme propose nommément l’opérateur JOIN, je veux bien, mais ça n’est pas à considérer comme une référence absolue, il faut relativiser (comme disait le monsieur qui tire la langue dans votre avatar). Pour un développeur Oracle, 20 ans peuvent se ramener à un an, pour Chamberlin (papa de SEQUEL 2, alias SQL), ça peut être 30 ans (il a lui même pondu une proposition — assez compliquée dois-je dire — en 1980 concernant l’outer join). Ainsi, je ne suis pas choqué que jejemimi utilise encore la paire (FROM, WHERE), et du reste ça m'arrive encore...

Une remarque en passant :

Je vous prie de noter que le concept d’assertion (déjà présent dans SEQUEL 2 en 1976, cf. « SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control ») fait partie de la norme SQL/2 (voyez l’instruction CREATE ASSERTION), c'est-à-dire depuis 1992. Mais quels SGBD proposent aujourd’hui cette instruction ? (Si quelqu’un a des informations à ce sujet, je suis preneur). Quand pourrons-nous écrire :

« Pourquoi utiliser des triggers pour coder des contraintes, alors que celles-ci s’expriment beaucoup plus simplement (et avec incomparablement moins de risques) par le biais d’assertions ? »

C'est-à-dire : quand pourrons-nous programmer à un niveau d’abstraction plus élevé ? Car c'est bien là le défi lancé en 1969 par Ted Codd.

[CinePhil]

La date d'apparition de JOIN dans la norme SQL est quand même une bonne référence et il est désolant que 18 ans après certains professeurs enseignent encore à leurs élèves la jointure avec FROM ... WHERE, apparemment sans même mentionner la syntaxe normalisée, si j'en crois le nombre impressionnant de fois que j'ai dû écrire cette fameuse phrase qui a provoqué la réponse, toujours excellemment documentée par l'historien des bases de données fsmrel.

JOIN est quand même bien plus lisible et facile à comprendre que l'ancienne syntaxe !

[fsmrel]

Envoyé par CinePhil

JOIN est quand même bien plus lisible et facile à comprendre que l'ancienne syntaxe !

Certes, et un grand merci à Chris Date de l’avoir concoctée et proposée, sinon on en serait tous encore à l’ancienne syntaxe SQL...

Mais (toujours concernant SQL), sa préférence va à l'emploi de "NATURAL JOIN" qui évite d'avoir à fournir le nom des colonnes de jointure (ce qui suppose que ces colonnes et elles seules ont le même nom, d’où utilisation éventuelle de l'opérateur RENAME), car on se rapproche alors de l’esprit de Tutorial D.


Prenons la requête 1 du 4e exemple dans Bases de données relationnelles et normalisation :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT DISTINCT Camion.CamImmat
FROM   Client JOIN Commande
                ON Client.CliId = Commande.CliId
              JOIN LigneCde  
                ON Commande.CdeId = LigneCde.CdeId
              JOIN Engagement  
                ON LigneCde.LigneId = Engagement.LigneId
              JOIN Livraison  
                ON Engagement.EngId = Livraison.EngId
              JOIN Camion  
                ON Livraison.CamionId = Camion.CamionId
WHERE  CliSiret = '12345678900001' ;

Avec Tutorial D, on peut simplifier et se contenter d'écrire :

(JOIN {Client, Commande, LigneCde, Engagement, Livraison, Camion} WHERE CliSiret = '12345678900001') {CamImmat}

Et si on utilise l'identification relative (par ailleurs plus performante), cas de la requête 2 :

(JOIN {Client, Livraison, Camion} WHERE CliSiret = '12345678900001') {CamImmat}

Tout ceci non seulement facilite la vie du développeur, mais aussi celle de l'optimiseur du SGBD dans le choix de la meilleure stratégie.

Quant à l’élévation du niveau d’abstraction, donc à la simplicité, à la concision (et une plus grande liberté pour l’optimiseur), je vous laisse juge.

[skuatamad]

Bonjour,

Envoyé par fsmrel

Quant à ORACLE... Si j’ai bien suivi les pointeurs (merci de me corriger si j’ai lu de travers), ça n’est qu’avec Oracle10g Release 2 que l’on a pu coder JOIN pour effectuer une jointure naturelle, c'est-à-dire en 2006. Là encore, avec une version de retard, les développeurs ont des excuses...

JOIN est en fait disponnible depuis la version 9iR1 sortie en 2001.
Le lien pointe vers de la documentation de la release 2, mais c'est bien à partir de la release 1, la documentation des 2 releases semblant être fusionnée.
Les développeurs ont donc déjà beaucoup moins d'excuses.

Envoyé par CinePhil

La date d'apparition de JOIN dans la norme SQL est quand même une bonne référence et il est désolant que 18 ans après certains professeurs enseignent encore à leurs élèves la jointure avec FROM ... WHERE, apparemment sans même mentionner la syntaxe normalisée

Mais les professeurs, eux, n'ont aucune excuse.

[fsmrel]

Bonjour,

Envoyé par skuatamad

JOIN est en fait disponnible depuis la version 9iR1

Merci pour l'information. Je vais donc mettre mes pointeurs à jour.

[jejemimi]

Bonsoir et merci beaucoup pour ces réponses,
tout d'abord, c'est vrai je vient de sortir d'un BAC+2 en informatique et on ne m'a enseigné que la jointure avec le WHERE. Je vois donc que j'ai énormément de retard en SQL et ca fait peur si l'on en croit ce que vous dites.

Ensuite j'ai malheureusement un peu de mal a comprendre la solution (qui marche très bien d'ailleurs) et je doit rajouter un champs NBTABLE qui donne le nombre de table dans la base (le même genre que NBCOLUMN)

En espérant que vous puissiez encore m'aider merci
Cordialement

[CinePhil]

Envoyé par jejemimi

je viens de sortir d'un BAC+2 en informatique et on ne m'a enseigné que la jointure avec le WHERE.

C'est vraiment lamentable de lire un truc pareil !

Je vois donc que j'ai énormément de retard en SQL et ca fait peur si l'on en croit ce que vous dites.

Mais ce n'est pas de ta faute. Heureusement, il y a Developpez.com pour compenser les lacunes de l'éducation nationale (sans majuscules ; elles ne sont pas méritées ! ).

Ensuite j'ai malheureusement un peu de mal a comprendre la solution (qui marche très bien d'ailleurs) et je doit rajouter un champs NBTABLE qui donne le nombre de table dans la base (le même genre que NBCOLUMN)

La requête que j'ai écrite plus haut donne des informations sur les tables. Là tu demandes des informations sur la BDD (le schema). Il faut donc faire une autre requête car le niveau de regroupement n'est pas le même.

Code :

1
2
3
SELECT COUNT(*) AS NBTABLE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'ma_base'

Tu peux ajouter cette requête dans le SELECT mais ce n'est pas très élégant parce que ce nombre va se répéter sur toutes les lignes. C'est au logiciel de présentation des données de lancer les requêtes adéquates pour afficher les résultat d'une manière cohérente ; et ça m'étonnerait que tu présentes le résultat de la requête tel qu'il sera donné par le SGBD.

Ça donnerait ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT t.TABLE_NAME, t.TABLE_ROWS,
    tmp.NBCOLUMN,
    c.COLUMN_NAME,
    (
        SELECT COUNT(*) 
        FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = 'ma_base'
    ) AS nb_tables
FROM information_schema.TABLES AS t
INNER JOIN information_schema.COLUMNS AS c 
    ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND c.TABLE_NAME = t.TABLE_NAME
INNER JOIN 
(
    SELECT TABLE_SCHEMA, TABLE_NAME, 
        COUNT(*) AS NBCOLUMN
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'mabase'
    GROUP BY TABLE_SCHEMA, TABLE_NAME
) AS tmp
    ON tmp.TABLE_SCHEMA = t.TABLE_SCHEMA
    AND tmp.TABLE_NAME = t.TABLE_NAME

Mais c'est pas beau !

[SQLpro]

Envoyé par fsmrel

En ce qui concerne SQL Server, SQLpro sera sans doute à même de nous donner les informations correspondantes.

Depuis SQL Server 7 au moins, soit une quinzaine d'années...

Envoyé par fsmrel

...
Une remarque en passant :

Je vous prie de noter que le concept d’assertion (déjà présent dans SEQUEL 2 en 1976, cf. « SEQUEL 2: A Unified Approach to Data Definition, Manipulation, and Control ») fait partie de la norme SQL/2 (voyez l’instruction CREATE ASSERTION), c'est-à-dire depuis 1992. Mais quels SGBD proposent aujourd’hui cette instruction ? (Si quelqu’un a des informations à ce sujet, je suis preneur). Quand pourrons-nous écrire :

« Pourquoi utiliser des triggers pour coder des contraintes, alors que celles-ci s’expriment beaucoup plus simplement (et avec incomparablement moins de risques) par le biais d’assertions ? »

...

Ocelot de Peter Gulutzan !

A +

[SQLpro]

Remis en forme, agrémenté :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, 
       tmp.NBCOLUMN, c.COLUMN_NAME, c.ORDINAL_POSITION
FROM   information_schema.TABLES AS t
       INNER JOIN information_schema.COLUMNS AS c 
             ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
                AND c.TABLE_NAME = t.TABLE_NAME
       INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, 
                          COUNT(*) AS NBCOLUMN
                   FROM information_schema.COLUMNS
                   GROUP BY TABLE_SCHEMA, TABLE_NAME) AS tmp
             ON tmp.TABLE_SCHEMA = t.TABLE_SCHEMA
                AND tmp.TABLE_NAME = t.TABLE_NAME
WHERE  TABLE_TYPE = 'BASE TABLE'    
ORDER  BY 1, 2, 5

Une autre solution plus simple existe avec les fonctions de fenêtrage :

Code :

1
2
3
4
5
SELECT TABLE_SCHEMA, TABLE_NAME, 
       COUNT(*) OVER(PARTITION BY TABLE_SCHEMA, TABLE_NAME) AS NOMBRE_COLONNES,
       COLUMN_NAME, ORDINAL_POSITION
FROM   INFORMATION_SCHEMA.COLUMNS
ORDER  BY 1, 2, 5

Mais il faudrait pour cela que MySQL qui a dix ans de retard se mette à les pratiquer !
A lire : http://blog.developpez.com/sqlpro/p9...udre-aux-yeux/

A +

[fsmrel]

Envoyé par SQLpro

Ocelot de Peter Gulutzan !

Certes. J’aurais dû préciser : SGBD utilisable en production. Il y a une bonne dizaine d’années, ayant acheté le bouquin de Gulutzan & Pelzer (SQL-99 Complete, Really) j’avais installé Ocelot livré avec. Le lendemain j’ai flanqué le CD à la poubelle. Qu’en est-il d’Ocelot aujourd’hui ?

Une anecdote concernant l'ouvrage. Le chapitre 30 "Searching with Joins" commence ainsi :

« It’s France in the 1600s. René Descartes is playing cards with Madame du Barry. »

Le problème est que René est mort en 1650 — Louis XIV avait onze ans — tandis que Jeanne est née en 1743, pendant le règne de Louis XV, petit-fils du Roi Soleil. G & P ont commis là comme un anachronisme de première...

Moralité : quand on oublie de vérifier ce qu'on écrit, pour assurer, on prévoit au moins une assertion comme quoi "une personne P1 ne peut pas jouer aux cartes avec une personne P2 si la date de décès de l’une précède, disons, l’année de naissance + 3 ans de l’autre".

Par ailleurs G & P tiennent les Français pour gens frivoles, puisqu’ils sous-entendent que René et Jeanne n’ont rien d’autre d’intéressant à faire que jouer aux cartes (« It’s France in the 1600s »). Mais bon, on a l’habitude, c'est l'hôpital qui se moque de la Charité...

[jejemimi]

Bonjour,
merci pour vos réponse.
Le code SQL de CinePhil marche très bien pour mysql
et le code SQL de SQLpro avec les fonction de fenêtrage marche aussi tres bien pour sql server
Mais pour sql server je suis face a un problème, je ne trouve pas d'information concernant le nombre de ligne par table (le TABLE_ROW de mysql).
Auriez-vous une idée de la façon de procéder

merci encore
Cordialement

[CinePhil]

TABLE_ROWS donne le nombre de lignes selon les statistiques du SGBD.
Si cette information ne figure pas dans information_schema de SQL Server, comme tu as un groupement par table, tu peux remplacer TABLE_ROWS par COUNT(*).

[jejemimi]

remplacer TABLE_ROW par COUNT(*) ? Ou exatement? dans ton code SQL ?

[CinePhil]

Non en fait je suis allé trop vite en besogne là !
COUNT(*) va compter les colonnes de la table, pas les lignes.

Pas le temps d'y réfléchir et en plus je ne maîtrise pas SQL Server, désolé.

[jejemimi]

Ok ce n'est pas grave merci quand meme
J'attendrai de voir si peut-être SQLPro a une idee

merci encore

[Oishiiii]

Il faut jouer avec sysindexes, pour récupérer le nombre de ligne de l'index cluster.

Code :

1
2
3
4
5
SELECT st.name, si.rows
FROM nomDelaBase.sys.TABLES AS st
	INNER JOIN nomDelaBase.sys.sysindexes AS si 
		ON st.object_id = si.id 
		AND si.indid = 1

Cela suppose que chaque table possède une clé primaire. Sinon essayez de remplacer "= 1" par "<= 1".

[jejemimi]

Salut et merci pour ta réponse, oui c'est vrai ca marche bien mais je ne sais pas comment l'intégrer aux dernières réponses
Pouvez-vous encore m'aider?
Cordialement