Oracle培训笔记20060414

表和视图

在图形工具中建立表和视图
打开:Enterprise Manager Console
独立启动
打开后就是当前的数据库
删除连接,并不是删除了数据库
名称只是个代号.
练习:用Enterprise Manager Console连接到一个数据库上,(以SYS身份)
打开SCOTT下的表和视图的定义
可以修改表或者视图的定义,比如主键等.
(如果数据库有问题
先在服务管理控制器里面重新启动一下实例
管理工具----服务关闭-----再启动)

*****************约束:CONSTRAINTS
约束是建立在表上的规则,总共有五种规则:
非空,唯一,主键,外键,验证

建立规则的方法:
一在应用程序里进行控制:麻烦,不可靠,可以实现非常负责的规则  最低
二用触发器(PL/SQL):复杂的规则,写在数据库里 次高
三完整性约束:只能实现较简单的规则,写在数据库里 优先级最高

如果规则三种方法都可以实现,则优先用完整性约束

**********有NOT NULL不一定是非空约束,而是由于主关键字自带的

NOT NULL:建立了约束的列上不允许出现空值
建立:Alter table tableName modify columnName not null;
删除:Alter table tableName modify columnName null;
CREATE TABLE N_TEST(A NUMBER,B NUMBER);
ALTER TABLE N_TEST MODIFY A NOT NULL;
INSERT INTO N_TEST VALUES(1,1);
INSERT INTO N_TEST(B) VALUES(null);//将不能插入,因为A加了非空约束

练习:用CTAS(create table as select)方式创建一个新表emp_bk和emp表列定义,数据相同

练习:在emp_bk表的SAL列上设置一个非空约束,然后实验效果.
SQL> alter table emp_bk modify sal not null;

表已更改。

SQL> insert into emp_bk values(1111,1111,1111,null,null,null,null,null);
insert into emp_bk values(1111,1111,1111,null,null,null,null,null)
*
ERROR 位于第 1 行:
ORA-01400: 无法将 NULL 插入 ("SCOTT"."EMP_BK"."SAL")

UNIQUE:唯一约束规则
  建立了约束列或者列的组合
  在表中不能出现在这些列上值重复的行
创建:alter table tableName add Constraint constraintName unique(columList)
删除:alter table tableName add Constraint constraintName;
如果约束多列,则当单独一列出现重复的时候,是允许的
练习:在emp_bk表上创建一个唯一约束
 要求职工号不能重复
练习:删除刚才建立的约束

SQL> alter table emp_bk add constraint zzz unique(empno);

表已更改。

SQL> insert into emp_bk values(7876,null,null,null,null,null,null,null);
insert into emp_bk values(7876,null,null,null,null,null,null,null)
*
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.ZZZ)

SQL> alter table emp_bk drop constraint zzz;

表已更改。

练习:(多列)创建一个表,工资表(职工编号,工资月份,金额)
  要求每个职工每个月只能有一条工资记录
SQL> create table salary(empno number(4),month number(4),sal number(7,2));
表已创建。

SQL> alter table salary add constraint uk_salary unique(empno,month);
表已更改。

也可以用图形工具也可以建立约束
同时,也可以利用图形工具来学习SQL语句,通过显示SQL来学习
所做的动作都可以用SQL显示出来

PRIMARY KEY:主键约束
  可以用来唯一标识表中一个行的列
唯一约束和主键约束的区别:
 (1)主键只能有一个,唯一约束可以有多个
 (2)主键约束上不能有空值,唯一约束可以有空值
创建:alter table tableName add constraint constraintName primary key(columnList);
删除:alter table tableName drop constraint constraintName;

练习:在emp_bk表上创建一个主键
SQL> alter table emp_bk add constraint pk_emp_bk primary key(empno);
表已更改。

练习:创建一个学生信息表,记录某校的学生信息
 包含学号,姓名,身份证号,专业,医疗卡号,不允许重复
 学号不允许重复也不允许有空值
 身份证号不允许重复,但是可以为空
 医疗卡号不允许重复也不允许有空值
SQL> alter table student add constraint UK_S1 unique(stu_iden);
表已更改。
SQL> alter table student add constraint PK_S primary key(stu_id);
表已更改。
SQL> alter table student add constraint UK_S2 unique(stu_hosno);
表已更改。
SQL> alter table student modify stu_hosno not null;
表已更改。

