Advanced MySQL

1, Aggregate query

The functions and usage used in aggregate query are very similar to that of Excel. If you play NB in Excel, you can basically combine query, which is very easy. 😁😁🤗🤗

Aggregate query is related to rows. Merging query results according to row dimensions means merging multiple rows.

1.1 aggregate function

Aggregate query also provides many aggregate functions, which can be understood as "library functions" provided by SQL.

function	explain
COUNT([DISTINCT] expr)	Returns the number of queried data
SUM([DISTINCT] expr)	Returns the sum of the queried data. It is not a number. It is meaningless
AVG([DISTINCT] expr)	Returns the average value of the queried data. It is not a number and meaningless
MAX([DISTINCT] expr)	Returns the maximum value of the queried data. It is not a number. It is meaningless
MIN([DISTINCT] expr)	Returns the minimum value of the queried data. It is not a number. It is meaningless

• COUNT

-- Query how many rows the current table has
select count(*) from indicate;

-- If the attribute is null，Then this attribute does not count
select count(attribute) from indicate;

• SUM

-- Calculate the total score of 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

-- return > 70 The lowest score in mathematics above
SELECT MIN(math) FROM exam_result WHERE math > 70;

1.2 group by

The above aggregation function is to combine all rows. group by is used for grouping aggregation, and each group is calculated or counted by aggregation function.

Case:

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

• Group query by group by
• Group by role

select role from teachers group by role;

Operation results:

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

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

Operation results:

1.3Having

For the data after group by grouping, you need to use Having to filter the conditions. where is the condition filtering before grouping.

📜 Case list:

Query the maximum salary of each role > 4500

select role,max(salary) from teachers group by role having max(salary) >4500;

Operation results:

Aggregate query is relatively easy to understand. If you are proficient in Excel, your aggregate query is no worse. Mainly pay attention to the group by in it.

2, Joint query

2.1 Cartesian product

Joint query is also called multi table query. Multi table query takes Cartesian product of data from multiple tables. It means to arrange and combine the results of two tables. Cartesian product is also a core concept in joint query.

What is Cartesian product???

Cartesian product is to list all possible results produced by two tables, and a new table of formal parameters.

• The number of columns in this table is the sum of the number of columns in the two tables
• The number of rows in this table is the product of the two tables

Cartesian product will list many possibilities, so there are unscientific data in it. We need to filter out these unscientific data, and we will add connection conditions to filter these redundant data and leave these reasonable data.

Those are reasonable data???

The so-called reasonable data means that there will be corresponding and equal data in two tables

When you know the Cartesian product, you can practice it

Suppose I create a database with classes, course s, score s and student s;

• There are a lot of relationships between student s and classes
• There is a many to many relationship between student and course. When there is a many to many relationship, there will be an intermediate table score to connect the relationship between the two table entities

student table:

2.2 internal connection

📜 Suppose we inquire about the grades of "Bai Suzhen"

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;

First demonstrate with the second syntax:

select * from student ,score; -- Cartesian product query all situations 

select * from student ,score where student.id =score.student_id;-- Set conditions to filter unreasonable data

The information of all the students has been listed. Just add a condition name = "Bai Suzhen" below

select * from student ,score where student.id =score.student_id and name='Bai Suzhen';

In this way, we found out Bai Suzhen's grades. In order to simplify, we can add some conditions

select student.name, score.course_id,score.score from student ,score where student.id =score.student_id and name='Bai Suzhen';

Of course, if you are proficient, you can also do it in one step:

select student.name, score.score from student , score  where
student.id=score.student_id and student.name='Bai Suzhen';

However, if you write this, it will look complex, difficult to read, difficult to understand, and inefficient. Because the essence of linked list query is to perform Cartesian product operation. If it is four or five tables, the calculated data will be very large and waste overhead. Therefore, in practical work, joint query should be used carefully because the amount of data may be large, But in the interview / written examination, it is often tested,

Another syntax:

select * from student inner join score on student.id=score.student_id;
//Both Cartesian product and conditional judgment are carried out

The above two grammars are OK. Both can represent internal connections, but sometimes there will be left external connections and right external connections. This needs to be in the form of join on.

2.3 external connection

External connections are divided into left external connections and right external connections. They also perform Cartesian product operations on two tables. When there is no corresponding relationship between some order data in the two tables, their representation will be different.

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 from Table name 1 right join Table name 2 on Connection conditions;

2.3 self connection

Self connection, comparing yourself with yourself in a table, is also the first to carry out Cartesian product, set conditions, let him use strange skills, and convert rows into columns. Compare columns to columns.

📜 Case: display all performance information with "computer principle" score higher than "Java" score,

select * from score s1,score s2;-- Cartesian product

select * from score s1,score s2 where s1.student_id=s2.student_id;
-- Filter by criteria to make it a comparison between columns

select * from score s1,score s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=3; -- Streamline query columns

select * from score s1,score s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=3 and s1.score <s2.score; -- Get the final result

 select s1.student_id, s1.score as java, s2.score as 'Computer principle' from score s1,score s2 where s1.student_id=s2.student_id and s1.course_id=1 and s2.course_id=3 and s1.score <s2.score; -- More streamlined

2.4 sub query

Subquery refers to the select statement embedded in other SQL statements, also known as nested query. A subquery is a subquery that combines multiple SQL statements,

📜 Case list:

Query classmates with "so and so"

Under normal conditions:

1. First query the class id of "so and so"

2. Find the students with the same class id again according to the found class id

 select classes_id from student where name='So and so'; -- Hypothetical query result class id Is 1
 
 select student.name from student where classes_id=1;

But the subquery is completed by one statement, so we can embed the first statement into the second statement.

select student.name from student where classes_id=(select classes_id from student where name='So and so' );

This is the subquery.

IN subquery is used for multi line subquery:

📜 Case list:

Query the score information of "Chinese" or "English" courses

Under normal conditions:

1. Query the course id.

2. Query the score details in the score table according to the course id.

select id from course where name='language' or name ='english'; -- Hypothetical course id Yes (4, 6)
select * from score where course_id in (4,6);

Subquery Code:

select * from score where course_id in (select id from course where name='language' or name ='english');

How good subqueries are performed by using exists in the subquery:

select * from score where exists (select score.course_id from course where (name='language' or name ='english') and course.id=score.course_id);

It is not recommended to use. The code is complex, difficult to understand and inefficient, but it should be mastered and tested in the interview.

2.5 consolidated query

Merge multiple result sets into one. In order to merge the execution results of multiple select ions, you can use the set operators UNION, UNION all. When using UNION and UNION all, the fields in the result set of the previous and subsequent queries need to be consistent

📜 Case:

Query courses with id less than 3 or name "English"

Write without merging query:

select * from course where id <3 or name='english';

Write with merge query:

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

The difference between union and union all

union automatically de duplicates lines. union all cannot de duplicate lines.

Here, we focus on mastering the inner connection and Cartesian product in the joint query, because this is a frequently tested question in the written examination and interview. Because SQL is relatively simple, we won't ask about SQL statements, but about the bottom of the database. The next post is about some principles of the bottom of the database, as well as indexes and transactions.

Iron juice, I think the author's writing is good, you can praise it ❤🧡💛💚💙💜🤎🖤🤍💟， Collection and attention, your support is the biggest driving force for me to write a blog