mysql简单应用题是什么题型_mysql简单sql复习题

#mysql连接查询，和子查询的复习

##数据库表简介

1、grade年级

2、student 学生

3、subject 科目

4、result 成绩

## 子查询

一个查询语句包含了另一个查询语句的编码方式

where 筛选分组前的内容，having筛选分组后的内容

只要是聚合函数做条件一定放在having后；

1考试成绩不及格(<60分)的学生姓名

查询出成绩小于60的学生编号select studentno from result where score<60;

查询出学生编号是多少的学生姓名 select studentname from student where studentno;

整合：select studentname from student where studentno in (select studentno from result where score<60);

2学生“金蝶”的所有考试成绩信息

查询出学生姓名为“金蝶”的学生编号 select studentno from student where studentname=”金蝶”;

通过学生编号查询出全部成绩信息：select score from result where studentno;

整合：select score from result where stuentno in(或者这里将in改为=) (select stuentno from student where studentname=”金蝶”);

细节：=()中可以编写limit关键字，而in()中不可以编写limit关键字

3“C#基础”考试中得了100分的学生姓名

在科目表中查询出科目为c#基础的科目编号:select subjectid from subject where subjectname=”c#基础”;

通过查询出的科目编号在成绩表中查询成绩为100的学生编号:select studentno from result where subjectid =?and score=100;

通过查询出学生编号在学生表中查询学生姓名 select studentname from student where studentno=?

整合：select studentname from student where studentno in (select studentno from result where subjectid=(select subjectid from subject where subjectname=”c#基础” ) and score=100);

4查询“第一阶段”中的出生日期在‘1990-1-1’之后的男生有多少个

在grade表中查询出第一阶段的gradeid：select gradeid from grade where gradename=”第一阶段”

根据gradeid在student表中查询出出生日期在1990-1-1之后的男生个数：

select count(*) from student where sex=”男” and borndate>’1990-1-1’ and grade=?

整合：select count(*) from student where sex=”男” and borndate>’1990-1-1’ and grade=( select gradeid from grade where gradename=”第一阶段”);

5查询“第三阶段”有多少科目课时超过70课时

在grade表中查询出第三阶段的id:select gradeid from grade where gradename=”第三阶段”;

根据gradeid在subject表中查询出科目：select count(*) from subject where hour>70 and gradeid=?

整合: select count(*) from subject where hour>70 and gradeid=( select gradeid from grade where gradename=”第三阶段”);

6查询参加过“第二阶段”课程考试的所有学生信息

在grade表中查询第二阶段的id: select gradeid from grade where gradename=”第二阶段”;

根据gradeid在subject表中查询出subjectid :select subjectid from subject where gradeid=?

查询第二阶段所有科目的学生id

select studentid from result where subjectid in(select subjectid from subject where gradeid=(select gradeid from grade where gradename=”第二阶段”));

根据学号查询出学生信息

select * from student where studentno in( select studentno from result where subjectid in(select subjectid from subject where gradeid=(select gradeid from grade where gradename=”第二阶段”)));

7查询“C#高级”课程考试成绩高于本课程平均分的学生信息

查询出”c#高级”课程的id:select subjectid from subject where subjectname=”c#高级”;

在成绩表中查询出平均成绩:select avg(score) from result where subjectid=?;

在成绩表中查询出所有课程为c#高级的学生成绩: select score from result where subject=?;

查询出课程成绩大于平均成绩的学生id : select studentno from result where score >(select avg(score) from result where subjectid=( select subjectid from subject where subjectname=”c#高级”)) and subjectid=( select subjectid from subject where subjectname=”c#高级”);

根据studentno查询出学生信息：select * from student where studentno in(select studentno from result where score >(select avg(score) from result where subjectid=( select subjectid from subject where subjectname=”c#高级”)) and subjectid=( select subjectid from subject where subjectname=”c#高级”));

8查询“java基础”课程获得最高分的有几个人

查询出java基础的课程号:select subjectid from subject where subjectname=“java基础”;

根据subjectid查询出java基础成绩最高的分数

select max(score) from result where subjectid=(select subjebctid from subject where subjectname=”java基础”);

根据最高分查询出人数：select count(*) from result where score= (select max(score) from result where subjectid=(select subjebctid from subject where subjectname=”java基础”)) and subjectid=(select subjectid from subject where subjectname=“java基础”);

##综合练习

32查询年龄比“金辉”大的学生

select * from student where borndate

查询手机号是11位的学生

select * from student where length(phone)=11;

查询学生邮箱为空的信息

select * from student where email is null;

查询学生邮箱不为空的信息

select * from student where email is not null;

查询出每个学生的年龄

select studentname,floor((to_days(now())-to_days(borndate))/365.25) as 年龄 from student;

33查询"mysql数据库"不及格的学生信息(姓名、性别等);

select * from student where studentno in (select studentno from result where subjectid=(select subjectid from subject where subjectname=”mysql数据库”) and score<60);

34查询考试平均分最低的学生

select studentno,avg(score) from result group by studentno;

分组之后查询最高，最低的结果使用limit

select studentno,avg(score) from result group by studentno order by avg(score) asc limit 0,1;

select * from student where studentno =( select studentno from result group by studentno order by avg(score) asc limit 0,1);

37 查询参加考试全部通过的学生信息

找到最低分高于60的学生。

select studentno,min(score) from result group by studentno having min(score)>=60;

## 连接查询

48查询学生信息不全(地址或者email 为空)的学生中谁参加了考试，显示姓名，科目名和成绩

select studentname,subjectname,score from student s join result r on s.studentno=r.studentno join subject sub on sub.subjectid=r.subjectid where address is null or email is null;

50查询电话是135开头的学生考试平均分的前3名的姓名,电话,平均分,学生当前所处的阶段名

select studentname,phone,avg(score),gradename from student s join grade g on g.gradeid=s.gradeid join result r on r.studentno=s.studentno where phone like “135%” group by s.studentno order by avg(score) desc limit 0,3;