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

This article mainly introduces the example analysis of sub-query in mysql database, which is very detailed and has certain reference value. Friends who are interested must finish it!

What is a MySQL subquery?

Sub-query, also known as internal query, relative to the internal query, including the internal query is called the external query. Subqueries allow you to nest one query within another.

The characteristics of mysql database subquery statement: subquery can be used wherever expressions can be used, as long as it returns a single value; subqueries are classified by the number of returned values, subqueries are external dependencies, and compare the differences of operators; this is often used in paging query sql statements.

First, the characteristics of subqueries:

Subqueries can be nested in statements such as select,insert,update,delete

In most cases, subqueries act as intermediate result sets

Subqueries can be nested, and nesting restrictions vary depending on memory and expression complexity

Anywhere an expression can be used, a subquery can be used, as long as it returns a single value

Second, the classification of subqueries:

According to the number of returned values, it can be divided into scalar quantum query and multi-valued subquery.

External dependencies by subquery: independent subquery, related subquery

According to the differences of comparison operators: IN,EXISTS,ANY,SOME,ALL and other forms

Three: the use of subqueries:

First create two tables (student table and teacher table)

# create student table mysql > create table tb_student (- > stu_ID long,-> class varchar (5),-> score int->); Query OK, 0 rows affected (0.23 sec) # create teacher table mysql > create table tb_teacher (- > tea_ID long,-> class varchar (5),-> age int->); Query OK, 0 rows affected (0.49 sec)

Insert some values into the table

Insert into tb_student values (1, "A", 20); insert into tb_student values (2, "A", 30); insert into tb_student values (3, "A", 70); insert into tb_student values (4, "B", 60); insert into tb_student values (5, "B", 70); insert into tb_student values (6, "B", 80); insert into tb_teacher values (1, "A", 25) Insert into tb_teacher values (2, "B", 40)

After the preparation work is completed, the subquery exercise is followed.

Example 1: ID of teachers in each class and average score of their classes

Mysql > select tea_ID,-> (select avg (score) from tb_student ass where s.class = t.class group by class)-> as Avg from tb_teacher as t +-+-+ | tea_ID | Avg | +-+-+ | 1 | 40.0000 | | 2 | 70.0000 | +-+-+ 2 rows in set (70.0000 sec)

Example 2: the age of teachers in each class and the number of passing classes (60 is the passing line)

Mysql > select age,-> (select count (*) from tb_student ass where s.class = t.class & & s.score > = 60 group by class)-> as Count from tb_teacher as t order by Count desc +-+-+ | age | Count | +-+-+ | 40 | 3 | 25 | 25 | 1 | +-+-+ 2 rows in set (0.00 sec) above are all the contents of the article "sample Analysis of Sub-query in mysql Database". Thank you for reading! Hope to share the content to help you, more related 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.