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

Editor to share with you the MySQL database multi-table query within the join, external join, sub-query example analysis, I believe that most people do not understand, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to understand it!

Multi-table query

Use a single select statement to extract relevant query results from multiple tables, and multi-table joins are usually based on related parent-child tables

1 Cross connection

All rows in the first table are multiplied by all rows in the second table, which is the Cartesian product.

Create a table of consumers and customers:

The code is as follows:

-- create table customers (--id int primary key auto_increment,-- name VARCHAR (20) not null,-- address VARCHAR (20) not NULL--);-- CREATE table orders (--order_namre VARCHAR (20) primary key,-- num char (20) not NULL,-- price int not null,-- customers_id int,-- constraint cus_ord_fk FOREIGN key (customers_id) REFERENCES customers (id)--) insert data by yourself. Syntax:

Implicit syntax (without keywords): select * from customers,orders

The running results are as follows:

Explicit syntax (using keywords): select * from customers c INNER JOIN orders o ON c.id=o.customer_id

The results of the two runs are the same, but there is an error in the Cartesian product, which is corrected by the following method

2 Internal connection

Because the result set obtained by the cross connection is wrong. Therefore, the inner connection is based on the cross connection.

Only the data rows in the join table that match the join conditions are listed, and records that do not match are not listed.

Syntax:

Implicit syntax:

Select * from customers,orders where customers.id=orders.customers_id

Explicit syntax:

Select * from customers c INNER JOIN orders o ON c.id=o.customer_id

The running result is as follows

We can also give the program an alias:

The code is as follows:

Select * from customers as corder o where c. Idorders o. Customersaccountability.select * from customers as c inner join orders o on c.id=o.customers_id

3 external connection

The internal link lists only the information of all users who have purchased goods, not those who have not purchased goods.

The outer link is based on a table, other table information is spliced, if there is a splicing, if there is no display of null; external links are divided into left outer links and lower right links.

Left outer join: stitching based on the table to the left of the keyword

Syntax:

Select * from customers c LEFT JOIN orders o ON c.id=o.customer_id

Right outer join: based on the table to the right of the keyword

Syntax:

Select * from orders o RIGHT JOIN customers c ON c.id=o.customer_id

4 subquery

In some cases, when a query is carried out, the necessary condition is the result of another select statement, and a subquery is used, and the query (internal query) that is first executed in order to provide data to the main query (external query) is called subquery; the subquery is divided into nested subquery and related subquery.

Nested subqueries:

The execution of the internal query is independent of the external query, and the internal query is executed only once. After the execution, the results are used as the conditions of the external query (the subquery statements in the nested subquery can be taken out and run separately. )

Grammar and exercises: find out all the students who have been taught by teachers with id 1.

Select * from students where id in (select s_id from teacher_student where t_id=1)

Related subqueries:

The execution of the internal query depends on the data of the external query, and each time the external query is executed, the internal query is also executed. Each time, the external query is executed first, taking a tuple from the external query table, passing the data in the current tuple to the internal query, and then executing the internal query. According to the results of the execution of the internal query, it is judged whether the current tuple meets the where condition in the external query, and if so, the current tuple is a record that meets the requirements, otherwise it does not meet the requirements. The external query then continues to fetch the next tuple data and perform the above operation until all tuples are processed.

Create three tables

Exercise 1. Find out all the students who have been taught by a teacher with id 1.

-create a teacher table

Create table teacher1 (id int primary key auto_increment,name char (20) not NULL,subject char (20) not null)

-create a student table

Create table student1 (id int primary key auto_increment,name char (20) unique not null,age int null)

-create a third table

Create table tea_stu (id int PRIMARY KEY,name char (20), t_id int,s_id int,score int not null,constraint teacher1_id_fk foreign key (t_id) references teacher1 (id), constraint student_id_fk foreign key (s_id) references student1 (id))

Exercise 1. Find out all the students who have been taught by a teacher with id 1.

Practice 1 write it in a separate way:

Select s_id from tea_stu where tours: select * from student1 where id in (2P3)

Practice 2:

Select * from student1 where id in (select s_id from tea_stu where t_id=1)

Related subqueries:

The execution of the internal query depends on the data of the external query, and each time the external query is executed, the internal query is also executed. Each time, the external query is executed first, taking a tuple from the external query table, passing the data in the current tuple to the internal query, and then executing the internal query. According to the results of the execution of the internal query, it is judged whether the current tuple meets the where condition in the external query, and if so, the current tuple is a record that meets the requirements, otherwise it does not meet the requirements. The external query then continues to fetch the next tuple data and perform the above operation until all tuples are processed.

Seek: the score of a student whose score in each subject is higher than the average score.

Select * from tea_stu as a where a.score > (select avg (b.score) from tea_stu as b where a.s_id=b.s_id)

The above is all the contents of this article entitled "sample Analysis of intra-table join, external join and Sub-query in MySQL Database". Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.