学生表 课程表 成绩表 教师表常用SQL语句

时间：2023-02-09 23:22:36

学生表 课程表 成绩表 教师表 50个常用sql语句

建表

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

---- If database exists the same name datatable deletes it.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Student') DROP TABLE Student;

---- Create TABLE
create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2))

关于表的50问

  1. 查询”001”课程比”002”课程成绩高的所有同学的学号:

    SELECT a.S#
    FROM (
    SELECT s#, score
    FROM SC
    WHERE C# = '001'
    ) a, (
    SELECT s#, score
    FROM SC
    WHERE C# = '002'
      ) b
    WHERE a.score > b.score AND a.s# = b.s#;

  2. 查询平均成绩大于60分的同学的学号和平均成绩:

    SELECT S#, avg(score)
    FROM sc
    GROUP BY S#
    HAVING avg(score) > 60;

    通过GROUP BY子句,可以让SUM、COUNT、MAX、AVG等聚合函数对属于一组的是数据起作用.属于同一同学的成绩将只能返回一行值,其中除S#字段,其他字段通过聚合函数返回.

    HAVING子句可以让我们筛选成组后的各组数据，WHERE子句在聚合前先筛选记录.也就是说作用在GROUP BY 子句和HAVING子句前.而 HAVING子句在聚合后对组记录进行筛选。

  3. 查询”李”姓老师的个数:

    SELECT count(DISTINCT (Tname))
    FROM Teacher
    WHERE Tname LIKE '李%';

    在表中可能包含重复值,关键字distinct用于返回唯一不同的值.distinct语句中select显示的字段只能是distinct指定的字段，其他字段是不可能出现的.
    select distinct name, id from A返回name+id不同的行

  4. 查询所有同学的学号、姓名、选课数、总成绩:

    SELECT stu.S#, stu.Sname, COUNT(scs.C#), SUM(scs.score)
    FROM Student stu
    LEFT JOIN SC scs
    ON stu.S# = scs.S#
    GROUP BY stu.S#, stu.Sname
    • inner join,满足交换律,”A inner join B”和”B inner join A”是相等的
    • left outer join,产生左表(Student)的完全集,右表(SC)匹配的则有值,没有匹配的则NULL值
    • full outer join,产生左右表的全集,没有匹配的则NULL,满足交换律.

  5. 查询没选修”李明”老师课程的学生的学号与姓名:

    SELECT stu.S#, stu.Sname
    FROM Student stu
    WHERE stu.S# NOT IN (
    SELECT DISTINCT scs.S#
    FROM SC scs, Course crs, Teacher tch
    WHERE scr.C# = crs.C# AND tch.T# = crs.T# AND tch.Tname = '李明'
    );

  6. 查询各科成绩最高分与最低分:课程ID,最高分,最低分

参考资料:
标签：

相关文章