Oracle0817

最新推荐文章于 2023-03-14 07:50:38 发布

一头拱白菜的猪ll

最新推荐文章于 2023-03-14 07:50:38 发布

阅读量251

点赞数

文章标签： oracle 数据库 java

本文链接：https://blog.csdn.net/weixin_45772393/article/details/126391480

版权

6.2.2 表结构设计

表与列的命名

表和列的名称要求
- 长度必须在1~30个字节之间
- 必须以一个字母开头
- 能够包含字母、数值、下划线符号_、英镑符号#和美元符号￥。
- 不能使用保留字，如CHAR或是NUMBER。
- 若名称被围在双引号""中，唯一的要求是名字的长度在1~30个字符之间，并且不含有嵌入的双引号
- 每个列名称在单个表内必须是唯一的。
列的类型
- 字符数据类型
  - CHAR[()[BYTE|CHAR]]
  - NCHAR[()]
  - VARCHAR2([BYTE|CHAR])
  - NVARCHAR2()
- 大对象数据类型
  - CLOB
  - NCLOB
  - BLOB
  - BFILE
- 数字数据类型
  - NUMBER[([,])]
- 日期和时间数据类型
  - DATE
  - TIMESTAMP[()]
  - TIMESTAMP[()] WITH TIME ZONE
  - TIMESTAMP[()] WITH LOCAL TIME ZONE
  - INTERVAL DAY[()] TO SECOND
- 二进制数据类型
  - ROW()
  - LONG ROW
- 行数据类型
  - ROWID
  - UROWID
列的约束

Oracle通过为表的列定义各种约束条件来保证表中数据的完整性

在 Oracle中可以建立的约束条件包括NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY。
- NOT NULL约束
  
  NOT NULL即非空约束，主要用于防止NULL值进图到指定的列。
  
  NOT NULL约束有以下特点：
  - 定义了NOT NULL约束的列中不能包含NULL值或无值。在默认情况下，Oracle允许在任何列中有NULL值或无值。如果再某个列上定义了NOT NULL约束，则插入数据是就必须为该列提供数据。
  - 只能在单列上定义NOT NULL约束条件
  - 在同一个表中可以在多个列上分别定义NOT NULL约束。
- UNIQUE约束
  
  唯一约束，该约束用于保证在该表中指定的各列的组合中没有重复的值。其主要特点如下：
  - 定义了UNIQE约束的列不能包含重复的值，但如果再一个列上仅定义了UNIQUE约束，而没有定义NOT NULL约束，则该列可以包含多个NULL值或无值。
  - 可以为单个列定义UNIQE约束，也可以为多个列的组合定义UNIQE约束。因此，UNIQE约束既可以在列级定义，也可以在表级定义。
  - Oracle会自动为具有UNIQUE约束的列建立一个唯一索引。如果这个列已经具有唯一或非唯一索引，Oracle将使用已有的索引
  - 对同一个列，可以同时定义UNIQUE约束和NOT NULL 约束
  - 在定义UNIQUE约束时可以为他的索引指定存储位置和存储参数。
- CHECK 约束
  
  即检查约束，其用于检查在约束只能指定的条件是否的得到了满足。
- PRIMARY KEY约束
  
  PRIMARY KEY约束即主键约束，其用来唯一地标识出表的每一行，并且防止出现NULL值。一个表只能有一个主键约束。PRIMARY KEY约束具有如下的特点。
  - 定义了PRIMARY KEY约束的列（或列组合）不能包含重复值，并且不能包含NULL值
  - Oracle会自动为具有PRIMARY KEY 约束的列建立一个唯一索引（unique index）和一个NOT NULL约束
  - 同一个表只能够定义一个PRIMARY KEY 约束的列（或组合）
  - 可以在一个列上定义PRIMARY KEY 约束，也可以在多个列的组合上定义PRIMARY KEY 约束。因此，PRIMARY KEY 约束及可以在列级定义，也可以在表级定义
- FOREIGN KEY 约束
  
  FOREIGN KEY 约束即外键约束，通过使用外键，保证表与表之间的参照完整性。在参照表上定义的外键需要参照主表的主键。

6.2.3 表的创建

用CREATE TABLE命令创建表

基本语法格式是：
```
create [[global] temporpry|table|schema.]table_name
(column1 datatype1 [default expq][column1 constaint],
 column2 datatype2 [default exp2][column1 constraint]
[table constraint])
[on commit {delete|preserve} rows]
[organizition {help|index|externai...}]
[partition by...(...)]
[tablespace tablespace_name]
[logging | nologging]
[compress|nocompress];
```
其中：
- column datatype1 为列指定数据类型
- default exq1 为指定默认值
- column constraint为列定义完整性约束(constraint)
- [table constraint]为表定义完整性约束(constraint)
- [organizition {help|index|external…}]为表的类型，如关系型（标准、按堆组织）、临时型、索引型、外部型或对象型。
- [partition by…(…)]为分区及子分区信息
- [tablespace tablespace_name]指示用于存储表或索引的表空间
- [logging | nologging] 是否保留重做日志
- [commpress|nocompress] 知识是否压缩。

