Java12---数据库设计，数据字典，序列，视图，索引，表空间，权限控制_指标建模数据字典表设计

导读

1.数据库设计范式
2.实体与关系
3.数据字典
4.序列
5.视图
6.索引
7.表空间与权限控制

数据库设计:
概念模型–>逻辑模型–>物理模型–>转换成SQL,创建表等对象
Power Design等软件都可以绘制ER图
物理模型可以直接转换成SQL语句

数据库设计范式

数据库设计范式:用来规范数据库的设计
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
后一个是基于前一个的,比前一个更严格通常遵循到第三范式。

1NF:
列应该是不可拆分的,原子的,不应该包含多个字段值

2NF:
要求数据库表中的每个实例或记录必须可以被唯一地区分
要求每个表都有一个主键列,能唯一的代表这个表,其它的列完全依赖于这个列
我们通常会选择一个与业务无关的列id

3NF:
要求一个关系中不包含已在其它关系已包含的非主关键字信息 ,消除冗余
实际中,有时为了查询的方便,反而会适当加一个冗余字段

eg:
	部门表:
	id ,name, region_id
	name完全依赖于id
	
	员工表:
	id,first_name,last_name,start_date,dept_id,name
	
	每个员工都有一个部门编号
	dept_id依赖id
	name依赖dept_id
	name间接依赖id
这里3NF要求不存在间接依赖，所以去掉员工表的name，但这不是绝对的
1
2
3
4
5
6
7
8
9
10
11
12
13

实体与关系

实体:表
实体的关系:表的关系

	一对一:
		丈夫和妻子
		一个丈夫最多只能有一个妻子
		一个妻子最多只能有一个丈夫
			外键:两侧都可以
		
		人和护照
			人可能没有护照
			护照一定有对应的人
			外键:在护照一侧加外键合理
		
	一对多:
		父亲和孩子
		一个父亲可以有多个孩子
		一个孩子只能有一个父亲
			
		
		部门和员工
			一个部门可以有多个员工
			一个员工属于一个部门
			在多的一侧加外键（在员工侧加）
			
	多对多:
		学生和老师
			一个学生可以有多个老师
			一个老师可以有多个学生
			一般我们会创建一个中间表
			id stu_id(外键)  tea_id(外键)
			1    		1      1
			2    		1      2
			3    		1      3
			4    		2      1


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
27
28
29
30
31
32
33
34

数据字典

数据字典
数据库中的表,一部分是用户自己创建的,用户表
还有一部分是安装时就创建了,为了维护数据库的正常运行,称为数据字典表

分类:
	USER开头,当前用户下各种信息相关的表,比如USER_TABLES
	ALL开头,所有有权限看到的用户的各种信息相关的表
	DICTIONARY:数据字典表的字典表，即数据字典的目录
	DBA开头:管理员
	V$:跟性能相关的表
	TABLE_PRIVILEGES:跟权限相关的表
1
2
3
4
5
6
7

介绍一些常用字典表:	
USER_TABLES:当前用户下的表相关的信息	
	eg:SELECT table_name FROM user_tables;
	
USER_USERS:当前用户的相关信息
	show user
	SELECT username FROM user_users;
	
USER_CONSTRAINTS:当前用户约束相关的信息
	能查到约束名,对应的表名
	SELECT constraint_name,table_name
	FROM user_constraints;
	
USER_CONS_COLUMNS:
	能查到约束名,对应的表名,对应的列名
	SELECT constraint_name,table_name,column_name
	FROM user_cons_columns;
	
USER_VIEWS:视图

USER_SEQUENCES:序列

USER_INDEXES:索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

序列

序列:SEQUENCE 掌握
为了达到列自动增长,使用序列
序列:就像一个取号机,取的号可以一次递增
通常:会为每一个表创建一个序列

