【mysql基础】统计各个姓氏

创建数据库和表

数据库

drop database if exists student_test cascade;
create database student_test;
use student_test;

建表

create table student (
	s_id int,
	s_name varchar(8),
	s_birth date,
	s_sex varchar(4)
);

插入数据

insert into student values
(1,'赵士者','1990-01-01','男'),
(2,'钱电大','1990-12-21','男'),
(3,'孙策','1990-05-20','男'),
(4,'李云龙','1990-08-06','男'),
(5,'周梅妹','1991-12-01','女'),
(6,'吴兰花','1992-03-01','女'),
(7,'孙竹兰','1989-07-01','女'),
(8,'赵小花','1990-01-20','女');

查询各个姓氏有多少

SELECT 
LEFT(s_name, 1) name, 
COUNT(LEFT(s_name,1))number 
FROM 
student
GROUP BY name
ORDER BY number DESC

查询单个姓氏有多少

SELECT
  LEFT(s_name, 1) name,
  COUNT(*)number
FROM
  `student`
WHERE
  s_name LIKE '赵%'