ORACLE百例试炼四

Oracle系列《四》：数据库的设计分析
一、序列的使用在很多数据库系统中都存在一个自动增长的列，如果在Oracle中要完成自动增长的功能，只能依靠序列完成
1、 要有创建序列的权限 create sequence 或 create any sequence2、 创建序列的语法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 缓存 CacheNextvalue 下一个Currval 当前值}
//实例应用//实现id的自动递增//第一步create table cdpt(id number(6),name varchar2(30),constraint pk_id primary key(id));Create sequence seq_cdptIncrement by 1Start with 1Maxvalue 999999Minvalue 1Nocyclenocacheinsert 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>SELECTSYSDATEFROMdual;dual是一张虚拟表，该表在SYS用户下有定义，可以使用以下语句查询到SQL>SELECT*FROMtabWHERETNAME=’DUAL’;
此表在SYS下，但SCOTT用户却可以直接通过表名称访问，正常情况下我们是需要使用”用户名.表名称”该情况就是同义词的作用 创建同义词：CREATESYSNONYM同义词名称FOR用户名.表名称’;例如，将scott.emp定义emp的同义词SQL>CREATESYSNONYMempFORscott.emp;删除同义词SQL>DROPSYSNONYMemp;同义词这种特性只适合于Oracle数据库
三、用户管理(*)
创建用户:免费云主机域名CREATEUSER用户名IDENTIFIEDBY密码;SQL>CREATEUSERtestIDENTIFIEDBYtest123;打开一个新的窗口使用test用户登录，发现其没有session权限无法进行登录，此时要进行授权SQL>GRANTCREATESESSIONTOtest;
将创建SESSION权限给test用户，之后该用户可以正常登录，但是其没有创建表的权限Oracle中可以将多个权限定义成一组角色，分配该角色给用户即可在Oracle中主要提供了两个角色：CONNECT、RESOURCE，将这两个角色赋予test用户SQL>GRANTCONNECT,RESOURCETOtest;
管理员对用户密码进行修改：SQL>ALTERUSERtestIDENTIFIEDBYhello;在一般系统中，在用户进行第一次登录时可以修改密码，可以使用如下方式ALTERUSER用户名PASSWORDEXPIRE;SQL>ALTERUSERtestPASSWORDEXPIRE;这时会提示用户输入旧口令及新的密码
锁住用户和对用户解锁SQL>ALTERUSERtestACCOUNTLOCK;SQL>ALTERUSERtestACCOUNTUNLOCK;
此时，想查询SCOTT用户下的表EMP，发现没有权限，执行如下命令即可SQL>GRANTSELECT,DELETEONscott.empTOtest; 收回权限的命令：SQL>REVOKESELECT,DELETEONscott.empFROMtest;
嵌套表的概念(了解)嵌套表：在一个表中还包含另外一个子表首先为嵌套表指定类型，该类型需要单独定义SQL>CREATETYPEproject_tyASOBJECT(priodNUMBER(4), pronameVARCHAR2(30),prodateDATE);/该类型创建成后，不意味着此类型能够直接使用，要为此类型指定一个名称SQL>CREATETYPEproject_ntASTABLEOFproject_ty/这样就可以使用project_nt表示project_ty类型，现在根据此类型创建department表SQL>CREATETABLEdepartment( deptnoNUMBER(2)PRIMARYKEY,dnameVARCHAR2(50)NOTNULL,projectsproject_nt)NESTEDTABLEprojectsSTOREASproject_nt_tab_temp;如果要进行数据插入的话SQL>INSERTINTOdepartment(deptno,dname,projects) VALUES(1,’tech’,project_nt( project_ty(1001,’ERP’,SYSDATE), project_ty(1002,’CRM’,SYSDATE)));查询部门表，可以返回多个项目SQL>SELECT*FROMdepartment; 如果需要查看一个部门的全部项目的话，查询嵌套表SQL>SELECT*FROMTABLE(SELECTprojectsFROMdepartmentWHEREdeptno=1);更新编号为1001的项目名称SQL>UPDATETABLE(SELECTprojectsFROMdepartmentWHEREdeptno=1)proSETVALUES(pro)=project_ty(‘1001′,’APR’,SYSDATE)WHEREpro.proid=1001; 可变数组的概念：是嵌套表的升级版…(有用到再了解)

数据库范式的概念第一范式：所有的信息都集中在一张表上，例如CREATETABLEperson(pidNUMBER(4)PRIMARYKEY,nameVARCHAR2(50),infoVARCHAR(200));第一范式会出现问题，例如创建一张学生选课表CREATETABLEselectCourse(stunoVARCHAR2(50),stunameVARCHAR2(50),stuageNUMBER,cnameVARCHAR2(50),gradeNUMBER,creditNUMBER); 以上不仅所有的课程信息冗余了，而且还存在以下的问题：1、没有学生选该门课，那么该门课就消失了2、课程本身有编号，按照以上设计，课程编号肯定重复3、要更改课程信息，则要修改许多记录
使用第二范式进行修改CREATETABLEstudent(stunoVARCHAR2(10)PRIMARYKEY,stunameVARCHAR2(20),stuageNUMBER);CREATETABLEcourse(cidNUMBER(5)PRIMARYKEY,cnameVARCHAR2(20),creditNUMBER);CREATETABLEselectCourse(stunoVARCHAR2(50),cidNUMBER(5),gradeNUMBER,设置主-外键关系）；
以上设计解决了以下问题：1、学生不选课，课程不会消失2、更新课程的时候直接更新课程表3、所有关联关系在关系表中体现 这里是完成了多-多关系
使用第三范式：按照第二范式的设计一张学生表，包括学号、姓名、年龄、所在院校、学院地址、学院电话等会出现一个学生同时在多个学院同时上课，正常应该是：一个学院包含多个学生，一个学生属于一个学院CCREATETABLEcollage(cidNUMBER(40)PRIMARYKEY,cnameVARCHAR2(50),caddressVARCHAR2(20),ctelVARCHAR2(20));CREATETABLEstudent(stunoVARCHAR2(50)PRIMARYKEY,stunameVARCHAR2(50),stuageNUMBER, cidNUMBER(4),建立主-外键关联);以上是很明确的1对多的关系

相关推荐: 创建原始数据库——创建数据、创建表

书籍：数据库系统原理与设计(第3版)——万常选 廖国琼等编著任务驱动：由于找不到原始数据库,只有从零开始创建数据库、设计数据库关系图、输入数据，为后面深入研究数据库做好数据准备。数据库版本：SQL Server 2005操作步骤：1.创建数据库源码如下或是通过…