MySQL查询练习（子查询）

1、查询全部一年级的学生信息 

SELECT * FROM student WHERE gradeid = (SELECT gradeid FROM grade WHERE gradename = '一年级');

2、查询全部二年级的学生的姓名和电话。

SELECT studentname , phone FROM student WHERE gradeid = (SELECT gradeid FROM grade WHERE gradename = '二年级');

3、 查询全部一年级女同学的信息。

SELECT * FROM student WHERE gradeid = (SELECT gradeid FROM grade WHERE gradename = '一年级') AND sex = '女';

4、 查询课时超过60的科目信息。

SELECT * FROM subjects WHERE classhour > 60;

5、 查询二年级的科目名称。

SELECT subjectname FROM subjects WHERE gradeid = (SELECT gradeid FROM grade WHERE gradename = '二年级');

6、 查询二年级男同学的姓名和住址。

SELECT studentname , address FROM student WHERE gradeid = (SELECT gradeid FROM grade WHERE gradename = '二年级') AND sex = '男';

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

SELECT studentname , (SELECT gradename FROM grade WHERE student.gradeid = grade.gradeid) gradename 
FROM student WHERE email IS NULL OR email = '';

8、 查询出生日期在1992年之后的男学生姓名和年级信息。

SELECT studentname , (SELECT gradename FROM grade WHERE student.gradeid = grade.gradeid) gradename
FROM student WHERE born > '1992-12-31' AND sex = '男';

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

SELECT * FROM result WHERE subjectid = (SELECT subjectid FROM subjects WHERE subjectname = '计算机基本原理') 
AND examdate = '2015-07-03';

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-07-01' 
AND subjectid = (SELECT subjectid FROM subjects WHERE subjectname = 'MySQL深入') ORDER BY studentscore DESC LIMIT 0 , 2;

13、 查询课时最多的科目名称及课时。

SELECT subjectname , MAX(classhour) classhour FROM subjects; 

14、 查询年龄最小的学生所在的年级及姓名。

SELECT studentname , (SELECT gradename FROM grade WHERE student.gradeid = grade.gradeid) 
gradename FROM student WHERE born = (SELECT MAX(born) FROM student);

15、 查询考试的最低分出现在哪个科目。

SELECT DISTINCT (SELECT subjectname FROM subjects WHERE subjects.subjectid = result.subjectid) subjectname FROM result
 WHERE studentscore = (SELECT MIN(studentscore) FROM result); 

16、 查询学号为“s1001”的学生参加过的所有考试信息，并按照时间前后次序显示。

 SELECT * FROM result WHERE studentno = 's1001' ORDER BY examdate;

17、 查询年龄超过25周岁的学生信息。

 SELECT * FROM student WHERE born < '1998-3-28';

18、 查询1月份过生日的学生信息。

 SELECT * FROM student WHERE born LIKE '%-01-%';

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

 SELECT studentname , (SELECT gradename FROM grade WHERE 
 student.gradeid = grade.gradeid) studentname FROM student WHERE born = '%-3-28';

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

21、 查询住址为“雁塔区”的学生姓名、电话、住址。

 SELECT studentname , phone , address FROM student WHERE address LIKE '%雁塔区%';

22、 查询名称中含有“计算机”字样科目名称、学时及所属年级，并按年级由低到高显示。

 SELECT subjectname , classhour , (SELECT gradename FROM grade WHERE subjects.gradeid = grade.gradeid) 
 gradename FROM subjects WHERE subjectname LIKE '%计算机%' ORDER BY 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 gradeid = (SELECT gradeid FROM grade WHERE gradename = '一年级') AND sex = '女';

26、 查询李四总成绩。

 SELECT 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 AVG(studentscore) FROM result WHERE studentno = 's1003';

29、 查询一年级的科目“Mysql”的最高分、最低分、平均分。

 SELECT MAX(studentscore) , MIN(studentscore) , AVG(studentscore) FROM result WHERE subjectid = 
 (SELECT subjectid FROM subjects WHERE subjectname LIKE 'MySQL%' AND 
 gradeid = (SELECT gradeid FROM grade WHERE gradename = '一年级'));

30、 查询每个年级的总学时数，并按照升序排列。

 SELECT SUM(classhour) FROM subjects GROUP BY gradeid ORDER BY classhour;

31、 查询每个参加考试的学员的平均分。（Group by 学号）

 SELECT AVG(studentscore) FROM result GROUP BY studentno;

32、 查询每门课程的平均分，并按照降序排列。（group by 课程）

 SELECT AVG(studentscore) FROM result GROUP BY subjectid ORDER BY AVG(studentscore) DESC;

33、 查询每个学生参加的所有考试的总分，并按照降序排列。（group by 学号）

 SELECT SUM(studentscore) FROM result GROUP BY studentno ORDER BY SUM(studentscore) DESC;

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

35、 查询每个年级西安地区的学生人数。

 SELECT COUNT(1) FROM student WHERE address LIKE '%西安%' GROUP BY gradeid;

36、 查询参加考试的学生中，平均分及格的学生记录，并按照平均成绩降序排列。

 SELECT * FROM result GROUP BY studentno HAVING AVG(studentscore) >= 60 ORDER BY AVG(studentscore) DESC;

37、 查询参加考试至少一次考试不及格的学生学号、姓名、不及格次数。

 SELECT studentno , (SELECT student.studentname FROM student WHERE result.studentno = student.studentno) studentname , COUNT(1) 
 FROM result WHERE studentscore < 60 GROUP BY studentno HAVING COUNT(1) > 1;

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

 SELECT studentname , phone , gradename FROM student INNER JOIN grade ON student.gradeid = grade.gradeid;

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

 SELECT subjectname , (SELECT gradename FROM grade WHERE subjects.gradeid = grade.gradeid) gradename , classhour 
 FROM subjects WHERE gradeid = 1;

40、 查询参加科目编号为1的考试的学生姓名、分数、考试日期。

 SELECT (SELECT studentname FROM student WHERE student.studentno = result.studentno)
  studentname , studentscore , examdate FROM result WHERE subjectid = 1;

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

  SELECT (SELECT subjectname FROM subjects WHERE subjects.subjectid = result.subjectid) subjectname , studentscore , examdate
   FROM result WHERE studentno = 's1001';

42、 查询所有科目的参考信息（某些科目可能还没有被考试过）。

 SELECT result.* , (SELECT subjectname FROM subjects WHERE subjects.subjectid = result.subjectid) subjectname FROM result; 

43、 查询没有被考过的科目信息。 

 SELECT * FROM subjects WHERE subjectid NOT IN (SELECT DISTINCT subjectid FROM result);