〖Python database development practice – MySQL chapter ⑱〗 – application of group query

Hits: 0

🎉 The faint thunder and the hazy sky. 🎉
🎉 But I hope the wind and rain come, I can keep you here. 🎉

Foreword :
✌ About the author: Husky thirsting for power✌, you can call me 🐶 Husky 🐶 , a blogger dedicated to TFS – full stack empowerment✌
🏆 CSDN blog expert certification, Rising Star Project Season 3 full stack track top_1 , Huawei Cloud Sharing experts, Alibaba Cloud expert bloggers 🏆
📫 If there are mistakes in the knowledge points of the article, please correct me! Learn and progress together with everyone👀
💬 Life motto: Being superior to others is not noble. The real nobility should be superior to your past self. 💬
🔥 If you feel that the blogger’s article is not bad, please 👍 follow, like, and bookmark to support the blogger 👍

Column Series (click to unlock) Learning Route (click to unlock) knowledge orientation
🔥Python Full Stack Whitepaper🔥 Zero-based introductory articles It’s easy to get started in an easy-to-understand way, and you will completely fall in love with the charm of Python.
Advanced Grammar It mainly focuses on multi-threaded programming, regular expression learning, and project exercises that are close to actual combat.
Office Automation Realize the automatic operation of daily office software, save time and improve office efficiency.
Automated testing in practice From the perspective of actual combat, one step ahead of others, rapid transformation of test and development engineers.
Database development in practice Master the knowledge of relational and non-relational databases, and improve the ability of actual database development.
Introduction and actual combat of reptiles updating
Data Analysis updating
Front-end entry + flask full stack updating
django+vue full stack updating
Outreach – Introduction to Artificial Intelligence updating
The road to cybersecurity Stepping on the pit Record the pits encountered in the process of learning and drills, so that the later ones can catch up
Internet Security Knowledge Literacy Three days of fishing without a deep understanding of the principles will only make you a script kid.
vulhub shooting range vulnerability recurrence It makes vulnerability reproduction easy, and allows security researchers to focus more on the vulnerability principle itself.
Shell programming articles It does not involve the Linux foundation, and the final case will be biased towards the direction of security reinforcement. [to be completed]
WEB Vulnerability Attack and Defense The update will stop on September 3, 2021, and move to safe communities and small secret circles such as the Prophet community
Penetration Tool Use Collection The update will stop on September 3, 2021, and move to safe communities and small secret circles such as the Prophet community
little bit engineer Test Artifact – Charles Software test data packet capture and analysis artifact
Test Artifact – Fiddler Yiwen can learn to fiddle, and it is rare to learn how to eat upside down!
Test Artifact – Jmeter It is not only a performance testing artifact, but also can be used to build a lightweight interface automated testing framework.
RobotFrameWork An automated testing tool implemented by Python, this chapter only introduces the UI automation part.
Java implements UI automation The document was written in 2016, and the UI automation implemented by Java still has reference significance.
MonkeyRunner There are not many application scenarios of this tool at present, the document has been deleted, and it is kept for the sake of good-looking layout.

Article directory

This chapter starts to learn about grouping queries. In the previous chapter, we learned about [aggregate functions] . The default statistics are data within the scope of the entire table. With WHERE, the scope of statistics can be narrowed. But this does not meet our requirements. For example, what is the average base salary of each department according to the previous data table? Such records need to be grouped by department number. According to the situation of the group, the maximum value, minimum value, average value, etc. in the group are counted. In this way, it can meet the needs of “querying the average basic salary of each department” just mentioned. In addition, “group query” is a very important syntax in SQL, and everyone must master it well.

🐳 Why group

  • Due to such limitations of aggregate functions, the concept of grouping arises, so there is a syntax for grouping.
  • The syntax for grouping is achieved through “GROUP BY”.

Group statement demonstration case: (calculate the average base salary of each department)

SELECT deptno, AVG(sal) FROM t_emp GROUP BY deptno;</p> <p>-- Use the GROUP BY clause to group deptno, and use the AVG() aggregate function to calculate the average monthly salary of each deptno (department)

SELECT deptno, ROUND(AVG(sal))
FROM t_emp
GROUP BY deptno;

🐳 Group by level

  • Sometimes it is not enough to have only large groups. It is also necessary to divide small groups within the large group, and then perform statistical calculation, so there is a level-by-level grouping.
  • What is progressive grouping? The MySQL database supports multi-column grouping conditions. When executing, it is executed in sequence according to multiple columns, which is a hierarchical grouping.
  • An example is as follows: (Query the number of employees and average salary for each position in each department.)

SELECT deptno, job, COUNT(*), AVG(sal) FROM t_emp GROUP BY deptno, job ORDER BY deptno;</p> <p>-- First, the employees should be grouped according to the department, and in the department, they should be grouped according to the title; that is "GROUP BY deptno, job" -- Then use the AVG of the aggregate function to calculate the average monthly salary; that is "SELECT deptno, job" , COUNT(*), AVG(sal)" -- finally sort by deptno(department number), use ORDER BY for ascending sort. {{EJS0}} </p> </blockquote> <h2>🐬 Do the summary calculation again for the grouped result set</h2> <blockquote> <p>
SELECT deptno, COUNT(*), AVG(sal), MAX(sal), MIN(sal)
FROM t_emp
GROUP BY deptno

— The “WITH ROLLUP” clause here is the result set grouped by “deptno”, and the summary calculation is performed again
</p> <hr /> </blockquote> <h1>🐳 GROUP_CONCAT function</h1> <blockquote> <ul> <li>If you want to match, you need to convert multiple records of non-grouping fields into one record, and MySQL provides the <code>GROUP_CONCAT</code>function to combine multiple records in non-grouping fields in a grouping query into one record.</li> </ul> <hr /> <p>
SELECT deptno, GROUP_CONCAT(ename), AVG(sal),COUNT(*)
FROM t_emp
WHERE sal >= 2000
GROUP BY deptno;

— Query employee table, filter condition is monthly salary greater than or equal to 2000, group by “deptno”
— “ename” field is not grouped, but we use “GROUP_CONCAT” function to convert multiple returned records of “ename” into one record

🐳 Execution order of GROUP BY clauses

All the clauses we have learned so far are executed in the following order:


You may also like...

Leave a Reply

Your email address will not be published.