Tag: article of public account

preparation

In order for the story to run smoothly, we need to create a table:

CREATE TABLE student_score (
  number INT(11) NOT NULL,
  name VARCHAR(30) NOT NULL,
  subject VARCHAR(30) NOT NULL,
  score TINYINT(4) DEFAULT NULL,
  PRIMARY KEY (number,subject)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

The student_score table is used to store student scores. We fill this bar with some data and it looks like this:

mysql> SELECT * FROM student_score; +----------+-----------+-----------------------------+-------+ | number | name | subject | score | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | 20180101 | Du Ziteng | sow postpartum care | 78 | | 20180101 | Du Ziteng | Theory of saddam hussein's war | 88 | | 20180102 | Du Qiyan | sow postpartum care | 100 | | 20180102 | Du Qiyan | theory of saddam hussein's war | 98 | | 20180103 | Fan Tong | sow postpartum nursing | | 59 20180103 | | Fan Tong | theory of saddam hussein's war | 61 | | 20180104 | Shi Zhenxiang | sow postpartum care 55 | | | 20180104 | Shi Zhenxiang ready to 46 | | | theory of saddam hussein's war +----------+-----------+-----------------------------+-------+ 8 rowsin set (0.00 sec)
Copy the code

What is GROUP BY?

We know that MySQL provides a series of aggregation functions, such as:

  • COUNT: indicates the number of statistics records.

  • MAX: Queries the maximum value of a column.

  • MIN: Queries the minimum value of a column.

  • SUM: indicates the SUM of the data in a column.

  • AVG: The average of a column.

Student score = student score = student score = student score = student score = student score

mysql> SELECT AVG(score) FROM student_score; + -- -- -- -- -- -- -- -- -- -- -- -- + | AVG (score) | + -- -- -- -- -- -- -- -- -- -- -- -- + | | + 73.1250 -- -- -- -- -- -- -- -- -- -- -- -- + 1 rowin set (0.00 sec)
Copy the code

If we only wanted to look at the average score of the subject “Postpartum Care for sows,” we would have added the WHERE clause:

mysql> SELECT AVG(score) FROM student_score WHERE subject = 'Post-natal care for sows'; + -- -- -- -- -- -- -- -- -- -- -- -- + | AVG (score) | + -- -- -- -- -- -- -- -- -- -- -- -- + | | + 73.0000 -- -- -- -- -- -- -- -- -- -- -- -- + 1 rowin set (0.00 sec)
Copy the code

For the same reason, we can also look at the average of saddam Hussein’s Preparation for War separately:

mysql> SELECT AVG(score) FROM student_score WHERE subject = 'On Saddam's War Preparations'; + -- -- -- -- -- -- -- -- -- -- -- -- + | AVG (score) | + -- -- -- -- -- -- -- -- -- -- -- -- + | | + 73.2500 -- -- -- -- -- -- -- -- -- -- -- -- + 1 rowin set (0.00 sec)
Copy the code

The question is, if 20 subjects are stored in the student_score table, how do we get the average scores of those 20 subjects separately? Write 20 separate queries? What if there were 100 courses?

Obviously, you can’t write a hundred statements foolishly. The guy who designed MySQL gave us the concept of grouping. We can group the data in the table by a column. For example, if we group the data in the table by the Subject column, all the records will be divided into two groups, as shown in the figure below:

GROUP BY: GROUP BY: GROUP BY: GROUP BY: GROUP BY: GROUP BY: GROUP BY: GROUP BY:

mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject; +-----------------------------+------------+ | subject | AVG(score) | +-----------------------------+------------+ | Theory of sow postpartum care 73.0000 | | | | | + 73.2500 war preparedness for saddam hussein -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + 2 rowsin set (0.00 sec)
Copy the code

An error

We only put the grouped column subject in the query list and the aggregation function AVG called on the records in the query list. What happens if we also put fields that are not grouped in the query list?

mysql> SELECT subject, name, AVG(score) FROM student_score GROUP BY subject;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dahaizi.student_score.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

mysql>
Copy the code

You can see that there is an error. Why is there an error? Recall that when we used the GROUP BY clause, we simply wanted to divide the records into groups and then call the aggregate function on each GROUP to do some statistical work. In this example, the query list has a name column that is neither a grouped column nor an aggregate function. What do we mean? Get the name column of a record from the records in each group? Which record should I take? For example, for the records in the group ‘postpartum care of sows’, should the value of name column be Du Ziteng, Du Qiyan, Fan Tong, or Shi Zhenxiang? We don’t know that either, so putting a non-grouped column in a query list is controversial and results in uncertainty, which is why the uncle who designed MySQL reported the above statement as an error.

But some students will say, if the value of a non-grouped column in a grouped group is the same, then I can add that non-grouped column to the query list. For example, if the value of the name column is the same for all records in the ‘post-natal care of sows’ group and the value of the name column in the’ On Saddam’s war preparations’ group after grouping by subject column, then we can put the name column in the query list. Maybe the MySQL designers thought this was a bit of a mistake, and they actually agreed to use a system variable called SQL_mode in some cases. Let’s take a look at the value of this system variable on my computer:

mysql> SHOW VARIABLES LIKE 'sql_mode'; +---------------+------------------------------------------------------------------------------------------------------- ------------------------------------+ | Variable_name | Value | +---------------+------------------------------------------------------------------------------------------------------- ------------------------------------+ | sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_EN GINE_SUBSTITUTION | +---------------+------------------------------------------------------------------------------------------------------- ------------------------------------+ 1 rowin set (0.02 sec)
Copy the code

Wow, that’s a long one. But don’t worry, we only care about one of these guys called ONLY_FULL_GROUP_BY. As long as there is the east, sql_mode value inside the MySQL server is “normal” (that is, do not allow the grouping columns on the query list), but if we put the things removed from the sql_mode system variables (remove this thing just reinstall the system variable, it is good to put this stuff away from values in, We don’t have to worry about what’s behind the edge of the value right now, just copy it down) :

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected (0.00 sec)
Copy the code

Then execute the statement above which the error was reported:

mysql> SELECT subject, name, AVG(score) FROM student_score GROUP BY subject; +-----------------------------+-----------+------------+ | subject | name | AVG(score) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- + | | sow postpartum nursing Du Ziteng 73.0000 | | | | war preparedness for theory of saddam hussein Du Ziteng | | 73.2500 +-----------------------------+-----------+------------+ 2 rowsin set (0.00 sec)
Copy the code

See, I won’t get an error this time. But is it a good thing? Personally, I don’t think so, because the MySQL server can’t guarantee that the value of the name column in the result set belongs to which record in the group. Everyone in the daily work, also hope to try not to use this opportunistic function, not what misuse, and easy to produce mistakes.

Tip: The value of SQL_mode may or may not contain ONLY_FULL_GROUP_BY by default in different MySQL versions. The inclusion of ungrouped columns in the query list may also be supported by default.

digression

Writing articles is tiring, and sometimes you feel that the reading is smooth, but it is actually the result of countless revisions behind. If you think it is good, please help to forward it, thanks a million ~ here is my public account “we are all small frog”, there are more technical dry goods, occasionally pull a calf, welcome to pay attention to: