超详细思路讲解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;
-
