Oracle模式对象

一 用户与模式的区别

      用户是用来连接数据库对象。而模式用是用创建管理对象的。模式跟用户在Oracle是一对一的关系。从定义中我们可以看出schema为数据库对象的集合，为了区分各个集合，我们需要给这个集合起个名字，这些名字就是我们在企业管理器的方案下看到的许多类似用户名的节点，这些类似用户名的节点其实就是一个schema，schema里面包含了各种对象如tables, views, sequences,stored procedures, synonyms, indexes, clusters, and database links。

     一个用户一般对应一个schema,该用户的schema名等于用户名，并作为该用户缺省schema。这也就是我们在企业管理器的方案下看到schema名都为数据库用户名的原因。Oracle数据库中不能新创建一个schema，要想创建一个schema，只能通过创建一个用户的方法解决(Oracle中虽然有create schema语句，但是它并不是用来创建一个schema的)，在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同，而且schema名字同user名字一一 对应并且相同，所有我们可以称schema为user的别名，虽然这样说并不准确，但是更容易理解一些。

      一个用户有一个缺省的schema，其schema名就等于用户名，当然一个用户还可以使用其他的schema。如果我们访问一个表时，没有指明该表属于哪一个schema中的，系统就会自动给我们在表上加上缺省的sheman名。比如我们在访问数据库时，访问scott用户下的emp表，通过select * from emp; 其实，这sql语句的完整写法为select * from scott.emp。在数据库中一个对象的完整名称为schema.object，而不属user.object。类似如果我们在创建对象时不指定该对象的schema，在该对象的schema为用户的缺省schema。这就像一个用户有一个缺省的表空间，但是该用户还可以使用其他的表空间，如果我们在创建对象时不指定表空间，则对象存储在缺省表空间中，要想让对象存储在其他表空间中，我们需要在创建对象时指定该对象的表空间。

 

二 Oracle中的常用数据类型如表3.1所示：

 

字段类型	中文说明	限制条件	其他说明
CHAR	固定长度字符串	最大长度2000 bytes
VARCHAR2	可变长度的字符串	最大长度4000bytes	可做索引的最大长度749
NCHAR	根据字符集而定的固定长度字符串	最大长度2000 bytes
NVARCHAR2	根据字符集而定的固定长度字符串	最大长度4000 bytes
DATE	日期(日-月-年) DD-MM-YY(HH-MI-SS)		如果要精确到毫秒，则用timestamp型
LONG	超长字符串	最大长度2G(231-1)	足够存储大部头著作
ROWID	数据表中记录的唯一行号	10 bytes
NUMBER(P,S)	数字类型		P为整数位，S为小数位
DECIMAL(P,S)	数字类型		P为整数位，S为小数位
INTEGER	整数类型		小的整数(可以简写成int)
FLOAT	浮点数类型		NUMBER(38)，双精度

 

三 建表及相关操作

 

 

--建立如下两张表

部门表

  部门编号 主键

  部门名称 非空

  部门描述 

员工表

  员工编号 主键

  员工名称 非空   唯一

  员工性别 非空   默认值‘男'

  年龄      18岁到 60

  联系电话 长度11位

  邮箱 非空

  入职时间 date

  薪水 number

  部门编号 外键，参考部门

 

--删除部门表

drop table department;

--建立部门表

create table department

(

  deptId int constraint p_dept primary key,

  deptName varchar2(20) not null,

  remark varchar2(55)

)

;

insert into department values(100,'财务部','管理公司财务');

insert into department values(101,'销售部','负责公司销售');

insert into department values(102,'研发部','负责公司技术研发');

insert into department values(103,'后勤部','管理后勤事务');

insert into department values(104,'人事部','管理人力资源');

 

--建立员工表

create table employee

