mysql中实现七表查询实例(二)

黄舟
发布: 2017-09-09 14:42:42
原创
1095 人浏览过

上一章的内容大家觉得怎么样?

接下来,让我们来一点一点继续深入。

1.查询成绩大于60分的所有信息:

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.查询年龄为18岁的男性学生考试成绩大于60分的所有信息:

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.查询每个学生参加课程的总数,总分,平均分及其所有信息(CNAME字段除外):

看上去很简单,但是:

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'
登录后复制

既然AS新表了,就不要再把查询的字段归到旧表下面了。

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)
登录后复制


没有指定分组,就直接把第一行的数据套过来了。

正解:

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.查询每个学生参加课程的总数,总分,平均分及其所有信息后按照总分排降序后显示评价分不小于80分的学生的所有信息:

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)
登录后复制


SQL语句是没有错的(CNAME字段漏了),但是结果就......

+----------+------------+------------+------+--------+--------+------+-------+-------+------+--------+--------+------+--------+
| 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)
登录后复制


而这个语句,这个结果的出现令我彻底蒙圈了。

最后想了一个以毒攻毒的办法终于将之拿下。

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)
登录后复制


然而并没有什么卵用......

当然还是有正解的:

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)
登录后复制


出于科学严谨的态度(呃,好吧,是偶搞错太多次了,怕了),让我们一起来验证一下:

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)
登录后复制

通过这个案例我们可以清楚的看到七个表的联合查询还是很有挑战性的,很多时候查询出来的结果虽然是对的,甚至有些时候就连语句看起来也是对的......但是,呃,其实都是错的。真正真确的语句是必须经得起反复验证的(终于搞定了,鼓励下)。

以上是mysql中实现七表查询实例(二)的详细内容。更多信息请关注PHP中文网其他相关文章!

相关标签:
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责声明 Sitemap
PHP中文网:公益在线PHP培训,帮助PHP学习者快速成长!