oracle 基本表操作

这篇文章主要是总结自己在使用oracle时，用到的一些创建和查询表的语句，这里总结一下，方便以后查询。

1、创建表的基本语法：

create table 表名
(
字段名1（列名） 数据类型 列的特征,
字段名2（列名） 数据类型 列的特征(NOT NULL),
)

oracle常用的类型：

VARCHAR2 (size) 可变长度的字符串, 必须规定长度
CHAR(size) 固定长度的字符串, 不规定长度默认值为１
NUMBER(p,s) 数字型p是位数总长度, s是小数的长度, 可存负数
最长38位. 不够位时会四舍五入.
DATE 日期和时间类型
LOB 超长字符, 最大可达4G
CLOB 超长文本字符串
BLOB 超长二进制字符串
BFILE 超长二进制字符串, 保存在数据库外的文件里是只读的.

约束条件：

非空 NOT NULL
不允许重复 UNIQUE
关键字 PRIMARY KEY
按条件检查 CHECK (条件)
外键 REFERENCES 表名(字段名)

例子，创建原始表

create table test(  
id  number(10) not null primary key,  
name varchar(2) null ,  
city number(1) null  
);  

例子，根据查询创建表

create table ADD_USER(学校名称,学校代码,学科代码,学科名称) AS
SELECT e.name, d.unit_id, d.disc_id ,s.name
FROM dsep_base_unit e, dsep_base_unit_disc d ,dsep_base_discipline s
WHERE e.id=d.unit_id and s.id = d.disc_id and e.name = '南京邮电大学' 

例子，使用concat连接创建表：

create table ADD_USER_DAIMA1(学校学科代码,学校代码,学科代码) AS
SELECT CONCAT(CONCAT(学校代码,'_'),学科代码), 学校代码,学科代码 FROM add_user 

例子，使用substr创建表：

create table ADD_USER_TEST(LOGIN_ID,NAME,UNIT_ID,DISC_ID,SCHOOL_NAME) AS
select daima.学校学科代码,mingcheng.学校学科名称,add1.学校代码,add1.学科代码,add1.学校名称
from ADD_USER_DAIMA daima, ADD_USER_MINGCHENG mingcheng ,add_user add1
where substr(daima.学校学科代码,0,5)=add1.学校代码 and substr(mingcheng.学校学科名称,0,5)=add1.学校名称

创建表时的命名规则：
1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#
2)大小写不区分
3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来．
4)用和实体或属性相关的英文符号长度有一定的限制

创建表时注意事项：
1)建表时可以用中文的字段名, 但最好还是用英文的字段名
2)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
3)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引
4)一个表的最多字段个数也是有限制的，254个.

2、插入表

单行数据插入：

INSERT INTO table [(column1,column2,...)] VALUE (value1,value2,...)

单行数据插入，例子：

insert into dep (dep_id,dep_name) values(1,'技术部');

多表多行插入：

INSERT [ALL] [condition_insert_clause][insert_into_clause values_clause] (subquery)

多表多行插入，例子：

INSERT ALL
	INTO sal_history(emp_id,hire_date,salary) values (empid,hiredate,sal)
	INTO mgr_history(emp_id,manager_id,salary) values (empid,hiredate,sal)
	SELECT employee_id empid,hire_date hiredate,salary sal,manager_id mgr
	FROM employees
	WHERE employee_id>200;

有条件插入：

INSERT [ALL | FIRST]
	WHEN condition THEN insert_into_clause values_clause
	[WHEN condition THEN] [insert_into_clause values_clause]
	......
	[ELSE] [insert_into_clause values_clause] 
	Subquery；

有条件插入，例子

Insert All
	when id>5 then into z_test1(id, name) values(id,name)
	when id<>2 then into z_test2(id) values(id)
	else into z_test3 values(name)
	select id,name from z_test;

根据其他表的内容插入：

insert into ADD_USER_TEST(LOGIN_ID,NAME ,UNIT_ID ,DISC_ID,SCHOOL_NAME )
SELECT CONCAT(CONCAT(t.学校代码,'_'),t.学科代码),CONCAT(t.学校名称,t.学科名称) ,t.学校代码 ,t.学科代码,t.学校名称 from add_user t

Oracle清空表数据
 
这里介绍两种Oracle删除表数据的方式： 
Sql代码  
delete [表名]  
truncate table [表名]  
 
delete 删除表数据，表文件不会被清空.只会设置unused标记. 
truncate table 删除表数据，表文件也会被清空. 
 
以上删除都会有引用异常，使用以下处理方式: 
 
Sql代码  
alter table [表名] disable primary key cascade;  
truncate table [表名];  
alter table [表名] enable primary key ;  

更新数据库中某一列的数据：

update to_zhong set name ='山东' where log_id like '%what%'