Ok, j'ai trouvé ce post très intéressant et je voudrais partager certaines de mes connaissances sur la création d'une requête. Merci pour ce Fluffeh . D'autres qui peuvent lire ceci et peuvent penser que je me trompe sont libres à 101% de modifier et de critiquer ma réponse. ( honnêtement, je me sens très reconnaissant d'avoir corrigé mes erreurs. )

je vais poster quelques-unes des questions les plus fréquemment posées dans MySQL balise.

---

astuce n ° 1 ( lignes qui correspondent à des conditions multiples )

vu ce schéma

CREATE TABLE MovieList
(
    ID INT,
    MovieName VARCHAR(25),
    CONSTRAINT ml_pk PRIMARY KEY (ID),
    CONSTRAINT ml_uq UNIQUE (MovieName)
);

INSERT INTO MovieList VALUES (1, 'American Pie');
INSERT INTO MovieList VALUES (2, 'The Notebook');
INSERT INTO MovieList VALUES (3, 'Discovery Channel: Africa');
INSERT INTO MovieList VALUES (4, 'Mr. Bean');
INSERT INTO MovieList VALUES (5, 'Expendables 2');

CREATE TABLE CategoryList
(
    MovieID INT,
    CategoryName VARCHAR(25),
    CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName),
    CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID)
);

