内容发布更新时间 : 2024/5/8 10:44:29星期一 下面是文章的全部内容请认真阅读。

性别sex，身份证cardId）；

购买purchase（客户号customerId，商品号goodsId，购买数量nums）； 请用SQL语言完成下列功能： 1. 建表，在定义中要求声明： (1). 每个表的主外键；

(2). 客户的姓名不能为空值；

(3). 单价必须大于0，购买数量必须在1到30之间； (4). 电邮不能够重复；

(5). 客户的性别必须是 男 或者 女，默认是男；

SQL> create table goods(goodsId char(8) primary key, --主键 goodsName varchar2(30)，

unitprice number(10,2) check(unitprice>0), category varchar2(8), provider varchar2(30) );

SQL> create table customer( customerId char(8) primary key, --主键 name varchar2(50) not null, --不为空 address varchar2(50),

email varchar2(50) unique,

sex char(2) default '男' check(sex in ('男'，'女'))， -- 一个char能存半个汉字，两位char能存一个汉字 cardId char(18) );

SQL> create table purchase( customerId char(8) references customer(customerId),

goodsId char(8) references goods(goodsId), nums number(10) check (nums between 1 and 30) );

表是默认建在SYSTEM表空间的

维护

商店售货系统表设计案例（2）

如果在建表时忘记建立必要的约束，则可以在建表后使用alter table命令为表增加约束。但是要注意：增加not null约束时，需要使用modify选项，而增加其它四种约束使用add选项。 1. 增加商品名也不能为空

SQL> alter table goods modify goodsName not null; 2. 增加身份证也不能重复

SQL> alter table customer add constraint xxxxxx unique(cardId); 3. 增加客户的住址只能是’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’；

SQL> alter table customer add constraint yyyyyy check (address in (’海淀’,’朝阳’,’东城’,’西城’,’通州’,’崇文’,’昌平’));

删除约束

当不再需要某个约束时，可以删除。

alter table 表名 drop constraint 约束名称； 特别说明一下：

在删除主键约束的时候，可能有错误，比如： alter table 表名 drop primary key；

这是因为如果在两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选项 如像：

alter table 表名 drop primary key cascade;

显示约束信息 1.显示约束信息

通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。

select constraint_name, constraint_type, status, validated from user_constraints where table_name = '表名'; 2.显示约束列

通过查询数据字典视图user_cons_columns，可以显示约束所对应的表列信息。 select column_name, position from user_cons_columns where constraint_name = '约束名';

3.当然也有更容易的方法，直接用pl/sql developer查看即可。简单演示一下下...

表级定义 列级定义 列级定义

列级定义是在定义列的同时定义约束。 如果在department表定义主键约束

create table department4(dept_id number(12) constraint pk_department primary key,

name varchar2(12), loc varchar2(12)); 表级定义

表级定义是指在定义了所有列后，再定义约束。这里需要注意： not null约束只能在列级上定义。

以在建立employee2表时定义主键约束和外键约束为例：

create table employee2(emp_id number(4), name varchar2(15), dept_id number(2), constraint pk_employee primary key (emp_id), constraint fk_department foreign key (dept_id) references department4(dept_id));

十四：Oracle索引、权限

管理索引-原理介绍 介绍

索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种我们主要介绍常用的几种： 为什么添加了索引后，会加快查询速度呢？

创建索引 单列索引

单列索引是基于单个列所建立的索引，比如： create index 索引名 on 表名(列名); 复合索引

复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必须不同，比如：

create index emp_idx1 on emp (ename, job); create index emp_idx1 on emp (job, ename);

使用原则 使用原则

1. 在大表上建立索引才有意义

2. 在where子句或是连接条件上经常引用的列上建立索引 3. 索引的层次不要超过4层

这里能不能给学生演示这个效果呢？ 如何构建一个大表呢？

索引的缺点 索引缺点分析

索引有一些先天不足：

1. 建立索引，系统要占用大约为表1.2倍的硬盘和内存空间来保存索引。 2. 更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的一致性。

实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

比如在如下字段建立索引应该是不恰当的： 1. 很少或从不引用的字段；

2. 逻辑型的字段，如男或女（是或否）等。 综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标。

其它索引 介绍

按照数据存储方式，可以分为B*树、反向索引、位图索引； 按照索引列的个数分类，可以分为单列索引、复合索引； 按照索引列值的唯一性，可以分为唯一索引和非唯一索引。 此外还有函数索引，全局索引，分区索引...

对于索引我还要说：

在不同的情况，我们会在不同的列上建立索引，甚至建立不同种类的索引，请记住，技术是死的，人是活的。比如：

B*树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不同值相对固定的列上。

显示索引信息

显示表的所有索引

在同一张表上可以有多个索引，通过查询数据字典视图dba_indexs和user_indexs，可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而user_indexs用于显示当前用户的索引信息：

select index_name, index_type from user_indexes where table_name = '表名';

显示索引列

通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息

select table_name, column_name from user_ind_columns where index_name = 'IND_ENAME';

你也可以通过pl/sql developer工具查看索引信息

管理权限和角色 介绍 介绍

这一部分我们主要看看oracle中如何管理权限和角色，权限和角色的区别在那里。

当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据库操作，则必须为其授予系统的权限；如果用户要访问其它方案的对象，则必须为其授予对象的权限。为了简化权限的管理，可以使用角色。这里我们会详细的介绍。看图：

权限 权限

权限是指执行特定类型sql命令或是访问其它方案对象的权利，包括系统权限和对象权限两种。

系统权限

系统权限介绍

系统权限是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或是一组数据库操作。比如当用户具有create table权限时，可以在其方案中建表，当用户具有create any table权限时，可以在任何方案中建表。oracle提供了100多种系统权限。 常用的有：

create session 连接数据库 create table 建表

create view 建视图 create public synonym 建同义词

create procedure 建过程、函数、包 create trigger 建触发器 create cluster 建簇

显示系统权限

oracle提供了100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们可以查询数据字典视图system_privilege_map，可以显示所有系统权限。

select * from system_privilege_map order by name;

授予系统权限 一般情况，授予系统权限是由dba完成的，如果用其他用户来授予系统权限，则要求该用户必须具有grant any privilege的系统权限。在授予系统权限时，可以带有with admin option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是角色。为了让大家快速理解，我们举例说明： 1.创建两个用户ken，tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。

create user ken identfied by ken; 2 给用户ken授权

1). grant create session, create table to ken with admin option; 2). grant create view to ken; 3 给用户tom授权

我们可以通过ken给tom授权，因为with admin option是加上的。当然也可以通过dba给tom授权，我们就用ken给tom授权： 1. grant create session, create table to tom; 2. grant create view to ken; --ok吗？不ok

回收系统权限

一般情况下，回收系统权限是dba来完成的，如果其它的用户来回收系统权限，要求该用户必须具有相应系统权限及转授系统权限的选项（with admin option）。回收系统权限使用revoke来完成。

当回收了系统权限后，用户就不能执行相应的操作了，但是请注意，系统权限级联收回的问题?[不是级联回收！] system --------->ken ---------->tom

(create session)(create session)( create session) 用system执行如下操作：

revoke create session from ken; --请思考tom还能登录吗？ 答案：能，可以登录