数据定义

模式

1. 定义模式

   • CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

   • CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];

   • 例子：

   • CREATE SCHEMA TEST AUTHORIZATION ZHANG
     CREATE TABLE TAB1(
                    COL1 SMALLINT,
                    COL2 INT,
                    COL3 CHAR(20),
                    COL4 NUMERIC(10,3),
                    COL5 DECIMAL(5,2));
     

2. 删除模式

   • DROP SCHEMA <模式名> <CASCADE|RESTRICT>;

基本表

1. 定义基本表

   • CREATE TABLE <表名> (<列名><数据类型> [列级完整性约束条件]
                        [,<列名><数据类型> [列级完整性约束条件]]
                        ...
                        [,<表级完整性约束条件>]);
     

   • 例子：

   • CREATE TABLE STUDENT
     (SNO CHAR(9) PRIMARY KEY,
     SNAME CHAR(20) UNIQUE,
     SSEX CHAR(2),
     SAGE SMALLINT,
     SDEPT CHAR(20));
     

   • CREATE TABLE SC
     (SNO CHAR(9),
     CNO CHAR(4),
     GRADE SMALLINT,
     PRIMARY KEY(SNO,CNO),
     FOREIGN KEY(SNO) REFERENCES STUDENT(SNO),
     FOREIGN KEY(CNO) REFERENCES COURSE(CNO));
     

     ​

2. 修改基本表

   • ALTER TABLE <表名>
     [ADD [COLUMN] <新列名> <数据类型> [完整性约束]]
     [ADD <表级完整性约束>]
     [DROP [COLUMN] <列名> [CASCADE|RESTRICT]]
     [DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]
     [ALTER COLUMN <列名> <数据类型>];    
     

   • 例子：

   • ALTER TABLE Student ADD s_entrance DATE;
     

   • ALTER TABLE Student ALTER COLUMN Sage INT;
     

   • ALTER TABLE Course ADD UNIQUE(Cname);
     

     ​

3. 删除基本表

   • DROP TABLE <表名> [RESTRICT|CASCADE];

索引

1. 建立索引

   • CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
     ON <表名>(<列名> [<次序> [,<列名> [<次序>]]...);
     

   • 例子：

   • CREATE UNIQUE INDEX Stusno ON STUDENT(Sno);
     

     ​

2. 修改索引

   • ALTER INDEX <旧索引名> RENAME TO <新索引名>;
     

3. 删除索引

   • DROP INDEX <索引名>;

     ​

数据查询

• SELECT [ALL|DISTINCT] <目标列表达式> [,<目标列表达式>] ...
  FROM <表名或视图名> [,<表名或视图名> ...]|(<SELECT 语句> [AS] <别名>)
  [WHERE <条件表达式>]
  [GROUP BY <列名1> [HAVING <条件表达式>]]
  [ORDER BY <列名2> [ASC|DESC]];
  

• 很多例子

• 单表查询

  • SELECT Sno,Sname
    FROM Student;
    

  • SELECT * 
    FROM Student;
    

  • SELECT Sname, 2014-Sage
    FROM Student;
    

  • SELECT Sname,'Year of Birth' 2014-Sage, LOWER(Sdept)
    FROM Student;
    

  • SELECT DISTINCT Sno
    FROM SC;
    

  • SELECT Sname,Sage
    FROM Student
    WHERE Sage<20;
    

  • SELECT Sname,Sdept,Sage
    FROM Student
    WHERE Sage BETWEEN 20 AND 30; 
    

  • SELECT Sname,Ssex
    FROM Student
    WHERE Sdept IN ('CS','MA','IS');
    

  • [NOT] LIKE '<匹配串>' [ESCAPE'<换码字符>']
    % 任意长度字符串
    _ 任意单个字符
    

  • SELECT Sno,Grade
    FROM SC
    WHERE Cno='3'
    ORDER BY Grade DESC;
    

  • COUNT(*)
    COUNT([DISTINCT|ALL] <列名>)
    SUM([DISTINCT|ALL] <列名>)
    AVG([DISTINCT|ALL] <列名>)
    MAX([DISTINCT|ALL] <列名>)
    MIN([DISTINCT|ALL] <列名>)
    聚集函数只能用于SELECT子句和GROUP BY中的HAVING子句
    

  • SELECT COUNT(*)
    FROM Student;
    

  • SELECT AVG(Grade)
    FROM SC
    WHERE Cno='1';
    

  • SELECT Cno,COUNT(Sno)
    FROM SC
    GROUP BY Cno;
    分组后聚集函数将作用于每一个组，即每一组都有一个聚集函数
    

  • SELECT Sno
    FROM SC
    GROUP BY Sno
    HAVING COUNT(*) > 3;
    

• 连接查询

  • SELECT Student.*,SC.*
    FROM Student,SC
    WHERE Student.Sno=SC.Sno;
    查询每个学生及其选修课程的情况
    

  • SELECT FIRST.Cno,SECOND.Cpno
    FROM Course FIRST,COURSE SECOND
    WHERE FIRST.Cpno=SECOND.Cno;
    自身连接是要为表取两个不同的名字
    

  • SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
    FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);
    左外连接
    

  • SELECT Student.Sno,Sname,Cname,Grade
    FROM Student,SC,Course
    WHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno;
    

