mysql 数据字典生成工具

一、学习目标

• 数据字典
• 表空间
• 约束

二、数据字典

定义：数据字典是oracle数据库中最重要的组成部分，记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys用户；用户只能在数据字典上执行查询操作,而其维护和修改是由系统自动完成的！

概念：
数据库是数据的集合， 数据库维护和管理着用户的数据，那么这些用户数据表都存在哪里，用户的信息是怎样的，存储这些用户的数据的路径在哪里，这些信息不属于用户的信息，却是数据库维护和管理用户数据的核心，这些信息就是数据库的数据字典来维护的，数据库的数据字典就汇集了这些数据库运行所需要的基础信息。每个数据库都提供了各自的数据字典的方案，虽然形式不同，但是目的和作用是一样的，比如在mysql里数据字典是在information_schema 里表现的，sqlserver则是在sys这个系统schema里来展示的。

Oracle的数据字典是Oracle数据库安装之后，自动创建的一系列数据库对象。数据字典是Oracle数据库对象结构的元数据信息。熟悉和深入研究数据字典对象，可以很大程度的帮助我们了解Oracle内部机制。

Oracle字典视图包括四个层次，分别为X$内部表、基础数据字典表、数据字典视图和动态性能视图。

数据字典表和用户创建的表没有什么区别，不过数据字典表里的数据是Oracle系统存放的系统数据，而普通表存放的是用户的数据而已，为了方便的区别这些表，数据字典表的名字都是用"$"结尾，在我们看到的sql语句里看到"$"结尾的这些表，大家就可以想到这可能是一个数据字典表了，同样，既然如此，我们创 建自己的用户表的时候就不要用“$”结尾，以免让别人误会，数据字典表是系统存放系统用户的，所以他的owner是sys，在手工用create database的命令的时候，会调用$ORACLE_HOME/rdbms/admin/sql.bsq文件，这个就会执行生成我们这些数据字典表。打 开sql.bsq会发现很多数据字典几乎都以$结尾，比如col$,tab$等。

数据字典的组成：

包括数据字典基表和数据字典视图 ，其中基表存储数据库的基本信息,普通用户不能直接访问数据字典的基表，数据字典视图是基于数据字典基表所建立的视图，普通用户可以通过查询数据字典视图取得系统信息。数据字典视图主要包括user_xxx、all_xxx、dba_xxx三种类型：

1)user_tables：用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表

比如:select table_name from user_tables;

2)all_tables:用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其他方案的表

如：select table_name from all_tables;

3）dba_tables:它会显示所有方案拥有的数据库表，但是查询这种数据库字典视图，要求用户必须是dba角色或是有select any table的系统权限

如：select table_name from dba_tables;

在建立用户时，oracle会把用户的信息存放到数据字典中，当给用户授予或是角色时，oracle会将权限和角色的信息存放到数据字典中;

a)通过查询dba_users可以显示所有数据库用户的详细信息;

b)通过查询数据字典视图dba_sys_privs，可以显示用户具有的系统权限

c)通过查询数据字典视图dba_tab_privs可以显示用户具有的对象权限

d)通过查询数据字典dba_col_privs可以显示用户具有的列权限

e)通过查询数据库字典视图dba_role_privs可以显示用户所具有的角色下面将一些重要的查询句：

• 1）查询oracle中所有的角色,一般是dba用户登录下;
  如：select * from dba_roles;
• 2)查询oracle中所有的系统权限，一般是dba用户登录下;
  如：select * from system_privilege_map order by name;
• 3)查询oracle中所有的对象权限,一般是dba用户登录下;
  如:select distinct privilege from dba_tab_privs;
• 4)查询数据库的表空间
  如：select tablespace_name from dba_tablespaces;
  select * from dba_roles;

查看数据字典：

数据字典：dba_tablespaces、dba_users(管理用户查看的)。user_tablespaces、user_users(普通用户查看的)

eg：
desc dba_tablespaces;

desc(describe)用来描述指定表名的表结构
desc(descend)用于order by 后降序

三、表空间

SQL Server数据库与Oracle数据库之间最大的区别要属表空间设计。Oracle数据库开创性地提出了表空间的设计理念，这为Oracle数据库的高性能做出了不可磨灭的贡献。可以这么说，Oracle中很多优化都是基于表空间的设计理念而实现的。

表空间概述：

