MySQL数据库-数据 基础查询操作命令演示

查询全部的班级
select * from classes;

查询全部的学生的“姓名”“年龄”“身高”“性别”
select name as “姓名”,age as “年龄”,height as “身高”,gender as"性别"from students;

查询身高为null的学生信息
select * from students where height is null;

查询身高为null的男生学生信息
select * from students where height is null and gender = 1;

查询全部男生信息
select * from students where genter = ‘男’;

查询男生大于18岁的
select * from students where genter = 1 and age >18;

查询身高大于160，年龄在18-30之间的女性，并且按照年龄升序排列、身高倒序排列
select * from students where height > 160 and (age between 18 and 30) and gender = 2 order by age asc, height desc;

查询名字是三个字的同学
select * from students where name like “___”

查询班级一共多少学生
select count(*) from students;

查询班级一共多少女生
select count(*) from students where gender = 2;

查询班级一共几种性别（两种方式：去重、分组）
select distinct gender from students;
select gender from students group by dender;

查询学生的最大年龄，最小身高，平均身高
select max(age),min(height),avg(height) from students；

查询最大年龄，最小身高，平均身高（保留两位小数）
select max(age),min(height),round(avg(height),2) from students;

每页显示10条 查询第2页学生信息 (页码 - 1) * 每页显示条数
select * from students limit 10,10;

每页显示10条 查询第5页学生信息
select * from students limit 40,10;

查询所有学生姓名、年龄、身高、性别、班级名称
select s.name,s.age,s.height,s.gender,c.name from students as s inner join classes as c on s.cls_id = c.id;
select g_new.cate_name,g.name,g.price from
(
select
cate_name,
max(price) as max_price from goods group by cate_name
) as g_new left join goods as g
on g_new.cate_name=g.cate_name and g_new.max_price=g.price order by g_new.cate_name;