• 嵌套查询

  • SELECT Sname
    FROM Student
    WHERE Sno IN
        (SELECT Sno
        FROM SC
        WHERE Cno='2');
    

  • ANY | ALL
    SELECT Sname,Sage
    FROM Student
    WHERE Sage<ANY(SELECT Sage
                  From Student
                  WHERE Sdept='CS');
    

• 集合查询

  • [UNION|INTERSECT|EXCEPT]
    SELECT * FROM Student WHERE Sdept='CS'
    UNION
    SELECT * FROM Student WHERE Sage<=19;
    

• 基于派生表的查询

  • SELECT Sno,Cno
    FROM SC,(SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno)
        AS Avg_sc(avg_sno,avg_grade)
    Where SC.Sno=Avg_sc.avg_sno and SC>grade>=Avg_sc.avg_grade;
    

数据更新

1. 插入数据

   • INSERT
     INTO <表名> [(<属性列1> [,<属性列2>] ...)]
     VALUES (<常量1>[,<常量2>] ...);
     

2. 修改数据

   • UPDATE <表名>
     SET <列名>=<表达式> [,<列名>=<表达式>] ...
     [WHERE <条件>];
     

3. 删除数据

   • DELETE
     FROM <表名>
     [WHERE <条件>];
     

空值的处理

1. IS NULL|IS NOT NULL
   UNIQUE
   

视图

1. CREATE VIEW <视图名> [(<列名>[,<列名>]...)]
   AS <子查询>
   [WITH CHECK OPTION];
   

   • CREATE VIEW IS_Student
     AS
     SELECT Sno,Sname,Sage
     FROM Student WHERE Sdept='IS'
     WITH CHECK OPTION;
     以后对该视图进行插入、修改、删除操作时，DBMS会自动加上Sdept='IS'的条件
     

   • CREATE VIEW IS_S1(Sno,Sname,Grade)
     AS
     SELECT Student.Sno,Sname,Grade
     FROM Student,SC
     WHERE Sdept='IS' AND Student.Sno=SC.Sno AND SC.Cno='1';
     

   • CREATE VIEW S_G(Sno,Gavg)
     AS
     SELECT Sno,AVG(Grade)
     FROM SC
     GROUP BY Sno;
     

2. DROP VIEW <视图名> [CASCADE];
   

3. UPDATE 有一些视图是不能更新的
   

4. SELECT 与表操作相同
   

授权

1. GRANT <权限>[,<权限>]...
   ON <对象类型> <对象名> [,<对象类型> <对象名>]...
   TO <用户>[,<用户>]...
   [WITH GRANT OPTION];
   
   SELECT|DELETE|ALL PRIVILEGES ...
   

2. REVOKE <权限>[,<权限>]...
   ON <对象类型> <对象名> [,<对象类型> <对象名>]...
   FROM <用户>[,<用户>]...[CASCADE|RESTRICT];
   

3. CREATE USER <username>[WITH][DBA|RESOURCE|CONNECT];
   

4. CREATE ROLE <角色名>
   

5. AUDIT ALTER,UPDATE
   ON SC;
   NOAUDIT ALTER,UPDATE
   ON SC;
   

完整性约束命名子句

1. CHECK (Ssex IN ('boy','girl'))
   

2. CONSTRAINT <完整性约束条件名> <完整性约束条件>
   

   ​