INSERT INTO CategoryList VALUES (1, 'Comedy');
INSERT INTO CategoryList VALUES (1, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Drama');
INSERT INTO CategoryList VALUES (3, 'Documentary');
INSERT INTO CategoryList VALUES (4, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Action');

QUESTION

trouver tous les films qui appartiennent au moins à la fois Comedy et Romance catégories.

Solution

cette question peut parfois être très délicate. Il peut sembler qu'une requête de ce sera la réponse:-

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName = 'Comedy' AND
        b.CategoryName = 'Romance'

SQLFiddle Demo

ce qui est certainement très mal parce qu'il produit pas de résultat . L'explication de ceci est qu'il existe une seule valeur valide de CategoryName sur chaque ligne . Par exemple, la première condition renvoie vrai , la deuxième condition est toujours fausse. Ainsi, en utilisant l'opérateur AND , les deux conditions doivent être vraies; sinon, elles seront fausses. Une autre requête est comme ceci,

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')

SQLFiddle Demo

et le résultat est encore incorrect parce qu'il correspond à l'enregistrement qui a au moins un match sur le categoryName . La réelle solution serait en comptant le nombre d'instances d'enregistrement par film . Le nombre d'instance doit correspondre au nombre total de valeurs fournies en l'état.

SELECT  a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
HAVING COUNT(*) = 2

SQLFiddle Démo (la réponse)

• SQL Relationnelle de la Division

---

truc n ° 2 ( enregistrement maximum pour chaque entrée )

schéma donné,

CREATE TABLE Software
(
    ID INT,
    SoftwareName VARCHAR(25),
    Descriptions VARCHAR(150),
    CONSTRAINT sw_pk PRIMARY KEY (ID),
    CONSTRAINT sw_uq UNIQUE (SoftwareName)  
);

INSERT INTO Software VALUES (1,'PaintMe','used for photo editing');
INSERT INTO Software VALUES (2,'World Map','contains map of different places of the world');
INSERT INTO Software VALUES (3,'Dictionary','contains description, synonym, antonym of the words');

CREATE TABLE VersionList
(
    SoftwareID INT,
    VersionNo INT,
    DateReleased DATE,
    CONSTRAINT sw_uq UNIQUE (SoftwareID, VersionNo),
    CONSTRAINT sw_fk FOREIGN KEY (SOftwareID) REFERENCES Software(ID)
);

INSERT INTO VersionList VALUES (3, 2, '2009-12-01');
INSERT INTO VersionList VALUES (3, 1, '2009-11-01');
INSERT INTO VersionList VALUES (3, 3, '2010-01-01');
INSERT INTO VersionList VALUES (2, 2, '2010-12-01');
INSERT INTO VersionList VALUES (2, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 3, '2011-12-01');
INSERT INTO VersionList VALUES (1, 2, '2010-12-01');
INSERT INTO VersionList VALUES (1, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 4, '2012-12-01');

QUESTION

trouvez la dernière version de chaque logiciel. Afficher les colonnes suivantes: SoftwareName , Descriptions , LatestVersion ( de la colonne VersionNo ), DateReleased

Solution

certains développeurs SQL utilisent par erreur MAX() fonction agrégée. Ils ont tendance à créer comme ceci,

SELECT  a.SoftwareName, a.Descriptions,
        MAX(b.VersionNo) AS LatestVersion, b.DateReleased
FROM    Software a
        INNER JOIN VersionList b
            ON a.ID = b.SoftwareID
GROUP BY a.ID
ORDER BY a.ID

SQLFiddle Demo

( la plupart des SGBDR génère une erreur de syntaxe sur ce parce que de ne pas spécifier certains de la non-agrégées colonnes sur la group by clause ) le résultat produit correct LatestVersion chaque logiciel mais évidemment le DateReleased sont incorrects. MySQL ne supporte pas Window Functions et Common Table Expression pourtant comme certains RDBMS le font déjà. La solution à ce problème est de créer un subquery qui obtient le maximum individuel versionNo sur chaque logiciel et plus tard être joint sur les autres tables.

SELECT  a.SoftwareName, a.Descriptions,
        b.LatestVersion, c.DateReleased
FROM    Software a
        INNER JOIN
        (
            SELECT  SoftwareID, MAX(VersionNO) LatestVersion
            FROM    VersionList
            GROUP BY SoftwareID
        ) b ON a.ID = b.SoftwareID
        INNER JOIN VersionList c
            ON  c.SoftwareID = b.SoftwareID AND
                c.VersionNO = b.LatestVersion
GROUP BY a.ID
ORDER BY a.ID

SQLFiddle Démo (la réponse)

---

donc c'était il. Je vais poster un autre dès que je me souviens de n'importe quel autre FAQ sur MySQL étiquette. Je vous remercie pour la lecture de ce petit article. J'espère que vous en avez au moins un peu appris.

UPDATE 1

---

astuce No 3 ( trouver le dernier record entre deux IDs )

Schéma Donné

CREATE TABLE userList
(
    ID INT,
    NAME VARCHAR(20),
    CONSTRAINT us_pk PRIMARY KEY (ID),
    CONSTRAINT us_uq UNIQUE (NAME)  
);

INSERT INTO userList VALUES (1, 'Fluffeh');
INSERT INTO userList VALUES (2, 'John Woo');
INSERT INTO userList VALUES (3, 'hims056');

CREATE TABLE CONVERSATION
(
    ID INT,
    FROM_ID INT,
    TO_ID INT,
    MESSAGE VARCHAR(250),
    DeliveryDate DATE
);

INSERT INTO CONVERSATION VALUES (1, 1, 2, 'hi john', '2012-01-01');
INSERT INTO CONVERSATION VALUES (2, 2, 1, 'hello fluff', '2012-01-02');
INSERT INTO CONVERSATION VALUES (3, 1, 3, 'hey hims', '2012-01-03');
INSERT INTO CONVERSATION VALUES (4, 1, 3, 'please reply', '2012-01-04');
INSERT INTO CONVERSATION VALUES (5, 3, 1, 'how are you?', '2012-01-05');
INSERT INTO CONVERSATION VALUES (6, 3, 2, 'sample message!', '2012-01-05');

QUESTION

Trouver la dernière conversation entre deux utilisateurs.

Solution

SELECT    b.Name SenderName,
          c.Name RecipientName,
          a.Message,
          a.DeliveryDate
FROM      Conversation a
          INNER JOIN userList b
            ON a.From_ID = b.ID
          INNER JOIN userList c
            ON a.To_ID = c.ID
WHERE     (LEAST(a.FROM_ID, a.TO_ID), GREATEST(a.FROM_ID, a.TO_ID), DeliveryDate)
IN
(
    SELECT  LEAST(FROM_ID, TO_ID) minFROM,
            GREATEST(FROM_ID, TO_ID) maxTo,
            MAX(DeliveryDate) maxDate
    FROM    Conversation
    GROUP BY minFROM, maxTo
)

SQLFiddle Demo

Partie 2 - Les Sous-Requêtes

Ok, maintenant le patron a encore fait irruption - je veux une liste de toutes nos voitures avec la marque et un total de combien de cette marque nous avons!

c'est une excellente occasion d'utiliser le prochain tour dans notre sac de goodies SQL - le sous-jeu. Si vous n'êtes pas familier avec le terme, un sous-jeu est une requête qui court à l'intérieur d'une autre requête. Il existe de nombreuses façons de les utiliser.

pour notre demande, mettons d'abord une requête simple ensemble qui énumérera chaque voiture et la marque:

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID

Maintenant, si nous voulions simplement obtenir un nombre de wagons triés par marque, on peut bien sûr écrire ceci:

select
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    b.brand

+--------+-----------+
| brand  | countCars |
+--------+-----------+
| BMW    |         2 |
| Ford   |         2 |
| Nissan |         1 |
| Smart  |         1 |
| Toyota |         5 |
+--------+-----------+

donc, nous devrions être en mesure d'ajouter simplement la fonction count à notre requête d'origine n'est-ce pas?

select
    a.ID,
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    a.ID,
    b.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         1 |
|  2 | Ford   |         1 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         1 |
|  6 | BMW    |         1 |
|  7 | Ford   |         1 |
|  8 | Toyota |         1 |
|  9 | Toyota |         1 |
| 10 | BMW    |         1 |
| 11 | Toyota |         1 |
+----+--------+-----------+
11 rows in set (0.00 sec)

malheureusement, non, on ne peut pas faire ça. La raison en est que lorsque nous ajoutons L'ID de la voiture (colonne A. ID) nous devons l'ajouter dans le groupe by - donc maintenant, quand la fonction count fonctionne, il n'y a qu'un seul ID par ID.

C'est là que nous pouvons cependant utiliser un sous - jeu-en fait nous pouvons faire deux types complètement différents de sous-jeu qui retourneront les mêmes résultats que nous avons besoin pour cela. La première consiste simplement à placer la sous-requête dans la clause select . Cela signifie que chaque fois que nous obtenons une rangée de données, le sous-jeu s'exécute, obtenir une colonne de données et puis pop dans notre ligne de données.

select
    a.ID,
    b.brand,
    (
    select
        count(c.ID)
    from
        cars c
    where
        a.brand=c.brand
    ) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  2 | Ford   |         2 |
|  7 | Ford   |         2 |
|  1 | Toyota |         5 |
|  5 | Toyota |         5 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 11 | Toyota |         5 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  6 | BMW    |         2 |
| 10 | BMW    |         2 |
+----+--------+-----------+
11 rows in set (0.00 sec)

Et Bam! ce serait faire de nous. Si vous avez remarqué, cette requête secondaire devra être exécutée pour chaque ligne de données que nous retournons. Même dans ce petit exemple, nous n'avons que cinq marques différentes de voiture, mais la sous-série a couru onze fois que nous avons onze rangées de données que nous retournons. Donc, dans ce cas, ça ne semble pas être la façon la plus efficace d'écrire du code.

pour une approche différente, lançons un subquery et prétendre qu'il est une table:

select
    a.ID,
    b.brand,
    d.countCars
from
    cars a
        join brands b
            on a.brand=b.ID
        join
            (
            select
                c.brand,
                count(c.ID) as countCars
            from
                cars c
            group by
                c.brand
            ) d
            on a.brand=d.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         5 |
|  2 | Ford   |         2 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         5 |
|  6 | BMW    |         2 |
|  7 | Ford   |         2 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 10 | BMW    |         2 |
| 11 | Toyota |         5 |
+----+--------+-----------+
11 rows in set (0.00 sec)

D'accord, donc nous avons les mêmes résultats (ordonnés légèrement différents - il semble que la base de données voulait retourner les résultats ordonnés par la première colonne que nous avons choisie cette fois - ci) - mais les mêmes nombres droits.

alors, quelle est la différence entre les deux - et quand devrions-nous utiliser chaque type de sous-marin? Tout d'abord, assurons-nous de comprendre comment fonctionne la seconde requête. Nous avons sélectionné deux tableaux dans le from clause de notre requête, puis a écrit une requête et a dit à la base de données qu'il était en fait une table à la place - qui la base de données est parfaitement heureux avec. Il peut être certains avantages à utiliser cette méthode (ainsi que certaines limites). Le plus important est que ce sous-query a couru une fois . Si notre base de données contenait un grand volume de données, il pourrait bien y avoir une amélioration massive par rapport à la première méthode. Cependant, comme nous l'utilisons comme un tableau, nous devons apporter dans les rangées supplémentaires de données - de sorte qu'ils puissent être effectivement joints à nos rangées de données. Nous devons également être sûrs qu'il ya assez lignes de données si nous allons utiliser une jointure simple comme dans la requête ci-dessus. Si vous vous souvenez, la jointure ne tirera en arrière les lignes qui ont des données correspondantes sur les deux côtés de la jointure. Si nous ne faisons pas attention, cela pourrait entraîner des données valides n'étant pas retournés de notre table de voitures s'il n'y avait pas une rangée correspondante dans ce sous-requête.

maintenant, en regardant en arrière au premier sous-jeu, il y a aussi quelques limites. parce que nous ramenons des données dans une seule rangée, nous pouvons seulement tirer en arrière une rangée de données. Les sous-séries utilisées dans la clause select d'une requête utilisent très souvent seulement une fonction agrégée telle que sum , count , max ou une autre fonction agrégée similaire. Ils ne ont à, mais c'est souvent comment ils sont écrits.

donc, avant de passer à autre chose, jetons un coup d'oeil rapide à l'endroit où nous pouvons utiliser un sous-jeu. On peut l'utiliser dans la where clause - maintenant, cet exemple est un peu artificiel, comme dans notre base de données, il existe de meilleures façons d'obtenir les données suivantes, mais vu que c'est seulement un exemple, permet d'avoir un regard:

select
    ID,
    brand
from
    brands
where
    brand like '%o%'

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  6 | Holden |
+----+--------+
3 rows in set (0.00 sec)

ceci nous renvoie une liste d'identificateurs de marque et de noms de marque (la deuxième colonne n'est ajoutée que pour nous montrer les marques) qui contiennent la lettre o dans le nom.

maintenant, nous pourrions utiliser les résultats de cette requête dans une clause où ceci:

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in
        (
        select
            ID
        from
            brands
        where
            brand like '%o%'
        )

+----+--------+
| ID | brand  |
+----+--------+
|  2 | Ford   |
|  7 | Ford   |
|  1 | Toyota |
|  5 | Toyota |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

comme vous pouvez le voir, même si le sous-magasin retournait les trois identifiants de la marque, notre table de voitures n'avait des entrées que pour deux d'entre eux.

dans ce cas, pour plus de détails, le sous-jeu fonctionne comme si nous écrivions le code suivant:

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in (1,2,6)

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Toyota |
|  2 | Ford   |
|  5 | Toyota |
|  7 | Ford   |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

encore une fois, vous pouvez voir comment un sous-jeu vs entrées manuelles a changé l'ordre des lignes en revenant de la base de données.

pendant que nous discutons des sous-commandes, voyons ce que nous pouvons faire d'autre avec une sous-commande:

• vous pouvez placer un sous-jeu dans un autre sous-jeu, et ainsi de suite. Il y a une limite qui dépend de votre base de données, mais à moins de fonctions récursives d'un programmeur fou et maniaque, la plupart des gens ne toucheront jamais cette limite.
• vous pouvez placer un certain nombre de sous - séries dans une seule requête, quelques-unes dans la clause select , quelques-unes dans la clause from et un couple plus dans la clause where - juste se rappeler que chacun que vous mettez en rend votre requête plus complexe et susceptible de prendre plus de temps à exécuter.

si vous avez besoin d'écrire un code efficace, il peut être bénéfique d'écrire la requête un certain nombre de façons Et Voir (soit en la chronométrant ou en utilisant un expliquer plan) qui est la requête optimale pour obtenir vos résultats. La première façon qui fonctionne peut ne pas toujours être la meilleure façon de le faire.

Partie 3 - Trucs et Efficace du Code

MySQL() de l'efficacité

j'ai pensé que je voudrais ajouter certains des bits supplémentaires, des conseils et astuces.

une question que je vois venir un peu, est Comment puis-je obtenir des lignes non-appariées à partir de deux tableaux et je vois la réponse la plus communément acceptée comme quelque chose comme ce qui suit (basé sur nos voitures et marques de table-qui a Holden inscrit comme marque, mais ne figure pas dans le tableau cars):

select
    a.ID,
    a.brand
from
    brands a
where
    a.ID not in(select brand from cars)

Et oui il va travailler.

+----+--------+
| ID | brand  |
+----+--------+
|  6 | Holden |
+----+--------+
1 row in set (0.00 sec)

mais il est pas efficace dans une base de données. Voici un lien à une question de débordement de la pile poser à ce sujet, et voici un excellent en profondeur article si vous voulez entrer dans la gravitation nitty.

la réponse courte est, si l'optimiseur ne le gère pas efficacement, il peut être beaucoup mieux d'utiliser une requête comme la suivante pour obtenir des lignes non appariées:

select
    a.brand
from
    brands a
        left join cars b
            on a.id=b.brand
where
    b.brand is null

+--------+
| brand  |
+--------+
| Holden |
+--------+
1 row in set (0.00 sec)

mettre à Jour le Tableau avec la même table dans la sous-requête

Ahhh, un autre ancien mais goodie - l'ancien Vous ne pouvez pas spécifier une table cible 'marques' pour la mise à jour DE la clause .

MySQL ne vous permettra pas d'exécuter un update... requête avec un sous-Select sur la même table. Maintenant, vous pourriez penser, pourquoi ne pas simplement le mettre dans la clause où il faut? Mais que faire si vous voulez mettre à jour seulement la ligne avec la date max() parmi un tas d'autres lignes? On ne peut pas faire ça dans une clause "où".

update 
    brands 
set 
    brand='Holden' 
where 
    id=
        (select 
            id 
        from 
            brands 
        where 
            id=6);
ERROR 1093 (HY000): You can't specify target table 'brands' 
for update in FROM clause

donc, on ne peut pas faire ça hein? Eh bien, pas exactement. Il ya un contournement sournois qu'un nombre étonnamment grand d'utilisateurs ne savent pas-bien qu'il ne comprend certains hackery que vous aurez besoin de prêter attention à.

vous pouvez coller le sous-jeu dans un autre sous-jeu, ce qui met assez d'espace entre les deux requêtes pour qu'il fonctionne. Cependant, notez qu'il peut être plus sûr de coller la requête dans une transaction - cela empêchera tout autre changement étant fait aux tables pendant que la requête est en cours d'exécution.

update 
    brands 
set 
    brand='Holden' 
where id=
    (select 
        id 
    from 
        (select 
            id 
        from 
            brands 
        where 
            id=6
        ) 
    as updateTable);

Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

vous pouvez utiliser le concept de requêtes multiples dans le mot-clé de FROM. Laissez-moi vous montrer un exemple:

SELECT DISTINCT e.id,e.name,d.name,lap.lappy LAPTOP_MAKE,c_loc.cnty COUNTY    
FROM  (
          SELECT c.id cnty,l.name
          FROM   county c, location l
          WHERE  c.id=l.county_id AND l.end_Date IS NOT NULL
      ) c_loc, emp e 
      INNER JOIN dept d ON e.deptno =d.id
      LEFT JOIN 
      ( 
         SELECT l.id lappy, c.name cmpy
         FROM   laptop l, company c
         WHERE l.make = c.name
      ) lap ON e.cmpy_id=lap.cmpy

Vous pouvez utiliser autant de tables que vous voulez. Utilisez les joints extérieurs et l'union où jamais il est nécessaire, même à l'intérieur des sous-séries de table.

C'est une méthode très facile d'impliquer autant de tables et de champs.

espère que cela lui permet de trouver les tables que vous lisez à travers la chose:

jsfiddle

mysql> show columns from colors;                                                         
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+           
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+