数据库-(4)-SQL基础1-数据定义
#SQL#模式的定义、删除#表的定义、修改、删除#索引的建立与删除 2023-11-07

基础概念

  1. 基本表

    • 本身独立存在的表
    • SQL中一个关系就对应一个基本表
    • 一个或多个基本表对应一个存储文件
    • 一个表可以带若干索引

  2. 存储文件

    • 逻辑结构组成了关系数据库的内模式
    • 物理结构是任意的，对用户透明

  3. 视图

    • 从一个或几个基本表导出的表
    • 数据库中只存放视图的定义，而不存放视图对应的数据(避免重复操作)
    • 视图可以是一个虚表(实际数据库中不存在的表)
    • 用户可以在视图之上再定义视图

学生－课程数据库

  • 学生表：\(Student(\underline{Sno},Sname,Ssex,Sage,Sdept)\)
  • 课程表：$Course(,Cname,Cpno,Ccredit) $
  • 学生选课表：\(SC(\underline{Sno,Cno},Grade)\)

SQL定义语句

模式的定义与删除

模式的定义

  1. 为用户 \(WANG\) 定义一个学生－课程模式 \(S-T\)

    1
    CREATE SCHEMA "S-T" AUTHORIZATION WANG;

  2. 不指定模式，模式名隐含为用户名

    1
    CREATE SCHEMA AUTHORIZATION WANG;

  3. 建立模式的同时建立相关表：为用户 \(ZHANG\) 创建一个模式 \(TEST\), 并且在其中定义一个表 \(TAB1\)

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE SCHEMA "TEST" AUTHORIZATION ZHANG;
    CREATE TABLE TAB1(
    	COL1 SMALLINT,
        COL2 INT,
    	COL3 CHART(20),
        COL4 NUMERIC(10,3), 
        COL5 DECIMAL(5,2) /*最后一行没有逗号 */
    ); /*整个语句写完之后才加上";" */

删除模式

  1. 级联模式删除

    1
    DROP SCHEMA ZHANG CASCADE;/*级联模式下删除，所有相关联的定义都将删除，就比如里面定义的表也会被删除*/

  2. 限制模式下删除

    1
    DROP ZHANG RESTRICT;/*级联模式下删除，所有相关联的定义都将删除*/

基本表的定义、删除与修改

定义基本表

  1. 建立一个学生表Student

    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE Student(
        Sno CHAR(9)PRIMARY KEY, /*列级完整性约束条件，这里是表示主码*/
        Sname CHAR(20)UNIQUE, /*取唯一值*/
        Ssex CHAR(2),
        Sage SMALLINT,
        Sdept CHAR(20)
    );

  2. 建立一个课程表"Course"

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE Course(
        Cno CHAR(4)PRIMARY KEY,
        Cname CHAR(40), 
        Cpno CHAR(4),
        Ccredit SMALLINT,
        FOREIGN KEY(Cpno) REFERENCES Course(Cno)/*参照完整性，Cpno是外码，被参照表是Course，被参照列是Cno*/
    );
    /*参照表和被参照表可以是同一个表*/

  3. 建立学生选课SC

    1
    2
    3
    4
    5
    6
    7
    8
    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)
    );

数据类型

数据类型 含义
CHAR(n), CHARACTER(n) 长度为n的定长字符串
VARCHAR(n), CHARACTERVARYING(n) 最大长度为n的变长字符串
CLOB 字符串大对象
BLOB 二进制大对象
INT, INTEGER 长整数(4字节)
SMALLINT 短整数(2字节)
BIGINT 大整数(8字节)
NUMERIC(p,d) 定点数,由p位数字(不包括符号、小数点)组成,小数点后面有d 位数字
DECIMAL(p,d), DEC(p,d) 同NUMERIC
REAL 取决于机器精度的单精度浮点数
DOUBLE PRECISION 取决于机器精度的双精度浮点数
FLOAT(n) 可选精度的浮点数,精度至少为n位数字
BOOLEAN 逻辑布尔量
DATE 日期,包含年、月、日,格式为YYYY-MM-DD
TLME 时间,包含一日的时、分、秒,格式为HH:MM:SS
TIMESTAMP 时间戳类型
INTERVAL 时间间隔类型

模式与表

  • 显示表示：CREATE TABLE "S-T".Student(…);
  • 在创建模式的时候直接创建表。
  • 设置所属的模式，然后就不必特意指定表的所属。

修改基本表

ALTER COLUMN 子句用于修改原有的列定义，包括修改列名和数 据类型

  1. 向 Student 表增加“入学时间”列，其数据类型为日期型。

    1
    ALTER TABLE Student ADD S_entrance DATE;

  2. 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

    1
    ALTER TABLE Student ALTER COLUMN DATE;

  3. 增加课程名称必须取唯一值的约束条件。

    1
    ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

当某个基本表不再需要时，可以使用 DROP TABLE 语句删除它。其一般格式为：DROPTABLE ＜表名＞ [ RESTRICT I CASCM>E] ;

  1. 删除 Student 表。

    1
    DROP TABLE Student CASCADE;

    基本表定义一旦被删除，不仅表中的数据和此表的定义将被删除，而且此表上建立的 索引、触发器等对象一般也都将被删除。有的关系数据库管理系统还会同时删除在此表上 建立的视图。

索引的建立与删除

在 SQL 语言中，建立索引使用CREATE INDEX 语句，其一般格式为:

1
2
CREATE [UNIQUE] [CLUSTER] INDEX ＜索引名＞
ON <表名>(＜列名＞ ［＜次序＞］ ［，＜列名＞ ［＜次序＞］］···);

索引建立

  1. 为学生－课程数据库中的 Student、 Course、 SC 三个表建立索引。其中 Student表按学号升序建唯一索引， Course 表按课程号升序建唯一索引，SC 表按学号升序 和课程号降序建唯一索引。

    1
    2
    3
    CREATE UNIQUE INDEX Stusno ON Student(Sno); 
    CREATE UNIQUE INDEX Coucno ON Course(Cno); 
    CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC); /*ASC表示升序，DESC表示降序，一般升序都可以默认不写*/

索引修改

  1. 将SC 表的 SCno索引名改为SCSno

    1
    ALTER INDEX SCno RENAME TO SCSno;

删除索引

  1. 删除 Student 表的Stusname 索引。

    1
    DROP INDEX Stusname;