Exemple d'implémentation d'une requête à sept tables dans MySQL (2)

Que pensez-vous du contenu du chapitre précédent ?

Ensuite, continuons à approfondir petit à petit.

1. Recherchez toutes les informations sur les scores supérieurs à 60 points :

mysql> select * from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and
 tadd.t=teacher.t) and cphone.s=student.s)as a1 where score>60;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
|    1 | 刘一   | 福建   |   18 |     2 |    2 | 数学   |    78 | 贺高   | 深圳   | 男   |  12345 |
|    1 | 刘一   | 福建   |   18 |     3 |    3 | 英语   |    67 | 杨艳   | 上海   | 男   |  12345 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |
|    2 | 钱二   | 深圳   |   19 |     2 |    2 | 数学   |    81 | 贺高   | 深圳   | 女   |  12346 |
|    2 | 钱二   | 深圳   |   19 |     3 |    3 | 英语   |    92 | 杨艳   | 上海   | 女   |  12346 |
|    2 | 钱二   | 深圳   |   19 |     4 |    4 | 物理   |    68 | 周磊   | 湖南   | 女   |  12346 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |
|    3 | 张三   | 上海   |   17 |     3 |    3 | 英语   |    88 | 杨艳   | 上海   | 男   |  12347 |
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |
|    4 | 李四   | 湖南   |   18 |     3 |    3 | 英语   |    90 | 杨艳   | 上海   | 女   |  12348 |
|    4 | 李四   | 湖南   |   18 |     4 |    4 | 物理   |    93 | 周磊   | 湖南   | 女   |  12348 |
|    5 | 王五   | 江西   |   17 |     3 |    3 | 英语   |    78 | 杨艳   | 上海   | 男   |  12349 |
|    6 | 赵六   | 广西   |   19 |     2 |    2 | 数学   |    68 | 贺高   | 深圳   | 女   |  13349 |
|    6 | 赵六   | 广西   |   19 |     4 |    4 | 物理   |    71 | 周磊   | 湖南   | 女   |  13349 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
14 rows in set (0.00 sec)

2. Recherchez toutes les informations sur les étudiants de sexe masculin âgés de 18 ans dont les résultats aux tests sont supérieurs à 60 points :

mysql> select * from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and
 tadd.t=teacher.t) and cphone.s=student.s)as a1 where (score>60 and sage=18)and ssex="男";
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
|    1 | 刘一   | 福建   |   18 |     2 |    2 | 数学   |    78 | 贺高   | 深圳   | 男   |  12345 |
|    1 | 刘一   | 福建   |   18 |     3 |    3 | 英语   |    67 | 杨艳   | 上海   | 男   |  12345 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+
2 rows in set (0.00 sec)

3. Interrogez le nombre total de cours suivis par chaque élève, la note totale, la note moyenne et toutes ses informations (sauf le champ CNAME) :

Cela paraît simple, mais :

mysql> select count(t),sum(score),avg(score),student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone
 where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1;
ERROR 1054 (42S22): Unknown column 'student.s' in 'field list'

Puisque AS a une nouvelle table, ne placez pas les champs de requête sous l'ancienne table.

mysql> select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1;
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|       21 |       1483 |    70.6190 |    1 | 刘一   | 福建   |   18 |     1 |    56 |    1 | 叶平   | 福建   | 男   |  12345 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
1 row in set (0.08 sec)

Si aucun groupe n'est spécifié, il suffit de mettre directement le groupe. Une ligne de données est incluse.

Bonne réponse :

mysql> select a1.*,sum(score),count(t),avg(score) from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and
 teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | sum(score) | count(t) | avg(score) |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+
|    1 | 刘一   | 福建   |   18 |     1 |    1 | 语文   |    56 | 叶平   | 福建   | 男   |  12345 |        259 |        4 |    64.7500 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |        320 |        4 |    80.0000 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |        282 |        4 |    70.5000 |
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |        271 |        3 |    90.3333 |
|    5 | 王五   | 江西   |   17 |     1 |    1 | 语文   |    46 | 叶平   | 福建   | 男   |  12349 |        177 |        3 |    59.0000 |
|    6 | 赵六   | 广西   |   19 |     1 |    1 | 语文   |    35 | 叶平   | 福建   | 女   |  13349 |        174 |        3 |    58.0000 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------------+----------+------------+
6 rows in set (0.00 sec)

4. Interrogez le nombre total de cours suivis par chaque élève, la note totale, la note moyenne et toutes les informations, puis triez la note totale par ordre décroissant et affichez toutes les informations. étudiants dont la note d'évaluation n'est pas inférieure à 80 points. Informations :

mysql>  select * from(select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s order by sum(score) desc)a3 having avg(score)>=80;
Empty set (0.00 sec)

<.>L'instruction SQL est correcte (le champ CNAME est manquant), mais le résultat est...