在数据库系统中，存储空间是较为重要的资源，合理利用空间，不但能节省空间，还可以提高系统的效率和工作性能。 Oracle 可以存放海量数据，所有数据都在数据文件中存储。而数据文件大小受操作系统限制，并且过大的数据文件对数据的存取性能影响非常大。同时Oracle 是跨平台的数据库， Oracle 数据可以轻松的在不同平台上移植，那么如何才能提供统一存取格式的大容量呢？ Oracle 采用表空间来解决。

表空间只是一个逻辑概念，若干操作系统文件（文件可以不是很大）可以组成一个表空间。表空间统一管理空间中的数据文件，一个数据文件只能属于一个表空间。一个数据库空间由若干个表空间组成。如图所示：

表空间分类：

永久表空间（数据库中要永久化存储的一些对象，比如表，视图，存储过程）、临时表空间（数据库操作当中中间执行的一些过程，当执行结束之后就释放掉）、UNDO表空间（保存事务修改前的旧址）

创建表空间：

Linux版本创建表空间

--创建表空间 linux centos6.9

--创建一个表空间，包含两个数据文件大小分别是 10MB， 5MB，要求 extent 的大小统一为 1M

CREATE TABLESPACE nod_tablespace

DATAFILE '/home/oracle/tablespace/A.ORA' SIZE 10M,

'/home/oracle/tablespace/B.ORA' SIZE 5M

EXTENT MANAGEMENT LOCAL

UNIFORM SIZE 1M

windows版本创建表空间

请注意：只有管理员才可以增加表空间

当表空间不足的时候可以用alter tablespace命令向表空间中追加数据文件并扩充表空间

修改表空间大小：

--扩充表空间

ALTER TABLESPACE nod_tablespace

ADD DATAFILE '/home/oracle/tablespace/C.ORA' SIZE 100M

查看表空间情况：

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name

FROM dba_free_space

GROUP BY tablespace_name;

创建用户时可以为用户指定某一表空间，那么该用户下所有数据库对象默认都存储在该空间中

指定用户的表空间为

create user nod identified by nod default tablespace nod_tablespace;

创建表指定表空间：

create table t2(id int)

tablespace nod_tablespace

表和索引一旦创建，表空间无法进行二次修改

四、约束

定义：
约束是ORACLE提供的自动保持数据库完整性的一种方法，它通过限制字段中数据、记录中数据和表之间的数据来保证数据的完整性。

分类：
a.列级约束：列级约束必须在列的定义后面
b.表级约束：表级约束不与列一起，而是单独定义的

完整性约束的基本语法格式：
[CONSTRAINT constraint_name(约束名)]<约束类型>、
说明：约束不指定名称时，系统会给定一个名称。

主键约束(primary key constraint)

用于定义基本表的主键，它是唯一确定表中每一条记录的标识符，其值不能为null也不能重复，以此来保证实体的完整性。表中主键只能有一个，但可以由多个列构成。

a.创建表时添加主键约束

Create table stuent(
	Sid number(8,0),
	Name varchar2(20),
	Sex char(2), 
	Birthday date,
	Addess varchar2(50),
	Constraint sid_pk primary key(sid)
);

b.修改表时添加主键约束

alter table studet add constraint sid_pk primary key(sid);

c.删除主键约束的方式

DROP PRIMARY KEY;

唯一性约束(unique constraint)

用于指定一个或者多个列的组合值具有唯一性，以防止在列中输入重复的值

注意事项：
a.使用唯一性约束的列允许为空值
b.一个表中可以允许有多个唯一性约束
c.可以把唯一性约束定义在多个列上

a.创建表时设置唯一性约束

Create table stuent(
	Sid number(8,0),
	Name varchar2(20),
	Sex char(2), 
	Birthday date,
	Addess varchar2(50),
	Email varchar2(50) unique, //直接设置唯一性约束  会有一个默认名
	Cardid varcha2(18), 
	Constraint uk_cardid unique(cardid)  //设置唯一性约束并命名
);

b.修改表时添加唯一性约束

Alter table studnt
		Add constraint uk_cardid unique(cardid);

默认约束(default constraint)

Create table stuent(
    	Sid number(8,0),
    	Name varchar2(20),
    	Cname varchar2(20) default "吴亦凡" //这里就是默认约束
    	Sex char(2), 
    	Birthday date,
    );

非空约束(not null consraint)