创建语法: 
	CREATE SEQUENCE 序列名
	START WITH n             以n开始,默认从1开始
	INCREMENT BY n           每次增加n,默认每次增加1
	MAXVALUE n|NOMAXVALUE    上限,默认无上限
	MINVALUE n|NOMINVALUE    下限,默认无下限
	CYCLE|NOCYCLE            是否是循环的,默认不循环
	CACHE n|NOCACHE;         缓存n个数,默认缓存20个

	注意:这些参数无前后顺序
1
2
3
4
5
6
7
8
9
10

给学生表创建一个序列:
	CREATE SEQUENCE student_id_seq
	START WITH 2
	INCREMENT BY 1;
	
序列的使用:
	序列名.NEXTVAL:取序列的下一个值
	序列名.CURRVAL:得到序列刚刚取过的值,如果本次连接中没有去过,会报错

INSERT INTO student (id,name,sex,age,sno)
VALUES (student_id_seq.NEXTVAL,'bb',1,15,3);

修改序列:了解
ALTER SEQUENCE 序列名
...;
只有起始值不能修改

删除序列:了解
DROP SEQUENCE 序列名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

视图

视图:VIEW

定义视图,只包含源表部分字段,源表隐藏,此时利用视图查看源表的数据
只能视图中包含的数据,达到只能看到公开的数据
定义视图,我们经常多表联查,使用频率很高,写起来麻烦
可以针对多表联查定义一个视图,查看多表数据通过视图即可,简化语句

本质上:视图就是一个起了名字的查询语句,没有存储数据的功能
查看视图,实际上还是查看源表

视图分类:
简单视图:数据来源于一张表,不能使用函数和分组,默认可以增删改查
复杂视图:数据来源于多张表,或者包含函数,分组等等,只能读

使用视图前需要管理员授予用户权限
管理员授予创建视图权限:CREATE VIEW
GRANT CREATE VIEW TO 用户名;
1
2
3

CREATE VIEW 视图名
AS 子查询;

为了能够让45号部门员工看到到他们的信息,定义一个视图
CREATE VIEW s_empv45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45;

OR REPLACE:如果不存在则创建,如果已存在则替换
eg:
CREATE OR REPLACE VIEW s_empv45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

使用视图查询:
SELECT 字段
FROM 视图名;
1
2
3

FORCE:如果创建视图时,表不存在,force强制创建,但是不能使用
		默认NOFORCE
WITH READ ONLY:针对于简单视图，默认是可以DML的，
但WITH READ ONLY只能通过视图读,不能DML
		
CREATE OR REPLACE VIEW s_empv45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45
WITH READ ONLY;
1
2
3
4
5
6
7
8
9
10

WITH CHECK OPTION:
	如果简单视图可以增删改,加了这个选项后,只能改变成不违背where的值,否则无法改变
	
CREATE OR REPLACE VIEW s_empv45
AS SELECT id,first_name,title,dept_id,salary
FROM s_emp
WHERE dept_id=45
WITH CHECK OPTION;	

UPDATE s_empv45
SET dept_id=50
WHERE id=10;  会报错
1
2
3
4
5
6
7
8
9
10
11
12

索引

索引:INDEX
作用:提高查找的效率
使用:首先数据量大,查找的频率高,结果占总数少,可以考虑定义索引

伪列:rowid–利用它可以找到真实的记录的物理地址,就可以查找到这一行，是查找记录效率最高的方式
默认情况下,查找一行,从上到下一行一行比较,
如果数据量很大,而且我要查找的数据占总数量比较少,
针对这一列定义索引,把这个列值及对应的rowid一起组织存储,按照这个列排序
在查询时,可以快速找到这个列值,就可以找到rowid,通过rowid查找对应的行

主键列,唯一约束列自动创建索引,
可以针对非唯一列手动创建索引
代价:如果这个列DML,索引需要更新,耗费资源
建议：给列定义索引,查询频率高,DML相对少

创建索引:
CREATE INDEX 索引名
ON 表名(列1,...);

如果针对列创建了索引,在查询时,会自动通过索引来查找
建议:面试时主要问索引的分类,相互区别
1
2
3
4
5
6

