超详细思路讲解SQL语句的查询实现，及数据的创建。

     最近一直在看数据库方面的问题，总结了一下SQL语句，这是部分详细的SQL问题，思路讲解:

第一步：创建数据库表，及插入数据信息
复制代码
1 --Student(S#,Sname,Sage,Ssex) 学生表
2 CREATE TABLE student(
3 sno VARCHAR2(5) PRIMARY KEY,
4 sname VARCHAR2(30) NOT NULL,
5 sage NUMBER(3),
6 ssex VARCHAR2(5)
7 );
　　
8 INSERT INTO student(sno,sname,sage,ssex) VALUES(‘001’,‘张三’,‘20’,‘男’);
9 INSERT INTO student(sno,sname,sage,ssex) VALUES(‘002’,‘李四’,‘21’,‘女’);
10 INSERT INTO student(sno,sname,sage,ssex) VALUES(‘003’,‘王五’,‘20’,‘男’);
11 INSERT INTO student(sno,sname,sage,ssex) VALUES(‘004’,‘王八’,‘20’,‘男’);
12 COMMIT;
13 SELECT * FROM student;
14
15 --Teacher(T#,Tname) 教师表
16 CREATE TABLE teacher(
17 tno VARCHAR2(5) PRIMARY KEY,
18 tname VARCHAR2(30) NOT NULL
19 );
20 INSERT INTO teacher VALUES(‘001’,‘孔老师’);
21 INSERT INTO teacher VALUES(‘002’,‘李老师1’);
22 INSERT INTO teacher VALUES(‘003’,‘李老师2’);
23
24 --提交
25 COMMIT;
26
27 SELECT * FROM teacher;
28
29 --Course(C#,Cname,T#) 课程表
30
31 CREATE TABLE course(
32 cno VARCHAR2(5) PRIMARY KEY,
33 cname VARCHAR2(30) NOT NULL,
34 tno VARCHAR2(5)
35 );
36 --创立外键关系
37 ALTER TABLE course
38 ADD CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno);
39
40 INSERT INTO course VALUES(‘001’,‘JAVA面向对象’,‘001’);
41 INSERT INTO course VALUES(‘002’,‘JSP/SERVLET网站开发’,‘001’);
42 – INSERT INTO course VALUES(‘003’,‘Oracle数据库’,‘001’);
43
44 INSERT INTO course VALUES(‘003’,‘JAVA基础’,‘002’);
45 INSERT INTO course VALUES(‘004’,‘C#开发’,‘002’);
46 INSERT INTO course VALUES(‘005’,‘数据库基础’,‘002’);
47
48 SELECT * FROM course;
49
50 COMMIT;
51
52 --SC(S#,C#,score) 成绩表
53
54 CREATE TABLE sc(
55 sno VARCHAR2(5) NOT NULL,
56 cno VARCHAR2(5) NOT NULL,
57 score NUMBER(4,1) NOT NULL
58 );
59
60 --创立外键关系
61 ALTER TABLE sc
62 ADD CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno);
63
64 --创立外键关系
65 ALTER TABLE sc
66 ADD CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno);
67
68 --创立外键关系
69 ALTER TABLE sc
70 ADD CONSTRAINT fk_sno_cno PRIMARY KEY(sno,cno);
71
72 INSERT INTO sc VALUES(‘001’,‘001’,‘90’);
73 INSERT INTO sc VALUES(‘001’,‘002’,‘85’);
74 INSERT INTO sc VALUES(‘001’,‘003’,‘80’);
75
76 INSERT INTO sc VALUES(‘002’,‘001’,‘95’);
77 INSERT INTO sc VALUES(‘002’,‘002’,‘87’);
78 INSERT INTO sc VALUES(‘002’,‘003’,‘78’);
79
80
81 INSERT INTO sc VALUES(‘003’,‘002’,‘86’);
82 INSERT INTO sc VALUES(‘003’,‘003’,‘74’);
83
84 --提交
85 COMMIT;
86
87 select * from sc; --成绩表
88 select * from Teacher; --教师表
89 select * from Course; --课程表
90 select * from Student; --学生表
复制代码
Student(SNO,Sname,Sage,Ssex) 学生表
Course(CNO,Cname,TNO) 课程表
SC(SNO,CNO,score) 成绩表
Teacher(TNO,Tname) 教师表

问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；
思路：先查出001课程的学生成绩，在查出002课程的学生成绩，
使用内联的方式查询

复制代码
1 SELECT A.sno ,
2 A.score,B.score
3 FROM
4 (SELECT * FROM sc WHERE cno=‘001’) A
5 INNER OIN
6 (SELECT * FROM sc WHERE cno=‘002’) B
7 ON A.sno=B.sno
8 WHERE A.score>B.score;
复制代码

2 、查询平均成绩大于85分的同学的学号和平均成绩；
思路：使用学生分组的形式

1 SELECT SNO,AVG(SCORE) FROM SC GROUP BY SNO HAVING (AVG(SCORE )>85);

3 、查询所有同学的学号、姓名、选课数、总成绩；
思路：先把学生编号，计算选课数、总成绩 分组查出来
再使用左联接的方式把要查的数据查出
知识点补充：
–INNER JOIN 是查询两张表之间共同拥有的部分的数据
–LEFT JOIN 是以左边的数据表为基准先查，而右边的表相关的数据查询出来填充左边的表数据中，如果没有用null填充
–RIGHT JOIN 是以右边的数据表为基准先查，而左边的表相关的数据查询出来填充右边的表数据中，如果没有用null填充
复制代码
1 SELECT S.SNO,S.SNAME,A.C_NO,A.S_SCORE
2 FROM STUDENT S
3 LEFT JOIN
4 (SELECT SNO ,COUNT(CNO) AS C_NO,SUM(SCORE) AS S_SCORE FROM SC GROUP BY SNO) A
5 ON S.SNO=A.SNO
6
复制代码
4 、查询姓“李”的老师的个数；
思路：使用模糊查询的方法
1 SELECT COUNT(TNAME) FROM TEACHER WHERE TNAME LIKE ‘李%’;
5 、查询没学过孔老师课的同学的学号、姓名；
思路：使用逆向思维把学过孔老师课的学生查出来（两表之间用内联，也可用子查询）
根据学生学的课程，查询所学的科目里没有孔老师所教的科目
知识补充：DISTINCT 查询唯一的不重复的列
复制代码
1 --方式一：
2 SELECT SNO,SNAME FROM STUDENT WHERE SNO NOT IN(
3
4 SELECT DISTINCT SNO FROM SC WHERE CNO IN(
5
6 SELECT C.CNO FROM COURSE C INNER JOIN TEACHER T ON C.TNO=T.TNO WHERE TNAME=‘孔老师’)
7 )
8
9 --方式二：
10 SELECT SNO,SNAME FROM student WHERE not exists(
11 SELECT 1 FROM sc WHERE exists (
12 SELECT 1
13 FROM Course c INNER JOIN Teacher t ON c.tno=t.tno
14 WHERE t.tname=‘孔老师’ and sc.cno=c.cno
15
16 )
17 and student.sno= sc.sno
18 )
复制代码
6 、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
思路：先根据成绩表查出学过001课程的同学
再查出学过002课程的同学
再两个表内联查询都学过的部分
复制代码
1 SELECT SNO ,SNAME FROM STUDENT S WHERE SNO IN(
2 SELECT A.SNO FROM
3 ( SELECT SNO FROM SC WHERE CNO =‘001’) A
4 INNER JOIN
5 ( SELECT SNO FROM SC WHERE CNO =‘002’) B
6 ON A.SNO=B.SNO
7 )
复制代码
7 、查询学过“孔老师”所教的所有课的同学的学号、姓名；
思路：先查处孔老师教过哪些课程,教了几门课程
再看看哪些学生学过孔老师的课程（三表联查）
复制代码
1 SELECT SNO,SNAME FROM STUDENT WHERE SNO IN (
2 SELECT A.SNO FROM
3 ( SELECT SC.SNO ,SC.CNO FROM SC
4 INNER JOIN COURSE C ON SC.CNO=C.CNO
5 INNER JOIN TEACHER T ON C.TNO=T.TNO
6 WHERE T.TNAME=‘孔老师’
7 )A
8 GROUP BY A.SNO HAVING (COUNT(1)=
9 (SELECT COUNT(CNO) FROM COURSE C
10 INNER JOIN
11 TEACHER T ON C.TNO=T.TNO
12 WHERE TNAME=‘孔老师’
13 )
14 )
15 )
16
复制代码
8 、查询所有课程成绩小于90分的同学的学号、姓名；
思路：先查出同学所有科目的最高分
如果最高分小于90分的话，就查出该学生的学号和姓名
1 SELECT SNO ,SNAME FROM STUDENT WHERE SNO= (
2 SELECT SNO, MAX(SCORE) FROM SC GROUP BY SNO HAVING (MAX(SCORE)<90));
9 、查询各科成绩最高和最低的分：形式显示：课程编号，最高分，最低分
思路：使用函数查询，并根据学生编号来分组
1 SELECT CNO,MAX(SCORE) MAX_SC,MIN(SCORE) MIN_SC FROM SC GROUP BY CNO;
10 、查询每门课成绩最好的前两名
思路：
先查出每门课的成绩进行排序
在根据课程来排序
知识补充：DENSE_RANK（），名词排序，如果同名词的，则依次排列

1 SELECT * FROM (
2 SELECT SNO,CNO ,SCORE, DENSE_RANK() OVER(PARTITION BY CNO ORDER BY SCORE DESC ) AS DRK FROM SC
3 )
4 WHERE DRK<=2 ORDER BY CNO,DRK;