1.模式与模式对象
模式概念(schema),又称方案:
是指一系列逻辑数据结构或对象的集合。
模式与用户的关系:
模式与用户相对应,一个模式只能被一个数据库用户所拥有,并且模式的名称与这个用户的名称相同。
通常情况下,用户所创建数据库对象都保存在与自己同名的模式中。
同一模式中数据库对象的名称必须唯一,而在不同模式中的数据库对象可以同名。
默认情况下,用户引用的对象是与自己同名模式中的对象,如果要引用其他模式中的对象,需要在该对象名之前指明对象所属模式。
模式对象是一种逻辑数据存储结构。模式对象与数据文件并不存在物理上的一一对应关系。
由于Oracle系统将模式对象存储在表空间中,因此一个模式对象可能被存储在一个表空间的多个数据文件中。
模式对象的类型有表、索引、索引化表、簇、触发器、数据库链接、PL/SQL包、序列、同义词、视图和存储函数与存储过程、JAVA类与其他JAVA。
当用户在数据库中创建一个模式对象后,这个模式对象默认地属于这个用户的模式,如果其他用户需要使用这个模式对象,则必须在对象名前加上它所属的模式名。
模式选择与切换:
1.如果用户以NORMAL身份登录,则进入同名模式;
2.若以SYSDBA身份登录,则进入SYS模式;
connect / as sysdba
3.如果以SYSOPER身份登录,则进入PUBLIC模式。
2.数据类型
数据类型:数据类型分为基本数据类型和用户自定义数据类型。
基本数据类型:
数值型 NUMBER、DECIMAL、INTEGER、FLOAT和REAL
字符型 CHAR、NCHAR、VARCHAR2、NVARCHAR2
日期型 DATE、TIMESTAMP
行标识类型 ROWID
LOB类型 BLOB、CLOB和BFILE
数字类型
NUMBER(m,n)
用于存储整数和实数。m表示数值的总位数(精度),取值范围为1~38,默认为38;n表示小数位数,若为负数则表示把数据向小数点左边舍入,默认值为0
字符类型
CHAR(n)
定长字符串,n的取值范围为1-2000字节
VARCHAR2(n)
可变字符串,n取值范围为1-4000字节,自动调整数据长度
NCHAR(n)
用来存储Unicode类型字符串。
NVARCHAR2(n)
它用来存储Unicode类型字符串。
日期类型
DATE
可以存储的日期范围为公元前4712年1月1日到公元4712年1月1日。 Oracle标准日期格式为:DD-MON-YY HH:MI:SS。
按7个字符来保存日期数据,如07-10月-12 表示2012年10月7日。
可用函数to_date指定特定格式,如to_date(‘2012-10-18’,’yyyy-mm-dd’)。
TIMESTAMP[(n)]:
表示时间戳,是DATE 数据类型的扩展,允许存储小数形式的秒值。n表示秒的小数位数,取值范围为1~9,默认值为6。可精确到毫秒级,对时间精度要求高的时候使用,如银行。
TIMESTAMP[(n)] WITH TIME ZONE
通过存储一个时区偏差来扩展TIMESTAMP类型。 时区偏差值为相对于通用协调时间(或称UTC,以前称为格林威治时间或GMT)的时差。
行类型
ROWID
行标识符,表中行的物理地址的伪列类型。ROWID类型数据由18位十六进制数构成,包括数据对象编号(1-6)、数据文件编号(7-9)、数据块编号(10-15)和块内行号(16-18)。
Select rowid,empno from scott.emp where empno=7369;
UROWID
行标识符,用于表示索引化表中行的逻辑地址。
LOB类型(大对象数据类型)
CLOB
用于存储可变长度的字符数据,如文本文件等,最大数据量为4 GB。
NCLOB
用于存储可变长度的Unicode字符数据,最大数据量为4 GB。
BLOB
用于存储大型的、未被结构化的可变长度的二进制数据(如二进制文件、图片文件、音频和视频等非文本文件),最大数据量为4 GB。
BFILE
用于存储指向二进制格式文件的定位器,该二进制文件保存在数据库外部的操作系统中,文件最大为4 GB。
用户自定义数据类型
例:
SQL> create or replace type mytype as object
(
province varchar(20),
city varchar(30),
details varchar(40)
);
类型已创建。
SQL> create table studentobj
(
stuname varchar(10),
stuaddress mytype
);
表已创建。
SQL> insert into studentobj values('Tom',MYTYPE('浙江省','宁波','海曙'));
SQL>select * from studentobj;
3.创建表
用户可以通过Oracle提供的SQL*Plus中使用CREATE TABLE语句创建表,也可以通过OEM图形化工具创建表。用户使用CREATE TABLE语句创建表必须具有CREATE TABLE系统权限,在其他模式中用户创建表必须有CREATE ANY TABLE权限。
例:在当前模式中创建一个具有4列的Student表
CREATE TABLE Student
(
cNo CHAR(10),
vName VARCHAR2(10),
nAge NUMBER(3),
cDeptid CHAR(4)
);
用户使用CREATE TABLE语句创建了一个名称为Student的学生表,该表包含cNo(学生学号)、vName(学生姓名)、nAge(年龄)和cDeptid(学生所在院系编号)4个列。
例:在当前模式中创建一个具有4列的Student表,并指定它位于USERS表空间。
CREATE TABLE Student
(
cNo CHAR(10),
vName VARCHAR2(10),
nAge NUMBER(3),
cDeptid CHAR(4)
) TABLESPACE USERS;
创建表时仅为表指定了表空间,而没有设置存储参数,它将自动采用所属表空间的默认存储参数设置,但表空间的默认存储参数设置并不一定对表空间中的每个表都适合,因此有时需要在创建表时显式指定存储参数设置,而不使用默认存储参数。对存储参数的设置可以通过在CREATE TABLE语句中使用STORAGE子句来实现。
在STORAGE子句中可以设置下面5个存储参数:
INITIAL
指定为表中数据分配的第一个盘区大小,以KB或MB为单位,默认为5个Oracle数据块的大小,当为已知数量的数据创建表时,可以将INITIAL设置为一个可以容纳所有数据的容量,这样可以将表中所有数据存储在一个盘区中,以免产生碎片。
NEXT
指定为表中数据分配的第二个盘区大小,以KB或MB为单位,默认为5个Oracle数据块的大小。当表的第一个盘区被填满后,NEXT参数将控制Oracle为随后的盘区分配空间大小。
PCTINCREASE
表示从第二个盘区之后,每个盘区相对于上一个盘区的增长百分比,每次分配盘区的时候,下一个盘区的大小都要根据PCTINCREASE比例增长,这意味着每次分配盘区的时候,用户盘区容量都会增长,通常情况下,建议将PCTINCREASE设置为0。
MINEXTENTS
指定允许为表中的数据分配的最小盘区数目。例如,在创建表时设置参数INITIAL和NEXT为1MB,PCTINCREASE设置为0,MINEXTENTS设置为6,则意味着创建表时将分配6个1MB的盘区。
MAXEXTENTS
指定允许为表中的数据分配的最大盘区数目,默认值为UNLIMITED(不受限制)。
例:在当前模式中创建一个具有4列的Student表,并通过STORAGE子句为它指定存储参数
CREATE TABLE Student
(
cNo CHAR(10),
vName VARCHAR2(10),
nAge NUMBER(3),
cDeptid CHAR(4)
) TABLESPACES USERS
STORAGE
(
INITIAL 64K
NEXT 50K
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 10
);
用户在创建表时,Oracle允许用户可以设置数据块空间管理参数:PCTFREE、PCTUSED、INITRANS和MAXTRANS。
PCTFREE和PCTUSED这两个参数都用于控制数据块中空闲空间的使用,其中PCTFREE用于控制数据块的更新预留的空间;而PCTUSED用于控制数据块在删除时释放空间的门槛。
PCTFREE
参数为数据块中行的更新预留了空闲空间的最小百分比,默认值为10。如果在CREATE TABLE语句中指定PCTFREE为20,则说明在该表的数据段内每个数据块的20%被作为可利用的空闲空间,用于更新已在数据块内存在的数据行。其余80%用于插入新的数据行。若PCTFREE设置得太高,则浪费磁盘空间;若PCTFREE设置得太低,则会导致迁移记录。
PCTUSED
参数设置了数据块是否空闲的界限,当数据块的使用空间低于PCTUSED的值时,此数据块标志为空闲,该空闲空间仅用于插入新的记录。如果数据块的使用空间已经达到PCTUSED的值时,此数据块标志为不可用,该空闲空间无法插入新的记录。
当实际应用UPDATE操作教多,并且更新操作会增加记录的大小时,可将PCTFREE设置大些,PCTUSED设置得小些;当实际应用INSERT和DELETE操作教多,并且更新操作不会增加记录的大小时,可将PCTFREE设置小些,PCTUSED设置得大些。注意设置数据块的PCTFREE和PCTUSED两个参数时,该两个参数值的和必须等于或小于100。
INITRANS和MAXTRANS
这两个参数用于控制能够并发访问数据块的事务数量,同时也会影响到数据块头部的空间使用情况。
例:在当前模式中创建一个具有4列的Student表,并将表的PCTFREE和PCTUSED参数设置为20和40,同时指定能够并发访问的事务总数为100,在数据块头部可以存放20个事务条目。
CREATE TABLE Student
(
cNo CHAR(10),
vName VARCHAR2(10),
nAge NUMBER(3),
cDeptid CHAR(4)
) TABLESPACES USERS
STORAGE
(
INITIAL 64K
NEXT 50K
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 10
)
PCTFREE 20
PCTUSED 40
INITRANS 20
MAXTRANS 100;
用户在创建表时可以使用NOLOGGING子句,使得对表的操作不会产生重做日志,由于不能在重做日志文件中记录表的操作,无法用数据库恢复操作来恢复表丢失的数据。一般创建表将使用LOGGING子句。
例:在当前模式中创建一个具有4列的Student表,并使表的操作不会被记录到重做日志文件中。
CREATE TABLE Student
(
cNo CHAR(10),
vName VARCHAR2(10),
nAge NUMBER(3),
cDeptid CHAR(4)
) TABLESPACES USERS
STORAGE
(
INITIAL 64K
NEXT 50K
MINEXTENTS 1
MAXEXTENTS unlimited
PCTINCREASE 10
) NOLOGGING;
4.表的修改
增加列
ALTER TABLE 表名 ADD 字段名 字段类型;
修改列名
ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新名;
修改列类型
ALTER TABLE 表名 MODIFY 字段名 新类型;
修改列默认值
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;
重命名表
ALTER TABLE 表名 RENAME TO 新表名;
移动表
ALTER TABLE 表名 MOVE TABLESPACE 表空间;
删除列
ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE 表名 DROP COLUMN (字段1名,字段2名,…);
使用unused关键字
例:
(1)将Student表中的nAge和vAddress两个列标记为UNUSED状态。
ALTER TABLE Student SET UNUSED (nAge, vAddress);
(2)删除Student表中被标记为UNUSED状态的字段。
ALTER TABLE Student DROP UNUSED COLUMN;
修改表的存储参数
表创建后可以使用ALTER TABLE语句来改变表的块参数和部分存储参数。
例:
修改表Student中的PCTFREE与PCTUSED参数
ALTER TABLE Student
PCTFREE 30
PCTUSED 50;
用户可能需要明确地为表分配一个指定大小的新盘区,这时可通过使用ALTER TABLE语句的ALLOCATE EXTENT子句以手动方式为表分配存储空间。
例:
(1) 为表Student手动分配存储空间,并且新盘区大小由参数NEXT设置。
ALTER TABLE Student ALLOCATE EXTENT;
(2) 为表Student手动分配一个大小为500KB的新盘区
ALTER TABLE Student ALLOCATE EXTENT (SIZE 500K);
(3) 为表Student在指定的数据文件中分配一个大小为1MB的新盘区。
ALTER TABLE Student ALLOCATE EXTENT (SIZE 1M
DATAFILE 'd:/oracle/data01.dbf');
注意:在ALTER TABLE语句的ALLOCATE EXTENT子句中,如果为分配的新盘区指定所在数据文件时,该数据文件必须属于表所处的表空间。
一般情况下,创建表时默认使用NOCACHE子句,但有时为了提高查询执行效率,对比较小而且经常查询的表,用户可以利用CACHE子句将表放入缓存中。
例:
ALTER TABLE Student ALLOCATE CACHE;
通常情况下Oracle不会自动回收已经为表分配的存储空间,而且没有必要回收表的存储空间,但有时为表分配过多的存储空间,会造成大量资源的浪费和利用率低。如PINCREASE参数设置不当,导致为数据段分配的区越来越大,在分配的区中存在过多的空闲空间。在这些情况下,需要手动回收表的存储空间,以便重新利用被浪费的存储空间。
例:
假设已经为表Student分配了3个盘区:第一个盘区的大小为10KB,第二个盘区的大小为20KB,第三个盘区的大小为30KB,HWM位于第二个区的中央(HWM为已经使用的存储空间与未使用的存储空间的分界线)。现在使用ALTER TABLE语句回收表Student中的空闲空间。
ALTER TABLE Student DEALLOCATE UNUSED
执行该语句后,Student表中只剩下前两个区,第三个区被完全回收,并且第二个区变为10KB。
5.表的删除
用户使用DROP TABLE语句在当前自己的模式(schema)中删除表,必须具有DROP TABLE系统权限,在其他模式中用户删除表必须有DROP ANY TABLE权限。
例:删除表Student
DROP TABLE Student;
若要求在删除表时,同时删除引用这个表的视图、约束或触发器等,这时将使用DROP TABLE语句中的可选参数CASCADE CONSTRAINTS。
例:
删除表Student以及存储在其中的数据,同时删除引用该表的视图、约束、触发器等。
DROP TABLE Student CASCADE CONSTRAINTS;
要想截断表,也就是删除表内的所有数据,可以使用TRUNCATE TABLE命令,注意该命令仅删除数据,不删除表的定义。
例:TRUNCATE TABLE Student
6.完整性约束
实体完整性约束主要指主键约束和唯一性约束
参照完整性约束主要指外键约束
域完整性约束主要指检查约束和非空约束。
主键约束的基本语法:
CONSTRAINT 约束名 PRIMARY KEY (列名1【,列名2...】)
(1)在创建Student表的同时指定sno属性为主键,可使用如下语句:
CREATE TABLE Student
(
sno NUMBER(8),
sname VARCHAR(20),
sage NUMBER(20),
CONSTRAINT PK_SNO PRIMARY KEY (sno)
);
其中:PRIMARY KEY(SNO)表示SNO是Student表的主键。PK_SNO是此主键约束名。
也可以写成如下语句:
CREATE TABLE Student
(
sno NUMBER(8) PRIMARY KEY,
sname VARCHAR(20),
sage NUMBER(20)
);
(2)在修改Student表的同时指定sno属性为主键
ALTER TABLE Student ADD CONSTRAINT PK_SNO PRIMARY KEY (SNO);
(3)删除Student表的主键约束
ALTER TABLE Student DROP CONSTRAINT PK_SNO
通过以下语句可查看约束是否已经被删除
SELECT CONSTRAINT_NAME,COLUMN_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME=’Student’
(4)关闭Student表的主键约束
ALTER TABLE Student DISABLE CONSTRAINT PK_SNO;
(5)启用Student表的主键约束
ALTER TABLE Student ENABLE CONSTRAINT PK_SNO;
唯一约束的基本语法:
CONSTRAINT 约束名 UNIQUE (列名)
例:
(1)在创建Student表的同时指定sno属性为主键,可使用如下语句:
CREATE TABLE Student
(
sno NUMBER(8) ,
sname VARCHAR(20),
sage NUMBER(20),
CONSTRAINT PK_SNO UNIQUE(sno),
CONSTRAINT U_SNAME UNIQUE(sname),
);
也可以写成如下语句:
CREATE TABLE Student
(
sno NUMBER(8) PRIMARY KEY,
sname VARCHAR(20) UNIQUE,
sage NUMBER(20)
);
(2)在修改Student表的同时为sname属性添加唯一约束
ALTER TABLE Student ADD CONSTRAINT U_SNAME UNIQUE (SNAME);
(3)删除Student表的唯一约束
ALTER TABLE Student DROP CONSTRAINT U_SNAME
外键约束的基本语法:
constriant 约束名 foreign key(列名) references 表名(列名)
例:
(1)创建部门表DEPT和职员表EMP,在EMP表DEPTNO字段上创建外键约束,引用表DEPT中DEPTID字段数据。可使用如下语句:
CREATE TABLE DEPT
(
DEPTID CHAR(10) PRIMARY KEY,
DEPTNAME VARCHAR(20)
);
CREATE TABLE EMP
(
EMPNO CHAR(8) PRIMARY KEY,
EMPNAME VARCHAR(20),
DEPTNO CHAR(10),
CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTID)
);
(2)在修改EMP表的同时指定DEPTNO属性为外键
ALTER TABLE EMP ADD CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTID);
(3)删除EMP表的外键约束
ALTER TABLE EMP DROP CONSTRAINT FK_DEPTNO
检查约束的基本语法:
constriant 约束名 CHECK(条件表达式)
检查约束是用来核查每一行的值必须符合约束条件的完整性约束。Oracle提供CHECK子句,供用户指定关系的检查约束。
例:
(1)创建表EMP并约束部门代号在1-99之间。可使用如下语句:
CREATE TABLE EMP
(
EMPNO CHAR(8) PRIMARY KEY,
EMPNAME VARCHAR(20),
DEPTNO CHAR(10),
CONSTRAINT CHK_DEPTNO CHECK(DEPTNO BETWEEN 1 AND 99)
);
当输入数据时,其部门代号不在1-99之间,则会违反CHECK约束。
(2)在修改EMP表的同时约束部门代号在1-99之间
ALTER TABLE EMP ADD CONSTRAINT CHK_DEPTNO CHECK(DEPTNO BETWEEN 1 AND 99);
如果想查询CHECK约束条件,可以查询USER_CONSTRAINTS表中的SEARCH_CONDITION字段,具体语句如下:
SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,SEARCH_CONDITION
FROM USER_CONSTRAINTS
WHERE TABLE_NAME=’EMP’
(3)删除EMP表的检查约束
ALTER TABLE EMP DROP CONSTRAINT CHK_DEPTNO
非空约束的基本语法:
字段 数据类型 NOT NULL
例5-27:
(1)创建表EMP并约束职员姓名不能为空。可使用如下语句:
CREATE TABLE EMP
(
EMPNO CHAR(8) PRIMARY KEY ,
EMPNAME VARCHAR(20) NOT NULL,
DEPTNO CHAR(10)
);
当输入数据时,若其职员姓名为空,则会违反非空约束。
(2)修改表EMP并约束职员姓名不能为空
ALTER TABLE EMP MODIFY EMPNAME NOT NULL
注意:对于NOT NULL的ALTER TABLE语句与其他约束稍微有点不同。