表空间

安装11g简化版数据库之后,启动创建一个数据库实例,名字XE,这个数据库实例维护对应的数据库，就可以直接通过客户端与服务器端进行交互
DBMS:数据库管理系统,利用数据里管理系统来维护数据库文件

表空间:
	数据库物理文件的逻辑映射
	一个表空间可以对应一个或多个文件,
	是最大的逻辑结构
	一个数据库可以有多个表空间
	
	在数据库默认有一些表空间,
	SYSTEM:在安装或创建数据库时就创建了,用来存储系统的数据
			默认情况下,创建的用户使用的表空间就是SYSTEM	
			TEMP:是一个临时表空间,用来存储一些临时数据,系统临时数据都存储在temp临时表空间中
	SYSAUX:SYSTEM辅助表空间
	...
1
2
3
4
5
6
7
8
9
10
11
12

查找用户对应的表空间和临时表空间:需要管理员权限
SELECT username,user_id,default_tablespace（使用的表空间）,temporary_tablespace（临时表空间）
FROM dba_users;
	
	
查找数据文件路径:需要管理员权限
SELECT file_name
FROM sys.dba_data_files;

创建表空间:
CREATE TABLESPACE 表空间名 
DATAFILE '路径'   
SIZE 起始小大   
AUTOEXTEND ON NEXT 每次扩展大小 MAXSIZE 上限
EXTENT MANAGEMENT LOCAL;

eg:
CREATE TABLESPACE test_dataspace 
DATAFILE 'E:\oracle_data\test_data.dbf'   
SIZE 256M  
AUTOEXTEND ON NEXT 5M MAXSIZE 512M
EXTENT MANAGEMENT LOCAL;

创建临时表空间:存储一些临时数据,比如排序产生的数据等
CREATE TEMPORARY TABLESPACE 临时表空间名 
TEMPFILE '路径'   
SIZE 起始小大   
AUTOEXTEND ON NEXT 每次扩展大小 MAXSIZE 上限
EXTENT MANAGEMENT LOCAL;

eg:
CREATE TEMPORARY TABLESPACE test_tempspace 
TEMPFILE 'E:\oracle_data\test_temp.dbf'   
SIZE 20M   
AUTOEXTEND ON NEXT 5M MAXSIZE 100M
EXTENT MANAGEMENT LOCAL;

创建用户时:如果没有指定表空间和临时表空间,则默认使用SYSTEM,TEMP
	CREATE USER 用户名 IDENTIFIED BY 密码;

指定表空间和临时表空间:
	CREATE USER 用户名 IDENTIFIED BY 密码
	DEFAULT TABLESPACE 表空间
	TEMPORARY TABLESPACE 临时表空间名;
	
eg:
CREATE USER test IDENTIFIED BY test
DEFAULT TABLESPACE test_dataspace
TEMPORARY TABLESPACE test_tempspace;
	
删除表空间:
DROP TABLESPACE oaecspace 
INCLUDING CONTENTS AND DATAFILES;
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

权限控制

系统默认有三个角色:
CONNECT
RESOURCE
DBA

授权:
GRANT  权限,...
ON 对象
TO 用户/角色/PUBLIC
[WITH GRANT OPTION];

PUBLIC:所有用户
WITH GRANT OPTION:权限的授予者还可以把权限赋予其他用户

修改密码:ALTER USER 用户名 IDENTIFIED BY 密码;
	要么管理员要么是当前用户
	
权限很多种,要给一个用户授权,可能要同时授予几十个等等,要给多个用户都授予这些权限等等。

角色:可以包含多个权限
可以根据需要定义角色,授权权限,在需要时给用户授予角色(对应的权限)即可	

回收权限:
REVOKE 权限,...
ON 对象
FROM 用户;

同义词:起个小名
	CREATE [PUBLIC] SYNONYM synonym_name FOR object_name;
	public:其他用户可以使用这个名字
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