First, environmental preparation

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'student id',
  `student_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Student name',
  `sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'gender \ r \ n',
  `age` int(11) NULL DEFAULT NULL COMMENT 'age',
  `result` double(10.0) NULL DEFAULT NULL COMMENT 'results'.PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student` VALUES (1.'wang'.'male'.18.90);
INSERT INTO `student` VALUES (2.'xiao li'.'woman'.19.80);
INSERT INTO `student` VALUES (3.'Ming'.'male'.20.85);
INSERT INTO `student` VALUES (4.'zhang'.'male'.21.87);
Copy the code

Second, query result sorting

The most common application is to screen hot products, or microblogging hot search.

Syntax format:

SELECT 1…. FROM the table name for the ORDER BY the field name 1 [ASC | DESC], the field name 2 [ASC | DESC]… ;

Fields 1 and 2 are used to sort the query results. ASC: ascending DESC: descending. The default is ASC.

Raise a paw:

SELECT * FROM  student ORDER BY  age DESC ;
Copy the code

If the first field name is the same, sort the field by the second one.

You’re left to figure it out.

Think about:

Student_name = age; student_name = age;

Third, query grouping and summary

Aggregation function

function role
AVG () Returns the average value of a column (average value)
The COUNT () Returns the number of rows for a column (statistics)
MAX () Returns the maximum value of a column (Max)
The MIN () Returns the minimum value of a column (min)
The SUM () Returns the sum of the values of a column.

3.1. Look up the average age of students

select AVG(age) as"Age"from student;
Copy the code

3.2. Find out what the total number is

select count(id) as"Total head count"from student;
Copy the code

3.3. Find out how many people there are at each age

select age, count(id) as"Total head count"from student GROUP BY age;
Copy the code

3.4 find out the oldest age

select MAX(age) as"Maximum age"from student ;
Copy the code

Less than the same.

3.5, find the number of men and women

select sex ,count(*) AS"The number"from student GROUP BY sex;
#GROUP BYIs to group the results by the field name that followsCopy the code

3.6, query the total score is how many

select sum(result) as"Total Score"FROM student;
Copy the code

self-talk

Fishing for the first time.