Oracle学习笔记（一）

一．             字段类型

字段类型	描述	字段长度及其缺省值	说明
CHAR (size )	用于保存定长(size)字节的字符串数据。	每行定长（不足部分补为空格）；最大长度为每行2000字节，缺省值为每行1字节。设置长度(size)前需考虑字符集为单字节或多字节。	用于描述定长的字符型数据，长度<=2000字节
VARCHAR2 (size )	用于保存变长的字符串数据。其中最大字节长度由(size)指定。	每行长度可变，最大长度为每行4000字节。设置长度(size)前需考虑字符集为单字节或多字节。	用于描述变长的字符型数据，长度<=4000字节
NCHAR(size )	由字符集决定保存的是定长(size）的字符或字节。	每行定长（不足部分补为空格）。 对固定宽度的字符集来说，长度(size）是指字符的个数，对变宽的字符集来说，长度(size）是指字节的个数。最大长度(size）取决于保存在一个字符中的字节的长度，每行的最大长度为2000字节。由字符集决定缺省值为1个字符或1个字节。	用来存储Unicode字符集的定长字符型数据，长度<=1000字节
NVARCHAR2 (size )	由字符集决定保存变长的字符或字节。其中最大长度由(size)指定。	每行变长。对固定宽度的字符集来说，长度(size）是指字符的个数，对变宽的字符集来说，长度(size）是指字节的个数。最大长度(size）取决于保存在一个字符中的字节的长度，每行的最大长度为4000字节。由字符集决定缺省值为1个字符或1个字节。	用来存储Unicode字符集的变长字符型数据，长度<=1000字节
LONG	保存变长的字符串数据。	在表中每行的长度可变，最大长度为每行231 - 1字节或2G。	用来存储最大长度为2GB的变长字符数据
NUMBER (p, s )	保存变长的数字。P和s都是可选的。P指精度(precision)，即总位数。S指小数位(scale)，小数点右边的位数。	每行变长。每行的最大长度为21字节。	用来存储整型或者浮点型数值
DATE	保存定长的日期或时间数据，范围为公元前4712/01/01到公元9999/12/31。	每行固定为7字节长度，缺省值的日期描述（例如DD-MON-YY）由NLS_DATE_FORMAT参数设置。	用来存储日期数据
RAW (size )	保存变长的二进制数据。最大长度由size指定。	在表中每行的长度可变，最大长度为每行2000字节。	用来存储非结构化数据的变长字符数据，长度<=2000字节
LONG RAW	保存变长的二进制数据。	在表中每行的长度可变，最大长度为每行231 - 1字节或2G。	用来存储非结构化数据的变长字符数据，长度<=2GB
BLOB	保存二进制数据。	最大长度为232 - 1字节或4G。	用来存储多达4GB的非结构化的二进制数据
CLOB	保存单字节字符数据。	最大长度为232 - 1字节或4G。	用来存储多达4GB的字符数据
NCLOB	保存由字符集指定的单字节或固定宽度多字节或变宽多字节的字符数据。	最大长度为232 - 1字节或4G。	用来存储多达4GB的Unicode字符数据
BFILE	保存在外部文件的二进制数据。	最大长度为232 - 1字节或4G。	用来把非结构化的二进制数据存储在数据库以外的操作系统文件中
ROWID	数据表中记录的唯一行号	10   bytes	********.****.****格式，*为0或1
NROWID	二进制数据表中记录的唯一行号	最大长度4000	bytes
DECIMAL(P,S)	数字类型	P为整数位，S为小数位
INTEGER	整数类型	小的整数
FLOAT	浮点数类型	NUMBER(38)，双精度
REAL	实数类型	NUMBER(63)，精度更高

二．             DDL（改变表结构）

2.1 创建表

Create table student(

Sid number(10),

Sname varchar2(10)

) tablespace tt;

以上tablespace不是必须的。默认不写，则创建在登录的用户所在的表空间上

2.2 使用子查询创建表

create table myemp as select * from emp;

create table myemp as select * from emp where deptno=10;

create table myemp as select * from emp 1=2;

2.3 添加字段

Alter table student add age number(5);

2.4 修改字段

Alter table student modify age number(10);

Alter table student rename column age to Sage;

2.5 删除字段

Alter table student drop column Sage;

2.6 清空表中的数据

Truncate table student;

正常情况下删除数据，如果发现删除错了，则可以通过rollback回滚。如果使用了截断表，则表示所有的数据不可恢复了.所以速度很快（更详细的说明可查看Oracle体系结构）

2.7 删除表

Drop table student;

2.8 重名名表

Rename student to student1；

三．DML（改变数据结构）

3.1 insert语句

表间数据拷贝 insert into dept1(id,name)   select dno,dname from dept;

3.2 update语句

将编号为7779用户的工作换成编号为7566的雇员的工作和所属上级。

UPDATE myemp SET(job,mgr) = (SELECT job,mgr FROM myemp WHERE empno=7566) WHERE empno=7779 ;

如果子查询中返回的是空，则目标字段也更新成NULL.

3.3 delete语句

关键字delete from后跟准备从中删除数据的表名，这是必不可少的。关键字where后跟删除条件，为可选的。

如果用户想删除customer表的所有记录，可用SQL语句delete from customer；

如果用户准备删除state_cd为CA的客户记录，可用SQL语句delete from customer where state_cd='CA'；

3.4 merge语句

Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和upda tes操作. MERGE命令从一个或多个数据源中选择行来updati ng或inserting到一个或多个表.在Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的  

2、UPDATE和INSERT子句可以加WHERE子句  

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表  

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

create table test1(eid number(10), name varchar2(20),birth date,salary number(8,2));

insert into test1 values (1001, '张三', '20-5月-70', 2300);

insert into test1 values (1002, '李四', '16-4月-73', 6600);

select * from test1;

 

create table test2(eid number(10), name varchar2(20),birth date,salary number(8,2));

select * from test2;

 

merge into test2

using test1

on(test1.eid = test2.eid )

when matched then

update set name = test1.name, birth = test1.birth, salary = test1.salary

when not matched then

insert (eid, name, birth, salary) values(test1.eid, test1.name, test1.birth, test1.salary);

select * from test2;