Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the method of MySQL connection query". In the daily operation, I believe that many people have doubts about the method of MySQL connection query. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "what is the method of MySQL connection query?" Next, please follow the editor to study!

Recognize the relation table again

We have been using student_info and student_score tables to store students' basic information and student performance information respectively. In fact, it is not impossible to merge them into one table. Suppose the new table name after merging the two tables is called student_merge, then it should look like this:

Student_ merge table

Numbernamesexid_numberdepartmentmajorenrollment_timesubjectscore20180101 du Ziteng male 158177199901044792 computer Science and Engineering 201809-01 Postpartum Nursing of sows 7820180101 Duzi Teng male 158177199901044792 computer Science and Engineering 2018-09-01 discussion on Saddam's War preparation 8820180102 du Qiyan female 151008199801178529 computer Science and Engineering 2018-09-01 Postpartum Care of sows 10020180102 du Qiyan female 151008199801178529 09-01 on Saddam's War preparation 9820180103 Fan Tong male 17156319980116959X computer School Software Engineering 2018-09-01 sow Postpartum Nursing 5920180103 Fan Tong male 17156319980116959X computer School Software Engineering 2018-09-01 on Saddam's War preparation 6120180104 Shi Zhenxiang female 141992199701078600 Sow Software Engineering 2018-09-01 2018-09-01 discussion on Saddam's War Prepare 4620180105 Fan Jian male 181048200008156368 Aerospace College aircraft Design 2018-09-01NULLNULL20180106 Zhu Yiqun male 197995199801078445 Aerospace College Electronic Information 2018-09-01NULLNULL

With this merged table, we can query both the basic information and the score information of the students in a query statement, such as this query statement:

SELECT number, name, major, subject, score FROM student_merge

Among them, name and major in the query list belong to the basic information of the students, subject and score belong to the students' achievement information, and number belongs to both the score information and the basic information. We can easily query all these information in a query statement of the student_ score table. But don't forget that a student may have achievement information in many subjects, that is to say, whenever we want to add a subject score information to a student, we must copy his basic information again. The redundant storage of this basic information of the same student will lead to the following problems:

Problem 1: waste of storage space.

Problem 2: when modifying a student's basic information, it must be modified in many places, which can easily cause information inconsistency and increase the difficulty of maintenance.

So in order to store as little redundant information as possible, we split this so-called student_merge table into student_info and student_ score tables at the beginning, but there is a relationship between the two tables as a link, which refers to the number columns that both tables have.

The concept of connection

The split table does solve the problem of data redundancy, but querying data becomes a problem. So far, in the query method we introduced, the query result set can only be one or more columns in a table, that is to say, there is no way to query a student's number, name, major, subject, score in a query statement.

Tip: although the subquery we introduced earlier can involve multiple tables in a query statement, the final result set produced by the entire query statement is still used to show the results of the outer query, and the results of the subquery are only used as intermediate results.

The era calls for a way to display the information of multiple tables in the result set of a query statement, and the join query undertakes this arduous historical mission. Of course, for the story to go smoothly, let's create two simple tables and fill them with a little data:

