之前看到斌哥正在做老师发的数据库题目,开玩笑地说了一句:“这不是很简单吗???”,然而我前天开始做,就被打脸了,哪有我想象的那么简单啊QAQ!!
间断地搞了一两天,终于把几个查询搞定了,现在应该没有什么太大的问题了!!
表结构如下:
Student(S,Sname,Sage,Ssex) 学生表
Course(C,Cname,TNO) 课程表
SC(S,C,score) 成绩表
Teacher(T,Tname) 教师表
1.建表过程
不再赘述,很简单,注意几个表之间的连接就行了!!
2.插入数据
Student表
Teacher表
Course表
SC成绩表
3.查询数据
1.查询平均成绩大于60分的同学的学号和平均成绩
先按照成绩表里的学生编号S进行分组,然后判断每个组成绩平均值是否大于60即可。
SELECT SC.S, AVG(score)
FROM SC
GROUP BY SC.S
HAVING AVG(score) > 60;
效果如下:
2.查询所有同学的学号、姓名、选课数、总成绩
在Student和SC两个表中一起查找,分组即可。
select Student.S, Sname, COUNT(SC.C), SUM(score)
FROM Student, SC
WHERE Student.S = SC.S
GROUP BY SC.S;
效果如下:
3.查询姓“李”的老师的个数
直接在Teacher表里查找,姓“李”使用 % 标识符
select COUNT(*) FROM Teacher
WHERE Tname LIKE '李%';
效果如下:
4.查询没学过“叶平”老师课的同学的学号、姓名
这个比较复杂,过程如下:
首先在Teacher里查询叶平老师的编号
select T FROM Teacher WHERE Tname = '叶平';
然后在Course里查询叶平老师教的课的编号
select Course.C FROM Course WHERE Course.T = (SELECT Teacher.T FROM Teacher WHERE Tname = '叶平');
再在SC表中查找选了叶平老师课的同学
SELECT distinct SC.S FROM SC WHERE SC.C = (SELECT Course.C FROM Course WHERE Course.T= (SELECT Teacher.T FROM Teacher WHERE Tname='叶平'));
最后再在Student表中查找没有选叶平老师课的同学的信息,用到NOT IN
select Student.S ,Sname FROM Student WHERE Student.S NOT IN (SELECT distinct SC.S FROM SC WHERE SC.C = (SELECT Course.C FROM Course WHERE Course.T= (SELECT Teacher.T FROM Teacher WHERE Tname='叶平')));
上面的就是最终代码
5.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
直接在Student表和SC表直接查询,首先对应Student.S和SC.S,然后再限制一下SC.C = 001,为学过001课程的同学,最后再限制一下学过002的同学即可。
SELECT Student.S, Sname
FROM Student, SC
WHERE Student.S = SC.S
AND SC.C = 001
AND EXISTS
(SELECT * FROM SC AS SC2 WHERE SC2.S = SC.S AND SC2.C = 002);
6.查询课程编号002的成绩比编号001成绩低的所有同学的学号、姓名
使用两个子查询,分别查询课程编号为002的成绩和课程编号为001的成绩,然后比较即可。
SELECT DISTINCT Student.S, Sname
FROM Student, SC
WHERE
Student.S = SC.S
AND
(SELECT score FROM SC AS SC2 WHERE Student.S = SC2.S AND SC2.C = 001)
>
(SELECT score FROM SC AS SC3 WHERE Student.S = SC3.S AND SC3.C = 002);
7.查询所有课程小于60分的同学的学号、姓名
直接在Student表和SC表中查询,直接判断是否有不存在大于等于60的同学即可,这里用到NOT EXISTS
SELECT DISTINCT Student.S, Sname
FROM Student, SC
WHERE
Student.S = SC.S
AND NOT EXISTS
(SELECT * FROM SC AS SC2 WHERE SC2.S = SC.S AND score >= 60);
8.查询没有学全所有课的同学的学号、姓名
首先把所有课程数记录查询出来,这代表所有课程的数目
SELECT COUNT(*) FROM Course;
然后再在Student表和SC表中查询课程数小于总课程数的学生信息
SELECT DISTINCT Student.S, Sname FROM Student, SC WHERE Student.S = SC.S AND (SELECT COUNT(*) FROM SC AS SC2 WHERE SC2.S = Student.S) < (SELECT COUNT(*) FROM Course);
基本查询的内容就是这些,不是很简单,但是多敲多想就会找到套路。一般就是子查询比较复杂。