MySQL SQL optimization nesting query-ground tornado

(-1) write in front

The data of this essay is used in Http://blog.csdn.net/friendan/article/details/8072668#comments , and there are some common Select query exercises.

I'm using it. MySQL the command line that comes with it

(1) Data Matching

Student Table

+---------+-----------+------+------+--------------+

| Sno | Sname | Ssex | Sage | sdept |

+---------+-----------+------+------+--------------+

| 9512101 | Li Yong | male   | 19 | Computer Systems |

| 9512102 | Liu Chen | male   | 20 | Computer Systems |

| 9512103 | Wang Min | female   | 20 | Computer Systems |

| 9521101 | Tension | male   | 22 | Department of Information |

| 9521102 | Wu Bing | female   | 21 | Department of Information |

| 9521103 | Zhang Hai | male   | 20 | Department of Information |

| 9531101 | money, little force . | female   | 18 | Department of Mathematics |

| 9531102 | Wang Dali | male   | 19 | Department of Mathematics |

+---------+-----------+------+------+--------------+

Course Table

+-----+--------------------+-------+

| Cno | Cname | Hours |

+-----+--------------------+-------+

| C01 | Computer Science and culture    | |

| C02 |    VB | 90 |

| C03 | Computer network    | |

| C04 | Database Basics   | 108 |

| C05 | Advanced Mathematics   | |

| C06 | Data Structure    | |

+-----+--------------------+-------+

SC Table

+---------+------+-------+

| Sno | Cno | Grade |

+---------+------+-------+

| 9512101 |    C01 | 90 |

| 9512101 |    C02 | 86 |

| 9512101 |  C06 | NULL |

| 9512102 |    C02 | 78 |

| 9512102 |    C01 | 82 |

| 9512102 |    C02 | 75 |

| 9512102 |    C04 | 92 |

| 9512102 |    C05 | 50 |

| 9521103 |    C02 | 68 |

| 9521103 |  C06 | NULL |

| 9531101 |    C01 | 80 |

| 9531101 |    C05 | 95 |

| 9531102 |    C05 | 85 |

+---------+------+-------+

(2) Questions and Answers

The Computer department is the name, gender, and achievement of the students who have repaired the " database base " .

The first way :

SELECT Sname name , Ssex sex , SC. Grade Results

Desc Select distinct student.sno,student.ssex,sc.cno,sc.grade from Studen T,sc,course where

student.sdept= " computer system " and student.ssex= " male " and

Student.sno = Sc.sno and Sc.cno = (select CNO from course where cname = " database base ") \g;

The second way

Desc Select B.sno,b.ssex,a.grade from (select Sno,ssex,sname from student) as B,

(select Sno,grade from SC where

CNO = (select Cno from course where cname= " database base c3>")) as a where A.sno = B. Sno \g;

(3) Summary

usually nested queries recommend using Join...on ... statement, which reduces the creation of temporary tables because Select clause creates a temporary table

Although none of the above statements use Join...on ... statement, the second way is still less than the first way to create a temporary table,

And the second way to filter the conditions specific to a single table, clear thinking, clear conditions, readability is very good, performance is better than the first way.

MySQL SQL optimization nesting query-ground tornado