(

 empId int constraint p_emp primary key,   --或者 empId int primary key

 empName varchar2(20) not null ,

 gender char(2) default '男',

  ageint constraint ck_age check( age>=18 and age<=60),

 phone char(11) constraint ck_phone check(length(phone)=11),

 email varchar2(30) not null,

 deptIdd int constraint fk_deptIdd references department(deptid)

-- 或者 deptIdd foreign key references department(deptid)

)

;

 

--添加列 alter table myemp add sal number;

--删除列 alter talbe myemp drop column sal;

 

--插入数据

 

insert into employee values(1000,'张三','男',23,'12325469854','zs@qq.com',100);

insert into employee values(1001,'李四','男',24,'12375847896','lisi@qq.com',101);

insert into employee values(1002,'王二','男',22,'13725643256','wanger@qq.com',102);

insert into employee values(1003,'麻子','男',32,'13525471456','mazi@qq.com',102);

insert into employee values(1004,'小丽','女',19,'13858478996','xiaoli@126.com',102);

insert into employee values(1005,'小红','女',29,'13102156487','xiaohong@126.com',104);

 

--验证日期型数据(date可以存放年月日时分秒，number型可以存放整数也可以存放小数，且没有精度限制)

create table DateTest

(

 dateId int,

 myDate date,

 testNum number

)

;

--插入日期数据要用to_date函数进行转换

insert into DateTestvalues(10,to_date('2010-01-25 13:32:39','YYYY-MM-DD HH24:MI:SS'),100);

也可以加上date关键字

insert into DateTestvalues(10,DATE'2010-01-25 13:32:39',100);

--要显示，要用to_char再进行转换

select dateid,to_char(myDate,'YYYY-MM-DDHH24:MI:SS'),testnum from datetest;

--查看用户的表

select  * from user_tables;

 

四 建立索引

--在部门名称上建立b树索引

create index idx_deptName ondepartment(deptName);

--删除索引

drop index idx_deptName;

--在员工表上建立唯一索引,会默认加上唯一约束

create unique index idx_uq_empName onemployee(empname);

--还可以在性别列上建立位图索引

create bitmap index idx_bmap_gender onemployee(gender);

--查看用户的索引

select * from user_indexes

 

五 建立视图

--创建一个视图,查看研发部员工的信息

create or replace view v_emp_dept

as

selecte.empid,e.empname,e.gender,e.phone,d.deptname

from employee e inner join department d

on e.deptidd=d.deptid

where d.deptname='研发部'

with read only

;

--如果当前用户没有权限，就要切换到系统用户赋权

grant create view to jack;

--通过数据字典查询自己建立的视图

select * FROM user_views;

--删除视图

drop view v_emp_dept;

 

六 建立序列

--创建序列

create sequence seq_for_emp

start with 1000    --从1000多少开始

increment by 1    --每次增加1

minvalue 1000     --最小值

maxvalue 99999   --最大值

nocycle       --不循环

cache 10     --缓存10个序列值

;

--使用序列

insert into employeevalues(seq_for_emp.nextval,'张三','男',23,'12325469854','zs@qq.com',100);

insert into employeevalues(seq_for_emp.nextval,'李四','男',24,'12375847896','lisi@qq.com',101);

insert into employee values(seq_for_emp.nextval,'王二','男',22,'13725643256','wanger@qq.com',102);

insert into employeevalues(seq_for_emp.nextval,'麻子','男',32,'13525471456','mazi@qq.com',102);

insert into employeevalues(seq_for_emp.nextval,'小丽','女',19,'13858478996','xiaoli@126.com',102);

insert into employeevalues(seq_for_emp.nextval,'小红','女',29,'13102156487','xiaohong@126.com',104);

--查序列的值到哪里了

select seq_for_emp.currval from dual;

--删除序列

drop sequence seq_for_emp;

七 建立同义词

create synonym qtf for user_tab_columns;;

create synonym ttt forSTMT_AUDIT_OPTION_MAP;

--如果没有权限，切换到system用户赋权

grant create synonym to jack;