Mysql > CREATE TABLE T1 (M1 int, N1 char (1)); Query OK, 0 rows affected (0.02 sec) mysql > CREATE TABLE T2 (m2 int, N2 char (1)); Query OK, 0 rows affected (0.02 sec) mysql > INSERT INTO T1 VALUES (1,'a'), (2,'b'), (3,'c') Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > INSERT INTO T2 VALUES (2,'b'), (3,'c'), (4,'d'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql >

We have successfully established two tables T1 and T2, both of which have two columns, one of type INT and the other of type CHAR (1). The length of the two tables filled with data is like this:

Mysql > SELECT * FROM T1 + | M1 | N1 | +-+-+ | 1 | a | 2 | b | | 3 | c | +-+-+ 3 rows in set (0.00 sec) mysql > SELECT * FROM T2 +-+-+ | m2 | N2 | +-+-+ | 2 | b | 3 | c | 4 | d | +-+-+ 3 rows in set (0.00 sec) mysql >

The essence of join is to take out the records in each table and add them to the result set and return them to the user. The process of joining the T1 and T2 tables is shown in the following figure:

This process seems to be to join the records of the T1 table and the T2 table to form new and larger records, so this query process is called a join query. The result set of a join query contains a combination in which each record in one table matches each record in another table. A result set like this can be called Cartesian product. Because there are three records in table T1 and three records in table T2, the Cartesian product of the join of the two tables has 3 × 3 records. In MySQL, the syntax of the join query is also very casual, as long as the FROM statement is followed by multiple table names separated by commas, for example, the query statement in which we join the T1 table and T2 table can be written as follows:

Mysql > SELECT * FROM T1, T2 +-+ | M1 | N1 | m2 | N2 | +-+ | 1 | a | 2 | b | 2 | b | 2 | b | 3 | c | 2 | b | 1 | a | 3 | c | | 2 | B | 3 | c | 3 | c | 3 | c | 1 | a | 4 | d | 2 | b | 4 | d | 3 | c | 4 | d | +-+ 9 rows in set (0.00 sec)

The * at the query list selects each column from the table listed after the FROM statement. The query statement above is actually equivalent to the following:

Write method 1:

SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2

This way of writing is to explicitly write out the column names in the T1 and T2 tables, that is, to use the fully qualified name of the column.

Writing method 2:

SELECT m1, n1, m2, n2 FROM t1, t2

Because the column names in the T1 and T2 tables are not duplicated, it is not possible to make the server confused, and it is possible to use column names directly on the query list.

The third way of writing:

SELECT T1, T2 * FROM T1, T2

This writing means to query all the columns of the T1 table and all the columns of the T2 table.

Brief introduction of connection process

We can join any number of tables if we like, but if there are no restrictions, the Cartesian product generated by the join of these tables can be very large. For example, three tables with 100 rows of records are joined together to produce a Cartesian product of 100 × 100 × 100 million rows of data! Therefore, it is necessary to filter out a specific combination of records when joining, and the filtering conditions in the join query can be divided into two types:

Conditions involving a single table

This kind of filter condition which only involves a single table has been mentioned ten thousand times before, and we have always called it a search condition. For example, t1.m1 > 1 is a filter condition only for T1 table, t2.n2.

< 'd'是只针对t2表的过滤条件。 涉及两表的条件 这种过滤条件我们之前没见过，比如t1.m1 = t2.m2、t1.n1 >

T2.n2, etc., two tables are involved in these conditions, and we will analyze in detail how this filter condition is used later.

Let's take a look at the general execution of a join query with filter conditions, such as the following query statement:

SELECT * FROM T1, T2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2

< 'd'; 在这个查询中我们指明了这三个过滤条件： t1.m1 >

one

T1.m1 = t2.m2

T2.n2

< 'd' 那么这个连接查询的大致执行过程如下： 首先确定第一个需要查询的表，这个表称之为驱动表。此处假设使用t1作为驱动表，那么就需要到t1表中找满足t1.m1 >

1. The T1 table records that meet this condition are as follows:

+-+-+ | M1 | N1 | +-+-+ | 2 | b | | 3 | c | +-+-+ 2 rows in set (0.01sec)

We can see that there are two records in the T1 table that match t1.m1 > 1.

In the previous step, every time you get a record from the driver table, you need to look for a matching record in the T2 table. The so-called matching record refers to the record that meets the filtering criteria. Because the records in the T2 table are found according to the records in the T1 table, the T2 table can also be called a driven table. The previous step got two records from the driver table, which means that the T2 table needs to be queried twice. At this point, the filter condition t1.m1 = t2.m2 for columns involving two tables comes in handy:

So the result set of the execution of the entire join query is like this:

+-+ | M1 | N1 | m2 | N2 | +-+ | 2 | b | 2 | b | 3 | c | 3 | c | +-+ 2 rows in set (0.00 sec)

For the first record obtained from the T1 table query, that is, when t1.m1 = 2, t1.n1 ='b', the filter condition t1.m1 = t2.m2 is equivalent to t2.m2 = 2, so the T2 table is equivalent to having t2.m2 = 2 and t2.n2.

< 'd'这两个过滤条件，然后到t2表中执行单表查询，将得到的记录和从t1表中查询得到的第一条记录相组合得到下边的结果： +------+------+------+------+| m1 | n1 | m2 | n2 |+------+------+------+------+| 2 | b | 2 | b |+------+------+------+------+ 对于从t1表种查询得到的第二条记录，也就是当t1.m1 = 3, t1.n1 = 'c'时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询，将得到的记录和从t1表中查询得到的第二条记录相组合得到下边的结果： +------+------+------+------+| m1 | n1 | m2 | n2 |+------+------+------+------+| 3 | c | 3 | c |+------+------+------+------+ 从上边两个步骤可以看出来，我们上边唠叨的这个两表连接查询共需要查询1次t1表，2次t2表。当然这是在特定的过滤条件下的结果，如果我们把t1.m1 >

1 this condition is removed, then there are 3 records found from the T1 table, and the T2 table needs to be queried three times. In other words, in the two-table join query, the driven table only needs to be queried once, and the driven table may be queried multiple times.

Internal connection and external connection

After understanding the execution of the join query, the perspective goes back to our student_ info table and student_ score table. Now we want to query not only the basic information of the students, but also the score information of the students in a query sentence, so we need to join the two tables. The join process is to take records from the student_ in table and look for records with the same number in the student_ score table, so the filter condition is student_info.number = student_score.number, and the whole query statement is like this:

Mysql > SELECT student_info.number, name, major, subject, score FROM student_info, student_score WHERE student_info.number = student_score.number +-- +-- +-+ | number | name | major | subject | score | | +-+ | 20180101 | du Ziteng | computer Science and Engineering | Postpartum care of sows | | | 78 | | 20180101 | du Ziteng | computer Science and Engineering | on Saddam's War Readiness | 88 | 20180102 | du Qiyan | computer Science and Engineering | Postpartum Nursing of sows | 20180102 | du Qiyan | computer Science and Engineering | on Saddam's War Readiness | | 98 | | 20180103 | Fan Tong | Software Engineering | Postpartum Care of sows | 59 | | 20180103 | Fan Tong | Software Engineering | on Saddam's War Readiness | 61 | | 20180104 | Shi Zhenxiang | Software Engineering | Postpartum Care of sows | 55 | | 20180104 | Shi Zhenxiang | Software Engineering | on Saddam's War preparation | 46 | + +-+ 8 rows in set (0.00 sec) mysql >

Tip: both the student_ in table and the student_ score table have number columns, but we only put the number column of the student_info table in the query list of the above query statement. This is because our filter condition is student_info.number = student_score.number, and the number columns of the records extracted from both tables are the same, so you only need to place the number column in one table to the query list. That is to say, we can also put student_score.number on the query list.

From the above query results, we can see that the corresponding scores of each student have been found, but there is a problem. Fan Jian and Zhu Yiqun, that is, students with student numbers 20180105 and 20180106, did not take the exam for some reason, so there is no corresponding score record in the studnet_score table. Well, if the teacher wants to check the test results of all the students, even those who are absent from the exam should show it, but the link query we have introduced so far is unable to complete such a requirement. Let's think about this requirement a little bit, and the essence of this requirement is that records in the driven table need to be added to the result set even if there are no matching records in the driven table. To solve this problem, there are the concepts of inner connection and outer connection:

For the two tables with inner joins, the records in the driven table cannot find a matching record in the driven table, and the record will not be added to the final result set. The joins we mentioned above are all so-called inner joins.

For two externally joined tables, the records in the driven table still need to be added to the result set even if there are no matching records in the driven table.

In MySQL, depending on the selected driver table, external joins can still be subdivided into two types:

Left outer connection

Select the table on the left as the driver table.

Right outer connection

Select the table on the right as the driver table.

However, there is still a problem, even for external joins, sometimes we do not want to add all the records of the driver table to the final result set. This is difficult, sometimes match failure to add the result set, and sometimes do not add the result set, this how to do, a little sad ah. Doesn't this problem be solved by dividing the filtering conditions into two types, so the filtering conditions placed in different places have different semantics:

Filter conditions in WHERE clause

The filter condition in the WHERE clause is the kind we usually see. No matter it is inner join or outer join, any record that does not meet the filter condition in the WHERE clause will not be added to the final result set.

Filter conditions in ON clause

For records of externally connected driven tables, if records that match the filter conditions in the ON clause cannot be found in the driven table, the record will still be added to the result set, and the fields of the corresponding driven table records will be filled with null values.

It should be noted that this ON clause is specifically proposed in the scenario of whether a record in the external join driven table should add the record to the result set when the driven table cannot find a matching record, so if you put the ON clause in the inner join, MySQL will treat it like the WHERE clause, that is, the WHERE clause and the ON clause in the inner join are equivalent.

In general, we put the filter condition that only involves a single table into the WHERE clause, and the filter condition that involves the two tables into the ON clause. We also call the filter condition that is put into the ON clause as the join condition.

Tip: left outer connection and right outer connection are referred to as left connection and right connection for short, so the word `outer'in the left outer connection and right outer connection mentioned below is expanded in parentheses to indicate that the word is optional.

Syntax for left (outer) joins

The syntax of the left (outer) join is quite simple. For example, if we want to make a left outer join query between the T1 table and the T2 table, we can write:

SELECT * FROM T1 LEFT [OUTER] JOIN T2 ON connection condition [WHERE normal filter condition]

The word OUTER in square brackets can be omitted. For LEFT JOIN-type joins, the table on the left is called the outer or driven table, and the right table is called the inner table or the driven table. So in the above example, T1 is the outer or driven table, and T2 is the inner table or driven table. It is important to note that for left (outer) and right (outer) connections, the ON clause must be used to indicate the join condition. After understanding the basic syntax of the left (outer) connection, let's go back to the real problem above to see how to write a query to query all the students' score information. even candidates who fail to take the exam should be put in the result set:

Mysql > SELECT student_info.number, name, major, subject, score FROM student_info LEFT JOIN student_score ON student_info.number = student_score.number +-- +-- +-+ | number | name | major | subject | score | | +-+ | 20180101 | du Ziteng | computer Science and Engineering | Postpartum care of sows | | | 78 | | 20180101 | du Ziteng | computer Science and Engineering | on Saddam's War Readiness | 88 | 20180102 | du Qiyan | computer Science and Engineering | Postpartum Nursing of sows | 20180102 | du Qiyan | computer Science and Engineering | on Saddam's War Readiness | | 98 | | 20180103 | Fan Tong | Software Engineering | Postpartum Care of sows | 59 | | 20180103 | Fan Tong | Software Engineering | on Saddam's War Readiness | 61 | | 20180104 | Shi Zhenxiang | Software Engineering | Postpartum Care of sows | 55 | | 20180104 | Shi Zhenxiang | Software Engineering | on Saddam's War preparation | 46 | | 20180105 | Fan Jian | aircraft Design | NULL | NULL | | 20180106 | Zhu Yiqun | Electronic Information | NULL | NULL | +- -+ 10 rows in set (0.00 sec) mysql >

It can be seen from the result set that although Fan Jian and Zhu Yiqun do not have a corresponding score record, they still put it in the result set because the connection type is the left (outer) connection. It's just filled with null values in each column of the corresponding score record.

Syntax for right (outer) connections

The principle of the right (outer) connection is the same as that of the left (outer) connection, and the syntax is just to replace LEFT with RIGHT:

SELECT * FROM T1 RIGHT [OUTER] JOIN T2 ON connection condition [WHERE normal filter condition]

It's just that the driven table is the table on the right, and the driven table is the table on the left, so don't nag.

Syntax for internal joins

The fundamental difference between inner joins and outer joins is that records in the driver table are not added to the final result set when they do not meet the join conditions in the ON clause, and the join queries we started nagging are all inner joins. However, I only mentioned the simplest inner join syntax, which is to put multiple tables that need to be joined directly after the FROM clause. In fact, MySQL provides a lot of different syntax for internal joins. Let's take the T1 and T2 tables as examples:

SELECT * FROM T1 [INNER | CROSS] JOIN T2 [ON connection condition] [WHERE normal filter condition]

In other words, the following inner joins are all equivalent in MySQL:

SELECT * FROM T1 JOIN T2

SELECT * FROM T1 INNER JOIN T2

SELECT * FROM T1 CROSS JOIN T2

The above writing is equivalent to putting the table name that needs to be joined directly after the first statement, separated by a comma:

SELECT * FROM T1, T2

Now we have introduced many ways to write internal connections, but it would be nice to be familiar with one. Here we recommend INNER JOIN (because the semantics of INNER JOIN is clear and can be easily distinguished from LEFT JOIN and RIGHT JOIN). It should be noted here that because the ON clause and the WHERE clause are equivalent in the inner join, the ON clause is not required to be mandatory in the inner join.

As we said earlier, the essence of a join is to take out the records in each join table and add a matching combination to the result set and return it to the user. No matter which table is used as the driver table, the Cartesian product generated by the join of the two tables must be the same. For internal joins, records that do not meet the conditions in the ON clause or WHERE clause will be filtered out, which is actually equivalent to kicking out records that do not meet the filtering conditions from the Cartesian product of two table joins, so for internal joins, the driven table and the driven table are interchangeable and will not affect the final query results. However, for external joins, the records in the driven table will be added to the result set even if the records that meet the conditions of on clause join can not be found in the driven table, so the relationship between the driven table and the driven table is very important at this time, that is to say, the driven table and the driven table of the left and right outer joins cannot be easily interchanged.

Summary

The above said a lot, but it doesn't give you a very intuitive feeling. We directly write the three connection modes of table T1 and T2 together, so that everyone can understand it very easy:

Mysql > SELECT * FROM T1 INNER JOIN T2 ON t1.m1 = t2.m2 +-+ | M1 | N1 | m2 | N2 | + + | 2 | b | 2 | b | 3 | c | 3 | c | +- + 2 rows in set (0.00 sec) mysql > SELECT * FROM T1 LEFT JOIN T2 ON t1.m1 = t2.m2 +-+ | M1 | N1 | m2 | N2 | +-+ | 2 | b | 2 | b | 3 | c | 3 | c | 1 | a | NULL | NULL | +-- -+-+ 3 rows in set (0.00 sec) mysql > SELECT * FROM T1 RIGHT JOIN T2 ON t1.m1 = t2.m2 +-+ | M1 | N1 | m2 | N2 | +-+ | 2 | b | 2 | b | 3 | c | 3 | c | NULL | NULL | 4 | d | +-- -+-+ 3 rows in set (0.00 sec)

The result set produced by a join query is like re-pasting the information scattered in two tables into a table. The pasted result set makes it easy for us to analyze the data, so we don't have to look at the two tables all the time.

Multi-table connection

As mentioned above, we can join any number of tables if we like, and let's create a simple T3 table:

Mysql > CREATE TABLE T3 (m3 int, n3 char (1)); ERROR 1050 (42S01): Table 't3' already existsmysql > INSERT INTO T3 VALUES (3,'c'), (4,'d'), (5,'e'); Query OK, 3 rows affected (0.01sec) Records: 3 Duplicates: 0 Warnings: 0mysql >

Like the structure of T1 and T2 tables, it is also an INT column and a CHAR (1) column. Now let's take a look at how we join these three tables together:

Mysql > SELECT * FROM T1 INNER JOIN T2 INNER JOIN T3 WHERE t1.m1 = t2.m2 AND t1.m1 = t3.m3 +-+ | M1 | N1 | m2 | N2 | m3 | n3 | +-+ | 3 | c | 3 | c | 3 | c | | +-+-+ 1 row in set (0.00 sec) mysql > |

In fact, the above query can also be written like this, which one to use depends on your mood:

SELECT * FROM T1 INNER JOIN T2 ON t1.m1 = t2.m2 INNER JOIN T3 ON t1.m1 = t3.m3

The execution of this query is expressed in pseudocode, and that's it:

For each row in T1 {for each row in T2 which satisfies t1.m1 = t2.m2 {for each row in T3 which satisfies t1.m1 = t3.m3 {send to client;}

In fact, no matter how many tables are joined, it is essentially the free combination of the records of each table in accordance with the filtering conditions.

Alias of the table

We have aliased the column before, such as this:

Mysql > SELECT number AS xuehao FROM student_info;+-+ | xuehao | +-+ | 20180104 | | 20180102 | | 20180101 | 20180103 | 20180105 | | 20180106 | +-+ 6 rows in set (sec) mysql >

We can use column aliases on clauses such as ORDER BY, GROUP BY, and so on, such as:

Mysql > SELECT number AS xuehao FROM student_info ORDER BY xuehao DESC;+-+ | xuehao | +-+ | 20180106 | | 20180105 | | 20180104 | 20180103 | 20180102 | 20180101 | +-+ 6 rows in set (0.00 sec) mysql >

Similar to column aliases, we can also define aliases for tables in the same format as the aliases that define columns, separated by white space characters or AS, which can make the statement clearer when the table name is particularly long, such as:

Mysql > SELECT s1.number, s1.name, s1.major, s2.subject, s2.score FROM student_info AS S1 INNER JOIN student_score AS S2 WHERE s1.number = s2.number +-- +-- +-+ | number | name | major | subject | score | | +-+ | 20180101 | du Ziteng | computer Science and Engineering | Postpartum care of sows | | | 78 | | 20180101 | du Ziteng | computer Science and Engineering | on Saddam's War Readiness | 88 | 20180102 | du Qiyan | computer Science and Engineering | Postpartum Nursing of sows | 20180102 | du Qiyan | computer Science and Engineering | on Saddam's War Readiness | | 98 | | 20180103 | Fan Tong | Software Engineering | Postpartum Care of sows | 59 | | 20180103 | Fan Tong | Software Engineering | on Saddam's War Readiness | 61 | | 20180104 | Shi Zhenxiang | Software Engineering | Postpartum Care of sows | 55 | | 20180104 | Shi Zhenxiang | Software Engineering | on Saddam's War preparation | 46 | + +-+ 8 rows in set (0.00 sec) mysql >

In this example, we define an alias S1 for student_info in the FROM clause and an alias S2 for student student score, so that the alias can be referenced elsewhere in the query statement to replace the name of the table itself.

Self-connection

What we are talking about above is the join between multiple different tables, in fact, the same table can also be joined. For example, we can generate Cartesian products for two T1 tables, like this:

Mysql > SELECT * FROM T1, T1 * error 1066 (42000): Not unique table/alias: 't1'mysql >

Gee, an error was reported because the uncle who designed the MySQL did not allow the same table name to appear in the FROM clause. What we need here is a join of two identical T1 tables, and in order to distinguish the two identical tables, we need to define aliases for the tables. Like this:

Mysql > SELECT * FROM T1 AS table1, T1 AS table2 +-+ | M1 | N1 | M1 | N1 | +-+ | 1 | a | 1 | a | 2 | b | 1 | a | 3 | c | 1 | a | 1 | a | 2 | b | | 2 | B | 2 | b | 3 | c | 2 | b | 1 | a | 3 | c | 2 | b | 3 | c | 3 | c | 3 | c | +-+ 9 rows in set (0.00 sec) mysql >

This is equivalent to defining two copies of the T1 table, one is table1 and the other is table2. I don't want to talk about the join process here, we just think of them as different tables. Because the joined table actually comes from the same table, this join is also called a self-join. Let's take a look at the practical significance of this self-connection. for example, we want to see who are majoring in the same major as Shi Zhenxiang.

Mysql > SELECT s2.number, s2.name, s2.major FROM student_info AS S1 INNER JOIN student_info AS S2 WHERE s1.major = s2.major AND s1.name = 'Shi Zhenxiang' +-+ | number | name | major | +-+ | 20180103 | Fan Tong | Software Engineering | | 20180104 | Shi Zhenxiang | Software Engineering | +- -+ 2 rows in set (0.01sec) mysql >

S1 and S2 can be thought of as a copy of the student_info table, and we can understand this query as follows:

Filter the S1 table according to the s1.name = 'Shi Zhenxiang' search criteria to get the basic information of the student:

+-+ | number | name | sex | id_number | Department | major | enrollment_time | +-+ | 20180104 | Shi Zhenxiang | | female | 141992199701078600 | School of computer Science | Software Engineering | 2018-09-01 | +- -+

Because by querying the S1 table, it is found that the major 'Shi Zhenxiang' is actually 'software engineering', and then you should query the S2 table. The filter condition s1.major = s2.major when querying the S2 table is equivalent to s2.major = 'software engineering', so two records are queried:

+-+ | number | name | sex | id_number | Department | major | enrollment_time | +-+ | 20180103 | Fan Tong | | male | 17156319980116959X | School of computer Science | Software Engineering | 2018-09-01 | | 20180104 | Shi Zhenxiang | female | 141992199701078600 | School of computer Science | Software Engineering | 2018-09-01 | +-+ | -+

We only need the data of the number, name, and major columns of the S2 table, so the final result looks like this:

+-+ | number | name | major | +-+ | 20180103 | Fan Tong | Software Engineering | | 20180104 | Shi Zhenxiang | Software Engineering | +- -+ Transformation between join query and subquery

Some query requirements can be solved either by join query or by subquery, such as

SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = 'computer Science and Engineering')

This subquery can be replaced:

SELECT s2.* FROM student_info AS S1 INNER JOIN student_score AS S2 WHERE s1.number = s2.number AND s1.major = 'computer Science and Engineering'

When you actually use it, you can write query sentences according to your own habits.

Tip: the MySQL server may internally convert subqueries into connection queries, or it may be handled in other ways, but for our beginner rookies, these are not important. It would be nice to know what information this statement will find out!

At this point, the study on "what is the method of MySQL connection query" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.