6.2.4 修改表结构

普通用户只能对自己方案中的表进行更改

用ALTER TABLE命令修改表结构
- 增加列
```
alter table [schema.] table_name add(column defintion1, column defition2);
```
  新添加的列总是位于表的结尾。column definition 部分包括列名、列的数据类型以及将具有的任何默认值
- 更改列
```
alter table [schema.] table_name modify(column_name1 new_attributes1,
       column_name1 new_attributes2...);
```
- 直接删除列
```
alter table [schema.] table_name drop 
(colume_name1, column_name2...)
[cascade constraints]
```
  如果删除的列是一个多列约束的组成部分，那么就必须指定cascade constraint选项，这样才会删除相关的约定。
- 将列标记为UNUSED状态
  
  为了避免在数据库使用高峰期间由于执行删除列的操作而占用过多系统资源，可以暂时通过ALTERTABLE SET UNUSED 语句将要删除的列设置为UNUSED状态。
  
  该语句的语法格式为
```
altertable [schema.] table_name set unused(column_name1, column_name2)
[cascade constraints];
```
  被标记为UNUSED状态的列与被删除的列之间是没有区别的，都无法通过数据字典或在查询中看到。另外、甚至可以为表添加与UNUSED状态具有相同名称的新列。
  
  在数据字典视图USER_UNUSED_COL_TABS, ALL_UNUSED_COLTABS和DBA_UNUSED_COL_TABS中可以查看到数据库有哪些表哪几列被标记为UNUSED状态。

6.3 索引

索引是将创建列的键值和对应记录的物理记录号（ROWID）排序后存储起来，需要占用额外的存储空间来存放。

6.3.2 创建索引

用SQL*Plus创建索引

创建索引的语法格式为：
```
create [unique]|[bitmap] index [schema.] index_name
on [schema.] table_name([column1[ASC|DESC], column2[ASC|DESC], ...]|[express])
[tablespace tablespace_name]
[pctfree n1]
[storage(initial n2)]
[compress na]|[nocompress]
[logging]|[nologging]
[online]
[compute statistics]
[reverse]|[nosort];
```
其中：
- pctfree选项用于指定为将来的insert操作所预留的百分比空间。假定表已经包含了大量数据u，那么在建立索引时应该仔细规划pctfree的值，以便为以后的insert操作预留空间。
- tablespace选项用于指定索引段所在的表空间
- 如果不指定bitmap选项，则默认创建的是B树索引。

6.3.3 删除索引

若出现如下几种情况之一将有必要删除相应的索引

索引的创建不合理或不必要，应删除该索引，已释放其占用的空间
通过一段时间的监控，发现几乎没有查询，或者只有极少数查询会使用到该索引
由于该索引中共包含损坏的数据块，或者包含过多的存储碎片，需要首先删除该索引，然后再重建该索引。
如果移动了表的数据，导致索引无效，此时需要删除并重建该索引
当使用SQL*Loader 给单个表装载数据是，系统也会同时给该表的索引增加数据，为了加快数据装载速度，应在装载之前删除所有索引，然后再数据装载完毕之后重新创建各个索引。

如果索引是使用create index语句创建的，可以使用drop index语句删除索引；如果索引是在定义约束时由Oracle自动创建的，则可以通过禁用约束（disable）或删除约束的方式来删除对应的索引。

在SQL*Plus中删除索引
```
drop index index_name;
```

6.4 视图

视图是由select子查询语句定义的一个逻辑表，只有定义而无数据，因此它是一个“虚表”。

使用视图的优点：提供各种数据表现形式、提供某些数据的安全性、隐藏数据的复杂性、简化查询语句、执行特殊查询、保存复杂查询等。

6.4.1 视图的概念

使用视图的作用主要表现在以下几个方面：

提供面向用户的数据表现形式
提供面向用户的安全性保证
隐藏数据的逻辑复杂性
简化用户权限的管理
重构数据库的灵活性

6.4.2 创建视图

语法

