MYSQL-----Use SELECT query, solving ideas to summarize---essence!

Select query statement, as a tester, using this statement is a routine, is a must grasp of the part, from the beginning to learn MySQL to online search questions to do, began to do a mess, get the title dizzy, not start, now slowly summed up a set of their own way to do the problem, very open sen, hey! From simple questions to more difficult questions, the idea is actually the same, today will do a summary of ideas, (description: Use of the key words such as self-learning, only explain the idea of the problem) see as follows:

We will use the table in the query, there are 4 tables are as follows:

1. Check out the "computer department" teacher's teaching results table

Problem Solving Ideas:
1.1) First, according to the requirements of the topic, find the relevant list, the table is related to: Teachers table, curriculum, scores table
1.2) Find the correlation points between the three tables, such as: Teacher table and Course table, associated point: TNO. Course table and score table, associated point: Sno
First, the problem has been disassembled, after the disassembly step by step to query, until the query to the results of the requirements of the topic, according to the problem-solving steps are divided into 3 steps, and each step with a specific query statement:
1th step: Find the ' computer system ' in which table, according to the graph can see ' computer system ' in the teacher table. And the column name appears as depart, based on this information to find the teacher number representing ' computer Department ':

SelectRn fromTeacherwhereDepart ='Computer Department';#1, results: TNO: 804, 825

2nd step: Find the TNO corresponding SNO data in the associated course curriculum based on the TNO results of the 1th step, so you need to use "1th step" as a condition
　　      SelectCno fromCoursewhereTnoinch(SelectTno fromTeacherwhereDepart ='Computer Department')#2, results: 3-105, 3-245

the 3rd step: The first 2 steps have been the ' computer Department ' teachers and the teaching of the course search completed, and finally left to find results, so the ' 2nd step ' find the CNO results as a condition, in the score table to find the SNO value corresponding results can be 　　　　　 SelectCno,degree fromScorewhereCnoinch(SelectCno fromCoursewhereTnoinch(SelectTno fromTeacherwhereDepart ='Computer Department'))#3, results: CNO for 3-105, 3-245 corresponds to degree will show all
  
#运行第3步, it shows the result of the title request.

2, inquires the ' sung ' teacher teaches the student's highest achievement, the result shows: The highest achievement student basic information as well as the result

Problem Solving Ideas:
1th Step: Query ' sung ' teacher's TNO
 　　 Select tno from teacher where tname=' sung '    #结果显示: 804

2nd Step: Search results for 1th step are conditional, find TNO corresponding CNO course number in course curriculum
  　 Select cno from course where tno on (select tno  from Teacher where tname=' sung ') #结果显示: 3-245


3rd Step: The 2nd step of the CNO results for the third step of the condition, to find out the CNO course number in the Score score table corresponding to the results. To see the correctness of the results, add the CNO when the results are displayed       
SelectCno,degreeFromscorewhereCNOIn (selectCNOFromCoursewhereTNOIn (selectTNOFromTeacherwheretname= ' sung '))#查询结果: 3-245 The corresponding scores are: 86,75,68

4th step: After the completion of the 3rd step, has been the ' sung ' teacher teaching students all the results, according to the requirements, need to find the highest score, so the 3rd step to modify, need to use Max (find max)
SelectCno,max (degree)FromscorewhereCNOIn (selectCNOFromCoursewhereTNOIn (selectTNOFromTeacherwheretname= ' sung '))

The 5th step: The above 4 steps have the teacher corresponding to the highest scores of students to query out, the remaining step to show the basic information of students, need to use the student table, so need to score and student two tables associated with, using Sno
  SelectS.*,cno,max (degree) fromScore Sc,student SwhereCnoinch(SelectCno fromCoursewhereTnoinch(SelectTno fromTeacherwhereTname= ' sung ')) andS.sno = Sc.sno;

#运行第5步显示的就是题目要求的结果

3, the query out 4 teachers, each teacher taught the highest scores of students, the results show: basic information of students, teachers basic information, course name, results, and according to the results from high to low display

#试题的解答案本宝宝已经列在下方, according to the above-mentioned problem-solving ideas, the answer to understand it can be
Selects.*,t.*, Sc.cno,sc.degree fromteacher T,course C,score SC, student swhereSc.cnoinch(SelectCno fromCoursewhereTnoinch(SelectTno fromteacher)) and T.tno  = C.tno andC.cno = Sc.cno andS.sno = Sc.snoGroup bySc.cnoOrder byDegreedesc;

A summary of solving ideas:

1. Decomposition questions

2. Find the corresponding table after the decomposition test

3. Find the correlation points between the corresponding tables

4, step by step according to the decomposition steps to achieve, the final result came out

Finished, the baby will be MySQL query problem solving ideas to write, problem-solving ideas to comb clear, in the difficult query question is not afraid, the company is left me a person, hurriedly run home ~ ~ Dragon Boat Festival Walk!

MYSQL-----Use SELECT query, solving ideas to summarize---essence!