SQL 语句

SQL 语句
 教学数据库中有三个基本表：
     S(S#, SNAME, AGE, ***) #***这里被屏蔽了，其实是S#E#X
     SC(S#, C#, GRADE)
     C(C#, CNAME, TEACHER)
1、基本表的创建，可用下列语句来创建：
     CREATE TABLE S
         (S# CHAR(4) NOT NULL, #长度为4的定长字符串
         SNAME CHAR(8) NOT NULL,
         AGE SMALLINT, #短整型，INT为长整型
         *** CHAR(1),
         PRIMARY KEY(S#) );
     #SQL中允许列值为空，不允许某一列的值为空时，可加上关键字"NOT NULL"

    CREATE TABLE C
        (C# CHAR(4),
        CNAME CHAR(10) NOT NULL,
        TEACHER CHAR(8),
        PRIMARY KEY(C#) );

    CREATE TABLE SC
    (S# CHAR(4),
    C# CHAR(4),
    GRADE SMALLINT,
    PRIMARY KEY(S#, C#),
    FOREIGN KEY(S#)REFERENCES S(S#),
    FOREIGN KEY(C#)REFERENCES C(C#),
        CHECK(GRADE BETWEEN 0 AND 100) );
    #上面的CHECK检查子句，指出成绩GRADE的值应在0~100之间
2、基本表结构的修改
    (1)在基本表S中增加一个地址列(ADDRESS)：#新增加的列不能定义为NOT NULL
        ALTER TABLE S ADD ADDRESS VARCHAR(30); #VARCHAR(30)最大长度为30的字符串
    (2)在基本表S中删除年龄列(AGE)，并且把引用该列的所有视图和约束也一起删除：
        ALTER TABLE S DROP AGE CASCADE;
    #CASCADE表示在基本表中删除某列时，所有引用到该列的视图和约束也一起自动删除；
    #RESTRICT方式表示在没有视图或约束引用到该属性时，才能删除该列，否则拒绝删除操作。
    (3)在基本表S中,S#的长度修改为6：
        ALTER TALBE S MODIFY S# CHAR(6);
3、基本表的撤消
    (1)撤消基本表S：
        DROP TALBE S RESTRICT; ##CASCADE,RESTRICT同上
4、索引表操作
    (1)对基本表S的列S#建立索引，索引键名为S#_INDEX:
        CREATE INDEL S#_INDEX ON S(S#);
    (2)对基本表SC的(S#, C#)建立索引：
        CREATE UNIQUE INDEX SC_INDEX ON SC(S# ASC, C# DESC);
    (3)撤消索引S#_INDEX和SC_INDEX：
        DROP INDEX S#_INDEX, SC_INDEX;
5、SQL的数据查询
    (1)检索学习课程号为C2的学生学号与成绩：
        SELECT S#, GRADE
        FROM SC
        WHERE C# = 'C2';
    (2)检索学习课程号为C2的学生学号与姓名：
        SELECT S.S#, SNAME
        FROM S, SC
        WHERE S.S = SC.S AND C# = C2;
        或者：
        SELECT S#, SNAME
        FROM S
        WHERE S# IN (SELECT S#
                FROM SC
                WHERE C# = 'C2';);
    (3)检索选修课程名为MATHS的学生学号与姓名：
        SELECT S.S#, SNAME
        FROM S, SC,C
        WHERE S.S# = SC.S# AND SC.C# = C.C# AND CNAME = 'MATHS';
        或者：
        SELECT S#, SNAME
        FORM S
        WHERE S# IN (SELECT S#
                FROM SC
                WHERE C# IN (SELECT C#
                    FROM C
                    WHERE CNAME = 'MATHS'));
    (4)检索选修课程号为C2或C4的学生学号：
        SELECT S#
        FROM SC
        WHERE C# = 'C2' OR C# = 'C4';
    (5)检索至少选修课程号为C2和C4的学生学号：
        SELECT X.S#
        FROM SC AS X, SC AS Y
        WHERE X.S# = Y.S# AND X.C# = 'C2' AND Y.C# = 'C4';
    (6)检索不学C2课程的学生姓名与年龄：
        SELECT SNAME, AGE
        FROM S
        WHERE S# NOT IN(SELECT S#
                FROM SC
                WHERE C# = 'C2');
        或者：
        SELECT  SNAME, AGE
        FROM S
        WHERE NOT EXISTS (SELECT *
                FROM SC
                WHERE SC.S# = S.S# AND C# = 'C2');

    (7)检索学习全部课程的学生姓名：
        SELECT SNAME FROM S
        WHERE NOT EXISTS
            (SELECT * FROM C
            WHERE NOT EXISTS
                (SELECT *
                FROM SC
                WHERE SC.S# = S.S# AND SC.C# = C.C#));
    (8)统计每一年龄选修课程的学生人数：
        SELECT AGE, COUNT(DISTINCT S.S#)
        FROM S,SC
        WHERE S.S# = SC.S#
        GROUP BY AGE;
    (9)求基本表S中男同学的每一年龄组(超过50人)有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列:
        SELECT AGE, COUNT(S#)
        FROM S
        WHERE *** = 'M'
        GROUP BY AGE
            HAVING COUNT(*) > 50
        ORDER BY 2, AGE DESC #2表示对SELECT子句中的第2个属性值进行排序
    (10)
6、SQL的数据更新
    (1)往基本表SC中插入一个元组(S4, C6, 90):
        INSERT INTO SC(S#, C#, GRADE)
            VALUES('S4', 'C6', 90)
    (2)在基本表SC中，把平均成绩大于80分的男学生的学号和平均成绩存入另一个已知的基本表S_GRADE(S#, AVG_GRADE):
        INSERT INTO S_GRADE(S#, AVG_GRADE)
            SELECT S#, AVG(GRADE)
            FROM SC
            WHERE S# IN
                (SELECT S# FROM S WHERE *** = 'M')
            GROUT BY S#
                HAVING AVG(GRADE) > 80
    (3)把课程名为MATHS的成绩从基本表SC中删除
        DELETE FROMS SC
            WHERE C# IN (SELECT C# FROM C
                WHERE CNAME = 'MATHS');
    (4)把C4课程中小于该课程平均成绩的成绩元组从基本表SC中删除：
        DELETE FROM SC
            WHERE C# = 'C4'
            AND GRADE < (SELECT AVG(GRADE) FROM SC
                    WHERE C# = 'C4')
    (5)把C5课程的任课教师姓名改为WU：
        UPDATE C SET TEACHER = 'WU'
            WHERE C# = 'C5';
    (6)把女同学的成绩提高10%:
        UPDATE SC SET GRADE = GRADE * 1.1
            WHERE S# IN (SELECT S# FROM S
                    WHERE *** = 'F')
    (7)当C4课的成绩低于该门课程平均成绩时，提高5%:
            UPDATE SC SET GRADE = GRADE * 1.05
                WHERE C# = 'C4'
                AND GRADE < (SELECT AVG(GRADE) FROM SC
                WHERE C# = 'C4')
    (8)在C中，把课程号为C5的元组修改为(C5, DB, LIU)
        UPDATE C
        SET ROW = (C5, DB, LIU)
        WHERE C# = 'C5';
7、视图的创建
    (1)对于教学数据库中基本表S, SC, C，用户经常要用到S#, SNAME, CNAME 和 GRADE等列的数据，那么可用下列语句创建视图：
        CREATE VIEW SUTDENT_GRADE(S#, SNAME, CNAME, GRADE)
            AS SELECT S.S#, SNAME, CNAME, GRADE
            FROM S, SC, C
            WHERE S.S# = SC.S# AND SC.C# = C.C#;
    (2)撤消SUTDENT_GRADE视图:
        DROP VIEW SUTDENT_GRADE;
    (3)如果视图是从单个基本表中只使用选择、投影操作导出且包含了基本表的主键，则这样的视图称为“行列子集视图”，且可以被执行更新操作。
    允许用户更新的视图在定义时必须加上“WITH CHECK OPTION”短语。