查询---**---【条件，排序，聚合函数，分组，分页】 --***--
查询所有列------>>【select * from 表名】
select * from students;
一定条件查询--->>【select * from 表名 where 条件】
select * from students where id>5
查询制定列----->>【select 字段 from 表名】
select name,age from students;
给字段起别名---->>【select 字段 as 别名 from 表名】
select name as '姓名', age as '年纪' from students;
通过表名字查询--->>【select 表名.字段 from 表名】
select students.name, students.age from students;
给表起别名查询--->>【select 别名.字段 from 表名 as 别名】
select s.name, s.age from students as s;
消除重复行------>>【select distinct 字段 from 表名】
select distinct age from students;
比较运算符----【> < >= <=】
查询年纪大于18岁的信息
select * from students where age > 18;
18岁到28岁之间(and)
select * from students where age > 18 and age < 28;
在18岁以上或者身高180以上的人(or)
select * from students where age > 18 or high > 180;
模糊查询-----【like】
【%】 替代1个或者多个甚至是没有
查询姓名中有‘T’的所有名字
select * from students where name like '%T%';
查询有两个字的名字
select * from students where name like '__';
查询至少有2个字的名字
select * from students where name like '__%';
【rlike】 正则
查询以T开始的名字
select * from students where name rlike '^T.*';
范围查询----【in | not in】
【in】 表示在一个非连续的范围内
查询年纪为18和19的人
select * from students where age not in (18,19);
select * from students where age=18 or age=19;
【between】 连续范围,顾头顾尾
查询 年龄在17岁到34岁之间的信息
select * from students where age between 17 and 34;
【not between】
查询 年纪不在18到23岁的信息
select * from students where age not between 18 and 23;
空判断-----【is null | is not null】
【is null】
查询身高为空的信息
select * from students where high is null;
【is not null】
select * from students where high is not null;
排序-------【order by】
【order by 字段】
【asc】从小到大排列,即升序
【desc】从大到小排序，即降序
查询年纪在18到34岁之间的男性，按照年纪从小到大
select * from students where (age between 18 and 34) and gender=1 order by age;
查询年纪在18到34岁之间的女性，身高从高到矮
select * from students where (age between 18 and 34) and gender=2 order by high desc;
【order by 多字段】
查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序
select * from students where (age between 18 and 34) and gender=2 order by high desc, age asc;
查询年纪在18到34岁的女性，身高从高到矮排序，如果身高相同的情况下按照年纪从小到大排序，如果年龄也相等那么按照id从小到大排序；
select * from students where (age between 18 and 34) and gender=2 order by high desc, age asc, id asc;
select * from students where (age between 19 and 21) and denger=2 having cls_id=2 order by age desc,high desc,id desc;
按照年纪从小到大,年纪相同情况下按照身高从高到矮的排序
select * from students order by age, high desc;
聚合函数
【count】总数
查询男性有多少人
select count(*) from students where gender=1;
【max】大值
查询大的年纪
select max(age) from students;
查询年纪大的记录
select * from students where age=(select max(age) from students);
+----+-------+------+--------+--------+--------+-----------+
|id|name|age|high|denger|cls_id|is_delete|
+----+-------+------+--------+--------+--------+-----------+
|5|Trump|45|179.01|男|2||
+----+-------+------+--------+--------+--------+-----------+
查询女性的高 身高
select max(high) from students where gender=2;
【min】小值
select min(high) from students where gender=2;
【sum】求和
计算所有人的年龄总和
select sum(age) from students;
【avg】平均值
计算平均年纪
select sum(age)/count(*) from students;
select avg(age) from students;
【round】保留2位小数
select round(avg(age), 2) from students;
【group by】分组
按照性别分组，查询所有的性别
select gender from students group by gender;
计算每组性别的人数
select gender, count(*) from students group by gender;
计算男性人数
select count(*) from students where gender=1 group by gender;
【concat | concat_ws】把查出来的内容拼接成一个字符串
select concat(name,' ',age,' ',high) from students;
select concat_ws('|',name,age,high) from students;
【group_concat】查询男性组中的姓名
select group_concat('|',name) from students;
【having】
-- 查询每个性别年纪平均超过30岁的性别
select gender from students group by gender having avg(age) > 30;
查询每种性别中的人数多于4个组的信息
select gender from students group by gender having count(*)>4;
分页
【limit】
显示分页
select * from students limit 5;
分页显示，每页显示2条数据
select * from students limit 0,2;
按照身高从高到矮排序，查找出所有女性，并且分页显示，每页显示2条数据
select * from students where gender=2 order by high desc limit 0,2;
