Mysql database operation group by, mysqlgroup

Mysql database operation group by, mysqlgroup

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.

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 |   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)

The query results are consistent. If you change the selection condition to a condition where the total score of all Department members is greater than 150 to list the persons in the department with the highest score, having must be used here, because the aggregate function sum can be used in having, and the total score of this group can be obtained only after dividing the group to compare whether the value is greater than 150:

mysql> select *,max(score) as max from student   group by dept having sum(score)>150 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.00 sec)

Add an example. For example, if you want to select a department that is not repeated, you can use

mysql> select distinct dept from student;+---------+| dept    |+---------+| dev     || design  || sales   || product |+---------+4 rows in set (0.02 sec)

However, if we want to list other information such as his id, we should:

mysql> select name,distinct dept from student;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct dept from student' at line 1

This is not acceptable, because distinct can only be placed at the starting position, if:

mysql> select distinct  dept,name from student;+---------+------+| dept    | name |+---------+------+| dev     | a    || dev     | b    || design  | b    || sales   | c    || product | d    || product | m    |+---------+------+6 rows in set (0.00 sec)

Why didn't we achieve the expected results? Because distinct applies to two fields, then we need to start groub.

mysql> select  dept,name from student group by dept;+---------+------+| dept    | name |+---------+------+| design  | b    || dev     | a    || product | d    || sales   | c    |+---------+------+4 rows in set (0.00 sec)

Grouping by dept will naturally achieve de-duplication. So sometimes if we encounter a problem that is difficult to solve, such as removing duplicates with distinct and adding other column values, we need to change our thinking, and the answer will naturally be found.

MYSQL Data GROUP

Grouping ID and price
SELECT pid, price, sum (num) as total
From A group by pid, price
Order by id

How to operate mysql by group statements in java

First, you need to load the driver package of the corresponding database in the project, and then query the database. The Code will be provided to you later.
String SQL = "";
Class. forName ("com. mysql. jdbc. Driver ");
Connection con = DriverManager. getConnection ("jdbc. mysql // localhost: 3306/<Your Database Name>", "username", "pwd ");
PreparedStatement pstmt = con. prepareStatement (SQL );
ResultSet rs1_pstmt.exe cuteQuery ();
While (rs. next ()){
System. out. println (rs. getInt (1) + "" + rs. getInt (2 ));
}