4. Query

4.1 aggregate query

4.1. 1 aggregate function

Common operations such as total statistics and average calculation can be realized by using aggregation functions. Common aggregation functions include:

function	explain
count	Returns the number of queried data
sum	Returns the sum of the queried data. It is not a number. It is meaningless
avg	Returns the average value of the queried data. It is not a number and meaningless
max	Returns the maximum value of the queried data. It is not a number. It is meaningless
min	Returns the minimum value of the queried data. It is not a number. It is meaningless

Case:

• count

-- Count the total number of students in the class
select count(*) from student;
+----------+
| count(*) |
+----------+
|        4 |
+----------+

select count(0) from student;
+----------+
| count(0) |
+----------+
|        4 |
+----------+

-- Statistics collected by the class qq_mail How many, qq_mail by null The data will not be included in the results
select * from student;
+----+------+--------+---------+------------+
| id | sn   | name   | qq_mail | classes_id |
+----+------+--------+---------+------------+
|  1 |    1 | Zhang San   | NULL    |          1 |
|  3 |    2 | Li Si   | NULL    |          1 |
|  4 |    3 | Wang Wu   | NULL    |          1 |
|  5 |    4 | Zhao Liu   | NULL    |          2 |
+----+------+--------+---------+------------+

select count(qq_mail) from student;
+----------------+
| count(qq_mail) |
+----------------+
|              0 |
+----------------+

• sum

-- Total score of Statistical Mathematics
select sum(math) from exam_result;

-- fail,<60 Total score of, no result, return null
select sum(math) from exam_result where math<60;

• avg

-- Statistical average total score
select avg(chinese+math+english) Average total score from exam_result;

• max

-- Return to the highest score in English
select max(english) from exam_result;

• min

-- Returns the lowest math score greater than 70
select min(math) from exam_result where math>70;

4.1.2 group by sentence

Use the group by clause in select to specify columns for grouping queries.
Need to meet: when using group by for grouping query, the field specified in select must be "grouping by field". If other fields want to appear in select, they must be included in the aggregation function.

select column1,sum(column2),... from table group by column1,column3;

Case:

• Prepare test table and data: employee table, including id (primary key), name (name), role (role), salary (salary)

 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
 ('Zhao Si','waiter',1000.20),
 ('Ma Liu','Game sparring',2000.99),
 ('Sun WuKong','Game character',999.11),
 ('Pig Wuneng','Game character',333.5),
 ('Sand monk','Game character',700.33),
 ('Wang Wu','chairman',12000.66);

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

select role,max(salary),min(salary),avg(salary) from emp group by role;
+--------------+-------------+-------------+--------------+
| role         | max(salary) | min(salary) | avg(salary)  |
+--------------+-------------+-------------+--------------+
| waiter       |     1000.20 |     1000.20 |  1000.200000 |
| Game character     |      999.11 |      333.50 |   677.646667 |
| Game sparring     |     2000.99 |     2000.99 |  2000.990000 |
| chairman       |    12000.66 |    12000.66 | 12000.660000 |
+--------------+-------------+-------------+--------------+

4.1.3 having

After grouping with the group by clause, if you need to filter the grouping results conditionally, you can't use the where statement, but you need to use having

• Displays the role whose average salary is less than 1500 and its average salary

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;
+--------------+-------------+-------------+-------------+
| role         | max(salary) | min(salary) | avg(salary) |
+--------------+-------------+-------------+-------------+
| waiter       |     1000.20 |     1000.20 | 1000.200000 |
| Game character     |      999.11 |      333.50 |  677.646667 |
+--------------+-------------+-------------+-------------+

4.2 joint query

  in actual development, data often comes from different tables, so multi table joint query is required. Multi table query is to take the Cartesian product of the data of multiple tables:

Note: associated queries can use aliases for associated tables. Syntax: table name [as] alias. If the table uses an alias, the query field also needs to be the alias of the table.
Table alias + field alias (almost all)

Initialize test data:

create database java43 character set utf8mb4;
 
 use java43;
 
 drop table if exists classes;
 
 create table classes (
 id int primary key auto_increment,
 name varchar(20),
 `desc` varchar(100)
);

