MySQL subqueries and grouped queries

Overview

Subquery is an important part of SQL query. It is a means for us to aggregate and judge data between multiple tables, making it easier for us to process complex data. In this section, we will mainly learn about subquery.

Let's prepare the data first. Here we create three tables: class, student, and graduation score tables for the following operations:

drop database if exists `Helenlyn_Class`;
create database `Helenlyn_Class`;

/*Class table*/
DROP TABLE IF EXISTS `classes`;
CREATE TABLE `classes` (
 `classid` int primary key AUTO_INCREMENT comment 'class id',
 `classname` varchar(30) DEFAULT NULL comment 'Class name'
) ENGINE=InnoDB comment 'class table';

insert into `classes`(`classname`)
values ​​('junior high school class 1'),('junior high school class 2'),('junior high school class 3');

/*Student table: Here we assume that both student ID and name are unique*/

DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
 `studentid` int primary key NOT NULL AUTO_INCREMENT comment 'student id',
 `studentname` varchar(20) DEFAULT NULL comment 'Student name',
 `score` DECIMAL(10,2) DEFAULT NULL comment 'Graduation score',
 `classid` int(4) DEFAULT NULL comment 'The class id, from the classid in the classes table'
)ENGINE=InnoDB comment 'student table';
insert into `students`(`studentname`,`score`,`classid`) values
('brand',97.5,1),('helen',96.5,1),('lyn',96,1),('sol',97,1),('weng',100,1),('diny',92.7,1),
('b1',81,2),('b2',82,2),('b3',83,2),('b4',84,2),('b5',85,2),('b6',86,2),
('c1',71,3),('c2',72.5,3),('c3',73,3),('c4',74,3),('c5',75,3),('c6',76,3);