+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|        4 |        320 |    80.0000 |    2 | 钱二   | 深圳   |   19 |     1 |    79 |    1 | 叶平   | 福建   | 女   |  12346 |
|        4 |        282 |    70.5000 |    3 | 张三   | 上海   |   17 |     1 |    91 |    1 | 叶平   | 福建   | 男   |  12347 |
|        3 |        271 |    90.3333 |    4 | 李四   | 湖南   |   18 |     2 |    88 |    2 | 贺高   | 深圳   | 女   |  12348 |
|        4 |        259 |    64.7500 |    1 | 刘一   | 福建   |   18 |     1 |    56 |    1 | 叶平   | 福建   | 男   |  12345 |
|        3 |        177 |    59.0000 |    5 | 王五   | 江西   |   17 |     1 |    46 |    1 | 叶平   | 福建   | 男   |  12349 |
|        3 |        174 |    58.0000 |    6 | 赵六   | 广西   |   19 |     1 |    35 |    1 | 叶平   | 福建   | 女   |  13349 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
6 rows in set (0.00 sec)

mysql>  select * from(select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s order by sum(score) desc)a3 having avg(score)>0;
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|        4 |        320 |    80.0000 |    2 | 钱二   | 深圳   |   19 |     1 |    79 |    1 | 叶平   | 福建   | 女   |  12346 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
1 row in set (0.00 sec)

Et cette déclaration et l'apparition de ce résultat m'a complètement dérouté.

Enfin, j'ai pensé à un moyen de combattre le feu par le feu et je l'ai finalement capturé.

mysql>  select * from(select count(t),sum(score),avg(score),s,sname,cadd,sage,class,score,t,tname,tadd,ssex,cphone from(select student.s,sname,cadd,sage,sc.class,score,teacher.t,tname,tadd,ssex,cphone from student,teacher,sc,course,
 cadd,tadd,cphone where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s order by sum(score) desc)a3 having sum(score)/4>=80;
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| count(t) | sum(score) | avg(score) | s    | sname  | cadd   | sage | class | score | t    | tname  | tadd   | ssex | cphone |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
|        4 |        320 |    80.0000 |    2 | 钱二   | 深圳   |   19 |     1 |    79 |    1 | 叶平   | 福建   | 女   |  12346 |
+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
1 row in set (0.02 sec)

Cependant, il n'y a pas œufs Utiliser...

Bien sûr, il y a une bonne réponse :

mysql> select * from (select a1.*,sum(score) as ss,count(t) as ct,avg(score) as a3 from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone
 where ((((student.s=sc.s and teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s desc)as a2 where a3=80 or a3>80;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | ss   | ct | a3      |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |  271 |  3 | 90.3333 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |  320 |  4 | 80.0000 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
2 rows in set (0.00 sec)

Par rigueur scientifique (bon, ok, j'ai fait trop d'erreurs, j'en ai peur), vérifions ensemble :

mysql> select * from (select a1.*,sum(score) as ss,count(t) as ct,avg(score) as a3 from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and
 teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s desc)as a2 where a3=80 or a3>60;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | ss   | ct | a3      |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |  271 |  3 | 90.3333 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |  282 |  4 | 70.5000 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |  320 |  4 | 80.0000 |
|    1 | 刘一   | 福建   |   18 |     1 |    1 | 语文   |    56 | 叶平   | 福建   | 男   |  12345 |  259 |  4 | 64.7500 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
4 rows in set (0.00 sec)

mysql> select * from (select a1.*,sum(score) as ss,count(t) as ct,avg(score) as a3 from(select student.s,sname,cadd,sage,sc.class,teacher.t,cname,score,tname,tadd,ssex,cphone from student,teacher,sc,course, cadd,tadd,cphone where ((((student.s=sc.s and
 teacher.t=course.t) and course.class=sc.class)and cadd.s=student.s)and tadd.t=teacher.t) and cphone.s=student.s)as a1 group by s desc)as a2;
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
| s    | sname  | cadd   | sage | class | t    | cname  | score | tname  | tadd   | ssex | cphone | ss   | ct | a3      |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
|    6 | 赵六   | 广西   |   19 |     1 |    1 | 语文   |    35 | 叶平   | 福建   | 女   |  13349 |  174 |  3 | 58.0000 |
|    5 | 王五   | 江西   |   17 |     1 |    1 | 语文   |    46 | 叶平   | 福建   | 男   |  12349 |  177 |  3 | 59.0000 |
|    4 | 李四   | 湖南   |   18 |     2 |    2 | 数学   |    88 | 贺高   | 深圳   | 女   |  12348 |  271 |  3 | 90.3333 |
|    3 | 张三   | 上海   |   17 |     1 |    1 | 语文   |    91 | 叶平   | 福建   | 男   |  12347 |  282 |  4 | 70.5000 |
|    2 | 钱二   | 深圳   |   19 |     1 |    1 | 语文   |    79 | 叶平   | 福建   | 女   |  12346 |  320 |  4 | 80.0000 |
|    1 | 刘一   | 福建   |   18 |     1 |    1 | 语文   |    56 | 叶平   | 福建   | 男   |  12345 |  259 |  4 | 64.7500 |
+------+--------+--------+------+-------+------+--------+-------+--------+--------+------+--------+------+----+---------+
6 rows in set (0.00 sec)

À travers ce cas, nous pouvons clairement voir que la requête conjointe de sept tables est toujours très difficile, et souvent les résultats de la requête Bien que les résultats sont corrects, et parfois même les phrases semblent correctes... mais en réalité, elles sont toutes fausses. Une déclaration vraiment vraie doit résister à des vérifications répétées (j'ai finalement compris, encouragez-moi s'il vous plaît).

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!