mysql基础练习题

目录

一：建表语句和数据

二：题目

1：从students表中查找年龄小于20的全部学生

2：从students表中查找 年龄在20和25之间的学生

 3：从students表中查找name中姓李的

4： 从students表中查找年龄大于20，性别是女的

 5：查找年龄小于20，或者性别为女的

 6：计算有多少名学生

7：从score中计算学生的总分和平均分向下取整并且从大到小排序

 8：求每个学生成绩的最大值和最小值

 9：按照总分求排名前三的学生

 10：求按照总分排名3-6名

 11：查找平均分小于60的学生

 12：求男女中按照年龄从大到小前三的学生

---

一：建表语句和数据

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);