What are the statements used in multi-table join queries?

In mysql, you can use the "inner join" statement to perform multi-table intra-join queries. You only need to use the "select * from table inner join table on table 1. field = table 2. field;" statement. . Combine records from two tables and return records with matching associated fields.

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

Inner join

join or inner join

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

This The statement is equivalent to: select * from student,score where student.ID=course.ID;

Extended information:

Outer connection

1. Left join left join or left outer join

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

2. Right join right join or right outer join

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

3. Complete outer join 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, different tables can be joined together into one large table, and subsequent queries can The operation is simpler.

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

Cross join

Cross join, a cross join without a clause specifying query conditions where will produce the Cartesian product of the two tables.

SQL statement: select * from student cross join score;

Table connection with different structures

When two tables have a many-to-many relationship, we need to establish an intermediate Table student_score, the intermediate table must have at least the primary keys of two tables.

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 C_name,grade from student left join score on student.Num=score.Stu_id where name='李五一';

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

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

The SELECT statements inside UNION must have the same number of columns, each column must also have similar data types, and the order of the columns in each SELECT statement must be the same.

select Num from student union select Stu_id from score;

The union operator checks for duplicates by default. If duplicate values ​​are allowed, union all can be used. For two tables with the same structure, union can also merge them into one table:

select * from student1 union select *from student2;

Subquery

Sometimes, the condition required for query is the result of another select statement, that is Subqueries will be used.

1. Subquery with IN keyword

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

2. With EXISTS keyword The inner query of the subquery

exists returns a true value. If true is returned, the outer query will query, otherwise the outer query will not query.

SQL statement: select * from student where exists(select * from score where C_name='computer');

3. Subquery with ANY keyword

Use As long as one of the ANY keywords is satisfied, the outer query will be 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 Science 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 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 Science Department');

Related learning recommendations: mysql tutorial(video)

The above is the detailed content of What are the statements used in multi-table join queries?. For more information, please follow other related articles on the PHP Chinese website!