创建视图时，视图的名称和列名必须符合表的命名规则，但又建议使用另一种命名习惯，以便区分表盒视图。
```
create [or replace] [force] view [schema.] view_name
[(column1, column2...)]
as
select...from...where...
[with check option][constraint constraint_name]
[with read only];
```
- force ：强制创建视图，不考虑基表是否存在，也不考虑是否具有使用基表的权限。
- schema：指出在哪个方案中创建视图
- with：使用视图时，检查涉及的数据是否能通过select子查询的where条件，否则不允许操作并返回错误提示。
- constraint constraint_name：当使用with check option 选项时，用于指定该视图的该约束的名称。如果没有提供一个约束名称，Oracle就会生成一个以SYS C 开头的约束名称，后面是一个唯一的字符串
- with read only：创建的视图只能用于查询数据，而不能用于更改数据。该子句不能与order by子句同时存在。
注意：同所有的子查询一样，定义视图的查询不能包含for update子句。

在Oracle中，提供强制创建视图的功能是为了使基表的创建和修改与视图的创建和修改之间没有必然的依赖性，便于同步工作，提高工作效率，并且可以据需进行目前的工作。
创建视图的步骤
- 编写select子查询语句
- 测试select子查询语句
- 检查查询结果的正确性
- 使用该select子查询语句创建视图，并注意命名方面与选项方面的规定。
复杂视图是指视图的select子查询中包含函数、表达式或分组数据的视图。使用复杂视图的主要目的是为了简化查询操作。

6.4.4 删除视图

drop view view_name ;

6.5 数据操纵与数据查询

6.5.1 复制原表插入记录

在Oracle中，可以使用create table table_name as 语句来创建一个表并且向其中插入记录。

创建表High_salary，该表对应了employees表中月薪超过5000元的雇员信息

create table High_Salary 
as 
select * from employees 
where salary > 5000.00;

6.5.2 使用视图

用视图进行插入

使用视图进行插入时，插入的数据需要满足对应基表相关约束。

创建一个视图v_department，再使用insert语句向视图中插入数据。

create view v_department 
as 
select department_id, department_name, manager_id
from departments
where location_id = 1700;

insert into v_department
value(999, 'bacmp', 205);

用视图进行修改
- 修改视图v_department中的记录
```
update v_department
set manager_id = 206
where department_id = 110;
```
  修改视图中的数据同样需要通过对应基表相关约束的检查。

用视图进行删除

select * from v_department 
where manager_id is not null;

delete from v_department 
where department_id = 300;

select * from v_department 
where manager_id is not null;

6.5.3 使用PL/SQL语言

declare
emp_id number(6);
dep_id number(4);
fname varchar2(20);
Iname varchar2(25);
c_email varchar2(25);
phone varchar2(20);
job varchar2(10);
n_salary number(6,2);
m_id number(6);
it_id number(4);

select department_id into it_id 
from departments 
where department_name = 'IT';
cursor cur1 is select department_id, employee_id, first_name, last_name, email, phone_number, job_id, salary, manager_id
              from employees;

begin 
   open cur1;
   fetch cur1 into dep_id, emp_id, fname, lname, c_email, phone, job, n_salary, m_id;
loop
    exit when cur1%NOTFOUND;
    if dep_id = it_id then
       insert into IT_EMPLOYEES
       values(emp_id, fname, lname, c_email, phone, job, n_salary, m_id);
    else 
    end if;
    fetch cur1 into dep_id, emp_id, fname， lname, c_email, phone, job, n_salary, m_id;
end loop;
close cur1;
end;

6.5.4 数据查询

一般条件查询

select employee_id, first_name, salary from employees where salary > 5000.00;

组合条件查询

完成对表employees中IT部门的雇员信息查询，IT部门的部门编号从departments表中获得

select employee_id, first_name, salary
from employees emp, departments dep
where department_name = 'IT' 
      and emp.department_id = dep.department_id;

完成对表employees中共IT部门月薪超过5000元的雇员信息查询

select employee_id, first_name, salary
from employees emp, departments dep
where department_name = 'IT' 
      and emp.department_id = dep.department_id
      and salary > 5000.00;

用group进行分组查询

查询部门名称、员工数量、总薪资和平均月薪，语句如下

select dep.department_name, count(emp.employee_id)dep_count, sum(emp.salary)total_salary, avg(salary)average_salary 
from  employees emp, departments dep
where emp.department_id = dep.department_id
group by dep.department_name;

查询平局工资超过5000元的部门的部门名称、员工数量、总薪资和平均薪资。

由于在条件比较中不能使用组函数，首先创建视图dep_salary获得部门编号和对应的员工数量、总薪资和平均月薪信息，语句如下：
```
create view dep_salary as
select department_id, count(employee_id)dep_salary
from employees emp 
group by department_id;
```
再由视图dep_salary和表departments做组合查询，获得所需信息，对应语句
```
select dep.department_name, average_salary
from dep_salary, departments dep
where dep_salary.department_id =dep.department_id
      and average_salary > 5000.00;
 
```

