数据库系统-数据定义

---

数据定义(CREAT, DROP, ALTER)

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有模式、表 、视图和索引等。因此 SQL 的数据定义功能包括模式定义、表定义、视图和索引的定义。

不支持模式修改，视图修改；只有重新删除创建

标准的SQL也不支持索引创建，商用关系数据库提供索引机制和相关语句

一个关系数据库管理系统的实例 （instance）中可以建立多个数据库，一个数据库中可以建立多个模式， 一个模式下通常包括多个表、视图和索引等数据库对象

模式定义与删除

模式定义

CREATE SCHEMA＜模式名＞AUTHORIZATION ＜用户名 >

如果没有指定＜模式名>，那么＜模式名>隐含为＜用户名 >

要创建模式，调用该命令的用户必须拥有数据库管理员权限，或者获得了数据库管理员授予的CREATE SCHEMA的权限

定义模式实际上定义了一个命名空间，在这个空间中可以进一步定义该模式包含的数据库对象，例如基本表、视图、索引等 。也就是说用户可以在创建模式的同时在这个模式定义中进一步创建基本表、视图，定义授权。即 ：

CREATE SCHEMA＜模式名＞AUTHORIZATION ＜用户名 > ［＜表定义子句＞ |＜视图定义子句＞|＜授权定义子句 >］

CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TAB1 (COL 1 SMALLINT,
COL2 INT,
COL3 CHAR(20),
COL4 NUMERIC(10,3),
COL5 DECIMAL(52)
)；

模式删除

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

其 中 CASCADE和 RESTRICT两者必选其一。选择了 CASCADE (级联)，表示在删除模式的同时把该模式中所有的数据库对象全部删除；选择了 RESTRICT (限制)，表示如果该模式中已经定义了下属的数据库对象(如表、视图等)，则拒绝该删除语句的执行。

基本表定义，删除，修改

创建了一个模式就建立了一个数据库的命名空间，一个框架。在这个空间中首先要定义的是该模式包含的数据库基本表

创建表

持久表

CREATE TABLE <表名>(＜列名＞＜数据类型＞［列级完整性约束条件］
［,＜列名＞＜数据类型＞［列级完整性约束条件］］
                  ...
［,＜表级完整性约束条件＞］) ；

完整性约束条件被存入系统的数据字典中，当用户操作表中数据时由关系数据库管理系统自动检查该操作是否违背这些完整性约束条件。如果完整性约束条件涉及该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY, /*严列级完整性约束条件， Cno是主码*/
 Cname CHAR(40) NOT NULL, /*列级完整性约束条件， Cname不能取空值*/
Cpno CHAR(4), /* Cpno的 含 义 是 先 修 课 */
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
/*表级完整性约束条件， Cpno是外码，被参照表是Course ,被参照列是Cno */
)；

(Cno CHAR(4) NOT NULL PRIMARY KEY,)

(gender bit NULL DEFAULT 1,) 可以为NULL，默认值为1

(bithday date NULL)

(Cno CHAR(4) FOREIGN KEY REFERENCES tbl(XX))

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);

由上可知外键和参照的表可以是同一个表

CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
FOREIGN KEY (Sno) REFERENCES Student(Sno),
/*表级完整性约束条件， Sno是外码，被参照表是Student */
FOREIGN KEY (Cno) REFERENCES Course(Cno)
/*表级完整性约束条件， Cno是外码，被参照表是Course*/
)；

临时表

在 SQL Server 中，可以通过以下两种方法创建临时表：
1. 使用 CREATE TABLE语句创建临时表，表名前加上 # 或 ##。例如：
SQL
CREATE TABLE #临时表名 (字段1 约束条件, 字段2 约束条件, ...)
CREATE TABLE ##临时表名 (字段1 约束条件, 字段2 约束条件, ...)

2. 使用 SELECT INTO 语句从现有表中创建临时表，表名前加上 # 或 ##。例如：
SQL
SELECT * INTO #临时表名 FROM 你的表;
SELECT * INTO ##临时表名 FROM 你的表;

其中，#临时表名 是本地临时表，只对当前会话可见；##临时表名 是全局临时表，对所有会话可见¹。希望这些信息对您有所帮助！

----mysql中

是的，在MySQL中也有临时表的概念。临时表是一种特殊类型的表，它允许您存储临时结果集，可以在单个会话中多次重用。当使用单个SELECT语句和JOIN子句查询数据不可能或代价高昂时，临时表非常方便¹。

您可以使用CREATETEMPORARYTABLE语句创建临时表。例如：
SQL
CREATETEMPORARYTABLE临时表名(字段1约束条件,字段2约束条件,...)

MySQL在会话结束或连接终止时自动删除临时表。当然，您也可以在不再使用临时表时使用DROPTABLE语句显式删除它。临时表仅对创建它的客户端可用和可访问。不同的客户端可以创建具有相同名称的临时表而不会引发错误，因为只有创建临时表的客户端才能看到它¹。

数据类型

整形

精准数值型

整数部分+小数部分 能够精准表示数据

浮点型

不能够精准表示数据

货币型

位型

字符型、Unicode字符型和文本型：char\nchar、varchar\nvarchar、text\ntext

