[Mysql] basic concepts of primary keys and Foreign keys for multi-table queries using inner join and nested queries

[Mysql] basic concepts of primary keys and Foreign keys for multi-table queries using inner join and nested queries

Although multi-table queries are required in computer science and databases, many teachers have not thoroughly explained this difficulty. It's confusing to have a bunch of things that are connected to the left and the right. The instructor will tell you all the multi-Table query methods, but one method is not mastered in the end. In fact, you only need to remember one type. You can learn multi-table queries. In addition to getting a high score in your database, you can use multi-table queries in many places in practical applications. For example, in the Forum project, you can analyze the relationship between the reply and post, and provide the function of posting the user. For example, in the information management system, you can analyze the relationship between the class and students and teachers, queries the number of students in the class. You need to use multi-table queries. You can't get every table as a view. Then, do not use a relational table? Specifically, it is how to create different views for different siege lions. The following uses MYSQL as an example to illustrate how to use inner join and nested queries to implement multi-table queries. In fact, all databases are the same, and SQL statements are common.

I. Basic Objectives

It is known that in the test database, the brain can be replaced with the school database, which contains the following table:

(1) The student table I _student records the data of all the students in the school, indicating that there are three students a, B, and c in the school.

(2) the class Table I _class records the data of all the classes in the school, indicating that there are two students in the school

(3) the class-student relationship table I _class_student records the correspondence between all the classes and students in the school, indicating that there are students in Class 1, class 2, and Class 2 in the school.

Then the question arises. What students are there in the first class? How to use SQL statements to query data?

Ii. Basic Ideas <喎?http: www.bkjia.com kf ware vc " target="_blank" class="keylink"> Vc3ryb25np1_vcd4kpha + xuTKtdXiyP3VxbHt09DI58/CvPK1pbXEudjPtaO6PC9wPgo8cD48aW1nIHNyYz0 = "http://www.2cto.com/uploadfile/Collfiles/20150305/2015030510004351.png" alt = "\">

The class-student relationship table I _class_student's class_id data is taken from the class Table I _class's primary key id, and the class-student relationship table I _class_student's student_id data is taken from the student table I _student's primary key id, there is a so-called "Reference integrity", and the arrow above is a foreign key. In other tables, the key icon is the primary key. The ID column of each table has the so-called "entity integrity". That is to say, the number of the ID column cannot appear for the second time or may not be left blank, it is also called unique and not empty.

Iii. Production Process

1. Based on the above analysis, you can start with the class-student relationship table I _class_student and use the following statement:

select student_id from i_class_student where class_id=1

Find all student_id under Class 1

2. Start with the student table I _student and find out the student names corresponding to student_id. here we need to query the above results, connect to the ID column and name column name in the I _student of the entire student table before querying. First, you must have the ID column and name column name of the I _student student table. Although the ID column and name column in The I _student of the entire student table are the I _student of the whole student table, the I _student of the actual student table usually contains more than two columns of information. You can use a simple statement to find out the name column name in the I _student of the entire student table:

select id,name from i_student

The query result is as follows:

3. Set the query result in step 1 to t1, and the query result in step 2 to t2. Connect table t1 to table t2. Only the result corresponding to student_id is retained, the final result is displayed:

Therefore, connect the query statement in step 1 with the query statement in step 2 and add a condition to the query statement to obtain the following query statement, however, you do not need to query the id of the student table I _student. This id is only used for comparison. You can remove the id from the query statement in step 2,

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 correct results:

Isn't Class 1 having two students, a and B?