Database subquery, database subquery

Database subquery, database subquery
Subquery

A select statement also contains another select statement, in which the select statement is called a subquery.

Subqueries can be divided into the following categories based on the position where select appears:

From subquery

Where subquery

Exists subquery

From the results returned by select, subqueries can be divided:

Scalar Query

The query result has only one value.

Example:

Requirement: query the classrooms where zhangsan is located

Ideas:

First, write and finally get the query = classroom

Select room from class ......

Query zhangsan by condition =

Where id = (select c_id from stu where s_name = 'hangsan ')

Column subquery

The query result is a column of data with multiple rows.

Requirements:

Query all students whose class is php

Select * from stu where c_id = (select id from class where c_name = 'php ');

Query all students whose classes are php and ios.

Select * from stu where c_id in (select id from class where c_name = 'php' or c_name = 'ios ');

Example:

Row subquery

The query result is one row and multiple columns.

Requirement: query the maximum number of records in the student table with sex 1 and age

Select * from stu where (age, sex) = (select max (age), sex from stu where sex = 1 );

Example:

Table subquery

The result is a table, which is equivalent to a data source.

Requirements:

Query the largest age of students in each class.

Example:

Exists subquery

Exists is equivalent to judgment

Requirements:

Query php students