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

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

• 第一步：创建数据库表，及插入数据信息

• --Student(S#,Sname,Sage,Ssex) 学生表
  CREATE TABLE student(
  sno VARCHAR2(5) PRIMARY KEY,
  sname VARCHAR2(30) NOT NULL,
  sage NUMBER(3),
  ssex VARCHAR2(5)
  );
  
  INSERT INTO student(sno,sname,sage,ssex) VALUES('','张三','','男');
  INSERT INTO student(sno,sname,sage,ssex) VALUES('','李四','','女');
  INSERT INTO student(sno,sname,sage,ssex) VALUES('','王五','','男');
  INSERT INTO student(sno,sname,sage,ssex) VALUES('','王八','','男');
  COMMIT;
  SELECT * FROM student; --Teacher(T#,Tname) 教师表
  CREATE TABLE teacher(
  tno VARCHAR2(5) PRIMARY KEY,
  tname VARCHAR2(30) NOT NULL
  );
  INSERT INTO teacher VALUES('','孔老师');
  INSERT INTO teacher VALUES('','李老师1');
  INSERT INTO teacher VALUES('','李老师2'); --提交
  COMMIT; SELECT * FROM teacher; --Course(C#,Cname,T#) 课程表 CREATE TABLE course(
  cno VARCHAR2(5) PRIMARY KEY,
  cname VARCHAR2(30) NOT NULL,
  tno VARCHAR2(5)
  );
  --创立外键关系
  ALTER TABLE course
  ADD CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno); INSERT INTO course VALUES('','JAVA面向对象','');
  INSERT INTO course VALUES('','JSP/SERVLET网站开发','');
  -- INSERT INTO course VALUES('003','Oracle数据库','001'); INSERT INTO course VALUES('','JAVA基础','');
  INSERT INTO course VALUES('','C#开发','');
  INSERT INTO course VALUES('','数据库基础',''); SELECT * FROM course; COMMIT; --SC(S#,C#,score) 成绩表 CREATE TABLE sc(
  sno VARCHAR2(5) NOT NULL,
  cno VARCHAR2(5) NOT NULL,
  score NUMBER(4,1) NOT NULL
  ); --创立外键关系
  ALTER TABLE sc
  ADD CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno); --创立外键关系
  ALTER TABLE sc
  ADD CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno); --创立外键关系
  ALTER TABLE sc
  ADD CONSTRAINT fk_sno_cno PRIMARY KEY(sno,cno); INSERT INTO sc VALUES('','','');
  INSERT INTO sc VALUES('','','');
  INSERT INTO sc VALUES('','',''); INSERT INTO sc VALUES('','','');
  INSERT INTO sc VALUES('','','');
  INSERT INTO sc VALUES('','',''); INSERT INTO sc VALUES('','','');
  INSERT INTO sc VALUES('','',''); --提交
  COMMIT; select * from sc; --成绩表
  select * from Teacher; --教师表
  select * from Course; --课程表
  select * from Student; --学生表

• Student(SNO,Sname,Sage,Ssex) 学生表
• Course(CNO,Cname,TNO) 课程表
• SC(SNO,CNO,score) 成绩表
• Teacher(TNO,Tname) 教师表
• • 问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；
  • 思路：先查出001课程的学生成绩，在查出002课程的学生成绩，
  • 使用内联的方式查询

• • SELECT A.sno ,
    A.score,B.score
    FROM
    (SELECT * FROM sc WHERE cno='') A
    INNER OIN
    (SELECT * FROM sc WHERE cno='') B
    ON A.sno=B.sno
    WHERE A.score>B.score;

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

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