第七章 Oracle数据库管理操作

7.1 学会使用视图

7.1.1 增加安全性

在需要授予用户只对表的一部分访问权限的情况下，选择通过视图进行权限设置对于系统的安全非常有效。

在许多情况下，需要更精确地授予用户的权限，而不仅仅是一个表的授权。例如：

不应将员工表中存储的个人信息或敏感信息的访问权授予那些需要访问该表其他部分用户
用户可能希望授予销售代表更新表的权限，包括更新其销售电话的说明，但此权限要仅限于其自己的电话。

通过视图用户只能查看和修改他们所能看到的数据，其他数据库或表既不可见也不可以访问。

使用视图增加安全性的具体操作过程如下：

建立视图，将不允许用户访问的数据隐藏起来
将对视图权限通过使用grant语句授予相关用户。
示例
- 销售经理需要访问数据库中有关该部门员工的信息。但是，该经理没有理由访问有关其他部门员工的信息。
  
  该示例描述了在人力资源HR示例数据库中，如何成为销售经理创建用户ID，如何创建可提供其所需信息的视图，以及如何给此销售经理用户ID授予相应的权限。
  - 使用grant语句创建新用户ID。在使用具有系统管理员（DBA）权限的用户ID登录时，其代码如下：
```
conn sys/zzuli as sysdba
```
  - 激活人力资源示例数据库账户，连接到HR示例数据库
```
Alter user hr identified by hr account unlock
conn hr/hr
```
  - 为销售经理创建用户ID，用户名为salesmanager，密码为sales，并将connect权限授予销售经理
```
grant connect to salesmanager identified by sales
```
  - 定义一个只查看员工编号和姓名的视图，将数据库中员工的工资等星系隐藏起来
```
create view emp_sale
as 
select employee_id, first_name, last_name
from hr.employees;
```
  - 授予salesmanager查看该视图的权限
```
grant select on emp_sale to salesmanager
```
  - 让销售经理以用户salesmanager登录数据后，销售经理可以查看员工的编号和姓名，但是不允许销售经理查看员工的工资信息，这样就一定程度上保证了数据的安全性。
```
conn saleamanager/sales
select * from sys.emp_sale;
select salary from hr.employees;
```

7.1.2 隐藏数据的复杂

示例

现有机场数据库，拥有PilotSkills表和Hangar表，其中表PilotSkills描述了飞行员和他们能够驾驶的飞机信息，表Hangar描述了停在飞机棚中的飞机信息。现要求查询能够驾驶飞机棚中共每一架飞机的飞行员的姓名。

提示：该查询的实现代码非常复杂，需要用到除法运算，他的思想是用除数除去被除数表，产生商或结果表。将PilotSkills表用飞机棚中共的飞机去除，就可以得到结果。

首先创建两个表，用PilotSkills表来描述分星宇以及飞行员可以驾驶的飞机信息，用Hangar表来描述飞机鹏中的飞机信息。

--创建PilotSkills表
create table PilotSkills
(
pilot char(15) not null,
plane char(15) not null,
primary key(pilot, plane));

--创建Hangar表
create table Hangar
(plane char(15) primary key);

为实现“找出能够驾驶飞机棚中每一架飞机的飞行员的名字”，可以创建一个视图

create view QualifiedPilots(pilot)
as
select distinct pilot
from PilotSkills PS1
where not exists(select * from Hangar 
                  where not exists(select * from PilotSkills PS2
                                   where (PS1.pilot = PS2.pilot)
                                    and (PS2.plane = Hangar.plane)));

另一种方法

create view QualifiedPilots(pilot)
as
select PS1.pilot
from PilotSkills PS1, Hangar H1
where PS1.plane = H1.plane
group by PS1.pilot
having count(PS1.plane) = (select count(plane) from Hangar);

用以下代码实现“找出能够驾驶飞机棚中每一架飞机的飞行员姓名”

select pilot from Qualifiedpilots；

一头拱白菜的猪ll

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle0817

现有机场数据库，拥有PilotSkills表和Hangar表，其中表PilotSkills描述了飞行员和他们能够驾驶的飞机信息，表Hangar描述了停在飞机棚中的飞机信息。在Oracle中，提供强制创建视图的功能是为了使基表的创建和修改与视图的创建和修改之间没有必然的依赖性，便于同步工作，提高工作效率，并且可以据需进行目前的工作。该示例描述了在人力资源HR示例数据库中，如何成为销售经理创建用户ID，如何创建可提供其所需信息的视图，以及如何给此销售经理用户ID授予相应的权限。...
复制链接

扫一扫