mysql 中无nvarchar,ntext
通常在其他数据库管理系统（如SQL Server）中使用，用于存储Unicode字符。在MySQL中，可以使用字符集和排序规则来支持Unicode字符。

二进制型和图像型

日期时间型

时间戳

平面和地理空间数据类型

其它

模式与表

设置表所属模式的三种方式

选择模式

CREATE TABLE "S-T".Student(...)

创建模式时创建表

CREATE SCHEMA TEST AUTHORIZATION ZHANG
CREATE TABLE TABL(
	COL1 SMALLINT,
	COL2 INT,
	COL3 CHAR(20),
	COL4 NUMERIC(10,3),
	COL5 DECIMAL(52)
)；

系统根据搜索路径（search path） 来确定该对象所属的模式

搜索路径包含一组模式列表，关系数据库管理系统会使用模式列表中第一个存在的模式作为数据库对象的模式名。若搜索路径中的模式名都不存在，系统将给出错误。

/*显示当前搜索路径*/
SHOW search_path;
/*搜索路径的当前默认值是$user, PUBLIC。 其含义是首先搜索与用户名相同的模式名，如果该模式名不存在，则使用PUBLIC模式。*/

/*数据库管理员也可以设置搜索路径，例如：*/
SET search_path TO "S-T", PUBLIC;
/*然后，定义基本表：*/
CREATE TABLE Student (...)；
/*实际结果是建立了 S-T.Student基本表。因为关系数据库管理系统发现搜索路径中第一个模式名S-T 存在，就把该模式作为基本表Student所属的模式。*/

基本表修改

ALTER TABLE ＜表名＞
-- 增加属性
[ADD [COLUMN] ＜新列名＞ ＜数据类型＞ [完整性约束]]
[ADD＜表级完整性约束＞] -- ALTER TABLE Course ADD UNIQUE(Cname);
-- 删除列
[DROP [COLUMN] ＜列名＞ [CASCADE|RESTRICT]]
--删除约束
[DROP CONSTRAINT＜完整性约束名＞ [RESTRICT | CASCADE ]]
-- 修改列
[ALTER COLUMN ＜列名＞＜数据类型＞] ;

基本表删除

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

若选择RESTRICT,则该表的删除是有限制条件的。

若选择CASCADE ,则该表的删除没有限制条件。在删除基本表的同时，相关的依赖对象，例如视图，都将被一起删除。因此执行删除基本表的操作一定要格外小心。

注意：不同的数据库产品在遵循SQL标准的基础上具体实现细节和处理策略会与标准有差别。

视图建立和删除

CREATE VIEW IS_Student /* Student 表上建立视图*/
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

DROP TABLE Student RESTRICT; /*删除 Student 表引
-ERROR: cannot drop table Student because other objects depend on it*/
/* 系统返回错误信息，存在依赖该表的对象，此表不能被删除》*/
DROP TABLE Student CASCADE; /*删除 Student 表*/
—NOTICE: drop cascades to view IS Student /*系统返回提示，此表上的视图也被删除*/
SELECT * FROM IS_Student;
--ERROR: relation " IS Student " does not exist

索引的建立和删除

当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。 类似于图书后面的索引，能快速定位到需要查询的内容。用户可以根据应用环境的需要在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。

数据库索引有多种类型，常见索引包括顺序文件上的索引、B+树索引、散列 （hash）索引、位图索引等。

• 顺序文件上的索引是针对按指定属性值升序或降序存储的关系，在该属性
  上建立一个顺序索引文件，索引文件由属性值和相应的元组指针组成。
• B+树索引是将索引属性组织成B+树形式， B+树的叶结点为属性值和相应的元组指针。 B+树索引具有动态平衡的优点。
• 散列索引是建立若干个桶，将索引属性按照其散列函数值映射到相应桶中，桶中存放索引属性值和相应的元组指针。散列索引具有查找速度快的特点。
• 位图索引是用位向量记录索引属性中可能出现的值，每个位向量对应一个可能值

索引虽然能够加速数据库查询，但需要占用一定的存储空间，当基本表更新时，索引要进行相应的维护，这些都会增加数据库的负担,因此要根据实际应用的需要有选择地创建索引。目前SQL标准中没有涉及索引,一般说来，建立与删除索引由数据库管理员或表的属主（owner）, 即建立表的人，负责完成。用户不必也不能显式地选择索引。索引是关系数据库管理系统的内部实现技术，属于内模式的范畴。

索引建立

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON＜表名＞（＜列名＞[＜次序＞] [＜列名＞ [＜次序＞]]•••）;
/*UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。
CLUSTER表示要建立的索引是聚簇索引。*/

索引可以建立在该表的一列或多列上，各列名之间用逗号分隔。每个＜列名>后面还可以用＜次序 >指定索引值的排列次序，可 选 ASC （升序 ）或 DESC （降序），默认值为ASC。

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 ＜新索引名＞ ；

删除索引

DROP INDEX ＜索引名＞；

数字字典

数据字典是关系数据库管理系统内部的一组系统表，它记录了数据库中所有的定义信息,包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等。关系数据库管理系统在执行SQ L 的数据定义语句时，实际上就是在更新数据字
典表中的相应信息。在进行查询优化和查询处理时，数据字典中的信息是其重要依据。

---

最惬意的时候，往往是失败的开始。