insert into classes(name,`desc`) values
('Class 1, grade 2019, computer department','Learned the principle of computer C and Java Language, data structure and algorithm'),
('Class 3, grade 2019, Chinese Department','I studied Chinese traditional literature'),
('Class 5, automation level 2019','Learned the mechanical principle');

 drop table if exists student;
 create table student (
 id int primary key auto_increment,
 sn int unique,
 name varchar(20) default 'unknown',
 qq_mail varchar(20),
 classes_id int,
 foreign key (classes_id) references classes(id)
);
 
 -- id There are 5 and 3 students in classes 1 and 2, respectively
 
 insert into student(sn,name,qq_mail,classes_id) values
 ('09982','Black Whirlwind Li Kui','xuanfeng@qq.com',1),
 ('00835','The Grapes ',null,1),
 ('00391','Bai Suzhen',null,1),
 ('00031','Xu Xian','xuxian@qq.com',1),
 ('00054','I just want to graduate',null,1),
 ('51234','Speak in a normal way','say@qq.com',2),
 ('83232','tellme',null,2),
 ('09527','Learn Chinese','chinese@qq.com',2);
 
 
 -- Create Curriculum
drop table if exists course;
create table course (
id int primary key auto_increment,
name varchar(20)
);
 
 
 insert into course(name) values
 ('java'),
 ('Chinese traditional culture'),
 ('Computer principle'),
 ('language'),
 ('Advanced mathematics'),
 ('English');
 
 
 drop table if exists score;
 create table score (
 id int primary key auto_increment,
 score decimal(3,1),
 student_id int,
 course_id int,
 foreign key (student_id) references student(id),
 foreign key (course_id) references course(id)
 );
 
 insert into score(score,student_id,course_id) values
 -- Black Whirlwind Li Kui
 (70.5,1,1),( 98.5,1 ,3 ),(33 ,1 ,5 ),(98 ,1 ,6 ),
 -- The Grapes 
 ( 60,2 ,1 ),(59.5 ,2 ,5 ),
 -- Bai Suzhen
 (33 ,3 ,1 ),(68 ,3 ,3 ),( 99,3 ,5 ),
 -- Xu Xian
 (67,4 ,1 ),( 23,4 ,3 ),( 56, 4,5 ),(72 ,4 ,6 ),
 -- I just want to graduate
 ( 81, 5,1 ),(37 ,5 ,5 ),
 -- Speak in a normal way
 ( 56,6 ,2 ),(43 ,6 ,4 ),(79 ,6 ,6 ),
 -- tellme
 (80 ,7 ,2 ),( 92, 7,6 );

4.2. 1 internal connection

Syntax:

select field from Table 1 alias 1 [inner] join Table 2 alias 2 on Connection conditions and Other conditions;
select field from Table 1 alias 1,Table 2 alias 2  where Connection conditions and Other conditions;

• Cartesian product query of class table and student table: students who are not in a class are also associated with display (error and redundant data are generated)

select c.id c_id,c.name c_name,s.id s_id,s.name s_name,s.classes_id from classes c,student s;

• You need to use association conditions to connect two tables

select c.id c_id,c.name c_name,s.id s_id,s.name s_name,s.classes_id from classes c,student s where c.id=s.classes_id;

select c.id c_id,c.name c_name, s.id s_id,s.name s_name,s.classes_id from classes c join student s on c.id=s.classes_id;

Case:
(1) Inquire about Xu Xian's grades

select sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='Xu Xian';

-- perhaps
select sco.score from student stu,score sco where stu.id=sco.student_id and stu.name='Xu Xian';

(2) Query the total scores of all students and their personal information:

-- The score sheet has a many to one relationship with the student sheet. The query of the total score is based on the students in the score sheet id To group
select 
stu.sn,
stu.name,
stu.qq_mail,
sum(sco.score)
from 
student stu
join score sco on stu.id=sco.student_id
group by 
sco.student_id;

+-------+-----------------+-----------------+----------------+
| sn    | name            | qq_mail         | sum(sco.score) |
+-------+-----------------+-----------------+----------------+
|  9982 | Black Whirlwind Li Kui      | xuanfeng@qq.com |          300.0 |
|   835 | The Grapes         | NULL            |          119.5 |
|   391 | Bai Suzhen          | NULL            |          200.0 |
|    31 | Xu Xian            | xuxian@qq.com   |          218.0 |
|    54 | I just want to graduate        | NULL            |          118.0 |
| 51234 | Speak in a normal way        | say@qq.com      |          178.0 |
| 83232 | tellme          | NULL            |          172.0 |
+-------+-----------------+-----------------+----------------+
7 rows in set (0.00 sec)

(3) Query the scores and personal information of all students:

-- The inquired students are all students with achievements, but the students of "learning Chinese" are not displayed
select * from student stu join score sco on stu.id=sco.student_id;

-- Associated query of three tables: student table, grade table and course table
select 
stu.id,
stu.sn,
stu.name,
stu.qq_mail,
sco.score,
sco.course_id,
cou.name
from 
student stu
join score sco on stu.id=sco.student_id
join course cou on sco.course_id=cou.id
order by 
stu.id

