mysql查询综合练习

创建一个学生表，插入数据

create table student(
id int,
name varchar(20),
chinese int,
english int,
math int
);

insert into student(id,name,chinese,english,math) values(1, '范建',80,85,90);
insert into student(id,name,chinese,english,math) values(2,'罗况',90,95,95);
insert into student(id,name,chinese,english,math) values(3,'杜子腾',80,96,96);
insert into student(id,name,chinese,english,math) values(4,'范冰',81,97,85);
insert into student(id,name,chinese,english,math) values(5,'申晶冰',85,84,90);
insert into student(id,name,chinese,english,math) values(6,'郝丽海',92,85,87);
insert into student(id,name,chinese,english,math) values(7,'郭迪辉',75,81,80);
insert into student(id,name,chinese,english,math) values(8,'拎壶冲',77,80,79);
insert into student(id,name,chinese,english,math) values(9,'任我行',95,85,85);
insert into student(id,name,chinese,english,math) values(10,'史泰香',94,85,84);

查询表中所有学生的信息

select * from student;

查询表中所有学生的姓名和对应的英语成绩。

select name,english from student;

过滤表中重复数据

select english from student;
select DISTINCT english from student;
select DISTINCT english,name from student;
select english+chinese+math from student;
select english+chinese+math as 总分 from student;
select name,english+chinese+math as 总分 from student;

在所有学生英语分数上加10分特长分。

select name,english+10 from student;

统计每个学生的总分。

select english+chinese+math from student;

使用别名表示学生分数

select name,english+chinese+math as 总分 from student;
select name,english+chinese+math 总分 from student;

查询姓名为何东的学生成绩

select * from student where name='何东';

查询英语成绩大于90分的同学

select * from student where english>90;

查询总分大于250分的所有同学

select * from student where english+chinese+math>250;

查询英语分数在 85－95之间的同学。

select * from student where english>=85 and english<=95;
select * from student where english between 85 and 95;

查询数学分数为84,90,91的同学。

select * from student where math=84 or math=90 or math=91;
select * from student where math in(84,90,91);

查询所有姓何的学生成绩。

select * from student where name like '何%';

查询数学分>85，语文分>90的同学。

select * from student where math>85 and chinese>90;

对数学成绩排序后输出。

select * from student order by math;

对总分排序后输出，然后再按从高到低的顺序输出

select * from student order by math+chinese+english desc;

对姓何的学生成绩排序输出

select * from student where name like '何%' order by math+chinese+english desc;
select name, math+chinese+english from student where name like '何%' order by math+chinese+english desc;

统计一个班级共有多少学生？

select count(*) from student;

统计数学成绩大于90的学生有多少个？

select count(*) from student where math>90;

统计总分大于250的人数有多少？

select count(*) from student where math+chinese+english>250;

统计一个班级数学总成绩？

select sum(math) from student;

统计一个班级语文、英语、数学各科的总成绩

select sum(math), sum(chinese), sum(english) from student;

统计一个班级语文、英语、数学的成绩总和

select sum(math+chinese+english)from student;
select sum(math)+sum(chinese)+sum(english) from student;

求一个班级数学平均分？

select avg(math) from student;

求一个班级总分平均分

select avg(math+chinese+english)from student;
select avg(math)+avg(chinese)+avg(english) from student;

求班级最高分和最低分

select max(math+chinese+english),min(math+chinese+english) from student;

top-N问题:

按math成绩从小大的排序, 求math成绩在5-8名的
select * from student order by math limit 4, 4;

limit后面的两个数字的意思:
	第一个4: 表示要跳过前面几个
	第二个4: 表示连续取几个.

分组数据

为学生表，增加一个班级列，练习分组查询。

alter table student add column class_id int;	

注意语法：Oracle中不能有“column”关键字，MySQL中有没有“column”都可以执行。

更新表：

update student set class_id=1 where id<=5;
update student set class_id=2 where id>5;
(update student set class_id=2 where id between 6 and 10;)

查出各个班的总分，最高分。

select class_id, sum(chinese+english+math) "总成绩", max(chinese+english+math) "最高分" from student group by class_id;

求各个班级 英语的平均分：

select classid, avg(english) from student group by class_id

如根据组函数的语法要求，将select后增加name列，而不加至group by 之后：

select name, classid, avg(english)from student group by classid;
会发现MySQL检查不出错误。相比Oracle数据库，MySQL分组检查不严格。
select sum(math+chinese+english),max(math+chinese+english) from student group by class_id;

查询出班级总分大于1300分的班级ID

select class_id from student group by class_id having sum(math+chinese+english)>1300;
select class_id from student where sum(math+chinese+english)>1300 group by class_id ;
		对于组函数的应用与Oracle类似，可以应用于Having中，但不能用于where子句中.
		
select class_id, sum(chinese+english+math) "总分" from student group by class_id having 总分>1300;	

注意: having后面若是用"总分"不可以, 但是不加""可以.
        			在oracle数据库中, having后面不可以使用别名, mysql可以使用别名(若是中文不要加"")