# Aggregate function

Take the `exam_result`table as an example

``````drop table if exists exam_result;
create table exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
``````
``````insert into exam_result (id,name, chinese, math, english)
values
(1,'A', 67, 98, 56),
(2,'B', 87.5, 78, 77),
(3,'C', 88, 98, 90),
(4,'D', 82, 84, 67),
(5,'E', 55.5, 85, 45),
(6,'F', 70, 73, 78.5),
(7,'G', 75, 65, 30),
(8,'H', 78, 32, 98);
``````

## count

Quantity

``````select count(*) from exam_result;
``````

## sum

Sum (not a number, meaningless)

``````select sum(math) from exam_result;
``````

## avg

average value

``````select avg(math) from exam_result;
``````

## max

Max

``````select max(math) from exam_result;
``````

## min

Minimum

``````select min(math) from exam_result;
``````

## group by

Grouping

Take the `emp`table as an example

``````drop table if exists emp;
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
``````
``````insert into emp(name, role, salary) values
('老师A','讲师', 2000.20),
('老师B','讲师', 2000.99),
('老师C','讲师', 2000.11),
('老师D','教授', 3000.5),
('老师E','辅导员', 1000.33),
('老师F','教授', 3000.66),
('老师E','辅导员', 1000.33);
``````

Query the maximum wage, minimum wage and average wage of each role

Divide the same roles into a group and find the maximum, minimum, and average value

``````select role,max(salary),min(salary),avg(salary) from emp group by role;
``````

## having

Filter condition

Aggregate functions cannot be added after where, you need to use having

Query the role whose average salary is lower than 1500 and its average salary

``````select role,avg(salary) from emp group by role having avg(salary)<1500;
``````

# Joint query

The joint query is two tables or more than two tables, and join the query

Because the data we need is not just from one table, but from multiple tables

Cartesian Product

All joint queries are to fetch data from the Cartesian product. When fetching data, certain rules must be met. Next, let's learn about various joint query methods~

Take the following list as an example

``````drop table if exists classes;
create table classes(
id int primary key auto_increment,
name varchar(50),
`desc` varchar(50)
);
``````
``````insert into classes(name, `desc`) values
('计算机系2018级1班', '学习了计算机原理'),
('中文系2018级3班','学习了中国传统文学'),
('自动化2018级5班','学习了机械自动化');
``````
``````drop table if exists student;
create table student(
id int primary key auto_increment,
sn int,
name varchar(30),
qq_mail varchar(30),
classes_id int
);
``````
``````insert into student(sn, name, qq_mail, classes_id) values
('10010','张三','[email protected]',1),
('10012','李四',null,1),
('10034','王五',null,1),
('10023','赵六','[email protected]',1),
('20019','郑一',null,1),
('20021','刘二','[email protected]',2),
('30098','lily',null,2),
('30045','joey','[email protected]',2);
``````
``````drop table if exists course;
create table course(
id int primary key auto_increment,
name varchar(20)
);
``````
``````insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('数学'),('英语');
``````
``````drop table if exists score;
create table score(
id int primary key auto_increment,
score DECIMAL,
student_id int,
course_id int
);
``````
``````insert into score(score, student_id, course_id) values
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
(60, 2, 1),(59.5, 2, 5),
(33, 3, 1),(68, 3, 3),(99, 3, 5),
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
(81, 5, 1),(37, 5, 5),
(56, 6, 2),(43, 6, 4),(79, 6, 6),
(80, 7, 2),(92, 7, 6);
``````

There are 8 students,
20 scores, a
total of 8*20=160

## Internal connection

``````select 字段 from 表1 别名1 inner join 表2 别名2 on 连接条件 and 其他条件
``````
``````select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件
``````
``````select stu.sn,stu.name,sco.score,cou.name as 课程名
from student stu
inner join score sco
on stu.id = sco.student_id
inner join course cou
on sco.course_id = cou.id
and stu.name='赵六';
``````

Note: inner can be omitted and
can be used directly, written in +where

``````select stu.sn,stu.name,sco.score,cou.name as 课程名
from student stu ,score sco,course cou
where stu.id = sco.student_id
and sco.course_id = cou.id
and stu.name='赵六';
``````
1. Query the total score of all students
``````select stu.sn,stu.name,sum(score)
from student stu,score sco
where stu.id = sco.student_id
group by stu.id;
``````

## Outer join

Query each classmate’s grades, personal information, and display if there is no grade

``````select stu.id,stu.name,sco.score
from student stu,score sco
where stu.id=sco.student_id
group by stu.id;
``````
``````select stu.id,stu.name,sco.score
from student stu left join score sco
on stu.id=sco.student_id
group by stu.id;
``````
``````select stu.id,stu.name,sco.score
from score sco right join student stu
on stu.id=sco.student_id
group by stu.id;
``````

## Self-connection

Use one table as two tables

Query all information with higher scores in "Computer Principles" than in "Java"

``````select s2.* from score s1,score s2
where s1.student_id = s2.student_id
and s1.score<s2.score
and s1.course_id = 1
and s2.course_id = 3;
``````

## Subquery

### Single row subquery

Subquery that returns a row of records

Query classmates of "Zheng Yi"

①Check which class he is in first

``````select classes_id from student stu where name = '郑一';
``````

②Check who is in this class

``````select * from student where classes_id = 1;
``````

③Merge

``````select * from student
where classes_id = (
select classes_id
from student stu
where stu.name = '郑一'
);
``````

### Multi-row subquery

Subquery that returns multiple rows

#### (not) in

of Chinese or English ①Check the id of Chinese or English first

``````select id from course
where name = '语文' or name = '英语';
``````

②Check the results corresponding to these two ids

``````select * from score
where course_id in (
select id from course
where name = '语文' or name = '英语'
);
``````

#### (not) exists

As long as this expression is true, return true

``````select * from A where exists (
select 1 from B
where B.id=A.id
);
``````

①First execute an external query and cache the result set. For example, `select * from A`
②Traverse each row of the result set of the external query as R, and substitute it into the subquery as a condition for the query. For example `select 1 from B where B.id=A.id`
, ③If the subquery returns a result, the exists clause returns true. A row of R can be used as an external query result row, otherwise it cannot

## Combined query (union)

union: unite and remove duplication

``````select * from student
where id <= 3
union
select * from student
where name = '张三';
``````

union all: merge (do not de-duplicate)

``````select * from student
where id <= 3
union all
select * from student
where name = '张三';
``````

# note

It is forbidden to query with more than three tables. If three tables are needed, solve it in the back-end code, not in MySQL