当前位置:Gxlcms > 数据库问题 > ORACLE百例试炼四

ORACLE百例试炼四

时间:2021-07-01 10:21:17 帮助过:22人阅读


一、序列的使用 

在很多数据库系统中都存在一个自动增长的列,如果在Oracle中要完成自动增长的功能,只能依靠序列完成


1、  要有创建序列的权限 create sequence 或 create any sequence

2、  创建序列的语法

CREATE SEQUENCE sequence  //创建序列名称

       [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1

       [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue

       [{MAXVALUE n | NOMAXVALUE}] //最大值

       [{MINVALUE n | NOMINVALUE}] //最小值

       [{CYCLE | NOCYCLE}] //循环/不循环

       [{CACHE n | NOCACHE}];//分配并存入到内存中

 

  NEXTVAL 返回序列中下一个有效的值,任何用户都可以引用

  CURRVAL 中存放序列的当前值

  NEXTVAL 应在 CURRVAL 之前指定 ,二者应同时有效

//解释

{

Create 创建

Sequence 序列 seqEmop 序列名称

Increment by 步长

Stat with 1 开始值

Maxvalue  最大值

Minvalue  最小值

 

Cycle 循环 nocycle 不循环

Cache 缓存   Cache<maxvalue-minvalue/increment by//一般不采用缓存

Nextvalue 下一个

Currval 当前值

}


//实例应用

//实现id的自动递增

//第一步

create table cdpt(

id number(6),

name varchar2(30),

constraint pk_id primary key(id)

);

 

Create sequence seq_cdpt

Increment by 1

Start with 1

Maxvalue 999999

Minvalue 1

Nocycle

nocache

 

insert into cdpt values(seq_cdpt.nextval,‘feffefe’);

commit;

select * from cdpt;


修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存

alter SEQUENCE sequence  //创建序列名称

       [INCREMENT BY n]  //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1

       [START WITH n]    //开始的值,递增默认是minvalue 递减是maxvalue

       [{MAXVALUE n | NOMAXVALUE}] //最大值

       [{MINVALUE n | NOMINVALUE}] //最小值

       [{CYCLE | NOCYCLE}] //循环/不循环

       [{CACHE n | NOCACHE}];//分配并存入到内存中

修改序列的注意事项:

l   必须是序列的拥有者或对序列有 ALTER 权限

l   只有将来的序列值会被改变

l   改变序列的初始值只能通过删除序列之后重建序列的方法实现

删除序列

l   使用DROP SEQUENCE 语句删除序列

l   删除之后,序列不能再次被引用

Alter sequence seqEmp maxvalue 5;

Select seqEmp.nextval from dual;



二、同义词的概念(了解) 

 

SQL> SELECT SYSDATE FROM dual; 

dual是一张虚拟表,该表在SYS用户下有定义,可以使用以下语句查询到 SQL> SELECT * FROM tab WHERE TNAME=‘DUAL‘; 


此表在SYS下,但SCOTT用户却可以直接通过表名称访问,正常情况下我们是需要使用"用户名.表名称" 该情况就是同义词的作用  

创建同义词:

 

CREATE SYSNONYM 同义词名称 FOR 用户名.表名称‘;

 例如,将scott.emp 定义 emp 的同义词 

SQL> CREATE SYSNONYM emp FOR scott.emp;

  

删除同义词 

SQL> DROP SYSNONYM emp; 

同义词这种特性只适合于Oracle数据库


三、用户管理(*) 


 

创建用户: CREATE USER 用户名 IDENTIFIED BY 密码; 

SQL> CREATE USER test IDENTIFIED BY test123; 

 

打开一个新的窗口使用test用户登录,发现其没有session权限无法进行登录,此时要进行授权 

SQL> GRANT CREATE SESSION TO test;


将创建SESSION权限给test用户,之后该用户可以正常登录,但是其没有创建表的权限

Oracle中可以将多个权限定义成一组角色,分配该角色给用户即可 

在Oracle中主要提供了两个角色:CONNECT、RESOURCE,将这两个角色赋予test用户 

SQL> GRANT CONNECT,RESOURCE TO test;


管理员对用户密码进行修改: 

SQL> ALTER USER test IDENTIFIED BY hello; 

在一般系统中,在用户进行第一次登录时可以修改密码,可以使用如下方式 

ALTER USER 用户名 PASSWORD EXPIRE; 

SQL> ALTER USER test PASSWORD EXPIRE; 

这时会提示用户输入旧口令及新的密码


锁住用户和对用户解锁 

SQL> ALTER USER test ACCOUNT LOCK;

SQL> ALTER USER test ACCOUNT UNLOCK;


此时,想查询SCOTT用户下的表EMP,发现没有权限,执行如下命令即可 

SQL> GRANT SELECT,DELETE ON scott.emp TO test;  

收回权限的命令: 

SQL> REVOKE SELECT,DELETE ON scott.emp FROM test;


嵌套表的概念(了解) 

 

嵌套表:在一个表中还包含另外一个子表 

首先为嵌套表指定类型,该类型需要单独定义 

SQL> CREATE TYPE project_ty AS OBJECT(  priod NUMBER(4), proname VARCHAR2(30),  prodate DATE );/

 

该类型创建成后,不意味着此类型能够直接使用,要为此类型指定一个名称 

SQL> CREATE TYPE project_nt AS TABLE OF project_ty /

 

这样就可以使用project_nt表示project_ty类型,现在根据此类型创建department表 

SQL> CREATE TABLE department(  

  deptno  NUMBER(2) PRIMARY KEY,

  dname  VARCHAR2(50) NOT NULL,

  projects project_nt 

)

NESTED TABLE projects STORE AS project_nt_tab_temp;

  

如果要进行数据插入的话 

SQL> INSERT INTO department(deptno,dname,projects) 

  VALUES(  1,‘tech‘,  project_nt( 

  project_ty(1001,‘ERP‘,SYSDATE),   

  project_ty(1002,‘CRM‘,SYSDATE)  ) );

  

查询部门表,可以返回多个项目 

SQL> SELECT * FROM department;  

如果需要查看一个部门的全部项目的话,查询嵌套表 

SQL> SELECT * FROM TABLE 

 (SELECT projects FROM department WHERE deptno=1); 

 

更新编号为1001的项目名称 

SQL> UPDATE TABLE (SELECT projects FROM department WHERE deptno=1) pro 

SET VALUES(pro)=project_ty(‘1001‘,‘APR‘,SYSDATE) WHERE pro.proid=1001;  

可变数组的概念:是嵌套表的升级版 ... (有用到再了解)



数据库范式的概念

第一范式:所有的信息都集中在一张表上,例如 

 CREATE TABLE person( 

 pid  NUMBER(4) PRIMARY KEY,

 name VARCHAR2(50),  

 info VARCHAR(200) );

  

第一范式会出现问题,例如创建一张学生选课表 

CREATE TABLE selectCourse(  stuno VARCHAR2(50),  

 stuname VARCHAR2(50),  

 stuage NUMBER, 

 cname VARCHAR2(50),  

 grade NUMBER,  

 credit NUMBER );  

以上不仅所有的课程信息冗余了,而且还存在以下的问题: 

1、没有学生选该门课,那么该门课就消失了 

2、课程本身有编号,按照以上设计,课程编号肯定重复 

3、要更改课程信息,则要修改许多记录


使用第二范式进行修改 

CREATE TABLE student( 

 stuno  VARCHAR2(10) PRIMARY KEY,  

 stuname VARCHAR2(20),  

 stuage NUMBER );

  

CREATE TABLE course( 

 cid NUMBER(5) PRIMARY KEY,  

 cname VARCHAR2(20),  

 credit NUMBER );

  

CREATE TABLE selectCourse(  

stuno VARCHAR2(50),  

cid   NUMBER(5),  

grade NUMBER, 设置主-外键关系); 


以上设计解决了以下问题: 

1、学生不选课,课程不会消失 

2、更新课程的时候直接更新课程表 

3、所有关联关系在关系表中体现  

这里是完成了多-多关系


使用第三范式: 

按照第二范式的设计一张学生表,包括学号、姓名、年龄、所在院校、学院地址、学院电话等 

会出现一个学生同时在多个学院同时上课,正常应该是:一个学院包含多个学生,一个学生属于一个学院C 

 

CREATE TABLE collage( 

 cid NUMBER(40) PRIMARY KEY,  

 cname VARCHAR2(50),  

 caddress VARCHAR2(20),  

 ctel VARCHAR2(20) ); 

 

CREATE TABLE student( 

 stuno VARCHAR2(50) PRIMARY KEY,  

 stuname VARCHAR2(50),  

 stuage  NUMBER,  

 cid NUMBER(4),  建立主-外键关联 ); 

以上是很明确的1对多的关系


本文出自 “ORACLE学习历程” 博客,请务必保留此出处http://yuemu.blog.51cto.com/10688235/1749722

ORACLE百例试炼四

标签:oracle   ocp   oracle基础知识   dba   oracle开发   

人气教程排行