/*Graduation assessment score ranking table*/
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`(
 `scoregrad` varchar(3) primary key comment 'Grade: S, A, B, C, D',
 `downset` int comment 'score rating lower limit',
 `upset` int comment 'Score rating upper limit'
) comment 'Graduation assessment score ranking table';
INSERT INTO `scores` values ​​('S', 91, 100),('A', 81, 90),('B', 71, 80),('C', 61, 70),('D', 51,60);

Subqueries

SQL supports the creation of subqueries, which are queries nested in other queries. In other words, other select statements may appear in a select statement, which we call subqueries or inner queries. The external select statement is called the main query or outer query.

Subquery Classification

According to the query results

1. Single row and single column (scalar subquery): returns the content of a specific column, which can be understood as a single-valued data;

2. Single row and multiple columns (row subquery): returns the contents of multiple columns in a row of data;

3. Multi-row single column (column subquery): returns the content of the same column in multiple rows, which is equivalent to giving an operation range;

4. Multiple rows and multiple columns (table subquery): The result returned by the query is a temporary table;

Distinguish by subquery position

Subquery after select: Only scalar subquery is supported, that is, only a single value of data can be returned.

From-type subquery: The inner query result is used as a temporary table for the outer SQL to query again, so table subqueries are supported.

Where or having subquery: refers to using the result of the inner query as the comparison condition of the outer query, supporting scalar subqueries (single column and single row), column subqueries (single column and multiple rows), and row subqueries (multiple columns and multiple rows).

It is usually used in conjunction with the following methods:

1) IN subquery: The inner query statement returns only one data column, and the value of this data column will be used for comparison by the outer query statement.

2) Any subquery: As long as any comparison condition in the inner subquery is met, a result is returned as the outer query condition.

3) All subqueries: The results returned by the inner subquery must satisfy all inner query conditions at the same time.

4) Comparison operator subquery: The comparison operators that can be used in subqueries include >, >=, <=, <, =, <>

Exists subquery: takes the query result of the outer layer (supports multiple rows and columns) to the inner layer to see whether the inner layer is established. Simply put, the outer layer (that is, the previous statement) will be executed only if the latter returns true, otherwise it will not be executed.

Let’s test them one by one.

Subquery after select

It is located after select and only supports scalar subqueries, that is, it can only return a single value of data. For example, in the student class table above, we can query the number of students in each class as follows:

mysql> select a.classid as class number, a.classname as class name,
(select count(*) from students b where b.classid = a.classid) as number of students from classes a;
+----------+----------+----------+
| Class Number| Class Name| Number of Students|
+----------+----------+----------+
| 1 | Class 1, Grade 9 | 6 |
| 2 | Class 2, Grade 9 | 6 |
| 3 | Class 3, Grade 9 | 6 |
+----------+----------+----------+
3 rows in set

To query the class to which a student brand belongs, you can write:

mysql> select
(select classname from classes a,students b where a.classid = b.classid and b.studentname='brand')
as class;
+----------+
| Class|
+----------+
| Class 1, Grade 9|
+----------+
1 row in set

from after subquery

The inner query result is treated as a temporary table, and the outer SQL is provided for further query, which supports table subqueries. But the subquery must be aliased, otherwise the table cannot be found.

Query the average score of each class:

mysql> select a.classid,avg(a.score) from students a group by a.classid;

+---------+--------------+
| classid | avg(a.score) |
+---------+--------------+
| 1 | 96.616667 |
| 2 | 83.500000 |
| 3 | 73.583333 |
+---------+--------------+
3 rows in set

Query the graduation assessment score ranking table: sort from S to low.

mysql> select * from scores order by upset desc;

+-----------+---------+-------+
| scoregrad | downset | upset |
+-----------+---------+-------+
| S | 91 | 100 |
| A | 81 | 90 |
| B | 71 | 80 |
| C | 61 | 70 |
| D | 51 | 60 |
+-----------+---------+-------+
5 rows in set

If you want to find out the average scores of each class based on the results of the two queries, you can use the subquery after from. The code is as follows:

select a.classid as class id, a.avgscore average graduation score, b.scoregrad score rating from
(select classid,avg(score) as avgscore from students group by classid) as a,
scores b where a.avgscore between b.downset and b.upset;

+--------+--------------+----------+
| class id | average graduation score | score rating |
+--------+--------------+----------+
| 1 | 96.616667 | S |
| 2 | 83.500000 | A |
| 3 | 73.583333 | B |
+--------+--------------+----------+
3 rows in set

For subtable queries, an alias must be provided, otherwise it will prompt: Every derived table must have its own alias. You can try it.

Where and Having Subqueries

According to what we mentioned above, after where or having, you can use three methods: scalar subquery (single row and single column subquery); column subquery (single column and multiple rows subquery); row subquery (multiple rows and multiple columns);

He has the following common characteristics:

1. Generally, subqueries are enclosed in parentheses.

2. Subqueries are generally placed on the right side of the conditions.

3. Scalar subquery, generally used with single-line operators, multi-line operators >, <, >=, <=, =, <>

4. Column subquery, usually used with multi-row operators

5. Use with in, not in, all, and any. in refers to any one in the list. any compares any one in the list. If score>any(60,70,80), then score>60. all compares all in the list. If score>(60,70,80), score must be>80.

Single scalar subquery application

That is, where or having is followed by only a scalar query, for example, to query students who have better scores than diny (92.7 points):

mysql> select * from students a where a.score >(select b.score from students b where b.studentname='diny');
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 5 | weng | 100 | 1 |
+-----------+-------------+-------+---------+
5 rows in set

Multiple scalar subquery applications

Where or having is followed by only a scalar query, for example, to query students who have worse scores than diny (92.7 points) and whose classes are not the same as diny's:

mysql> select * from students a where
a.score <(select b.score from students b where b.studentname='diny')
and a.classid <> (select b.classid from students b where b.studentname='diny') ;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+-------------+-------+---------+
12 rows in set

Subquery + grouping function

Get the average scores of three classes respectively, and filter out the class information with scores lower than the average score of the whole grade, using the having expression

mysql> select a.classid,avg(a.score) as avgscore from students a group by a.classid
having avgscore < (select avg(score) from students);
+---------+-----------+
| classid | avgscore |
+---------+-----------+
| 2 | 83.500000 |
| 3 | 73.583333 |
+---------+-----------+
2 rows in set

Subquery description

Column subqueries need to be used with multi-row operators: in (not in), any/some, all. Using the distinct keyword to remove duplicates can improve execution efficiency.

Example subquery + in: all students who are not in class three

mysql> select * from students a where a.classid in (select distinct b.classid from classes b where b.classid <3);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 5 | weng | 100 | 1 |
| 6 | diny | 92.7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+-------------+-------+---------+
12 rows in set

Example subquery + any: any student who is not in class 3

mysql> select * from students a where a.classid = any (select distinct b.classid from classes b where b.classid <3);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 5 | weng | 100 | 1 |
| 6 | diny | 92.7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+-------------+-------+---------+
12 rows in set

Subquery + all: equivalent to not in

mysql> select * from students a where a.classid <> all (select distinct b.classid from classes b where b.classid <3);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+-------------+-------+---------+
6 rows in set

Row subquery description

Query the student with the smallest student number but the best grades:

mysql> select * from students a where (a.studentid, a.score) in (select max(studentid),min(score) from students);
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 19 | lala | 51 | 0 |
+-----------+-------------+-------+---------+
1 row in set

exists subquery

Also called a correlated subquery, it takes the query result of the outer layer (supports multiple rows and columns) and brings it to the inner layer to see whether the inner layer is established. Simply put, the outer layer (that is, the previous statement) will be executed only if the latter returns true, otherwise it will not be executed.

1. Exists query result: 1 or 0, 1 is true, 0 is false. The result of the exists query is used to determine whether there is a value in the result set of the subquery.

2. The exists subquery can generally be replaced by in, so exists is rarely used.

3. Different from the previous query methods, the main query is executed first, and then the results of the subquery are used to filter according to the results of the main query. Because the subquery contains the fields used in the main query, it is also called a correlated subquery.

Example: query the class names of all students

mysql> select classname from classes a where exists(select 1 from students b where b.classid = a.classid);

+-----------+
|classname|
+-----------+
| Class 1, Grade 9|
| Class 2, Grade 9|
| Class 3, Grade 9|
+-----------+
3 rows in set

Use in instead (it looks simpler):

mysql> select classname from classes a where a.classid in(select classid from students);

+-----------+
|classname|
+-----------+
| Class 1, Grade 9|
| Class 2, Grade 9|
| Class 3, Grade 9|
+-----------+
3 rows in set

Combined query

Most SQL queries consist of a single SELECT statement that returns data from one or more tables. MySQL also allows you to execute multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are often called union or compound queries.

Multiple returns for a single table

Combine results from different queries

 select cname1,cname2 from tname where condition1
 union
 select cname1,cname2 from tname where condition2

Multiple tables return the same structure

Combine fields with the same quantity structure

 select t1_cname1,t1_cname2 from tname1 where condition
 union
 select t2_cname1,t_2cname2 from tname2 where condition

I won’t go into details here, there is a special chapter about this later.

Summarize

You can learn from two aspects: the return type of the query and the position of the subquery in the statement.

Pay attention to the use of in, any, some, and all

Whether it is comparison, query or count, null values ​​in the field will always cause misunderstanding. It is recommended that the field is not empty when creating the table, or a default value is provided.

The above is the details of MySQL subquery and group query. For more information about MySQL query, please pay attention to other related articles on 123WORDPRESS.COM!