mysql 高级查询

• 查询指定字段

select name,age from students;

• 起别名

select name as 姓名,age as 年龄 from students;

select s.name s.age from students as s;

• 去重

select name as 姓名,age as 年龄 from students;

select s.name s.age from students as s;

• 条件查询

--比较运算符.. > < >=<==!=
查询大于18岁信息
slet* from students where age >= 18；
逻辑运算符
--and or not
查看18到28岁之间的所有学生的信息
select* from students
where age>18 and age<28;
年龄小于或等于18并且是女性
select * from students where age<=18 and gender-°女:

• 模糊查询

-like
- %替换1个或者多个
-_替换1个
一查询姓名中以小开头的名字
select * from students where name like “小%”
-查询有2个字的名字
select name from students where name like" .
一查询至少有2个字的名字
selet name from students where name like" _%"
-- rlike正则
-查询以周开始的姓名
seles name from students where name rlike“周.*”;
一查询以周开头以伦结尾
selet name from students where name rike “^周.*伦$;

• 范围查询

--in(1,3,8)表示在一个非连续的范围内
not in (1,3,8)
 --查询年龄为18,34的姓名
select name,age from students where age in (18,34);
--between..and..表示在一个连续的范围

not between ... and ..
 --查看年龄在18到34之间的姓名，年龄
select name,age from students where age between 18 and 34;

--判空is null
判非空is not null
--查询身高为空的信息
select * from students where height is null;

• 排序

--orderby字段
-- asc从小到大，升序 
--desc从大到小，降序
-- 默认升序

--查询年龄子啊18到34岁之间的男性，按照年龄从小到大
-- select * from表名where条件order by字段asc/
desc字段asc/desc;
select * from students where (age between 18 and 34) and gender= "男" order by age asc;
select* from students where (age between 18 and 34) and gender='男”order by age asc , height asc;

• 聚合函数

--总数count
--查询男性有多少人，女性有多少入
 select count(*) as男性人数from students where gender= 1;
 select count(*) as女性人数from students where gender=2;

--最大值max
-- 最小值min
--查询最大的年龄
select max(age) from students;
--查询女性最高升高
select max(height) from students where gender=2;
--求和sum
-- avg平均值
-计算所有人的年龄总和
select sum(age) from students;
--计算平均年龄avg sun(agel/count()
select avg(age) from students;
- round(123.23, 1)保留1位小数
 -- 计算所有人的平均年龄，保留2位小数
 select round(avg(age),2) from students;
--计算男性的平均身高，保留2位小数
select round(avg(height,2)) from students where gender=1; 

• 分组查询

-- group by
--按照性别分组,查询所有性别
 select gender from students group by gender; 

-- group concat0
--计算每种性别中的人数
select gender,count(*) from students group by gender;
select gender,group concat(name,age) from students group by gender; 

--计算男性人数
select gender,count(*) from students where gender=1 group by gender;
-- having
--查询平均年龄超过30岁的性别，以及姓名having avg(age)>30
select gender,group concat(name),avg(age) from students group by gender having avg(age)> 30;
--查询每种性别当中的人数大于2的信息
select gender,group concat(name) from students group by gender having count(*)> 2;

• 分页查询

 -- limit start, count start 开始的位置，不写默认从开始，count 个数
 -- 限制查询出来的数据的个数
 select ★from students limit 5;
-- 查询id6-10
select * from students limit 5,5
--显示第N页，每页显示5个limit (第N页-1)*5,5不能这样写

• 多表查询

--链接查询
--内连接.inner join ... on ..
--查询有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_ id = classes.id;
-按照要求显示姓名、班级
select students.*,classes.name from students inner join classes on students.cIs id = classes.id;
--给数据表起名
select s.*,c.name from students as s inner join classes as c on s.cls_ id = c.id; 
-左链接left join ...
--查询每位学生对应的班级
select * from students as s left join classes as C on s.cIs_ id = c.id;
-查询没有对应班级信息的学生where/having
select * from students as s left join classes as C on s.cls_ id = c.id having c.name is null; 
select * from students as s left join classes as C on s.cIs_ id = c.id where c.name is null;