• •   3 、查询所有同学的学号、姓名、选课数、总成绩；
  • 思路：先把学生编号，计算选课数、总成绩  分组查出来
  • 再使用左联接的方式把要查的数据查出
  • 知识点补充：
  • --INNER JOIN 是查询两张表之间共同拥有的部分的数据
  • --LEFT JOIN  是以左边的数据表为基准先查，而右边的表相关的数据查询出来填充左边的表数据中，如果没有用null填充
  • --RIGHT JOIN   是以右边的数据表为基准先查，而左边的表相关的数据查询出来填充右边的表数据中，如果没有用null填充

  • SELECT S.SNO,S.SNAME,A.C_NO,A.S_SCORE
    FROM STUDENT S
    LEFT JOIN
    (SELECT SNO ,COUNT(CNO) AS C_NO,SUM(SCORE) AS S_SCORE FROM SC GROUP BY SNO) A
    ON S.SNO=A.SNO

  •   4 、查询姓“李”的老师的个数；
  • 思路：使用模糊查询的方法
  • SELECT COUNT(TNAME) FROM TEACHER WHERE TNAME LIKE '李%';
  •   5 、查询没学过孔老师课的同学的学号、姓名；
  • 思路：使用逆向思维把学过孔老师课的学生查出来（两表之间用内联，也可用子查询）
  • 根据学生学的课程，查询所学的科目里没有孔老师所教的科目
  • 知识补充：DISTINCT 查询唯一的不重复的列

  • --方式一：
    SELECT SNO,SNAME FROM STUDENT WHERE SNO NOT IN( SELECT DISTINCT SNO FROM SC WHERE CNO IN( SELECT C.CNO FROM COURSE C INNER JOIN TEACHER T ON C.TNO=T.TNO WHERE TNAME='孔老师')
    ) --方式二：
    SELECT SNO,SNAME FROM student WHERE not exists(
    SELECT 1 FROM sc WHERE exists (
    SELECT 1
    FROM Course c INNER JOIN Teacher t ON c.tno=t.tno
    WHERE t.tname='孔老师' and sc.cno=c.cno )
    and student.sno= sc.sno
    )     

  •    6 、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
  • 思路：先根据成绩表查出学过001课程的同学
  • 再查出学过002课程的同学
  • 再两个表内联查询都学过的部分

  • SELECT SNO ,SNAME FROM STUDENT S WHERE SNO IN(
    SELECT A.SNO FROM
    ( SELECT SNO FROM SC WHERE CNO ='') A
    INNER JOIN
    ( SELECT SNO FROM SC WHERE CNO ='') B
    ON A.SNO=B.SNO
    )

  •   7 、查询学过“孔老师”所教的所有课的同学的学号、姓名；
  • 思路：先查处孔老师教过哪些课程,教了几门课程
  • 再看看哪些学生学过孔老师的课程（三表联查）

  • SELECT SNO,SNAME FROM STUDENT WHERE SNO IN (
    SELECT A.SNO FROM
    ( SELECT SC.SNO ,SC.CNO FROM SC
    INNER JOIN COURSE C ON SC.CNO=C.CNO
    INNER JOIN TEACHER T ON C.TNO=T.TNO
    WHERE T.TNAME='孔老师'
    )A
    GROUP BY A.SNO HAVING (COUNT(1)=
    (SELECT COUNT(CNO) FROM COURSE C
    INNER JOIN
    TEACHER T ON C.TNO=T.TNO
    WHERE TNAME='孔老师'
    )
    )
    )

  •     8 、查询所有课程成绩小于90分的同学的学号、姓名；
  • 思路：先查出同学所有科目的最高分
  • 如果最高分小于90分的话，就查出该学生的学号和姓名

  • SELECT SNO ,SNAME FROM STUDENT WHERE SNO= (
    SELECT SNO, MAX(SCORE) FROM SC GROUP BY SNO HAVING (MAX(SCORE)<90));

  • 9 、查询各科成绩最高和最低的分：形式显示：课程编号，最高分，最低分
  • 思路：使用函数查询，并根据学生编号来分组
  • SELECT CNO,MAX(SCORE) MAX_SC,MIN(SCORE) MIN_SC FROM SC GROUP BY CNO;
  •     10 、查询每门课成绩最好的前两名
  • 思路：
  • 先查出每门课的成绩进行排序
  • 在根据课程来排序
  • 知识补充：DENSE_RANK（），名词排序，如果同名词的，则依次排列

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