"Mysql" implements multi-table queries using inner joins and nested queries, basic concepts of primary key and foreign key

Although the multi-table query, in the computer must class, the database, inside already big speaks, but this difficulty many teachers did not speak deeply to speak thoroughly. With you a lot of net pull, left connected to the right connected things, let people confused. The teacher tells you all the methods of multi-table query, but in the end one method is not mastered. In fact, just remember one is enough, learn more table query, in addition to your test database can get a very high score, to the actual application, many places are used to multi-table query. For example, the Forum project analysis of the relationship between the post, to provide the user posted what the function of the post, such as information management system, the analysis of the relationship between the class and students, teachers, to provide the number of students in the class query function. You need to use a multi-table query, you can't always get every table as a view, and then the relationship table do not put? When it comes to how to create different views for different siege lions, use MySQL below to illustrate how to use inner joins and nested queries to implement multi-table queries. In fact, all databases are the same, and SQL statements are generic.

I. BASIC OBJECTIVES

It is known that in the test database, the school database can be self-contained, the following table exists:

(1) The student table I_student, which records the data of all the students in the school, indicates that there are 3 students in the school a,b,c

(2) Class table I_class, which records all classes in the school data, indicating that there are 2 students in the school

(3) Class-Student relationship form I_class_student, which records the correspondence between all classes and students in the school, indicates that there are 1 students in the school 1,2,2 Class 3

Then the question came, what students in Class 1? How to query with SQL statement?

Second, the basic idea

In fact, these three tables have the following simple relationship:

Class-Student Relationship table I_class_student class_id data taken from the class table I_class the primary key ID, class-Student relationship table I_class_student student_id data from the student table i_student the primary key ID, There is a so-called "referential integrity", the above arrow is the foreign key. The key icon in the rest of the table is the primary key, the ID column of each table has so-called "entity integrity", that is, the ID column number can no longer appear the second time may not be filled, also known as the only and non-empty.

Third, the production process

1, based on the above analysis, you can first on the class-Student relationship table I_class_student, using the following statement:

Select student_id from I_class_student where class_id=1

Find out all the student_id in Class 1.

2, again on the student table i_student, according to find these student_id corresponding student name on the line, here to put the above query results, and the entire student table i_student ID column and the last Name list name linked up and then query. First you have to have the ID column of the student table i_student with the last name listed in name. Although now the whole student table i_student in the ID column and the surname name is the entire student table i_student, but the actual student table i_student often not only two columns of information. Through a simple sentence can not be simpler, all the database must know the statement to find out the entire student table i_student Name column name:

Select Id,name from I_student

The query results are as follows:

3, set the first step of the query results for T1, the second step of the query result is T2, the T1 and T2 two tables connected together, only the retention ID and student_id corresponding results, the final result is obtained:

So also the first step of the query statement and the second step of the query statement, add conditional connection, then get the following query statement, but at this time we do not have to query the student table I_student ID, this ID only for the role of comparison, you can take the second step of the query in the ID removed,

Select name from i_student as T1 INNER JOIN (select student_id from I_class_student where class_id=1) as T2 where t1.id=t2 . student_id

And naturally get the right results:

Class 1 Does it have a A, B two students?

"Mysql" implements multi-table queries using inner joins and nested queries, basic concepts of primary key and foreign key