外键约束(FOREIGN KEY)
规则:引用完整性
(1)建立外键约束的列,称为引用列,建立外键约束的表，称为引用表
(2)外键引用的表,称为被引用表,外键引用的列,称为被引用列
(3)引用列上的值,要么为空值,要么必须在被引用列上出现
(4)被引用列必须是被引用表的主键或者唯一键 
创建:alter table  tableName add constraint constraintName foreign key(columnList) references 被引用表(被引用列列表);

SQL> alter table emp_bk add constraint fk_emp_bk foreign key(deptno)
  2  references dept(deptno);
表已更改。

练习:在emp_bk表上创建一个约束,要求每个领导的编号(MGR)必须是职工表内某个职工的职工编号
SQL> alter table emp_bk add constraint fk_emp_bk foreign key(mgr)
  2  references emp_bk(empno);

表已更改。

检查约束:CHECK
规则:要求表中的列或者列与列之间要满足某些条件
创建:
alter table 表名字 add constraint 约束名 check(条件)

练习:要求emp_bk表的职工的姓名和职工的姓名和职工的职务
 不允许出现小写字母
SQL> alter table emp_bk add constraint ck_emp_bk
  2  check(upper(ename)=ename and upper(job)=job);

表已更改。
练习:要求分成不允许超过工资的200%
SQL> alter table emp_bk add constraint ckk_emp_bk
  2  check(comm<=2*nvl(sal,0));

表已更改。

权限设置:
 直接权限:分为两类,一类是系统权限,一类是对象权限
系统权限是执行某个系统动作的能力
对象权限是某个对象上的权限(一个,有名字,已经存在的权限)
CREATE TABLE:系统权限
SELECT ANY TABLE:系统权限(是存在的权限,但是不是一个)
CREATE SESSION:系统权限

select on scott.emp;
references on scott.emp;
带ON的肯定是对象权限，否则肯定不是对象权限

系统权限的授权方法:

grant 权限名列表 to 帐户名列表 [with admin option];
一次可以授予多个权限,也可以授予多个帐户
with admin option:可以继承授权能力

revoke 权限名列表 from 帐户名列表;
REVOKE不能级联收回权限,需要多次收回
ANY:任何帐户
CREATE ANY TABLE:创建任何帐户的表
CREATE TABLE SCOTT.ABC(A NUMBER);

练习一:创建一个帐户,该帐户可以能够登陆,创建表,并且能够查询任何帐户的表.并且可以把这些能力授予起他的帐户.
SQL> create user zzzz identified by zzzz default tablespace users;

用户已创建
SQL> grant create session,create table,select any table to zzzz with admin optio
n;

授权成功。
在图形工具下查看创建结果及授权结果如附图 未命名3
对象权限:限定到一个已经存在的对象上的权限
授权:grant 权限名列表 on 对象名 to 帐户名列表
 with grant option;
(1)一次只能授予一个对象上的若干权限
(2)对象权限和系统权限不能用一个语句一起授权
(3)with grant option用于对象权限,with admin option用于系统权限
收回的时候with grant option可以级联收回
练习:删除练习一的帐户
练习:新建一个帐户,要求可以登陆,可以查看SCOTT下的EMP表,可以修改SCOTT下的EMP表,并且可以将这些权限授予其他帐户.

SQL> create user zzzz identified by zhangzhongliang default tablespace users;

用户已创建

SQL> grant create session to zzzz with admin option;

授权成功。

SQL> grant select,alter on scott.emp to zzzz with grant option;

授权成功。

练习:创建一个一个自己的帐户,并且在自己的帐户下建立一个与EMP表完全相同的
表.
列权限:
GRANT UPDATE(列名列表) on 对象名 TO 帐户名列表;
GRANT INSERT(列名列表) on 对象名 TO 帐户名列表;
角色:权限的集合.
(1)不需要使用频繁的命令
(2)可以调整角色
创建角色:
 CREATE ROLE 角色名;
向角色中添加权限:
 GRANT ....TO 角色
 REVOKE .....FROM 角色
将角色授予帐户
 GRANT 角色名 TO 帐户名
改变角色相当于改变了用户的权限.

练习5，新建一个帐户
 新建一个角色R1
        角色中包含，登录，查询scott下的emp表
        修改scott下的emp表的sal列的权限
        将这个角色授予新建的账户
SQL> create user zzzz identified by zzzz default tablespace users;
用户已创建
SQL> create role zhang;
SQL> grant create session to zhang;
授权成功。
SQL> grant select,update(sal) on scott.emp to zhang;
授权成功。
SQL> grant zhang to zzzz;
授权成功。

预定义的角色:一般建立帐户的时候,直接给一个帐户两个角色:CONNECT和RESOURCE,两个角色共有14个权限.