MYSQL row-to-column conversion and basic aggregate function count, used in combination with group by and distinct, countdistinct

MYSQL row-to-column conversion and basic aggregate function count, used in combination with group by and distinct, countdistinct

The count function is often used in statistical queries. Here, the basic MYSQL row-to-column conversion and basic aggregate function count are used in combination with group by and distinct.

-- Create table 'tb _ student '('id' int (11) not null, 'stu _ name' varchar (255) character set utf8mb4 default null comment 'student name', 'tea _ name' varchar (255) default null comment 'instructor name', 'stu _ class' varchar (255) default null comment 'class name', 'stu _ sex' varchar (255) default null comment 'student Gender', 'stu _ sex_int 'tinyint (4) default null, primary key ('id') ENGINE = InnoDB default charset = utf8; -- INSERT data/* insert into 'tb _ student 'VALUES ('0', 'xiaoming ', 'Old Zhang ', 'shift 1', 'male', 0); insert into 'tb _ student 'VALUES ('1', 'small red', 'old Zhang ', 'shift 1 ', 'female', 0); insert into 'tb _ student 'VALUES ('2', 'xiaogang', 'old Wang ', 'shift 1 ', 'male', 0); insert into 'tb _ student 'VALUES ('3', 'Alan', 'old Wang ', 'First ban', 'female, 0 ); insert into 'tb _ student 'VALUES ('4', 'xiaojun', 'zhang ', 'class 2', 'mal', 0 ); insert into 'tb _ student 'VALUES ('5', 'xiaofang', 'zhang zhang', 'second class', 'female, 0 ); insert into 'tb _ student 'VALUES ('6', 'xiaoqiang', 'old Wang ', 'second class', 'mal', 0 ); insert into 'tb _ student 'VALUES ('7', 'dana', 'old Wang ', 'second class', 'female, 0 ); insert into 'tb _ student 'VALUES ('8', null, null ); * // **************************/explain select count (2) from tb_student; SELECT count (*) from tb_student; // 8 SELECT count (1) from tb_student; // 8 SELECT count (stu_name) from tb_student; // 7 SELECT count (NULL) from tb_student; // 0/** summary when the expression of count is NULL, it does not count, so count (fieldName) does not count when fieldName is null, so count (n) number of records used to query the table */select count (DISTINCT tea_name) from tb_student; select distinct tea_name from tb_student; SELECT *, count (tea_name) from tb_student group by tea_name; /** query the Number of Students taught BY each teacher in the first class and the number of students taught in the second class */select *, count (id) FROM tb_student group by tea_name, stu_class; /* this method is not intuitive. We can clearly express the number of students handed in by each teacher in the result row or column. */SELECT tea_name, COUNT (case when stu_class = 'first shift 'then 1 else null end) AS 'one cell number', COUNT (case when stu_class = 'second shift 'then 5 else null end) AS 'number of Students in second Class' FROM tb_student group by tea_name;/** Number of Students taught BY each teacher */SELECT tea_name, COUNT (*) AS 'student count 'FROM tb_student group by tea_name;