What is a MySQL subquery? MySQL Database subquery actual use example

what is a MySQL subquery?

subqueries, also called internal queries, are called external queries, as opposed to internal queries, which contain internal queries. Subqueries allow a query to be nested within another query.

MySQL Database subquery features: Any place where an expression can be used, you can use a subquery, as long as he returns a single value, the subquery by the number of return values, the sub-query on the external dependency, the difference between the comparison operators to classify; This is often used in paged query SQL statements.

One: Features of sub-query:

1. Subqueries can be nested in statements such as Select,insert,update,delete

2. In most cases, the query acts as an intermediate result set role

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

4. Any place where an expression can be used, a subquery can be used, as long as he returns a single value

Second: Sub-query classification:

1. The number of return values can be divided into: scalar quantum query, multi-valued subquery

2. External dependencies by sub-query: Independent subquery, correlated subquery

3. By comparison operator differences: In,exists,any,some,all and many other forms

Third: Use of sub-query:

1. 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 a teacher table mysql> create table Tb_teacher (tea_id long, class varchar (5), age int, etc.); Query OK, 0 rows affected (0.49 sec) 

2. Inserts some values into the table

   insert into Tb_student VALUES (1, "a", +), insert into tb_student values (2, "a", "+"), insert into tb_student values (3, "a", +); Insert I Nto tb_student VALUES (4, "B", $), insert into tb_student values (5, "B", +), insert into tb_student values (6, "B", +); inse
    RT into Tb_teacher values (1, "A", +), insert into tb_teacher values (2, "B", +); 

3. Ready to work, next subquery exercise

• Example one: Class Teacher ID and its class average score

  mysql> Select tea_id, (select AVG (score) from Tb_student as s where S.class = T.class Group by Class), as Avg fr Om Tb_teacher as t;+--------+---------+| tea_id | AVG |+--------+---------+| 1 | 40.0000 | | 2 | 70.0000 |+--------+---------+2 rows in Set (0.00 sec) 

• Example two: The age of each class teacher and their class pass number (60 is the pass 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)