What is mysql subquery? Mysql database subquery actual usage example

What is mysql subquery?

Subquery is also called internal query. Compared with internal query, the one containing internal query is called external query. Subqueries allow one query to be nested within another query.

Characteristics of mysql database subquery statements: Anywhere an expression can be used, a subquery can be used, as long as it returns a single value; the subquery depends on the number of return values, and the subquery has external dependencies , classification based on differences in comparison operators; this is often used in paging query SQL statements.

1: Characteristics of subqueries:

1. Subqueries can be nested in select, insert, update, delete and other statements

2. In most cases, subqueries act as intermediate result sets

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

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

2: Classification of subqueries:

1. According to the number of return values, it can be divided into: scalar subquery, multi-value subquery

2. According to the subquery's external dependency: independent Subquery, related subquery

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

Three: Use of subqueries:

1. First create two tables (student table and teacher table)

   # 创建学生表
   mysql> create table tb_student(
       -> stu_ID long,
       -> class varchar(5),
       -> score int
       -> );
   Query OK, 0 rows affected (0.23 sec)
   
   # 创建教师表
   mysql> create table tb_teacher(
       -> tea_ID long,
       -> class varchar(5),
       -> age int
       -> );
   Query OK, 0 rows affected (0.49 sec)

2. Insert some values ​​into

   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);

3. in the table has been prepared. Now proceed to the subquery exercise

• Example 1: Teacher ID of each class And the average score of the class

  mysql> select tea_ID,
      -> (select avg(score) from tb_student as s 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 (0.00 sec)

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

  mysql> select age,
      -> (select count(*) from tb_student as s 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 |     1 |
  +------+-------+
  2 rows in set (0.00 sec)

Related Recommended:

mysql paging query statement database query_MySQL

Mysql limit subquery statement_MySQL

Video: Advanced data query - where clause-six days to take you through MySQL

The above is the detailed content of What is mysql subquery? Mysql database subquery actual usage example. For more information, please follow other related articles on the PHP Chinese website!