MySQL Database Operations group

MySQL Database Operations group

During the IT interview, database-related questions are basically mandatory, and SQL statements are also an important knowledge point that is frequently investigated.

Next we will introduce an important operation group by in an SQL statement. Its important line is reflected in the difficulty of understanding and the extensiveness of the application.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

First, a studnet student table is provided:

Create table 'student '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (30) default null,
'Sex' tinyint (1) DEFAULT '0 ',
'Score 'int (10) not null,
'Dept' varchar (10) default null,
Primary key ('id ')
) ENGINE = MyISAM AUTO_INCREMENT = 8 default charset = utf8

Add some test data:

Mysql> select * from student where id <10;
+ ---- + ------ + ------- + --------- +
| Id | name | sex | score | dept |
+ ---- + ------ + ------- + --------- +
| 1 | a | 1 | 90 | dev |
| 2 | B | 1 | 90 | dev |
| 3 | B | 0 | 88 | design |
| 4 | c | 0 | 60 | sales |
| 5 | c | 0 | 89 | sales |
| 6 | d | 1 | 1 | 100 | product |
+ ---- + ------ + ------- + --------- +

Provide the requirement and write the SQL statement:

The highest score of students in each department.

To get students from different departments, you must first group them by department, and then find the highest score in each department.

Therefore, the SQL statement is:

Mysql> select *, max (score) as max from student group by dept order by name;
+ ---- + ------ + ------- + --------- + ------ +
| Id | name | sex | score | dept | max |
+ ---- + ------ + ------- + --------- + ------ +
| 1 | a | 1 | 90 | dev | 90 |
| 3 | B | 0 | 88 | design | 88 |
| 4 | c | 0 | 60 | sales | 89 |
| 6 | d | 1 | 100 | product | 100 |
+ ---- + ------ + ------- + --------- + ------ +
4 rows in set (0.00 sec)

This is just a simple example. We can complicate this example. For example, the highest score must be girl, that is, the sex column value must be 1, the SQL statement should be:

Mysql> select *, max (score) as max from student group by dept having sex = '1' order by name;
+ ---- + ------ + ------- + --------- + ------ +
| Id | name | sex | score | dept | max |
+ ---- + ------ + ------- + --------- + ------ +
| 1 | a | 1 | 90 | dev | 90 |
| 6 | d | 1 | 100 | product | 100 |
+ ---- + ------ + ------- + --------- + ------ +
2 rows in set (0.46 sec)

Having is not used here, but is used here for simple explanation, because our condition is After grouping. In fact, sex = '1' is selected before grouping ', it is also feasible to group by dept department. Here we will look at the Requirements of the subject:

Mysql> select *, max (score) as max from student where sex = '1' group by dept order by name;
+ ---- + ------ + ------- + --------- + ------ +
| Id | name | sex | score | dept | max |
+ ---- + ------ + ------- + --------- + ------ +
| 1 | a | 1 | 90 | dev | 90 |
| 6 | d | 1 | 100 | product | 100 |
+ ---- + ------ + ------- + --------- + ------ +
2 rows in set (0.05 sec)

For more details, please continue to read the highlights on the next page:

1 2 Next Page