数据库查询

1、 查询全部一年级的学生信息。
select * from student where gradeid=1;
select gradeid from grade where gradename='一年级';
2、 查询全部二年级的学生的姓名和电话。
select studentname,phone from student where gradeid=(select gradeid from grade where gradename='二年级');

select gradeid from grade where gradename='二年级';
3、 查询全部一年级女同学的信息。

select * from student where sex = '女' AND gradeid=1;
select gradeid from grade where   gradename='一年级';

4、 查询课时超过60的科目信息。
select * from subjects where classhour>60;
5、 查询二年级的科目名称
select * from subjects where gradeid=(select gradeid from grade where gradename='二年级');
6、 查询二年级男同学的姓名和住址。

select studentname,address from student where sex = '男' AND gradeid=2;
select gradeid from grade where   gradename='二年级';

7、 查询无电子邮件的学生姓名和年级信息。
select * from grade;

 select s.studentname,s.gradeid  , (select  gradename from grade where grade.gradeid=s.gradeid ) gname
  from student s where email is null or email='';
8、 查询出生日期在1992年之后的男学生姓名和年级信息。
select s.studentname, (select gradename from grade where grade.gradeid=s.gradeid) gname from student s where born>='1992-12-31' and sex='男';

select version();
9、 参加了日期为2015年7月3日的“计算机基本原理”科目考试的成绩信息

select studentscore from result where examdate='2015-7-3' and subjectid=(select subjectid from subjects where subjectname='计算机基本原理');

10、 按照出生日期查询一年级的学生信息。
select * from student where gradeid = (select gradeid from grade where gradename='一年级') order by born;
11、 按成绩由高到低的次序查询参加编号为1的科目考试信息。
select * from result where subjectid=1 order by studentscore desc;
12、 查询2015年7月1日参加“MySQL深入”考试的前2名学员成绩信息。
select * from result where examdate='2015-7-1' and subjectid=(select subjectid from subjects  where subjectname='MySQL深入') order by studentscore desc limit 2;
select subjectid from subjects  where subjectname='MySQL深入';
13、 查询课时最多的科目名称及课时。
select max(classhour) from subjects;
select * from subjects where classhour=(select max(classhour) from subjects);
14、 查询年龄最小的学生所在的年级及姓名。
select max(born) from student;

select s.*, (select gradename from grade where grade.gradeid=s.gradeid) gname from student s where born=(select max(born) from student);

select * from student , grade  where born=(select max(born) from student) and student.gradeid = grade.gradeid;

15、 查询考试的最低分出现在哪个科目
SELECT subjectname,studentscore
FROM subjects,result
WHERE subjects.subjectid = result.subjectid AND studentscore = (SELECT MIN(studentscore)FROM result WHERE studentscore != 0);
16、 查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。
select *
from subjects,result
where  result.subjectid = subjects.subjectid AND result.studentno = 's1001' ORDER BY examdate DESC;
17、 查询年龄超过25周岁的学生信息。
SELECT *
FROM student
WHERE 2023 - born > 25;
18、 查询1月份过生日的学生信息

select * from student where bron like '%-01-%';

19、 查询今天过生日的学生姓名及所在年级。

20、 新生入学，为其分配一个Email地址，规则如下：S1+当前日期 [+@bd.com](mailto:+@bd.com)

21、 查询住址为“雁塔区”的学生姓名、电话、住址
select * from student where address like '%雁塔区%';
22、 查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。
select * from subjects ,grade  where subjects.gradeid=grade.gradeid and  subjectname like '%计算机%' order by subjects.gradeid desc ;

23、 查询电话中含有以“130”开头的学生姓名，住址和电话。

select studentname,address,phone from student where phone like '130%' ;
 

24、 查询姓“赵”的学号、姓名和住址。

select studentno,studentname,address from student where studentname like '赵%';

25、 统计一年级女生的总人数。
select count(1) from student where sex='女' and gradeid = (select gradeid from grade where gradename='一年级');
26、 查询李四总成绩
select studentno from student where studentname ='李四';
select studentno,sum(studentscore) from  result where studentno in (select studentno from student where studentname ='李四' ) group by studentno;

27、 学号为s1003的学生所有课总成绩

select sum(studentscore) from result where studentno ='s1003';

28、 学号为s1003的学生考试的平均分。
select sum(studentscore),avg(studentscore) from result where studentno='s1003';
29、 查询一年级的科目“Mysql”的最高分、最低分、平均分。
select max(studentscore),min(studentscore),avg(studentscore),subjectid, (select subjectname from subjects where subjects.subjectid=result.subjectid) subjectname from result where subjectid in (select subjectid from subjects where subjectname ='MySQL深入' and gradeid=(select gradeid from grade where gradename='一年级')) group by subjectid;

select subjectid from subjects where subjectname ='MySQL深入' and gradeid=(select gradeid from grade where gradename='一年级');

30、 查询每个年级的总学时数，并按照升序排列。
select gradeid, (select gradename from grade where grade.gradeid=subjects.gradeid) gradename ,sum(classhour )from subjects group by gradeid order by sum(classhour);
31、 查询每个参加考试的学员的平均分。（Group by 学号）
SELECT studentno,(SELECT studentname FROM student WHERE student.studentno = result.studentno) stuname,AVG(studentscore) from result GROUP BY studentno; 
32、 查询每门课程的平均分，并按照降序排列。（group by 课程）
select subjectid,avg(studentscore) from result group by subjectid order by avg(studentscore) desc;
33、 查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）

select studentno,sum(studentscore) from result group by studentno order by sum(studentscore) desc;

-- 34、 查询一年级的平均年龄。

select gradeid from subjects where subjectname = '一年级'; 
select avg(now() - born) from student where gradeid = (select gradeid from subjects where subjectname = '一年级');

-- 35、 查询每个年级西安地区的学生人数。
select gradeid,count(1) from student where address like '%西安%' group by gradeid;

-- 36、 查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
select studentno,avg(studentscore) from result group by studentno having avg(studentscore)>=60 order by avg(studentscore) desc;
-- 36、 查询参加2015-7-1 考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列
select studentno,avg(studentscore) from result where examdate='2015-7-1' group by studentno having avg(studentscore)>=60 order by avg(studentscore) desc;
-- 37、 查询参加考试至少liangci次考试不及格的学生学号、姓名、不及格次数。
select studentno,(select studentname from student where student.studentno=result.studentno) stuname,count(1) from result where studentscore<60 group by studentno having count(1)>=2;

-- 38、 查询学生姓名、所属年级名称及联系电话。

select s.studentname, (select g.gradename from grade g where g.gradeid=s.gradeid) ,s.phone from student s;

-- 39、 查询年级编号为1的科目名称、年级名称及学时。

select s.subjectname, (select g.gradename from grade g where g.gradeid=s.gradeid) , s.classhour from subjects s where gradeid=1;

-- 40、 查询参加科目编号为1的考试的学生姓名、分数、考试日期。
select * from result where subjectid=1;

-- 41、 查询学号为s1001的学生参加的考试科目名称、分数、考试日期。

select  (select s.subjectname from subjects s where  s.subjectid=r.subjectid ) ,r.studentresult,r.examdate from result r where studentno='s1001';

-- 42、 查询所有科目的参考信息（某些科目可能还没有被考试过）
select * from subjects left join result on subjects.subjectid=result.subjectid;

-- 43、 查询没有被考过的科目信息。
select * from subjects left join result on subjects.subjectid=result.subjectid where result.subjectid is null;
select * from subjects where subjectid not in (select distinct subjectid from result);