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

This article mainly explains the "sql multi-table join query statements are used", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "sql multi-table join query statements used in what is" it!

In mysql, you can use the "inner join" statement to query multiple intra-table joins, just use the "select * from table inner join table on table 1. Field = Table 2. Field;" statement. Combines records from two tables to return records that match the associated fields.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Internal connection

Join or inner join

SQL statement: select * from student inner join score on student.Num=score.Stu_id

The statement at this time is equivalent to: select * from student,score where student.ID=course.ID

Extended data:

External connection

1. Connect left join or left outer join to the left

SQL statement: select * from student left join score on student.Num=score.Stu_id

two。 Connect right join or right outer join on the right

SQL statement: select * from student right join score on student.Num=score.Stu_id

3. Fully externally connected full join or full outer join

SQL statement: select * from student full join score on student.Num=score.Stu_id

Through the above three methods, you can join different tables together into a large table, and then the query operation is easier.

For select * from student,score;, try not to use this statement, the result is too cumbersome.

Cross connection

Cross join, the cross join of clauses without where specifying query conditions will result in the Cartesian product of two tables.

SQL statement: select * from student cross join score

Table joins with different structures

When there is a many-to-many relationship between the two tables, we need to create an intermediate table, student_score, which has at least two primary keys of the table.

SQL statement: select s.name _ C.Cname from student_score as sc left join student as s on s.Sno=sc.Sno left join score as c on c.Cno=sc.Cno

Select Centrename grade from student left join score on student.Num=score.Stu_id where name=' Li Wuyi'

The red part is the middle table, which is a total table that collects all the contents of the two tables.

The UNION operator is used to merge the result sets of two or more select statements.

SELECT statements within UNION must have the same number of columns, each column must have a similar data type, and the columns in each SELECT statement must be in the same order.

Select Num from student union select Stu_id from score

The union operator is duplicated by default, and union all can be used if duplicate values are allowed. For two tables with the same structure, union can also merge them into one table:

Select * from student1 union select * from student2

Subquery

Sometimes a subquery is used when the condition for a query is the result of another select statement.

1. Subquery with IN keyword

SQL statement: select * from student where Num IN (select Stu_id from score)

two。 Subquery with EXISTS keyword

The query within exists returns a true value. If true is returned, the external query is queried, otherwise the external query is not queried.

SQL statement: select * from student where exists (select * from score where computers' computer')

3. Subquery with ANY keyword

Using the ANY keyword, as long as one is satisfied, the outer query is executed through this condition.

SQL statement: select sname, (date_format (from_days (now ())-to_days (birthday)),'% Y') + 0) as' age 'from student where birthday > ANY (select birthday from student where bumen=' computer Department')

4. Subquery with ALL keyword

Using the ALL keyword must satisfy all the results returned by all inner query statements before executing the outer query

SQL statement: select sname, (date_format (from_days (now ())-to_days (birthday)),'% Y') + 0) as' age 'from student where birthday > ALL (select birthday from student where bumen=' computer Department')

Thank you for your reading, the above is the "sql multi-table join query statements used in what" the content, after the study of this article, I believe you on the sql multi-table join query statements used in this question has a more profound understanding, the specific use of the situation also need to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.