数据库中的数据定义操作

发表于 更新于 分类于

SQL——结构化查询语言

SQL特点：

  1. 综合统一：可以独立完成数据库生命周期中的全部活动
  2. 高度非过程化：主要提出”做什么”，无需了解具体过程
  3. 面向集合的操作方式：操作对象是集合，结果也是集合
  4. 多种使用方式：既是独立语言又是嵌入语言

SQL的动词

SQL功能 动词
数据查询 SELECT
数据定义 CREATE, DEOP, ALTER
数据操纵 INSERT, UPDATE, DELETE
数据控制 GRANT, REVOKE

数据定义

SQL的数据定义功能：定义各种数据库的”对象”

  • 模式定义
  • 表定义
  • 视图定义
  • 索引定义

SQL的数据定义语句

操作对象 操作方式 操作方式 操作方式
创建 删除 修改
模式 CREATE SCHEMA DEOP SCHEMA
表 CREATE TABLE DROP TABLE ALTER TABLE
视图 CREATE VIEW DROP VIEW
索引 CREATE INDEX DROP INDEX ALTER INDEX

数据字典
系统内部的一组系统表，记录数据库中所有对象的定义信息及一些统计信息：

  • 关系模式、表、视图索引的定义
  • 完整性约束的定义
  • 各类用户对数据库的操作权限
  • 统计信息等

关系数据库管理系统在执行SQL时，实际上就是在更新数据字典表中的信息。

模式定义

定义模式
定义模式实际上定义了一个”命名空间”(或者说一个目录)。
在这个空间中可以定义该模式包含的数据库对象，例如基本表、视图、索引等。
在CREATE SCHEMA中可以接收CREATE TABLE,CREATE VIEW和GRANT子句。
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [表定义|视图定义|授权定义]

1
2
3
4
5
6
7
8
9
10
11
#为用户Wang定义一个模式Test (MySQL)
CREATE SCHEMA Test AUTHORIZATION Wang;
#模式名的缺省值是用户名同名
CREATE SCHEMA AUTHORIZATION Wang;
#为用户Zhang创建Test模式，并在其中定义一个表格Tab_1
CREATE SCHEMA Test AUTHORIZATION Zhang
CREATE TABLE Tab_1 (
  Col_1 SMALLINT,
  Col_2 INT,
  Col_3 CHAR(20)
);

删除模式
删除模式有两种方式：CASCADE(级联)、RESTRICT(限制)。
前者将删除模式中所有的对象，后者只有在模式内为空时才允许删除。
DROP SCHEMA <模式名> <CASCADE|RESTRICT>

1
2
#删除模式Test及其内部所有对象
DROP SCHEMA Test CASCADE;

基本表定义

定义基本表
CREATE TABLE <表名> (
<列名> <数据类型> [<列级完整性约束条件>]
[,<列名> <数据类型> [<列级完整性约束条件>]]
...
[,<表级完整性约束条件>]);
列级完整性约束条件：涉及相应属性列的完整性约束条件。
表级完整性约束条件：涉及一个或多个属性列的完整性约束条件。
（如果完整性约束条件涉及多个属性列，则必须定义在表级）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#建立“学生”表Student，学号是主键，姓名取值唯一。
CREATE TABLE Student (
  Sno CHAR(9) PRIMARY KEY,  #主键PRIMARY KEY
  Sname CHAR(20) UNIQUE,    #唯一UNIQUE
  Ssex CHAR(2),
  Sage SMALLINT,
  Sdept CHAR(20)
);
#建立“课程”表Course
CREATE TABLE Course (
  Cno CHAR(4) PRIMARY KEY,
  Cname CHAR(40),
  Cpno CHAR(4),
  Ccredit SMALLINT,
  FOREIGN KEY (Cpno) REFERENCES Course(Cno)
  #FOREIGN KEY外键 REFERENCES参照对象
);
#建立学生选课表SC
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)
);

数据类型：关系模型中“域”的概念。
MySQL中的数据类型

修改基本表
删除列/完整性约束时，同样有两种方式CASADE、RESTRICT。
前者将连带相关的对象都删除，后者只有目标对象不被引用才允许删除。
ALTER TABLE <表名>选择表
[ADD [COLUMN] <新列名> <数据类型> [完整性约束]]增加新列
[ADD <表级完整性约束]增加表级完整性约束
[DROP [COLUMN] <列名> [CASADE|RESTRICT]]删除列
[DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE]]删除完整性约束
[ALTER COLUMN <列名> <数据类型>]修改列名/数据类型

1
2
3
4
5
6
#向Student中增加“入学时间”，日期类型。
ALTER TABLE Student ADD S_entrance DATE;
#将年龄的数据类型有字符改为整数
ALTER TABLE Student ALTER COLUMN Sage INT;
#新增课程名必须取唯一值的约束条件
ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表
删除基本表时，同样有两种方式CASADE、RESTRICT。
前者将连带相关的对象都删除，后者只有目标对象不被引用才允许删除。
DROP TABLE <表名> [RESTRICT|CASCADE];

1
2
#删除Student表及其相关对象
DROP TABLE Student CASCADE;

索引定义

建立索引的目的： 加快查询的速度

关系数据库管理系统常见的索引：

  • 顺序文件上的索引
  • B+树索引
  • 散列(HASH)索引
  • 位图索引

特点：

  • B+树索引具有动态平衡的优点
  • HASA索引具有查找速度快的特点

建立索引的权限： 数据库管理员或表的属主(建表人)
维护和使用索引： 系统自动维护、选择，用户不能显式地选择索引

建立索引
创建索引有两个选项：UNIQUE、CLUSTER。
前者表示每索引值只对应唯一的数据记录，后者表示要建立的索引是聚簇索引。
CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名> (<列名>[<次序>][,<列名>[<次序>]]...);

  • <表名>：建立索引的基本表的名字
  • 索引：可以建立在该表的一列或多列上，各列名之间用逗号隔开
  • <次序>：指定索引的排列次序，升序(缺省)ASC、降序DESC
  • UNIQUE：此索引的每一个索引值只对应唯一的数据记录
  • CLUSTER：表示要建立的索引是聚簇索引
    1
    2
    3
    4
    5
    6
    7
    #为Student Course SC三个表建立索引：
    #Student表按学号升序建立唯一索引，
    #Course表按照课程号升序建立唯一索引，
    #SC表按学号升序和课程号降序建立唯一索引。
    CREATE UNIQUE INDEX Stusno ON Student(Sno);
    CREATE UNIQUE INDEX Coucno ON Course(Cno);
    CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);

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

1
2
#将SC表的SCno索引改名为SCSno
ALTER INDEX SCno RENAME TO SCSno;

删除索引
DROP INDEX <索引名>;

1
2
#删除Student表中的Stusname索引
DROP INDEX Stusname;