4：单表查询-聚合函数，分组，排序，去重，限量

--字段去重--distinct     
select distinct sname from student

--排序--order by(ASC升序[默认] DESC降序)
select distinct sage from student order by sage asc（先降序在去重）

select count( distinct sage)  from  student ---不同年龄的个数（先去重，在count）

select * from person order by age desc, phone asc（先age降序，在按照age分组后phone字段升序  ）

--限量--limit start,end  从start+1条开始往下end条

select DISTINCT chinese from user ORDER BY english desc limit 3

每页显示记录数*（第几页-1），每页显示记录数

 

-- 部分列
select name,age from person;

-- 指定列名---列的简单运算和别名
select name as '名字', age as '年龄' from person;

select name,(english+chinese) as total_score from user

-- 条件查询
select * from person where phone='13545269582';
select * from person where phone !='13545269582';
select * from person where age >= 24;

select * from person where age >= 24 and name=‘lijia’;

-- between(包含-闭区间)
select * from person where age between 22 and 25;

-- like模糊 _单字符  %所有
-- 以朱开头
select * from person where name like '朱%';
-- 以1结尾
select * from person where name like '%1';
-- 所有带朱字的人
select * from person where name like '%朱%';

-- in 其中一个
select * from person where phone in (12645825963,12645825967);

-- 判断 空 is null   not null

select * from person where name is null;
select * from person where name is not null;

 

聚合函数

1：count：*统计全部条数，有字段（字段值为空的不统计）

SELECT count(*) as '总和' from user
SELECT count(chinese) from user

2：sum：求和

-- 求所有年龄之和
select sum(age) from person

select sum(age+age1+age2) from person

3：max min

-- 年龄最大
select max(age) from person
-- 年龄最小
select min(age) from person

4：avg--平均值

select avg(chinese) from user

 

分组-group by    having分组后过滤（先分组和执行字段筛选条件后出结果后，在进行having过滤）

 select name,count(*) from employee_tbl group by name HAVING count(*)>=2

 

-- 求每个班级的平均年龄
select cls,avg(age) from person group by cls
-- 查询每个班级的最大年龄
select cls,max(age) from person group by cls

 

-- 查询每个班级有多少人
select cls,count(*) from person group by cls
-- 查询班级人数小于3人的班级
select cls,count(*) from person group by cls having count(*) < 3