+----+-------+-----------------+-----------------+-------+-----------+--------------------+
| id | sn    | name            | qq_mail         | score | course_id | name               |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+
|  1 |  9982 | Black Whirlwind Li Kui      | xuanfeng@qq.com |  70.5 |         1 | java               |
|  1 |  9982 | Black Whirlwind Li Kui      | xuanfeng@qq.com |  98.5 |         3 | Computer principle         |
|  1 |  9982 | Black Whirlwind Li Kui      | xuanfeng@qq.com |  33.0 |         5 | Advanced mathematics           |
|  1 |  9982 | Black Whirlwind Li Kui      | xuanfeng@qq.com |  98.0 |         6 | English               |
|  2 |   835 | The Grapes         | NULL            |  60.0 |         1 | java               |
|  2 |   835 | The Grapes         | NULL            |  59.5 |         5 | Advanced mathematics           |
|  3 |   391 | Bai Suzhen          | NULL            |  33.0 |         1 | java               |
|  3 |   391 | Bai Suzhen          | NULL            |  68.0 |         3 | Computer principle         |
|  3 |   391 | Bai Suzhen          | NULL            |  99.0 |         5 | Advanced mathematics           |
|  4 |    31 | Xu Xian            | xuxian@qq.com   |  67.0 |         1 | java               |
|  4 |    31 | Xu Xian            | xuxian@qq.com   |  23.0 |         3 | Computer principle         |
|  4 |    31 | Xu Xian            | xuxian@qq.com   |  56.0 |         5 | Advanced mathematics           |
|  4 |    31 | Xu Xian            | xuxian@qq.com   |  72.0 |         6 | English               |
|  5 |    54 | I just want to graduate        | NULL            |  81.0 |         1 | java               |
|  5 |    54 | I just want to graduate        | NULL            |  37.0 |         5 | Advanced mathematics           |
|  6 | 51234 | Speak in a normal way        | say@qq.com      |  56.0 |         2 | Chinese traditional culture       |
|  6 | 51234 | Speak in a normal way        | say@qq.com      |  43.0 |         4 | language               |
|  6 | 51234 | Speak in a normal way        | say@qq.com      |  79.0 |         6 | English               |
|  7 | 83232 | tellme          | NULL            |  80.0 |         2 | Chinese traditional culture       |
|  7 | 83232 | tellme          | NULL            |  92.0 |         6 | English               |
+----+-------+-----------------+-----------------+-------+-----------+--------------------+

4.2. 2 external connection

The external connection is divided into left external connection and right external connection. If the table on the left is completely displayed in the joint query, we say it is a left outer connection; The table on the right completely shows that it is the right outer connection.
Syntax:

-- Left outer connection, fully shown in Table 1
select Field name from Table name 1 left join Table name 2 on Connection conditions;

-- Right outer connection, fully shown in Table 2
select Field name from Table name 1 right join Table name 2 on Connection conditions;

Case:
Query the scores of all students and their personal information. If the student has no scores, it also needs to be displayed

-- "Learn Chinese"The students have no test results, which are also displayed
select * from student stu left join score sco on stu.id=sco.student_id;

-- The corresponding right outer connection is:
select * from score sco right join student stu on stu.id=sco.student_id;

-- Associated query of three tables: student table, grade table and course table
select 
stu.id,
stu.sn,
stu.name,
stu.qq_mail,
sco.score,
sco.course_id,
cou.name
from 
student stu
left join score sco on stu.id=sco.student_id
left join course cou on sco.course_id=cou.id
order by 
stu.id;

4.2. 3 self connection

Self join refers to joining self queries in the same table.
Case:
Display all performance information with "computer principle" scores higher than Java scores

-- First query "computer principle" and“ Java"Curricular id

select id ,name from course where name='java' or  name='Computer principle';

-- Then query the score table,"Computer principle"Achievement ratio“ Java"High score information
select 
 s1.*
 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;
 +----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 21 |  70.5 |          1 |         1 |
| 27 |  33.0 |          3 |         1 |
+----+-------+------------+-----------+

-- You can also use join on Statement to perform a self join query
 select 
 s1.*
 from
 score s1
 join score s2 on s1.student_id=s2.student_id
 and s1.score<s2.score
 and s1.course_id=1
 and s2.course_id=3;

The above query only displays the score information and is executed step by step. To display student and grade information in one sentence:

 select
 stu.*,
 s1.score Java,
 s2.score Computer principle
 from 
 score s1
 join score s2 on s1.student_id=s2.student_id
 join student stu on s1.student_id=stu.id
 join course c1 on s1.course_id=c1.id
 join course c2 on s2.course_id=c2.id
 and s1.score<s2.score
 and c1.name='java'
 and c2.name='Computer principle'; 
