文章目录
1、简单查询
(1)各位学生的学号、班级、姓名;
(2)所有课程信息;
(3)所有的专业班级;
(4)学时数大于60的课程信息。
SELECT SNO,CLASS,SNAME FROM STU --各位学生的学号、班级、姓名
SELECT * FROM COU --所有课程信息
SELECT DISTINCT CLASS FROM STU --所有的专业班级
SELECT * FROM COU WHERE CHOUR>60 --学时数大于60的课程信息
2、查询在1999年出生的学生学号、姓名、出生日期
SELECT SNO,SNAME,BIRTH
FROM STU
WHERE SUBSTRING(BIRTH,1,4)=1999
SELECT * FROM STU
3、三次作业成绩都在60分以上的学生学号、课程号
SELECT H_SNO,H_CNO
FROM HOMEWORKS
WHERE (H_SCORE_1>60 AND H_SCORE_2>60 AND H_SCORE_3>60)
SELECT * FROM HOMEWORKS
4、查询姓张学生学号、姓名和专业班级
SELECT SNO,SNAME,CLASS
FROM STU
WHERE SUBSTRING(SNAME,1,1)='张'
SELECT * FROM STU
5、查询03级的男生信息
--方法1
SELECT SUBSTRING(CLASS,3,2) FROM STU
--方法2
SELECT * FROM STU WHERE (SUBSTRING(CLASS,3,2)='03' AND SEX='男')
--方法3
SELECT * FROM STU WHERE (CLASS LIKE '__03' AND SEX='男')
--原表
SELECT * FROM STU
6、查询没有作业成绩的学号和课程号
SELECT H_SNO,H_CNO
FROM HOMEWORKS
WHERE (H_SCORE_1 is NULL OR H_SCORE_2 is NULL OR H_SCORE_3 is NULL)
SELECT * FROM HOMEWORKS
7、查询学号为0444的学生作业1总分
SELECT SUM(H_SCORE_1)
FROM HOMEWORKS
WHERE H_SNO='0444'
SELECT * FROM HOMEWORKS
8、查询选修了K422课程的学生人数
SELECT COUNT(H_SNO) AS '选修了K422的人数'
FROM HOMEWORKS
WHERE H_CNO='K422'
SELECT * FROM HOMEWORKS
9、查询数据库中共有多少个班级
SELECT COUNT(CLASS) FROM STU
SELECT * FROM STU
10、查询选修三门以上(含)课程的学生学号、作业1平均分、作业2平均分、作业3平均分
核心要点:按人来分组(分类),再去计算每个人所选修的课程数量。
(1)错误示范
SELECT H_SNO,AVG(H_SCORE_1),AVG(H_SCORE_2),AVG(H_SCORE_3)
FROM HOMEWORKS
WHERE COUNT(H_CNO)>=3
GROUP BY HOMEWORKS.H_SNO
因为此处需要计算课程数量大于3,而WHERE中又无法使用聚合函数COUNT(),因此必须进行分组,再使用HAVING语句进行条件判断。
(2)正确结果
SELECT H_SNO,AVG(H_SCORE_1),AVG(H_SCORE_2),AVG(H_SCORE_3)
FROM HOMEWORKS
GROUP BY HOMEWORKS.H_SNO
HAVING (COUNT(H_CNO)>=3)
SELECT * FROM HOMEWORKS
11、查询企鹅的选课信息,列出学号、姓名、课程名
SELECT STU.SNO,STU.SNAME,COU.C_COURSE_NAME
FROM STU,COU,HOMEWORKS
WHERE (STU.SNO=HOMEWORKS.H_SNO AND COU.CNO=HOMEWORKS.H_CNO AND STU.SNAME='企鹅')
SELECT * FROM STU
核心要点:需要用“.”运算来取不同表中的字段。