Day8 数据库查询示例

查询

– 查询结果，只需要名字
select name from user where sex=‘女’ and age>15;
– 统计男生有多少人
select count() from user where sex=‘男’;
– 计算男生的平均年龄
select avg(age) from user where sex=‘男’;
– 找出最大的年龄
select max(age) from user;
– 找出用户表中，最小的年龄
select min(age) from user;
– 分组查询，分别统计男女人数
select count() ,sex from user group by sex;

– 取别名，用as，当然as也可以不写，用空格代替
select count(*) as ‘总人数’,sex ‘性别’ from user group by sex;
–查询名字叫小明学号为123的用户
select * from user where name='小明’and pwd=‘123’;

–统计java10班平均成绩
select avg(score) from student where classmate=‘java10’;
– 统计java10班每一科目的平均成绩
select avg(score) ,subject from student where classmate='java10’group by subject;
– 查询每个班有多少人语文成绩大于80
select count(),classmate from student where subject=‘语文’ and score>80 group by classmate;
– 查询优秀人数大于一的班级
– where 和having的区别？
– 分组以后再过滤只能用having，group by只能用having
– where用于表名的后面，用来过滤查询结果
select count(),classmate from student where subject=‘语文’ and score>80
group by classmate having count(*)>1;
*– 排序 使用order by 列名。表示结果按照这个排序
– 默认从小到大升序
select from student where classmate=‘java10’
and subject=‘语文’ order by score;
– desc后，就变成了从大到小，降序
select *from student where classmate=‘java10’
and subject=‘语文’ order by score desc ;