+----+------+-----------------+-----------------+------------+------+-----------------+
| id | sn   | name            | qq_mail         | classes_id | Java | Computer principle      |
+----+------+-----------------+-----------------+------------+------+-----------------+
|  1 | 9982 | Black Whirlwind Li Kui      | xuanfeng@qq.com |          1 | 70.5 |            98.5 |
|  3 |  391 | Bai Suzhen          | NULL            |          1 | 33.0 |            68.0 |
+----+------+-----------------+-----------------+------------+------+-----------------+

4.2. 4 sub query

Subquery refers to the select statement embedded in other SQL statements, also known as nested query

• Single line subquery: returns the subquery of one line of records
  Query students in the same class as "just want to graduate":

select * from student where classes_id =(select classes_id from student where name='I just want to graduate');
+----+------+-----------------+-----------------+------------+
| id | sn   | name            | qq_mail         | classes_id |
+----+------+-----------------+-----------------+------------+
|  1 | 9982 | Black Whirlwind Li Kui      | xuanfeng@qq.com |          1 |
|  2 |  835 | The Grapes         | NULL            |          1 |
|  3 |  391 | Bai Suzhen          | NULL            |          1 |
|  4 |   31 | Xu Xian            | xuxian@qq.com   |          1 |
|  5 |   54 | I just want to graduate        | NULL            |          1 |
+----+------+-----------------+-----------------+------------+

• Multi line subquery: returns the subquery of multi line records
  Case: query the score information of "Chinese" or "English" courses
  1.[NOT] IN keyword:

-- use in
select * from score where course_id in(select id from course where name='language' or name='English');
+----+-------+------------+-----------+
| id | score | student_id | course_id |
+----+-------+------------+-----------+
| 37 |  43.0 |          6 |         4 |
| 24 |  98.0 |          1 |         6 |
| 33 |  72.0 |          4 |         6 |
| 38 |  79.0 |          6 |         6 |
| 40 |  92.0 |          7 |         6 |
+----+-------+------------+-----------+

-- use not in
select * from score where course_id not in(select id from course where name!='language' or name!='English');

Multiple columns can contain:

 
 -- Insert duplicate scores: score,student_id,course_id Column repetition
 insert into score (score,student_id,course_id) values
 -- Black Whirlwind Li Kui
 (70.5,1,1),(98.5,1,3),
 -- The Grapes 
 (60,2,1);
 
 -- Query duplicate scores
 select
 *
 from 
 score
 where
 (score,student_id,course_id) in (select score,student_id,course_id 
 from score 
 group by score,student_id,course_id 
 having count(0)>1);

1. [not] exists keyword:

-- use exists
select * from score sco where exists (select sco.id from course cou where (name='language' or name='English') and cou.id=sco.course_id);

-- use not exists
select * from score sco where not exists (select sco.id from course cou where (name !='language' or name !='English') and cou.id=sco.course_id);

• Use subqueries in the from clause: subquery statements appear in the from clause. Here we need to use the technique of data query to use a sub query as a temporary table.
  Query all score information higher than the average score of "Class 3, grade 2019, Chinese Department":

-- Obtain the average score of "Class 3, grade 2019, Chinese Department" and regard it as a temporary table
select 
 avg(sco.score)score
 from 
 score sco
 join student stu on sco.student_id =stu.id
 join classes cls on stu.classes_id =cls.id
 where
 cls.name = 'Class 3, grade 2019, Chinese Department';

Query the scores higher than the average score of the above temporary tables in the score table:

select
 * 
 from
 score sco,
 (
 select
 avg(sco.score) score
 from 
 score sco
 join student stu on sco.student_id = stu.id
 join classes cls on stu.classes_id = cls.id
 where
 cls.name = 'Class 3, grade 2019, Chinese Department'
 ) tmp
 where
 sco.score>tmp.score;
 

4.2. 5. Merge query

In practical application, in order to merge the execution results of multiple select ions, the set operators union and union all can be used When using Union and union all, the fields in the result set of the previous and subsequent queries need to be consistent.

• union
  This operator is used to obtain the union of two result sets. When this operator is used, duplicate rows in the result set are automatically removed.
  Case: query courses with id less than 3 or name "English":

select * from course where id<3
union 
select * from course where name='English';

-- Or use or To achieve
select * from course where id<3 or name='English';

• union all
  This operator is used to obtain the union of two result sets. When this operator is used, duplicate rows in the result set are not removed.
  Case: query the course with ID < 3 or name "java"

select * from course where id<3
union all
select * from course where name='English';