目录
7：从score中计算学生的总分和平均分向下取整并且从大到小排序
一：建表语句和数据
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('0','1') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;
insert into students(name,age,sex)VALUES("李四",19,"1");
insert into students(name,age,sex)VALUES("王五",20,"1");
insert into students(name,age,sex)VALUES("赵六",21,"1");
insert into students(name,age,sex)VALUES("钱琪",22,"1");
insert into students(name,age,sex)VALUES("杨幂",18,"0");
insert into students(name,age,sex)VALUES("丽丽",19,"0");
insert into students(name,age,sex)VALUES("莎莎",20,"0");
insert into students(name,age,sex)VALUES("慧慧",22,"0");
insert into students(name,age,sex)VALUES("翠翠",23,"0");
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`subjectName` varchar(255) NOT NULL,
`score` int(11) NOT NULL,
`studentId` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;
insert into score(subjectName,score,studentId)VALUES("语文",80,1001);
insert into score(subjectName,score,studentId)VALUES("数学",89,1001);
insert into score(subjectName,score,studentId)VALUES("英语",73,1001);
insert into score(subjectName,score,studentId)VALUES("语文",81,1002);
insert into score(subjectName,score,studentId)VALUES("数学",99,1002);
insert into score(subjectName,score,studentId)VALUES("英语",94,1002);
insert into score(subjectName,score,studentId)VALUES("语文",65,1003);
insert into score(subjectName,score,studentId)VALUES("数学",45,1003);
insert into score(subjectName,score,studentId)VALUES("英语",12,1003);
insert into score(subjectName,score,studentId)VALUES("语文",33,1004);
insert into score(subjectName,score,studentId)VALUES("数学",68,1004);
insert into score(subjectName,score,studentId)VALUES("英语",59,1004);
insert into score(subjectName,score,studentId)VALUES("语文",19,1005);
insert into score(subjectName,score,studentId)VALUES("数学",100,1005);
insert into score(subjectName,score,studentId)VALUES("英语",80,1005);
insert into score(subjectName,score,studentId)VALUES("语文",85,1006);
insert into score(subjectName,score,studentId)VALUES("数学",88,1006);
insert into score(subjectName,score,studentId)VALUES("英语",73,1006);
insert into score(subjectName,score,studentId)VALUES("语文",88,1007);
insert into score(subjectName,score,studentId)VALUES("数学",45,1007);
insert into score(subjectName,score,studentId)VALUES("英语",89,1007);
insert into score(subjectName,score,studentId)VALUES("语文",51,1008);
insert into score(subjectName,score,studentId)VALUES("数学",42,1008);
insert into score(subjectName,score,studentId)VALUES("英语",43,1008);
insert into score(subjectName,score,studentId)VALUES("语文",90,1009);
insert into score(subjectName,score,studentId)VALUES("数学",56,1009);
insert into score(subjectName,score,studentId)VALUES("英语",69,1009);
insert into score(subjectName,score,studentId)VALUES("语文",99,1010);
insert into score(subjectName,score,studentId)VALUES("数学",63,1010);
insert into score(subjectName,score,studentId)VALUES("英语",88,1010);二：题目
注意：题目是我自己编的如果有错误请多多指教哦
1：从students表中查找年龄小于20的全部学生
select * from students where age<20;
2：从students表中查找 年龄在20和25之间的学生
select * from students where age between 20 and 25;
3：从students表中查找name中姓李的
select * from students where name like '李%';
注意：%是匹配所有以前面开头的，_是匹配一个
4： 从students表中查找年龄大于20，性别是女的
select * from students where age>20 and sex='0';
5：查找年龄小于20，或者性别为女的
select * from students where age<20 or sex='0';
6：计算有多少名学生
select count(*) as count from students;
7：从score中计算学生的总分和平均分向下取整并且从大到小排序
select studentId,FLOOR(sum(score) )as sum,FLOOR(avg(score)) as avg from score group by studentId order by sum desc;8：求每个学生成绩的最大值和最小值
select studentId,min(score) as min, max(score) as max from score group by studentId;9：按照总分求排名前三的学生
select studentId,sum(score) as sum from score group by studentId order by sum desc limit 3;
10：求按照总分排名3-6名
select studentId,sum(score) as sum from score group by studentId order by sum desc limit 3,3;
11：查找平均分小于60的学生
select studentId,FLOOR(avg(score)) as avg from score group by studentId having avg <60;12：求男女中按照年龄从大到小前三的学生
select * from students as s1 where 3>(
select count(*) from students as s2 where s1.sex=s2.sex and s1.age <s2.age);