用于确保列不能为null，如果列定义了NOT NULL约束，那么当插入数据时，必须为该列提供数据；当更新列数据时，不能将其值设置为NULL。

非空（not null）属于列级约束

a.创建表时添加非空约束

Create table stuent(
	Sid number(8,0),
	Name varchar2(20) not null,     //直接非空约束，会给一个默认名
	Sex char(2) cnstraint nn_sex not null,  //添加非空约束并命名为nn_sex
	Birthday date,
	Addess varchar2(50)
);

b.修改表时添加非空约束：（与其他约束有所不同）

修改其他约束语法格式：

ALTER TABLE table_name
	ADD[CONSTRAINT constraint_name] constraint_type(column);

添加非空约束时语法格式：使用MODIFY语句

ALTER TABLE table_name
		MODIFY (column_name datatype NOT ULL)

删除非空约束的方式

ALTER TABLE table_name
			MODIFY column_name datatype NULL;

c.删除约束：

将约束无效化或激活（暂时性删除）

DISABLE | ENABLE CONSTRAINT constraint_name

将约束彻底删除

DROP CONSTRAINT constraint_name

删除主键约束的方式

DROP PRIMARY KEY;

删除非空约束的方式

ALTER TABLE table_name
		MODIFY column_name datatype NULL;

检查约束(check constraint)

用于对输入列或者整个表中的值设置检查条件。以限制输入值，保证数据库的完整性。（如对性别的要求）

a.创建表时设置检查约束

Create table stuent(
	Sid number(8,0),
	Name varchar2(20),
	Sex char(2) check(sex=’男’ or sex=’女’),
	Birthday date,
	Addess varchar2(50),
);

或者

Create table stuent(
	Sid number(8,0),
	Name varchar2(20),
	Sex char(2), 
	Birthday date,
	Addess varchar2(50),
	Constraint ck_sex check(sex=’男’ or sex=’女’)
);

b.修改表时添加检查约束

Alter table stdentt
		Add constraint ck_sex check(sex=’男’ or sex=’女’);

c.删除检查约束

禁用：alter table student disable constraint ck_sex; 彻底删除：alter table student drop constraint ck_sex;

外部键约束(foreign key constraint)

用于建立和加强两个表数据之间的链接的一列或多列。外间约束是唯一涉及两个表关系的约束

先创建的表叫主表 后创建的表叫从表 有一列的内容是相同的作为外键

设置外键约束的语法格式

a.列级约束

CREATE TABLE 从表
	(column_name datatype REFERENCES
主表(column_name datatype)[ON DELETE CASCADE],...);
						//这里的 ON DELETE CASCADE代表着是否级联删除

什么是级联删除？

删除包含主键值的行的操作，该值由其它表的现有行中的外键列引用。在级联删除中，还删除其外键值引用删除的主键值的所有行。

例如员工表中一项数据是部门ID,部门ID是部门表的主键，如果是级联删除，当删除了部门A的时候，会把所有属于部门A的员工都给删除。

b.表级约束(创建的是从表)
在表加入

CONSTRAINT constraint_name FOREIGN KEY(column_name)
REFERENCES 主表(column_name)[ON DELETE CASCADE]

创建表时设置外面约束(方法1)

主表：

Create table department(
					Depid varchar2(10) primary key,
					Depname varchar2(30)
);

从表：

Create table stuent(
			Sid number(8,0),
			Name varchar2(20),
			Sex char(2), 
			Birthday date,
			Addess varchar2(50),
			Depid varchar2(10) references department(depid)
);	
//Depid varchar2(10) references department(depid)
//Constraint fk_depid foreign key(depid)
//References department(depid)
//On delete cascade

创建表时设置外面键约束(方法2)

主表：

Create table department(
					Depid varchar2(10) primary key,
					Depname varchar2(30)
);

从表：

Create table stuent(
			Sid number(8,0),
			Name varchar2(20),
			Sex char(2), 
			Birthday date,
			Addess varchar2(50),
			constraint fk_depid foreign key(depid)
			references department(depid)
			on delete cascade
);

外键约束注意事项：
a.设置外键约束时，主表的字段必须时主键列（或唯一列）
b.主从表中相应字段必须是同一数据类型
c.从表中外键字段的值必须来自主表中的相应字段的值，或者为null

修改表时添加外键约束

Alter table student  Add constraint fk_depid foreign key(depid) References department(depid) On delete cascad