如何快速写出Mysql 语句
一遇到sql语句不会,或遇到复杂的sql头疼,是很多人的通病,尤其对于一些新手。本文将重点教大家如何快速的写出sql语句。
遵循如下原则:
1.将要查询的业务列出来。
2.将要做判断或过滤的东西放在where或Having中,同时where在group by前,having 在后。
3.分组聚合出现的统计字段一定要体现在group by中
4.对上诉的语句拼接即可。
如:我先创建3张表,分别为:student(学生表),course(课程表),score(得分表)
mysql> select * from student;
+------------+------+
| student_id | name |
+------------+------+
| 10010101 | jack |
| 10010102 | wang |
+------------+------+
mysql> select * from course;
+-----------+-------------+
| course_id | course_name |
+-----------+-------------+
| 1 | math |
| 2 | english |
+-----------+-------------+
mysql> select * from score;
+----------+------------+-----------+-------+
| score_id | student_id | course_id | score |
+----------+------------+-----------+-------+
| 1 | 10010101 | 1 | 80 |
| 2 | 10010101 | 2 | 90 |
| 3 | 10010102 | 1 | 30 |
+----------+------------+-----------+-------+
其中得分表通过student_id,coure_id分别关联学生表和课程表。如上学生id为10010101的学生jack的数学课程成绩是80分。
问题1:求所有学生的姓名,他们的所有课程的总成绩是多少?
按照上面的步骤我们先将要求的里列出来:
待求:student.name,score.score(需要用到score)
所有学生:group by student_id分组;
涉及的表student,score;
拼接大概是:select student.name,sum(score.score) from student,score where group by student_id.
最后修改下:select stu.name,sum(sc.score) from student stu,score sc where stu.student_id=sc.student_id group by sc.student_id;
mysql> select stu.name,sum(sc.score) from student stu,score sc where stu.student_id=sc.student_id group by sc.student_id;
+------+---------------+
| name | sum(sc.score) |
+------+---------------+
| jack | 170 |
| wang | 30 |
+------+---------------+
问题2:求所有学生中没有学完所有课程的学生名字?
按照上面的步骤我们先将要求的里列出来:
待求:student.name,score.course(需要用到count)
所有学生:group by student_id分组;
涉及的表student,score,还需要知道课程表有多少课程,所以还涉及course表
拼接大概是select stu.name from student stu,course co,score sc where sc.student_id=stu.student_id group by sc.student_id having (注意having在后)count(*) < (select count(*) from course)
最后修改下: select stu.name from student stu,score sc where stu.student_id=sc.student_id group by sc.student_id having count(*) < (select count(*) from course);
mysql> select stu.name from student stu,score sc where stu.student_id=sc.student_id group by sc.student_id having count(*) < (select count(*) from course);
+------+
| name |
+------+
| wang |
+------+
好了,今天就到这里了。